Timezones in Oracle 10 I have an application that stores its timestamps in Oracle using UTC (Coordinated Universal Time). Another application reads these timestamps from the database, but there is no convenient way to convert the timestamps to another timezone automatically (from within that application). Is it possible to adjust session settings in Oracle to return timezone adjusted timestamps for SELECT queries?

Since the data is stored in a TIMESTAMP column, you cannot automatically convert it.  You'll have to write some code that tells Oracle what time zone to treat the data as being stored in the GMT time zone and tell it that you want it converted to the session's time zone.  You would get the automatic behavior you are looking for if you used the TIMESTAMP WITH LOCAL TIME ZONE data type instead.  If you used the TIMESTAMP WITH TIME ZONE data type, you would avoid having to tell the database what time zone the timestamp comes from when you write the conversion.

Assuming that your session sets its time zone properly

ALTER SESSION SET time_zone = '-8:00'

for example, would tell Oracle that the current session is 8 hours before GMT (currently the Pacific time zone), if the data is stored in a TIMESTAMP column, you'd need something like

FROM_TZ( ts, 'GMT' ) AT TIME ZONE sessiontimezone

If the data is stored in a TIMESTAMP WITH TIME ZONE column, you'd just need

ts_tz AT TIME ZONE sessiontimezone

If the data is stored in a TIMESTAMP WITH LOCAL TIME ZONE column, you would just select that column.

An example

SQL> ed
Wrote file afiedt.buf

  1  create table foo(
  2    ts timestamp,
  3    ts_tz timestamp with time zone,
  4*   ts_local_tz timestamp with local time zone )
SQL> /

Table created.

SQL> ed
Wrote file afiedt.buf

  1  insert into foo
  2    values( sys_extract_utc( systimestamp ),
  3            systimestamp,
  4*           systimestamp )
SQL> /

1 row created.

SQL> select from_tz( ts, 'GMT' ) at time zone sessiontimezone ,
  2         ts_tz at time zone sessiontimezone,
  3         ts_local_tz
  4    from foo;

FROM_TZ(TS,'GMT')ATTIMEZONESESSIONTIMEZONE
---------------------------------------------------------------------------
TS_TZATTIMEZONESESSIONTIMEZONE
---------------------------------------------------------------------------
TS_LOCAL_TZ
---------------------------------------------------------------------------
02-MAY-13 01.59.03.171000 PM -08:00
02-MAY-13 01.59.03.171000 PM -08:00
02-MAY-13 01.59.03.171000 PM

In response to the comment below from DylanKlomparens -- you cannot just apply the AT TIME ZONE to a TIMESTAMP and expect to get the proper result so I'm not sure that I understand what you are seeing.  If you just to an AT TIME ZONE on a plain TIMESTAMP, the timestamp is simply treated as having the time zone that you specified.  It doesn't change the time.  It also doesn't depend on the session time zone.  In all these cases, if ts represents a UTC time, the PST version represents the wrong time-- there should be an 8 hour difference between the starting and ending values.

SQL> select ts from foo;

TS
---------------------------------------------------------------------------
02-MAY-13 09.59.03.171000 PM

SQL> select ts at time zone 'PST' from foo;

TSATTIMEZONE'PST'
---------------------------------------------------------------------------
02-MAY-13 09.59.03.171000 PM PST

SQL> alter session set time_zone = '-0:00';

Session altered.

SQL> select ts at time zone 'PST' from foo;

TSATTIMEZONE'PST'
---------------------------------------------------------------------------
02-MAY-13 09.59.03.171000 PM PST

SQL> alter session set time_zone = '-4:00';

Session altered.

SQL> select ts at time zone 'PST' from foo;

TSATTIMEZONE'PST'
---------------------------------------------------------------------------
02-MAY-13 09.59.03.171000 PM PST

In response to Vincent's comment-- A TIMESTAMP WITH LOCAL TIME ZONE does adjust automatically to the session's time zone regardless of the server's time zone.  As I adjust my session's time zone, the results change as well.

SQL> select ts_local_tz from foo;

TS_LOCAL_TZ
---------------------------------------------------------------------------
02-MAY-13 05.59.03.171000 PM

Elapsed: 00:00:00.00
SQL> alter session set time_zone = '-0:00';

Session altered.

Elapsed: 00:00:00.00
SQL> select ts_local_tz from foo;

TS_LOCAL_TZ
---------------------------------------------------------------------------
02-MAY-13 09.59.03.171000 PM

Elapsed: 00:00:00.00
SQL> alter session set time_zone = '+4:00';

Session altered.

Elapsed: 00:00:00.00
SQL> select ts_local_tz from foo;

TS_LOCAL_TZ
---------------------------------------------------------------------------
03-MAY-13 01.59.03.171000 AM

Elapsed: 00:00:00.00


File stream in SQL Server 2008 Hello all I referred to this https://stackoverflow.com/a/5613926/842112

In that they mentioned this

  if your pictures or document are typically over 1 MB in size, storing
  them in the filesystem is more efficient (and with SQL Server 2008's
  FILESTREAM attribute, they're still under transactional control and
  part of the database)

Now my question is how to use File stream attribute in SQL Server 2008? I referred to http://msdn.microsoft.com/en-us/library/cc949109.aspx but I don't get how to use this. So please suggest me how to use this I want to store images in a database table. Thank you.

To use FILESTREAM, it has to be configured at the OS and instance level. At the OS level, FILESTREAM is enabled either during the installation of SQL Server 2008 or by running SQL Server Configuration Manager. Once FILESTREAM has been setup, you have to create a FILESTREAM filegroup. You then have to create a data container in the FILESTREAM filegroup. Once all these steps are done, you can create a table in your DB to use FILESTREAM.

(From your link) Here are the steps to configure FILESTREAM at the OS level, if not done during installation:

On the Start menu, point to All Programs, point to Microsoft SQL
Server 2008, point to Configuration Tools, and then click SQL Server
Configuration Manager.
In the list of services, right-click SQL Server Services, and then
click Open.
In the SQL Server Configuration Manager snap-in, locate the instance
of SQL Server on which you want to enable FILESTREAM.
Right-click the instance and then click Properties.
In the SQL Server Properties dialog box, click the FILESTREAM tab.
Select the Enable FILESTREAM for Transact-SQLAccess check box.
If you want to read and write FILESTREAM data from Windows, click
Enable FILESTREAM for file I/O streaming access.
Enter the name of the Windows share in the Windows Share Name box.
If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data. Click Apply.

Next configure the SQL instance for FILESTREAM access. Run the following query on the instance (SSMS):

EXEC sp_configure filestream_access_level, 2; -- Options are 0/1/2
GO
RECONFIGURE;
GO

Now add the FILESTREAM filegroup to your DB:

ALTER DATABASE Production ADD
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM;
GO

ALTER DATABASE Production ADD FILE (
       NAME = FSGroup1File,
       FILENAME = 'F:\Production\FSDATA') -- Location for your data container
TO FILEGROUP FileStreamGroup1;
GO

You can now use FILESTREAM in your tables.


Do all Relational DBMSes store table tuples in a clustered index based on primary key by default? So i was reading MySQL innoDB, which apparently stores table data by default on a clustered Index (b+tree) based on primary key, and the tuples are in the leafs of that b+tree

https://blog.jcole.us/2013/01/10/btree-index-structures-in-innodb/

i was wondering do all the famous Relational DBMSes like PostgreSQL, Oracle, SQL server, SQLite,  store it the same way? by making a clustered Index (b+tree) based on primary key and store data on the leafs of tree?

and does Clustered index on primary key in databases basically mean storing the table based on primary key on a B+tree table?

(sorry if this is a general question but i cant make 4-5 seperate questions asking the same for each of the databases i mentioned, if you only know about one or few of them please tell)

also if its a database does not use clustered by default, can you explain how does it structure the files, for example do they make a b+tree on primary key which the leafs point to some sort of address inside a heap or...?

EDIT :

so far we got the answers for all of them except SQLite, if anyone have any info on how tables are actually stored in SQLite by default please do tell.

Do all the famous Relational DBMSes like PostgreSQL, Oracle, SQL server, SQLite, store it the same way? by making a clustered Index (b+tree) based on primary key and store data on the leafs of tree?

No, not all. Lets take them one by one:

MySQL. MySQL has several "engines" and depending on what engine a table is defined to use, the storage is:

InnoDB: Yes, what you describe, the table data is stored in a clustered index, with the index based on the primary key columns on the table (and if there isn't a PK defined, on the first UNIQUE index with non-null columns and in the lack of that, on a secret 6-byte internal column).
MyISAM: No, the table is a heap, not a clustered index.
other engines: (NBD, Blackhole, CSV, etc) No, I think none of them uses a clustered index (except maybe NBD, not sure)
TokuDB: Yes. Similar to InnoDB but you may define more than one clustered indexes!

SQL Server: Yes, the default behaviour for tables that have a PK is to be clustered on the PK. This can be overridden though by declaring that the PK is NONCLUSTERED. You can also define another index (not the PK) to be the clustered index of the table. If the PK is deined as NONCLUSTERED and none of the indexes is defined as CLUSTERED, then the table is a heap. In recent versions, a 3rd option was added (besides clustered index and heap):  columnstore which is a different way of organizing the table data.
PostgreSQL: No. All the tables are heaps, period. You can create additional indexes of various types (btree, hash, gin, gist, brin, etc) but the table data are stored in a heap.
Oracle: No. By default the tables are heaps unless created as Index Organized Tables (Oracle's term for clustered indexes).

Additional clarifications about table data organization:

Clustered Index: There is a btee+ index - based on some column(s) - and the leafs contain the table's data.
Heap: There is no btree index for the heap (other indexes like the PK may still use a btree). The data are stored as unordered lists of records. When a new row is inserted, it is usually added in a disk page with available space. Rows are referenced by some special Page/RowID (the details are implementation dependend and surely differ from DBMS to DBMS), so other indexes will include this reference.
Columnstore: Types of structure where data are stored as columns rather than rows.

Some more details can be found in the documentation of each DBMS and in:
- Wikipedia: Database Storage Structures
- Wikipedia: Columnstore databases


SQL Server DeleteFolder Job ran successfully but it doesn't deleted the folder I have a job to delete past 15 days folder in SQL Server. The job run successfully and delete the folder in local SQL Server.

But it runs successfully and does not delete the folder in another SQL Server which is accessed by remote desktop connection.

Please suggest how to solve it. Is it permission problem?

The following is the code I use

{
--Script to enable the XP_CMDSHELL
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

DECLARE @path VARCHAR(256) -- path for backup files

DECLARE @cmd VARCHAR(8000)

DECLARE @folderName VARCHAR(256) -- filename for backup

SET @folderName = + GETDATE()-15 --(CONVERT(varchar(10), GETDATE()-15, 'yyyy_mm_dd'))  -- 15 days back date folder name
SET @folderName =  CONVERT(varchar(4),YEAR(@folderName)) + '_' + Right('00' + CONVERT(varchar(2),MONTH(@folderName)),2) + '_' + Right('00' + CONVERT(varchar(2),DAY(@folderName)),2)
SET @path = 'D:\FTP-Snapshots\' + @folderName -- Folder path
SET @cmd = 'RD ' + @path + ' /S /Q' -- Delete Files and the Folder IF all files deletions were successful.

EXEC master..xp_cmdshell @cmd
}

After 2 days of my hard times, I found that I have problem in "Permission". Since I am using "xp_cmdshell" which use window log in permission to delete the folder.
I ask my server guy to give me "Modify permission" which can delete the folder. It solve my whole problem. Thanks guys


Slow Performance of Parametrized Query in Excel due to Parameter Datatype I'm pulling Data from a SQL Server 2008 into Excel using a Parametrized ODBC Query created by the Excel Wizard. (Data-->getExternalData/FromOtherSources-->FromDataConnectionWizard-->ODBC DSN)

My query faces a huge Performance Problem as soon as I'm using a parameter in it. Assuming that the Parameter1 points at a Cell which hold E20140718 the following Query times out:

SELECT Top 20 *
FROM Viewname
Where RunId=?
Order by RelativeRWAVariation desc

However the following query has a runtime of only 2 seconds:

SELECT Top 20 *
FROM Viewname
Where RunId='E20140718'
Order by RelativeRWAVariation desc

The Parametrization in the first query should work correct as this style of queries works very good for selecting from other, a bit less complicated Views.

I'm pretty sure, that the problem I'm facing is described in this thread. This means that if i could somehow tell the SQL Server that the Parameter he's getting is a varchar(9) the parametrized query would probably only take 2 seconds aswell. Is there any way in Excel to give the Parameter a datatype or another solution to the problem?

Convert(Varchar(9),?) doesn't work, since the Parameter can't be used as part of a forumla according to Excel :(

Edit:
I just tried the same thing in SQL Server Management System and surprisingly (to me) faced the same Problem

The following statement takes ages in SSMS:

Declare @Run varchar(9);
Set @Run='E20140718';

Select Top 20 *
From Viewname
Where RunId= @Run
Order by RelativeRWAVariation Desc

While the second Query from above performs very good.

The issue appears to be parameter sniffing as the link you provided indicates.  I'm not sure how to fix the issue in Excel, but if you can figure out how to specify the datatype when it's creating the parameterized statement, that would fix it.

You have a few options that can help with the issue on the SQL Side.  I would only choose one of them and each of them have their price.  I think the best solution would be to use a stored procedure to mask it, but you will know your environment the best.

If you can modify the source table:

add a computed column of the NVARCHAR data type, equal to the RunID column, persisted.  Increased storage and a minor hit when RunID is inserted or updated but cleanest solution overall.  Modify your query from excel to use the computed column.
change the data type of the column to NVARCHAR.  This would probably impact everything in your environment, but would be the easiest to implement.
Create a stored procedure that accepts an NVARCHAR as an argument, then cast that to a different variable but with the correct data type, then return the desired results.  Clean solution, but requires a stored procedure be created and maintained.

To answer a question below (can't add a comment to my own answer, apparently).

You would create a stored procedure that accepts the NVARCHAR data type as the argument, then explicitly cast that value to a varchar type and use that in the query.  It saves the execution plan from doing a conversion on every row.  So something like this

CREATE PROCEDURE sptest (@inputparam NVARCHAR(50))
AS
DECLARE @queryparam VARCHAR(50) = CAST(@inputparam AS VARCHAR(50))
SELECT * FROM tabletest WHERE columnvalue = @queryparam


Percent of total: Is there a more efficient way than using a subselect? I have an existing ASSET_SUMMARY_VW view:

+-----------------------+---------------+
|      ASSET_TYPE       |     COST      |
+-----------------------+---------------+
| TRANSPORTATION        | 1,000,000,000 |
| TRANSPORTATION        |   500,000,000 |
| TRANSPORTATION        |   500,000,000 |
| ACTIVE TRANSPORTATION |   100,000,000 |
| ACTIVE TRANSPORTATION |    50,000,000 |
| STORMWATER MANAGEMENT |   500,000,000 |
+-----------------------+---------------+

I want to GROUP BY and calculate the percentage of each ASSET_TYPE compared to the total cost:

+-----------------------+---------------+------------------+
|      ASSET_TYPE       |     COST      | PERCENT_OF_TOTAL |
+-----------------------+---------------+------------------+
| TRANSPORTATION        | 2,000,000,000 |             75.4 |
| ACTIVE TRANSPORTATION |   150,000,000 |              5.6 |
| STORMWATER MANAGEMENT |   500,000,000 |             18.8 |
+-----------------------+---------------+------------------+

I can divide the COST by a subselect that gets the total cost:

SELECT
    ASSET_TYPE
    ,SUM(COST) AS COST
    ,SUM(COST/(
               SELECT
                   SUM(COST)
               FROM
                   USER.ASSET_SUMMARY_VW)
               ) AS PERCENT_OF_TOTAL
FROM
    USER.ASSET_SUMMARY_VW
GROUP BY
    ASSET_TYPE

However, it's occurred to me that using a subselect is inefficient. Is there a faster way of doing this?

with data as
(
  select 'TRANSPORTATION' as asset_type, 2000000000 as cost from dual union all
  select 'ACTIVE TRANSPORTATION' as asset_type, 150000000 as cost from dual union all
  select 'STORMWATER MANAGEMENT' as asset_type, 500000000 as cost from dual
  )
select
  data.asset_type, data.cost,
  trunc(ratio_to_report(data.cost) over () * 100, 1) as percent_of_total
from data;

ASSET_TYPE                  COST PERCENT_OF_TOTAL
--------------------- ---------- ----------------
TRANSPORTATION        2000000000             75.4
ACTIVE TRANSPORTATION  150000000              5.6
STORMWATER MANAGEMENT  500000000             18.8

RATIO_TO_REPORT still works with your updated data:

with data as
(
  select 'TRANSPORTATION' as asset_type, 1000000000 as cost from dual union all
  select 'TRANSPORTATION' as asset_type, 500000000 as cost from dual union all
  select 'TRANSPORTATION' as asset_type, 500000000 as cost from dual union all
  select 'ACTIVE TRANSPORTATION' as asset_type, 100000000 as cost from dual union all
  select 'ACTIVE TRANSPORTATION' as asset_type, 50000000 as cost from dual union all
  select 'STORMWATER MANAGEMENT' as asset_type, 500000000 as cost from dual
  )
select
  data.asset_type, sum(data.cost),
  trunc(ratio_to_report(sum(data.cost)) over () * 100, 1) as percent_of_total
from data
  group by data.asset_type
;

ASSET_TYPE            SUM(DATA.COST) PERCENT_OF_TOTAL
--------------------- -------------- ----------------
TRANSPORTATION            2000000000             75.4
ACTIVE TRANSPORTATION      150000000              5.6
STORMWATER MANAGEMENT      500000000             18.8


How do we reference to a collection in MongoDB? This is my first my first post in this forum. I am from RDBMS background and wanted to learn MongoDB which will help me in migrating our applications to MongoDB. I am creating two collections (customer & address).

Customer collection

Has generic information like name,gender,city,country,phone, address (which should be reference to another collection).

Address collection

Has an array of information like current address, office address, mailing address. I wanted to know, how can I add a reference to another collection? Considering this scenario, Customer Collection will be having an Address document which will have to contain reference to ids in Address Collection.

If someone could provide me a sample example for this model. It would be great. Thank you in advance.

I wanted to know, how can I add a reference to another collection?

Relationships in MongoDB represent how various documents are logically related to each other. Relationships can be modeled via Embedded and Referenced approaches. Such relationships can be either 1:1, 1:N, N:1 or N:N.

It's possible through Modeling Referenced Relationships and DBRefs.

Here i am going to discuss about Modeling Referenced Relationships.

Let us consider the case of storing addresses for customers. So, one customer can have multiple addresses making this a 1:N relationship.

Following is the sample document structure of customer document

{
"_id":ObjectId("52ffc33cd85242f436000001"),
   "name": "Gokulnath Kumar",
   "gender": "Male",
   "city": "Doha",
   "country": "Qatar",
   "Phone": "777 95057",
   "address": "100A, Doha,Qatar"
})

Following is the sample document structure of address document −

{
"_id":ObjectId("52ffc4a5d85242602e000000"),
   "address": "100A, Doha,Qatar",
   "office address": " 200B, Doha Qatar",
   "mailing address": "abc@gmail.com"
}

Let's first i am going to write down here Modeling Embedded Relationships, for your better understanding.Even as @JJussi has already told that we can write down all documents information in one collection through modeling Embedded Relationship.

  Note: The drawback is that if the embedded document keeps on growing too much in size, it can impact the read/write performance.

Modeling Embedded Relationships

Data in MongoDB has a flexible schema. Collections do not enforce document structure. Decisions that affect how you model data can affect application performance and database capacity.

This document describes a data model that uses embedded documents to describe relationships between connected data.

If your application frequently retrieves the address data with the name information, then your application needs to issue multiple queries to resolve the references. A more optimal schema would be to embed the address data entities in the customer data, as in the following document:

{
"_id":ObjectId("52ffc33cd85242f436000001"),
    "name": "Gokulnath Kumar",
   "gender": "Male",
   "city": "Doha",
   "country": "Qatar",
   "Phone": "777 95057",
   "address": [
{
"address": "100A, Doha,Qatar",
   "office address": " 200B, Doha Qatar",
   "mailing address": "abc@gmail.com"
}]}

Then Modeling Referenced Relationships

This is the approach of designing normalized relationship. In this approach, both the customer and address documents will be maintained separately but the customer document will contain a field that will reference the address document's id field.

{
   "_id":ObjectId("52ffc33cd85242f436000001"),
   "name": "Gokulnath Kumar",
   "gender": "Male",
   "city": "Doha",
   "country": "Qatar",
   "Phone": "777 95057",
   "address_ids": [
      ObjectId("52ffc4a5d85242602e000000"),
      ObjectId("52ffc4a5d85242602e000001")
   ]
}

As shown above, the customer document contains the array field address_ids which contains ObjectIds of corresponding addresses. Using these ObjectIds, we can query the address documents and get address details from there. With this approach, we will need two queries: first to fetch the address_ids fields from customer document and second to fetch these addresses from address collection.

>var result = db.customer.findOne({"name":"Gokulnath Kumar"},{"address_ids":1})
>var addresses = db.address.find({"_id":{"$in":result["address_ids"]}})

For further your ref Here, Here and Here


SQL Server evaluation period has expired OS: Windows 10 Enterprise 64bit

SQL Server: Microsoft SQL Server 2019 (RC1)

SQL Server Version: Developer Edition

I'm unable to start the SQL Server service due to the following error, "SQL Server evaluation period has expired".  What's strange about this is I'm already using SQL Server Developer edition.  Can anyone make sense of this?

Below are screenshots of the error and proof of my version.

Version:

ERROR

SSCM:

Event Viewer:

Log File:

Release candidates and community technology previews (CTPs) are considered evals and they expire 180 days after installation.  You may not be able to upgrade the instance as upgrades are not supported (see Upgrading SQL Server 2019 from CTP to RTM version: Is it even possible?)

Backup up your user database files (you'll have to copy the actual .mdf, .ldf, etc. files since you can't start SQL Server to do database backups), and then uninstall this instance, install the RTM version, and then attach the databases and create the necessary logins.  Alternatively, of course, you could just install another instance with the RTM version, but then you'd have to connect to a non-default instance, etc.


Importing BACPAC file from azure to SQL creating an empty database Exported Data-Tier_application from an Azure database to create a BACPAC of it.

  SQL editions I'm trying to use to import the BACPAC:

  SQL Server 2016 express.

  SQL Server 2017 Developer Edition.

I droped some views and procedures to fix error and it fixed everything.

I have now a 3GB BACPAC file and when i'm trying to import it(right click - import data tier application on databases menu from SSMS )  to my SQL Server 2016 or 2017, I receive this error:

with this error message:

  TITLE: Microsoft SQL Server Management Studio

  Could not import package. Warning SQL72012: The object

  [DATABASE_NAME_Data] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.

  Warning SQL72012: The object [DATABASE_NAME_Log] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.

  Error SQL72014: .Net SqlClient Data Provider: Msg
  7609, Level 17, State 5, Line 2 Full-Text Search is not installed, or
  a full-text component cannot be loaded. Error SQL72045: Script
  execution error.  The executed script: CREATE FULLTEXT INDEX ON
  [dbo].[Table]
      ([Nome] LANGUAGE 1033, [field_1] LANGUAGE 1033)
      KEY INDEX [PK_Table]
      ON [table];

it's not possible to have already these DATA and LOG because It's the first time I'm restoring it.

Changing .BACPAC file to .ZIP I could see a DATA folder with a lot of BCP files:

The bacpac file has 3GB.

Other answers from websites just say : "wait until the end" but the export ended 4 days ago.

I have another BACPAC file from a different database and I can import it to sql server normally. I've made the same steps for both BACPAC files when exporting.

I ran into that problem a few minutes and found a way to remove the fulltext stuff from a bacpac.

I found this powershell script (but fixed my backup by hand, because it was faster than trying to understand and use the script)

https://gist.github.com/JohnLBevan/6a8876f71a25aa600254d7127cf1a819

Rename your backup to zip
extract model.xml and Origin.xml
open model.xml with a text editor and look for "fulltext"
Delete all fulltext elements in the xml
set fulltext to false in the header
Calculate the SHA256 hash of the edited xml (I used hashtab for that)
Fix the hash/checksum in origin.xml
Add the updated files back to the zip
Rename back to bacpac and import
"Worked on my machine" - I hope it helps


Must declare the scalar variable question That is my first post here, need helps:
in a stored procedure I had some following code

CREATE PROCEDURE [dbo].[SP_getAvg] (
    @projectId INT
    ,@carrierId INT
    ,@fetchType VARCHAR(20)
    )
AS
BEGIN TRANSACTION GetDataSet * *

DECLARE @recAvg FLOAT * *
DECLARE @FACCT VARCHAR(20)
DECLARE @counter INT
DECLARE @carrierAlias AS VARCHAR(20)
DECLARE @tmpDate VARCHAR(20)
DECLARE @sql1 VARCHAR(500)

SET @counter = 1

IF @projectId > 0
BEGIN
    SELECT @FACCT = FACCT
    FROM projects
    WHERE projectId = @projectId
END

SELECT @carrierAlias = carrierAlias
FROM carriers
WHERE carrierId = @carrierId

SET @sql1 = 'SELECT @recAvg = ISNULL(AVG(cast(FWEIGHT as float)/case when CAST(FPIECES as float) = 0 then 1 else cast(FPIECES as float) end),0) from psorderh where (DATEPART(m, manifestDate) =' + cast(@counter AS VARCHAR(2)) + ') AND (DATEPART(yyyy, manifestDate) = DATEPART(yyyy, GETDATE()))' + CASE
        WHEN @projectId > 0
            THEN 'AND FACCT = ' + @FACCT
        ELSE ''
        END + CASE
        WHEN @carrierId > 0
            THEN 'and FCARRIER=' + @carrierAlias
        ELSE ''
        END

EXEC (@sql1)

PRINT @recAvg

......

I got this error message:

  Must declare the scalar variable "@recAvg".

where I did wrong?

Mostly to eliminate all the SQL injection and string handling issues, and remove things unused:

CREATE PROCEDURE [dbo].[SP_getAvg]
    @projectId INT
    ,@carrierId INT
    ,@fetchType VARCHAR(20)
AS
BEGIN
  DECLARE @recAvg FLOAT,
          @FACCT VARCHAR(20),
          @counter INT = 1,
          @carrierAlias VARCHAR(20),
          @sql NVARCHAR(MAX);

  IF @projectId > 0
  BEGIN
    SELECT @FACCT = FACCT FROM projects WHERE projectId = @projectId;
  END

  SELECT @carrierAlias = carrierAlias FROM carriers WHERE carrierId = @carrierId;

  SET @sql = N'SELECT @recAvg = ISNULL(AVG(cast(FWEIGHT as float)/case
      when CAST(FPIECES as float) = 0 then 1 else cast(FPIECES as float) end),0)
      from psorderh where DATEPART(MONTH, manifestDate) = @counter
        AND DATEPART(YEAR, manifestDate) = DATEPART(YEAR, GETDATE())'
    + CASE WHEN @projectId > 0 THEN N' AND FACCT = @FACCT' ELSE N'' END
    + CASE WHEN @carrierId > 0 THEN N' AND FCARRIER = @carrierAlias' ELSE N'' END;

    DECLARE @params nvarchar(max)
      = N'@counter int,@FACCT varchar(20),@carrierAlias varchar(20),@recAvg float OUTPUT';

    EXEC sys.sp_executesql @sql, @params, @counter, @FACCT, @carrierAlias, @recAvg OUTPUT;

    PRINT @recAvg;
END
GO


Get a specific row from a TSQL common table expression I have this tsql code to recursively get the whole workgroup hierarchy for a specific workgroup (WgID 283 here):

WITH GroupList(WgID,WgParentWgID) AS (
  SELECT WgID,WgParentWgID
  FROM tblplWorkGroup WITH (NOLOCK) WHERE WgID = 283 AND WgActive=1

  UNION ALL

  SELECT B.WgID,B.WgParentWgID
  FROM GroupList A
  INNER JOIN tblplWorkGroup B WITH (NOLOCK) ON A.WgParentWgID = B.WgID
  WHERE B.WgActive=1
) SELECT * FROM GroupList

Gives this:

WgID    WgParentWgID
283     315
315     272
272     4
4       0

I need to get just the second to last row. I can do it by changing the last select statement.

 SELECT *
 FROM GroupList
 WHERE WgParentWgID = (SELECT TOP (1) WgID
                       FROM GroupList
                       WHERE WgParentWgID = 0)

Gives:

WgID    WgParentWgID
272     4

However, if I want to get a nth row, it starts to get cumbersome. Is there a way to get a nth row from a WITH table expression? Or output row information in the output table too?

I tried converting the WITH expression to use a temporary table (where looks like I can get the nth row) but could not make the recursive functionality work. Any help is much appreciated.

There are a few ways to do this.  If there is a 1-2-1 relationship between the parents and widgets ( ie a parent only has one widget and vice versa ) you can just add a column in the CTE to count how many rows have passed through.  See the xlevel example below.

If that doesn't work for you ( and I would probably expect a parent to have multiple widgets ) then you could just throw all the results into a temp table using the IDENTITY function, then grab the third row.  Of course you need some kind of meaningful order for this to work, but see the #tmp table version below.

You could also use ROW_NUMBER(), available in SQL Server 2008 to add an arbitrary row number to the resultset.  Again you need some kind of order to give it meaning.  See the ROW_NUMBER example below:

;WITH GroupList(xlevel, WgID,WgParentWgID) AS (
SELECT 1 xlevel, WgID,WgParentWgID
FROM tblplWorkGroup WITH (NOLOCK) WHERE WgID = 283 AND WgActive=1

UNION ALL

SELECT xlevel + 1, B.WgID,B.WgParentWgID
FROM GroupList A
    INNER JOIN tblplWorkGroup B WITH (NOLOCK) ON A.WgParentWgID = B.WgID
WHERE B.WgActive=1
)
SELECT *
FROM GroupList
WHERE xlevel = 3
GO

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp

;WITH GroupList(WgID,WgParentWgID) AS (
SELECT WgID,WgParentWgID
FROM tblplWorkGroup WITH (NOLOCK) WHERE WgID = 283 AND WgActive=1

UNION ALL

SELECT B.WgID,B.WgParentWgID
FROM GroupList A
    INNER JOIN tblplWorkGroup B WITH (NOLOCK) ON A.WgParentWgID = B.WgID
WHERE B.WgActive=1
)
SELECT IDENTITY( INT, 1, 1 ) rowId, *
INTO #tmp
FROM GroupList
ORDER BY WgParentWgID, WgID
GO

SELECT *
FROM #tmp
WHERE rowId = 3
GO

;WITH GroupList(WgID,WgParentWgID) AS (
SELECT WgID,WgParentWgID
FROM tblplWorkGroup WITH (NOLOCK) WHERE WgID = 283 AND WgActive=1

UNION ALL

SELECT B.WgID,B.WgParentWgID
FROM GroupList A
    INNER JOIN tblplWorkGroup B WITH (NOLOCK) ON A.WgParentWgID = B.WgID
WHERE B.WgActive=1
)
SELECT *
FROM
    (
    SELECT ROW_NUMBER() OVER( ORDER BY WgParentWgID, WgID ) rowId, *
    FROM GroupList
    ) x
WHERE rowId = 3
ORDER BY rowId
GO

HTH


What is the best way to save a large array in database? Currently, I'm working on a simple CBIR (Content-based Image Retrieval) and I have a question.

What is the best way to save a large array on the database? For example, I want to save the results of Canny or GLCM (Gray-level Co-Occurrence Matrix) which is very large.

Also, if someone knows about Image Retrieval techniques, Please tell me is it a good way to save all of the information which mentioned algorithms produced for comparison or not? If it's not, so what is a good way?

Thanks.

It depends on where the data is coming from:

If you do not have a .NET application and you do have the data in a text file, then you can use the BCP.exe utility.
If you have a .NET application, then my preference would be to use a Table-Valued Parameter (TVP). I have an example of one (with .NET / C# and T-SQL code) in the following Stack Overflow answer:

How can I insert 10 million records in the shortest time possible?


Moving SSIS Packages to New Server I have a number of SSIS packages in a number of folders on MSDB SSIS on SQL Server 2008R2.
We are migrating to a new server host but remaining on SQL Server 2008R2.

How can I copy/migrate these SSIS packages to the new server? Is Dtutil the best option?

Yes, DTutil is still going to be the best option. I have a post, SSIS Package Extract from MSDB on how to use dtutil + the data in the packages table to generate the call(s) to export to disk. Instead of the file destination, you would substitute sourceserver. If you've used folders to organize your packages, then you'll need to create them beforehand.

Script posted here for posterity. This applies for 2008+ packages or 2012/2014 package that have used the package deployment model. With 2005, sysssispackagefolder would be replaced with sysdtspackages90 and sysssispackagefolders with sysdtspackagefolders90

;
WITH FOLDERS AS
(
    -- Capture root node
    SELECT
        cast(PF.foldername AS varchar(max)) AS FolderPath
    ,   PF.folderid
    ,   PF.parentfolderid
    ,   PF.foldername
    FROM
        msdb.dbo.sysssispackagefolders PF
    WHERE
        PF.parentfolderid IS NULL

    -- build recursive hierarchy
    UNION ALL
    SELECT
        cast(F.FolderPath + '\' + PF.foldername AS varchar(max)) AS FolderPath
    ,   PF.folderid
    ,   PF.parentfolderid
    ,   PF.foldername
    FROM
        msdb.dbo.sysssispackagefolders PF
        INNER JOIN
            FOLDERS F
            ON F.folderid = PF.parentfolderid
)
,   PACKAGES AS
(
    -- pull information about stored SSIS packages
    SELECT
        P.name AS PackageName
    ,   P.id AS PackageId
    ,   P.description as PackageDescription
    ,   P.folderid
    ,   P.packageFormat
    ,   P.packageType
    ,   P.vermajor
    ,   P.verminor
    ,   P.verbuild
    ,   suser_sname(P.ownersid) AS ownername
    FROM
        msdb.dbo.sysssispackages P
)
SELECT
    -- assumes default instance and localhost
    -- use serverproperty('servername') and serverproperty('instancename')
    -- if you need to really make this generic
    -- File extraction command line
    'dtutil /sourceserver localhost /SQL "'+ F.FolderPath + '\' + P.PackageName + '" /copy file;".\' + P.PackageName +'.dtsx"' AS cmd
    -- Move Package command line
    -- 'dtutil /SQL "'+ F.FolderPath + '\' + P.PackageName + '" /SOURCEUSER srcUserName /SOURCEPASSWORD $Hj45jhd@X /DestServer <servername> /MOVE SQL;"'+ F.FolderPath + '\' + P.PackageName + '" /DESTUSER destUserName /DESTPASSWORD !38dsFH@v' AS cmd
FROM
    FOLDERS F
    INNER JOIN
        PACKAGES P
        ON P.folderid = F.folderid
 WHERE
     F.FolderPath <> '\Data Collector'


Why query plan changes by number of rows I execute following two queries on an oracle database:

select * from qsn join plc on plc.fk_qsn = qsn.id where rownum < 48507

which returns result in less than a second and with following execution plan

and

select * from qsn join plc on plc.fk_qsn = qsn.id where rownum < 48508

which takes more than 10 seconds to execute and uses following execution plan

Can anyone help me understand the reason why this strange behavior happens?


Can database administrators change physical schema? I was reading the Chapter 1 of the 7th edition of Database System Concepts and ran into this:

• Schema and physical-organization modification. The DBA carries
out changes to the schema and physical organization to reflect the
changing needs of the organization, or to alter the physical
organization to improve performance.

Is it true that database administrators can access the physical schema and change it or have I misunderstood what is cited above?

One of the innovation of the relational model was to separate thr logical view that programmers see (tables of columns) from the physical storage (files on disks). To function the DBMS must be aware of both and hence must have the ability to amend both. So yes, if you define DBA as "someone who manages a DBMS" a DBA can change the physical schema.
Most DBMS implement a permission system. Different permissions will affect the creation of tables and the use of disks. In smaller teams it may be that one person has granted both of these permissions. In larger teams it becomes more likely the tasks are separated. You will find application DBAs defining tables and queries and operational DBAs looking after disks and CPUs. In businesses dealing with financial data or PII it may be that neither of those roles has access to any of the data.


Trouble with fixing orphan users I am creating a script where I have to fix orphan user (app service account) when db gets refreshed from Devl --> Test --> Prod. Since my company sells this software, it is essentially huge number of servers and I alone cannot manage it. So, I decided to create a script and give just that permission to application support people so that they can run that script which should fix orphan user.

This is the script

CREATE PROCEDURE [dbo].[usp_FixPermissionsAfterDBRefresh]
AS
BEGIN

    DECLARE @dbname VARCHAR(50)
    DECLARE @sqlstring NVARCHAR(100)

    DECLARE db_cursor CURSOR FOR
    SELECT name
    FROM sys.databases
    WHERE name NOT in ('master','msdb','model','tempdb')

    OPEN db_cursor
    FETCH NEXT from db_cursor into @dbname

    WHILE @@FETCH_STATUS = 0
    BEGIN

            SET @sqlstring = N'USE ' + @dbname
            EXECUTE sp_executesql @sqlstring
            print @sqlstring
            --EXEC sp_change_users_login 'Auto_Fix', 'SomeUser'
            IF EXISTS (SELECT dp.name  As Orphan_Users
                        FROM sys.database_principals dp
                        left join sys.server_principals sp
                        ON dp.sid=sp.sid
                        WHERE sp.name IS NULL
                        AND dp.type='S' AND
                        dp.name NOT IN ('guest','INFORMATION_SCHEMA','sys'))
            BEGIN
            ALTER USER SomeUser WITH LOGIN = SomeUser
            ALTER ROLE [db_backupoperator] ADD MEMBER [SomeUser]
            ALTER ROLE [db_owner] ADD MEMBER [SomeUser]
            END

        FETCH NEXT FROM db_cursor INTO @dbname
    END

    CLOSE db_cursor
    DEALLOCATE db_cursor
END
GO

If I run this script from any database except the one that got restored, the script exits successfully but won't fix orphan user and grant permissions. However, if I run this script from the newly refreshed DB then the script works just fine and fixes orphan user and grants necessary permissions. I have searched online but couldn't really get this script working. I will be setting this up as an agent job and grant permissions to application support who will be performing db refresh.

Please advise on what I am doing wrong.

The problem resides here:

SET @sqlstring = N'USE ' + @dbname
EXECUTE sp_executesql @sqlstring

What's happening is this executes - changes the database in the execution context for sp_executesql but will not change the parent context. Later on the code assumes this to be true by not continuing to use dynamic sql which means it'll run in the original database context.

IF EXISTS (SELECT dp.name  As Orphan_Users
                        FROM sys.database_principals dp
                        left join sys.server_principals sp
                        ON dp.sid=sp.sid
                        WHERE sp.name IS NULL
                        AND dp.type='S' AND
                        dp.name NOT IN ('guest','INFORMATION_SCHEMA','sys'))

ALTER USER SomeUser WITH LOGIN = SomeUser
ALTER ROLE [db_backupoperator] ADD MEMBER [SomeUser]
ALTER ROLE [db_owner] ADD MEMBER [SomeUser]

This can be fixed by making it execute in the proper context by extending the dynamic sql.


SQL Server 2008 R2 SQL Server Profiler and "exec sp_reset_connection" Yesterday i ran the "SQL Server Profiler" tool and outputted the data to a table, now when i look in it i find many "exec sp_reset_connection" rows and thats expected with our data access layer!

But what i can't explain is that some times it takes 8 seconds to execute this query...

What could cause the sp_reset_connection to delay that much?

You sure it is 8 seconds?

Starting with SQL Server 2005 (as stated in BOL) when a trace is saved to either a file or a database table, the duration column value is in microseconds

Update
Came across this post on SO: sp_reset_connection taking a long time to run


What's the best practice of inserting a collection of data into a SQL Server database 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?

You don't want either of those two options, though #2 is quite close. Option #1 is slow due to being row-by-row and each call being its own separate transaction. Even if you wrap all of the Stored Procedure calls into a single explicit Transaction, it will still be slower than a set-based approach.

Option #2 is flawed mainly for the additional work being done in both layers: joining the collection into a single string in the app layer, and splitting the string back out into elements in the data layer. However, the general structure of this idea — passing in just two params: ID and the collection of strings — is the way to go. You just need to use a Table-Valued Parameter (TVP) so that you can iterate through the collection as is, and receive it as a pre-populated Table Variable in SQL Server.

You do this by creating a User-Defined Table Type (which here could be a single NVARCHAR column) and using that as the parameter for the collection (hence the "Table"-Valued "Parameter").

The other piece to this puzzle is, on the app code side, creating a method that accepts int ID, List<String> _listobj and returns IEnumerable<SqlDataRecord>, and then use that method as the "Value" for the SqlParameter that is mapped to the TVP. Meaning, something like:

private static IEnumerable<SqlDataRecord> SendRows(List<String> RowData)
{
   SqlMetaData[] _TvpSchema = new SqlMetaData[] {
      new SqlMetaData("SomeSomething", SqlDbType.NVarChar, 4000)
   };
   SqlDataRecord _DataRecord = new SqlDataRecord(_TvpSchema);

   foreach (string _CurrentRow in RowData)
   {
      _DataRecord.SetString(0, _CurrentRow);

      yield return _DataRecord;
   }
}

Then, you would just pass in SendRows(_listobj) as the value for the TVP parameter. When you execute the SqlCommand, it will call that method and stream that collection into SQL Server.

The technique shown above requires no additional memory or processing, it just sends the collection as a collection. No need for DataTables or String.Joining or splitting :-).

To see a complete example code setup for this, please see my answer to the following Stack Overflow question: Pass Dictionary to Stored Procedure T-SQL.


Oracle Audit: Why Logons are less than 0.1% of logoffs? I see the following quantities of logins versus logoffs vs logoffs by cleanup in a database:

SQL> select action_name, count(*) qty
from Dba_audit_session
group by action_name
order by 1;  2    3    4

ACTION_NAME                        QTY
--------------------------- ----------
LOGOFF                         1946180
LOGOFF BY CLEANUP               754683
LOGON                             1026

It doesn't make sense that logons are less than 0.1% of logoffs.

Any ideas why?

It may depend on whether you are using connection pooling or not.

Also, do you realize LOGON and LOGOFF for a session are recorded in the same audit row? So here the LOGON is showing how many are still logged in right now.

Consider the following scenario.

SQL> select ACTION_NAME, username, count(*) from dba_audit_session group by action_name, username;

When you run this first time it shows LOGON 1.

Without logging off, try to connect as the same user as you have connected before.

SQL> select ACTION_NAME, username, count(*) from dba_audit_session group by action_name, username;

Now, it shows LOGON 1 and LOGOFF 1.

SQL> select ACTION_NAME, username, count(*) from dba_audit_session group by action_name, username;

Now, it shows LOGON 1 and LOGOFF 2.

Each time you are seeing the 1 currently logged in session as 'LOGON', and the (increasing) number of completed sessions as 'LOGOFF'.


How to view the user limits like max_queries or max_updates in MySQL? How to show that I've imposed limitations for queries per hour ?

Can't find anything online or notes. Would appreciate anybody's help on this.

MySQL 8.0 Reference Manual  /  ...  /  Setting Account Resource Limits (valid for 5.5+ at least).

  The server stores resource limits for an account in the user table row corresponding to the account. The max_questions, max_updates, and max_connections columns store the per-hour limits, and the max_user_connections column stores the MAX_USER_CONNECTIONS limit.

SELECT CONCAT('\'', user, '\'', '@', '\'', host, '\'') account,
       max_questions,
       max_updates,
       max_connections,
       max_user_connections
FROM mysql.user;


How do I assign an entire Active Directory group security access in SQL Server 2008? I would like to use integrated security with my internal application which is all on a domain.  Unfortunately, I've never been able to get this to work well.  I would like to assign an entire Exchange (Active Directory) Group a role in SQL Server for read/write access to certain tables.  That way I wouldn't have to create an operator whenever someone is hired or delete an operator whenever someone is fired.  Is this possible?  What steps would I take to do this?

Set the AD group as a login. And "login" means server level login not the AD concept of user/login. In SQL Server speak, this is a server level principal
Create a mapped user in. You shouldn't really permission a user directly on tables. And "user" means database user not the AD concept of user: in SQL Server speak, this is a "database level principal"
Add user to role (also a "database level principal")
GRANT permissions to the roles on the tables (a table or proc etc is a "securable")

Sample script

USE master;
GO
CREATE LOGIN [MYDOMAIN\APPLICATION SUPPORT] FROM WINDOWS;
GO
USE mydb;
GO
CREATE USER [MYDOMAIN\APPLICATION SUPPORT] FROM LOGIN [MYDOMAIN\APPLICATION SUPPORT];
GO
CREATE ROLE rSupport;
GO
EXEC sp_addrolemember 'rSupport', 'MYDOMAIN\APPLICATION SUPPORT';
GO
GRANT SELECT, INSERT,UPDATE, etc ON Mytable TO rSupport;
GO

sp_addrolemember is deprecated starting with SQL Server 2012, where ALTER ROLE should be used instead.


Difference between Aurora Global database and Aurora regional database with multiple read replicas? I am planning to migrate my 300GB RDS PostgreSql database to Aurora MySql using SCT and DMS. RDS Postgresql is in seven regions in current setup. Datapipelines are used to ingest data in these instances and keep them in sync. I was thinking that once I create global database in one region, I will be able to add secondary instances in six other regions. But, I read that global instances support just one additional secondary region.

The only relevant benefits for global database from the docs are:

Having an additional secondary instance will have faster replication
compared to having a read replica.
Faster disaster recovery as the
secondary instance can be promoted to primary under a minute.

Now I am wondering what is the difference between:

Having a Aurora global database in one region with writer and reader, adding a secondary region, adding five read replicas(from primary or secondary)
Having a Aurora regional database (with a writer and reader)  and adding six read replicas.

Aurora uses MySQL replication to replicate to a secondary data
while Aurora Global database uses underlying hardware to replicate your data from another region which doesn't affect your database performance compared to using read replicas.


Why does mongodb fail my json schema validation? I am using mongodb 3.6 and have below validator for the students collection:

rs1:PRIMARY> db.getCollectionInfos({name: 'students'})
[
    {
        "name" : "students",
        "type" : "collection",
        "options" : {
            "validator" : {
                "$jsonSchema" : {
                    "properties" : {
                        "name" : {
                            "bsonType" : "string",
                            "description" : "name"
                        },
                        "age" : {
                            "bsonType" : "int",
                            "minimum" : 10,
                            "maximum" : 30
                        }
                    },
                    "required" : [
                        "name",
                        "age"
                    ],
                    "bsonType" : "object"
                }
            },
            "validationLevel" : "strict",
            "validationAction" : "error"
        },
        "info" : {
            "readOnly" : false,
            "uuid" : BinData(4,"111r7z+rQpGoebZ1jAHO4g==")
        },
        "idIndex" : {
            "v" : 2,
            "key" : {
                "_id" : 1
            },
            "name" : "_id_",
            "ns" : "test.students"
        }
    }
]

it requires two properties name and age. Name must be a string and age must be between 10 and 30. But why does below insert fail?

rs1:PRIMARY> db.students.insert({age: 11, name: 'Mike'})
WriteResult({
    "nInserted" : 0,
    "writeError" : {
        "code" : 121,
        "errmsg" : "Document failed validation"
    }
})

As I am able to see you are getting the insert writeError with Error code : 121 like that

"nInserted" : 0,
    "writeError" : {
        "code" : 121,
        "errmsg" : "Document failed validation"

As per MongoDB blog documentation of @Jai Hirsch here for MongoDB document validation.
Sometimes you want completely free form documents and sometimes you don’t. In the 3.2 release of MongoDB the idea of document validation was introduced.
One area I have encountered problems in the past is with dates being inserted using different data types.

Example: (command line notation used for brevity)
The first team was using numeric dates:

 db.date.insert({date:20150321})

The second team was using string format:

db.date.insert({date:"03/21/2015"})

The third team was using ISO dates:

 db.date.insert({date:new Date('Mar 21, 2015')})

The communication issues that lead to this problem is a story for another time, but needless to say a fair amount of data cleanup was required to fix the issue as the primary application that read the data was expecting an ISO date.  The code issue was solved by using a shared library that defined the database mapping object.  Now let’s take a look at how document validation could be used to solve this problem.

We will create a collection with the following command:

db.createCollection( "date", {
    validator: { date: {$type:"date"}}
} )

With the collection created with the validator one can now only insert using the new Date() format:

db.date.insert({date:new Date('Mar 21, 2015')})

The numeric and string inserts will give the following error:

WriteResult({
    "nInserted" : 0,
    "writeError" : {
        "code" : 121,
        "errmsg" : "Document failed validation"
    }
})

likewise almost every developer new to MongoDB will make this "mistake" on the command line like:

db.numbers.insert({number:2})

One would think that they have inserted a document containing the integer number two into the collection, when in fact the document now contains a floating-point value.

I can attest to the pain this can cause (especially if you are using a strongly typed language such as JAVA).

We can use the validator to safeguard against this:

db.createCollection( "numbers", {
    validator: { number: {$type:"int" }}
} )

The preceding insert will now give us the 121 error code and you need to use the NumberInt constructor:

db.numbers.insert({number:NumberInt(2)})


How to log custom messages from inside a PostgreSQL transaction? I have a software that generates transactions and executes them. I would like to asynchronously inspect about the transaction progress in term of the simple proportion of queries executed over total number of queries. I'm able to read both stdout and stderr generated by psql, so I was thinking that a solution could be printing to stdout some custom messages like "Progress: 3/8" (or another custom text string).

I've thought about executing a "logging query" which stores in a proper table the informations about query progress, but this table wouldn't be available until the transaction completes, making it unuseful for me to inspect transaction progress.

At the moment I tried the following (suppose to have 3 queries in the transaction which do some stuff):

BEGIN;
CREATE OR REPLACE FUNCTION progress(curr int, total int) RETURNS float AS $$
BEGIN
  RAISE NOTICE '___PROGRESS___%', curr/total::float;
  RETURN curr/total::float;
END;

-- First query
SELECT ... FROM ...;
-- Log the progress
SELECT * FROM progress(1,3);

-- Second query
SELECT ... FROM ...;
-- Log progress
SELECT * FROM progress(2,3);

-- Third query
SELECT ... FROM ...;
-- Log progress
SELECT * FROM progress(3,3);

COMMIT;

I execute the script from a bash file using the following syntax:

res=$((psql -U username -d db -h localhost --log-file=plog.log -f "the transaction above") 2>&1>clog.log)

What I get from a transaction like the one above using the previous bash command are three outputs: plog.log, clog.log and $res bash variable. I'll post some extract of them to make it clear what's their content.

plog.log

....
********* QUERY **********
SELECT * from progress(1, 3);
**************************

     progress
-------------------
 0.333333333333333
(1 row)

...
********* QUERY **********
SELECT * from progress(2, 3);
**************************

     progress
-------------------
 0.6666666666666667
(1 row)

...
********* QUERY **********
SELECT * from progress(3, 3);
**************************

     progress
-------------------
 1
(1 row)

clog.log

...
     progress
-------------------
 0.333333333333333
(1 row)

...
     progress
-------------------
 0.6666666666666667
(1 row)

...
     progress
-------------------
 1
(1 row)

$res variabile (psql return value)

psql:/path/to/script.sql:11: NOTICE:  ___PROGRESS___0.333333333333333
psql:/path/to/script.sql:16: NOTICE:  ___PROGRESS___0.666666666666667
psql:/path/to/script.sql:21: NOTICE:  ___PROGRESS___1

The problem is that I can't real-time access the content of $res variable. I can instead access the content of clog.log and plog.log, so the usage of RAISE NOTICE doesn't make sense at all because it doesn't appear in both clog.log and plog.log but only in $res.

Should I stick to parse clog.log or plog.log in order to find the SELECT output value from the progress() function (removing the RAISE NOTICE statement in that function because of it's uselessness) or there's an alternative method? Am I missing something?

Thank you

RAISE NOTICE is the right method.

The reason RAISE NOTICE output does not appear in clog.log is that your standard error redirection is wrong at the shell level.

You want:

psql --log-file=plog.log -f file.sql  >clog.log 2>&1

That way it redirects the standard output into clog.log and then the standard error to that standard output.

The way it's done in the question: 2>&1>clog.log does not achieve that because of the wrong order, as described in the bash manual:

  Note that the order of redirections is significant. For example, the
  command

  ls > dirlist 2>&1

  directs both standard output (file descriptor 1) and standard error
  (file descriptor 2) to the file dirlist, while the command

  ls 2>&1 > dirlist

  directs only the standard output to file dirlist, because the standard
  error was made a copy of the standard output before the standard
  output was redirected to dirlist.


Print filter parameters on a report I'm using MS Access 2010.

I have a form with a number of fields of various types (number, text, lookup, checkbox).

Users can right click on any field to filter the form.

Then I have a button that generates a report based on this filtered data:

Private Sub Filter_and_Report_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If Me.FilterOn Then strWhere = Me.Filter
DoCmd.OpenReport "WallsRpt", acViewPreview, , strWhere
End Sub

My question is how to list the fields that were filtered in the report (probably in the header/footer).

The Filter condition is in Me.Filter: you use it above.

You'd have to parse this, but you must have set it previously to be able to apply it. Ignoring SQL Injection risk (I just got this off the interwebs), you know in this snippet you are filtering on ProductCatID

Me.Filter = "ProductCatID = """ & Me.cboShowCat & """"
Me.FilterOn = True

You can set a new variable/property (eg Me.Tag) for use in the Header/Footer


Pivot with rollup in sql server Is it possible to use a cursor to create a dynamic table and then use those columns to aggregate data in SQL server 2008?

For example take the following table.

CREATE TABLE Billing (
    BillingId BIGINT IDENTITY,
    SubscriptionId BIGINT,
        ExternalServiceName VARCHAR(50),
        BillYear INT NOT NULL,
    BillMonth INT NOT NULL
);

INSERT INTO Billing (BillingId, SubscriptionId, ExternalServiceName,
                     BillYear, BillMonth)
VALUES (1, 1, 'Dogs', 2018, 4),
       (2, 2, 'Cats', 2018, 4),
       (3, 1, 'Dogs', 2018, 5),
       (4, 2, 'Cats', 2018, 5);

CREATE TABLE BillingData (
    BillingDataId INT IDENTITY PRIMARY KEY,
    BillingId INT NOT NULL,
    Feature VARCHAR(50) NOT NULL,
    Usage INT NOT NULL,
    Measurement VARCHAR(50),
    Cost NUMERIC(18,2) NOT NULL
);

INSERT INTO BillingData(BillingId, Feature, Usage, Measurement, Cost)
VALUES (1, 'Walks', 25, 'walks', 200.32),
       (1, 'Baths', 5, 'baths', 251.32),
       (2, 'Litter change', 53, 'changes', 110.21),
       (2, 'Groom', 25, 'brushings', 123),
       (2, 'Scratching', 213, 'clipping', 123),
       (3, 'Pilling', 11, 'medicate', 10),
       (4, 'Groom', 5, 'brushings', 50),
       (4, 'Exercise', 1, 'run', 25.12),
       (1, 'Walks', 500, 'walks', 12351.31),
       (1, 'Baths', 53, 'baths', 1235),
       (2, 'Baths', 53, 'baths', 1235);

What I'd like to be able to do is create a table with this format

 +-------------+---------+---------+-----------------+---------+--------------+---------+----------+
 | [BillingId] | [Walks] | [Baths] | [Litter change] | [Groom] | [Scratching] | [Usage] | [Cost]   |
 +-------------+---------+---------+-----------------+---------+--------------+---------+----------+
 | 1           | 525     | 58      | 0               | 0       | 0            | 583     | 14037.95 |
 | 2           | 0       | 53      | 53              | 25      | 213          | 344     | 1591.21  |
 +-------------+---------+---------+-----------------+---------+--------------+---------+----------+

The only way I could think to accomplish this was to aggregate the vertical table.

By doing something like the following query

SELECT MAX(BillingId), MAX(Feature), SUM(Usage), MAX(Measurement), SUM(Cost)
FROM BillingData;

But then I'd have to dynamically join those columns into the Billing table, especially since the BillingData may not be the same from month to month. For example:

SELECT DISTINCT Feature FROM BillingData WHERE BillYear=2018 AND BillMonth=5;

Is different from

SELECT DISTINCT Feature FROM BillingData WHERE BillYear=2018 and BillMonth=4;

So while the columns BillingId, Walks, Baths, Litter change, Groom, Scratching, Usage, Cost are appropriate for April the columns for May would be just BillingId, Pilling, Groom, Exercise, Usage and Cost.

I believe a pivot table may be what I need here but I suspect it may need to be dynamic as columns would need to be different for each month.

I'm not sure the best way to go about doing this. Some help would be greatly appreciated.

This can be done with PIVOT and it can be done dynamically, but before you jump in trying to do this dynamically, you should try to get the result you want using a static or hard-coded version of the query, then convert it to dynamic sql.

Since you are using SQL Server 2008 and you want a total column for both Usage and Cost, I would first start by looking at sum(<your column) over(...). This will allow you to aggregate your data in a single step before pivoting it.

To get the static version, I'd first start out with query similar to this:

select
    b.BillingId,
    bd.Feature,
    bd.Usage,
    TotalUsage = sum(bd.Usage) over(partition by bd.BillingId),
    TotalCost = sum(bd.Cost) over(partition by bd.BillingId)
from Billing b
inner join BillingData bd
    on b.BillingId = bd.BillingId
where b.BillYear = 2018 and b.BillMonth = 4

See SQL Fiddle. This query gets you the basic data that you want to pivot:

| BillingId |       Feature | Usage | TotalUsage | TotalCost |
|-----------|---------------|-------|------------|-----------|
|         1 |         Walks |    25 |        583 |  14037.95 |
|         1 |         Baths |     5 |        583 |  14037.95 |
|         1 |         Walks |   500 |        583 |  14037.95 |
|         1 |         Baths |    53 |        583 |  14037.95 |
|         2 |         Baths |    53 |        344 |   1591.21 |
|         2 | Litter change |    53 |        344 |   1591.21 |
|         2 |         Groom |    25 |        344 |   1591.21 |
|         2 |    Scratching |   213 |        344 |   1591.21 |

Including your BillingId, each of the Features you eventually want in a new column, then the Usage, TotalUsage, and TotalCost for each BillingId. The sum(<yourcolumn> over(partition by bd.BillingId) gives you the value for each account without having to use GROUP BY.  Once you've got this data, you can apply the PIVOT function:

select
    BillingId,
    Walks = IsNull(Walks, 0),
    Baths = IsNull(Baths, 0),
    [Litter Change] = IsNull([Litter Change], 0),
    Groom = IsNull(Groom, 0),
    Scratching = IsNull(Scratching, 0),
    Usage = TotalUsage,
    Cost = TotalCost
from
(
    select
        b.BillingId,
        bd.Feature,
        bd.Usage,
        TotalUsage = sum(bd.Usage) over(partition by bd.BillingId),
        TotalCost = sum(bd.Cost) over(partition by bd.BillingId)
    from Billing b
    inner join BillingData bd
        on b.BillingId = bd.BillingId
    where b.BillYear = 2018 and b.BillMonth = 4
) x
pivot
(
    sum(Usage)
    for Feature in ([Walks], [Baths], [Litter Change], [Groom], [Scratching])
) piv;

See SQL Fiddle for Demo. This gives a result:

| BillingId | Walks | Baths | Litter Change | Groom | Scratching | Usage |     Cost |
|-----------|-------|-------|---------------|-------|------------|-------|----------|
|         1 |   525 |    58 |             0 |     0 |          0 |   583 | 14037.95 |
|         2 |     0 |    53 |            53 |    25 |        213 |   344 |  1591.21 |

Now that you've gotten the final result you are looking for, you can  start converting the query to dynamic SQL. In order you do this, you will need to get a list of the values you want to be column aka the Feature values. This is done by querying your tables with the BillYear and BillMonth you want, and concatenating the values into a string, then taking that list of columns and executing a full sql string. The full code could be similar to:

DECLARE
    @BillYear int = 2018,
    @BillMonth int = 4,
    @colsNull AS NVARCHAR(MAX),
    @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ', ' + QUOTENAME(bd.Feature)
                    from Billing b
                    inner join BillingData bd
                        on b.BillingId = bd.BillingId
                    where b.BillYear = @BillYear
                        and b.BillMonth = @BillMonth
                    group by bd.Feature
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

select @colsNull = STUFF((SELECT ', IsNull(' + QUOTENAME(bd.Feature)+',0) as '+ QUOTENAME(bd.Feature)
                    from Billing b
                    inner join BillingData bd
                        on b.BillingId = bd.BillingId
                    where b.BillYear = @BillYear
                        and b.BillMonth = @BillMonth
                    group by bd.Feature
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'');

set @query = N'SELECT BillingId, ' + @colsNUll + N', TotalUsage, TotalCost
            from
             (
                select
                    b.BillingId,
                    bd.Feature,
                    bd.Usage,
                    TotalUsage = sum(bd.Usage) over(partition by bd.BillingId),
                    TotalCost = sum(bd.Cost) over(partition by bd.BillingId)
                from Billing b
                inner join BillingData bd
                    on b.BillingId = bd.BillingId
                where b.BillYear = '+cast(@BillYear as nvarchar(4))+N'
                  and b.BillMonth = '+cast(@BillMonth as nvarchar(2))+N'
            ) x
            pivot
            (
                sum(Usage)
                for Feature in (' + @cols + N')
            ) p '

exec sp_executesql @query;

See SQL Fiddle with Demo. You'll notice that there are two variables for the columns - one @cols this is used inside of the PIVOT function and then @colsNull this is similar to the first, but it replaces the nulls in the final select list with zero - you can exclude using this if you don't need it. If you execute this for BillingMonth = 4 you'll get the same result as the static version:

| BillingId | Baths | Groom | Litter change | Scratching | Walks | TotalUsage | TotalCost |
|-----------|-------|-------|---------------|------------|-------|------------|-----------|
|         1 |    58 |     0 |             0 |          0 |   525 |        583 |  14037.95 |
|         2 |    53 |    25 |            53 |        213 |     0 |        344 |   1591.21 |

Then if you change the BillingMonth = 5 you get the results without having to change the query (Demo):

| BillingId | Exercise | Groom | Pilling | TotalUsage | TotalCost |
|-----------|----------|-------|---------|------------|-----------|
|         3 |        0 |     0 |      11 |         11 |        10 |
|         4 |        1 |     5 |       0 |          6 |     75.12 |


Database Structure and Design Okay my project in simple words. Multiple shop sites are hosted on different hosting services receiving orders from customers. All the orders are to be directed to the main company website where the employees will receive the orders, complete and update the system. So its a two way traffic of information between shop sites and the company website.

Its basically a network of content writing websites where orders will be received and sent to the main writers lounge being hosted on the company website. Initially the plan was to build 1 centralized database on the company website and remotely connect all the website to it BUT there are many factors causing the recent change in plans such as security, vulnerability, sustainability of each website and speed issues since all the websites will be connecting remotely to the database being hosted on the company website. There are almost 20 tables in the database, give or take. And the client does not want to invest in the dedicated hosting or anything, hence it has to be different websites on different hosting plans.

I would appreciate any of your professional thoughts on this. Diversify your answer by assuming two situations: 1) All the websites are being hosted with different hosting service providers and read:writer ratio is 70:30. 2) All the websites are being hosted with the same hosting service provider but different hosting accounts and not a dedicated VPS thing. This might help in overcoming the delay issues in remote database queries.

Solutions that we could think of : * Host 1 centralized database being hosted on the company website and connect all the sites remotely to it. * Host a personal database of each website on its hosting. With a separate database for the Writers being hosted on the main company website and copy the data daily from multiple databases to the writers database. Although we don't think this method is really worth it because it will anyways delay the process and hinder the smoothness of the system.

Sounds like a great opportunity to use a webservice to me.

on your customers end, whenever an order is entered, either:

have the site update the customers local database and post a webservice call to your backend with the order details.
have the order inserted only in the customer local database with a sync flag set to unsynced. have a background process poll for such unsynced orders and post them to your web service.

on your end, do the same in reverse;

whenever an order is prepared, post to the originating customers' webservice with the new details so that their data can be updated.

now you need not worry about the database or potentially even the dbms used.


Postgres Identifying 2 records with 1 attribute equal and 1 attribute not equal I am attempting to pull ids of records where attribute a is the same, but attribute b has at least 1 record that does not have a match. The db has >1million records so the statement cannot be absolute but has to change with the data.

i.e in the below I want the result to return id 1, 2 and 3 because a is equal and b is not equal in all instances, and return id 4 and 5

ID   A   B

1    y   t
2    y   m
3    y   t
4    z   r
5    z   f
6    q   c

This could be a way:

select
    ID
from
    t t1
where exists (select 1
              from   t t2
              where  t2.A = t1.A
                     and t2.B <> t1.B)
order by
    ID;

It could be read as: Give me all ID's if exists another row with same A and different B.

| id |
| -: |
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |

db<>fiddle here


Can one assign a performance score to a stored proc to compare performance of 2 procs I have an existing stored procedure,
and an updated version of that same stored procedure where changes have been made, but the same resultset is returned.

The updated version of the procedure focused on using existing #temp tables in order to do calculations as opposed to going back to the database a lot of times in order to get result values. Also a few tweaks were made to the updated version of the procedure.

Now the updated version runs a bit faster, but I can't consistently prove it because the line to the database which I query is remote and fluctuates with usage on the line.

My question - is there a common denominator which I can use as a "performance score", in order to see which of the 2 procedures are more efficient - less database calls for example?

When you review/compare a SQL Server Execution Plan, each plan is given a cost value. Although these values can indeed be used for a relative comparison between plans, it is often more beneficial to dig a little deeper than that.

For example, you could compare the execution plans of the two stored procedures, perhaps using a tool such as the Free Plan Explorer from SQL Sentry.

This would allow you to break down the "score" you seek into more useful metrics, such as execution time, CPU consumption, Disk IO etc. and use these for your comparison.

Using this method would enable you to establish exactly where your performance gain(or loss) is achieved.


Postgres Server staying in read only mode after a barman recover command is executed All,

I am trying to perform a PITR restore using barman using recover command. The problem is that the server remains in the read-only state once the barman recover is done. Is there a way by which the destination server comes up as a read-write server on its own once the PITR is done? Based on barman documentation, I was hoping --target-action 'promote' might work but that didn't help.

I using barman 2.10 on postgres version 10.12.

Following is the command that I am using to perform PITR:

barman@abc-barman01:~ $ barman recover --target-time  '05/13/2020 14:00:00' --target-action 'promote'  --remote-ssh-command "ssh postgres@abc-pg03" abc-pg01  20200512T203002 /var/lib/pgsql/10/data
Starting remote restore for server abc-pg01 using backup 20200512T203002
Destination directory: /var/lib/pgsql/10/data
Remote command: ssh postgres@abc-pg03
Doing PITR. Recovery target time: '2020-05-13 14:00:00-04:00'
Using safe horizon time for smart rsync copy: 2020-04-04 09:27:29.180929-04:00
Copying the base backup.
Copying required WAL segments.
Generating recovery configuration
Identify dangerous settings in destination directory.
IMPORTANT
These settings have been modified to prevent data losses
postgresql.conf line 219: archive_command = false
WARNING
You are required to review the following options as potentially dangerous
postgresql.conf line 83: ssl_cert_file = 'server.crt'
postgresql.conf line 84: ssl_key_file = 'server.key'
Recovery completed (start time: 2020-05-19 15:38:23.157146, elapsed time: 24 minutes, 21 seconds)
Your PostgreSQL server has been successfully prepared for recovery!
You have mail in /var/spool/mail/barman

Currently, I run pg_wal_replay_resume() manually once the recovery is done to bring the server to read-write mode. I would really appreciate if someone can look into this.

Since barman 2.4, there are two option for after recovery behavior.
They are
--target-action option, accepting the following values:

shutdown: once recovery target is reached, PostgreSQL is shut down
pause: once recovery target is reached, PostgreSQL is started in pause state, allowing users to inspect the instance
promote: once recovery target is reached, PostgreSQL will exit recovery and is promoted as a master

Barman 2.4 also adds the --standby-mode option for the recover command which, if specified, properly configures the recovered instance as a standby by creating a standby.signal
Good luck.
If you like the answer, pls vote it up.
WY


Why is the transaction log not truly circular? I did a little bit of hands-on research recently about how the transaction log in sql server works.  I came to a problem that I can't get my head around.

First I created a new test database with full recovery mode and created an empty test table in it.  Then I did an initial full backup and also afterwards a first tlog backup. This resulted in the following DBCC LOGINFO output:

So far so good. Afterwards I inserted several rows, to make sure the first three VLFs are in use. As my next step I did another tlog backup and manual checkpoint, so that the first two VLFs were truncated which resulted in this DBCC LOGINFO:

Then I added more rows, so that the transaction log wrapped around and the database engine used the first VLF again. In addition to that I added more and more rows and finally the second VLF was full. Since the third VLF was not yet truncated the transaction log grew and four additional VLFs were added. After all of this I did the next transaction log backup and also a manual checkpoint, which led finally to this:

Now what bothers me is what happened next. I thought that when I make so many changes, that the last VLF (here in row 8) will become full, the log will wrap around again and will start to write to the first VLF again (here with FSeqNo 41), since this VLF is already truncated. I expected this, because in " https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide?view=sql-server-ver15 " they suggest, that the transaction log is of a circular nature. But what really happened is, that the database engine wrote to the next VLF with the lowest FSeqNo:

Why is this behavior contrary to the Microsoft docs or, didm I just not understand the it right?  Any help or explanation would be appreciated.

In the face of log file growth events and / or large, long-running transactions, the order of the output of DBCC LOGINFO can look at a little wacky - temporarily, at least.

The important thing to remember is that the FSeqNo is the logical order of the VLFs.

Paul Randal talks about this in his blog post Inside the Storage Engine: More on the circular nature of the log (emphasis mine):

  Once the active transaction I created commits or rolls back, VLFs 3, 4, 1, and 2 can clear and then the ‘normal’ sequencing of VLFs in the log will resume.

  Every VLF has a small header which contains the sequence number of the VLF within the transaction log, so the log can kind of do contortions to work around active VLFs in the middle of the log.

These irregularities should self-correct over time as VLFs clear normally.


SQL Server Multiple Pivots I would like some help please with the following.

Here is my SQL Server 2014 table schema:

Date [DateTime]
Invoice1 [float]
Invoice2 [float]
Operator [nvarchar]
Client [nvarchar]

In my Operator column I have 2 values: Operator 1 and Operator 2.

I would like to achieve the following output (in columns) in a single query:

Date
Invoice 1 of Operator 1
Invoice 1 of Operator 2
Invoice 2 of Operator 1
Invoice 2 of Operator 2
Count of Clients

I tried the following query but it returns an error:  Aggregates on the right side of an APPLY cannot reference columns from the left side

SELECT *
FROM
(
    SELECT
        [Date],
        CASE
            when col = 'Invoice 1' then col + ' of ' + [Operator]
            when col = 'Invoice 2' then col + ' of ' + [Operator]
            when col = 'Clients' then col
    END AS pivot_col,
    CASE
        when col = 'Invoice 1' then SUM(value)
        when col = 'Invoice 2' then SUM(value)
        when col = 'Clients' then value
    END AS myvalues
FROM [table]
CROSS APPLY
(
VALUES
    ([Invoice1], 'Invoice 1'),
    ([Invoice2], 'Invoice 2'),
    (COUNT([Client]), 'Clients')
) x (value, col)
GROUP BY
    [Date],
    col + ' of ' + [Operator],
    col
) AS [Source]
PIVOT
(
    SUM(myvalues)
    FOR pivot_col IN (
        [Invoice 1 of Operator 1],
        [Invoice 1 of Operator 2],
        [Invoice 2 of Operator 1],
        [Invoice 2 of Operator 2],
        [Clients]
    )
) AS [Pivot]

I assume the error is due to the fact that I am using COUNT([Client]) inside the CROSS APPLY VALUES block. However I am not able to solve the problem.

Any ideas please?

Thanks
Reda

[EDIT]

Bonjour Julien,

As requested:

Sample DB Data:

  Date | Invoice1 | Invoice2 |  Operator  |  Client  |  Entity
----------------------------------------------------------------
  JAN  |    10    |    15    | Operator 1 | Client 1 | Entity A
  FEB  |    20    |    25    | Operator 1 | Client 1 | Entity B
  JAN  |    30    |    35    | Operator 1 | Client 2 | Entity C
  FEB  |    40    |    45    | Operator 1 | Client 2 | Entity D
  JAN  |    50    |    55    | Operator 2 | Client 1 | Entity E
  FEB  |    70    |    75    | Operator 2 | Client 2 | Entity F
  JAN  |    80    |    85    | Operator 2 | Client 1 | Entity G
  FEB  |    90    |    95    | Operator 2 | Client 2 | Entity H

Desired output (please scroll to the right):

  Date  | Sum of Invoice 1 for Operator 1 | Sum of Invoice 2 for Operator 1 | Sum of Invoice 1 for Operator 2 | Sum of Invoice 2 for Operator 2 | Count of Distinct Clients | Count of Distinct Entities
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   JAN  |             40                  |             50                  |         130                     |         140                     |       2                   |       4
   FEB  |             60                  |             70                  |         160                     |         170                     |       2                   |       4

This works although I am not sure how to handle the date (sample is not a date):

Select DATENAME(month, [Date]) as 'date'
    , SUM(Case When Operator = 'Operator 1' then Invoice1 end) as 'Sum of Invoice 1 for Operator 1'
    , SUM(Case When Operator = 'Operator 1' then Invoice2 end) as 'Sum of Invoice 2 for Operator 1'
    , SUM(Case When Operator = 'Operator 2' then Invoice1 end) as 'Sum of Invoice 1 for Operator 2'
    , SUM(Case When Operator = 'Operator 2' then Invoice2 end) as 'Sum of Invoice 2 for Operator 2'
    , COUNT(distinct Client) as 'Count of Distinct Clients'
    , COUNT(distinct Entity) as 'Count of Distinct Entities'
From @data
Group By [date];

Output:

date        | Sum of Invoice 1 for Operator 1   | Sum of Invoice 2 for Operator 1   | Sum of Invoice 1 for Operator 2   | Sum of Invoice 2 for Operator 2   | Count of Distinct Clients | Count of Distinct Entities
January     | 40                                | 50                                | 130                               | 140                               | 2                         | 4
February    | 60                                | 70                                | 160                               | 170                               | 2                         | 4

Your data (replaced by real dates):

Declare @data TABLE ([Date] datetime, Invoice1 int, Invoice2 int, Operator varchar(10), Client varchar(8), Entity varchar(10));
INSERT INTO @data(Date, Invoice1, Invoice2, Operator, Client, Entity)
VALUES
    ('20150101', 10, 15, 'Operator 1', 'Client 1', 'Entity A'),
    ('20150201', 20, 25, 'Operator 1', 'Client 1', 'Entity B'),
    ('20150101', 30, 35, 'Operator 1', 'Client 2', 'Entity C'),
    ('20150201', 40, 45, 'Operator 1', 'Client 2', 'Entity D'),
    ('20150101', 50, 55, 'Operator 2', 'Client 1', 'Entity E'),
    ('20150201', 70, 75, 'Operator 2', 'Client 2', 'Entity F'),
    ('20150101', 80, 85, 'Operator 2', 'Client 1', 'Entity G'),
    ('20150201', 90, 95, 'Operator 2', 'Client 2', 'Entity H')
;


MySQL server cannot be stopped I am using Ubuntu release 14.04, MySQL 14.14 Distrib 5.5.43.

To reset the MySQL root password, as described in the official MySQL doc How to reset the root password, I need to stop the MySQL server.

Using htop before killing the server, I see the following related processes:

Then, as indicated, I stop the MySQL server with the following command:
kill $(cat /var/run/mysqld/mysqld.pid).

But, a few seconds later, all associated processes are automatically restarted under different PIDs:

How can I stop the server without it restarting new processes each time ?

Try stopping the mysql server, instead of killing the process:

sudo /etc/init.d/mysql stop


Optimize table on large table online DDL Feature I am running optimize table on MySQL table size 340GB in size and storage engine is InnoDB.

mysql> show variables like '%tmp%';
+----------------------------+------------------+
| Variable_name              | Value            |
+----------------------------+------------------+
| default_tmp_storage_engine | InnoDB           |
| innodb_tmpdir              |                  |
| max_tmp_tables             | 32               |
| slave_load_tmpdir          | /dbtmp/mysql_tmp |
| tmp_table_size             | 33554432         |
| tmpdir                     | /dbtmp/mysql_tmp |

tmpdir directory is filling up space .

/dev/mapper/vgdbtmp-lvdbtmp
                      296G  231G   51G  83% /dbtmp

What will be the alternate solution to optimize table without creating copy of table.

MySQL 5.6 version

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.46-log |
+------------+
1 row in set (0.01 sec)

I have read below MySQL link.

https://mysqlserverteam.com/mysql-5-6-17-improved-online-optimize-table-for-innodb-and-partitioned-innodb-tables/

https://dev.mysql.com/doc/refman/5.6/en/optimize-table.html

Prior to Mysql 5.6.17, OPTIMIZE TABLE does not use online DDL. Consequently, concurrent DML (INSERT, UPDATE, DELETE) is not permitted on a table while OPTIMIZE TABLE is running, and secondary indexes are not created as efficiently.

But here no one is firing DDL/DML/select on table currently.

It should run online without creating copy of table right?

OPTIMIZE TABLE is almost useless for InnoDB.  Why are you running it?
Some people like to break up the disk into partitions.  But that leads to overflowing one partition when there is spare room in another.  You could move tmpdir to a bigger partition?
OPTIMIZE TABLE table must copy the table over.  Ditto for flavors of ALTER TABLE.  Hence, you must have enough spare disp space, preferrable in the same FS, as the table.
MySQL 8.0 has some "inline" or "in place" ALTERs; maybe it would help here.
Percona's pt-online-schema-change avoids blocking writes during schema changes.  Ditto for gh-ost.  These probably need the extas disk space, so may not be relevant.

Moral of this story...  Don't let the disk get so full that you can't make a full copy of the biggest table.


How un-clustered is a CLUSTER USING table in PostgreSQL I have some tables which benefit greatly from CLUSTER ON/CLUSTER USING in Postgres SQL:

# CLUSTER table USING index_name;
# ANALYZE VERBOSE table;
# CLUSTER VERBOSE;

A maintenance task periodically runs CLUSTER VERBOSE to keep things fresh.  But is there a test I can run to see how fragmented the table is, prior to running CLUSTER VERBOSE? Maybe something like:

# CLUSTER ANALYZE
table 40000 records. 4000 observed clusters, 5000 potential clusters (20% fragmentation)

Note that I use CLUSTER so data accessed at the same time is "defragmented" into a small number of disk blocks.  For example I have thousands of attributes that go with each page.  a CLUSTER page_attribute USING page_id; puts all the attributes next to each other, greatly reducing disk load.

I am not entirely sure how much this information helps, but the system table pg_stats contains a correlation column:

select schemaname,tablename,attname,correlation
from pg_stats
where schemaname='public'
order by correlation;

From the manual

  Statistical correlation between physical row ordering and logical ordering of the column values. This ranges from -1 to +1. When the value is near -1 or +1, an index scan on the column will be estimated to be cheaper than when it is near zero, due to reduction of random access to the disk. (This column is null if the column data type does not have a < operator.)

This value is on a per-column basis, so my guess is, that if the correlation for the first index column is near zero, the table is "un-clustered", if near -1 or 1 it is closer to the order of the index and thus "more clustered". This if course can only work for B-Tree indexes that are defined as ASC (I guess).


User can't execute stored procedure despite in role with permission I'm setting up a number of stored procedures and I would like to limit the users who can execute them to a specific server role. I've created my role and users like this:

USE [MyDB]
GO
CREATE ROLE [MyExecuters]
GO
CREATE USER [MyUser] FOR LOGIN [MyUser] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [MyExecuters] ADD MEMBER [MyUser]
GO

And I've granted permissions on the SP to the role like this:

USE [MyDB]
GO
GRANT EXECUTE ON [dbo].[MyProcedure] TO [MyExecuters] AS [dbo];
GO

However if MyUser tries to execute the SP, they get an access denied message. I tried explicitly granting the EXECUTE permission on the SP to MyUser (through the procedure properties in Management Studio) and now they can execute it fine. What's going on?

this script gives the permissions assigned to a table or stored procedure in a script format.

have a look what it returns for your MyProcedure
that could be a start.

SELECT
  (
    dp.state_desc + ' ' +
    dp.permission_name collate latin1_general_cs_as +
    ' ON ' + '[' + s.name + ']' + '.' + '[' + o.name + ']' +
    ' TO ' + '[' + dpr.name + ']'
  ) AS GRANT_STMT
FROM sys.database_permissions AS dp
  INNER JOIN sys.objects AS o ON dp.major_id=o.object_id
  INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
  INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id
WHERE 1=1
    AND o.name IN ('MyProcedure')      -- Uncomment to filter to specific object(s)
--  AND dp.permission_name='EXECUTE'    -- Uncomment to filter to just the EXECUTEs
ORDER BY dpr.name


Is it ok to create additional column for helping other column to be a PK? Let's say a Person can have more than one nickname. He(she) must enter his(her) nicknames alongside with other details:

Here is the table columns:

  User_Id, Nickame, Age

Of course, for now there is anot any column or columns which are candidate to be a PK(composite or not).

In order to create PK, I have added Row_Num column to the table, which will start from 1 for each user. I will calculate the Row_Num in insert trigger based on the User_Id.

For example, table content will look like this:

15, 1, Assassin, 18
15, 2, Fighter, 14
15, 3, Gentleman, 5

16, 1, Lady, 5
16, 2, Clever, 15

Of course, the Pk will be UserId and Row_Num.

Could you please tell me is that idea Ok or not? If not, why and what is the better choices?

Is it ok? Or, I must include ID column wit autoincrement option?

UPDATE:

Let's say user will enter his nicknames in order. And one user can have same nicknames in different ages. And a user can have 3 nicknames in same age. And the firsta nd the last of nickname can be same:

15, 1, Assassin, 18
15, 2, Fighter, 18
15, 3, Assassin, 18

If I understood you correctly, you have two sorts of entities to manage:

Players (human beings that participate in your game)
Characters (fictional entities that the players use/manipulate in your game)

Once you look at your problem like that, you could model it easily like this:

players table, with a player_id as PK and information about your real users
characters table, with a character_id as PK and information about those game entities
A relation table to map characters to their owners.

(From a practical point of view, the relation table might be extraneous - you could store the owner as a column in the characters table .)

This allows you to use identity columns or sequences for both player and character IDs, and you don't have to worry about tricky triggers for inserts.


Is the SQLTRACE_FILE_BUFFER wait type documented? What does it indicate? We run a background task on one of our servers which polls sys.dm_exec_requests for long-running queries, gathering diagnostic information when it finds them, including wait type.

I'm seeing a number of queries with the SQLTRACE_FILE_BUFFER wait. This isn't documented anywhere I've looked - I wondered if anyone has any further information about this wait type?

The name suggests that there's a delay writing trace data to disk; we have a server-side trace running on the instance which writes to a file for audit and performance monitoring purposes.

The SAN admin tells me we have IOPS to spare, so I don't think this is a simple I/O issue. What else should I check?

UPDATE

We disabled the server-side trace and continued to see the same issue; the latest thinking is that the SQLTRACE_FILE_BUFFER wait is a symptom of memory pressure (there's a different documented wait type used when the trace buffer is being written to disk - SQLTRACE_BUFFER_FLUSH - which doesn't appear).

UPDATE 2

Disabling the default trace removes the wait. We are still investigating the underlying cause of the performance problem.

I don't believe it is documented anywhere (even in Bob Ward's Wait Type Repository - a useful resource in any case), and I suspect you are quite right about the underlying cause.

If this wait type seems to be causing you significant performance issues (and you've isolated the wait as the cause and not a symptom, by observing relieved pressure when you temporarily disable the trace), probably the best thing would be to investigate the trace and make sure:

you are filtering adequately to prevent the writing of data you'll never look at.
you are capturing only the event types you need.
you aren't writing to a severely under-powered or over-utilized disk, to the same local disks where your data/log files are, or over a network connection that is just not capable of handling the load (if writing remotely the wait may not show up as network - especially if the write is going over iSCSI or other remote-disks-that-look-kind-of-local-to-SQL-Server).

For most things your trace is probably collecting, you may be better off converting your trace to use Extended Events. Not everything there is more lightweight than trace, but most of it is. The EE team at Microsoft blogged about how to do this, but if you're planning a migration to SQL Server 2012, you'll want to watch Jonathan Kehayias' blog.


How to skip OR if AND statement row count is not 0 Heey Guys,

I have a question and i can't find the solution.
So this is my QUERY

SELECT *
FROM `hwr_story`
WHERE question_count = '8'
AND (lower(subcategory) = lower('True') OR subcategory IS NULL)
ORDER BY RAND() LIMIT 1

Result:
http://cl.ly/image/1r0c2P0m3K07

So what i want, is if this return a row, i don't want the or statement.

SELECT *
FROM `hwr_story`
WHERE question_count = '8' AND lower(subcategory) = lower('True')
ORDER BY RAND() LIMIT 1

But if the row count is 0 then i want it to get

SELECT *
FROM `hwr_story`
WHERE question_count = '8' AND subcategory IS NULL
ORDER BY RAND() LIMIT 1

Is there anyway to do this in one Query?

You want to limit your result to one row anyway, so just write your order by like this:

SELECT *
FROM hwr_story
WHERE question_count = '8'
AND (lower(subcategory) = lower('True') OR subcategory IS NULL)
ORDER BY subcategory IS NULL, RAND() LIMIT 1

subcategory IS NULL returns 1 or 0, true or false. Then just sort ascending and you got it :)


Migrate database through database link There's a database called FOO in an Oracle XE 10g server that's due to be decommissioned (old.example.com). I've been asked to migrate it to some other Oracle XE 11g server (new.example.com). Both servers are on the same LAN. I don't know anything about the database (it isn't a project of mine). There isn't a DBA in the room. My random Google searches suggest it's a terribly complicated task. I can use SQL Developer or SQL*Plus.

I've figured out that using impdp over a network link might be a reasonably straightforward method. But docs take most stuff for granted and I can't make it work.

So far:

I've assigned EXP_FULL_DATABASE to user FOO in source server (role DATAPUMP_EXP_FULL_DATABASE does not seem to exist in 10g).
I've created a temporary user called tmp_imp in target server with CREATE DATABASE LINK and DATAPUMP_IMP_FULL_DATABASE roles.
I'm stuck in the create database link part. I've logged into new.example.com as tmp_imp and tried all possible combinations of the CREATE DATABASE LINK statement. They either trigger ORA-00933: SQL command not properly ended or are accepted but produce a non-working link (ORA-39001: invalid argument value, ORA-39200: Link name "OLD_FOO" is invalid., ORA-12154: TNS:could not resolve the connect identifier specified).

This is one of my attempts:

CREATE DATABASE LINK OLD_FOO
CONNECT TO FOO IDENTIFIED BY 'pa$$w0rD'
USING '//old.example.com:1521/xe';

What am I doing wrong? Is there a simpler tool to migrate the database?

The USING clause expects a valid TNS entry or alias. Use this:

CREATE DATABASE LINK OLD_FOO
CONNECT TO FOO IDENTIFIED BY "pa$$w0rD"
USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = old.example.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))';