texts: Lost SQL credentials and unable to connect to SQL via SQLCMD

texts:
I have a machine running SQL 2014 but have neither the windows authentication or sa authentication details due to mismanagement of a previous support team.

Having tried logging in to SQL Management Studio using my Microsoft AD account I received the following error:

  ===Cannot connect to BACKUP-MGMT-01\CONSULT.===
  A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (.Net SqlClient Data Provider)

I suspect this is due to my account not having privileges.

I then found some steps online allowing you to add a new sa or windows authenticated user via SQLCMD. However, when I run either the SQLCMD -S .\BACKUP-MGMT-01\CONSULT or SQLCMD -S .\BACKUP-MGMT-01 I just receive the following error:

  C:\Users\frank>SQLCMD -S .\BACKUP-MGMT-01\CONSULT
  Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : SQL Server Network Inte
  rfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. .
  Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login timeout expired.
  Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : A network-related or in
  stance-specific error has occurred while establishing a connection to SQL Server
  . Server is not found or not accessible. Check if instance name is correct and i
  f SQL Server is configured to allow remote connections. For more information see
   SQL Server Books Online..

Can anybody assist?


texts: Predicting AWS RDS MySQL Instance Performance

texts:
I have a small MySQL db running on AWS RDS t2.small instance.

So far, the CPU usage has been very predictable based on the traffic from my app.

Example:

1000 concurrent users = 15% CPU
2000 concurrent users = 30% CPU
3000 concurrent users = 45% CPU
4000 concurrent users = 60% CPU

My RDS is averaging just 15% with very predictable traffic spikes and I do have enough credits to cover the CPU spikes.

As my app is occasionally hitting 100% CPU, and since my "Reserved" instance will expire soon, I will have to soon decide on my next instance type.

As I'm not really able to benchmark the app/db and I will have to select another "Reserved" instance, considering all things being equal, the questions are:

Since the t2.small is only 1 vCPU | 2GB memory, if I upgrade to t3.medium with 2 vCPU and 4GB of memory, should I expect double capacity?

T2.small vs T3.medium

T2.small  | 1 vCPU | 2BG memory
T3.medium | 2 vCPU | 4GB memory

Should I expect the t3.medium will be running only at 30% with 4000 concurrent users?

Considering my app usage is growing around 10% per month, should the t3.medium keep me running for another year?

I don't really have a budget for anything fancier, so unless the AWS Aurora would be a better option, I would like to stay with the T3 instance family.

Here is a CPU screenshot.

SHOW GLOBAL STATUS LIKE '%connect%'

# Variable_name, Value
'Aborted_connects', '47'
'Connection_errors_accept', '0'
'Connection_errors_internal', '0'
'Connection_errors_max_connections', '0'
'Connection_errors_peer_address', '0'
'Connection_errors_select', '0'
'Connection_errors_tcpwrap', '0'
'Connections', '2986313'
'Max_used_connections', '113'
'Performance_schema_session_connect_attrs_lost', '0'
'Ssl_client_connects', '0'
'Ssl_connect_renegotiates', '0'
'Ssl_finished_connects', '0'
'Threads_connected', '4'


texts: Why when I use variables on SQL the Database Engine avoid the indexes?

texts:
I'm improving our SSIS process passing the variables through a declare and I found this:
Here you have the plan execution using variables brentozar.com/pastetheplan/?id=rkSxs7VPv for the following statement:
select *
from fact_Venta
where Data_ID >= @data1
and Data_ID <= @data2

And here without variables brentozar.com/pastetheplan/?id=SJ8soXEww
SELECT *
FROM [fact_Venta]
WHERE [Data_ID]>=@1
AND [Data_ID]<=@2

As you can see, when I use the declare SQL Database Engine doesn't use the index. The index used is a non-clustered for the column used on the where.
Both executions were on the same server, with the same set options and at the same time executed.


texts: Effective indexing and querying of overlapping date/time ranges

texts:
I have a table with the following schema:

AccountId: int
StartDateTime: datetime2(0)
EndDateTime: datetime2(0)

I want to be able to query this for rows with a certain AccountId, and whose date range intersects with a specified date range. E.g:

SELECT *
FROM ... AS d
WHERE d.AccountId = @AccountId
    AND d.StartDateTime <= @EndDateTime
    AND d.EndDateTime >= @StartDateTime

This will be a very large table and I'll want to be able to query it as efficiently as possible. I've specified a single composite index of AccountId, StartDateTime, and EndDateTime.

Will this index, and the above query work effectively together? If not, what would be a better alternative? Thanks.


texts: Stored procedure variable storage takes long time to execute

texts:
I have a stored procedure which take an endless time for execution:

BEGIN
set @my_user_id=null;

select  user_id into @my_user_id
    from  office_user
    where  username like my_username
      and  `password` like MD5(my_password);

select  user_id,`name`,username,mobile_no,email,designation,role_id,
       signImage,colorCode,category,role_new.permission,report
    from  office_user
    INNER JOIN  role_new on office_user.role_id=role_new.roleId
    where  user_id=@my_user_id;

select  id,`name`,parent_id
    from  businesslevels
    where  parent_id=1
      AND  is_active =1
      AND  `name`!='Main'
      and  FIND_IN_SET(id,
(          ( SELECT  GROUP_CONCAT(topLevelId)
                    from  businesslevels
                    where  FIND_IN_SET(id,
(                         ( SELECT  GROUP_CONCAT(distinct bLevelid)
                                    from  forms
                                    where  FIND_IN_SET(Fname,
(                                        ( SELECT  GROUP_CONCAT(formName)
                                                    from  userforms
                                                    where  userType='office'
                                                      and  emp_id IN (@my_user_id))))))
                          )
    ORDER BY  `name`;

END

and when I am using this i.e. No variable usage, direct as a repeat sub query

This procedure execute quickly:

BEGIN
select  user_id,`name`,username,mobile_no,email,designation,role_id,
       signImage,colorCode,category,role_new.permission,report
    from  office_user
    INNER JOIN  role_new on office_user.role_id=role_new.roleId
    where  where username like my_username
      and  `password` like MD5(my_password);

select  id,`name`,last_name,emp_code,reporting_to,shift_start,
       shift_end,model,version,track_interval,conveyance,reporting_to
    from  field_user
    where  FIND_IN_SET(reporting_to,concat(@my_user_id,GetUserFamilyTree
(          ( SELECT  user_id
                            from  office_user
                            where  username like my_username
                              and  `password` like MD5(my_password))));

END

I don't know why this happening. I don't want to write subquery again. As there are more queries in procedure that need user_id

Any suggestion will be appreciated


texts: Why is @temp table performance some times worse than #temp table performance?

texts:
I was recently working in a very slow stored procedure (took 5 minutes to run).  I made a very small tweak from doing this:

declare @tempTable table
(
  ...
)
insert into @tempTable
select .....

to

select ... into #tempTable from someTable

The script then ran in ~2 seconds.  What can explain this time difference?


texts: How to examine PostgreSQL server's SSL certificate?

texts:
Suppose there is a PostgreSQL server running and it has SSL enabled.  Using "standard" Linux and PostgreSQL tools, how can I examine its SSL certificate?

I'm hoping for output similar to what you would get from running openssl x509 -text ....  And I'm hoping for a one- or two-liner command line answer so I don't have to resort to running a packet sniffer.

I do not have access to the PostgreSQL server, so I cannot look at its configuration files directly.

I do not have a superuser login, so I can't get the value of the ssl_cert_file setting and then pg_read_file on it.

Using openssl s_client -connect ... doesn't work because PostgreSQL doesn't seem to want to do the SSL handshake right away.

From a quick look at the psql documentation, I could not find a command-line parameter that makes it show that information on startup.  (Though it does show me certain cipher information.)


texts: Upgraded DB Driver SRVSQL High Waits PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICY

texts:
Upgraded our PHP SQL driver and now the Top 3 waits are below?
Wait_Type   Wait_Time_Seconds   Waiting_Tasks_Count Percentage_WaitTime
PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICY 5.551000    53732   21.924246613215371
PREEMPTIVE_OS_DELETESECURITYCONTEXT 4.417000    108255  17.445396737627868
PREEMPTIVE_OS_CRYPTACQUIRECONTEXT   3.868000    53737   15.277064655002172

Anyone ever seen these, i cant find a lot of info on them?


texts:
Couple of weeks ago I asked this question Error logs various questions and got the answer I needed. Based on the answer and some readings I want to create the following jobs:

cycle the error log once it reaches a size in Mb or expands to say 2 weeks
move the oldest error log to a different location once we reach the limit of archived error logs files (now we have the default, 6)

On the answer to my previous question I got the powershell snippet I'm posting at the end to get the size of the error logs. But is the first time I will attempt to do something with powershell so I do not know even where to start with this. Any help to setup these jobs? Maybe powershell is not needed and can be done using CmdExec or tsql? Is not that I know CmdExec, just asking on other approaches. The best one would be tsql as I've more experience with it.

$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server("YOUR_SQL_SERVER_NAME")
$SqlServer.EnumErrorLogs() |
    Select-Object Name, @{Name = "SizeMB"; Expression = {[System.Math]::Round($_.Size/1024/1024,2)}}


texts: Insert into table select - Replication lag - Percona Server 5.6

texts:
I have two MySQL instances (Percona server 5.6.31) in Master-Slave replication setup.

I have set the below configuration:

ROW based replication is set.
Transaction Isolation is set to read-committed.

Today, there was a insert going on in my Master. It was in the format

INSERT INTO table1 SELECT * FROM table2

Table 2 has 200 million rows.

Though the number of insert records was only 5000 but the operation lasted for 30 mins. I observed replication lag during the insert operation.

I have load infile disabled due to security concerns. Hence I can't insert using that as well.

I went this article from Percona which says that this can be resolved if txn isolation is used as ROW and versions above 5.1 that this is resolved.

In what way I can make my slave to be in sync with Master in such conditions?
Why does the slave lag here?


texts:
Does this design satisfy 3NF, and can any improvements be made?

If it does not satisfy 3NF - why?

By improvements, I only care if it will seriously affect scalability, efficiency, etc - I'm not interested in personal preferences.

To add more detail, there can be many bids to a project and any employee can create a new bid. Unit price can be different for each project/bid.


texts: Relation between Faculty, Student and Course tables

texts:
What should be the relation between following tables. Confuse whether i create an enroll table with student and course or courses_assigned table using faculty and course and then create an enroll table with a foreign key of courses_assigned table.

This model is for an educational institute system, where users are students and faculty members. Courses are assigned to faculty members and students are enrolled in courses.

System will show courses and their instructors(faculty members) to each student which he or she has to study and faculty members will see record of all students of a particular section which he or she has to teach.


texts: How to generate auto increment values for my duplicate column in my sql?


texts: Query Store "Overall Resource Consumption" details

texts:
I want to know how much total CPU a database has consumed for a given date span, for all queries.

I can see it in SSMS (under Query Store / Overall Resource Consumption), but how can I query for that programmatically?


texts: Untraceable error importing Huge dataset with SQLCMD

texts:
I am currently doing an internship and I received a 42 gb ms sql-server import script.
I tried to import the script using the ms SQLCMD tool all at once.
It ran for 30 minutes and then I got the following error:

msg 102 level 15 state 1 line 37 incorrect syntax near '7500'

So naturally I checked line 37 and searched for the value 7500 but I could not find it.
Then I tried to find the last executed query in my server log.
Having found the last inserted query, I knew what line in my script was causing the error.

Unfortunately I could not find the value 7500 in the error causing line.
And the next 3 "batches" imported just fine...

So I cleared my database, and tried to import the script a second time with the exact same error as result.

So I tried to split the 42 gb script into 3000 smaller scripts.

I am now running the 3000 scripts sequentially and it seems to be running just fine.
It has been running for 4 hours now and it is past the point of the previous error.

My question is: Did I find a bug in SQLCMD or is this a known problem with larger import scripts? If this is a know problem what is the best way to counter this (Splitting the file took a long time)

Thanks.


texts:
I am trying to create a procedure that will generate an xml document of a given table whilst updating the information in said table. My procedure does not recognise the use of the temporary table unless I use a CREATE TABLE statement (I have not tried table var's yet). Ideally I would not use the CREATE TABLE statement in case I/another developer wanted to add another column(s) in the future.
Here is my code currently:
UPDATE Audit.ErrorLog SET ErrorReported = 0 OUTPUT inserted.* INTO ##temp

SELECT *
FROM ##temp
FOR XML PATH('Error'), ROOT ('Errors')

DROP TABLE ##temp

And it fails with:

Msg 208, Level 16, State 0, Line 24
Invalid object name '##temp'.

It's not the end of the world if I have to use a CREATE TABLE statement, but I would prefer not to explicitly declared it.
Notes:

I know there is no error handling at the moment, I want to get this section working first.
I have also tried naming the columns instead of using Inserted.*
I am purposely setting ErrorReported = 0 as I am in a test environment


texts: Best way to backup PostgreSQL cluster before upgrade

texts:
I noticed that PGDATA folder changes during an upgrade (which is normal as I use the pg_upgrade --links command), but I don't know the best way to backup up my cluster before the upgrade in order to restore it in case of failure.

AFAIK, the several ways to backup my cluster are:

pg_dumpall
pg_basebackupp
tar

I tested pg_dumpall and pg_basebackup, but I've seen that after doing an upgrade and restore, the size and files of PGDATA changed (like the WAL folder for example).

Is this normal and which method is recommended to backup/restore cluster ?


texts: Are there any SQL Server or MySQL versions that allow MULTIPLE NULL for a UNIQUE Constraint?

texts:
I wanted to know whether there are any versions that allow multiple NULL on UNIQUE.
Also, single NULL for UNIQUE.
Please specify the versions.


texts: Can SQL Server (2012-2014) be configured to run "on-demand?"

texts:
I am very, very new to SQL, and am having difficulty configuring the server appropriately. All I have to work with for now is a small copy of the database schema of the company I work for, with randomized data in it. Running a small server with just a few tables that I only occasionally query to test applications, can I configure SQL to use resources "on-demand" (perhaps an agent that listens for queries, and does only this, not caching data in memory) or to use minimal system resources?

I find that, even with only 15 tables totaling 250MB in storage, SQL is always using at least 2GB of memory on a machine with only 8GB to spare.

I did look for similar questions here on StackExchange, but only found debates about how much memory to devote to the OS as opposed to the database on a server machine.


texts:
I am completely new to database modelling (except for some academic knowledge from a long time ago) but for my new project I want to build a database for the following application.
I am collecting different income figures from different companies from different product categories. I calculate the aggregate of these figures and calculate some metrics such as averages and growth of the figures over the years/over product categories and over the sectors of these companies. I wanted to build a database to accomplish this and already made the following draft. It is based on a simple star model but I would like to get your input on what can be better.
Looking forward to your input!


texts: mongodb - different keyfiles for different replica sets?

texts:
I am building a cluster using mongodb (3.4). Can I use separate keyfiles for my configuration replica set and my shard replica sets? Judging by the documentation (https://docs.mongodb.com/v3.2/tutorial/enforce-keyfile-access-control-in-existing-replica-set/), it seems possible but I'm not sure.

Thanks


texts: Solution for the deletion of old Database Backups for Linux SQL Server 2017 after Backups created with Ola Hallengren scripts?

texts:
I have a problem when I want to delete older backups, I've created with Ola Halengreen scripts.

USE Maintenance
EXECUTE dbo.DatabaseBackup
 @Databases = 'USER_DATABASES',
 @Directory ='/mssql/backup/',
 @DirectoryStructure ='${InstanceName}{DirectorySeparator}{DatabaseName}',
 @Filename='{DatabaseName}_{BackupType}_{Year}{Month}{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}',
 @BackupType = 'FULL',
 @Compress = 'Y',
 @CleanupTime = 3

I got the following error message:

  The value for the parameter @CleanupTime is not supported. Cleanup is not >supported on Linux.

So far so good, but whats the best way now to delete old Backups without to destroy the Backup chain?

My frist thought was to have a script to delete

#!/bin/sh
find /mssql/backup -name "*.bak" -type f -mtime +3 -exec rm -f {} \;
find /mssql/backup -name "*.trn" -type f -mtime +4 -exec rm -f {} \;

The internal system of the SQL Server Instance will not recognize the clean up after the script was executed. The Backup History in the internal Backup Management will not updated and in the case of a recovery the system wants to have backups which are not existing anymore.

So does anyone have a solution to the problem?


texts: How to startup a oracle database with sql plus or with any developer tool without connecting to the Database

texts:
How to startup a database without connecting to Database. Actually what I did is I shutdown my database and then my sql plus screen hanged and so closed unhandled.

then I tried to connect my database then I got ORA-12514 error.

I checked my database service in Services.msc -- it is working and I restarted my service as well. I check tnsname.ora file as well.

May be solution of
ORA-12514:
TNS:listener does not currently know of service requested in connect descriptor


texts:
I have database in PostgreSQL. It is simplest possible database with schema presented here:

  http://www.postgresqltutorial.com/postgresql-create-table/

I want to migrate it to MySQL. I trying to follow after tutorial:

  https://mysqlworkbench.org/2012/11/how-to-migrate-postgresql-databases-to-mysql-using-the-mysql-workbench-migration-wizard/

I installed ODBC, but during testing connection I have error:

Could not connect to Source DBMS.
[07006] Received an unsupported type from Postgres. (14) (SQLGetData)

ODBC connection string: DRIVER=PostgreSQL ANSI;SERVER=127.0.0.1;PORT=5432;DATABASE=test;UID=daniel;UseDeclareFetch=1;

My system: Ubuntu 18.

Update 1

I simplified considered schema to single table

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

I found similar topic on stackoverflow:

  https://stackoverflow.com/questions/26199390/error-when-trying-to-migrate-postgresql-db-to-mysql-with-workbench

changed driver value to psqlODBC and problem still exists. I can connect to database from Datagrip without problems.


texts: I have faced with some challenge regarding my audit. I need to save my audit events on a file, but file should be in a readable format ( like txt) which will allow to analyze it out of SSMS. XXXXX.sqlaudit format does not allow this. Maybe you know options that will help to export audit events` file to readable format ? Thanks in advance.


texts: How to changes or update local server connection in Maintenance plan job

texts:
Two days back our client changed one of our Dev Server name

After Server Renamed, All my maintenance jobs and other jobs are failing because server name mismatch.

We are using sql server 2012 version and server 2008 OS

So today morning i renamed my Sql server 2012 name to updated given name and made table, procedures updates

I tried to update Local server connection in maintenance job but it is uneditable.
Then i added new server connection,still no use i am getting below error, while executing jobs.

After i tried with target page in jobs property option, there also only target server is selected and multiple target server is disable.

Error below

Executed as user:
NT Service\SQLSERVERAGENT.
Microsoft (R) SQL Server Execute Package Utility  Version 11.0.2100.60 for 64-bit  Copyright (C) Microsoft Corporation. All rights reserved.
Started:  12:01:28 AM  Error: 2013-12-16 00:01:43.98     Code: 0xC00291EC     Source: {410F7661-F71A-4B68-9584-BA422AB00F02} Execute SQL Task
Description: Failed to acquire connection "Local server connection".
Connection may not be configured correctly or you may not have the right permissions on this connection.  End Error
Error: 2013-12-16 00:02:00.00
Code: 0xC0024104
Source: Territory_Update
Description: The Execute method on the task returned error code 0x80131904
(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)).
The Execute method must succeed, and indicate the result using an "out" parameter.  End Error
Error: 2013-12-16 00:02:15.00
Code: 0xC0024104
Source: {4E2AF328-0B8D-4905-83BE-839FDDEFC09C}
Description: The Execute method on the task returned error code 0x80131904
(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)).
The Execute method must succeed, and indicate the result using an "out" parameter.
End Error  DTExec: The package execution returned DTSER_FAILURE (1).
Started:  12:01:28 AM
Finished: 12:02:15 AM
Elapsed:  46.641 seconds.
The package execution failed.
The step failed.

Help me out in this guys, Thanks in advance


texts: DELETE a single row from a table with CASCADE DELETE picks a slow plan... but not always

texts:
Schema Summary

A dozen tables related by foreign key to a central table (call it TableA). All tables have a PK that is INT IDENTITY, and Clustered. All the tables' FKs are indexed.

This looks like a star configuration. TableA has fairly static personal info such as name, & DOB. The surrounding tables each have lists of items about the person in TableA that change or grow over time: for example, a person might have several emails, several addresses, several phone numbers, etc...

In the unusual event that I want to delete from TableA (test data that gets inserted during performance checks, for example), the FKs all have CASCADE DELETE to handle removing all subordinate data lists if they exist in any of the surrounding tables.
I have three environments to play with: DEV, QA, and UAT (well, four if you count PROD, but "play" is not something I would want to do to PROD).
DEV has about 27 million people in TableA with various counts upward of 30M in the surrounding tables.
QA and UAT are only a few hundred thousand rows.

The Problem

The simple "delete from TableA where Id = @Id" takes < 1ms on DEV (the big one) and the execution plan looks fine, lots of tiny thin lines and all index seeks... but here's the rub: infrequently on DEV, and ALWAYS on QA and UAT, the simple delete takes about 1 second and the plan shows almost all the indexes are being scanned, with big fat lines showing the entire row counts.

Observations

The delete statement is issued by Entity Framework Core running inside an API so I have limited capability to mess with it (making it into a stored procedure, index hinting, using a different predicate, and other ideas...)

Despite all three environments being identical (same script created all three environments), nothing I have done so far has improved QA and UAT, but DEV is usually fast.

When DEV becomes slow, it remains slow until "something" happens. I haven't figured out what the "something" is, but when it occurs, the performance reverts to fast again and remains that way for days.

If I catch DEV at a slow time, and use SSMS to manually run a delete statement, the plan is fast (<1ms); but the deletes coming from the API use a slow plan (1s). Entity Framework is (as best I can tell) using sp_executesql to run a parameterized "delete from tableA where Id = @Id". The manual query is "DELETE FROM TableA WHERE Id = 123456789"

The row being deleted is always a recently-added row, meaning that the Id is right at the "top" and probably not within the range of the index statistics (although I speak from a position of profound ignorance on that topic and probably have my wires crossed...)

What I have tried so far

Reading up on FK cascade delete issues, it seems all the FKs need to be indexed, so I did that.

Rebuild (not just Reorg) every index.

Selectively delete the bad plans from the plan cache using DBCC FREEPROCCACHE (plan handle)

Running the excellent tools from Brent Ozar got me checking that the FKs were all is_not_trusted = 0

Looked at these (and other) previous StackExchange questions:1, 2, 3, 4

Of those, I suspect that the last one, with a description of how the cardinality estimator gets confused, might be pointing to the source of the problem, but I need help figuring out what to do next...

The plan shot below (from ssms) shows the slow plan: some of the FK indexes are being scanned (but not all) and there is an excessive memory grant.  The fast plan would show all index seeks. The whole plan is at ShowMyPlan Link

I hope someone can point out what I have missed, or what I can investigate further.

Thanks!

Bad Plan


texts: "Untrusted Domain" error when connecting to SQL Server localhost and specifying port

texts:
I have two SQL Server 2016 instances running on localhost, and I can connect to both with no issues (localhost and localhost\test) using windows authentication. However, if I specify the port explicitly in SSMS ("localhost,1433" or "localhost,1435", respectively), I receive the following error:

  Login failed. The login is from an untrusted domain and cannot be used
  with Windows authentication. (Microsoft SQL Server, Error: 18452)

I'm connecting to a local instance (it's not a domain PC, but it shouldn't matter) and I'm a local admin, so there are no permissions issues - when I connect without the port specified, I connect without any issues at all.

I've seen this error when connecting across domains or in a workgroup, but that's not what's happening here as I can connect if I remove the port number.


texts:
I've joined a Ecommerce business where they use SQL Server 2012 AlwaysOn for HADR and this is the first time I've come across supporting this technology.

The Sql Server log on both nodes gets spammed almosy daily with the error messages shown below.

What do these errors mean? Are they benign?

I asked the Infrastructure guys to take a look at system and network logs - and they couldnt see anything odd at the times correlating with when the below messages are logged. The AlwaysOn Dashboard records error 35206 against the connection timeout message and the BOL explanation does not make clear whether I should be addressing these timeouts.

  Message
  A connection timeout has occurred on a previously established connection to availability replica 'SQLWEB02' with id [A794F13A-6FDA-4E7C-B418-A70B320D1AB3].  Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role.

  Message
  AlwaysOn Availability Groups connection with primary database terminated for secondary database 'DB1' on the availability replica with Replica ID: {a794f13a-6fda-4e7c-b418-a70b320d1ab3}. This is an informational message only. No user action is required.

  Message
  BACKUP failed to complete the command BACKUP LOG DB1. Check the backup application log for detailed messages.

  Message
  A connection for availability group 'AG2' from availability replica 'SQLWEB01' with id  [CC17411E-D3E1-4B32-95D8-8D69BB94E7E0] to 'SQLWEB02' with id [A794F13A-6FDA-4E7C-B418-A70B320D1AB3] has been successfully established.  This is an informational message only. No user action is required.

  Message
  AlwaysOn Availability Groups connection with primary database established for secondary database 'DB1' on the availability replica with Replica ID: {a794f13a-6fda-4e7c-b418-a70b320d1ab3}. This is an informational message only. No user action is required.

  Message
  Error: 35285, Severity: 16, State: 1.

  Message
  The recovery LSN (187402:6392:1) was identified for the database with ID 11. This is an informational message only. No user action is required.*

Thanks


texts: How can I get a local variable that's a list in a stored procedure?

texts:
I have some code in C# I'm trying to move to a stored procedure - Sql Azure does not like multiple calls. So everything needs to get moved to a proc.

Here's the C# code:

    List<string> addList = new List<string>(EmailDomains);
    List<string> deleteList = new List<string>();
    using (IDbCommand cmd = conn.CreateCommand())
    {
        cmd.Transaction = transaction;
        cmd.CommandText = "select * from CompanyDomains where CompanyDomains.CompanyId = @CompanyId";
        DbUtilities.AddParam(cmd, "@CompanyId", CompanyId);
        using (IDataReader reader = cmd.ExecuteReader())
        {
            int ordinal = reader.GetOrdinal("EmailDomain");
            while (reader.Read())
            {
                string domain = reader.GetString(ordinal).Trim();
                if (! addList.Remove(domain))
                {
                    deleteList.Add(domain);
                }
            }
        }
    }
    foreach (string domain in deleteList)
    {
        using (IDbCommand cmd = conn.CreateCommand())
        {
            cmd.Transaction = transaction;
            cmd.CommandText =
                "delete from CompanyDomains where CompanyDomains.CompanyId = @CompanyId and CompanyDomains.EmailDomain = @EmailDomain";
            DbUtilities.AddParam(cmd, "@CompanyId", CompanyId);
            DbUtilities.AddParam(cmd, "@EmailDomain", domain);
            cmd.ExecuteNonQuery();
        }
    }

    // deleted domains get added/updated to DeletedCompany
    foreach (string domain in deleteList)
    {
        DeletedCompany.InsertOrUpdate(conn, transaction, domain, CompanyName);
    }

    foreach (string domain in addList)
    {
        using (IDbCommand cmd = conn.CreateCommand())
        {
            cmd.Transaction = transaction;
            cmd.CommandText = "insert into CompanyDomains (CompanyId, EmailDomain) " +
                              "values (@CompanyId, @EmailDomain)";
            DbUtilities.AddParam(cmd, "@CompanyId", CompanyId);
            DbUtilities.AddParam(cmd, "@EmailDomain", domain.Trim());

            cmd.ExecuteNonQuery();
        }
    }

    // added domains get removed from DeletedCompany
    foreach (string domain in addList)
    {
                    using (IDbCommand cmd = conn.CreateCommand())
    {
        if (transaction != null)
            cmd.Transaction = transaction;
        cmd.CommandText = "delete from DeletedCompany where DeletedCompany.Domain = @Domain";
        DbUtilities.AddParam(cmd, "@Domain", domain);
        cmd.ExecuteNonQuery();
    }

    }

What I'm totally stopped on is I don't see any equivalent of a List is SQL. Is there a way to build all this up in a list?

Or is there a delete/insert/update I can do where the select uses the list I pass in? I am passing in a list in the form of a defined type(table) that is the new list of domains. From that list:

Any domain in the passed in list that is not in CompanyDomains is added to CompanyDomains.

Any domain in the CompanyDomains table that is not in the passed in list is 1) removed from CompanyDomains and 2) added/updated to the DeletedCompany table.

Any domain that is in the liast and in the DeletedCompany table is deleted from the DeletedCompany table.

The DeletedCompany.Domain column is unique in that table. So additions to it must be an upsert.

The CompanyDomains table also has a CompanyId column and everything there is where CompanyDomains.CompanyId = @CompanyId (passed in parameter to the proc).

This is for Sql Server 2008 & Sql Azure. It does not need to run on anything else.

thanks - dave


texts: How does mysql decide if concurrent insert is possible for a MyISAM table?

texts:
For "concurrent inserts", MySQL reference manual has the following explanation:

  The MyISAM storage engine supports concurrent inserts to reduce
  contention between readers and writers for a given table: If a MyISAM
  table has no holes in the data file (deleted rows in the middle), an
  INSERT statement can be executed to add rows to the end of the table
  at the same time that SELECT statements are reading rows from the
  table.

http://dev.mysql.com/doc/refman/5.5/en/concurrent-inserts.html

Let's say our database "concurrent insert" parameter is set to "Auto" (1).
And we have a MyISAM table with a gap. When we insert new rows and fill those gaps, does the table "immediately" get ready to accept "concurrent inserts" for future insert queries?

Or do we need to run "OPTIMIZE" before the table knows there are no gaps?


texts: MySQL use both FULLTEXT and normal index

texts:
I have a table like this:

    id  type    idElem  name      date  content  lang     state
------  ------  ------  ------  ------  -------  ------  --------

When I run this query against it:

SELECT
  `contents`.*
FROM
  `contents`
WHERE `contents`.type = "files"
  AND `contents`.name = "url"
  AND `contents`.state = 2
  AND MATCH(`contents`.content) AGAINST ("somewords")

Only the FULLTEXT index on content is used, not the one on type, idElem, name, state :

    id  select_type  table     partitions  type      possible_keys  key     key_len  ref       rows  filtered  Extra
------  -----------  --------  ----------  --------  -------------  ------  -------  ------  ------  --------  -------------------------------
     1  SIMPLE       contents  (NULL)      fulltext  type,idx       idx     0        const        1      5.00  Using where; Ft_hints: sorted

Why ?

Here's my table schema (with more than 300k rows):

CREATE TABLE `contents` (
  `id` int(11) NOT NULL,
  `type` varchar(35) NOT NULL,
  `idElem` int(11) NOT NULL,
  `name` varchar(35) NOT NULL,
  `date` int(11) NOT NULL,
  `content` longtext NOT NULL,
  `lang` varchar(3) DEFAULT NULL,
  `state` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `contents`
  ADD PRIMARY KEY (`id`),
  ADD KEY `type` (`type`,`idElem`,`name`,`state`);
ALTER TABLE `contents` ADD FULLTEXT KEY `idx` (`content`);

ALTER TABLE `contents`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;


texts:
How do I change the MySQL data folder path in a Mac?

I am running OS X 10.10 & MySQL 5.6
In Windows I can edit datadir in my.ini
For Mac, mysqld does not have parameter for --defaults-file=


texts: Are there differences in performance of getting data between FullText Search and indexed columns

texts:
In SQL Server 2008 R2 I have a query which looks like

create stored procedure give_me_art @filter varchar(15),@skl_id int
begin
select
  id,naziv,sifra,isnull(lager.kolicina,0) as lager
from art
left outer join lager on art.id=lager.art_id and lager.skl_id=@skl_id
where sifra like '%'+@filter+'%' or naziv like '%'+@filter+'
end

I have an index on column naziv and column sifra.

I am considering changing this query to full text search. Table art has around 150K records and my main goal is to get faster response from SQL Server, because this query is common during daily work.

If I make a full text index on these two columns and redesign my query to use the full text index, what will happen with performance of this query?


texts: Which Optimistic Concurrency Control method will work out best in the long run?

texts:
These are the options I've thought of:

DotNet Dave:

UPDATE mytable set f1=newval where id=666 and f1 := f1ValueFromSelect

Charlie Checksum:

UPDATE mytable set f1=newval where id=666 and md5(concat(id,f1)) := checksumFromSelect

Timothy Timestamp:

UPDATE mytable set f1=newval where id=666 and mytimestampfield := timestampFromSelect

Veronica Version:

UPDATE mytable set f1=newval, version=version+1 where id=666 and version=versionFromSelect

I like Dave, as no schema changes required. Have to keep all of record's original data on client though. How to compare blobs?

Charlie is good in that less data to send to client. Not sure about performance with blobs/clobs

Tim and Veronica are good as pretty simple though may require schema change. Timestamp looks a bit more foolproof. How cross-platform is it?

Thanks!


texts:
I am trying to understand the implications of setting up a 2 node MongoDB cluster with the following configuration (Documentation does say a minimum of 3 nodes is required).
MongoDB Version is 3.2.
If I configure both nodes with "arbiterOnly": false, and set Node1 with Priority: 2 and Node2 with Priority: 1, in case Node1 goes down, won't Node2 take over? Why not?


texts: How to get a list of locked accounts / check that account is locked?

texts:
I can use following statement to unlock an account:

ALTER USER username ACCOUNT UNLOCK

But which statement can I use to verify that account is currently locked out?


texts: Compare and auto fix conflicts between two databases T-sql

texts:
Consider we have a database for an windows application it's for 2 years ago, now we have changed many things in database so there is differences and we don't know what are the differences.

I don't want to use compare tools like Red Gate compare tool.

What's the best way to see new columns are in new database :

Query result like :

New Db Name | Schema | Table Name | Column Name | Default Value | IsNullable | DataType
NewDb       | dbo    | myTable    | newColumn   | ''            | No         | nvarchar(30)

Thank you

Edit :
I need a query after this to fix the conflicts so both databases be the same :

Add NewDb columns to OldDb,delete OldDb columns that are not in NewDb and update all columns to NewDb version ( if two columns datatype or something is different in two databases. set the NewDb's to OldDb too.)


texts: I have SSMS 2012 installed on my laptop, and SQL Server 2012 on my hosting, but when i try to schedule a sql job i didn't find "SQL Server Agent" in the left side list!!?


texts: SSIS Packages get stuck in SQL Agent jobs

texts:
We run about a dozen scheduled SQL Agent jobs which run SSIS packages.

Intermittently, every scheduled job will get stuck in a running state. i.e. we'll arrive in the morning and notice that there's a bunch of jobs still running. Sometimes we don't actually notice it for days. Further investigation seems to indicate that the package never starts:

DTEXEC process has 0 or very little CPU time
No logging or data activity occurs in the database

There is no loss of functionality in the actual database server, we don't notice anything (except stale data) until we check SQL Agent status, or start a job and it doesn't finish

We can stop and restart the jobs and they simply get stuck again
SQL Commands and CMDEXEC steps still work fine.

One job's first step copies a file with XCOPY then runs a package. The job completes the file copy then gets stuck on the package

There are a few customisations

Most packages are run from a master package, but the issue occurs for standalone packages
We have a custom built tool which queries and starts jobs via T-SQL but this is not in use
We use a framework called BIPA for our logging

Looking in the SQL Server logs, there seem to be a lot of AD type windows authentication errors. However these happen all the time regardless of the SQL Agent issue

We don't use the SSIS service. Packages are run from the file system using the SSIS step.

I can't think of anything further to troubleshoot at this stage. Does anyone have any ideas?

EDIT:

If you're interested I narrowed this down to our SSIS framework which is called 'BIPA'. I deleted the BIPA logging addin and we no longer have this problem. Based on the .net wait's happening in SSIS I believe it just couldn't instantiate the provider somehow. Why it only does this at night I don't know.

Strange thing is it does run fine when run interactively from DTEXEC (i.e. in a batch file) but won't run in SQL Agent (as a different user of course)


texts: HIVE + understanding the hive-metastore logs

texts:
we have HDP cluster version - 2.6.4 , and we are runs spark streaming app

and we are uses presto cluster in order to run Hive queries

when we look on the hivemetastore logs ( under /var/log/hive ) , we can see the following warnings , that repeat many times in the log

2020-01-23 16:39:37,182 WARN  [pool-5-thread-200]: conf.HiveConf (HiveConf.java:initialize(3093)) - HiveConf of name hive.log.dir does not exist
2020-01-23 16:39:37,182 WARN  [pool-5-thread-200]: conf.HiveConf (HiveConf.java:initialize(3093)) - HiveConf of name hive.log.file does not exist
2020-01-23 16:39:37,271 WARN  [pool-5-thread-200]: conf.HiveConf (HiveConf.java:initialize(3093)) - HiveConf of name hive.log.dir does not exist
2020-01-23 16:39:37,271 WARN  [pool-5-thread-200]: conf.HiveConf (HiveConf.java:initialize(3093)) - HiveConf of name hive.log.file does not exist
2020-01-23 16:42:37,156 WARN  [pool-5-thread-200]: conf.HiveConf (HiveConf.java:initialize(3093)) - HiveConf of name hive.log.dir does not exist
2020-01-23 16:42:37,157 WARN  [pool-5-thread-200]: conf.HiveConf (HiveConf.java:initialize(3093)) - HiveConf of name hive.log.file does not exist
2020-01-23 16:42:37,239 WARN  [pool-5-thread-200]: conf.HiveConf (HiveConf.java:initialize(3093)) - HiveConf of name hive.log.dir does not exist
2020-01-23 16:42:37,239 WARN  [pool-5-thread-200]: conf.HiveConf (HiveConf.java:initialize(3093)) - HiveConf of name hive.log.file does not exist

and

2020-01-23 16:15:05,827 WARN  [pool-5-thread-110]: metastore.ObjectStore (ObjectStore.java:getDatabase(698)) - Failed to get database global_temp, returning NoSuchObjectException
2020-01-23 16:15:08,252 WARN  [pool-5-thread-110]: metastore.MetaStoreDirectSql (MetaStoreDirectSql.java:executeWithArray(1741)) - Failed to execute [select "PARTITIONS"."PART_ID" from "PARTITIONS"  inner join "TBLS" on "PARTITIONS"."TBL_ID" = "TBLS"."TBL_ID"     and "TBLS"."TBL_NAME" = ?   inner join "DBS" on "TBLS"."DB_ID" = "DBS"."DB_ID"      and "DBS"."NAME" = ? inner join "PARTITION_KEY_VALS" "FILTER1" on "FILTER1"."PART_ID" = "PARTITIONS"."PART_ID" and "FILTER1"."INTEGER_IDX" = 1 where ((cast("FILTER1"."PART_KEY_VAL" as decimal(21,0)) > ?))] with parameters [profiles_super_spammers, default, 20191223]

and

2020-01-23 15:44:49,420 WARN  [main]: metastore.MetaStoreDirectSql (MetaStoreDirectSql.java:runTestQuery(265)) - Self-test query [select "DB_ID" from "HIVE".""DBS"] failed; direct SQL is disabled

and from log - hiveserver2.log we get

2020-01-23 15:45:18,180 ERROR [main]: metastore.RetryingHMSHandler (RetryingHMSHandler.java:<init>(81)) - HMSHandler Fatal error: MetaException(message:Version information not found in metastore. )
    at org.apache.hadoop.hive.metastore.ObjectStore.checkSchema(ObjectStore.java:6933)

and

Caused by: java.lang.RuntimeException: java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient
    at org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:569)

and

2020-01-23 15:45:18,180 ERROR [main]: metastore.RetryingHMSHandler (RetryingHMSHandler.java:<init>(81)) - HMSHandler Fatal error: MetaException(message:Version information not found in metastore. )

we are get lost from here because we not sure if all warning in the log are complain about wrong HIVE configuration

I will appreciate to get hint or help about the logs and what is the direction


texts:
Here is my SQL (with explain):

EXPLAIN
SELECT * FROM
GBI gbi
INNER JOIN VF_KRED kred ON
    gbi.vendor = kred.vendor;

Giving below the outcome:

Now, the select SQL takes ~10 seconds to produce the outcome.

Below are the indexes on tables:

VF_KRED table:

GBI table:

Any clues?


texts: Migrating from SQL Server to MySQL using MySQL Workbench tool

texts:
I'm trying to migrate few tables from SQL Server to MySQL using MySQL Workbench migration wizard. All work fine for structure migrations but when I go to the data migration section it throws an error for one table:

  ERROR: dbo.Documents:SQLExecDirect(SELECT [DocumentID],
  [CategoryID], CAST([DocumentName] as NVARCHAR(255)) as [DocumentName],
  [Active], [NavigatorID], CAST([DocumentText] as NTEXT) as
  [DocumentText], [UseSubtitle], CAST([DocumentSubtitle] as
  NVARCHAR(255)) as [DocumentSubtitle], CAST([DocumentPlainText] as
  NTEXT) as [DocumentPlainText], [DocumentType], CAST([DocumentLink] as
  NVARCHAR(255)) as [DocumentLink], [Sitemap], CAST([SubtitleImage] as
  NVARCHAR(255)) as [SubtitleImage], CAST([MetaTags] as NVARCHAR(8000))
  as [MetaTags], CAST([MetaDescription] as NVARCHAR(8000)) as
  [MetaDescription], [AccessLevel] FROM [ctool_test].[dbo].[Documents]):
  42000:1131:[Microsoft][ODBC SQL Server Driver][SQL Server]The size
  (8000) given to the convert specification 'nvarchar' exceeds the
  maximum allowed for any data type (4000).

  2131:[Microsoft][ODBC SQL Server Driver][SQL Server]The size (8000)
  given to the convert specification 'nvarchar' exceeds the maximum
  allowed for any data type (4000).

Based on that what I can understand it limits columns with nvarchar data to max size of 4000 when MySQL can handle 65535.

Any clue how I can get this to work?

Thanks


texts: Create SQL login to multiple instances at once

texts:
I have a AD group that needs to be added to multiple SQL instances, I am wondering is there a quicker way of adding this group at once rather than having to login to each server and creating a login in SQL?

Thanks!


texts: How to combined table 2 value in table 1 whare id field is same?


texts: SQL Query For Displaying 'No Data Found' for values containing null and 0 along with other records

texts:
The following was one of the practice questions in the online course i took on udemy for SQL.

I have a table 'emp' and consider the below names in the query map to their corresponding columns in table. In this table there is a column named 'comm' which is configured to accept number datatype. What i want is to retrieve data from the table in such a way that wherever the value in comm is empty(null) or 0, i want to display 'No data found' and wherever there is a value, it should show that number normally.

I executed the below query and it worked. But i'm looking for another concise and efficient way to write this, maybe using subquery or something else, i don't know.

select ename,sal,job,'No data found'as comm from emp
where comm is NULL or comm=0
union
select ename,sal,job,to_char(comm) as comm from emp where
comm is not null and comm!=0

In the query after union, i had to use to_char to avoid datatype error. Which makes me think that even when we are displaying data using select statements and not inserting/updating the record, the data type of the values in column should be same. Is it ? I was under the impression that since we are displaying data, we can display it however we want it. Please do comment on this also.

Appreciate your suggestions/thoughts here!


texts: What alternatives exist when a table requires too many foreign keys?

texts:
We have a base table that defines parts and holds information like part number, description, price, weight, etc.  We also have approximately 400 tables that reference the base table and provide additional information about the parts based on their type / category.

We started by using foreign key constraints so that a part cannot be deleted from the base table if it is being referenced in one of the 400 part specific tables but we quickly reached the maximum 253 recommended foreign keys for SQL Server 2005.

Are there any alternatives to foreign keys in this situation that will ensure data integrity?  We haven't seen performance issues when accessing the data but updating an existing part in the base table will fail as the query plan is too complex.


texts: Connect to SQL Server 2012 Database from 2008 Agent Job containing 2008 SSIS Package

texts:
I'm planning the rollout of SQL Server 2012. My client has a number of SQL Server Integration Services (SSIS) packages currently deployed to SSIS 2008 and consumed by SQL Server Agent Jobs defined on an instance of SQL Server 2008.

The SSIS packages (and the databases they connect to) will be upgraded to SQL Server 2012 over several months.

If the packages remain on the SSIS 2008 (consumed by the SQL Server Agent Jobs on the SQL Server 2008 instance), can the 2008 instance Agent connect to the 2012 server?


texts: SQL Server Upgrading Compatibility Level from 2005 to 2012

texts:
We have old database in SQLServer 2012 Enterprise in Compatibility mode: 90 - SQL Server 2005 . What are the consequence of changing database compatibility mode from 90 into 110-SQL Server 2012? Many legacy applications utilize the database.
Nothing is breaking in QA automated unit and integration tests, however trying to review potential items which could be missing.

I understand backward compitability has issues, changing database from 110- to 90, eg any queries applying newer syntax or tempdb will fail.

However what kind of issues can occur going from 90 to 110? What items can break?


texts: Retrieve parent and child data using child id

texts:
I have a parent and child table, I am able to retrieve all data using UNION SELECT.

Parent Table:

id | parent_name
 1 | Parent A
 2 | Parent B

Child Table:

id | parent_id| child_name
 1 |        1 | Child A
 2 |        2 | Child B
 3 |     NULL | Child C

Using this query (http://sqlfiddle.com/#!9/f9f1c6/1)

Select parent.child_id, parent.parent_id, parent.child_name from Child c
RIGHT JOIN (
  Select NULL as child_id, parent.id as parent_id, parent_name as child_name
  from parent
  Union
  Select child.id as child_id, NULL as parent_id, child_name
  from Child
) parent on parent.parent_id = c.parent_id;

I get this result:

child_id| parent_id|         name|
   NULL |        1 |    Parent A |
      1 |        1 |     Child A |
   NULL |        2 |    Parent B |
      2 |        2 |     Child B |
      3 |     NULL |     Child C |

This is where I'm kinda lost.
I want to retrieve a child that will still include the parent data.

Using WHERE ChildId = '1'; outside of join.

Expected result:

child_id| parent_id|        name |
   NULL |        1 |    Parent A |
      1 |        1 |     Child A |


texts: Is it better to distribute a large database over several dbf files than over a single one?


texts: Display department name,the number of employees and their names

texts:
I have two tables (employees) (departments)

I want to generate the following output:

num_of_employees   department       employee_name
10                 marketing            john
10                 marketing            mark

so on and so forth.

I tried using this`

select count(employee_ID) as "num of employees", department_name, employee_name
from employees e, departments d
where e.dept_id = d.dept_id
group by department_name, employee_name

Without adding the emp name I get the correct solution, but with the employee I get 1 in the num of employees


texts: how to resolve ELFCLASS32 error in mysql for UDF lib_mysqludf_sys.so

texts:
I'm trying to install lib_mysqludf_sys.so in mysql to create sys_exec function which will run an external java programm ..

first of all i issued this command :

CREATE FUNCTION sys_exec RETURNS INT SONAME 'lib_mysqludf_sys.so';

and i got this error :

ERROR 1126 (HY000): Can't open shared library 'lib_mysqludf_sys.so' (errno: 0 /usr/lib/mysql/plugin/lib_mysqludf_sys.so: wrong ELF class: ELFCLASS32)

this error is due to the 64 bit os...

i'm using 64 bit Ubuntu and this library file is of 32 bit.

I'm not getting the library of 64 bit..

I have gone trough this :

gcc -Wall -m64 -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o /usr/lib/lib_mysqludf_sys.so -fPIC

this is also giving error that file not present ..how should i resolve this


texts: Greenplum : Inserting non-duplicate rows on unique constraint violation

texts:
I have a stored procedure that does the following :

INSERT INTO schema.my_unique_values
     SELECT DISTINCT id, value
       FROM schema.a_huge_table
      WHERE NOT EXISTS (SELECT 1
                          FROM schema.my_unique_values)

In summary, this query will insert a unique rows into the my_unique_values table from the a_huge_table.

The problem I have is that when I run this query concurrently in a multi-threaded script for a reason, it can insert duplicate rows. I have tried using serializable to avoid this 'phantom reads', but still no luck. My idea is to allow Postgres to only insert non-duplicate rows on unique constraint violation. But is this possible ?
My current experience is that when a unique constraint violation is hit, it will cancel the whole transaction, so it will not insert the non-duplicate rows. How can I achieve my goal ?

Note: I am using Greenplum 4.3.11 that is using Postgres 8.2, therefore there are limitation on the query that I can use.

Thanks..


texts: What is best way to move large number of existing indexes into new file-group - DB Size: 700GB

texts:
I have requirement to move all the indexes into different storage space (file-group) within existing SQL Server 2014 Database (Current Size: ~700GB)

I'm planning for approach where i can drop existing indexes and recreate them on new file-group. I prepared a script to do the same activity (re-creating indexes one by one) with cursor, but after executing the script, my database is very slow, and it's running for more than 20 hours.

Any advise/recommendations to accomplish this activity in production database where it's got to manage more than 1,000 users requests.


texts: How do I organize database initialization so it can be executed in a fixed order?

texts:
I am trying to update an established database system to leverage change management. The main point is to make the development and production environments follow the same process. I need help understanding how to group database elements into change sets, and which order to apply them in.

In my current design, change sets are grouped into roles, schemas, objects, and grants. objects is a catch-all for tables, indexes, sequences, views, etc.

Here is a sample directory layout:

roles/DATE-admin
roles/DATE-appA
roles/DATE-appB
schemas/DATE-schemaA
schemas/DATE-schemaB
objects/DATE-foo
objects/DATE-bar
grants/DATE

My current design applies any outstanding change set from roles, then schemas, then objects, then grants. The change sets which have not been applied from each group would be applied to the target database, and a helper script would be supplied to engineers to assist database setup. Liquibase tracks applied changes for me. (In production, roles would be managed via control panel, but they exist here for developer convenience.)

Is there a better grouping and order of operations that I can use to future-proof this design? Is this rigid order of operations reliably scriptable?


texts:
I have this query:

SELECT *
FROM (
  SELECT v.*,
         ROWNUM recnum
  FROM (
    SELECT hostaddress,
           modulename,
           severity,
           escalation,
           classification,
           description,
           remarks,
           count(remarks) AS tally,
           max(entrydate) AS lastupdate,
           min(entrydate) AS firstupdate
    FROM vw_ericsson_summary
    GROUP BY hostaddress,
             modulename,
             severity,
             classification,
             escalation,
             description,
             remarks
    ORDER BY lastupdate ASC
  ) v
)
WHERE ROWNUM BETWEEN 1 AND 100

This works okay. but when rownum is greater than 100 such as 101, it won't return records anymore.

SELECT *
FROM (
  SELECT v.*,
         ROWNUM recnum
  FROM (
    SELECT hostaddress,
           modulename,
           severity,
           escalation,
           classification,
           description,
           remarks,
           count(remarks) AS tally,
           max(entrydate) AS lastupdate,
           min(entrydate) AS firstupdate
    FROM vw_ericsson_summary
    GROUP BY hostaddress,
             modulename,
             severity,
             classification,
             escalation,
             description,
             remarks
    ORDER BY lastupdate ASC
  ) v
)
WHERE ROWNUM BETWEEN 101 AND 200;

Although records are actually greater than 100, (it's 128). I'm using Oracle for Toad.

Is there any other way of returning s specified row number from a selection such as above?


texts:
I am trying to write a query where I have to calculate number of visits for a customer by taking care of overlapping days. Suppose for itemID 2009 start date is 23rd and end date is 26th therefore item 20010 is between these days we will not add this purchase date to our total count.

Example Scenario:

Item ID Start Date   End Date   Number of days     Number of days Candidate for visit count
20009   2015-01-23  2015-01-26     4                      4
20010   2015-01-24  2015-01-24     1                      0
20011   2015-01-23  2015-01-26     4                      0
20012   2015-01-23  2015-01-27     5                      1
20013   2015-01-23  2015-01-27     5                      0
20014   2015-01-29  2015-01-30     2                      2

OutPut should be 7 VisitDays

Input Table:

CREATE TABLE #Items
(
CustID INT,
ItemID INT,
StartDate DATETIME,
EndDate DATETIME
)

INSERT INTO #Items
SELECT 11205, 20009, '2015-01-23',  '2015-01-26'
UNION ALL
SELECT 11205, 20010, '2015-01-24',  '2015-01-24'
UNION ALL
SELECT 11205, 20011, '2015-01-23',  '2015-01-26'
UNION ALL
SELECT 11205, 20012, '2015-01-23',  '2015-01-27'
UNION ALL
SELECT 11205, 20012, '2015-01-23',  '2015-01-27'
UNION ALL
SELECT 11205, 20012, '2015-01-28',  '2015-01-29'

I have tried so far:

CREATE TABLE #VisitsTable
    (
      StartDate DATETIME,
      EndDate DATETIME
    )

INSERT  INTO #VisitsTable
        SELECT DISTINCT
                StartDate,
                EndDate
        FROM    #Items items
        WHERE   CustID = 11205
        ORDER BY StartDate ASC

IF EXISTS (SELECT TOP 1 1 FROM #VisitsTable)
BEGIN

SELECT  ISNULL(SUM(VisitDays),1)
FROM    ( SELECT DISTINCT
                    abc.StartDate,
                    abc.EndDate,
                    DATEDIFF(DD, abc.StartDate, abc.EndDate) + 1 VisitDays
          FROM      #VisitsTable abc
                    INNER JOIN #VisitsTable bc ON bc.StartDate NOT BETWEEN abc.StartDate AND abc.EndDate
        ) Visits

END

--DROP TABLE #Items
--DROP TABLE #VisitsTable


texts: Loop through list of fields, check against lookup table

texts:
I have a list of fields:
FIELD_DOMAIN_ENG_VW
+-------------+------------+-------------+
| TABLE_NAME  | FIELD_NAME | DOMAIN_NAME |
+-------------+------------+-------------+
| ENG.TABLE_1 | FIELD_1    | DOMAIN_ABC  |
| ENG.TABLE_1 | FIELD_2    | DOMAIN_XYZ  |
| ENG.TABLE_2 | FIELD_1    | DOMAIN_XYZ  |
+-------------+------------+-------------+

The view looks at all the tables in a geodatabase, and lists any fields that have a domain associated with them (a domain is the GIS equivalent of a lookup table/validation table).
The underlying tables look like this:
TABLE_1
+--------------+--------------+
| FIELD_1      | FIELD_2      |
| {DOMAIN_ABC} | {DOMAIN_XYZ} |
+--------------+--------------+
| A            | X            |
| B            | Y            |
| C            | zzzz         |
| BLACK SHEEP  |              |
+--------------+--------------+

TABLE_2
+--------------+--------------+
| FIELD_1      | FIELD_2      |
| {DOMAIN_XYZ} |              |
+--------------+--------------+
| Z            | ...          |
| Y            |              |
| X            |              |
| asdf         |              |
+--------------+--------------+

The domains look like this:
DOMAIN_VALUES_VW
+------------+------+-------------+
| DOMAIN     | CODE | DESCRIPTION |
+------------+------+-------------+
| DOMAIN_ABC | A    | EH          |
| DOMAIN_ABC | B    | BEE         |
| DOMAIN_ABC | C    | SEE         |
+------------+------+-------------+
| DOMAIN_XYZ | X    | EX          |
| DOMAIN_XYZ | Y    | WHY         |
| DOMAIN_XYZ | Z    | ZEE         |
+------------+------+-------------+

The source is an xml column in a single system table; I've extracted all the domains into this view.
Question
For validation purposes, I have made a query that will check if there are values in a field that do not match the corresponding domain:
INSERT INTO ENG.CV_ERRORS
(TABLE_NAME, FIELD_NAME, ERROR)
SELECT
    'TABLE_1' AS TABLE_NAME
    ,'FIELD_1' AS FIELD_NAME
    ,FIELD_1 AS ERROR
FROM
    ENG.TABLE_1
    LEFT JOIN
        (
        SELECT CODE
        FROM  INFRASTR.D_CV_ENG_VW
        WHERE DOMAIN = 'DOMAIN_ABC'
        )
        ON FIELD_1 = CODE
WHERE
    FIELD_1 IS NOT NULL
    AND CODE IS NULL

+------------+------------+-------------+
| TABLE_NAME | FIELD_NAME | ERROR       |
+------------+------------+-------------+
| TABLE_1    | FIELD_1    | BLACK SHEEP |
+------------+------------+-------------+

However, this query is hardcoded to be run on a single field, in a single table at a time. I need to check all of the fields with domains, in all of the tables in the database - programmatically.
How can I do this? I'm pretty sure this can be done with PL/SQL and dynamic SQL, but I'm so new to PL/SQL that it is proving to be rather difficult.


texts: How can Amazon RDS grant CREATE EXTENSION to their RDS_SUPERUSER role?

texts:
I've been trying for some time to allow ordinary, non-superuser users to create extensions.
While reading across RDS documentation, I find that their internal "master user", rds_superuser, is capable of creating extensions, even though it is not an actual superuser, as is well documented.
Further searching leads me to this table:
|Database Engine|System privilege|Database role|
|---------------|----------------|-------------|
|PostgreSQL     |`CREATE ROLE, CREATE DB, PASSWORD VALID UNTIL INFINITY, CREATE EXTENSION, ALTER EXTENSION, DROP EXTENSION, CREATE TABLESPACE, ALTER < OBJECT> OWNER, CHECKPOINT, PG_CANCEL_BACKEND(), PG_TERMINATE_BACKEND(), SELECT PG_STAT_REPLICATION, EXECUTE PG_STAT_STATEMENTS_RESET(), OWN POSTGRES_FDW_HANDLER(), OWN POSTGRES_FDW_VALIDATOR(), OWN POSTGRES_FDW, EXECUTE PG_BUFFERCACHE_PAGES(), SELECT PG_BUFFERCACHE`|RDS_SUPERUSER

When I try executing grant create extension to any user, I get a syntax error.
How is this grant possible?


texts: Disabled SSL on SQL Server, now connection fails

texts:
I have a customer that is worried about the latest SSL vulnerability POODLE. They have a utility (IIS Crypto 1.4) that disables SSL 2.0-3.0 on the servers they run it on. They have disabled SSL on both the server my service is running and the remote SQL Server. The SQL connection string is pretty standard:

Server=myServerIPAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;

When my service starts it tries to perform a Linq to SQL query and gets the error:

  A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

If they re-enable SSL on the SQL Server it works fine. My program is a Windows Service built on .NET 3.5. What is the cause of this error, and where can I make adjustments so my service will run?

edit:
below is the stacktrace that is spit out by my program when it tries to load

System.Data.SqlClient.SqlException: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean& marsCapable)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Linq.SqlClient.SqlConnectionManager.UseConnection(IConnectionUser user)
at System.Data.Linq.SqlClient.SqlProvider.get_IsSqlCe()
at System.Data.Linq.SqlClient.SqlProvider.InitializeProviderMode()
at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
at System.Data.Linq.DataQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
at System.Linq.Enumerable.ToArrayTSource(IEnumerable`1 source)


texts: Some book suggestions for performance tuning

texts:
I am in the process of improving performance on our database application. While I am not a DBA, I am pretty comfortable with SQL. I am after a book that can help me understand how queries written in different ways affect performance and also   understanding things like table scans, indexes  an statistics.

I don't just want to blindly add everything the tuning advisor says so I am looking for the knowledge required to assess these recommendations in the context our environment and how to fully utilise the tools available.  I am working with MSSQL2008+ so a book the utilises this environment specific would be good.

Thanks in advance


texts: Create table with variable name and fixed columns in Postgres

texts:
I need to create a table daily with different names but the same set of columns. How can I do that? I find this

do $$ begin execute format('create table "table_%s_pk" (i int)',now()::date); end; $$;

which creates the table but I don't know how to include column names in this query as well.


texts: How to use DAY/WEEK/MONTH/YEAR constant as parameter in stored procedure?

texts:
Assume I have a table looks like this:

Scores {
    Id uniqueidentifier,
    ScoredAt datetime,
    Value int
}

Basically I want to create a stored procedure that works similar to this DATEDIFF(DAY, @day, GETDATE()) which can use DAY/WEEK... as parameter. This is what i did:

CREATE PROCEDURE GetHighScores
    @scope int --- <<== THIS GUY HERE
AS
BEGIN
    SELECT *
    FROM Honor.Scores
    WHERE DATEDIFF(@scope, Honor.Scores.ScoredAt, GETDATE()) = 0
END
GO

What do I have to put into GetHighScores parameter so that I can do this:

EXEC GetHighScores(MONTH)

As far as I know, MONTH/WEEK/DAY.. those are not actually a value but more of a tricky macro. But that guy DATEDIFF can use it, why couldn't I?

Anyone have any idea to get this done?


texts: Please explain why deadlock is detected in my example

texts:
I think Deadlock will happen when T1 lock tabA and T2 lock tabB. Then querying T1 to tabB (locked by T2) and T2 querying to tabA (locked by T1). So T1 and T2 will wait and wait till end of life!
But in my example: Running T2 after executing T1, T2 will wait till T1 is committed. Then running T3 (maybe T1's lock had been released by some special reasons that I don't know because T2 executed complete). So, why deadlock happen at T3? I can't understand. T3 should wait till T2 is committed (like T2 waiting T1 is committed) or T1's lock is not released yet or another reason...?

drop table TabA;
create table TabA (a int, b int);
insert into TabA values (1,11);
insert into TabA values (2,22);
insert into TabA values (3,333);
/
select * from TabA;
/
--T1. Run T1 first.
begin tran t1
insert into TabA values (5,55);
update TabA set b = 44 where a=4;
select * from TabA where a=4;
waitfor delay '00:00:10';

--T2. Run T2 after T1 (run continuously, it must after cooldowning 10s).
--T2 is still waiting T1 is committed.
begin tran t2
insert into TabA values (4,444);
update TabA set b = 111 where a=1;

--T3. Run T3 after T1 is completed.
--Deadlock is detected at there and T2 is executed complete.
select * from TabA where a=1;

I use 2 MS-SQL 2008 R2 client (test with my friend using 2014 is okay). T1 and T3 is belong to client #1 and T2 is belong to client #2:

Client #1:
--T1
--T3
Client #2:
--T2

Ordinarily query by: T1>T2>T3.
//I will find and try with another PC using MS-SQL 2008 R2 later.


texts: How do I know if my PostgreSQL is up do date with DST rules?

texts:
Recently (2019-04-25) brazilian government ended the use of daylight savings time all around the country.

In my current project I rely on PostgreSQL to do timezone conversions and analytics based on time from different time zones and dates.

As far as I am concerned PostgreSQL implements daylight savings and timezones through Olson database.

1) Is PostgreSQL (version 11, for instance) up to date with the new no daylight saving time rule in Brazil?

2) If it is not updated, how can I fix it? Is there any default PostgreSQL patch calendar/schedule in that sense?


texts: Log Reader replication agents are constantly restarting

texts:
I am recreating (push) transactional replication from a SQL 2000 instance to a SQL 2008 instance which I had to break in order to do a db migration. The 2008 instance is both the distributor and a subscriber.

About 1/2 of my subscriptions are failing with

  Could not find stored procedure 'sp_MSins_dboTABLE'.

(more info):

  Command attempted: if @@trancount > 0 rollback tran (Transaction
  sequence number: 0x0002A2B400008D19000100000000, Command ID: 20)

  Error messages: Could not find stored procedure
  'sp_MSins_dboTABLE'. (Source: MSSQLServer, Error number: 2812)
  Get help: Could not find stored procedure
  'sp_MSins_dboTABLE'. (Source: MSSQLServer, Error number: 2812)
  Get help:
  I've chased just about every path I can find, from running sp_scriptpublicationcustomprocs (I executed the result in the distribution database) to deleting the subscription, then the publication, and then re-creating them.

The working subscriptions were created using the same methodology.

Here are my create scripts:

https://gist.github.com/3152160

Edit:

Update --- My failing subscriptions are also throwing the following error (through Profiler):

  Table already has a primary key defined on it.


texts: How do I modify the user interface language in SSMS?

texts:
How do I modify the user interface language in SQL Server Management Studio?

For instance, if I installed in English, but wish to view in Japanese (to name but one possible example).

I am running this environment in a non-[English-as-a-first-language] environment, and would prefer all my windows to read in my natural language. (my particular natural language isn't really important is it?)


texts:
Result right now I am getting is:

EmployeeNo  Column1   Column2
================================
1           |  NULL       | Column2
1           |  Column1    |   NULL

I want like

EmployeeNo  Column1   Column2
================================
1          | Column1  |   Column2

Kindly provide any help or way to do this

Thanks in advance


texts: MariaDB: Why is this still using temporary and filesort - is it because I'm using a self-join? How to fix?

texts:
SELECT  e2.personId, COUNT(*)

FROM        entries e1
JOIN        entries e2   ON  e2.categoryId   =   e1.categoryId
                        AND  e2.personId    !=   e1.personId

WHERE       e1.personId  =  1

GROUP BY    e2.personId

gives:
id   select_type   table   type   key                   key_length   ref                     rows   extra
1    SIMPLE        e1      ref    personId_categoryId   4            const                   59     Using index; Using temporary; Using filesort
1    SIMPLE        e2      ref    categoryId_personId   4            project.e1.categoryId   8      Using where; Using index

If I remove the GROUP BY I get "Using index".
What's the problem here?  Is it something to do with joining a table onto itself?
(The indexes present on the table are the two shown in the explain output, containing the columns that the names suggest.)
DDL:
CREATE TABLE `entries` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `personId` int(10) unsigned NOT NULL,
  `categoryId` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `personId_categoryId` (`personId`,`categoryId`),
  KEY `categoryId_personId` (`categoryId`,`personId`),
  CONSTRAINT `entries_ibfk_1` FOREIGN KEY (`personId`) REFERENCES `people` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `entries_ibfk_2` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1465605 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


texts: Dropping person while their connection sleeping in SQL Server

texts:
I have a C# application that communicates with SQL Server.

When I try to drop a user that recently had a connection with SQL Server, it gives me an error that I can't delete them because they have connections open in the database.

All SQL queries are appropriately wrapped in using statements but when I run sp_who, it tells me that that person has a connection in a sleeping state, I have researched this and found out that this is normal behavior meant for speed improvements.

To get rid of this connection, I used SqlConnection.ClearPool which works and I can now delete the user but then the queries becomes a decent amount slower, which I expected and know the reasons why it's happening.

What's the best way to deal with this? Force the connections closed only when trying to delete the person somehow? If so, how is this done?

Thanks in advance!


texts: Best way to restore 400GB+ worth of data in MySQL 5.6

texts:
So I'm trying to figure what the best/quickest way would be to refresh a massive amount of data. We're wanting to copy 3 months worth of data from a replication server to a QA server for product testing.

It's one table and I "only" need to refresh the QA server with the last 3 months, then set something up so it does a weekly refresh of the new records. My biggest priority at the moment is getting this 400G worth of data over. Mysqldump would obviously take forever, even if I cut it up. I was thinking about doing 1 week at a time but that's still 150 million rows and probably about 40GB.

It's a reporting server, so the table only gets added to, none of the data gets updated or changed. The table is innodb and binlong_format is set to row and logs are expired every 4 days on the host. The destination server is a VM with 16G of memory, innodb_buffer_pool_size = 8G, and innodb_flush_log_at_trx_commit = 2

I could probably go through the backups and get the previous binlogs, but is there even a way to pull out the data for just that one table? Am I destined to spending days on end dump/restoring this data?


texts: SQL Server 2008 R2 warning when tried to execute the script of size approx 500MB

texts:
When I tried executing a SQL script, which was of 500MB approx. Management Studio throws an error

  Insufficient memory to continue the execution of the program

My system memory is 8GB and only 3GB is free. I am using a .bak file. I even tried SQLCMD but it's not working. Any other solution for this?

I faced this issue when I was trying to restore a database using backup of higher version (SQL Server 2014) to a lower version (SQL Server 2008 R2) using scripting method.

The compatibility level of the SQL Server 2014 database is 120.


texts:
I've normalized a single table to reduce the number of nullable columns from 3 down to 0. The idea was to save a little drive space but I'm seeing a 45% decrease in space needed to store the same amount of data between the two schemes but it just seems too good to be true.

Am I calculating or going about this incorrectly?

First I'm using this query to determine size of the tables, so its possible this is the problem.

There is a total of 88324 rows. 1187 are considered root posts and have values for Title, ProductId, TotalViews and have a NULL for ParentPostId. 87137 are considered replies and have NULL for Title, ProductId, TotalViews and have a value for ParentPostId.

At any rate the query, linked to above, outputs the following results in Kilobytes which equates to a 45% improvement which seems to big of an improvement to me.

+---------+--------+
|Current  |Future  |
+---------+--------+
|94,936   |42,784  |
+---------+--------+

My table structure went from a current state of:

to future state of:


texts: There uesd to be a directory called pg_xlog that stored all of the WAL logs in PostgreSQL? Part of restoring a basebackup under the archiving scheme required me to copy the WAL into DATA_DIR/pg_xlog. What happened to this directory?


texts:
This might be some ambiguous but I have not much info about this so I need some start point;

As a general idea; in conventional RDBMS', there are a DB engine and storage of data. We use the language 'SQL' to access these datas on the DB. To access datas, is there other ways different from using SQL queries? Can we 'fire' events of DB engine without using SQL and get the datas in the storage? If yes, how?


texts: Missing rows under read committed isolation

texts:
It's well known that Read Committed isolation is prone to different anomalies. I read The Great Paul White's series on isolation levels. The post relevant to the discussion is this one:

The Read Committed Isolation Level

It states (again, it's reasonably well known), that a statement running under read committed isolation:
Can encounter the same row multiple times;
Can miss some rows completely;

My question is about the 'missing rows' part. Examples that talk about missing rows usually demonstrate the problem using queries like:
select count(*) from table.
My question is can the rows be missed in a 'regular' select query? Meaning, can a query like
select * from table
or even
select * from table where id = @id
also miss rows that are committed before the start of that query?
This question applies to Read committed with locking (not RCSI) only, as RCSI doesn't allow these types of anomalies.


texts: Add shared folder to AlwaysOn AG Failover

texts:
We have setup a 2 node Synchronous AG with automatic failover to provide HA for our ERP application (Dynamics GP)

The GP desktop client loads a number of shared reports and dictionaries from a file share currently located on one of the database servers.  In order to provide complete high availability this share needs to be available in the event of a failure of the primary replica.

At first I thought I would just copy the folder and setup shares on the secondary replica but realized SMB is not accessible through the AG listener.

My second thought was to move the share to another location however this still introduces a single point of failure and defeats the purpose of HA.

My only 2 real choices are to somehow add the folder as a resource to the cluster, however the 2 nodes are virtual machines and creating a shared SAN disk would be complex

Or a messier option would be to create a task that checks the server name and in the event of a failover would update DNS to direct clients via UNC to the correct server share.

What would be the recommended approach for this?


texts:
Is it possible in a three-member-replica set (1 primary, 2 secondaries) to update records on the primary, or even delete several records and insert the updated data, and still execute read-ops on the secondaries by setting the read-preference to secondary?

I do know that all write-ops will only go to the primary and will be replicated by the secondaries but I do not know if it is possible to delay the replication or even better to replicate one secondary and after it is finished to replicate the other one.

Thank in advance


texts:
I have a table (9mm records) with a field called filename nvarchar(max)(I didn't decide on this datatype and can't change it).

I'm wanting to count how many records meet the following criteria: Where filename like 'ATT000%'. The results are only 8k records. I thought about adding a filtered index, but of course you can't do that with LIKE. So instead I went with

WHERE FileName >= 'ATT000'
AND filename < 'ATT001'.

I receive the same record count. However, when I add that filtered index SQL refuses to use that index and still goes back to use the clustered index when I use that same criteria (copy and paste) from the index filter.

Its obviously WAY smaller and it works fine if I force the index hint. But I can't force it since this is a vendor application.

Any idea why I can't get SQL to use that index?


texts: When will SQL Server automatically use a supporting indexed view?

texts:
I have the following expensive query that runs against our data warehouse:

SELECT
    d.DateYear,
    COUNT_BIG(*) AS WidgetCount
FROM Widgets AS w
JOIN Dates AS d
    ON d.DateId = w.DateId
WHERE w.TypeId IN (1, 2)
GROUP BY
    d.DateYear

I've discovered that I can hugely increase the performance of this query with the following indexed view (see also: Using indexed views for aggregates - too good to be true?)

CREATE VIEW IX_Widgets
WITH SCHEMABINDING
AS
    SELECT
        TypeId,
        DateId,
        COUNT_BIG(*) AS WidgetCount
    FROM Widgets
    GROUP BY DateId
GO

CREATE UNIQUE CLUSTERED INDEX IX_Widgets ON IX_Widgets
(
    TypeId,
    DateId
)

The best bit is that SQL Server starts using this indexed view even though its not referenced in the original query - this is really nice for a data warehouse as it means that queries can be written against a well-defined reporting schema, and then supported by crazy (but super efficient) indexed views that do weird groups.

The frustrating thing is that as soon as I change the typeIds that I am filtering by or parameterise the TypeId values (e.g. WHERE w.TypeId IN (@typeId1, @typeId2, ...)) SQL Server stops using the indexed view and reverts back to a really slow query.

What decides when SQL Server will use an indexed view? I've experienced similar problems in the past where it is difficult to determine when SQL Server will and won't use an indexed view. It would be great to be able to use this without having to change my query to explicitly use this view, but only if I know with certainty that its going to perform.


texts: how to get History of queries executed with username in SQL

texts:
I can get executed queries by using:

SELECT deqs.last_execution_time AS [Time]
    ,dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

but I am also looking for username column, who executed these queries.


texts: How does getting the 5 more and 5 less expensive prices of the avg of all prices work?

texts:
I am currently doing a few exercises on SQL. In one exercise on was supposed to find the 5 more and 5 less expensive products to a mean of all products.

The code below is working and giving the right result, however I am not understanding, how the 5 more and 5 less expensive products to the mean are found.

I assume: The where clause generates a triangle of all pairs of prices with itself and looks, which "rows" have euqal or less than 5 elements in them.

In fact there are generated two triangles, (5 for the more expensive, 5 for the less expensive).

Is this correct? What happens step by step?

SELECT p1.price, COUNT (*) as Distance_to_avg

FROM

( SELECT DISTINCT price FROM product )

AS p1 ,

( SELECT DISTINCT price FROM product )

AS p2

WHERE p1. price <= p2. price

AND p2. price <= ( SELECT AVG ( DISTINCT price ) FROM product )

OR p1. price >= p2. price

AND p2. price >= ( SELECT AVG ( DISTINCT price ) FROM product )

GROUP BY p1. price

HAVING COUNT (*) <= 5

UNION

SELECT AVG ( DISTINCT price ), 0 FROM product

ORDER BY price


texts: Moving a SQL Cluster to Another Data Center w/No Downtime

texts:
We have a two node SQL 2008 Enterprise cluster in a data center in California, and are looking to move hosts from a leased environment to a colo environment much closer to our home office.  Our cluster is quite busy with around 600GB of live data, so we're looking for a way to migrate with as little downtime as possible.

This is the only thing that I believe will be the challenge in the entire environment.

My initial thought was to add a passive node to the SQL Cluster and allow it to slowly replicate over to the other data center.  Once caught up, it wouldn't be hard, in theory, to fail over to this node say overnight and be active, DNS propagated, and so on, by that morning.

Does this sound viable, and if not, are there some other suggestions?

Thanks in advance for your help.

Note that both environments are very beefy resource wise, and one has 100MBps unmetered, while the destination is 1GBps unmetered.  Bandwidth, space, and resources are not a concern.


texts: How do I tell which Edition and Version my SSAS Instance is running?

texts:
In SQL Server on the database engine, you can run a SQL query like this to get the server's version info like Edition and Version, and Update Level, etc.

SELECT @@VERSION

What is the MDX equivalent for querying an SSAS (Tabular or Multidimensional) instance? Are there SSAS DMVs that can get me this answer?

I browsed the Books Online page for SSAS DMVs, but I didn't notice any DMVs that could help me. DISCOVER_INSTANCES didn't seem to have the info.

Let's assume that SQL Server Database Engine is not installed on this same server as the SSAS instance, so I cannot check this by querying the database engine.

In my case, this is for a SQL 2012 Tabular instance, but would like to know how to query Multidimensional instance too.


texts: Hostname configuration for oracle12c on OL7 VM

texts:
I am trying to install Oracle 12c (for learning/studying purposes)on a VirtualBox VM.  i have set up a VM with Oracle Linux 7.4 and when I setup the wireless/networking aspects during installation I decided to change the hostname from:

localhost

to:

OracleLinux7VM

I'm trying to follow along with the Oracle Installation Docuemenation and I'm on the HostsFile configuration section, it states:

"""

For example.

127.0.0.1       localhost localhost.localdomain localhost4 localhost4.localdomain4
192.168.0.215   ol7.localdomain  ol7

Set the correct hostname in the "/etc/hostname" file.

ol7.localdomain

"""

In the terminal on my VM, if I execute:

hostname

it returns

OracleLinux7VM

but if I cat the /etc/hosts file:

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

::1 localhost localhost.localdomain localhost6 localhost6

I tried to understanding this through googling and reading the documentation but I'm not sure how exactly I should configure my /etc/hosts file based on my hostname: OracleLinux7VM based in order to successfully install Oracle 12c.  Any and all guidance would be greatly appreciated.


texts: Index Maintenance on Indexed View using Ola Hallengren Script

texts:
I’m using Ola Hallengren's below script in my environment and it fails with the error:

USE DBA
EXECUTE dbo.IndexOptimize
    @Databases = ‘USER_DATABASES’,
    @FragmentationLow = NULL,
    @FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
    @FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
    @FragmentationLevel1 = 5,
    @FragmentationLevel2 = 30,
    @UpdateStatistics = ‘ALL’,
    @PageCountLevel = 0 ,
    @LogToTable = ‘N’

  Msg 50000, Sev 16, State 1, Line 153 : Msg 2706, Table ‘_abcd’ does not exist. [SQLSTATE 42000]

‘_abcd’ actually is a view and the stored procedure is unable to update statistics on view and it fails.
we've indexes on this view
Please guide me how can I solve this issue


texts: Database not able to startup showing an error in reading control file

texts:
On Windows Server 2008 R2, when I am trying to startup Oracle 11g database it shows an error:

SQL> startup
ORACLE instance started.

Total System Global Area 3206836224 bytes
Fixed Size                  2180024 bytes
Variable Size            1996491848 bytes
Database Buffers         1191182336 bytes
Redo Buffers               16982016 bytes
ORA-00204: error in reading (block 3, # blocks 8) of control file
ORA-00202: control file: 'D:\APP\FROZEN\ORADATA\ORCL\CONTROL01.CTL'
ORA-27070: async read/write failed
OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 1117) The request could not be performed because of an I/O device error.

The disk is online.

Can any one help me how to resolve this issue?


texts: SQL Server - Recursive "find all FK connections for ID through the entire DB" query

texts:
I'm dealing with a DB with hundreds of tables, with interconnecting FK references producing potentially thousands of different permutations for how for example table A and table K can join together. Now, I need to create a query that will be given a single table name and it's PK ID, and then using FK references in the DB would find ALL connections regardless of how far down the line it has to go.

Now, I'm not looking for a script, as I understand this will become quite complex. I'm looking for strategic advice on what would be the best way to create such a script, or tips on where to find previous similar examples.

Currently what I have is a procedure that can find all TABLE + PK_ID values referencing, or referenced by one TABLE + PK_ID. And what I was thinking of doing is building up some kind of a looping TSQL that would use that procedure and a number of #temp tables to find the connections in levels. But it's going to be tricky to make this thing truly recursive.

Basically, say I was looking for the connections to and from table A, it could produce a list logically similar to the one below:

      A - J   I - E
     / \     / \ /
H - B   C - D - F - O
 \ /       /
  G ------/


texts:
We have query as below. What we need is to select particular training with latest date and only single data but now when we run the query below we get more then one results.

SELECT tblTraining.clientTrainingID AS lastID, MAX( tblTraining.trainingDate ) AS lastDate
FROM tblTraining
WHERE tblTraining.trainedID =37
GROUP BY tblTraining.clientTrainingID


texts:
While doing DB operations on PostgreSQL pg_xlog_archive is consuming more than 150GB disk space with the option "archive_mode = on". How to cleanup/reduce pg_xlog_archive folder size.

What will happen if I disable "archive_mode= off". I have tried "off" option also but DB operations are taking too much time. How archive_mode do work?


texts:
I have inherited a large (SQLServer) database with hundreds of columns that represent amounts of one thing or another. The units for these values (e.g. "gallons", "inches", etc) are stored in the MS_Description field of Extended Properties. I am wondering if there is a better way to store this information. I suppose it is fine for documentation purposes, but it would be difficult to make robust unit-conversion calculations based on this data. At this point I am not prepared to make an invasive change, but if I get the chance to do so, what is the recommended Best Practice in this regard? Options, off the top of my head, might include:

Change column name to included units (e.g., "TotalVolumeInGallons". This would make the information a little more readily available, but it still seems weak to me.)
Add a separate "Units" column to correspond to every "Amount" column (this column could be nvarchar OR it could be a foreign key to a separate Units table which might make it easier to calculate unit conversions. On the other hand, adding so many columns could fairly double the size of my database -- with terribly redundant data.)
Create a new field in Extended Properties dedicated specifically for units. (Unfortunately, I don't think this can be a foreign key to a Units table.)
Is there another idea that I'm overlooking?

UPDATE: After reading @Todd Everett's answer, a possible solution occurred to me, so I'm going to go ahead and answer my own question. (See below)


texts: How to add values matching from table B to table A?

texts:
I've been struggling for a while now. Can someone help me?

I have 2 tables.

I have a table 'SKUS_info' with SKUS(a number assigned to a product), approximately about 30k rows.

And the second table 'truckparts_info' with all the data about products. It has 2 million rows. OE_NUMBER is identical to SKU from the first table.

I want to fill the table SKUS_info with the data from the table truckparts_info

What I tried to do:

I wrote a script:

select v.sku, v.net_price t.TTC_TYP_ID, t.CAR_BRAND,t.CAR_MODEL, t.CAR_TYPE,
t.CAR_BODY, t.CAR_OF_YEAR, t.CAR_KW, t.CAR_PM, t.CAR_CC,
t.ART_NAME, t.ART_GROUP, t.PARTS_INFO, t.ART_CROSS
from truckparts_info t inner join skus_info v on t.OE_NUMBER= v.SKU order by v.SKU

But it does exact opposite of what I need. The v.SKU and v.Net_price are being added to the table B and the query result shows 500k rows in the end.

Any ideas how to change the script the way I want?

EDIT:

The script output is:

In this case you can see that SKU, Net_price are added to the table B


texts: How does SSMS connect to a server's database without the instance name?

texts:
I have a question that I am confused about. I am also new to database stuff as well so this question may be basic.

For example, at my work, we use SSMS to connect to our server's databases.

When connecting from SSMS, we enter in (server_name\instance). Like for example PC100\SQLexpress.

But for some, only the server is entered and not the instance along with it. How does this work? I thought in order to connect, we always need to type an instance after the server name?

Like for example, some we need PC100\SQLexpress in order to connect to the server's databases.
And then for others we just need to type in PC200 to connect, and not needing to type the instance.

Can anyone explain?


texts: Enable MySQL trigger for all users in mysql

texts:
I have setup triggers in my MySQL tables and when I applied them they were restricted to the account I used when creating the triggers, now I have multiple users in my MySQL and I want it so that the triggers will work for all users not just for the one I used when creating them. I currently tried this:

CREATE DEFINER=`%`@`%` TRIGGER after_atm_account_insert
    AFTER INSERT ON `system_database`.atm_account
    FOR EACH ROW
BEGIN
    INSERT INTO `log_database`.system_logs
    SET actionDone = 'insert',
        employee = USER(),
        logDate = NOW(),
        tableChange = 'atm_account',
        rowChange = NEW.atmID,
        new_data = concat("Account #",NEW.accountNumber,", Pin:",NEW.pin,", Security Code:",NEW.securityCode,", Withdrawal Limit:",NEW.withdrawalLimit);

END


texts: How long does it take a full text index to update after inserting a large amount of text?

texts:
I'm trying to track down an issue where some searches do not return a hit but do return a hit after some time.

Process. We currently grab some 150,000 pages of text and insert them into our database. After, we then do a search on the text for some keywords. However it would seem that when we do an initial search the FT catalog doesn't find our keywords. But after rerunning the search the key words show up.

So what I'd like to know. How long of a lag is there between the time you do an insert on a table and the result populating in the full text catalog and showing up in a search?

Server Specs

Dual Xeon E5-2670 @2.6ghz

256 gb ram

ssd drives.


texts: MySQL - Merge or split datetime intervals (start date to end date)