texts: Help needed with SQL Server query: Accessing columns not in GROUP BY clause

texts:
I have a table whose columns are as follows:

CREATE TABLE Tests (
  TestDate date,
  EntityID int)

Using a stored procedure, I want to find the ‘academic year’ of pairs of tests that happened in the same ‘academic year’, where ‘academic year’ is defined as year part of the test date after a fixed number of months (@AcYearOff, value is typically equal to 7) is subtracted. I do this using the following code:

CREATE TABLE #tmpTable (
  vYear char(4),
  vDate char(8))

INSERT # tmpTable (vYear, vDate)
SELECT DATEPART(yyyy, dateadd(MONTH, @AcYearOff, TestDate)), format(TestDate, 'MMM yyyy')
FROM Tests
WHERE Tests.EntityID = @ EntityID

SELECT vYear
FROM # tmpTable
GROUP BY vYear
HAVING count(vYear) = 2
ORDER BY vYear

The above code functions exactly as expected. I now want to add to this query a string that shows the actual month and year in which the two tests were taken. I added the column vDate to the temporary table for this purpose, but I cannot figure out how to combine the two vDate values and return them as a string.

To be clear, here is some example data from the Tests table:

2018-08-12, 21
2018-09-04, 22
2019-04-17, 21
2019-08-03, 21

and the stored procedure should return:

‘2018’, ‘Aug 2018 and Apr 2019’

I look forward to seeing your suggestions. Thank you.


texts: Modifying legacy database tables that use composite primary keys

texts:
Suppose you have an SQL Server database used by a legacy application that has many tables with composite primary key columns (and thus, composite foreign key columns as well). Instead of composite primary key columns, a single primary key column is desired for use by other, more-modern applications, but since the legacy application cannot be changed, neither can these composite primary key columns.

My initial solution involves the following:

Adding a new identity column to each table with composite primary key columns, as well as adding unique non-clustered indexes to these identity columns.
Adding new nullable integer columns to each table with composite foreign key columns, as well as foreign key constraints to the corresponding new identity columns and non-clustered indexes to these integer columns.
Adding AFTER INSERT/UPDATE triggers to these tables to update these new integer columns when new rows are inserted or existing rows are updated.

Given the above scenario, is my solution an effective way to modify these SQL Server database tables with existing composite primary key columns to allow for a single unique column to be introduced to allow for other applications to use instead? Are there any storage or performance issues to consider?


texts: indexes and partition switching - how it works

texts:
We use partition switches to quickly load data, switching entire tables.  We load an empty Table A with INSERTs and then switch it with empty Table B.  These tables have identical indexes.

What I don't understand why Table A's index as no fragmentation but Table B's index has fragmentation (16% in one example).  I assume because both tables had to have the same indexing, the indexes also got switched, but clearly that's not the case.

I am looking for an explanation as to how this works and why this happens.


texts: using SUM on foreign view's keys grouped on multiple key/values?

texts:
I had a question on executing SUM on rows from one table and inserting them in a column on another table where the calculated rows shared id with a row in the new table.

The solution worked very well for that instance. However, I have a similar situation in which that query no longer works.

I have this TABLE:

CREATE TABLE buildings (
    id SERIAL PRIMARY KEY UNIQUE,
    name character varying(20) NOT NULL UNIQUE,
    document jsonb
);

then this VIEW:

CREATE VIEW v_rooms AS
SELECT buildings.id AS building_id,
    buildings.name AS building_name,
    jsonb_array_elements( document -> 'levels' ) -> 'meta' ->> 'level_number' AS level_number,
    ((jsonb_array_elements(jsonb_array_elements(buildings.document -> 'levels'::text) -> 'rooms'::text) -> 'meta'::text) ->> 'sqft'::text)::numeric AS sqft
FROM buildings
GROUP BY buildings.id;

finally in this VIEW:

CREATE OR REPLACE VIEW v_levels AS
SELECT
  buildings.id AS building_id,
  buildings.name AS building_name,
  jsonb_array_elements( document -> 'levels' ) -> 'meta' ->> 'level_number' AS number,
  sqft
FROM buildings

I want the COLUMN 'sqft' to be the SUM of all rows in v_rooms where v_rooms.building_id = v_levels.building_id AND v_rooms.level_number = v_levels.number.

I tried this VIEW definition:

CREATE OR REPLACE VIEW v_levels AS
SELECT
  buildings.id AS building_id,
  buildings.name AS building_name,
  jsonb_array_elements( document -> 'levels' ) -> 'meta' ->> 'level_number' AS number,
  sqft
FROM buildings
JOIN (
  SELECT building_id, level_number, sum(sqft) as sqft
  FROM v_rooms
  GROUP BY ( v_rooms.building_id, v_rooms.level_number )
) v_rooms ON ( v_rooms.building_id = buildings.id and v_rooms.level_number = level_number );

but I get duplicates, one row for the matching building_id and then again every time level number matches. which is an issue because level number is not unique to the table and only to the building.

FIDDLE


texts: MsMerge_genhistory has alot of rows with pubid = null

texts:
I have a merge replication and I am worried that the cleanup of metadata might not be enough. I have a retention period of 60 days and I can see thet the metadatacleanup-job do remove rows i msmege_genhistory that are older but only for rows that has the right guid in pubid. most of the rows, about 1,6 million, has the value NULL in pubid and I cannot figure out why.
does anybody know why there is so many null-values?


texts: How does IDENTITY_INSERT affect concurrency?

texts:
I am trying to help a customer with a 3rd party SAP add-on that has a posting glitch and has gone out-of-support.

Under certain circumstances, it archives and incomplete post from the posting queue table to the posting archive table. I need to move these archived results back into the queue.

The queue ID is an identity column and I would like to keep it the same.

The question is, if I do identity_insert on/insert/identity_insert off, what can I expect with respect to concurrency with processes that create the queue entries and expect the identity column to be generated automatically?

Any pointers on the best way to demonstrate such behavior would be greatly appreciated as well.


texts: Is it overkill to encrypt an already hashed BCrypt password?

texts:
I'm using BCrypt to hash my passwords on the server side.  Before I store it in my MySQL database, would it be overkill to encrypt my hashed-BCrypt password or would storing the hash directly in the database suffice?

This website advises to encrypt passwords after hashing them:

  As long as an attacker can use a hash to check whether a password
  guess is right or wrong, they can run a dictionary or brute-force
  attack on the hash. The next step is to add a secret key to the hash
  so that only someone who knows the key can use the hash to validate a
  password. This can be accomplished two ways. Either the hash can be
  encrypted using a cipher like AES, or the secret key can be included
  in the hash using a keyed hash algorithm like HMAC.

I'm coding in Java.  I'm trying to gauge whether the added layer of protection vs. speed performance of read & retrieval of passwords for user logins is worth it or not.


texts: End of file reached. Error 38 in SQL. Passes verification checks

texts:
Having issues restoring a database in SSMS, error 38.

The databases are transferred up to google via their Drive API.

Download it on my side and has error.

If I transfer it through teamviewer from client PC I still get the same error.

Only happening on two clients the rest are fine.

I have tried backing up with FORMAT options and WITH CHECKSUM. Both succeed and backup is taking when verifying.

I have ran restore fileheaders only and get proper data except the physicalname path has a lowercase 'c' for the drive on the problem databases.

I have read this could be a corrupt database but I don't understand how the checksum and verify sql functions pass if so. Any insight would really help.

This is the backup command used in C#

"BACKUP DATABASE " + database + " TO DISK = '"
+ database + "_" + dateTime.ToString("yyyy_MM_dd_HH") +
"_CloudBackup.bak' WITH NAME = 'database', INIT, CHECKSUM"

EDIT: Running dbcc check on the database produced no errors. I have tried updated the physical name of the .mdf and .ldf it does not work still. Taking a backup from SSMS produces a valid backup file. It has something to do with the services I wrote.

EDIT 2: I am restoring through SSMS GUI. I have also tried RESTORE DATABASE db_name FROM 'backup_path'

Commands

RESTORE VERIFYONLY FROM DISK = 'backup_path'

On both computers, mine and the server PC yields "The backup set on file 1 is valid."

RESTORE FILELISTONLY FROM DISK = 'backup_path'

Have only test on my PC. Returns paths to mdf and ldf, no errors.

Both PCs have sql server 2012 (SP1)


texts: Why NO_DATA_FOUND exception has a different behavior than others exceptions?

texts:
I have this PL/SQL function:

CREATE OR REPLACE FUNCTION Test_FunctionNoDataFound RETURN NUMERIC AS
    var NUMERIC;
BEGIN
    SELECT val INTO var FROM table1 WHERE id = -1; -- Throw NO_DATA_FOUND
    RETURN 1;
END;

And I want to run this SQL statement:

SELECT Test_FunctionNoDataFound FROM dual;

Even if Test_FunctionNoDataFound throw an exception, my statement runs normally and the function returns NULL value.
But now, if Test_FunctionNoDataFound is defined as:

CREATE OR REPLACE FUNCTION Test_FunctionNoDataFound RETURN NUMERIC AS
    var NUMERIC;
BEGIN
    SELECT val INTO var FROM table1 WHERE id > 0; -- Throw TOO_MANY_ROWS
    RETURN 1;
END;

If I run the previous SELECT statement, this time, I get an exception.

Why NO_DATA_FOUND exception has a different behavior than TOO_MANY_ROWS?
Is it the only exception with this kind of behavior?


texts: Data guard broker cascaded standby delay parameter is ignored

texts:
I have 3 databases: db01 primary, db02 standby cascading, db03 standby cascaded.

I have configured broker parameters by the following way:

DGMGRL> show configuration
Configuration – DB_HQ_DR
Protection Mode: MaxPerformance
Members:
db01 – Primary database
db02 – Physical standby database
db03- Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

DGMGRL> edit database ‘db01′ set property RedoRoutes='(LOCAL:db02)(db02:db03)’;
Property “redoroutes” updated

DGMGRL> edit database ‘db02′ set property RedoRoutes='(LOCAL:db01)(db01:db03)’;
Property “redoroutes” updated

--Configured delaymins parameter:

DGMGRL> edit database ‘db03′ set property DelayMins=’21600’;

When selecting log_archive_dest_2 parameter on db02 I see that there is delay parameter 21600 as it should be.

SQL> show parameter log_archive_dest_2

log_archive_dest_2   |  service="db03",  delay=21600 optional compression=disable  max_failure=0 max_connections=1 reopen=300

  db_unique_name="db03" net_timeout=30, valid
  _for=(standby_logfile,all_roles)

DGMGRL> show configuration
Configuration – DB_HQ_DR
Protection Mode: MaxPerformance
Members:
db01 – Primary database
db02 – Physical standby database
db03- Physical standby database (receiving archived redo)
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

Problem is that apply process is not delayed. It applied as soon as there is generated archive log.
What happens? Why delay parameter is ignored?


texts:
I have two machines, M1 and M2, through which I am connecting to MySQL Database on machine M3 using Perl script (DBI.pm).

So, here is the scenario that I am confused about:
M1 places a table level lock, and performs some operations. M2 also does the same. Table level locking mechanism is guarantying me that both of them will not be locking the table at the same time.

But, what if M1 has locked the table, and suddenly after performing half hte updates, the machine crashes or the Perl script process crashes, what happens to the lock then? My updates were performed mid-way, do those get rolled back? And what is the best way to deal with these kind of locks?


texts: How to dynamically add/remove role members from SSAS 2016 Tabular model

texts:
I am developing a number of tabular models, which currently have a limited number of users assigned to roles within each. At the moment, with the exception of 1 or 2 cases, the majority of the users are "full read" users in all the reports. I am in the process of implementing row-level security in to a number of these roles and I am getting to the point now where, before these models get deployed in to live I think I should be developing a security model to be able to easily add/remove members to/from these roles.

My question is, how can I deploy a model that allows for dynamically adding and removing users, without having to deploy the solution each time.

In my mind my options are as follows:

Do this manually... not dynamic!
Create user groups in AD and add the user groups to the roles. This will be ok for "full read" users (e.g. Directors), or "manager" roles (e.g. sales manager for sales reports), but would not work for users that will be using row-level security (e.g. salesperson).
Create a "global security" table to add to the model, which contains the 'variable' for read access permissions for "full read" access (e.g. Directors and Managers). I will also have another security table that will hold the data for row-level security for other users (e.g. salesperson filters). Then each role in the model will essentially use row-level security based on these security tables.
Use TMSL to process each deployment in the SSAS instance and "createOrReplace" each role deployment where this user exists/needs to be added... unfortunately I don't know any TMSL so I don't know how hard this is to learn?

I want to maintain a clean environment, so when somebody looks at the model in future they don't see users who left years back. Ultimately, when somebody leaves their AD account will be deactivated so this is less of an issue and more of an OCD. However, new members will need to be added.

I'm leaning towards option 3, but are the above options my only choices? Has anybody deployed a different model? Am I missing something obvious!?!?!? I hope not.

Thanks in advance for any help/tips/advice.

EDIT: I have also just thought of using PowerShell to add/remove users. Based on what David said, I could get one of my guys to maintain this instead.


texts: Is it possible to get SQL Server 2008 to download a file from a URL

texts:
I have a set of XML based services running on my network.

The data in these services needs to be mirrored into a Table on an SQL Server 2008 instance.

Getting the XML into a table is no problem I can already do that, but what I'm having to do at the moment is pass a huge long string of XML to a stored proc which then uses the OPENXML command to insert it into the table.

What I'm wanting to know, is instead of using a 3rd party program to get this data from the service then call the stored proc and insert it, is it in anyway possible for me to get SQL Server to just grab the XML directly from the service URL and process it like that.

All I can seem to find no matter how much I try is plenty of articles & posts on reading / writing files to / from the server file system and the several million or so on actually inserting the XML data, but I can't seem to find anything on getting the server to grab data directly from a URL.

Cheers

-----===== Update 25/8/2012 =====-----

After a little bit more research Iv'e found a third way to do this:

declare @xmlObject as int
declare @responseText as varchar(max)
declare @url as varchar(2048)

select @url = 'http://server/feed/data'

exec sp_OACreate 'MSXML2.XMLHTTP', @xmlObject OUT;
exec sp_OAMethod @xmlObject, 'open', NULL, 'get', @url, 'false'
exec sp_OAMethod @xmlObject, 'send'
exec sp_OAMethod @xmlObject, 'responsetext', @responseText OUTPUT
exec sp_OADestroy @xmlObject

select @responseText

exec sp_xml_preparedocument @idoc OUTPUT, @xmlData

Which seems to work, and work quite efficiently, however here's the weird thing.

If I use the full URL, which returns pure XML data with a mime type of 'text/xml' then response text contains nothing, it's null but if I strip it back to say 'http://server/feed/' or 'http://server/' so that the web server is just passing a 404 page or default html page, then I get the actual page content in.

At first I thought it might be the mime type,  'text/xml' vs 'text/html' but testing that made no difference, anything that returns valid XML seems to give null, but anything that returns broken XML seems to work!!

The code above however does work with correctly formatted XML from the internet, for example 'geonames' (which is what the original source of the above code was based on) works fine.

I suspect, that it's something to do with my feed server's config however, so need to do some work to resolve this, I thought I'd add the code here for others.

@Remus thank's for the suggestion, but that's how I currently do the task, I have a CLR binary that I wrote, that runs once a day to sync the feed to the DB, but it takes too long to sync the data.  Using it to feed the XML is faster than doing a pure loop however, but the size of the XML can be quite variable (esp given that a binary would pass it to the stored proc using L2S) and Iv'e already overflowed the input to the SP a couple of times because there's been too much data, hence why I'm looking to get the SP to retrieve the data itself.

@Mr Browstone - SSIS seems to be the way to go, that's what other suggestions have been, I do however admit I don't know an awful lot about using SSIS other than to use the (Import / Export wizard - I'm more of a dev than a DBA :-)  ) so any pointers on how I'd achieve this using SSIS would be helpful.

As for using CLR procs in the DB, I had thought of trying that, but Iv'e been bitten badly using these things before (And crashed a couple of servers too!!) so I'm a little wary of using them.


texts: Is the NOLOCK hint completely safe to use on a table that's guaranteed not to change?

texts:
If I have a table where the data is guaranteed to never change, is it completely safe to add the NOLOCK hint to my SELECT queries against that table?
If I have a table where some of the rows will be updated, but I'm only selecting rows that are guaranteed not to change, is this also a completely safe scenario to use the NOLOCK hint?


texts:
I want to create a function that will return a table as a result. Now, I have written quite a big SQL that works, but as I need to push dates trough that SQL to the beginning, I need a funtion that will do that. Now, as an example, to see if I can get it working, I've created this so far:

create or replace type test_row as object (date_id number,day date)

and

create or replace type test_table as table of test_row

and written this function:

create or replace FUNCTION test_function
(
  p_year_start_in integer,
     p_year_end_in integer
) RETURN test_table AS

      v_ret test_table;
      p_start date;
      p_end date;

BEGIN
    select td.date_id, td.day
    bulk collect into v_ret
    from T_D_DATES td
    where date_id between p_year_start_in and p_year_end_in;

  RETURN v_ret;
END test_function;

but the compiler throws an error. Specifically is says:

PL/SQL: SQL Statement ignored for the select td.date_id, td.day part and

PL/SQL: ORA-00947: not enough values on  from T_D_DATES td

but I am not completely sure how to fix it. I've tried to make a PIPELINED
 function but got lost on how to insert all my values from my select into the table type.

Funtions aren't really my forte and especially ones that return a table as a result. So, how can I write a function that would work with the sample select?

P.S: I've looked online for solutions but it's either a super simple loop that writes some hardcoded values or something really complex and I can't decipher it.


texts:
I have a testdb. the recovery mode is on simple and I am back my system up with veeam. Now how can i check if my log from the testdb was being truncated after the backup or not?

I just found some commands like DBCC SQLPERF(LOGSPACE). But with that i just see the usedspace.


texts: Ola Hallengren's IndexOptimize for Updating Statistics exclude schema

texts: I am using Ola Hallengren's IndexOptimize for Updating Statistics only. Can I exclude a schema from the UpdateStatistics?


texts: Resolving sp_Blitz Finding: Many Plans for One Query

texts:
Why Multiple Plans for One Query Are Bad

So I was running sp_Blitz to get a handle on some systems.  Got some code to get cleaned up as usual.  Some heaps that should have been clustered indexes.  etc.

This particular query was using literals, seemed to be resulting in a lot of plans.  Got the indexing and stuff sorted out on the tables in this query, and even got the query parameterized for the latest code/database to go into production.

But still the query is showing as a parameterization issue.  (DBA helpfully turned the plan cache query into an SSRS report so I can just run through them quickly on the PROD environment from my browser).

Where to go from here?  Ignore it?  (Seems like a lot of plan counts)

Use forced parameterization?  (But the one that is explicitly parameterized is showing up).

Ugh - I see the developer didn't take my advice that the LEFT JOIN was being turned into an INNER JOIN...  I'll have to get that fixed...

Without forced parameterization, will all these literal-style queries never share the same plan?  Is this query too complicated for simple parameterization?


texts:
I have found a query that is a major performance issue in my environment. I looked at the actual execution plan for this query and found out the main problem is the remote scan (98%). Its remote because SQL Server is accessing its system objects (DMVs).

If you look at the estimated number of rows it says 33, but the actual number of rows is over 16 000.

If it was a regular table query I would look at the statistics for the columns but in this case I don't know what I can do.

DECLARE @Duration FLOAT
DECLARE @Date DATETIME
DECLARE @MinDuration FLOAT
SET @MinDuration = 5
SET @Duration = null

SELECT TOP 1 @Duration = wt.wait_duration_ms / 1000.0
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
order by wt.wait_duration_ms desc

IF (@Duration IS NOT NULL and @Duration > @MinDuration )
BEGIN
    SET @Date = GETDATE()
    INSERT INTO Log_Locks
        SELECT
        db.name DBName,
        tl.request_session_id,
        wt.blocking_session_id,
        OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
        tl.resource_type,
        h1.TEXT AS RequestingText,
        h2.TEXT AS BlockingTest,
        tl.request_mode,
        wt.wait_duration_ms,
        @Date,
        es.host_name BlockedHostName,
        ec2.client_net_address BlockedClientNetAddress
        FROM sys.dm_tran_locks AS tl
        INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
        INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
        INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
        INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
        INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
        left join sys.dm_exec_sessions es ON es.session_id = wt.blocking_session_id
        CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
        CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
END

Do you have any ideas on how to optimize it?


texts: Online clustered index rebuild using more log space than expected

texts:
I have a table TableA

ID PK BIGINT
TrxID BIGINT
CardBalance INT
AccountBalance INT

SP_SPACEUSED 'TableA' output:

name    rows       reserved     data         index_size   unused
TableA  904054184  55547120 KB  34661408 KB  19963544 KB  922168 KB

So the data size is about 33 GB. There is a clustered index on TrxID. There is one nonclustered index, which comes to about 19GB.

My transaction log file size is about 250 GB. DBCC SQLPERF('logspace') shows < 1% usage.

When I run:

ALTER INDEX CIX_TableA_TrxID ON dbo.TableA REBUILD WITH (ONLINE = ON)

The transaction log hits 100% after about 20 minutes and then continues to grow.

Is there a reliable way to calculate how much log space is required for this operation?

Also this seems like a lot of space to use in the log file, does this sound right?

This database is in an Availability Group (so using the full recovery model) with 1 other asynchronous replica.
This is a test database, so there aren't any other transactions running at the same time as the the reindex.


texts: Database structure for users, events and event lists

texts:
I am creating a web service for managing events. The service will have users, who are listed in one database, called Users. It will also have events, which are listed in another database, called Events.

Each event will have a list of invited users, which is variable in length. Each invited user has various properties, including if they accepted the invitation, and if they attended.

I plan to use SQL databases; either SQLite, MySQL or PostgreSQL. However, this could be changed if that was advised.

My question is how should I store the invited user info for each event? The requirements are:

I can look up the users invited to an event and the events a user is invited to equally easily.
The solution is scalable, i.e. if I end up having millions of users and millions of events then the approach can cope with that. Note that each event will only ever have a small number of guests e.g. 2-1000.
The solution is efficient, in access time, computational resources and storage requirements.

Some options I have thought of:

For each event guest list, store the user IDs and other properties as a blob, or a blob per property, in the Events database. The issue here is that the number of guests varies. Fixing the length would mean wasting space or limiting the max guest length size. Also, this approach doesn't allow a fast look up of events a user is invited to.
For each user, store the list of events they've been invited to, plus the other properties, as a blob in the Users database. Again, the issues are that the number of events varies, and this approach doesn't allow a fast look up of users invited to an event.
Combination of 1 and 2. This introduces redundancy, and the possibility of errors occurring when keeping the two databases in sync.
Separate SQLite database for each event user list. I suspect this will be very inefficient both storagewise and computationally. Also it doesn't allow the events a user is invited to to be computed easily.
Another database which stores user-event items, one for each user invited to each event. This must allow fast filtering by user or event. I'm new to databases so don't know if this can be achieved in an SQL database, or requires another type, and if so, what.

Comments on these options, and any further suggestions, are gratefully received.


texts: Is tuning the innodb_buffer_pool_size important on Solaris ZFS?

texts:
We're running a moderate size (350GB) database with some fairly large tables (a few hundred million rows, 50GB) on a reasonably large server (2 x quad-core Xeons, 24GB RAM, 2.5" 10k disks in RAID10), and are getting some pretty slow inserts (e.g. simple insert of a single row taking 90 seconds!).

Our innodb_buffer_pool_size is set to 400MB, which would normally be way too low for this kind of setup. However, our hosting provider advises that this is irrelevant when running on ZFS. Is he right?


texts: Disaster recovery plan - Having multiple servers

texts:
I work in an organization where departments are in different buildings. I have a data centre in the main building. All the in-house applications connect to the SQL Server residing in the server in the main building datacenter.

Sometimes during weekends, if the network fails or the software throws a bug, it is very hard to come back to office and fix the problem.
It was suggested that a local machine be placed in one building (other than the datacenter) and install SQL Server on it. Have this SQL Server store the data first, and then replicate it on main datacenter every few minutes.

This will help in case the network gets disconnected with the main datacenter.

I would like to know if this the only solution we can adopt? What problems would we face with this strategy?


texts: Which TCP port to choose for SQL Server to listen to for security reasons?

texts:
I know that the default TCP port for MS SQL Server is 1433 and it is better to change that for some security reasons. But I want to know which port is better to choose instead of 1433 for SQL Server in order to work properly and not to intervene with the work of other programs? What if I choose a port which is already used by another program?

Thanks for your time!


texts: SQL Server equivalent of Oracle USING INDEX clause

texts:
Is there a SQL Server 2008 equivalent of the USING INDEX clause in Oracle?
Specifically for the construct:
CREATE TABLE c(c1 INT, c2 INT);
CREATE INDEX ci ON c (c1, c2);
ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1) USING INDEX ci;

In the Sql Server Documentation on Unique Indexes it states (emphasis added):

Unique indexes are implemented in the following ways:
PRIMARY KEY or UNIQUE constraint
When you create a PRIMARY KEY constraint, a unique clustered index on the column or columns is automatically created if a clustered index on the table does not already exist and you do not specify a unique nonclustered index. The primary key column cannot allow NULL values.

Which seems to imply that there is a way of specifying what index should be used for a Primary Key.


texts: Prod environment uses a different SSIS version to Dev when running a legacy SSIS package

texts:
I have a dev environment with SQL Server 2008 R2 and SSIS (10.0) installed.

I have a number of SSIS packages built with SQL Server 2008 R2 BIDS and these have been deployed to a Prod server running SQL Server 2017 and SSIS (14.0). The legacy SSIS packages run fine on Prod's SQL Server 2017.

I recently installed SQL Server 2017 & SSIS onto my Dev environment. The plan being to upgrade the SSIS packages in the future. Therefore in my dev environment, I have SQL Server 2008 R2 with SSIS 10.0 and also SQL Server 2017 with SSIS 14.0. Everything seems to co-exist fine.

The reason for my question is that in my dev environment, when I run the 2008 R2 SSIS packages from anywhere (BIDS / SQL Server 2008 R2 / SQL Server 2017) it requires the 'SQL Server Integration Services 10.0' Windows service to be running. If I run the same package on the Prod server which only has SQL Server 2017 and SSIS 14.0 installed it requires the 'SQL Server Integration Services 14.0' Windows service to be running.

Why in Dev is it falling back to use the older SSIS 10.0 Windows service, even when launched from SQL Server 2017? The package is deployed in an identical way onto the Dev SQL Server 2017 and prod SQL Server 2017, yet the Dev install uses SSIS 10.0 and Prod uses 14.0. Is this to be expected? I don't have a huge issue with this, but with Dev using a different SSIS runtime to Prod, I am not guaranteeing a similar SSIS environment for testing.

Is this behaviour to be expected?

Many thanks in advance.


texts:
I am looking for SQL Server TOP (X) Analog in MySQL

In SQL Server:

SELECT
  TOP 5
  Z
...
FROM
ORDER BY Z DESC

How can this be done in MySQL?

ADDED:

I have found for other RDBMS: Returning TOP N Records


texts: How to drop database that I can't get out of single user mode?

texts:
I've got a database in single user mode that I can't get out of single user mode. I've tried resetting the server, and I've even tried resetting my computer but with every attempt I make to do anything to it SQL Server insists that there is some connection already open to it somewhere.

I don't really understand how it's possible for there to be a connection open to it after I've just reset my computer and have not opened a connection to it.  I am using SSMS and I made a connection to the server using the object explorer after which I tried to delete the database from there directly by using the delete key (i.e. I don't have any query tabs open so definitely no connections there).

Unfortunately, the application I'm using doesn't allow me to specify a database name which means I've got to figure out how to get rid of this stupid database so I can restore another one.

I've been googling and trying different things but nothing seems to work... even stuff that supposedly should work in this situation.

So here I am looking for any more ideas???

EDIT:

My latest attempt has been to use SP_WHO to find which process is connected and then KILL it.  But every time I KILL it, it comes back under another number immediately.  Don't understand what in the world could possibly be causing such behavior.

EDIT2:

I just tried select * from master..sysprocesses where spid = <spid> to see the kpid which, if I understand correctly, correlates to the PID column in details tab on the task manager.  Only problem is that the value is 0, which would mean it's the system idle process.  Maybe I'm misunderstanding something here though.


texts:
I trying to understand page splits in SQL Server, reading WHAT IS A PAGE SPLIT? WHAT HAPPENS? WHY DOES IT HAPPEN? WHY WORRY? by Tony Rogerson

CREATE TABLE mytest
  (
     something_to_see_in_data CHAR(5) NOT NULL CONSTRAINT pk_mytest PRIMARY KEY CLUSTERED,
     filler                   VARCHAR(3000) NOT NULL
  )

go

insert mytest ( something_to_see_in_data, filler ) values( '00001', replicate( 'A', 3000 ) )
insert mytest ( something_to_see_in_data, filler ) values( '00002', replicate( 'B', 1000 ) )
insert mytest ( something_to_see_in_data, filler ) values( '00003', replicate( 'C', 3000 ) )
go

To check the pages of my table:

DBCC IND ( 0, 'mytest', 1);

+---------+---------+--------+--------+------------+---------+-----------------+-------------------+----------------+----------+------------+
| PageFID | PagePID | IAMFID | IAMPID |  ObjectID  | IndexID | PartitionNumber |    PartitionID    | iam_chain_type | PageType | IndexLevel |
+---------+---------+--------+--------+------------+---------+-----------------+-------------------+----------------+----------+------------+
|       1 |    3520 | NULL   | NULL   | 2065259704 |       1 |               1 | 72057595357560832 | In-row data    |       10 | NULL       |
|       1 |    3519 | 1      | 3520   | 2065259704 |       1 |               1 | 72057595357560832 | In-row data    |        1 | 0          |
+---------+---------+--------+--------+------------+---------+-----------------+-------------------+----------------+----------+------------+

To check the page details of data page:

dbcc traceon( 3604 )

go

DBCC page( 0, 1, 3519, 1 ) with tableresults

Offset:

Slot 0, Offset 0x60, Length 3016, DumpStyle BYTE
Slot 1, Offset 0xc28, Length 1016, DumpStyle BYTE
Slot 2, Offset 0x1020, Length 3016, DumpStyle BYTE

Updating one of the records, so that it will not fit into the current page, and page split will happen (i.e.) new page will be created?

update mytest
    set filler = replicate( 'B', 3000 )
where something_to_see_in_data = '00002'

Now checking the pages again:

DBCC IND ( 0, 'mytest', 1);

+---------+---------+--------+--------+------------+---------+-----------------+-------------------+----------------+----------+------------+
| PageFID | PagePID | IAMFID | IAMPID |  ObjectID  | IndexID | PartitionNumber |    PartitionID    | iam_chain_type | PageType | IndexLevel |
+---------+---------+--------+--------+------------+---------+-----------------+-------------------+----------------+----------+------------+
|       1 |    3520 | NULL   | NULL   | 2065259704 |       1 |               1 | 72057595357560832 | In-row data    |       10 | NULL       |
|       1 |    3519 | 1      | 3520   | 2065259704 |       1 |               1 | 72057595357560832 | In-row data    |        1 | 0          |
|       1 |    3521 | 1      | 3520   | 2065259704 |       1 |               1 | 72057595357560832 | In-row data    |        2 | 1          |
|       1 |    3522 | 1      | 3520   | 2065259704 |       1 |               1 | 72057595357560832 | In-row data    |        1 | 0          |
+---------+---------+--------+--------+------------+---------+-----------------+-------------------+----------------+----------+------------+

As we can see, two new pages got created:

3521 -- Index page
3522 -- Data page

I can understand the reason behind creation of new Data page(3522) because my data is more than 8kb so new page got created.

What is the use of the Index page and when will it will be created? I did lot of research in Google there is no proper documentation on Index page. Is it for maintaining B-Tree?


texts: Unable to connect to SQL Server 2016 remotely

texts:
I am trying to access SQL Server 2016 from outside of my network but each time I try I get this error message.

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (.Net SqlClient Data Provider)

The ports on my managed firewall are open, and I have also turned off Windows Firewall too in the event that this was the blocker but I am still getting the issue.

I have checked the Configuration Manager and its listening on port 1433, however, when I look at netstat -an I dont see this port is being listened on so suspect this may be where the issue exists.

A question I have is if configuration manager says its listening on this port, why is it not showing in netstat?

Also, what else can I try in order to gain access.

Thanks


texts: What are current values in PG admin configuration editor for postgresql.conf

texts:
I am tuning a postgresql.conf file and here is what is shown in PG admin configuration editor

What does the current value corresponds to?
For the shared buffers (red line), what is the value there?


texts: PostgreSQL: Displaying inheritance relationship?

texts:
I want to know if a certain PostgreSQL table is a part of any inheritance relationship (i.e. is a parent or a child of other table(s)).

Is there a query for that? Where is the inheritance data stored?


texts: Inserting data to a table which isn't present in another table

texts:
I am having the below portion of the database to deal with. I am making an attendance system. I have to insert the record of students who haven't marked their attendance. If a student marks his/her attendance, their record will be saved in Attendance with status as 1.
I've to make sure that when a button is pressed at the end, the students who are in Registered_Course table against a specific teacher_id from Teacher table, batch_id from the Batch table, and course_id from Course table, they should be marked as absent in the Attendance table by inserting their record.
I've tried using Left Join to get the reg_number from Registered_Course but it doesn't select the expected values. Maybe I am doing something wrong there.
here is my query without join:

SELECT DISTINCT(reg_number) from Registered_Course R, Attendance A
where
R.id!=A.registered_course_id
and R.teacher_id=1 AND R.course_id='CSC112'

I am unable to get how should I deal with this. Any help would be really appreciated. Thanks


texts: How to define the table structure for measured data in sqlite

texts:
I have an embedded system with an sqlite database.
How should I define the table structure for the measured data from 4 sensors?

Each sensor give us every second an numeric measured value.
The measured value should be stored with an timestamp in the database.

Criteria are:

Table are "fill only". There are no delete and update operations.
INSERT (timestamp, value) should be fast.
SELECT (timestamp, value) for an specific time range should be fast.

First Approach: One table for all sensors

CREATE TABLE Measured_Values (
    id        INTEGER  PRIMARY KEY AUTOINCREMENT,
    sensor_id INTEGER  NOT NULL,
    timestamp DATETIME NOT NULL,
    value     REAL,
    FOREIGN KEY (sensor_id) REFERENCES Sensor_List(id))

with

CREATE TABLE Sensor_List(
    id   INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT)

Second Approach: One table per sensor

CREATE TABLE Measured_Values_Sensor_ONE (
    id        INTEGER  PRIMARY KEY AUTOINCREMENT,
    timestamp DATETIME NOT NULL,
    value     REAL)

CREATE TABLE Measured_Values_Sensor_TWO (
    id        INTEGER  PRIMARY KEY AUTOINCREMENT,
    timestamp DATETIME NOT NULL,
    value     REAL)

and so on.

OR is there another better design?

Which design is usually used?
Which design gives me small insert time but also fast query time?


texts: Fragmentation of indexes with small a database

texts:
I'm trying to figure out whether its worth defragging a 2GB mssql database.
indexs are above 70% fragmented but queries don't appear to be talking too along.

That being said I am having a delay when requesting to play multiple voice files and havent figured out yet if this because of a delay when our software requests the path of the .wav from the Database, or if its down to some other factor


texts:
I am using cx_Oracle with python to select a dataset of about 200,000 rows.

I am having memory issues with my application, so I am memory profiling. The memory use for these lines is over 1G, however, the actual data returned into list (I used pickle to get the size), was only about 100MB.

What am I missing here? I would expect it to be 100MB or at most 200MB if I am copying over the data from memory when I create the list.
Thanks.

cx = cx_Oracle.connect(x,y,z)
c = cx.cursor()
l=[]
c.arraysize=1000
results = c.execute(query)
for row in results:
    l.push(row)


texts:
We can achieve Always Encrypted on Windows Server Machine using SSMS on my Application Server and if Database Server too want to view the encrypted column, we can always transfer the certificate from my application server to the database server.
But how to achieve the same, if database server is SQL Server on Linux? How to transfer the certificate?
Or another case both, database and application server are on Linux, how to configure Always Encrypted?


texts: Index Reorganize During Database Full Backup

texts:
Index maintenance usually starts 3 hours before the database full backup and ends before the full backup start.

The database is using the Simple Recovery model.

Last weekend the index maintenance took more than 5 hours and the full backup was running at the same time.

We have noticed that since the full backup started the transaction log File started to grow every 30 Seconds until it filled the disk and both full backup and maintenance failed due to insufficient disk space for log file.

Why, when the backup has started while index reorganize is running, does this happen to the transaction log file?


texts:
In the answer to How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?

I see the line \connect hostdb why and when do I need this line?

I'm executing role creations SQL line from a .sql file


texts: MySQL vs SQL Server Express (Compare Free DBMS)

texts:
I am searching for a comparison table between MySQL and SQL Server Express.

Comparisons should include...

Usage limit
Tools (management/dev/backup/monitoring/...)
"simultaneous connection"

It's for my company, I need to know what should I install.

It's for migrating from access db

if you have opinion, suggestions,....

Thank you


texts: How to use the dbms_sql to get the analyze for insert into statement

texts:
declare
 v_cursor number;
 stmt varchar2(2000);
 s varchar2(40);
 cursor cc1 is select voc.SQL_ID from v$open_cursor voc left join v$sql vs on voc.SQL_ID=vs.SQL_ID  where voc.SID in(select sid from v$mystat) and to_char(substr(vs.SQL_FULLTEXT,1,2000)=stmt;
begin
delete from table1;
v_cursor:=dbms_sql.open_cursor;
stmt:='inset into table2 (column1,column2) values(''a'',''b'')';
dbms_sql.parse(v_cursor,stmt,dbms_sql.native);
insert into table1 select * from tables(dbms_xplan.display
open cc1;
loop
fetch cc1 into s;
exit;
end loop;
close cc1;
insert into table1 select * from(dbms_xplan.display_cursor(s,0,'ALL'));

I execute this and select  from the table1 but the record in table1 is tells me:

  it could also be that the plan is no longer in cursor cache(check v$sql_plan).

But if I change the stmt to update statement and execute, the record in table1 is correct.
So i want to know where can I get the analyze about the insert statement.


texts: How to sync two MySQL high load tables with 10M rows?

texts:
I have two identical MySQL innodb table with 10M rows each. One is used at backend while other at application end. Thus, either of them loaded. I need to synchronize them in a way which is fast and non blocking. How can I do that?

Is there any particular LINUX software for this?

And how about syncing two dbs?

EDIT

Both tables exist on different dbs hosted on different machines. And both of them are updated separately before the sync process.


texts: SQL Query to find the location of the running query?

texts:
I am trying to get the information about the running queries for query performance monitoring purpose.

Is there a way to get the location of the running query in SQL-server?
For instance, if I have a stored procedure running in a server and if I'm getting all the activity monitoring data for the running query, I want to fetch the path of the stored procedure in which the query is running.

Is there a query to do this or any other way to get this path?

Example Path/hierarchy : sever/db/stored_procedure_name/query


texts: AttributeError: 'NoneType' object has no attribute 'value' - pgAdmin4

texts:
I Ctrl+C'd out of the initial pgAdmin4 setup aka. python pgAdmin4.py and now if I try to rerun the install, I get a mountain of errors. I'm using pgAdmin3 for now but I would like to know how to fix this. The full traceback is below:

Traceback (most recent call last):
File "pgAdmin4.py", line 37, in <module>
    app = create_app()
File "/home/sean/.virtualenvs/pgadmin/lib/python2.7/site-packages/pgadmin4/pgadmin/__init__.py", line 244, in create_app
    db_upgrade(app)
File "/home/sean/.virtualenvs/pgadmin/lib/python2.7/site-packages/pgadmin4/pgadmin/setup/db_upgrade.py", line 25, in db_upgrade
    flask_migrate.upgrade(migration_folder)
File "/home/sean/.virtualenvs/pgadmin/local/lib/python2.7/site-packages/flask_migrate/__init__.py", line 244, in upgrade
    command.upgrade(config, revision, sql=sql, tag=tag)
File "/home/sean/.virtualenvs/pgadmin/local/lib/python2.7/site-packages/alembic/command.py", line 254, in upgrade
    script.run_env()
File "/home/sean/.virtualenvs/pgadmin/local/lib/python2.7/site-packages/alembic/script/base.py", line 421, in run_env
    util.load_python_file(self.dir, 'env.py')
File "/home/sean/.virtualenvs/pgadmin/local/lib/python2.7/site-packages/alembic/util/pyfiles.py", line 93, in load_python_file
    module = load_module_py(module_id, path)
File "/home/sean/.virtualenvs/pgadmin/local/lib/python2.7/site-packages/alembic/util/compat.py", line 75, in load_module_py
    mod = imp.load_source(module_id, path, fp)
File "/home/sean/.virtualenvs/pgadmin/lib/python2.7/site-packages/pgadmin4/pgadmin/setup/../../migrations/env.py", line 94, in <module>
    run_migrations_online()
File "/home/sean/.virtualenvs/pgadmin/lib/python2.7/site-packages/pgadmin4/pgadmin/setup/../../migrations/env.py", line 87, in run_migrations_online
    context.run_migrations()
File "<string>", line 8, in run_migrations
File "/home/sean/.virtualenvs/pgadmin/local/lib/python2.7/site-packages/alembic/runtime/environment.py", line 817, in run_migrations
    self.get_context().run_migrations(**kw)
File "/home/sean/.virtualenvs/pgadmin/local/lib/python2.7/site-packages/alembic/runtime/migration.py", line 329, in run_migrations
    step.migration_fn(**kw)
File "/home/sean/.virtualenvs/pgadmin/lib/python2.7/site-packages/pgadmin4/migrations/versions/fdc58d9bd449_.py", line 34, in upgrade
    if get_version() != -1:
File "/home/sean/.virtualenvs/pgadmin/lib/python2.7/site-packages/pgadmin4/pgadmin/setup/db_version.py", line 21, in get_version
    if int(version.value) > int(config.SETTINGS_SCHEMA_VERSION):
AttributeError: 'NoneType' object has no attribute 'value'


texts: Why are so many MPP solutions based on PostgreSQL instead of MySQL?

texts: Astor Data, Greenplum and GridSQL all allow Massive Parallel Processing of SQL queries. They are also all built around PostgreSQL technology. Is this just because of licensing issues or are there other reasons? To me, it seems like the MyISAM, not being ACID complient and therefore not running into the same issues with MVCC (like seen here) as PostgreSQL is far better suited for building high-performance data warehouses. After all OLAP load does not require transactions as far as I can see.


texts: SSMS 2016 takes forever to bring up context menu for an index

texts:
Go to a random table in the Object Explorer
Expand it down to indexes
Right-click on an index - it takes around 10 seconds to come up

If I attempt to create a DROP & CREATE INDEX script off the context menu, it takes  a similar amount of time.

I've looked at the Profiler trace, and it has about 4 pages of largely repeating queries.  If I look at the Performance/Ethernet tab of the Task Manager, i see that it is bringing information at a rate of 800kb-1mb and sending at about 200kb or so.

Is this a bug?  Feature?  Am I missing some simple setting?

Version of SSMS: 13.0.15900.1.
Updated SSMS to 13.0.16000.28 with no effect.
Version of SQL Server: 2016 with cumulative update 2.
I had SQL Prompt 7 installed.  Uninstalled it, but nothing changed.
SELECT COUNT(*) FROM sys.indexes; returns 1979.
SELECT COUNT(*) FROM sys.partitions; returns 1973.

I have the box all to myself, so there is no contention.  Plus this box is the fastest, best spec'd box I've ever worked on.  Everything is snappy on it except this specific operation.


texts: Would SQL Replication prove to be a FASTER option than downloading compressed DB files every time from servers at different geographic locations?

texts:
My requirement of synchronising two DB servers in different geographic locations is as follows

synchronise some DBs on 2 servers in regular intervals (manual). One in US is an intermediate release server and we need those updated DBs on our local server every time a new build is deployed.
DBs are huge (not very huge though, more or less 30 GB)
Minimum possible action/disturbance on Source server while synchronising

Currently IT team here downloads the compressed database files from server at US to local server to get the latest updated DBs, which takes huge time for SOME of the DBs.

My question is: Would SQL Replication prove to be a FASTER option in this case?

DBs that take much time have only master look up tables but with huge data, which are updated less frequently. But we need to include these DBs as well, so as to keep the process clean and synchronised.

It seems, I cannot use Transactional Replication as not all tables have primary key here. Initially I thought,once replication is set-up, transactional replication anyways runs snapshot first (which would be a one time activity, so its okay even if it takes time) and replicating only the transactions later would not take much time. Hence, it would save heavy downloads every time.

But, if Snapshot replication is the only option left, I am wondering would it even save time over downloading MDFs,as what I understood till now is that snapshot replication will create a complete snapshot of DB everytime and apply them in batches.

I am not a DBA but a developer, and I was thinking by any chance if I can do something to speed-up current process. First thing I could think of, was replication. I have set-up a snapshot replication for a 30 GB DB which has replicated only 1.5 Gb of data in 2 hours. Please suggest.


texts: Can Postgresql exploit "accidental" clustering in plans?

texts:
Suppose I have an append-only table with columns customer_id (randomly generated string) and x, and lookups are always done on customer_id.
Say the data look like below, as if we get a batch of rows when a customer signs up for something initially, and then never again for that customer.
customer_id=XCVFY0001, x=...
customer_id=XCVFY0001, x=...
(continues for ~1 page with same customer_id)
customer_id=HUMBN0001, x=...
customer_id=HUMBN0001, x=...
(continues for ~1 page with same customer_id)
(and so on...)

So while customer_id's alphabetical ordering isn't correlated with physical rows, we could make statements like:

there are few distinct customer IDs per page
there are few pages per ID
there are long "runs" of IDs, or, if you need one customer_id, you're likely to find it on a few contiguous pages
in terms of information theory, I think they'd say there's no correlation, but there is high "mutual information"

Can the query planner use information like this in estimates, if one doesn't explicitly run CLUSTER? I'd assume that if there's low correlation as reported in pg_stats, it would guess that rows are uniformly distributed throughout pages, and might be pessimistic with various plans.
(In my real-world analogue, a plain non-clustered index made things nice and fast anyways, but I just got curious when I noticed the pattern in the data.)


texts:
How do I generate table structure from a view, in one script? Will only need data types, I am seeking a Table Create Script with some type of dynamic sql

(1) can remove primary key/constraint,

(2) don't care if null/not null added (would be nice to have however)

I have proposed sample question/answer below, feel free to code review, edit/optimize for other data types not included in table in future, special cases/issues/exceptions may have not accounted for. Is there any open source code for this by the way?

Table 1 and 2:

create table dbo.Customer
(
     CustomerId int primary key,
     CustomerName varchar(255),
     ZipCode varchar(9)
)
create table dbo.CustomerTransaction
(
     CustomerTransactionId int primary key identity(1,1),
     CustomerId int,
     SalesAmount numeric (10,2),
     PurchaseDate datetime
)

View:

create view dbo.CustomerTransactionVw
as
select
     ct.CustomerTransactionId,
     ct.SalesAmount,
     ct.PurchaseDate,
     cust.CustomerId,
     CustomerName,
     cust.ZipCode
from dbo.CustomerTransaction ct
inner join dbo.Customer cust
  on cust.CustomerId = ct.CustomerId

Intended Table Create Script:

create table dbo.CustomerTransactionBigTable
(
     CustomerTransactionId int identity(1,1),
     CustomerId int,
     SalesAmount numeric (10,2),
     PurchaseDate datetime,
     CustomerId int,
     CustomerName varchar(255),
     ZipCode varchar(9)
)

Current Proposed Solution:

declare @TableCode varchar(max) = 'create table dbo.CustomerLargeTable
( ' +
    (select STUFF((
    SELECT ',
    '
    + c.name + ' ' +
case
    when t.name like '%char%' then t.name + '(' + cast(c.max_length as varchar(10)) + ')'
    when t.name like '%numeric%' or t.name like '%decimal%' then t.name + '(' + cast(c.precision as varchar(10)) + ',' + cast(c.scale as varchar(10)) + ')'
    else t.name
end
FROM .sys.columns c
inner JOIN sys.types t
    on t.user_type_id = c.user_type_id
    and t.system_type_id = c.system_type_id
where c.object_id = object_id('CustomerTransactionVw') and is_identity = 0
FOR XML PATH(''), TYPE).value('.','nvarchar(max)'),1,2,''))
+ '
)'

print @TableCode

Note: I am only interested in table structure DDL, not data DML

https://stackoverflow.com/questions/7254380/how-to-find-the-derived-column-types-of-a-view-in-sql-server-2005


texts:
Ok, so it's kind if difficult for me as a non-native english speaker to get this into writing, so please bear with me.

I have a source-Table as following:

CREATE TABLE [dbo].[Mailbox](
[ObjectSID] [nvarchar](184) NULL, --ObjectSID of Mailbox
[Database] [nvarchar](64) NULL, --Exchange Database
[PrimarySMTP] [nvarchar](254) NULL, -- eMailAddress
[ItemCount] [int] NULL, -- SUM of eMails
[Itemsize_MB] [int] NULL, -- size of Mails
[Itemsize_del_MB] [int] NULL, -- size of deleted mails
[Arch_Database] [nvarchar](64) NULL, -- Name of the Archive Exchange Database
[Arch_ItemCount] [int] NULL, -- Sum of all archived mails
[Arch_Itemsize_MB] [int] NULL, -- Size of archived mails
[Arch_Itemsize_del_MB] [int] NULL,  --Size of deleted archived mails
[ScanTime] [date] NULL  --Date of the last SCAN
) ON [PRIMARY]
GO

As you can Imagine from the Name, Ill write Exchange-Information into this Database for every Mailbox in our Company, every month.
SAMPLE-DATA

Insert into Mailbox Values
 (111,N'Database1',N'Sample.User1@domain.com',63913,16535,1,N'ARCH1',0,0,0,'2018-10-22')
,(111,N'Database1',N'Sample.User1@domain.com',63958,16540,2,N'ARCH1',0,0,0,'2018-10-24')
,(111,N'Database1',N'Sample.User1@domain.com',64533,16664,2,N'ARCH1',0,0,0,'2018-11-19')
,(222,N'Database2',N'Sample.User2@domain.com',296,11,0,N'ARCH2',39139,10867,0,'2018-11-19')
,(222,N'Database2',N'Sample.User2@domain.com',296,11,0,N'ARCH2',39139,10867,0,'2018-10-24')
,(222,N'Database2',N'Sample.User2@domain.com',296,11,0,N'ARCH2',39139,10867,0,'2018-10-22')
,(333,N'Database1',N'Sample.User3@domain.com',55292,12723,23,N'ARCH1',37302,7128,0,'2018-10-22')
,(333,N'Database1',N'Sample.User3@domain.com',55532,12855,25,N'ARCH1',37306,7128,0,'2018-10-24')

My query results in 1 line per Scan (ScanTime) per Mailbox (ObjectSID)

WITH
MBBB (  ObjectSID
,Itemsize_MB
,Itemsize_del_MB
,Arch_Itemsize_MB
,Arch_Itemsize_del_MB
,ScanTime
,ROW
,[Database])
AS (SELECT  ObjectSID
,Itemsize_MB
,Itemsize_del_MB
,Arch_Itemsize_MB
,Arch_Itemsize_del_MB
,Scantime
,ROW_NUMBER() OVER(PARTITION BY ObjectSID ORDER BY ScanTime) ROW
,[Database]
FROM Mailbox),
Growth( [Database]
,ObjectSID
,Itemsize_MB
,Itemsize_del_MB
,Arch_Itemsize_MB
,Arch_Itemsize_del_MB
,ScanTime
,Growth)
AS (select S.[Database]
,S.ObjectSID
,S.Itemsize_MB
,S.Itemsize_del_MB
,S.Arch_Itemsize_MB
,S.Arch_Itemsize_del_MB
,S.ScanTime
,ISNULL((S.Itemsize_MB+S.Itemsize_del_MB+S.Arch_Itemsize_MB+S.Arch_Itemsize_del_MB),0)-ISNULL((X.Itemsize_MB+X.Itemsize_del_MB+X.Arch_Itemsize_MB+X.Arch_Itemsize_del_MB),0) Growth
FROM MBBB S
LEFT JOIN MBBB X
ON S.ObjectSID=X.ObjectSID
AND S.Row=X.Row+1
where s.ROW  >= (select MAX(s.ROW)-3
from MBBB s))

select
g.[Database]
,g.ObjectSID
,SUM(g.Itemsize_MB + g.Itemsize_del_MB + g.Arch_Itemsize_MB + g.Arch_Itemsize_del_MB) as [Mailbox in MB]
,g.Growth
,g.ScanTime
from Growth g
Group By g.ObjectSID, g.[Database], g.ScanTime ,g.Growth
order by g.[Database]

My Problem:
If a Mailbox (ObjectSID) is deleted, the new Size is 0. Therefore the Growth should be negative last size.
i.e  Mailbox of User A is 12GB in size on July. The Mailbox get's deleted, and is not listed in August (since it has been deleted) I want a Growth of -12GB (negative 12GB)
BUT, since the Mailbox is deleted, it is not showing as a new row_Number(Since Mailbox = NULL == No new ROW_Number() )
The Problem comes now with grouping in SSRS:
If I group all Mailboxes per Database, I see that for a Database If have a positive growth, but the SUM of all Mailbox-Sizes is decreasing.
i.e
Database__Old Size___Growth__New Size
Database1 __ 10GB __ _4GB____9GB
I need some kind of join, if entry in left table, and not in right, then substract from 0)
I hope this is half way understandable
Thank you all very much in advance.


texts: SQL Query - Set Date Variable by Table/Column Reference

texts:
How do I set the @DATE variable by using a table/column reference?
Right now I've set it manually as you can see below with the opening:
SET @DATE = '2012-03-31 01:00:00';

The table from which I would pull the date variable is not the table from which the SELECT statement is pulling.
SQL query mockup:
SET @DATE = '2012-03-31 01:00:00';

SELECT
  Column 1
FROM
  Table
WHERE
  table.column = @DATE


texts:
I'm having trouble with an appropriate DB design for flashcards that display their data with one of numerous templates.

A user has many flashcards. They pick the card template during creation. There are only 4 templates, but up to infinity in the future.

My current DB design is as follows (all id fields are indexed with a foreign key - but not all will be filled on every flashcard):

create_table "flashcards", force: :cascade do |t|
  t.datetime "created_at"
  t.datetime "updated_at"
  t.text "fields_n_data"
  t.integer 'template_type'
  t.integer 'word_id'
  t.integer 'example_sentence_id'
  t.integer 'meaning_id'
  t.integer 'grammar_id'
  t.integer 'image_id'
  t.integer 'user_id'
end

Some use user-inputted data, some use data from our DB. Some have just 2 fields (front, back), while others have numerous (up to infinity in theory). Some pertain to a combo (or none) of: 1) a word, 2) a meaning, 3) a grammar point (from which information can be auto-filled).

fields_n_data stores all manually inputted information in an array of key/value pairs, where each key is the name of the field, and each value is the data to be filled in that field.

When time to display, I retrieve the appropriate template, iterate through the display fields - for each, I first check if the foreign key object is present, and if so I retrieve and fill using that info; if not, I go to the fields_n_data to get the value of the appropriate key.

This is working, but feels quite wrong, is a pain in the butt, and won't allow a user to create their own template in the future. Is there a better way?


texts:
MySQL seems to crash suddenly but recovered in around 2minutes. I am running on Ubuntu 18.04 (16vcpu, 32GB RAM). I dont know what caused it.
Here is my.ini
[mysql]
default-character-set=utf8mb4

[mysqld]
lower_case_table_names = 1
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /data/mysql
log-error       = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address   = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
max_allowed_packet=1G
max_connect_errors=1000
max_connections=2500
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
innodb_buffer_pool_size=27G
server-id=2
log-bin=env1
expire_logs_days=2
sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
net_read_timeout=300
net_write_timeout=300
log_bin_trust_function_creators=1

Here is my /var/log/mysql/error.log

The crash is around 8:11am as seen on the image above
Im trying to find the root cause for this. Is there any logs in Ubuntu that I can check to find more info on what caused MySQL to crash?
EDIT:
I saw the OOM (Out of Memory) error on the /var/log/syslog. Any idea what caused this?


texts:
Which is faster? Multiple update queries in a cursor loop, or a single query?
For example, in this case, which is faster? This query is in a trigger.

Multiple Query in loop

DECLARE done INT DEFAULT FALSE;
DECLARE record_id INT;
DECLARE cur CURSOR FOR SELECT id FROM table1 WHERE column = 0;

OPEN cur;

  users_loop: LOOP

     FETCH cur INTO id;

     IF done THEN
         LEAVE users_loop;
     END IF;

     UPDATE table2 SET column = 0 WHERE id = id;

  END LOOP;

CLOSE cur;

Single Query

 CREATE TEMPORARY TABLE tmp_table (id int(10));

 INSERT INTO tmp_table SELECT id FROM table1 WHERE column = 0;

 UPDATE table2 SET column = 0 WHERE id IN(SELECT id FROM temp_table);

 DROP TABLE IF EXISTS tmp_table;


texts: Azure Analysis Service Cube Sizes Changed Since July

texts: we have come across very strange thing the over all size and individual sizes of all AAS cube databases where reduced with out any changes happened. Did anyone saw this change dip and any cause?


texts: SQL Server - High Performance Record Comparison

texts:
Scenario : I have a collection of records to be inserted/updated into a table. Each record before getting inserted/updated in the table must be checked with the other rows in the table so as to find whether the same record already exists.

Currently used logic : Used the concept of cursor in an SQL server stored procedure to compare each record with every other record in the table before insertion/updation.

Limitations : As the number of records increases in the target table, performance is considerably decreased.

Can I get a better solution than what I have used?


texts: Get datetime between start and end datetime


texts: get query execution time in command prompt

texts:
I'm doing this query select * from rentals. This table has more than 10,000 rows and it takes more than 4 minutes to retrieve the results.

But I get this time after I get the results: 10000 rows in set (0,05 sec).

Do you know how to get the real execution time?

I try with:

set profiling = 1;
select * from rentals;
show profiles;

But also appears that the query duration is: 0,5080950.


texts: Mysql Slave - Stop applying changes from a particular user

texts:
I have a mysql master and 3 slave. In the master im running Archive commands to delete old records everyday.

But I don't want to Purge the records on the Slave 3. Im using this as a Archive DB.

So, How can I achieve this?

Im using a seperate user for archive.

Is there any options to prevent this operation from the Particular slave?
Or any way to achieve this?


texts: Set based solution to group multiple rows within a sequence


texts: How to get the ignored row while IGNORE_DUP_KEY is ON on SQL Server?

texts:
I have a tmp table shown below.

In the above table

IGNORE_DUP_KEY is set to ON

and

the id column is the primary key.

Said table has only one key. After inserting lots of data I will get the

  Duplicate key was ignored.

message because of some redundant data.

I want to check which redundant row was tried to insert. I checked the origin of the message it was sys.messages.

Now, how to store the row which failed while the insertion attempt took place because of the duplicate primary key value?


texts: Connections increasing by 10+ a second with no requests

texts:
I'm getting no answers from my host, but I suspect their DB servers are compromised.

I was getting some ridiculous page loads for requests to a standard Wordpress installation - upwards of 30 seconds, and always multiples of 30 plus a normal load time, so it might be 32 seconds, or 122 seconds, or 211 seconds for example. This was even for ajax requests for the simplest of tasks.

I then started getting Mysql has gone away errors, sometimes, but not always, in addition to max_connected_users errors.

After querying SHOW STATUS WHERE Variable_name LIKE '%onn%' I noticed that the Connections variable was increasing at the rate of 10 a second.

My question is, is this normal? Would there possibly be hidden host processes that would increase the Connections by this much, constantly, and take up active connections and processing power that limit my access to the DB?


texts: MongoDB Storage Architecture (MySQL innodb_file_per_table equivalent to MongoDB)

texts:
I am new to MongoDB.I looked inside dbpath I found

users.0 -  16.8MB
users.1 -  33.6MB
users.ns - 16.8MB

Further when I dig deeper I found,

The .0, .1 files are datafiles. Each datafile is preallocated to a particular size. (This is done to prevent file system fragmentation, among other reasons.) The first filename for a database is .0, then .1, etc. .0 will be 64MB, .1 128MB, et cetera, up to 2GB. Once the files reach 2GB in size, each successive file is also 2GB.
The ".ns" files are namespace files. Each collection and index would count as a namespace. Each namespace is 628 bytes, the .ns file is 16MB by default.Thus if every collection had one index, we can create up to 12,000 collections. The --nssize parameter allows you to increase this limit

But I when I started comparing MySQL with I figured out if you specify

innodb_file_per_table = true/1

But I cannot find any option for MongoDB to create separate file to each collection.

Does MongoDB only creates datafiles(.0, .1) which will contain all the collection?

Or

Is there any way to tell MongoDB to create separate files for each collection?

Your kind help and precious time would be highly appreciated


texts:
Using SQL Server 2000, I am getting the following error.

  The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space."

How to solve it? I am unable to take backup of tempdb.

I have tried all the options like :

backup log tempdb with truncate_only
dump tran tempdb with no_log;
shrink the database.


texts: Display the error message returned form Mysql

texts:
I need to get the whole error message returned from MySQL and assign it to @error_string :

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
       BEGIN
        SET @error_string = 'SQLEXCEPTION_ERROR_MESSAGE'; <-- here
       END;

Is there any alternative, thanks.


texts: Truncate a table with 17 billion rows in an AG

texts:
I need to truncate a table with 17 billion rows, the table is in a database that is part of an AG.
What will be the effect of this operation on the AG latency and the size of log backups?
Is there a recommended way of doing this?


texts: how often to run reorganize index & update statistic maintenance task on sql server 2012?

texts:
I know this is a hard question to answer without more details, but I am hoping for some general guidelines or rule of thumb ideas, or perhaps a how-to determine what would be best for my set of databases.

I have a system of about 100 databases, that since its creation 8 years ago, i did index reorg, stats update, check integrity task on all databases, every night. most databases are about 1GB in size, one is 10GB

As the databases grew, this task takes more and more time, and now has adverse effects on performance, and there is no good time when I can handle the performance effect.

I am afraid of just turning this task off, and I am looking for some guidance - I don't even know if doing such maintenance task is necessary!  Should I be doing such maintenance job? how often?

Thank you!


texts: Can't connect to Azure Database for MySQL server

texts:
I created a resource in azure: "Azure Database for MySQL server".
I added the firewall rule to connect from my ip. It is a fixed IP.

When I try to check if the socket open it works:

When I try to connect from Cloud shell with user and password it works fine.
But when I try with a client on PC and from the right IP I get:
Lost connection to MySQL server at 'reading initial communication packet', system error: 0

I tested with DBeaver and MySQL Workbench.
Here an example of connection parameters with DBeaver:

I excluted so far:

Connectivity problems
Credential problems (it works fine connecting from cloud shell)
ssh problem: I had some tests with it enabled or disabled.

Seeking for suggestions.


texts: How to replicate a mongodb pod with its persistent storage (Minikube - Kubernetes)

texts:
I made these settings :

     apiVersion: v1
     kind: PersistentVolumeClaim
     metadata:
       labels:
         name: mongo-claim0
       name: mongo-claim0
       namespace: my-app
     spec:
       accessModes:
       - ReadWriteOnce
       resources:
         requests:
           storage: 100Mi
     status: {}

       apiVersion: v1
       kind: ReplicationController
       metadata:
         labels:
           name: mongo
         name: mongo-controller
         namespace: my-app
       spec:
         replicas: 1
         template:
           metadata:
             labels:
               name: mongo
           spec:
             containers:
             - image: mongo
               name: mongo
               ports:
               - name: mongo
                 containerPort: 27017
               volumeMounts:
               - mountPath: /data/db
                 name: mongo-claim0
             restartPolicy: Always
             volumes:
             - name: mongo-claim0
               persistentVolumeClaim:
                 claimName: mongo-claim0

    apiVersion: v1
    kind: Service
    metadata:
      name: mongo
      namespace: my-app
      labels:
        name: mongo
    spec:
      ports:
      - port: 27017
        targetPort: 27017
      selector:
        name: mongo

When I try to scale this pod, Minikube UI show :

  mongo-controller-xr21r -> Waiting: CrashLoopBackOff Back-off
  restarting failed container Error syncing pod

And I got an error on the new pod :

  exception in initAndListen: 98 Unable to lock file:
  /data/db/mongod.lock Resource temporarily unavailable

Could you help me to be able to scale a pod with a persistent storage ?


texts:
When multiple selects are used in a UNION/UNION ALL are the table locks for each individual SELECT statement handled individually or are all table locks held until all SELECT statements are completed?

For example, 100 individual select statements are concatenated together via UNION/UNION ALL.  (Assuming they are resolved sequentially) If the first SELECT in the transaction is completed and another transaction in a different session attempts to access the same data, is the data still locked by the UNION?

I am just trying to get an understanding on how UNIONs behave with the physical tables while they are still executing.

For the query which prompted this there are 7 different statements in the UNION (Not UNION ALL).  With this particular question I was just wanting to get an understanding on the back-end architecture of how the UNION/UNION ALL worked.  The query which prompted this question causes a lot of blocking but only runs for about a minute, so I was trying to get an understanding on if it was the whole query causing it or just a few of the individual pieces.

I have added a follow-up question here: Backend Table/Row Locking Structure using CTE Queries


texts: Granting permission on a particular job to execute on SQL Server 2005

texts: An user is requesting permission on a particular job. How can I give him access on job activity monitor which shows only that requested job?


texts: SQL - Query on table creation, referential integrity

texts:
Please let us know what does the below definition means on table creation.

CREATE TABLE A(w INT PRIMARY KEY);

CREATE TABLE B(x INT PRIMARY KEY REFERENCES A(w) ON SET NULL)

As per my understanding, there is a foreign key reference on attribute-B(x) with attribute-A(w) and B(x) will set to null when A(w) is deleted that reference to B(x). Will this not violate the primary key constraint on X, which cannot be NULL?


texts: Where can I find exact rules of a collation?

texts:
Is there any documentation anywhere for the exact sorting/equality rules defined by a collation in Windows (SQL Server), for instance "Finnish_Swedish_CI_AS"?

I know about CI/CS, AI/AS etc, but I'm looking for a table or set of rules that actually states that e.g. 'a' is sorted before 'b' and so on. Maybe something similar to this page: http://collation-charts.org/mssql/mssql.040B.1252.Finnish_Swedish_CI_AS.html The problem with that page is that I'm unclear on some aspects of how to read it. Also it doesn't indicate that æ is regarded as equal to 'ae', and I'm wondering if there are more such cases or other rules not shown.

Also, I'm trying to find the exact difference between Finnish_Swedish_CI_AS and Finnish_Swedish_100_CI_AS


texts: How can I make this DECLARE PROCEDURE working?

texts:
I'm new to Oracle DB, coming from MS SQL Server.
I created this script:
DEFINE USER_NAME = &1
DEFINE PASSWORD = &2
DEFINE TABLESPACE = &3

CONNECT &DB_ADMIN/&DB_PWD@//localhost:1521/&DB_NAME

SET VERIFY OFF
SET SERVEROUTPUT ON
SET FEEDBACK OFF

DECLARE PROCEDURE Create_User
  ( userName IN VARCHAR2
  , password IN VARCHAR2
  , tSpace IN VARCHAR2
  ) AS
  dbCount INTEGER := -1;
  createStmt VARCHAR2(500);
BEGIN
  SELECT COUNT(*)
    INTO dbCount
    FROM CDB_USERS u
      INNER JOIN v$pdbs d ON u.CON_ID = d.CON_ID
    WHERE d.NAME = '&DB_NAME'
      AND u.USERNAME = userName;

  IF dbCount > 0
    THEN
      EXECUTE IMMEDIATE 'DROP USER ' || userName || ' CASCADE';
      DBMS_OUTPUT.PUT_LINE('User ' || userName || ' dropped.');
    END IF;

  createStmt := 'CREATE USER ' || userName || ' IDENTIFIED BY ' || password || ' DEFAULT TABLESPACE ' || tSpace || ' QUOTA UNLIMITED ON ' || tSpace;
  DBMS_OUTPUT.PUT_LINE('User about to be created:');
  DBMS_OUTPUT.PUT_LINE(createStmt);
  DBMS_OUTPUT.PUT_LINE('');

  DBMS_OUTPUT.PUT_LINE('Creating user ' || userName || ' ...');
  EXECUTE IMMEDIATE createStmt;
  DBMS_OUTPUT.PUT_LINE('Database ' || userName || ' successfully created.');

  END Create_User;

BEGIN
  Create_User('&USER_NAME', '&PASSWORD', '&TABLESPACE');
  END;
  /

When I run it in SqlPlus, like this:
SQL> DEFINE DB_NAME = 'MYDB'
SQL> DEFINE DB_ADMIN = 'SA'
SQL> DEFINE DB_PWD = 'pwd'
SQL> @/db-install/sql/add-user.sql 'USERNAME' 'pwd' 'TSPACE'

I get this error message:
Connected.
DECLARE PROCEDURE Create_User
*
ERROR at line 1:
DECLARE
*
ERROR at line 1:
ORA-01918: user 'USERNAME' does not exist
ORA-06512: at line 19
ORA-06512: at line 36

Can someone please point me to the right direction? What am I doing wrong?
Your answer is appreciated.

EDIT
I have this exact same code in another script, and it's working fine there:
DEFINE DB_NAME = &1
DEFINE DB_ADMIN = &2
DEFINE DB_PWD = &3

CONNECT &DB_ADMIN/&DB_PWD@//localhost:1521/&DB_NAME

SET VERIFY OFF
SET SERVEROUTPUT ON
SET FEEDBACK OFF

BEGIN
  DBMS_OUTPUT.PUT_LINE('Adding tablespaces to database ...');
  END;
  /

--- Procedure: dropping and creating a given tablespace ---
DECLARE
  dbCount INTEGER;
  createStmt VARCHAR2(500);
  filePath dba_data_files.FILE_NAME%TYPE;
PROCEDURE Create_TS
...

EDIT 2
It doesn't make a difference if I put the variables between DECLARE and PROCEDURE:
DECLARE
  dbCount INTEGER := -1;
  createStmt VARCHAR2(500);
PROCEDURE Create_User
  ( userName IN VARCHAR2
  , password IN VARCHAR2
  , tSpace IN VARCHAR2
  ) AS
BEGIN

Connected.
DECLARE
*
ERROR at line 1:
ORA-01918: user 'USERNAME' does not exist
ORA-06512: at line 19
ORA-06512: at line 36


texts: How to add a summary count to this query

texts:
I am using Postgres 9.4 and I have a query that looks like the following:

SELECT attendees.event_id, attendees.user_id, users.tags
FROM users, attendees
WHERE 'crystal:promo' = ANY(users.tags)
AND users.id = attendees.user_id
AND attendees.status = 'paid'
GROUP BY attendees.event_id, attendees.user_id, users.tags
ORDER BY attendees.event_id desc;

Result:

 event_id | user_id | tags
----------+---------+------
 1        | 2       | ...
 1        | 4       | ...
 1        | 7       | ...

I'd like to add a summary column for each row that is the total number of people per event independent of user tags such that the result would be something like:

select count(*) from attendees where status='paid' group by event_id

and joined with the above results (assuming 11 paid):

 event_id | user_id | tags | total_event_paid
----------+---------+------+------------------
 1        | 2       | ...  | 11
 1        | 4       | ...  | 11
 1        | 7       | ...  | 11


texts: After Exporting Database should be empty

texts:
I am exporting data from database for that I have written following query:

SELECT ParameterID, Name,Type,Datatype,LogDate
FROM Param_Ems
WHERE DataType = 10
INTO OUTFILE 'C:/tmp/ParamData.csv'
FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

Is there any way to export and delete these records within the same query?


texts: SQL Server Sch-S locks on unrelated tables

texts:
I am a bit confused. I myself am MCSE and thought I would understand most parts of the SQL Server.

Now I have a stored procedure here, doing inserts and updates on two tables dbo.tabA and dbo.tabB. Let's call this SPID 1.

On a complete different table sales.invoices I am recreating a clustered index on a partition scheme. Let's call this SPID 2.

To my surprise SPID 1 is blocked by SPID 2.

Looking into sys.dm_tran_locks, it tells me that SPID 1 is waiting for a Sch-S lock on sales.invoices (OBJECT LOCK) but  sales.invoices is neither mentioned directly in the SPID 2 query nor is it part of any view or function.

Why is SPID 1 trying to achieve a lock on an object that is totally untouched by its query?


texts:
below mentioned code for cursor works only in Oracle 11g, as LISTAGG() function introduced starting version 11g. I need to adapt this code for Oracle 10g. Any help would be appreciated.

SELECT DISTINCT (t3.field1), t3.field2,
       LISTAGG(t3.field3, ', ') WITHIN GROUP (ORDER BY t3.field3),
       SUM(t2.field1), SUM(t2.field2)
  FROM Table1 t1, Table2 t2, Table3 t3
 WHERE t1.id = t2.idoft1
   AND t2.id = t3.idoft2
GROUP BY t3.field1, t3.field2;

UPDATE:

Here is SQLFiddle version.


texts: Trying to load a file into a database on virtual machine

texts:
I have set up a Postgres db on a linux vm and have been having no issues using a GUI to connect to it. However, I am trying to load a large, 32GB, file onto it and so am skeptical of the old way I was doing as it takes a lot of bandwith. I set up a dropbox folder to sync on the VM, which it has, and tried to COPY the file and got error message:

ERROR:  could not open file "~/Dropbox/0ptimus-Jaspin/nation/VoterMapping--NH--03-17-2014-HEADERS.tab" for reading: No such file or directory

I used the following to try to do the copy:

COPY nh FROM '/Dropbox/0ptimus-Jaspin/VoterMapping--NH--03-17-2014-HEADERS.tab';

Thanks!


texts: Create a trigger on all the last_modified columns in PostgreSQL

texts:
In PostgreSQL 9.5, I have tables with columns in the form

prefix_last_modified timestamp without time zone NOT NULL DEFAULT  (clock_timestamp() AT TIME ZONE 'UTC')

I was looking for a way to set the last modified value automatically updated at each update of the rows, and I found this nice post that defined the function:

CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.modified = now();
    RETURN NEW;
END;
$$ language 'plpgsql';

Now, I'd like to know if there is any way to pass the column name to the PostgreSQL function and to execute it to the NEW row? E.g.

CREATE OR REPLACE FUNCTION update_modified_column(varchar column)
    RETURNS TRIGGER AS $$
    BEGIN
        NEW.column = now();
        RETURN NEW;
    END;
$$ language 'plpgsql';


texts: SQL Server - storing ulong as primary key

texts:
I need to store ulong values as a primary key in SQL Server. Since SQL Server doesn't have a ulong datatype (or for that matter anything unsigned), the only way to go is to have column as varbinary(8).

Now, having a varbinary as a PK doesn't sound like a good idea. So what are the alternatives to store ulong values?


texts: There are 1000 picture that are jpeg format.How do I load them as varbinary(MAX) format into SQL Database?But at the same time I want the bulk insert functionality.


texts: What is the default username and password to the oracle pre-built virtual machine?

texts:
I downloaded DeveloperDaysVM2016-06-02_13.ova from http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html and successfully loaded it into Virtual Box.

I logged in as "oracle" user and I can see the desktop.

From within the virtual machine, I opened Firefox and navigated to:

http://localhost:8080/ords/hrrest/employees/ and successfully received a list of employees in JSON format. So I know the database is up and running.

My only problem is I cannot login to the database.

I tried:

[oracle@vbgeneric oracle]$ sqlplus oracle

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 5 17:32:22 2016

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

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

Enter user-name:

my ORACLE_SID=orcl12c

How do I login to the database itself?


texts: SQL Server replication subscriptions marked as inactive

texts:
Is there any way to force SQL Server NOT to mark subscriptions as inactive, ever?

It happens sporadically when there are connection issues and I don't want to have to reinitialize the subscription every time.

Note, I'm not talking about the subscriptions being marked as expired...just as inactive.

We have retention set to 0 (infinite). I'd rather have a big distribution database than have to reinitialize a 15GB database to a subscriber over the WAN. Anyway, the real issue here is that they are getting marked as inactive within just a few hours sometimes...or sometimes a few days...it seems sporadic. Is there any way to tell WHY SQL decided in its wisdom to mark them inactive?


texts:
I found all of the Oracle data files including redo, control, system, index and everything are in just one directory.

That directory is on Raid5 disks.

In this case do I need to do something for optimal performance?

Or in a raid system there is nothing to care about? Since no io contention happens?


texts: Sigle sign on solution for Cassandra with Keycloak

texts: Please suggest if there are possible solution of itegrating SSO for Cassandra with keycloak. I have searched for blogs for keyclock integration with cassandra. I couldn't find how to integrate these two for single sign on.


texts: Do any document databases (noSQL) allow you to do cross database joins?

texts: I am currently looking at MongoDB and ArangoDB as the database for our web application, but neither appear to support cross-database joins, does anyone know if there is such a document database that supports this please?


texts: How to find if the application uses the database snapshots in Management Studio

texts:
I'm taking over dba responsibilities for the migration of an environment that the application owners are unable to give enough information on.
I need to comment on possibility of optimizing licensing cost. Having compared Enterprise and Standard edition features, the only concerns i have are for Database snapshots and TDE, both of which are only supported by Enterprise in SQL 2012.
There are snapshots in SSMS for some of the databases, but how do I know if the application is using them or not? My thoughts are that, if they are not being used then we could look at using Standard edition.
For TDE I can check that with sys.dm_database_encryption_keys.
Any thoughts to guide me will be appreciated please.
Thank you


texts:
I have a view,which is slow when i query a simple select statement with a where clause.The where clause column is indexed(Non-clustered index)
Here is the view and the plan.
execution plan

CREATE VIEW [dbo].[CurrentIncidentStatus]
AS
SELECT [incidentDetails].[IncidentStatus], [incidentDetails].[IncidentStatus_FieldValue], [incidentDetails].[IncidentStatus_Description], Report.Id ReportId, Form.Id FormId
FROM
[dbo].[IncidentDetailsPage_Incident] incidentDetails WITH (NOLOCK)
INNER JOIN [dbo].[IncidentDetailsPages] detailsPage WITH (NOLOCK)
ON incidentDetails.PageId = detailsPage.Id
INNER JOIN Form WITH (NOLOCK)
ON detailsPage.FormId = Form.Id
INNER JOIN Report WITH (NOLOCK)
ON Form.ReportId = Report.Id
LEFT OUTER JOIN dbo.IncidentDetailsPage_Supplements supplement WITH (NOLOCK)
ON detailsPage.Id = supplement.PageId
INNER JOIN
(
    SELECT ReportId, Max(FormNumber) RecentFormNumber FROM
    (
        SELECT Report.Id ReportId, FormId, COALESCE(SupplementNumber, '0000') formNumber
        FROM dbo.IncidentDetailsPages detailPage WITH (NOLOCK)
        INNER JOIN Form WITH (NOLOCK)
        ON detailPage.FormId = Form.Id
        INNER JOIN Report WITH (NOLOCK)
        ON Form.ReportId = Report.Id
        INNER JOIN dbo.IncidentDetailsPage_Incident incident WITH (NOLOCK)
        ON detailPage.Id = incident.PageId
        LEFT OUTER JOIN dbo.IncidentDetailsPage_Supplements supplement WITH (NOLOCK)
        ON detailPage.Id = supplement.PageId) FormNumbers
        GROUP By ReportId) RecentForm
ON Report.Id = RecentForm.ReportId AND
RecentForm.RecentFormNumber = COALESCE(supplement.SupplementNumber, '0000')
GO

I analyzed the plan in Sentry Plan explorer and learned that the HASH JOIN Aggregate is the costly operation in the plan.
I am trying to avoid/remove that HASH JOIN someway.?
If any of you have experienced similar situation please give some suggestion.

Additional Info:

The plan says Hash key is built on Table Report and column Id.
In-fact the Idcolumn is the primary key in the Report table ,so a clustered index is present also a Non-clustered index is created for Id column.
Still why Hashing is required for Report.Id?

Here is the screenshot of the HASH MATCH Aggregate operation.

Info:
When i select TOP 100 or 1000 it doesn't show HASH MATCH (Aggregate).

Thanks


texts: AlwaysOn Replicas set to manual but failing automatically

texts: My two synchronous-commit SQL AO replicas are set for manual failover mode but are failing over automatically. When I look through the logs and extended events, it looks just like what would happen if it were set automatically. This was set before a time when I was out of this office and the servers have been rebooted multiple times for updates, so I know the service account isn't waiting for a restart. Manual failover works but unsure why automatic is happening. There were 4 nodes, but the 2 remote DR nodes are currently removed both from SQL AOAG and WSFC. Any advice on where to look to figure this out?


texts: Whats the name for columns which purpose is not to identify?

texts:
I have a contacts table.

Columns are:

id
user_id
provider_id
title
phone_number
phone_ext

I want to know if there's a word that refers to the columns that make up the meat of the table. ie. (title, phone_number, phone_ext)

Thanks in advance


texts: returning the number of columns in a row that are zero in mysql

texts:
I have a table that contains amongst other things a datetime column, and 4 other columns that contain either a zero or a number. I would like to return how many columns in a row contain a zero in mysql.

for example

1_datetime      0  2  5  0

2_datetime      5  6  3  0

I would like for each datetime to know how many columns have zeros in

so i want

1_datetime    2

2_datetime    1

thanks


texts: Can sequential queries result in concurrent transactions?

texts:
I'm using PostgreSQL 9.4.4 and the default configuration. I use a JDBC connection to run multiple queries in succession. Only one Java thread is using the connection and before a new query is executed the result set of the former one is fetched. Below a simplified sketch:

while(true) {
    PreparedStatement stmt = //...
    ResultSet rs = stmt.executeQuery();
    //process result
}

The query that is causing trouble invokes the following stored procedure:

CREATE OR REPLACE FUNCTION delete_top_message(_queue_id BIGINT
                                            , _source_client_id BIGINT)
 RETURNS TEXT AS
$$
   DECLARE rv TEXT;
   BEGIN
     DELETE FROM message m
     USING (
        SELECT * FROM message
        WHERE message.queue_id = _queue_id
        AND (message.receiver_client_id = _source_client_id OR
             message.receiver_client_id = -1)
        ORDER BY insertion_time ASC LIMIT 1) AS tmp
     WHERE m.id = tmp.id
     RETURNING m.content INTO rv;

     IF (SELECT rv IS NULL) THEN
       RAISE 'No message available.';
     END IF;
     RETURN rv;
   END;
$$ LANGUAGE plpgsql;

I am observing behavior as it is described here http://www.postgresql.org/docs/9.1/static/transaction-iso.html in the section 13.2.1. Read Committed Isolation Level.

What happens is that the exception in the procedure is triggered although when I abort everything when the exception happens and invoke the procedure manually the exception is not raised. I noticed further that if a transaction is already holding a row exclusive lock on the message table that then the exception is raised.

Also if I add

     LOCK TABLE message IN ROW EXCLUSIVE MODE;

at the beginning of the procedure the problem disappears.

I thus suspect that in the above loop that a query is returning a result before its transaction is actually committed such that the next transaction is conflicting with it. Can that happen? N.B. I did not find something that explains this in the PostgreSQL documentation. Also I don't have Async commit enabled.


texts: How can I make MySQL client read password from mylogin.cnf?

texts:
I'm trying to make the mysql client connect to a mysql server without requiring the password to be given interactively.
Steps taken:

1) First create a mylogin.cnf file

$ mysql_config_editor set --user=<user> --password --host=<host>
Enter password:

2) File created successfully:

$ ls -la .mylogin.cnf
-rw-------. 1 urmt urmt  136 Dec 19 11:01 .mylogin.cnf
$ mysql_config_editor print --all
[client]
user = <user>
password = *****
host = <host>

3) Connect using mysql client

$ mysql <dbname>
ERROR 1045 (28000): Access denied for user '<user>'@'<host>' (using password: NO)

Is there a default value/configuration somewhere that makes the client ignore the password in mylogin.cnf? The user and host properties were read correctly from the file.

I am able to connect just fine if I provide the password on the command line:

$ mysql -p <dbname>
Enter password:
Reading table information...
...
mysql>

MySQL client version is 5.6.22, MySQL Server version is 5.6.22, both on Oracle Linux 6. Client and server are on different hosts.

Thanks


texts: What are all the ways an open transaction will rollback?

texts:
When a transaction is opened in SQL Server, what are all of the ways that it can be rolled back? We are currently working with a 3rd party application that is frequently showing open transactions while the query is in a sleeping status, sometimes for days (showing via sp_WhoIsActive). This leads me to believe that there is an error somewhere in the application that is not allowing the transactions to commit. What are all the ways that these transactions could possibly rollback?

Client-side timeout (if specified)
Client closes or restarts application
Manual kill of the SPID
Anything else?

Should I be doing anything with transactions that have been open for hours or days? If they are going to rollback or timeout at some point anyway, is there any harm in killing the process?


texts: Informix onunload/onload to take a snapshot and then restore

texts:
I have an Informix database. I want to take a snapshot, make some changes, then restore the database from that snapshot. (If Informix supported nested transactions, I would be using transactions.)

The database is of moderate size (it can run on a consumer laptop) and there is no replication. It is critical that this backup/restore procedure is as fast as possible (I would like to do a backup-modify-restore cycle several times per second if possible). Only a small part of the database would be modified between the backup/restore points.

Reading around a bit, it sounds like onupload/onload commands are the way to go. I can't quite figure out how to use them.

Can you please show me the way to take a snapshot of a running database, and restore that database after some changes? (All on one machine.)

Or if there is a better way to achieve this effect? (Perhaps there is a way to fork the process and all its files in a copy-on-write manner and have the child process serve a clone of the database on a different port?) If so please let me know.


texts: Where does the magic column "name" come from?

texts:
I got this by accident:

db=> select name from site;
ERROR:  column "name" does not exist
LINE 1: select name from site;
               ^
db=> select site.name from site;
     name
---------------
 (1,mysitename)
(1 row)

The second query return a tuple containing a whole row. Using postgres 9.0.1.

Edit: the definition of site by request. I doesn't really matter, this quirk works for any table.

db=> \d site
                         Table "public.site"
 Column |  Type   |                     Modifiers
--------+---------+---------------------------------------------------
 id     | integer | not null default nextval('site_id_seq'::regclass)
 title  | text    | not null


texts: Why SqlBulkCopy gets slower by table's row count?

texts:
I'm using SqlBulkCopy to insert a lot of data (10000/second at peek) into a database. And when the table starts empty, each bulk insertion takes like 300 milliseconds. But after a day when the records count become more than 20 millions, the insertion time becomes 2.5 seconds per 10K records. And this time increases with the count of records.

I have only a simple table, with no indexes except an identity clustered primary key.

I thought that this behavior is an append-only behavior and won't be affected by the table size. Like throwing a pile of litter on yet a huge pile of litter. That shouldn't be affected by the size.

What do I miss here?

Update: Table's script created via SSMS is:

CREATE TABLE [pardis].[DeliveryReports](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [PardisId] [bigint] NOT NULL,
    [Status] [bigint] NOT NULL,
    [PartId] [bigint] NOT NULL,
    [ShortCode] [varchar](100) NOT NULL,
    [PhoneNumber] [varchar](100) NOT NULL,
    [DateTimeReceived] [datetime] NOT NULL,
    [PersianDateTimeReceived] [varchar](100) NOT NULL,
    [DateTimeSentToCp] [datetime] NULL,
    [PersianDateTimeSentToCp] [varchar](100) NULL,
    [DeliveringToCpResult] [nvarchar](max) NULL,
    [IsProcessing] [bit] NOT NULL CONSTRAINT [DF_DeliveryReports_IsProcessing_1]  DEFAULT ((0)),
    [IsSentToCp] [bit] NOT NULL CONSTRAINT [DF_DeliveryReports_IsSentToCp]  DEFAULT ((0)),
 CONSTRAINT [PK_DeliveryReports] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Update 2: This is the execution plan for inserting 1 record via SSMS. As you can see, no hidden cost exists in inserting records in the table. Please note that this execution plan is after deleting the primary key index to prevent any index from affecting the performance.