texts: Login failure when running a SSIS package from a SQL Server job

texts:
I have a SSIS package that migrates data from a Sybase database to SQL Server. The connection to the source database has been made through an ODBC connection.

If I run the package directly on my machine, it runs successfully, however if I want to run it through a SQL Server job I have created on my local machine (where the destination database is), I'm getting a login failure error:

An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Login failed for user 'xxx\user1'.".  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Cannot open database "MySybaseDatabase" requested by the login.

I know that the job runs as 'SQL Server Agent Service Account' that is in fact xxx\user1
 and it is different from a user has been set up in ODBC connection to access the source database, but don't know why the odbc connection login does not take the precedance and is used rather than the agent service account?

I read some posts in different sites, they were talking about setting up proxy, etc, but don't know if it is quite relevant to my issue.

The connection to the source database has been set up via ODBC, but as the step was failing, I added the userId and password to it to force SQL uses the userId I want rather than SQL Sever agant user, but it didn't help.

Do you know how we can fix this login issue?

Thank you.


texts: SQL Server: allocated vs reserved vs commited

texts:
What exactly is the memory that is allocated to the a memory component in SQL Server?

I believed committed memory is the amount of physical memory that is being used, and reserved is the amount of memory in Virtual Address Space that does not yet map to physical memory.

But I am puzzled when it comes to allocated memory. When a memory component requests memory from a Memory Manager, what is the nature of this memory being granted? Real/Physical or VAS?

Here is an example DMV:

SELECT pages_kb AS A
,virtual_memory_committed_kb AS C
,virtual_memory_reserved_kb AS R
FROM sys.dm_os_memory_clerks;

There is no relationship whatsoever between columns A, C and R. Initially I was expecting A = C + R, which implied that A, the allocated memory pages, should start out as memory in VAS, and represent the intent to use this much of memory. But clearly it is not right.

How do I interpret allocated memory? Thanks


texts: OUTER JOIN Works in one direction but not the other

texts:
This started as a Rails app question but has evolved as I need to do a raw SQL query.  My title may be off but it's the best I could come up with.

Here is my data:

SELECT
     "model_locations".*
FROM
     "model_locations"

    +----+---------------------+-------------+
    | id |            model_id | location_id |
    +----+---------------------+-------------+
    |    | 17                  | 50          |
    |    | 17                  | 51          |
    |    | 10                  | 24          |
    |    | 19                  | 11          |
    |    | 19                  | 5           |
    |    | 19                  | 51          |
    +----+---------------------+-------------+
    6 rows in set

SELECT
    "models"."id", "models"."location_from_id", "models"."location_to_id"
FROM
    "models"'

+----+------------------+----------------+
| id | location_from_id | location_to_id |
+----+------------------+----------------+
| 17 | 1                | 5              |
| 18 | 50               | 24             |
| 10 | 3                | 8              |
| 1  | 50               | 11             |
| 19 | 1                | 5              |
| 20 | 1                | 11             |
| 21 | 11               | 5              |
+----+------------------+----------------+
7 rows in set

So for example:

Model 17 has Locations 50, 51 AND 1 ,5
Location 11 has Models 19, 1 AND 20, 21

So I can find the Model Locations:

SELECT
    "locations".*
FROM
    "locations"
    LEFT JOIN "model_locations" ON "locations"."id" = "model_locations"."location_id"
WHERE
    "model_locations"."model_id" = 17 OR "locations"."id" IN (1,5)

This works great - I get my 4 locations however I can't get the reverse to work:

SELECT
     "models".*
FROM
     "models"
WHERE
     "models"."location_from_id" = 11 OR "models"."location_to_id" = 11
INNER JOIN "model_locations" ON "model_locations"."model_id" = "models"."id"
WHERE
     "models_locations"."location_id" = 11'

This fails at the INNER JOIN:

PG::SyntaxError: ERROR:  syntax error at or near "INNER"

I am pretty weak on my SQL but I don't get while this doesn't work.  I have tried several variations etc. and am sure I don't have a typo.

I need the SQL that will get me Models 19, 1 AND 20, 21 related to Location 11

UPDATE

I should really not stay up late working on this stuff.  This seems to work and a swear I tried this:

SELECT
     "models".*
FROM
     "models"
LEFT JOIN "model_locations" ON "model_locations"."model_id" = "models"."id"
WHERE
     "models_locations"."location_id" = 11'
     OR
     "models"."location_from_id" = 11
     OR
     "models"."location_to_id" = 11


texts: How to use single domain name to connect to multiple database servers?

texts:
We have multiple Windows server 2016 machines hosting single SQL server instance on each server. Every server hosts ~100 databases. We have a master table in which has a record for each database & its connection string. Connection string contains IP of the server where database is hosted & some other typical information.  Since databases are distributed across multiple servers we have to query on master table first to get correct connection string & then connect to the desired database to perform any operation.

As we have multiple DB server(and new ones might get added in near future), it would be great if we can put all our DB servers under single domain name so that all DBs can be accessed through one domain name. This will give us flexibility of changing IPs if needed without updating connections strings. Also adding new servers will become easy without changing any application code.

To be able to achieve this, Can someone please put me in right direction on how to do following?

How to add a domain name to existing SQL server instance?
How to add a proxy which will accept connection requests from application code & forward it to correct database server using some logic?

Thanks in advance!


texts: Using SQL Server TDE - Encryption 0 percent

texts:
I'm using TDE for SQL Server. Querying certificate:

select database_name = d.name,
dek.encryptor_type,
cert_name = c.name
from sys.dm_database_encryption_keys dek
left join sys.certificates c
on dek.encryptor_thumbprint = c.thumbprint
inner join sys.databases d
on dek.database_id = d.database_id;

Output:

database     encryptor        cert_name
tempdb      ASYMMETRIC KEY    NULL
TDE_Test    CERTIFICATE       TestZert3

Querying encryption status:

select db_name(database_id), encryption_state,
percent_complete, key_algorithm, key_length
from sys.dm_database_encryption_keys

Output:

database  encrypt_state percent_complete key_algorithm
tempdb      3             0              AES 256
TDE_Test    3             0               AES 256

Why is the complet_percent 0?


texts:
This business model is for a case management database. This is closely modeled on the idea of a file folder representing the phase and a sequential checklist representing the stages. A case consists of a phase that can have one or more stages.  A phase can only have one stage that is "Current" or open at any one point in time.  A case can only start from one type of stage but can progress to any one of a number of stages that are end types. In this business model there are many different types of phases and stages

An example: you apply for a license. The process always starts with you submitting a form but can have different endings: the application is approved or rejected or sent back for more information.

Edit: @Colin 't Hart  asks what a phase is in relation to a case. Here is where trying to simplify a question can omit details.  The complete schema structure is:
- one case can have one or more phases but only one phase is open or "current" at at time.
- each phase can have one or more stages but only one phase is open or "current" at a time.
- there are different types of cases/phases/stages and transitions from the current unit to the next unit require adding a close date to the current and inserting a new record with an open date.
An example: a production line for widgets

a production ticket initiates the creation of the case
the first phase: sourcing components is created
the first stage: contacting suppliers is created
the first stage is completed, the second stage: orders from suppliers is opened
orders stage is closed, inventory check stage is created
inventory stage is closed, sourcing components phase is closed
new phase: assembly is opened
new stage: move components to shop floor is opened
moving components stage is closed, new stage production line is opened
and so on....

Problem:

the existing table structure is flawed in that the same information (what is the first type of stage for a kind of phase) is stored in two different tables
You can have more than one entry in STAGE where IS_START_STAGE = 1 which violates a business rule
You can insert a new entry into STAGE where IS_START_STAGE = 1 and this does not match the corresponding entry in PHASE_FIRST_STAGE
the relationship should be something like constraint PHASE_FIRST_STAGE.STAGE_ID can only be in the entries in STAGE where IS_FIRST_STAGE = 1
Is there anyway to enforce these business rules?

CREATE TABLE PHASE_FIRST_STAGE
(
  PHASE_ID           NUMBER(9)           NOT NULL, --PRIMARY KEY and foreign key to PHASE
  STAGE_ID           NUMBER(9)           NOT NULL,  --FOREIGN KEY to STAGE table
);
ALTER TABLE PHASE_FIRST_STAGE ADD (CONSTRAINT PFS01
  FOREIGN KEY (PHASE_ID)
  REFERENCES PHASE (ID),
  FOREIGN KEY (STAGE_ID)
  REFERENCES STAGE (ID));
COMMENT ON TABLE PHASE_FIRST_STAGE IS 'Contains the default first stages to enter when a phase is entered.';

CREATE TABLE STAGE
(
  ID               NUMBER(9)           NOT NULL, --PRIMARY KEY
  PHASE_ID         NUMBER(9)           NOT NULL,  --FOREIGN KEY to PHASE
  DISABLED         NUMBER(1)           DEFAULT 0   NOT NULL,  --CHECK IN (0,1)
  IS_START_STAGE            NUMBER(1),--CHECK IN (0,1)
  IS_END_STAGE              NUMBER(1) --CHECK IN (0,1)
);

COMMENT ON TABLE STAGE IS 'Contains all the stages a phase can have. Each stage must have only one phase. ';
--not shown is a similar table called PHASE with a one phase => many type of stage relationship


texts: Query to insert records from one table into another table based on the column of the first table

texts:
I have a table with the structure below :

Table(customer_Num  int , Channel_Id  Varchar(50) ,  Branch_Code  int)

and some example data for this table is :

                  Table
__________________________________________
customer_Num  |  Channel_Id  |  Branch_Code
    1         |    'I'       |     1000
    1         |    'Y'       |     1001
    1         |    'L'       |     1002

and the final result I want in the output is :

                          Result_table
__________________________________________________________
customer_Num  | I_branch_Code |  Y_branch_Code  | L_Branch_Code
    1               1000             1001             1002

is there a better solution rather than using continuous Insert  queries? Cause with this query
I will have many nulls in my final table!And I also won't have the result in one record.

 insert into Result_table (customer_Num  ,I_branch_Code )
 select customer_Num   , Branch_Code
 from Table
 where Channel_Id   = 'I';

 insert into Result_table (customer_Num  ,Y_branch_Code  )
 select customer_Num   , Branch_Code
 from Table
 where Channel_Id   = 'Y';

insert into Result_table (customer_Num  ,L_Branch_Code)
select customer_Num   , Branch_Code
from Table
where Channel_Id  = 'L';


texts: How can primary keys in a Netezza table be indexed?

texts:
What is the best way to index a Netezza fact table with lots of primary keys.  For now, let's suppose we have 10 primary keys.  This table is mainly used to store meta data about our database.

===================

6/21/2012 - update

This is the diagram for SQL Server or Oracle, not Netezza.  I haven't built this table on Netezza yet.  Some of the primary keys you see in the fact table are also foreign keys (concept_cd, provider_id, encounter_num are 3 example FKs).


texts:
The optimizer will attempt to use default database statistics in preference to dynamic statistics. In some cases, oracle will auto use dynamic statistics.

parallel execution
missing statistics
stale statistics
insufficient statistics
have sql plan directives

My question is if a SQL statement with highly selective filters on column that has missing index statistics, will it benefit from dynamic sampling?


texts: postgres crosstab with unknown number of categories

texts:
I have below table

CREATE TABLE bar (project,month,hours)
    AS VALUES
    ('A', 'aug', 1 ),
    ('A', 'sep', 3 ),
    ('B', 'aug', 2 ),
    ('B', 'sep', 5 );

I can crosstab this with month as category as below

select * from crosstab (
'select project, month, hours from bar',
'select distinct month from bar'
) as
c (project text, aug int, sep int);

The problem is month column can be vary. it can be vary from number or names.

Ex: Next time month column in bar table can have oct also which I don't know beforehand.

Then How I create crosstab query for that? In summary what I want is to remove hardcoded aug, sep from my crosstab query.


texts: SQL: shopping cart design for a marketplace

texts:
I'm working on a marketplace that requires the user to be logged in to add items to the shopping cart. In most online examples, I can't see the benefits of creating a separate cart and cart_item tables.
Example: https://mysql.tutorials24x7.com/blog/guide-to-design-database-for-shopping-cart-in-mysql
Why not just create an empty order and set its status to cart and when the user submits the order, then just reduce the quantities from the product table and fill the other columns with the required values and change the status of the order?
Example with shopping cart

Example using the order table to act as a cart when setting its status to cart.


texts: dbcreator role member not able to access database it just restored

texts:
I have added a new windows account [PROD\apdf] and given it the dbcreator role.

The user initiates a restore from an app server.
The database restores correctly.  Under database properties it shows the prod\apdf user as the database owner.  However, that user is not granted access to the database. The sysadmins and dbcreators are added to security on the new database, but not the one it was restored by.  When pulling up the dbcreator role list, the [PROD\apdf] user is on that list.

I have made sure that the account is not orphaned and it shows it is a member of the dbcreator role using:

EXEC sp_helpsrvrolemember 'dbcreator';

I have seen an article that indicates this may be by design, but I wanted to see if anyone else had an answer about this.


texts: Does dm_os_memory_clerks show all SQL Server memory?

texts:
We are running SQL Server 2017 on a machine with 256GB Ram. Noticed a dramatic shift in some performance counters (e.g. page life expectancy usually very high, but had started fluctuating a lot the last few days which prompted this).
Noticed that the buffer pool only seemed to be using ~60GB whereas it was using ~140GB last week.  If I use sys.dm_os_memory_clerks
SELECT type, SUM (domc.pages_kb) / (1024 * 1024)
FROM sys.dm_os_memory_clerks AS domc
GROUP BY domc.type
ORDER BY SUM (domc.pages_kb) DESC;

It gives out the following

Which implies SQL Server is only using 65GB of the 230GB it has committed.  Are there some other DMVs that can show the difference and where the rest has gone?


texts: Select from two different tables and group by dates


texts:
I was reading this BBC News article and the following excerpt, caught my attention.  It sounds like Always On Availability Groups or High Availability Mirroring, maybe with security automatically included.
Is blockchain a potentially viable database solution for modern, high transaction volume applications?
It is pretty easy to see it's value for low volume transactions like personal medical records, but what about high volume databases?

What is blockchain?
Blockchains rely on cryptography to allow a set of computers to make changes to a global record without needing a central actor.
Removing the middleman cuts costs in almost every sector.
The blockchain is a ledger that records everything that happens to a collection of data known as a "block" in a chronological order or "chain".
As a currency this is an important feature because it allows users to be sure their digital money is one of a kind, the same way each note in your wallet is unique.
"Blockchain tech will be the way we create assets because it allows you to transfer digital information without copying," says Adam Ludwin, chief executive of Chain.com, which builds blockchain networks.
Blockchain can be used to track the history of all sorts of information and maintain its value, so, for example, doctors could use it to update medical records.
Since each change to a blockchain is made simultaneously across the whole network, no information is lost and because changes cannot be undone the system maintains its transparency. A special key is needed to make changes to each block, so individuals can keep their records safe by protecting that key.


texts:
I've been thinking about this a lot recently:
Is there a consensus as to how to check SQL Server Agent jobs have run correctly?

Some advice seems to be alerting the operator on failure and addressing those, but this won't account for jobs that haven't run (say if the agent went down for a time) at all rather than run and failed.
Some advise to use Job Activity Monitor and check the schedules and statuses, which is better but requires you to connect to every server, which can be arduous in a large estate, and maintain a list of job timings.
We currently have a list of jobs per server and have an operator set up that mails to a single DBA account. Daily we go through and check each job in the spreadsheet against the notification emails checking failures or absence of a notification.

I feel like there has to be a better solution, but my googling hasn't brought anything up. Does anyone have a better solution for managing the status of agent jobs?

EDIT -
I should also add that we IDERA SQLDM to monitor some servers, but don't have the budget to look at all our servers. Also our SCOM implementation is in the hands of our server team and currently getting info out of it isn't easy for us. This makes getting reliable information about the status of the agent hard.


texts: Is two separate index equals to a composite index?

texts:
For example, this is a sample table

id int PK,
pid int,
cid int,
deleted int,
createdAt timestamp,
UNIQUE KEY pid_cid(pid, cid)

used WHERE clauses are

where pid=$pid and cid=$cid [and deleted = 1];
where pid=$pid [and deleted = 1];
where cid=$pid [and deleted = 1];

since I have indexed UNIQUE KEY pid_cid(pid, cid), should I index on pid and cid separately?

Or selecting by a index pid faster than selecting by a UNIQUE KEY pid_cid(pid, cid)?


texts: How to configure connection accessing Sybase Anywhere from Sybase IQ via ODBC on Linux

texts:
I'm trying to access an ASA16 server from IQ16 via ODBC driver. Both servers are installed on the same Linux box.

I've installed unixodbc-bin and added a file .odbc.init into home of user running the databases:

[mes_input]
Driver=/opt/sqlanywhere16/lib64/libdbodbc16_r.so
uid=dba
pwd=SQL
EngineName=mes_input
CommLinks=tcpip(host=127.0.0.1;port=49152)
AutoStop=no
DatabaseName=mes_input

The ASA is running on 49152 on that box; IQ is running default 2638.

Also I've added an entry into interfaces file locking like that:

mes_input
        master tcp ether myserver 49152
        query tcp ether myserver 49152

Back on IQ I can add via Sybase Central the remote server as well as tables from this box. Also testing the connection and is showing no error.

But when I'm now trying to copy some data from ASA via e.g.

INSERT locale_table LOCATION 'mes_input.mes_input' 'SELECT * FROM table'

I'm getting:

Connectivity libraries cannot be found (check your dynamic library
search path).  Selects from attached databases are not possible without
these libraries.
-- (hqmlib/hqm_cpm.cxx 146)
SQLCODE=-1003005, ODBC 3-Status="HY000"
Zeile 1, Spalte 1

Something I've missed or missconfigured?


texts:
This should be a quite easy query, but I honestly think its execution time can be improved.

select idTag,MAX(pctimestamp) AS PCTIMESTAMP,getdate() AS NOW, datediff(SECOND,MAX(pctimestamp),getdate()) AS DELAY
from ValuesTagsOPC
group by IdTag

This query returns 1386 rows from 'ValuesTagsOPC' table, that contains about 40 million rows, and has the following structure, retrieved by the create script:

CREATE TABLE [dbo].[ValuesTagsOPC](
    [IdTag] [int] NOT NULL,
    [TTimeStamp] [datetime] NOT NULL,
    [PCTimeStamp] [datetime] NOT NULL,
    [Value] [nvarchar](50) NOT NULL,
    [Quality] [int] NOT NULL,
 CONSTRAINT [PK_ValuesTagsOPC] PRIMARY KEY CLUSTERED
(
    [IdTag] ASC,
    [PCTimeStamp] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Obviously, there is a clustered index on its primary key.
Time and IO statistics from SQL Server are the following (sorry, it's in spanish):

(1386 filas afectadas)
Tabla 'ValuesTagsOPC'. Recuento de exámenes 5, lecturas lógicas 224612, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

 Tiempos de ejecución de SQL Server:
   Tiempo de CPU = 9578 ms, tiempo transcurrido = 3019 ms.

I've checked that the estimated execution plan is the same as the real one, and it tells me that 94 % of the cost comes from the cluster index scan. What I don't understand is why it is not performing a seek instead of a scan, since all required fields in the query are included in the clustered index....

Thanks in advance!!


texts: We are running AlwaysOn on three replicas. The primary and first secondary are synchronous for failover purposes. The second secondary is a read-only asynchronous server for reporting. The only thing we don't have a good handle on is SQL Agent jobs on failover. I've seen a few solutions from others, but nothing I thought was really slick. How do you handle SQL Agent jobs on a secondary replica that is intended for failover?


texts: Removing duplicates from a query with conditions in Access 2007

texts:
I have a table of transactions with 880,095 records that I am querying.

There are duplicate Transaction ID's in the table that I would like hidden/removed when querying the table.

Eg 1.

Transaction ID 001 is has two records
The data in both rows is identical except for Interest Rate.
Row 1 has interest rate of 5%
Row 2 has interest rate has no rate and value in the cell is 0

I would like all duplicate Transaction ID's with no interest rate to be removed. This is simple enough, the complication is the following.

Eg 2.

Transaction ID 002 has 4 records
The data in all 4 rows is identical
The interest rate in all rows is 0, ie there is no interest rate.
I would like the query to return any 1 of these rows, and remove the other 3.

In summary, I need help designing an MS Access query where duplicate transaction Id's are removed where:

Transaction ID has multiple rows, remove the row which  has a 0 Interest Rate value
Transaction ID has multiple rows, with all Interest Rates values as 0. Remove all duplicates leaving just one row.

Please let me know if more explanation is needed.


texts: Automatically add new dbo objects in SQL Publication

texts:
Is it possible to automatically add new dbo objects to SQL transactional replication? Is there some out of the box solution for that or that can be achieved by T-SQL?
Thanks


texts:
I have two tables like this:

Runners:
Name|Date    |FromDist|ToDist|RaceN
Al  |1/1/2017|1       |9     |axe
CC  |1/1/2017|11      |12    |axe
Bm  |2/1/2017|6       |2     |Charity

RaceStandards:
RaceN  |Date    |FromDist|ToDist|Direction|Rank
axe    |1/1/2017|1       |10    |+        |1
Charity|2/1/2017|10      |4     |-        |2

The race standards table gives the direction and distance ranges for each race. The runners table gives the distances ran by each runner.
Question/Issue/What I want to do:
1. Get the direction
2. If its +, range should be >=FromDist and <=ToDist; If its -, range should be <=FromDist and >=ToDist
3. Compare the distance range of Runners FromDist and ToDist based on steps 1 and 2. Assign the Rank number of that race if they fall in range, otherwise assign 0
4. If Runner is out of Range the value can be zero

So the end result would be something like this:

Runners:
    Name|Date    |FromDist|ToDist|RaceN   |Rank
    Al  |1/1/2017|1       |9     |axe     |1
    CC  |1/1/2017|11      |12    |axe     |0
    Bm  |2/1/2017|6       |2     |Charity |2

There would be hundreds of rows in each table so I would want it dynamic. There could be overlaps and stuff but I just need a headstart for now, once I get a base code I can try to expand from there. I would appreciate any help. I tried to give as much detail, if more is required please ask.


texts: PostgreSQL update column B with values generated from function that uses values from column A

texts:
Pretty new to SQL.

I have a table (client_info) in PostgreSQL (v11.1) that includes the following columns:

+----------+------------------+
| clientip | clientip_country |
+----------+------------------+
|  8.8.8.8 | null             |
|  1.1.1.1 | null             |
|  4.4.4.4 | null             |
+----------+------------------+

I'm trying to create a function/script that will lookup the country ISO code for each clientip using a local install of the MaxMind GeoLite2 database and then update the corresponding clientip_country with the result to give:

+----------+------------------+
| clientip | clientip_country |
+----------+------------------+
|  8.8.8.8 | US               |
|  1.1.1.1 | AU               |
|  4.4.4.4 | US               |
+----------+------------------+

What's the best way to go about achieving this?

For example I'm able to retrieve the country ISO code of an IP using the following bash command:

$ mmdblookup -f /usr/share/GeoIP/GeoLite2-Country.mmdb -i 1.1.1.1 country iso_code | sed -e '/^$/d' -e 's/.*"\(.*\)".*/\1/'
AU

What direction should I be looking to? An SQL command that queries the GeoLite2 DB? Or a bash script that queries the Postgres DB and updates the clientip_country column with the results?


texts: Is this table-backed-queue implementation safe?

texts:
We've a system where kinds of orders should be processed in batches, by advancing them from Status 0 to Status 1. There's a max number of a particular kind of order we can process in any one go, and it depends on the kind of the order, so we might be able to process 10 OrderKind 3's but only 7 OrderKind 5's.

Waiting to go in the orders table might be 20 of OrderKind 3, and 70 of OrderKind 5 etc. So in any one batching operation we only want to pick up max 10 of the OK 3's plus max 7 of the OK 5's, giving a batch of 17 rows total.

Here's the stored procedure that finds the oldest (according to when the order was approved) X orders, updates them to Status 1 and returns them to the front end app.

ALTER   procedure [ProcessPendingOrders]
as
begin transaction

declare @Processing table
(
    Id int,
    CustomerId uniqueidentifier,
    OrderControlId int,
    Destination nvarchar(256),
    Quantity int
);

update  Orders
set     StatusID = 1 --Being processed
output inserted.Id,
       inserted.CustomerID,
       inserted.OrderControlId,
       inserted.Destination,
       inserted.Quantity
into   @Processing
from    Orders o
where   o.Id in (   select  topN.Id
                    from    OrderControl oc
                    cross apply (   select  top(oc.OrdersPerBatch)
                                            oo.Id
                                    from    Orders o2
                                    join    OrderControl oc2 on o2.OrderControlId = oc2.Id
                                    where   o2.StatusID = 0       --waiting
                                    and     oc2.ProcessOrders = 1
                                    and     oc2.ID = oc.ID
                                    order by o2.ApprovalDate asc
                                ) topN
                )

if @@error <> 0
 begin
    rollback
    raiserror ('Error processing orders', 16, 1)
    return
 end

 select * from @Processing

commit

My question is; what would happen if two external processes were to execute this stored procedure concurrently? Is it possible that the same order rows would be given to each process? I've a concern it will because of the extra legwork doing a top N per kind of order. Is there anything that can be done to apply locks such that two different processes can't get the same set of rows out of this procedure?

Would the same problem afflict the earlier iteration of this code that didn't have top N?  It was literally just a single UPDATE orders SET status = 1 OUTPUT blah INTO @abc WHERE status = 0 query.


texts:
I want to ensure consistency and make sure only one phone line is the default for a house.

Schema: PhoneLine
ID | isDefault | HouseID

I want to add a trigger so that when you add/update a row, and set isDefault to True, all other rows in same table with the same HouseID set isDefault to False.

I can't figure out a way to do this without creating a recursive mess.

What's the best way of approaching it?

I tried:

CREATE OR REPLACE FUNCTION unsetCurrentDefault()
RETURNS trigger AS $unsetCurrentDefault$
    BEGIN
        IF NEW.isdefault AND NEW.houseid IS NOT NULL THEN
            -- Current ones get reset.
            UPDATE phoneline SET isdefault = false
            WHERE houseid = NEW.houseid AND id <> NEW.id AND isdefault;
        END IF;
        RETURN NEW;
    END;
$unsetCurrentDefault$ LANGUAGE plpgsql;

CREATE TRIGGER updateDefaultValue AFTER INSERT OR UPDATE ON PhoneLine
    FOR EACH ROW EXECUTE PROCEDURE unsetCurrentDefault();

But the behaviour seems recursive.


texts: What version of PostgreSQL first implemented autovacuum?

texts: I'm just wondering what version of PostgreSQL first implemented autovacuum.


texts:
I am new here and have a little question.

In a customer environment after checking his index fragmentation we see some "NULL" named index that we couldn't rebuilt or reorganize.

This was a SQL Server 2008 R2 Sp1. I have try looking on the net for this unnamed index but have no luck finding anything about it, can anyone please enlighten me on what are those.


texts:
I have two physical servers, hosting 6 two node RAC databases running in 11.2.0.3 version (both grid and database).

I'm told to have the grid software in 11.2.0.4 version and the databases in the same 11.2.0.3 version since there could be future plans to upgrade the database to a higher version.

Plan is to create new grid software (11.2.0.4) and create a new ASM instance with new luns and create diskgroups with the new luns, create a new database and then export/import from old database to this new database. So that the new database will have the Grid and ASM running in 11.2.0.4 and database in same 11.2.0.3.

Perform the same activity for all 6 databases, post that uninstall 11.2.0.3 software and binaries, resulting in the required result wherein only one ASM instance will be running (11.2.0.4).

Is the above mentioned activity feasible? I'm not in a situation to upgrade 11.2.0.3 grid to higher version as I cannot afford downtime. But then the above mentioned activity will have two ASM instances running at the same time for some period.

I can add/clear doubts if the mentioned activity is unclear.


texts: Fastest way to move a database from one server to another

texts:
I'm moving a single MySQL database (~10GB uncompressed) from one server to another on the same network. The current MySQL version is 5.1.41 and the new version is 5.5.24. The database contains both MyISAM and InnoDB tables. Is it possible to use this method:

Shut down MySQL on both servers
Copy the /data directory from old server to new server
Start new server

I realize this basic question has probably been asked 1000 times before but most of the ones I've seen don't mention changing versions & supporting both MyISAM and InnoDB.


texts: Is it safe to use the Windows "Compress directory to save space" feature on the directory that contains my MySQL general log file?

texts: I have changed MySQL 8 on my Windows 10 development machine to write logfiles (general log and slow queries log) to "E:\mysql logfiles". I'd like to compress this directory using the Windows "Compress Directory to save space" advanced feature in the Windows 10 directory properties to save space (currently my logfile is over 50 GB). I'm wondering though: is this a good idea? Or will this break MySQL in (subtle or not) ways?


texts: How do I translate this query from MS Access into SQL Server

texts:
How do I translate this into SQL Server:

select
-Sum((12=[GroupNum])*[BoardStatus]) AS G12
FROM
 QryAllBoardsByPacketIDs
INNER JOIN
 tBsReportGroups
  ON QryAllBoardsByPacketIDs.NominalLengthMetric=tBsReportGroups.NomLenMet
GROUP BY
 QryAllBoardsByPacketIDs.ParentPacketID,
 QryAllBoardsByPacketIDs.GradeID,
 QryAllBoardsByPacketIDs.NominalWidthMetric,
 QryAllBoardsByPacketIDs.NominalThicknessMetric;

Kind regards,

Martin


texts: Conversion failed when converting date and/or time from character string while inserting datetime sql server

texts:
I'm working with a project in SSRS (SQL Server Reporting Services) and project is about employees of company,where i should find out age of 200 employees from their Social Security Number (CPR).

In Database i have a column called SSN and in this column , i have Social Security Numbers for all employees and all numbers look like this with out any ( - or /) example 1111111111 (DDMMYYXXXX) and Datatype is nvarchar(30).

When i executed my query ,i get error 'Conversion failed when converting date and/or time from character string'. Can someone please point me in the right direction. Thanks.

My query look like this :

SELECT Users.Id, Users.FirstName + ' ' + Users.LastName AS Medarbajder,
Users.SSN AS CPRNR,

DATEDIFF(YEAR,CONVERT(date,STUFF(STUFF(LEFT(Users.SSN, 6), 3, 0, '.'), 6, 0, '.'), 4), GETDATE()) As Age,
convert(varchar(10), Paychecks.WorkStartDate, 105) AS StartDato ,

Paychecks.DepartmentName AS Afdelinger

FROM dbo.Paychecks, dbo.Users
WHERE Users.CustomerId=214 AND Users.Id=Paychecks.UserId order by Users.FirstName;


texts: Mysql join two tables and limit by first input?

texts:
i am only starting to play with MySQL and i seem to have been stumped by one problem.

I have one table containing a number of fields including a primary id field. I have a second table containing multiple rows with the value of user_id corresponding to the first table.

Or, this

Table 1:
Id name
1 foo
2 bar
3 baz
4 qux

Table 2:
id uid
1 1
2 2
3 2
4 2
5 3

I need to make a query with a limit i.e say LIMIT 3
Then the first 3 rows of table 1 should be selected, and all rows from table 2 having uid between 1 and 3 (The ids of the first 3 rows) should be returned.


texts: Need a better way to insert non-existent values in referenced table during insert/update in referencing one

texts:
I'm looking for the best way to insert data into MariaDB with JDBC. Please take a look at the following schema

CREATE TABLE color (
  id   SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE ball (
  id       INT PRIMARY KEY AUTO_INCREMENT,
  name     VARCHAR(255) NOT NULL,
  color    SMALLINT UNSIGNED,
  KEY color (color),
  CONSTRAINT color_fk FOREIGN KEY (color) REFERENCES color(id)
);

and test data

INSERT INTO color (name) VALUES ('green'), ('yellow'), ('red');
INSERT INTO ball (name,color) VALUES ('Big Yellow Ball',(SELECT id FROM color WHERE name = 'yellow')); -- OK
INSERT INTO ball (name,color) VALUES ('Small Purple Ball',(SELECT id FROM color WHERE name = 'purple')); -- DOESN'T EXIST

and as result NULL color value is inserted for Purple Ball

MariaDB [zimbra]> select * from ball;
+----+-------------------+-------+
| id | name              | color |
+----+-------------------+-------+
|  1 | Big Yellow Ball   |     7 |
|  2 | Small Purple Ball |  NULL |
+----+-------------------+-------+

Thus I can see the following solution

try to read required color
then if it doesn't exist insert a new record for it
then extract generated key
prepare and execute next query/statement with it

which doesn't look very elegant as it requires from 2 to 3 statements preparation (in Java), with further execution and results extraction for each of them. This becomes even more ugly taking into account that there are more than one such referenced tables in my real case. Another reason to optimize this is performance: to send less SQL requests and to execute less PreparedStatements).

Ideally, I would like to be able to construct such SQL insert statement which would bother about non-existent referencing values.

Otherwise, if it is impossible, what other solution I should look at which would allow me to simplify my Java code?


texts: SQL Server 2005 database engine login failed error?

texts:
I installed SQL Server 2005 Enterprise Edition on Windows xp.

This account exists in administrators role.But I can't login to SQL Server database engine using Windows authentication method or sql server authentication method and I receive this error message:

An error has occurred while establishing a connection to the server. When connecting to sql server 2005, this failure may be caused by the fact that under the default setttings sql server does not allow remote connections.(Provider:named Pipes provider,error:40-could not open a connection to sql server)(Microsoft sql server,error:53).

But im trying to connect to a local instance in my computer. Also i checked SQL SERVER 2005 surface area>> surface area configuration for services and connections >>which shows error >>No sql server 2005 componenets were found on the specified computer. Anyone can give clues on this issue. Do i need to install any components to make this work? I had selected all the options while installing.

please help me on this..
 I am attaching the summary log of setup herewtih.

  Microsoft SQL Server 2005 9.00.1399.06
  ==============================  OS Version      : Microsoft Windows XP
  Professional Service Pack 3 (Build 2600)  Time            : Wed Oct 05
  09:49:21 2011    CHANGEME1 : The current system does not meet the
  minimum hardware requirements for this SQL Server release. For system
  requirements, see Hardware and Software Requirements, in the readme
  file or in SQL Server Books Online. Machine         : CHANGEME1
  Product         : Microsoft SQL Server Setup Support Files (English)
  Product Version : 9.00.1399.06  Install         : Successful  Log File
  : C:\Program Files\Microsoft SQL Server\90\Setup

  Bootstrap\LOG\Files\SQLSetup0002_CHANGEME1_SQLSupport_1.log

  Machine         : CHANGEME1  Product         : Microsoft SQL Server
  Native Client  Product Version : 9.00.1399.06  Install         :
  Successful  Log File        : C:\Program Files\Microsoft SQL
  Server\90\Setup

  Bootstrap\LOG\Files\SQLSetup0002_CHANGEME1_SQLNCLI_1.log

  Machine         : CHANGEME1  Product         : Microsoft Visual Studio
  2005 Premier Partner Edition - ENU  Product Version : 8.0.50727.42
  Install         : Successful  Log File        : C:\Program
  Files\Microsoft SQL Server\90\Setup

  Bootstrap\LOG\Files\SQLSetup0002_CHANGEME1_PPESku_1.log

  Machine         : CHANGEME1  Product         : Microsoft Office 2003
  Web Components  Product Version : 11.0.6558.0  Install         :
  Successful  Log File        : C:\Program Files\Microsoft SQL
  Server\90\Setup Bootstrap\LOG\Files\SQLSetup0002_CHANGEME1_OWC11_1.log

  Machine         : CHANGEME1  Product         : Microsoft SQL Server
  2005 Books Online (English)  Product Version : 9.00.1399.06  Install
  : Successful  Log File        : C:\Program Files\Microsoft SQL

  Server\90\Setup Bootstrap\LOG\Files\SQLSetup0002_CHANGEME1_BOL_1.log

  Machine         : CHANGEME1  Product         : Microsoft SQL Server
  2005 Backward compatibility  Product Version : 8.05.1054  Install
  : Successful  Log File        : C:\Program Files\Microsoft SQL
  Server\90\Setup

  Bootstrap\LOG\Files\SQLSetup0002_CHANGEME1_BackwardsCompat_1.log

  Machine         : CHANGEME1  Product         : MSXML 6.0 Parser
  Product Version : 6.00.3883.8  Install         : Successful  Log File
  : C:\Program Files\Microsoft SQL Server\90\Setup

  Bootstrap\LOG\Files\SQLSetup0002_CHANGEME1_MSXML6_1.log

  Machine         : CHANGEME1  Product         : SQLXML4  Product
  Version : 9.00.1399.06  Install         : Successful  Log File
  : C:\Program Files\Microsoft SQL Server\90\Setup

  Bootstrap\LOG\Files\SQLSetup0002_CHANGEME1_SQLXML4_1.log

  Machine         : CHANGEME1  Product         : Microsoft SQL Server
  2005 Tools  Product Version : 9.00.1399.06  Install         :
  Successful  Log File        : C:\Program Files\Microsoft SQL

  Server\90\Setup Bootstrap\LOG\Files\SQLSetup0002_CHANGEME1_Tools.log


texts: Pros and Cons of having additional columns in join table of many to many relationship

texts:
I searched but did not find any discussions on this topic.
Are there any pros and cons on creating many extra columns on joined table?
Does it affect speed of data retrieval because we are joining 3 tables?

As far as what I think, it may slow tiny bit but it is necessary otherwise how can we save additional data about many-to-many relationships. Or is there any other way of which I am not aware of?


texts: How to fan out rows with a specified range of values?

texts:
I have a table which essentially contains 3 columns: ID, FIRST, LAST. They're all integers.

I'm wondering if there's a way to use generate_series() to get a query result where for each row in the original table, there are many rows containing numbers between FIRST and LAST?

E.g. for a row with data (42, 5, 8) the output contains rows (42, 5), (42, 6), (42, 7) and (42, 8).


texts: SQL Server memory issues after upgrade from Express

texts:
I have a SQL Server 2014 Standard (x64) instance that was recently upgraded from SQL Express.

The VM that the SQL instance is on has ~16GB of RAM and SQL MAX MEMORY has been configured to use ~12GB

I want to understand while i configured: max server memory (MB) = 12000, why the SQL instance is not consuming more than ~4154MB when i verified it with following query:

select (physical_memory_in_use_kb/1024) usage_in_MB
from sys.dm_os_process_memory


texts: What is the best way to make a one to many relationship between tables without redundancy?

texts:
I can purchase a monitor, a bookstand, and a table lamp for a single shipping fee of $20. But the problem arises when I want to specifically breakdown the shipping fees that each item incurred.
Sometimes it is difficult and impractical to actually be too precise with the costs due to scenarios like these, but there are also some instances where you can workaround issues like these by creating an estimate. We can create a fraction that can represent the volumetric weight of each item and multiply it with $20 to get the approximate shipping cost for example.
This example is true even for business applications where multiple purchases can have a certain expense amount bundled into one value.
My issue lies in the documentation process of scenarios like these where I want to do profitability analysis of a certain product by trying to link expenses related to a certain transaction and find out whether the pricing of the product is correct and whether I am making money.
In accounting, an account like Freight expenses will be debit and Cash on hand will be credited for the same amount, but my goal is not purely for book keeping. I store my data mainly for the insights and business decisions so I do think in a database mindset whenever I come up with decisions like these on how I will be able to extract information from the data. There may be a proper way accountants do it, but I do not have the expertise so I am hoping to find a solution.
The first part starts with a purchase so I create a table that may represent my purchases as follows:

Below is an example image of what kind of information I want from my data:

In storing the data, I can only think of two ways to do it:
1.)

The problem with this first one is that the Affected IDs column is separated by commas thus the computer will not be able to properly understand this.
2.)

The problem with the 2nd method is that the Document ID # column would have duplicate values for the single document used and may cause issues with how you are going to trace which documents were already placed into the database, and whether you made a double entry.
So my long time problem with storing my data is how to best layout my database to solve both problems of being able to point the costs to their corresponding transactions that the computer can use for decision making purposes while being able to trace whether it is a double entry or not
I am not adept at created tables and database structures so I am pretty sure I am not doing it correctly.
If you were to create a table to achieve this goal, how would you layout (introducing dummy columns and etc.)
I've spent the last 30 minutes trying to come up with a relevant title that explains the problem at hand but I do not completely understand the problem so I am not able to phrase it properly.


texts:
I have been having some issues creating a redundant multi master setup. Here's the proposed layout:

The problem I'm having is when something is written to master1 it replicates to slave1 and master2 but not slave2. And when something is written to master2 it replicates to slave2 and master1 but not to slave 1.

Is there any way I can get this to work where both slaves are updated no matter which master is written to?


texts: Combine several missing index suggestions with one index?

texts:
I investigate on missing indexes recommendations of my sql server engine.

Looking at the most useful ones (for a specific table) that are candidates based on their potential usage and potential improvement impact, I wonder if some of the candidates left might be covered by just one index (or at least by less indices than sql server suggests to create).

Samles:

 1. EQUALITY: [OID], [DFlag] INEQUALITY: [Stat]
 2. EQUALITY: [OID], [DFlag], [ChangeNr] INEQUALITY: [UID] INCLUDES: [Stat]
 3. EQUALITY: [OID], [DFlag], [ChangeNr] INEQUALITY: [UID] INCLUDES: [ID], [Int], [Stat]

So I wonder if maybe index No.1 might already be covered by index no. 2 because the search predicates are also included there?
And if index no. 2 might be covered by creation of index no. 3 because it hase the same predicates and the included colum is part of the included columns there as well?

So my thesis is that by creation of index 3 the missing index suggestions of no 1 + 2 in theory should dissapear because index no. 3 could perfectly meet the criteria of all of those. It this correct?


texts:
My current project requires a form on my website to populate a table in a MySQL database with data a user submits via the form. This will work great but my complete goal involves getting that data into a MSSQL table.

I'm populating the MySQL table and linking to this MySQL data from MS SQL Management Studio via a linked table. I am able to connect to the MySQL database successfully.

My question is whether or not I'm going in the right direction, and further, how do I get this data sent into a MSSQL table? The project I am on involves giving my users visibility to data submitted via the web forms as soon as it is submitted.

Curious if there is any feedback that can point me in the right direction for this particular scenario? What is the best practice for a situation that requires a website to populate a MySQL database, and that data getting "copied" to a MSSQL server database?

Thanks!


texts:
We have a query that is called very frequently, so I would like to improve performance, but some colleagues are concerned about the index's size and its impact on inserts an updates.

SELECT field1, field2
  FROM table1
  WHERE field3 = '101'
  AND   field2 = @variable

Table table1 has about 5 million rows, but only about 2000 has field3 = 101.

 CREATE TABLE table1 (
   field1 varchar
   field2 int
   field3 int);

Would it be possible to create and index for (field2, field1) only for these 2000 rows in oracle  11g?
How can I do it?


texts: Does splitting 1 tempdb file into 4 require a SQL Server restart?

texts:
please can anyone help?

I have a SQL 2008R2 instance with 4 cores, configured with 1 tempdb data file and 1 logfile.

I want to change this setup as per best practices by adding 3 additional euqally sized files. I want to size the files to fill the capacity of the drive. The dedicated drive has a capacity of 50GB but a recent ad hoc user query pushed the size up to 47GB (log is 2GB).

Are the following order of steps correct?
Will it require a restart of SQLServer?
Will the files be used proportinally by SQL Server correctly after, i.e. not pushing contention to a new file?

1) Shrink the original data file down to 12GB using DBCC SHRINKFILE.
* Size taken up by the drive should now be 14GB (2GB log + 12GB data file 1)

2) Add 3 new additional data files at 12GB each.
* The drive should now be full at 50GB capacity

This will be done during a planned outage on a production system, but wanted to know if there will be any potential issues with the approach?

Thanks in advance.


texts: My database keeps crashing on startup how can i repair it without it running?

texts:
I've got some problems with my database. I hope somebody can help me out.
Problem:
The database keeps crashing on startup. In my log i see a couple off these errors:
[ERROR] /usr/libexec/mysqld: Table '.tablename' is marked as crashed and should be repaired
Normally i would repair this table using PHPMyAdmin or SSH but is it possible to do this without the database running? (because when i start it it crashes and stops)
Or is there any other way to get this all working again? I've got a back up but i will lose some data. The table that is crashed contains cache data, so i could miss that.
Thank you!
Gr. Matthijs
UPDATE by RolandoMySQLDBA

Please post contents of /etc/my.cnf
Is the DB Server bare metal machine or VM ?
How much RAM does the server have ?
Are all the tables InnoDB, MyISAM, or a mixture of both ?
Is the OS Linux or Windows ?


texts: What is the correct way to select data grouped by time interval?

texts:
I need to display a chart that would illustrate how many ticket sales happen at different time intervals on different dates, e.g. How many tickets for Friday show get sold 3 hours in advance, etc.

The end result will look something like:

I have written a query by calculating values for different offsets at the SELECT statement:

WITH
  -- Create a date range; use LEFT JOIN/ COALESCE to construct date specific report; we don't want gaps in the data
  dates AS (
    SELECT generate_series(
      '2018-04-26',
      '2018-04-28',
      INTERVAL '1 day'
    ) AS date
  ),
  sales AS (
    SELECT
      ts1.date,
      SUM(CASE WHEN ts1.event_starts_at - ts1.created_at < INTERVAL '0 hour' THEN 1 ELSE 0 END) after_event_start,
      SUM(CASE WHEN ts1.event_starts_at - ts1.created_at BETWEEN INTERVAL '1 hour' AND INTERVAL '2 hour' THEN 1 ELSE 0 END) hour_0_to_hour_1,
      SUM(CASE WHEN ts1.event_starts_at - ts1.created_at BETWEEN INTERVAL '1 hour' AND INTERVAL '2 hour' THEN 1 ELSE 0 END) hour_1_to_hour_2,
    -- [..]
      SUM(CASE WHEN ts1.event_starts_at - ts1.created_at BETWEEN INTERVAL '22 hour' AND INTERVAL '23 hour' THEN 1 ELSE 0 END) hour_22_to_hour_23,
      SUM(CASE WHEN ts1.event_starts_at - ts1.created_at BETWEEN INTERVAL '23 hour' AND INTERVAL '24 hour' THEN 1 ELSE 0 END) hour_23_to_hour_24,
      SUM(CASE WHEN ts1.event_starts_at - ts1.created_at > INTERVAL '24 hour' THEN 1 ELSE 0 END) after_24_hour
    FROM ticket_sale ts1
    WHERE
      ts1.movie_id = 1012718 AND
      ts1.starts_at > '2018-04-26' AND
      ts1.starts_at < '2018-04-28'::date + INTERVAL '1 day'
    GROUP BY ts1.date
  )
SELECT
  to_char(d1.date, 'YYYY-MM-DD') "date",
  COALESCE(s1.hour_0_to_hour_1, 0) hour_0_to_hour_1,
  COALESCE(s1.hour_1_to_hour_2, 0) hour_1_to_hour_2,
  -- [..]
  COALESCE(s1.hour_22_to_hour_23, 0) hour_22_to_hour_23,
  COALESCE(s1.hour_23_to_hour_24, 0) hour_23_to_hour_24,
  COALESCE(s1.day_6_to_day_7, 0) after_24_hour
FROM dates d1
LEFT JOIN sales s1 ON s1.date = to_char(d1.date, 'YYYY-MM-DD')
ORDER BY d1.date

This works. However, as a query it looks odd.

Is there a better way to count sales at different time intervals?


texts: Online rebuild consuming lot of log space

texts:
why the online rebuild on a primary key clustered index in taking almost equal to the space of non clustered index size which is untouched.
Details:

We have a table with primary key clustered index (bigint type) of
index size 7GB.
We have another non-clustered filtered index on same table (varchar(36) type) of index size nearly 1.5TB.
Online rebuild on primary key clustered index is consuming almost 1.6TB of transaction log size.
Options used for rebuild - (DATA_COMPRESSION = PAGE, ONLINE = ON, SORT_IN_TEMPDB = ON)
Another observation, the previous index was created without compression, not sure if that is what causing such a huge growth.
Could someone please shed some internals about it?


texts: What would cause newly restored SQL Databases to show tables, but display an 'Invalid Object' error when trying to view table data?

texts:
Problem:

As an untrained and involuntary SQL DBA, I'm taking a set of SQL 2008R2 databases and restoring to a new SQL 2008R2 server in another environment.

The restore process goes fine and gives no errors. When I try to connect to them with our database explorer I see the databases and tables populate correctly. Unfortunately when I try to look at a table's data it gives me an "Invalid Object" error.

Background:

When we have performed these moves in the past for other clients, we generally run this script to "repair orphaned users". This is usually all we run and the databases then work with our db explorer and software. If we're going between SQL versions we will also change compatibility mode for the databases with a separate script

use dbar;
EXEC sp_change_users_login 'update_one', 'nurse', 'nurse';
use dbarforms;
EXEC sp_change_users_login 'update_one', 'nurse', 'nurse';
use dbdoc;
EXEC sp_change_users_login 'update_one', 'nurse', 'nurse';
use dbhistory;
EXEC sp_change_users_login 'update_one', 'nurse', 'nurse';
use dbhl7;
EXEC sp_change_users_login 'update_one', 'nurse', 'nurse';
use dbmds;
EXEC sp_change_users_login 'update_one', 'nurse', 'nurse';
use dbparadox;
EXEC sp_change_users_login 'update_one', 'nurse', 'nurse';
use dbpicklist;
EXEC sp_change_users_login 'update_one', 'nurse', 'nurse';
use dbreport;
EXEC sp_change_users_login 'update_one', 'nurse', 'nurse';
use dbrx;
EXEC sp_change_users_login 'update_one', 'nurse', 'nurse';

Troubleshooting Steps:

I noticed that our test servers running on MSSQL all show the nurse login as having ownership of the 10 databases. In the user column it says "nurse" and in the default schema column it shows "dbo"

In the case of this server that i'm having issues with, it shows the nurse login as having ownership of the 10 databases, the user column and default schema columns BOTH say "nurse".

So my first guess was that the other environment was setup to use a schema called "nurse" and the new server doesn't have it. I tried to create a new schema called "nurse" in one of the databases and I get this error:

I then tried this on a few tables to see if it worked... no such luck

ALTER SCHEMA dbo TRANSFER nurse.Table1 <--- This actually did fix my issue. I had it backwards before.

My main issue is that I don't even know what the root problem is, just that it doesn't work. I've talked to our developers and others who have some SQL experience and they all were stumped.

Any support would be greatly appreciated. Thanks in advance!

~A-A-Ron


texts:
I have a large database (~450gb) which I need to convert from utf8 to utf8mb4.

I'm aware of the steps I need to take, but is this practical on tables containing this much data? I'm mainly concerned that CONVERT TO CHARACTER SET utf8mb4 will be too intensive, as it's fairly slow in my test environment (on just a fraction of the rows).


texts: How to change the login for a database's dbo user?

texts:
I just finished a successful install of SQL Server 2014 Express, onto a domain-joined VM instance of Win8.1.

During setup which I ran under DOMAIN\Admin I accepted the default configuration of adding the current user as a SQL admin. Upon reconsideration after completion, however, I'd like to use the DOMAIN\DbAdmins group instead of a single user on the domain.

All well and good. Create the group, add it to SQL Server's Security node in SSMS, put it in the SysAdmin role, grant a 'Connect SQL' securable and we're off and running—right?

Not so fast.

The installer mapped DOMAIN\Admin to the dbo user on these two databases:

ReportServer
ReportServerTempDB

And of course, dbo can't be modified or removed.

I'd like to do this without uninstalling and reinstalling Reporting Services. Will it be possible to do so?


texts:
I recently found out that, the databases does not have standard architecture(data model), some of them are,

No relationship between tables(entities).
No indexes defined.
Only integer and varchar data types are used all over the database.
Same data fields are stored on multiple tables (entities).

Example:

mobile_number int(100)
geo_location_id varchar(200)
Same user profile is stored in multiple locations.

There are many other issues, but as of now I am only concerned about the storage, production server have enough storage space and memory to deal with the growing data size, but in our backup storage, we are running out of memory, some specifications about our server.

Production:

Server OS: Cent OS.
MySQL Server Version: 5.5.
Engine Type: InnoDB.
Number of Databases: 46.
Number of Tables: Average 20 tables per database.

Backup: From replication server.

Backup Storage Type: NAS.
Backup Size: 2 Tb (2 months).
Data growth rate: 6%.
Requirement: Need to store for 13 months.

Apart from performance issue, I need to know whether the above mentioned issues contribute to the increase of data size.

The server is at present used by many applications, if the above mentioned issues effect the storage space, is there any other possible solutions other than reverse engineering and fix it from scratch. Also, if possible, kindly mention the

Consequences of the above mentioned issues?
What are the things which effect the storage space?

This questions is linked to MySQL Backup Strategy have a look at it.

Thanks in advance.


texts:
I have a development and production server, both with different amounts of RAM. Development machine has 8G, while production has 64G.
I'm importing a large table from development to production, but before I started the process I dropped all the indexes to speed the import process, because I'm using stored procedure and with indexes it was very very slow.
On the development machine, while I was playing with creating/dropping indexes, I noticed that mysql used temporary space on the disk (default /tmp) for creating the indexes and it got to almost 8G.
The question is, since the production server has 64G of ram, will the mysql use the RAM for storing the temporary table while creating the indexes or will it use /tmp for the same thing?


texts:
I am a bit confused with the SET INTEGRITY concept in DB2, why we can OFF the set integrity check to put the table in PENDING state ? does that mean that we can neglect the constraint definitions on the table and insert invalid rows into table arbitrarily ? if that is the case why we create the constraint at the first place ?

further more, there is options to turn on and off access. I am further confused, does that mean even before the integrity of table data being checked, we can allow the data to be read accessed ? is that same as saying we allow dirty read here?

+another question is, if that integrity check is put off, how to bring the table out of pending state ? does this always requires manual intervention?


texts: Do any of the graph based/aware databases have good mechanisms for maintaining referential integrity?

texts:
Do any of the graph-based/graph-aware databases (Neo4j, ArangoDB, OrientDB, or other) have mechanisms for maintaining referential integrity on a par with those offered by relational databases?

I'm exploring various document-based databases to find a suitable engine to use for adding an auxiliary data storage to a certain project.

I discovered graph-based/multimodel databases and they seemed like a good idea, but I was surprised to find that they don't seem to offer the same level of protection of relations/links/edges that modern relational databases have.

In particular, I'm talking about linking deletions of entities/vertices with deletion of links/edges. In a relational database, I can have a foreign key constraint that links records from one table with records in another table, and will either

prevent deletion of record in table A if it's referenced by record in table B ("on delete no action"), or
delete the referencing record(s) in table B if a referenced record in table A is being deleted.

I expected to find a similar mechanics in graph-aware databases. For example, if a "comment" vertex links to a "post" vertex (forming a many-to-1 relation), then there are the following problems/challenges to solve:

Prevent deletion of a post while there are edges from comments to this post. This way, a comment could never have a dangling link/edge to a post. The solution would be: depending on the link/edge properties, either

prevent deletion of a post until all edges from comments to this post are deleted, or
delete all comments linking to this post when the post is being deleted.

Prevent deletion of an edge from a comment to a post without deleting the comment itself, to prevent the comment from not having a link/edge to a post at all.
Only allow creation of a comment if an edge is created to link this comment to a post at the same time.

Are mechanisms like this really lacking in graph-based databases, or was I just unable to find them?

I know that OrientDB has the "link" data type that probably solves the second and the third problem (if a link-typed property is declared mandatory and non-null, then it's impossible to create a record without specifying the link destination, and later it's impossible to break the link by un-setting the property).

However, as far as I remember, it's possible to delete the record which a link-typed property points to, thus producing a dangling link (so the first problem is not solved).

I also know that in certain databases I can use nested documents as an alternative to having multiple linked documents. However, this approach doesn't scale well (for cases when the number of linking records grows can grow indefinitely). Also, it is quite limited (it can't be used as an alternative when several links are needed, say, to a post and to a user; there are other important limitations, too).


texts: What load does SQL Server Reporting Services (SSRS) have on the server?

texts:
Does SQL Server Reporting Services use a lot of server resources?
Is it okay to install SSRS on the same server as the SQL Server instance? What should I take into consideration?
Is the SQL Server instance performance affected by SSRS say when reports are generated?


texts: Is it advisable to use a temp table when the stored procedure could be run simultaneously by different users?

texts:
I am working on a stored procedure that retrieves the objectGUID from active directory.  I am storing the result in a temp table and then returning the value in an output parameter for use with other processes.  The SP will be called from different stored procedures as well as web applications PHP, ASP Classic and ASP.Net.

I read HERE that (regarding temp tables):

  If created inside a stored procedure they are destroyed upon completion of the stored procedure.  Furthermore, the scope of any particular temporary table is the session in which it is created; meaning it is only visible to the current user.  Multiple users could create a temp table named #TableX and any queries run simultaneously would not affect one another - they would remain autonomous transactions and the tables would remain autonomous objects.  You may notice that my sample temporary table name started with a "#" sign.

Sounds like I am good to go but I wanted to get some advice to make sure there aren't any gotchas I am unaware of.  Here is the SP.

Thanks in advance.

CREATE PROCEDURE stp_adlookup
@user varchar(100),
@objectGUID varbinary(256) OUTPUT
AS
SET NOCOUNT ON;
DECLARE @qry char(1000)
CREATE TABLE #tmp(
objectGUID nvarchar(256)
)

SET @qry = 'SELECT *
FROM openquery(ADSI, ''
SELECT  objectGUID
FROM    ''''LDAP://mydomaincontroller.com''''
WHERE sAMAccountName = ''''' + @user + '''''
'')'
INSERT INTO #tmp
EXEC(@qry)
SELECT @objectGUID=CAST(objectGUID as varbinary(256))  FROM #tmp;
DROP TABLE #tmp
SET NOCOUNT OFF;
GO


texts:
I am trying to use the MongoDB shell to run some scripts. Inside the JavaScript I am attempting to run var map = new Map() then map.set(1,1).

However, I am getting the following error:

E QUERY    [js] TypeError: map.set is not a function :

I am running this mongo shell version: MongoDB shell version v4.2.1

Does the mongo shell not support the map.set() function?


texts: Queueing MySQL record inserts to avoid over-subscription of a related resource ... table locking?

texts:
Given a simplified hypothetical of seats in a lifeboat, if I have the following setup with a lifeboats table and a seats table where each record is one occupied seat in the given lifeboat:
CREATE TABLE lifeboats (
  id INT UNSIGNED NOT NULL,
  total_seats TINYINT UNSIGNED NOT NULL,
  PRIMARY KEY (id));

INSERT INTO lifeboats (id, total_seats) VALUES (1, 3);
INSERT INTO lifeboats (id, total_seats) VALUES (2, 5);

CREATE TABLE seats (
  lifeboat_id INT UNSIGNED NOT NULL);

INSERT INTO seats (lifeboat_id) VALUES (1);
INSERT INTO seats (lifeboat_id) VALUES (1);
INSERT INTO seats (lifeboat_id) VALUES (1);
INSERT INTO seats (lifeboat_id) VALUES (2);

I can find lifeboats with available seats by querying:
SELECT
    l.id, l.total_seats, COUNT(s.lifeboat_id) AS seats_taken
FROM
    lifeboats AS l
        LEFT JOIN
    seats AS s ON s.lifeboat_id = l.id
GROUP BY l.id
HAVING COUNT(s.lifeboat_id) < l.total_seats

What is the best way to ensure 2 clients do not grab the last seat in a lifeboat without implementing some coordinating process queue?
My only idea (assuming I'm trying to grab seat in lifeboat 2) is going LOCK TABLE rambo like:
LOCK TABLE seats WRITE, lifeboats AS l READ, seats AS s READ;

INSERT INTO seats (lifeboat_id)
SELECT
    id
FROM
    (SELECT
        l.id, l.total_seats, COUNT(s.lifeboat_id) AS seats_taken
    FROM
        lifeboats AS l
    LEFT JOIN seats AS s ON s.lifeboat_id = l.id
    WHERE l.id = 2
    GROUP BY l.id
    HAVING COUNT(s.lifeboat_id) < l.total_seats) AS still_available;

UNLOCK TABLES;

but this is not very elegant, needless to say.
(My environment is MySQL8/InnoDB)
UPDATE ... Another go:
I've been called out for giving a bad example. The question is really just:
For a given table, how would you best limit (to X) the number of records inserted with a given value Y?
The process receives the limit X & value Y , you query the existing records where value = Y to see if you are under the limit X or not, and if so you insert the record.
But obviously you risk 2 people grabbing the "last" record unless you ... do something .... but what? (I thought the lifeboat analogy was actually a good one!)
Idea one: Write lock the table before beginning the process. Other processes forced to wait. But this stops everybody ... including others with a different value Y.
Idea/Question 2: If I have a 2nd table t2 with unique set of all the Y values and my select "count of Y" query includes t2 reference + a "FOR UPDATE OF t2", will the write lock placed on the Y row in t2 effectively force processes with value=Y to wait until others have completed the process?


texts: Combining retail sales and payment OLAP cubes

texts:
I'm using SQL Server 2005 Standard Edition Analysis Services (SSAS). I'm trying to build cubes from a retail OLTP database that can answer these kinds of questions:

List down all products that were paid with certain type of payment (which products are bought with voucher type X, etc)
Create a breakdown of frequency of payment type used to pay for product X (how many times cash is used to pay for product X, how many times debit, credit, voucher, etc)

There are 3 main transaction tables: SALESHEADERS, SALESDETAILS, and SALESPAYMENTS.

SALESHEADERS contains sales date, cashier, POS code, etc.
SALESDETAILS contains header code, product code, quantity, unit price, gross, discount, etc.
SALESPAYMENTS contains header code, payment type code, payment amount, etc.

One transaction can contain many products. One transaction can be paid with multiple types of payment. There is no connections between detail and payment except through header (sales ID).

Currently I have created 2 cubes, one sales cube and one payment cube.

Sales cube is created by joining the header and detail. Payment cube is created by joining the header and payment.

Both cubes are performing well, but they can't answer the questions at the top of my post.


texts: MySQL listens at port 3306 even after changing it

texts:
I changed MySQL default port from 3306 to 12929 in my.cnf in both client and mysqld sections on Debian.

After that I restarted MySQL service. Then I ran netstat -tln and saw that MySQL is still listening at port 3306.

Why is that?


texts:
I have a large database, about 30GB. In order to delete some data from a table (most data is in this table, simply because the # of rows), I have to do delete in batch. However, if I delete in too many batches, the system becomes very slow. So, what we do is: query the # of data that will be deleted, then use 1/10 of the # as batch size.

When I test this, I found a weird thing.

since it has lots of data to be deleted, the delete batch loop takes a long time to finish. during the delete, when I use sp_who2 to check, I can see the CPU time and DiskIO of 2 SPID keep changing. One is for DELETE and one is for SELECT. the delete is for bacth delete. but the SELECT is for getting the # of data that will be deleted.

I'm using the sqlcommand in c# to do this. The SELECT was done first, once get the #, then we do DELETE. The log actually shows the result of SELECT. My question is: why sp_who2 still shows the CPU time and DiskIO changing? Should it be done already? what can cause this?

and at the same time, the CPU Time and DiskIO for delete is changing too.

anyone has an explaination of this?

thanks


texts: Getting locked out of PostgreSQL databases

texts:
One of the greatest fears I have is getting locked out of my own data due to the security features of a heavy weight database such as PostgreSQL (and PostGIS).  Whilst I can log in as the administrator, I would like to do things properly and create users with defined roles.  unfortunately in my preliminary testing, I have never been able to get this to work reliably.  I clearly do not understand the security model of PostgreSQL as managed by pgAdmin III.
Questions:

Is there a simple guide to setting up roles and users in PostgreSQL?
Can data be recovered in the case of lost passwords?
Can PostgreSQL be used with security off similar to ms-access?
In the PostgreSQL security model, the users are created at server level and can be assigned to different roles in each of the databases listed within a server?
Is there a trick with creating users and roles (see below)?

In ms-access if a user is created called 'ted' and given a password, then deleted and then a user called 'ted' recreated.  The first ted and second ted are not the same.  In addition to the visible name, there is a hidden unique code which is generated each time a user is created.  This makes it impossible to recreate a deleted user.  I just wonder if PostgreSQL has hidden logic like that that can bamboozle a new user.

The key issue I am having is that when I define permissions for a user on a particular database via PgAdmin III, I can't connect to the database using that users name and password from QGIS.

Currently, I am using SpatialLite to avoid this issue.


texts: Drop all tables from database command apart from named few

texts:
I have been looking for a command to drop all tables from a single database apart from a few tables without doing something like this:

DROP table table1, table2, table3

and ignoring the tables I do not want to drop. Is there a better way to do this by dropping all tables apart from the few that I do not want to drop?


texts: Easiest way of shutting down a PostgreSQL server on Ubuntu

texts:
According to pg_lsclusters I have 2 servers running on Ubuntu 14.04:

    Ver Cluster Port Status Owner    Data directory               Log file
    9.1 main    5432 online postgres /var/lib/postgresql/9.1/main /var/log/postgresql/postgresql-9.1-main.log
    9.3 main    5433 online postgres /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.3-main.log

What is the easiest way of shutting down the 9.1 server forever?

Using:

    $/usr/lib/postgresql/9.1/bin/pg_ctl stop

I get the error:

    pg_ctl: kein Datenbankverzeichnis angegeben und Umgebungsvariable PGDATA nicht gesetzt

(no database directory given and environment variable PGDATA not set)

What is the meaning of this error?


texts:
I'm running a postgres database on a windows server. Windows resource monitor tells me that I have about a dozen postgres.exe processes running, and about 4 times that number listed as 'terminated'.
My questions are

Why so many terminated processes?
How can I tell what they were doing before they were terminated?
Is there any way to find out why they were terminated?

I know that just having the postgres server up and running accounts for about half a dozen processes, and each connection to the database should account for one more process. so that leaves about 50 un-accounted for!


texts: Conditions that can make a MySQL COMMIT query fail?

texts:
I have an application where I run explicit COMMIT query on a MySQL standalone server
COMMIT;

The query works as expected in the vast majority of cases, but sometimes the query seems successful but at the same time seems not commiting the transaction to the db.
So I was digging in the possibility that a COMMIT query could fail. I found this similar question: https://stackoverflow.com/questions/3960189/can-a-commit-statement-in-sql-ever-fail-how
but on the official MySQL doc never mentions that a COMMIT can fail.
I want to understand in which conditions a commit query can fail and how to reproduce it, possibly supported by official doc pages.


texts: Restoring dump(backup): ERROR 1022 (23000) at line 44: Can't write; duplicate key in table

texts:
I'm doing tests to change a current table from my InnoDB database to Archive, but I had a problem trying to restore a dump after the change.

After several tests, the problem is related to the value declared in the AUTO_INCREMENT attribute in CREATE TABLE. But this script is created automatically.

How can I fix this error so that I can restore the dump quickly without having to change it?

...

--
-- Table structure for table `tabela`
--

DROP TABLE IF EXISTS `tabela`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tabela` (
  `cd_tabela` int(11) NOT NULL AUTO_INCREMENT,
  ...
  PRIMARY KEY (`tabela`)
) ENGINE=ARCHIVE AUTO_INCREMENT=3081487 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tabela`
--

LOCK TABLES `tabela` WRITE;
/*!40000 ALTER TABLE `tabela` DISABLE KEYS */;
INSERT INTO `tabela` VALUES (1,...), ... (in this line the error appears)

...


texts: If I created 3 mongodb users: user1, user2, user3, as a mongodb admin with root privilege. Is there any way that I can check the login (as well as usage) history of these users?


texts: Cannot delete MySQL trigger. Appears in information_schema, but no .TRN file

texts:
I have a MySQL database with a few tables and triggers. A process was trying to drop one of the triggers and it failed with the following error:

Malformed file type header in file 'trigger_name.TRN'

I tried to drop the trigger manually, but obviously got the same error. So I went ahead and deleted the .TRN file itself (was that a bad idea?).

After deleting the .TRN file, the trigger still appears in information_schema.TRIGGERS. If I try to drop it I get error "Trigger does not exist". If I try to create another trigger with the same name, table and event manipulation I get error "Trigger already exists".

I don't know what else to do. How can I really delete this trigger so that it can be created again?

MySQL version is 5.0.22 and all tables are MyISAM.

SHOW CREATE TABLE outputs:

CREATE TABLE `cash_operation` (
  `id` int(11) NOT NULL auto_increment,
  `amount` decimal(16,8) NOT NULL default '0.00000000',
  `comment` varchar(500) NOT NULL default '',
  `reason_id` int(11) NOT NULL default '-1',
  `date` datetime NOT NULL default '1980-01-01 00:00:00',
  `reconciliation_number` int(11) NOT NULL default '-1',
  `reason_desc` varchar(250) NOT NULL default '',
  `automatic` tinyint(1) NOT NULL default '0',
  `employee_id` int(11) NOT NULL default '-1',
  `employee_code` varchar(250) NOT NULL default '',
  `employee_name` varchar(250) NOT NULL default '',
  `payed_employee_id` int(11) NOT NULL default '-1',
  PRIMARY KEY  (`id`),
  KEY `cash_operation_reason_id` USING BTREE (`reason_id`),
  KEY `cash_operation_reconciliation_number` USING BTREE (`reconciliation_number`),
  KEY `cash_operation_automatic` USING BTREE (`automatic`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

SELECT @@global.datadir; results in error "Unkown system variable 'datadir'"


texts:
I have a search like the example below. The problem is that the result returns a key muliple times if the term "beach" is in myTextColumn multiple times, for example "I went to the beach because the beach is cool". I need it to be returned only once.

  SELECT DISTINCT
    Src.[Key] AS MyID
FROM
    CONTAINSTABLE(MyTable, myTextColumn, N'FORMSOF(INFLECTIONAL,beach )',500) Src
ORDER BY
    MyID


texts:
I have an Alwayson test environment with the following settings.

My test goals are as follows:

When SqlNod1 is primary instance, I want SqlNod2 to answer read-only queries.
When SqlNod2 is primary instance, I do not want SqlNod1 to answer any queries.
I want to use SqlNod3 to mimic offsite replica (with poor network, all my nodes are virtual machines i am planning to disconnect nic card and connect a slow one etc.)

My problem is, say my primary instance is SqlNod2 and core cluster resources are hosted on SqlNod2, when i fail over using SSMS Alwayson dashboard, Dashboard shows that SqlNod1 is primary instance, but windows failover cluster manager shows that cluster resources is hosted by SqlNod2. When that happens (or vice versa) i cannot make secondary instance to answer any queries.

In order to complete failover i have to move cluster resources to same node using WSFC Manager. Is this the default behaviour? I feel something is wrong, not being able to failover using only TSQL and needing WSFC Manager.


texts: If i restore a DB to a DB that has beed modified vill it work? In case the modification and the backup could be compatible like if a new table has been added without required connections to it, or an existing table has a new column that can be null.


texts:
Goodafternoon,

i am looking for a variable for the date and time. I have several backups in one directory and want to restore the latest to a another server. Is there a variable i can use in my script?

test_db_201508**162300**.BAK

the script

restore database [test]
from disk = N'R:\testserver\test_db_201508**162300.BAK**'
with file = 1
    ,move N'test' to N'E:\Databases\test.mdf'
    ,move N'test_log' to N'F:\Logfiles\test_1.ldf'
    ,NOUNLOAD
    ,REPLACE
    ,STATS = 10
go


texts:
I'm attempting to count the number of items if they are not within 30 seconds of the first item in a "group". I'm having a hard time figuring this out.

So, I have this table:

WITH ADates AS (
    SELECT
          Id
        , SharedId
        , TheDateTime
    FROM (VALUES
        (CAST(1 AS int), CAST(1 AS int), CAST('2019-01-01 01:01:00.00' AS datetime2(7))),
        (2, 1, '2019-01-01 01:01:33.00'),
        (3, 1, '2019-01-01 01:02:00.00'),
        (4, 1, '2019-01-01 01:02:01.00'),
        (5, 1, '2019-01-01 01:02:04.00'),
        (6, 1, '2019-01-01 01:06:15.00'),
        (7, 2, '2019-01-01 01:06:00.00'),
        (8, 2, '2019-01-01 01:06:45.00'),
        (9, 1, '2019-01-01 01:02:31.00'),
        (10, 2, '2019-01-01 01:06:05.00'),
        (11, 2, '2019-01-01 01:06:46.00'),
    ) X (Id, SharedId, TheDateTime)
)

So, the expected result that I'm looking for is:

+==========+=======+
| SharedId | Count |
+==========+=======+
| 1        | 4     |
+----------+-------+
| 2        | 2     |
+----------+-------+

The numbers are determined by:

Count since first in new group.
Not within 30 seconds of previous group so it is a new group and count.
Don't count since it is within 30 seconds of 2.
Don't count since it is within 30 seconds of 2.
Count since not within 30 seconds of previous group (Item 2).
Count since not within 30 seconds of previous group (item 2).
Count in new group for SharedId.
Count since not within previous grouping.

I'm thinking I should be doing a Window Function for this. Just not sure how to have it rely on just the first of the group.


texts: Does my choice of pg_dump format impact restore speed?

texts:
According to the docs for my version of Postgres (8.4.2), pg_dump can "output in script or archive file formats."  The script-formatted dump (4GB) takes 1.5 hours to restore, and I'm wondering if the archive format is any faster.

My script-formatted dump is using COPY commands, by the way, so it's not like I'm doing one insert at a time.

Thanks in advance.


texts: join from different tables gives same result, but it shouldn't

texts:
This is the code I'm using and I'm having a problem with it.

SELECT `disenos`.*, `votos`.`valoracion`, `usuario_contacto`.`nombres`,
`usuario_contacto`.`apellidos`, `users`.`username`, `users`.`avatar` as usuario_avatar,
COUNT(comentarios.id_comentario) as comentarios,
COUNT(votos.id_usuario) as votos
FROM (`disenos`)
LEFT JOIN `votos` ON `votos`.`id_diseno` = `disenos`.`id_diseno` AND votos.id_usuario = 1
LEFT JOIN `users` ON `users`.`id_usuario` = `disenos`.`id_usuario`
LEFT JOIN `usuario_contacto` ON `usuario_contacto`.`id_usuario` = `disenos`.`id_usuario`
LEFT JOIN `comentarios` ON `comentarios`.`id_diseno` = `disenos`.`id_diseno`
WHERE `disenos`.`estado` = 1 AND `disenos`.`votacion` = 1
GROUP BY `disenos`.`id_diseno` LIMIT 5

both COUNTs are giving me the same result although they shouldn't. If I get rid of the

LEFT JOIN `comentarios` ON `comentarios`.`id_diseno` = `disenos`.`id_diseno`

the COUNT(votos.id_usuario) as votos gives the correct result.

I've been dealing with this for the last 4 hours and can't seem to get it solved so I appeal to your wisdom :D

Thanks in advance!


texts: How to enhance the SQL query performance for thousands and millions of huge data

texts:
Now Our team are involved in a forum data analysis project. Its target data is so huge(I think). We should find the potential informations from it. Such as the popular products,the customers activity,also the complains of customers.

one case:(we try to analysis the customers' complain post)
we have the following tables:

Threads(ThreadID,TechnologyID,CreateTime,IsAnswer)20 millions of data

Posts(PostID,ThreadID,CreateTime,IsAnswer) 100 millions of data
--(multiply posts can set as Answer)

CustomerConcerns(ThreadID,PostID,NegativeWords,Verbatim)
--(if complained, there will be a record in it.)

 We also contain many mapping tables(millions).

(When thread was set as Answered but it still have customers involved in it and it recored in the CustomerConcerns table. It looked as one complain.)

So from the three tables, we need to find the complained threads， also how many compains after answered.

select t1.ThreadID,t1.CreateTime,MaxAnswerDate,MinAnswerDate,p1.MaxReplyDate,p1.Replies,p1.Users
from
(
    select  th.ThreadID,th.CreateTime,max(p.CreateTime) as MaxAnswerDate,min(p.CreateTime) as MinAnswerDate
    from Thread th,TopConcernTest tt,Post p
    where  tt.ThreadID=th.ThreadID and tt.[Type]=1 and th.LanguageID=58 and th.TechnologyID=''
        and th.IsAnswer=1
        and th.BigintTime>=20130101 and th.BigintTime<=20130501
        and p.ThreadID=th.ThreadID and p.IsAnswer=1
        group by th.ThreadID,th.CreateTime
    -- First I try to get the MaxAnswerDate and MinAnswerDate of the threads.
    -- Because The TopConcerTest is small.(compare with thread, post) So I joined with
    -- it.
)t1
cross apply
(
    select max(tp.CreateTime) as MaxReplyDate,Count(tp.PostID) as Replies,count(distinct tp.AuthorID) Users
        from
    (
        select p.CreateTime,p.IsAnswer,p.AuthorID,p.PostID from Post p
        where t1.threadID=p.ThreadID
    )tp
    where tp.IsAnswer<>1 and tp.CreateTime>MinAnswerDate
                )p1
    where p1.MaxReplyDate is not null
    -- I use a cross apply here. Because I think I have minimize the data before.
    -- And I try to get the MaxReplyDate and How many post and unique users
    -- after the first answered reply.
)t2
inner join
(
    select tt.ID as TopConcerID,th.ThreadID,th.ThreadTitle,th.ThreadUrl,p.PostID,p.AuthorID,p.CreateTime as PostCreateTime,tt.Entities,tt.NegativeWords,tt.Verbatim
    from Thread th,TopConcernTest tt,Post p
    where  tt.ThreadID=th.ThreadID and tt.[Type]=1 and th.LanguageID=58 and th.TechnologyID=''
        and th.BigintTime>=20130101 and th.BigintTime<=20130501
        and th.IsAnswer=1
        and p.ThreadID=th.ThreadID and tt.PostID=p.PostID
        )p2
    on t2.ThreadID=p2.ThreadID and p2.PostCreateTime>t2.MinAnswerDate
    -- I get the threadTitle,ThreadUrl and the Complain data here.
    -- You see I may get these data before instead to query them(ThreadTable,TopConcernTable) again. But I found I will spend much time to group them
    -- and order.
        )t3
where t3.ID between 1 and 20
-- Actually I have a page split at here. For I still have to query more infos.

This is my query. Sorry it's so complex. But My questions most of the time I can hardly to tun my code.

I want to minimize the range of my query data. So I use the TopConcernTest table at two place. But I also doubt whether I should scan the table twice?
You see I scan the Post table 3 times(It a very big table.), because I have to get the MaxAnsweredTime,MinAnsweredTime,MaxReplyTime. The three time are incompatible.
Whether I should scan 3 times?
I had try to get the data from TopConcernTest table one time. But it cost much time to group,or order the data.

Because our customers' query are mostly base on 3 variables:technology,language,time. I double whether I should set these three variables into every table, so I can minimize the range of data. base the results of subquery? (It will make too many duplicate data?


texts: Unable to start SQL Server Integration Services 12.0 service (SQL Server 2014) after upgrade to SQL Server 2019 and patched to CU4

texts:
I upgraded a SQL Server 2014 Standard instance to SQL Server 2019 Standard (also required upgrading the OS from Windows Server 2012 R2 to Windows Server 2019), and then patched to CU4.

During the SQL Server upgrade process, everything showed green checks at the end (including Integration Services), so nothing to indicate that there was an issue with upgrading the Integration Services (or any other features or components for that matter).

After doing the necessary reboots, now the SQL Server Integration Services 12.0 service will not start.

The Event Viewer Application log shows the following for Source SQLISService120 (Event ID 260):

  Microsoft SSIS Service failed to start. Error: The SQL Server
  Integration Services service requires Integration Services to be
  installed by one of these editions of %SQL_PRODUCT_SHORT_NAME%:
  Standard, Enterprise, Developer, or Evaluation. To install Integration
  Services, run SQL Server Setup and select Integration Services.

I have a legacy 3rd party ETL solution that utilizes CLR and Service Broker that are hard-code dependent on the 12.0 Integration Service, so I need to get this running again as those ETLs are failing (despite having the newer SSIS version installed). Let's assume that I cannot change the application code, so I need to get this service running again.

I have already tried to use the SQL S014 (with Service Pack 2) installation media to try to install only the Integration Services feature (as suggested by the Event Viewer), but Integration Services is already checked and grayed out, preventing me from attempting to re-install it.

This 3rd party ETL solution throws the following error when they execute and fail:

  Integration Services evaluation period has expired.

However, I'm not sure if that is a misleading error because the 12.0 service is offline, or if it found the binaries but thinks it is expired?

I wasn't here when this was initially implemented 6 years ago in 2014, so I don't think I have a way to confirm if this environment started as Evaluation then was upgraded to Standard.

I have already tried changing the CommonFiles DWORD from 1 to 3 at  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\150\ConfigurationState, described in both "SSIS Evaluation Period Has Expired" on Dev Instance? and Microsoft guidance at https://support.microsoft.com/en-us/help/971268/evaluation-period-has-expired-error-message-when-working-with-sql-serv described in Case 3 Option 2 section and re-running the SQL 2019 Upgrade Edition to try to get it to Standard Edition.

Not sure if this is a clue, but following the question posed by the answer in this post https://stackoverflow.com/questions/527839/ssis-package-fails-on-execute-integration-services-evaluation-period-has-expire/528479#528479 :

  Check what version of DTEXEC you are using as default (start-run: cmd, dtexec)

When I do dtexec from a cmd prompt, it says it's Version 12.0.5659.1 for 64-bit which seems to indicate the default dtexec is the old one.

Is there a way to change the default dtexec to be the new SQL 2019 version and perhaps that will allow the 3rd party ETLs to work (without needing the IS 12.0 service)?

Otherwise:

What other options or ways do I have to get the Integration Services 12.0 service to start successfully so that I can run these legacy ETLs?


texts:
I am able to connect to a remote SQL Server with the instance name using PowerShell. I am using an XML file (something like a configuration file) which includes all my database details. PowerShell uses this for connection establishment.

Now I want to get all "schemas" associated with a particular database that is already defined in that XML file. How can I achieve that using Powershell scripting?

Any help would be really appreciated.


texts: Procedure showing different duration / reads in Profiler vs. SSMS

texts:
I have a database which is getting a lot of calls to the following procedure:

exec dbo.SM_SP_MAILEVENTNAMES 167260

In Profiler using the TSQL_Duration trace these calls show up as:

EventClass: RPC: Completed
Duration:   900 to 1500 ms
Reads:      8818

Running the same query in Management Studio with Statistics I/O:

set statistics io on
exec dbo.SM_SP_MAILEVENTNAMES 167260

Runs instantaneously and has the following I/O output:

  Table 'SM_MAIL_CONTACTS'. Scan count 0, logical reads 29, physical reads 0, read-ahead
  reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'SM_MAIL_EVENT_CONTACTS'. Scan count 13, logical reads 68, physical reads 0,
  read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'SM_MATTER_EVENTS'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The query itself returns about 3 rows.  Why the disparity in what I am seeing in Profiler and Management Studio?


texts: How to retrieve rows with max value in one column for each combination of other two columns?

texts:
I need to query an SQL database to find all records with max RATE for each combination of fields A and B. For example, consider the following table with 4 columns, A, B, RATE & VALUE:

A B RATE VALUE
= = ==== =====
a b 10   100
a b 2    110
a c 1    20
a c 3    55
a d 8    22
b c 3    10

I wish to get back one row from each distinct pair A & B with max RATE, getting these rows:

A B RATE VALUE
= = ==== =====
a b 10   100
a c 3    55
a d 8    22
b c 3    10

How can I formulate such a query in SQL?


texts: alter database backup controlfile vs cp Linux file

texts:
We have the following explanation in the documentation:

  Back up the control file to a binary file (duplicate of existing
  control file) using the following statement:

ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/backup/control.bkp';

My question is: why do I when modify control_files parameter to use the controlfile backed up with the alter database statement I need to do a recover the database and with cp command not? what is the difference between cp and the backup command?


texts: mac os - virtualbox - oracle connection error - invalid username/password; logon denied

texts:
I have followed the below step guide(URL) to setup Oracle on Mac OS. Everything is fine on the guest side. But, I am facing some issues in host level.

I can connect database in Guest (Virtual box) but, when I tried to open the connection from host then I got a below error message.

"ORA-01017: invalid username/password; logon denied.

Note: I am unable to do the step to stop the firewall on Guest.

https://dimitrisli.wordpress.com/2012/08/08/how-to-install-oracle-database-on-mac-os-any-version/

http://blog.codiez.co.za/2013/09/setup-oracle-instant-client-ruby-oci8-gem-mac/

sqlplus hr/oracle@cdb1

SQL*Plus: Release 11.2.0.4.0 Production on Wed Nov 18 13:32:48 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

Update: file - /usr/local/oracle/admin/network/tnsnames.ora

cdb1=
 (description=
   (address_list=
     (address = (protocol = TCP)(host = 127.0.0.1)(port = 1521))
   )
 (connect_data =
   (service_name=cdb1)
 )
)


texts:
Getting this error on AWS RDS with MS SQL

  Database XXX cannot be restored because there is already an existing
  database with the same file_guids on the instance.

I've imported from S3 once, it worked. Deleted the database and tried to import a more recent version FULL DB and it broke.

I've created a new instance also but still get the error. I can import other DBs from S3 fine.

Anyone have any ideas that can help?


texts: msdb.dbo.sp_send_dbmail temp table does not exist

texts:
I have created a temp table and would like to email the results I am getting the following error.

  Query execution failed: Msg 208, Level 16, State 1, Server
  PRODYCHDBX3\X3V7, Line 1 Invalid object name '#WODups'.

After the temp table is created I ran the following code.

 DECLARE             @recordcount INT
 SELECT @recordcount = ISNULL(COUNT(*),0)
 FROM #WODups

 IF (@recordcount > 0)

 BEGIN

 SELECT * FROM #WODups
 EXEC msdb.dbo.sp_send_dbmail
 @profile_name = 'SQL Mail',
 @recipients = 'jXXn.XXXn??@yXXXXXX.com;XXXXXXXX1@gmail.com',
 @query = 'SELECT FROM #WODups',

 @subject = 'Duplicate Work Order Production Tracking',
 --@attach_query_result_as_file = 1,
 @body =0;

 DROP TABLE #WODups

Why am I getting this error, and how may I resolve it?


texts: calculate the difference between two column aliases in a SELECT statement?

texts:
I'm not sure how to do the last part of my query, which is doing calculations directly with the aliases: (SUM(task_avg - minutes) AS difference.  I don't think I can do it as easy as I'd like, but can't find a means to do it.

$result = mysql_query("SELECT U.user_name, SUM(TA.task_average*M.minutes) AS task_avg, SUM(M.minutes) AS minutes, SUM(task_avg-minutes) AS difference
                            FROM summary S
                            JOIN users U ON U.user_id = S.user_id
                            JOIN tasks TA ON TA.task_id = S.task_id
                            JOIN minutes M ON M.minutes_id = S.minutes_id
                            GROUP BY U.user_name
                            LIMIT 0 , 30");


texts:
I used Shutdown normal, instead of Shutdown immediate.

Now I want to stop the process, because its taking too long.

How can I do it?


texts: Restore a clustered SQL database from a specific time

texts:
I have a 2-node clustered Lab SQL Server and I need to restore the database to the previous day state. This is my first time to try a restore in a cluster. I have a full recovery model and since this environment is rarely used, I have a once a week full backup complemented by 2 differential backups and daily log backups; i.e. Full=Mondays at 1am, Diff=Wed and Fri at 1am, and Log=daily at 9pm.

Now my questions is - if I wish to restore the database on the Monday state, can I simply restore the full backup on Monday (1am), then restore log backup on same day (9pm), then recover the database?

Do I have to worry about restoring any of the master, model or msdb system databases?

Thanks in advance.

Regards,
Trev