texts: SQL Server 2012 CPU spike due to LinkedServer - how to find the offending query?

texts:
As part of another question (SQL Server 2012 performance issues (maybe due to linked server)) I've identified a CPU spike to be directly related to a spike in Bytes Received, which leads me to believe it is likely due to the link server.

THE ENVIRONMENT

2 servers, one server accesses a database on the first via a linked server, for nice diagram, please see SQL Server 2012 performance issues (maybe due to linked server)

CPU spike directly related to Bytes Received Spike

QUESTION
How to find which queries could be causing it??

TYPES OF QUERIES WE RUN

I've went through all our stored procedures, and they are optimized. Here are the types of linked server access that we do:

select one value from remote table

select @userid = userid from FBGC.FBGCommon.dbo.players where playerid = @playerid

execute stored procedure that returns result set of max few hundred rows

exec FBGC.Fbgcommon.dbo.qPlayersFriends @PlayerID

execute stored procedure that returns one value

exec @HasEnoughCredits = FBGC.FBGCommon.dbo.qDoesPlayerHaveEnoughCredits @PlayerID, @Cost

delete a few rows from remove table

delete from FBGC.FBGCommon.dbo.InactivePlayersToBeWarned where PlayerID = @AccountOwnerPlayerID

Update one row in remote table, or add one row to remote table

update FBGC.FBGCommon.dbo.users set XP = XP + @XPForTitle where userid = @userid
insert into FBGC.FBGCommon.dbo.UserXPHistory (userid, time, XPReceived, XPFromTypeID, XPFromRealmID) values (@UserID, GETDATE(), @XPForTitle, 1, @ThisRealmID)

I believe all of these are optimized, yet, it appears we are joining tables over the linked server!

Some interesting wait stats that I do not know how to interpret.
Ran a query from http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

I get this DURING the CPU spike:

I get this when there is no CPU spike:

These wait times are on the server with DB2, the SQL 2012 server which accesses a remote database on another server running sql server 2008 (for diagram, see SQL Server 2012 performance issues (maybe due to linked server)

Restarting sql server help - no CPU spike for significant amount of time.

We've also found that, restarting SQL Server helps - we could be getting intermittent spikes for hours, many times an hour, then after restart, no spikes for an hour or more..

Process explorer

As suggested by @EdwardDortland, I used Process explorer to see what thread is consuming most CPU during the slow down

unfortunately, no single thread shows up as the main culprit - during CPU spike, simply all threads consume more CPU. although I am seeing a lot more threads highlighted in red (meaning deleted) during the CPU spike - not sure how to interpret this.

Threads before the CPU spike

Threads during the CPU spike


texts:
I am requiring my students to keep a 3 day food log. I want to use that data to display on their personalize web dashboard and will be bringing that info in as a JSON. The primary data I'll display is the date, food they ate, calories from fat, protein, carbs and total calories.

What would be the best way to structure my MYSQL database? Should I have one row in the same table with all the data? In the example below, the student drank chocolate milk. Would I have one row with fields for student ID, name of food, fat, protein, carbs and date?  They may drink chocolate milk more than once per day so that would be a lot of duplicate information in each row but I'm not sure how to better structure this?

Below is a sample of the JSON I'll be inserting into my table(s):

{
    "foods":[
        {
            "isFavorite":true,
            "logDate":"2011-06-29",
            "logId":1820,
            "loggedFood":{
                "accessLevel":"PUBLIC",
                "amount":132.57,
                "brand":"",
                "calories":752,
                "foodId":18828,
                "mealTypeId":4,
                "locale":"en_US",
                "name":"Chocolate, Milk",
                "unit":{
                    "id":147,
                    "name":"gram",
                    "plural":"grams"
                },
                "units":[226,180,147,389]
            },
            "nutritionalValues":{
                "calories":752,
                "carbs":66.5,
                "fat":49,
                "fiber":0.5,
                "protein":12.5,
                "sodium":186
            }
        }
    ],
    "summary":{
        "calories":752,
        "carbs":66.5,
        "fat":49,
        "fiber":0.5,
        "protein":12.5,
        "sodium":186,
        "water":0
    },
    "goals":{
        "calories": 2286
    }
}

Thanks in advance,

Tim


texts:
I know the title may seem confusing but I have yet to find a solid answer to this design pattern. Let's say I want to create an Employee table and have multiple addresses for each employee. So this would call for your standard one-to-many relationship. You obviously want and Address Type column to track what type of addresses you are storing for each employee.

Now for my issue, I want to only allow one address per address type for each employee. This breaks the one-to-many model because I want to force this restriction at the database level. I see a few ways of dealing with this issue but I am stuck on which one is best based on best practices and functionality.

Below are the same tables with the AddressId removed, for lack of usefulness, and instead a compound primary key of EmployeeId and AddressTypeId. This would enforce one address type per employee, allowing each employee to have multiple addresses but only one of each type.

For the purposes of enforcing key restraints on the address table, this would probably work fine. But this seems more like a one-to-one/none relationship which could cause me issues with ORM that don't work with this kind of table design. (Correct me if I am wrong please.)

Below is what I know to be the typical bridge table (many-to-many-to-many) relationship. It, like the above table, deals with the address type restriction I want to enforce while also keeping each table well structured and with its own singular primary key. This design will work fine with most ORMs and is probably the correct way to go but it seems like overkill and it seems to break the meaning of the many-to-many relationship as there will never be an address with multiple employees.

Is the bridge table relationship design the best to be using despite its complexity? Other than the issues that might arise with an ORM would the compound key relationship cause issues I am not seeing? Or am I totally insane and seeing this all the wrong way and there is actually a better way to be dealing with this scenario?


texts: Is there a systematic way to force PostgreSQL to load a specific table into memory, or at least read it from disk so that it will be cached by the system?


texts: How to profile trigger database performance impact

texts:
I want to set up some triggers on a DB2 box (LUW, 9.7).  The DBAs are concerned that if the trigger fires too often, it will adversely affect general performance.

What's a reasonable cheap approach to sizing up performance impact?  By "cheap", I mean I don't want to set up complicated monitoring/profiling tools; my impression is that this database was set up with a minimum of logging tools and so forth, and I don't want to change that.  I'm thinking more in terms of things like watching for page faults and also things that can be seen with db2top.

The environment:

It's an enterprise-level analytics database.  Data is fed into it via replication (Golden Gate, now owned by Oracle).  The triggers we're creating track updates.  (Upon update, a small record containing the primary key is stored in another table.  It's not a great way to do things; we're trying to ship data somewhere else, and right now we're highly constrained and can't use a technically advanced and thorough solution like replication.  An alternative is adding a column to record updates, but we've been told not to do that, though that might change.)  One reason I'm leery of just adding triggers willy-nilly is I'm concerned I might break the replication process.  My current idea is to add a trigger that performs a simple hash on the primary key (which is a BIGINT) and record updates only for a fraction of the rows, so I can get an idea of how often updates occur.  Anyway, that's why I'm asking about simple, reasonable metrics to see how triggers affect e.g. inserts into a particular table.


texts:
SQL Server 2016 Enterprise, CU15 + GDR.
Client recently started using OPCON in production and I noticed that when OPCON creates jobs, it doesn't add the Local Server as the target. This prevents the job from executing.

Is there anyway to determine if a specific job doesn't have any target server defined as is the case above?
Thanks,
Craig


texts: Postgresql Log message without its statement

texts:
If I want to write a message to the Postgres log I can do this:

DO $$
BEGIN
  RAISE LOG 'Justin Is Awesome';
END $$;

However, it logs the statement as well so I get 5 lines of output instead of one:

2016-07-12 16:38:13 UTC LOG:  Justin Is Awesome
2016-07-12 16:38:13 UTC STATEMENT:  DO $$
  BEGIN
    RAISE LOG 'Justin Is Awesome';
  END $$

I can reduce it to two lines with the following:

2016-07-12 16:36:04 UTC LOG:  Justin Is Awesome
2016-07-12 16:36:04 UTC STATEMENT:  DO $$ BEGIN RAISE LOG 'Justin Is Awesome'; END $$

However is it possible to reduce it down to one? Is there a way to send a line of text to the Postgres log without postgres logging the statement that generated the log?


texts: Does PG somehow support the ability to set a column to "variable type"?

texts:
I sometimes get into the situation where I wish to store "arbitrary fields" with their value. Rather than creating a table structure with tons of columns, and perpetually having to change this table as more "fields" are added, I have a table with basically just a "field" and "value" column, where both are of text type, meaning it can "store any value", albeit in a less-than-optimal manner in terms of speed and "deep knowledge" by the database engine about the data in the table.
For example, it's unclear if a boolean value sent from the application will be stored as a "0"/"1" or literal "true" or "false". It just "feels icky", although I don't consider having actual columns for every value to be a sensible alternative to this approach, which I consider to be even more problematic.
Since this is probably a very common situation, it struck me that PG might support a "variable" column type of some sort, so that it can dynamically become a "boolean" or "text" or "date" or "integer" depending on the value inserted/updated for the "field" column for a specific row? Or perhaps based on what I specify with ::whatever?
Is this a thing? Is it stupid? I can work with my current system of "text columns that hold anything", but I always have the nagging feeling that "this isn't quite the optimal or intended way to do things".


texts: Which foreign keys do I use if they are repeated

texts:
I have the following two tables

company_relationships

|ID|Company_ID|Affiliate_ID|<Relationship Info>|
+--+----------+------------+-------------------+

jobs

|ID|Company_ID|<Job Info>|
+--+----------+----------+

and I need to keep a record of job offers (i.e. a company offers a job to its affiliate). Do I use:
job_offers

|ID|Job_ID|CompanyRelationship_ID|<Offer Info>|
+--+------+----------------------+------------+

or

|ID|Job_ID|Affiliate_ID|<Offer Info>|
+--+------+------------+------------+

or something else?

I want to use the first one, because relationship info applies to the job offer, but then Company_ID is specified by both Job_ID and CompanyRelationship_ID. Is there an issue with this / better schema to use?


texts:
I'm quite new to SSIS.

Is it a straightforward to grant SSIS permissions in the AD environment so that that a user can only create/manage/run ETL tasks on specific databases/Analysis Services projects on a SQL Server that contains multiple databases?

I.e. So a user can't extract/load data from/into a database/Analysis services project that they shouldn't etc.

Database version is SQL Server 2012.

Thanks!


texts: What steps do I need to take to move my SQL instance to a different drive on the same server but with the same drive letter?

texts:
I need to move my SQL instance to a different drive on the same server but plan to rename the drive to the same drive letter as the original drive the instance was on (after renaming the old drive).

I'm guess I need to stop the SQL service before moving the files, and then start it again.

Are there any other steps needed or precautions I should take?

Note: This instance has databases that are part of synchronous AlwaysOn AGs. Some of the databases are the primary replicas while others are secondary replicas. The primary replica databases are also being moved from the same source and to the same destination drive (as the server instance files).


texts: After new row INSERT statement, use newly created PK as FK in a second INSERT statement within the same Stored Procedure

texts:
Using SQLExpress --

I have a table Orders that contains a PK of order_id. I have a second table Items that contains model and quantity along with a FK of order_id.

I am using a gridview to collect model/quantity data for an order and then I want to take that gridview data row by row and insert a new row into Orders, then insert it into the Items table, using the same order_id for each row of the Gridview. Problem is that the order_id doesn't exist until the first Insert is done.

After researching I had hoped I'd found a solution but it is not working as expected. I don't think the title of my question makes sense but I have no idea how to word it. Thanks in advance!

UPDATE: I have tried the edits suggested in the comments and referenced the link provided but still have not gotten my answer.

Below is my code:

Gridview Code:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" HeaderStyle-HorizontalAlign="Center" HeaderText="Assigned" HorizontalAlign="Center" Width="346px">
<Columns>
    <asp:BoundField DataField="Manuf" HeaderText="Manuf" />
    <asp:BoundField DataField="Model" HeaderText="Model" />
    <asp:BoundField DataField="Qty" HeaderText="Qty" />
    <asp:BoundField DataField="Notes" HeaderText="Notes" />
</Columns>
</asp:GridView>

Code behind to call Stored Procedure:

 protected void CreateOrder_Click(object sender, EventArgs e)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["HMTDBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                foreach (GridViewRow g1 in GridView1.Rows)
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = con;
                    cmd.CommandText = "sp_ProdDataInsert";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@customer_id", TextBox1.Text);
                    cmd.Parameters.AddWithValue("@manuf_name", g1.Cells[0].Text);
                    cmd.Parameters.AddWithValue("@model_name", g1.Cells[1].Text);
                    cmd.Parameters.AddWithValue("@qty", g1.Cells[2].Text);
                    cmd.Parameters.AddWithValue("@notes", g1.Cells[3].Text);
                    cmd.Parameters.AddWithValue("@track_num", TextBox2.Text);
                    cmd.Parameters.AddWithValue("@pckg_num", TextBox3.Text);

                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();

                    MessageBox.Show("New Order Created!");
                    Response.Redirect("NewOrder.aspx");
                }
            }
        }

Stored Procedure Code:

ALTER PROCEDURE [dbo].[sp_ProdDataInsert]

@customer_id INT,
@manuf_name NVARCHAR (50),
@model_name NVARCHAR (50),
@qty NUMERIC,
@notes NVARCHAR (255),
@track_num NVARCHAR (50),
@pckg_num NUMERIC,
@order_id int OUTPUT AS

BEGIN
INSERT INTO Orders
    (customer_id, track_num, pckg_num)
VALUES
    (@customer_id, @track_num, @pckg_num)

SELECT @order_id = coalesce(MAX(order_id),0)+1 FROM Orders (UPDLOCK)
INSERT INTO Items
    (manuf, model, qty, notes, order_id)
VALUES
    (@manuf_name, @model_name, @qty, @notes, @order_id)

END


texts: PostgreSQL 8.4: How to tell if a procedural language is installed or not?

texts:
I have a setup program that requires plpgsql to install stored procedures in a PostgreSQL 8.4 database. I need to make sure the language is installed or the app will fail. I don't want to drop the language and re-add it, as that could screw some other things up.

Is there a way to install the language "gently"?
CREATE LANGUAGE IF NOT EXISTS does not appear to be valid.


texts: MySQL 5.7 + Ubuntu 14.04 eating up my RAM

texts:
I've recently installed MySQL 5.7.11 in my Ubuntu 14.04.3 system following these instructions: http://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/

Being totally empty and not being called by any other program, it always ramps up RAM usage in a couple of hours until I can't start Node apps because of low memory (ENOMEM). I first assumed it to be because I'm running on a 768MB VPS, so I tweaked my.cnf, using some very low parameters, but it kept behaving like that, eating up more than 600MB of RAM after a couple of hours.

Please notice that it's really taking up all that RAM, it's not some buffer/cache voodoo, because it stops other programs from starting. Or am I wrong here?

Just uninstalled everything and reinstalled MySQL 5.6 and this problem isn't happening. Can anyone lend me a hand here? Am I doing something wrong or is this some kind of bug?

Thanks!


texts: Is Service Pack 2 completely installed on Passive Node of SQL Server 2012 Instance?

texts:
We are applying SQL Server 2012 Service Pack 2 (SP2) to a two-node active/passive cluster, starting with the passive node (as per best practices):

How to install a Service Pack at a SQL Server 2012 Failover Instance:
Best Practices
(http://blogs.msdn.com/b/john_daskalakis/archive/2014/09/05/how-to-install-a-service-pack-at-a-sql-server-2012-failover-instance-best-practices.aspx)

Applying SP2 to the passive node produced a result indicating four installation failures (as per FIGURE 1).

FIGURE 1

When we begin to re-apply SP2, we can see that the Build Number is correct and the Upgrade Status is "Incompletely Installed" (as per FIGURE 2).

FIGURE 2

AND SO THE QUESTION:
Will the passive node remain in an Incompletely Installed upgrade status until the active node is failed over to the passive (making the passive node ACTIVE)?


texts: Delete files from disk, with a job, respecting some rules ( SQL Server 2008 )

texts:
We have An application that our customers use to download and upload files.

I need to delete these files from disk after 30 days, of if it was downloded, in 7 days.

How can i Use the script below, in a powershell, to do this task?

select ArquivoServidor from MyTable
  where DATEDIFF(day,LogDownload,GETDATE())>= 7
    or DATEDIFF(day,LogUpload,GETDATE())>= 30

( i'm not in the server where files are )

Thank you very much.

Edit1:

I can use this ( After a lot of tests, it's working ):

    create table #temp
    ( ArquivoServidor varchar (max))
go
insert into #temp
select 'xp_cmdshell '+ ''''+'del '+ArquivoServidor+''''+';' from MyTable
  --set Arquivo = NULL
  where DATEDIFF(day,LogDownload,GETDATE())>= 7
    or DATEDIFF(day,LogUpload,GETDATE())>= 30

    select * from #temp where ArquivoServidor is not null
        drop table #temp

it will show me something like this:

  xp_cmdshell 'del \remote_server\folder\folder\file.txt'

I'm thinking in something like execute sql, But Im having problems with files with spaces in name ( jhon vacation.mp3 ) for example.

Any Ideas?

Edit2:

Query done, now I need to fix the Spaces in files name.

declare @sql varchar(max)
set @sql =

'select ''exec xp_cmdshell ''
    + ''''
    +''del ''
    +ArquivoServidor
    +''''
    +'';''
    + CHAR(13)
    + CHAR(10)
from Mytable
  --set Arquivo = NULL
where
    ArquivoServidor is not null
        and DATEDIFF(day,LogDownload,GETDATE())>= 7
    or DATEDIFF(day,LogUpload,GETDATE())>= 30
        and  ArquivoServidor is not null'''

$Computer = Read-Host "Input Computer Name (remote PC)"
$Path = Read-Host "Path of temp folder"
If((Test-Path $Path) -eq $True) {
    Invoke-Command -ComputerName $Computer -ScriptBlock {
        $File_Counts = (Get-ChildItem -Path $Path | Measure-Object).Count
        $FPath = $Path + "\*.*"
        Remove-Item -Path $FPath }
    $OutPut_File = "RemoteDeletes_log.txt"
    $File_Counts.ToString() + " files have been deleted from this $Path folder on remote $Computer PC" >> $OutPut_File
    Invoke-Item $OutPut_File
} else {
    Write-Host "Folder isn't exist, please input correct path"}

It doesn't follow the rules that I need.


texts: PostgreSQL import XML to empty database, automatically create columns

texts:
The server is Linux but the development machine is Windows so I'm using psql command line. I tried \i C:/Users/John/Desktop/Parts.xml which did not work so either \i doesn't work with XML or I'm missing some syntax.

I have an empty database (and the user has permissions as far as i can tell). How do I import XML and have it's columns automatically created based on the column defined in the XML file? The XML file is an export of a Microsoft Access 2010 database table.


texts:
I'm encountering issues checking ownership and accessibility of entities in my application.

Here is a simplified summary of my problem. Consider the following diagram (I don't have a precise DDL, this is a simplified example to illustrate my issue: access to an entity efficiently in my app depending on the properties of its parents [of parents ...]):

A writer can write many books, add chapters to it, then attach notes
to chapters (a book may have co-authors, but not at the the moment).
He is the owner of the book thus no other writer can update it, same
for its belonging chapters and notes.
He can archive his books, this imply can he no more edit books chapter
and notes.

I have an API that performs CRUD operations on books, chapters, and notes.

Foreach each operation I have to verify if the current user is the owner of that ressource and if the book it belongs to is not archived.

I'm using an ORM and currently on a "edit notes" api call, I populate objects by making a select that join the chapter then the book, then the owner.
Then i can perform the ownership check and the archiving check.

Is it wrong doing big queries with chaining join to prevent
subsequent database calls ?  I feel it makes these calls more and
more complex and heavy as the application grows.
Should I make more simplest request for each ressource?
Should I use another database design to make it easier/faster
checking ownership and parents (of parents ...) properties (e.g redundancy, something else...)?


texts: Connecting to local Oracle Server via MacOS SQL Developer

texts:
I am new to Oracle SQL, but I want to give it a shot and play around with the HR schema.
I don't want to use a VM, because I had a lot of issues with them.
Setup:
Oracle Server setup on WIN64 PC. HR and SYS works fine. (10.0.0.10 --> to understand later on)
Trying to connect via MacOS and SQL Developer (10.0.0.23).
On my Mac I am able to ping 10.0.0.10 and the other way around (10.0.0.10 (PC) to 10.0.0.23 (MAC)).
Ping from Mac to Pc
So then I setup my listener (listener.ora) and my tnsnames.ora in the way oracle wants me to do (examples folder).
Listener Setup
tnsnames setup
After this setup I rebooted the server.
Now I tried to connect as HR to my 10.0.0.10 machine.
(On my win pc I can connect and use HR and sys schema.)
connectionHR
After trying to connect, a "Testing connection" window opens and closes after 1 minute.
Status: No success: I/O-Error: The Network Adapter could not establish the connection

Ive also tried to import the tnsnames.ora file and start from this file, but this wouldn't work either.
EDIT:
I updated my listener.
Anmeldung bei (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS des LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 19.0.0.0.0 - Production
Startdatum                27-DEZ-2020 00:11:42
Uptime                    0 Tage 0 Std. 3 Min. 14 Sek.
Traceebene               off
Sicherheit                ON: Local OS Authentication
SNMP                      OFF
Parameterdatei des Listener C:\Users\Ben\Desktop\db_home\network\admin\listener.ora
Logdatei des Listener    D:\benOracle\diag\tnslsnr\DESKTOP-FT8E4S1\listener\alert\log.xml
Zusammenfassung Listening-Endpunkte...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=DESKTOP-FT8E4S1)(PORT=5500))(Security=(my_wallet_directory=D:\BENORACLE\admin\orcl\xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services ▄bersicht...
Dienst "52448234712340b69f274bcc790ecfe0" hat 1 Instanzen.
  Instanz "orcl", Status READY, hat 1 Handler f³r diesen Dienst...
Dienst "CLRExtProc" hat 1 Instanzen.
  Instanz "CLRExtProc", Status UNKNOWN, hat 1 Handler f³r diesen Dienst...
Dienst "cfc353defce0494a8a440e7d1f817c2d" hat 1 Instanzen.
  Instanz "orcl", Status READY, hat 1 Handler f³r diesen Dienst...
Dienst "orcl" hat 1 Instanzen.
  Instanz "orcl", Status READY, hat 1 Handler f³r diesen Dienst...
Dienst "orclXDB" hat 1 Instanzen.
  Instanz "orcl", Status READY, hat 1 Handler f³r diesen Dienst...
Dienst "orclpdb" hat 1 Instanzen.
  Instanz "orcl", Status READY, hat 1 Handler f³r diesen Dienst...
Der Befehl wurde erfolgreich ausgef³hrt.

Host= Localhost
Why is host still the local ?
--> This is in German. Comment if translation is needed.

All connections on the port 1521:


texts: Getting "Read Timed Out" exception frequently for last few days on my already running SQL Server 2008 R2

texts:
Our client has a setup of 2 VMs . One of them has SQLServer 2008 R2 and other has a Java utility which I am using to update the Database..

It was working some time back, but recently it started giving "Read Timed Out". It gives this error at different point of time after I run the utility so I dont think it is query time out.

IT team of the client do not recall anything changed recently to the server.
There is also no firewall as per them.

I have checked the even logs and there are bunch of following errors :

  2014-05-27 11:21:39.82 Logon       Error: 18456, Severity: 14, State:
  38.
  2014-05-27 11:21:39.82 Logon       Login failed for user 'xxxx'. Reason: Failed to open the explicitly specified database. [CLIENT:
  192.168.32.38]

however I checked that the utility is not using the username specified in the above error. It is instead using default 'sa' credential.

And more importantly the above error did not trigger when I ran the utility, but I think it could be something related to SQL Server performance so pasted it.

I checked that "AUTO_CLOSE" is false for the DB. Utility at the end gives the error "Connection Already Closed" as I try to close the connection in the finally block.

I am using

validationQuery = select 1 as dbcp_connection_test
testOnBorrow = true
maxIdle = 40

I get connected to the DB in the starting part of the utility, but get the below error anywhere between 5 seconds to 90 seconds.

I have tried using IP instead of hostname but no difference.

There is one more application connecting to another DB on same server, and that is working fine, so I think it should be something specific to this DB/Connection .

I also checked in my utility logs, the connection closing code is not executed until I get the below error, so I think after I connect to SQLServer somehow connection is getting killed/dropped from the server side.

Here is the error:

  [java] 06-Jun-2014 10:08:32
  com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement close
       [java] WARNING: SQLServerPreparedStatementID:15965 ( ConnectionID:6 TransactionID:0x0600000040000000): Error (ignored)
  closing PreparedHandle:0
       [java] com.microsoft.sqlserver.jdbc.SQLServerException: Read timed out
       [java]   at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(Unknown
  Source)
       [java]   at com.microsoft.sqlserver.jdbc.TDSChannel.read(Unknown Source)
       [java]   at com.microsoft.sqlserver.jdbc.TDSReader.readPacket(Unknown Source)
       [java]   at com.microsoft.sqlserver.jdbc.TDSReader.readPacket(Unknown Source)
       [java]   at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(Unknown Source)
       [java]   at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(Unknown Source)
       [java]   at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$1PreparedHandleClose.doExecute(Unknown
  Source)
       [java]   at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
       [java]   at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown
  Source)
       [java]   at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown
  Source)
       [java]   at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.closePreparedHandle(Unknown
  Source)
       [java]   at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.close(Unknown
  Source)
       [java]   at org.apache.commons.dbcp.DelegatingStatement.close(DelegatingStatement.java:152)


texts: How to generate current Oracle timestamp in perl?

texts:
I use DBI. And I want to make some like

INSERT INTO ... VALUES (the_generated_timestamp, ...);

What is the command? How could it be created, the perl ``ocaltimescalar value has a very complex, for human eyes optimized format (f.e.Mon Apr 28 15:58:51 2014). My goal were some like as the unixgettimeofday()` does (giving back the seconds since 1970.1.1), converted by some format string, which I can let eat by the Oracle.

But any simpler solution were also okay, if it exists.

It need to be generated in perl. INSERT ... (current_timestamp, ...) isn't okay.


texts: How can I drop a view only if it exists in Oracle?

texts: I would like to perform a DROP VIEW IF EXISTS... command on an Oracle DB as it can be done on a MySQL database. How can this be achieved?


texts:
Can someone explain to me what is the difference between collation sql_slovak_cp1250_ci_as vs Slovak_CI_AS?

I have a db set to sql_slovak_cp1250_ci_as and shared hosting supports only Slovak_CI_AS. Should not be a problem?

Thank you very much.


texts: Limit the number of results but show how much would be returned

texts:
I have a big problem in T-SQL with large amount of data where I need to limit number of returned rows to 200K but also to show the number of rows that would be returned. It is needed to be done under 10 min.

At the end for the result I am joining 3 tables which has 10'sK, 100'sK, or millions of rows in various combinations, depending on given parameters. I am using just several rows from each table and need the exact number of distinct rows that are returned.

When I place data into a temp table, writing it to disk takes too long (hours), so I am now trying to run the query twice. First time just for counting and second time for returning the result set without writing it to a temp table. This is because if I have more than 200K rows, than it can take too long otherwise.

I am wondering if COUNT ( DISTINCT [field 1] + ... + [field n] ) is going to take too much resources. How can I make this run faster:

DECLARE @count INT = ( SELECT COUNT( DISTINCT [field 1] + ... + [field n] )
    FROM ( #tempDC dc WITH (NOLOCK)
            JOIN #tempUID u WITH (NOLOCK) ON dc.[hashUFI] = u.[hashUFI]
                AND dc.iId IN ( '', u.iId ) )
        LEFT JOIN #rLog ON u.[hashUFI] = #rLog.[hashUFI]
            AND #rlog.iId IN ( '',  u.iId )
            AND #rLog.docId = dc.docId
    WHERE dc.rangeStart <= dc.rangeEnd AND u.rangeStart <= u.rangeEnd
        AND u.rangeStart <= dc.rangeEnd AND u.rangeEnd >= dc.rangeStart
)

I have indexes on all the hashUFI fields and also on all the pairs (rangeStart, rangeEnd).

This is just a problematic snippet of a much larger procedure (around 2000 lines of code), which is taking data from some audit tables.


texts:
I used spAskBrent for troubleshooting a SQL Server 2005 performance issue.

I found the problem was someone had ran DBCC FREEPROCCACHE. It caused low performance (very low PLE) on my database and queries to time out.

How can I find out who ran the query to free the plan cache?


texts: Preferred node for read on Availability Group in a multisite configuration

texts:
We are going to set up a multi (at least 2) site configuration, and we want to achieve both HA as well as a good resource usage.

The sites will be interconnected through a high speed, dedicated fiber network, so latency will generally be very low.

However we still prefer that each site uses the nearest secondary server for read intent data.

Is this possible?


texts: Mysql 8 ST_GeomFromText giving error Latitude out of range in function st_geomfromtext. It must be within [-90.000000, 90.000000]

texts:
I am trying the following insert query on mysql 8.

Insert Into fence Set
            fenceName='aa',
            radius=2,
            fenceGeometry=ST_GeomFromText('POINT(102.1893310546875 3.880696482497261)', 4326)

It works perfectly on mysql 5.7 but here in mysql 8 I get this error Latitude 102.189331 is out of range in function st_geomfromtext. It must be within [-90.000000, 90.000000].

Based on some suggestion I also did this.

ALTER TABLE fence MODIFY fenceGeometry geometry NOT NULL SRID 4326;

But yet the it gives me the same results.


texts:
I'm reading about Oracles PDB and CDB, and their test performances

http://www.oracle.com/technetwork/database/multitenant/learn-more/oraclemultitenantt5-8-final-2185108.pdf

They compare PDB with non-CDB's.
But I dont understand what a non-CDB is. Here is the definition from the document

"multiple stacked single-instance databases (non-CDBs) on the same operating system"

Is non-CDB one single Database process with several databases or
Is non-CDB several Database processes?


texts: Adding a geographically dispersed third node to an AAG cluster

texts:
We have a 2 node Always setup in our data center at DC and I have been tasked to setup a third node in this cluster in San Jose. What are the considerations I need to take to make this happen? I read the below given document which provides some insights. Is this document sufficient to consider as a reference?

http://download.microsoft.com/download/D/2/0/D20E1C5F-72EA-4505-9F26-FEF9550EFD44/SQLServer2012_MultisiteFailoverCluster%20(2).docx

My major questions would be where do I place the quorum (probably a disk and file share witness) . And if Asynchronous commit is the only option available because of the geographical distance is it viable to just a have a manual failover option?


texts: Old ODBC app throws "cannot generate sspi context" on Windows 10

texts:
An old Access 2003 .adp project is currently running on Windows 7 machines, while SQL Server 2008 is on the backend.
Since the app is moved to a Windows 10 machine, the app will trigger "Cannot generate SSPI context" error.

There are a lot of messages over the Internet to explain possible reasons and solutions.

In our case, we may overcome the problem running Msaccess.exe from commandline, using:

runas /netonly /user:userdomain\useralias "C:\Program Files\<path-to-office-folder>Msaccess.exe"

The downside is that the user is prompted for password each time the shortcut is being used.

EDIT:

The weird fact is that the context under which the program is running is apparently the same as the Windows 10 user has the right credentials to access domain resources (can access server side networks shares correctly), and also has credentials within SQL server configuration. As a proof, under Windows 7, the same credentials are being used without errors.

It looks like the app in Windows 10 is incorrectly passing computername\useralias credentials to SQL Server in place of userdomain\useralias.....

Is there a way to fix this so that the app (or Windows 10) are addressing the domain user name correctly, avoiding the error?


texts: Fetch the newest row grouped by a column in MySQL

texts:
My problem seems like it should have a much simpler solution than what I have come up with. Starting with this data set:

log_table

+--------+-----------+------------------+---------+
| log_id | entity_id |       date       | comment |
+--------+-----------+------------------+---------+
|      1 | A         | 2012-10-23 07:50 | foo     |
|      2 | B         | 2012-10-23 07:59 | bar     |
|      3 | B         | 2012-10-23 08:11 | baz     |
|      4 | A         | 2012-10-23 08:23 | bat     |
+--------+-----------+------------------+---------+

Say I wanted to get the latest date of log entries for each entity so that the result looked like:

Results:
+-----------+------------------+--------------+
| entity_id |  last_log_date   | last_comment |
+-----------+------------------+--------------+
| B         | 2012-10-23 08:11 | baz          |
| A         | 2012-10-23 08:23 | bat          |
+-----------+------------------+--------------+

I'm currently using MySQL that looks something like:

SELECT
  `entity_id`,
  `date` AS last_log_date,
  `comment` AS last_comment
FROM (
  SELECT *
  FROM `log_table`
  ORDER BY `date` DESC, log_id ASC
) AS `ordered_log`
GROUP BY `entity_id`

This works fine but it doesn't seem very efficient to me, there has to be a better way of doing this, surely?


texts: T-SQL - OPTION (FAST x) and trace flag 8722

texts:
I have searched a long time and still haven't found the answer to my problem.

Our Dynamics AX is generating queries with the query hint OPTION (FAST x), which sometimes forces a bad execution plan to be used.
The devs say it is by default and is hard to change (has to be fixed on all forms potentially).

So I was after a way to override these hints using trace flags. I found the lovely trace flag 8722 which is claimed to make SQL Server ignore some query hints, especially the ones in the OPTION clause.

However, this doesn't work in my case. I tried to enable trace flag 8602 (disable index hints) as well, but my query is still running with the FAST x hint (it is much slower than when I actually remove the OPTION clause).

I tried to clear the plan cache as well, to no avail.

Any ideas? Am I missing anything?

P.S. I enabled the trace flags globally, this is SQL Server 2012 Developer edition


texts: Restoring .trn files on a regular basis to a backed-up database

texts:
We have been given a .bak file of a database from our vendor on a completely different network. They are placing their .trn files every 15 minutes on a sftp for us to pickup and write to the .bak file they sent us. This way we will have data up-to-date every 15 minutes or so.

What I have done is restored their backup WITH NORECOVERY. Then I restored all 800 .trn files WITH NORECOVERY, setting the last restore to WITH RECOVERY. Now that it is out of NO RECOVERY we can read from the DB.

Now that we are up-to-date, what is the process for restoring the new .trn files that they send us?

I apologize for the ignorance, I am new to the DBA game.


texts: MySQL column defined with NOT NULL is allowing nulls

texts:
I had created a table a while back and started adding data to it. Recently I added a new column (address) to it with NOT NULL as part of the new column. The old rows (pre-addition) are still null, which created a warning as part of the definition. However, new rows with the new column are still being allowed to insert nulls.

Is the new column's pre-addition nulls the source of them being allowed? If so, is there a way to tell MySQL to not allow it even though it was before?

mysql> show create table my_table\G
*************************** 1. row ***************************
       Table: my_table
Create Table: CREATE TABLE `my_table` (
  `entry_id` int(11) NOT NULL auto_increment,
  `address` varchar(512) NOT NULL,
  `follow_up_to` int(11) default NULL,
  PRIMARY KEY  (`entry_id`),
  KEY `follow_up_to` (`follow_up_to`),
  CONSTRAINT `my_table_ibfk_1`
     FOREIGN KEY (`follow_up_to`)
     REFERENCES `my_table` (`entry_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=535 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>


texts:
I have a trigger that needs to create an Oracle Advanced queue message. The payload of that message should contain the new record that was created, that is accessible via :new.

What I am looking for is a way to transform the :new object into raw or a custom data type that can be set as the payload of the queue message, in a generic way.

At the moment I'm trying to convert :new to anydata and set that as the payload data type.


texts: How to quickly cascade rare delete to a large table?

texts:
I have a schema that looks like this:

create table items (
    id serial primary key
);

create table revisions (
    id serial primary key,
    item_id int not null references items(id),
    name text not null,
    property_a text,
    property_b text,
    property_c text
);

create table deltas (
  id serial primary key,
  item_id int not null references items(id) on delete cascade,
  old_revision_id int not null references revisions(id) on delete cascade,
  new_revision_id int not null references revisions(id) on delete cascade
);

This schema can't be changed.

We need to support the rare operation of deleting from items or revisions with the delete cascading to the deltas table. These deletes rarely happen compared to inserting/selecting data from the deltas table (but at least ~20-30 times a day).

A major problem is that the deltas table is very big so these cascades were very slow (taking > 30 seconds). So, we added separate indexes to item_id, old_revision_id, and new_revision_id which turned the cascading delete into a sub-millisecond operation. Side note: on our actual table, there are other columns and other indexes to support our application's normal query patterns.

This introduced a new problem: the combined size of these indexes for the deltas table is now ~7-8 times the size of the actual table and the rate of growth is pretty high since the deltas table has a lot of writes to it.

It seems silly to use many large indexes to support such a rare operation. We have a couple of options to solve this:

Leave it with multiple indexes and deal with the disk size as a separate problem.
Re-architect the application to avoid doing any deletes for these tables to prevent the cascades from happening in the first place.
Change our indexes.

For #3, I read in the Postgres docs that we might be able to use a multi-column GIN index on (item_id, old_revision_id, new_revision_id) which could support the cascading delete like delete from changeset_deltas where old_revision_id = <deleted_revision_id>:

  A multicolumn GIN index can be used with query conditions that involve any subset of the index's columns. Unlike B-tree or GiST, index search effectiveness is the same regardless of which index column(s) the query conditions use.

Am I correct in understanding this and will it make a difference rather than 3 separate indexes or should we take a different approach?


texts:
I'm having an issue setting up my current environment using AlwaysOn and SSB and I'll try to explain as best as I can here:

Current environment:

Server A             Server B
database x(Pub)      database x (Sub)
                     database y

Our app runs on database x. We have transactional replication setup to make database x available on Server B.
Database y is used to receive data from another app. We use it to process this data and just send the necessary information to database x. The replica of database x on Server B helps us to validate all the information and make sure we won't send any invalid data.

To communicate between database y and database x we use SSB. Why? It's faster.

We are moving to AlwaysOn and we'd like to maintain the current setup and add the failover cluster technology along with it.

So we come to the problem: AlwaysOn does not let us choose what we want to have on the replica. So it has everything, including the SSB objects (Services, Queues, etc.)
When we set up the databases and try to communicate database y and x, we get this error on transmission_queue:

  Database x is read-only mode

So SSB is using the local services we have on the replica of database x in Server B instead of looking at our routes that tell him to go to database x in Server A.

One alternative we found is to create a new instance for database y. I don't like the approach and from what I've read we also lose the automated failover from AlwaysOn with two instances and we'd have to do it manually.

My personal guess is that SSB Always uses the local services and I have no way out of this. One thing I have considered is removing the AutoCreatedLocal route from Server B and see if that Works (still have to try) but if it does, I'm not sure how I'll make the setup when it does the failover. I'll probably have to have a script to run and create it back.

Appreciate any help on this.

Editing to add more information:
Followed the link provided by Remus ( Service Broker with AlwaysOn ) to configure SSB with AlwaysOn. Basically just changed the route to use the AG instead of the specific server.

Still having the same error: database in read-only mode.


texts: Every administrators, developers have their own collection of scripts (some patterns). Time to time they are dismissed from one job and they are employed to other one. Is there any good, easy used, indexed tool to collect the common used sql scripts (structured folder/file system as a default)?


texts: NVMe+bcache as huge storage cache for vertica database

texts:
We are in process of selecting hardware for 3 machines vertica cluster capable to serve 20-30TB of data (15TB compressed). This translates to 8TB of post-RAID storage. With 16 CPU cores, the storage should yield 800MB/s of "large block random I/O" throughput.

Cost of the storage becomes pretty high, once we count in drives, RAID overhead and server chassis necessary to hold all the drives. 10k RPM drives are relatively small (SAS 10k RPM tops at 1.2TB max) and we need many of them to provide the total storage required. SSD are four times more expensive with SATA/SAS becoming the bottleneck.

I have been thinking about "hybrid" solution of using super-fast NVMe storage (2GB/s thoughput) as cache for a much more slower HDD backend (couple of 10TB SATA drives in RAID1). This can be accomplished by using Bcache technology of linux kernel.

The assumptions are:

most reporting queries to vertica cover only "hot" data area of hour/day/week worth of data. Bcache would cache the frequently used data onto NVMe.
as time goes, "hot" data area shifts on the backend disks surface and Bcache automatically adjusts cached blocks to represent it.
write queries (data import) of vertica will be much slower than reads and that would not block any read operations (write-through mode). This is ok, as we do data import in bulk and don't need it to be immediate.

Back of envelope calculations show that cost of such system to be in range of $2000 as opposed to $8000 fully SSD-based solution (per server).

Can this approach work for vertica (or any other analytics database)? Have anyone used the "fast cache in front of slow backend" approach for databases?


texts: Designing a SQL database for a Tire Purchase System

texts:
Ok so I don't have code for this as I am unsure of how it would even go. Take this scenario I am working on, a purchase system for, let's say, tires.

I have one table named tStock and another one for customers named as cOrder.

tStock has these columns:

(PK) Tyre_ID
(VARCHAR) Manufacturer
(int) Quantity

while cOrder has:

(PK) Customer_ID
(varchar) Cust_name
(int) Purchase_quantity

What I hope to achieve is that when I have a form that takes a customer's details and orders of how many tires (s)he wants min of 1 tire max of 4 tires that it will then subtract this amount from the database and update it.

Would I be right in thinking that I would need to have customer_ID and possibly purchase_quantity in as a  foreign key constraint for it to update?

Also would it need to be joined in someway i.e outer, equijoin, natural?

Any suggestions is greatly appreciated


texts: I upgraded my MySQL version from 5.5 to 5.7 and in phpmyadmin I get this error message. I tried to reinstall and reconfigure it but I have still this problem. Can you help me? I am using Linux Mint. Thank you


texts: Count values in column on join then group by

texts:
Forgive me if this is super easy. I am doing some data analysis on the Stack Overflow database model. I need to count the number of badges a user has. They can have multiple of the same badge name.

For example:

A user can have 3 x Teacher badges, and 2 x Editor badges, etc.

I would like to be able to see it in the below format:

 UserName | Badge_Name | Amount
 ------------------------------
 JamesBond| Teacher    | 3
 JamesBond| Editor     | 2
 Arthur1  | Teacher    | 5

So far all I am able to retrieve is the display name of the user and the amount of badges they hold:

SELECT t2.DisplayName, COUNT(t1.Id) AS TotalNumberOfBadges
FROM Badges t1
JOIN Users t2 ON t2.Id = t1.UserId
GROUP BY t2.DisplayName
ORDER BY t2.Id;


texts: Declaratively select an entity requiring relations from all (dynamic number of) related entities

texts:
I am trying to construct a where clause that requires an X number of children to have a relation to a specific parent with a N-to-N relationship. Something similar to "Get all receipts where these products were sold together."
An example scenario is easier to explain:

DECLARE @Nodes TABLE(
  Id INT
);

DECLARE @Arc TABLE(
  Id INT IDENTITY(1,1),
  Source INT, -- FK @Nodes.Id
  Dest INT    -- FK @Nodes.Id
);

INSERT INTO @Nodes (Id) VALUES (1),(2),(3),(4);

INSERT INTO @Arc (Source, Dest) VALUES
   (1, 2)
  ,(1, 3)
  ,(1, 3)
  ,(2, 1)
  ,(2, 3)
  ,(2, 4);

In the SP, the input is preprocessed into a table like this:

DECLARE @InputConnectedNodes TABLE ( Id INT );
INSERT INTO @InputConnectedNodes (Id) VALUES (3),(4); -- not a fixed number of nodes

In this example we want all nodes that have an arc to nodes 3 and 4 (only node 2 in the above example).
Currently we have this:

SELECT DISTINCT Source
    FROM @Arc
    WHERE Dest IN (SELECT Id FROM @InputConnectedNodes);

Then we have some ugly procedural code that loops through the input and (nested) output checking for all relationships. I'd like to replace it with something declarative. The best I came up with so far is this:

SELECT Source
    FROM @Arc
    WHERE Dest IN (SELECT Id FROM @InputConnectedNodes)
    GROUP BY Source
    HAVING COUNT(Source) = (SELECT COUNT(*) FROM @InputConnectedNodes)

But it will fail when nodes have multiple (identical) arcs like (1->3) in my example.

I could also solve this by dynamically generating a where clause, but I'd prefer a fully declarative solution.


texts: Distributed - across multiple databases or servers - Transactions

texts:
I have some doubts related to transactions in SQL. I perform below steps.

Connection 1 open for DB1 database
Begin transaction for Connection 1
Run the commands on Connection 1
Connection 2 open for DB2 database
Begin transaction for Connection 2
Run the commands on Connection 2
Commit transaction for Connection 2
Commit transaction for Connection 1

If  commit transaction on Connection 1 fails(rollback) then transaction on connection 2 should rollback.
Is it possible? How it can be done?


texts: How to change an incorrect datatype for a birth date field from VARCHAR to DATE

texts:
I made a mistake when creating my users table with a birthdate field and instead of putting the DATE datatype, I put a VARCHAR!
So now my users table looks like this:
CREATE TABLE IF NOT EXISTS users
(
  id INT    UNSIGNED NOT NULL,
  birthdate VARCHAR (200) NOT NULL,

  PRIMARY KEY (id)
) DEFAULT CHARSET=utf8;

and it is populated as follows (sample):
INSERT INTO users (id, birthdate) VALUES
  (1,'1991-01-23'),
  (2,'yyyy-01-23'),
  (3,'1991-mm-23'),
  (4,'1991-01-dd'),
  (5,''),
  (6,'1991-01-d3'),
  (7,'1983-05-23'),
  (8,'1991-0m-23'),
  (9,'19yy-01-23'),
  (10,'y991-01-23');

Now I want to update every incorrect birthdate to NULL, or set a default value like 2020-01-01. see my sqlfiddle here.


texts:
Looking for a way to let selected users have case-sensitive logon check on Oracle 12.1. Setting SEC_CASE_SENSITIVE_LOGON = TRUE enables all users to have case sensitive logon, but trying to limit that to some.

Defining a profile having a verify function which checks for at least one upper and lower letter seems to only check when changing the password, but not upon logon.


texts: Which major database platforms support TTL?

texts:
I need to store some data in a database that will be hosted on Amazon Web Services, and I have complete freedom over choosing which database platform to install, based on what I think will best suit the particular application.

One thing to note, however, is that AWS has the following four databases essentially "built-in" to their RDS service:

Oracle
MySQL
PostgreSQL
SQL Server

But on top of that, it is also possible to spin up an EC2 instance and install anything else; the only trouble there is then I have to install and maintain the database myself whereas some of that work is farmed out if I go with one of the options above.

The particular application I'm working on will store a lot of data that, for all intents and purposes, looks like logs / audit history. And there's a lot of it. Each day, we might insert a few 100K rows into this table. In general, we'll only be operating on the data that has been inserted that same day, though we would want to keep data around for at least a week or two.

But after that point, we'll need to essentially "expire" (aka delete) the old data so that we don't run out of disk space. So I'm looking for a platform where it's relatively easy to "expire" data.

I saw that MongoDB has built-in support for this on a row level by setting an index with the expireAfterSeconds option. But I'm not sure about any of the SQL options I've listed above, or any others really.

Do any of the above SQL databases have any mechanism for automatically pruning old data, or treating a table as a "rolling log" with a cap to the file size or row count? Or do you have any other recommendations?

I know that I could simply write this on the application level itself, by having an indexed "created date" field and running a DELETE query at midnight. But I have concerns about the transaction log getting exponentially bigger as a result of doing things this way.


texts: What is the 131072 in the call to lo_open()

texts:
ByteA to BLOB conversions typically make a call to lo_open(),

CREATE OR REPLACE FUNCTION make_lo(bytea)
RETURNS oid AS $$
DECLARE
  loid oid;
  fd integer;
  bytes integer;
BEGIN
  loid := lo_creat(-1);
  fd := lo_open(loid, 131072); -- <<< --==HERE==--
  bytes := lowrite(fd, $1);
  IF (bytes != LENGTH($1)) THEN
    RAISE EXCEPTION 'Not all data copied to blob';
  END IF;
  PERFORM lo_close(fd);
  RETURN loid;
END;
$$ LANGUAGE plpgsql STRICT;

In the above what is

fd := lo_open(loid, 131072);

You can see others asking this same question here,

  The number 131072 is some flag I couldn't find what means, and 999999999 is the max read size, but I could read the value anyway.


texts: How to Clean Sensitive Data from SQL Server Dump Files

texts:
How do I clean a SQL Server dump file of confidential/PII/PHI information so I can prepare it for third party analysis? What am I looking for in the dump files and are there any automated tools to obfuscate things? I have looked on Microsoft and other sites but the information is focused on how to diagnose SQL dumps rather than what is actually contained within.

In the most recent release of SSMS they have the "SQL Server Diagnostics (Preview)" (https://blogs.msdn.microsoft.com/sql_server_team/sql-server-diagnostics-preview/) which offers to "Analyze Dumps - Customers using this extension will be able to debug and self-resolve memory dump issues".

I'd like to utilize this new tool but confidentiality is paramount, hence the question.


texts:
Say I have a large table that holds the user's info and another table that holds several locations. Then I use another table that holds the user_id and the location_id.

In order to retrieve the data I have to use Left Join query. Doesn't that make the whole process longer to retrieve rather than having it all in one table? Eg I could have the location as text on the same table.

EDIT: Here is an example.

CREATE TABLE  `user` (
`id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `gender` enum('M','F') DEFAULT NULL
);

CREATE TABLE `user_location` (
  `user_id` int(11) NOT NULL,
  `location_id` int(11) NOT NULL
);

CREATE TABLE `location` (
`id` int(11) NOT NULL,
  `location` varchar(45),
  `parent_id` varchar(45)
);

Note: Please assume that  all related fields are properly indexed between them.

Edit: I currently have a large database with users that retrieve their location via a junction table as described above. I was asked to optimize the database because the search results are slow. I've added memcache and it improved significantly but now I am just wondering about Left Joins.

For example, The current query is something like that:

SELECT * FROM users
LEFT JOIN user_location
ON user_location.user_id = user.id
LEFT JOIN location
ON location.id = user_location.location_id;

And that is just to get the location. They have several other fields that are retrieved through junctions and they are all needed to view a user's profile. We have phone numbers, addresses, passwords, D.O.B and many others all in different tables.

In order for me to create a page for the user profile I have to send the server a large query. Now after the first time it gets cached and it's fine. But I was just wondering why would someone build their database like that?


texts: Tweaking indexes for tables with "geographical" data

texts:
We have quite a large table in our database which looks something like this:

CREATE TABLE `nests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pokemon_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `latitude` varchar(64) NOT NULL,
  `longitude` varchar(64) NOT NULL,
  `is_cluster` int(11) NOT NULL,
  `is_repeater` int(11) NOT NULL,
  `status` int(11) DEFAULT NULL,
  `spawn_type` int(11) DEFAULT NULL,
  `current_species` int(11) DEFAULT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `current_species` (`current_species`),
  KEY `spawn_type` (`spawn_type`),
  KEY `status` (`status`),
  KEY `latitude` (`latitude`),
  KEY `pokemon_id` (`pokemon_id`)
) ENGINE=InnoDB AUTO_INCREMENT=76214 DEFAULT CHARSET=latin1;

This table basically stores user submitted data and is polled aggressively to find results within a bounding box. Here's an example of a few of the queries which run against this table.

SELECT `id`,
       `current_species`,
       `status`,
       `spawn_type`,
       `latitude`,
       `longitude`
FROM `nests`
WHERE latitude <= 50.953560763747
  AND latitude >= 50.898161073089
  AND longitude <= 7.0303058624268
  AND longitude >= 6.8912601470947
  AND `spawn_type` IN (1, 2, 3, 0)
  AND `status` IN (1, 2)
LIMIT 201;

The table, today, has 76,000 rows and when running an EXPLAIN for that query it seems to narrow by very little and keys off of status.

           id: 1
  select_type: SIMPLE
        table: nests
         type: range
possible_keys: spawn_type,status,latitude
          key: status
      key_len: 5
          ref: NULL
         rows: 54288
        Extra: Using index condition; Using where
1 row in set (0.00 sec)

We have a few ideas, but since rolling out this query we've had to create A LOT of MySQL read slaves because of how long it takes to run these queries.

The two things I've looked into so far are to create a new index (and remove a bunch more) where we take latitude, remove the decimal and flatten to the first 5 ints. So, a latitude of 50.953560763747 becomes 50953 and use this column to key off of.

The second thing I've started looking into is GEOSPATIAL POINT type and key off of that. However, I understand that would require us changing from InnoDB to MyISAM and I'm not sure if it'll help as I have very little experience in GEOSPATIAL column/data.

The final thought I had on this was to break out the lat/long data into it's own MyISAM table with an id and a POINT, then create a foreign key relation to the nests table. Thereby querying the geospatial data and inner join that onto nests for final filters.


texts: Postgresql server causing a lot of IO activity

texts:
I have a postgresql server running on a small linode partition. It is running a web app for a site which gets on average 0 users / minute (it has not launched yet). At one point, I loaded up the database with a lot of data, to do a stress test. I have since deleted the data and now have a few hundred megabyte DB just sitting there without anyone asking anything of it.

The linode server is reporting an average IO rate of 2.6k blocks a second. I ran pidstat -d 10 90 and figured out that the culprit was postgres:

Average:          PID   kB_rd/s   kB_wr/s kB_ccwr/s  Command
Average:        28836   1251.31      0.00      0.00  postgres

Further digging shows that approximately once every three minutes postgresql decides to do some heavy lifting (a short burst of reading at 21mb/s).

I know the bare minimum when it comes to DB administration and have an issue. My research unearthed things like VACUUMing and such. However, I am not sure what is happening, why, and how to stop it. There is absolutely no load on this machine. Can anyone point me in the right direction?


texts:
Updated 3x
I'm using FILESTREAM, but am having an issue with garbage collection.
The FILESTREAM files are not being cleaned up when I set the column to null.

In the event log, there is an enormous number of this error:
Internal FILESTREAM error: failed to access the garbage collection table.

It is error number 5571 on this page: http://msdn.microsoft.com/en-us/library/cc645602.aspx

Using this blog post I've confirmed that a 'tombstone' table exists, but I cannot query it (Invalid object name 'sys.filestream_tombstone_xxxxxxx'.)

I'm not deleting rows, I'm setting the FILESTREAM column to null, as per this msdn article: "When a FILESTREAM field is set to NULL, the BLOB data associated with the field is deleted."

The database is on a local drive.

The database is using Simple Recovery.

I have executed a number of CHECKPOINTs when activity was very low. This seems to have done nothing. The error message seems to indicate the FILESTREAM garbage collector is trying to garbage collect, but cannot access the table to find out what to do.

SQL Server 2008 (10.0.2531.0, SP1, Enterprise Edition

There are no other errors preceding the 5571 errors that I can see in the SQL Server Log or Event Log.

DBCC CHECKDB
There was an error detected. What do I do about this?
DBCC results for 'sys.filestream_tombstone_1819153526'.
Msg 8951, Level 16, State 1, Line 1
Table error: table 'sys.filestream_tombstone_1819153526' (ID 1819153526). Data row does not have a matching index row in the index 'FSTSNCIdx' (ID 2). Possible missing or invalid keys for the index row matching:
Msg 8955, Level 16, State 1, Line 1
Data row (4:4712151:3) identified by (oplsn_fseqno = 62856 and oplsn_bOffset = 82647 and oplsn_slotid = 2) with index values 'file_id = 65537 and rowset_guid = '3F972309-9B0B-4C4F-939A-5618897050B4' and column_guid = '4A143C0D-B877-494E-B1E6-B70B0A834BB6' and oplsn_fseqno = 62856 and oplsn_bOffset = 82647 and oplsn_slotid = 2'.
There are 209624 rows in 3382 pages for object "sys.filestream_tombstone_1819153526".

Questions:

How should permissions be set up for FILESTREAM to work? Other people have access to this server, and this database may have been restored from a backup at some point.

How do I check FILESTREAM (Windows) share/container account permissions and account under which SQL Server runs?

Thanks to @vgv8 for prompting for more info and ideas.


texts:
I have a table with a column of numbers that are consecutive but not concurrent. So 91 may be followed by 92 or it may be followed by 96, for example) with intervening numbers not in the column.

I want to search on this column for a particular number and then return the entire row if it is found. If the number is not found I want to return the row with the lower number.

So using the example above if I search for 95, which won't be found, I want to return the row with the number 91 in it.

I presume this must be possible but I just can't work out how to do it.


texts: Howto implement correctly Businness Logic in database

texts:
I'm searching about how to disable triggers, and seems like it's not possible under MariaDB.

My problem is, how to manage then, when you import data.
I have a database where almost all business logic is inside it with triggers and stored procs. Probably, there are records autogenerated by triggers and stored-procs that were filled later by a user, and I don't know how to manage this if I import a database dump, or importing a csv file for example.

I'm sure the problem is I'm not seeing the obvious, and there are some problem in my data model, but because I'm starting with this project, I'm in time to modify what is needed, knowing best practices to take into consideration.

My question is, there are some best-practices to take into consideration, when we want to implement the business logic of an application, inside the database?

I hope I explained correctly.

This is a little Example coming to my mind:
On a deployed database, there are a record that will be created from a trigger, and later, this record, is modified by the user. When import an sqldump, because this records exists, sql dump will fail trying to inserting this existing recods.


texts: MySQL: How do I limit or prevent caching for specific tables?

texts:
I'm currently working on a database schema for tables to hold user information on a website with integrated forums.

Right now my idea is to have all the data in two separate tables: one table with "essential" information stored with the InnoDB engine (for transactions and crash-recovery) while "nonessential" information is stored in a regular MyISAM table:

CREATE TABLE users_data (
    user_id        int unsigned NOT NULL AUTO_INCREMENT,
    username       varchar(24) UNIQUE NOT NULL,
    password       char(32) NOT NULL,
    name           varchar(64) NOT NULL DEFAULT '',
    gender         enum('M','F','U') NOT NULL DEFAULT 'U',
    birth_date     date NOT NULL,
    email          varchar(256) NOT NULL,
    member_group   enum('Banned','Member','Moderator','Admin'),
    join_date      date NOT NULL,
    avatar         varchar(40) NOT NULL DEFAULT '',
    views          int NOT NULL DEFAULT '0',
    posts          int NOT NULL DEFAULT '0',
    ip             int unsigned NOT NULL DEFAULT '0',
    last_seen      timestamp NOT NULL,
    PRIMARY KEY (user_id),
    UNIQUE KEY email (email),
    KEY     join_date_key (join_date, username),
    KEY     post_count_key (posts, username),
    KEY     last_seen (last_seen)
) ENGINE=InnoDB

CREATE TABLE users_profile (
    user_id        int unsigned NOT NULL,
    location       varchar(64),
    interests      varchar(500),
    website        varchar(128),
    msn            varchar(256),
    aim            varchar(256),
    facebook       varchar(50),
    signature      varchar(500) NOT NULL DEFAULT '',
    PRIMARY KEY (user_id)
)

With the tables separated like this, I get high concurrency on the InnoDB table, which is where information is most frequently going to be selected and inserted, while most of the arbitrary text that a user can store for their profile is in a separate MyISAM table. Also, because the MyISAM table does not require a row for every user, there is less wasted space from the allocation of space for the large varchar fields.

One of the factors that influenced this organization is that the data in users_profile is not accessed very frequently. My instinct is that it would then be helpful to disable the cache for this table so that more memory may be allocated for other purposes such as indices on MyISAM tables and other caches. If my conclusion is correct, how would I go about doing this?


texts: Database primary key Guid as clustered index alternative

texts:
I've red a couple of blogs/stackoverflow posts about not using a Guid as primary clustered key. Unfortunately we have at the moment and due to this we're experiencing performance degradation (with 900.000 rows in the database).

Our table looks like this:

| ID (PK)         | PARENTID (FK)    | CONTENT       | TYPE         | ROWVERSION |
| UNIQUEIDENIFIER | UNIQUEIDENTIFIER | NVARCHAR(MAX) | NVARCHAR(25) | rowversion |

In this table we're building up a tree (max 3 levels deep). The root has an empty ParentId, in other situation it's pointing to another Id (foreign key).

I want to refactor this table to the following, in order to prevent the UNIQUEIDENTIFIER to be a primary key:

| ID     | PARENTID | TECHID           | CONTENT       | TYPE         | ROWVERSION |
| BIGINT | BIGINT   | UNIQUEIDENTIFIER | NVARCHAR(MAX) | NVARCHAR(25) | rowversion |

I'm keeping the UNIQUEIDENTIFIER in the table because that's what we expose/communicate to the outside world.

The question I have specifically. Which column(s) should be handy for my primary key?

The TechId as (non clustered) primary key and a clustered index on ID.
The TechId as (non clustered) primary key and a clustered index on ID and ParentId.
The Id as clustered primary key and a non clustered key on the TechId.

Or any other suggestions?

Thanks in advance


texts: How to predict how much space a VACUUM FULL would reclaim?

texts:
Is there a way to determine in advance how much disk space a VACUUM FULL on a particular table would return to the OS? Therefore you can decide whether it's worth the cost in doing so.
Bonus if there's a simple query to do this for every table in a database/server (rather than doing each one separately).


texts:
I'm an accidental DBA with no scripting knowledge/background whatsoever. We have a bunch of different DBs (each client has their own) that have the same structure, but different data. That said, we regularly create functions that everyone needs to use. This means that I have to deploy these to each and every one of our 120+ DBs. How do I do that without 'manually' deploying it to every DB?

I have asked around about this before, but nothing was really helpful. The answers I got were 'use scripting' (not viable because I don't even know how to start), 'use RedGate's tool' (don't have the money for it and the company won't buy it), and 'don't use scalar UDFs to begin with' (completely unhelpful).

I've also been told 'just put it in master, or some other DB and call out to that'. That's a good thought, but soon our permissions/logins will change and I don't think that one DB user will be able to call out to another DB (HIPPA and all that).

Can anyone provide some guidance for a 'DBA' of my level?

Thanks

UPDATE: I have found that one can use sp_MSforeachdb for DDL Statements. Clearly, this is not best practice as sp_MSforeachdb is undocumented and not 'supported' by MS. As in, use at your own risk. It worked like a charm for me.
See example 2: https://www.mssqltips.com/sqlservertip/1414/run-same-command-on-all-sql-server-databases-without-cursors/

For those that do not want to go that route and are in a similar boat than me, use Johnathan Fite's PS Script provided in the answers section. That's a handy script to have, especially if you have more than one server/instance.

Thanks everyone!


texts: I want to fetch the data as below example using informatica Data Quality Tool(IDQ)

texts:
SOurce is similar as below :
I want Result like below :


texts:
Does anyone have a UML/ERD diagram for Power BI Report Server?
To clarify, is there a way to automatically generate the ERD of a given Power BI report server, or at least for a given report?


texts: How to measure Key Lookups per Second on SQL Server

texts:
I am working on reducing the amount of transactions doing key lookups on our SQL Servers.

First I want to define a baseline for the normal or tolerable number on that metric and as we constantly have releases in production, then, I want to create an automation based on it that notifies me when it goes south.

The problem that I am having is actually finding the right counter either on perfmon or a SQL DMV to get the data from.

I looked on the obvious object SQL Server, Access Methods Object yet, I could not find a specific one. I also tried other counters or SQL views but could not find it.

Any suggestions would be very appreciated!


texts: How to set IN values on an update (SQL Server)?

texts:
Quick question, I did try to google but couldn't find an answer.

I have 3 separate queries here, I want to do SET IN ('price1', 'price2', 'price3') but when I tried I got an error. How would I do this?

Update apotable
set price1 = '0.00'
where po_number IN ('x', 'y', 'z')

Update apotable
set price2 = '0.00'
where po_number IN ('x', 'y', 'z')

Update apotable
set price3 = '0.00'
where po_number IN ('x', 'y', 'z')


texts:
I need some help identifying what I did wrong.

I created two tables to track history from my main table. When I update a state from 0 to 1, the first trigger copies the data into history table 1.

But when I change the state from 1 to 2, the second trigger is not triggering, thus not copying the data into history table 2.

CREATE TABLE stateOneHistoryTable (
  historyDate DATETIME DEFAULT NULL,
  eventID int(11) NOT NULL,
  eventTIME decimal(15,0) NOT NULL,
  newestEventTime decimal(20,0) DEFAULT NULL,
  deviceIP varchar(20) NOT NULL,
  syslogMessage varchar(1000) NOT NULL,
  syslogState decimal(2,0) NOT NULL,
  eventCount decimal(5,0) NOT NULL
)

CREATE TABLE stateTwoHistoryTable (
  historyDate DATETIME DEFAULT NULL,
  eventID int(11) NOT NULL,
  eventTIME decimal(15,0) NOT NULL,
  newestEventTime decimal(20,0) DEFAULT NULL,
  deviceIP varchar(20) NOT NULL,
  syslogMessage varchar(1000) NOT NULL,
  syslogState decimal(2,0) NOT NULL,
  eventCount decimal(5,0) NOT NULL
)

Below are the triggers I created.

DELIMITER //

CREATE TRIGGER stateOneHistoryTableTrigger
AFTER UPDATE
ON syslog
FOR EACH ROW

BEGIN
    IF (old.syslogState=0 & new.syslogState=1)
        THEN
            INSERT INTO stateOneHistoryTable (historyDate, eventID, eventTIME, newestEventTime, deviceIP, syslogMessage, syslogState, eventCount)
            VALUES (utc_timestamp(), new.eventID, new.eventTIME, new.newestEventTime, new.deviceIP, new.syslogMessage, new.syslogState, new.eventCount);
    END IF;
END; //

DELIMITER ;

DELIMITER //

CREATE TRIGGER stateTwoHistoryTableTrigger
AFTER UPDATE
ON syslog
FOR EACH ROW

BEGIN
    IF (old.syslogState=1 & new.syslogState=2)
        THEN
            INSERT INTO stateTwoHistoryTable (historyDate, eventID, eventTIME, newestEventTime, deviceIP, syslogMessage, syslogState, eventCount)
            VALUES (utc_timestamp(), new.eventID, new.eventTIME, new.newestEventTime, new.deviceIP, new.syslogMessage, new.syslogState, new.eventCount);
    END IF;
END; //

DELIMITER ;


texts:
I have a SharePoint content database that has been restored into our test environment. I am getting errors in the SQL log indicating our SharePoint service account is not able to access the database.  SVC.Sharepoint exists as a login at the server instance level and is the db_owner for the database.  However, when I check sys.database_principals it doesn't appear in the list.  When I look at all of the articles and blogs regarding this I'm told it is an orphaned users problem.  But isn't an orphaned user when you have the user but no login?  I appear to have the opposite problem where the login exists with no matching database user.  How can I fix this?

EDIT: The below statement returns 0 rows.

EXEC sp_change_users_login 'Report'

This statement also does not fix the problem.

EXEC sp_change_users_login 'Auto_Fix','SVC.Sharepoint'

I get a message stating 0 users were fixed.


texts: Generate INSERT list but only for non-nullable columns

texts:
I am currently using SSMS 2016 as client and have an Azure SQL database (also 2008 r2).

But I am looking for a way to create a custom template to have an insert statement prepared with only the fields that have 'non-null'

If I right click on a table and generate an insert into statement, I get a really long list of all possible columns. I would like to see the bare minimum of columns which needs to be filled. Is there a way to generate such a sql script for ssms? Or is this only possible with other db tooling?

I have a table with over 30 columns and about 90% is allowed to be null, so I prefer my generated insert statement to only include the 10% of columns which I really need to fill in.


texts: I am trying to write a join using temporary table in hive

texts:
I am trying to write a join using subquery/ temporary table in hive but it doesn't seem to be working:
create temporary table  q as select server, max(time) as time from ghs group by header.server;

create temporary table new_joined as
select q.server,ghs.proccount, ghs.net from ghs right outer join
q on ghs.server = q.server;

the first query gives me around 23k rows, I want the joined table also to give me only 23k rows but I see that this is joining with each row in ghs.
ghs table is like superset having 1.2 million records.
Can someone help me understand what I am doing wrong?


texts: MSSQL 2000 replication, incrementing an integer column on a single row using an update statement

texts:
I have 2 web servers, both of which connect to separate mssql 2000 database servers, these two servers use merge replication in order to collate tracking data.

Now each web server runs an update statement to its database which goes something like this:

"update trackingdata set trackingvalue=trackingvalue+1 where id=@id"

Now I would have expected that when i go to each web server and action this that there would be a value on 1 in each table and then when the merge replication runs i would have a value of 2 in each table as there was 2 update statements but when the merge runs i am being left with a value of 1.

Can anyone explain why this is and or if I am doing something wrong ?

I need the value to increment on each server and for the new updates to add together on a merge.

Thanks in advance for any help you can provide guys!

Cheers.
Dylan.

Update: I think I should be using the 'Additive conflict resolver' for the article in question however I have supplied the column name in the text box below but I am receiving an error The specified conflict resolution column 'trackingvalue' could not be found., does anyone know how to get Additive conflict resolution working ?


texts: How to fix type conversion error in Data Flow task?

texts:
I have an SSIS job with a data flow task having an OLEDB source and OLEDB destination.

I am moving almost 40,000 rows from one DB to another DB. In my SQL query**(Source**) , i have 10 columns all are of type Varchar2(100).

In my destination table, all the 10 columns have the same data type. But, when connected the source with destination, i am getting the error :

Can not convert between string and unicode string types

But, the data types exactly matching. Where does this error come from ?

please help

Update:

I tried to solve the problem by adding a data conversion transformation, but it is taking almost 3 hrs to process 40,000 records which is worse performance.

I need to eliminate this conversion. But, Firstly i am not sure why at all this is required, if both the source and destination data types are matching ?


texts: Giving a session-local temporary name to a schema

texts: I have a schema foo_bar but also foo_x. During a particular session, is it possible to just set foo_bar to bar in PostgreSQL (8.4)?


texts: How improve the performance of the query

texts:
I think the correct answer is B and C. My reasoning:

The returned row is 1, but the Disk IO and Query is very high. So optimizer use a full table scan not index scan. B and C can make query choose index scan.

But the provided answer is A and E. Why, is there valid reasoning for A and E? Are my answers and understanding correct? Or something else?


texts: Why doesn't `1` fit into a decimal(4, 4) column?

texts:
I have a decimal(4, 4) column in MS SQL Server 2008 R2.  If I understand correctly, that means:

Precision of 4, ie up to four digits can be stored after the decimal place
Scale of 4, ie a a total of four digits can be stored

When I run an update command to set the column to 1 (update myTable set myDecimalColumn=1), I get this error:

  Arithmetic overflow error converting numeric to data type numeric.

I don't understand how that is true.  1 has no digits after the decimal and is only one digit long.


texts: how to sum times in EXPLAIN for Postgres parallel query

texts:
I'm having trouble understanding this EXPLAIN on Postgres 12.3:
EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT count(1) FROM mytable WHERE page ~ 'foo';

This is a 22GB table with 30 million rows, on a server with 16GB memory. The query counts 7 matching rows.
I interpret the output as saying 164 seconds was spent on I/O, yet the whole query only took 65 seconds. I thought it might be double counting some parallel workers, but when I add VERBOSE, it doesn't seem to add up either.
It looks like it's saying each of 2 workers spent ~55 seconds reading. If that sums to 110 seconds, how do I get to 164 seconds of I/O? (Since this query takes ~10 seconds when pages are cached, I'm guessing the actual time reading isn't too far from 50 seconds here, FWIW)
I'm also confused how the Parallel Seq Scan seems to take 32 seconds, but then there's still another 30+ seconds left to get the final result. I would think that because of the 7 rows it finds, there's really almost no work to do besides the scan. Am I reading this section wrong?
                                                                       QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=3092377.14..3092377.15 rows=1 width=8) (actual time=65028.818..65028.818 rows=1 loops=1)
   Output: count(1)
   Buffers: shared hit=75086 read=2858433 dirtied=1
   I/O Timings: read=164712.060
   ->  Gather  (cost=3092376.92..3092377.13 rows=2 width=8) (actual time=65028.732..65030.093 rows=3 loops=1)
         Output: (PARTIAL count(1))
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=75086 read=2858433 dirtied=1
         I/O Timings: read=164712.060
         ->  Partial Aggregate  (cost=3091376.92..3091376.93 rows=1 width=8) (actual time=65026.990..65026.990 rows=1 loops=3)
               Output: PARTIAL count(1)
               Buffers: shared hit=75086 read=2858433 dirtied=1
               I/O Timings: read=164712.060
               Worker 0: actual time=65026.164..65026.164 rows=1 loops=1
                 Buffers: shared hit=25002 read=952587
                 I/O Timings: read=54906.994
               Worker 1: actual time=65026.264..65026.264 rows=1 loops=1
                 Buffers: shared hit=25062 read=954370 dirtied=1
                 I/O Timings: read=54889.244
               ->  Parallel Seq Scan on public.ui_events_v2  (cost=0.00..3091374.68 rows=896 width=0) (actual time=31764.552..65026.980 rows=2 loops=3)
                     Filter: (ui_events_v2.page ~ 'foo'::text)
                     Rows Removed by Filter: 10112272
                     Buffers: shared hit=75086 read=2858433 dirtied=1
                     I/O Timings: read=164712.060
                     Worker 0: actual time=16869.988..65026.156 rows=2 loops=1
                       Buffers: shared hit=25002 read=952587
                       I/O Timings: read=54906.994
                     Worker 1: actual time=64091.539..65026.258 rows=1 loops=1
                       Buffers: shared hit=25062 read=954370 dirtied=1
                       I/O Timings: read=54889.244
 Planning Time: 0.333 ms
 Execution Time: 65030.133 ms


texts: Can SQL Server 2014 schema be defined using ODBC?

texts:
I am trying to use the ODBC driver for SQL Server 2014 to export data from an Autodesk Revit 2015 model to a database.

However, I would like to export to a default database (e.g. db1), creating a new schema for each data export.

Is it possible?


texts: Query rows with common value over result set partition

texts:
I'm on Postgres 11 and I have these tables

 foo              foobar

| id |      | foo_id | bar_id |
------      -------------------
|  a |      |      a |      1 |
|  b |      |      a |      2 |
|  c |      |      b |      2 |
|  d |      |      b |      3 |

And I need all bar_id which are common to all foos. So I have this query:

select b.bar_id from foo f join foobar b on b.foo_id = f.id

which gives me

1
2
2
3

while I'd need just 2, i.e. the intersection of the result set partitioned by f.id. Then, I might apply a where clause, e.g. where f.id in (...). How can I achieve this?


texts:
I have this issue with around 7 servers, we have an RMAN job that takes backup and  validate the backup ...

here is RMAN settings:

RMAN> show all;

RMAN configuration parameters for database with db_unique_name A103775 are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 21 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'ENV=(TDPO_OPTFILE=C:\RMAN\tdpo_A103775.opt)';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFA103775.ORA'; # default

here is the backup job:

connect target backupuser/backuppassword
connect catalog rman/rmanpassword@rcat
run
{
allocate channel tdpo type 'SBT_TAPE' parms 'ENV=(TDPO_OPTFILE=C:\RMAN\tdpo_ADATABASE.opt)';
backup database plus archivelog delete all input TAG 'Full Database Backup';
delete noprompt obsolete;
restore database validate;
release channel tdpo;
}

and I get this error message!

RMAN-06207: WARNING: 2 objects could not be deleted for SBT_TAPE channel(s) due
RMAN-06208:          to mismatched status.  Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Backup Piece    papcfat0_1_1
RMAN-06214: Backup Piece    c-317588623-20140703-03

I tried crosscheck and delete obsolete, delete expired ... and it did not help!


texts:
My idea is to trace a few days all statements (scripted the replay template) on a SQL Server 2008 r2 and replay those against a deployed version of the db on a newer version (SQL Server 2014).

Is a re-playable trace file suitable to figure out server version compatibility issues? Does this scenario make sense?

How can one realize problems? How are errors displayed when replaying queries that have issues?


texts:
Postgres 12 on Windows 7
This works:
CREATE DATABASE "foo"
    ENCODING 'UTF8'
    LC_COLLATE = 'en-US'
    LC_CTYPE = 'en-US'
    TEMPLATE template0

Afterwards, running SHOW LC_COLLATE with database foo selected reports:

But when running
SELECT UPPER('kedi' COLLATE "en-US")

it reports:
ERROR: collation "en-US" for encoding "UTF8" does not exist

Shouldn't both accept the same collation names?


texts:
After changing my PC name and workgroup, I am no longer able to connect to SQL Server.

Currently in my servers list I have only Home & localhost. Anybody know how to add server to networks list?

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

  The network path was not found


texts: MySQL pooled connections and crontab jobs (not reusing connections)

texts:
I have several cron jobs running and the task that cron has to do is execute a file that in turn checks and compares crawled data with data in the database, if it exists then the script ends and if it does not exist it inserts the data to be used by another cron task at a later time.

I have MySQL connections set by pooling, this works as expected for front end users as when I watch the process list as I can see users of my application in real time re-use a sleeping connection in MySQL and not create a new one.

The issue I am having with cron jobs is that every time cron runs the same job it creates a new connection to MySQL on a new port and does not look to reuse its last connection for that same task from the pool via the same port even though I use connection.release();. Is this normal behaviour from cron?.. If it isn't normal then is there a way to force re-using of the same port by the same repeating query?.

The outcome of this is that after 3-4 days I have hit the connection limit and have to restart server to clear this and to begin again. Obviously I would sooner get to the root of the issue without having to resort to rebooting every few days, so any advice or pointers in the right direction would help.

The system is Ubuntu 18.04 and running node processes via cron. (npm MySQL library)


texts: Is it possible to use Change Tracking on a Replication target?

texts:
We have a setup where the production SQL Server databases are replicated to an offsite location using SQL Server Replication (transactional replication).

I am looking at using SQL Server Change Tracking to get the updated data in order to perform some processing and add values to a cache. Before I start down this road, does anyone know if it's possible to use Change Tracking on a replication target? It seems like it should work, but it also seems complex enough that there would be gotchas.


texts:
i am following learn sql the hard way tutorial, in tutorial tutor suggest to use sqllite 3 but im using MSSQL 2008 and i have a problem, tutor ask from me to find something using AND or OR operators, her is the query i wrote;

select * from pet where name like 'Flu%' and age = 1000

but i receive:

  Msg 206, Level 16, State 2, Line 13 Operand type clash: text is
  incompatible with smallint

i try this one

select * from pet where name like 'Flu%' and age = '1000'

i receive :

  Msg 402, Level 16, State 1, Line 13 The data types text and varchar
  are incompatible in the equal to operator.

and when i convert age to varchar it works;

select * from pet where name like 'Flu%' and convert (varchar, age) = 1000
select * from pet where name like 'Flu%' and convert (varchar, age) = '1000'

it returns the correct answer

Here is the table design (as tutor wants);
id=int, name=text, breed=text, age=text, dead=int

why it doesnt work before converting to varchar?

Looking forward to your reply.

Kinds.


texts: SQL Server database restore from backup changes all dates with datetime2 type return with values to future dates

texts:
I'm trying to restore a SQL Server database from a .bak file.
The process completes without any issues, but the dates with datatype datetime2 are presented with totally different values (change to future dates, e.g. I see dates 1/1/4014).
The source backed-up database collation is Cyrillic_General_CI_AS.
The login user has been set to have English-us and Russian language but the same issue persists in both cases.
Other types of data, e.g. float numbers, are ok (and readable), only dates are affected.

What exactly is the issue here and can it be resolved?
Is there a specific step we missed and we need to configure?


texts:
I'm trying to automatize a bunch of postgreSQL process, namely to use PostGIS and pgRouting extensions, so for that purpose I created a sql script file that I could run on command line passing in an input argument, a table name, like the following:

ALTER TABLE ''''||:table::char(52)||''''  ADD COLUMN "source" integer;
ALTER TABLE ''''||:table::char(52)||'''' ADD COLUMN "target" integer;

SELECT pgr_createTopology(''''||:table::char(52)||'''', 0.00001, 'geom', 'gid');
CREATE INDEX ''''||:table::char(52)||''''_source_idx ON ''''||:table::char(52)||''''("source");
CREATE INDEX ''''||:table::char(52)||''''_target_idx ON ''''||:table::char(52)||''''("target");

So when I try to run in the command line, as such:
psql -h my_host -U my_user -d my_database -c table="my_table" -f /script/pgRouting.sql

I get a syntax error, so what's the correct way of doing this?


texts: Strange query results for DELETE with ROW_NUMBER

texts:
In the following DELETE statement I try to delete all rows except the first one ordered by some criteria. (The actual query makes more sense, this is just a repro. All the sys.objects stuff is just for generating test data.)

Note the filter r <> 1. Yet, the OUTPUT clause outputs deleted rows with r = 1. How can this be?

USE tempdb
SET XACT_ABORT ON

BEGIN TRAN

    SELECT *
    INTO #o
    FROM sys.objects

    SELECT TOP 2 name FROM #o ORDER BY object_id --debug output

    DELETE k
    OUTPUT Deleted.name, Deleted.r
    FROM (
        SELECT k.*, ROW_NUMBER() OVER (ORDER BY object_id) r
        FROM #o k
    ) k
    WHERE r <> 1 --OUTPUT returns rows with (r = 1)

    SELECT TOP 2 name FROM #o ORDER BY object_id --debug output

ROLLBACK

Query results:

(The 3rd result set is complete - only one row.)

Note, that all rows except the first one were deleted. The numbering order of column r does not seem to match what was requested. And there is a row with r = 1.

This is SQL Server 2014 CU3 with trace flag 4199 enabled.


texts: select row where a couple of column values is different

texts:
I have a table A with thoses columns : date, ip, created_by.

I would like to display date, ip and created by of rows where for one ip it exists different created_by.

Thank you

I explain more :

table answers:

date    ip    user_id   something_else    something_else
--------------------------------------------------------

table users:

id  name  siret tel email...
----------------------------

I would like the list of answers from answers table (date + ip + name_of_the_user +  siret_of_the_user) where for the same ip I have different users.

thank you


texts: Difference in Size of SQL Server 2008 R2 backup on Local disk and on Remote Network Share

texts:
I wanted to take backups of all SQL Server 2008 R2 databases on a network share, I already  have a backup schedule for backup on local disk. I have tried two methods:

using SQLBackupAndFTP Software
using SQL Server 2008 R2 maintenance Job

Using both above methods for the backup on network drive, there is hell a lot of difference of the size of data as compared to backup on local disk. Please see details below

Backup of data using SQL Server 2008 R2 Scheduler:

When I took the backup of the data using the SQL Server 2008 R2 scheduler

The size of the data was almost 10.5 GB.
The number of files that were backup were 30 items/files

Backup of data using SQLBackupAndFTP software:

When I took the backup of the data using the SQL Server 2008 R2 scheduler

The size of the data was almost 822 MB.
The number of files that were backup were 28 items/files

I really wonder what has been done, the backup files in both cases have extension .bak and no compression is done.

Backup of data using SQL Sserver 2008 R2 on Local Disk and on Network Share:

I then configured two jobs:

One for backup on the local disk drive
One for the backup on the network share

To my surprise, there is still difference of Size in data and files, please see screen shots below. I really wonder what is happening and why is there so much difference in the size of data.

Any body has any idea, please feel free to comment and share your knowledge.


texts:
I am new to MongoDB. I am trying to create a Replica Set for testing and development. It shows me the message in the picture!

I followed the steps shown in the official documentation:

Deploy a Replica Set for Testing and Development

What is the problem please ?


texts: SQL Error [42883] - function does not exist - Cannot call function from trigger

texts:
I want to insert a new row to Table2 when Table 1 is inserted a row. I have created a function for that. However, when I try to create the trigger, it gives an error although function is created and I can see the function.

SQL Error [42883]: ERROR: function insertintoautoincrementexample() does not exist

Following are the details related to it. I tried to create the trigger with some text parameters but no luck.

//Table 1
CREATE TABLE myOriginalTable(
    companyName VARCHAR(15) PRIMARY KEY NOT NULL,
    location VARCHAR(30)
);

//Table 2
CREATE TABLE autoincrementexample (
    entryid INT4 NOT NULL DEFAULT nextval('autoincrementexample_entryid_seq'::regclass),
    companyname VARCHAR(15) NOT NULL,
    location VARCHAR(30) NOT NULL,
    PRIMARY KEY (companyname)
);

//Function
CREATE OR REPLACE FUNCTION insertIntoAutoIncrementExample(companyname text,location text)

    returns void
    language plpgsql
    as $function$

    declare
    varaudseq bigint;

    begin
        varaudseq :=0;

    begin
        SELECT NEXTVAL(autoincrementexample) INTO varaudseq;
        INSERT INTO autoincrementexample(companyname,location)VALUES(companyName,location);
    COMMIT;
    END;
    END;
    $function$;

//Trigger
CREATE TRIGGER after_insert_original
    AFTER INSERT ON myoriginaltable
    FOR EACH row
    EXECUTE PROCEDURE insertintoautoincrementexample();

Would appreciate any help regarding this.