texts:
I'm using this DBMS: Caché

I've never used a pivot table in SQL and I'm not even good at it in Excel, but I assume that's what I need.  Given the following sample data:

ID    Name    Result    Date
01    Steve   Win       2015-01-06 00:00:00
02    James   Win       2015-05-12 00:00:00
03    Bob     Lose      2015-06-21 00:00:00
04    Bob     Win       2015-05-23 00:00:00
05    James   Win       2015-04-04 00:00:00
06    James   Lose      2015-01-06 00:00:00
...

How can I get results like this? (numbers don't match sample data)

Wins By Month

        Jan    Feb    Mar
Steve   4      0      1
James   1      3      1
Bob     3      1      1
Steve   2      3      2


texts:
Is anyone familiar with this error?

Firstly, I am trying to set up a Pull Subscription Transactional replication where in server is outside of the firewall while the distributor and subscriber (both are on the same instance) is on our company.

I tried to create a test in my local PC wherein there are 2 instances, 1 will act as Publisher and 1 will act as Distributor and Subscriber (pull subscription type).

The first error I encountered was issue in synchronizing and the resolution was to create a login of the subscriber on the publisher then grant connect on the database of the publication.

The thing is, on the production I cannot the add the loging of the subscriber to the publisher since both server has no instance name so the default of the account was both NT SERVICE\MSSQLSERVER and NT SERVICE\SQLSERVERAGENT.

What is the solution/work around on this?

Note: My snapshot folder was in UNC format when set up. Below is the current permission on that folder.

Thank You.


texts: Creating a Database for Multiple Calendars Support

texts:
I am trying to create a calendar application that can support multiple calendars, for instance Indian, Nepali, Russian etc. Users can enable or disable the calendars according to their needs.

I have an idea in mind to make the English calendar as a base and create a mapping table for additional calendars. But I am not quite sure what is the efficient way to design database schema. Can you provide me the best way for designing the relevant database schema?

Since each calendar has different national holidays as well I want to support for it as well.

My work so far

My entity-relationship diagram so far:

...where:

base_calendar: Stores the English date
additional_calendar: English equivalent date (for example in BS)

Source: git.


texts:
List Jobs, Schedules, and Next Scheduled Run Datetimes

I'm trying to list Jobs, Schedules, and Next Scheduled Run Datetimes.  However, I don't appear to be succeeding.

sysjobactivity has next_scheduled_run_date, which is what I need, but I cannot figure out to connect it to both the Job and Schedule.

If there's a way other than through sysjobactivity, please guide me there.

SELECT
sj.name AS jobName
, ss.name AS scheduleName
, sja.next_scheduled_run_date
FROM msdb.dbo.sysjobs sj
INNER JOIN msdb.dbo.sysjobactivity sja ON sja.job_id = sj.job_id
INNER JOIN msdb.dbo.sysjobschedules sjs ON sjs.job_id = sja.job_id
INNER JOIN msdb.dbo.sysschedules ss ON ss.schedule_id = sjs.schedule_id
WHERE (sj.name LIKE 'xray%')
AND (sja.next_scheduled_run_date > GETDATE())


texts: simple question about group by and custom groups

texts:
Imagine a MySQL table that only has 2 columns: an id and a name of a color.

With this query, I know how many id's I have for each color:

SELECT color_name, count(id) FROM color_table GROUP BY (color_name)

red:    10
blue:   5
yellow: 3
green:  1

My question is, is there a way I can specify to the "group by" some custom groups?? I mean, is there a query that results in this:

red: 10
colors different than red: 9


texts:
Is there a way to get the exact time that SQL Server will use in a temporal table?
My goal is to save it in an audit-like record so that I can link some information with the specific version of the row that was changed.

Motivation: I could then get the version that was created during that transaction by using AS OF saved_time and I could easily get the version that existed before by querying the Historical table on sysEndTime = saved_time.

Looking at query plans, it seems what I want is internally systrandatetime() but that is not exposed to T-SQL code.

I tried to use transaction_begin_time from sys.dm_tran_active_transactions but it's not exactly the same.


texts: Dynamically choosing column for WHERE clause

texts:
I've got a table that holds columns named "Day1" through to "Day31".

Is there a way I can dynamically choose the correct column to put in a WHERE clause?

I can get the current day, that's no problem, but I don't know where to go from there.


texts: dba_registry_history vs dba_registry_sqlpatch and OJVM patch

texts:
Should dba_registry_sqlpatch-view list all installed patches and therefore it would also list OJVM patches?

Or OJVM patches are only listed in dba_registry_history-view?


texts: Need to search users by latitude and longitude

texts:
I'm still learning SQL and this is a bit more advanced than my capabilities, so I could use some help. I think I need a sub-query or something.

In the database I have a table called user with a column called city that contains the ID of their city

I have another table called cities that contains 3 columns: ID, city_name, and latlong. The column latlong contains a POINT() with the latitude and longitude coordinates.

I just need to select users who are within 100 miles of a particular city. My SQL is below and I know it's completely wrong (it doesn't work). My fear even with accomplishing what I want is that it would be horribly inefficient...

For discussion, let's say that the city with an ID of 20 is Chicago. This won't happen, but for the sake of good form let's say there are hundreds of thousands of users in here. Each time you browse the users within a certain distance from Chicago, will it have to calculate this distance equation for every single user? That seems like a big burden every time a simple user search is performed. Would it make more sense to first get the distances from Chicago for every city in the cities table, then select the users whose city matches the cities whose distance is within 100 miles of Chicago? Just thinking about how to do that makes my brain hurt.

So from what I can conjure up, it sounds like this is what needs to happen for the most efficient searching:

Select cities within 100 miles of Chicago (how to save this list in SQL? Or should I do it in PHP?)
Select users whose city matches one of the cities from #1

Does that sound correct? Should I handle #1 in PHP or can I do it all within SQL?

If you could help me out with this I'd really appreciate it. I don't think it's something that's incredibly difficult, I've just never done anything this complex with SQL.

Here's my current SQL. The distance equation works, but the rest you should just ignore ;)

SELECT user.id,
    (
        (
            (acos(
                sin((13.72767 * pi() / 180))
                * sin((X(cities.latlong) * pi() / 180))
                + cos((13.72767 * pi() / 180))
                * cos((X(cities.latlong) * pi() / 180))
                * cos(((100.52415 - Y(cities.latlong)) * pi() / 180))
            )) * 180 / pi()
        ) * 60 * 1.1515
    ) as distance
FROM `cities`, `user`
WHERE user.city = 20
HAVING distance <= 100 ORDER BY distance";


texts:
I need to export an Oracle 11g database to an Oracle 10g instance.  I have the command, which I believe is correct:

expdp user/pass@192.168.10.8 full=Y VERSION=10.2 directory=m_dump
    dumpfile=DB10G.dmp logfile=expdpDB10G.log

When I run this I always get this error:

UDE-12541: operation generated ORACLE error 12541
ORA-12541: TNS:no listener

I can connect to the server with SQLDeveloper, the 'no listener' error only occurs with this command.


texts: How to track only the tables used by app

texts:
I have a very messed up database with lot of tables that are really not used by the app so I'm tracking all the used tables with the general_log.

I'm making the request to the app and with tail -f /path/to/general_log.log following the connection id in the general log and getting all the tables requested, but this is going to be a very frustrating activity.

I want to know if there's a tool or an easy way to do this


texts: Oracle 10g dump file push into Oracle 11g database

texts:
Currently when executing the imp command with the following parameters:  file=my.dmp log=my.log fromuser=myuser touser=myuser

The following errors occur:

IMP-00058: ORACLE error 12560 encountered
ORA-12560: TNS:protocol adapter error
IMP-00000: Import terminated unsuccessfully

However, tnsping successfully looks up the values in my tnsnames.ora file.  My question is does the dump file created in 10g cause an issue on import into 11g or is this error something else that I am unable to diagnose.


texts: MySQL Range Optimization and Multipart index


texts:
How would I know in MySQL if the user has exceeded his max_queries per hour?
I know that I can see this error on the client side, but I just want to know that before client is reporting to me, somewhere on the server side, would be nice to have it in error_log at least.


texts: check if MySQL is using the Henry Spencer implementation for REGEX

texts:
Prior to MySQL 8.0.4, MySQL used the Henry Spencer implementation for REGEX and you could use this for word boundaries:

'[[:<:]]' and '[[:>:]]'

From 8.0.4 MySQL is using the ICU implementation and the word boundaries have changed to this:

'\\b'

I need to find out which REGEX implementation is used.
Relying on MySQL  version number might not be the best option as for example MariaDB has different version numbers.

I thought to check by querying MySQL and find out which implementation it's  using like this:

"SELECT 1 FROM DUAL WHERE 'icu regex' REGEXP '\\bregex';"

This is working with MySQL 8.0.4 and previous versions. It will return 1 if the ICU implementation is used by the DB, 0 otherwise.

The opposite doesn't work instead as MySQL 8.0.4 throws an error:

"SELECT 1 FROM DUAL WHERE 'henry regex' REGEXP '[[:<:]]regex';"

Is there any better way to approach this?

MySQL reference about REGEX changes are here: https://dev.mysql.com/doc/refman/8.0/en/regexp.html#regexp-compatibility


texts:
I have a Percona MySQL master-slave server configuration(5.6.20-68.0). The memory usage by MySQL on the slave server is constantly growing.

Now it reached 68G despite the fact that innodb_buffer_pool_size was set to 30720M. On the Master server memory is stable. Any idea?

I have checked the connections on both servers. But the results are strange. On the Master DB server there are more connections than on the slave:

Master

netstat | grep TIME_WAIT | wc -l

148

htop
mysql VIRT 44G RES 34,7G

Slave

netstat | grep TIME_WAIT | wc -l

78

htop
mysql VIRT 174G RES 68G


texts: INSERT INTO table FROM SELECT * with nextval() specification

texts:
I need to copy records within the same table changing only one field.
My table has default-generated sequence entry_id_seq, however I am not sure id column is SERIAL (how to check that?).

\d tab returns just this

      Column      |              Type              |            Modifiers
 -----------------+--------------------------------+------------------------
       id         |             integer            |            not null
...
Indexes:
"tab_entry_pkey" PRIMARY KEY, btree (id)

So the problem is: when I try copy record in a simplified manner:

insert into tab_entry select * from tab_entry where id = 3052;

it throws error

ERROR:  duplicate key value violates unique constraint "tab_entry_pkey"
DETAIL:  Key (id)=(3052) already exists.

Default sequence doesn't generate next value by default. Is there any concise syntax allowing inserting and changing single field without full table specification aka FROM tab(col1, col2, col3, ..., col N)?

The table has many fields, so I don't want to write them all, as this will affect code readability.
I want something like this, but this syntax doesn't work

insert into tab_entry(id, *) select nextval('seq'), * from tab_entry where id = 3052;

And will this SELECT nextval('seq') approach work if there be multiple records at once?

Psql and Postgres version is 9.6.2.


texts: Need to find the number of employee who are not in all department

texts:
i have a table T1 which has two columns empname , department

every emp must be in 37 department , but there are some employee who are in less than 37 department .

I need to find the names of emp who are not in all 37 departments.

i'm using a query :

select empname from T1 having count (department) < 37;

but it is not giving me the desired output.


texts: Informix trigger to delete records before insert

texts:
I am trying to create a trigger to simply delete records from systables_growth table before an insert happens based on the where clause used on the date_added column for records older than a certain number of days (in this example, 15 days):

I am using the below:

CREATE TRIGGER systables_growth_clnup
INSERT ON systables_growth
DELETE FROM systables_growth
WHERE date_added < (CURRENT - 15 UNITS DAY)

I am getting the below syntax error when performing the above.

CREATE TRIGGER systables_growth_clnup
INSERT ON systables_growth
DELETE FROM systables_growth
#^
#  201: A syntax error has occurred.
#
WHERE date_added < (CURRENT - 15 UNITS DAY)


texts: Increasing working memory in Postgres does not improve query performance

texts:
I am very new to database administration and could use any advice from more expert users of PostgreSQL

My database is an AWS RDS db.m4.large whose specification are here

My queries against one of the larger tables in the database (330 million rows) are running extremely slow and/or timing out, even when I am have a query such as

SELECT(DISTINCT value) FROM mytable;

where there are only 4 or 5 unique values in the value column.  It is not indexed.

pg_size_pretty(pg_total_relation_size(relid)) shows the size of the table to be 118 GB

Even when setting work_mem to 240 MB, which should allow Postgres to do larger in-memory sorts, the query is equally as slow

EXPLAIN ANALYZE SELECT(DISTINCT value) FROM mytable;
also times out

What does one do to optimize memory usage for querying on a sufficiently large database?  Which pg settings would I need to monitor and/or configure differently?

Any help would be appreciated.


texts: Postgres query to select all records that have at least one matching array value from another array

texts:
Rough table structure:
id                      uuid
fields                  jsonb
created_at              timestamptz
related_content_ids     _uuid
updated_at              timestamptz

I'm trying to write a query that will find all records in this table that have at least one value from a specified array in the related_content_ids column.
Something like this works for matching all values in the array, but I just need at least one to match:
SELECT * FROM content WHERE related_content_ids @> '{fbaa1235-1069-496f-9a54-e91e13aadae8,65a109eb-ee4b-4a23-9f91-6980b5bb9340}'


texts:
Is there a way to generate TPC-DS/TPC-H data using Normal Distribution, i.e., is there a way to specify the distribution type while generating TPC-DS/TPC-H?

EDIT : I believe there is an option for TPC-H DBGEN. I would like to know if there is a similar one for TPC-DS DSGEN.

Thanks!


texts: How to stop MySQL replicating users on slave

texts:
I have set --replicate-ignore-db=mysql in MySQL slave, but still it is replicating any users I create on the master.

Is there a way to stop the slave from replicating users?


texts: How can I fix this unexpected Windows Event Log message which appears whenever a database with FILESTREAM is created or restored?

texts:
We have recently noticed that whenever we create or restore a database with FILESTREAM enabled, a message like the following is added to the System Event Log:

  The description for Event ID 1006 from source RsFx0320 cannot be
  found. Either the component that raises this event is not installed on
  your local computer or the installation is corrupted. You can install
  or repair the component on the local computer.

  If the event originated on another computer, the display information
  had to be saved with the event.

  The following information was included with the event:

  \Device\RsFx0320

  The specified resource type cannot be found in the image file

The Event ID will be 1006 or 1007.

From the "Details" tab of Event Viewer, it's possible to see that the binary data associated with the error message contains the path of the FILESTREAM folder for the database being created/restored.

This occurs on each of our Windows 2012 R2 servers & 64-bit Standard Edition, and on my local machine running Windows 10 Pro & 64-bit Developer Edition. All of the machines are running 12.0.5203, but the issue was certainly occurring before 12.0.5203 was installed.

Unfortunately the Windows logs on the machines have cycled so it's impossible to confidently pick a date when this started.

Some research has revealed that these Event IDs probably relate to the group policy error message codes 1006 and 1007.

I think there are two issues here:

i) The OS is trying to verify file system permissions for a local account via group policy, but the AD controller can't be found.

ii) When i) occurs, error reporting is damaged in such a way as to prevent the proper error message being recorded in the event log.

Regarding ii) RsFx0320.sys is a Microsoft driver installed as part of the OS; I've not been able to find much information about it. I've seen a couple of suggestions that rebooting the system will clear the message but that doesn't seem to be true in this case, since instances of the error on all the servers predate the most recent restart.

Any suggestions on how to fix this/whether it needs fixing? (I don't believe the error is having any impact on the functionality of SQL Server, but it's annoying to have this message clogging up the logs.) Google has so far drawn a blank for me.


texts: Should I use a Domain User account to manage SQL Server (or other services)

texts:
As the SQL Server administrator for a server running inside a Windows Domain, I followed some rules:

SQL service runs under a Domain User account (least privilege)
"sa" has been disabled

The question is basically about "my" account as an administrator. To expand on this, I have two AD accounts, 1 to login to my workstation (call it ADU1), and 1 to login to the server itself (ADU2). Note that this somewhat following the MS best practices for their recommended security model.
The question is, do I use my Domain User (ADU1) which has the "sysadmin" role, or (ADU2) or basically a different AD account other than my regular domain account?
Hope this makes sense. Looking for some guidance... thanks!


texts:
While running DBCC CHECKDB I got this error

  Msg 8921, Level 16, State 1,   Line 1 Check terminated. A failure was
  detected while collecting facts. Possibly tempdb out of space or a
  system table is inconsistent. Check previous errors.

After that a coworker showed me this error, while executing a stored procedure.

  The Database ID 85, Page (1:3403), slot 0 for LOB data type node does
  not exist. This is usually caused by transactions that can read
  uncommitted data on a data page. Run DBCC CHECKTABLE

Then I tried to run DBCC CHECKTABLE for the table inside that procedure, Command Executed successfully for all table except one. I had to restart the server in order to overcome this problem... what if it occur in production server, how to over come it then.


texts: Optimising file shrink and index reorganization operations

texts:
I'm archiving my SQL tables. So I am shrinking my files (because I need the free space) and re-organising my indexes(to defragment them). I know shrinking a file causes the indexes to be fragmented. So after shrinking my file, I check the fragmentation % of my indexes and if the degree of fragmentation is greater than 10%, I reorganise them. I am reorganising it partition by partition by employing the following code:

ALTER INDEX <index_name> ON <table_name> REORGANIZE PARTITION = <partition_number>

And for shrinking files, the following code is being employed:

DBCC SHRINKFILE ('<file_name>',<desired_size>)

Now so far, I have been shrinking my files, reorganising my indexes only to find that the file pertinent to the index has again bloated up, requiring to be shrunk up again; the said shrinking again causes fragmentation(as expected) requiring defragmentation again. I have had to perform these operations several times to get rid of index fragmentation and set the file to a desired size.

Is there a way to optimise the vicious cycle of shrinking -> defragmenting -> shrinking -> defragmenting? Is there a way to reduce the number of steps?


texts: SQL Server Replication How to keep primary key schema at publisher

texts:
I have implemented Transactional replication and want to have similar schema structure at both subscriber and publisher end. But while creating the publication, Not For Replication appears in front of Primary key, I got some guidance from this forum, that if I set sync_type to initialize from backup it can be achievable

So, I followed instructions from various blogs like this and this,but, nothing seems to work as the issue reappears, Am I doing something wrong? is it even possible?


texts: Type conversion with default fallback value

texts:
In PostgreSQL (8.4), I'm trying to convert a string parameter into a date within a SQL query, falling back to now() when the string is not a valid date (or empty).

In "pseudo-SQL", this would be something like this:

SELECT CASE WHEN ? is not a valid date THEN now()::DATE ELSE CAST(? AS DATE) END;

I've tried to simplify the problem to detect an empty string using these two queries:

SELECT CASE WHEN ?='' THEN now()::DATE ELSE CAST(? AS DATE) END;
SELECT DATE(CASE WHEN ?='' THEN now() ELSE ?  END);

For example, if the parameter is '', this is equivalent to this:

SELECT CASE WHEN ''='' THEN now()::DATE ELSE CAST('' AS DATE) END;
SELECT DATE(CASE WHEN ''='' THEN now() ELSE ''  END);

Both fail with ERROR: invalid input syntax for type timestamp with time zone: ""
It makes sense, but it implies that the ELSE block is evaluated (or at least that its types are resolved) whether or not the CASE condition is true. The following works, but what I'd like the CASE (or similar) condition to handle, is precisely the case when it's not a valid date.

SELECT CASE WHEN '2011-12-01'='' THEN now()::DATE ELSE CAST('2011-12-01' AS DATE) END;

The closest I got to a working solution is this:

SELECT DATE(COALESCE(NULLIF(?, '')::timestamptz, now()));

In this case, if the parameter is '', it returns the current date, otherwise, it returns the date passed in the string parameter (provided it can be turned into a valid date).

What I would like is to go a step further and make anything that cannot be turned into a DATE use the current date. I guess this could be done using a custom PL/pgSQL function that would trap this error, but can this be done without such a function, in "plain" SQL (or at least using the existing PostgreSQL functions)?


texts: Primary Key and Unique Constraints in Partitioned Table

texts:
I am currently creating a project that applies to a table that contains about 40 columns (including varchar (max)) and will be partitioned. About 200-250 records per second will be inserted into the table, which gives a daily volume of around 21 600 000 records. The last 14 days should be kept in the table, i.e. there will be about 300 000 000 records in total. Every day in the evening, records older than 14 days should be deleted. The table will be partitioned based on the column that is provided on record insertion (due to 3rd party system). We are currently thinking of 8 filegroups - 16 files - 32 partitions. INSERTs and UPDATEs are widely used in the table. DELETE will only once a day for data older than 14 days. Moreover, another system for users will sometimes ask for data from this table. My questions:

Is the partitioned table really the best solution here?
Let's assume that the table has an IDENTITY (1,1) column named TransactionID, which should actually be the only PK of this table. PartitionID column is the partition column. An external system for users will query our table for data only on the basis of TransactionID. So my question is, which columns should be included in CLUSTERED INDEX here and in which FileGroup? I see it this way:

    CREATE PARTITION FUNCTION [pfData] (smallint)
    AS RANGE LEFT FOR VALUES
    (   1,
        2,
        3,
        4,
        5,
        6,
        7,
        8,
        9,
        10,
        11,
        12,
        13,
        14,
        15,
        16,
        17,
        18,
        19,
        20,
        21,
        22,
        23,
        24,
        25,
        26,
        27,
        28,
        29,
        30,
        31
    );

    CREATE PARTITION SCHEME [psData]
    AS PARTITION [pfData]
    TO
    (
        [fg1],
        [fg1],
        [fg1],
        [fg1],
        [fg2],
        [fg2],
        [fg2],
        [fg2],
        [fg3],
        [fg3],
        [fg3],
        [fg3],
        [fg4],
        [fg4],
        [fg4],
        [fg4],
        [fg5],
        [fg5],
        [fg5],
        [fg5],
        [fg6],
        [fg6],
        [fg6],
        [fg6],
        [fg7],
        [fg7],
        [fg7],
        [fg7],
        [fg8],
        [fg8],
        [fg8],
        [fg8]
    );

    CREATE TABLE [dbo].[Data]
    (
        [TransactionID]  bigint       NOT NULL IDENTITY(1, 1),
        [PartitionID]    smallint     NOT NULL,
        [Desc]           varchar(MAX) NOT NULL,
        [InsertDateTime] datetime     NOT NULL,
        CONSTRAINT [PK_TransactionID_PartitionID]
            PRIMARY KEY NONCLUSTERED (
                                         [TransactionID],
                                         [PartitionID]
                                     ),
    ) ON [psData] ([PartitionID]);

    ALTER TABLE [dbo].[Data]
    ADD CONSTRAINT [AK_TransactionID]
    UNIQUE CLUSTERED ([TransactionID]) ON [PRIMARY];

The question is whether the fact that our UNIQUE CLUSTERED INDEX is in another filegroup will not make it very large? In fact, I want users to search for values based on TransactionID. Is it worth creating a separate special filegroup and file for the CLUSTERED INDEX? Think that 90% SELECT statemets will be with condition on TransactionID without PartitionID(no one will have information about PartitionID).


texts: Repository for organizing company SQL statements?

texts: We have a few people here that use SQL and have quite a few queries we each individually use and share.  I'm wondering if there's a solution to store all of these statements in a central repository where they can be used and copied but not modified. We did consider a folder on a network drive and storing the .sql files, but were wondering if there was a better solution out there.


texts: How to completely hide a function from all other users and roles?

texts:
Database : Postgres 9.6

Use case : I have a function that needs to be hidden, only a particular role/user can see it. I don't even  want to expose even the name or body of function to other users/roles.

For example if I have a user named test1 then he should not have any knowledge of existence of function called top_secret_function_1 (not via PgAdmin not via any other command.)

Only the the users/roles that I want to see this function should see this function.

Importance : This function executes some important logic which I don't want to expose but still I need to give some other database access rights to some other users.

Update 1

Klin suggested use of pg_temp but its does not seems effective but his suggestion triggered a idea of using different schema and then restrict the users/roles to schema.

when i reconnect the database the secret function is no longer valid in pg_temp !
I cannot use pg_temp as suggested because then I will have to define function in each users pg_temp schema when he connects


texts: I'm running PostgreSQL 10 on AWS for a couple years in production and wondering if it's worth the downtime to perform a VACUUM FULL on the database.


texts:
We recently upgraded our test database servers from SQL 2014 SP2 CU3 to 2014 SP2 CU10. Most of the servers are fine, but on 2 of them we are getting the following error when connecting to the reporting services website:

sorry, it didn't scale well, the message reads :

  "The version of the report server database is either in a format that
  is not valid, or it cannot be read. The found version is '163'. The
  expected version is '164'. (rsInvalidReportServerDatabase)"

Having a look at the log files I can see this error:

  library!WindowsService_0!4cc!02/26/2018-12:47:43:: e ERROR: ServiceStartThread: Exception caught while starting service. Error: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: An error occurred within the report server database.  This may be due to a connection failure, timeout or low disk condition within the database. ---> System.Data.SqlClient.SqlException: Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_CI_AS_KS_WS" in the equal to operation.
  Cannot resolve the collation conflict between "Latin1_General_CI_AS_KS_WS" and "Latin1_General_CI_AS" in the equal to operation.

After that we get a number of entries about the database version being incorrect:

  library!DefaultDomain!1bf0!02/26/2018-12:49:42:: e ERROR: Throwing Microsoft.ReportingServices.Library.InvalidReportServerDatabaseException: , Microsoft.ReportingServices.Library.InvalidReportServerDatabaseException: The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is '163'. The expected version is '164'.;

I'm assuming it's something to do with Collation, but everything I can see is set to Latin1_General_CI_AS. I'm not sure where it's getting the other collation from or how to fix it.

Any ideas on who to get the database to upgrade to the new version?


texts:
I'm joining two tables, filter out results by second table and order results by first table's ID.

The query is super-slow on Postgres Database. I see the issue is in the ORDER BY statement. But increasing work_mem (SET LOCAL work_mem = '256MB';) didn't help.

select A.a_id
from B
inner join A
on A.b_id=B.b_id
where
    A.date_one between '2012-01-07 04:05:06' and '2019-01-08 03:07:06'
 or A.date_two between '2012-01-07 04:05:06' and '2019-01-08 03:05:06'
order by A.a_id ASC
limit 100;

Execution plan:

"Limit  (cost=690932.94..690933.19 rows=100 width=8) (actual time=19412.278..19412.344 rows=100 loops=1)"
"  Buffers: shared hit=12521 read=287185"
"  ->  Sort  (cost=690932.94..698624.09 rows=3076458 width=8) (actual time=19412.274..19412.293 rows=100 loops=1)"
"        Sort Key: A.a_id"
"        Sort Method: top-N heapsort  Memory: 29kB"
"        Buffers: shared hit=12521 read=287185"
"        ->  Hash Join  (cost=34731.92..573352.93 rows=3076458 width=8) (actual time=822.673..19052.607 rows=417486 loops=1)"
"              Hash Cond: (A.b_id = BB.b_id)"
"              Buffers: shared hit=12521 read=287185"
"              ->  Seq Scan on A  (cost=0.00..432736.38 rows=7512005 width=16) (actual time=0.044..9306.081 rows=7512001 loops=1)"
"                    Filter: (((date_one >= '2012-01-07 04:05:06-05'::timestamp with time zone) AND (date_one <= '2019-01-08 03:07:06-05'::timestamp with time zone)) OR ((date_two >= '2012-01-07 04:05:06-05'::timestamp with time zone) AND (date_two <= '2019-01-08 03:05:06-05'::timestamp with time zone)))"
"                    Rows Removed by Filter: 18"
"                    Buffers: shared hit=12198 read=270298"
"              ->  Hash  (cost=24997.52..24997.52 rows=778752 width=8) (actual time=820.550..820.550 rows=377405 loops=1)"
"                    Buckets: 131072  Batches: 1  Memory Usage: 14743kB"
"                    Buffers: shared hit=323 read=16887"
"                    ->  Seq Scan on fs_invoice_quote invoicequo0_  (cost=0.00..24997.52 rows=778752 width=8) (actual time=0.007..471.024 rows=777884 loops=1)"
"                          Buffers: shared hit=323 read=16887"
"Total runtime: 19412.432 ms"

Any suggestions on what to do?


texts: SQL table constraint limit number of same domain < 5

texts:
I have a table "Applies" with one of its attribute "applicantNum".

Is there a way to set a constraint to check the value inside a domain has less than 5 repeated value?

E.g.

insert into applies(applicantNum) values ('s12345');<-- 1st insertion
insert into applies(applicantNum) values ('s12345');<-- 2nd insertion
insert into applies(applicantNum) values ('s12345');<-- 3rd insertion
insert into applies(applicantNum) values ('s12345');<-- 4th insertion
insert into applies(applicantNum) values ('s12345');<-- 5th insertion
insert into applies(applicantNum) values ('s12345'); <-- error


texts:
I created a folder in "MySQL Server 5.7\Data" And copied frm,myd,myi files there from another database folder ( mysql 5.1 )
However the data is encrpted and there is a program that sends queries, decrypts and displays the result. ( written in Delphi 7, db component Zeoslib )
Whenever i try to search a big table, i get "Cannot retrieve resultset data" error. I am a total noob and dont know how to solve this.Any suggestions ?

edit: I am not particularly concerned about efficiency and i didnt write the decryptor program, i just downloaded it along with mysql 5.1 data files.Because the data is encrypted i am stuck with using this program for queries.I know several people managed to get this to work so it should be possible.I want to change mysql settings such that the program wont complain when searching big tables ( 50 million entries )


texts:
We have a back-end server using ASP Core 3.0, and we are using pomelo library to make a connection to our MySQL database.

Every now and then the server starts to fire a LOT of SQL exceptions saying that all connections in the pool are in use.

We have a script to analyze slow queries and it's filled with this:

# administrator command: Change user;
User@Host: username[username] @ [x.x.x.x]
Query_time: 8.998701 Lock_time: 0.000000 Rows_sent: 1 Rows_examined:436537
SET timestamp=1576705430`

Just query after query depleting all the connections in the pool.

I don't know where this query is coming from, why it shows for a while and then stops showing.

If I use the SHOW PROCESSLIST; command I see many processes with no database selected and the query is Change user!

Is this even a valid command?

Almost everything is left to default for pool settings:

100 connections max.
changed command timeout to 120 seconds
enable retry on failure (max count 5), tried with this setting off
as well


texts:
How do I write an SQL script to copy the securables, memberships and owned schemas of a current database user?

I currently have to repeatedly restore databases on my local machine for development work. I then have to manually add database users to the master level login section so that I can allow my web app to authenticate with the database. I can see that you can right-click on the user -> Script User as -> Create To -> New Query editor window. The resulting SQL does not artificially mimic any of the securables, memberships, or owned schemas.

I already found that you can programmatically add a user to the database that uses the Master login information.

CREATE USER [user_name] FOR LOGIN [user_name] WITH DEFAULT_SCHEMA=[dbo]
GO


texts: Create global temp table on one DB and use it in a query on another DB - is it possible?

texts:
My issue is as follows:

I'm trying to create a global temp table using data from DB1, then use that temp table in a query executed in DB2 and then have the results imported into a new table on DB2. Since the query is a bit more complicated a simple Merge join won't do as I need to transform the data and add an extra column (dense_rank) based on data from both Databases.
According to one of my colleagues this should all be possible to do in SSIS.

Simplified Query that I would like to execute on connection from DB2:

Select db2.a,db2.b,db2.c,db1.c, db1.d,
DENSE_RANK() OVER (PARTITION BY db2.a order by db1.c asc) as FinalRank
from DB2.table  as db2
left join ##globaltemp as db1 on db2.a=db1.a

After extensive googling and different tries, the DB2 source connection won't find my temp table (##globaltemp) which has been created in DB1.

Is such an operation even possible ?

tldr: Is it possible to create a global temp table in SSIS session and pull data from it no matter the Database?


texts: Monetdb - Performance of two node distributed query processing using TPC-H data is much slower than a single node case

texts:
We ran the following query on two-node setup loaded with TPCH-20 data where all tables except nation and region are generated with two splits - each loaded into one node. Nation and region are replicated in both nodes.
Query -
SELECT
L_LINESTATUS,
L_RETURNFLAG,
avg(L_DISCOUNT) AS AVG_DISC,
avg(L_EXTENDEDPRICE) AS AVG_PRICE,
avg(L_QUANTITY) AS AVG_QTY,
count('*') AS COUNT_ORDER,
sum(L_EXTENDEDPRICE) AS SUM_BASE_PRICE,
sum((L_EXTENDEDPRICE * (1 - L_DISCOUNT)) * (1 + L_TAX)) AS SUM_CHARGE,
sum(L_EXTENDEDPRICE * (1 - L_DISCOUNT)) AS SUM_DISC_PRICE,
sum(L_QUANTITY) AS SUM_QTY
FROM LINEITEM
WHERE L_SHIPDATE <= '1998-12-01'
GROUP BY L_LINESTATUS,L_RETURNFLAG;

Query execution time -
Single node(64GB RAM 4 CPUs)         - **4.669**s
Two-node setup(64GB RAM 4 CPUs each) - **32.906**s

We observed that there was a lot of data transfer across the two nodes. Is there any way to achieve better performance with distributed processing?


texts: How to get the last 10 query run on MongoDB?

texts:
I am using mongoose driver in my Node.js app and I want to know what queries mongoose run behind the scene when I call available function on mongoose model.

e.g. I am saving a signup information for a user using mongoose create function and as per its definition: it should create the document in the database with the provided information. It returns a document to the caller, so I am curious to know whether it run extra query to get the returned document or it makes it from the provided information. In the first case it will take a network bandwidth for the already available information so I want to avoid this by using any other function but I don't think it is working like this.

Anyway, please suggest me how can I see the last 10 queries that hits MongoDB Database?


texts: Log file increases while differential backup is in progress

texts:
Database server: Microsoft SQL Server 2012 (SP1) - 11.0.3128.0

Database Size: 500+ GB

We have a staging history database which stores what so ever operation(Insert, update, delete) is done on live database is getting transferred to our history server via service broker. And that message is processed and stores data in history database.

We recently found a issue that message queue of our destination database is not clearing processed messages. We started cleaning the queue there were around 320+ M records which were stored in this queue.

We are running one script 24x7 which cleans processed messages of the queue.

Now, the problem which started recently is whenever our scheduled History database's differential backup is progress log file of History database increases to 10+ GB and as soon as it completes everything gets normal.

Is there anything that we are missing or is this is a normal thing?

Transaction backups do run in between differential backups. Usually differential backup takes around 1 hour and in between this one hour time 4 Transaction backups takes place.


texts: Search on part of a column in a SQL Server cross-database view

texts:
I'm currently writing an application that interfaces with a 3rd party vendor's SQL Server 2008 database that I only have read access to. We have a separate database that contains cross database views and stored procedures into the vendor's database for our custom applications.

One of the columns contains building and room data like this:
DON-203, DON-303, SGRUE-102, EVN-1010

Basically, it's a set of letters (that indicates the building) followed by a hyphen followed by digits (which indicates the room number). I need to perform queries on just the room number (numeric) portion of the column.

I can't use a precomputed column in the table, since I don't have write access to the original database.
I can't create triggers in the original database
Scalar functions will return the correct results, but seems incredibly inefficient for large datasets that will be queried very frequently
As far as I know, I can't use an indexed view, since the view refers to a table in a different database.
New buildings can be added to the program at any time, so I can't hardcode a list of all the building prefixes somewhere. They're also not stored anywhere.

Is there a decent way to handle this situation?

EDIT: Here's a sample schema (with all irrelevant data removed)

CREATE TABLE [dbo].[Room]
(
    [RoomID] [int] IDENTITY(0,1) NOT NULL,
    [Description] [varchar](30) NOT NULL,
    CONSTRAINT [PK_Room] PRIMARY KEY CLUSTERED
    (
            [RoomID] ASC
    )
)

There is a non-unique index on Description in ascending order

Sample data:

RoomID  Description
0       DON-101
1       DON-102
2       DON-103
...
118     DON-427
119     DON-428
...
124     EVN-107
125     EVN-108
...
511     NGRI-1007
512     NGRI-1008
...
564     NGRI-227
565     NGRI-228


texts: Postgres 10.0 upsert syntax error not sure what to google for

texts:
Postgres 10.0

table:

pcr=> \d tableFindOld
                               Table "public.tablefindold"
  Column  |            Type             | Collation | Nullable |         Default
----------+-----------------------------+-----------+----------+-------------------------
 hostname | character varying(16)       |           | not null |
 lastmiss | timestamp without time zone |           |          |
 lastfind | timestamp without time zone |           |          |
 uname    | character varying(64)       |           |          | NULL::character varying
Indexes:
    "tablefindold_pkey" PRIMARY KEY, btree (hostname)

upsert statement:

INSERT INTO tableFindOld(hostname, lastMiss, lastFind, uname)
VALUES('pcname', DEFAULT, '2019-03-04 11:38:04', 'billy')
ON CONFLICT (tableFindOld.hostname)
    DO UPDATE SET lastFind='2019-03-04 11:38:04', uname='billy'
    WHERE tableFindOld.hostname='pcname';

error:

ERROR:  syntax error at or near ")"
LINE 1: ...8:04', 'billy') ON CONFLICT (tableFindOld.hostname) DO UPDAT...
                                                             ^

I have tried googling this to no avail. A few questions:

What is the specific error here? (Really asking Why am I getting this error?)
In upsert statements, for a column conflict condition (in this example it is the primary key conflict) is the column name supposed to be enclosed in parantheses or not? I have seen some inconsistent info on this.
In an upsert statment with an update, are we supposed to include the DO keyword or not? Also have seen inconsistent info on this.

Also, I created the table with DEFAULT NULL on all columns except the primary key, so I'm not sure why that's not reflected in the description.

Thanks in advance for your help.

EDIT:
Here is my table creation code:

CREATE TABLE tableFindOld(
    hostname VARCHAR(16) PRIMARY KEY,
    lastMiss TIMESTAMP DEFAULT NULL,
    lastFind TIMESTAMP DEFAULT NULL,
    uname VARCHAR(64) DEFAULT NULL
);

SOLUTION:
Thanks to McNets for this answer. For reference, the working statement:

INSERT INTO tableFindOld(hostname, lastMiss, lastFind, uname) VALUES('pcname', DEFAULT, '2019-03-04 12:30:04', 'billy') ON CONFLICT (hostname) DO UPDATE SET lastFind='2019-03-04 12:30:04', uname='billy' WHERE tableFindOld.hostname='pcname';


texts: Defining two distinct relationships between a user table and a company table

texts:
A very basic design question here. I would like to build a scalable app and would want to know what's the best way forward.
So in the database there are two tables : users and companies.

users has the following fields:

-id
-name
-company_id
-join_date
-leave_date

companies has the following fields:

-id
-admin_id
-address

I want a 1-to-M between users and companies as one company could have many users, but one user belongs to only one company. Also, I want to have a 1-to-1 on users and companies because one company has one admin and vice versa.
I'm not sure if I am tackling this right. The admin role would basically have more privileges on the system. But I cannot understand if it should be a 1-to-1 or not. Who would the parent be, etc.


texts: Storing a json string that is 10 kb in size in a SQL Server CE cell

texts:
I have a SQL Server CE database in which I have column that takes values of json strings. These json strings are above 9kb as a result of which I am unable to use varchar or char. I guess I could tackle this through the usage of ntext.

But in SQL Server, this is marked to be removed in an upcoming edition. I am not sure of SQL Server CE. Also I am having where and having conditions / statements on other values stored in this column.

Therefore having ntext may not help as well. Are there any other data types that I could use to store this json string? Any help would be much appreciated.


texts:
I've got a task to work with a 9.6 Postgres DB, and for some reason Autovacuum is disabled.

The database was extremely slow, and after a manual VACUUM it could breathe again.

The point is, why disable Autovaccum? Is there any practical scenario for a small database to have Autovaccum disabled?


texts: What is the JDBC URL for Oracle's Data Guard Fast-Start Failover?

texts:
Currently I have...

url=
"
  jdbc:oracle:thin:@
    (
    DESCRIPTION=
      (
      ADDRESS_LIST=
        (LOAD_BALANCE=OFF)
        (FAILOVER=ON)
        (
        ADDRESS=
          (PROTOCOL=TCP)
          (HOST=10.0.0.6)
          (PORT=1521)
        )
        (
        ADDRESS=
          (PROTOCOL=TCP)
          (HOST=10.0.0.7)
          (PORT=1521)
        )
      )
      (
      CONNECT_DATA=
        (SERVICE_NAME=mySID.nowhere.org)
        (SERVER=DEDICATED)
      )
     )
"


texts: In general should we avoid using ORM's with Redshift

texts: My team is considering utilizing Hibernate/Envers for a granular point in time rollback solution for Redshift data. I have heard that using an ORM with Redshift is not a good idea. Are there particular drawbacks related to using an ORM such as hibernate with Redshift?


texts: Select results from table ONLY if rows meet 2 conditions

texts:
**OrdersTable**
  OrderID
  Product

SELECT OrderId, Product
FROM OrdersTable
WHERE Product = 'ProductA'
AND Product = 'ProductB' --doesn't work as AND looks for the same data in one row

OrderID   Product
-----------------
628       ProductB
628       ProductA

I am trying to query OrderID's that contain BOTH Product 'A' AND Product 'B'.
I've tried using EXISTS, AND/OR, etc but they always return an either or situation (i.e. the query returns orders that have a Product 'A', but not a Product 'B' or vice versa). How can I query only OrderID's that contain both Products A and B?


texts:
This query not working, but the error not make sense

SELECT count(*) FROM t
WHERE json_typeof(info->'vatids')='array' AND json_array_length(info->'vatids')>1;

ERROR:  cannot get array length of a scalar

PS: idem with JSONb.


texts: Get a parent and the children using a child ID or the parent ID

texts:
I have a hierarchical table with 2 levels of depth – that is, parents and children.

Having a parent ID, I want to extract the parent and its children.

Having a child ID, I want to extract the parent and the other children related of that parent.

I've come up with the following query that seems to work OK:

SELECT *
FROM album a
JOIN (
    SELECT IFNULL(a.parent_album_id, a.album_id) parentalbumid
    FROM album a
    WHERE a.album_id = 64
) t1 ON t1.parentalbumid = IFNULL(a.parent_album_id, a.album_id)

But is this the most optimal way of achieving my goal?


texts:
Using MySQL Work Bench, When I look at the system variables of a server I am connected to, it shows the SQL_MODE as "no engine substitution"

When I open a Workbench Model, and use the "sync with any source" menu item. When it alters the tables, it runs the alter query using the follow sql_mode:

SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

Im not sure where it is getting that from, is it safe to override the sql_mode in the connection  to be just no engine substitute?

Why would it be trying to use a different mode when altering than the server shows it is using?

The server itself is running 5.6.47


texts: Fastest way to get the length of the intersection of two text arrays in PostgreSQL

texts:
This is what I am currently using:

CREATE FUNCTION array_intersect(a1 anyarray, a2 anyarray)
RETURNS anyarray AS
$$
  SELECT ARRAY(
    SELECT unnest($1)
    INTERSECT SELECT unnest($2)
    ORDER BY 1
  );
$$ LANGUAGE sql
IMMUTABLE STRICT;

--get the length:
select array_length ( array_intersect(array[...], array[...]), 1);

Is there a faster way?


texts: Same query, Same linked server. Different result

texts:
I have two instances (SQL2008R2_A and SQL2008R2_B), they have same linked server configuration to Oracle_C. I used the same query to select from Oracle_C. SQL2008R2_A returned the correct result but SQL2008R2_B get nothing.
And I tested linked server connection of both instances. They are all successfully.
Interesting...


texts:
I've this query:

 SELECT DISTINCT `default_wall_posts_comments`.`c_id` as c_id,
  `default_wall_posts_comments`.`comments` as comments,
  UNIX_TIMESTAMP() - default_wall_posts_comments.date_created AS pass_time_comment,
  `default_profiles`.*, `default_wall_posts`.*,
  UNIX_TIMESTAMP() - default_wall_posts.date_created AS pass_time
 FROM (`default_wall_posts`)
 LEFT JOIN `default_profiles`
    ON `default_profiles`.`user_id` = `default_wall_posts`.`userid`
 LEFT JOIN `default_wall_posts_comments`
    ON `default_wall_posts`.`p_id` = `default_wall_posts_comments`.`post_id`
 WHERE `default_wall_posts`.`userid` =  '1'
 ORDER BY `default_wall_posts`.`date_created` DESC LIMIT 7

But results for the main table which is default_wall_posts are repeated for each default_wall_posts_comments.

I'm trying to get all comments for one default_wall_posts. Can be this query optimized in some way?


texts: Two backup programs, diff and log out of sync

texts:
I am running encrypted backup on SQL Server 2017.
Doing a test restore, it failed:
RESTORE DATABASE [test]
FROM DISK = N'\\DPH-DD-SLI-001\SLI_SQL_Backups\Encrypted\DPH-SQL-SLI-12P\BTBLEAD\FULL\DPH-SQL-SLI-12P_BTBLEAD_FULL_20201021_185145.bak'
WITH NORECOVERY, REPLACE;

RESTORE DATABASE [test]
FROM DISK = N'\\DPH-DD-SLI-001\SLI_SQL_Backups\Encrypted\DPH-SQL-SLI-12P\BTBLEAD\DIFF\DPH-SQL-SLI-12P_BTBLEAD_DIFF_20201022_071002.bak'
WITH NORECOVERY;

Processed 199264 pages for database 'test', file 'BTBLEAD_Data' on file 1.
Processed 2 pages for database 'test', file 'BTBLEAD_Log' on file 1.
RESTORE DATABASE successfully processed 199266 pages in 446.348 seconds (3.487 MB/sec).
Msg 3136, Level 16, State 1, Line 6 This differential backup cannot be restored
because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 6 RESTORE DATABASE is terminating abnormally.

It WAS running flawlessly, until they installed Commvault cloud backup.
I found that if a Commvault backup kicks off, I can no longer restore my native local backup past the point in time that Commvault kicked off!
Apparently, SQL Server resets the LSN on each backup, but doesn't know there are two distinct programs running.  When Commvault kicks off, the LSN is brought forward, and my native backup does not know about it
I like having my own local backup, as it is faster and quicker to restore than the cloud backup, than waiting for another group to do the restore from the cloud for me.
Is there any way to keep the backups separated?
Another group manages the Commvault backup. I am new to SQL Server - I am an Oracle DBA by trade.


texts: Postgres Refresh Materialized View Locks

texts:
I have a materialized view in Postgres, and want to know what locks (if any) are taken out when refreshing that view.

CREATE TABLE people ( name VARCHAR(30) );
INSERT INTO people VALUES ('Alice'), ('Bob'), ('Cher');
CREATE MATERIALIZED VIEW test AS SELECT * FROM people;
REFRESH MATERIALIZED VIEW test;

Specifically, I'm trying to understand whether the REFRESH MATERIALIZED VIEW command takes out an ACCESS EXCLUSIVE lock.

I tried an explain with no success:

#> EXPLAIN REFRESH MATERIALIZED VIEW test;

                QUERY PLAN
-------------------------------------------
Utility statements have no plan structure


texts: Check difference between results not listed and identify which day is the date

texts:
I have the following query:

SELECT
    V.CHAPA,
    F.NOME,
    V.DATA,
    MAX(V.SEQUENCIALBATIDA) AS BATIDA
      FROM
            ARELBATIDATRANSITOVIEW AS V
            LEFT JOIN V_DADOSFUNC  AS F ON V.CHAPA = F.CHAPA
        WHERE
            V.CHAPA = 9132
        AND BATIDA IS NOT NULL
        AND V.CODCOLIGADA = 1
        AND  YEAR ( V.DATA ) = 2016
        AND MONTH ( V.DATA ) = 9
         GROUP BY V.CHAPA,
                  F.NOME,
                  V.DATA
          ORDER BY V.DATA ASC

It returns me:

1234    JOAO DA SILVA   2016-09-02 00:00:00.000 4
1234    JOAO DA SILVA   2016-09-03 00:00:00.000 4
1234    JOAO DA SILVA   2016-09-04 00:00:00.000 2
1234    JOAO DA SILVA   2016-09-05 00:00:00.000 4
1234    JOAO DA SILVA   2016-09-06 00:00:00.000 4

>>> 1234    JOAO DA SILVA   2016-09-07 00:00:00.000 2
>>> 1234    JOAO DA SILVA   2016-09-09 00:00:00.000 4

1234    JOAO DA SILVA   2016-09-10 00:00:00.000 4
1234    JOAO DA SILVA   2016-09-11 00:00:00.000 2
1234    JOAO DA SILVA   2016-09-12 00:00:00.000 4
1234    JOAO DA SILVA   2016-09-13 00:00:00.000 4

>>> 1234    JOAO DA SILVA   2016-09-14 00:00:00.000 4
>>> 1234    JOAO DA SILVA   2016-09-16 00:00:00.000 4

As you can see in places marked with >>> there is no record that date, eg the date jumps 7 to 9 date and the date 14 jumps to 16, it ta correct because that day had no change.

What do I need to know: How many ente day on 08 and 15 which are the days that has no movement because this period can not exceed 7 days.

Another vestment I need is: At the same query:

SELECT
    V.CHAPA,
    F.NOME,
    V.DATA,
    MAX(V.SEQUENCIALBATIDA) AS BATIDA
      FROM
            ARELBATIDATRANSITOVIEW AS V
            LEFT JOIN V_DADOSFUNC  AS F ON V.CHAPA = F.CHAPA
        WHERE
            V.CHAPA = 9132
        AND BATIDA IS NOT NULL
        AND V.CODCOLIGADA = 1
        AND  YEAR ( V.DATA ) = 2016
        AND MONTH ( V.DATA ) = 9
         GROUP BY V.CHAPA,
                  F.NOME,
                  V.DATA
          ORDER BY V.DATA ASC

I need to identify what day of the week is the date, and list only Sunday.

How could proceed with these two cases?

  Note: I'm sorry if you have any clerical error and because I am not
  good at English and used a translator.


texts: Problem in Fetching Result set in Desired Format

texts:
I have 2 tables having the following columns

Person
------
person_id,
first_name,
middle_name,
last_name

Items
-----
person_item_id,
person_id,
item_type,
status,
ordered_date

The query is

select
    Person.person_id as PID,
    Group_concat(Items.item_type) AS ITYPE,
    Group_concat(Items.status) AS STATUS,
    Group_concat(Items.orderd_date)AS ODATE,
    Group_concat(Items.person_item_id) AS IID
from
    Persons inner join
    Items ON Person.person_id = Items.person_id
group by person_id;

The resultset returned is as follows

PID ITYPE      STATUS                ODATE                     IID
1   A,A,B,C    Y,N,Y,Y         2/5/2012,5/5/2012,17/5/2012     1,1,2
2   A,B        Y,N             5/5/2012,15/6/2012              1,2

One Person can have many item_type and can order same item many times,I want to show the record set in such a manner that no matter the item is purchased by person or not , if the item is present in the record set i am drawing a column and have to show the result corresponding to that as such if the item is purchase 3 times then i want to show the item and it's corresponding record i.e order_date,status etc in sequential manner.

For eg :-

If there are 3 items A, B,C then output should be shown on the screen as :-

PID           A           B                 C

1             Y           N                 Y
          2/5/2012      5/5/2012         7/5/2012
         -----------
              Y
          17/5/2012

2             Y           N
          12/6/2012    15/6/2012

Now Suppose there is another item so what i am doing is first fetching the result set from above query and then traversing through that result set to check the existence of that item & if that item is present i am showing it & it's corresponding values , so 1st i have to traverse the whole result set to get the item_type and then have to show it .


texts: Is it possible to see the (completed) SQL Statements executed by a specific session?

texts:
Is it possible to see a list of all past SQL statements executed by a specific session (or number of sessions)?

For example, the following query gives me all currently executing statements from all sessions where the machine is VZWCLIENT8. But I would like to see the past execution.

select s.sid, s.serial#, a.sql_text
from v$session s
join v$sqlarea a on a.hash_value = s.sql_hash_value
where upper(machine) like 'VZWCLIENT8'


texts:
Very frequently when I restore databases the restore operation gets stuck and the wait type is ASYNC_IO_COMPLETION.
It can be in such state for 10-20-30 minutes untill it proceeds to PREEMPTIVE_OS_WRITEFILEGATHER and this is when progress (percent_complete) starts to grow.

However, if I see ASYNC_IO_COMPLETION and interrupt the process of restore and then run it again, I immediately see PREEMPTIVE_OS_WRITEFILEGATHER and percent_complete goes up.

It would be good to understand this behaviour and learn if there is a more proper way to speed up the restore operation.
Thank you


texts: Drop database from remote that has connected users

texts:
I have a "small" problem. There is a database server that contains a database where some test frontends are connected to. I want to update that database from a dump + some scripts to the most recent version. For that I need to drop the db. But when I kill the connections to the DB, the clients immediately reconnect within a second. :-(

So I try to disallow new connections, kill the connections and then drop the DB. For that I write a small script. The essential part is that:

SQL="UPDATE pg_database set datallowconn = 'false' where datname = '$DB_NAME';
     SELECT pg_terminate_backend(procpid) FROM pg_stat_activity where datname = '$DB_NAME' and current_query
     NOT LIKE '%SELECT pg_terminate_backend(procpid)%'; DROP DATABASE $DB_NAME;"

psql -h $DB_HOST -W -U $DB_USER "$DB_NAME" -c "$SQL"

But psql is not able to perform the drop database in a multiline statement...

So I tried (just for the sake of trying it out) to do the drop db in a second statement. But as I expected that complains that it can't drop the DB because the DB does not allow new connections...

Isn't there a reliable way to perform an action like this?

I can't simply stop all clients that are connected, because I don't know in my script which clients are connected from where. So that option is not possible.


texts: Can one of Windows failover cluster nodes run on Linux?

texts: As SQL Server is available for Linux can I configure Clustering on Linux nodes? I mean mixed with Windows - some nodes are on Linux, others on Windows in the same cluster?


texts: Get unique ids based on order by another column using group by with row_number

texts:
Here is a link to the SQLfiddle. For pagination purpose, I am first executing a query to execute if's and order by's and return a list of ids, using which I query for all the data with contained lists for those ids. I am having a problem with order by in the first query, where it wont let me use order by in a nested statement(view). In short, a fiddle is provided. Using the query

SELECT req.reqId, req.RN from (
SELECT
    (ROW_NUMBER() OVER (ORDER BY A.requisitionId asc)) as RN,
    A.reqId as reqId
    FROM dbo.RequisitionToPatient B
    INNER JOIN dbo.Requisition A ON A.reqId = B.reqId
    INNER JOIN dbo.Orders C on C.reqId = A.reqId
) req
GROUP BY req.reqId, req.RN

I am getting

reqId   RN
----------
12      1
12      2
14      3
11      4
11      5
10      6
10      7
9       8
9       9
9       10
9       11

What I want is

reqId
-----
12
14
11
10
9


texts: Show table records based on user condition in SQL Server

texts:
I have a web application backed by SQL Server database. It has a report, that combines students of selected semesters and a specific way. the following image show the interface for selecting semester and number of students.

The user selects the semesters and enters numbers of students.
To be clear, suppose a user enters 3 students and selects 1th and 2th semesters. The system should return all students which are enrolled in 1th and 2th semester as following:

------------------------
Id  StudentName Semester
------------------------
1       A          First
4       D          First
7       G          First
2       B          Second
3       C          Second
5       E          Second
9       I          First
10      J          First
12      L          First
6       F          Second
8       H          Second
11      K          Second

The dataset is as follow:

Student Table

-----------------
Id  StudentName
-----------------
1       A
2       B
3       C
4       D
5       E
6       F
7       G
8       H
9       I
10      J
11      K
12      L

Semester Table

-----------------
Id  Semester
-----------------
1       First
2       Second
3       Third

Enrollment Table

----------------------------
Id  StudentID   SemesterID
----------------------------
1      1            1
2      2            2
3      3            2
4      4            1
5      5            2
6      6            2
7      7            1
8      8            2
9      9            1
10     10           1
11     11           2
12     12           1

Any idea on how to show the records of the above table as mentioned?


texts: Why SQL Server Express 2012 take automatically backup in "Virtual Device" without virtual device configuration?

texts:
I have noticed that after migration of SQL Server 2012 Enterprise to SQL Server 2012 Express, every day it shows "spurious" virtual backup after taking manual backup of database.

When I execute this T-SQL to show backup details

select * from msdb.dbo.backupset;

select * from msdb.dbo.backupmediafamily;

It shows that details of backup with manual backup details & virtual device backup details. I am also attaching the screenshot of backupset & backupmediafamily :

* After Edited*

I have check out from windows PowerShell , The SQLWriter service is running in window services. And my Windows Server 2012 R2 has installed in Domain environment. So, The virtual backup is running from SQLWriter service.

Why does it show most of the automatically backup taken in virtual device with NT AUTHORITY\SYSTEM user name & by default it shows the backup with virtual device & their some virtual location. But my SQL Server database is not attached with any SAN device.

The database is on SQL Server 2012 Express. So, there is no any option of SQL Server Agent Automatic Backup. How it shows automatic backup in log files. Automatically it take the "Device_Type=7" which signifies virtual device location, as per MSDN  https://msdn.microsoft.com/en-us/library/ms178018(v=sql.110).aspx & https://technet.microsoft.com/en-us/library/ms190284(v=sql.110).aspx  if "Device_Type=7" i.e, Virtual device.

But here I would like to say that before migration of database, the database was on SQL Server 2012 Enterprise & it was setup with SQL Server Agent automatic backups. But this time using SQL Server 2012 Express.

Even automatically it is taking backup of "System databases" as well as "User Databases".

My question is that if any virtual device has not setup then why my SQL Server Express database is taking virtual backups. Even this database is my audit database. If this backup will going on continuation manner.

Is there any impact of my audit data?

Any suggestion would be appreciated?


texts:
Got a bit of a strange one that I can't seem to work out.

We have a vendor database ( so we have no control over the table structure, foreign keys, etc...) that's on SQL Server 2012 SP2. We have a large amount of data that's getting loaded daily via an SSIS package.

The SSIS package is inserting rows using an OLEDB Command that is simply executing a stored procedure

EXEC StoredProc @Param1, @Param2, @Param3, @Param4, @Param5

The simplified version of what this procedure does is as follows

 INSERT INTO TableA (Col1, Col2)
 VALUES(@Param1, @Param2)
 SET @IdentA = SCOPE_IDENTITY()

 INSERT INTO TableB (Col3, Col4)
 VALUES(@IdentA, @Param3)
 SET @IdentB = SCOPE_IDENTITY()

 INSERT INTO TableC (Col5, Col6)
 VALUES(@IdentB, @Param4)
 SET @IdentC = SCOPE_IDENTITY()

 INSERT INTO TableD (Col7, Col8, Col9, Col10)
 VALUES(@IdentA,@IdentB,@IdentC,@Param5)
 SET @IdentD = SCOPE_IDENTITY()

 INSERT INTO TableE (Col11, Col12, Col13, Col14, Col15)
 VALUES(@IdentA,@IdentC,@IdentD,@Param3,@Param5)

We have triggers on each of the tables involved to transfer newly inserted data across to another system.

What appears to be happening is that the triggers do not fire when the procedure is run from SSIS. Everything works as expected when running the procedure from SQL Management Studio.

Anyone got any thoughts on why this could be occurring, and how we can resolve the issue.

Cheers
Phil


texts: How to substitute substring in column A with matching string in map table

texts:
I want to process foo's Column A to become B, using the replacement patterns found on a separate table bar.

CREATE TABLE foo
AS
  SELECT *
  FROM ( VALUES
    ( 'one, 1, two, 2, three, 3' , 'one apple, two bananas, three strawberries' ),
    ( 'one, 1, four, 4, three, 3', 'one apple, four oranges, three strawberries' ),
    ( 'two, 2, four, 4, three, 3', 'two bananas, four oranges, three strawberries' )
  ) AS t(a,b);

CREATE TABLE bar
AS
  SELECT *
  FROM ( VALUES
    ( 'one, 1'    , 'one apple' ),
    ( 'two, 2'    , 'two bananas' ),
    ( 'three, 3'  , 'three strawberries' ),
    ( 'four, 4'   , 'four oranges' )
  ) AS t(pattern,repl);


texts: Missing mysqld.sock file on linux server

texts:
I have this error:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2 "No such file or directory")

Does anyone know what is wrong? It seems it isn't installing mysqld.sock when I run apt-get install mysql-server. I tried find / -name "mysqld.sock" but it can't find anything. How can I get mysqld.sock?


texts: Create event trigger as non-superuser in Postgres

texts:
Is it possible to give a role or a set of attributes to a non-superuser in postgres that would allow the user to create an EVENT TRIGGER?  Simple example of what (obviously) doesn't work in psql:

=> CREATE OR REPLACE FUNCTION do_nothing()
->   RETURNS event_trigger
->  LANGUAGE plpgsql
->   AS $$
$> BEGIN
$>   SELECT 1;
$> END;
$> $$;
CREATE FUNCTION

=> CREATE EVENT TRIGGER do_nothing_on_ddl_change ON ddl_command_start
->    EXECUTE PROCEDURE do_nothing();
ERROR:  permission denied to create event trigger "do_nothing_on_ddl_change"
HINT:  Must be superuser to create an event trigger.

The root reason for needing this is that I'm using an RDS-backed Postgres instance, and attempting to setup streaming of that database (data and schema changes) into a Redshift instance.  Using Amazons DMS services, setting this up is up is fairly straightforward - however, there's a catch: Starting up the streaming process creates a DDL audit table in the database, as well as an event trigger that captures any DDL changes.  The trigger writes the changes to the audit table, and those changes are streamed into redshift.

Here's where the problems lie:

A non-superuser (lets call it app_production, since this is part of a rails app) is responsible for making DDL changes to the db.
If we use the non-superuser to create the data streaming task in AWS, that user is unable to create the required event triggers to stream schema changes.
If we use the superuser to create the streaming task, then future schema migrations made by app_production fail, as they attempt to write to an audit table created by the superuser.

Any ideas?


texts: Join second table based on third table with conditions

texts:
I"m using Doctrine for a website that has 3 tables to connect together. In simplest terms, they look like this

Contractor
- id
- name

ContractorJob
- id
- contractor_id
- job_id
- status

Job
- id
- start_date
- end_date

I want to join Jobs to Contractors based on a date range, but you can see that the only way to do that is through the ContractorJob table. It means that a simple inner join between Contractor and ContractorJob is essentially random, and therefore the subsequent join between ContractorJob and Job is mostly useless.

How can I join Contractor to Job using start_date and end_date as conditions?

Pseudo query is as follows

select from contractor c
left join ContractorJob cj on cj.contractor_id = c.id
left join job j with j.contractor_job = cj.id and j.start_date < :start_date and j.end_date > :end_date
where j.id is null

(find all contractors who do not have a job in the given date range)

*note: Doctrine is a web framework DB manager that handles low level query processing so that applications can use more high-level functions. It allows you to say query.innerJoin('a.second_table', 'b') and have the correct join made


texts: DBCC SHRINKFILE on the distribution database

texts:
I have an .mdf file from distribution database which is growing.
I know the advantages and disadvantages of shrinking database/files.
Is it okay to shrink the .mdf file to some a smaller size.
Is there any data loss during this time and does this affect the replication.?


texts: How to import excel to MS Access table design?

texts:
NOTE: This is NOT asking how to export MS Access table design to excel. This is the opposite direction.

Given a excel sheet, it has field info (name, start position, and width). I want to export this excel sheet into MS Access table design.

The excel file:

Field 1 len = 3 start = 1
Field 2 len = 2 start = 4
......

The MS Access design table

Field name Field length
Field 1    3 (to be appeared)
.......


texts: Msg 22830 level 16 on sp_cdc_enable_db_internal - could not update metadata that indicates database is enabled for Change Data Capture

texts:
I'm attempting to create an MCVE for this recent question.

SQL Server is returning an error when attempting to update the system tables in master to indicate CDC is enabled for my test database.

Here's the code:

USE master;
IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'CDC_test')
BEGIN
    ALTER DATABASE CDC_test SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE CDC_test;
END
CREATE DATABASE CDC_test;
ALTER AUTHORIZATION ON DATABASE::CDC_test TO [sa];
GO
USE CDC_test;
GO
EXEC sys.sp_cdc_enable_db;

And the error:

  Msg 22830, Level 16, State 1, Procedure sp_cdc_enable_db_internal, Line 193 [Batch Start Line 11]
  Could not update the metadata that indicates database CDC_test is enabled for Change Data Capture. The failure occurred when executing the command 'sp_addrolemember 'db_owner', 'cdc''. The error returned was 916: 'The server principal "sa" is not able to access the database "master" under the current security context.'. Use the action and error to determine the cause of the failure and resubmit the request.

Some checks I've performed from the same session:

SELECT [SQL Server Edition] = SERVERPROPERTY('Edition')
    , [Am I member of sysadmin] = IS_SRVROLEMEMBER('sysadmin', SUSER_SNAME())
    , [Is sa Member of sysadmin] = IS_SRVROLEMEMBER('sysadmin', 'sa')
    , [sa is enabled?] = (SELECT ~ sp.is_disabled FROM sys.server_principals sp WHERE sp.name = 'sa');

╔═════════════════════════════╦═════════════════════════╦══════════════════════════╦════════════════╗
║     SQL Server Edition      ║ Am I member of sysadmin ║ Is sa Member of sysadmin ║ sa is enabled? ║
╠═════════════════════════════╬═════════════════════════╬══════════════════════════╬════════════════╣
║ Enterprise Edition (64-bit) ║                       1 ║                        1 ║              1 ║
╚═════════════════════════════╩═════════════════════════╩══════════════════════════╩════════════════╝

SQL Server Login Mode is set to Mixed Authentication:

EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE'
    , N'Software\Microsoft\MSSQLServer\MSSQLServer'
    , N'LoginMode';

╔═══════════╦══════╗
║   Value   ║ Data ║
╠═══════════╬══════╣
║ LoginMode ║    2 ║
╚═══════════╩══════╝

I can see the code for sys.sp_cdc_enable_db, which looks like:

create procedure [sys].[sp_cdc_enable_db]
as
begin
    declare @retcode int
            ,@containment tinyint
            ,@db_name sysname

    -- Verify SQL Server edition
    -- CDC is restricted to Eval, Enterprise and Developer editions
    IF (sys.fn_MSrepl_editionid () not in (30,31))
    BEGIN
        DECLARE @edition sysname
        SELECT @edition = CONVERT(sysname, SERVERPROPERTY('Edition'))
        RAISERROR(22988, 16, -1, @edition)
        RETURN (1)
    END

    -- Verify caller is authorized to enable change data capture for the database
    if (isnull(is_srvrolemember('sysadmin'),0) = 0)
    begin
        raiserror(22902, 16, -1)
        return 1
    end

    /*
    ** Contained Database check (Replication is not yet supported on contained databases)
    ** If the current database is a contained database, then we error out.
    */
    SELECT @containment=containment FROM sys.databases WHERE
        database_id = db_id()
    if (@containment != 0)
    BEGIN
        set @db_name = db_name()
        RAISERROR(12839, 16, -1, @db_name)
        RETURN(1)
    END

    exec @retcode = sys.sp_cdc_enable_db_internal

    if (@@error <> 0) or (@retcode <> 0)
    begin
        return 1
    end

    return 0
end

However, I cannot find the code for sys.sp_cdc_enable_db_internal... I've looked in master and mssqlsystemresource for both system and non-system objects.  I can run sp_cdc_enable_db_internal from the DAC, but it still fails with the same error.


texts:
I'm new to PostgreSQL (using ver 8.4.20) and need to setup a master and standby SQL server setup.  I am planning to use warm standby setup.  I have read the docs on PG and understand the basics, but a few things aren't clear:

If I use log shipping, what program is responsible for moving the logs from the master to the standby? Do I need to setup an rsync job, or does PG take care of this automatically?
If I wanted to keep control of this synchronizing (by using rsync), could I disable PG's synchronization (if such a thing exists)
Assuming I need to setup an rsync job, do I sync the archive directory?  or the main PG log directory?
Is it possible to combine log shipping (to cover longer outages) and streaming/SR (for record level synchronization)?  Is that a desirable thing?
Is it possible to script something on the standby which tells me if it cannot sync with the master because too many old archive files are missing).  So that I can send myself an alert, or run some other script.


texts: Give user access to information schema on mysql

texts:
[42000][1044] Access denied for user 'user'@'%' to database 'information_schema'

I'm trying to execute a database in Ubuntu using DataGrip and I get the error of the title. I've looked into other similar issues and apparently this is not wrong because I have only read permissions -> https://stackoverflow.com/questions/18238372/1044-access-denied-for-user-chirops0localhost-to-database-information-s
The problem is that the solution is not clear as I have done the same thing on windows and it does not give me issues. I've only had this issue in Ubuntu.
+-----------+------------------+------------+------------+
| host      | user             | Grant_priv | Super_priv |
+-----------+------------------+------------+------------+
| %         | user             | Y          | Y          |
| localhost | mysql.infoschema | N          | N          |
| localhost | mysql.session    | N          | Y          |
| localhost | mysql.sys        | N          | N          |
| localhost | root             | Y          | Y          |
+-----------+------------------+------------+------------+

I checked the correct drivers used and the connection is correctly set up and tried the parameters that I have on my windows machine with no success. Here is what DataGrip highlights to be the problem.
CREATE TABLE `Admin` (
  `Admin_id` varchar(13) DEFAULT NULL,
  `Priviledge_Level` varchar(29) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


texts: Get count estimates from pg_class.reltuples for given conditions

texts:
Is it possible to query the reltuples column for a given table with additional conditions like table.name LIKE 'hello%'?

Currently on my bigger tables the SELECT count(*) query takes long and I wouldn't need an exact count. So I wanted to know if it's possible to add WHERE clauses to the reltuples as well?


texts:
If an index has more than one attribute in it, is there any speed gained in a select statement whose where clause uses one of the attributes in the index?

For example, take a table T with an index on attributes a and b. Is the index useful for the query:

select * from T where a='foo'

I ask because the book I'm reading has the following statement which I have trouble understanding:

  If the key for the multiattribute index is really the concatenation of the attributes in some order, then we can even use this index to find all the tuples with a given value in the first of the attributes.


texts: How to UPDATE a column to SET previous row value to NULL cells?

texts:
I have a table as

id  value
1   music
2
3   movie
4
5
6   book
7
8

Some values are missing, and I want to assign the value of previous row to produce

id  value
1   music
2   music
3   movie
4   movie
5   movie
6   book
7   book
8   book


texts:
I am working on a new project and I am in the process of designing the Microsoft SQL Server database behind the system.

The system will contain an infinite amount of customers and each customer has multiple users. Each customer will have their own subset of data and the amount of data is going to be vast. Worst case scenario is approximately 5+ million rows per month across multiple tables per customer.

To optimize performance per customer I was considering a system containing one system database and multiple customer databases. My thought is mainly that I see no reason why one customer should query through other customer's data. The issue as I see it is that this solution would cause relationships between the customer databases and the system database (cross-database relationships).

Am I overthinking the performance issue?


texts: Are multiple databases sharding for read on docker as a good idea?

texts:
So I have a server with multiple CPU cores and it installed a database. Do you think that if we install multiple databases (data sharding) with docker, then each request will go to different databases is a good idea? The data is different in each database and based on the request from a client it will query on the different database.

I don't know this can be worse than a dedicated database with all data or not?


texts: SQL server standard 2016 fail over cluster Amazon AWS

texts: I am in the process of migrating to Amazon AWS and need a SQL server high availability solution. The current licence that I have is SQL standard 2016. At this time Amazon does not support shared volumes for Windows instances. Therefore, I am not able to do a regular SQL cluster fail over solution. This is the one where if the entire server goes down the stand by server picks up the slack and continues writing to the same storage. My only option is high availability always on basic groups. As I am starting to get familiar with this feature I find it very maintenance intensive and can see it becoming a problem when dealing with thousands of databases. In my case I have about 5k databases mostly small in size 600mb or less each. My question is Amazon not a viable hosting environment for a full SQL fail over solution. Is the high availability always on basic groups one per database a viable solution?


texts:
Help me create a hierarchical query.
I have two table:

Table 1(dic_kgs)

KGS_ID    KGS_PARENT_ID    KGS_CODE
1                           A
2              1            A0
3              1            A1
4                           B

and so on

Table 2(dic_kgs_strings)

KGS_ID    LANG_ID    KGS_NAME    KGS_ADD_INFO
1            1        Name1
2            1        Name2
3            1        Name3
4            1        Name4

and so on.

I want to get result in the follow view:

A Name1
A0 Name2
A1 Name3
B Name4

I have writen only part of query:

select kgs_code
  from dic_kgs
  start with dic_kgs.kgs_parent_id is null
      connect by prior dic_kgs.kgs_id = dic_kgs.kgs_parent_id;

Thanks.


texts: Record MultiSubnetFailover value at connection?

texts:
With AlwaysOn, there are 3 options for the keyword MultiSubnetFailover

True (yes)
False (no) (example)
Not sent/used

Not all applications support it's use in connection strings. Not all vendor contacts understand it's importance to performance at failover.

As a DBA I would like to be able to validate what property is being used by the application connection.  If I have not made the appropriate setting change to RegisterAllProvidersIP it can cause issues.  See: Follow-up: After Creating an Availability Group Listener

It does not appear to be capture in either sys.dm_exec_sessions nor sys.dm_exec_connections

How can I capture what (if any) keyword values were used to create a connection to one of my instances?


texts: ERROR: invalid input syntax for type json in postgres import

texts:
I have taken a dump of postgres database from server, let's say abc.sql whose total size is 56 GB. I created a local database in postgres, let's say import_here where I have to import the abc.sql. I am using the following command to import:
sudo -u postgres psql import_here < abc.sql

I get the following error:

I have checked the validation of json using jsonlint and it was valid. I am working on ubuntu 20.04 and the postgres version is 10.51.


texts: Not all variables bound ORA-01008: not all variables bound error

texts:
I am using Oracle SQL developer. I am running a query on employees table which is by default, having columns like
first_name, last_name, employee_id, email, phone_no, hire_date, job_id, salary, commission_pct, manager_id, department_id.

I am writting a report that displays the last name and salary of employees who earn more than an amount that the user specifies after a prompt.
If you enter 12000, it should display all employees earning more than 12000.
Eg: Salary_value: 12000.

My report is like this

select last_name, salary
from employees
where employees.salary>&sal;

But I am getting an error like this

ORA-01008: not all variables bound
01008. 00000 - "not all variables bound"
*Cause:
*Action:
Vendor code 1008

Clearly there is nothing in the cause section. I need help please. Thanks in advance.

BUT I can succesfully run it from Sql plus command line utility as well as I can run it as a *.sql file. But It is not working in case of a report. Why is this happening?


texts: Oracle redo log buffer cache vs redo log file

texts:
While going through the Oracle architecture I got stuck with the below question. Could someone help me answering this?

Why do we need both redo log buffer cache and redo log files? Why can't we write the data directly to redo log files from database buffer cache?


texts: Table design for child tables with multiple many-to-many relationships

texts:
We are working on designing tables for a new Networking application, and have been having some problems with finding the best design for the top level tables.

Network is the top level table.
Under the Network there are: Subnet, VirtualLan (VLan), and Virtual Private Lan Service (VPLS).
A Network can optionally have a Subnet, a VLan, and a VPLS. Subnets, VLans, and VPLSs, if they exist, they must belong to a Network.
Many-to-many relationships can exist for Subnets, VLans, and VPLSs.
Subnets, VLans, and VPLSs all have one or more Location(s) (e.g. – Boston, Washington), and if they have any relationships to each other, the Locations amongst the relationships must all agree.

We've taken a couple of runs at this, and keep running into problems when we get down to many-to-many relationships among the Subnets, VLans, and VPLSs and the locations.  we were trying to use an association table that held a foreign key from each of the Subnet, Vlan, VPLS, and Location tables, but we run into problems when we try to create the relationships (like what if there are existing relationships - do we delete those first, and then create new relationships).

Can anyone give me ideas on the best approach at designing these tables?


texts: Bind variable vs. string-concatenated variable

texts:
I've made an anonymous block that has dynamic SQL:

01    DECLARE
02        l_table_name VARCHAR2(100);
03        l_field_name VARCHAR2(100);
04        l_domain_name VARCHAR2(100);
05
06    BEGIN
07        FOR list_fields IN (
08            SELECT
09                TABLE_NAME
10                ,FIELD_NAME
11                ,DOMAIN_NAME
12            FROM
13                ENG.FIELD_DOMAIN_ENG_VW
14            WHERE
15                TABLE_NAME NOT LIKE '%ANNO%'
16            )
17        LOOP
18            l_table_name := list_fields.TABLE_NAME;
19            l_field_name := list_fields.FIELD_NAME;
20            l_domain_name := list_fields.DOMAIN_NAME;
21
22            EXECUTE IMMEDIATE
23            'INSERT INTO ENG.CV_ERRORS
24            (TABLE_NAME, FIELD_NAME, ERROR)
25            SELECT
26                :bv1 AS TABLE_NAME
27                ,:bv2 AS FIELD_NAME                 --<<-- Lines in
28                , ' || l_field_name || ' AS ERROR   --<<-- question.
29            FROM ' ||
30                l_table_name ||
31                ' LEFT JOIN
32                (
33                SELECT CODE
34                FROM  ENG.D_CV_ENG_VW
35                WHERE DOMAIN = :bv3
36                )
37                ON ' || l_field_name || ' = CODE
40            WHERE
41                ' || l_field_name || ' IS NOT NULL
42                AND
43                CODE IS NULL'
44
45            USING l_table_name, l_field_name, l_domain_name;
46
47        END LOOP;
48    COMMIT;
49    END;

+------------+------------+-------------+
| TABLE_NAME | FIELD_NAME |    ERROR    |
+------------+------------+-------------+
| TABLE_1    | FIELD_1    | BLACK SHEEP |
| TABLE_1    | FIELD_2    | zzzz        |
| TABLE_2    | FIELD_1    | asdf        |
+------------+------------+-------------+

There's a part of it that I don't understand how/why it works.

27        ,:bv2 AS FIELD_NAME
28        , ' || l_field_name || ' AS ERROR

Line 27 uses a bind variable that is tied to the l_field_name variable. The value that is returned is the name of the field (example: FIELD_1, FIELD_2, FIELD_1).

Line 28 breaks up the string, and uses the l_field_name variable directly (via concatenation). The value that is returned is the value of the field (example: BLACK SHEEP, zzzz, asdf).

Why do the bind variable (line 27) and the string-concatenated variable (line 28) return different values, when they reference the same thing (l_field_name)?


texts: Does @os_run_priority in sp_add_jobstep actually work in SQL Server 2008 R2?

texts:
Does @os_run_priority in sp_add_jobstep actually work, in SQL Server 2008 R2?

It is described as "reserved" or "undocumented". However, I see it in the sp_add_jobstep definition:

@os_run_priority INT = 0, -- -15 = Idle, -1 = Below Normal, 0 = Normal, 1 = Above Normal, 15 = Time Critical)


texts: Restarting database server or just the database

texts: I am an application programmer. We have a SQL Server 2012 instance that hosts many related databases (around 12 databases). The vendors did some changes to one of the database and the application and they want us to reboot the database server. I did some research and found out that rebooting a db server is always not a great option unless changes have been made in the server configuration. Is there a way to restart just that database? Would that help?


texts: Setting up Mirroring in SQL Express Edition

texts: I know doing something like mirroring in SQL Server express edition is not possible, but can we do real-time db backup and restore and setup some automated task which acts like mirroring and keep them in sync.


texts:
Where does the logins users are stored in the SQL server “in which data base “
In some softwares that requires the admin to create users/password for his users such as ArcGIS server the logins information are stored in a separate database so that the admin takes periodically backups just in case if the server was down to avoid losing the usernames and passwords. In the SQL server 2012 how can I manage to back up my logins since the moved the database form one machine to another preserves the users but the users cannot connect to the SQL server due to the loss of logins.


texts: How To Set Up Web Synchronisation With IIS 7.5

texts:
I set up merge replication web sync using a pull subscription.

When I try to start the merge agent at the client side I get this error,

The Merge Agent could not connect to the URL
'http://address.com/MergeReplication/replisapi.dll' during Web synchronisation.
Please verify that the URL, Internet login credentials and proxy server settings are
correct and that the Web server is reachable.

When I browse to the address http://address.com/MergeReplication/replisapi.dll?diag it works fine when I specify the same login credentials as I did for the subscription.

If we have a think about some of the possible causes, proxy server, we're not using that. URL is fine (as far as I can tell). Login credentials are OK as far as I can tell too.

The other thing I noticed is that I can see the https traffic in fiddler when I put the ?diag address in my browser, but when I start the merge agent I don't see the traffic in fiddler.

UPDATE:

I used wireshark to look at the traffic going between my subscription PC, and the IIS server. When I run the subscription agent I get this traffic,

TLSv1 Client Hello
TLSv1 Server Hello
TLSv1 Client Key Exchange
TLSv1 Change Cipher Spec, Encrypted Handshake Message
TCP https [FIN, ACK]
TCP https [RST, ACK]

The last item there RST is hilighted as red, and does not appear when I browse to the ?diag page.

Perhaps there is a problem with the certificate I have. I created a self signed security certificate and installed it on my PC. When I browse to the ?diag page I don't get a security certificate error.


texts: Convert Select statement results into binary and insert into a varbinary column

texts:
I am attempting to accomplish the following and 1) not sure if it can be done and 2) if it can I have no idea how!  I have tried a couple of approaches but no joy. FYI I am not a full time DBA....just have the task as part of my web dev job.

Using SQL Server 2008 R2 I have a table with a varbinary column that holds a .csv file. The current external process gets the results from a select statement, writes the results to disk, retrieves the file, and the writes it to the database column. What I am wondering is if the following is possible:

Within the stored procedure take the results from the select statement and insert them directly into the varbinary column via a CONVERT or ??. My goal is to eliminate the writing to disk portion and hopefully just have to run the procedure to get the data into the database.

Any assistance you could provide would be appreciated! Thanks in advance...


texts: Extract specific backupfiles from SQL Server 2008 backup

texts: I have a big .bak file containing a full backup and some transaction log backups. Is it possible to extract only the transaction log backups so I can copy them to another PC? (the full backup has already been restored and I don't want to send the big *.bak file again)


texts:
I have a machine which have 2 SQLServer instances

WINXP-PRO\SQLSERVER2000 (SQL Server 2000)
WINXP-PRO\SQLEXPRESS (SQL Server 2005 Express Edition).

In \SQLEXPRESS, I have database wavinet2 and wavinet2_mapic. In \SQLSERVER2000, I create linked server to [IP_ADDRESS]\SQLEXPRESS, the linked server is created.

All tables have shown up except for database wavinet_mapic.

I provide the remote login using sa. I try to select and it cannot found the table.

Any hint what is causing this problem?


texts: Best practices for creating partition schemes and functions

texts:
I'm implementing partitioning for the first time in SQL Server 2008 R2. I've several large tables that I am partitioning.

My main question is: Should I use the same Partition Scheme and Function for all these tables, or should I create multiple Partition Schemes and Functions?

When I move older data to a different file group, will having separate Schemes and Functions make moving the data easier?

And what are some things that I should consider when making this decision?


texts: Wal-e : " pg_stop_backup still waiting for all required WAL segments to be archived"

texts:
I've been trying to use wal-e to take incremental backup of my postgresql database by following this blog. I've a database on my postgresql and using an AWS S3 bucket. while I'm using the following command

sudo -u postgres /usr/bin/envdir /etc/wal-e.d/env /usr/local/bin/wal-e backup-push /var/lib/postgresql/10/main

it shows this

Any help on this?

Thanks :)


texts: Finding by ID in postgresql very slow on big table

texts:
I have videos table with 18M rows. When I search for particular video by ID, it takes up to 6 seconds to complete. Sometimes it takes few milliseconds, sometimes up to 6 seconds, but on average it is around 2 seconds.

Application is hosted on heroku and I'm using Crane database (https://addons.heroku.com/heroku-postgresql) with 410MB of RAM.

Is there any way to speed this up? I'm querying for videos 50 times per second on average, and new videos are inserted/updated at rate of 50 per second.

explain analyze SELECT * FROM videos WHERE id = 17841464 LIMIT 1;
                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..6.43 rows=1 width=119) (actual time=2337.892..2337.894 rows=1 loops=1)
   ->  Index Scan using videos_pkey on videos  (cost=0.00..6.43 rows=1 width=119) (actual time=2337.888..2337.888 rows=1 loops=1)
         Index Cond: (id = 17841464)
 Total runtime: 2337.943 ms

Here is how the table looks like:

 \d+ videos;
                                                           Table "public.videos"
     Column     |            Type             |                      Modifiers                      | Storage  | Stats target | Description
----------------+-----------------------------+-----------------------------------------------------+----------+--------------+-------------
 id             | integer                     | not null default nextval('videos_id_seq'::regclass) | plain    |              |
 uuid           | character(11)               | not null                                            | extended |              |
 channel_id     | integer                     | not null                                            | plain    |              |
 category_id    | integer                     |                                                     | plain    |              |
 title          | character varying(255)      |                                                     | extended |              |
 published_at   | timestamp without time zone |                                                     | plain    |              |
 view_count     | bigint                      |                                                     | plain    |              |
 like_count     | integer                     |                                                     | plain    |              |
 dislike_count  | integer                     |                                                     | plain    |              |
 favorite_count | integer                     |                                                     | plain    |              |
 comment_count  | integer                     |                                                     | plain    |              |
 disabled       | boolean                     | default false                                       | plain    |              |
 created_at     | timestamp without time zone |                                                     | plain    |              |
 updated_at     | timestamp without time zone |                                                     | plain    |              |
Indexes:
    "videos_pkey" PRIMARY KEY, btree (id)
    "videos_uuid_idx" UNIQUE, btree (uuid)
    "videos_latest_by_channel_idx" btree (channel_id, published_at DESC)
    "videos_top_by_channel_idx" btree (channel_id, view_count DESC)
Has OIDs: no