texts: Search efficiency of datetime vs date + indexed smallint (PostgreSQL)

texts:
Context

We are storing weather forecasts as multiband rasters (using Postgres with PostGIS). Each forecast is identified by its date and time (either 0:00, 6:00, or 12:00). We are looking at about 20 years of data. Besides analyses conducted for research purposes, for which speed isn't of primary importance, there will also be an online dashboard that will display the latest forecast(s) on a map.

The table could have the following columns:
datetime, metadata_id, raster_data

I foresee two main use cases: (1) either getting the most recent forecast, or (2) selecting a single past forecast. The queries could look something like this:

SELECT datetime, metadata_id, raster_data
FROM myTable
ORDER BY datetime DESC
LIMIT 1;
SELECT datetime, metadata_id, raster_data
FROM myTable
WHERE datetime= '2015-10-12 12:00:00'::timestamp;

Broader Context

The above situation actually applies to 6 different tables that only differ in that their data comes from different sources, and different physical variables are represented. I am therefore considering a single table with the following columns:
datetime, dataSource_physVar, metadata_id, raster_data

The different sources do have different raster extents, so I am consider using child tables.

Question 1

Given the limited use cases, is it better to use a single datetime field, or rather a date field with an indexed smallint field for the hour?

Question 2

Given the quantity of data (365*3*20 years = 22k lines), is it even worth worrying about efficiency?


texts: How to locate all the tables that have a field named username

texts:
Is there a way in PhpMyAdmin to do a SQL search for all tables, that have a fieldname username in it?

I am trying to help a friend that has a database with over 1,000 tables in it.

He needs to find all of them that have username as a field name, so he can find the list of tables and not have to manually look at each table.

I don't know MySQL enough to do it.

Anyone know where to find the way?


texts: Toad for Oracle: Procedures, Packages, Job, and DBMS_SCHEDULER

texts:
If I have about 10 queries which contain subqueries, would it be a best practice to contain all of them in one procedure? This script creates a small fraction of the data required for a report.

DROP TABLE TEST1;
CREATE TABLE TEST1 AS (
SELECT
C.DATE,
C.ITEM,
(SELECT name FROM name_codes WHERE code = C.DEPARTMENT) AS DEPARTMENT,
C.BUSINESS
FROM LOGS C));

That's a fraction of it but the code more or less creates tables, filters fields, and combines outputs until the desired result. I am looking to automate data input for other scripts as well. Each script calculates a KPI and would be its own procedure. Then all of those procedures would be wrapped into a package.

That package will be scheduled as a job through DBMS_SCHEDULER. Does this sound like the correct course of action? If so, can I call an existing script to create a procedure or would I need to paste the PLSQL into the procedure script? For the moment, I just want to test the first script as a procedure which is scheduled as a job through DBMS_SCHEDULER.


texts: Disable the SQL profiler to be executed on particular query running from ASP.NET application

texts: I have set of queries executing from an ASP.NET application and I don't want those SQL queries to be traced by SQL Server Profiler. How can I do it?


texts: How to relate two rows with cyclic relationship in the same table in sql?


texts: I need to identify hot partitions of Sybase IQ table (Sybase IQ 16). In Sybase ASE i can use MDA tables (monOpenPartitionActivity), is there any way to measure logical, physical reads for individual partitions in Sybase IQ? I tried sp_iqsysmon, but it doesn't show statistics for individual partitions.


texts: wordpress table is crashed and should be repaired

texts:
I run a wordprees website and use a managed VPS server with cPanel/WHM. Got bellow error log and check the table. I repaired the table with repair table command from phpmyadmin. But this problem occur repeatedly.

131121 21:58:16 [ERROR] /usr/sbin/mysqld: Table './HIDDEN_wrdp2/wp_options' is marked as crashed and should be repaired
HIDDDEN_wrdp2.wp_options info : Found block that points outside data file at 7534168
info : Found block that points outside data file at 7534212
warning : Duplicate key for record at 1150328 against record at 520756
warning : Number of rows changed from 25466 to 25463
status : OK

And I frequently find a screen showing error establishing database connection.

What should I do now? Can anyone help me to solve this issue?


texts: What happens when you restore the same transaction log multiple times?

texts:
I’m currently trying to keep a copy of our production database up to date by restoring a full backup, followed by a differential, then supplementing with transaction logs as they become available from the production database. The source is SQL Server 2008 Enterprise Edition and the destination is SQL Server 2014 Web. Doing this I noticed that you can restore the same log multiple times (as long as a newer one hasn't been restored after).

Question: what happens when you restore the same transaction log multiple times? Does it get overwritten? Discarded? Is it screwing up my database?

E.g.

  sqlcmd -E -Q "RESTORE LOG [databasename] FROM DISK = 'D:\ LOG\SERVERNAME_databasename_LOG_20160809_141504.trn' WITH NORECOVERY"
  Processed 0 pages for database 'databasename', file 'databasename_dat' on file 1.
  Processed 0 pages for database 'databasename', file 'MyDBDat' on file 1.
  Processed 65662 pages for database 'databasename', file 'databasename_log' on file 1.
  RESTORE LOG successfully processed 65662 pages in 27.589 seconds (18.593 MB/sec).

  sqlcmd -E -Q "RESTORE LOG [databasename] FROM DISK = 'D:\LOG\SERVERNAME_databasename_LOG_20160809_141504.trn' WITH NORECOVERY"
  Processed 0 pages for database 'databasename', file 'databasename_dat' on file 1.
  Processed 0 pages for database 'databasename', file 'MyDBDat' on file 1.
  Processed 65662 pages for database 'databasename', file 'databasename_log' on file 1.
  RESTORE LOG successfully processed 65662 pages in 26.238 seconds (19.551 MB/sec).


texts: PostgreSQL Binary Replication -do i really need to copy pg_xlog separated?

texts:
I am doing postgresql replication by issuing:

On the master:

>select pg_start_backup('clone', true);
>\q
#tar -cz main -f main.tar.gz

(Explanation: main is the data directory on my system /var/lib/postgresql/9.6/main)

Then I transfer main.tar.gz to the slave....

On the slave:

--Uncompressing main.tar.gz....
#tar -zxf main.tar.gz
--Copying previously created recovery.conf
#cp recovery.conf main/
--And then just starts the slave
#service postgresql start

On the master:

--And stoping backup mode on the master:
>select pg_stop_backup();

What I get is that replication is working fine, and it really works nice.

But I am with doubt after reading the official documentation that after the copy I should copy pg_xlog  in an additional step... https://wiki.postgresql.org/wiki/Binary_Replication_Tutorial

But I never do that, and never experienced a problem - do a lot of tests, like counting every table lines, in both master and slave and much more, but what worst can happen?


texts: How do I use a different domain and username in a SQL Server BACKUP LOG command?

texts:
I looked here,

https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-2017#security

I want to backup my log to a different server.  When I do this through the SSMS, I get access denied because my SQLServer Service user is not what the other machine expects, and I did not want to run the MSSQLServer service with a special domain account.

I tried a share that has everyone for SMB and NTFS, but it did not help.

How can I run the BACKUP LOG command to my mapped drive with a specific username and password that does have permissions?

I considered Powershell's SQL Server cmdlet, but I am on SQL Server 2012 and am unsure if the new PS libraries will corrupt anything.

https://docs.microsoft.com/en-us/sql/powershell/download-sql-server-ps-module?view=sql-server-2017

I tried import-module SqlServer on the server, but it is not found.


texts:
I am facing an issue which it looks like the server is hitting disk write limit. But after some test, I realize it wasn't. I was running the following query:

DECLARE @MyCounter int;
SET @MyCounter = 0;

SET NOCOUNT ON;

WHILE (@MyCounter < 10000)
BEGIN
   INSERT INTO TestTable VALUES ('testing text', 99)
   SET @MyCounter = @MyCounter + 1;
END

The following shows some print screen and explanation of my problem:

At 1st, I opened 1 query window and run the query, then I checked Performance Monitor:

It seems like I'm hitting limit of around 120 Disk Writes/sec.

Then, I opened another query window and run the same query, now I got the following Performance Monitor screen:

Ok, Disk Writes/sec limit seems increased to 240.

And again, I opened 1 more query window and run the same query again, I got the following Performance Monitor screen:

As expected, Disk Writes/sec increased to around 360.

My question is: Is SQL Server / Windows server / any other thing limiting this Disk Writes/sec ? Thanks in advance.

I'm using SQL Server 2014 in Windows Server 2012 R2 64-bit running on Dell PowerEdge 2900 server. This server has just been formatted.


texts: RHEL 7.4, heavy workload(s) and SWAP usage

texts:
This might not be the correct board for this, but since I'm a DBA and I'll ask here.

I have several RHEL 7.4 servers, running a mix of MariaDB (10.1, 10.2) and MongoDB (3.4).  The problem was happening with RHEL 7.3 as well.

All of these servers have 256Gb of memory with local SSD array storage, and even given heavy workloads the highest active memory-in-use footprint is less than 100Gb at any one time.  I've been profiling this for quite some time:

On each of these servers, even though there is always plenty of free memory available, the systems are incrementally going into swap.  I've tried setting the vm.swappiness value to 1, but incremental jumps of swap are still happening.

Is this happening to anyone else?  Does anyone know, with a large amount of memory available, if setting swappiness to 0 has ill effects?

Thanks


texts: IBM i Access ODBC driver returning only first letter of column names

texts:
I'm new to DB2 and am connecting to a DB2 for i (V7R1) database using unixODBC and the IBM i Access ODBC driver for Linux.

When I query the database, the results only include the first letter of the column names. For example, a typical query run with the isql utility will give me something that looks like this:

SQL> SELECT column1, column2 FROM schema.table WHERE column1 = 12345
+--------+---------------------------------+
| C      | C                               |
+--------+---------------------------------+
| 12345  | Lorem ipsum dolar sit           |
+--------+---------------------------------+
SQLRowCount returns -1
1 rows fetched

This is a problem for me because when I try to query the database in applications (e.g. using the pyodbc library for Python), I can't access the results by column name; both columns are named "C" in the result set.

My questions are:

Why is this happening?
Is it possible to change this behavior? (How?)

EDIT:

This happens even if I try to give the columns aliases:

SQL> SELECT column1 as foo, column2 as bar FROM schema.table WHERE column1 = 12345
+--------+---------------------------------+
| F      | B                               |
+--------+---------------------------------+
| 12345  | Lorem ipsum dolar sit           |
+--------+---------------------------------+
SQLRowCount returns -1
1 rows fetched


texts:
I have a databse dump db.sql.schema
I import it using psql -U user dbname < ~/db.sql.schema
After import in my DB I see table mytable but not mytable_id_seq.

# \d mytable
        Table mytable
      Column       |     Type     | Collation | Nullable | Default
-------------------+--------------+-----------+----------+---------
 id                | integer      |           | not null |
 field1            | integer      |           | not null |
 field2            | integer      |           | not null |
 field3            | integer      |           | not null |

I got the follwong error:

# INSERT INTO mytable (field1, field2, field3) VALUES (1, 1, 1) RETURNING mytable.id;
ERROR:  null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, 1, 1, 1).

My postgres version is 10.12
It looks like auto-increment of id doesn't works.
Is it a postgres issue or issue with my dump? Is it loo old or too new?


texts: Views created by SQL server Database Tuning advisor

texts:
Several years ago I ran Database Tuning Advisor on our database. Then the database wasn't very big so it was easy.

I implemented the suggestions, and it created several views with names like _dta_mv_xxx

I have never written a query against those views, and I wonder if they are used in any way by SQL-Server. I can't see any indexes on the views either.

Is it safe to delete those views or will it impact performance?

The database is around 400 gb so working with creating and recreating indexes is slow.

The database is SQL Server 2016 Enterprise.


texts: What are the steps to change tables engines from MyISAM to InnoDB?

texts:
I have a Datebase with 10MB size .
The Tables' engines were MyISAM (table-lock level).
So I deiced to change them to InnoDB (row-lock level).
I used the sql query for each table :

ALTER TABLE t ENGINE = INNODB;

After that the type column in PHPMyAdmin is showing InnoDB.

After this I found that my table is still using table-lock level because when I have two queries:

INSERT OR UPDATE (which should use row-lock) and after them a SELECT. All the select queries stop until the users finishes their INSERT or UPDATE.

IMPORTANT NOTES:

I did not restart MySQL service after changing the engines .
MySQl version 5.1.72-cll
The default engine of my database is still MyISAM

what I want to Know is : Should I change any system variables in MySQL when I move to InnoDB?


texts: 512 Bytes are not being used from SQL Server's 8 KByte data page

texts:
I have create the following table:

CREATE TABLE dbo.TestStructure
(
    id INT NOT NULL,
    filler1 CHAR(36) NOT NULL,
    filler2 CHAR(216) NOT NULL
);

and then created a clustered index:

CREATE CLUSTERED INDEX idx_cl_id
ON dbo.TestStructure(id);

Next I populated it with 30 rows each size is 256 byte (based on table declaration):

DECLARE @i AS int = 0;

WHILE @i < 30
BEGIN
    SET @i = @i + 1;

    INSERT INTO dbo.TestStructure (id, filler1, filler2)
    VALUES (@i, 'a', 'b');
END;

Now based on information I read in "Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012 (Itzik Ben-Gan)" book:

  SQL Server internally organizes data in a data file in pages. A page
  is an 8 KB unit and belongs to a single object; for example, to a
  table or an index. A page is the smallest unit of reading and writing.
  Pages are further organized into extents. An extent consists of eight
  consecutive pages. Pages from an extent can belong to a single object
  or to multiple objects. If the pages belong to multiple objects, then
  the extent is called a mixed extent; if the pages belong to a single
  object, then the extent is called a uniform extent. SQL Server stores
  the first eight pages of an object in mixed extents. When an object
  exceeds eight pages, SQL Server allocates additional uniform extents
  for this object. With this organization, small objects waste less
  space and big objects are less fragmented.

So here I have the first mixed extent 8KB page, populated with 7680 bytes (I have inserted 30 times 256 byte size row, so 30 * 256 = 7680), to check the size I have run size check proc - it returns the following result

index_type_desc: CLUSTERED INDEX
index_depth: 1
index_level: 0
page_count: 1
record_count: 30
avg_page_space_used_in_percent: 98.1961947121324
name : TestStructure
rows : 30
reserved :  16 KB
data : 8 KB
index_size : 8 KB
unused :    0 KB

So 16 KB are reserved for the table, first 8 KB page is for Root IAM page, the second one is for leaf data storage page which is 8KB with occupation of ~ 7.5 KB, now when I insert a new row with 256 Byte:

INSERT INTO dbo.TestStructure (id, filler1, filler2)
VALUES (1, 'a', 'b');

it is not stored in the same page although it have a space of 256 byte (7680 b + 256 = 7936 which is still smaller than 8KB), a new data page is created, but that new row could be fit on the same old page, why does SQL Server create a new page when it could save space and searching time buy inserting it in the existing page?

Note: the same thing is happening in heap index.


texts: How can I write windowing query which sums a column to create discrete buckets?

texts:
I have a table which includes a column of decimal values, such as this:

id value size
-- ----- ----
 1   100  .02
 2    99  .38
 3    98  .13
 4    97  .35
 5    96  .15
 6    95  .57
 7    94  .25
 8    93  .15

What I need to accomplish is a little difficult to describe, so please bear with me.  What I am trying to do is create an aggregate value of the size column which increments by 1 each time the preceding rows sum up to 1, when in descending order according to value.  The result would look something like this:

id value size bucket
-- ----- ---- ------
 1   100  .02      1
 2    99  .38      1
 3    98  .13      1
 4    97  .35      1
 5    96  .15      2
 6    95  .57      2
 7    94  .25      2
 8    93  .15      3

My naive first attempt was to keep a running SUM and then CEILING that value, however it doesn't handle the case where some records' size end up contributing to the total of two separate buckets.  The below example might clarify this:

id value size crude_sum crude_bucket distinct_sum bucket
-- ----- ---- --------- ------------ ------------ ------
 1   100  .02       .02            1          .02      1
 2    99  .38       .40            1          .40      1
 3    98  .13       .53            1          .53      1
 4    97  .35       .88            1          .88      1
 5    96  .15      1.03            2          .15      2
 6    95  .57      1.60            2          .72      2
 7    94  .25      1.85            2          .97      2
 8    93  .15      2.00            2          .15      3

As you can see, if I were to simply use CEILING on crude_sum record #8 would be assigned to bucket 2.  This is caused by the size of records #5 and #8 being split across two buckets.  Instead, the ideal solution is to reset the sum each time it reaches 1, which then increments the bucket column and begins a new SUM operation starting at the size value of the current record.  Because the order of the records is important to this operation, I've included the value column, which is intended to be sorted in descending order.

My initial attempts have involved making multiple passes over the data, once to perform the SUM operation, once more to CEILING that, etc.  Here is an example of what I did to create the crude_sum column:

SELECT
  id,
  value,
  size,
  (SELECT TOP 1 SUM(size) FROM table t2 WHERE t2.value<=t1.value) as crude_sum
FROM
  table t1

Which was used in an UPDATE operation to insert the value into a table to work with later.

Edit:
I'd like to take another stab at explaining this, so here goes.  Imagine each record is a physical item.  That item has a value associated with it, and a physical size less than one.  I have a series of buckets with a volume capacity of exactly 1, and I need to determine how many of these buckets I will need and which bucket each item goes in according to the value of the item, sorted from highest to lowest.

A physical item cannot exist in two places at once, so it must be in one bucket or the other.  This is why I can't do a running total + CEILING solution, because that would allow records to contribute their size to two buckets.


texts: Linked Server alternative in Sql Azure database

texts:
We are migrating database from Rackspace to Azure. Our databases were deployed on Virtual Machines in rackspace. In our queries we are using Linked server to other databases. Basically cross database query.
problem is that after migrating databases, we are unable to use linked server queries as Sql Azure db does not supports Linked Servers.

Kindly help.

UPDATE:

Not all databases are in azure. Some databases are with their respective owners in remote locations. We need to query  database that are running outside Azure.
There's no VM for Sql Server in Azure. We are using Database as a service in Azure.

See the image below for more details


texts: SQL Server : Execute as multiple users simultaneously?

texts:
I need to query a proprietary database where the software vendor has set tight permissions on each schema (one username per schema), and each schema-user is only allowed to read from their own schema.

I now need to run a query across multiple schemas. Is it possible to run a single query against multiple user accounts? Something like EXEC AS USER 'A' AND 'B'?

Obviously the correct method is to create a user with all required permissions, but I am running the query from a stand-alone utility and will not have admin privileges on my future clients' instances.


texts: Table Structure: 2 Related Entities Share the Same Entity

texts:
I was hoping I could get some advice on my table structure. Im writing a simple workout tracker for me and my buddies. These are the requirements:

Multiple users
A user can have multiple workouts
A workout has multiple sets
A set can have multiple users

I have a way to accomplish what I need to but I feel like theres a much simpler way that Im missing.

tbl_user

| UserId |   Name |
-------------------
|      1 |  John  |
|      2 |  Greg  |

tbl_workout

| WorkoutId |
-------------
|     1     |
|     2     |

tbl_user_workout

| WorkoutId | UserId |
----------------------
|     1     |    1   |
|     2     |    1   |

tbl_set

| SetId | WorkoutId |
---------------------
|   1   |      1    |
|   2   |      1    |

tbl_user_set

| UserSetId | SetId | UserId | Reps | Weight |
----------------------------------------------
|     1     |    1  |    1   |  20  |   50   |
|     2     |    1  |    2   |  15  |   60   |

I figure this way I could get:

A users sets with user->userset
A users workouts with user->userworkout
A workouts sets with workout->set

Is this structure logical?


texts: How to select multiple id from same table

texts:
Here is the simplified version of my problem. I have these tables:

Master table

id - name - surname - fathers name - mothers name

Names

id - name

Surnames

id - surname

I want to query master table but I want to get id's for name, father's name and mother's name from other tables. I want something like;

SELECT
    'id from names table that matches record name'
    , 'id from surnames table that matches record surname'
    , 'id from names table that matches record fathers name'
    , 'id from names table that matches record mothers name'
FROM master_table

My problem is that I don't know how to join same table multiple times.


texts: How do mySQL and web-interface deal with hashed data

texts:
I've recently been given access to a mySQL database which will sit behind a website designed to allow people to register their interest in a given subject and provide further information about themselves.  Of course the password is hashed/salted, but both the answers to the security questions are stored in clear text.  When I questioned this I was told that the website couldn't retrieve the answers if they were hashed, and since the same website is used by the helpdesk to verify peoples details if they contact them it is necessary for the helpdesk staff to be able to read the answers.

I am completely new to the security/website interaction side of things, so I don't even know where to start querying this.  So my questions are as follows:

Is it likely that there is a technical issue that prevents the site from dealing with hashed information (I'm told the website has been developed in Google Apps)?

Where can I find resources that will help me argue for the further development of the site/app so that it can deal with hashed information?

Am I just being paranoid?

Thanks,
Anne

PS I hope this is the right forum for this, otherwise please let me know and I'll remove the post (that's how new I am to this), same goes for editing tags.


texts: Postgres value that never goes below zero

texts: For my application i need to make a row with a value that can never go below zero. I know about check but this one can't fail the query if i do for example: update table set special_row = special_row - 100 where special_row = 50 what i want it to do is to just make special_row be 0 instead o -50 as the query would do.


texts: Determine percentage from count() without cast issues

texts:
I'm trying to run the following query to provide the % of rows in my patients table that have a value the refinst column. I keep getting a result of 0.

select (count (refinst) / (select count(*) from patients) * 100) as "Formula"
from patients;

The table has 15556 rows, and select count(refinst) from patients tells me that 1446 of those have a value in the refinst column. The response I'd like to get from the query would be 30.62 (1446/15556*100=30.62XXXXX, rounded to two decimals).

I'm pretty sure it has something to do with the data type of the count results (integers I'm assuming). If I divide an integer by an integer and the result is less than 0 it is truncated to 0 correct? If that's the case, can someone show me how to cast the results of the counts as a number with 2 decimal places so that the result will be rounded to 2 decimal places as well?

I'm sure there's a better way to write this code than multiple count statements. I am looking for a more processor-efficient way to write this query in particular.


texts: Migrating from text and image to varchar(max) and varbinary(max)

texts:
I have a SQL Server database which contains a number of image and text columns, and I'm studying potential problems that might arise from migrating them to their non-deprecated counterparts varbinary(max) and varchar(max).

Aside from changes to application code, my main concern are potential "gotchas" associated with this. For example, is there functionality supported by the older data types but not in the new ones?

Data loss due to truncation at least doesn't seem to be a problem since the new types are at least as large as the old ones.


texts: Database Users and Server Logins and the Mapping between them

texts:
I have an instance of SQL Server 2008 R2 Express Edition at home and another on a server at a clients premises which I am using to develop an application using Access as the front end. This application has 5 separate logins which I can use in the connection string (based on the Access Group the user is in). The home instance has several databases - some of which have nothing to do with this applications

I have manually created the logins in each instance over time (and probably in a different order).

I am hoping to be able to (more than once) take a backup from my home machine of the database I am working on and load it on the instance at the clients premises using restore, just moving the files in the backup set to the actual files used on this server during the restore process.  The piece I don't understand is the mapping between the database user and the login.

I had thought that the correspondence of name should be enough, but in trying to fix an issue on which users of the restored database didn't seem to be mapped to the server login I came across the Transact-SQL ALTER USER statement and in particular its reference to using WITH LOGIN and some discussion about a "SID".

Since (I presume) the Server Login names have difference SID's in each instance, does it mean that after I load a backup between instances that I have to reconnect the mapping again with a series of ALTER USER WITH LOGIN commands?


texts: Restrict insertion of new row if a row exists with certain field values

texts:
Using a Postgres DB. I have a table let say t with columns txn_type, txn_id. I want to add a constraint where it restricts to insert new row if I have a row with particular values existing in table.
e. g row 1
withdrawal, txn1
I am trying to add a constraint where it restricts to insert new row with txn_type <> 'withdrawal' and txn_id = txn1.
e.g it restricts to insert row with following values
non_withdrawal, txn1
My situation is like if a record exist with transaction_type = withdrawal & txnid=txn1 then no non-withdrawal record with same txn id should be able to be inserted.


texts: Truncate/delete tables for non-owner schema

texts:
Is it possible to give truncate rights to user A to truncate tables owned by user B?

But I should be able to truncate each table of B by A as per wish.

Some procedures would be needed I suppose? Kindly help!!

Platform - Oracle


texts: SSAS - Calculation in DSV or in view from data source

texts:
This wasn't an easy question to Google, a lot of word overlap.

My table has a column with a date. I'd like another column that is a calculation on that date. I can do this with a Named Calculation on the dsv. Or, I could do this in the view from the data source that feeds this table into SSAS. Trying to figure out which would be better.

This calculated column will be for a dimension attribute.


texts: Error Authenticating Proxy Account when Executing SSIS job

texts:
I have a SQL Server instance that runs 5 scheduled tasks each night, each of which run SSIS packages.

These packages have been running for years and the associated steps run via a Proxy Account (PackageExecutor).  PackageExecuter is associated with a SQL Credential that was a former  domain admin account.

Soon, the domain associated with this admin account is going to be shutdown.   I have to use a new account, on a new domain, as the admin account associated with my roxy, PackageExecutor.  When I created a new Credential for the new Admin account and associated it with PackageExecutor, I started to get the following error when I tried to run one of my SQL jobs as a test:

Unable to start execution of step 1 (reason: Error authenticating
proxy *Domain\Admin_Account*@*fully.qualified.domain.com*, system
error: Logon failure: unknown user name or bad password.).  The step
failed.

If I'm understanding this reasonably explicit error, what it's telling me is that the Credential accounts, associated with my proxy is in correct.  How do I validate this?

I know that this account is legitimate-- I've already associated it with every associated server group, I've made it a sysadmin user on the server.

What could be causing this problem?

To be clear, I haven't mis-typed the account name or the password associated with the Proxy Credential.  However, when I entered the account name Domain\Admin_Account and clicked the Check Names button, SQL Server automatically transformed the User ID to the fully-qualified version.  I'm not sure if this has anything to do with this problem.

I'm at a bit of a loss.  I've given my credential account full access to everything that I can think of.  What might I need to do to get this to work?

UPDATE

Sorry, one more quick mention.  I've found this MSDN kb article.  Resolution method #1 is what I've been doing for years.  The others don't seem to apply, or I'm missing something.  Any tips or clarification would be beneficial.


texts: In Place Rebuild a TDE Encrypted SQL Server Instance

texts:
I have a TDE Encrypted SQL Server machine (with a masterkey, Certs, etc).  I need to rebuild this SQL Server onto new hardware, BUT keep the original SQL Server Name. The new box will be named exactly like the old Box was. This is to avoid a multitude of application and connection changes.

I am going to backup all all encrypted DBs along with logins, jobs, etc. and copy these to the new machine. Then, take the original box offline and bring the new one online (install and configure SQL, etc). Basically I am going to swap the machines.

Has anyone ever need to do something like this?

How do I handle the TDE from the original machine - master key and certs? I need to set up TDE on the new machine which will now has exactly the same name as the original machine did. If I create a new master key using the

CREATE MASTER KEY ENCRYPTION ...

statement on the new machine, will I be able to restore the backed up DBs to my new box using the old Certs from the original machine?


texts: MS SQL Server 2008 Transaction log file back up failure

texts:
I've done some reading around this subject here and on StackOverflow but nothing really answers my question enough for me to go forward.

I'll explain we have a MS SQL 2008 server running, the transaction log file backup has been failing every night, now I've had a look at some of the other errors we've had, long and the short of it, it looks like this could be disk or I/O or so I'm informed, as this is a hosted machine we've initiated a change but that takes time and Sods law means the closer you are to making a change the more likely something catastrophic will happen. ie I need to make sure we're safe.

I'm not a DBA (If I was I probably wouldn't be asking questions).

Now as I understand it, if the transaction log backup fails when you are on a Full recovery model, then you're stuffed.

So I've changed to a simple recovery model (I appreciate the implications interms of point in time better we have a backup than one that will fail on restore) as I understand it a Simple Model writes everything to the database during the backup, what I'm not sure of is, what this really means in terms of the transaction log. ie if the database died this moment and I had to restore from a backup, but the transaction log backup fails what this means to me. We have a backup plan for the DB and the logs. We know the logs backup fails, but if it's in simple recovery mode and the database is backed up but the log back up fails would I be able to recover the database? By recover lets get this straight, I'm talking about getting the thing working again by simple restoring from my last back up?

Now to the 2nd part, if we assume there is a problem with a sector on the disk (Appreciate I need to move as soon as I can, but new machine etc etc are out of my sphere of influence), then it occurs to me that deleting and recreating a log file won't work as the file is likely to be in the same part of the disk. So wouldn't creating new log file and removing the old log file (but leaving the old file on the disk) be the thing to do? As that would effectively stop the bad sector being used (Different partition would also solve this, just this is a C: and D: affair and space is tight on C:.

So if so, how do I do this? I guess I'm really asking for step by step here as I'm unsure of all implications, I've read elsewhere that you will be in a whole world of pain if you just delete a log file by say stopping the service deleting the file and recreating new file same name and starting the service. So what do I do?

Really appreciate all comments

Thanks in adavance


texts: Why does OpenQuery hang for hours before raising Query timeout expired when query timeout on Linked Server is set much shorter?

texts:
In production have two SQL 2012 instances on separate servers, with a Linked Server set up between them. No problems there. In development, I have the databases for both in one 2012 instance, and have a self-referencing Linked Server set up to mimic production.

I have an Agent job that runs every 15 minutes that contains a step that runs a stored procedure. The procedure does a SELECT INTO FROM OpenQuery() to populate a local temp table with data from across the Linked Server. Occasionally, only in development, that job step will hang for almost 4 hours. The error is:

  Cannot fetch a row from OLE DB provider "SQLNCLI11" for linked server
  "MyLinkedServer". [SQLSTATE 42000] (Error 7330)  OLE DB provider
  "SQLNCLI11" for linked server "MyLinkedServer" returned message "Query
  timeout expired". [SQLSTATE 01000] (Error 7412).  The step failed.

In the Linked Server, I have the query timeout set to 1200, yet the job step will hang for almost 4 hours before getting the timeout. Why isn't the timeeout occurring after 20 minutes per this setting? How can I enforce a timeout on this job, job step, procedure, or statement?


texts: SSDT sqlpackage.exe CreateNewDatabase Parameter Not Respected

texts:
Originally asked this on SO but that question seems to be populated only by tumbleweed so wondered if this might be a better forum for it.

I have a VS2103 solution which includes a database project for which we have a number of publish profiles. One of these is for the purposes of testing some functionality that requires an actual database to be in place so it is supposed to publish the database to (localDb), add any lookup data and then run some post deployment scripts to add some fake data.

The test project has a pair of pre-build events to try and ensure that the DB project has been built and is then deployed:

"$(MSBuildBinPath)\msbuild.exe" "$(SolutionDir)CUIA Database\CUIA Database.sqlproj" /t:Rebuild

"$(PROGRAMFILES)\Microsoft Visual Studio 12.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120\sqlpackage.exe" /Action:Publish /SourceFile:"$(SolutionDir)CUIA Database\$(OutDir)CUIAWorkspace.dacpac" /Profile:"$(SolutionDir)CUIA Database\UNIT-TEST.publish.xml" /Variables:test_run="True" /p:CreateNewDatabase="True"

The SQLCMD variable is what determines if the test data is inserted into the newly created database, and is defaulted to False.

The problem is that it does not seem to work every time and I cannot find a reason why. If I start with nothing on the local db instance, then it works as expected, the DB is created and the tests have something to use. However, if I then change some of the test data using SSMS and re-run the tests, it seems to be a crapshoot as to whether or not the DB is recreated and mostly it is not. I can see this because my amended test data is still there so it has not been dropped. I have also verified the same behavious on a SQL Express instance. Database logs show no problems and the build outputs from successful runs are the same as for those where the DB is not re-created.

I haven't been able to find much in the way of information on what I am trying to achieve so I'm have to go with some hunches and guesswork so I appreciate that my pre-build event idea is possibly not the best solution. If anyone knows a better one then please speak up and share. However, the primary concern is why is the /p:CreateNewDatabase="True" flag not being respected? Surely this should demand that the database is dropped and re-created every single time?


texts: How to effectively monitor PostgreSQL 9.5 Replication?

texts:
Background: I've got quite some experience running and automating MySQL Clusters with several independent replication streams. However I rarely had to run an PostgreSQL replication myself. They were either managed by some other team or fully managed Amazon RDS. Now I need care for a replicated PostgreSQL setup myself and need some help.

We've got an PostgreSQL 9.5 setup with (at least) one slave replicating from a master. Mostly for failover purposes. We are not running a real HA setup (yet).

For Monitoring we use Prometheus and an PostgreSQL Exporter for Prometheus. This service executes queries and provides the results to the monitoring system.

With MySQL monitoring the replication process was easy. You had to make sure that the slaves were streaming binlogs from the server and applying those locally. Usually it was OK to just check for the Seconds_Behind_Master. With PostgreSQL it seems to be a little harder.

Question: What are the most important metrics/properties to make sure my replication is running properly and that we can promote the slave to a master any time?


texts:
I am trying to understand in terms of performance which approach is better:

Query 1:

SELECT * FROM table1
WHERE col1 NOT IN(SELECT col1 FROM table2)

Query 2:

SELECT * FROM table1
LEFT JOIN table2 ON table1.col1 = table2.col1
WHERE table2.col1 IS NULL

The first query uses DEPENDENT SUBQUERY,The second uses simply index.


texts:
I use a database named BI, every night it is restored. It worked great but since some days ago it is shown that the database is in recover. I used
RESTORE <BI> WITH RECOVERY

but it gives me
RESTORE cannot operate on database 'BI' because it is configured for database mirroring or has joined an availability group. If you intend to restore the database, use ALTER DATABASE to remove mirroring or to remove the database from its availability group.

does anybody has other ideas to solve this problem?


texts:
I just migrated from Mongo 2.6 (Single node) to 3.2 (ReplicaSet).

In the old server the Mongo's data folder was a total of ~12GB, but looks like in the new servers (master and its slave) it's just ~2.5GB - make sense? Has Mongo enhanced the way they're holding data?

The size I mention above, ~12GB includes indexes (that have significant size in my DB, almost double it).


texts: Inserting query result to another table hangs on "Copying to temp table on disk" on MySQL

texts:
I started the process of inserting returned results to another table. The query groups the rows in respect of indexed IDs. This causes 149,000,000 rows to be decreased to 460,000 rows.

The query includes 3 table INNER JOINs, with each table having about 20,000,000 rows.

Further information, the process completes in about 12 seconds for a test file which has 1000 input rows, and returns 703 rows.

I started the query earlier ### we don't know when earlier is ###, but it is still running in the state: "Copying to temp table on disk" after 38000 seconds (10 and a half hours).

I think there is a problem during the insertion process. What am I probably doing wrong here? If it helps, the operating system of the computer is Windows 7, it has 3 GB RAM, an Intel Core2Duo 2.27GHz processor. ### you forgot to tell us details on the hard drive. One partition in, one out, same disk, same partitions, etc ###

Here's my query as it currently reads:

INSERT INTO kdd.contents
            (adid,
             descriptionwords,
             purchasedkeywordwords,
             titlewords)
SELECT t.adid,
       dt.tokensid,
       pkt.tokensid,
       tt.tokensid
FROM   kdd.training t
       INNER JOIN kdd.purchasedkeywordid_tokensid pkt
               ON t.keywordid = pkt.purchasedkeywordid
       INNER JOIN kdd.titleid_tokensid tt
               ON t.titleid = tt.titleid
       INNER JOIN kdd.descriptionid_tokensid dt
               ON t.descriptionid = dt.descriptionid
GROUP  BY adid;


texts:
I have a rare scenario. Is there any possibility to achieve the target? Here is the situation:

I have developed a responsive site for a supermarket. I am using php with foundation framework and the back-end is MySQL. It is for promoting their products. They already having software for billing, which was developed using WPF with SQL Server back-end. Now they need to include online shopping in the web site, and they need to reduce the product from the local software according to the online sale.

I can use replication if both the databases are the same. But in this case, the local DB is SQL Server and the live DB is MySQL. How can I achieve the target of syncing live and local DBs?


texts: PolyLang WP Update to replace one string to another

texts:
I have to replace one string to another one in post_content column in table wp_post ( Wordpress ).
My query works only then when I use SELECT but when I want to use UPDATE command with REPLACE then I have error. My query:
UPDATE wp_posts SET post_content = REPLACE(post_content, 'str1', 'str2') FROM wp_posts WHERE ID IN (
SELECT ID
FROM wp_posts
WHERE ID IN (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(description, '"ar";i:', -1), ';',1) FROM wp_term_taxonomy WHERE taxonomy = 'post_translations'))

Mysql said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM wp_posts WHERE ID IN (
SELECT ID
FROM wp_posts
WHERE ID IN (SELECT S...' at line 1


texts: What do I need to check why log_reuse_wait_desc keeps coming back to REPLICATION

texts:
What should I do to check or need to do why log_reuse_wait_desc is REPLICATION?

A database of ours keeps coming back to REPLICATION for the log_reuse_wait_desc   after essentially following this https://support.microsoft.com/en-ph/help/2871474/sql-transaction-log-grows-when-you-use-change-data-capture-for-oracle

Note: This is not CDC for Oracle but instead the DB, before I came in as DBA, was using CDC and eventually disabled when it was restored to Standard Edition ( CDC is EE only )

In our case, since CDC is not available in Standard Edition and there's no way to proceed as directed by the link, I enabled Snapshot replication for a test storedprocedure so that I can  perform the "fix."  When log_reuse_desc_wait becomes NOTHING, I perform log backup and shrink the log I then remove the replication.

Now in a matter of minutes, log_reuse_desc_wait returns to REPLICATION and since the log has been reduced, I wait for this again to grow and do the dance again.

Server info

  Windows Server 2012 R2

  Sql Server 2014 Standard Edition
     (SP3)

Update: I can consistently replicate that log_reuse_wait becomes REPLICATION from NOTHING once the log backup starts. I am not really sure why.


texts:
I have a stored procedure that ends with FOR XML EXPLICIT and I need to put the xml file in a folder.

I've set up the following PowerShell, and run the script providing the two parameters.

The name of the script is xml.ps1

./xml "xml" "c:\test.xml"

param
(
    [string]$outputType,
    [string]$filename
    )

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=SLISQL;Database=Development_App;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "dbo._REX_NewSP"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]
if ($outputType  -eq "Text")
{
$DataSet.Tables[0] | format-table -auto > $filename
}

if ($outputType  -eq "xml")
{
$DataSet.Tables[0] |Export-Clixml $filename
}

I see the xml output in the PowerShell screen, but no text.xml file is output.


texts: Update a column for every row of a large table to a unique value in a set range

texts:
We have a SQL Server table with over 3 million rows in it. Each row represents a product. These products are provided by about 500 suppliers, and are updated each night via a file from each supplier.
These products are displayed by our e-commerce site, and because of the nature of the business, products are always ordered by the product 'last updated' date. This is a column on the products table called LastUpdated.
This gives suppliers whose products were updated last an unfair advantage.
To get around this problem, we shuffle the products by setting the LastUpdated datetime column to a random datetime value over a 4 hour period. The problem is that this process takes about 15 hours. We must also ensure that the suppliers are spread out evenly, so suppliers' products are not bunched together when queried by the website.
We can't change the way the website queries the data (always by LastUpdated DESC), because of the way other parts of the business work, so we can't randomise when querying the data.
We are looking for a faster way to update the LastUpdated column, for every table row, to a unique datetime covering a 4 hour window, that also prevents supplier products from being bunched together when ordering by LastUpdated.
Example

Note: Some suppliers have a different number of products, so any bunching of supplier records must be as random as possible towards the bottom of the queried results set.
To clarify, we have a very slow query using cursors to do this product shuffling, but we feel there is a better way to do this, that does not take 15 hours.
Update: Vladimir's answer is excellent, as is the comment from Shoeless, but what about part of the question ensuring suppliers products are not bunched together when randomising?
Update: Some people have questioned why the LastUpdated order by is so important. Suppliers pay our website to display these products (approx. 10,000 products per subscription), but they can also pay for a limited number of these products to remain at the top of the results for a user's search criteria. If multiple suppliers want this top spot, then we have to rotate the order of these at the top of the results. We do this base on a queue system, based on an updated date. There could be 30 suppliers all paying for the same top spot for the chosen criteria.


texts: I'm not sure if there is such a question that I can't seem to find. I have a clustered instance named FC\SQLSRV. Because this is the virtual name so I can't create an alias for the clustered instance. I have another SQL Server database server (B) using the default name instance (MSSQLSERVER). What I want is to create the alias for the server B using the same clustered instance name. The reason is I just want to switch my application (that is connecting to the server B) to use the clustered instance.


texts: Just 4 Table Join query takes too much time and some time fail to execute

texts:
I am working on getting candidate detail from mysql database and for that i have created one query.

Is my query structure is wrong?

Here is the query:

SELECT  `candidate`.`id`, `candidate`.`email`, `candidate`.`fname`,
        `candidate`.`lname`, `candidate`.`id` as `can_id`, `candidate`.`update_date` AS `updated_at`,
        DATE_FORMAT(candidate.dob, '%d-%m-%Y') AS dob, `candidate`.`gender`,
        GROUP_CONCAT(DISTINCT candidate_experience.functional_area_name SEPARATOR ',') AS can_functional_area,
        GROUP_CONCAT(DISTINCT candidate_experience.industry_type_name SEPARATOR ',') AS can_industry_type_name,
        GROUP_CONCAT(DISTINCT qualification_name SEPARATOR ',') AS qualification_name,
        GROUP_CONCAT(DISTINCT candidate_key_skills.name SEPARATOR ',') AS skill,
        `candidate_desired_job`.`current_location`,
        `candidate_desired_job`.`total_experiance`, `candidate_desired_job`.`prefered_location`,
`candidate_desired_job`.`notice_period`,
IFNULL(candidate_desired_job.expected_salary, 'N') AS expected_salary
    FROM  `candidate`
    LEFT JOIN  `candidate_key_skills`  ON `candidate_key_skills`.`candidate_id` = `candidate`.`id`
    LEFT JOIN  `candidate_qualification`  ON `candidate_qualification`.`candidate_id` = `candidate`.`id`
    LEFT JOIN  `candidate_desired_job`  ON `candidate_desired_job`.`candidate_id` = `candidate`.`id`
    LEFT JOIN  `candidate_experience`  ON `candidate_experience`.`candidate_id` = `candidate`.`id`
    WHERE  ( `name` LIKE '%PHP%' ESCAPE '!' )
      AND  ( `prefered_location` LIKE '%Ahmedabad%' ESCAPE '!' )
      AND  `total_experiance` >= '-1'
      AND  `total_experiance` <= '40'
      AND  `expected_salary` >= '0'
      AND  `expected_salary` <= '500000'
      AND  ( `candidate_experience`.`industry_type_name` LIKE '%Accommodation and  Food Services%' ESCAPE '!'
           )
      OR  `carrier_summary` LIKE '%PHP%' ESCAPE '!'
      OR  `carrier_summary` LIKE '%Ahmedabad%' ESCAPE '!'
      OR  `carrier_summary` LIKE '%Accommodation
      and  Food Services%' ESCAPE '!'
      AND  `candidate`.`id` >0
    GROUP BY  `candidate_key_skills`.`candidate_id`, `candidate_desired_job`.`id`
    ORDER BY  `candidate_desired_job`.`id` DESC


texts: Connecting to a SQL server whose IP changes

texts:
I want to connect to a server whose IP is dynamic via a VB.net application. I have configured the connection string in my VB application to a static IP.
I also gave a static IP to the database.

However, this work-around can be dangerous. Can someone please suggest an efficient way to connect to a SQL server in a remote machine whose IP is dynamic.


texts: In Postgresql, after pg_dump the access privileges on the database itself are not restored

texts:
Suppose user foo owns a database gee, and user foo has granted user bar with all access privileges on database gee

gee=> \l
                              List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 gee       | foo      | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/foo              +
           |          |          |             |             | foo=CTc/foo          +
           |          |          |             |             | bar=CTc/foo

We dump this database,

$ pg_dump -U foo -v -f gee.dump gee

In database gee user bar has created a schema. So because all of these users we restore database gee as user postgres (after user foo has created a database goo),

$ sudo -u postgres psql -d goo -f gee.dump

User bar does not have the access privileges on the database goo. But inside database goo every privilege is OK, for all users, on schemas and tables.

goo=> \l
                              List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 goo       | foo      | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

Why are the access privileges on the database itself not restored?


texts:
I have a .Net MVC app that, when submitted, captures the current DateTime in a jTable. The information in the jTable in populated from the MongoDB that the information hits when the app is submitted.

The problem I am having, is that when I go to export the data, it is showing the date -1 information for the current-day column. When I launch Studio 3T for MongoDB, the "DateCreated" column has date -1, but, when I hover the cursor over the row containing the "_id," it shows the correct, current date.

Is there a way to correct this that I am missing?

Thank you!


texts: Sql Database server Remote access not working

texts:
I have configured Remote settings from TCP/IP with 1433 port and
Firewall is also disabled

I have checked settings over intenet and applied still not able to connect remotly

I am using windows server 2008 r2 and server has Private Ip

Can anybody tell me is there any other setting which i need to do?

== Edit ==
The error is:

  "A network-related or instance-specific error occurred while
  establishing a connection to SQL Server. The server was not found or
  was not accessible. Verify that the instance name is correct and that
  SQL Server is configured to allow remote connections."


texts:
I have some queries dealing with coordinates stored in POINT.
Everything is ok without order (about 0.6 ms to query a 5 millions posts and 1 million users database, for testing) but go to 13s when add ORDER BY created_at DESC.)

All fields in WHERE clauses have index.
Created_at index is DESC sort.

Deal is to recover Posts submitted by Users in Geographical Sphere.

SET @radius = 30;

SET @center = GeomFromText('POINT(12.005 -1.001)');
SET @r = @radius/69.1;
SET @bbox = CONCAT('POLYGON((',
  X(@center) - @r, ' ', Y(@center) - @r, ',',
  X(@center) + @r, ' ', Y(@center) - @r, ',',
  X(@center) + @r, ' ', Y(@center) + @r, ',',
  X(@center) - @r, ' ', Y(@center) + @r, ',',
  X(@center) - @r, ' ', Y(@center) - @r, '))'
);

SELECT * FROM posts INNER JOIN users ON posts.user_id = users.user_id
WHERE posts.user_id IN (
    SELECT user_id  FROM users_contact WHERE
    Intersects( coordinates, GeomFromText(@bbox) )
    AND SQRT(POW( ABS( X(coordinates) - X(@center)), 2) + POW( ABS(Y(coordinates) - Y(@center)), 2 )) < @r )
    AND users.user_status=1 AND posts.status=1 AND posts.created_at <= '2016-10-24 10:30:53' AND posts.context = '1'
# ORDER BY posts.created_at DESC
LIMIT 30;

Tried INNER JOIN instead of IN Clause, but result is the same.
Putting Coordinates in users table instead of users_contact is the same.

Thanks, MySQL 5.5


texts: Sql Agent Job: Behavior different than running on command line: starts new process vs. existing process

texts:
I'm using sql server agent job (sql server 2019 cu1) to execute a function in a third-party running program. From the command line it is simple:
myProg.exe -myParam
This will always activate the running instance of myProg.exe (if it is not running already) and pass the parameter myParam, executing the desired function. (The behavior is like winword.exe, and unlike notepad.exe: a duplicate instance of myProg.exe will never be created).

But: when executing the same thing as an Sql Server Agent job, I don't get that behavior, whatever I try. Instead, Agent silently runs a NEW and hidden instance of myProg.exe. (I was baffled by this at first, since Agent always reports success, until I opened task manager and saw a zillion copies of MyProg.exe.) Unfortunately a new instance of MyProg.exe does not execute the function (the one triggered by myParam when myProg.exe is called from the command line).

Thanks for any suggestions.


texts: statistics are up to date, but estimate is incorrect

texts:
When I do dbcc show_statistics ('Reports_Documents', PK_Reports_Documents) I get the following result for Report ID 18698:

For this query:

SELECT *
FROM Reports_Documents
WHERE ReportID = 18698 option (recompile)

I get a query plan that makes a Clustered Index Seek on PK_Reports_Documents as expected.

But what baffles me is the incorrect value for Estimated Number of Rows:

According to this:

  When the sample query WHERE clause value is equal to a histogram
  RANGE_HI_KEY value, SQL Server will use the EQ_ROWS column in the
  histogram to determine the number of rows that are equal to

This is also the way I would expect it to be, however it seems not to be the case in real life. I also tried some other RANGE_HI_KEY values that were present in the histogram provided by show_statistics and experienced the same. This issue in my case seems to cause some queries to use very unoptimal execution plans resulting in an execution time of a few minutes whereas I can get it to run in 1 sec with a query hint.

All in all: Can someone explain me why is EQ_ROWS from the histogram not being used for the Estimated Number of Rows and where does the incorrect estimate come from?

A bit more (possibly helpful) info:

Auto create statistics is on and all the statistics are up to date.
The table being queried has about 80 million rows.
PK_Reports_Documents is a combination PK consisting of ReportID INT and DocumentID CHAR(8)

The query seems to load a total of 5 different statistics objects, all of which contain ReportID + some other columns from the table. They have all been freshly updated. RANGE_HI_KEY in the table below is the highest upper bound column value in the histogram.

+-------------------------------------------------------------------------+----------+--------------+--------------+---------------------+--------------+------------+----------+---------------------+----------------+
|                                  name                                   | stats_id | auto_created | user_created | Leading column Type | RANGE_HI_KEY | RANGE_ROWS | EQ_ROWS  | DISTINCT_RANGE_ROWS | AVG_RANGE_ROWS |
+-------------------------------------------------------------------------+----------+--------------+--------------+---------------------+--------------+------------+----------+---------------------+----------------+
| PK_Reports_Documents                                                    |        1 |            0 |            0 | Stationary          |        18722 | 0          | 2228,526 |                   0 | 1              |
| _dta_index_Reports_Documents_42_1629248859__K1_K63_K14_K13_K22_K23_72_6 |       62 |            0 |            0 | Stationary          |        18698 | 0          | 2228,526 |                   0 | 1              |
| _dta_stat_1629248859_1_1_59                                             |       76 |            0 |            1 | Stationary          |        18686 | 50,56393   | 1        |                   0 | 13397,04       |
| _dta_stat_1629248859_1_22_14_18_12_6                                    |       95 |            0 |            1 | Stationary          |        18698 | 0          | 2228,526 |                   0 | 1              |
| _dta_stat_1629248859_1_7_14_4_23_62                                     |       96 |            0 |            1 | Stationary          |        18698 | 56,63327   | 21641,5  |                   0 | 14526,44       |
+-------------------------------------------------------------------------+----------+--------------+--------------+---------------------+--------------+------------+----------+---------------------+----------------+

sp_updatestats is scheduled to run every night to update stats.


texts: Invalid column name 'database_id' in sp_blitzfirst

texts:
I am receiving the following in messages when running the sp_blitzfirst @expertmode = 1 on the following box:

kit version: SQL-Server-First-Responder-Kit-20161210

Microsoft SQL Server Standard Edition (64-bit) version 10.50.6000

  Setting up configuration variables
  Msg 207, Level 16, State 1, Line 5
  Invalid column name 'database_id'.
  Now starting diagnostic analysis
  Capturing first pass of wait stats, perfmon counters, file stats
  Beginning investigatory queries
  Finished running investigatory queries
  Waiting to match @Seconds parameter
  Capturing second pass of wait stats, perfmon counters, file stats
  Analyzing changes between first and second passes of DMVs
  Analysis finished, outputting results
  Msg 207, Level 16, State 1, Line 5
  Invalid column name 'database_id'.

Here is what I get running sp_blitzwho:

  Msg 207, Level 16, State 1, Line 5
  Invalid column name 'database_id'.

Any help would be appreciated.


texts:
Is there a way i can track for a given query how much time was spent by SQL server vs time spent in transferring data in network? I am learning performance tuning, hence this question.

I am looking for a way to do it in SSMS but would like to know if there is any other tools/framework for the same.

Thanks.


texts: Can't default date to CURRENT_TIMESTAMP in MySQL 5.5

texts:
I am not able to set Current_timestamp as default value. My Mysql version is 5.5.47.

Query is

ALTER TABLE `downloads` ADD `date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ;

While it is working fine on my local DB with mysql V5.6.56.


texts: Find continuous records with equal values in each group

texts:
I have a table named "MachineHealth". Here is the sample data:

Date
MachineId
IsBad

2020-01-05
1
1

2020-01-04
1
1

2020-01-03
1
1

2020-01-02
1
0

2020-01-01
1
1

2020-01-05
2
1

2020-01-04
2
1

2020-01-03
2
0

2020-01-02
2
0

2020-01-01
2
1

I want to find continuous 1 count in field IsBad group by MachineId, that means the number of days that a machine is continuously bad for the most recent time. For example, the query on above data should return:

MachineId
ContinuousBadDays

1
3

2
2

How to write this sql?


texts:
I have a common scenario where I need to drop and create an Amazon Redshift table or view (more often with views). There are situations where an ALTER is not going to work, which would preserve privileges.

What query can I use to identify all of the privileges on a particular table or view so that I can script out the reapplication of those privileges, after I perform a drop and create operation?


texts: Is it possible to have a loop in SQL without creating a stored procedure?

texts:
I would like to loop through the vertices in a SHAPE/GEOMETRY column in a user-defined data type (ESRI's version of ST_GEOMETRY). I've posted the details in a GIS SE post here: https://gis.stackexchange.com/questions/209894/update-sde-st-geometry-m-values-to-cumulative-distance-using-oracle-sql

I believe this would normally be done in a stored procedure (probably in conjunction with the sde.ST_GEOMETRY functions ST_NumPoints and ST_PointN) . However, I'm merely a data analyst, not a DBA, and do not have CREATE PROCEDURE permissions, so I can't make a stored procedure. Additionally, in my company/organization, a request for a procedure to be made would either be rejected or take years to be implemented.

So I'm wondering, as an alternative to creating a stored procedure, if I could loop through the vertices within an SQL update statement instead?

Oracle 12c (12.1.0.2.0)


texts: SSRS report stuck on Loading but ExecutionLog says otherwise

texts:
I have a report that is stuck on loading in the reporting server portal (it's been over a day). However when i check the ExecutionLog...

select * from ExecutionLog3
WHERE UserName = 'SOMEREPORTSERVER\SomeName'
AND ExecutionID = 'cbk0hdnpzrspg5qpkvqxubyn'
order by TimeStart desc

...it says that the status is 'rsSuccess'.

Here is the AdditionalInfo XML which might help... The query took about 1 minute to run which is how long it took to run in SSMS. Rendering seems pretty fair with the amount of data returned so I am not sure what's going on.

I made sure that Interactive Sizing is standard 8.5 x 11 so that it can show at least some data while processing the other pages but that did not help.

<AdditionalInfo>
  <ProcessingEngine>2</ProcessingEngine>
  <ScalabilityTime>
    <Pagination>0</Pagination>
    <Processing>0</Processing>
  </ScalabilityTime>
  <EstimatedMemoryUsageKB>
    <Pagination>263471</Pagination>
    <Processing>3795406</Processing>
  </EstimatedMemoryUsageKB>
  <DataExtension>
    <SQL>2</SQL>
  </DataExtension>
  <Connections>
    <Connection>
      <ConnectionOpenTime>24</ConnectionOpenTime>
      <DataSets>
        <DataSet>
          <Name>nocontacts</Name>
          <RowsRead>79650</RowsRead>
          <TotalTimeDataRetrieval>10297</TotalTimeDataRetrieval>
          <QueryPrepareAndExecutionTime>0</QueryPrepareAndExecutionTime>
          <ExecuteReaderTime>10225</ExecuteReaderTime>
          <DataReaderMappingTime>0</DataReaderMappingTime>
          <DisposeDataReaderTime>0</DisposeDataReaderTime>
        </DataSet>
      </DataSets>
    </Connection>
    <Connection>
      <ConnectionOpenTime>30</ConnectionOpenTime>
      <DataSets>
        <DataSet>
          <Name>contacts</Name>
          <RowsRead>724715</RowsRead>
          <TotalTimeDataRetrieval>70893</TotalTimeDataRetrieval>
          <QueryPrepareAndExecutionTime>0</QueryPrepareAndExecutionTime>
          <ExecuteReaderTime>26506</ExecuteReaderTime>
          <DataReaderMappingTime>0</DataReaderMappingTime>
          <DisposeDataReaderTime>0</DisposeDataReaderTime>
        </DataSet>
      </DataSets>
    </Connection>
  </Connections>
</AdditionalInfo>

Edit: SSRS - 14.0.807.134 / SSMS - 14.0.17177.0 / Browser - Chrome
Edit: Trying IE - 11.309.16299.0 - I don't have the access to update report viewer settings.


texts: Backup suddenly is taking too long to complete

texts:
We have an SQL Server 2014 Enterprise Edition database around 3 TB in size. We are taking compressed full backup weekly, it was working fine till last week and now suddenly it is taking 18 hours to complete.

When backup completes the backup size is around 550 GB.

Backup drive has around 950 GB disk space free.

What could be the issue?


texts:
Suppose I have a table like this:

create table mytable(cola int primary key, colb varchar(10));

Let's put some data in it:

insert into mytable values(1, 'abc');
insert into mytable values(2, 'def');

When I select using this query:

select * from mytable where colb = 0;

I get ALL rows.

Please note colb data type is varchar, but I use 0 as value. If I use 1 or other integers, I get no rows.

Why is that? Is there a setting to prevent this behavior?

BTW mysql 5.5.9 for windows.


texts: openrowset to connect to SQL server with a password containing single quotes

texts:
Can someone help me if there is ever a way to query an Azure SQL database using openrowset with an SQL authenticated account having "single quotes" in it's password string. My statement looks like the below

(I'm querying the Azure database from on-premises)

SELECT a.*
FROM OPENROWSET('SQLNCLI11', 'SERVER=sqlserver.database.windows.net,1433;DATABASE=testDB;Uid=admin;Pwd=Fj'C(D=JJ&'XE6vx};','select * from table1 with (nolock)')a

I tried the below solutions

solution 1 - Dynamic SQL

declare @string varchar(max)
set @string=
'SELECT a.*
FROM OPENROWSET(''SQLNCLI11'', ''SERVER=sqlserver.database.windows.net,1433;DATABASE=testDB;Uid=admin;Pwd="Fj'''+'C(D=JJ&'''+'XE6vx}";'',''select count(*) from dbo.table1 with (nolock)'')a'

exec (@string)

Solution 2 - Passing the password in as a parameter

declare @string varchar(max)
declare @pwd varchar(max)
set @pwd = 'Fj'''+'C(D=JJ&'''+'XE6vx}'
set @string=
'SELECT a.*
FROM OPENROWSET(''SQLNCLI11'', ''SERVER=sqlserver.database.windows.net,1433;DATABASE=testDB;Uid=admin;Pwd='+@pwd+';'',''select count(*) from dbo.table1 with (nolock)'')a'

exec (@string)

They all result in the very same error (points to the password)

  Msg 102, Level 15, State 1, Line 16 Incorrect syntax near 'C'.

using a SQL authenticated account without any "single quote" in it's password works absolutely fine.

I could easily change the password to fix this issue, But still would like to know if there is ever a solution to this.


texts: Will more indexes on a table affect performance?

texts:
Possible Duplicate:
  How do indices impact query performance?
  How to know when/if I have too many indexes?

Say ‘X’ report using some "where" condition in the statement so we are creating an Index ‘A’ on table ‘T’.
Say ‘Y’ report using some "where" condition in the statement so we are creating an Index ‘B’ on table ‘T’..
Say ‘Z’ report using some "where" condition in the statement so we are creating an Index ‘C’ on table ‘T’.
Say ‘M’ report using some "where" condition in the statement so we are creating an Index ‘D’ on table ‘T’.

Now there are 4 indexes and this table has a huge volume of records.

When I insert or update rows in this table, do the indexes affect the performance of those operations?
If so, then how do I tune those queries to perform well?


texts: Unable to select the records from specific partition in mysql?

texts:
I've created a table using the following query,
CREATE TABLE employees  (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    fname VARCHAR(25) NOT NULL,
    lname VARCHAR(25) NOT NULL,
    store_id INT NOT NULL,
    department_id INT NOT NULL
)
    PARTITION BY RANGE(id)  (
        PARTITION p0 VALUES LESS THAN (5),
        PARTITION p1 VALUES LESS THAN (10),
        PARTITION p2 VALUES LESS THAN (15),
        PARTITION p3 VALUES LESS THAN MAXVALUE
);

Then, i've inserted the data to the table using the partition information and the query as follows,
INSERT INTO employees VALUES
    ('', 'Bob', 'Taylor', 3, 2), ('', 'Frank', 'Williams', 1, 2),
    ('', 'Ellen', 'Johnson', 3, 4), ('', 'Jim', 'Smith', 2, 4),
    ('', 'Mary', 'Jones', 1, 1), ('', 'Linda', 'Black', 2, 3),
    ('', 'Ed', 'Jones', 2, 1), ('', 'June', 'Wilson', 3, 1),
    ('', 'Andy', 'Smith', 1, 3), ('', 'Lou', 'Waters', 2, 4),
    ('', 'Jill', 'Stone', 1, 4), ('', 'Roger', 'White', 3, 2),
    ('', 'Howard', 'Andrews', 1, 2), ('', 'Fred', 'Goldberg', 3, 3),
    ('', 'Barbara', 'Brown', 2, 3), ('', 'Alice', 'Rogers', 2, 2),
    ('', 'Mark', 'Morgan', 3, 3), ('', 'Karen', 'Cole', 3, 2);

Now, I'm trying to select the records from specific partition, and my query was,
SELECT * FROM employees PARTITION (p1);

But i'm keep on getting the same error as,
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(p1) LIMIT 0, 30' at line 1
FYI, initially i was using InnoDB database engine, now i've changed it to MyISAM, but nothing helped me out.


texts: MySQL master slave replication on a per database basis

texts:
We use MySQL master-slave replication to maintain a backup database server as a replica of our master / production database server.

If there are issues with the master slave replication (i.e. it's become corrupted for some reason), we stop the replication, read lock the master, take the replication co-ordinates, take dumps from the master, unlock the master, restore the dumps to the slave

In our database servers, we have several databases, including some which are quite large (100 - 200 GB). As such, taking the dumps can take a long time during which the master database is subject to read locks. Also, restoring the slave can take a long time, which means we're without our backup replication for a long time.

Is it possible to run master-slave replication, or some equivalent, on a per database basis?

That is, for each individual database on the servers, the replication of changes is done separately, to separate files etc. This would allow us to turn replication on/off for individual databases. Then, if the replication gets corrupted for any individual database, we can turn off the replication for that database, fix and restart it, without having to turn off the replication for the other databases.

I'm aware of the ability to limit the master replication or slave replication by databases (replication-do-db or replication-ignore-db), but the data is still bundled in single files.


texts: Changing startup accounts on alwayson setup

texts:
I have a 2 node Always On Availability Group cluster where we are planning to take backups to a file share which will then be moved to Azure cloud. We have a service account from the Windows team for the backup job, but the startup account of the database engine and the SQL Server Agent are local accounts. Using proxies did not help me with the situation so I am now thinking of providing the service account credentials in the SQL Server Configuration Manager.

What I want to know is how do I proceed?

This is what I have planned. Please let me know if I have missed something in the flow

Change service account on the Node2 (secondary) - both SQL Server (InstanceName) (database engine) and SQL Server Agent (InstanceName) (job engine)
Pause synchronization
Restart the SQL Server (InstanceName) service and the SQL Server Agent (InstanceName)
Failover the AOAG to the secondary, then change the account details of the primary (now the seconday)
repeat step 2. and 3.
Fail back to the primary again

Please let me know if I am missing any step here

Thanks!


texts: SQL Server Editions: Backup restore of Enterprise Edition 2014 on Standard Edition 2017

texts:
I know, this kind of question has already been answered some times but I don't find some current answers and wonder if SQL Server Standard Edition 2016 SP1 has changed something in the matter as it supports all "programmability features" earlier supported by enterprise edition only.

My specific scenario is as follows:
I have got a central backup server which will be running SQL Server 2017 Standard Edition and regulary restores new backups taken from other SQL Server instances. Most instances will be on SQL Server 2017 Standard as well...however there is a Data Warehouse running SQL Server 2014 Enterprise Edition.

Is it possible to restore backups taken from  a SQL Server 2014 Enterprise Edition instance on a SQL Server 2017 Standard Edition instance or could this result in an error regarding to Enterprise Edition features?
Is there any way to try this out for free? SQL Server evaluation edition is equivalent to Enterprise Edition isn't it or has Microsoft now introduced a way to downgrade developer and evaluation licenses to SQL Server Standard Edition features?

Thank you very much in advance for your assistance


texts: Billing Module (Fee) for School Management System

texts:
I am working on a School Management software which includes module for billing of students i.e, fee collection from students.

The fee is collected on monthly basis (12 times a year) and the total monthly fee is combination of various charges, fixed as per class and month. Some of the charges include Tuition Fee, Bus Fee, Printing Charge or others. A fixed amount of late fee is also charged on per month basis starting from the date, which can vary from month to month. Bus fee is charged as per the bus category of a particular student. There is a provision for partial payments as well.

My current approach is like this:

A master table to store fee settings, which include month & class and the fee settings.

feeMaster
    fid  -> Primary key
    month_year -> Stores Month Year
    stu_class -> Class of Student
    tuition_fee -> Tuition fee for that class
    tuition_fee_percent -> Percentage of Tuition fee to take, defaults to 100%
    bus_fee_percent -> Percentage of bus fee
    late_fee_start -> Day of month from which to charge late fee
    late_fee -> fixed late fee on per month basis
    printing_charge -> Printing charge if any
    other_fee -> Other fee if any
    other_fee_reference -> Other fee reference

Every time a student comes to pay his/her fee, calculation is made and a transaction takes place in the system. The details of the transaction are stored in two tables.

Transaction master table is used to store the transactions

transMaster
    tid -> Primary key
    purpose -> purpose of transaction, monthly fee
    amount -> amount of transaction
    type -> transaction mode / cash / cheque / dd
    created -> date

The details of this transaction are stored in another table as

studentFeeDetails
    sfid -> unique id
    tid -> transaction id from transMaster table
    fid -> fee id from fee settings table feeMaster
    tuition_fee -> calculated tuition_fee
    bus_fee -> calculated bus_fee
    printing_charge -> calculated printing_charge
    other_fee -> calculated other_fee
    late_fee -> calculated late_fee
    total_fee -> total fee calculated
    discount -> discount if given any
    amount_payable -> net amount payable
    amount_paid -> paid amount
    balance -> balance - if paid amount is greater or lesser than the original one,
               it is stored here
    status -> status - true if partial fee else false
    created -> date of creation

This is the current architecture of the module. There is no accounting practices involved and hence creates lots of problems for our accounting deptt.

To report the total payable fee for a month, every time, the system runs calculation algorithm on all the students and comes up with figure.
To find the pending fee for a class, the system again first checks the receivable fee for that class and removes the entries found in studentFeeDetails table to generate the pending report.
There is no proper segregation of fee heads under this system.
Now the current system needs to be converted into proper accounting system which can track the advances and balances.

I am thinking of a system where every month a posting process debits every student's account with the fee due for that month and on every late fee start date another process debits late fee to a student's account, if the fee is still pending.

This approach can keep a check on receivables, pending and received fee.

Please help, if the approach is correct and how to go with it. I am stuck with the db schema part and its implementation.


texts: Why does my procedure run slowly until I update statistics?

texts:
I am using a SQL 2008 database (not R2 sadly but have also tested this on an R2 database server) and have an ELT process running. Data is extracted from source and loaded to staging tables in the destination DB, and then the transform processes are run. On Tuesday (26 Nov) for no known reason, the procedure which 'shreds' the staging data for providers into the relational tables went from running approx 9 per second to one every 5½ minutes. No parts of the database, ELT process (not a typo!), database server etc were changed.

With around 800 records every night (and up to several thousand) this single step of the process has gone from taking just over a minute to potentially taking just over 3 days (this doesn't happen in practise of course because we notice and stop the long running job). This happens even if I manually run the code which is within the procedure rather than letting it run as part of SSIS, and the same happens if I run through the code for just one record rather than letting the loop go through.

By sheer chance I happened to discover that if I ran the following code against the provider staging table and the provider destination relational table, suddenly my ELT process runs as normal again and zips through everything:

update statistics tblProviderStaging with fullscan
update statistics tblProvider with fullscan

My question (sorry for the journey to get here) is why would I have to run this manually every morning? I can't even run it before the ELT as it makes no difference - only affects the process if I run it while the process is slowly churning through. Presently I am running this at 7am every morning (SSIS kicks off at 6:30). Auto Update Statistics and Auto Create Statistics are both set to 'True'.

I have never, in my limited experience, come across a scenario where I have to manually run this every morning? And the fact that I can't just run it either but have to run it while the ELT is still running? We have tried running the SSIS package from a different server, against a different destination database server (restored the DB to another DBS), have checked all indexes, I/O performance is running as normal, there is no undue server load happening while this runs (it was running about 18% capacity and nothing to speak of on any of the performance metrics).

The staging table in question has just over 1.2 million rows and uses up just over 1.5GB of space (including index size). The destination table in question has 430k rows and uses around 140 MB of space (including index size). This has not changed significantly.

If there is any more information I need to provide please let me know but I'm shooting into the dark here so I don't know what is helpful and what is just in the way. If anyone has any ideas of places and things to check to see why I am having to do this please shout!

Please note we have run checkDB which has not shown any issues in the DB.


texts:
The mysql service was stopped and in mysql log I saw these warning:

161125 16:41:10 [Warning] IP address 'x.x.x.x' has been resolved to the host name 'x.x.x.x.xxx.xxxxxx.com', which resembles IPv4-address itself.
161125 23:58:23 [Note] /usr/sbin/mysqld: Normal shutdown

Notice:

with iptable, only the server address is accepted for port 3306
In my.cnf I set the bind_address = server_adress_ip: bind_address = x.x.x.x

Why the mysql service was stopped?


texts: Problem with decimal point setting in Windows

texts:
I am using bcp.exe from a SQL Server 2017 installation on Windows 10 (build 19041). The OS language is English (US) but I use scandinavian settings for date, time, currency and decimal delimiter - the decimal delimiter is a comma (,) not a point (.)
When I do a "bcp out -c" the file is written, as expected, with decimal point as I am not using the -R option. But when I do a "bcp in" on the same file the . is ignored so 1.23 is imported as 123.
If I change the OS setting to decimal point it works fine, so it seems that "bcp out" works as expected (always decimal point) but "bcp in" erroneously takes Windows settings into account.
Anyone else has this experience and possibly knows a fix?


texts: What is the threshold of sequence# difference between primary and standby database when checking replication status?

texts:
I am checking if replication is working properly in standby database. For this, i calculate the difference between sequence# of v$log_history.
I am using this query to find the max sequence number.
select max(sequence#) a from v$log_history where FIRST_TIME > SYSDATE -1;

Executed the query in primary and standby database and then calculate the difference.
What is the threshold value of the difference from which i can decide if the replication works properly or not?


texts: Executing Stored Procedure that accesses another SQL instance

texts:
I apologize if this question repeats another already asked.  I have searched for hours and have not found one that fits my situation.

Desired Outcome

A user using SQL authentication has execute permissions to Database1 on Server1 (default instance) and that is it.  The user executes a stored procedure that, as part of its process, accesses Database 2 on Server1\Instance2.  I would like it to be safe and simple (both are important).

More Info

My windows credentials has access to both instances (which are on the same server).  Therefore, I can execute the stored procedure under my login without difficulty.  However, I don't want to give the user my level of access.  I also need to use a SQL login since the user will not be on the domain.

What I would like would be to give the stored procedure my level of access just for that procedure.  Since I am a sysadmin, that would give the user everything they needed for that procedure.  If I got that to work, I would probably create an account just for that purpose instead of using mine, but either way it would be safe since I control what the stored proc does.

I tried putting the "WITH EXECUTE AS" statement in my stored proc but I couldn't get it to take my windows login information.  When I put it in, I would get the following error upon compiling the stored proc:

  Cannot execute as the user 'domain\jdoe', because it does not exist or you do not have permission.

The user is sysadmin on both servers, like I said, so I'm not sure what more it needs.

I have looked into the following:

TRUSTED - I would rather not expose my database and this looks scary
Linked server - I don't want to give extra permissions.  I don't trust the other database to have access to my database and I don't trust my database to have access to all of the other database.
Certificates - This seems complicated and difficult.  Unless I could find a very simple way to do this and maintain it, I'm not sure it is worth the trouble.
Ownership chaining - Again, scary.  It looks like this causes more security issues when my goal is to prevent security issues.
Mirrored user - I've even created the same (different SID obviously) user on the other server instance and gave it the same password.  No go.

I feel like I am missing something obvious but I'm not sure what it is.  Since I've been banging my head against the wall all day on this, I'm probably too close to see it.  I would very much appreciate it if someone here could give me a hand or point me in the right direction.  I will say that I have read a lot of the MSDN articles (boy do I hate them - they never seem to tell me what I want to know).  What I would really like is a simple, easy to follow tutorial that walks me through how to do this.  Short of that, even a general indication of the direction I need to go would be helpful.


texts: How to solve repetitive ORA-2391 error in DBeaver 4.3.3.1

texts:
I can't seem to find the way to avoid ORA-2391 "exceeded simultaneous SESSIONS_PER_USER limit" when working with DBeaver 4.3.3.1. Everytime my session goes idle and I need to recconect I'm getting this error. I'm not sure if this is a DBeaver issue or I am missing something in my Oracle connection configuration.

I already talked with DBA and he showed me that whenever I connect to my database DBeaver creates two sessions (two rows in table v$session) and when he tries to kill them, they hang out for a while until they finally go away and I can connect again.

I am sure I don't have any other session opened with any other tool.

My goal is to keep working with DBeaver since this tool has some amazing features, but without having to bother my DBA everytime I can't connect due to simultaneous sessions.

If there could be any way to stop DBeaver from creating more than one session for just one connection, I will appreciate any help.

PS:(I hope I'm clear with my question, English is not my native language)


texts: Best database design for website ranking

texts:
I am working on a project in which I need to rank websites in two way - Global rank and their rank in a particular country. There are various factors based on which both are calculated. There are ~200 countries for which rank needs to be calculated.

I am using MySQL

I have two questions -

Global rank

Whenever any new website is added -

Either I need to recalculate the ranks for all the website if I store the website rank in database. This requires a lot of processing each time a new website is added.
or the other option is to assign a score to each website and whenever query is made assign rank by doing order by based on score, and returning its position. This doesn't seem scalable way as order by is expensive operation.

Country Rank database and updation

How should I store per country rank of each website ?
How to update all these ranks when a new website is added ? Here the updation will be even more costly than in case of Global rank.
Should I generate it on-the-fly when queried and then cache the result ?

How do websites like play store show personalized and country based results ?


texts:
An application INSERTS a parent record then child objects. Both these procedure call different stored procedures. Parent can have many child objects.  Typically both parent and child would be inserted in milliseconds.

I am finding the parent record is created ok but the child objects arent. The inserts are not bound by a transaction , they are also done individually  - no batch inserting.

This problem manifests itself as a parent with no child rows or a parent with a single child row or a parent with no end row (or i guess missing child in the middle but this would be hard/impossible to identify).

To the end user they see the parent with no end/closing row generally i.e the first CHILD that arrives creates a new PARENT object based on its status.

I am using SQL profiler to examine/alert on deadlocks. There are a few but fall far short of the missing child objects.

I have reduced SQL cpu pressure , was bouncing off 100% , not rarely gets near 100%.

Using SPOTLIGHT i can monitor lock/waits but see nothing unusual , infact look better than normal. LATCHES are relatively low too.

Today our application logs are not showing any timeouts but they have appears in small quantities in the past particular when sql server cpu at 100% or during out of hours maintenance.

"Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."

I monitor disk read/write speed. Not problems.

  QUESTIONS

1)In regards to the TIMEOUT which timeout value should be amended on the connection string to potentially counter this error above ?

2)Regards failure to INSERT , can you suggest any processes i should be monitoring to quantify the problem ?

Thank you , I hope the above is clear enough.
(SQL 2005 Standard)
(Parent table 200m records)
(Child table 1bn records)


texts: Most efficient way to solve the subset sum problem in PostgreSQL

texts:
I'm looking now at the subset sum problem... inspired from this post.

It's an interesting problem.. Here we create two tables

foo with 1,000 rows (id,rand) between 5..105
bar with 10 rows (id,rand) between 1..1,000

Source code to generate them is as follows.

CREATE TEMP TABLE foo
AS
  SELECT x AS id,
    trunc(random()*100)+5 AS rand
  FROM generate_series(1,1000)
    AS t(x);

CREATE TEMP TABLE bar
AS
  SELECT x AS id,
    trunc(random()*1000) AS rand
  FROM generate_series(1,10)
    AS t(x);

Now the goal is to find whatever combinations of foo sum closest to the row in bar without going over.

Ideally, I'd like something like this.

bar.id  bar.rand   foo.ids              foo.sum
1       872        {14,398,72,...etc}   868


texts: Are string columns with very few different entries automatically compressed?

texts:
We have a table which contains a column with only a dozen different strings in it. Is there anything I need to do in order to get the DB to compress this information? We don't have too much disk space available and it seems like a waste to store all these long stings over and over again.

I would like the DB to store this information internally in a compressed form. For example, the current data looks like this:

Column "TypeInfo"
---------------------------------------------------------
very_long_descriptor_someone_came_up_with_a_long_time_ago
this_desciptor_is_also_very_long
nobody_knows_why_this_descriptor_is_so_long
very_long_descriptor_someone_came_up_with_a_long_time_ago
very_long_descriptor_someone_came_up_with_a_long_time_ago
nobody_knows_why_this_descriptor_is_so_long

I'd like to see SQL Server store some kind of small key instead that represents those longer strings that are repeated:

Column "TypeInfo"
-----------------
1
2
3
1
1
3
(+ mapping information)


texts: MySQL DB import/export command line in Windows

texts:
How to export / import a database in MySQL through the command line?

MySQL system configuration, WampServer server installed.

OS: Windows

The advice for my local host, not an live hosting...


texts: How to log successful login attempts to a database?

texts:
Is there a way to log database name when auditing logins in SQL Server (2005 and above)?

I basically want to log the username and the database name the user successfully connected to.

I have tried profiler, databasename column is blank.


texts: PostgreSQL converting undefined JSONB fields into a row dynamically

texts:
I have a table with a field of type jsonb, I would like to split the column of field jsonb for all the json keys. This column lacks a schema. For instance:

From

CREATE TABLE v(id,jsonb)
  AS VALUES
    (1,'{"a":"4", "b":"5"}'::jsonb),
    (2,'{}'),
    (3,'{"a":"8", "c":"9", "d":"9"}');

to

id  |  a   |   b   |  c  |  d

1   |  4   |   5   |     |
3   |  8   |       |  9  |  9

for this specific case one solution is

select * from table, json_to_record(optional) as x("a" text, "b" text, "c" text, d text)

As you can see the keys can vary and and in a big database is difficult put all the keys in my real problem I have 31 keys and  on other hand If I want to reuse this script in other table I have to fill manually the keys.

Is there any way to do a select all over the keys of jsonb without specify manually the keys?


texts: Powershell Get-Counter not returning values for SQL counters

texts:
I am attempting to use PowerShell to get some SQL counters but it doesn't seem to return the SQL specific counters anymore. I am not sure what could have changed on my server that would have caused this.

The script is pretty straight-forward, I am not sure what I am missing:

## Define some variables
$serverName = "ISOMORPH" # Server we are collecting from

# Define our list of counters
$counters = @(
    "\Memory\Available MBytes",
    "\Memory\Pages/sec",
    "\PhysicalDisk(_Total)\Avg. Disk sec/Read",
    "\PhysicalDisk(_Total)\Avg. Disk sec/Write",
    "\PhysicalDisk(_Total)\Current Disk Queue Length",
    "\PhysicalDisk(*)\Avg. Disk sec/Read",
    "\PhysicalDisk(*)\Avg. Disk sec/Write",
    "\PhysicalDisk(*)\Current Disk Queue Length",
    "\Process(sqlservr)\% Privileged Time",
    "\Process(sqlservr)\% Processor Time",
    "\Processor(*)\% Privileged Time",
    "\Processor(*)\% Processor Time",
    "\SQLServer:Buffer Manager\Buffer cache hit ratio",
    "\SQLServer:Buffer Manager\Buffer cache hit ratio base",
    "\SQLServer:Buffer Manager\Lazy writes/sec",
    "\SQLServer:Buffer Manager\Page life expectancy",
    "\SQLServer:Memory Manager\Memory Grants Pending" ,
    "\SQLServer:SQL Statistics\Batch Requests/sec",
    "\System\Context Switches/sec",
    "\System\Processor Queue Length"
)

## Get our performance counter data
Get-Counter -Counter $counters -SampleInterval 1 -MaxSamples 1

The script will return all of the counters, but all of the SQL counters show 0 values. I am not getting any errors, it just shows all of the SQL counters as having a value of 0.

If I manually open Performance Monitor I can see these counters and they have values. I am running this on a Windows 7 laptop with SQL 2014 Developer Edition installed.

EDIT: Further testing leads me to believe this is an OS specific problem. Someone else tested this script with Windows 8.1 running SQL 2012 and it returns 0 for the SQL specific counters in that case as well.


texts: Delete unused indexes from Microsoft Dynamics CRM Application

texts:
So I found tons of unused indexes from our CRM application with this query from Pinal:

SELECT
o.name AS ObjectName
, i.name AS IndexName
, i.index_id AS IndexID
, dm_ius.user_seeks AS UserSeek
, dm_ius.user_scans AS UserScans
, dm_ius.user_lookups AS UserLookups
, dm_ius.user_updates AS UserUpdates
, p.TableRows
, 'DROP INDEX ' + QUOTENAME(i.name)
+ ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement'
FROM sys.dm_db_index_usage_stats dm_ius
INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID
FROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) p
ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1
AND dm_ius.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC
GO

I was reading about the CRM application and how it creates it's own indexes and etc.

I don't know if this is the right place to ask a question about Microsoft CRM but, is it safe to drop those indexes with 0 seeks, lookups and scans with tons of updates and rows?

I can find problems it they hard coded that index inside the source code, but, is this a good pratice that microsoft uses?

Note: I can close this post if this is not the place to ask this question.


texts: SQL Server - Server name same as the instance name and most common use for instances?

texts:
Is the server name the same thing as an instance name? I have looked around and am not sure; I think they are but I don't just want to guess.

Also, what is the most common use for SQL instances? I understand why it would be useful to have different instances, but I would like to know what others find the most use of it from.