texts: In regards to GS1 barcode - what size DB field are used?

texts:
In regards to GS1 barcode - what size DB field is used or is the entire GS1 typically not stored in database, but rather processed in the application layer and stored in the database as a collection of the component parts?

The length of the GS1 in its entirety is the subject of this question


texts: How to know how healthy an SQL Server database is now?

texts:
I want to know how healthy an SQL Server database is now (before I started to work with it).
Then, in a month or so, I want to check again to see how healthy database is at that time and compare it to the previous state.
Basically is there any way I can take something like a snapshot of the database health now and later? And then see what's been improved, so I can track my progress.
Any source of information would be great.


texts: why user with db_owner & db_BackupOperator database role is unable to restore database?

texts:
I'm in sysadmin server role on the server .
All Programmers are in dbCreator Server role , So they can create databases for themselves and they have in db_owner & db_Backupoperator database role for their own databases.

All programmer have remote desktop access to the server just like me . I would like every Programmer be able to backup and restore their own databases .

But When they are trying to restore , in Specifying back-up dialog , when user click on add button they get this error ( while i can restore ).

C:\Program files\Microsoft SQL Server\mssql10_50.sql2008\backup
Cannot access the specified path or file on the server .
Verify that you have the necessary security privileges and that the path or file exists

I appreciate any help .


texts: Changing the partitioning column in SQL Server

texts:
I created a partitioned table with 100+ million rows and accidentally specified the wrong datetime column as the partitioning key when I created the table.

Is there an easy way to change the partitioning column from one datetime column to another one.   In my case, we have a column InsertedDate and a CompleteDate and I accidentally used the first one instead of the second one.


texts: Converting Non Unicode string to Unicode string SSIS

texts:
I am creating a package where I will be exporting data from a database into an empty excel file. When I added only the source and destination components and I ran the package I got a conversion error stating Output column and column "A" cannot convert between unicode  and non-unicode string data types.

To fix this I added a data conversion component and converted all the columns to

"Unicode String [DT_WSTR]"

and I no longer received the error. The only problem is that I had about 50 columns where I had to go 1 by 1 and select "Unicode String [DT_WSTR]" from the drop down list. I then had to go into the destination component and map the newly converted columns to my excel file.

My question is, if anyone else has come across this, is there a better more efficient way to get around having to do all the manual data type conversions? Having to convert and map all the columns one by one doesn't seem to practical especially if you have a large number of rows.

I understand excel files are not the best way to go for importing and exporting data but it is what is required in this particular case.

I might look for a way to just export to a flat text file and then try to convert to excel as a last step in the package. I'm hopping this wont trigger the same unicode / nonunicode conversion error.


texts: Overcoming the HDD IOPS limits on a billion row table? Is getting SSD the only solution?

texts:
I have a table with this structure:

CREATE TABLE `requests` (
 `userid` int(11) NOT NULL,
 `messageid` int(11) NOT NULL,
 `queueid` int(11) NOT NULL AUTO_INCREMENT,
 `problem_id` int(3) NOT NULL,
 `cluster` int(11) NOT NULL,
 `processed` tinyint(1) NOT NULL,
 `status` tinyint(1) NOT NULL,
 `legitimate` tinyint(1) NOT NULL,
 PRIMARY KEY (`userid`,`messageid`),
 UNIQUE KEY `queueid` (`messageid`),
) ENGINE=InnoDB AUTO_INCREMENT=1010112 DEFAULT CHARSET=latin1

The problem is that depending on the primary key chosen, I face two outcomes:

The autoincrement as the primary key - SELECTS are fast, inserting new data is painfully slow ( 70 rows inserted per second )
Userid as the primary key - Inserts are fast ( 3000 rows inserted per second ) but since the rows are not clustered around the userid field, SELECTS kill the HDD performance once I start doing "SELECT *" on the table for multiple userids.

My queries all start with the userid, basically, this table won't ever be queried without userid - that's why it improves selects by so much. Because the data can be read sequentially.

The problem is, that the stream of inserts comes with random userid values ( they come ordered by messageid which I extract from other table ). I don't get all rows for a userid - they all come in a random order. So each time I get a userid, I have to place it in its assigned page. Every incoming row-insert is for a different userid.

Please tell me - is the SSD the only viable option? Or I can somehow make both SELECTs and INSERTS to be sequential?

( the table is partitioned horizontally and I insert rows in bulk of 50,000 to 70,000 elements, using transactions )


texts:
I want to merge data from one database to another. So I create dump with mysqldump and then import it to another database (with same tables structure). I don't have any problems (such as duplicate entries or something else) in this case.

But I do some merges for testing purposes and I'll do final merge later. So, I want to execute merge (data may be changed) a few times. Notice, my rows in my tables never deletes, only can be inserted or updated.

Can I create mysqldump with ON DUPLICATE option? Or may be I can merge dump that inserts new data and update modified data?

Sure, I can insert ON DUPLICATE in dump manually, but I want to automate merge process.


texts: PostgreSQL results of conditional insert

texts:
I've got a simple query which inserts data to table when value (unique) does not exist:

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM mytable WHERE hash = 'somelonghash'
    )
    THEN
        INSERT INTO mytable (id, hash, status, datecreated)
        VALUES (123, 'somelonghash', 2, '2018-01-01T12:34:56.789Z');
    END IF;
END $$;

But if I want to receive results like "RETURNING *" if there was an insert, I receive error: "query has no destination for result data".
I've tried something like:

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM mytable WHERE hash = 'somelonghash'
    )
    THEN
        INSERT INTO mytable (id, hash, status, datecreated)
        VALUES (123, 'somelonghash', 2, '2018-01-01T12:34:56.789Z')
        RETURNING *;
    END IF;
END $$;

or even like

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM mytable WHERE hash = 'somelonghash'
    )
    THEN
        WITH ins_res AS (
            INSERT INTO mytable (id, hash, status, datecreated)
            VALUES (123, 'somelonghash', 2, '2018-01-01T12:34:56.789Z')
            RETURNING *;
        ) SELECT * FROM ins_res;
    END IF;
END $$;

I assume I'm missing SELECT statement somewhere at the very beginning, like SELECT * FROM (IF-THEN-ELSE results), but can't figure out where. Also this query without IF-THEN works:

WITH ins_res AS (
    INSERT INTO mytable (id, hash, status, datecreated)
    VALUES (123, 'somelonghash', 2, '2018-01-01T12:34:56.789Z')
    RETURNING *
) SELECT * FROM ins_res;


texts: When does SSIS SQL Server Configuration write to the configuration table?

texts:
I am using SQL Server configuration in an SSIS pacakge. Other than when you create/edit the SQL Server configuration entry in the SSIS Package Configurations, are there other times that SSIS writes to the SQL Server table?

Reason I am asking is because what happens if I modify the connection manager the SQL Server Configuration is using by pointing it to a different database (same table name).

Will the SSIS Pacakge Configurations notice this right away or at run time or when the package is re-opened and then correctly create or update the SSIS Configuration table in the new Database that the connection manager is now pointing to? Or do I have to go into SSIS Pacakge Configurations and then back out so that it will refresh?


texts: Asynchronous database inserts from C# always result in a deadlock

texts:
I'm hoping this doesn't come off as too amateurish, but I'm converting a synchronous program to run asynchronously. So far, I've gotten everything running asynchronously except database inserts.
Essentially, where it fails is that two of the tasks are trying to insert into the same table at the same time, judging by the fact that the exception returned references the line of my stored procedure with the insert statement.

Here is the relevant C# code:

I attempt to spin the input loading function into a different context, which allows the program to continue processing (and which will eventually lead to calling this same function on another list of files, which almost always occurs before the first document is finished loading into the DB)

await Task.Run(() => context.LoadInput(fileList))

My input files are CSV files. I load them into the database using stored procedures that take a user-defined table type as a parameter. I assemble the UDT on the C# side and send it to the database. In the database, the following stored procedure is called:

CREATE PROCEDURE ImportDocument @documentData dbo.DocumentImportStructure READONLY, @newDocumentId INT OUT AS SET NOCOUNT ON;
BEGIN
    BEGIN TRY
    -- Detect duplicate file
    DECLARE @duplicateId AS INT = (SELECT Id FROM dbo.Documents AS d WHERE EXISTS (SELECT Id FROM @documentData as i WHERE i.[File_Name] = d.[File_Name] AND i.File_Size = d.File_Size));
    If(@duplicateId IS NOT NULL)
        If((SELECT [Status] FROM dbo.Documents WHERE Id = @duplicateId) < 2)
            SET @newDocumentId = @duplicateId
        ELSE
            BEGIN
                DECLARE @ErrorMsg AS NVARCHAR(2048) = 'The document "' + (SELECT [File_Name] FROM @documentData) + '" created ' + CAST((SELECT File_CreationDate FROM @documentData) AS NVARCHAR) + ' already exists in the database under file ID ' + CAST(@duplicateId AS nvarchar) + '.';
                THROW 60001, @ErrorMsg, 1;
            END;
    ELSE
        BEGIN
        -- Add new file
        BEGIN TRANSACTION
            INSERT INTO dbo.Documents([File_Name], File_CreationDate, File_Size, MailingType, StatementDate, DueDate, RecordCount, [Status])
                SELECT [File_Name], File_CreationDate, File_Size, MailingType, StatementDate, DueDate, RecordCount, [Status] FROM @documentData;
        SET @newDocumentId = @@identity
        COMMIT
        END;
    RETURN @newDocumentId
    END TRY
    BEGIN CATCH
        DECLARE @state AS INT = XACT_STATE()
        IF(@state = -1)
        BEGIN
            PRINT N'The transaction cannot be committed in its current state. Rolling back transaction.'
            ROLLBACK TRANSACTION;
        END;
        ELSE IF (@state = 1)
        BEGIN
            PRINT N'The the transaction can be committed in its current state. Committing transaction.'
            COMMIT TRANSACTION;
        END;
        THROW;
    END CATCH
END

Line 18 contains the INSERT statement, and it's also where the following exception is thrown soon after all of the documents start simultaneously being written to the database:

$exception  {"Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

The transaction cannot be committed in its current state. Rolling back transaction."}   System.Data.SqlClient.SqlException

I could load the data synchronously and just have everything else be asynchronous, but  I feel like there's a way to do this that I'm missing, or an issue I'm overlooking.


texts: Creat db snapshot of a db in restoring state

texts: Is it possible to create a snapshot of a SQL db that is in a restoring state?


texts: Move transactional replication subscriber to another server without re-snapshotting

texts:
I have publisher and subscriber at same server. I need to move subscriber's DB to another server but without creating new snapshot. I've tried this steps:

Stop all agents
Backup subscriber's DB
Restore subscriber's DB on
another server with KEEP_REPLICATION
Changed server name in
syssubscriptions, MSsubscriber_info, MSsubscriber_schedule
Changed
server name in scheduled task for replication

Then I've started agents and got error 20053 (Server not registered). I fixed it with adding linked server to new server

After that I've got an error 20044 (Subscription is outdated or not present), but subscription is present.

What did I do wrong or maybe I have missed any step(s)?

I can't use initialization from backup, because subscriber has some additional tables, views, routines.
Thanks


texts:
I need mysql admin_user (not root user) which can create write_user with permissions: CREATE, UPDATE, INSERT, DELETE and SELECT.

admin_user and write_user have access to main_db.

write_user will create user_db and only he can access to that db.

If I want that admin_user can create new user with some permissions, admin_user has to be created "WITH GRANT OPTION" but then he has access to all databases.

Question: How to create mysql user with CREATE USER grant but without access to new databases?


texts: What is the available SQL Server Express database size?

texts:
SQL Server Express allows the maximum of 10GB/10240MB for a database size.
I have a database which currently is ~8000 MB in size. The unallocated space is ~7000 MB.
The question is: how many megabytes are available for growth/storing data on this database from that moment on?
Is it ~2000MB or ~9000MB?
What will happen if the database .mdf file reaches the maximum of 10240MB while the unallocated space is still around 7-8 000 MB?
Will it prevent new records in the database from that moment on, or it will start using the unallocated space?
Thanks!


texts: MongoDB cloneCollection between CentOS servers times out at 2 hours

texts:
I'm trying to clone a MongoDB collection from CentOS server A to CentOS server B. Both are running 2.4.6. The copy works great until exaxctly 2 hours, at which point I get a 9001 socket exception: "errno:10054 An existing connection was forcibly closed by the remote host."

I have tried to set the tcp_keepalive_time to 3600, down from 7200 (2 hours) on both servers with the hope that my keep alive probes will tell routers to keep my connection alive after the 1 hour mark.

No dice. Even with a shorter keepalive time (and 70 second interval for keepalive probes), my connection dies at 2 hours.

Does anyone know how to set up my servers and/or my mongodb configuration to allow my tcp connection to be active past 2 hours?

Thanks!


texts: Error 'Unknown table engine 'InnoDB'' on query. after restarting mysql

texts:
I have mysql DB on server S1 (mysql version 5.1.41-3ubuntu12.7-log).

I have created master-slave for this DB on server S2 (mysql version 5.1.54-1ubuntu4-log).

The DB on S1 was using one data file (ibdata).

After dumping the DB to S2, I set innodb_file_per_table=1. This made every table to have its own ibd file. now everything went fine and smoothly.

After restarting mysql on S2, I faced a problem with getting this error:
Error 'Unknown table engine 'InnoDB'' on query. Default database: MyDB
and when I try to show engines, I get the following:

show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+

InnoDB is not listed.

In error log, I can see this:

InnoDB: Database physically writes the file full: wait...
InnoDB: Cannot initialize created log files because
InnoDB: data files are corrupt, or new data files were
InnoDB: created when the database was started previous
InnoDB: time but the database was not shut down
InnoDB: normally after that.
111016  8:24:11 [ERROR] Plugin 'InnoDB' init function returned error.
111016  8:24:11 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
111016  8:24:11 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=S2-relay-bin' to avoid this problem.

I have tried to delete ib_logfiles but this didn't work as well.
if i delete ib_logfiles and ibdata file, innodb will return back normally, but i can't access my innodb tables, ie. after deleting ibdata1 and restarting mysql

desc article;
ERROR 1146 (42S02): Table 'MyDb.article' doesn't exist

My innodb configuration in my.cnf is as follows:

innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_buffer_pool_size = 384M
innodb_log_file_size=5M
innodb_lock_wait_timeout = 18000

although the table is there!!

Has anybody faced such issue before??

Any idea is highly appreciated

Thanks


texts:
I'm getting the error:

Maximum Number Of Process Exceeded

I want to see (using a query) what the setting is in my database for the process limit.

My question is: What is the query to get the process limit on Oracle 11g?


texts:
I created a maintenance plan to run full backups on a SQL Server 2008 R2.

The schedule runs every last Thursday of the month. Sometimes due to some unknown reason, such as electric outage. When the backup is successful, I verify it by checking SQL Server report for backup and restore events.

Now my question is: does SQL Server provide a way to check the next time the maintenance plan will run based on the the maintenance plan schedule? Also is there is a script to run to show me this would be appreciated?


texts: MySQL ignores foreign keys constraints in a table representing a many to many relationship

texts:
In MySQL, the foreign keys of a table standing for a many to many relationship do not work even though I did set them to the "child" table named course_inscription.

When I execute a statement to insert a new row into course_inscription, MySQL must launch the error due to the checking of the foreign key constraint, but it does not work. I do not understand why the new row is "added" to the table successfully.

The structure and constraints of the relevant database are:

 CREATE TABLE student (
    idStudent INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    Gender VARCHAR(10),
    birthdate VARCHAR(40) NOT NULL,
    PRIMARY KEY ( idStudent )
  );

  CREATE TABLE subject (
    idSubject INT NOT NULL AUTO_INCREMENT,
    Name VARCHAR(100) NOT NULL,
    Credit INT NOT NULL,
    PRIMARY KEY ( idSubject )
  );

  CREATE TABLE course_inscription (
    idSubject INT NOT NULL,
    idStudent INT NOT NULL,
    Classroom VARCHAR(40) NOT NULL,
    PRIMARY KEY ( idSubject, idStudent ),
    FOREIGN KEY (idSubject) REFERENCES subject(idSubject),
    FOREIGN KEY (idStudent) REFERENCES student(idStudent)
  );

  SET FOREIGN_KEY_CHECKS = 1;

The insert statement:

INSERT INTO `studentmgnt`.`course_inscription`
    (`idSubject`, `idStudent`, `Classroom`)
VALUES
    ('1', '1', '12');

What did I do wrong to my database?  How can I correct the issue?


texts: best way to add Entity id & entity types in database

texts:
we are doing shopping site with mysql with eav model to store values.

in below image , 2 types of entity types are there under products_information table : 1) Mobile 2) T-shirts

I wanted to know in which table i have to store those entity types & entity ids

Is i need to create new table or adding above 2 columns in existing table ?


texts:
I have scenario where I need to store unstructured data but the rest of my data is structured and relational. An example of the type of unstructured data is as explained below:

User Type 1:

How do you blah blah : 5 fields

User Type 2 :

How do you blah blah : 3 fields

User Type 3 :

How do you blah blah : 7 fields

All 3 types are asked the same question "How do you blah blah" but each user type answers it using different number of fields. And there can be a lot of different user types.

For the relational data, I'm using MySQL but i'm a little confused on how to store this unstructured data:

Serialize as JSON and store in MySQL
Use NoSQL

My requirements are high reads, average updates, average inserts & no deletes. No JOINS needed. I need guaranteed writes & high availability. If I were to choose a NoSQL, it'd be a AP type according to the CAP theorem. I won't be hitting millions of records anytime soon.

I also plan to provide a text search for this data in the future, but it doesn't need to be a real time search, so I can always index the data using Lucene periodically. But of course, document based NoSQL implementations do provide this out of the box. But I have read in a few places where people have advised against storing JSON data in MySQL. But adding a NoSQL layer could be overkill.

What do I do & if you do advise me to go for NoSQL DB, which one should I choose?


texts: How do I design a database/query that queries users's answers to user tags

texts:
My current setup in PostgreSQL:

CREATE TABLE "usertag" (
    "id" INTEGER NOT NULL DEFAULT E'nextval(\'usertag_id_seq\'::regclass)',
    "questionTitle" VARCHAR(255) NULL DEFAULT NULL,
    "factTitle" VARCHAR(255) NULL DEFAULT NULL,
    "type" VARCHAR(255) NULL DEFAULT NULL,
    "defaultAnswer" VARCHAR(255) NULL DEFAULT NULL,
    "priorityLevel" INTEGER NULL DEFAULT NULL,
    "created_at" TIMESTAMP NULL,
    "updated_at" TIMESTAMP NULL,
    PRIMARY KEY ("id")
)

CREATE TABLE "usertaganswer" (
    "id" INTEGER NOT NULL DEFAULT E'nextval(\'usertaganswer_id_seq\'::regclass)',
    "userId" INTEGER NULL DEFAULT NULL,
    "inactiveUserId" INTEGER NULL DEFAULT NULL,
    "tagId" INTEGER NULL DEFAULT NULL,
    "created_at" TIMESTAMP NULL,
    "updated_at" TIMESTAMP NULL,
    "numberAnswer" NUMERIC(8,2) NULL DEFAULT NULL,
    "textAnswer" VARCHAR(255) NULL DEFAULT NULL,
    PRIMARY KEY ("id")
)

CREATE TABLE "usertagansweroptions" (
    "id" INTEGER NOT NULL DEFAULT E'nextval(\'usertagansweroptions_id_seq\'::regclass)',
    "answer" VARCHAR(255) NULL DEFAULT NULL,
    "userTagId" INTEGER NULL DEFAULT NULL,
    "created_at" TIMESTAMP NULL,
    "updated_at" TIMESTAMP NULL,
    PRIMARY KEY ("id")
)

CREATE TABLE "usertaganswer_answeredoptions" (
    "id" INTEGER NOT NULL DEFAULT E'nextval(\'usertaganswer_answeredoptions_id_seq\'::regclass)',
    "userTagAnswerOptionId" INTEGER NULL DEFAULT NULL,
    "userTagAnswerId" INTEGER NULL DEFAULT NULL,
    "created_at" TIMESTAMP NULL,
    "updated_at" TIMESTAMP NULL,
    PRIMARY KEY ("id")
)

So if I'm query users based only on one userTag, that's fine. Here's an example of a query that works as expected:

select distinct "user"."id"
from "user"
inner join "usertaganswer" on "user"."id" = "usertaganswer"."userId"
left outer join "usertaganswer_answeredoptions" on "usertaganswer"."id" = "usertaganswer_answeredoptions"."userTagAnswerId"
where "allowedToInteractWith" = true
and "usertaganswer"."numberAnswer" between 5 and 30

but when I try adding a criterion, I can't wrap my head around it. This works:

select distinct "user"."id"
from "user"
inner join "usertaganswer" on "user"."id" = "usertaganswer"."userId"
left outer join "usertaganswer_answeredoptions" on "usertaganswer"."id" = "usertaganswer_answeredoptions"."userTagAnswerId"
where "allowedToInteractWith" = true
and (
    "usertaganswer_answeredoptions"."userTagAnswerOptionId" = '5'
    or "usertaganswer_answeredoptions"."userTagAnswerOptionId" = '6'
    or "usertaganswer_answeredoptions"."userTagAnswerOptionId" = 4
)

but I don't want an 'or' of all the criteria I want an 'and'. But if I do the following, it will return zero results even thought it should return 1

SELECT "user"."id"
from "user"
inner join "usertaganswer" on "user"."id" = "usertaganswer"."userId"
left outer join "usertaganswer_answeredoptions" on "usertaganswer"."id" = "usertaganswer_answeredoptions"."userTagAnswerId"
where "allowedToInteractWith" = TRUE
AND  (
    "usertaganswer_answeredoptions"."userTagAnswerOptionId" = '5'
    or "usertaganswer_answeredoptions"."userTagAnswerOptionId" = '6'
)
AND "usertaganswer_answeredoptions"."userTagAnswerOptionId" = 4


texts:
I am in the early stages of designing a database, and I have little (none) database design/planning experience. The database is part of a job tracking engine..

As jobs are created, they are assigned a job number, description, client, and a number of keywords that are selected from a list. My questions relates to choosing the most efficient (runtime speed) way of linking the selected keywords to the jobs.

I see a number of way to do this, some less conventional and maybe more dangerous than others...

Option A - limit the maximum number of keywords that can be assigned jobs to value 'N' (likely 5) and have the following table columns..

  [id] [job number] [description] [client] [keyword 1] .. [keyword N]
    1     123           zyx          kk        word           test
    2     183           tyx          ff        test           -
    3     214           xyx          tt        bleh           -

Option B - Have a keyword table with a column for every keyword. The keywords in the entire database is administered manually, but there could be any arbitrary number of keywords, lets say X (likely in excess of 200). As job are created, a new row is added to the table and the job number, or id (link from another table) entered in each of the columns where the keyword has been selected. Eg

[word]     [test]       [bleh]   ....... [keyword X-1]     [keyword X]

 1            -            1                      1             -
 -            2            -                      -             -
 -            -            3                      3             3

Option C - Similar to 'Option B', except keywords are given their own table. But that would just mean there are X number of tables in my database...

At this stage option A is the front runner, but I don't particularly like the hard limit on the number of keywords that can be assigned to a job. Can anyone suggest an alternative design, so that I don't have a ridiculous number of rows/tables,  and so I don't have to limit the number of keywords assigned to a job.

Thanks


texts: How to detect a circular reference in a hierarchy table?

texts:
I have table like this:

CREATE TABLE MyTable (
    Id INT NOT NULL PRIMARY KEY,
    ParentId INT NOT NULL
);

ALTER TABLE MyTable ADD FOREIGN KEY (ParentId) REFERENCES MyTable(Id);
select * from MyTable
desc MyTable

INSERT INTO MyTable (Id, ParentId) VALUES (0, 0);
 INSERT INTO MyTable (Id, ParentId) VALUES    (1, 0);
 INSERT INTO MyTable (Id, ParentId) VALUES    (2, 4);
 INSERT INTO MyTable (Id, ParentId) VALUES    (3, 2);
 INSERT INTO MyTable (Id, ParentId) VALUES    (4, 3);

How to find a circular reference in table references?


texts: MySQL - A Count with INNER JOINS - that multiplies the result by 8

texts:
The following SELECT with INNER JOINS works, but does not count it right.

I have a tests table:

Tests

TestNumber (int primary key)
TestComplete (int 0 = not complete, 1 = complete)
...other stuff

with values

(7, 0)
(8, 0)
(9, 0)

TestUsers

TestNumber (int - ties in with TestNumber in Tests)
UserId (int - the person in the test)
UserSlot (int - the seat they have in the test)
ResignedTimestamp (TIMESTAMP - when they resigned from their seat in the test or '1970-01-01 00:00:01' if they have not resigned)

with values

(7, 25, 5, 1970-01-01 00:00:01) this counts
(8, 25, 2, 2016-04-23 12:00:00) this should not count because the one below has filled the slot
(8, 32, 2, 1970-01-01 00:00:01)

What I need to do is count the number of tests a user is signed into and are not complete. It must include all Tests they have not resigned from. (This is easy).

BUT it must also include all the Tests they have resigned from and another User has not taken their place. (This is the tricky bit).

Another user will have been deemed to have taken the place of the User in question if they have an entry in TestUsers with the same TestNumber and the same UserSlot (note both), and they either have not resigned themselves (ResignedTimestamp='1970-01-01 00:00:01') or they have resigned but their ResignedTimestamp is later.

Here is my SQL.

SELECT COUNT(*) FROM testusers tu
    INNER JOIN tests t ON
        t.TestNumber=tu.TestNumber AND
        t.TestComplete=0 AND
        tu.UserId=25
    INNER JOIN testusers tu2 ON
        (tu.ResignedTimestamp='1970-01-01 00:00:01' OR
        (tu.ResignedTimestamp!='1970-01-01 00:00:01' AND
        tu.TestNumber=tu2.TestNumber AND tu.PlayerSlot=tu2.PlayerSlot AND
        (tu2.ResignedTimestamp='1970-01-01 00:00:01' OR tu.ResignedTimestamp < tu2.ResignedTimestamp)
        )
    )

The result counts row (7, 25, 5, 1970-01-01 00:00:01) eight times, and adds to that (8, 25, 2, 2016-04-23 12:00:00) once. So it is giving me an answer of 9 when the answer should just be 1.


texts:
Consider a financial system, where the customer demands a financial statement consisting of
| opening_balance | amount_credited | amount_debited | closing_balance | remarks.
Two approaches to doing this are:

Store the above details (opening_balance, amount (+/-), closing_balance) during transaction entry

Just record the amount(+/-) & remarks and get my final statement from performing window queries in SQL (specifically LEAD() and LAG() functions)

Challenge: Consider 5 entries 1, 2, 3, 4, 5. There are high chances that middle entries will change at any time. For example, if the transaction_amount of 2nd entry changes, then it will affect the closing_balance, in return it will affect the opening balance & closing_balance of 3rd entry, and so on, sort of a chain.
Which approach better suits the current scenario?
PS. I'm using Django, but I don't really care about this, because my concern is the concept of implementation


texts: What are thresholds in SQL Server 2014 "big table join to small table" cardinality estimation optimization?

texts:
The SQL Server 2014 Cardinality Estimator white paper says:

  The new CE, however, uses a simpler algorithm that assumes that there is a one-to-many join association between a large table and a small table. This assumes that each row in the large table matches exactly one row in the small table. This algorithm returns the estimated size of the larger input as the join cardinality.

But it doesn't say how SQL Server determines what is a "large table" and "small table" for purposes of this optimization.

Are these criteria documented anywhere?  Is it a simple threshold (e.g. "small table" must be under 10,000 rows), a percentage (e.g. "small table" must be <5% of rows in the "large table"), or some more complicated function?

Also, is there a trace flag or query hint that forces use of this optimization for a particular join?

Finally, does this optimization have a name that I can use for further Googling?

I'm asking because I want this "use the cardinality of the large table" cardinality estimation behavior in a join of master/detail tables, but my "small table" (master) is 1M rows and my "big table" (detail) is 22M rows. So I'm trying to learn more about this optimization to see if I can adjust my queries to force use of it.


texts: How to get count for different columns on same table

texts:
Table #01 Status:

StatusID    Status
-----------------------
 1          Opened
 2          Closed
 3          ReOpened
 4          Pending

Table #02 Claims:

ClaimID     CompanyName StatusID
--------------------------------------
1               ABC     1
2               ABC     1
3               ABC     2
4               ABC     4
5               XYZ     1
6               XYZ     1

Expected Result:

CompanyName TotalOpenClaims TotalClosedClaims TotalReOpenedClaims TotalPendingClaims
--------------------------------------------------------------------------------
ABC                 2           1                      0               1
XYZ                 2           0                      0               0

How do I need to write the query so that I could get the result as expected?


texts: SSMS crashes when attempting to create a database diagram

texts: I'm currently using the latest version of SSMS (17.8.1) on windows 10 and everytime I try to create a database diagram it crashes without giving me any error message. I've already tried to re-install SSMS but the problem still occurs.


texts: How can I remove references to tables after removing .frm files?

texts:
So I had a bunch of csv files in my /var/lib/mysql/imports directory for importing into mysql tables I had created.  To clean up the folder I deleted all the files in the directory (incorrectly thinking it only contained .csv files).  The only problem was that the folder also stored table data from my imports database.  Now that I realized my error I was just going to recreate the database I did delete and re-import from the csv file.  However I cannot create the table again because mysql must have references to the table.  How can I remove this table completely so that I can simply recreate it and re-import the information?

Edit:

mysql> DROP TABLE imports.ncaa_passing_statistics;
ERROR 1051 (42S02): Unknown table 'ncaa_passing_statistics'
mysql> SELECT * FROM imports.ncaa_passing_statistics;
ERROR 1146 (42S02): Table 'imports.ncaa_passing_statistics' doesn't exist
mysql> CREATE TABLE imports.ncaa_passing_statistics (id INT);
ERROR 1050 (42S01): Table '`imports`.`ncaa_passing_statistics`' already exists
mysql>


texts: Getting PL/SQL: ORA-00942: table or view does not exist in plsql function but not from the command view

texts:
i have function with a cursor the read the dba_scheduler_jobs which is a view. How can i use a view in a cursor?

create or replace function fn_gss_chk_scheduled_jobs(p_key IN varchar2)
  return boolean is
  --Result     boolean;
  failed_cnt number;
  stas       varchar2(20);
  check_type varchar2(50) := 'Scheduled Jobs';

  cursor c1 is

    select JOB_NAME, COMMENTS
      from dba_scheduler_jobs
     where owner = 'FCUBSPROD'
       AND state <> 'SCHEDULED'
       AND state <> 'RUNNING';

begin

  for rec in c1 loop

    IF c1%FOUND THEN
      Dbms_Output.Put_Line(Rec.Comments);
      stas := 'ERROR';
      pn_gss_update_dash_det(p_key, rec.job_name, rec.comments, 'D');

    ELSE

      stas := 'OK';
    END IF;
  end loop;

  pn_gss_update_dash_det(p_key, check_type, stas, 'S');

  return(true);
end fn_gss_chk_scheduled_jobs;


texts: Permissions for executing subscription in SSRS

texts:
I'd like to execute an SSRS subscription after some ETL work run by a SSIS package that takes a varying amount of time completes. I'm considering using this method, which basically consists of SSIS calling the same stored procedure that the SQL Server Agent jobs for subscriptions call to execute a subscription.

However, I'm not sure what the best way of managing permissions to execute the subscription would be. For example, I'm reluctant to add users to the RsExecRole because it's discouraged by Microsoft in some documents, and I'm reluctant to grant object-level permissions to execute the ReportServer.ado.AddEvent procedure because they'll likely get overlooked in any migrations. (Edit: I suppose we could work around this by having something execute the Agent job instead of calling the stored proc.)

I'm also hoping to avoid issues with the the code that executes the subscription breaking when one of the GUIDs changes.

How do SSRS professionals manage permissions for this type of request? Alternatively, is there a simpler method that bypasses these issues?


texts: How to add indexing for a search based on multiple columns in MySQL table

texts:
I am adding a search button with the suggestions displayed while we type on it similar to google and I need to search multiple columns in a single table. How to add indexing for that columns?

I have two tables in my application named as Employer and Employee. I want to add a search box so that the Employer can search employees of only his organisation based on their name or email or phone number. When the employers types on the search box I need to show the suggestions below. For Ex. when employer types Jo on the text box, I will show suggestions as

1. Jon, jon@gmail.com, 12345

2. Bobby, jon-bobby@gmail.com, 9876

3. .....

The Employee table has columns id, name, email, phone, employer_id fk references Employer table.

What I think the query might be is

SELECT *
FROM Employee
where employer_id = 1
and LOWER(name) like '%jo%'
or LOWER(email) like '%jo%'
or LOWER(phone) like '%jo%'

I want to know whether this is a valid query to use. I am not sure about its performance. Also I am not sure how to add index to this table in this particular use case.

I would really appreciate your valuable suggestions.


texts: What would make a MySQL database create tables in a different collation other than its default?

texts:
I am adding tables to a Wordpress database on my localhost and it is changing my collation from utf8_general_ci to latin1_swedish_ci.  I'm kinda stumped because I'm not familiar with working directly with databases since I am new to them.  Could you explain why this happens?

My default character set is utf8 and my collation is utf8_general_ci.

Also, when I use the same code to create a table in other databases it works correctly.

HOLY CRAP I just now tried using wordpresses $wpdb class and it is doing the SAME THING!  What the crap!

FOUND ANSWER AT WORDPRESS ANSWERS ---- https://wordpress.stackexchange.com/questions/5927/default-table-collation-on-plugin-activation?rq=1


texts:
I tried below 2 codes.... showing same error

1.SELECT columnname FROM tablename ORDER BY columnname DESC LIMIT 1

SELECT MAX(columnname ) AS max FROM tablename

I am trying to increment value of column.(++string) It worked properly till 10. After 10 it is not increment.

then i checked manually, then i get result 9.

  Result Got - 1 2 3 4 5 6 7 8 9 10 10 10 10

  Result Required - 1 2 3 4 5 6 7 8 9 10 11 12

I think database count 10 as 1. i dont know why?

In future i want change number to string like... pro1, pro2 like that..so that i dont want to change datatype


texts:
I'm trying to build SQL Server 2012 Cluster on top of Windows Cluster 2008R2.

Can anyone please suggest.. do we need quorum disk in setting up SQL cluster 2012 on top of windows cluster 2008r2


texts: Postgres connection access denied on IPv6 address

texts:
Installed PostgreSQL 9.1 x64 on Windows, set up a listen address, but when connecting with pgAdmin I get the following error. Not sure why PostgreSQL is seeing my IPv6 address and not my regular IP address:

To get authentication working, based on the error message, I updated pg_hba.conf with this:

host    all         all fe80::c5d2:XXXX:XXXX:3bc0/12          trust

That worked, but it's ugly, and too specific. I tried the following based on PostgreSQL docs, but none worked, I get the same 'access denied' error:

local all all trust
host all all 0.0.0.0/12 trust

I got this one working, which covers the entire IPv6 address space, but how can I specify an IPv6 range for more restriction?

host  mydb  myuser  ::/0   trust

Questions

Why does pgAdmin pick up my IPv6 address and not my normal IP?
How do I specify a range in IPv6 without resorting to ::/0?


texts: How to query/join tables where foreign key constraint is set in schema

texts:
I'm relatively new to sqlite and databases in general.
I've successfully setup a schema of 4 tables, each having a foreign key pointing to its parent table.  The schema also has the foreign key constraint set on the child keys.

Performing a query where every table is joined with the SELECT * FROM...JOIN...ON ......... works without issue, but it is laborious manually connecting the keys.

My question is: Is it possible to perform a query/join of the tables without the ON statement which links each parent/child key relationship?  Since this was already established in the schema definition, this seems to be redundant.  I'm trying to simplify the sql command and make use of the parent/child relationship already defined.  When I perform said query but omit the ON statements, the results are not what I expected.  Any guidance is appreciated.


texts: SQL Select where value = to another select

texts:
I have two tables. Orders, and OrderParts. One Order can have many OrderParts.

I want a list of all the order.customerID where the number of OrderParts ordered = Total_number_parts in the order table.

Below is where i have got to so far, but where do i put the where sum(partqty)=total_number_parts bit!!

Select a.customerID, a.Total_number_parts
From Orders a
Join (Select sum(PartQty) from OrderParts Group by customerID) B
   on B.customerID = a.customerID
Group By a.CustomerID


texts: How to handle multiple associative tables?

texts:
I really need so help regarding an issue I'm having. I'm trying my best to explain the problem, if anything is not clear please tell me. Some help would mean the world to me!

Here's my problem, let's say I have these 4 tables:

**CRIMINAL**
+ ID
+ Name
+ Image

**CAR_CRIMINAL**
+ ID
+ Type
+ Image

**COMPANY_CRIMINAL**
+ ID
+ Name
+ Image

**IMAGE**
+ ID
+ Link

For example, all entities (Criminal, car_criminal, Company_criminal) all have the attribute image. But an image can be assigned to multiple companies and a company can have multiple images so therefor it's a many-to-many relationship right?

That's also applicable to the all other tables. My question is, would I have to create an associative table for each of those many-to-many relationships or is there another way to resolve this problem?


texts: How can I get identity-like values in a MERGE INSERT statement

texts:
I would like to merge a table bar into another table foo, and while doing so I have to assign a unique value to a column messagenr. This is my attempt to do so (sql-server):

MERGE foo AS target
using bar AS source
ON target.message LIKE source.message
WHEN NOT matched BY target THEN
INSERT (messagenr,
        language,
         message)
VALUES ((SELECT Max(messagenr)
         FROM   foo)
       + 1,
       'EN',
       source.message);

Of course it is not working. I get the very same value for messagenr for every new dataset inserted.
I cannot modify the table structure itself, so I have to work with the structure given.

How can I get a UNIQUE value for messagenr in every new dataset in the target table?


texts: Count how many consecutive days a user log in

texts:
I have a table that contains every login a user makes. I need to count how many consecutive days a user log in.
I just compute the count by selecting all timestamp and cycling them with php.
Is there a more practical way to do this with mysql?

I have a users_login table with user and datetime column:

SELECT `datetime` FROM `users_login` WHERE `user_id`=`3123` order by `datetime` desc;

Right now I implemented a for loop to scan every datetime and check if the timestamp is inside the day range.

Should I keep track of the count at every login? I means, having a column consec_days starting from 0 and increasing by one at every daily login?


texts: i want to export from mysql into sqlite. currently i can export from mysql into a dump file. the only problem is importing to a sqlite db. the import feature on sqlite does not currently support the dump from mysql. is there any other way.


texts:
echo $ORACLE_SID
YBRD
conn sys/hello as sysdba
SQL>shutdown immediate

Then I was waiting for it to shutdown for a couple of minutes. It didn't, so I closed my window.

Then I again logged in and it displayed:

ORACLE IMMEDIATE SHUTDOWN IN PROGRESS

Will it shutdown automatically by itself after a while?


texts: Does varchar size matter in temporary tables?

texts:
There is a debate at my wife's work about just using varchar(255) for all varchar fields in temporary tables in stored procedures.  Basically, one camp wants to use 255 because it will always work even if the definition changes, and the other camp wants to stick with the size in the source tables for potential performance improvements.

Is the performance camp right?  Are there other implications?  They are using SQL Server.


texts: unexpected null returned from calculated columns

texts:
I'm working on a program that will calculate journey times based on bluetooth MAC addresses and i'm trying to nail down the mySQL commands I need before I get cracking on the software part of it.

i have the following "rough & dirty" command below which pretty much does what I need:

select *,
time(bluetooth_2_maxrssi.timestamp) as "time_1",
time(bluetooth_1_maxrssi.timestamp_1) as "time_2" ,
time(time(bluetooth_2_maxrssi.timestamp)-time (bluetooth_1_maxrssi.timestamp_1)) as "JT1",
time(time(bluetooth_1_maxrssi.timestamp_1)-time (bluetooth_2_maxrssi.timestamp)) as "JT2",
if(time(time(bluetooth_2_maxrssi.timestamp)-time (bluetooth_1_maxrssi.timestamp_1)) like "%-%","670->671","671->670") as "direction"
from bluetooth_1_maxrssi
inner join bluetooth_2_maxrssi on bluetooth_1_maxrssi.MAC_1=bluetooth_2_maxrssi.MAC
where bluetooth_1_maxrssi.timestamp_1 like "%2013-03-25%" and bluetooth_2_maxrssi.timestamp like "%2013-03-25%"

which produces a set of results for a single day in the format shown in this picture:

(This table is just data table for site 2 bolted onto the side of the data table for site 1, then the calculation is done in some extra columns.)

The problem is, as you can see, there are a quite a few null entries in these results (JT1 & JT2). When extended through to the entire  amounts to roughly half of the table having null results when there should be a value calculated.

Does anybody know what is going wrong? it's very likely that i am taking the completely wrong approach to this, or there is a much simpler and easier way to go about doing this.

any and all assistance is very much appreciated.


texts:
Our company servers are currently hosted on a VPS and we've decided to move into dedicated servers.

When choosing the best hardware for a DB server, what should we invest more resources:
better CPU (more cores)? or more RAM?

Where is the best ROI?

Any suggestions?


texts: Distribution Agent running but subscription does not exist?

texts:
I have an issue where in my replication topology, the distribution agent is running, and says there are no commands to distribute. However, checking the undistributed commands, it shows there are a few hundred thousand left. If I look at the subscriber and choose validate subscription, then SSMS tells me that the subscription does not exist, although I can see it in object explorer.

It is a push subscription, and there are two further push subscriptions that are working fine.

Any idea what could cause this? How can I fix it?


texts: Does the SQL/JSON Path specification allow accessing the property of a filter expression's result

texts:
I noticed a difference in the evaluation of a SQL/JSON path expression between Oracle and Postgres and I wonder which one is correct.

Consider the following JSON structure:

{
  "key1": {"id":1000, "price": 42},
  "key2": {"id":2000, "price": 3.14},
  "key3": {"id":3000, "price": 2.7}
}

To get the price of the product with ID = 1000, Postgres allows me to use the following JSON path expression:

jsonb_path_query(doc, '$.* ? (@.id == 1000).price')

However, when using Oracle's json_value() (or json_query() - doesn't matter)

json_value(doc, '$.* ? (@.id == 1000).price')

I get the following error (using Oracle 18c):

 ORA-40597: JSON path expression syntax error ('$.* ? (@.id == 1000).price')
 JZN-00209: Unexpected characters after end of path
 at position 21

There is workaround in Oracle 18c:

json_value(json_query(doc, '$.* ? (@.id == 1000)'), '$.price')

So my question is:

Does the SQL standard allow the property access as done in Postgres' implementation?
Or is this simply not specified in the standard and marked as "implementation dependent"?

(Given Postgres' track record with sticking to the standard as much as possible, I suspect Postgres is correct and Oracle is not implementing the full standard)


texts: Referencing label from Select clause in where, simple sql query

texts:
Consider I'm having a query such as:

SELECT student.name, MAX(grades.final_grade) as max_grade
From grades , students
where student.id == grade.student_id and max_grade == 90

Is this query wrong and won't work, because it's illegal to acess label mentioned in select in a where clause?


texts: Using SSAS for retrieval data instead of using SSRS to make a report?

texts:
If you work with Reporting service in order to create a report and you need to retrieve data from the relational database.

The datamodel of the relational database can be structured in a certain way that can be very difficult to retrieve right data by using join and lots of SQL. In order to save time and cost, it is recommended to use analysis service to retrieve right data that takes less time and cost and the result of the analysis service can be used in reporting service?

In other words, is it recommended to retrieve data in SSAS that will sent to SSRS instead of using SSRS to do lots of SQL coding in order to display right data in the report?

// Fullmetalboy


texts: Temporarily Archive Databases on SQL Server 2008 R2

texts:
I've got a client that has approximately 100 databases across 10 servers. We're attempting to create a standardized process for removing old and unnecessary databases. In the process, we'd like a step where we can temporarily 'archive' a database in case it is still in use. This way, we can easily tell if someone is attempting to access it but can not, and if they are we can turn it back on. Otherwise, we'll remove it.

We have backups, so obviously one option would be to create the backup and delete the database. Then, if it needs to be restored, restore from a backup. However, my client is looking for a simpler method if at all possible.

I've googled for awhile and I can't find any method of temporarily archiving a database in SQL Server. So, I was considering recommending that the client rename the database for approximate a week, and then, depending on the response from clients, delete the database. This method would still include the backups.

My question boils down to: What are the potential pitfalls with temporarily renaming a database? Does SQL Server remove any logins or authentication information when a database is renamed and then renamed back? Does anyone have a more standardized recommendation for this process, or a direction to point me to get me researching further?

Thank you!


texts:
I am  new to sqlcmd and I want to create a new user. I have done the following:

1> CREATE LOGIN FYI_DBA WITH PASSWORD = 'V_2017'
2> go
1> CREATE USER FYI_DBA FOR LOGIN FYI_DBA
2> go

Then from cmd prompt I ran  the following:

sqlcmd -s LOCALHOST -U 'FYI_DBA' -P 'V_2017'
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user ''FYI_DBA''..

What am I doing wrong?


texts: How to prevent/detect fetching data from another database on same server

texts:
I have discovered a problem in our development where I need some help.
We had some reports developed by an outsourced person, and now I am cleaning up his mess.
The biggest problem is that he has often written select statements using 3 parts, for example
[DB_T].[dbo].[SomeTable]

Whether this is good practice or not, I don't care right now.
My problem is he sometimes fetches data from our TEST database in Production queries.
So I was hoping for some way to detect this.
Let's say my test database is called DB_T and my production database is called DB_P and that the application has a connection string to DB_P
Now I discovered query like this
select *
from   table1 t1
  join [DB_T].[dbo].[Table2] t2 on t1.id = t2.id

When this query is executed, the resultset will unfortunate contain data mixed from both databases.
My question is, is there some way I can detect this ?
Some of these queries are written in a C# Winforms application, some are in a view.
But all will be executed from a c# WinForms application with a connection string pointing to DB_P
It will take me weeks to dig thru all his queries and look for this (some are build at runtime in string variables)
So it would help if I could detect this somehow, or maybe there is a way to disallow reading from another database. In that case the application would throw an error, which is fine by me, better an error then working with wrong data.


texts: Fastest way of restoring log shipping of multiple databases

texts:
Looking for the fastest way of restoring multiple SQL Server standby databases that got out of sync.

Server A is a production server, has 50 databases configured with LSBACKUP jobs.
Server B is a standby server, has 50 databases in READONLY/STANDBY mode getting up to date with LSCOPY and LSRESTORE jobs.

The following error arises after a long maintenance*:

  The log in this backup set begins at LSN 5286000000042100001, which is too recent to apply to the database. An earlier log backup that includes LSN 5286000000039400001 can be restored.

Obviously, the required LSN is nowhere to be found so the way to fix this would be to take a full backup on production database with NORECOVERY and restore it on the standby, then restart the restoring jobs.

The issue here is doing so one by one is a long and tedious process:

What is the fastest approach to solve this problem? I'm not as much looking for a way to make the backups faster than a way to automate the whole.

* We downgraded from Enterprise to Standard and somehow the log shipping chain got broken in the process.


texts:
I am setting up a brand new SQL 2017 installation. I want to set up and use gMSA(s) for the Service Accounts. What are the best practices for SQL Server? Should I use individual gMSAs for each service? Should there be individual gMSAs for each server or instance or site?

I have read the following and several other articles and posts:

Follow best practices described here:
https://docs.microsoft.com/en-us/sql/sql-server/install/security-considerations-for-a-sql-server-installation?view=sql-server-2017
Consider the use of Managed Service Accounts and Group Managed Service Accounts described here:

https://blogs.msdn.microsoft.com/markweberblog/2016/05/25/group-managed-service-accounts-gmsa-and-sql-server-2016/
https://blogs.msdn.microsoft.com/markweberblog/2016/05/25/group-managed-service-accounts-gmsa-and-sql-server-2016/

Any help that you can offer would be awesome.


texts: In postgres is there any real drawback on using a json field over multiple tables and one to many relationships?

texts:
In our current application we are basically going to provide something called "translations" for each field. So a table would originally be like:
CREATE TABLE organisation (
   id SERIAL PRIMARY KEY,
   name TEXT NOT NULL DEFAULT '',
)

But instead the name would now be language dependent.
The basic solution would be to no longer store the name field in the organisation table but instead in an "organisation_language" table:
CREATE TABLE organisation (
   id SERIAL PRIMARY,
);

CREATE TABLE organisation_language
(
  id SERIAL PRIMARY KEY,
  organisation INTEGER NOT NULL,
  field_name TEXT NOT NULL,
  field_language TEXT NOT NULL,
  field_translation TEXT NOT NULL DEFAULT '',

  FOREIGN KEY (organisation)
    REFERENCES public.organisation (id) MATCH SIMPLE
      ON UPDATE CASCADE
      ON DELETE CASCADE
      NOT VALID
);

(And yes I'm deliberately ignoring I could add a lookup table for language keys like en-US and refer to that).
However, now I can no longer "guarantee" that every field (like name) is defined. Of course the code could make sure it does but that's another layer. It also adds quite a bit of complexity as it is no longer easy to see what fields even belong to a datamodel. Or what translations are given for a certain organisation.
If I look at the JSON I would actually "like" my backend to send to the frontend upon requesting "data from organisation 1" it would look like:
{
    id: 1
    name: {
        "en-us": 'hello world',
        "nl-nl": 'hoi wereld'
    }
}

On top of that, the main actions that happen are "insertion" - but then insertion of all data at once (so all translations at once), and retrieval of all language data, not ever a single language. (Due to "paths", like if a field doesn't exist in en-gb check en-us, and this calculation happens in backend).
Modification happens so rarely that we are even considering to just "not support it" and instead go for copy-on-write and deactivating the old "organisation".

This made me think, is it a bad idea to change the type of the "name" field to JSON, instead of having a language table? (and do this for all fields). It would then look like:
CREATE TABLE organisation
(
  id SERIAL PRIMARY KEY,
  name JSON NOT NULL DEFAULT '{}'::JSON
);

And inserted data into the name field would be (backend has to verify the schema):
{
    "en-us": 'hello world',
    "nl-nl": 'hoi wereld'
}

Is there an actual drawback to using JSON fields, as opposed to using a language table? It has the advantage of easier lookup, and insertion (or deletion). And since modification is not supposed to happen anyways there's no drawback - correct?
Or will PostgreSQL choke on this later?


texts: Oracle change initialization parameter with "deferred" keyword

texts:
The description of deferred and scope keyword is listed below (extracted from Oracle documentation). The meaning of them gives me an indication that deferred can just use the memory scope. Is my understanding right? Thanks.

DEFERRED
The DEFERRED keyword sets or modifies the value of the
parameter for future sessions that connect to the database. Current
sessions retain the old value.
SCOPE
The SCOPE clause lets you specify when the change takes effect.
Scope depends on whether you started up the database using a
traditional plain-text parameter file (pfile) or server parameter file
(spfile).
MEMORY indicates that the change is made in memory, takes effect
immediately, and persists until the database is shut down. If you
started up the database using a parameter file (pfile), then this is
the only scope you can specify.
SPFILE indicates that the change is made in the server parameter
file. The new setting takes effect when the database is next shut down
and started up again. You must specify SPFILE when changing the value
of a static parameter that is described as not modifiable in Oracle
Database Reference.
BOTH indicates that the change is made in memory and in the server
parameter file. The new setting takes effect immediately and persists
after the database is shut down and started up again.
If a server parameter file was used to start up the database, then
BOTH is the default. If a parameter file was used to start up the
database, then MEMORY is the default, as well as the only scope you
can specify.


texts: RECOVERY MODEL changed on a database, no record in Default Trace

texts:
I have a Log backup job running every hour via SQL Agent. This morning it failed at 3:00 AM with this error:
The statement BACKUP LOG is not allowed while the recovery model is SIMPLE.
But it ran fine at 2:00 AM. So sometime between 2 and 3, it went from FULL to SIMPLE.
My response was:

Change it back to FULL recovery
Run a FULL DATABASE BACKUP
Run the Log Backup job to make sure it would still work.
All good, no data loss.

So I got to investigating the Default Trace for events about possibly WHO might have made the change between 2 and 3. I first identified the event on Step #1 above so I knew what types of events I was looking for. Unfortunately, there is no event to say, "The recovery model changed from x to y". All it has is "Object: Altered" for my login for the database in question, with rows for BEGIN and COMMIT.
Here's the issue: I found NOTHING in the default trace in the time span to indicate that anyone made a change to this database.
Question: How could this change have occurred and it NOT get recorded in the Default Trace? It certainly recorded the fact that I changed it back. Why not the first change?
Any help would be appreciated.


texts: How to get table definition of a PostgreSQL table?

texts: I would like to get field's name, datatype of each field, length of each field and precision of each field of a PostgreSQL table. Please let me SQL query to get this information.


texts: Can MySQL show the respective timestamps in each INSERT output message?

texts:
For example, after an INSERT, I see:

  Query OK, 296 rows affected (0.02 sec)
  Records: 296  Duplicates: 0  Warnings: 0

I would like to see timestamps with these lines, if possible, e.g.:

  2019-02-15 12:00:00.000 - Query OK, 296 rows affected (0.02 sec)
  2019-02-15 12:00:00.020 - Records: 296  Duplicates: 0  Warnings: 0

Can I configure MySQL to do this?


texts: Why am I getting error 35353 when enabling CDC on a table with a CCI?

texts:
I'm trying to enable Change Data Capture on a table with a clustered columnstore index, a unique non-clustered index, and a non-unique non-clustered index in SQL Server Standard 2019. The command fails with this error:
Msg 35353, Level 16, State 1, Procedure sys.sp_cdc_enable_table_internal,
Line 243 [Batch Start Line 16]
Change Data Capture cannot be enabled on a table with a clustered columnstore index.
Consider dropping clustered columnstore index

I don't understand why I'm getting this error since, as far as I can tell from Microsoft documentation, CDC is supported for CCIs.
Has anyone else run into this error or know why it's occurring?


texts: Making the Oracle optimizer use a virtual column to find out about a partition

texts:
I have a large table that has three columns like this:

"START_DATE" DATE,
"START_VALUE" NUMBER(10,7)
"START_DATE_VALUE" NUMBER(18,7)
  GENERATED ALWAYS AS
  (
    (extract(YEAR FROM START_DATE) * 10000 +
     extract(MONTH FROM START_DATE)*100 +
     extract(DAY FROM START_DATE))*power(10,3) +
     (START_VALUE+180)
  ) VIRTUAL

The START_DATE_VALUE column is a virtual column that is used for partitioning. However, when I have a query like this:

select *
from mytable
where
      start_date > to_date('02-01-2012', 'MM-DD-YYYY')
 and start_value > 120.23452

It scans all partitions for the result. How can I make Oracle use the virtual column and then pick just the right partition to work on it?

Sorry my table definition is very large, I can't copy it in here.


texts:
I am working in a PHP application and some "complex" queries are starting to appear in the code. Because of the complexity I am not able to use any ORM and the only resource I have is a plain SQL and PHP MySQL native functions which I don't like.

Without more here is one of the queries I want to convert into a view:

SELECT
  COUNT(*) AS 'rec',
  CONCAT(
      IF(agreement_list.ActiveFlag, '', 'Agreement is Inactive.'),
      IF(agreement_type.ActiveFlag, '', 'Agreement Type is Inactive.'),
      IF(distributor.ActiveFlag, '', 'License Distributor is InActive.'),
      IF(agreement_distributor.ActiveFlag, '', 'Agreement Distributor is InActive.'),
      IF(customer.ActiveFlag, '', 'Customer is Inactive.'),
      IF(cf_program_level.ActiveFlag, '', 'Program Level is Inactive.')
  ) AS errormessage,
  IF((agreement_list.ActiveFlag + agreement_type.ActiveFlag + distributor.ActiveFlag + agreement_distributor.ActiveFlag + customer.ActiveFlag + cf_program_level.ActiveFlag) < 6, 1, 0 ) AS error
FROM
  license
  JOIN agreement_list ON (agreement_list.AgreementTypeID = license.AgreementTypeID AND agreement_list.CustomerSiteID = license.CustomerSiteID AND agreement_list.Source = license.Source)
  JOIN customer ON (customer.id = license.CustomerSiteID AND license.source = customer.Source)
  JOIN distributor ON (distributor.DistributorID = license.DistributorID AND license.source = distributor.Source)
  JOIN distributor AS agreement_distributor ON (agreement_distributor.DistributorID = agreement_list.DistributorID AND agreement_list.source = agreement_distributor.Source)
  JOIN agreement_type ON (agreement_type.AgreementTypeID = license.AgreementTypeID AND license.source = agreement_type.Source)
  JOIN cf_program_level ON (cf_program_level.CFProgramLevelID = '{$CFProgramLevelID}' AND license.source = cf_program_level.Source)
WHERE
  license.AgreementTypeID = '{$AgreementTypeID}'
  AND license.CustomerSiteID = '{$CustomerSiteID}'
  AND license.Source = '{$Source}'

$CFProgramLevelID, $AgreementTypeID, $CustomerSiteID, $Source are parameters coming from PHP and that's my only problem. How do I pass a parameter to a view if it's possible?

I am using MySQL 5.6 at the moment.


texts:
I developed a document tracking database and front end C#/.NET app on my local machine (SQLServer Express and VS Express).  Once it was working and usable I had our IT branch install the database (SQL Server Express) on the main server, and pointed the app to it instead of my local version.  I then used bcp to export from my box and import to the server.  I noticed that the parent child relationships are now all broken.  Each table has an identity column called UID, which is referenced in child tables.  But the UIDs did not copy as they were on my machine, rather they were all recreated upon insert.  I have not found any documentation on the proper way to handle this, but now the data is basically unusable.  I can easily redo the BCP, but can anyone point me to my mistake?

Here is what I used for the export (both the data and format just in case):

bcp DocControl.dbo.Documents out Documents.dat -n -S MYMACHINE\SQLEXPRESS -T
bcp DocControl.dbo.Documents format nul -T -n -f Documents.fmt -S MYMACHINE\SQLEXPRESS

And for the import (also, before import I had duplicated the schema, so the tables, keys and stored procedures were all recreated ahead of time, but without the data):

bcp DocControl.dbo.Documents in Documents.dat -n -S SERVER\SQLEXPRESS -T

This is just for one of the tables, but the other tables were done exactly the same way.  Now the Documents table UID starts at 1 and goes up, whereas previously it started at something like 380 and went up from there.  So the referring IDs in child tables no longer point to the right parent.


texts: Less than operator not working on where clause in mysqldump

texts:
I am trying to backup some tables with the built in mysqldump function from MariaDb version 10.4. My tables all have a column called insertDateTime with a timestamp in the form of 2019-09-11 12:00:00

Querying one of my tables with the simple statement:

select * from account where insertDateTime < '2019-08-13 18:00:00'

will return a set of rows where that condition matches. However when I run the following code in command prompt no rows are returned in my dumped sql file.

C:
cd c:\Backup_Location

"C:\Program Files\MariaDB 10.4\bin\mysqldump.exe" -u root -p password database account > account.sql --where="insertDateTime < '2019-08-13 18:00:00'"

I have been wracking my brain on why these two statements should return different amounts of rows but am unable to figure out why. Does anyone know why this would be.


texts: Retrieving data speed tweaks SQL Server 2005

texts:
I have a database in Microsoft SQL Server 2005.

I have table with 3 columns, namely HASHKEY (BIGINT), NOTE_ID (INT) and TIME_OFFSET (INT).

Columns HASHKEY has 19 digit BIGINT values, which can DUPLICATE. But values will be always of 19 digits.

I have application mainly depends on data in this table. Application retrieves data from this table with query like:

select HASHKEY, NOTE_ID, TIME_OFFSET
from TABLE_NAME
where HASHKEY in (<list of around 30000 hashkeys>)

But this query takes around 2 minutes to retrieve data. This is my problem. The application is real time processing application, and need to retrieve data in about 5 seconds. How can I tweak things in server, so time to retrieve data can be decreased?

I have indexed the table by HASHKEY column when I created table, but still retrieving data is taking much time.

Is there any setting which I can do in database to so time can be decreased? I will welcome any type of solution. But I need to solve this. I am not very expert in this.

Also HASHKEY is just random values of 19 digits, no relation with other values.

Result of following query is,

select count(HASHKEY) from TABLE_NAME
go
select count(distinct(HASHKEY)) from TABLE_NAME

Result:

225899932
189200251

Time taken: 2 minutes, 1 second

EDIT

This is script to create table:

USE [fp]
GO
/****** Object:  Table [dbo].[fp_core]    Script Date: 09/13/2013 22:40:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[fp_core](
    [hashkey] [bigint] NOT NULL,
    [note_id] [int] NOT NULL,
    [timeoffset] [int] NOT NULL
) ON [PRIMARY]

I will ask random set of around 30000 hashkeys for data, no any ordering.

When I was having around 20000000 rows in table, query was taking less than 2 seconds, but now retrieval time is increasing.

EDIT

And here is script to create index, only one index in this table.

USE [fp]
GO
/****** Object:  Index [IX_fp_core]    Script Date: 09/13/2013 23:04:24 ******/
CREATE NONCLUSTERED INDEX [IX_fp_core] ON [dbo].[fp_core]
(
    [hashkey] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

Please help me.


texts:
I am backing up a database to a network drive.

I am seeing the below Wait types for the backup. I have also tried backing up to a local drive and same issue (though backup does run much quicker). What is the backup waiting on?


texts: MySQL Count Rows with Two Cells the same

texts:
I have a db where the cells are height and width. (It's for banner ads)
Some banners are the same width, but different height, or visa versa.
for example:
row1 hieght = 600 width = 120
row2 hieght = 600 width = 120
row3 hightt = 600 width = 90
row4 height = 125 width = 125
etc

I want to know how many rows are 600x120, how many are 600x90, etc

How do I fix the sql query as follows to get the info I want?

$sql="SELECT  COUNT(`height`) GROUPED BY (`width` )
FROM  `rv_banners`
WHERE 1 " ;


texts: Relating 2 tables with possible wildcards?

texts:
I am having trouble getting the desired results when trying to relate the following tables.

NULL in the following table essentially means that if it is not an exact match with another row then choose this one.  A wildcard if you will.  I have 100% control over this table so if this is a bad design for what I am trying to accomplish please advise.

tblRateCategory

RateCategoryID Managed Customer    Category
10             0       NULL        NULL
20             0       NULL        A
30             0       CustC       A
40             1       CustC       A
50             1       NULL        A
60             0       NULL        B

tblJob

JobNumber Managed Customer     Category
1         0       CustA        C
2         0       CustB        D
3         0       CustC        A
4         1       CustC        A
5         0       CustD        A
6         0       CustE        B
7         1       CustF        A

Desired results

JobNumber RateCategoryID
1         10
2         10
3         30
4         40
5         20
6         60
7         50

I have tried this which gave duplicate job numbers and incorrect RateCategoryID if Category and/or Customer has match.  So I know I need to somehow look for match first and then settle for NULL or Any if no match found.

Select JobNumber, RateCategoryID
From tblJob J
Inner Join tblRateCategory RC
ON isnull(RC.Category,J.Category)=J.Category and
RC.Managed=J.Managed and isnull(RC.Customer,J.Customer)

Results from above query:

JobNumber RateCategoryID
1         10
2         10
3         10
3         20
3         30
4         10
4         20
4         40
5         10
5         20
6         10
6         60
7         10
7         50


texts: combining multiple rows with arbitrary number of columns

texts:
I am attempting to format a query but, I don't think I know all the required methods to do what I want currently I have these results

Source
------
item    |PackQty|TotalUsage |JobDescription|job_usage
STE0295 |5      |20         |41721-2       |1
STE0295 |5      |20         |41925-1       |1
STE0295 |5      |20         |41949-1       |1
STE0295 |5      |20         |41997-1       |1

and I want to combine all item keys into a single row like this

STE0295 |5      |20         |41721-2       |1   |41925-1    |1  |41949-1    |1  |41997-1    |1

These results are not the entire set; there are thousand of unique 'item' records and they appear in arbitrary number of rows based on the different job Description. I have used pivots before and also the stuff for xml method but, I feel like I am missing some concepts to achieve this format. Here is the query so far

    select  tv.Item,
       --tv.ItemDescription,
       tv.PackQty,
       TotalUsage = (
                        SELECT sum(tvss.Qty * -1)
                        FROM transactions_VIEW as tvss
                        WHERE tvss.Item = tv.Item
                          AND TranDateTime between '2019-04-01 00:00:00.000' and '2019-04-30 11:59:59.000'
                          AND tvss.Supplier IN ('###', '###', '###')
                          AND tvss.TranType IN ('I','B')
                    ),
       tv.JobDescription,
    count(*) as job_usage
from transactions_VIEW as tv
         join Job as j on tv.JobNumber = j.MyNo
WHERE j.Active = 1
  AND TranDateTime between '2019-04-01 00:00:00.000' and '2019-04-30 11:59:59.000'
  AND tv.Supplier IN ('###', '###','###')
  AND tv.TranType IN ('I', 'B')
group by tv.Item, tv.JobDescription, tv.ItemDescription, Qty, PackQty

I have looked around but, I don't know the name of the technique to use so I am floundering. Any pointers in the right directions would be awesome.


texts: In Memory OLTP causing OOM Issue in SQL Server even though no data in In Memory OLTP Table

texts:
We have only 1 memory optimized table in each database and there are total 10 databases as such. This table is completely empty. But still we can see in DBCC MEMORYSTATUS Output that In memory OLTP is consuming 7.68 GB out of 13 GB that has been assigned to SQL Server.
MEMORYCLERK_XTP (node 0)                         KB

VM Reserved                                       0
VM Committed                                      0
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                             8055696 -- 7.68 GB

Sometimes CHECKPOINT is also not running because of OOM situation.
Other than restarting SQL Service, is there any other solution to resolve this issue?
SQL Server Version: Microsoft SQL Server 2016 (SP2-CU12) (KB4536648) - 13.0.5698.0 (X64)   Feb 15 2020 01:47:30   Copyright (c) Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3  (Build 9600: ) (Hypervisor)


texts: Break very large records in a table into smaller records

texts:
SQL Server 2016 on Azure - let's say we have a table with 4 columns: ID (Primary Key/Identity), Source, Url, Text.  This table has approximately 20 million records. Approximately 20% of these records contain a Text field with data that is greater than 10MB.  We want to, on a daily basis, scan the table and break up any record with a Text field greater than 10MB into smaller records, each with a maximum of 10MB in size.  So for example, if a record has a Text field that is 28MB in size, it would get broken into 3 records - 2 each of 10MB and one of 8MB.  The resulting records would have incrementing ID values (as is the default on table inserts), and the Source and URL fields would be identical across the 3 records.

How would one begin to approach this?  Can this be achieved by writing a query as a Stored Procedure and setting it to run on a schedule, or is something that should be written as an application?

EDIT 1: per user's question below, it's valuable to add why we need to do this: The reason for doing this is that we push our data through an extractor process to parse the text and extract relevant information. The largest amount of data the extractor can take in a given call is 10MB.


texts: Why MySQL init_connect variable gets updated after rebooting?

texts:
After SET GLOBAL init_connect=''; query everything works fine. However after rebooting the server or restarting the MySQL service it updates the init_connect value to some random value (OÇÿOÇÖ). Do I need to set init_connect to '' after every reboot or is there any solution for that?

Thanks in advance,

`


texts: How can I store data with unpredictable data types in a table?

texts:
I'm currently working on a Headless CMS project and was wondering what approach would be the best for storing "fields" data in the database.

Basically, users would be able to create a content model with fields of various field types including Media, Text, Long Text, Date and Time (Very similar to contentful.com's content model feature). I have designed a database schema and need validation before integrating it into a live database.


texts:
Can I check whether a global read lock is in place?
My application uses MySQL (actually MariaDB) on InnoDB. I'm going to run mysqldump --all-databases, which will impose a global read lock for an hour. I'd like my application to check for the existence of this lock, but it doesn't appear to show up in anything I've tried (unless a request which is waiting on the lock already exists):

SHOW ENGINE INNODB STATUS
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS
SHOW FULL PROCESSLIST


texts: inserting data in temp table with read only database access

texts:
I only have read only access to my company's database (no chance of getting write) and want to import Excel data to a temp table, is this possible?

I can create temp tables tied to just my computer, but can't find a way other than inserting, is there a quicker way?

create table #tmp
(col1 int, col2 int)

insert into #tmp (col1, col2)
values(1,2)

I may be doing something wrong, but I don't have a local database that I can save tables to. I'm using the client version if that helps, I've been told I would need to pay for a license for write capability.

I'm trying to create a temp table because I only have read access to 1 server/database, is there another way to save? I'm looking to create a table from an Excel file and am having trouble using the data wizard because of my read only access.  Do I need write access to import any external data, even if being loaded to a temp table?

I have an Excel file that is going to be continuously updated that I want to run QC checks on and would much rather do it in SQL Server than Excel. I am the only one that needs to use it.

So first question is: Do I have my own database and just can't find it? Have tried local, the computer name, searching networks within SQL settings. When I try to import a file I have to connect to a database to do so and the only one I have is the read only.


texts: How to grant more memory to run R processes in SQL Server 2016 R Services

texts:
I'm running a fresh install of SQL Server 2016 Standard Edition (13.0.5026.0) with R Services on Windows Server 2016 Standard.  With all sorts of happiness I went to run an R script that completes within 15 mins on my lame desktop machine (8 GB RAM, 1 Intel proc @3.4 GHz) and found that it takes over two hours to run on my carefully configured server.

I'm wondering what else I need to do to get my R processes using more memory?  My script spawns 12 R processes, which sit there using up to 460MB each even though there is plenty available and I've told them to use more.  On my desktop in R Studio this same script happily grabs all available memory.

Here's the relevant details on my setup:

72 GB RAM
2 Intel Xeon procs @ 2.0GHz with 6 cores each
SQL Server max memory set to 32 GB (I've also had it at 57.6GB, no difference noted)
Though I don't think it matters since I don't have Enterprise Edition w/Resource Governor, I've set the internal and external pools to use 40 and 60 max memory percent, respectively.
I have my MAX DOP set at 8 per MS's recommendations, but I've also tried disabling parallel processing by setting this to 1 as I've heard that under certain conditions R's parallel processing can complete with SQL server's.  No change in memory usage by R was seen, but certainly my CPU usage decreased significantly.
I've also added a line to the RLauncher config file (E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\rlauncher) to use MEMORY_LIMIT_PERCENT=60.
I've confirmed that the SQL Server Service and SQL Server Launchpad services have the correct user rights in the local security policy to replace a process level token, manage memory, etc.
Yes, I can successfully run basic R scripts.
Yes, I've restarted the server after making my configuration changes.
I've ensured that the server is running in high performance mode and not power saving mode in both the BIOS and in Windows.
Ensured no errors in the event logs that point to an issue.
Ensured that the worker accounts MSSQLSERVER01-20 all have permissions to log on locally and are part of the SQLRUserGroup.


texts: Prevent adding a new record if multiple columns have the same value

texts:
I have a table:

id | name | date  | prod| val | tar |  op   | status | action
1    A      date_1   1    null  null   null    null       c
2    B      date_2   1      2   null   null    true       u
3    C      date_3   2      3     1    null    false      c
4.   D      date_4   2      3     1     1      false      c

Is there any way to prevent adding a new record if:

multiple columns have the same value

Do not add a record if:

status of the record in the database is not true and
if action, prod, val,tar, op values for the new record are the same  with a record that exist already in the database

The idea is:

status by default is null; if another operation is executed and fail is false, if is execute and done is true
if a (operation)record exist in db (define by prod| val | tar |  op  |action) was not executed or fails(status null, false) and the new (operation)record is the same don't add the new record

For example, if I try:

shouldn't be added because the action, prod, val,tar, op  are the same
and status is null

name | date  | prod| val | tar |  op   | status | action
  E   date_5    1   null  null  null     null     c

should be added because action is different compared to 1

name | date  | prod| val | tar |  op   | status | action
  Z   date_6    1   null  null  null     null     u

should be added because status of the record in database(2) is true

name | date  | prod| val | tar |  op    | status | action
F     date_6    1      2   null   null   null      u

should be added because the action of the record in db(4) is different

name | date  | prod| val | tar |  op    | status | action
H     date_6    2     3   1      1        null      u

shouldn't be added because the status of the record in db(4) is false

name | date  | prod| val | tar |  op    | status | action
H     date_6    2     3   1      1        null      c


texts:
I have 2 Tables.

Bank (Original Table):

ID
Bank_Code
Bank_Transaction
Delete

Bank_History:

ID
Bank_ID
CodeH
TransactionH

I need for the Bank table to copy into Bank_History using triggers. Here is what I have so far:

CREATE TRIGGER `Bank_after_update` AFTER UPDATE ON `Bank`
 FOR EACH ROW BEGIN

    IF NEW.Deleted THEN
        SET @Change_Type = 'DELETE';
    ELSE
        SET @Change_Type = 'EDIT';
END IF;
INSERT INTO Bank_History (bank_id, Change_type) VALUES (NEW.id, @Change_Type);
END

It creates a new record but it doesn't transfer all the info.


texts: Automatically detect table name in MSSQL Server database using stored function

texts:
I'm working on implementing Change Data Tracking in a SQL Server database and I set up a single table to test changes.  In the past, changes have been made manually, but I'd like to create a way to track updates and deletions automatically through the built-in CDC process. In many cases, these changes are spread across dozens of different tables, so the ideal situation would be to create a view that collocates all of the changed data.

CDC works at the table level and doesn't track the user who makes changes. I found a way to add user data to the CDC tracking tables by creating a new user_name column and adding SUSER_SNAME() to the "Default Value or Binding" column property using the SQL command: "ALTER TABLE cdc.dbo_MyTable_CT ADD UserName nvarchar(50) NULL DEFAULT(SUSER_SNAME())" from a separate Stack Overflow question - (https://stackoverflow.com/questions/869300/sql-server-2008-change-data-capture-who-made-the-change).

I'm trying to find a similar stored function to track the table name from this list - https://msdn.microsoft.com/en-us/library/ms187812.aspx.

So far the only stored function I've successfully implemented from the list is db_name() which automatically returns the database name.  I've tried schema_name and object_name, but those automatically revert to N'schema_name()' or N'object_name()' in the design view.  In the table view, the changed rows return (schema_name()) or (object_name()).

Does anyone know how to automatically populate the a column with the table name of the table from which changes were made using some sort of stored function?


texts:
I have to select all duplicates by PrimaryKey

Here is my DB

ID   UserID   GroupID
1    1        1
2    1        1
3    1        2
4    2        2

If I pass ID=1 need to select row 1 and 2

Here is my searched id:

DECLARE @id INT = 1;

Here is my SQL Script 1:

SELECT *
FROM table as t1
WHERE EXISTS  (
      SELECT 1
      FROM table as t2
      WHERE
           t2.ID=@id AND
           t2.UserID = t1.UserID AND
           t2.GroupID = t1.GroupID
)

Here is my SQL Script 2:

SELECT t1.*
FROM table as t1 INNER JOIN
(
    SELECT *
    FROM table as t3
    WHERE t3.ID = @id
) as t2 ON t2.UserID = t1.UserID AND t2.GroupID = t2.GroupID

With this Selects the result is ok, but I'm not sure is the correct way and who is the best for the performance

Please give me some advice. Thanks.


texts: Extracting Service name from stored procedure

texts:
I have the following procedure

USE ServiceBrokerTest;
GO
CREATE PROCEDURE PublishNotification
    @NotificationData VARCHAR(1000)
AS
    DECLARE @InitDlgHandle UNIQUEIDENTIFIER
    BEGIN TRAN
    --Determine the Initiator Service, Target Service and the Contract
    BEGIN DIALOG @InitDlgHandle
    FROM SERVICE [//SBTest/NotificationsService]
    TO SERVICE'//SBTest/NotificationsService'
    ON CONTRACT [//SBTest/NotificationContract]
    WITH ENCRYPTION=OFF;
    --Send the Message
    SEND ON CONVERSATION @InitDlgHandle
    MESSAGE TYPE [//SBTest/NotificationMessage](@NotificationData);
    COMMIT TRAN GO

Which I run with

EXECUTE PublishNotification N'<root><first type="string">lorem</first><second type="integer">0</second></root>';

Now I'd like to extract also the service to use (in the code above //SBTest/NotificationsService) since I have multiple services. Note All services use the same NotificationContract and NotificationMessage.

How can I do that?


texts: Create a table from a CSV file with headers

texts:
I'm seeking to find a way to generate a new MySQL table solely based on the contents of a specified CSV. The CSV files I'll be using have the following properties;

"|" delimited.
First row specifies the column names (headers), also "|" delimited.
Column names & order are not fixed.
The number of columns is not fixed.
Files are of a large size (1 mil rows / 50 columns).

In Excel this is all rather simple, however with MySQL it does not appear to be (no luck with Google). Any suggestions on what I should be looking at?


texts:
I need to change the Microsoft SQL Server 2014 license key (just key - not versions). I have found that it is possible via SQL Server Installation Wizard:

http://www.alexandreviot.net/2015/11/10/sql-server-2014-how-to-change-the-product-key/
http://www.sqlcoffee.com/SQLServer2012_0015.htm

Do I need to stop SQL services for that? Or will some downtime be needed?
I think that when upgrade is clicked, SQL services will be restarted probably, right?


texts: How to search id from one table in another table's nvarchar column

texts:
I have 2 tables

table1 ( id int, name nvarchar)
table2 ( pid int, parameters nvarchar)

parameters in table2 contains table1.id in nvarchar format, like
"some text 12345 some text", where 12345 is the id which is contained in table1.
Now I need to find all the table1.id s which are mentionioned in table2.parameters

So for my script is this, but this does not work

select id, name
from table1 r
where exists
(
select [Parameters] from table2
where [Parameters] like cast(r.id as nvarchar(15))
)

I can't use % with like but I need it. What is the way I can do it?


texts:
I have the following data in rows:

1
2
3
6
7
15
16

I want to write a SELECT statement to return contiguous ranges. Given the sample data above, the output would be:

1,2,3
6,7
15,16


texts: SSIS | unable to complete login process due to delay in opening server connection

texts:
I have deployed 2 SSIS package on a server. both fetches data from an Azure SQL Server. Both Packages have one execute SQL task and one for each loop container.
for package one, execute SQL task returns an object which has 2 columns and in case of package two, it returns an object which has 4 columns.

The problem is i am getting the error "unable to complete login process due to delay in opening server connection" for package 2 but not for package 1. Both packages are running fine from my local.


texts: How would I index a text field that requires explicit characters and wildcards on both ends (postgres)?

texts:
I would like to search for a tracking number while only having partial of the value.

I have a tracking number "1Z1234abcde" in my table.

I want to be able to:
select * from tracking_numbers where value ILIKE '%abc%';

I currently have this as a 'text' field type in my table but I am not able to index it correctly so I'm doing a Seq Scan when I use those wildcards.

                                             Table "public.tracking_numbers"
          Column          |            Type             | Collation | Nullable |                   Default
--------------------------+-----------------------------+-----------+----------+----------------------------------------------
 id                       | integer                     |           | not null | nextval('tracking_numbers_id_seq'::regclass)
 value                    | character varying           |           |          |

Full text search and trigram do not solve my problem because I need the characters to be exact, not variations. (ie. an exact tracking_number)


texts:
As soon as I start SQL Server with no application querying, in the task manager it starts to consume memory very fast till it consumes around 15GB.
How can I resolve this issue?


texts: Granting select on syscat.schemata - DB2

texts:
I was wondering if it is safe to grant select rights to syscat.schemata to users on a DB2 database? What could go "wrong"?
Some tool like DataGrip and DBeaver cannot load connection schemas because of this missing rights.
Others tools like AQT or SquirrelSQL load schemas that they see with not problem.
I am not really at home in DB2 environment.


texts: Which aspects of PostgreSQL `UPDATE` do impact IOPS?

texts:
Premises: there is a big table (hundreds of millions of rows), a new column is added to the table and needs to be populated with values. UPDATE by chunks (e.g. id ranges of different sizes) causes huge IOPS spikes.

There are few techniques like creating a new table with CREATE AS and later renaming,
and TRUNCATE with usage of a tmp table. Though some concerns on these approaches raise:

table-wise lock (no write ops possible) and mandatory recreation of constraints;
some writes could get lost appearing in the gap between CREATE TEMP TABLE and TRUNCATE, also stale data could get written from TEMP TABLE;

If to proceed with updating by chunks (e.g. UPDATE WHERE id >= x1 AND id < x2), which aspects of the operation do cause IOPS spikes? Like:
- index updates;
- VACUUM;
- UPDATE is actually DELETE/INSERT. Is it?

Which are the best way to alleviate IOPS spike for that case?

Are there any other possible strategies to add a column value for big tables?


texts: PostgreSQL SELECTs not returning correct result following recovery?

texts:
I took a copy of the current database from a live slave using pg_basebackup and recovered it to a brand new isolated computer following the instructions in the docs. Once the new database had started up, I could see everything was present and had been restored OK, but some simple SELECT queries were failing (returning 0 results), specifically:

SELECT * FROM users WHERE email_address='me@example.com';

However, some email addresses did work. The fix for it was a REINDEX on the email address index. Now, the manual says in the caveats section that hash indexes aren't transferred across in the WAL logs, so any slaves won't get the updates and also following recovery, a REINDEX should be done, but my index is a B-Tree and nothing is mentioned about that.

What is happening?
I don't see this on the live slave (connected to master in streaming replication) at the minute, will I in the future?

Systems:

Live M/S: CentOS 6.6, with PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit

Isolated Machine: OS X 10.1, PostgreSQL 9.3.5 on x86_64-apple-darwin13.3.0, compiled by Apple LLVM version 5.1 (clang-503.0.40) (based on LLVM 3.4svn), 64-bit

CentOS:

production=# explain analyse verbose select user_id from hive.users where email_address='me@example.com';

QUERY PLAN
----------------------------------------------
 Index Scan using users_email_address_key on hive.users  (cost=0.14..8.16 rows=1 width=4) (actual time=0.044..0.044 rows=1 loops=1)
   Output: user_id
   Index Cond: (users.email_address = 'me@example.com'::text)
 Total runtime: 0.093 ms
(4 rows)

production=# select user_id from hive.users where email_address='me@example.com';
 user_id
---------
       1
(1 row)

Mac OS X:

production=> explain analyse verbose select user_id from hive.users where email_address='me@example.com';

QUERY PLAN
----------------------------------------------
 Index Scan using users_email_address_key on hive.users  (cost=0.14..8.16 rows=1 width=4) (actual time=0.014..0.014 rows=0 loops=1)
   Output: user_id
   Index Cond: (users.email_address = 'me@example.com'::text)
 Total runtime: 0.031 ms
(4 rows)

production=> select user_id from hive.users where email_address='me@example.com';
 user_id
---------
(0 rows)

following the re-index:

production=# reindex index hive.users_email_address_key;
production=# explain analyse verbose select user_id from hive.users where email_address='me@example.com';
QUERY PLAN
------------------
 Seq Scan on hive.users  (cost=0.00..1.41 rows=1 width=4) (actual time=0.019..0.019 rows=0 loops=1)
   Output: user_id
   Filter: (users.email_address = 'me@example.com'::text)
   Rows Removed by Filter: 33
 Total runtime: 0.045 ms
(5 rows)

production=# select user_id from hive.users where email_address='me@example.com';
 user_id
---------
       1
(1 row)