texts: Two active + one passive node for failover clustering

texts:
I'd like to know if it is possible to have a single passive node for two SQL Servers in a failover clustering deployment.
For example, both active servers A and B will use server C as their contingent server. If it is apt to do so, will server C run two instances of SQL Server; one for A and one for B?
As a side question; what is the benefit of "dynamic quorum" and "dynamic witness"?


texts: SQL Server Configuration Manager 2012 - unable to set IP All = No

texts:
I have a 4-node SQL Server 2012 SP1 cluster, and under Network Configuration -> TCP/IP -> Protocol (tab), I am able to switch Listen All from Yes to No. However, when I hit OK (or Apply), I get the message:

  Any changes made will be saved; however they will not take effect until the service is stopped and restarted.

OK, fine.

Now, if I re-open the TCP/IP properties, Listen All is back to Yes.

This happens even after I restart the SQL Server service, and it happens on all 4 instances.

I have checked the version of Configuration Manager - 2011.0110.2100.060. I've compared this with a (non clustered) SQL Server 2012 where I can flip the IP All field at whim, the versions are the same (as are the SQL Server versions, 11.0.3000.0 Build 7601).

I've also ensured that under IP Addresses, the one that I want to use has Enabled = Yes, all other IPs have Enabled = No.

Neither Books Online nor Google have any useful information for me.

Has anyone seen this before?


texts: Pitfalls of renaming tables in sql server

texts:
I've thought of an approach for a process that builds a fairly large table (2million rows), now I'm canvassing opinion for it.  What's going to bite me with this renaming approach to a making a table 'live'?

Background:
The table (data.CatExt) is an overlayed version of our product catalogue. It builds daily in a complex procedure (getting data from lots of different areas and deciding which source trumps when there are conflicts - the complexity is in the business logic, not the technological side).  All the work and any tables required to get the final output happen in a 'build' schema, and the final table lives in the 'data' schema.  The schemas have different permissions.

The table is pretty wide, about 100 fields. We've taken the approach of building it from scratch each day, rather than using a 'change/additions/deletions' approach, because of the nature of the data sources (horrible to work out what is going to require a change to the final table.)  So we build a new version of the table each day, and then (after it passes some automated checks) allow that to become the new live version.

New approach:
So here's where the new approach comes in.  What's the best way to replace the live version? The simplest option is to truncate and replace with the new data, but that seems like a lot of writing of data unnecessarily. We could do an update over each field, but with 100 fields that's a lot of nasty coding/comparison to find any differences.  So I thought, why not rename the table?  It would go like this:

Live version data.CatExt exists and is useable through steps 2 and 3.
Build new version of the table build.CatExt.
Checks, indexing etc over build.CatExt all complete - ie, it's perfect.
Drop (well, I'm too chicken - probably rename) data.CatExt, rename build.CatExt to data.CatExt.

Voila, new version, no unnecessary moving of data and associated long time while the live version is being locked.

Please point out the flaws. (Be gentle, I'm new! :) )


texts: How to filter MSSQL Xevents by attach_activity_id.guid

texts:
I need to create an Extended Events session with the TRACK_CAUSALITY = ON parameter to catch errors in the batch that is executed after RPC, that's why I use this parameter.
I used this script.
When creating an event, is it possible to set up a filter by WHERE, where there will be attach_activity_id.guid equality for sqlserver.error_reported and sqlserver.module_end events to filter data before outputting to a file.
Thanks.


texts:
I am trying to create a spatially enabled PostGIS database by following the PostGIS documentation.

In the short version, i executed,

createdb yourdatabase
createlang plpgsql yourdatabase

But I did not execute the last three commands,

psql -d yourdatabase -f postgis.sql
psql -d yourdatabase -f postgis_comments.sql
psql -d yourdatabase -f spatial_ref_sys.sql

Is my database still spatially enabled? If not, then can you tell me where these .sql files are located so I can execute those last commands, since I am getting the following error,

postgis.sql: No such file or directory

I am using Ubuntu 12.04.


texts:
I have a MySQL master slave configuration and the slave is not working anymore. When trying to stop and start again the slave db, I am getting this error:

140530 14:21:23 [Warning] Slave I/O: Notifying master by SET @master_binlog_checksum= @@global.binlog_checksum failed with error: Unknown system variable 'binlog_checksum', Error_code: 1193
140530 14:21:23 [ERROR] Error reading packet from server: Client requested master to start replication from impossible position; the first event 'mysqliv-bin.000142' at 184658851, the last event read from './mysqliv-bin.000142' at 4, the last byte read from './mysqliv-bin.000142' at 4. ( server_errno=1236)
140530 14:21:23 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position; the first event 'mysqliv-bin.000142' at 184658851, the last event read from './mysqliv-bin.000142' at 4, the last byte read from './mysqliv-bin.000142' at 4.', Error_code: 1236

Any idea what can be the problem here?


texts: NULLs in a composite primary key - SQL Server

texts:
I'm trying to work out the best way of creating SQL Server primary keys, foreign keys and constraints to accurately represent my data model in LINQ / Entity Data Objects. Let's assume - for the purposes of simplification - that I have four main tables - Cats, Dogs, Pets and Owners - and one association table: OwnersToPets:

create table Cats (
    ID_Cats int NOT NULL IDENTITY PRIMARY KEY,
    Name nvarchar(50) NOT NULL,
    miceEaten int NULL
)

create table Dogs (
    ID_Dogs int NOT NULL IDENTITY PRIMARY KEY,
    Name nvarchar(50) NOT NULL,
    favouriteToy nvarchar(50) NULL
)

create table Owners (
    ID_Owners int NOT NULL IDENTITY PRIMARY KEY,
    Name nvarchar(50) NOT NULL,
)

create table Pets (
    ID_Pets int NOT NULL IDENTITY PRIMARY KEY,
    ID_Cats int references Cats(ID_Cats) NULL,
    ID_Dogs int references Dogs(ID_Dogs) NULL
)

create table OwnersToPets (
    ID_Owners int references Owners(ID_Owners) NOT NULL,
    ID_Pets int references Pets(ID_Pets) NOT NULL
)

When I create my entity objects by importing from the database, I get the following:

The problem is the relationship between Pets and Cats / Dogs. Each Cat / Dog has a single Pet reference. Each Pet object should have one Cat reference and one Dog reference, one of which will always be null. Conceptually, at least, there is a 1-to-0..1 between Cats / Dogs and Pets.

However, the entity data model created 0..1-to-many relationship between Cats / Dogs and Pets.

Any suggestions? Obviously, my database already exists and is currently in use with version 1 of the application I'm writing version 2 of, so I'd prefer not to have to change the database too much.


texts: SQL Agent embedded PowerShell script in CmdExec step fails with import-module sqlps

texts:
SQL Server 2008R2
PowerShell 2.1

I am trying to create a SQL Agent job that dynamically backs up all non-corrupted SSAS databases on an instance without the use of SSIS. In my SQL Agent job, when I create a CmdExec step and point to a PowerShell script file (.ps1) like this:

powershell.exe "c:\MyPSFile.ps1"

the job executes successfully (or at least gets far enough to only encounter logic or other syntax issues).

This approach won't work for a final solution, because there is a requirement to keep the PowerShell script internal to SQL. So I have a different CmdExec step that embeds the PowerShell script like so:

powershell.exe "import-module sqlps –DisableNameChecking

$server_name = "localhost"
$backup_location = "C:\BackupsGoHere"

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") | out-null
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($server_name)

# Generate an array of databases to be backed up
foreach ($database in ($server.get_Databases() | Where {$_.EstimatedSize -gt 0 -and $_.Cubes.Count -gt 0})) {
    $directory_path = $backup_location + "\" + $database.Name
    if (!(Test-Path -Path $directory_path)) {
        New-Item $directory_path -type directory | out-null
    }
    [string] $timestamp = date
    $timestamp = $timestamp.Replace(':','').Replace('/','-').Replace(' ','-')
    $database.Backup("$directory_path\$database-$timestamp.abf")
}
$server.disconnect()"

However, when executed with the embedded script, the job errors out quickly with the following response:

  The specified module 'sqlps' was not loaded because no valid module
  file was found in any module directory.

Why can't I reference the module from an embedded script, but doing so in a ps1 file works just fine?


texts: Can I implement Intellisense into my stored procedure with custom constants?

texts:
I created a stored proc to do some cleanup on some orphaned temp tables by issuing a DROP.
However, I want to vary the time threshold so I have a stored procedure that accepts a time unit (e.g. hours, minutes, days - as varchar(25)) and the number of said units (1,2,3.. - as int).

So in my code, I have hard-coded varchar expressions to call the correct DATEADD function:

IF @TimeOffsetUnit = 'Hour'
    SET @CutoffDateTime = DATEADD(hh, @UnitAmount * -1, GETDATE())

IF @TimeOffsetUnit = 'Day'
    SET @CutoffDateTime = DATEADD(dd, @UnitAmount * -1, GETDATE())

IF @TimeOffsetUnit = 'Minute'
    SET @CutoffDateTime = DATEADD(mi, @UnitAmount * -1, GETDATE())

Obviously, when I call my procedure, I need to know in advance to send in the parameter 'Hour' not 'Hours' otherwise the code will not execute the intended statement.  If someone besides myself wants to use this procedure, they will undoubtedly need to examine the code to get the parameter constant spelling correct.

Is there a way, like DATEADD, where you start typing in a valid value and have Intellisense provide a dropdown of acceptable values for a stored procedure?  Or am I expecting too much from SQL Server?


texts: Does SQL Server prioritize select and update statements?

texts:
Assume that SQL Server received both select and update statements to the same table at the same time

Do any of them get prioritized? I know that select statements are delayed until update completed. If table lock continue for a long time due to update, select statement gets cancelled with too much waiting error

So what happens when both received at the same time?


texts: Deploy dacpac fails with compatibility set to 130

texts:
When we do a build with dacpac, there is a timeout error connecting to the server when the compatibility level is set to 130 on one of the databases. The process builds just fine as long as the one database is set to compatibility level 100. That is where it used to be.

We use SQL Server 2016
This is the sqlpackage executable:
C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin\sqlpackage.exe

Am I missing settings or something else on the database that would cause this?


texts:
I have truncated all tables in a rather large Microsoft SQL Server 2014 database, so that I could take a backup of just the data schema, permissions, etc. (I can't just let SSMS create scripts of the database, since there are circular dependencies between different databases..).

When I'm creating a backup of the database the resulting file size is 1.3GB which seems rather excessive for an empty database. What kind of meta data (all tables are empty and report sizes of 0) could explain such a large file size and how could I investigate this?

There are some CLR assemblies in the database, but when I let SSMS generate a database creation script (which includes the assemblies base64 encoded it seems), the resulting sql file is only 12MB large so I don't think this would explain it.

As far as I know the backup really only backs up the actual data and not the unused space - otherwise most backups would be much larger than they are. I tried to shrink the files just to be on the safe side though (not like I can fragment an index of an empty table too badly) but that didn't change anything.

sp_spaceused returns database_size 1291.57MB, unallocated space 1.95 MB; reserved 1192696 KB; data 1174208 KB; index_size 15208 KB; unused 3280 KB. SQL Server really seems to think that I have lots of data somewhere, just not in my tables.

SELECT is_read_committed_snapshot_on FROM sys.databases returns 1 for this database.

I put the database in single user mode and took it offline (so that should definitely kill every transaction) and then ran sp_spaceused again. I get the same numbers as before and the backup is still the same size.

SELECT count(*) AS NumRows FROM sys.dm_tran_version_store returns 0.


texts: How can I list all tables without a primary key?

texts: I've seen a ton of queries to list primary and foreign keys but how can I query for tables missing a primary key?


texts: SQL Server Failover Cluster setup shared drive issue

texts:
I'm trying to complete an SQL install on a two node cluster which has three LUNs  assigned from Filer storage present and currently owned by the first node.

I've selected the "New SQL Server failover cluster installation" option from the setup media and got to the "Instance Configuration" step.

Here I've named the SQL Server Network Name and instance and chosen the LUN I;ve dedicated to the DB Data (H:\MSSQL10_50.SQL_DevInst1), however, when I click on next I get the following error:

  Instance directory (or command line option /INSTANCEDIR) value for a cluster installation cannot be a directory on shared drive.

I'm baffled by this as the LUN is not a shared drive!

Choosing an actual local drive (C:) does allow this to continue but surely should accept my first choice?


texts: Are having TRIGGERs for every upvote/downvote detrimental in terms of performance?

texts:
I plan to have three tables: Users, User_Reputation and Voting_Records. The Users table would hold the primary key id for the registered users, along with personal information such as email and passwords. If they upvote something, it would record it in the Voting_Records like so:

+------+-------------+-----------+--------+
|  id  |   user_id   | author_id |  vote  |
+------+-------------+-----------+--------+
|  3   |   12389129  |  123421   |   -1   |
+------+-------------+-----------+--------+

With the user_id and author_id being a foreign key to the id of the Users table, this would subtract 1 from the overall reputation.

So instead of using SUM() for every time a user queries for a total of their reputation, would it be more beneficial to have TRIGGER to update the total_votes on the User_Reputation table every time someone upvotes/downvotes on their profile? It would definitely be more easy to implement features such as badges, which would be automatically awarded to people with more than X points.


texts: Is there any way to find the number of pages read by each operator in query plan?

texts:
I need to find number of pages scanned by the operator in a sql-server query plan.
I used

SET STATISTICS IO ON

This returned number of logical, physical, read-ahead pages scanned per table,
but I need for every operator.

Moreover, I am unable to read the IO messages using JDBC  driver programs, and
I have more than 100 queries to be executed and to record number of pages read by each operator.

Is there any method to at least get number of pages per table, that can be accessed by JDBC driver programs

OR

Is there any flag kind of thing to be set to get the number of pages scanned in the XML PLAN Itself.


texts: DBCC Command Not being run by current user

texts:
I have a stored Proc that runs a DBCC command CHECKIDENT
The sproc is being run by a user with adequate permissions.
however, it is failing on the DBCC command with the error.

  User 'DifferentUser' does not have permission to run DBCC CHECKIDENT for object

I cannot figure out how this user is the one being used I have even removed this DifferentUser from the instance and all of the databases.

I am in a quandary


texts: Bulk Updates (inserts, update) and check constraints

texts:
Every now and then I need to update all the records (of one attribute) in a very large table. My default strategy is to create a new table, fill all the new values, create indexes, and rename the table to the new one at the end of the transaction.

This gives a huge performance speed up compared to a simple update statement (for a 5GB sample case 63 sec vs 111 secs; for a real system 10 mins compared to 4 hours). I believe this is because postgres is able to employ sequential writes compared to random read and writes in update of all records. Is it possible to tell to postgres by sacrificing visibility / MVCC of the version of the rows (blocking all queries that depend on the table of interest?)

Having a simple check constraint (not null, or integer is positive) slows down the bulk insert/upload dramatically. How should one deal with such simple constrains (foreign, primary, unique key, etc. constrains I create after the bulk update)? Should one add those constrains during the creation of the table, or add afterwards, are there any other options?

For the toy example above (six not null constraints), having constraints in create table statement resulted in 110 secs or running time. Adding constrains afterwards was 115 secs. I wonder why operative environment changed the numbers so dramatically.


texts: Search all table, find PrimaryKeys with Id = int/bigint and enable Identity, autoincrement

texts:
I have a large list of tables and due to change in middleware platform, the middleware is no longer creating the auto-increment on Add New Record at the app layer, as a result, I need to do this in the DB.

How can I Search all tables, find PrimaryKeys with Id = int/bigint ?

To enable/toggle all tables PrimaryKeys with Id = int/bigint  and turn-on Identity with auto-increment
To disable Identity identity & auto-increment (for some tests, which middle ware is still failing)

Edit:

SET IDENTITY_INSERT gettableWithIdentity ON

INSERT gettableWithIdentity (IdentityColumn, col2, col3, ...)
VALUES (SetAnIdentityValue, col2value, col3value, ...)

SET IDENTITY_INSERT gettableWithIdentity OFF


texts: Why does Postgres ORDER BY seem to halfway ignore leading underscores?

texts:
I have an animal table with a name varchar(255), and I've added rows with the following values:

Piranha
__Starts With 2
Rhino
Starts With 1
0_Zebra
_Starts With 1
Antelope
_Starts With 1

When I run this query:

zoology=# SELECT name FROM animal ORDER BY name;
      name
-----------------
0_Zebra
Antelope
Piranha
Rhino
_Starts With 1
_Starts With 1
Starts With 1
__Starts With 2
(8 rows)

Notice how the rows are sorted in an order that implies the leading _ is used to place the _Starts With 1 rows before the Starts row, but the __ in the __Starts With 2 seems to ignore this fact, as if the 2 at the end is more important than the first two characters.

Why is this?

If I sort with Python, the result is:

In  [2]: for animal in sorted(animals):
   ....:     print animal
   ....:
0_Zebra
Antelope
Piranha
Rhino
Starts With 1
_Starts With 1
_Starts With 1
__Starts With 2

Furthermore, Python ordering suggests that underscores come after letters, which indicates that the Postgres's sorting of the first two _Starts rows before the Starts row is incorrect.

Note: I'm using Postgres 9.1.15

Here are my attempts at finding the collation:

zoology=# select datname, datcollate from pg_database;
  datname  | datcollate
-----------+-------------
 template0 | en_US.UTF-8
 postgres  | en_US.UTF-8
 template1 | en_US.UTF-8
 zoology   | en_US.UTF-8
(4 rows)

And:

zoology=# select table_schema,
    table_name,
    column_name,
    collation_name
from information_schema.columns
where collation_name is not null
order by table_schema,
    table_name,
    ordinal_position;
 table_schema | table_name | column_name | collation_name
--------------+------------+-------------+----------------
(0 rows)


texts: Question on Design for User + User Groups + User Types + Subscriptions Model

texts:
I am redeveloping an existing web application with both weak source code and a weak underlying database design.  I'm trying to develop things in a way that future expansion will be much easier, namely:

Supporting new login mechanisms
Supporting new user types (ie new subscriptions plans)
Supporting new user roles

Here are a few screenshots of my unfinished schema.  Do you see any problems / data inconsistencies that could arise from this design?

Orange squares are primary keys, and blue squares are foreign keys.  Had to split this up into two images.

Users / Subscriptions / Auth Groups:

User Groups / Subscription Types:

Thanks in advance!


texts: Where can I get this nice phpMyadmin features for mongodb

texts:
In phpmyadmin

Say I browse a table, or do anything.

I will be told the actual sql command that I should run to execute the query.

Say I want to do a query and then I want to do something else similar. I can just run some query. Copy the SQL command, modify it, and move on. This is obviously less error prone than writing queries from scratch, see that it doesn't work, and not knowing what's wrong.

In rockmongo I don't have that feature.

Say I want to search for certain record. Then I want to modify the search with additional search. I can't do that.

So what other software to access mongodb that give that feature?

Another related feature is a feature to run any sql command I wish in phpmyadmin.

I do not have that feature in mongodb.


texts: Pull data from multiple tables in a view or function

texts:
There are 3 tables from which I need to pull data, and print it in a specific way.

http://sqlfiddle.com/#!15/59481/8

Refer to this question to see how the query was produced. The version in the fiddle working partially, but I need to modify the query to produce a different output.

I have the table project_report which is storing data for keyword:

1. If a keyword has data for a particular day it will store it in `project_report`
2. A keyword might have no data to store for a particular day.
3. A keyword might store multiple rows of data for a particular day (the data is uniq)
4. A keyword might not have data for a particular day, but it might have for a different day.

I believe to solve my problem a DB function will be the best approach, because I would like to be able to specify a date and get all the data from project_reports for each keyword.

I don't know how to write this query, to produces the below results, also I don't know what will be better, to have a view or a db function, I think that with a view in order to select a specific date, I will need for each keyword and each date to produce those outputs. The other problem is that I would also like to use these results in a chart, which will take the data for a range, for that I think a view is better, then calling a function n times.

So if keyword_id: 1 has in the project_report:

ID: 1, keyword_id: 1, project_id: 1, position: 1, created_at: '2014-07-09'
ID: 2, keyword_id: 1, project_id: 1, position: 2, created_at: '2014-07-09'

For the selected date 2014-07-09 I should get (please see that position will be the smallest position instead of [1, 2]):

keyword_id: 1, project_id: 1, position: 1, yesterday_pos: 0, last_week_pos: 0, last_month_pos:0 created_at: '2014-07-09'

If keyword_id: 2 has in the project_report:

ID: 3, keyword_id: 2, project_id: 1, position: 3, created_at: '2014-07-09'
ID: 4, keyword_id: 2, project_id: 1, position: 4, created_at: '2014-07-08'
ID: 5, keyword_id: 2, project_id: 1, position: 5, created_at: '2014-07-08'
ID: 6, keyword_id: 2, project_id: 1, position: 4, created_at: '2014-07-03'

For the selected date 2014-07-09 I should get:

keyword_id: 2, project_id: 1, position: 3, yesterday_pos: 4, last_week_pos: 4, last_month_pos: 0 created_at: '2014-07-09'

If keyword_id: 3 has in the project_report:

ID: 7, keyword_id: 3, project_id: 1, position: 10, created_at: '2014-07-08'
ID: 8, keyword_id: 3, project_id: 1, position: 11, created_at: '2014-07-03'

For the selected date 2014-07-09 I should get:

keyword_id: 3, project_id: 1, position: 0, yesterday_pos: 10, last_week_pos: 11, last_month_pos: 0 created_at: '2014-07-09'

If keyword_id: 4 has in the project_report:

`nothing`

For the selected date 2014-07-09 I should get:

keyword_id: 4, project_id: 1, position: 0, yesterday_pos: 0, last_week_pos: 0, last_month_pos: 0 created_at: '2014-07-09'


texts: SQL Server update query on linked server causing remote scan

texts:
I have a SQL Server 2012 setup as a linked server on a SQL Server 2008 server.

The following queries executes in less than 1 second:

   SELECT kg.IdGarment
   FROM Products p
   INNER JOIN ZAMStaffRentals r ON r.ProductID = p.UniqueID
   INNER JOIN ZAMCustomer_Staff s ON r.StaffID = s.UniqueID
   INNER JOIN ZAMRentalGarments g ON g.StaffRentalID = r.UniqueID
   INNER JOIN [REMOTE_SERVER].[REMOTE_DB].dbo.t_ProcessIT_Garment kg on g.BarcodeRFID = kg.IdGarment
   INNER JOIN ZAMKannChanges log on log.GarmentID = g.UniqueID
   WHERE log.ActionType = 'I'

t_ProcessIT_Garment contains 37,000 rows, the query returns two records, the IdGarment column is the Primary Key. No problem here.

However, if I run this query to do a remote update, it takes 24 seconds, and 2 rows is affected:

   UPDATE [REMOTE_SERVER].[REMOTE_DB].dbo.t_ProcessIT_Garment SET
   IdGarment = IdGarment
   FROM Products p
   INNER JOIN ZAMStaffRentals r ON r.ProductID = p.UniqueID
   INNER JOIN ZAMCustomer_Staff s ON r.StaffID = s.UniqueID
   INNER JOIN ZAMRentalGarments g ON g.StaffRentalID = r.UniqueID
   INNER JOIN [REMOTE_SERVER].[REMOTE_DB].dbo.t_ProcessIT_Garment kg on g.BarcodeRFID = kg.IdGarment
   INNER JOIN ZAMKannChanges log on log.GarmentID = g.UniqueID
   WHERE log.ActionType = 'I' ;

I tested using IdGarment = IdGarment to keep things simple. The execution plan shows it uses Remote Query for the first query, but Remote Scan for the second query, which has 100% of the cost.

The table joins are identical in both queries, why is it using Remote Scan for the second query, and how do I fix this?


texts: Using a Parameter Query to generate Charts in MS Access Reports

texts:
I am using Microsoft Access 2007 and I have a query which looks something like this;

PARAMETERS Identifier TEXT(255);

SELECT
  DateDiff('h', (SELECT Min(SampleDateTime) FROM Specimen WHERE UserID=[Identifier]), SampleDateTime) AS ElapsedTime, Specimen.ValueA
FROM Specimen
WHERE Specimen.UserID=[Identifier]
ORDER BY Specimen.SampleDateTime

Now, this query works fine if I open the query and then enter the appropriate 'Identifier' value in the form that pops up. However, I would like to then use this query to generate a chart in a report that lists all of the studies. ie. The report will be made of 1 page per record, and each page will contain 1 chart, which shows the above data plotted as a line chart.

However, if I pop a chart in and use the wizard to create the chart, when I get to the stage where I would like to assign a fiend from the current record in the report to a value in the chart, I am unable to assign a report variable to the parameter I described above 'Identifier'. Now, normally, I would build the query to pull down all of the data, and then assign a 'filter' on that data I guess using the Chart Wizard. However, as I have a sub-query, this isn't going to work in this case.

What I believe I'm looking for, is a way of assigning the parameter 'Identifier' from the current record parameters in a multi-record report.


texts: MariaDB 10.5.8 refusing my connection over SSH tunnel once I add "skip-name-resolve"

texts:
I've moved from a 10.3 installation to a 10.5 installation, and ported my various my.cnf settings over.
One of those is skip-name-resolve.
If I leave it in I'm suddenly unable to connect to the database with Navicat.  Normally it uses an SSH tunnel and then connets to localhost on port 3306:
1045 Access denied for user 'root'@'127.0.0.1' (using password: YES)
My web app is unaffected, and can still connect to the database happily.
If I take it out, I can connect quite happily.  What should I change?  I tried changing localhost to 127.0.0.1 but that made no difference.
Thanks


texts:
For security reason I always took back up of my database. I took full backup in each month. And differential in each week. I used to save each back up file in different name. I lost my latest full back up file. But I have my latest differential back up file. So when I tried to restore my differential back up with the previous full back up , I am getting an error that

  This differential backup cannot be restored because the database has not been restored to the correct earlier state

My scenario:

Took Full Backup        - File is with me
Took Differential       - File is with me
Took Another full       - Lost this file
Differential file       - is with me

So can I restore 4th one with 1st one. With out the 3rd ?

Is there any solution for this scenario? Can I restore it without losing data?

I am using SQL Server 2008 R2


texts: Performance difference between two MariaDB slaves

texts:
I have a pool of MariaDB servers like this :

master (5.5.38-MariaDB-log)
slave1 (5.5.38-MariaDB-log)
slave2 (5.5.38-MariaDB-log)

The two slaves servers run on the exact same hardware (Dell Poweredge 2950, RAM 32GB, 8 sas disk, Intel(R) Xeon(R) CPU E5420  @ 2.50GHz)

Both slaves are installed in the same way, on a debian Wheezy (up-to-date), same fs (ext4 noatime, nobarrier)...etc

Dell OMSA do not report any hardware problem on these servers.

MariaDB config is the same on both (SHOW VARIABLES differs only for hostname, server_id, timestamp, log_error path and pseudo_thread_id)

The problem is that queries on slave2 are far slower than the same queries on slave1.

Database schema and data are equal (comparing mysqldump output and pt-table-checksum)

Though, it does not appear related to database schema or data because I created a fresh test table with 10k rows like this:

CREATE DATABASE nptest;
use nptest
CREATE TABLE np (id int NOT NULL PRIMARY KEY AUTO_INCREMENT, val int);
DELIMITER $$
CREATE PROCEDURE prepare_data()
BEGIN
  DECLARE i INT DEFAULT 100;

  WHILE i < 100000 DO
    INSERT INTO np (val) VALUES (i);
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;
CALL prepare_data();

And I still notice the performance difference.

Look at this: Here is the profiling for some simple queries on each slave:

SLAVE1 (the fast one)

nptest=# show profiles;
+----------+------------+------------------------------------+
| Query_ID | Duration   | Query                              |
+----------+------------+------------------------------------+
|        1 | 0.00032486 | SELECT * FROM np WHERE id = 123456 |
|        2 | 0.00032632 | SELECT * FROM np WHERE id = 12345  |
|        3 | 0.00030954 | SELECT * FROM np WHERE id = 12346  |
|        4 | 0.00033966 | SELECT * FROM np WHERE id = 12347  |
+----------+------------+------------------------------------+
4 rows in set (0.00 sec)

AVG = 0,000325095 sec.

SLAVE2 (the slow one)

nptest=# show profiles;
+----------+------------+------------------------------------+
| Query_ID | Duration   | Query                              |
+----------+------------+------------------------------------+
|        1 | 0.00665687 | SELECT * FROM np WHERE id = 123456 |
|        2 | 0.00302109 | SELECT * FROM np WHERE id = 12345  |
|        3 | 0.00303166 | SELECT * FROM np WHERE id = 12346  |
|        4 | 0.00448533 | SELECT * FROM np WHERE id = 12347  |
+----------+------------+------------------------------------+
4 rows in set (0.00 sec)

AVG = 0,004298737 sec.

So slave2 is 10x slower then slave1!

What can explain these performance differences?
Any tips on what to investigate would be appreciated.

EDIT 26/09/2014 : Running sysbench (mysql, cpu, memory) confirm that slave2 is far slower than slave1. Something to notice in sysbench tests, is that the "max" value is always very high for slave2, comparing to slave1. Whereas "min" or "95 percentile" values are quite equal. For example:

sysbench --test=memory --memory-block-size=1M --memory-total-size=10G run

SLAVE1:
min: 0.13ms
avg: 0.13ms
max: 0.96ms
approx. 95 percentile: 0.14ms

SLAVE2:
min: 0.13ms
avg: 0.14ms
max: 12.32ms
approx. 95 percentile: 0.14ms

However, Dell OMSA reveals no CPU or memory failure.

Anyway, the server is 6 years old, he was taken out of production...


texts: LOCK TABLE public.schema_migrations IN ACCESS SHARE MODE when executing pg_dump

texts:
I have a cron job that execute a backup on all databases on the sever. The pg_dump gets stuck in one database/table. Tracking the issue on pg_status_activity table I got:

Message:

LOCK TABLE public.table_name IN ACCESS SHARE MODE

No errors on PG logs were found
Server OS: Linux CentOS 7
Database : PostgresSQL 12
Thanks


texts:
I have configured apex instance to use smtp email, and I can send email with IR when using download option from action menu. But the IR subscription is not sending emails.
I have checked there is no record on APEX_MAIL_LOG and APEX_MAIL_QUEUE.
Any idea how to troubleshoot problem?


texts:
I have a database, with one main table: Project, and about 10 other tables (Entity1, Entity2, etc.)

Each of the different entities can belong to one or many projects.

I know I can have tables like ProjectEntity1, ProjectEntity2, ProjectEntity3, to store relationships, but that gets old.

Are there any issues with creating a ProjectContents table, which would hold projectid, entityid, and entitytype? (Entitytype would be name of the source table.)

(Currently, this is in SQL Server Compact 4.0, as a standalone solution. It may never evolve past that.)

EDIT: More details -
It's yet another world builder type desktop application, where I can dream up/generate stuff - worlds, cultures, languages, religions, alien species, characters, plots - and then assign them to a full blown project if I want. So, a project can have many entities of varying types, and an entity can belong to many projects, or none at all. Right now, all CRUD/Reads are done with C# LINQ, and unless I switch to SQL Express or something else, I don't see the need for stored procedures. At least until I hit some performance bottlenecks.


texts: large insert script (generated from tasks) - errors when running from sqlcmd

texts:
I have a large generated .sql file for the data from our UAT database.  I'm trying to load that into my (localdb)\v11.0 using sqlcmd.

I was able to use the following:

sqlcmd -S (localdb)\v11.0 -d myDB -I c:\temp\mydatafile.sql

The .sql file is 5GB. Everything seemed to be doing well; however, at the end of the output in the command window I see an error:

  Msg 102, Level 15, State 1
  Incorrect syntax near...

Apparently the data that it is pulling from the DB doesn't work in the insert statements.  Since the file is 5GB it won't open in an editor, so I can't fix it.

I did a quick data compare and I see records not in my localdb that are in the UAT database.  So that leads me to believe that it did not finish inserting all the data.

Is there a way to skip over these types of errors using sqlcmd?

It would appear that if I can't open the file, yet sqlcmd is running into errors when running that file that the next best thing is knowing what table has that error.

My next question is, is there a way to message out what table has that error so I can exclude it from the original script?


texts: Should frequently accessed tables containing large blobs with one-to-one relationships be normalized and columns split into two tables?

texts:
I have a frequently accessed table containing 3 columns of blobs, and 4 columns of extra data that is not used in the query, but just sent as result to PHP. There are 6 small columns (big int, small int, tiny int, medium int, medium int, medium int) that are used in the queries in the WHERE/ORDER BY/GROUP BY.

The server has very low memory, around 1GBs, and so the cache is not enough to improve the performance one on the large table. I've indexed the last 6 small columns, but it doesn't seem to be helping.

Would it be a good solution to split this large table into two?
One table containing the last 6 columns, and the other containing the blobs and extra data, and link it to the previous table with a foreign key that has a one to one relationship?
I'll then run the queries on the small table, and join the little number of rows remaining after filtering to the table with the blobs and extra data to return them to PHP.

Please note, I've already done this, and I managed to decrease the query time from 1.2-1.4 seconds to 0.1-0.2 seconds. However I'm not sure if the solution I've tried is considered good practice, or is even advisable at all?


texts: Why can't I log in to SYSTEM without AS SYSDBA

texts:
When loging in to sqlplus as SYSTEM, why does the login fail with an invalid username/password error if I do not include "AS SYSDBA"

C:\Windows\System32>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 16 01:00:13 2013

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

SQL> conn system/password
ERROR: ORA-01017: invalid username/password; logon denied

SQL> conn system/password as sysdba
Connected.
SQL>

I have a script that I need to run locally.  This script runs fine on other machines, but fails with the error above for me.


texts: PostgreSQL "freeze"/"unfreeze" command equivalents

texts:
In Derby (an embedded database written in Java which is mostly used for testing or prototyping) there are "freeze" and "unfreeze" commands which can be used during an online backup. "Freeze" simply causes all database accesses to block until "unfreeze" is called. This is useful for backing up using an external program, which you might do if the external program is much faster than using Derby's internal backup solution. For my use case, I can take a snapshot almost instantaneously using some built-in filesystem utilities, so it is a constant-time operation (not O(length of DB files)).

I'm migrating an application which has outgrown Derby to PostgreSQL, and I was wondering if there's anything comparable there which I can use to quiesce all connections. Also, I'd prefer to know what my serialization point is from inside my application so that I don't get caught in some awkward state, so being able to pause/resume all other accesses is really nice-to-have for me.

Since PostgreSQL has a transaction log, I could just take a snapshot without "freeze"ing, but the snapshot would need to be run through PostgreSQL's recovery mechanism before I can use it because otherwise what's stored on disk would be the same as if I pulled the plug on a normal filesystem. This solution is not ideal.

EDIT I learned that pg_start_backup() is close, but it doesn't cause incoming transactions to block until a matching call to pg_stop_backup(), forcing me to do a point-in-time-recovery back to the transaction id pg_start_backup() returns from a filesystem snapshot. It would be nice not to have to actually shutdown PostgreSQL to get this (perhaps there is a pseudo-shutdown command that keeps connections open?).


texts:
I have used mysql-administrator to back up my mediawiki and bugzilla database.
Now there is an issue  with charsets I can't fix.
So I deciced to try mysqldump
with -single-transaction --routines --triggers.  My database is a mix of inno and mysiam tables.

I am confused about the different size of the dump files.
While the mysqldump is ~150 MB, the mysqladmin file is ~220MB.
I cannot explain this, and I can't analyze the dump files, it  is too big for my notepad.

I have two questions:

What's the cause of the different sizes?
Anyone any suggestions about my options to backup?


texts: Is it possible to COPY tables into PostgreSQL without setting structure first?

texts:
I'm just getting to grips with PostgreSQL 9.6.

I'm wondering if there is a simple way to add a .csv table to a database without first creating the table structure in psql or pgAdmin?

Or...I've not used scripts before but if someone could suggest one and give me a quick run through, I'd be very grateful.


texts: Is there trade off for using FORCESEEK in SQL query?

texts:
What is the trade off for using Forceseek vs naturally letting SQL Server decide the execution plan? Is there a possibility that using forceseek invalidates the original query by returning a different result set?

Maybe I should open a different question for this, but in a query where I have multiple joins, SQL server is not happy at all when I specify forceseek hint on more than 1 tables. Is that impossible or am I using the wrong syntax?


texts: Should I invest the time to change the column type from CHAR(36) to UUID?

texts:
I have a few million rows in my database already. I didn't know about the PostgreSQL UUID data type when I designed my schema.

One of the tables has 16M rows (about 3.5M to 4 M records per shard), growing at about 500K records per day. I still have the luxury of taking the production system down for a few hours if required. I won't have this luxury in one or two weeks.

My question is, will it be worthwhile to do so? I'm wondering about JOIN performance, disk space use (full gzip'd dump is 1.25 GiB), things of that nature.

Table schema is:

# \d twitter_interactions
                Table "public.twitter_interactions"
         Column          |            Type             | Modifiers
-------------------------+-----------------------------+-----------
 interaction_id          | character(36)               | not null
 status_text             | character varying(1024)     | not null
 screen_name             | character varying(40)       | not null
 twitter_user_id         | bigint                      |
 replying_to_screen_name | character varying(40)       |
 source                  | character varying(240)      | not null
 tweet_id                | bigint                      | not null
 created_at              | timestamp without time zone | not null
Indexes:
    "twitter_interactions_pkey" PRIMARY KEY, btree (interaction_id)
    "twitter_interactions_tweet_id_key" UNIQUE, btree (tweet_id)
    "index_twitter_interactions_on_created_at" btree (created_at)
    "index_twitter_interactions_on_screen_name" btree (screen_name)
Triggers:
    insert_twitter_interactions_trigger BEFORE INSERT ON twitter_interactions FOR EACH ROW EXECUTE PROCEDURE twitter_interactions_insert_trigger()
Number of child tables: 9 (Use \d+ to list them.)


texts: What's the best practice of inserting a collection of data into a SQL Server database

texts:
I am using C#, Entity Framework. SQL Server for the development. I have a method which has two parameters (int ID, List<String> _listobj). For each ID, there is a list of obj. All of these IDs and lists will be inserted into different tables in database. So the question is: what's the best practice for this? I have couple of options:

In C#, split the list<obj> as single value, then create a stored procedure which takes two parameters (ID, str), then the C# will call the stored procedure many times depending on the size of the list
Pass the ID and list<obj> as two parameters to the stored procedure and let the stored procedure split the list<obj>. This way C# will only call the stored procedure once

Is the second strategy doable? If yes, which is better in terms of performance?


texts: Testing if an element in a PostgreSQL array?

texts:
Is there an operator in PostgreSQL to test whether an element is in an array?

Currently, I'm doing such tests in a slightly complex way by first constructing a singleton array for the element and then using the <@ operator between arrays.

SELECT ARRAY[1] <@ ARRAY[1,2,3];

( SELECT 1 <@ ARRAY[1,2,3]; does not work).

Is there a more succinct/clear way?


texts:
I've got a query running on SQL Server 2000 (and using SQL Server Management Studio 10.x)

It results in three columns with multiple rows.

What I am trying to do, and can't get my head round, is to get the multiple rows into one row for each unique item in column 1 (does that make sense).

The query is:

select last_allocated_to as engineer, count(*) as qty, left(post_code, charindex(' ', post_code) - 1) as postcode
from calls with (nolock)
inner join clients on link_to_client=client_Ref
where last_event_status = 'A'
and call_type not in ('VW')
and dbo.dateonly(scheduled_date_Time) = dbo.dateonly(getdate())
and link_to_so in ('OS1','OS2','OS3')
group by last_allocated_to, left(post_code, charindex(' ', post_code) - 1)

The result is (for example):

engineer   qty    postcode
JWH        1      AB25
JWH        1      AB31
JBR        6      EH51
RSC        1      EH51
CPA        1      FK1
RYM        3      FK10
CARPET     1      FK2
RDU        1      FK2
RSC        2      FK2
SWO        5      FK2
BJON       4      FK3
CPA        4      FK3

I want the result to be:

engineer   result
JWH        1 x AB25, 1 x AB31,
JBR        6 x EH51
RSC        1 x EH51
RYM        3 x FK10, 1 x FK2, 2 x FK1
etc etc

Is this at all possible? Or am I on a hiding to nothing?


texts: SQL-Server 2008 R2, update Microsoft.SqlServer.Types.dll

texts:
Is it possible to update the hosted assembly with a new one? Guessing not. Would like new features found in SqlGeography, like STContains() and not have to write ugly SQL workaround.

//Daniel


texts: Efficient way to generate Time Slots for a schedule


texts: Oracle - Session still holding the locks

texts:
We are using MULE(Anypoint Studio) as the ESB(Enterprise Service Bus) layer.
We are using bitronix transaction manager.
Database connection to ORACLE are made inside the transaction through EJB running on JBOSS.

We have situation in which the MULE server will be stopped without the transaction being COMMITTED OR ROLLED BACK.

When i restart the MULE server, new ejb connections are being made and the old sessions are not getting timed out.

When i query the db using :

select
   sid,
   serial#
from
   v$session
where
   sid in (
   select
      session_id
   from
      dba_dml_locks
   where
      name = '<table_name>');

I can see that two sessions are still active and holding the locks to the table.
When will the locks on the table be released.
Should i set any property in JBOSS or MULE or ORACLE for this timeout?
I can also see that manually killing those sessions, releases the locks. But this is not possible in the production environment. Wont the sessions be timed out automatically?

I can see that changing IDLE_TIME of the profile in ORACLE releases the locks after the given time.

ALTER PROFILE DEFAULT LIMIT IDLE_TIME <time>;

But the client would not accept this, because it affects the complete profile.
Is there any way to timeout only the idle sessions of a particular table?


texts:
I need to create a batch script on windows that periodically starts a sql function written in 'plpgsql'.

I can use windows schedule task for creating a cron job but I don't know how to write the script.


texts: Array stringifies to using `{}` in Postgres output

texts:
I have an array column but when I output it using SQL SELECT, it looks like

{{"a":"b","c":"d"},{"e":"f","g":"h"}}

But why does it start and end with {{ and }}? Shouldn't it be [{ and }] for arrays? Inside output of JSONB columns, arrays look as expected (square brackets, not curly braces, surrounding the array values).

Have I done something wrong, or is the output above truly an array of objects?


texts:
I'm trying to balance read traffic between two SQL Server 2016 Enterprise Edition instances (Primary and Secondary) using availability groups and the read only routing.

Here is my current setup:

The servers are in AWS in two different AZ
The servers are in a failover cluster without shared storage
AG is enabled on both instances
The databases in an Availability Group have the "Readable Secondary" option set to Yes
I've setup the AG with the read only routing on both the current primary and secondary instances
Adding the read only switch in my application connection string allows me to route traffic to the secondary

Is there a way to have the AG Listener load balance read only traffic between the primary and secondary?


texts: MySQL Trigger: Can you turn a query updating multiple columns to multiple queries where each update one column?

texts:
Here's what I'm trying to achieve: use a trigger to divide an update query which updates more than 1 columns into several update query and each of the query updates just 1 columns.

For example, the incoming query is:

update table set column_1 = 'a', column_2 = 'b', column_int = 100 where id = 1

I want to use trigger (or is there any other way?) to execute this query in the way:

update table set column_1 = 'a' where id = 1;
update table set column_2 = 'b' where id = 1;
update table set column_int = 100 where id = 1;

And of course after the three query we don't want to run the original update query (skip it to avoid run the query twice on the table).

Is this possible? The use case is that in MySQL you can update columns in a view, as long as the columns are from just 1 base table. So if I update two columns from different underlying base table MySQL will throw error, hence I want to use trigger to divide them.


texts:
In SQL Server Management Studio you can use the shortcut CTRL + K > C or CTRL + K > U to comment/uncomment lines in bulk.

Is there a similar keyboard function that applies single quotes across a list of values? I have 150 IDs that I have copied from an Excel spreadsheet and I would like to execute a query that uses an IN clause on this list, and I dont want to manually add single quotes and comma around each item.

For example:

SELECT * FROM tbl WHERE someValue IN (
ABC1
ABC2
ABC3
ABC4
)

I want to highlight the values and using a keyboard shortcut turn the list into:

SELECT * FROM tbl WHERE someValue IN (
'ABC1',
'ABC2',
'ABC3',
'ABC4'
)


texts: sp_sqlagent_get_perf_counters SQL Server 2005 running every 20 seconds

texts:
A little background info:

We are running SQL Server 2005 SP2 on a server with Microsoft Windows Server 2003 SP2.

When I run profiler I notice that EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters is running every 20 seconds.

Here is what the profiler is running every 20 seconds:

SELECT N'Testing Connection...'
go
EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
go

What I have done so far:

I have deleted all the demo alerts on the system.

Possible answers:

I was wondering if a service pack 3 or 4 on 2005 might fix this issue?
or is there something I can set in the registry or in sql itself?
or is there a patch for Windows Server 2003 that might help?


texts: Alternative approaches to "primary" record in child table

texts:
I have a database with a Customers table, and two child tables Addresses and Contacts, as customers often have multiple locations and contacts.

However we also need a "primary address" and a "primary contact" for each customer that we use as the default.

My current design was to put a PrimaryContactID and a PrimaryAddressID in the Customers table (both are nullable), but I was never very comfortable with this design - a parent table referencing the child table doesn't feel right.

The alternative approach I considered was an IsPrimary bit field on the child tables. This would need a trigger that would unset the IsPrimary value on the other records and maintain integrity but could lead to problems if a query tried to set multiple records as primary for the same customer: I'd have to write the trigger to fail in these situations.

Any ideas/comments or other approaches I should consider?


texts: MySQL crashes and currently the error.log file is huge

texts:
I've created a droplet on DigitalOcean created using Laravel Forge 6 months ago. Two weeks ago, we decided it was time to upgrade the droplet and we moved from a 4GB RAM/2CPUs to a 16GB RAM/6CPUs droplet and since a few days ago the MySQL server just crashes and the only way to make it work again is by rebooting the server (MySQL makes the server unresponsive).

When I type htop to see the list of processes is showing a few of /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysql.pid (currently is showing more than 30 entries like that).

The error log is bigger than 1GB (yes, I know!) and shows this message hundreds of times:

[Warning] InnoDB: Difficult to find free blocks in the buffer pool (21 search iterations)! 21 failed attempts to flush a page! Consider increasing the buffer pool size. It is also possible that in your Unix version fsync is very slow, or completely frozen inside the OS kernel. Then upgrading to a newer version of your operating system may help. Look at the number of fsyncs in diagnostic info below. Pending flushes (fsync) log: 0; buffer pool: 0. 167678974 OS file reads, 2271392 OS file writes, 758043 OS fsyncs. Starting InnoDB Monitor to print further diagnostics to the standard output.

The only thing that changed recently is now we send weekly notifications to customers (only the ones that subscribed to it) to let them know about certain events happening in the current week. This is kind of a intensive process, because we have a few thousands of customers, but we take advantage of Laravel Queues in order to process everything.

I've tried to change innodb_buffer_pool_size from the default value to 80% of available RAM (~13GB) and instead of the previous message, now it's showing:

"InnoDB: page_cleaner: 1000ms intended loop took 4228ms. The settings might not be optimal.".

And this change made the database run slower. For example, to process 30k records (the notifications thing that I mentioned) it took 6 hours but before the change it was taking around 3 (when it didn't crashed).

Is this a MySQL-settings related issue?

EDIT: Global Status and Variables after innodb_* suggested changes

Show Variables and Show Global Status


texts:
In a simple query like

SELECT *
FROM table
WHERE id='x' OR id='y'

How to give a priority to get only one of the WHERE clauses?

I mean getting rows with id='x'. Only if there is no row, get row with id='y'.

In other words, do not use id='y' if there is a row for id='x'.


texts: Does updating a jsonb column trigger updates on all expression indexes on that column?

texts:
I am using the jsonb type in PostgreSQL. I frequently make updates of the following kind (where jdoc is a jsonb column):

UPDATE my_table SET jdoc = jdoc || '{a: "Hello World!"}'::jsonb WHERE id = 123

I have a number of expression indexes on the jsonb column:

CREATE INDEX ix_b ON my_table some_function(jdoc->>b)
CREATE INDEX ix_c ON my_table some_other_function(jdoc->>c)

As you can see, I only change jdoc->a, but I have to set the whole jdoc column. Will I therefore suffer the full performance penalties of updating the indexes on jdoc->b and jdoc->c? Or is PostgreSQL smart enough to notice that those values did not change, and that the indexes therefor does not need to be updated? If I suffer the full performance penalty, is there any way to avoid it?


texts: To use or not use ISNULL(NULLIF(Field1, ''))?

texts:
In the past I've seen use of something such as

SELECT ISNULL(NULLIF(Field1, ''), 'NewValue')

to tersely get a fallback value.

However, since the advent of CASE in TSQL, we've favored something more like

SELECT CASE Field1 WHEN '' THEN 'NewValue' ELSE Field1 END

Is one going to perform better than the other? What other reasons would there be to choose one over the other?


texts: SQL Analysis Services Microsoft ACE OLEDB provider is not displayed

texts:
I working on an SQL Analysis Services school project and my given database is an access database .mdb file when I trying to make a new data source for access file I can only see Microsoft.Jet.OLEDB.4.0 though I have already installed following drivers:

MS Office 2016 Microsoft.ACE.OLEDB.12.0
Microsoft.ACE.OLEDB.15.0
Microsoft.ACE.OLEDB.16.

I have been searching for a solution to use other providers, could not find any. So, I'm using Microsoft.Jet.OLEDB.4.0 on my project, but I'm following error:

The problem is I'm unable to get list of providers other than Microsoft.Jet.OLEDB.4.0, and if I use that getting error (as per above screenshot). Can anyone advise fix for this:
Here is my providers list:


texts: What is the best query to get the latest row version based on time?

texts:
I'm wondering what can be the most efficient query to get the latest rows from the following table.

CREATE TABLE AGE_DATA
(
  INSERTED_DAY TIMESTAMP(6),
  HOSTNAME VARCHAR2(255 CHAR),
  CONCEPT  VARCHAR2(255 CHAR),
  REPORTED_VALUE NUMBER(10)
);

If I want to know the latest data inserted by hostname, I have 2 approaches, and I want to know which would be the most efficient, depending the cases a) having ~100 rows, b) having more than ~1M rows.

with latest as (select hostname,max(inserted_day) inserted_day
from AGE_DATA group by hostname)
select *
from latest l
join age_data ad on (l.hostname = ad.hostname and l.inserted_day = ad.inserted_day)

or

select * from age_data
where (hostname,inserted_day) in (select hostname,max(inserted_day) inserted_day
    from AGE_DATA group by hostname)


texts: How think about column value decide to refer table in mysql

texts:
I'm designing table relationship.

I have an one question.

An Example)

Some customer want to contract car or bike.

Customer must contract an one only.

I'm thinking for this case table relationship.

1.

CREATE TABLE IF NOT EXISTS `contract` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '[ID]',
  `no` VARCHAR(20) NOT NULL COMMENT '[NO]',
  `type` TINYINT UNSIGNED NOT NULL COMMENT '[contract target]..1:car,2:bike',
  `type_id` INT UNSIGNED NOT NULL COMMENT '[target table id]',
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB;

2.

CREATE TABLE IF NOT EXISTS `contract` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '[ID]',
  `no` VARCHAR(20) NOT NULL COMMENT '[NO]',
  `car_id` INT UNSIGNED NOT NULL COMMENT '[car table id]',
  `bike_id` INT UNSIGNED NOT NULL COMMENT '[bike table id]',
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB;

car, bike table primary key is id.

case 1.

If type value is 1. Refer car table. 2 is bike table.

This case can't set foreign key constraint.
but size is smaller than case2.

case 2.

If contract target is car. bike_id column value is 0.

If you have a idea Please tell me.

Thank you.


texts: One table for article comments, video comments and photo comments or multiple tables?

texts:
First DBA question. Greetings people.

I hope I'm not duplicating a question but it's very hard finding database design related questions here (for multiple comment tables), as all 'design' questions share similar or identical titles, like 'Best way to design this?' or 'How do I correctly design this database?' - they're not very descriptive!

Anyway, I have car magazine website that I'm trying to add a commenting system to. The site has a few areas that need comments, which aren't related to each other at all. For example, there's articles, videos, photos and technical guides that will all have commenting functionality.

Obviously, I'd like to build this database the most efficient way possible from the start, as we now have quite a few users that will quickly fill up the rows. The question I have is, do I build a comment table for each section, like this?

articleComments
---------------------
commentID
userID
articleID
comment
timePosted

techguideComments
---------------------
commentID
userID
techguideID
comment
timePosted

photoComments
---------------------
commentID
userID
photoID
comment
timePosted

videoComments
---------------------
commentID
userID
videoID
comment
timePosted

Or would it be better to build one table, but structure it like so:

comments
-----------------------------
commentID
userID
...
articleID
techguideID
photoID
videoID
...
comment
timePosted

My initial thought is to build a table for each section, but seeming as I'm not having replies - single threaded comments only - maybe one chunky would do and maybe one table is easier to manage. I'm really not sure.

What are the pros and cons for each design?


texts:
I am having Centos 6.3. I have added IPs in the pg_hba file. I want to reload the pg_hba file but I am unable to reload.
Following are the location of my directories
/usr/pgsql-9.2/bin/pg_ctl
/home/ABC/XYZ/9.3/data/
I ran the following command but unsuccessful

sudo service postgresql restart
postgresql: unrecognized service

sudo /etc/init.d/postgresql reload
postgresql: unrecognized service

usr/pgsql-9.2/bin/pg_ctl -D /home/ABC/XYZ/9.3/data/ reload
-bash: usr/pgsql-9.2/bin/pg_ctl: No such file or directory

[root@manager100 ~]# su - postgres
-bash-4.1$ psql
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
-bash-4.1$ /** I cannot use select statement as there is no psql installed **/

[root@manager100 ~]# systemctl | grep postgres
-bash: systemctl: command not found

What should I do in order to reload the file.


texts:
How do I show a message if none of the rows returned have a specific value?
If all of the results in a report have a status of "OK", I want to return a message indicating that there were no warnings.
How can I do that?
Steve


texts: Is there any way to query postgres JSON/JSONB columns with ilike and indexing?

texts:
I have a table called user_data where I have JSON column called data which can have any key that can be given by application users. Here I wanted to do ilike query with using proper index.

Since keys in JSON field are created by application users I can't create index for specific key. Also tried JSONB column as well, we can have index for JSONB but it will be used for only equality check. I could't get appropriate solution for querying JSON/JSONB with using proper indexing when using ilike operator.

Note: As of now table have 4 billion rows. It will keep grow every day. I know partition/sharding may help me. But it would be better to know if there is any solution other than partition. Is any other data type like hstore solve this problem?


texts: Where should I store a custom ".my.cnf" file?

texts:
I am working in a Vagrant box (for development) and I have a provisioner script written in Bash. The script does install MySQL and setup a few things: basically it does what mysql_secure_installation does but without use it. See below:

sudo yum install -y mysql-server
sudo chkconfig --add mysqld && chkconfig mysqld on
mysql --user=root -e "DROP DATABASE IF EXISTS test;USE mysql;DELETE FROM mysql.user WHERE User='';DELETE FROM mysql.db WHERE Db='test' OR Db='test_%';CREATE USER 'root'@'%';GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;FLUSH PRIVILEGES;"

That worked as expected however I need to import a "large" SQL file and MySQL complaints and fails with the famous max_allowed_packet error.

I know the solution is to increase such value by editing the my.cnf file and add it to the section [mysqld].

Because the provisioner script should do this by default I am looking for the easiest way to do it which for me is: copy a file from the host to the guest while provisioning. I could use commands like sed to insert that line in the default my.cnf file but is overcomplicate something easy.

Having said that I did some research and from docs found here I can see a few places where I could store that custom file:

+---------------------+------------------------------------------------------------+
|      File Name      |                          Purpose                           |
+---------------------+------------------------------------------------------------+
| /etc/my.cnf         | Global options                                             |
| /etc/mysql/my.cnf   | Global options (as of MySQL 5.1.15)                        |
| SYSCONFDIR/my.cnf   | Global options                                             |
| $MYSQL_HOME/my.cnf  | Server-specific options                                    |
| defaults-extra-file | The file specified with --defaults-extra-file=path, if any |
| ~/.my.cnf           | User-specific options                                      |
+---------------------+------------------------------------------------------------+

I do know also that I could execute a query as follow (not sure if it's correct since I took it from here during my research):

SET GLOBAL max_allowed_packet = 1073741824;

Now that I have all that information above I wanted to ask: if I choose the latest option "User-specific options" and knowing that by default a vagrant user is created, knowing also that MySQL runs as root (I think so I might be wrong):

Where I should store the custom file? The vagrant user home directory? The root user home directory?

Is not clear to where ~/.my.cnf points to in the docs.

I am open to suggestions as well if the best way to do it is by running the query or by putting the needed config in some other path just let me know.

The MySQL version is as shown below:

[root@appserver home]# mysql --version
mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1


texts:
Please advice how can I audit all actions of specific user in SQL Server 2008 EE? If it can be traced and written to log file? If the audit has the levels?

With regards!


texts:
I have a After Update trigger where trigger insert a value in same table to find a duration column.My goal is whenever end_time value is inserted then trigger will find duration in hh:mm:ss format and insert into duration column.
Problem- Trigger causes so many deadlock therefore blocking another process.

Column datatype informatiom..
Start_time datetime, End_time datetime, Duration time(3)

ALTER TRIGGER [dbo].[GET_DURATION] ON [dbo].[INBOUND_CALL_xxx]
AFTER UPDATE AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Begin Transaction

               UPDATE INBOUND_CALL_xxx
               SET DURATION = convert(time(0),(END_TIME- START_TIME))
               FROM INBOUND_CALL_xxx with (nolock)
Commit;


texts: MySQL Regex matching newline or end of string

texts:
I have a text column in mysql which contains some values separated by a newline. I want now to query by these values and thought a regex should help me with that by doing something like:

SELECT * FROM table WHERE column REGEXP 'myValue';

That works so far except I also have that string also as part of a longer value like myValueLonger, then it would match both, so I need to add the newline as a separator. So I tried

SELECT * FROM table WHERE column REGEXP 'myValue[\n]';

But that returns zero results, it looks like multiline is somehow not supported in MySQL regex?

That is where I'm already hanging right now, at the end I would also need to add a way to allow newline or the total end of string, as I don't want to end empty newlines at the end and beginning of my text field.

Does anyone know how this can be accomplished in MySQL?

Edit:
Found the problem with the multiline stuff, I had \r\n breaks, so that works:

SELECT * FROM table WHERE column REGEXP 'myValue\r\n';

But still struggling with the issue how to do that OR the end of string.


texts: How can I select records which are between 2 dates based on a date in a field in PostgresSQL?

texts:
I have two tables:

1 with an Unique ID with the first time they visited (in date format)
and a historical addresses table with the same Unique ID, a start field (date field), an end field (date field) and the addresses fields.

What I like to do is create a table which get the addresses of the persons (Unique ID) based on the date where they lived at the time of the visit.

I'm able to select records with my own made up dates, but I would like to get them from the 'first time visit' field.

The SQL query with random dates:

SELECT * FROM historical_adresses
WHERE (startdate, enddate) OVERLAPS ('2013-05-15'::DATE, '2013-05-15'::DATE);

Who can help me do this in PostgresSQL?


texts: Oracle DB Server with Multiple Databases - SGA and PGA size

texts:
I have a Oracle DB server where there are 3 Databases, therefore each one was created with its own SGA and PGA sizes (SGA was approx. 20 GB and PGA is 30-40% of SGA) Total memory on the server is 64 GB.
Now I am checking the server memory consumption and it at 97%. Obviously because each one takes approx. 20 GB of RAM. Now I have to create a new Database and there is not enough RAM.
My question is more philosophical than technical, how should I restructure the SGA/PGA targets? what are the points I should keep in mind about each database when setting their SGA/PGA?


texts:
Oracle here. I have the following tables:

[orders]
===
order_id : integer constraint pk_orders primary key using index
order_name : varchar2(40 char)
order_ordered_by : integer constraint fk_shoppers references accounts
order_total : number(10,2) not null
order_status : char not null

[line_items]
===
line_item_id : integer constraint pk_line_items primary key using index
order_id : integer not null constraint fk_line_items_orders references orders on delete cascade
product_id : integer not null constraint fk_line_items_products references products
line_item_quantity : integer not null

[products]
===
product_id : integer constraint pk_products primary key using index
product_name : varchar2(40 char)
product_category : varchar2(10 char)
product_available_on : date

I am trying to write a query that updates orders and sets their statuses to "ORDERED" where:

the orders.order_status is currently "PENDING"; and
the products.product_category is currently "COFFEE"; and
the products.product_available_on is currently less than or equal to the present time (now)

My best attempt thus far does work and get the job done:

UPDATE orders
SET status = 'ORDERED'
WHERE order_id IN (
    SELECT DISTINCT orders.order_id
    FROM orders
    INNER JOIN line_items ON line_items.orderId = orders.order_id
    INNER JOIN products ON line_items.product_id = products.product_id
    WHERE
        orders.status = 'PENDING' AND
        products.product_category = 'COFFEE' AND
        products.product_available_on <= CURRENT_DATE
);

Again, this does work, however its pretty slow so I'm trying to see if there is a way I can rewrite this to be more efficient where I'm not using the IN condition (I've read in several places that IN can cause performance issues in Oracle). Is there any way to accomplish rewriting this query without the IN so I can compare performance?

Please note: changing the tables (tweaking their fields, adding constraints/indexes/anything) is out of the realm of possibility in my particular use case!


texts: Performance Impact of Constant Login / Logout SQL On High Latency Connection

texts:
I'm reviewing the performance of a .NET application that sends 1,000s of consecutive select statements to a Microsoft SQL Server 2014 Instance. Ping time to server from client is 70 ms or higher across a WAN.

The client application is using Ole DB to connect to the SQL database.

Almost every single query SQL Profiler shows this pattern

Audit:Login
SQL:BatchStarting (Single SELECT statement)
SQL:BatchCompleted
Audit:Logout
RPC:Completed - exec sp_reset_connection

This repeats thousands of times. Is it possible the application could be re-coded to avoid constant login / logout, saving expensive network calls

Or is it expected to see this information for every query.


texts: Azure Database Size Same Before and After Deleting Most of the Rows

texts:
I have a database hosted on Azure which is showing as nearly full, it is at 1017MB/1024MB. The table contained around 27000 rows, I have deleted 20000 of those rows to try and free up space, however the amount of available space is the same as before I deleted the rows.

The Database only has one table the create script is below as you will see it only has text stored in the database and should not be as high as this.

CREATE TABLE [dbo].[VersionLog](
[Id] [varchar](255) NOT NULL,
[Date] [datetime] NOT NULL,
[WanIP] [varchar](255) NOT NULL,
[LocalIP] [varchar](255) NOT NULL,
[ShopName] [varchar](255) NOT NULL,
[MachineName] [varchar](255) NOT NULL,
[ApplicationName] [varchar](255) NOT NULL,
[VersionNumber] [varchar](255) NOT NULL,
[ServerName] [varchar](255) NOT NULL,
CONSTRAINT [Id] PRIMARY KEY CLUSTERED
 (
    [Date] ASC,
    [LocalIP] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

EDIT:

I re-indexed the table and have waited over the weekend to see if the database would shrink as per the suggestion below, although it has remained at the same size.

I have also attached a picture of how much each table in the database is using up. (in MBs)


texts: Issue with Patroni - postgres not starting

texts:
I am learning Postgres and I have installed 3 VMs as follows:
node1 - Installed Postgres and etcd here
node2 - Installed Postgres and etcd here
node3 - Installed etcd here
I have setup streaming replication between node1 and node2 and it worked fine (even etcd health showed as fine for all 3 nodes).
Then I installed and configured Patroni on node1 and node2 and when I first started it node2 was promoted to master and node1 was acting as a slave. At the time I could access psql from both nodes but a change (I inserted a row in a table) I did in the master was not propagated to the slave.
I then stopped Patroni on node1 (slave) and restarted it but postgres on the slave is not starting and I cannot access psql on the slave either now. I don't know whether I should be able to access the DB from the slave or not when using Patroni (I could with streaming replication), i.e. should the postgres service remain switched off by design on the slave when you have Patroni? As from my understanding if it finds that there is a master holding a lock, postgres on the slave will fail to start.
I then stopped postgres on master as I thought that it will failover to node 1 (and node1 to become a master) but no automatic failover occurred. Furthermore, postgres service is not starting on either now (not even on node2 - but I can connect using psql on node2 - which still seems to be the master). Should postgres service be showing as running on the master or it also fails to start by design and one should only check the status of Patroni? Patroni is active and running on both nodes.

When I try to start postgres on node2 (the node holding the lock):

Log on node1 (the slave):

etcd is working fine:

From my research I found that pg_rewind might help to get the slave in sync with my master, however, I'm not sure whether that is even my issue.
What do I need to do to fix this situation?

I mainly need to start postgres on the master (I have no idea how I can use psql but postgres service is failing to start on the master).
I need the slave to be able to promote itself to master (through Patroni), if the master fails.

My config files seem fine (especially since it was working fine prior to trying to switch off one instance for a failover to occur)


texts: Running total until specific condition is met

texts:
I have a question about running totals, I know there are several approaches. However I have slight modification of it and I'm struggling to find the right way.

So I have orders, each order has its unique id and quantity. Quantity can go up (buying) and down (selling). Ids go in order but are not 'dense', so there might be a gap between two subsequent orders. I need to select all orders that represent the oldest position. Position is a continuous range of orders, overall sum of those is 0, example (id, qty): (1, 1) (2,2) (3,-3) (4,1) (5,-1) - there are two positions, the oldest position consists of orders with ids 1,2,3.

My current approach:

Schema

CREATE TABLE [dbo].[orders](
    [id] [int] NULL,
    [qty] [int] NULL
) ON [PRIMARY]

Solution

create table #or (id int, qty int, rn int)
create clustered index ix_orid on #or(rn)
insert into #or
select *, ROW_NUMBER() over(order by id) as rn from dbo.orders;

with position as (
select o1.*, o1.qty as s from #or o1 where rn = 1
union all
select o1.id, o1.qty, o1.rn, o1.qty + position.s
from #or o1
join position
on o1.rn = position.rn + 1
where position.s > 0
)
select * from position
option (maxrecursion 0)

drop table #or

My concern is that I create big temporary table, for all orders. And all time goes into creating this table. I have about ~3.000.000 rows in dbo.orders and ~15.000 is the result set. My current idea is to limit insert into #or using top hint with reasonable amount, and run it several times until we are done. But I wonder if it can be done easier?


texts: Is there a way to catch a dbcc checkident command

texts:
A colleague of mine noticed incorrect ids generated for inserted items. Most probable cause: some script issues a command like the following:

dbcc checkident('dbo.table', reseed, 1)

I have thought about setting up a DDL trigger to catch this commands, but this list of DDL events does not seem to include anything related to reseeding.

Question: is there a way to catch such commands in a similar way DDL triggers catch various database schema changes?


texts:
I have seen many options for doing such, however most of them involve using bcp or using exec master..xp_cmdshell which I am unable to use on my server (security restrictions).

I was looking at other alternatives, such as calling a powershell script from a stored procedure, or maybe SSIS.  Since I am unable to use the above 2 options, what is the quickest/cleanest option, to run a stored procedure and export the results to a .csv file?

EDIT
I get an error of:

  Invalid object name 'tempTest'.

When I try this syntax:
Select
    name1
    ,name2
    ,add1
    ,add2
    ,phone1
    ,phone2
    Into temptest
    From prod

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Master',
@from_address = 'haheho12@gmail.com',
@recipients='haheho12@gmail.com',
@subject='CSV Extract',
@body='See attachment',
@query ='SELECT name1,name2,add1,add2,phone1,phone2 FROM temptest',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'CSV_Extract.csv',
@query_result_separator = ',',
@query_result_header = 1


texts: How to export an image column to files in SQL Server?

texts: I will migrate from a database. There is one column of type image that I would like to export to binary files on the file system. One file for each record. How can I do this with SQL Server?


texts: Explaining the problem with a column of values from a non-mutually exclusive domain

texts:
I'm trying to convince the powers-that-be that their current database design is risky and inefficient. I'd like to verify whether my assumptions are correct, and I'd like help in finding technical literature and/or terminology that explains the problem in a way that sounds more convincing than my "It's bad, mmmkay."

It's parcels data. The current structure is as seen here:

PARCEL_ID   PARCEL_TYPE     RIGHTS_SURFACE      RIGHTS_MINERAL
46          EASEMENT            0                   0
47          DISPOSED            1                   0
47          OWNED               0                   1

I see two, related issues with the PARCEL_TYPE column:

1) A single column identifies ownership. However, we may own mineral rights but not surface rights. In which case, the current workflow is (as above) to create a second entry for that parcel.  The mineral rights and surface rights columns do not actually indicate ownership; they only indicate whether the value found under PARCEL_TYPE applies to them.

2) The column refers to non-mutually exclusive characteristics: ownership (current, past, never--as it applies to the surface vs minerals) and access (whether we have an easement, lease, or permit). That is, we could have a parcel that we formerly owned that we now have a permit to access; with the current data structure, we could only indicate that by creating a copy of the parcel.

To address both of these issues at once, my recommendation would be to restructure the table like so:

PARCEL_ID   RIGHTS_SURFACE      RIGHTS_MINERAL      ACCESS
46          NOT OWNED           NOT OWNED           EASEMENT
47          DISPOSED            OWNED               OWNED

This would prevent duplicate entries from referring to the same parcel, and--as far as I can tell--it would keep the possible values in fields mutually exclusive.

Is this a better (or the best) way to structure the data? And, if so, to what database management standards is this change conforming?

EDIT:
More research has brought me suspect that this results from not normalizing to the 1st, 3rd, and Boyce-Codd normal forms (and that there are some domain/key normal form issues with the table as well). Maybe throwing around that terminology will be convincing enough.


texts: Create partitioned with move partitioned data from original table

texts:
I have big size of database almost 500GB and i just wanted to retain 1 years of data from some large tables and move others data to a different location, So I am trying this by creating partitions on table by date and time. After creating the partition, I can see the partitioned schema in the storage folder and I can also see the original data in the main table.
Is there any way to move the data from the original table while creating a partition or only option is to archive the data.
Any suggestion would be very helpful.


texts:
I have a stored procedure which returns a weekly result from input parameters start date and end date. I am getting multiple rows in the result.

Requirement: I want to add the results of columns in a single column. Below is my final query. From this I want to concatenate the result:

SET @sqlText =
    N'SELECT ['+REPLACE(@columns,',','],[') + ']' +
    '
    FROM dbo.timesheet
    WHERE
        month ='''+ @strMonth+'''
        AND [Task ID] IN
        (
            SELECT TaskID
            FROM ManageTasks
            WHERE
                TeamMemberUserID ='+  Cast(@UserID AS VARCHAR(max)) +
        ')'

--Here I want to add the columns result and need to show as single column

PRINT @sqlText
EXEC (@sqlText)


texts: What's the difference between varchar(255) and varchar(max)

texts:
I was asked an interesting question today. I've thought about it before but couldn't come up with a good answer.

If the values of a column are only going to be <= 255 characters, is there any real difference between using varchar(255) and varchar(max).

I realize that varchar(255) has a limit of 255 characters and varchar(max) 2 gb but they only use as much space as needed for the actual values stored. I'm not counting the extra space needed for the var part because as far as I know it's the same (or is it?). So in terms of performance & space is there a difference?


texts: Where can I download oracle 10g software for solaris?

texts:
I need 10g RAC software (Solaris 11 SPARC) for an audit.

It's no longer on OTN.
I've opened an SR, but it is taking forever then they gave me the wrong O/S.

Do you know of any other place to get it?


texts: How to implement sql replication when subsciber cannot acces the publisher

texts:
I have 2 servers

Server A (Publisher) and Server B (Subscriber)

However server A can access Server B but Server B cannot access Server A.

There are no VPN between these servers Server A can access B over the internet but B cannot do the same


texts: Using an in-memory table with a regular stored procedure

texts:
I am working on converting one of the tables in my database to an in-memory table in SQL Server Enterprise 2014.  Let's call the table in question table A.  When I convert table A from a regular table to the in memory table I use the query

CREATE TABLE A(
    id1 SMALLINT NOT NULL PRIMARY KEY NONCLUSTERED,
    id2 BIGINT,
    date DATETIME )
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);

Table A is used by a regular stored procedure in my database, which simply reads id1 and id2 from Table A. Similarly, Table A is called by a regular function in my database which reads id1 from Table A.

The problem I am having is that when I change this table to in-memory, the stored procedure and/or the function which call Table A are no longer working when called through my application - but when I call them through the database, they work just fine.  Is there an issue with using regular functions and stored procedures on in-memory tables?

EDIT: I figured out the issue.  Turns out the application was executing

SET TRANSACTION LEVEL READ UNCOMMITTED

before every query of the in-memory table, and this is incompatible with in-memory tables, so there was an error not being thrown for that within the application.  Changed the database to take snapshots before reading from in-memory tables, solved the issue.


texts: ola-hallengren running full backups and log backups at the same time-- restore issues?

texts:
I recently ran across an issue trying to restore a db and log files.  DB restored without issues.  About 10 log files in, I received a message stating the LSN was out of sync and could not finish.  I used a tool that puts them in order, so that's not my issue.   I have my log backups set to run in 15 minute intervals.  This large database takes a couple of hours to perform a full backup. Could the log backups happening simultaneously during the full backup cause this situation?
The end result was not bad this time, but this is alarming.  I am trying to maintain a RPO of 15 minutes.
This little test resulted in an 8 hr RPO.... unacceptable.
Regards,
Tim


texts: SSRS - Install Reporting Server (incl IIS) separate from SQL Server

texts:
We have to migrate our existing SSRS database from an old SQL server to our new SQL cluster.

With this opportunity we would like to install de the reporting server on separate machine from the database.

I have installed SQL Server Reporting Service on a new server, but came to the conclustion that de tool uses the reporting server from the old database...

Is it possible to have the reporting on a separate server including the web server?


texts: SQL Server permissions to create arbitrary custom-types and stored procedures with them

texts:
What are the most restrictive permissions to allow for creation of arbitrary custom types and stored procedures with use the types in SQL Server?
For instance:
create type dbo.MyType as table (dt: DateTime2, primary key clustered (dt asc))
go
create procedure dbo.MyProcedure
@input dbo.MyType readonly
as
begin
  print 'done'
end


texts: Target/total memory is higher than max_server_memory

texts:
I have the max_server_memory setting configured to 141 GB on our production SQL Server and target/total server memory is 144 GB.

I thought max_server_memory represents how much memory I am willing to let the SQL Server engine use, if so why is it using more than that?

Running SQL Server 2012 SP3, physical machine with 160 GB memory installed, no other instances running on the box but we are also running Integration Services.

We have 75 user databases, for a total size of 928 GB. I am not sure if columnstore is being used, or how could I check that. This is a vendor application.

Right now target memory = 144,384,008 and total = 144,378,261.

The instance has been up since last night, 2019-05-01 22:20:21.097 EST. We had some infrastructure maintenance that required the instance to be stopped. However I have noticed for quite some time that total/target is always at 144 while max has always been set to 141. PLE during main business hours Monday-Friday is about an .5 to 1 hour on average, sometimes it jumps to 2 hours.


texts:
I am using sql Server 2008 R2.
We are using below query for reducing the msdb size.
use msdb exec sp_delete_backuphistory '01-MAR-2020'

When i execute this query in  server whose msdb size is 15GB no rows were deleted.
Some testing server with lesser rows it worked fine.
Why  is it  not working ? How to reduce the msdb size is there any other method?


texts: SQL query using CTE not working after database migration

texts:
We have recently migrated an application, and in the process we have migrated from SQL Server 2005 to SQL Server 2008 R2.

I have noticed that some SQL queries using CTE do not work anymore since.

I have tried simplifying the queries, but to no avail.
Even this following simple query (which gets the top parent in a hierarchy) ends up with the error message hereunder :

Code:

DECLARE @Companies TABLE (
      AccountId UNIQUEIDENTIFIER
      ,NAME VARCHAR(100)
      ,ParentAccountId UNIQUEIDENTIFIER
      )

INSERT INTO @Companies
SELECT
      AccountId
      ,NAME
      ,ParentAccountId
FROM AccountBase;

WITH CTE
AS (
      SELECT *
            ,0 [Level]
      FROM @Companies

      UNION ALL

      SELECT
            CTE.accountid
            ,CTE.NAME
            ,cmp.ParentAccountId
            ,LEVEL + 1
      FROM CTE
            INNER JOIN @Companies AS cmp ON CTE.ParentAccountId = cmp.AccountId
      WHERE
            cmp.ParentAccountId IS NOT NULL
      )
SELECT
      c.AccountId
      ,c.NAME
      ,c.ParentAccountId as TopParentAccountId
FROM (
      SELECT *
            ,MAX([Level]) OVER (PARTITION BY NAME) [MaxLevel]
      FROM CTE
      ) c
WHERE
      MaxLevel = LEVEL

Error message (msg 530):

  The statement terminated. The maximum recursion 100 has been exhausted
  before statement completion.

What could be possible reasons ?

Thanks in advance.


texts: Handling 'Variable not found' in a tokenised job step

texts:
I have an Agent job which is usually triggered by a WMI event.  The job has a token within it, which automatically takes a value from the WMI event and substitutes it into the step's Powershell code, e.g.:

$alert_wmi_state = '$(ESCAPE_SQUOTE(WMI(State)))';

The SQL Agent is configured with the Replace tokens for all job responses to alerts setting checked so, when the WMI event triggers, the substitution happens fine, and the code that is run is:

$alert_wmi_state = '7';

However, there is the possibility that this job could be run manually (i.e. right-click, Start Job at step...) and I would like my step code to handle this gracefully, so I have this logic further in my step:

if ($alert_wmi_state -eq ('$' + '(ESCAPE_SQUOTE(WMI(State)))')) {
    ...
}

...the theory being that, splitting $ from (ESCAPE_SQUOTE... would reasonably mean that this is not recognised as a token, and so does not get substituted, so the test would read literally:

if ($alert_wmi_state -eq '$(ESCAPE_SQUOTE(WMI(State)))') {
    ...
}

However, when running the job manually, the SQL Agent always errors out at the step (regardless if it's configured as a CmdExec job step type or a PowerShell job step type) before any of my code is run, with the error Variable WMI(State) not found.

My guess is that this is because the substitution of ESCAPE_SQUOTE happens before the step code is actually parsed/loaded, so the Agent never runs the step.

I expect I could have two steps, one with the token and one without, and have the failure action of the tokenised one start the non-tokenised one, but that duplication of step code is not desirable.

So, my question is, is there any way to gracefully handle the non-existence of a step token within the step code?


texts: How can I reinitialize some MyISAM tables without restarting MySQL?

texts:
I'm using rsync to copy some measurement data from one PC to the server.

Is it possible to load the new MyISAM tables without restarting the MySQL server?


texts: How to get archive re-do log file name containing the given SCN in Oracle?

texts:
Scenario:
Let us consider oracle 3 node RAC. RMAN Managed Recovery is configured between production and standby DBs. Let us assume that the SCNs of 3 nodes are S1, S2, S3 at some point of time respectively. Let Smax=max(S1,S2,S3)

Is there any possibilities to get the archive file name containing the given SCN, Smax in oracle so as to stop applying logs at standby database as soon as the archive logs of the file containing the given SCN, Smax are applied?


texts:
I am trying to update the values of a column to the results of a sub query. I get the error that the sub query is returning more that one row, which it should. It should be returning a column of values.

Update companies
set c_c_score = ( select t.summ
              from ( select sum(case when b.hot_tip = 1
                                     then ((@maxrank-c.rank)/(@maxrank-1)) * (b.score-3)
                                     else 0
                                     end) as summ
                     from companies a
                     left join table2 b
                         on a.id = b.company_id
                     left join table3 c
                         on b.table3_id = c.id
                     group by a.id ) AS t);

The sub query runs fine by itself.

    select a.id,  sum(case when b.hot_tip = 1 then ((@maxrank-c.rank)/(@maxrank-1)) * (b.score-3) else 0 end)
        from companies a
        left join table2 b
        on a.id = b.company_id
        left join table3 c
        on b.table3_id = c.id
        group by a.id;


texts: Auditing of database connections in SQL Server 2008 R2

texts:
We are finally planning to close down our last SQL Server 2008 R2 server. Before we can proceed with this we need to track what users are connecting to which databases. We have tried different options like using SQL Audit and checking the logins in the error log but all of these are missing the information to which database the connection are made. We did also look into using the LOGON trigger but as it is triggered when the user logins to the instance it will not track if the user afterwards change the database.
In newer versions I have used Extended Events for this kind of auditing but the sqlserver.login events is not yet available in this database version. It seems like it should be possible to use Profiler for this but I'm worried about continuously running trace specially on this old server. Are there any other options that I have missed or is Profiler the only option?


texts:
I have a role engineer1 | No inheritance, Cannot login | member of {priv_app}. The priv_app user ownes the tables in the schema. Users are members of engineer1 by default which only grants them select access. However, there are reasons the user may need to alter tables so they need to elevate their privileges to the priv_app role using set role priv_app. However this gives them the rights of the table's owner. Which is acceptable except that a user can then grant another user DML privileges on any tables.

Is there a away to prevent the table owner from granting privileges? If not what would be the correct way to architect security groups so that users can alter tables but not grant privileges.


texts: Backup stored procedure for full, differential and log backup

texts:
I wrote a simple stored procedure for backing up FULL, Differential and LOG backup. This is my stored procedure:

Alter PROCEDURE BackupDB
(
    @BaseLocation   varchar(1024),
    @BackupType     varchar(32)

)
AS
BEGIN

    Declare @DBName     varchar(255) = db_name()

    Declare @FileName   varchar(256)

    Declare @Date datetime = getdate()
    Set @BackupType = UPPER(@BackupType)

    Set @FileName = @DBName + '_'  + Cast(Year(@Date) as varchar(4)) + '_' +
                    Right('00' + Cast(Month(@Date) as varchar(2)),2) + '_' +
                    Right('00' + Cast(Day(@Date) as varchar(2)),2) + '_' +
                    Right('00' + Cast(DatePart(hh,@Date) as varchar(2)),2) + '_' +
                    Right('00' + Cast(DatePart(mi,@Date) as varchar(2)),2) + '_' +
                    Right('00' + Cast(DatePart(ss,@Date) as varchar(2)),2) + '_' +
                    Right('000' + Cast(DatePart(ms,@Date) as varchar(3)),3) + '_' + UPPER(@BackupType) +
                    (Case When @BackupType = 'FULL' Or @BackupType = 'DIFFERENTIAL' Then '.BAK' Else '.TRN' End)

    Declare @FullPath varchar(1280) = ''

    If Right(@BaseLocation,1) <> '\'
    Begin
        Set @BaseLocation = @BaseLocation + '\'
    End
    Set @FullPath = @BaseLocation + @FileName
    Print @FullPath

    If @BackupType = 'FULL'
    Begin
        Backup database @DBName To Disk = @FullPath
    End
    Else If @BackupType = 'DIFFERENTIAL'
    Begin
        Backup database @DBName To Disk = @FullPath WITH DIFFERENTIAL
    End
    Else If @BackupType = 'LOG'
    Begin
        BACKUP LOG @DBName
             TO Disk = @FullPath
    End

END
GO

I ran my stored procedure and it seems to work perfectly for parameters passed in. Now the next step is to create a SQL JOB for this. However before I do this, I want to know is this correct procedure? I've read many articles and they all seem to point out one line "Test backup strategy."

Now I would be creating a SQL JOB weekly, daily and every 5 minutes i.e Weekly full backups, Daily Differential and Log backups every 5 minutes.

Do you think this stored procedure is incorrectly written or do you think it should be all fine for my scenario?


texts: Restoring a database back to a point in time before full backup was made

texts:
A problem with one of our database tables arose at the end of last week and I wanted to restore that database to a server in order to retrieve the table. We only have the last backup, and a full set of transaction logs before the error occurred, up to and after the full back was taken.

However, when I restore, I can only use the transaction logs to roll forward, not back. Is what I'm trying to do possible?

Sequence of events:

Error occurred: Friday April 5 at 12:00
Full backup taken: Sunday April 7 at 02:00

Many thanks.