texts: Ms VS2017 with SSDT has the 'User with Password' template but thinks it's wrong

texts:
In my database project when I choose the 'User with Password'  template in the Add New Item dialog, code is generated, kind of

CREATE USER [u2]
    WITH PASSWORD = 'jbniq_ushfbwc=l5nAxu8aapmsFT7_&#$!~<Jt}ho_aypip@'

GO

GRANT CONNECT TO [u2]

And it's immediately marked as having syntax error:

SQL46010: Incorrect syntax near 'jbniq_ushfbwc=l5nAxu8aapmsFT7_&#$!~<Jt}ho_aypip@'.

VS 2017 Comminity with SSDT 15.1.61903
Shoud I change some project settings or what?


texts: MySQL: How to get the maximum disposition and the maximum date from my database

texts:
I want to get the top disposition of every ID where 1 is the lowest and 13 is the highest, however when there are IDs with similar disposition (e.g. ID #2 having multiple dispositions of '9') I cannot get the record with the latest time / timestamp.

How can I group the results by ID getting the maximum disposition and if there are multiple disposition, get the most recent?
(e.g. ID #2 getting '2017-04-06 14:16:00' as the top or max.)

edit:
I forgot to mention that I also need to group them by date. I have a separate column for date. The expected output is: Latest + Highest dispo of ID #2 on April 6 and Latest + Highest dispo of ID#2 on April 7 and so forth.

edit2:


texts:
Today my database world was shattered.

For my whole lifetime I thought, comparing with a null value in Oracle results in the "false-case".

But not so for the decode function.

Anybody know, what's the idea behind this?


texts: Why does it take so long for Postgres to return a sequence number?

texts:
I have an application that does bulk loads into a large table (100 million rows). I am using Postgres' COPY FROM functionality to load data from a flat file. The target table has a primary key of id.

To get the bulk insert to work, I created the ids for each row in the load file using:

 SELECT  nextval('apps_id_seq'::regclass)
 FROM    "apps"
 ORDER   BY "apps"."id" ASC
 LIMIT   1

Unfortunately, I am no seeing this query take in excess of 150 seconds. It's causing a whole lot of backups, because some of these files have tens of thousands of rows in them.

Yet when I run that at the command line, I get the return in thousandths of a millisecond. Here is an explain analyze:

                                                            QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.57..0.64 rows=1 width=4) (actual time=0.016..0.017 rows=1 loops=1)
   ->  Index Only Scan using apps_pkey on apps  (cost=0.57..15886651.40 rows=228128608 width=4) (actual time=0.015..0.015 rows=1 loops=1)
     Heap Fetches: 0
 Total runtime: 0.030 ms

What could it be that causes the delay? The delay is being reported from the NewRelic service.


texts:
Is there anyway to get back database if I deleted database. Is mysql creates temp file which contain temporary data from database and whether I can recover from that

Edited:

cp my.cnf my.cnf41

mv /var/lib/mysql mysqlbackup

Yum remove mysql mysql-server

yum install mysql mysql-server


texts: The efficiency of an hierarchical table and query

texts:
I have this hierarchical table that will end up containing 5 or 600 unique names, many of which will be assigned to multiple parents, such as "Hunt" below...

+-------------+----------------------+--------+
| category_id | name                 | parent |
+-------------+----------------------+--------+
|           1 | Phillips             |   NULL |
|           2 | Barnes               |      1 |
|           3 | Moore                |      2 |
|           4 | de Mohr              |      3 |
|           5 | Oswald               |      9 |
|           6 | Hunt                 |      2 |
|           7 | Burnham              |      6 |
|           8 | Buckley              |      7 |
|           9 | Paine                |      4 |
|          10 | Sturgis              |      6 |
|          11 | Hunt                 |      3 |
+-------------+----------------------+--------+

Query:

SELECT
  t1.name AS lev1,
  t2.name AS lev2,
  t3.name AS lev3,
  t4.name AS lev4,
  t5.name AS lev5,
  t6.name AS lev6
FROM category t1
  LEFT JOIN category t2
    ON t2.parent = t1.category_id
  LEFT JOIN category t3
    ON t3.parent = t2.category_id
  LEFT JOIN category t4
    ON t4.parent = t3.category_id
  LEFT JOIN category t5
    ON t5.parent = t4.category_id
  LEFT JOIN category t6
    ON t6.parent = t5.category_id
WHERE t1.name = 'Phillips'

Output:

+-------------+-------------+-------------+---------+---------+---------+
| lev1        | lev2        | lev3        | lev4    | lev5    | lev6    |
+-------------+-------------+-------------+---------+---------+---------+
| Phillips    | Barnes      | Hunt        | Sturgis | NULL    | NULL    |
| Phillips    | Barnes      | Moore       | de Mohr | Paine   | Oswald  |
| Phillips    | Barnes      | Hunt        | Burnham | Buckley | NULL    |
+-------------+-------------+-------------+---------+---------+---------+

I just have 2 questions about this.

Is it going to be wiser to incorporate a linking table to avoid duplicating "names" for each parent in one table, or is there some other way to avoid this, or is this even an issue?

The output I'll be getting for each page will be a long, fairly complex outline layout, which would probably mean that I'd need to run this kind of query sometimes up to 30 or 40 times per page. Is this kind of query something I can run that many times to load a page without a problem, or do I need to come up with something more efficient?


texts: Mongodb - sharding + replicating an existing collection

texts:
I have an existing mongo (3.4) database that is growing quickly, so I need to shard it. I am only getting started with sharding/replication and I'm going through the documentation at the moment.

Is this a logical sequence of events for sharding and replicating an existing collection:

create 3x config server replica set
connect the existing mongo server with the config set
enable sharding on the existing db
create another mongo server and connect with the config set
shard existing collection
create replica servers and connect with the config set

Thanks


texts: Date dimension changes between Test and Production Server

texts:
I have a cube deployed into a Test environment and Production environment. I've noticed something strange happen with the date dimension.

When deployed into our test environment, the Date Key comes out looking like this:

However, when deployed into Production it comes out looking like this:

Both servers are running SQL Server 2012 and Analysis Services. On the test environment analysis services is installed on the same machine as SQL Server on the production one it is a different server. Apart from that they should be identical...

I would like the DateKey to look like how it appears in the Test environment. I.e. 2012-12-01 00:00:00.


texts: SQL Server 2012 database backup successfully report but no backup file

texts:
I have created maintenance plan in SQL Server 2012. And every day, the maintenance plan should backup the database. There is no database backup file when I look in the folder where the backups must be stored. But SQL Server logs history about this maintenance plan are successful.

What is the problem?

I am using SQL Server 2012. The operating system is Windows Server 2008 R2.

Thank you for paying attention.


texts: Advanced design question related to compound primary keys

texts:
As a database design expert, I had a discussion with an academic person about database design and I need some arguments on why some design is bad or not normalized or bad practice. As we cannot discuss here or have opinions, I'm just asking why something is wrong. A possible answer would be to show a clear violation of normalization rules or a pointer to a commonly accepted design standard. Or even an answer that this cannot be decided based on clear rules.

So, let's assume we have three tables. First is a Client table with values C1 and C2. Then we have a Users table with users A, B, C, D, and users A and B belong to Client C1, while users C and D belong to Client C2, so the Users table has a foreign key to the Client table. Then we have a Products table with values 1, 2, 3. Now we need to connect Products with Users (n:n), so we need a connection table.

If we list all possible values for the planned connection table, we get (Product, User, Client the user belongs to):

1 A C1
2 A C1
3 A C1
1 B C1 *
2 B C1 *
3 B C1 *
1 C C2
2 C C2
3 C C2
1 D C2 *
2 D C2 *
3 D C2 *

In my experience, where I lack of proof, every table should have a single primary key, not related to any content. The (wrong) design I was confronted with, made the connection table to not only have the Product-ID and User-ID, but additionally store the redundant field Client-ID (which could be looked up in Users table). Argument was that this is the only way to ensure that products are not connected twice to users of the same client (the values with the star in the above list would not be allowed). This can be done by adding a unique key on the connection table fields Product-ID and Client-ID, but it requires to have the Client-ID be present in that table, although it is already defined in the Users table and could be looked up there.

So why is this wrong? Only because of the redundant data (normalization rule)? How can we otherwise ensure then that no duplicates are stored (only by application logic or triggers)?

In a different scenario (but same underlying question), let's assume we have a grid with data, like an Excel sheet. To store this data we would have a table with the data per cell and foreign keys to the columns/rows table. The columns and rows are not different, so both columns and rows are stored in the same lookup table. This ColumnRow table had in the bad design a compound primary key (two fields), so the foreign key in the grid table would need both fields for each foreign key, requiring a total of 4 fields to reference the row and column. Now there's a restriction that the first part of the two foreign keys must be identical for both row and column, so only the "common" first part was stored ensuring this. If I design this with normal (single, not compound) foreign keys, we cannot ensure that both row and column belong to the same higher-level thing. So this makes it even more difficult for me to argue for single primary keys.

So in general, is there no generic consent to have single (non-compound) and data-unrelated primary keys? Or is this all just opinion-based? Is there expert (reference) literature about this subject that I could point to?


texts: How can I count all children & grandchildren until the 7th level?

texts:
I have 3 tables right now

 1. members
 2. members_parents
 3. members_status

How can i count the children & grandchildren until to the depth level and separate the count() for the status 0 = free and 1 = subscriber

table: members - list all the records of members
table: members_parents - list of members & the parent member
table: members_status - list the members & parents status where 0 or 1

tbl_members_parents:

id   member     parent
1    10000      NULL
2    10001      10000
3    10002      10001

and so on ...

How can i count the child & grandchildren until to the 7th level of grand children ?

Thank You


texts: Are newly inserted data automatically stored in InnoDB buffer pool

texts: I understand that innoDB will store select statement result in the buffer pool, does InnoDB store newly inserted data (insert into statement) into buffer pool even after data has committed and wrote to disk?


texts: How to update integer primary key adding 1000?

texts:
I have the following primary keys in my table: 1,2,3,4,5...

Is it possible to update these keys to: 1001,1002,1003,1004,1005... ?

UPDATE mytable SET `id`= (select `id` from mytable where id=??)+1000


texts:
My database contains more than 200 tables. When I want to modify or insert a table, I find it difficult to find it among all those tables.

Is there any way to collect related tables in a folder structure inside my DB? I'd have groups of folders, each one containing tables for a certain part of my application.
If that's not possible, how can I manage all these tables and make it easy for myself?


texts: Query not returning result if the join fails (how to get it to return zero)

texts:
My query is trying to find monthly records with missing days, ie if month has 31 days but less than 31 records I want to detect it. This works fine unless all 31 records are missing when nothing is returned! (By the way Daily is a big table). So how do I get the transmitters with no records at all in a particular month to return zero?

  SELECT SUM(w.amount) AS MonthlyTotal,COUNT(*) AS Days,DAY(LAST_DAY('2016-06-01')) AS DaysInMonth
    FROM Daily  w,Transmitter t
    WHERE t.SiteID='1907' AND t.UtilityID='1' AND w.TransmitterID=t.TransmitterID  AND NOT t.deleted
    AND w.TimeStamp  BETWEEN '2016-06-01' AND LAST_DAY('2016-06-01') GROUP BY t.TransmitterID;


texts: When would one want to use Oracle transparent data encryption

texts:
Oracle TDE features comes with various limitations such as not being able to encrypt columns which are used in foreign key constraints. And probably is available only with enterprise edition which costs upwards of 100 thousand dollars.

Why would one want to pay and use TDE instead of simply using file system encryption with the OS? This is free and byepasses various limitations regarding FKs etc.

Am I missing some advantages that it provides?


texts:
I have the following piece of statement entered into MySQL5.6 Command Line Client. However, the following error was received. I haven't even been able to add in END// Delimiter; after the select statement.

At the same time, i was wondering after the stored procedure has been created successfully, how do i CALL the stored procedure without the command line but using java codes.

Kindly assist. Greatly appreciated!


texts: Conflict resolution for hash-backed unique constraint of large text bodies

texts:
So we've just run into
ERROR: index row size 2736 exceeds maximum 2712 for index "foo__bar__un"
  Hint: Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full text indexing.

(foo is table, bar is column and un for "unique")

Consider a function index of an MD5 hash of the value

Alright, let's.
This is simple enough to do:
ALTER TABLE foo DROP CONSTRAINT foo__bar__un;
CREATE UNIQUE INDEX foo__bar__un ON foo(md5(bar));

So far, so good, this is the solution both proposed in the error hint and found online in numerous places.
What I haven't found yet, though, is how to deal with collisions.
In my case, I have a database with results for biochemical experiments. I can't just go to the scientists and say "can you please change your result a tiny bit so we can actually store it in our database? yes, I understand this may cost a couple of your customers' lives and you personally a potentially prohibitive amount of money, but I can't allow you to store it otherwise".
(Realistically, we'd just drop the constraint way before that, but you get my point.)
I do understand that collisions are almost negligibly rare, but I'd rather know I can deal with them if against all odds one should happen. And given that this isn't a niche issue only we are having, I'm sure I'm not the only one to have ever wondered about this.
So what is the way to deal with collisions, in this case? I'd rather not have to replace the unique index with a DIY trigger-based workaround, if possible.
Can I somehow shoehorn in an additional "equals" check to be executed for entries with the same hash, for example, and only throw an exception if they actually are?


texts: After Restore Log_reuse_wait_desc of Replication

texts:
I'm wondering if anybody has ran into this issue before and has any suggestions on what's causing it/how to fix it.  We are restoring a database into lower Dev environments and after the restore the log_reuse_wait_desc is Replication.  The problem is we are not using keep replication or CDCs.

This happens in a couple of different setups but I'll keep this initial one simple.

Database from a 2008 instance restored to a 2012 instance.  Only way to clear it out is to create a test publication on any random table, no subscription needed, and log_reuse_wait clears.


texts: How can I swap two values from particular column in a table in Postgres?

texts:
Consider a table test contains an attributes of id and name.

The values for that.

Id   |   Name
1    |   Raj
2    |   Kumar

From the above sample table, I know only the id, So through the Id I need to swap  the name, like given below,

Id   |   Name
1    |   Kumar
2    |   Raj


texts: One to many relation getting recent records plus one column from previous record

texts:
Table 1

ProductID   Status
PD_1       OPN
PD_2       CLS

Table2

Table2_id   ProductID   Amount
1           PD_1        900
2           PD_1        800
3           PD_2        1000
4           PD_2        900
5           PD_2        700

Final expected result

ProductID   Recent_Amount   Previous_Amount   Status
PD_1        900             800               OPN
PD_2        1000            900               CLS


texts:
I am needing to alter the time piece of a datetime field.  I tried to use this syntax, but it gives me an error of

  Incorrect syntax near the keyword as

What syntax can I use to alter the end time of my datetime field?

case when CAST([pcpts].stopit As DateTime)+CAST(case when CAST([pcpts].stopit As Time) > '11:00:00.000' then '11:00:00.000' ELSE CAST([pcpts].stopit As Time) end as datetime) As [SI]

EDIT
What I am after is if the time value for [pcpts].stopit is > 11:00:00.000 I want my query to show 11:00:00.000,

if the time value for [pcpts].stopit is < 11:00:00.000 then show [pcpts].stopit

Examples would be:
[pcpts].stopit = '01/01/2014 10:00:00.000'
query output should show '01/01/2014 10:00:00.000'

[pcpts].stopit = '01/01/2014 16:00:00.000'
query output should show '01/01/2014 11:00:00.000'


texts:
"persons" table:

id | name
-----------
1  | alice
2  | ben
3  | claire

"items" table:

id | name        | person_id | is_active
-----------------------------------------
1  | apple       | 1         | NO
2  | banana      | 2         | YES
3  | carrot      | 2         | NO
4  | dragonfruit | 3         | NO
5  | eggplant    | 3         | YES

So by running the below query:

SELECT * FROM persons p
LEFT JOIN items i ON p.id = i.person_id

I get:

id | name   | id | name        | person_id | is_active
-----------------------------------------------------
1  | alice  | 1  | apple       | 1         | NO
2  | ben    | 2  | banana      | 2         | YES
2  | ben    | 3  | carrot      | 2         | NO
3  | claire | 4  | dragonfruit | 3         | NO
3  | claire | 5  | eggplant    | 3         | YES

Rules in application-level as follows:

Each person must have at least 1 item.
Each person can have 0 or 1 active items only.

I want to filter the above result to get:

Get 1 record only for each person.
Get the row with is_active = "YES" if person has an active item. If person doesn't have any active item, get 1 record (any record) of that person.
Order result by person id DESC.

So I'm trying to get something like this:

id | name   | id | name        | person_id | is_active
-----------------------------------------------------
3  | claire | 5  | eggplant    | 3         | YES
2  | ben    | 2  | banana      | 2         | YES
1  | alice  | 1  | apple       | 1         | NO

EDIT:

I need to get the persons records (even ones without an active item). From the items table I need only the active item's name. If the person doesn't have an active item, it's fine if I can get it as empty string or null. So something like this will also be ok:

id | name   | item_name
-----------------------------------------------------
3  | claire | eggplant
2  | ben    | banana
1  | alice  |


texts: how to connect SQL server from oracle in SQL developer

texts:
I have two connections in SQL developer that one of them is SQL server (A) and the other one is oracle (B).
how can I make a database link between this two connections from (B) to (A) to select connection A data from connection B?


texts: Is it a good idea to create an index just for new data to increase index creation performance?

texts:
I have a table with about 350 M rows with 45 GB, it has around 10 columns ,simplified version below

CREATE TABLE PAYMENT (
    ID NUMERIC(20) PRIMARY KEY,
    CREATION TIMESTAMP,
    VALUE DECIMAL(10, 2),
    USER_ID NUMERIC(20)
    ....
)

Once known that

Table have a really high concurrency
It receives about 3M INSERTS and 5M UPDATES per day, update uses the PK
By company policies reasons

I must keep old data to grant history
I can't partitioning that table
I'm really going to have this one table with all data and there is not chance to change that

I have to create an index for (CREATION, USER_ID) columns

Is it a good idea to create an index excluding old data to get the index created faster, avoiding table locks and save storage knowing that my queries will just hit the new data?

CREATE INDEX ON PAYMENT(CREATION, USER_ID) WHERE CREATION > TIMESTAMP '2018-03-25 00:00:00';


texts: MySQL Bug reference in release notes, but where?

texts:
This may be a daft question, but on the MySQL release notes, a lot of them are followed by BUG Reference numbers. But most of these BUGS don't exist when you search for them at bugs.mysql.com.

example: InnoDB: A full-text query raised an assertion. Under certain circumstances, DDL operations such as ALTER TABLE ... RENAME caused full-text auxiliary tables to be removed on server restart. (Bug #13651665)

Despite my best efforts I can't find out where to find these BUGS. Can anyone point me at the right location?


texts: Do frequent DML operations on a table with indexes and huge data affect SELECT query performance?

texts:
If I have frequent DML(insert,update,delete) operations on a table with huge data with clustered and non-clustered indexes. Will it affect the Performance of SELECT queries on that table with a where condition on index columns,
Or will the performance remain same as when there are no DML operations?
This Link says UpdateStatistics does block Select queries. So will DML operations cause UpdateStatistics. It says " The only time you wouldn’t be in control of this is if AUTO_UPDATE_STATISTICS is enabled on your database and AUTO_UPDATE_STATISTICS_ASYNC was disabled.". So is changing these values the best solution?
Note:
 I will be using isolation level (Read Uncommitted) for the SELECT query which should not be blocked by locks.


texts: When is it appropriate to use SQL Server Developer Edition?

texts:
Do you use SQL Server Developer Edition on server-class machines in DEV and STAGING environments?

I am working on a large project where (if it passes the proof of concept stage) we will have several large geographically distributed enterprise class database servers running SQL Server 2012 Enterprise Edition.

We will have a production environment will initially have 3 servers, our Staging environment will have a minimum of 3 servers, and our Development environment will have 1 server (hosting three instances).

I was under the impression that we would only need to acquire Enterprise licences for the actual production servers, and we could get by with developer edition in our developer and staging environments because they are not "production".

Other sources have told me that we need to have an enterprise licence on all of the above mentioned machines, and that the developer edition is only meant for a single developer on their workstation.

Since developer edition has all the sexy features of Enterprise, I can't really see the value of it on a workstation class machine, especially for developing and testing the type of high availability system we are building.

If we have to fork out Enterprise licences for a dev server, that will just about kill our proof of concept stage, thus killing the project.  Forcing an enterprise licence on a staging environment will make management just want to skip staging altogether.


texts: Execute permission denied on object sp_start_job

texts:
I need to allow a user to kick of a specific agent job without having any ability to start other ones. To accomplish this, I've created the following procedure (simplified):

ALTER PROCEDURE [dbo].[RunJob]
    @job_name nvarchar(200)
WITH EXECUTE AS 'sysadminaccount'
AS
BEGIN
    --SET NOCOUNT ON;
    BEGIN TRY
        EXEC msdb.dbo.sp_start_job @job_name = @job_name

        -- Wait for job to finish
        DECLARE @job_history_id AS INT = NULL
        DECLARE @job_result AS INT = NULL

        WHILE 1=1
        BEGIN
            SELECT TOP 1 @job_history_id = activity.job_history_id
            FROM msdb.dbo.sysjobs jobs
            INNER JOIN msdb.dbo.sysjobactivity activity ON activity.job_id = jobs.job_id
            WHERE jobs.name = @job_name
            ORDER BY activity.start_execution_date DESC

            IF @job_history_id IS NULL
            BEGIN
                WAITFOR DELAY '00:00:01'
                CONTINUE
            END
            ELSE
                BREAK
        END

        -- Check exit code
        SET @job_result = (SELECT history.run_status
        FROM msdb.dbo.sysjobhistory history
        WHERE history.instance_id = @job_history_id)

        RETURN @job_result;

    END TRY
    BEGIN CATCH
        THROW;
        RETURN;
    END CATCH
END

However, when I call this procedure (having verified it is running via "sysadminaccount"), I get the following error message:

  Msg 229, Level 14, State 5, Procedure sp_start_job, Line 1
  The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'.

The account is a member of the sysadmin role, so I as understand it there shouldn't be any problems kicking off jobs. I've verified that it is a member of the three sqlagent roles in msdb, and those roles all have execute permission on sp_start_job.

How can I give this account the appropriate permissions? Is there something else that needs to be done because of the impersonation?


texts: MySQL, get rows for non-matching reference or reference does not exist in other table

texts:
MySQL, select non-matching reference or reference does not exist in other table.

For example.

table: category
category_id,    category_name
1               XYZ_1
2               XYZ_2
3               XYZ_3
4               XYZ_4
5               XYZ_5
6               XYZ_6

table: type
type_id,    type_name
1           A
2           B
3           C
4           D

table: type_match
match_Id,   type_id category_id
1           2       1
2           3       1
3           4       1
4           1       1
5           2       2
6           3       2
7           2       3
8           4       3
9           2       4

I need category_id where type_id is not 3 or category_id does not exist in type_match table:
expected result:

category_id
3  // category_id exist in type_match table but not matching type_id = 3
4  // category_id exist in type_match table but not matching type_id = 3
5  // category_id does not exist in type_match table
6  // category_id does not exist in type_match table

Please help.


texts: Postgres permission issues for new views

texts:
I have a RDS postgres database that only uses the public schema.

I expect the webservice user to get access to all newly created views. When I create a view using the master user, I see all the grants as expected.

Here are the DEFAULT PRIVILEGES I have setup.

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLES TO webservice;

When the rormigration user creates a view in the public schema, I see it is owned by rormigration  and none of the DEFAULT PRIVILEGES are present. I have to switch the owner to master and grant rights.

What can I do to fix this permission issues?


texts: Is there any way to run the definition of multiple stored procedure at a time?

texts:
I have created the definition of more than 200 stored procedures. My problem is that I have to execute them on a different server and on a different database.
The process which I am following is that first I check each stored procedure that it exists on that server and database or not. If not then I create them else I drop the earlier definition and create the new one.

So I just wanted to know that is there any other way out. As I have to perform this activity for each stored procedure. I want to create a script or so, which can run and create the definition of all my stored procedure on the server and database.


texts: PostgreSQL : send all rows of a table to a function

texts:
I have a table with one column (double precision numbers). I also have made a function (with double precision numbers as input and output), and I want to send every value of my table to the function, and store the output in a second (new) column.

What is the best way to do this? Make a void function with a loop in it? Or are there better ways?

Thannks


texts: Replication Access denied in Replication

texts:
I have a database on-prem, and I have an Azure account.

I need to establish transactional replication from the on-prem to Azure.

First of all I created a publication and after that I made a subscription to it.

Then I went to the publication and started the job.

It gives me the following error.

Any solution for that?


texts: Possible to CREATE EVENT NOTIFICATION with a different owner?

texts:
We are in the process of removing a previous dba login and he owns all the endpoints and event notification objects. Endpoints were easy to change; Event notification objects not so much.

I found this thread about changing the owner of an event notification object (you have to drop and recreate). I don't want to go through this process again if I can avoid it. I doubt it's possible, but outside of logging in as another user, can you create an event notification that runs as sa, etc.?


texts: Sqlite Affinity for hashvalues with certain size

texts:
I must store many SHA256-values in a sqlite-database and now i wonder which sqlite-affinity would be the correct one. SHA256-values have an output-length of 256bit=32byte. I am looking for an sqlite-affinity which can handle this but which also does not "waste" diskspace since in my case i have very many datasets. I have read on this site many useful things about datatypes in sqlite but i still do not know which type would be correct for SHA256-values. i would prefer something like "bytearray(32)" so i thought varchar(32) could be the one. but then i remembered that i can probably not converting all of this bytes to printable characters to get them stored as character-array. So it seems that I must use Blob.

Unfortunately do not really find any useful information about limiting the Blob-size in sqlite and how sqlite handles blobs internally. I would like to use the information that my hash-values have always the same length to optimize the disk-usage of the database: I want sqlite to know that for this Blob-column it always have to reserve only 32 bytes, never more. So I would like to use something like Blob(32) as column-type. Is this possible? And if yes: Does it make sense or is there any fallacy?


texts:
I have a table (mysql) in the following pairs' structure:

 home   | door
 table  | window
 door   | table
 window | home
 home   | table
 table  | door

I'm looking for the best solution (fast and logical, to run on large tables) to get all unique triplets i.e home is paired with door and also with window so that's a triplet, and it's cardinal order so window,door,home is not distinct from door,home,window etc.

Wanted results:

home  | door   | window
home  | door   | table
home  | window | table
table | door   | window


texts:
Team,
Can we allocate 10 gb RAM to postgres out of 30gb on Linux machine.
If yes can someone share me the steps


texts:
Assume you have 2 SSAS databases, DB1 and DB2, that are identical.

Now they branch off. Changes A and B are made to DB1 and changes C and D are made to DB2.

Is there an easy method to migrate changes A and B from DB1 to DB2 without overwriting changes C and D made in DB2?

I'm using SSAS 2005 - does this functionality improve in 2008? Third-party tools that do this?


texts: How to monitor a single db of an instance in SQL Server

texts:
I am trying to program against a vendor supplied database, and would like to understand what SQL statements or procedures they are calling.

Is there a way I can monitor a specific database?  The system was setup with both the production and test database in the same instance (so monitoring an instance is too broad).


texts: Performance of VIEWs with UNIONs in different databases

texts:
For various corporate and historical and replicating reasons, I have multiple databases that contain similar-structured tables, holding the main production data used as a source for other home-made applications and reports.

In a particular application, I created views that UNION all theses similar tables, adding a column to know the source of each row. The problem I have is that this view, as explained in Books Online and various other posts in various forums, cannot have any indexes (because of the UNION and the fact that the source is in another database, thus it cannot be SCHEMABOUND), and so is performing quite poorly.

The question is: if I need the data as live as possible, what other choices do I have to increase performance somewhat?

Database A, table X
Database B, table X
Database C, table X

Application S (in it's own database), containing view:

CREATE VIEW vw_X AS
SELECT 'A' as source, * FROM A..X
UNION
SELECT 'B' as source, * FROM B..X
UNION
SELECT 'C' as source, * FROM C..X

I am using SQL Server 2012, if there are server-specific elements that could help me.

I am lucky, for now, all databases are on the same server, so I am not using linked servers. But application S can be using Synonyms to reference the various "table X" if needed.

Yes, I did a lot of searches. I got a lot of "it's impossible", but came up empty solution-wise, other than the idea of copying the tables locally on a schedule, something hard for us considering the amount of data that would need to be copied over, and how often it would need to be copied. I am looking for alternative ideas that could lurk in the corners.

The option of turning the view into a stored procedure could be considered.

Thanks in advance


texts:
We are in a process of upgrading our SQL Server database. We are also ditching the Oracle databases that we had and are migrating all the databases to SQL Server 2016.

The internal db structure is something like, we have two Master databases, which have all the organization and product information. The rest of the web applications access these two databases frequently (mostly read-only).

For web applications, we have one user per application, which is also added in the Master databases (to ensure access of information).

Over the years, many of the web applications are decommissioned and some new ones have been created, (some are in the process of development), so we are also deleting their users and checking others as well.

What I want to inquire is that, what are the drawbacks of having a common user for all web applications? Does it affect performance of the web applications or the DB server? Which one will give me optimum performance and will be better both for the applications and the DB?

1 User for all web apps.
1 User for each web app added to the master databases as well.


texts:
I am planning the design of a star schema for a data warehouse. The data will be loaded from 2 separate staging tables (Client and System).

The end user will need to be able to compare any differences between the 2 sets of data. They will also need to be able to match items from either source. e.g. ItemA from SourceA matches ItemA from SourceB.

For the design, I am considering having both sources on one row in the Fact Table. So for SourceA (Client source), there would be NULL values for SourceB columns (System source) and vice versa. The 'Source' column is then used to flag whether the data is Client source or System source.

I am thinking it will be easier to do this design especially when we may need to match items. So on a match, the NULLs would be replaced with the matched item's values.

Am I correct with this design or is there a better solution? Thanks!

Schema design:


texts: Enforce a column as unique based on another column value

texts:
I want to make a column unique, but only if a different column is a specific value.

Consider the following table:

CREATE TABLE [SampleTable]
(
     [Id] INTEGER NOT NULL IDENTITY(1,1)
    ,CONSTRAINT [PK_SampleTable]
        PRIMARY KEY ([Id])

    ,[Code]      NVARCHAR(255) NOT NULL
    ,[Deleted]   BIT           NOT NULL DEFAULT 0
    ,[CreatedOn] DATETIME      NOT NULL DEFAULT GETDATE()
);

The intention is that an item can be 'deleted' by setting the [Deleted] column to 1.

What I want is for the [Code] column to be enforced as unique, but only among non-deleted rows.

Enforcing data correctness at the database level is generally my strong preference. However, I've used this pattern a lot in the past, but have never been sure if it were possible to enforce this kind of constraint at the database level. Deadline pressures being what they are, I never bothered to find out. So I've always just enforced them at the application level.

If there's a way to do it though, I'd really like to know what it is.

To be clear, I can't just use a combined unique constraint, because I need to be able to support the following data:

    [ID]   [Code]   [Deleted]  [CreatedOn]
=====================================================================
     1     'ABC'    1          Ages ago
     2     'ABC'    1          A while ago
     3     'ABC'    1          Quite recently, actually
     4     'ABC'    0          Just a moment ago!

A combined unique constraint won't work, because for my purposes three different 'deleted' entries with the same code is valid.

I ask this question because the 'enforcing this at the application level' policy has recently bitten me in the ass when it came to integrating data from a third-party application. It would have been nice if the database had rejected the bad integration data outright, because fixing the integration before it happened would have been a lot easier than cleansing the data after it happened incorrectly.

I'm using SQL Server 2008 R2. But I don't mind upgrading if I have to to get this functionality, I've been meaning to upgrade anyway.

Aaron answered this pretty much immediately. I needed to be using a filtered unique index.

The following code demonstrates the solution.

IF EXISTS (SELECT * FROM SYS.TABLES WHERE [name] = 'SampleTable')
BEGIN
    PRINT 'Dropping Table [SampleTable]';
    DROP TABLE [SampleTable];
END;
GO

PRINT 'Creating Table [SampleTable]';
CREATE TABLE [SampleTable]
(
     [Id] INTEGER NOT NULL IDENTITY(1,1)
    ,CONSTRAINT [PK_SampleTable]
        PRIMARY KEY ([Id])

    ,[Code]      NVARCHAR(255) NOT NULL
    ,[Deleted]   BIT           NOT NULL DEFAULT 0
    ,[CreatedOn] DATETIME      NOT NULL DEFAULT GETDATE()
);

CREATE UNIQUE INDEX
    [UNQ_SampleTable_Code]
ON
    [SampleTable]([Code])
WHERE
    ([Deleted] = 0);

INSERT INTO [SampleTable] ([Code],[Deleted]) VALUES ('ABC', 1);
INSERT INTO [SampleTable] ([Code],[Deleted]) VALUES ('ABC', 1);
INSERT INTO [SampleTable] ([Code],[Deleted]) VALUES ('ABC', 1);
INSERT INTO [SampleTable] ([Code],[Deleted]) VALUES ('ABC', 1);
INSERT INTO [SampleTable] ([Code],[Deleted]) VALUES ('ABC', 0);
INSERT INTO [SampleTable] ([Code],[Deleted]) VALUES ('ABC', 0);

UPDATE [SampleTable] SET [Deleted] = 0 WHERE [Id] = 1;

SELECT * FROM [SampleTable];

The sixth (final) insert and the update both fail because of the filtered index.

I'll make good use of this in future. Thanks Aaron!


texts: Fail to execute SQL command asynchronously

texts:
I want to invoke a SQL command INSERT INTO for 3,000,000 times to insert 3,000,000 records via ADO.

Other than the BULK INSERT command, I also want to utilize the ADO.Command object to execute the SQL command asynchronously.

So I do as follows:

  CString strSQLChange;

  _ConnectionPtr pConnection = NULL;
  _CommandPtr pCmdChange = NULL;

  // Open connection.
  pConnection.CreateInstance(__uuidof(Connection));
  pConnection->Open (strCnn, "", "", adConnectUnspecified);

  // Create command object.
  pCmdChange.CreateInstance(__uuidof(Command));
  pCmdChange->ActiveConnection = pConnection;

  for (UINT uIndex = 0; uIndex < 3000000, uIndex ++)
  {
     strSQLChange.Format(_T("INSERT INTO MyTable VALUES (%u, %u, %u);"), uIndex, uIndex *2, uIndex *3);
     pCmdChange->CommandText = _bstr_t(strSQLChange);
     pCmdChange->Execute(NULL, NULL, adCmdText | adAsyncExecute | adExecuteNoRecords);
  }

The first execution is OK. But the second one will cause the following error

  Operation cannot be performed while connecting asynchronously

Does that means the ADO command object cannot execute the SQL command asynchronously?

I am also thinking about using BULK INSERT command, but since there are multiple tables, I also need to execute BULK INSERT command multiple times. It is better to execute them asynchronously so executing SQL commands asynchronously is also a solution I need to seek.


texts: What is a common way to save 'debit' and 'credit' information?

texts:
I'm working on an accounting system, and for each transaction I need to save if this is either debit or credit. I can think of two ways (MySQL database):

METHOD 1

Amount (decimal)
Type (enum, debit/credit)

METHOD 2

Debit (decimal)
Credit

In the first setup, I save the type of transaction, but in the second way I rather save the amounts in the debit or credit column. Pros of this method are that I can more easily sum both debit and credit totals than in method 1. But I am wondering if there is a common way to do this?


texts: Create a temp table, loop, add data, select from it? "Query has no destination for result data"

texts:
I am creating a temp table, and am fetching data from multiple tables and inserting data into it. When I try to get the data back from the table I get an error:

[42601] ERROR: query has no destination for result data
Hint: If you want to discard the results of a SELECT, use PERFORM instead.

do
$$
    DECLARE
        sampleproductId   varchar;
        productIds        text[] := array [
            'abc1',
            'abc2'
            ];
        tId              varchar;
        DECLARE result  jsonb;
        DECLARE resultS jsonb[];
    begin
       CREATE TEMP TABLE IF NOT EXISTS product_temp_mapping
        (
            accountid      int,
            productUID      varchar,
            sampleproductId text
        );
        FOREACH sampleproductId IN ARRAY productIds
            LOOP
                tId := (select id
                        from product.product
                        where uid = sampleproductId);
                INSERT into product_temp_mapping (accountid, productUID, sampleproductId)
                select accountid, tId, sampleproductId
                from product.accountproductmap
                where productId = cast(tId as int);
            END LOOP;
        select * from product_temp_mapping;
    end ;
$$;

Is this the right way to do it? This is the first time I am doing something with a temp table.


texts:
Hello everyone that are reading this!

I'm trying to perform a fixpack install into DB2 9.7 version database in a linux 64 bits server.

i terminate my database, stop my instance.

And when i try to install with the following command:

./installFixPack -b DB2DIR

This error message appear:

"The 32-bit library file libpam.so is not found on the system"

I have been search in many sites, i've tryed the the following tips:

ln -s /lib/i386-linux-gnu/libpam.so.0 /lib/libpam.so.0

And it returns: "The selected folder already exists", Someone can help me?


texts: PostGIS error building with `make` in ./extensions: nothing to be done for 'comments'

texts:
I'm using code I checked out from SVN. Whenever I run make in ./extensions/, I get

 make
for DIR in postgis  postgis_tiger_geocoder  postgis_topology address_standardizer; do \
    echo "---- Making all in ${DIR}"; \
    make -C "${DIR}" all || exit 1; \
done
---- Making all in postgis
make[1]: Entering directory '/home/ecarroll/code/postgis/extensions/postgis'
mkdir -p sql_bits/
/usr/bin/perl -pe 's/BEGIN\;//g ; s/COMMIT\;//g' ../../postgis/postgis_for_extension.sql > sql_bits/postgis.sql
make -C ../../doc comments
make[2]: Entering directory '/home/ecarroll/code/postgis/doc'
make[2]: Nothing to be done for 'comments'.
make[2]: Leaving directory '/home/ecarroll/code/postgis/doc'
cp ../../doc/postgis_comments.sql sql_bits/postgis_comments.sql
cp: cannot stat '../../doc/postgis_comments.sql': No such file or directory
Makefile:72: recipe for target 'sql_bits/postgis_comments.sql' failed
make[1]: *** [sql_bits/postgis_comments.sql] Error 1
make[1]: Leaving directory '/home/ecarroll/code/postgis/extensions/postgis'
Makefile:26: recipe for target 'all' failed

Whenever I run make comments in the root I get a small portion of those errors,

make comments
make -C doc comments
make[1]: Entering directory '/home/ecarroll/code/postgis/doc'
make[1]: Nothing to be done for 'comments'.
make[1]: Leaving directory '/home/ecarroll/code/postgis/doc'

How do I resolve this?


texts: Predicate not resulting in expected scan

texts:
So I have this query that I'm trying to tune and am running into an issue that I'm having trouble understanding. First of this is the query I'm using.

SELECT
    si.LoanNbr AS [LoanNumber],
    fi.[SvcClientNbr] AS ClientID,
    si.LoanMasterID,
    si.LoanSrcCode AS [LoanSourceCode],
    fi.LoanPurpCode,
    fi.[PropState] AS [Property State],
    im.ImagedocumentID AS [Image Document ID],
    -- im.requestID AS [Request ID],
    CONVERT(VARCHAR(10),im.[ImageDate],101) AS ImageDate,
    im.[PageCount],
    im.[SignatureInd]
FROM dbo.NotMybaseTable Si
INNER JOIN dbo.NotMyTableName fi
    ON si.LoanMasterID = fi.LoanMasterID
INNER JOIN [dbo].[ImagedDocument] im
    ON si.loanmasterid = im.loanmasterid
    AND im.[DocTypeCode] = '10112'
WHERE CASE WHEN si.loansrccode = 'CORE' AND  Im.[SignatureInd] IN ('Y') THEN 1
        WHEN si.FundingSysCode = 'LIS' and CASE WHEN si.loansrccode = 'CORE' THEN 0 ELSE 1 END = 1 THEN 1
    ELSE 0 END = 1
    AND [ImageDate] BETWEEN DATEADD(WK, DATEDIFF(WK, 0, GETDATE()) - 4, -30) AND DATEADD(WK, DATEDIFF(WK, 0, GETDATE()) - 4, 0) + 5

When I run this query the execution plan I get looks like this.

So the query is doing a very large scan on this large table returning 30 million rows just to pare it down to 3K. The index it's using looks like this.

CREATE NONCLUSTERED INDEX [IX_ImageDoc_DocType] ON [dbo].[ImageDoc]
(
    [LoanMasterID] ASC,
    [ImageDate] ASC,
    [ImageDocType] ASC
)

I altered to query in a way that gives it much better performance by moving the date portion of the predicate outside of the initial query like so.

select
    *
from (
    SELECT
        si.LoanNbr AS [LoanNumber],
        fi.[SvcClientNbr] AS ClientID,
        si.LoanMasterID,
        si.LoanSrcCode AS [LoanSourceCode],
        fi.LoanPurpCode,
        fi.[PropState] AS [Property State],
        im.ImagedocumentID AS [Image Document ID],
        CONVERT(VARCHAR(10),im.[ImageDate],101) AS ImageDate,
        im.[PageCount],
        im.[SignatureInd]
    FROM dbo.NotMybaseTable Si
    INNER JOIN dbo.NotMyTableName fi
      ON si.LoanMasterID = fi.LoanMasterID
    INNER JOIN [dbo].[ImagedDocument] im
      ON si.loanmasterid = im.loanmasterid
      AND im.[DocTypeCode] = '10112'
    WHERE CASE WHEN si.loansrccode = 'CORE' AND  Im.[SignatureInd] IN ('Y') THEN 1
            WHEN si.FundingSysCode = 'LIS' and CASE WHEN si.loansrccode = 'CORE' THEN 0 ELSE 1 END = 1 THEN 1
        ELSE 0 END = 1
        ) as F
WHERE [ImageDate] BETWEEN DATEADD(WK, DATEDIFF(WK, 0, GETDATE()) - 4, -30) AND DATEADD(WK, DATEDIFF(WK, 0, GETDATE()) - 4, 0) + 5

This resulted in much better performance and a query plan that is obviously superior.

The index it's scanning now looks like this.

CREATE NONCLUSTERED INDEX [IX_ImageLoanDoc_SystemName_ICMPDocTypeCode] ON [dbo].[ImageDoc]
(
    [SystemName] ASC,
    [ICMPDocTypeCode] ASC,
    [LoanMasterID] ASC,
    [ImageDate] ASC
)
INCLUDE (   [ImageDocumentID],
    [ImageDocType],
    [BatchName],
    [SignatureInd],
    [ICMPDocCategoryCode],
    [ICMPDocSubTypeCode])

So here's my question. With ImageDate being in this index why does having it in my query hurt performance so badly? Shouldn't having that predicate included make it easier to eliminate rows? What am I doing/thinking incorrectly?


texts: MongoDB durability and consistency with j= true and w:majority

texts:
Assume there is a 7 member replica set, where each node is a voting member and no arbiter node is present. For a write w1, Using j=true and w:majority would ensure that a write is written to the journal and let's assume to the disk also after the default time interval.

If 3 nodes fail (all having the write w1 mentioned above) at the same time including the primary, only one of the remaining 4 nodes has the write w1. If this node does not get enough votes (say it's overloaded and will not be suitable for role of a primary), and some other node gets all enough votes to become primary, we have basically lost the write w1.

I know this is a rare case, but it still can happen. So isn't it true that using j= true and w:majority also will not assure that the writes are durable?


texts:
I am not a SQL Server DBA but I need some advise. I am having a problem with a prod server that’s being pounded by constant queries and data mining. I have a second server that I would like to setup to have a copy of production but on a day lag.

I know I can restore a backup from prod once a day but the proble is prod is in Azure and it takes 4 hrs to download the bkup.

So I am thinking log shipping except I want to control how the logs are applied so as to end up with just the prior day’s transactions every day.

Any suggestions as to how this can be accomplished would be greatly appreciated. Thank you in advance.


texts: Warning for missing statistics in execution plan

texts:
I have a situation that I can't understand. My SQL Server execution plan tells me that I have missing statistics on the table, but the statistics are already created:

But if we look at the table, we will see that there is a statistic that has been created automatically:

Can somebody help to understand how it can be?

Auto_Update and Auto_Create statistics is turned on on the current DB.

I am using SQL Server 2014.


texts: How to improve this query (using table variables or not?)

texts:
I'm having a bit of problems with these tables...

TableCost
(
TableCostId int,
Cost numeric,
MonthsId int
)

and

Taxes
(
Percentt numeric,
MonthsId int
)

I have to calculate a new value by getting the biggest Taxes.MonthsId that do not exceed the  TableCost.MonthsId (for each row).

For example, if I have the following registers:

TableCost:

(1,10,1),
(2,5,3),
(3,12,7),
(4,17,8),
(5,22,11)

Taxes:

(55,1),
(60,2),
(65,6),
(70,10),
(75,12)

To calculate the TaxedCost of the

first row of the TableCost -> 10*55
Second row -> 5*60
Third row ->12*65
Fourth row -> 17*65
Fifth row -> 22*70

The select must return the following fields

(Cost,TaxedCost)

I've tought of declaring a table variable... storage the Taxes values on it, runs a normal select on the TableCost table and looping each of its rows calculating the TaxedCost after the select.

Any ideas of improve this query?


texts: Storing a Shipping Address Best Practice

texts:
I'm building a checkout system which needs to store the customer's shipping address in the database and am unsure the best way to go about it.

Currently I have an addresses table which stores each address with a unique ID:

id | address         | city     | division   | country | postcode
-----------------------------------------------------------------
1  | 23 Example Road | York     | Yorkshire  | GB      | PE21 5QR
2  | 7 Lala Land     | Stockton | California | US      | 96341

I then have a many to many relationship table called account_addresses which links addresses to user accounts:

account_id | address_id
-----------------------
23         | 1
48         | 2

For storing the shipping address in the database I was going to allow the user to select one of their existing addresses or let them add a new one to their account and store that address ID in the orders table. However, I realised that if I just store the address ID, if the user edits that address it would change the shipping address thus making it wrong.

I only see two solutions:

Store the shipping address as a new row in the addresses table
although this will duplicate the data that already exist.
When a user edits their address it inserts a new address and leaves
the old one in the database instead of updating the data.

What's the best solution and why?


texts: MDF/TempDB Growth, SQL Server 2005 Enterprise

texts:
Here's my scenario:

SQL Server 2005 Enterprise
Records from Database A being added to Database B
Size of Database A MDF file: 3.15 GB
I have separate drives for Data, Logs, and System.

I ran a T-SQL script, adding 1.8 million records from database A to a table with 7.3 million records in database B.

When I started the script I had about 200 GB free on my Data drive. 12 hours later the job wasn't finished and completely filled up my Data drive!

I could maybe understand if I had lots of indexes on this destination table that would need to be updated when adding new records, but the indexes only take up 687.969 MB. How could a database with a 3.15 GB .MDF fill up 200 GB of space?

I shrank the .MDF and tried again, this time Inserting to a temp table. Before running this script I had 168 GB free on my system drive. After running for 2 hours the script wasn't finished and TempDB completely filled up my system drive!

What could be causing this? I have confirmed the script isn't adding more records than it should.

Here's my code - I've used similar code to this in the past without this runaway .MDF file growth:

INSERT INTO mydbB.dbo.TableA (FieldA, FieldB, FieldC, FieldD, FieldE, FieldF, FieldG,
                              FieldH, FieldI, FieldJ, FieldK, FieldL, FieldM)
   SELECT
      a.FieldA, b.FieldB, 129 as FieldC, a.FieldD, c.FieldE, b.FieldF, a.FieldG,
      b.FieldH, b.FieldI, b.FieldJ b.FieldK, b.FieldL b.FieldM
   FROM
      mydbA.dbo.TableA a
   LEFT OUTER JOIN
      mydbA.TableB b ON a.FieldB = b.FieldB
   LEFT OUTER JOIN
      mydbA.TableC c ON a.FieldG = c.FieldG
   WHERE
      a.FieldA NOT IN (SELECT FieldA FROM mydbB.dbo.TableA WHERE FieldB = 129)

It seems to be copying the right number of records. If I do a select count(*) on my select statement, I get 1,839,793, which makes sense given I'm doing left outer joins:

select count(*) from mydbA.dbo.tableA  --1,839,793

select count(*) from mydbA.dbo.tableB  --716,840

select count(*) from mydbA.dbo.tableC --32,289

So what could cause runaway .MDF growth when the query returns the correct number of records and the source database is only 3.15GB?


texts:
I have three statements to combine but I cannot find the correct solution for SQL Server 2008 R2:

I need to concatenate three columns into one but in three different ways depending on the result in the last one. Column 3 can be a null-value, an alphanumeric letter or begin with '-' followed by a number. I tried the following:

if (select * from col3  where col3 is null)
 select col1 + ' ' + col2
else if (select * from col3 where col3 like '-%'
 select col1 + ' ' + col2 + col3
else if (select * from col3 where col3 not like '-%'
 select col1 + ' ' + col2 + ' ' + col3

Separately, they work like a charm. When trying to combine them like the above statement they throw me an error saying I have created a Boolean instead of the expected condition.

How do I formulate this to be correct?


texts: Query to get total time logged in per week

texts:
I need to find the number of hours each user is logged into an application per week. The table structure is as follows:

[ID]
[UserID]
[Time] (datetime field)
[EventType] ("login" or "logout")

There can be many logins/logouts per day. Since it would probably be significantly easier to get the first login and last logout of the day, that is also acceptable (although less accurate).

Any suggestions where to start? I'll probably have to do some partitioning, right?

The table logs the launch of a desktop application. The application will time out on its own without activity. Launching another instance of the application will log out your active session and start a new one, so I don't think there could be an overlap. I do not have control over the data types as they are part of the application. EventType is a string.


texts:
I have four tables :

table  p_d :

id   price
1     12
2     13

table s_d

id   name
1    name1
2    name2

table b_d

id    p_d_id
1       1
2       1

table b_d_s

id     b_d_id    s_d_id
1       1         1
2       1         2

Some info about tables :

for table b_d

column p_d_id reference p_id (id)

for table b_d_s

column b_d_id references b_d(id) , column s_d_id references s_d(id)

Now problem is that, i am unable to link all tables together using inner join .

What i need from table b_d_s:

p_d.price  where s_d_id = 1

I am not able to join table b_d_s  and  table p_d  because there is not any common column between them.

result might be

price
 12

I also tried it at my end ,  but could not join all tables together. i used inner join .


texts: Compare a date withing range of date fields in mysql

texts:
I have this simple query that retrieving data:

select leave_detail_id,
       emp_id,
       leave_type,
       leave_current_status_id,
       leave_start_date,
       leave_end_date
from tbl_emp_leave_details eld

Data:

Now I have this query, i want to filter my record based on date:

select leave_detail_id,
       emp_id,
       leave_type,
       leave_current_status_id,
       leave_start_date,
       leave_end_date
from tbl_emp_leave_details eld
where leave_start_date >= '2019-09-01'
and   leave_end_date <= '2019-09-31'

Now it should bring the highlighted record but it shows nothing (no record found). What should I do bring the highlighted record? what is the problem with my query?


texts: Postgres PITR: Recovery does not seem to fetch from Archive Dir

texts:
Make and give permission to database dir

cd /home/admin-12/Desktop/

mkdir test2
sudo chown postgres:postgres test2

mkdir test2_wal
sudo chown postgres:postgres test2_wal

touch test2.log
sudo chown postgres:postgres test2.log

Init Database

/usr/lib/postgresql/11/bin/initdb -D /home/admin-12/Desktop/test2

Edit postgres conf

nano /home/admin-12/Desktop/test2/postgresql.conf

archive_mode = on
archive_command = 'cp %p /home/admin-12/Desktop/test2_wal/%f'

/usr/lib/postgresql/11/bin/pg_ctl -D /home/admin-12/Desktop/test2 -l /home/admin-12/Desktop/test2.log start

Dummy Data

create table testPITR1 as select * from pg_class, pg_description;
select * from current_timestamp;

Backup

SELECT pg_start_backup('label', false, false);
tar -cvzf test2.tar test2
SELECT * FROM pg_stop_backup(false, true);

More Dummy data

create table testPITR2, testPITR3, testPITR4

Kill existing db

/usr/lib/postgresql/11/bin/pg_ctl -D /home/admin-12/Desktop/test2 -l /home/admin-12/Desktop/test2.log stop
mv test2 test2.bad
tar -xvzf test2.tar
sudo chown -R postgres:postgres test2

Recovery conf

nano /home/admin-12/Desktop/test2/recovery.conf

restore_command = 'cp /home/admin-12/Desktop/test2_wal/%f %p'
recovery_target_time = '2019-08-21 16:14:11.175781+05:30'

WITHOUT THIS STEP I CANNOT RECOVER Table 2,3,..

cp test2.bad/pg_wal/0* test2/pg_wal/

Any tips?

IF i do above steps in Postgres Docker (using volumes), then I get PANIC:  could not locate a valid checkpoint record, but this somehow is limited to docker


texts: TempDB Autogrowth - Does SQL need a restart?

texts:
I have a case where i'm running into an issue with the tempdb not being large enough. Here is my error:

Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

The tempDB spans 16 files each with an initial size of 4096 MB and Autogrowth is set to none.  I have 118 GB free space on the SSD's

I want to turn Autogrowth on however this is in production.  Does SQL need a restart to pickup the new setting?

Yes the executing stored procedure will be investigated to see why it's storing so much data in table variables.


texts: Count of rows for each 2 secs in PostgreSQL


texts:
We have "AdfsConfiguration" and "AdfsArtifactStore" databases for our ADFS deployment.  I keep running into disk space issues due to the full and transaction log backups being taken on the server.

Right now, these two databases are in Full recovery model - but I wonder if I can just switch to Simple.  Are transaction log backups going to be important in these two databases?

I have searched a lot but can't really understand whether these DBs need actually need the trans log backups or if just a nightly full backup is good enough.

Also, I understand the general rules of thumb for deciding between the two models (SIMPLE or FULL recovery model for databases?) for I just can't come to and understand on what these ADFS databases are really doing to make the decision.

This is a screenshot of the "restore" GUI screen. it shows how I have log backups taken every 30 min:

This basically means our RPO is 30 min?  And then the full backup ensures ALL previous transactions are recoverable if they occurred before it was taken (in other words... I don't need to have log backup files from before the point in time in which the full backup was taken, right?)

I am not using compression (only available in Enterprise Edition right?)

I have thought about daily DIFFs and weekly FULLs. I honestly just seem to read online of a lot of people doing nightly FULLs. All instances I inherited were already set up to do nightly FULLs and I haven't seen any that do daily DIFFs (not that this means I shouldn't make a change of course).

Honestly I'm one of those "accidental" DBAs (am more of a developer). It's been overwhelming to actually try to get up to speed on everything I need to understand and put into practice.


texts:
I read some articles and also tried SSMA, linked server and other things, but not help. I have one client's mysql Db (later we need other too) in my local pc and have Azure DB which used for SAAS-based application (i.e. all client's data is store in Azure DB with bifurcate from ClientId column in each table. Good thing is, before each migration process, we know what is cilentid.
What I tried,

SSMA, its simple and very usefull, but not have logical things
Linked server, some one suggest to migrate first the mysql to local sql and then migrate to Azure, it is faster.  This is good, only that based on Patient-id of patient table have reference of other patient table in AzureDB, so again required the other logic for that and not help.

I want to migrate the patient table and another reference patient table of MySql (local PC) to Azure sqlserver.
Details

In MySql (installed in my local pc) which has patient table which has PatNum (auto-incremented or PK) and other columns, the same data need to migrate in Azure DB.

Azure DB has tblPatientMaster have PatientId( auto-increment id or PK ), PatientNum (PK of Mysql above table). Also change some columns name, add some columns (like Recordstatus, CreatedBy, CreatedDate etc)

Want to loop each record and insert a record with the below  condition

MySql
Azure SQL Server

PatNum
PatientNum

ClientId (this is already known and fix and while insert, we pass via parameter)

PatStatus
PatientStatus

PatStatus=2 or 3
RecordStatus = 2

other columns
others columns same as Mysql

After patient master insert and get a patient-id of Azure Sqlserver, need to add a record in patient reference table like patient allergy (PatAllergy) of Mysql DB where replace the PatNum of Mysql with PatientId of sqlserver and insert into tblPatientAllergy of Azure DB.

I have some question as

Is there any option other than SSIS for this type of migration.
If no and only SSIS, then anybody gives steps or hint that how to loop each record of my SQL and while loop call Sqlserver procedure (I think I used stored procedure at Azure side) and do the logical thing and then insert in the first PatientMaster.
In SSIS, after insert into Patient Master, how to fetch and insert MySQL to Azure Sqlserver for that patnum.


texts:
When I run following command

mysqld_safe --skip-grant-tables &

I get message

myuser@myvm:~$ 200523 08:24:41 mysqld_safe Logging to syslog.
200523 08:24:41 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

After that when I type mysql then I get following error:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2 "No such file or directory")

How do I restart mariadb in safe mode to reset root password?


texts: How do I determine the appropriate size for my tablespace when importing a .dmp file?

texts: I have a 14g DMP file. How large should I size my tablespace to import it into an Oracle 11g database?


texts: Prevent explicit insert into serial columns

texts:
I want to prevent explicit inserts into serial columns. I have come up to the following trigger:

drop table test_table;

create table test_table(
id bigserial primary key,
foobar text
);

create or replace function serial_id_check() returns trigger as
$$
begin
    if new.id != currval(TG_TABLE_NAME||'_id_seq') then
        raise exception 'Explicit insert into serial id, currval = %, tried to insert = %', currval(TG_TABLE_NAME||'_id_seq'), new.id;
    end if;
    return new;
end;
$$ language plpgsql;

create trigger test_table_serial_id_check
before insert on test_table
for each row
execute procedure serial_id_check();

Maybe there is a better approach? Maybe this approach is broken and this cannot be achieved at all?

P.S. I also think about not granting rights for the insert and update, but only for pgplsql procedure for inserting/updating - but this approach is not possible for me right now.


texts: SQL-Server: Why do we use FILE = 1 in scripts for restoring a database

texts: Recently when I came across the script for restoring a database, I got a doubt on why we have to use "FILE = 1"? Can't we restore a database without that statement!? Basically, what it is used for?


texts: SSAS Design 2 Fact tables (1 Weekly, 1 Monthly)

texts: I am currently busy building a sales cube for a customer. However they would also like to see their current stock count in the same cube. The Sales Fact table is on a daily grain, where as the Stock Fact is aggregated on a weekly grain. They would like to see their stock as at sales date. I know i will need to add a calculated field, i am just not sure how to create this. For instance, they close business on a Sunday, so as from Monday, I need to subtract total sales from the Closed Sunday Stock and so forth for each day until Sunday again.


texts: Can running a query on a readable secondary slow down (block) the primary in AG

texts:
Someone told me that running queries on a readable secondary in an AG can slow down (or even block) the primary (no matter if it's in sync or async mode).

I do not understand how this is possible. I'll try to explain why I think this is not possible.

Sync mode:
Log records are sent to the secondary and when those records are hardened on the secondary (written to log file) an ACK is sent to the primary and the transaction is committed on the primary.If there is some latency on sending the ACK this can affect the primary but I think this can't be caused by a query running on the secondary.
Async mode:
Log records are sent to the secondary and the primary isn't waiting for an ACK from the secondary, it is just 'fire and forget'.

What can happen is that the REDO-thread is blocked by a long running query => changes can't be applied to the secondary but I think this doesn't affect the performance on the primary.

Is my assumption correct or am I missing something?


texts: how to set a row's value from a certain row's value?

texts:
I have a MySQL table named "activities" with fields id(int), tstamp(timestamp), amount(decimal) balance(decimal). Decimal fields hold money values.

id   tstamp                   amount   balance
----------------------------------------------
 1   2013-03-18 00:00:10       57.00      0.00
 2   2013-03-18 00:00:11       13.05      0.00
 3   2013-03-18 00:00:12      110.00      0.00
 4   2013-03-18 00:00:13       23.50      0.00
 5   2013-03-18 00:00:14       35.44      0.00
 6   2013-03-18 00:00:15       76.00      0.00
 7   2013-03-18 00:00:16       34.74      0.00
 8   2013-03-18 00:00:17      120.47      0.00
 9   2013-03-18 00:00:18       35.00      0.00
10   2013-03-18 00:00:09       46.00      0.00

so balance fields' values must be like that:
current row's balance = CHRONOLOGICALLY previous row's balance + current row's amount.

Notice last row's tstamp value is smaller than first row's tstamp value. so when I say previous row I do not mean current id minus 1. So highest balance value must be at row #9.

And the problem is how to update all balances with chronogically previous row's balance value + current row's amount value?


texts:
I have an SQL Database with a lot of unused space.
However when I run
Tasks | Shrink | Database
And
Tasks | Shrink | Files
from Management studio it isn't freeing up any space.
Unfortunately the machine that it is running on is running out of hard disk space. Any ideas on why this space isn't freeing up.

Also recovery mode on DB is set to simple.

Here is the unused dialog that comes up when I go to shrink files:

I've also tried doing this via T-SQL

  DBCC SHRINKDATABASE (myDatabase, 5);

I get following response:

DBCC SHRINKDATABASE: File ID 1 of database ID 6 was skipped because the file does not have enough free space to reclaim.
DBCC SHRINKDATABASE: File ID 2 of database ID 6 was skipped because the file does not have enough free space to reclaim.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


texts:
We are looking at using merge replication to maintain a 2-way data flow between a central SQL Server 2012 Standard database and, eventually, 100's of international satellite SQL Express 2012 installations.  The central database being the publisher with the SQL Express installations being subscribers.

I understand that I can setup a parameterised filter so that data can be sent specifically to each satellite database - e.g. using the unique ID of each satellite installation.

Is there a maximum number of subscribers that one publication on the SQL Server Standard 2012 can have?  From my investigation I have read some people saying 25 but others saying they have many more than that.  I can find nothing definitive.  Obviously if it is as low as 25 then this solution will not work.


texts: How to capture workload from production to replay on test?

texts:
I want to perform performance testing on my test server.

How can I capture workload from production and replay on my test server?

Basically I'm looking for detailed steps so that I can replay the workload from production to my test environment.


texts: PostgreSQL How to DEFAULT Partitioned Identity Column?

texts:
PostgreSQL 11
What is the best way to generate default values for identity columns on partition tables.
E.g

CREATE TABLE data.log
(
  id              BIGINT GENERATED ALWAYS AS IDENTITY
                  (
                    INCREMENT BY 1
                    MINVALUE -9223372036854775808
                    MAXVALUE 9223372036854775807
                    START WITH -9223372036854775808
                    RESTART WITH -9223372036854775808
                    CYCLE
                  ),
  epoch_millis    BIGINT NOT NULL,
  message         TEXT NOT NULL

) PARTITION BY RANGE (epoch_millis);

CREATE TABLE data.foo_log
PARTITION OF data.log
(
  PRIMARY KEY (id)
)
FOR VALUES FROM (0) TO (9999999999);

If I do:

INSERT INTO data.foo_log (epoch_millis, message)
VALUES (1000000, 'hello');

I get:

  ERROR: null value in column "id" violates not-null constraint
  DETAIL: Failing row contains (null, 1000000, hello).
  SQL state: 23502

because the default generated value is not applied to the partition UNLESS I insert it into the root table like this:

INSERT INTO data.log (epoch_millis, message)
VALUES (1000000, 'hello');

There are times though that I want to insert directly into a specific partition for performance reasons (like doing bulk COPY).
The only way I can get this to work is to create the partition while knowing about the sequence that was implicitly created for the identity column like this:

CREATE TABLE data.foo_log
PARTITION OF data.log
(
  id DEFAULT nextval('data.log_id_seq'),
  PRIMARY KEY (id)
)
FOR VALUES FROM (0) TO (9999999999);

Is there a better way to do this and if so how?


texts: auto-incrementing key in self-referencing table, performance

texts:
My design contains a table where almost every entry has a parent it has to point to, within the same table (differently put, self references).

    /* scenario executions */
    CREATE TABLE `boteval`.`scenario_executions` (
      `scenario_id` BIGINT NOT NULL COMMENT 'connects to a scenario id',
      `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT 'execution id',
      `parent_id` BIGINT NULL COMMENT 'execution id of parent executor (null would mean no parent, i.e. a top level execution)',
      `started` DATETIME(6) NOT NULL,
      `ended` DATETIME(6) NULL COMMENT 'can be null while the execution has not yet ended',
      PRIMARY KEY (`id`),
      UNIQUE INDEX `id_UNIQUE` (`id` DESC));

The use case being one where tree structures need to be incrementally recorded into the table, first the parent, than children, children of children, etc, as the program unfolds.

Using an auto-incrementing key for this table, I have reason to believe it would be extruciatingly slow if every insert needed to happen only after its predecessor had already returned its database-assigned auto incremented key.

On the other hand, I think that batching this workload would be way beyond naive, as a sql that strings together the returned ids would become extremely nested for each buffered batch, not to mention the dependencies between batches.

I have several solutions to this, but I wonder if there are very specific design patterns you would recommend. Maybe one of my design constraints would need to be relaxed: maybe the auto-incrementing, if I can safely supply a unique key from my code. In a way, this might not even really be a good fit for a relational database like MySQL.

Thanks in advance for any comments on good patterns for this kind of scenario!


texts: Where clause to filter rows for Azure Stretch Database?

texts:
Currently stretch database in SQL Server 2016 moves the entire table to Azure. However, this InfoQ article mentions an "archive row" mode for Stretch database that will allow you to filter which rows in a table to move to the cloud (ex. records before 2010). I haven't been able to find any official Microsoft documentation regarding this unreleased feature.

Can someone point me to an official source that says this is coming for RTM? I did find a French blog entry on MSDN mentioning it but nothing else.


texts:
In the Stack Exchange Data Explorer each Sunday all databases are dropped and recreated one by one with this stored procedure.

For each database the script starts with (paraphrased) for example Dba.StackExchange:

Create Database [dba_Temp]
          ON  PRIMARY (NAME = 'dba', FILENAME = 'dba.mdf')
          LOG ON (NAME = 'dba_log', FILENAME = 'dba_log.ldf')

Then it does for the tables an select into from a table that is on a linked server, for example for the Users table (paraphrased again):

Select * Into [dba_Temp].[dbo].[Users] From [CO_SQL03].[StackExchange.Dba].[dbo].[vExportUsers];

At the end the current StackExchange.Dba database is dropped and the dba_temp is renamed to StackExchange.Dba

Alter Database [dba_temp] Modify Name = [StackExchange.Dba]

Rinse repeat for the other 349 databases.

I'm interested in how long it took for each database to get refreshed, for example in a query like this so we can provide better support for questions like I'm getting an “Invalid object name 'StackExchange.Cstheory.Meta_Temp.dbo.posts'.” when executing a cross-site query in SEDE

The output of sys.databases for a restored database baffles me a bit. I've been using this query.

The create_date for the StackOverflow database is 2020-05-24 10:36:19 but that isn't the creation date, that is the last action that took place. That is also confirmed by looking at the create_date of Tables. The first table (Users) is created on 2020-05-24 05:49:59, the last table on 2020-05-24 10:31:07. The last statement in the refresh script is the creation of the database principal. Its create date is 2020-05-24 10:36:19. That matches the create_date of the database.

I'll accept that I can always use the workaround to investigate to create_data of the objects in the database to find the closest possible date to the create_data of the database.

Is there any other system catalog view I could query to get the actual create_date of the database in the context of the Stack Exchange Data Explorer?  That last part might be a frustrating limitation as the SEDE instance seems pretty hardened. Probably because I knocked a bit too hard on the door now and then. For example sys.database_files does return rows but the SVC_SEDE user doesn't have permission on sys.master_files. None of those views have a create_date (except that the date and time is encoded in the database filename, uuggghhh) so even if that user had access it wouldn't have solved my problem.

Which system catalog view has the create_date of a database which name was modified? Or an alternative reliable method if such view doesn't exist.


texts: Query to combine each duplicate found entry

texts:
Hey all I am wanting to create a query so that I can combine each of the found duplicates into one entry.

An example of this is:

 Name        | ID    | Tag   | Address           |carNum
 -------------------------------------------------------
 Bob Barker  |2054   |52377  |235 Some road      |9874
 Bill Gates  |5630   |69471  |014 Washington Rd. |3700
 Bob Barker  |2054   |97011  |235 Some road      |9874
 Bob Barker  |2054   |40019  |235 Some road      |9874
 Steve Jobs  |8501   |73051  |100 Infinity St.   |4901
 John Doe    |7149   |86740  |7105 Bull Rd.      |9282
 Bill Gates  |5630   |55970  |014 Washington Rd. |3700
 Tim Boons   |6370   |60701  |852 Mnt. Creek Rd. |7059

In the example above, Bob Barker and Bill Gates are both in the database more than once so I would like the output to be the following:

 Bob Barker|2054|52377/97011/40019|235 Some road     |9874
 Bill Gates|5630|69471/55970      |014 Washington Rd.|3700
 Steve Jobs|8501|73051            |100 Infinity St.  |4901
 John Doe  |7149|86740            |7105 Bull Rd.     |9282
 Tim Boons |6370|60701            |852 Mnt. Creek Rd.|7059

Notice how Bob Barker & Bill Gates appends the tag row (the duplicated data) into one row instead of having multiple rows. This is because I do not want to have to check the previous ID and see if it matches the current id and append to the data.

I am hoping a SQL query guru would have a query to do this for me!

Code from another forum

  SELECT [USER],
    STUFF(b.[licenseDup],1,1,'')                                            AS licenseDup,
    STUFF(c.[yearDup],1,1,'')                                               AS yearDup,
    STUFF(d.[makeDup],1,1,'')                                               AS makeDup,
    STUFF(e.[modelDup],1,1,'')                                              AS modelDup,
    STUFF(f.[colorDup],1,1,'')                                              AS colorDup
  FROM
    (SELECT DISTINCT a.*            FROM [FEBCardb].[dbo].[sheet1$]         AS a)           AS a
    CROSS APPLY
    (SELECT '/' + b.[License]       FROM [FEBCardb].[dbo].[sheet1$]         AS b            WHERE a.[User] = b.[User]   FOR XML PATH('')) b(licenseDup)
    CROSS APPLY
    (SELECT '/' + b.[Year]          FROM [FEBCardb].[dbo].[sheet1$]         AS b            WHERE a.[User] = b.[User]   FOR XML PATH('')) c(yearDup)
    CROSS APPLY
    (SELECT '/' + b.[Vehicle Make]  FROM [FEBCardb].[dbo].[sheet1$]         AS b            WHERE a.[User] = b.[User]   FOR XML PATH('')) d(makeDup)
    CROSS APPLY
    (SELECT '/' + b.[Vehicle Model] FROM [FEBCardb].[dbo].[sheet1$]         AS b            WHERE a.[User] = b.[User]   FOR XML PATH('')) e(modelDup)
    CROSS APPLY
    (SELECT '/' + b.[Color Name]    FROM [FEBCardb].[dbo].[sheet1$]         AS b            WHERE a.[User] = b.[User]   FOR XML PATH('')) f(colorDup);

This produces the following (what I see from that query above):

USER   |licenseDup     |yearDup   |makeDup       |modelDup         |colorDup
------------------------------------------------------------------------------
V5432E |XXXXXX/ZZZZZZZ |1998/2001 |Pontiac/Honda |Grand Am/Odyssey |Silver/Green
V5432E |XXXXXX/ZZZZZZZ |1998/2001 |Pontiac/Honda |Grand Am/Odyssey |Silver/Green
S89542 |XXXXXX         |2010      |Dodge         |Viper            |Black

And this is what I am looking to see:

USER   |licenseDup     |yearDup   |makeDup       |modelDup         |colorDup
------------------------------------------------------------------------------
V5432E |XXXXXX/ZZZZZZZ |1998/2001 |Pontiac/Honda |Grand Am/Odyssey |Silver/Green
S89542 |XXXXXX         |2010      |Dodge         |Viper            |Black


texts: InfluxDB - How to get start timestamp of alternating series


texts: Crashing CLR on SQL Server 2014 (windows 2012R2)

texts:
I have this small CLR that does a RegEX function on a string in columns.

When running on SQL Server 2014 (12.0.2000) on Windows Server 2012R2 the process crashes with

  Msg 0, Level 11, State 0, Line 0 A severe error occurred on the
  current command.  The results, if any, should be discarded.

and gives a stack dump if I do

select count (*) from table where (CLRREGEX,'Regex')

but when I do

select * from table where (CLRREGEX,'Regex')

it returns the rows.

Works perfectly on same SQL Server build running on Windows 8.1 .

Any ideas?

-- Edit
It is as simple as it can be

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlTypes;           //SqlString, SqlInt32, SqlBoolean
using System.Text.RegularExpressions; //Match, Regex
using Microsoft.SqlServer.Server;     //SqlFunctionAttribute
public partial class UserDefinedFunctions
{
    public static readonly RegexOptions Options = RegexOptions.IgnorePatternWhitespace | RegexOptions.Multiline;
    [SqlFunction]
    [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
    public static SqlBoolean RegExMatch(SqlString input, SqlString pattern)
    {
        if (input.IsNull || pattern.IsNull) //nulls dont qualify for a match
            return SqlBoolean.False;
    return Regex.IsMatch(input.Value, pattern.Value, RegexOptions.IgnoreCase);
    }
}

So by little changes this works now: Main lesson in C# seems to be the same as in TSQL beware of implicit data conversion.

using System;
using System.Text;
using System.Data.SqlTypes;           //SqlString, SqlInt32, SqlBoolean
using System.Text.RegularExpressions; //Match, Regex
using Microsoft.SqlServer.Server;     //SqlFunctionAttribute
public partial class UserDefinedFunctions
{
public static readonly RegexOptions Options = RegexOptions.IgnorePatternWhitespace | RegexOptions.Singleline | RegexOptions.Compiled | RegexOptions.IgnoreCase | RegexOptions.CultureInvariant;

    [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true, DataAccess = DataAccessKind.Read)]
    public static SqlBoolean RegExMatch(SqlString input, SqlString pattern)
{
    if (input.IsNull || pattern.IsNull) //nulls dont qualify for a match
        return SqlBoolean.False;
    string sqldata = input.ToString();
    string regex = pattern.ToString();
    return Regex.IsMatch(sqldata, regex);
 }


texts: Can not connect to SQL Server from Ubuntu sqlcmd client

texts:
I'm trying to connect to a SQL Server Express server from a Linux Ubuntu 16.04 computer. I Installed all SQL commad line utilities for the linux machine and follow instructions from Microsoft SQL Server on Linux docs.

When I run

sqlcmd -S ip/SQLEXPRESS -U SA -P 1234

the command always returns the following error :

  Microsoft ODBC Driver 13 for SQL Server : Login timeout expired.
  Microsoft ODBC Driver 13 for SQL Server : TCP Provider: Error code 0x2AF9

I was able to run the same command from a Windows 10 computers and it works with no flaws.

The linux computer can communicate to the server machine and SQL Server 1433 port. I test this with the linux nc command (netcat).

Linux nc command results:

nc -z -v -w5 192.168.0.1 1433

  Connection to 192.168.0.1 1433 port [tcp/ms-sql-s] succeeded!

Any suggestions or something missing?

I have tried changing the ports, parameter order etc... and Linux version of sqlcmd doesn't work.

I also tried including extra '/' character, because it is special on Linux. The new command line is sqlcmd -S ip//SQLEXPRESS -U SA -P 1234 but it doesn't work. It works faster but returns other error:

  Microsoft ODBC Driver 13 for SQL Server : TCP Provider: Error code 0x2746.

Instance name doesn't work neither. sqlcmd -S SERVER-NAME\\SQLEXPRESS -U SA -P 1234 or sqlcmd -H ipaddress -S SERVER-NAME\\SQLEXPRESS -U SA -P 1234 The command recognizes quotes so sqlcmd -S "SERVER-NAME\SQLEXPRESS" -U SA -P 1234. Nothing works.

The correct syntax is sqlcmd -S ip\\SQLEXPRESS -U SA -P 1234 with double '\'. It doesn't connect anyway. I have several days struggling with it and could not find a way to make this thing work.


texts: How to evenly distribute a value over varying time periods with T-SQL?

texts:
I am quite a newbie to MS SQL Server 2014 and in need for a sql skript that would retrieve evenly distributed budgets over given project durations.

The budget amounts need only to be equally distributed based on the total running months over the entire project duration.

Since durations widely vary between projects, end/start and entire years of project duration must be taken into account as well as monthly budget amounts need to be calculated at the same time, I can't come up with a concept of how to apply T-SQL to retrieve what I want.

Therefore, any ideas or sql snippets would be highly appreciated as well as a short explanation that a beginner could understand.

Data available within my project database:

|   id  |   budget  |   start    |      end        |
+-------+-----------+------------+-----------------+
|   1   |1000000,00 | 2001-06-01 |   2003-09-30    |
|   2   |3000000,00 | 2013-03-01 |   2016-06-30    |

For the budget distribution, I want my final results to look like
this:

|   id  |  Year_01  |  Year_02  |  Year_03   |  Year_04   |
+-------+-----------+-----------+------------+------------+
|   1   | 250000,00 | 428571,43 | 321428,57  |   NULL     |
|   2   | 750000,00 | 900000,00 | 900000,00  |  450000,00 |


texts: How can I filter query results based on whether they have ALL values from another table?

texts:
For example, some actions that users can do, require a set of permissions.
Users are stored in the Users table:

UserId

1

2

and permissions in the Permissions table. Users can have permission A, B, or both.

UserId
Permission

1
A

1
B

2
A

Required permissions are stored in RequiredPermissions. From this, the POST action requires both permissions A and B

ActionType
Permission

POST
A

POST
B

GET
B

I want to query all users that can POST (user 1). This query would give users that have any permissions (that is, user 1 and 2):
select distinct u.UserId
from Users as u
join RequiredPermissions as rp
  on rp.ActionType = "POST"
join Permissions as p
  on u.UserId = p.UserId

How do I query users which have both permissions A and B with this schema?


texts:
when I tried to delete some records from a MYSQL server  (version 5.0.95-log)  and checked the process list I got  the result as follows

mysql> show processlist \G
*************************** 2. row ***************************
     Id: 3480
   User: admin
   Host:
     db: test_db1
Command: Connect
   Time: 8367
  State: invalidating query cache entries (key)
   Info: NULL

I  would like know what is this "invalidating query cache entries (key)" and is this due to any configuration settings.Replication is configured in this machine and it acts as the Master Server.

Please find the my.cnf file setting blow

[mysqld]

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
lower_case_table_names = 1
default-storage-engine=InnoDB
innodb_buffer_pool_size=5G
innodb_log_file_size=512M
query_cache_size=512M
table_cache=64
innodb_flush_method=O_DIRECT

#Replication

log-bin=mysql-bin
server-id=1
sync_binlog=1
innodb_flush_log_at_trx_commit=1

binlog-do-db=test_db1
binlog-do-db=test_db2

# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid


texts:
I am designing a data warehouse using Azure Databases (not Azure Data Warehouse though),

I have 2 databases: the main data warehouse and a staging database. As in a traditional ETL process, the raw data is stored in the staging, then transformed and finally loaded to the data warehouse/data marts.

Since it is Azure SQL, cross database queries don't work and the only workaround I know is using external data sources and external tables, which is pretty problematic.

Can you recommend the best solution to access both databases at the same time? My ideal solution would be cross database queries like in SQL Server on premise.


texts: Index for a complex similarity query Postgres

texts:
I have a materialized view that has a few columns that I need to run a complex OR query on with similarity matching and the structure is like this

mymatview:

column a,
column b,
column c,
column d,
column e

and on column D (which is a TEXT field) I run a query like this

SELECT * FROM mymatview
WHERE ((LOWER(d) = <<some value>> ) OR (LENGTH(d) > 4 AND d % <<some value>>))
    OR ((LOWER(d) = <<some other value>> ) OR (LENGTH(d) > 4 AND d % <<some other value>>))

Currently the matview has 1 mil+ rows in it and it runs a sequence scan on that table using the filter above with the OR conditionals. Does anyone know what a good index would be on column d that would help with performance on running a query like this?

UPDATE: I am using the package pg_trgm for the '%' similarity operator here. Postgres 9.6 https://www.postgresql.org/docs/9.6/pgtrgm.html


texts: T-SQL insert value based on identity column

texts:
Is it possible to have a table with PK based on Identity columns?

Lets say I have a table of customers, two fields are important for our case Id which is PK nvarchar(20) and SequenceId which is int identity(1,1). I want to have a stored procedure that will have(among others) a parameter @CustomerType and based on this parameter will generate the value for Id and insert a new row into the table. Something like this:

CREATE PROCEDURE createCustomer
@CustomerType int,
@GivenName nvarchar(20),
@Surname nvarchar(20),
@BirthDate date = null
...
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @prefix nvarchar(1) = N'x'
    IF @CustomerType = 1
    BEGIN
        SET @prefix = N'P'
    END
...
    ELSE
    BEGIN
        SET @prefix = N'x'
    END

    INSERT INTO [dbo].[Customers]
           ([Id],
            [GivenName],[Surname],[BirthDate])
    VALUES
           (@prefix + (SequenceId + 1000000),
        @GivenName,@Surname,@BirthDate);

    SELECT INSERTED.Id AS CustomerId;
END


texts: MySQL scheduled events not working on newly-added data

texts:
I can get a scheduled event to perform calculations on a current table. But it won't perform calculations on any new rows that are added to the table. How can I get the event to use the current contents of a table?
CREATE EVENT IF NOT EXISTS calculation ON SCHEDULE EVERY 5 SECOND STARTS '2021-01-01 00:00:00' DO SET @c = 0; SET @c = (SELECT COUNT(*) FROM myTable); SET @rownum:= 0; UPDATE myTable SET rank = (100 / @c) * (@rownum:= 1 + @rownum) ORDER BY other_column DESC LIMIT 100000;


texts:
My current system houses different variants of Products in different tables;

Productgroups
Productcolors
Productsizes
Productmodels

Products can reside under 1 Productgroup and can have multi-variance Colors, Sizes and Models.

The logic on filling the database now fully relies on the website-scripts; meaning when I make a new Product in my back-office I am filling and relating the Colors, Sizes and Models to the product and make sure they have a combination of themselves.

This structure quickly grows; like 3 Color-variants of 6 Sizes and 2 Models already convert into 36 different products.

I wonder if there is a more logical way to this Database-wise. The problem is that in the future it might be possible that a product will get another property (let's say Sublabel). How could I make this method ever-extendable?

I read about EAV, but that's really too much info to go on for now. Before I dive in the deep, I wonder if there are other approaches on this matter.


texts: Difference between Session Timeout and Heartbeat in always on

texts:
What is the difference between Session Timeout and Heartbeat in always on...
My Network is laggy and i get frequent connection timeout error on error logs and also at the I see missed heartbeat errors on the failover cluster errors ....Which settings should I change. Crossing which thresholds forces a failover---Session timeout or heartbeat ?

Will appreciate any help. Thanks


texts:
From the PostgreSQL wiki,

  MERGE is typically used to merge two tables, and was introduced in the 2003 SQL standard. The REPLACE statement (a MySQL extension) or UPSERT sequence attempts an UPDATE, or on failure, INSERT. This is similar to UPDATE, then for unmatched rows, INSERT. Whether concurrent access allows modifications which could cause row loss is implementation independent.

Further PostgreSQL's INSERT ... ON CONFLICT DO NOTHING/UPDATE is marketed as UPSERT and was added in 9.5

What then is MERGE? And how does it fit into the mix?


texts:
I am confused by joins.  I want to know how to choose the join based on the relationship between tables, or do joins have no dependency on relationship?

if relationship is "one-to-one" then "Inner join" is the best.
if relationship is "one-to-many" then ?
if relationship is "many-to-one" then ?
if relationship is "many-to-many" then left join be the best.


texts: Scope_Identity() returns null after inserting first row in table

texts:
I have a stored procedure that is used for inserting values in two tables.

The tables have a parent-child relationship i.e. the first table has an identity column, and the second table references the first table.

In the following procedure, I am returning Scope_Identity() value as a column:

ALTER PROCEDURE [dbo].[Insert_Header_Details_Tables]
    (
        @ProcessID [int]=null,
        @FileName [varchar](50)=null,
        @VendorName [varchar](250)=null,
        @LastName [nvarchar](100)=null,
        @FirstName [nvarchar](100)=null,
        @isFirstMSH [bit]
    )
AS
--BEGIN TRANSACTION
-- Insert into Jobs table
IF(@isFirstMSH = 1)
BEGIN
INSERT INTO LabStagingHeader
             ([FileName],
             [VendorName]
            )
    VALUES  (@FileName,
             @VendorName
            )
END
-- Retrieve the automatically @ProcID VALUE from the Header table

SET @ProcessID = SCOPE_IDENTITY()

-- Insert new values into LabStagingDetails table
INSERT INTO LabStagingDetails
            ([ProcessID],
            [LastName],
            [FirstName]
            )
    VALUES (@ProcessID,
            @LastName,
            @FirstName
            )

The issue is when isFirstMSH is false, the ProcessID value is NULL. If isFirstMSH value is false, it should insert the last generated value in table.


texts: Will canceling InnoDB compression that's already in progress corrupt data?

texts:
I have a 155GB table that is in the process of being compressed.

ALTER TABLE $table_name ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

This compression has been running for a long time and is impeding my ability to backup.

I'm thinking about killing the compression query, backing up, and trying the compression again once my devs know how long it'll be till the next backup.

Is there any danger in just running a kill statement? Is there a safe way to back out of this compression?

My fear is that backing out of a compression mid query will corrupt the table. I would like to run the following query below but I believe it will be dangerous.

KILL QUERY 12345