texts: SSIS 2016 conditional split with multiple columns in conditions

texts:
I am having an issue in executing a conditional split for upserting records into my production table, using SSIS 2016 and MSSQL 2016 (Standard Ed.)

I am trying to load two separate files (produced from an OpenVMS database) that contain similarly-formatted content, however they are from two different companies: AB_CustomerData.txt and CD_CustomerData.txt.

Customer Format files

RecordType: CU01
----------------
RecordType 2 characters
Company 2 characters
CustomerNumber 7 characters
CustomerName 50 characters

RecordType: CU02
----------------
RecordType 2 characters
Company 2 characters
CustomerNumber 7 characters
City 9 characters
State 8 characters

RecordType: CU03
----------------
RecordType 2 characters
Company 2 characters
CustomerNumber 7 characters
Phone 10 characters

AB_CustomerData.txt
-------------------
CU01AB0001234ABC Company
CU02AB0001234SmalltownAnywhere
CU03AB00012342135551212
CU01AB0002345Unbrella Corp
CU02AB0002345SmalltownAnywhere
CU03AB00023452135551213
CU01AB0003456MegaCorp
CU02AB0003456SmalltownAnywhere
CU03AB00034562135551214

CD_CustomerData.txt
-------------------
CU01CD0001234Jake's Widgets
CU02CD0001234SmalltownAnywhere
CU03CD00012342134441313
CU01CD0005678Jane's Doohickies
CU02CD0005678SmalltownAnywhere
CU03CD00056782135551314
CU01CD0006789Frank's Thingamabobs
CU02CD0006789SmalltownAnywhere
CU03CD00067892135551315

My end result is to have this in my production table:

Company | CustomerNumber | CustomerName         | City      | State    | Phone
-----------------------------------------------------------------------------------
AB      | 0001234        | ABC Company          | Smalltown | Anywhere | 2135551212
AB      | 0002345        | Umbrella Corp        | Smalltown | Anywhere | 2135551213
AB      | 0003456        | MegaCorp             | Smalltown | Anywhere | 2135551214
CD      | 0001234        | Jake's Widgets       | Smalltown | Anywhere | 2135551313
CD      | 0005678        | Jane's Doohickies    | Smalltown | Anywhere | 2135551314
CD      | 0006789        | Frank's Thingamabobs | Smalltown | Anywhere | 2135551315

I have a ForEach container to loop through these files in my directory, and do the following:

load the file into a pre-staging table
process the customer record
type (CU01, CU02, CU03 for each customer) into record-type specific
staging tables (ie: record-type CU01 goes to a CU01 staging table,
etc)
merge the record types into one larger staging table, containing
all records
merge join the staging table and the production table, to
prepare for upserting
upsert the production table

My conditional splits are defined as follows:

INSERT: (ISNULL(Production_CustomerNumber) && !ISNULL(Staging_CustomerNumber)) && (ISNULL(Production_Company) && !ISNULL(Staging_Company))

UPDATE: (!ISNULL(Production_CustomerNumber) && !ISNULL(Staging_CustomerNumber)) && (!ISNULL(Production_Company) && !ISNULL(Staging_Company))

DELETE: (!ISNULL(Production_CustomerNumber) && ISNULL(Staging_CustomerNumber)) && (!ISNULL(Production_Company) && ISNULL(Staging_Company))

On the first pass of the ForEach container, the data from the first company file loads correctly all the way through to production.  However, on the second pass of the ForEach container, any data pre-existing in the production table gets deleted.  I am almost positive it is because of my conditional split definitions, but I can't seem to figure out where.


texts:
Database Type : SQL server 2008 R2

Issue description:

There are two machines. One is server machine(OS- Windows server Enterprise) another one is client machine(OS- Windows 7 professional). In Both machines SQL server 2008 R2 is installed.

Same database is being restored in both SQL servers. A process(consists of stored procedures) is running with in transaction scope from the application(Language : C# .NET) and application access the database.

In server, its taking long time to execute the procedures(above 5 minutes). In client machine its taking only 1 - 2 minutes. In Both machines, database used is same. Transaction Timeout value too declared same (5 minutes)

Server properties:

Client machine properties

SQL server Properties - Server:

SQL server properties - client machine

Please share your thoughts. Any idea for this issue? Thanks in advance


texts: SQL Server mirrored database is stuck in recovery mode

texts:
I have a database that is stuck in recovery mode for few days already. There are multiple threads about this, but the solutions there don't work for me.
Here is what I tried already:
RESTORE DATABASE [DBNAME] WITH RECOVERY
Error: Exclusive access could not be obtained because the database is in use.

ALTER DATABASE [DBNAME] SET OFFLINE WITH ROLLBACK IMMEDIATE
Error: ALTER DATABASE failed because a lock could not be placed on database 'DBNAME'
I get the same error when trying to set the db to SINGLE_USER

exec sp_who2 --> nothing that contains my database, so nothing that I can kill
(Or I need to look for something else ??)

I can't just stop the SQL-service, because it contains too many databases that can't go down.
Who knows what I can do to get the database out of recovery mode? At both the primary and mirror location is the database state 'In recovery'.
UPDATE
I found a process in sp_who2 with command DB STARTUP.
At sys.dm_tran_locks, I see this session has a resource_database_id for the database that is in recovery, so this keeps the database locked. Anyone knows how to fix this without stopping SQL Server?


texts:
I've got the following SP which I wrote to calculate the size of all the tables on my database

SELECT
    name,dbo.nice(rows) as [rows],
    dbo.nice(CAST(total-indexes as DECIMAL)/1024) as [data (MB)],
    dbo.nice(CAST(indexes as DECIMAL)/1024) as [indexes (MB)],
    dbo.nice(CAST(total as DECIMAL)/1024) as [total (MB)]
FROM (
   Select A.name,rows
   ,SUM(CASE B.indid WHEN 1 THEN B.used ELSE 0 END)*8 as [total]
   ,SUM(CASE B.indid WHEN 1 THEN 0 ELSE B.used END)*8 as [indexes]
   from sysobjects A (nolock), sysindexes B (nolock)
   where A.id = b.id and A.type = 'u' and rows > 0
   group by A.name,rows
) as [d]
order by total desc

I can't recall where I got this exactly. I wrote the outer part and found the inner part online somewhere.

Anyway, it does the trick. It calculates the size of my data by subtracting the indexes. I got a shock today when I ran the SP and compared it to a result from a few days ago

table        rows             data (MB)   indexes (MB)   total (MB)
MyBigTable   240,106,454      35,428      33,001         68,429       // 4 days ago
MyBigTable   241,442,655      57,128      11,559         68,688       // today

My data had jumped in size! After inspecting the SP source code and remember how it works I realise of course the data size did not change (much). What happened was the indexes drastically reduced in size.

What causes this? I didn't make an edits to the indexes. I didn't shrink the DB or run anything on it that should affect the indexes. Why did my indexes decrease so dramatically? And why didn't it affect the overall size of the database?

UPDATE:

I looked into my indexes and found one was seriously fragmented, at over 80%. I rebuilt it and now the data has jumped again. Here are the new stats.

table        rows             data (MB)   indexes (MB)   total (MB)
MyBigTable   240,106,454      35,428      33,001         68,429       // 5 days ago
MyBigTable   241,442,655      57,128      11,559         68,688       // yesterday
MyBigTable   241,705,941      35,581      32,538         68,119       // today


texts:
We've recently upgraded a server to SQL Server 2016 (Enterprise). But then the syspolicy_purge_history started failing on the 3rd step. I traced this back to msdb.dbo.syssubsystems which had an invalid agent_exe value for PowerShellStart.
When I tried to update this using sp_verify_subsystems I found that the paths in this SP hadn't been updated as part of the 2016 upgrade to point to the 130 folder and were still point at the 120 folder.

2014 SP Version

     EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\Microsoft Sql Server\120', N'VerSpecificRootDir', @VersionRootPath OUTPUT
 IF @VersionRootPath IS NULL
 BEGIN
   RAISERROR(14659, -1, -1) WITH LOG
   RETURN(1)
 END

2016 SP Version

        EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\Microsoft Sql Server\130', N'VerSpecificRootDir', @VersionRootPath OUTPUT

    IF @VersionRootPath IS NULL
    BEGIN
        RAISERROR(14659, -1, -1) WITH LOG
        RETURN(1)
    END

I checked another server recently upgraded to 2016 and the stored procedure had been updated successfully.

Is there any reason why an in place upgrade would complete successfully but not update the system stored procedures? All databases are running in 2016 compatibility mode.

Obviously I can update the stored procedure myself, but I am just wondering if any others have been missed?


texts:
I'm trying to put together a query to aggregate some information into a view, and am having trouble working out how to get that data I need from a couple of different tables. I'll start by describing the 3 tables I'm working with.

I have artists, which is as follows:

Ref | Artist
  1 | Metallica

I have tracks:

Filename | Title             | artist_ref
     001 | Sad But True      |       1
     002 | Through The Never |       1

And lastly I have popularity:

Filename | week | month | quarter
     001 |   23 |    42 |     138
     002 |    4 |    23 |      42

What I'm trying to do is get an aggregated popularity table formatted as so:

artist_ref | week | month | quarter
         1 |   27 |    65 |     180

I understand I'll have to use some kind of subquery and SUM, but my knowledge of this area is fairly limited. Any help is hugely appreciated as I'm finding this a bit of a brain buster!


texts: Is using the rename database feature in PHPMyAdmin safe?

texts:
I know the rename database functionality has been taken out of MySQL. My question is, does renaming it in PHPMyAdmin work and is safe? I know they have written functionality to deal with a number of issues that can arise but I haven't found any commentary on whether it's safe.

By safe I mean no data/structure loss.

Thank you


texts: How to get online the database which has missing/corrupted FILESTREAM files?

texts:
I've corrupted the files related to FILESTREAM in a database.

The .mdf and .ldf files are still intact, but when I try to get the database online, it complains, saying that the files related to FILESTREAM are incorrect.

I don't care about the data stored in FILESTREAM, but I do care about the other data. Can I get it back from .mdf and .ldf files? How?

When I execute:

sp_attach_db @dbname = 'Demo',
             @filename1 = N'<File path>.mdf',
             @filename2 = N'<File path>.ldf'

the response is:

Msg 5120, Level 16, State 105, Line 1
Unable to open the physical file "<Location>". Operating system error 2: "2(The system cannot find the file specified.)".
Msg 5105, Level 16, State 14, Line 1
A file activation error occurred. The physical file name '<Location>' may be incorrect. Diagnose and correct additional errors, and retry the operation.
Msg 1813, Level 16, State 2, Line 1


texts:
I am trying to dump this data into Postgres:

COPY address_types (game_id, device_type) FROM stdin;
1   Ps3
2   Xbox
3   Other
\.

But I always get this error:

[Err] ERROR:  syntax error at or near "1"
LINE 2: 1 Ps3
        ^

Any ideas what might be wrong?


texts:
We have a 120GB database. There was table with 60GB of data which is useless and we have truncated it.

Now database size is 120GB with free space 60GB. The database will not grow up to 60GB in at least 3 months. So can we shrink the data file.

Am aware of fragmentation issue. I can rebuild my index since our's is not a 24*7 database.

Please advice on shrinking MDF file


texts: avg_page_space_used_in_percent and fill factor

texts:
sys.dm_db_index_physical_stats contains column avg_page_space_used_in_percent.

How does this fit in with the fill factor of an index?

For example:

I have index idx_HeaderInfo

I create the index with a fill factor of 80%.

If I then immediately look at avg_page_space_used_in_percent do I see:

100
80

I ask because I need a way of rebuilding or reorganising based on internal fragmentation rather than external and I don't want to create logic to rebuild/reorg if a avg_page_space_used_in_percent < 75 if the fill factor in the first place is 70 for instance.


texts: Serialize a column based on sorting order?

texts:
I have a column number_order and products_name I'm struggling to build a command that will sort by A-Z so that respectively:

A = 1
B = 2
C = 3

I'm using MariaDB. To be clear I want to run a command that will set the number_order starting from 1 to X, based on the products_name so that a product called A would have number_order = 1


texts: write csv file only after SQL table update, insert, delete

texts:
Objective: Write csv file of updated data from sql table only after SQL table update, insert, delete.

I've read from the community that I should not do triggers to execute python.  I should not run triggers to write to file from within sql.  Also, my IT keeps the environment very very restricted and I can't use XP_cmdshell, or sqlcmd.

I have python in windows. This must be a very common task.  Yet, surprisingly, it's hardly mentioned.  Perhaps I'm using the wrong search terms.  Please no expensive software suggestion.  I can't ask the IT to buy it.

How can I execute python only after the sql table updates?


texts:
I am trying to get the MAX and MIN values for the current month and the dates for these values.

I am getting the MAX and MIN values but I am not getting the dates for these values. Instead I get the first day of the month.

Here is my query:

SELECT  DATE(CREATED) AS date
      , MIN(ROUND(AMBIENT_TEMPERATURE,1)) AS min_temp
      , MAX(ROUND(AMBIENT_TEMPERATURE,1)) AS max_temp
FROM WEATHER_MEASUREMENT
WHERE (MONTH(DATE(CREATED)) = Month(NOW()));

And this is what I get:

date        min_temp  max_temp
----------  --------  --------
2016-05-01  17.8      43.2

I would like to have a date for the max_temp and a date for the min_temp.

How can I do it?


texts: SSIS or SQL Server way of sectioning a flat file

texts:
I have a few hundred thousand flat text files which contain a single column of values. The first 40 values are parameters followed by between 4000 - 8000 rows of sensor data. The files are imported once, will never change and will be accessed by parameter selection for further analysis/calculations.

I think using two tables (one for parameters, one for sensor data) would be a good schema. So I need to split the file after import, transpose the parameter rows to columns and then load all the sensor data into a single table with a key back to the row in the parameters table.

I looked at pivot and unpivot but that won't let me transpose a single row to columns.

Any help on how to do this in SSIS or SQL and thoughts on my schema appreciated I'm not sure if the database will be responsive when I get a few million rows in the sensor data table.


texts:
Is there a way to query a DB2 Linked Server without using Openquery?  IE, is it possible to query DB2 through a linked server by doing something like the following?

select *
from [db2_linked_server].[database].[tablename]


texts: Building a custom database in C with/without SQL?

texts: Provide link or tutorials to build a custom database management in C. I am looking for Full Text Search options of about 10GB DB.


texts: How to get random samples from a table? I don't want repeated records between samples

texts:
I have this problem, I want to get some random extracts from a table and I dont want repeated records between those extracts, in order that when I join all the different samples together, they look as the main table.

I am working with sql server 2012

thanks in advance


texts: SQL Server 2008R2 trigger sometimes doesn't fire

texts:
We use wireless barcode scanners to scan our goods as they wind their way around the factory, scanning each product at fifteen separate checkpoints.  After passing through a checkpoint, the product gets a STATUS ID which corresponds to the relevant checkpoint.

The scanners pass the status ID, Barcode of the product, Scanner name, and date/time to a table called HW_SCANNER_TRANSACTIONS and the trigger then updates other tables before updating the original table with a date and time that the trigger ran.

Recently, we have been seeing errors where the data is verified as in the scanner_transactions table but has not updated the other tables.  There appears to be no pattern to these.

USE [DBMAIN]
GO
/****** Object:  Trigger [dbo].[TRANSACTIONS_InsertTrigger]    Script Date: 09/02/2015 09:02:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[TRANSACTIONS_InsertTrigger]
ON [dbo].[HW_SCANNER_TRANSACTIONS]
AFTER INSERT
AS

BEGIN

SET NOCOUNT ON
UPDATE [GLA_FRAME_LABEL]
SET ITEM_STATUS_ID =  inserted.StatusID
FROM inserted
WHERE substring(inserted.[BarcodeID],2,15) = [GLA_FRAME_LABEL].[LABEL_ID]

UPDATE [QUOTE_ITEM]
SET ITEM_STATUS_ID  =  inserted.StatusID
FROM inserted
WHERE [QUOTE_ITEM_ID] =  (SELECT QUOTE_ITEM.QUOTE_ITEM_ID FROM QUOTE_ITEM INNER JOIN GLA_FRAME_LABEL ON
QUOTE_ITEM.QUOTE_ITEM_ID = GLA_FRAME_LABEL.QUOTE_ITEM_ID WHERE (GLA_FRAME_LABEL.LABEL_ID = SUBSTRING ( inserted .BarcodeID,2,15)))

UPDATE [GLA_FRAME_LABEL]
SET ITEM_STATUS_ID  =  inserted.StatusID
FROM inserted
WHERE [QUOTE_ITEM_ID]  =  (SELECT QUOTE_ITEM_ID FROM GLA_FRAME_LABEL WHERE LABEL_ID = SUBSTRING ( inserted .BarcodeID,2,15))
AND QTY_COUNT = (SELECT QTY_COUNT FROM GLA_FRAME_LABEL Where LABEL_ID = SUBSTRING ( inserted .BarcodeID,2,15))

UPDATE [HW_SCANNER_TRANSACTIONS]
SET [Synched] = GETUTCDATE()
FROM inserted
WHERE inserted.idx =[HW_SCANNER_TRANSACTIONS].[idx]

END;

When the errors occur, the only update that runs is the final one in the list which updates the transaction table synch date to say that it's been run.

I'm at a loss to explain why and wondering if there was anything wrong or needed adding to my trigger to make it work.


texts:
I am starting to work with SQLite in Qt Creator. I never worked with SQLite before. How can I create a table to store the following data?

int id;
QString fullName;
int age;
QVector<QString> friendsFirstName;
QVector<QString> friendsSurname;

In fact, my doubt is how to store vectors that must be linked to a single record? SQLite provides some vector type?

Thanks in advance


texts: Case insensitive search on Always Encrypted columns

texts:
How would one go about doing a case-insensitive search on a column encrypted with Always Encrypted?

The problem arises when you encrypt email addresses for example.
When encrypted, John.Doe@gmail.com !=  john.doe@gmail.com

Is the best solution truly to duplicate the encrypted column with the ToLower version of the text to search against?


texts: How can I run multiple concurrent versions of my application against the same database?

texts:
Background: I have multiple concurrent versions of my application running in my production environment. Depending on the user account that's used, the user will have access to a different version of the software.

Environment: Currently SQL Server 2005, imminently migrating to SQL Server 2008, ASP.Net

Problem: Each version of the software may or may not use different versions of the stored procedures that interact with the data in the database. Currently when a version is changed, whoever changes it creates a new copy and appends an incremental version number to the end. At this point we have numerous versions of some stored procs and only one version of others and nobody is sure which version of the application is pointing to which version of the stored procs - it's a mess.

I'm looking for a solution that will neatly package the stored procs up for any given version of the application to be used as the basis for a new version. This means that the new version of the application can be pointed to the new set of procs which can be rewritten or modified to the end user's content without affecting other versions of the applications that are currently available in production.

I originally thought about schemas, but one part of the issue is that the procs are heavily coupled with other procs and user defined functions, so when copying to another schema, we'd new to map and replace all these links which isn't ideal. Is there a way to write the procs so that moving them to another schema would bring the relationship between them along for the ride so that for instance if Schema1.Proc1 referenced Schema1.Proc2 and we copied them to Schema2 that Schema2.Proc1 would now reference Schema2.Proc2 without having to do any additional work?  How would this handle user defined functions where you have to reference the function by SchemaX.Func1()?  It would be handy if I could refer to the function without defining the schema the function belonged to in the same way I can with a stored procedure, but I don't think that's possible.

It seems like this is a problem that should already be solved, but I don't know what I'm looking for in order to find a viable solution.

Does anyone have any ideas?


texts: Is there a way to examine SQL Jobs for references to linked servers?

texts:
In preparation for migrating a server farm to the cloud, I am attempting to enumerate all the linked servers that have been defined over the years.  I also need to find (and correct) the code that references these linked servers.

Here is something I threw togetether to help identify names of objects that refer to the linked server, but this only works against stored procs, views, functions and triggers:

declare @sql varchar(2000)
select  @SQL = 'use ?;
print ''?'';
print replicate(''-'',len(''?''));
print '' '';

select object_name(sc.id)
  from syscomments sc
  where text like ''%TheLinkedServer%''
'

exec sp_msforeachdb @sql

I know I will have to crack open the DTS/SSIS packages...I am just trying to get as many finished as quickly as possible.


texts: Oracle 12c; cannot connect to HR without running ALTER PLUGGABLE DATABASE ALL OPEN

texts:
I just installed Oracle 12c and I'm trying to connect to the default HR database.

When I open a command prompt and type connect hr/hr@pdborcl; I get the following:

ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0

To connect to HR, I have to do this first:

connect / as sysdba;

alter pluggable database all open;

Only then can I run hr/hr@pdborcl;  and connect successfully

This is a bit of a pain... having to connect as sysdba and alter the database just to connect.  Is there a better way?

As a side note, all of this is on my development laptop.  I don't keep Oracle running when it's not needed as that's a waste of CPU and RAM.


texts: What can cause [DataDirect][ODBC Oracle Wire Protocol driver]Connection refused.?

texts:
The Title speaks for itself.

We have this error message on the client side of the application.
The devs suspect a problem on the database.
The alert log on the database shows no errors at all.
The only events I have are the regular archived logs shipping to the standby database.

I was suspecting processes and sessions parameters to be a bit too low, but currently

select count(*) from v$process shows 36 process running. Granted this is not peak time, but I would need to have ora-00020 in my alert log anyway should I hit this threshold...

What else could be happening?


texts: How does autocommit=off affects bulk inserts performance in mysql using innodb?

texts:
I know that turning off autocommit can improve bulk insert performance a lot according to:

Is it better to use AUTOCOMMIT = 0

And I have made experiments to confirm that conclusion. But what I want to know is, why turning off autocommit can improve bulk inserts? And what happens internally in inno db when a transaction is commited?


texts: I have an oracle database with no asm.  If I shut down the database, move some of the dbf files to a location where I cannot preserve the timestamp on the files, then move them back... Will the database start up without any problem?  I will not change anything in the contents of the dbf files nor the permissions.. only the OS timestamp will change.  is this ok?  Thanks!


texts: I have a lottery database with columns Ball1 to Ball6 how do i find number of times a ball has been drawn from columns Ball1 to Ball6


texts: How can change the default authentication method in MariaDB / MySQL

texts:
I'm trying to set the default authentication method to unix_socket.
Following the instructions here:
https://lindevs.com/change-default-authentication-plugin-in-mysql/
I added to my.cnf :
[mysqld]
default_authentication_plugin=unix_socket

And restarted: sudo service mysql restart
Unfortunately, I don't see any change:
MariaDB [(none)]> SHOW VARIABLES LIKE 'default_authentication_plugin';
Empty set (0.001 sec)

This is on WSL1 and 10.3.27-MariaDB-0+deb10u1-log Debian 10.
Thanks


texts: SQL Server suggestion to create nonclustered index - on 2 columns, reversed

texts:
I have a query, where SQL Server 2012 suggested a create a nonclustered index as follows:

CREATE NONCLUSTERED INDEX [<name>]
ON [dbo].[tblUserCourses] ([courseID])
INCLUDE ([userID])

I created the index with this code.

Running the query, it now is suggesting a similar index, but with the INCLUDE reversed:

CREATE NONCLUSTERED INDEX [<name>]
ON [dbo].[tblUserCourses] ([userID])
INCLUDE ([courseID])

Excuse my ignorance... is there a (significant) difference between these 2, or is this one of the cases I am reading, where the SQL Server index suggestions may be off?

Many thanks

UPDATE:

Here is the query - its a pretty basic query, getting a list of courses, and the number of people (men/women/paid/not paid) for each course. Would be happy to hear if there is a better way...

Interestingly, adding in the eventID = 2 at the very end, made the query run in 2 mins+ rather than 4 seconds without that filter!

    select C.*
    ,(
        select COUNT(*)
        from tblUserCourses UC
        inner join tblUserEvents on UC.UserID = tblUserEvents.UserId
        inner join tblCourses on tblCourses.courseID = UC.courseID
        where UC.courseID = C.CourseID
            and tblUserEvents.StatusID = 50
            and tblUserEvents.EventID = C.eventID
        ) as PaidParticipants
    ,(
        select COUNT(*)
        from tblUsers U
        inner join tblUserEvents on U.UserID = tblUserEvents.UserId
        inner join tblUserCourses UC on U.UserID = UC.UserId
        inner join tblCourses on tblCourses.courseID = UC.courseID
        where UC.courseID = C.CourseID
            and tblUserEvents.StatusID = 50
            and tblUserEvents.EventID = C.eventID
            and UPPER(U.gender) = 'M'
        ) as Males
    ,(
        select COUNT(*)
        from tblUsers U
        inner join tblUserEvents on U.UserID = tblUserEvents.UserId
        inner join tblUserCourses UC on U.UserID = UC.UserId
        inner join tblCourses on tblCourses.courseID = UC.courseID
        where UC.courseID = C.CourseID
            and tblUserEvents.StatusID = 50
            and tblUserEvents.EventID = C.eventID
            and UPPER(U.gender) = 'F'
        ) as Females
    ,(
        select COUNT(*)
        from tblUserCourses UC
        inner join tblUserEvents on UC.UserID = tblUserEvents.UserId
        inner join tblCourses on tblCourses.courseID = UC.courseID
        where UC.courseID = C.CourseID
            and tblUserEvents.StatusID < 50
            and tblUserEvents.EventID = C.eventID
        ) as NonPaidParticipants
from [tblCourses] C
where (eventID = 2)
order by scheduledDate
    ,internalOrder


texts: Last value in two levels of group by in MySQL

texts:
I have the following data set:

2018-01-02  Oranges 1479    1480
2018-01-03  Oranges 1478    1504
2018-01-04  Oranges 1507    1494
2018-01-05  Oranges 1510    1551
2018-01-02  Apples  2150    2196
2018-01-03  Apples  2161    2167
2018-01-04  Apples  2192    2169
2018-01-05  Apples  2169    2194

I'd like to get the following result:

2018-00 Oranges 1479    1551
2018-00 Apples  2150    2194

Where:

2018-00     Oranges   1479                        1551
year-week   grouped   first value third column    last value fourth column

Is there a way to do this in one query with MySQL?
My query actually is that:

select
    CONCAT(YEAR(date), '/', WEEK(date)) week,
    fruit,
    init,
    something_like_last_column_value_fn(end)
from fruits_flow
    where CONCAT(YEAR(date), '/', WEEK(date)) = '2018/0'
    and fruits in ('Oranges', 'Apples')
group by week, fruit;

I'm limiting in where clause only to test.

The third column value is easy because after group is the first value in line. But the last value of forth column, I really tried a lot of things and zero success.


texts: Obtaining the average of three columns per row in SQL

texts:
The AVG() function in SQL works particular column data. But here, we want to calculate the average of three such columns for each row. In math, we would do

AVG=(col1 + col2 + col3)/3

Similarly: is there any query to calculate AVG(col1, col2, col3...)?


texts: Get timing on individual queries in Anonymous code block

texts:
I have a set of queries that I need to run in succession, carrying the result from the previous query into the next. My only need is the execution time on the last query in the list, but SET TIMING ON gives me the execution time for the whole block. Any idea how to get the time of JUST the last query? The beef is that I don't want the previous queries to interfere with the timing it gives me. Example below:

set echo on;
set feedback on;
set timing on;
set serveroutput on;

declare
  pid varchar(20);
  aid varchar(20);
  bob char(1);
begin

  select st.some_id, st.person_id into aid, pid
    from some_table st
      join person p on st.person_id = p.person_id
    where p.name not like '%bob%' and rownum = 1;

  -- get timing of another query here

end;

When I run this it gives me the total execution time of the code block:

Elapsed: 00:00:00.045


texts: Is there a way to suppress DBCC TRACEON/OFF messages in the Error Log?

texts:
We have quite a few dataloading processes that run throughout the day all day long.  These processes are setting the 610 flag on for their connection then turning it off when they are done.  Because these messages are put into the ERROR LOG, our ERROR LOG is growing quite a bit.  We've tried using the WITH NO_INFOMSGS parameter of DBCC but it still puts the entry in the ERROR LOG (even though it doesn't put it in the query output).

Any ideas?

The messages look like this:

  Date      10/23/2014 8:32:46 AM Log       SQL Server (Current - 10/23/2014
  8:31:00 AM)

  Source        spid57

  Message DBCC TRACEON 610, server process ID (SPID) 57. This is an
  informational message only; no user action is required.

...

  Date      10/23/2014 8:32:54 AM Log       SQL Server (Current - 10/23/2014
  8:31:00 AM)

  Source        spid57

  Message DBCC TRACEOFF 610, server process ID (SPID) 57. This is an
  informational message only; no user action is required.


texts: DB2: Is it good to create the LONG table space to use 32 KB data page

texts:
Our DB2 database (V9.7) has only defined one table space to store all the big objects like XML.
And xml size could be different. Some are about 2kb average. And some are 7kb average.
So does it make sense to set the table space to use 32kb data page?
How to judge it? I have once seen that one data page can only contain one xml row.
Is that true?


texts: TSQL substitute for MAX() string ranking

texts:
I have a table with repeating values (col1) and I need to return col2 based on a ranking system. In my example the ranking system is handled by MAX() giving me the highest value. I then return the full row where col2 is of the highest value. Simple. But what if instead of numbers in col2 I have a set of strings, 'dog','cat','bird'. I want only the dog row for distinct values in col1. If there is no 'dog' row I'll take the 'cat' row. And finally if no cat I take the 'bird' row.

I'm looking for the string equivalent of MAX where I can rank the strings. Do I have to write a UDF? Can I do it with set based operation instead?

CREATE TABLE TESTX (
    COL1 VARCHAR(56),
    COL2 VARCHAR(56),
    COL3 VARCHAR(56),
    COL4 VARCHAR(56)
)

INSERT INTO TESTX (COL1,COL2,COL3,COL4) VALUES (1,1,3,4)
INSERT INTO TESTX (COL1,COL2,COL3,COL4) VALUES (1,3,3,4)
INSERT INTO TESTX (COL1,COL2,COL3,COL4) VALUES (2,2,3,4)
INSERT INTO TESTX (COL1,COL2,COL3,COL4) VALUES (2,2,3,4)
INSERT INTO TESTX (COL1,COL2,COL3,COL4) VALUES (3,1,1,1)
INSERT INTO TESTX (COL1,COL2,COL3,COL4) VALUES (3,2,2,2)
INSERT INTO TESTX (COL1,COL2,COL3,COL4) VALUES (3,3,3,3)

SELECT * FROM TESTX

WITH CTE_X AS (
   SELECT
      COL1, MAX(COL2) AS COL2
   FROM TESTX
   GROUP BY COL1)
SELECT DISTINCT
    A.COL1, A.COL2, A.COL3, A.COL4
FROM
    TESTX A
JOIN
    CTE_X B ON A.COL1 = B.COL1 AND A.COL2 = B.COL2


texts: Script to backup TDE certificate and Key throws error Cannot write into file

texts:
I am attempting to automate TDE key backups.  I created the stored procedure below and was going to schedule it to be executed as a sql agent job.
However, when I execute the procedure I get this error after about 20 seconds:

Msg 15240, Level 16, State 1, Line 3
Cannot write into file '\\FileServerName\TDEcertificateBackups\ServerName_CertificateName_CurrentDate_key.pvk'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.

I have tried the following:

Verified that the SQL Agent account has full access to the UNC path.
UNC to the UNC path to validate it.
File does not already exist as the folder location is empty
Used the PRINT @SqlCommand results and ran them under my domain admin account.
googling it :)

After reading the documentation, saving to a UNC path should work.
Is this a permissions issue that I need to track down with the network/sys admins, an error with my script, or some other "gotcha" I'm not currently aware of?
Stored Procedure:
CREATE OR ALTER PROC dbo.BackupTDEcertificate
AS
SET XACT_ABORT, NOCOUNT ON
/*
    This script can be used to backup the certificate and private key used for TDE.
*/

/*Declare variables*/
DECLARE @DateTime NVARCHAR(8)
SET @DateTime =
        CAST(YEAR(GETDATE()) AS NVARCHAR(4))
        + CAST(MONTH(GETDATE()) AS NVARCHAR(2))
        + CAST(DAY(GETDATE()) AS NVARCHAR(2))
--SELECT @DateTime
DECLARE @CertificateName NVARCHAR(50) = (
    SELECT TOP 1 CER.name [Certificate]
    FROM
        master.sys.certificates CER
    ORDER BY
        CER.start_date DESC
)--SELECT @CertificateName
DECLARE @Path NVARCHAR(150) = '\\FileServerName\TDEcertificateBackups\'
--SELECT @Path

/* Perform the backup*/
DECLARE @SqlCommand NVARCHAR(2000) =N'
        USE master;
        BACKUP CERTIFICATE ' + @CertificateName + '
         TO FILE = N''' + @path + @@SERVERNAME + '_' + @CertificateName + '_' +@DateTime + '.cer''
             WITH PRIVATE KEY (
             FILE = N''' + @Path + @@SERVERNAME + '_' + @CertificateName + '_' + @DateTime + '_key.pvk'',
            ENCRYPTION BY PASSWORD = ''SuperSercretPassword''
            );
'
PRINT @sqlcommand --Actual procedure has this line commented out.
--exec sp_executesql @sqlcommand --Actual Procedure does not comment this line out.


texts:
My application server is running SQL Server 2008 R2.
OS is Windows 2008 R2 with SP1.
Total physical memory is 16 GB.

When I looked into the Task Manager, the sqlservr.exe was using "6,734,588 K".

When I looked the Memory Setting, it's all default (see below).

It is obvious that the max memory can go as high as 2,147,483,647,000,000 in BYTES, which is more than the available physical memory.

How much is the recommended setting for typical server like mine?

My application sometimes throw "out of memory" error. I haven't linked that issue with the SQL Server, but I am trying to see what I can do in terms of memory usage.

If I changed the max memory setting, do I need to restart the SQL Server?


texts: SSIS Package Duration is big before it begins executing

texts:
hello i have this question.
From Visual Studio i have deployd package in Sql server and i am manualy executing Package.
BUt i have noticed that package need 6 sec to do all things  but Duration is 8 sec Like on pictures so can you tell me what is  this 2 sec?


texts: PostgreSQL - getting Too many client repeatedly

texts:
I am running PSQL with PostGis extension. After the start of server, after about 3 weeks, I start getting fatal errors for connections with "too many clients already". When I restart the server, problem is gone, but is back again after aprox. 3 weeks.

If I run pg_stat_activity, I got

SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

"active";2
"idle";944

And all idle queries are the same - SELECTs.

I cannot debug this problem and restarting server all over is not a real solution.


texts: Why does my table keep getting corrupted after removing large amounts of data?

texts:
I have a database that is about 400 gigs. The majority of the space is consumed by images of utility bills stored in string form as varchar(max) in a single table. It's a poor database design that I inherited. I have already exported the utility bills as pdfs to a network folder. Now I am trying to replace the images in the database with an empty string. I have been updating in small batches of 40k to 80k records at a time. The problem is that after a few batches, the table gets corrupted. I'll get a message that the update failed. When I run CHECKDB I get

Msg 8909, Level 16, State 1, Line 1 Table error

It's happened twice so far and I have to restore the database from a backup.
I need to know why this is happening and if there is a way to avoid it. I'm doing this in a test environment and I need to get this problem figured out before I can work on the production database.
Thank you for your help.
Edit: Changed binary to string. One of the images is stored as a continuous string of 65,535 characters.
Edit: This is happening on MSSQL 2014 SP3 (Version 12.0.6024.0)


texts: debian sudo service mongod status shows failed but mongo is working

texts:
I started a replica set with three servers with default port and used a different data directory. But when i did sudo service mongod stop. it said failed. so i went into mongo shell and did a shutdown and mongo shell stopped working. now when i did sudo service mongod start it said failed but i was able to go to mongo shell and the reolica set was working. I checked sudo service mongod status it said failed.

sudo service mongod status
[FAIL] Checking status of database: mongod apparently not running failed!

i edited mongod.config to have a replica set name , commented out bind_ip, used keyfile for auth. i was working fine before starting a replica set

mongo version 2.6.5.
installed using instructions from http://docs.mongodb.org/manual/tutorial/install-mongodb-on-debian/

The output of ps ax | grep [m]ongo is:

/usr/bin/mongod --config /etc/mongod.conf


texts:
Using SQL 2016 Enterprise Edition

The default instance will listen on 1433 and the DAC port will listen on TCP 1434. SQL Browser will listen on UDP 1434. Any subsequent named instances will get a dynamically allocated port from windows in the range 49152 to 65535 as will their DAC ports. Connection requests (including DAC) to those named instances will make a call to SQL Browser and told the port number.

You can use the SQL Server Configuration Tool to change the port for the named instance to a static port number, but there is no option to set the DAC port to a static port number.

When I suggested to the networks team that I'd like the entire port range opened up for remote DAC connectivity, they were less than enthusiastic.

Is there any way to set the DAC port to a known, static port number for named instances?


texts: How to flush SQL Server database from RAM to hard disk?

texts:
As part of the nightly maintenance plan, my working MS SQL Server 2012 databases are backup-ed with options of simple recovery and full backup. Transaction log is not backup-ed. Also, at nights the MSSQLSERVER service or SQL Server is restarted

Checking the the properties of corresponding files in operating system (Windows Server 2008), I observe that "Modified" time of .mdf files correspond to the time of maintenance plan (making backup) run but .log files modification time is one-two weeks earlier.

Why is such difference and why the operating system fails to update modification time?

So, what is the time and reason of .mdf file modification time?
Is it restart of SQL Server or the time of backup?

How can I estimate when a database was last written to a physical disk, that is how recent is .mdf file of a corresponding database?
How can I force SQL Server to flush the database to hard disk in order to have most actualized .mdf file in case of seeing problems in SQL Server functioning?


texts: How to show a column as NULL if it doesn't exist in match?

texts:
I have a query as shown below.
In this query I am trying to get the result of orders which don't have messages (which I thought as null). But I was expecting that this query would produce a NULL in message_id if someone has an order and doesn't have a message. What should I do do reach my goal?

select *, a.message_id as x
from
  messages a
  FULL OUTER JOIN customers b ON a.customer_id = b.customer_id
  FULL OUTER JOIN orders c ON c.customer_id = b.customer_id
  FULL OUTER JOIN delivery_templates d ON c.billing_template_id = d.delivery_template_id
  FULL OUTER JOIN products e ON a.product_id = e.product_id
  FULL OUTER JOIN order_items f ON f.order_id = c.order_id
where
  a.seller_id in (2, 3, 1, 6)
  AND
  a.message_id ISNULL


texts: Select not repeated results from same tables in different databases

texts:
I have 3 tables in 3 different databases which are exactly the same. I'm trying to sync from this 3 databases to a master database as shown in the picture below:

Right now I am running the same script (made in PHP) and changing all the time the slave database (in the figure db1, db2, db3 act as slaves) and I want to change that. I have the following query that returns data from user table:

SELECT
  u.id AS uid,
  u.username AS username,
  u.firstname AS ufirstname,
  u.lastname AS ulastname,
  u.email AS uemail,
  u.isactive AS uisactive,
  u.lastmodifieddate AS ulmd,
  t.id AS tid,
  t.name AS tname,
  t.lastmodifieddate AS tlmd,
  ut.lastmodifieddate AS utlmd
FROM `territory` AS t
JOIN `userterritory` AS ut ON (ut.territoryid = t.id)
JOIN `user` AS u ON (ut.userid = u.id)
WHERE u.lastmodifieddate > some_date_value
   OR ut.lastmodifieddate > some_date_value
   OR t.lastmodifieddate > some_date_value

But as I said before I want to run the same query in the 3 databases at the same time and not get repeated results? Any help?

There are a few tables included on the query as you may notice but think that 3 databases are exactly the same and can share different or the same data in some cases so use or don't the tables as reference on your answer. DB's are running on the same server and share the same credentials


texts:
After restoring from a percona-xtrabackup without a preparation step, ib_logfile0 and ib_logfile1 were not present in the restore data. Therefor during startup the following message is observed:

140223 04:10:24 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
140223 04:46:51 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140223  4:46:51  InnoDB: Initializing buffer pool, size = 18.0G
140223  4:46:54  InnoDB: Completed initialization of buffer pool
140223  4:46:54  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
140223  4:46:54  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
140223  4:46:54  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
140223  4:46:55  InnoDB: Error: page 7 log sequence number 45 3368182252
InnoDB: is in the future! Current system log sequence number 42 975484940.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html

How can I fix this?


texts:
I want to select an exact subset from a table.

DDL to create table:

CREATE TABLE groups (group_id NUMBER, activity_id number);
INSERT INTO groups (group_id, activity_id) values (1,1);
INSERT INTO groups (group_id, activity_id) values (1,2);
INSERT INTO groups (group_id, activity_id) values (2,1);
INSERT INTO groups (group_id, activity_id) values (2,2);
INSERT INTO groups (group_id, activity_id) values (2,3);
INSERT INTO groups (group_id, activity_id) values (3,1);
INSERT INTO groups (group_id, activity_id) values (3,2);
INSERT INTO groups (group_id, activity_id) values (3,3);
INSERT INTO groups (group_id, activity_id) values (3,4);

If I want the group_id of activity_ids (1, 2 and 3), then I would like only the exact match for group_id 2 to return, not 1 and 3.

I can do this with LISTAGG but wanted to see if there was a cleaner solution or suggestions if I should structure my table differently.


texts: SQL Server Transactional Replication delay

texts:
I am running into strange situation currently. I have a transactional replication configured between 2 servers. When I update a record in one article it gets replicated after 30 mins. I am unable to figure out why.

Log reader and distributor agent is setup to run continuously. There is no replication latency/errors found in replication monitor. But still it happens.

A tracer token is very fast. It just shows 5 secs as overall latency. The article I am updating is added into multiple publication and my environment is SQL Server 2012. No blocking. SQL Server is running fine. I don't see any resource intensive queries running on the server.

I would appreciate if any one could help me figure out this issue.


texts: Excel / MS SQL Server: Exception of type Microsoft.Office.PowerPivot.BackEnd.StorageException

texts:
We have an Excel table that gather data from our datawarehouse db on a SQL Server 2016. It queries a relative simple view to find the top customers of the last few days (including some aggregated columns) from a very large (some billion rows) ColumnStore table. The query usually needs about 1 minute to return about 200-300 rows.

A few days ago it started (randomly) to throw a very unspecific Exception of type Microsoft.Office.PowerPivot.BackEnd.StorageException error message after opening and / or manually refreshing the data in the Excel sheet (after about 30 seconds).

While Excel is refreshing the data, I can see the query when running sp_WhoIsActive / sp_BlitzWho.

Executing the query manual in SSMS works usually fine, there was no real workload at the server and after some tries (in SSMS) Excel started to work again.


texts: How to exclude the entire result of a Grouped row based on a value of any of the rows it contains

texts:
I have a table that looks something like this:
 ID | Class | Item
 ---+-------+------
 1  | 1     | 1
 2  | 1     | 2
 3  | 2     | 1
 4  | 2     | 3
 5  | 3     | 2

and a query that looks something like this:
Select Class, MAX(Item) FROM myTable WHERE Item <> 3 GROUP BY Class

This results in:
  Class | Item
 -------+------
  1     | 2
  2     | 1
  3     | 2

However I want to exclude all results where a Class contains an Item with the value of 3, not just rows that are 3 so it should return:
  Class | Item
 -------+------
  1     | 2
  3     | 2


texts: How do I define a 'flexible' relationship in my table?

texts:
I'm working on a large application, with tons of tables and a huge codebase. It's a system in which people have rights to use functionality X and/or functionality Y and/or functionality Z.

Functionality X may be a user-related record in a table called dossiers.
Functionality Y may be a user-related record in a table called portfolios.
Functionality Z may be a user-related record in a table called api-couplings.

There's a bunch more but I'm trying to keep it straight-forward here. The problem I'm facing is that there's no overarching object called Product. A table in which we set the prices, the invoicing interval and what not. Functionalities X to Z are now granted to the user by an admin who just adds a record to those respective tables. That's gonna change; a user will be able to just select a product, pay and use it right away.

How do I setup this table flexibly? How do I refer to those individual tables? An option I do not want is this:

id
product_name
price
functionality_x_id (nullable)
functionality_y_id (nullable)
functionality_z_id (nullable)

That just feels wrong and will have me change the database table every time we add a new product type.

Something I did think about is something like this:

id
(...)
functionality_table (functionality_x, functionality_y, functionality_z)
functionality_record_id

That sounds way more flexible. I do lose my relational integrity, but I don't know if that's such a big issue.

Just to sum it up: in the end, the user has to select a product from which a record in any of the applicable tables will get created for him/her and thus grants the user privilege to that functionality.


texts: Do SQL Server compressed indexes remain compressed on rebuild without specifying data compression?

texts: After one rebuilds their SQL Server indexes using page compression (ALTER INDEX IX1 REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)), do subsequent rebuilds (as done by some maintenance scripts past a certain fragmentation threshold) need to specify data compression again? Would the indexes otherwise be effectively decompressed?


texts:
Consider the following table:

i | a | b | c
--------------
0 | 1 | 2 | 3
1 | 4 | 5 | 6
2 | 7 | 8 | 9
3 | x | y | z

The table, in the end, will have several thousand rows, and even more columns then described here (20+).  I plan on adding indexes to the table, but cannot figure out which indexes to make.  In the end, I'll be doing SELECT statements with different combinations of these, and sometimes only on 1 column.  Is the most efficient way to make the indexes:

(A) Index each column separately, eg. aIndex(a), bIndex(b), cIndex(c), etc..

(B) An Index for each type of select statement, eg. aIndex(a), abIndex(a,b), acIndex(a,c), etc..

(C) Just index them all together into one, eg. tIndex(a,b,c).

Indexes are one of those things I'm still getting a grasp on, so I apologize if this seems like a silly or simplistic question.  What I'm trying to avoid is unnecessary indexes as they will increase disk-space and INSERT times dramatically.


texts: How to prevent Performance issue & going for Denormalization

texts:
We are planning to do an e-commerce shopping site with PHP-MYSQL combination.

I read in almost every link related to Normalization & Denormalization : Normalize until it hurts, denormalize until it works.

One thing I understood is Normalization is must for good performance , am i right ?

We have to do Denormalization only when we face problems of Performance [ the very low page speed ] , otherwise Denormalization is not required.

Is for 100% it will hurt - Normalization? can we do anything to prevent [not to hurt.] can we take some steps for performance boost other than Denormalizing & Refactoring complete database again.

Can we do Denormalization without doing Normalization before ?


texts: Autovacuum is not running even after configuring it on

texts:
I am using aurora-postgresql version 9.6.8. and I have one main instance , one writer and one reader replica.

I am looking for below information:

How I can check on a particular instance when autovacuum was executed when I am trying to use "pg_stat_user_tables" this show lot of dead tuple. But when we see max last autovacuum executed date its one month back. Seems like autovacuum not executed from last one month on any of the table. In my environment we have huge insert update and delete every day.

Thank you in advance for any suggestion!!!

Below is my auto vacuum configuration:

name    setting
autovacuum_analyze_scale_factor 0.05

autovacuum_analyze_threshold    50

autovacuum_freeze_max_age   200000000

autovacuum_max_workers  4

autovacuum_naptime  5

autovacuum_vacuum_cost_delay    5

autovacuum_vacuum_cost_limit    -1

autovacuum_vacuum_scale_factor  0.1

autovacuum_vacuum_threshold 50

maintenance_work_mem    4122624

vacuum_cost_limit   200

vacuum_freeze_min_age   50000000


texts: MySQL GRANT requiring additional permissions

texts:
I have a MySQL user (rundeck) that is being used as part of a deployment process. Its purpose is to create a new database, create a new user, and grant permissions to the new user for the new database. The problem is that I can't seem to find a granular permission for the user that allows granting permissions without also exposing the data in the database. If I run with any permissions other than GRANT and ALL, I get the following output:

mysql> GRANT ALL ON `mydatabase`.* TO ''@'192.168.101.%';
ERROR 1044 (42000): Access denied for user 'rundeck'@'hostname.com' to database 'mydatabase'

I really would like to restrict the rundeck user from accessing the data, but can't get GRANT to work without also using ALL. If I use both together, it works perfectly, unsurprisingly. We suspect that the mydatabase.* may be the element requiring additional permissions. Can anyone suggest a group of permissions that will allow a user to grant permissions to a database that they cannot access the data to?

SHOW GRANTS output from working configuration:

GRANT ALL PRIVILEGES ON . TO 'rundeck'@'hostname.com' IDENTIFIED BY PASSWORD 'passhash' WITH GRANT OPTION

SHOW GRANTS output from non-working configuration:

GRANT CREATE, DROP, RELOAD, SHOW DATABASES, CREATE USER ON . TO 'rundeck'@'hostname.com' IDENTIFIED BY PASSWORD 'passhash' WITH GRANT OPTION


texts: Failed sp_executesql queries and rpc_completed

texts:
I'd like to track which procedures are failing in my database using extended events. Procedures are being executed like that from an application:

EXEC sp_executesql 'exec dbo.GetData';

And when that fails, I expect to capture XE event of rpc_completed with result = 1 (failure) but it comes with result = 0

My procedures look like that:

CREATE PROCEDURE dbo.GetData
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        SELECT 1/0 AS DivisionByZero;
    END TRY
    BEGIN CATCH
        EXECUTE dbo.LogError;
        THROW;
    END CATCH
END

Is there some sort of configuration that needs to happen to track failed rpc_completed events or I do something wrong?


texts: Get ids only where one id is null and other isn't

texts:
I have a table as such where id's are repeating .

    +-------+------+
      ID      NUM
    +-------+------+
      1        50
      1        51
      2        52
      2        NULL
      3        NULL
      3        NULL
      4        53
      4        54
      4        NULL
      5        NULL
      6        55
      7        NULL
    +-------+------+

I want to get table where i only have the ids which have both null and non null values in the column labelled (num)

like so

 +------+-----+
   ID     NUM
 +------+-----+
   2      52
   2      NULL
   4      53
   4      54
   4      NULL
 +------+-----+


texts: EXCEPTION logic is not working in Oracle PL/SQL function

texts:
These are the tables I have created and inserted some values:

CREATE TABLE DEPARTMENT
(DEPARTMENT_ID NUMBER PRIMARY KEY,
 DEPARTMENT_NAME VARCHAR(30) NOT NULL
 );

CREATE TABLE EMPLOYEES
(EMPLOYEE_ID NUMBER PRIMARY KEY,
 FIRST_NAME VARCHAR(20) NOT NULL,
 LAST_NAME VARCHAR(25) NOT NULL,
 EMAIL VARCHAR(25) NOT NULL,
 PHONE_NUMBER VARCHAR(20) NOT NULL,
 HIRE_DATE DATE NOT NULL,
 JOB_ID NUMBER NOT NULL,
 SALARY DECIMAL NOT NULL,
 DEPARTMENT_ID NUMBER NOT NULL,
 CONSTRAINT emp_job_fk FOREIGN KEY(JOB_ID) REFERENCES JOBS(JOB_ID),
 CONSTRAINT emp_department_fk FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPARTMENT(DEPARTMENT_ID)
 );

INSERT INTO DEPARTMENT (DEPARTMENT_ID,DEPARTMENT_NAME)
VALUES(1,'IT');
INSERT INTO DEPARTMENT (DEPARTMENT_ID,DEPARTMENT_NAME)
VALUES(2,'Sales');
INSERT INTO DEPARTMENT (DEPARTMENT_ID,DEPARTMENT_NAME)
VALUES(3,'Accounting');

INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
VALUES (1,'Tony','Starc','starc@gmail.com','0123456789',TO_DATE('15/1/2008','DD/MM/YYYY'),1,45000.00,1);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
VALUES (2,'Bruce','Wayne','bruce@gmail.com','0123456788',TO_DATE('15/1/2009','DD/MM/YYYY'),1,40000.00,1);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
VALUES (3,'Larry','Ellison','larry@gmail.com','0123456787',TO_DATE('15/1/2010','DD/MM/YYYY'),1,30000.00,1);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
VALUES (4,'Steve','Jobs','steve@gmail.com','0123456786',TO_DATE('15/1/2011','DD/MM/YYYY'),2,35000.00,2);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
VALUES (5,'Remy','Lebeau','remy@gmail.com','0123456785',TO_DATE('15/1/2012','DD/MM/YYYY'),2,30000.00,2);
INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)
VALUES (6,'Clark','Kent','clark@gmail.com','0123456784',TO_DATE('15/1/2013','DD/MM/YYYY'),2,20000.00,2);

Then I have created a function to display number of employees working in a specific department:

CREATE or REPLACE FUNCTION GET_EMP_COUNT_JC450912 (dept_name VARCHAR)
RETURN VARCHAR
IS
no_of_employees NUMBER;
BEGIN
SELECT COUNT(*) INTO no_of_employees
FROM    DEPARTMENT, EMPLOYEES
WHERE   DEPARTMENT.DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID
AND DEPARTMENT_NAME = dept_name;
DBMS_OUTPUT.PUT_LINE ('No of employees');
RETURN no_of_employees;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('Department Not Available');
END GET_EMP_COUNT_JC450912;

BEGIN
DBMS_OUTPUT.PUT_LINE(GET_EMP_COUNT_JC450912('IT'));
END;

DBMS OUTPUT:
No of employees
3

BEGIN
DBMS_OUTPUT.PUT_LINE(GET_EMP_COUNT_JC450912('Sales'));
END;

DBMS OUTPUT:
No of employees
3

As you can see from the code above the function is successfully displaying number of employees working in a specific department when I'm entering valid department name.
However, the problem that I'm facing is when I'm entering an invalid department name (that doesn't exist in department table), it is supposed show the error message 'Department Not Available' as I have set [EXCEPTION
    WHEN OTHERS
    THEN
    DBMS_OUTPUT.PUT_LINE ('Department Not Available');]

Instead I'm getting this message when I'm entering invalid department name:

BEGIN
DBMS_OUTPUT.PUT_LINE(GET_EMP_COUNT_JC450912('Medicine'));
END;

DBMS OUTPUT:
No of employees
0

Where could I possibly go wrong ?


texts: Designing a Products database: One Products table or many separated by vendor?

texts:
In order to prevent the unwieldiness of managing product data spread across a number of excel spreadsheets, I have set up a Products database.

My data is in spreadsheets because it was (somewhat) easy to export a tab-delimited text upload file for both the Amazon and Google e-commerce platforms.

But I've realized that I can achieve the same simple exporting goal through a relational database, minus the inconsistency headaches and plus the added benefit of an expressive query language.

Now my main question is how I should design my database; and, namely, how many tables my Products database should contain. I see three options here:

1 table for all products. This seems to me like it would avoid redundancy, and thus feels like the best choice. Take an example: if I decide to add an attribute to the database at a later date, I would not have to do so for each vendor/category (see option 2 and 3). I am inclined to say that there is a downside because products of one category will have many NULLs in attributes meant for other categories, but I'm not sure if this is actually a negative.
1 table for each vendor and its products. This was my instinctive first choice, but I don't find it to be the most logical division: what's critical in determining the uniqueness of a product's attributes is its category, not which vendor it was purchased from. Moreover, even though one vendor will often specialize in a certain kind of product, there will be category overlap if a vendor sells more than one kind of product.
1 table for each kind of product (e.g. padlocks, chain, safety equipment). I think this is the most reasonable choice other than number 1, because it is almost a guarantee that one kind of product will require different attributes than another. The obvious con I see to this strategy is the difficulty of making the divisions. I'll take hard hats as an example. Should hard hats have their own table? Certainly not. (Unless of course your business is in hard hats.) So, moving up the category chain, should construction accessories have its own table? Maybe closer. How about safety apparel? That makes sense to me, but then there will be the same problem of the many NULLS as option 1 because hard hats will be lumped in with safety vests and goggles etc. etc.

Hopefully I'm not way off the mark with my suggestions. I didn't want to ask before giving my questions some thought, but I'm new to databases, so I'm sure I didn't make the most informed reasoning. I'm heavily leaning towards option number 1, but I'd love to hear advice on any, or if I missed an obvious strategy entirely.


texts: Update columns values using select from external database and table in MariaDB trigger

texts:
I have two databases pdone and veevan. pdone is a kind of master and veevan keep on continue sync against an external sources. I need to perform a internal sync between veevan and pdone so I am using triggers for achieve this. Now at pdone db I have a table targets and at veevan I have a table address_vod__c. Every time a new record is inserted on address_vod__c or every time a record is updated I need to update the row at pdone.targets so this is how the process should run:

INSERT a new record on veevan.address_vod__c, then SELECT the current inserted row (the values) find the match on pdone.targets and UPDATE columns
UPDATE a record on veevan.address_vod__c, then SELECT the current updated row (the values) find the match on pdone.targets and UPDATE columns

This is what I have so far:

USE `veevan`;

DELIMITER $$

DROP TRIGGER IF EXISTS veevan.account_ai$$
USE `veevan`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `veevan`.`address_vod__c_ai` AFTER INSERT ON `address_vod__c` FOR EACH ROW
BEGIN
    DECLARE vCount INT(6) DEFAULT 0;
    SET vCount=(SELECT COUNT(*) FROM pdone.targets WHERE targets.veeva_account_id=NEW.account_vod__c);
    IF (vCount>0) THEN
        SET @TargetLastMod=(SELECT targets.lastSyncAt FROM pdone.targets WHERE targets.veeva_account_id=NEW.account_vod__c LIMIT 0,1);

        UPDATE pdone.targets
        SET
        `address1` = avc.address1,
        `address2` = avc.address2,
        `city` = avc.city,
        `state` = avc.state,
        `zip` = avc.zip,
        `phone` = avc.phone,
        `fax` = avc.fax,
        `lastSyncAt` = NOW(),
        `updatedAt` = NOW()
        FROM (
            SELECT
            address_vod__c.name AS `address1`,
            address_vod__c.address_line_2_vod__c AS `address2`,
            address_vod__c.city_vod__c AS `city`,
            address_vod__c.state_vod__c AS `state`,
            address_vod__c.zip_vod__c AS `zip`,
            address_vod__c.phone_vod__c AS `phone`,
            address_vod__c.fax_vod__c AS `fax`
            FROM
                address_vod__c avc
            WHERE
                address_vod__c.account_vod__c = NEW.account_vod__c
                    AND external_id_vod__c IS NOT NULL
                    AND address_vod__c.primary_vod__c = 1
                    AND account.recordtypeid COLLATE utf8_general_ci IN (SELECT id FROM veevan.recordtype WHERE name = 'Professional_vod')
        )
        WHERE
            targets.veeva_account_id=NEW.account_vod__c;
    END IF;
END$$
DELIMITER ;

But I don't know if the UPDATE using that SELECT will works and that is my doubt, how do I achieve an UPDATE from SELECT? Any time I try to run that SQL on I got this error:

  Error Code: 1064. You have an error in your SQL syntax; check the
  manual that corresponds to your MariaDB server version for the right
  syntax to use near 'FROM     SELECT              address_vod__c.name
  AS address1,             addr' at line 19

Can any give me some feedback and|or help on this?

veeva_account_id is the PK on pdone.targets and account_vod__c is the FK (kind of since that DB has not real relationships) on veevan.address_vod__c.


texts: How can I detect the availability of sp_addextendedproperty on sql server

texts:
How can I find out if sp_addextendedproperty is available before trying to use it in a sql script?

I gather it's not available on Azure.

(I'm working on this bug, if you're interested).


texts: What interval does SQL Server Profiler's "duration" refer to?

texts:
I've been having strange things on a production server. Clients (all on the local LAN) are getting timeout errors on queries that should have no problem at all.

it happens during times of low CPU usage (<1%)
and when there is low hard drive I/O (<1%)

I spent an afternoon watching traces live in SQL Server Profiler, and the absolute epitome of the non-sense was the batch:

SELECT GETDATE() As ServerDate

Where profiler shows the numbers:

CPU: 0
Reads: 0
Writes: 0
Duration: 17,130 ms
StartTime: 2017-02-28 15:00:20.777
EndTime: 2017-02-28 15:00:37.907

What can possibly be going on in this database engine that perfectly benign queries take too long?

anti-virus software?
a VMWare hypervisor that doesn't schedule the virtual machine for 17 seconds?
a kernel model stall for 17 seconds that doesn't show up in Task Manager?

Because even if the server were busy, a query that takes:

zero cpu
zero reads
zero writes
and has no side effect on any pages in memory

should not take 17 seconds to complete.

Duration

The only way forward is to try to figure out what duration means.

What does duration mean? Is it:

Start: after entire batch has been received by the database engine
End: after the query is complete

or is it:

Start: after entire batch has been received by the database engine
End: after the last byte has been sent to the client

or is it:

Start: after entire batch has been received by the database engine
End: after the last byte has been received by the client

(but then how does SQL Server know when the last byte has been "received" by the client).

Because given that:

there's nothing wrong on the server
there's nothing wrong on the database
there's nothing wrong on the client

there must be something wrong in between. So I need to know exactly what conceptual events SQL Server uses to denote the start and end.

Locks

Duration from SQL Server 2005 traces is stored (i.e. when you save the trace to a table or file) in microseconds (one-millionth of a second)
Duration in Profiler is presented (i.e. on screen and in a screenshot) in milliseconds (one-thousandth of a second)

Of course, milliseconds vs microseconds was not my question.

Given that the only lock that getdate() takes is:

Acquire: S (shared) lock on D (database)
Release: S (shared) lock on D (database)

If the issues were due to locking:

can anyone suggest why acquiring a shared lock on a database might take 17 seconds
and if it were due to a 17 second delay acquiring a shared database lock
why other batches can continue to acquire/release shared database locks without a stall?
and if the stall was due to taking 17 seconds to acquire a lock
what I can do about it?

That also wasn't my question. My question is tautological.

Tautology

My question as written is pretty simple:

  What does duration mean?

Of course that's trivial to answer:

  duration is EndTime - StartTime.

Which means I'm actually asking (without being pedantic):

during what stage of query execution is StartTime timestamp recorded?
during what stage of query execution is EndTime timestamp recorded?

If I can get an answer to that, my question next will be to find all events that go in between.

Bonus Chatter

It might be helpful to see a timeline of all events during select getdate():

Acquire Shared Database lock
Release Shared Database lock
SP:CacheMiss
SQL:BatchStarting
SQL:StmtStarting
SQL:StmtCompleted
SQL:BatchCompleted

Of course, this is just one, non-repeatable, random, example of queries that take an inordinate amount of time to run.

Bonus Reading

SQL Server insert randomly takes 10+ seconds to complete?  (closed by grumpy people)
SQL Profiler CPU / duration units (it's 17,000 ms)
View and Analyze Traces with SQL Server Profiler


texts: Trigger to apply user mapping to logins on database creation

texts: Does anyone have any example triggers that apply user mapping on database creation?


texts: How can I restore a master database from its bak file?

texts:
During a penetration test I found multiple .bak-files for an MSSQL database. I want to restore them all. In a Windows VM I installed SQL Server 2017. For restoring the master database, I put the server in single-user mode and tried restoring the database as follows (also tried the same via SSMS):

RESTORE DATABASE master
FROM DISK = 'C:\Foo\<path>\master.bak'
WITH MOVE 'master' TO 'C:\Foo\master.mdf',
MOVE 'master_log' TO 'C:\Foo\master_log.ldf',
REPLACE
GO

This gave me an error like:

  .. can't be restored because it was created by a different version of the server (13.00.4435) than this server (14...).

I then downloaded MSSQL 2016 with service pack 1 by subscribing to "Visual Studio Dev Essentials" on my.visualstudio.com. Now I got the error:

  .. can't be restored because it was created by a different version of
  the server (13.00.4435) than this server (13.00.4001).

So now I needed to update. I downloaded MSSQL 2016 service pack 2, which gave me the error:

  .. can't be restored because it was created by a different version of
  the server (13.00.4435) than this server (13.00.5026).

So I really seem to require the exact patch. However, I can't find a list of patches with corresponding server versions. Does anyone have an idea on how to find the right patch?

If I could just extract the .bak file to its .mdf- and .ldf files that would be enough. I found a bunch of tools for restoring MSSQL .bak files, but want they all want to connect to a database (to do a simple restore). I'd be very happy if I could either get the right SQL Server version or extract the .bak file.


texts: PostgreSQL server integrity after losing a tablespace

texts:
I'm running a PostgreSQL server with 2 tablespaces: 'ssd' and 'hdd'. The first contains databases that need to be queried quickly, and is located on a single SSD that is not redundant in any way (no RAID). I don't care if I lose these databases to a disk failure. By contrast, the 'hdd' tablespace is located on a RAID array and contains critical databases that need to survive a disk failure.

Question: if my SSD fails and PostgreSQL loses the 'ssd' tablespace completely (or it becomes corrupt/unstable), will databases in 'hdd' continue functioning normally? Or will the entire server experience issues?


texts:
I'm currently writing a plpgsql function that will select from a table and produce a substantial amount of data.

I was originally going to return an array, but I found a resource that explains the "RETURN NEXT operation - Very handy for callers.

However, I read this blurb which is concerning:

  Note: The current implementation of RETURN NEXT and RETURN QUERY stores the entire result set before returning from the function, as discussed above. That means that if a PL/pgSQL function produces a very large result set, performance might be poor:

Is there a Postgres feature that would allow my function to stream the results back to the caller as they're produced? This might be a limitation of Postgres' architecture in general, but I want to make sure I'm not overlooking something!


texts: Allocate space for object 'dbo.SORT temporary run storage

texts:
DBCC CHECKDB returns:

  Could not allocate space for object 'dbo.SORT temporary run storage:
  140737951236096' in database 'tempdb' because the 'PRIMARY' filegroup
  is full.

  Create disk space by deleting unneeded files, dropping
  objects in the filegroup, adding additional files to the filegroup, or
  setting autogrowth on for existing files in the filegroup.

  Msg 9002,
  Level 17, State 4, Line 1


texts:
This is probably a rudimentary question for most, but for some reason I'm not getting it right.  I currently use two specific SET formulas for "1st day of last month" and "Last day of last month"

1st day of last month (11/1, November 1st):

SET @strFirstofMonth = (SELECT CONVERT(VARCHAR, DATEADD(m, DATEDIFF(m, 0, DATEADD(MM, -1, @dtDate)), 0), 112))

Last day of last month (11/30, November 30th):

SET @strLastofMonth = (SELECT CONVERT(VARCHAR, DATEADD(m, DATEDIFF(m, 0, DATEADD(m, 1, DATEADD(MM, -1, @dtDate))), -1), 112))

I'm now needing a formula for the 1st day of the month before last, and the last day of the month before last

Example.  If today is 12/6 (December 6), I need a formula for 10/1 (October 1st) and 10/31 (October 31st).


texts:
I have a daily reporting system in place which shows the number of calls made by a particular telecaller in a day. And it works well.

I'd like to bifurcate it further into hours for e.g 9-10, 10-11 and so on.

The current mysql code is this. Please excuse imperfect code, I'm a self learner and have learnt what I have from Stackexchange.

    SELECT
comments.hrname,
IF((HOUR(comments.date))= '9', COUNT(comments.Outcome),'') AS `9-10`,
IF((HOUR(comments.date)) = '10', COUNT(comments.Outcome),'') AS `10-11`,
IF((HOUR(comments.date)) = '11', COUNT(comments.Outcome),'') AS `11-12`,
IF((HOUR(comments.date)) = '12', COUNT(comments.Outcome),'') AS `12-13`,
IF((HOUR(comments.date)) = '13', COUNT(comments.Outcome),'') AS `13-14`,
IF((HOUR(comments.date)) = '14', COUNT(comments.Outcome),'') AS `14-15`,
IF((HOUR(comments.date)) = '15', COUNT(comments.Outcome),'') AS `15-16`,
IF((HOUR(comments.date)) = '16', COUNT(comments.Outcome),'') AS `16-17`,
IF((HOUR(comments.date)) = '17', COUNT(comments.Outcome),'') AS `17-18`,
IF((HOUR(comments.date)) = '18', COUNT(comments.Outcome),'') AS `18-19`,
IF((HOUR(comments.date)) = '19', COUNT(comments.Outcome),'') AS `19-20`,
IF((HOUR(comments.date)) = '20', COUNT(comments.Outcome),'') AS `20-21`,
IF((HOUR(comments.date)) = '21', COUNT(comments.Outcome),'') AS `21-22`
FROM
comments
WHERE
DATE(comments.date) = DATE(NOW()) AND
MONTH(comments.date) = MONTH(NOW()) AND
YEAR(comments.date) = YEAR(NOW())
GROUP BY
comments.hrname

And the outcome I get is this:

Where am I going wrong?

P.S This is an internal software for approx 10 users, primarily designed to eradicate shared excel use, so SQL Injection etc is not really a concern AFAIK.

Any help would be much much appreciated.


texts: Partitioning a Table Referenced by a Foreign Key

texts:
I have one table:

CREATE TABLE [dbo].[entry]
(
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [EntryDate] [datetime] NOT NULL,
    [createddate] [datetime] NOT NULL,

    CONSTRAINT [PK_Entry_ID] PRIMARY KEY CLUSTERED
    (
        [ID] ASC
    )
    WITH
    (
        PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80
    ) ON [PRIMARY]
) ON [PRIMARY];

...where the primary key is referenced by another table.
To partition this table I have already done following steps :

 CREATE PARTITION FUNCTION EntryFunc (DATE)
   AS RANGE LEFT
   FOR VALUES ('2011-01-01')

  CREATE PARTITION SCHEME EntryScheme
    AS PARTITION EntryFunc
    TO ([FileGroup2], [PRIMARY])

The above 2 steps successfully completed,but when I am partitioning table I am not able to drop primary key clustered index because it is referenced by other table. My motive is to partition the table Entry according to created date.


texts: Report server (SSRS) email delivery delayed for 10 hours

texts:
Weird thing happened the other day.
Around 2 am there are lots of subscriptions fires on report server that sends emails.
Users received some of them, but not all.
Then around noon, all of the sudden users started to receive bunch of emails.
It turned out those were emails that supposed to be delivered at 2 am, however, the delay took around 10 hours (till noon).
It seems like subscriptions did the job and fired emails, but those emails were queued somewhere for such long period of time, then delivered all at the same time.
I looked at ExecutionLog1, 2 and 3. Looks all normal. Between start and end only 3 seconds.

Agent job also fine. Finished successfully at 2 am.
What could cause such a huge delay in email delivery?
It happened on Monday, which can be busy but not as busy as it would be at the end of the month.
Where else should I look for a reason?


texts: How to test mirroring with a single Microsoft SQL Server license?

texts:
I want to test mirroring in Microsoft SQL Server Enterprise in order to decide if I would implement one in production. Buying another copy of SQL Server for another machine is not an alternative, given the high cost of the product.

When configuring the mirroring and specifying the same server as the mirror server instance, the following error is shown:

  The principal and mirror server instances cannot be the same instance of SQL Server. Select another instance as the mirror server instance.

How can I test and discover mirroring feature of Microsoft SQL Server without having to purchase additional licenses?


texts: What causes disk space growth from Informatica load onto SQL Server?

texts:
We've been dealing with huge disk growth on SQL Server from Informatica.  After the load, the database grows to 2.4TB.  After the database shrink, it goes to 1.05TB.  What could likely cause this to happen?  What settings can we check in Informatica and/or SQL Server for our next run to troubleshoot this or do a guess/check?

EDIT:

There are two ways to move data using an Informatica mapping.  Using SQL Overrides (running straight SQL) using the SQL Transformation type or using the built in data streams with Informatica's out of the box functionality.  In this case, we are using data streams.  When data streams are used, straight SQL is still used, but Informatica creates the SQL code behind the scenes.  We are loading in 1,000,000 record increments.  I thought perhaps Informatica might be tellinig SQL Server to allocate disk space as it loads, but I'm not even sure what command(s) to look for if it did.


texts: mariadb: setting AUTO_INCREMENT using SELECT

texts:
I am migrating a MariaDB database from my personal machine to a test
server.  The database schema has changed so I need to recreate the
tables.  The source database is also a mix of data that I want to keep
and a lot of test junk that I weeded out of the dump text file
manually. Since I dropped a lot of data, I want to update the AUTO_INCREMENT
value back to the new maximum.

This works:

MariaDB [imok]> alter table accounts AUTO_INCREMENT 114;
Query OK, 11 rows affected (0.04 sec)
Records: 11  Duplicates: 0  Warnings: 0

But when I try to create a generic statement using a select, MariaDB
complains:

MariaDB [imok]> alter table accounts AUTO_INCREMENT (select max(AcctID)+1 from accounts);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(select max(AcctID)+1 from accounts)' at line 1

I tried using a variable, with the same result:

MariaDB [imok]> set @autoinc=(select max(AcctID)+1 from accounts);
Query OK, 0 rows affected (0.00 sec)

MariaDB [imok]> select @autoinc;
+----------+
| @autoinc |
+----------+
|      114 |
+----------+
1 row in set (0.00 sec)

MariaDB [imok]> alter table accounts AUTO_INCREMENT @autoinc;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@autoinc' at line 1

QUESTION: How do I set the AUTO_INCREMENT value to something other than
a literal value in MariaDB?

NOTE: I am using MariaDB not MySQL.

NOTE: The '=' in the ALTER statement is optional, MariaDB gives the same complaint
with or without it; setting AUTO_INCREMENT using a literal value 113 works
without or without the '='.


texts: Queries does not get into performance_schema.events_statements_summary_by_digest

texts:
I have an Elixir application and want to do some performance measurements to get bottlenecks. To get query statistics I'm using Percona Monitoring and Management tool.
I know that it uses performance_schema.events_statements_summary_by_digest MySQL table to get stats for queries. But I have an issue with it: I don't see queries in this table that was send via TCP from my Elixir application.
If I execute a query directly from standard mysql client, I see that this query gets into performance_schema:
SELECT SCHEMA_NAME, digest_text, count_star \
FROM performance_schema.events_statements_summary_by_digest \
WHERE digest_text = 'SELECT * FROM `test` . `account`' LIMIT 1 \G

*************************** 1. row ***************************
                            SCHEMA_NAME: performance_schema
                            digest_text: SELECT * FROM `test` . `account`
                            count_star: 2

But if I will execute the same query from application mysql client I don't see that counters of this query are increased. MySQL 8.0 is used.
Does anybody have any ideas what it could be?
Thank you.


texts: Dacpac deployment via sqlpackage, “DropObjectsNotInSource=True” is trying to drop .mdf & .ldf

texts:
Powershell deployment script:

Executing: C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe /Action:Publish /SourceFile:********.dacpac /Profile:Release.Publish.xml /TargetConnectionString:server=********;database=********;uid==********;pwd=********;app=********;timeout=900 /p:IgnoreAuthorizer=True /p:IgnorePermissions=True /p:IgnoreRoleMembership=True /p:IgnoreUserSettingsObjects=True /p:BlockOnPossibleDataLoss=False /p:TreatVerificationErrorsAsWarnings=True /p:GenerateSmartDefaults=True
Error *** The object [********] 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 *** The object [********_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.
Info Initializing deployment (Complete)
Info Analyzing deployment plan (Start)
Info Analyzing deployment plan (Complete)
Info Updating database (Start)
Info Starting ********.sql
Info Update complete.
Info Updating database (Complete)
Info Successfully published database.

Release.Publish.xml:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <CreateNewDatabase>False</CreateNewDatabase>
    <BackupDatabaseBeforeChanges>False</BackupDatabaseBeforeChanges>
    <BlockOnPossibleDataLoss>True</BlockOnPossibleDataLoss>
    <NoAlterStatementsToChangeCLRTypes>False</NoAlterStatementsToChangeCLRTypes>
    <DropObjectsNotInSource>True</DropObjectsNotInSource>
    <DeployDatabaseInSingleUserMode>False</DeployDatabaseInSingleUserMode>
    <DeployScriptFileName>Release.sql</DeployScriptFileName>
    <ProfileVersionNumber>1</ProfileVersionNumber>
    <UnmodifiableObjectWarnings>False</UnmodifiableObjectWarnings>
  </PropertyGroup>
</Project>

Why is this trying to drop mdf and ldfs?

From Project-Oriented Database Development using Command-Line Tools: I can tell the fix would possibly be to use DoNotDropObjectType to make DropObjectsNotInSource behave more responsibly, if so how?

Honestly I'm confused why this is even an issue in the first place.


texts: SSAS 2012 role fails to start in a Win 2012 cluster

texts:
I started a thread in MSDN SSAS Forum.  Please see this and help me understand the problem. I have never seen this sort of issue before. I'll try to explain the issue here as well.

I got SQL Server installed in a failover cluster environment.  SQL Server, Agent Service & Analysis Service. Everything was working well. Without any apparent reason SSAS service stopped working one day. There was no installation of any sort before this event. Whenever I try to start SSAS, I get the following error:

I checked the SSAS log file and I see below entries.

(3/26/2017 3:46:14 PM) Message: The Query thread pool now has 1 minimum threads, 10 maximum threads, and a concurrency of 8.  Its thread pool affinity mask is 0x000000000000000f. (Source: \\?\L:\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x4121000A)
(3/26/2017 3:46:14 PM) Message: The ParsingShort thread pool now has 4 minimum threads, 4 maximum threads, and a concurrency of 8.  Its thread pool affinity mask is 0x000000000000000f. (Source: \\?\L:\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x4121000A)
(3/26/2017 3:46:14 PM) Message: The ParsingLong thread pool now has 4 minimum threads, 4 maximum threads, and a concurrency of 8.  Its thread pool affinity mask is 0x000000000000000f. (Source: \\?\L:\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x4121000A)
(3/26/2017 3:46:14 PM) Message: The Processing thread pool now has 1 minimum threads, 64 maximum threads, and a concurrency of 8.  Its thread pool affinity mask is 0x000000000000000f. (Source: \\?\L:\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x4121000A)
(3/26/2017 3:46:14 PM) Message: The IOProcessing thread subpool with affinity 0x000000000000000f now has 1 minimum threads, 64 maximum threads, and a concurrency of 8. (Source: \\?\L:\OLAP\Log\msmdsrv.log, Type: 1, Category: 289, Event ID: 0x4121000B)
(3/26/2017 3:46:14 PM) Message: Failed to initialize SQLSQM timer. One of the following can be the source of the problem: registry doesn't contain all necessary information, server instance name can not be retrieved correctly, failure during timer creation, etc. (Source: \\?\L:\OLAP\Log\msmdsrv.log, Type: 3, Category: 289, Event ID: 0xC121001B)
(3/26/2017 3:46:15 PM) Message: The service cannot be started:  (Source: \\?\L:\OLAP\Log\msmdsrv.log, Type: 3, Category: 289, Event ID: 0xC1210000)
(3/26/2017 3:46:15 PM) Message: Service stopped. (Source: \\?\L:\OLAP\Log\msmdsrv.log, Type: 1, Category: 2

Below is Failover Cluster Manager event log details

1. Cluster resource 'Analysis Services' of type 'Generic Service' in clustered role 'SQL Server (MSSQLSERVER)' failed.

   Based on the failure policies for the resource and role, the cluster service may try to bring
   the resource online on this node or move the group to another node of the cluster and then
   restart it.  Check the resource and group state using Failover Cluster Manager or the
   Get-ClusterResource Windows PowerShell cmdlet.

2. Generic service 'Analysis Services' failed with error '0'. Please examine the application event log.

Other info, which may help you understand the issue further

I'm restricted to add further links here. So, please check the link above

How can I get SSAS to start?


texts: How to update the statistics of an index that has just been reorganized, both with IndexOptimize script

texts:
I would like to know if we can update the statistics of an index that has been reorganized, using the IndexOptimize script.
Indeed, an index that is reorganized needs to have its statistics updated. Is there a way to use IndexOptimize script to update the statistics of this reorganized index only?
For instance, is there a flag to recognize the reorganized index in order to update the statistics on this flagged index?
Thanks.


texts: pg_repack slows down PostgreSQL replication

texts:
I have a master PostgreSQL 9.5 server and a standby server. For replication I use repmgr (WAL streaming). Typically the delay between master and standby is <5s:

$ psql -t -c "SELECT extract(epoch from now() - pg_last_xact_replay_timestamp());"
  0.044554

Periodically pg_repack is invoked on master in order to optimize indexes and tables. Repacking tables causes massive changes in WAL streaming and significantly slows down replication, so that the difference between master and standby could be more than 1h.

Is there a way how to reduce such delay? Is it possible to synchronize newly incoming data with higher priority than repack changes?


texts: What's the difference between WITH CTE & WITH CTE ()?

texts:
As shown in Using Common Table Expressions on MSDN, you can define a CTE as:

WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )

and use it like:

SELECT <column_list> FROM expression_name;

Let's say I have following 2 CTEs

with cte1 as(
select name from Table1
)

with cte2(name) as(
select name from Table1
)

A query outputs the same results for both CTEs as the inner query is same. The only difference between these two is that cte2 has column name((name)) defined in its declaration.

When I execute both CTEs, I don't see any difference in the execution plan.

I am just curious to know:

What difference does it make if I don't specify any column names in CTE definition?
Why I should/should not specify column names while creating CTE?
Does it affect query execution plan by any chance? (As far as I have seen, it doesn't make any difference.)


texts: SSRS error - A scope is required for all aggregates in the page header or footer which reference fields

texts:
I had the following textbox expression in my SSRS report and it was working fine.

  = First(Fields!FirstName.Value) +" " + First(Fields!LastName.Value) + ", MD"

When I moved the textbox to the report header area I started getting the "scope required" error message

I did some research and saw that I needed to specify the dataset. Once I included the dataset (PPR) below the error went away.

  = First(Fields!FirstName.Value,"PPR") +" " + First(Fields!LastName.Value, "PPR") + ", MD"

SSRS is now giving the same error but for another textbox/expression (below) that was working fine until I moved it to the header.

="Interim Score:   " & CINT((sum(CINT(left(fields!Points.value,1))) / sum(CINT(right(fields!Points.value,1))))*100) & "%"

I tried the following 2 modifications to include the scope (PPR) in the expression but I am still getting the same error. Is this just a syntax error on my part or am I trying to do something that can't be done in the header?

didn't work

="Interim Score:   " & CINT((sum(CINT(left((fields!Points.value,"PPR"),1))) / sum(CINT(right((fields!Points.value,"PPR"),1))))*100) & "%"

didn't work

="Interim Score:   " & CINT((sum(CINT(left(fields!Points.value,"PPR",1))) / sum(CINT(right(fields!Points.value,"PPR",1))))*100) & "%"

Below is the full error message I am getting:

  The Value expression for the text box ‘Textbox6’ references a field in
  an aggregate expression without a scope.  A scope is required for all
  aggregates in the page header or footer which reference fields.

I only have 1 dataset (PPR) in my report.

I was reading up on the below page but I can't seem to pinpoint what I am doing wrong. Any suggestions would be greatly appreciated.

https://technet.microsoft.com/en-us/library/dd220421%28v=sql.110%29.aspx


texts: SQL Server 2008 R2 returning wrong record

texts:
I have a SQL Server 2008 R2 hosting a database with which my software works through the ODBC API. The database has a table, called Users, which has a number of columns, but there are 3 columns (3 first ones in the Table Designer, if it matters) instrumental to understanding the problem:

UserID - INT, PK, identity, incremented by 1, starts from 10
NetworkUserID - BIGINT
NetworkID - INT

Fields 2 and 3 are indexed.

There is a stored procedure, which receives NetworkUserID and NetworkID through the input parameters and returns UserID through an out parameter, here is a semi-pseudocode of the procedure:

PROCEDURE [dbo].[Procedure]
@NetworkID INT,
@NetworkUserID BIGINT,

@UserID INT OUT
AS
BEGIN
SET @UserID = 0

-- check whether the user exists
IF NOT EXISTS
(
    SELECT [UserID]
    FROM [Users]
    WHERE [NetworkID] = @NetworkID AND [NetworkUserID] = @NetworkUserID
)
BEGIN

    -- user does not exist, create a new record for him
    INSERT INTO [Users]
        ([NetworkUserID], [NetworkID], [some_other_fields])
    VALUES
        (@NetworkUserID, @NetworkID, @some_other_values)

END
ELSE
BEGIN

    -- user has already registered, update his profile
    UPDATE [Users] SET [some_irrelevant_fields_are_being_updated_here, UserID_is_not_touched] WHERE [NetworkID] = @NetworkID AND [NetworkUserID] = @NetworkUserID

END

-- get the internal user ID
SELECT @UserID = [UserID] FROM [Users] WHERE [NetworkID] = @NetworkID AND [NetworkUserID] = @NetworkUserID

RETURN
END

Here is the problem - when the physical server running this SQL server starts to get low on memory, the SQL server starts to slam the disk due to massive swapping.

This results in overall, pretty significant, slowing down of query execution and here the problem pops up:

occasionally the SQL server returns the wrong UserID for the specified pair of NetworkID and NetworkUserID. This happens very randomly, so I cannot track it down. But it only happens when the SQL server slows down due to swapping.

I have instructed the SQL server not to consume more than 3/4 of available RAM, but it doesn't help, because the remaining memory is eventually eaten up by a leaky system service, which eventually leads to swapping anyway. From time to time I see the following messages in the logs of my software produced by the ODBC API:

  SQLFetch: state = 40001, native error = 1205, message = [Microsoft][SQL Server Native Client 10.0][SQL Server]Transaction (Process ID 63) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

  SQLDriverConnect: state = 08001, native error = 258, message = [Microsoft][SQL Server Native Client 10.0]Shared Memory Provider: Timeout error [258].

Apparently, these messages have something to do with the described above error.

Did anyone experience anything like that?

How can the SQL server return a totally wrong record by a uniqie pair of values NetworkID + NetworkUserID?


texts: DB Design - 1 column for each dataset OR dataset_name AND dataset_value?

texts:
I am creating a database table for storing numeric time-series data sets regarding cities that will be accessed via a web app.  Primarily annual, quarterly and monthly data will be stored in it.  There will be hundreds, potentially a couple thousand data sets, for each city.  Some data sets may have data on a monthly, quarterly AND annual basis while other data sets may only be available on a monthly, quarterly OR annual basis.

I am trying to decide between 2 different designs for the table's columns:

Option #1

city_id year subyear dataset_name dataset_value dataset_unit_type dataset_source

Option #2

city id year subyear dataset1-PROPERTY_TAXES dataset2-POPULATION dataset3-ETC ... datasetX

With Option #1, a hypothetical row look like:

12345 _ 2012 _ 'Q4' _ 'Property Taxes Collected' _ 950000 _ 'USD' _ 'city govt'

OR with monthly data:

12345 _ 2012 _ 'M8' _ 'Property Taxes Collected' _ 330000 _ 'USD' _ 'city govt'

With Option #2, a hypothetical row look like:

12345 _ 2012 _ 'Q4' _ 950000 _ 1400500 _ 234.53 ... XXXX

OR with monthly data:

12345 _ 2012 _ 'M8' _ 330000 _ 1400500 _ 120.12 ... XXXX

If I do #1, I will have hundreds (or maybe a couple thousand?) of columns and many rows will have a lot of 'blank' spaces because a lot of data sets will not have data for each row (e.g. some data that is only available quarterly will always be 'blank' in columns for rows describing monthly data).  Also, I think there will need to be a corresponding table for describing the sources and units (e.g. U.S. Dollars, Square Miles, etc.) for each dataset column.  I am concerned that the large number of columns and blank spaces may be problematic.

Alternatively, if I do #2, I will have very few columns but millions (potentially billions?) of rows and will have many queries with a WHERE clause searching to see if the dataset_name column equals a certain string (e.g. SELECT * WHERE dataset_name='Property_Taxes_Collected').  Also, since all types of numeric values are stored in the dataset_value column, the data type here will have to be pretty flexible (Maybe something like DECIMAL 16,4?).   I'm primarily concerned this may become slow with a lot of data.

I am using MySQL.

My Question:
Is possible to say which of these 2 general table designs is most likely the better design choice if my primary concern is query speed?  Or is it fairly clear that I should be approaching this in a completely different manner?


texts: Strategy to handle an SQL Server DB with too many files (BLOBs) in it?

texts:
Scenario:
SQL Server 2005 database servicing an ASP.NET application (on a separate web servers).

Database:
DB has about 5GB of "normal" data in it, and about 15GB of "files" (e.g.: a 200k PDF stored as an image (BLOB), that sort of thing). More files are being uploaded by users, and are rapidly consuming more disk space (DB could grow to 50GB in the next few months, mostly files).

Concerns:
Storing so many files in the DB is already causing problems (e.g.: The large total size of the database makes occasional whole-DB backups and deployments difficult.).

And we are worried there will be more problems. (e.g.: performance issues - maybe caused by not being able to keep the whole DB in RAM, perhaps?)

Question:
What technical solution would you suggest to this problem? Store the files in the file system? Split the database in two and have a bigger, slower one for files?

Further Details if Needed:
These files are not super-important, and don't need very fast access times - a couple of seconds would be fine, and there are maybe a dozen selects per hour at most, currently. The other "normal" data in the DB includes info needed many times per second.