texts: Postgres: PANIC: WAL contains references to invalid pages

texts:
I have a slave database that is being replicated from a Master DB.

All of a sudden, my slave DB just exited and wasn't able to restart. Here are the error messages when it exits. Similar ones appear when I restart:

WARNING:  page 21561636 of relation base/16384/54391 is uninitialized
CONTEXT:  xlog redo visible: rel 1663/16384/54391; blk 21561636
PANIC:  WAL contains references to invalid pages
CONTEXT:  xlog redo visible: rel 1663/16384/54391; blk 21561636
LOG:  startup process (PID 1573) was terminated by signal 6: Aborted
LOG:  terminating any other active server processes

Both slave and master are using Postgres 9.2.3 in a Centos 5 quad-core server with 32 GB of RAM each.

When I try to restart the slave, with "zero_damaged_pages=true", the server still refuses to start with lines like the ones above.

At this point, I don't care if the slave ever starts as I can start from scratch. But is this an issue with Postgres? Has anyone encountered this and is it fixed in future versions?

Looking up research online doesn't give me much confidence, so that's why I'm posting here.


texts: What is a good database choice for storing time-series data for billions of users?

texts:
The problem
I have a problem which can be illustrated like so: I need to store the historical transactions for millions (or billions) of stores.
A small percentage (1% or so) of stores will have millions of transactions.
The majority of the stores will have very few transactions (less than 100).
My end goal is to be able to query the DB such that I can:

filter stores with over 1k (as example) historical transaction - the 1%.
get the time series of the revenue of these stores.

What I tried

Time series DB seems to be a good fit, however it works as the number of stores grows to over 10k or 100k, it cannot perform the filter (pruning), as it first needs to group.

Graph DB seems to be a good fit as well, but I don't know if it can handle the time-series component as it is not build for this.

A combination of Graph with Time series can possibly achieve this, where I get the names of the stores from the Graph query and then only keep the historical information for them in the time series. The downside is that this sounds complication and has some flexibility issues.


texts:
I want to write a trigger on one of the tables in my database so that no one can update the column listprice (the price) by more than 10 percent.

I'm totally confused. I can't write the trigger.

The column would be like this:

listprice
1000
2000
3000

and the calculation would be listprice=listprice*1.1


texts: How do I connect to a SQL Server 2008 instance on my network?

texts:
I have 2 SQL instances in 2 virtual machines. I'm able to see the other instance in one machine but I cant connect to it.

Probably some permission problem...but I don't know where to look.

Can someone help me?

edit:
the first problem was in fact not having TCP/IP enabled, solved that and the error changed now I have error 18452. (I'm trying to connect with windows authentication, also tried with sql authentication in that case I get error 18456)

Both SQL instances are default

edit2:
After changing the autentication to windows and SQL, I can login in one of the instances using sql autentication (user SA), on the other...it gives me error 18470, but everything looks ok with that login (at least for me... maybe one of you can tell me were should I look)

edit3:
After enabling login sa everything works as expected in SQL authentication mode.

I change my question to, what if I wanted windows authentication mode? how could I do it?


texts: Insert into junction table and one of the many-to-many tables

texts:
I have these two tables, exercises and workouts, each just for this example having only id and name and a junction table between them workout_exercises, since an exercise can belong to many exercises and vice versa.
When a user then creates a workout, I would want to insert into the workouts table id and name and then into the junction table multiple rows all with the workout id just inserted and then go through all exercises and add a row for each of them.
Which is the best way to do this? Can I somehow send into an sp workout_id workout_name and a list of exercises and then first of insert into workout then loop through the exercise list and insert into the junction table for each iteration?
I feel like this should be possible somehow as calling the db for each exercise in the workout would be very bad. I am quite novice on db design so mostly asking if there is a best practice for these kind of inserts

The idea here is that a user will on the website when creating a workout give it a name and search in a list all exercises from db and add the exercises it want to belong to the workout they are creating. On saving I want to save the workout and the relationship between the selected exercises and the new workout.
When adding a exercise, it will just insert into the exercises table. When adding a workout, it will insert into both the workouts table and the workout_exercises table. The exercises will come from the frontend as a list and be handled backend wise as a List of objects having the same properties as the exercises table.


texts:
We have TWO of: your standard 2-node active/passive Windows Server 2008 R2 with SQL Server 2008 SP1 Cluster with 1 TB of RAM on the active node and 512 GB on the passive (same on both clusters).

Both OS and SQL "see" the 1 TB of memory and additionally set the following: "min memory" setting to 450 000 (MB) and "max memory" setting to 950 000 (MB)

The problem: every possible monitoring (perfmon metric; red gate sql monitor, task manager, resource monitor etc....) and every possible DMV query show that BOTH clusters cap at exactly 432.6 memory used (PerfMon reports the following for "SQLServer:Memory Manager - Total Server Memory " = 432.6 GB)

I understand the concept behind "it will only use as much as it needs", but I'm certain it can stuff more in there, because the clusters each host 2000+ databases for a total of more than 2 TB worth of often accessed data.

PLUS why would BOTH cluster need exactly 432.6 GB?


texts: How to find all participants of a chat thread?

texts:
In my database I have the following tables: user, post, post_participant, and post_reply, largely as inspiration from this answer.

One thing I'm having trouble with figuring out though is how to find all the participants of a post.

For example, one way would be to just do a SELECT DISTINCT user on the post_reply table for a given post_id, but this would be missing the original poster if they hadn't replied to their own thread.

Okay, easy enough: do a SELECT on the post table as well and then do UNION ALL with the SELECT on the post_reply table. The problem with that though is that for my application I need to order this result set by post date, so the author should always be on top, and UNION ALL messes up the order.

Okay, fair enough, I can then just use multiple CTEs (union the first, distinct the second, and then finally order by). Except at this point it's become quite complex just to find out all the participants for a post.

The SELECT DISTINCT may also get slow once a post has many replies as well, since I'd imagine it's O(n).

An alternative is that the original poster and all posters can be added to the post_participant table.

The problem with that though, is that I'm not really sure if it's possible to do this efficiently. Do I just need to do INSERT INTO post_participant ON CONFLICT DO NOTHING literally every time anyone makes a reply to a post? That seems extremely inefficient. Or is there a way to cheaply determine whether or not it's someone's first reply to a post, to avoid this database call?

It seems like there are no good solutions to this. Either I have to do an additional write attempt on every reply call, or I have to do a slow and complex DISTINCT UNION every time a post is opened. Is there a better way?


texts: How do I cast a varchar to Bigint in DB2

texts:
I have a field which is varchar(25) and I need to create a join between that field and a field that is a bigint

So I guess I need to convert the field into bigint aswell but it's giving me some grief because one result has an alphabet in it.
How do I make it ignore the alphabet so I can create a join.

I'm currently trying
left join cast(queens.studentid as bigint) = north.studentid

Any help would be appreciated


texts: Is it mandatory to enable --log_slave_updates with MySQL Replication using GTID?

texts:
I'm setting up 5.6 Percona Mysql, in order to enable GTID should I have to enable log_slave_update on both Master and Slave ?

If I should enable log_slave_update, then I'm counting this to CONS for having GTID.

Also if you can let me know any other issues in having GTID enabled?


texts: Auto-update hierarchyid of multiple records in INSTEAD OF UPDATE trigger

texts:
I am working with an INSTEAD OF UPDATE trigger, where I have to calculate / re-calculate the hierarchyid of a row based on another column value. Thus far, I have it working. The problem that I run into however, is that I need all hierarchyid values in this column to be unique; and when ascertaining the last child of x parent, I always get the same result for every row that is being updated in this trigger.

DECLARE @tenantId UNIQUEIDENTIFIER = (SELECT TOP 1 Id FROM Tenant.tenant WHERE Sid=SUSER_SID());
UPDATE dbo.organization
SET
    Name = i.Name,
    Parent = i.Parent,
    Node = COALESCE(
            -- Use existing value for Node if parent hasn't changed
            (SELECT node FROM dbo.organization WHERE tenant_id = @tenantId and id=i.id AND parent = i.parent),
            -- Change Node to root if organization is assigned as a parent to itself
            (SELECT hierarchyid::GetRoot() WHERE i.parent IS NULL OR i.parent = i.id),
            -- Update the value of Node as a new descendant of the updated Parent node
            (SELECT node.GetDescendant(
                (SELECT TOP 1 node FROM dbo.organization
                    WHERE Tenant_Id = @tenantId and Parent = i.Parent AND node.GetLevel() != 0 AND id != i.Id
                    ORDER BY node desc), NULL)
                FROM dbo.organization
                WHERE Tenant_Id = @tenantId AND id = i.parent),
            -- Fallback to insert as new descendant of parent node if NULL was inserted
            (SELECT node.GetDescendant(
                (SELECT TOP 1 node FROM dbo.organization
                    WHERE Tenant_Id=@tenantId AND Parent=i.Parent AND node.GetLevel() != 0 AND id != i.Id
                    ORDER BY node DESC), NULL)
                FROM dbo.organization
                WHERE Tenant_Id = @tenantId AND id=i.Parent AND i.node IS NULL),
            i.Node)
FROM inserted i
WHERE dbo.organization.Tenant_Id = @tenantId AND dbo.organization.Id = i.Id;

EDIT:
I feel I should add some additional info here.
An example: If you were to update 100 rows via this trigger, and set their Parent column to point to the record containing the root hierarchyid (/), all of the values updated by this trigger would be set to /1/ instead of /1/, /2/, /3/... etc.

Any insight that can be provided would be greatly appreciated.


texts:
Today, after a power failure, one database (with Recovery: full) shows "In Recovery" in SSMS. So:

  myDatabase (In recovery) (database status: recovery, Shutdown)

After finish, the "recovery process" the database shows the name myDatabase without "(In recovery)". I thought that the problem was solved, but it was not.

When I started the application that uses that database, the extra text "(In recovery)" appears again next to the name of my database.

I waited until the "recovery process" finished and then I took the database offline and brought it back online.

I restarted the server, restarted the computer and when my application was running the extra text appears again. In the SQL Server logs the message "Starting up database 'myDatabase'" appears few times. It seems that the database is working because I can insert data, but the state is showing that something it happens.

The server log is not showing anything interesting. The only abnormal thing is that I have 30 entries of "Starting up database 'myDatabase'".

I know that when the server starts every database goes through recovery before it is ready for use. But in my case, the database comes online then shows "myDatabase (In recovery)". If I close the application, the database goes to Status: Normal. This is driving me crazy.

I've even installed a new instance of SQL Server, and put the old database "myDatabase" on it. The problem still happens.

When I run this query:

SELECT databasepropertyex('nyDatabase', 'STATUS')

It shows recovering, online, suspect and the back to online and then recovering and so on.


texts: No "Using locked pages for buffer pool" in SQL Server log

texts:
As a trial run for a client I am trying to enable the "Lock Pages in Memory" setting on my development server, but no matter what I change, I cannot get the "Using locked pages for buffer pool" to show up in the SQL Server log. I want to make sure I can do this correctly before I do it for a client.

Our development server is a Windows 2003 with 4 GB of RAM, SQL Server 2008 R2 Standard Edition 32Bit.

Below is what I have attempted:

Checked "Use AWE to allocate memory".
Set the "Maximum server memory"
setting.
Added startup parameter -T845 in the
SQL Server service configuration.
The SQL Server service is running on
the LocalSystem Account so from what
I have read I do not need to enter an
account in the "Lock Pages in Memory"
priveledge. However, I did try
changing the service to operate under
the "Administrator" login, and added
that login to the "Lock Pages in
Memory" privelege.
Restarted SQL Server Service.

Nothing I have done will put

  Using locked pages for buffer pool

into my SQL Server log, but the 845 trace appears to be turned on when I run DBCC TRACESTATUS.

Anyone have any ideas about why nothing I have done appears to turn this on?


texts:
I can not find my SQL Server logs for all days in the past. In only found for last 4 days. Is it because that I restarted the server and logs are dumped if the hosting server is restarted?

How can I keep entire logs?

I am using the GUI to view the logs.


texts: Properly delete data forever and free up the space it was using?

texts:
I've been having issues finding a real answer to this question as the response is always "Don't do this, it's a bad idea, that space was obviously needed and it will just get used up again in quick order." But that response doesn't apply to me - I have a copy of the production database that I am using solely for developmental purposes - my read/write activites to be limited to a small amount of the database, but I will need to constantly restore the database to overwrite my changes, so I want to reduce the size of the database - I can achieve this by deleting the data in a handful of tables.
Here is what I have done so far:

Made sure database was in simple recovery mode
Ran DELETE FROM TABLE_NAME queries
Tasks > Shrink > Files > File Type: Log, Release Unused Space - this step was successful and reduced the .ldf to its initial minimum size.
Tasks > Shrink > Files > File Type: Data, Release Unused Space - this step did not do anything. It "executed" in half a millisecond and the .mdf is just as big as it was before running the DELETE queries...

Any input appreciated.


texts:
I want to debug in TSQL.When I start the debug the following message box popup. In SSMS v17.2

Then I see this message:

In Microsoft Visual Studio 2015 Server Explorer same Error:

Then I see this message:

Thank You!


texts: Using PostgreSQL functions instead of joining?

texts:
I don't have a question on how to do something but if I should. Sorry if there's a more appropriate forum.

I have a function:

CREATE OR REPLACE FUNCTION get_name(user_id UUID) RETURNS TEXT AS $$
    SELECT username
    FROM   users
    WHERE  users.id=user_id
$$ LANGUAGE 'sql';

I like using this function to translate from user_id to username in other queries. It's a common operation and I thought a function would speed up development time in looking up one-off stats for data analysis purposes. I am not concerned about query speeds. Is this good practice to use a function to perform this operation or should I just perform a join?

I am new to this and my coworker said it was bad practice to use functions for things that regular selects/ joins can perform. He also mentioned that functions should preferably be used for things that regular selects just can't do. So I wanted to ask other people what good practices are?

Edit: If this is a bad practice, would it be more reasonable for the case when the function does a common operation that's a little more complicated?


texts: How to safely change MySQL innodb variable 'innodb_log_file_size'?

texts:
So I'm fairly new to tuning InnoDB. I'm slowly changing tables (where necessary) from MyIsam to InnoDB. I've got about 100MB in innodb, so I increased the innodb_buffer_pool_size variable to 128MB:

mysql> show variables like 'innodb_buffer%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.00 sec)

When I went to change the innodb_log_file_size value (example my.cnf on mysql's innodb configuration page comments to change the log file size to 25% of the buffer size. So now my my.cnf looks like this:

# innodb
innodb_buffer_pool_size = 128M
innodb_log_file_size = 32M

When I restart the server, I get this error:

  110216  9:48:41  InnoDB: Initializing buffer pool, size = 128.0M
  110216  9:48:41  InnoDB: Completed initialization of buffer pool
  InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
  InnoDB: than specified in the .cnf file 0 33554432 bytes!
  110216  9:48:41 [ERROR] Plugin 'InnoDB' init function returned error.
  110216  9:48:41 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

So my question: Is it safe to delete the old log_files, or is there another method to change the innodb_log_file_size variable?


texts:
SELECT        dbo.Contracts.ContractID AS [Service Unit ID], dbo.Jobs.JobName, dbo.Customers.CustomerName, dbo.Contracts.ContractAutoID, dbo.Contracts.ContractType, dbo.Contracts.ContractStatus AS [Unit Status],
                         dbo.Contracts.Area
FROM            dbo.Customers WITH (READPAST) INNER JOIN
                         dbo.Jobs WITH (READPAST) ON dbo.Customers.CustomerID = dbo.Jobs.CustomerID INNER JOIN
                         dbo.Contracts WITH (READPAST) ON dbo.Jobs.JobID = dbo.Contracts.JobID
WHERE        (dbo.Contracts.ContractType = 'service')

I understand Hash Match indicates the indexing is not appropriate, which is why it takes up so much space. I am wondering what i need to different with my indexing to get these Hash Matches to become nested loops. Below is a picture of the Execution Plans.

  Customer Index

  Index Key Column: CustomerID

  Included Columns: City, CustomerStatus, CustomerName

  Contracts Index

  Index Key Column: ContractType

  Included Columns: ContractAutoID, JobID, Area, ContractStatus,
  ContractID

  Jobs Index

  Index Key Column: JobID

  Included Columns: CustomerID, JobName, Area


texts: Can I bulk insert into an empty page-compressed table and get full compression?

texts:
I have a lot of large tables (around 10 million wide rows) which need to be regularly loaded into SQL Server 2016 for read-only reporting. I would like these tables to be as small as possible on disk, and this matters more than performance improvements in either loading or querying.

Here is what I have been doing for the tables which require no further indexing:

Create the table with DATA_COMPRESSION=PAGE.
Use bcp to bulk insert the data from a flat file into the new table.

Column types in the tables are varchar (never more than 512, not max), float, tinyint, or date (not datetime). All columns are created as nullable and no primary or foreign keys are defined -- they don't matter for the querying and the tables are never updated directly. Default collation on everything is SQL_Latin1_General_CP1_CI_AS.

When I do this, I can see in sys.allocation_units that page data compression has been applied to the heap and I can see in sys.partitions that the fill factor is correctly 0 (100%). Since the tables are much smaller than uncompressed tables would be, I thought the compression was accomplished.

However, if I then rebuild with the same option DATA_COMPRESSION=PAGE, the supposedly-already-compressed table gets about 30% smaller! It looks like it's going from about 17 rows per data page to 25 rows per page. (Only once, though. Rebuilding again after that doesn't make it any smaller than the first rebuild did.)

The questions

So my questions are: (a) what is going on here? and (b) is there a way to get this extra-small compressed size directly as I load the table without having to rebuild after the data is loaded?


texts: Not able to restore a database with multiple backup files from Azure blob storage (block blobs)

texts:
I was testing the backup with multiple backup files (6) and I was able to successfully take backup for one of my large database (3TB) to my azure storage account by using MAXTRANSFERSIZE, BLOCKSIZE parameters.
Then I tried to restore the database using the multiple files. Since it's a very large database and it takes few hours to restore I tried to run RESTORE FILELISTONLY from URL to verify if I was able to get the list of files. When I ran the RESTORE FILELISTONLY statement I have received the below error
Cannot open backup device ‘https://mystorageaccount.blob.core.windows.net/sqlbackup/ServerName-Test_DB_FULL_20200912_030001_1.bak’. Operating system error 2(The system cannot find the file specified.)
I used SAS key to create the SQL Credential and hence my blobs are block blobs. I have also created access policy on my container with all types of permission exists on the policy (racwdl). The SAS key was generated on the azure portal with 1 year validity. Do I need to provide the read, write permissions while creating the SAS key (which can be achieved only through Azure Storage Explorer).
If I replace the SAS key with the newly generated key with read write permission, would I be able to restore with the backups which were taken with the old SAS key?
Or Am I missing anything? Please advice


texts:
Following is the query I'm using to scrub some fields in the JSONB column. I'm trying to radomize the first and last name so would like to use something like md5(random()::text) as values.
    update people set
    data = to_jsonb(data) || '{"firstName": "random_text", "lastName": "random_text"}'
    where id = 'b3c09005-7afb-4ad6-922d-76078875e59e';

I tried replacing random_text with md5(...) but I get an error

"DETAIL:  Token "md5" is invalid.".

I also tried using || to concat but that didn't work either.


texts: Update statement using Case statement, derived table, and ROW_NUMBER

texts:
Running MS SQL 2008 R2. I am provided a CSV list of product-IDs which I parse into individual rows and normalize. I then need to take those product-IDs (anywhere from 1-10) and write them to their corresponding columns in a table. I'm using a combination of CASE statements, a derived table, along with ROW_NUMBER in order to assign the correct product-ID value to each corresponding column. For some reason, it only seems to update the product_id_1 column, which is not right since there are other values stored in the #contactProducts table Below please find the T-SQL to recreate my issue:

CREATE TABLE #contactProducts(
[contact_product_id] [int] IDENTITY(1,1) NOT NULL,
[temp_import_id] [int] NOT NULL,
[product_id] [varchar](50) NOT NULL,
[createDt] [datetime] NOT NULL,
CONSTRAINT [PK_#contactProducts] PRIMARY KEY CLUSTERED
(
[contact_product_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO #contactProducts VALUES(3,'B6281-81',getDate())
INSERT INTO #contactProducts VALUES(3,'B100839-1',getDate())
INSERT INTO #contactProducts VALUES(3,'B101203-5',getDate())
INSERT INTO #contactProducts VALUES(3,'B101204-7',getDate())
INSERT INTO #contactProducts VALUES(3,'B101204-7',getDate())

SELECT * FROM #contactProducts

-- Temp_Import_2
CREATE TABLE #temp_import_2(
[temp_import_2_id] [int] IDENTITY(1,1) NOT NULL,
[temp_import_id] [int] NOT NULL,
[PRODUCT_IDS] [varchar](2000) NULL,
[PRODUCT_ID_1] [varchar](50) NULL,
[PRODUCT_ID_2] [varchar](50) NULL,
[PRODUCT_ID_3] [varchar](50) NULL,
[PRODUCT_ID_4] [varchar](50) NULL,
[PRODUCT_ID_5] [varchar](50) NULL,
[PRODUCT_ID_6] [varchar](50) NULL,
[PRODUCT_ID_7] [varchar](50) NULL,
[PRODUCT_ID_8] [varchar](50) NULL,
[PRODUCT_ID_9] [varchar](50) NULL,
[PRODUCT_ID_10] [varchar](50) NULL,
CONSTRAINT [temp_import_2] PRIMARY KEY CLUSTERED
(
[temp_import_2_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO #temp_import_2 (temp_import_id, product_IDs)
VALUES(3, 'B6281-81,B100839-1,B101203-5,B101204-7,B102472-14')

SELECT * FROM [#temp_import_2]

-- update with derived table
-- even tho there are values for thisRowNum = 2,3,4, etc. they don't seem to update. only works for thisRowNum = 1

UPDATE #temp_import_2 SET
 #temp_import_2.PRODUCT_ID_1 = CASE WHEN derivedTbl.thisRowNum = 1 THEN derivedTbl.product_id END
,#temp_import_2.PRODUCT_ID_2 = CASE WHEN derivedTbl.thisRowNum = 2 THEN derivedTbl.product_id ELSE '2' END
,#temp_import_2.PRODUCT_ID_3 = CASE WHEN derivedTbl.thisRowNum = 3 THEN derivedTbl.product_id ELSE '3' END
,#temp_import_2.PRODUCT_ID_4 = CASE WHEN derivedTbl.thisRowNum = 4 THEN derivedTbl.product_id ELSE '4' END
,#temp_import_2.PRODUCT_ID_5 = CASE WHEN derivedTbl.thisRowNum = 5 THEN derivedTbl.product_id ELSE '5' END
,#temp_import_2.PRODUCT_ID_6 = CASE WHEN derivedTbl.thisRowNum = 6 THEN derivedTbl.product_id ELSE '6' END
,#temp_import_2.PRODUCT_ID_7 = CASE WHEN derivedTbl.thisRowNum = 7 THEN derivedTbl.product_id ELSE '7' END
,#temp_import_2.PRODUCT_ID_8 = CASE WHEN derivedTbl.thisRowNum = 8 THEN derivedTbl.product_id ELSE '8' END
,#temp_import_2.PRODUCT_ID_9 = CASE WHEN derivedTbl.thisRowNum = 9 THEN derivedTbl.product_id ELSE '9' END
,#temp_import_2.PRODUCT_ID_10 = CASE WHEN derivedTbl.thisRowNum = 10 THEN derivedTbl.product_id ELSE '10' END
FROM (
select temp_import_id, product_id, ROW_NUMBER() OVER (PARTITION BY temp_import_id ORDER BY contact_product_id) AS thisRowNum
from #contactProducts
) derivedTbl INNER JOIN #temp_import_2 ON derivedTbl.temp_import_id = #temp_import_2.temp_import_id

SELECT * FROM [#temp_import_2]

DROP TABLE #contactProducts

DROP TABLE [#temp_import_2]


texts: Do conditional 1:1 relationships improve normalization?

texts:
Imagine a database schema that includes (among other things) two tables:

USER - A table of users, including their signon ID, name, etc.  The primary key is a surrogate key, "UserID" (auto integer).

ADMIN - A table of administrators, including a series of columns that a USER does not need but an ADMIN does, e.g. "SuperuserPassword" which is a hash of a password used for elevated permissions (this is just an example).  The primary key of this table is also "UserID" and is a foreign key to USER.UserID.

These two tables technically have a 1:1 relationship, although not every USER record will have an ADMIN record.  I believe this sort of relationship is referred to as a variant record, or in C terms it might be called a tagged union.  Essentially USER is a supertype and ADMIN is a subtype.

In the above scenario, assume both tables are in BCNF.

Now for my question.  Imagine a second scenario, where the DBE was a little bit lazier, and has all of the above data combined into a single table USER.  If the record pertains to a non-admin user, the SuperuserPassword field is simply left NULL.

In the second scenario, does the table design violate normalization rules?  Which type of normalization does it violate?

I am thinking there is an implied attribute here (user type) which can be inferred by the presence of the admin record.  Since the SuperuserPassword is an attribute that depends on a non-key [implied] attribute, the second design is a violation of third normal form.  But I am not certain this is technically accurate.

Please note, this is a completely academic question, and I am not asking for advice on this table design, which is just a straw man.


texts: How to improve estimate of 1 row in a View constrained by DateAdd() against an index

texts:
Using Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64).

Given a table and index:

create table [User].[Session]
(
  SessionId int identity(1, 1) not null primary key
  CreatedUtc datetime2(7) not null default sysutcdatetime())
)

create nonclustered index [IX_User_Session_CreatedUtc]
on [User].[Session]([CreatedUtc]) include (SessionId)

Actual Rows for each of the following queries is 3.1M, the estimated rows are shown as comments.

When these queries feed another query in a View, the optimizer chooses a loop join because of the 1 row estimates.  How to improve the estimate at this ground level to avoid overriding the parent query join hint or resorting to an SP?

Using a hardcoded date works great:

 select distinct SessionId from [User].Session -- 2.9M (great)
  where CreatedUtc > '04/08/2015'  -- but hardcoded

These equivalent queries are view compatible but all estimate 1 row:

select distinct SessionId from [User].Session -- 1
 where CreatedUtc > dateadd(day, -365, sysutcdatetime())

select distinct SessionId from [User].Session  -- 1
 where dateadd(day, 365, CreatedUtc) > sysutcdatetime();

select distinct SessionId from [User].Session s  -- 1
 inner loop join  (select dateadd(day, -365, sysutcdatetime()) as MinCreatedUtc) d
    on d.MinCreatedUtc < s.CreatedUtc
    -- (also tried reversing join order, not shown, no change)

select distinct SessionId from [User].Session s -- 1
 cross apply (select dateadd(day, -365, sysutcdatetime()) as MinCreatedUtc) d
 where d.MinCreatedUtc < s.CreatedUtc
    -- (also tried reversing join order, not shown, no change)

Try some hints (but N/A to View):

 select distinct SessionId from [User].Session -- 1
  where CreatedUtc > dateadd(day, -365, sysutcdatetime())
 option (recompile);

select distinct SessionId from [User].Session  -- 1
 where CreatedUtc > (select dateadd(day, -365, sysutcdatetime()))
 option (recompile, optimize for unknown);

select distinct SessionId                     -- 1
  from (select dateadd(day, -365, sysutcdatetime()) as MinCreatedUtc) d
 inner loop join [User].Session s
    on s.CreatedUtc > d.MinCreatedUtc
option (recompile);

Try using Parameter/Hints (but N/A to View):

declare
    @minDate datetime2(7) = dateadd(day, -365, sysutcdatetime());

select distinct SessionId from [User].Session  -- 1.2M (adequate)
 where CreatedUtc > @minDate;

select distinct SessionId from [User].Session  -- 2.96M (great)
 where CreatedUtc > @minDate
option (recompile);

select distinct SessionId from [User].Session  -- 1.2M (adequate)
 where CreatedUtc > @minDate
option (optimize for unknown);

The statistics are up to date.

DBCC SHOW_STATISTICS('user.Session', 'IX_User_Session_CreatedUtc') with histogram;

The last few rows of the histogram (189 rows total) are shown:


texts:
I am looking for an example to understand the following statement:
Schema of unstructured data can be defined at query time


texts: Is it possible to represent a ‘mutual IS-A’ relationship in an entity-relationship diagram?

texts:
Is the following scenario correct and why:

A and B are two entities
B is-a A
A is-a B

And what if A, B were relationship?


texts: Changing IP address from existing Mariadb10.1 Galera Cluster

texts:
I have a multimaster cluster with MariaDB Galera working properly, it is set to perform the replica through its WAN interface. This configuration was a result of a previous migration from another datacenter, but now I need to take advantage from 10Gbps internal network which gives me the new datacenter and for this I need to reconfigure the cluster IPs. That is changing the WAN IPs to LAN IPs.
There is a specific procedure for this? Or I should just shut down gracefully the nodes, change the IP and start again?


texts: Strange PostgreSQL behaviour on service start

texts:
Centos 8.3, postgresql 12 (i tried even 10, same thing).
I have set
the permission of the data directory to postgres:postgres. If I start
the service from systemctl, it gives me the error message

"creation of lock file "postmaster.pid" failed: Permission denied".

If I copy the command from postgresql.service which is postmaster -D /data/pgdata and I run from the console as postgres user, it works, and the postmaster.pidfile is correctly created.
I have no ideas, because it's not a permission problem...
What am I missing?


texts: Filtering on a virtual column from a UNION ALL with LEFT OUTER join causes a table scan?

texts:
I asked this question on SO but havn't got many bites and even a vote to close for being "Off Topic." So, I thought this stack exchange might be better suited.

Consider the following SQL...

SELECT allStuff.id
  FROM    (SELECT id, 1 AS clazz_
             FROM MyStuff
           UNION ALL
           SELECT id, 2 AS clazz_
             FROM MyWarehousedStuff) allStuff
       LEFT OUTER JOIN
          Chilren child
       ON allStuff.id = child.id
 WHERE allStuff.id IN (/*fast sub select */)
       AND allStuff.clazz_= ? /* if I remove this line the query is fast (index fast)
                                 but if it stays in then DB2 does a tablescan
                                 on MyStuff and MyWarehousedStuff (very expensive) */

To make things a bit more confusing, if I remove the LEFT OUTER JOIN or make it an INNER JOIN DB2 does the right thing. I find it highly unusuall that an AND would cause worse perforamnce. I am also surprised by the use of a table scan when the field in question isn't actually on the table and is constant.

Note, this is a cleaned up Hibernate query so I'm not to worried about fixing the SQL in question but rather I want to know why DB2 is acting a particular way.

What would be causing this behavior?

[update] - It appears that even adding allStuff.clazz_ to the column list is enough to trigger the table scanning.

[update] - Also seems that the IN subslect is part of the problem. Switching that to a literal subselect IN (?, ?) is fast but IN (select 1 from SYSIBM.SYSDUMMY1) is not

[update] - Using DB2 v9.1 z/OS

[update] - To be clear, the following is also slow

SELECT allStuff.id, allStuff.clazz_
  FROM    (SELECT id, 1 AS clazz_
             FROM MyStuff
           UNION ALL
           SELECT id, 2 AS clazz_
             FROM MyWarehousedStuff) allStuff
       LEFT OUTER JOIN
          Chilren child
       ON allStuff.id = child.id
 WHERE allStuff.id IN (select 1 from sysibm.sysdummy1)

But this is fast...

SELECT allStuff.id, allStuff.clazz_
  FROM    (SELECT id, 1 AS clazz_
             FROM MyStuff
           UNION ALL
           SELECT id, 2 AS clazz_
             FROM MyWarehousedStuff) allStuff
       LEFT OUTER JOIN
          Chilren child
       ON allStuff.id = child.id
 WHERE allStuff.id IN (?, ?)

Fast...

SELECT allStuff.id, allStuff.clazz_
  FROM    (SELECT id, 1 AS clazz_
             FROM MyStuff
           UNION ALL
           SELECT id, 2 AS clazz_
             FROM MyWarehousedStuff) allStuff
       INNER JOIN
          Chilren child
       ON allStuff.id = child.id
 WHERE allStuff.id IN (?, ?)

Fast...

SELECT allStuff.id, allStuff.clazz_
  FROM    (SELECT id, 1 AS clazz_
             FROM MyStuff
           UNION ALL
           SELECT id, 2 AS clazz_
             FROM MyWarehousedStuff) allStuff
 WHERE allStuff.id IN (select 1 from sysibm.sysdummy1)

Fast...

SELECT allStuff.id
  FROM    (SELECT id, 1 AS clazz_
             FROM MyStuff
           UNION ALL
           SELECT id, 2 AS clazz_
             FROM MyWarehousedStuff) allStuff
       LEFT OUTER JOIN
          Chilren child
       ON allStuff.id = child.id
 WHERE allStuff.id IN (select 1 from sysibm.sysdummy1)


texts: IP Addresses tab is not showing a specific IP Address

texts:
I have SQL SERVER 2008 R2 installed on windows Server 2016, it runs smoothly on a LAN network interface.
I Added a USB MODEM (4G) to the server and went to the IP Addresses tab in network configuration to Adjust its IP Settings (enable the IP and set the port for listening) but it is not shown in the IP Addresses tab, I tried to restart the SQL services and restart the server but it is not showing.
I searched the web but couldn't find an answer, what is the problem with it ?


texts: Promoting MySQL slave with unknown replication delay

texts:
Consider I have a master on DC1 and slave on DC2. Now, DC1 network is completely down for indefinite time and I want to resume the service as soon as possible on DC2 by promoting the slave to master.

There are two problems:

I don't know how much the slave is lag behind the master
If original master went back online, how to solve the conflict?

Are there any recommended steps and procedures?


texts: Speed of Searching Text vs INT/BIGINT - SQL Server 2012

texts:
I am building an application that will need to handle extremely high amounts of traffic. The traffic will consist of incoming requests for information from a SQL Server 2012 database. When a request is made, a unique KEY is passed that will be used to identify the account that the information belongs to. This KEY is an alphanumeric string and the table that contains the KEYs can contain millions of rows of data. The length and data type of the KEY can change based on situation.

Example Request

KEY=a123f-5n12d1km9-3cmc32mc32-32cm0429c-243g4453-g43f43
TYPE=123

--The system will look up data for type '123' in the database for account 'a123f-5n12d1km9-3cmc32mc32-32cm0429c-243g4453-g43f43'

The main focus will be on the speed of the query, so I am not concerned too much with database size or memory utilization. I know that searching for integers is a lot more efficient than searching text, so I came up with a way to easily convert the strings into integers. The goal is to make an efficient way to take a string, convert it to an integer, and then perform a quick search to return the results. The problem is that the integers generated by the strings will either take up two BIGINT columns or 4 INT columns.

My question is would it be more efficient to search and compare 4 INT columns or 2 BIGINT columns? Or would searching for the text be faster?

BIGINT Sample Query

SELECT (FIELDS)
FROM KEYS (JOIN .......)
WHERE C1 = xxxxxxxxxxxxxxxxxxxx and  C2 = xxxxxxxxxxxxxxxxxxxx

INT Sample Query

SELECT (FIELDS)
FROM KEYS (JOIN .......)
WHERE C1 = xxxxxxxxxx and  C2 = xxxxxxxxxx and  C3 = xxxxxxxxxx and  C4 = xxxxxxxxxx

Text Sample Query

SELECT (FIELDS)
FROM KEYS (JOIN ........)
WHERE KEY = 'a123f-5n12d1km9-3cmc32mc32-32cm0429c-243g4453-g43f43'

(NOTE: All columns are either type INT or BIGINT respectively)


texts: VLF count suddenly growing and unable to reduce it

texts:
Owing to reductions in IT resources, I am now finding myself having to manage this with limited knowledge and so am seeking advice, please.

We have 2 SQL servers with replication of multiple databases between them. The high VLF count is coming from the "parent" and started to be reported on 21st February 2020 and has continued to grow on odd days, despite my efforts. These servers are in production and in use 24/7.

The servers are running SQL Server 2008 R2 (and yes I do know that is out of support, but there is no money at all to do anything about that) and are in Full Recovery model.

There are 3 databases that are warning of high VLF count and so what I have tried to do it this:

Back up the log file using:

USE dbName
GO
BACKUP LOG dbName TO DISK='M:\dbNamelog2020_02-24.bak'
GO

Shrink the log using the SSMS GUI:

right click dbName
choose Tasks > Shrink > Files
change the drop down from Data to Log and leaving all other choices as their default

Taking one of the databases as a specific example the VLF count has gone from:

53 on 21/2/20
85 on 24/2/20
101 on 26/2/20
117 on 27/2/20

...and then I ran the above shrink and now it is 103.

The system I have inherited alarms when the threshold for VLFs is over 60, so apparently there is still a problem.

I have followed the same process with 2 other databases and with similar results (i.e. the VLF count remains above 60).

Now the Log file for my above specific example today shows (via SSMS):

Currently allocated space = 59750.38Mb
Available free space = 54423.27Mb (91%)

The Log file has an Initial Size of 59,751Mb and Autogrowth is set to grow By 10000 MB, restricted growth to 2097152 MB.

I hope I have given enough detail that either someone can spot a stupid mistake or illuminate how I can progress with this issue. I thought I read somewhere that it should be possible to work out what is creating these VLF's (especially if they are due to incomplete transactions, such as replication), but if that is true then I cannot discover how to actually identify what is creating them.

Any and all advice will be much appreciated.

Many thanks,
Caspar


texts: Getting the error "Can't rename a table because it is published for replication" while it's not

texts:
I'm trying to rename a table, but I'm getting the error :
"cannot rename the table because it is published for replication".
But this table is not among the published articles.
I'm suspecting it's flagged somewhere as published, because it's possible that someone added it before to publication, and dropped it but it was not unflagged in SQL Server. But I don't know how to confirm / disconfirm this.

How can I solve this problem ?


texts:
What is the different between GDR security update and CU security update?

I am looking to understand difference between the below two updates. If I apply KB3194724, would it also include KB 3194721?

Build 11.00.6567 (3194724 MS16-136: Description of the security update for SQL Server 2012 Service Pack 3 CU: November 8, 2016)

AND

Build 11.0.6248.0 (3194721 MS16-136: Description of the security update for SQL Server 2012 Service Pack 3 GDR: November 8, 2016) for SQL Server 2012?

Which one should I apply if SQL Server 2012 is on SP3?


texts: does LIMIT clause mean constant memory/storage in a PostgreSQL query?

texts:
When a LIMIT clause is used in a query with a very large number of records, e.g.

SELECT *
    FROM my_tab
    ORDER BY column1
    LIMIT 1 OFFSET 9

, does PostgreSQL always use a (roughly) constant amount of memory (and/or storage) during the query (1 row in the example)?

The documentation doesn't say much about the underlying mechanism or resource consumption.


texts: Getting @@ROWCOUNT when there is an insert query in SQL-SERVER?

texts:
Hope you are doing well.
I have a procedure as you can see below :

ALTER Procedure [dbo].[OpenExcel]
  @TableName varchar(2000),
  @FileName  varchar(2000),
  @ResultOpenExcel tinyint output

AS
Begin

 Declare @Rowflag Bit=0
 Set     @ResultOpenExcel=0

   DECLARE @Provider Nvarchar(2000);
   SET @Provider = 'Microsoft.ACE.OLEDB.12.0';

   DECLARE @SQL Nvarchar(MAX)=
   ' SELECT * INTO #TTT
     from openrowset(''' + @Provider + ''',''Excel 8.0;Database=' + @FileName +';hdr=yes'+''',''select * from [Sheet1$]'' );

     insert into ' + @TableName+'
     select *
     from #TTT
     drop Table #TTT'

   EXEC (@SQL)

IF(@@ROWCOUNT>0)
SET @RowFlag=1

Print @@Rowcount

if(@RowFlag=1)
               set @ResultOpenExcel=1
            else
               set @ResultOpenExcel=0

               Return @ResultOpenExcel
END

The problem is that @@ROWCOUNT won't work for the insert statement I guess !!! How Can I solve this issue?
Thanks in advance


texts: Log implications of copying data from one table to another

texts:
I need to move 10's of millions of rows from one table to another. Are there different ways I can do this that will minimize impact on log files? Clients sometimes choose Simple Recovery over the default Full. But even with simple recovery, bulk copy operations seem to consume huge amounts of log space. We can suggest a different recovery model if that will help.

INSERT INTO Table2 (<columns>)
SELECT <columns>
FROM Table1
WHERE <condition>;

DELETE FROM Table1
WHERE <condition>;

COMMIT;

Many thanks in advance


texts: Strategies for organising SQL Server with large amount of RAM

texts:
We now have a new server for our DB and amongst other things we have 128GB of RAM available (previously I had 16GB) . I know SQL Server is very good at managing it's resources, but I was wondering if there are any special settings or strategies that I should employ in either the server/db settings or processing code (stored procs/indexes etc) to ensure that SS takes best advantage of the available ram.

The DB is about 70GB and it's a non transactional db (it's a data warehouse). So basically large WRITE followed by massive READ is the normal flow of things.


texts: Setting up relationships properly with the Access UI

texts:
I am making the database design of my website in Microsoft Access 2013 because it has a nice user interface and I believe it will help me understand everything a whole lot better with the UI.

I have to create a relationship between two fields in one table with one field in the other - have I done this correctly (please see the screen capture below)? If not, what would I do to correct my errors?

Thanks in advance.


texts: When I do transactional replication does SQL truncate T-Logs on the Publisher?

texts: I would like to do Transactional  replication 10 tables on a Prod server to a Secondary server.  The prod server is in Full mode and has t-logs.  Secondary is in Simple.  Can I still capture T-Logs from Production and restore them (with a full backup of course)  if I have a catastrophic failure on Prod?  Do the T-Logs get truncated each time the subscriber pulls the transaction from the Publisher (Prod) and applies it to the Subscriber?


texts:
I executed this query that gave schema access to a login role called test

GRANT ALL ON SCHEMA open_waters TO test;

I want to now revoke the access to test


texts: How to calcuate current IOPS consumed in AWS

texts:
We have a SQL Server hosted on AWS using EC2 and EBS. SQL Files are hosted on EBS. EBS vol type is GP2 and drive IOPS is 480.

At an instance of time Write Througput in AWS is showing is 725 and Read Throughput is 72. Vol Queue Length is 0.5

So for that point of time, can we say?

Total IOPS consumed =  Write Througput + Read Throughput = 725 + 72 = 797
Since Total IOPS consumed(797) > Drive IOPS (480), can we say that IOPS are the bottleneck and IOPS needs to be increased?

If my understanding is wrong, then what is the way of identifying current IOPS value and how to determine if the current EBS volume is the bottleneck and needs to be upgraded?

Also just wanted to know, when Throughput comes into picture? how to identify if the bottleneck lies in Throughput and when it should be increased? How to calculate current throughput and max throughput?


texts: "Lambda" function returns type string instead of type record

texts:
I am using PostgreSQL 9.5.4 and trying to create a lambda function based on this code: https://github.com/decibel/pg_lambda/blob/master/sql/lambda.sql

set client_min_messages to debug;

create or replace function lambda_record(
    code text,
    variadic inputs anyarray
) returns record language plpgsql as $code$
declare
    fname text;
    foid regprocedure;
    fargs regtype[];
    call_args text[];
    sql text;
    out record;
begin
    -- name of function
    fname := format('pg_temp.lambda_function_%s', md5(code));
    raise debug 'fname=%', fname;

    -- create function
    sql := format('create function %s%s', fname, code);
    raise debug 'sql=%', sql;
    execute sql;

    -- get function id and arguments
    foid := fname::regproc::oid;
    select proargtypes::regtype[] into fargs
        from pg_proc where oid = foid;

    call_args := array(
        select format('%L::%s', input, argtype)
            from unnest(inputs, fargs) as u(input, argtype)
    );
    sql := format(
        'select %s(%s)',
        foid::oid::regproc,
        array_to_string(call_args, ', ')
    );
    raise debug 'sql=%', sql;

    -- out is a string but should be type record
    execute sql into out using inputs;
    raise debug 'out=%', out;

    -- drop function
    sql := format('drop function %s', foid::text);
    raise debug 'sql=%', sql;
    execute sql;

    return out;
end;
$code$;

select lambda_record(
$lambda$(
    IN arg1 numeric,
    IN arg2 numeric,
    OUT out1 numeric,
    OUT out2 numeric
) returns record language sql AS $$
    select $1::numeric, $2::numeric;
$$
$lambda$, 1, 2);

The problem is that the out variable in lambda_record() returns a string instead of a record:

SET
Time: 0.539 ms
psql:/Users/ian/Desktop/lambda3.sql:55: LOG:  duration: 2.759 ms
CREATE FUNCTION
Time: 3.445 ms
psql:/Users/ian/Desktop/lambda3.sql:64: DEBUG:  fname=pg_temp.lambda_function_f1ee9780eb612939b06081fce4ba88cb
psql:/Users/ian/Desktop/lambda3.sql:64: DEBUG:  sql=create function pg_temp.lambda_function_f1ee9780eb612939b06081fce4ba88cb(
        IN arg1 numeric,
        IN arg2 numeric
    ) returns record language sql AS $$
        select $1::numeric, $2::numeric;
    $$

psql:/Users/ian/Desktop/lambda3.sql:64: DEBUG:  sql=select pg_temp_2.lambda_function_f1ee9780eb612939b06081fce4ba88cb('1'::numeric, '2'::numeric)
psql:/Users/ian/Desktop/lambda3.sql:64: DEBUG:  out=("(1,2)")
psql:/Users/ian/Desktop/lambda3.sql:64: DEBUG:  sql=drop function pg_temp_2.lambda_function_f1ee9780eb612939b06081fce4ba88cb(numeric,numeric)
psql:/Users/ian/Desktop/lambda3.sql:64: LOG:  duration: 7.511 ms
┌───────────────┐
│ lambda_record │
├───────────────┤
│ ("(1,2)")     │
└───────────────┘
(1 row)

Time: 7.625 ms
psql:/Users/ian/Desktop/lambda3.sql:69: LOG:  duration: 0.407 ms

For some reason the line execute sql into out using inputs sets out to a RECORD which contains a single value which is a TEXT representation of what it should be.

But it should work like this:

create or replace function pg_temp.test(in arg1 integer, in arg2 integer,
    out out1 integer, out out2 integer)
language sql as $$
    select $1, $2;
$$;
select pg_temp.test(1, 2);
CREATE FUNCTION
Time: 0.529 ms
psql:/Users/ian/Desktop/lambda3.sql:70: LOG:  duration: 0.193 ms
┌───────┐
│ test  │
├───────┤
│ (1,2) │
└───────┘
(1 row)

I've been using Postgres for a long time, but I'm relatively new to the PL/pgsql language so any would be very appreciated.


texts: What are the consequences of enabling slow_query_log and flushing the cache on mysql?

texts:
I have a large database that is performing poorly recently. I want to enable the slow_query_log to investigate the queries and flushing the cache but I need to know what are the consequences of doing such action on a live system that is being used frequently 24/7. (I do not have permission to create a test copy of the database).

Much appreciated.


texts:
I'm creating some reports via BCP to be sent via emails.
create TABLE ##tempsss
    (
        create TABLE ##JOB_DataAssociacao_verContrato
(
     F1        VARCHAR(6)
    ,F2        VARCHAR(200)
    ,F3        VARCHAR(22)
    ,F4        char(1)
    ,F5         varchar(10)
    ,F6         varchar(15)
    ,F7         varchar(30)
    ,F8          varchar(10)
    ,F9          VARCHAR(18)
)  --it needs to be varchar to be able to use header
    )

insert into  ##tempsss
SELECT
...fields...
FROM some table or view
go
-------------------------------------------------------------
--passo 2 --

exec xp_cmdshell 'bcp "select ''field1'',''field2'',''field3'' UNION ALL select * from table or view" queryout "filepath\filename.xls" -U sa -P password -w  -S servername'
-------------------------------------------------------------

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'dba profile or something',
    @recipients = 'recipients email',
    @subject = 'email subject',
    @file_attachments='path\filename.xls'
-------------------------------------------------------------

drop table ##tempsss

with a 9k rows, I have a 2mb file. I just copy the content of this file, and paste inside an empty excel file and I have 160KB.
how can I use bcp but creating smaller files?
I'm havin gproblems to send files over 1mb via dbmail.
I set dbmail to send files with 10mb+. smtp is ok too.


texts:
Is there an automatic way to track reports and their dependencies for SSRS? What I want is a map/tree where I can see whether a report depends on a other report or subreport.

So far we did this by hand using Visual Studio 2008 checking all the 'actions' in text field properties, image field properties etc. for generating such a report tree

Someone advised me to query the catalog table in the reportdatabase. However I can't see a possibility there to see which report depends on an other report. Maybe I'm doing it wrong?

What I want is a map like:

Report A refers to/is used by Report B and C

Report B refers to/is used by D and F

Report F refers to/is used by Report G

...

Any advice?


texts:
I am getting an error when shard the existing collection. Can you help me?
This my error
{"t":{"$date":"2020-11-23T15:11:05.331+07:00"}
 ,"s":"I", "c":"MIGRATE"
 , "id":23893
 , "ctx":"MoveChunk"
 ,"msg":"MigrationCoordinator delivering decision to self and to recipient"
 ,"attr":{"decision":"aborted"
 ,"migrationId":{"uuid":{"$uuid":"0d05a912-9ca1-4bf6-ba94-b9656b01da4d"}}}}
{"t":{"$date":"2020-11-23T15:11:05.332+07:00"}
 ,"s":"W"
 , "c":"MIGRATE"
 , "id":22022
 , "ctx":"MoveChunk"
 ,"msg":"Failed to clean up migration"
 ,"attr":{"chunkMigrationRequestParameters":"ns: exampleDB.exampleCollection, [{ _id: MinKey }
 , { _id: -4611686018427387902 })
 , fromShard: shard0000, toShard: shard0002"
 ,"error":"InterruptedDueToReplStateChange: Stepped down while persist migrate abort decision"
 ,"migrationId":{"uuid":{"$uuid":"0d05a912-9ca1-4bf6-ba94-b9656b01da4d"}}}}
{"t":{"$date":"2020-11-23T15:11:05.332+07:00"},"s":"W", "c":"SHARDING", "id":23777, "ctx":"MoveChunk","msg":"Error while doing moveChunk","attr":{"error":"IllegalOperation: Transaction numbers are only allowed on a replica set member or mongos"}}
{"t":{"$date":"2020-11-23T15:11:05.332+07:00"},"s":"I"
  , "c":"SHARDING", "id":22080
  , "ctx":"MoveChunk"
  ,"msg":"About to log metadata event"
  ,"attr":{"namespace":"changelog"
           ,"event":{"_id":"mongodb-shard:27017-2020-11-23T15:11:05.332+07:00-5fbb6e993cfb07ec322470fd"
          ,"server":"mongodb-shard:27017"
          ,"shard":"shard0000"
          ,"clientAddr":""
          ,"time":{"$date":"2020-11-23T08:11:05.332Z"}
                   ,"what":"moveChunk.from"
                   ,"ns":"exampleDB.exampleCollection"
                   ,"details":{"min":{"_id":{"$minKey":1}}
                   ,max":{"_id":-4611686018427387902}
                   ,"step 1 of 6":0
                  ,"step 2 of 6":2
                  ,"to":"shard0002"
                  ,"from":"shard0000"
                 ,"note":"aborted"}}}}


texts: Error: "temporary file size exceeds temp_file_limit"

texts:
My database is suddenly hanging on some queries that join many tables and I see:

temporary file size exceeds temp_file_limit (1025563kB)

A sample query and query plan can be seen here:

https://pastebin.com/wug86Wrr

http://www.sharecsv.com/s/a6333a7f610e049dd81ebcfc19a4c02f/temp_file_limit_exceeded.csv

This query normally takes less than 100ms but hangs when hit temporary file size limit.

When I run:

SELECT temp_files AS "Temporary files", temp_bytes AS "Size of temporary files"
FROM pg_stat_database db;

I see:

Temporary files  Size of temporary files
---
22550            10651900248

How do I resolve this?

I was able to enable more of the problem. Say a user lists they can speak 2 languages - the query runs ok. Then they edit their profile to say they know 20 languages. The query ends up exceeding the temp_file_limit and hanging.


texts: How should I update ID field values for all records based on previous values?

texts:
I need a SQL query that can update ID fields for all records in a table from their previous values to a new value based on that previous value.

For example, if the ID is 1, I want to change it to 9991 - prepending '999' to every ID in the table.

What is the most efficient means of doing this?


texts:
I would like to see the activity on of the SQL server. So I can run a query when the server load is low. I know there is an activity monitor in SQL server but my company doesn't want me to give the rights to use this tool. Apparently you can kill other people queries and this is too dangerous.

Is there a way to check the status of the server with code?
(Or view the activity without needing those rights so I can manually run them?)


texts:
I have a table, which has a "LockedBy" field, which can either be null (no one has locked this record for editing) or it can have a user ID.

The idea is to make it so that only one team member can "check out" the record (and all of the records in other tables that are tied to this record) at a time, which sounded easy in my head, until I realized I don't really know how locking works with transactions in SQL Server.

Currently, I am updating tables using transactions and save points in a stored procedure, but I don't know how to incorporate the "lock":

BEGIN TRANSACTION TR1
BEGIN TRY

    UPDATE LOCK_TABLE SET UpdatedDate=GETDATE() WHERE ID=@ID
    --======================================================================
    --only update this table if the lock field is null; make sure no one
    --else can modify the lock field until the whole of TR1 is committed
    --or rolled back
    --======================================================================

    --Table 1
    SAVE TRANSACTION TR2
    BEGIN TRY
        UPDATE TABLE1 SET somefield='something' WHERE LockTableID=@ID
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION TR2;
        PRINT ERROR_MESSAGE();
    END CATCH

    COMMIT TRANSACTION TR1;
    --======================================================================
    --release the lock
    --======================================================================
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION TR1;
    SET @RETURN=-1;
    PRINT ERROR_MESSAGE();
END CATCH

I understand locks from a concurrent programming point of view, but every SQL Server introduction to locks seems to either be about preventing deadlocks or how to use different granularities of locks.

I think what I want is just "from here to here, don't let anyone else execute this stored procedure" (like a lock in a multi-threaded function), but maybe I'm coming at it from the wrong angle.

Does SQL Server do what I want automatically when I use transactions?  Or is there something special I need to add so that I can lock out other calls to this stored procedure?


texts: How these conflicting scenarios will work in cassandra when one of the mode crashes?

texts:
Say we have two nodes(simple replica no sharding) i.e. N1,N2  with replication factor as 2. I am using default Read and write consistency levels i.e. R and W are 1 which means it will read/write from/to single node

Scenario 1

Consider write request goes to node N1  to update record R1. Say name scott updated to scott_update1
Before it is replicated to N2, N1 crashed
Another write request goes to node N2  to update the same record R1. Now scott updated to scott_update2
Now when N1 comes back, will the name scott_update1 be updated to scott_update2 ?

Scenario 2

Consider write request goes to node N1  to update record R1. Say name scott updated to scott_update1
Before it is replicated to N2, N1 crashed
Read request  goes to node N2  to read the same record R1. It returns scott
Now when N1 comes back, will the name scott_update1 be updated to scott or it will stay scott_update1 ?


texts: Creating Clustered Index uses all my tempdb space. Why?

texts:
Table size = 44GB  (no clustered index)

Row Count  = 122016576

Disk space allocated to tempdb = 200MB

I wanted to create a clustered index for the table due to slow query times so I ran the following query:

USE [myDatabase]
GO

CREATE CLUSTERED INDEX [IX_Name] ON [dbo].[myTable]
(
    [myColumn] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

After 50 minutes of executing, my server's tempdb files (4 total) ran out of space and the database was sent into "In Recovery" status for about 10 minutes.  It's up and running now but I'm confused as to why tempdb was utilized so much when I specifically said SORT_IN_TEMPDB = OFF.

Does anyone know why this operation utilized so much tempdb space?


texts: GRANT USAGE on all schemas in a database?

texts:
I want to GRANT USAGE to a user/role for a given database. The database has many schemas.

I know there's an ON ALL TABLES IN SCHEMA, but I want "all schemas". I tried GRANT USAGE .. ON DATABASE, but that's obviously wrong (it doesn't actually exist).

This is for Postgres 9.3 or 9.4, it happens to be a server that is on AWS RDS.


texts:
At the end of each financial year, audit team asks for audit records for samples dates in a year as per their methodology and ideally we are supposed to provide them. (Say, give them list of schemas dropped in a particular month or modifications made for a schema in a month)

Now, im in a situation I need to save the audit data for one complete year, is there anyway to purge the audit data on a monthly basis and at the same time provide the necessary info by the end of the year. The reason I need to purge is because of the audit file size which grows to a huge size.

What choices do I have? I do not have license to Oracle Audit Vault as well.


texts: Does a SQL server unique constraint partially or fully fail a multi-row write?

texts: If some rows of a writing query fails a unique constraint, will only those writes be rejected, or will the entire query fail?


texts:
The validation phase of optimistic concurrency control has two derctions: one is backward validation, checking for conflicts with any previously validated transaction, the other is forward validation, checking for conflicts with transaction that not yet committed.
The validated transactions install modifications into "global" database, which means the main work backward validation needs to do is checking conflicts with "global" database. However, the forward validation needs to check conflicts with each running transaction. It introduces expensive communication between threads if the database supports multithreading, and also extensive memory read when the concurrency level of transaction is pretty high.
As far as I know, forword validation is more widely adopted than backward validation. Why? Which cases are suited for forward validation and backward validation respectively?


texts: PostgreSQL not starting correctly, is it repairing itself first?

texts:
I never saw that problem before. I had problems and many PostgreSQL processes were stuck so I killed them with a -KILL...

When I tried to restart, it says that it cannot restart, but the daemon continues to run and uses a little processor a lot of I/O. Is it trying to repair the database?

I get no log at all. I think there is a way to increase log output... I'll look into that. At this point, the socket to connect to the server doesn't get created, but the server does not quit or emit any error/message, so I have no clue what is going on!?

If anyone has a clue, I'd be glad to hear about it.


texts: Optimising MySQL database with read only tables

texts:
I have a MySQL database with 15 tables. Three of them are used for website authentication and three other are tables that are frequently read and written to. Then other tables are set in stone and I'd never need to change them unless I make some significant changes to the web app.

Is there anything I can do to make my database more efficient and for performance, given that I don't need to write to these tables at runtime?

From Googling, I was able to see some settings for MyISAM, but would prefer to stick with InnoDB. Couldn't find any other info other than certain people having their entire database as read only. However, mine has some tables that I'd need to read/write.

EDIT:

Four tables are 4MB and below and one is 7.55MB. Also wouldn't I then need to adjust all my procedures to check whether the table in memory exists or not? Cause it may disappear when the instance is restarted or something.


texts: Unknown number of potential values in a table from a foreign table

texts:
I can't seem to figure out the best way to do this...

Table A                   Table B
    Id                    Id
    TableBId              ValueNeeded
                          TableAId

So I Table A needs values from Table B, where Table B's 'TableAId' is matching the id.

Example

TableA                   TableB
_________________________________________________________
ID        TableBId       Id     ValueNeeded    TableAId
1         ???            1      'Foo'          1
                         2      'Bar'          1

I need Table A to pull both value's from table B, ('Foo','Bar'). but the complicated part is that Table A can only have one column to return multiple values. And I foresee up to nearly 30 potential rows needed from Table B, for Table A. I think I need a lookup table in between, but does said lookup table need to have 30 columns? and what if a use case requires more than those 30 columns? I need this to work somehow

Edit: clarity - I'm creating a web app for myself and some friends to use for Dungeons and Dragons, this is for the 'Proficiencies' portion of the player character generation. Every player gets different proficiencies based on class, race, and level. So a level 1 player might only have 8-15 proficiencies, whereas a level 20 player could have dozens they've amassed. What I'm trying to accomplish is: One player(TableA) with ideally, one column, that points to a reference table(TableB),  that then (ideally) has a few rows (race, class) that then pulls from another table (TableC) to finally fill out all the proficiencies that the player will have. The more I think about it the more sure I am that I need to make the reference table with a LOT of nullable columns....


texts:
I am trying to do a bulk update of a column from a csv file, however when I do the actual update operation my tempdb balloons to over 20 GB in size (overtaking the entire size of the SSD the tempdb is running on).

I load the csv file using a bulk copy in to a temporary table that has matching schema to the destination table, I then do the query

Update [Med_Rec_Entries]
set [Comments] = [Tmp_Med_Rec_Entries].[Comments]
from [Tmp_Med_Rec_Entries]
where [Tmp_Med_Rec_Entries].[ID] = [Med_Rec_Entries].[ID]

And that is where temp db blows up in size. Both the source and destination tables have 1,770,373 rows in it (there is a 1=1 on ID relationship for the update).

I tried running the database tuning advisor and it had me make a new index, but it did not help. The database I am inserting in to is just the middle step in a data migration and no queries other than other csv imports like one I am doing here and a bulk export at the end will be run against this database so it does not have any indexes or keys itself.

Here is the create table statement for Med_Rec_Entries

CREATE TABLE [dbo].[Med_Rec_Entries](
    [ID] [nvarchar](255) NULL,
    [Patient_ID] [nvarchar](80) NULL,
    [Med_Rec_Code] [nvarchar](20) NULL,
    [Tx_Date_Performed] [nvarchar](254) NULL,
    [Comments] [nvarchar](max) NULL,
    [Staff_Code] [nvarchar](21) NULL,
    [Quantity] [real] NULL,
    [Hide_This_Entry] [bit] NULL,
    [Exclude_From_Printed_History] [bit] NULL,
    [Image_ID] [int] NULL,
    [Remote_Status] [nvarchar](2) NULL,
    [Special_Flag] [nvarchar](20) NULL,
    [Tx_Time_Performed] [nvarchar](254) NULL,
    [Tx_Date_Recorded] [nvarchar](254) NULL,
    [Appended_Comments] [nvarchar](max) NULL,
    [Tx_Time_Recorded] [nvarchar](254) NULL,
    [Entry_Is_Locked] [bit] NULL,
    [Create_User] [nvarchar](21) NULL,
    [Audit_DTS] [nvarchar](20) NULL,
    [Audit_Actor] [nvarchar](21) NULL,
    [vQPixAreax_] [varbinary](max) NULL,
    [Route] [nvarchar](80) NULL,
    [Units] [nvarchar](20) NULL,
    [CFR_Reason_Code] [nvarchar](40) NULL,
    [rpl_Key] [nvarchar](14) NULL,
    [rpl_DateTime] [nvarchar](14) NULL,
    [Medical_Description] [nvarchar](max) NULL,
    [caseID] [int] NULL,
    [Sign_Off_Target] [nvarchar](21) NULL,
    [Sign_Off_Date] [nvarchar](254) NULL,
    [Location_Code] [nvarchar](50) NULL,
    [ACUP_ID] [nvarchar](15) NULL,
    [ExcludeFromWeb] [bit] NULL,
    [SecondarySort] [int] NULL,
    [AutosaveState] [nvarchar](20) NULL,
    [_CommentsAndFindingsText] [nvarchar](max) NULL,
    [NewFormat] [bit] NULL,
    [Temp] [nvarchar](80) NULL,
    [Colour] [nvarchar](20) NULL,
    [PrimaryKey] [nvarchar](200) NULL,
    [Sign_Off_Time] [nvarchar](254) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

--From the database engine tuning advisor
CREATE NONCLUSTERED INDEX [_dta_index_Med_Rec_Entries_13_933578364__K1] ON [dbo].[Med_Rec_Entries]
(
    [ID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

Here is the create table statement for Tmp_Med_Rec_Entries

CREATE TABLE [dbo].[Tmp_Med_Rec_Entries](
    [ID] [nvarchar](255) NULL,
    [Comments] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

What can I do to stop tempdb from growing too large?


texts:
In SQL Server 2008 R2, I have the following two tables, Employee and EmployeeStatus:

CREATE TABLE [dbo].[Employee]
(
    [employeeID] [int] IDENTITY(9500,1) NOT NULL,
    [firstName] [nvarchar](50) NOT NULL,
    [middleName] [nvarchar](50) NULL,
    [lastName] [nvarchar](50) NOT NULL,
    [suffix] [nvarchar](50) NULL,
    [createdDate] [datetime] NOT NULL CONSTRAINT [DF_Employee_createdDate]  DEFAULT (getdate()),

    CONSTRAINT [PK_Employee]
        PRIMARY KEY CLUSTERED ([employeeID] ASC)
             WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
                   IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
                   ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[EmployeeStatus]
(
    [employeeStatusID] [int] IDENTITY(1,1) NOT NULL,
    [employeeID] [int] NOT NULL,
    [validEmployeeStatusID] [int] NOT NULL,
    [statusChangeBeginDate] [datetime2](7) NOT NULL
         CONSTRAINT [DF_EmployeeStatus_statusChangeBeginDate]  DEFAULT (getdate()),
    [statusChangeEndDate] [datetime2](7) NULL,
    [createdDate] [datetime2](7) NOT NULL
         CONSTRAINT [DF_EmployeeStatus_createdDate]  DEFAULT (getdate()),

    CONSTRAINT [PK_EmployeeStatus]
         PRIMARY KEY CLUSTERED ([employeeStatusID] ASC)
            WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
                  IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
                  ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

I'd like to get COUNT(distinct e.employeeID) for each month less than the @month variable. Here's what I've tried:

declare @year int = 2016, @month int = 7

select
    month(es.statusChangeEndDate) as Month,
    count(distinct e.employeeID) as EligibleEmployees
from
    Employee e
left join
    EmployeeStatus es on e.employeeID = es.employeeID
where
    ((es.validEmployeeStatusID is null) or
        not exists (select 1
                    from Employee emp
                    join EmployeeStatus emps on emp.employeeID = emps.employeeID
                    where
                        (emps.statusChangeEndDate is null or
                        (year(emps.statusChangeEndDate) = @year
                            and month(emps.statusChangeEndDate) > @month))
                ))
    group by month(es.statusChangeEndDate)

The employeeID column serves as foreign key on the EmployeeStatus table. An employee can have 0 or more statuses on the EmployeeStatus table. Note that I'm excluding employees who have a null value for statusChangeEndDate or the  month of their statusChangeEndDate is greater than @month variable (in this case 7).

What I'd like to get is a result set like this:

Month  EligibleEmployees
=====  ===================
1      2567
2      2763
3      2595
4      2673
5      2792
6      2499

However, it returns something like this:

Month  EligibleEmployees
==========================
NULL   2567

Your helps is appreciated. Thanks. [I had this question on the main Stack Overflow but realized this is a better place for it. Deleted that question.]


texts:
I'm sitting here and do some practise but I just can't find the Solution. Maybe someone could give me some help?

I have a table and should find the highest sale amount of a quarter per year.

Data table:

My attempt:

SELECT Year, Quarter, MAX(SUM(Revenue)) OVER(PARTITION BY Year)
FROM Sale
GROUP BY Year, Quarter
ORDER BY Year, Quarter;

Output:

As you can see the numbers are correct.
But my expected output should be:

2012  |  4  |  25
2013  |  2  |  35

Would be great if someone has an idea.
Thanks in advance!

UPDATE:
Solution is found.
Forgot to mention. I used Oracle XE.

SELECT Year, Quarter, QMax
FROM (
  SELECT Year, Quarter,
    MAX(SUM(Revenue)) OVER(PARTITION BY Year) AS QMax,
    RANK() OVER (PARTITION BY Year ORDER BY SUM(Revenue) DESC) AS QRank
  FROM Sale
  GROUP BY Year, Quarter) QResult
WHERE QRank = 1
ORDER BY Year, Quarter;


texts: How to disable ldf file or limit its size

texts:
Possible Duplicate:
  Why Does the Transaction Log Keep Growing or Run Out of Space?

My .ldf file size keeps increasing. I already tried to shrink it but after that it just grows again.
How can I permanently limit the filesize or perhaps even disable it entirely (since I don't think I'll ever use it).


texts: SQL Server 2012 Publisher SQL Server 2008 Subscriber version issue

texts:
So I have a setup where one virtual machine is a SQL Server 2012 publisher and I need to setup merge replication on a SQL Server 2008 subscriber on a different vm.

When setting up the subscription at the subscriber, during the wizard step where to add the SQL Server subscriber, I get that error :

  For merge publications, the version of the Subscriber must not exceed the version of the Publisher.

I read that SQL Server 2012 publishing supported 2008 and above subscribers.

Any ideas ?


texts: Postgresql update multiple rows and skip row on error

texts:
I have a query which updates single column of multiple records using column value from another table. For example below are the tables--

Table A

+-----------------+---------------------+------+
| id(primary_key) | user_id(unique_key) | f_id |
+-----------------+---------------------+------+
| 1               | 1                   | 11   |
+-----------------+---------------------+------+
| 2               | 3                   | 22   |
+-----------------+---------------------+------+

Table B

+----+------+
| id | f_id |
+----+------+
| 1  | 11   |
+----+------+
| 3  | 22   |
+----+------+

Now I tried to update user_id column for all records in Table A using id column from Table B on basis of f_id column which is common using below query.

UPDATE
    A
SET
    user_id = B.id
FROM
    B
WHERE A.f_id = B.f_id

user_id column in Table A is unique which generates an error on bulk update as:

  ERROR: duplicate key value violates unique constraint "A_user_id_key"
  DETAIL: Key (user_id)=(221207) already exists.

But when I update this particular record using below query

UPDATE
    A
SET
    user_id = 221207
FROM
    B
WHERE A.f_id = B.f_id

it executes successfully without any error. Only bulk updates shows that error.
I don't get what is going wrong.


texts: Joining subquery from same table on 1=1 in Oracle

texts:
I have a table with zip codes, latitude and longitude. I have a working query that takes a radius and latitude and longitude and returns all the zip codes within the required radius:

        SELECT zip.zip,
                ROUND(SDO_GEOM.SDO_DISTANCE((MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(:clng, :clat, NULL), NULL, NULL)), (
                            SELECT geom
                            FROM standard_zip
                            WHERE zip = zip.zip
                            ), 0.005) / 1609.34, 2) AS dist
            FROM standard_zip zip
            WHERE zip.zip IN (
                    SELECT DISTINCT (zip) AS zip
                    FROM standard_zip c
                    WHERE SDO_FILTER(c.geom, SDO_UTIL.CIRCLE_POLYGON(:clng, :clat, :r, 5)) = 'TRUE'
                        AND SDO_RELATE(c.geom, SDO_UTIL.CIRCLE_POLYGON(:clng, :clat, :r, 5), 'mask=ANYINTERACT querytype = WINDOW') = 'TRUE'
                    )
            ORDER BY DIST

Since the lat and long are already in the table I would like to provide a zip code as input for the query instead of having to do a separate lookup first, but this just seems to churn forever..

SELECT
    zip.zip,
    ROUND(SDO_GEOM.SDO_DISTANCE((MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(inp.lng, inp.lat, NULL), NULL, NULL)), (SELECT geom FROM standard_zip WHERE zip = zip.zip), 0.005) / 1609.34, 2) AS dist,
    inp.lat,
    inp.lng
FROM standard_zip zip
LEFT JOIN (SELECT x as lng, y as lat from standard_zip where zip = :zip) inp on 1=1
WHERE zip.zip IN (
        SELECT DISTINCT (zip) AS zip
        FROM standard_zip c
        WHERE SDO_FILTER(c.geom, SDO_UTIL.CIRCLE_POLYGON(inp.lng, inp.lat, (:r * 1609.34), 5)) = 'TRUE'
            AND SDO_RELATE(c.geom, SDO_UTIL.CIRCLE_POLYGON(inp.lng, inp.lat, (:r * 1609.34), 5), 'mask=ANYINTERACT querytype = WINDOW') = 'TRUE'
        )
ORDER BY DIST

What am I doing wrong?

MguerraTorres suggested using a CTE, which sounds great. Here's what I tried:

WITH inp as (SELECT x as lng, y as lat from standard_zip where zip = :zip)
SELECT
    zip.zip,
    ROUND(SDO_GEOM.SDO_DISTANCE((MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(inp.lng, inp.lat, NULL), NULL, NULL)), (SELECT geom FROM standard_zip WHERE zip = zip.zip), 0.005) / 1609.34, 2) AS dist,
    inp.lat,
    inp.lng
FROM standard_zip zip
WHERE zip.zip IN (
        SELECT DISTINCT (zip) AS zip
        FROM standard_zip c
        WHERE SDO_FILTER(c.geom, SDO_UTIL.CIRCLE_POLYGON(inp.lng, inp.lat, (:r * 1609.34), 5)) = 'TRUE'
            AND SDO_RELATE(c.geom, SDO_UTIL.CIRCLE_POLYGON(inp.lng, inp.lat, (:r * 1609.34), 5), 'mask=ANYINTERACT querytype = WINDOW') = 'TRUE'
        )
ORDER BY DIST

Problem is, Oracle says inp.lng is an invalid identifier. Why?


texts: Does requiring SSL matter if using AWS RDS and AWS EC2 in a VPC

texts:
I have a MySQL RDS DB and am connecting it through an EC2 instance.

My RDS isn't publicly accessible, so I think it can only be connected to through my EC2 instances.

I see at this link https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.SSL.html that you can set up SSL with your RDS.

Does setting up SSL with your RDS matter if your RDS is protected?


texts:
What Happened:

I reinitialized a subscription 16 hours ago. I had about 1.2M records to transfer, so I was expecting it to take a while. The Replication Monitor was saying "N transactions with N commands were delivered" for hours, so I figured everything was OK.

The Issue:

This morning when I check the Replication Monitor, I see this:

Oh no!, I exclaim, thinking I need to reinitialize replication with a newer snapshot to avoid pulling 1.2M rows. But I look at the other Tab on the Replication Monitor, and I see this:

Question:

I'm wondering why Transactions are being delivered, despite Replication Monitor complaining about the snapshot.


texts: How do I completely reset ServiceBroker including all messages, queues, triggers, etc?

texts:
I have a database where I have a ServiceBroker up and running. For unknown reasons we have experienced a lot of problems with missing conversation handlers;

I.e.

The conversation handle "D33EC01C-CEA9-E811-95C5-00155D009F03" is not found.

It is my belief that this has happened when DB has been restarted while conversations have been "open" if that makes any sense.

What I wish to do now is to "reset" the ServiceBroker, remove all conversations, messages, triggers(!) etc.

Unfortunately I haven´t seen any documentation on how to achieve this.

Ideas?


texts:
In my system's database we have a table which stores 3 fields: id int, name(varchar) and description (mediumtext). The table is by far the biggest in our database and the queries on it are exclusively by id, which is indexed.

The problem is, we are unable to delete or archive any data and in addition, there is no way to partition the table in a way that will make sense. Currently the performance is good (we're talking about a 7.5 GB table) but I'm afraid we'll encounter some issues in the future.

A colleague suggested me to experiment with hash partitioning, which should divide the table to equally divided groups of data and keep the index level low.

Now my questions regarding this topic are:

Is hash partitioning a good solution to solve the issue I'm talking about, or the index is enough considering the queries are only by id
In case that I decided to go for hash partitioning, how many partitions should I go for? To remark, the table is currently 7.5 GB and expected to keep growing.


texts: Minimal permissions required to read index size in SQL Server

texts:
I'm involved with managing an application with a large number of SQL Server 2014 databases and am extracting table and index sizes in order to track capacity. I'm using the following query:

select
    db_name() as DBName,
    object_schema_name(i.object_id) as SchemaName,
    object_name(i.object_id) as TableName,
    isnull(i.name,'(HEAP)') as IndexName,
    ps.SizeInPages
from
    sys.indexes i
        inner join
        (
            select ps.object_id,ps.index_id,sum(ps.row_count) as IndexRows, sum(ps.used_page_count) as SizeInPages, count(*) as PartitionCount
            from sys.dm_db_partition_stats ps
            group by ps.object_id,ps.index_id
        ) ps on
            ps.object_id = i.object_id and
            ps.index_id = i.index_id
where
    object_schema_name(i.object_id) not in ('cdc','sys')

Ideally I'd like to run this query from a SQL Login with the minimum required permissions, but so far it seems like the user needs VIEW SERVER STATE and SELECT permissions on all the tables. I've also tried putting the above query in a stored procedure and giving the user account EXECUTE permissions on that, but sys.indexes is still filtered based on what the user has select rights to.

Is there another way of doing this that I'm missing?


texts:
Oracle has a function for encoding a binary value, specifically a RAW type, into base64: BASE64_ENCODE. This makes sense. I want to turn binary into something I can more easily port across systems.

However, in addition to accepting a binary RAW for its input, it also returns a binary RAW for its output. I find this extremely confusing, as the purpose of base64 is to encode the bytes in a text format.

So what does this output RAW actually contain? What is the proper way to convert it into an actual text type (e.g., VARCHAR2)?

I additionally assume that BASE64_DECODE expects its input to be in the same format as the output of BASE64_ENCODE, but please note if that's not the case.

I'm specifically using Oracle 11.2, but I doubt this has changed since it was introduced. Feel free to correct my if I'm wrong on that.


texts:
I have installed SSRS 2008 R2 on my desktop and server. When I hit the reports link on my desktop

http://mypc/Reports_mypc/Pages/Folder.aspx

all I get to see is this

I cant create a new folder or data source or anything of the sort

On the server where I am attempting to set up SSRS 2008 R2, all I get is a white screen that shows the virtual folder name in large fonts, followed by the version of the reporting services server on the next line. This is not leaving me any clues as to what needs to be fixed. On both pcs I am using the credentials of the local admin. Any clues on what needs to be fixed?


texts: 'oracle.jdbc.driver.OracleSQLException': ORA-01031: insufficient privileges. (SYS.DBMS_XMLSAVE)

texts:
I have this function (written here by someone), placed in the package ENVER.ENCODING_VERIFIER:

FUNCTION loadxml_table (p_owner IN VARCHAR2, p_tablename IN VARCHAR2, p_format IN VARCHAR2, p_tmpClob IN NUMBER) RETURN number
IS

  v_ctx dbms_xmlsave.ctxType;
  v_rows number := -1;

BEGIN

  for c1 in (select theclob
               from enver.tmp_xml_clob
              where id = p_tmpClob) loop

    v_ctx := dbms_xmlsave.newContext(upper(p_owner || '.' || p_tableName));
    dbms_xmlsave.setDateFormat(v_ctx, p_format);

    v_rows := dbms_xmlsave.insertxml(v_ctx, c1.theclob);
    dbms_xmlsave.closeContext(v_ctx);

    delete from enver.tmp_xml_clob
     where id = p_tmpclob;
  end loop;

  return v_rows;

END loadxml_table;

END;

In the table: tmp_xml_clob, column: theclob (id=1), I have this record:

<ROWSET>
 <ROW>
  <ID>1</ID>
  <IMAGE></IMAGE>
  <MESSAGE>a</MESSAGE>
 </ROW>
</ROWSET>

The structure of MYSCHEMA.MYTABLE table is like the following:

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ID                                                 NUMBER(38)
IMAGE                                              BLOB
MESSAGE                                            VARCHAR2(10)

When I call it from SQLPLUS logged as user ENVER (but it give me the same error if I call it from Java method, as explained in the link to the page I have included above):

declare
     x number;
     begin
     x := enver.encoding_verifier.loadxml_table('MYSCHEMA', 'MYTABLE', 'dd-MMM-yyyy HH.mm.ss', 1);
     end;
/

Unfortunately, this error is returned:

ORA-29532: Java call terminated by uncaught Java exception:
oracle.xml.sql.OracleXMLSQLException: 'oracle.jdbc.driver.OracleSQLException:
ORA-01031: insufficient privileges
' encountered during processing ROW element 1.  All prior XML row changes were
rolled back. in the XML document.
ORA-06512: at "SYS.DBMS_XMLSAVE", line 115
ORA-06512: at "ENVER.ENCODING_VERIFIER", line 179
ORA-06512: at line 4

The error is generated by the line:

v_rows := dbms_xmlsave.insertxml(v_ctx, c1.theclob);

In the trace file I have this message:

Exception in thread "Root Thread" oracle.xml.sql.OracleXMLSQLException: 'oracle.jdbc.driver.OracleSQLException: ORA-01031: insufficient privileges
' encountered during processing ROW element 1.  All prior XML row changes were rolled back. in the XML document.
        at oracle.xml.sql.dml.OracleXMLSave.saveXML(OracleXMLSave.java:2568)
        at oracle.xml.sql.dml.OracleXMLSave.insertXML(OracleXMLSave.java:1521)
        at oracle.xml.sql.dml.OracleXMLStaticSave.insertXML(OracleXMLStaticSave.java:466)

I don't know what privileges refers to the error. The user ENVER have all privileges in any of these tables. I tried to perform the same function as SYS, but nothing.

I have already granted execute on DBMS_XMLSAVE to ENVER, but it still does not work.

What could be the problem?

Thank you.


texts: SQL Server: TSQL or safe steps needed: Change collation en masse for group of tables

texts:
Looking for a safe TSQL (preferred) or safe steps (rather automated if possible) to change collation on dozens of tables in one throw.

Background:

We have a db, SQL Server 2005, with ~100 tables.

30-40 of them have a collation set which is inappropriate for the app we are running. (we need them all to be "SQL_Latin1_General_CP1_CS_AS" but this group of tables is "SQL_Latin1_General_CP1_CI_AS")

Possible Solution:

We found this script:
http://www.codeproject.com/Articles/12753/SQL-Server-2000-Collation-Changer

Issue with found solution:

But this script appears to introduce the possibility of data loss, AND it appears to neither detect nor warn nor measure if there will be data loss.

Feels risky to use!

Question to Community:

Is there a more advanced way of tackling this?

Preferably, a script that:

Detects if there will be data loss and at least gives a warning. (Even a script which just scans tables, considering change from collation A to B, and tells me what will happen if this change is made...)
Can work in bulk (e.g. I tell it what my target collation is, and it does all the work against all tables and columns in the db and reports back what it did)

Thank you!


texts: Not able to run mongorestore job from schedular task on windows

texts:
I have a Shell Script under which restore command is mentioned for restoring the DB from target folder.
My File name will be mongorestore.sh

  NOTE: Dump data location will be E:Backup/test

#!/bin/sh
DEST="Location where my backup is stored"
mongorestore -h 127.0.0.1:27021 -d secondary "E:Backup/test/dbname"

NOTE: If I will double-click on this shell file then it will restore the backup. But it will not be done through schedule task. Schedule Task CMD will Open And then Instantly closed. Not able to get the error also. Already Checked on Schedule task log file but there is no error.

And I will Create the Schedule task for invoking this file for creating the restore process. But it will not do. Even I am not able to see the error. As it open and instant close. If anyone has any idea regarding this?

And please check whether my command will be right or not. Any help is really appreciated.

My mongodump Script file will be:

#!/bin/sh
DIR=`date +%m%d%y`
DEST="E:Backup/test/$DIR"
mkdir $DEST
mongodump -h 127.0.0.1:27017 -d myDbName -o $DEST

It was working fine


texts: Schema for 1  0..1(+), many related but only one primary

texts:
Using tools available in SQL Server 2008 and Entity Framework, what is a solid way to represent a one-to-many relationship, where there is an underlying unique relation that I need to enforce? My specific example is a set of people who have posted zero or more documents, but only one is their primary. It is not necessarily their latest.

Here's what I'm trying...

Can I enforce a relation both from DOCUMENT to PERSON on personId, and from PERSON to DOCUMENT on a composite key, while allowing null on primaryDocumentId? I'm a little worried this could cause me trouble in my ORM.

1. PERSON                       DOCUMENT
   personId unique        <- -> personId
   primaryDocumentId null    -> documentId unique

Here are some other ideas:

2. PERSON                    DOCUMENT
   personId unique        <- personId
   primaryDocumentId null    documentId unique

This does not prevent inconsistency of a person having someone else's document as their primary. It has the benefit that I can enforce a primary document by removing the null if I wish.

3. PERSON             DOCUMENT with trigger
   personId unique <- documentId unique
                      personId
                      isPrimary

This has the problem of the searched/nested action the trigger could require under some circumstances. Also, I've entirely avoided triggers until now and would prefer to keep it that way, especially for core schematic needs.

4. PERSON             DOCUMENT
   personId unique <- documentId unique
                      personId
                      date unique

This is fun, in that there is inherently only one oldest/newest document for each person. However, that doesn't represent my use in this scenario.

5. PERSON             DOCUMENT                          PRIMARYDOCUMENT
   personId unique <- personId           / composite \  personId unique
                      documentId unique  \ relation  /  documentId

This looks like it might be the right way. I assume it is practiced, but I've never seen it before. Have you seen any risks associated with it?

Or any other suggestions?


texts:
I have database server with collation: SQL_Latin1_General_CP1_CI_AS

I got database backup file which collation is: Latin1_General_CI_AS

When I'm restoring this database and executing some SQL, I'm getting this error:

Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation

I've changed database collation to match:

ALTER DATABASE database_name
COLLATE SQL_Latin1_General_CP1_CI_AS

And now getting even more this kind of errors.

Is there any common way to modify all database object collation to prevent this kind of issues?


texts: SQL Server: compare names ignoring non-alpha

texts:
I have a database table with a text column "title".
New rows are inserted all the time, and every time I would like to check for potential duplicates on this column.

I define duplicate by "having the same sequence of alpha characters". So, for example, if I already have the string "Lisa's Nights":

"LISASNIGHTS" is duplicate, because case doesn't matter, spaces don't
matter, apostrophe doesn't matter
"Lisa's Night" is not duplicate because the final 's' is missing.

Of course I could store an altered version of the title and search there. But is there any other solution?


texts:
Trying to connect to SQL Server (SQL Server 2008 R2) installed on Windows Server 2008 R2 results in the following error message (according to ERROLOG):

  Error: 18456, Severity: 14, State: 38.

Does anybody have an idea what could be the problem?


texts: Error while opening SQL worksheet in SQL Developer

texts:
I am facing the following issue while opening SQL worksheet in SQL developer

Popup with Error Message --> "An error occurred while opening ide.worksheet.file:/C:/users/%USERNAME%/Appdata/Local/Temp/worksheet....."

After clicking on OK I am able to continue work as usual.

I am not able to determine what is causing this problem.
I do not want this pop up coming up every time I start a new worksheet.

Any help is highly appreciated.

Regards.


texts: SQL Server 2014 - AlwaysOn HA - Always Synchronizing

texts:
I have a Windows Server 2012 R2 cluster with SQL Server 2014 configured in a HA configuration.

The secondary databases always say Synchronizing when the data movement is happening. Is this normal? Should it say synchronized at some point?


texts: SQL Server 2016 Availability Group on DPM 2016

texts: I'm trying to backup my availability group in SQL Server 2016 using DPM 2016, but after I add the cluster in DPM, availability group show nothing to back up. I've set every secondary node to be readable secondary and add sysadmin role to \SYSTEM user. How can I fix this?


texts:
I'm going through a long stored procedure and I found this, as part of a long query:

SELECT ....
FROM   ---
WHERE  condition
  AND  CHECKSUM(r.roomprovider_id) <> CHECKSUM(ISNULL(br.roomprovider_id, r.roomprovider_id))

roomprovider_id is an INT value

First time I see it and I've no clue on what is being achieved with this. I made some searches, read the definition of CHECKSUM on the tech pages and couple of sources more but can't understand the point of using CHECKSUM here.

I'm not looking for an answer that explains exactly this case (if possible, even better) but I would be satisfied with an answer explaining the point of this way to use checksum.


texts:
I’ve been researching how to optimize only fragmented tables in MySQL and reviewed this post on optimizing tables. It basically performs a query against the information_schema database for any table with data_free > 0 and builds a SQL statement to OPTIMIZE only those tables. I ran this query and it identified 148 tables for optimization. All of the tables identified are InnoDB tables. After executing the resultant optimization SQL script, I re-ran the original script to identify fragmented tables and it returned the exact same tables during the first pass.

I have seen conflicting posts regarding the InnoDB tables and the OPTIMIZE command. Some say that OPTIMIZE will not work with InnoDB tables and that you need to run ALTER TABLE table_name ENGINE=INNODB. Others say that OPTIMIZE actually calls the ALTER TABLE command when executing against InnoDB tables. With that in mind, I ran the ALTER TABLE command against one of the InnoDB tables identified as being fragmented (data_free > 0) and found that the data_free did not change afterwards. It’s still greater than 0. I also re-started MySQL and checked it only to find the same results.

Now, we have several servers running MySQL 5.5.29 in our organization and I ran a query against all of them to identify any InnoDB tables with DATA_FREE=0 or NULL and none were returned. They are all greater than zero.

I also ran the OPTIMIZE command against a few MyISAM tables where DATA_FREE was greater than zero and verify that it was zero afterwards.

Can anyone shed some light on this for me? What is the proper method to remove fragmentation from InnoDB tables? What is the proper method to determine fragmented InnoDB tables?

Thanks