texts:
I'm using SQL Server 2016 to manipulate some large research data-sets.

I keep setting the recovery mode to simple, however it keeps reverting somehow back to full. Any guidance or advice on how to make the change permanent?

The data are low value, easily reproducable, etc. so there's no need for full recovery.


texts: Remote connection to a dBase database living on a private server

texts:
So I have to work with legacy code, built on dBase. The dBase app is currently used on desktop through a bunch of UI forms. This will still be the case, but added functionalities are needed. This code lives on a private server, so I will connect to it through a VPN. Then I have a cloud application that needs to consume the data from dBase (which will provided added functionalities). The problem is that I don't know much about dBase (or microsoft's side of the force for that matter).
I can see 2 ways to go on about this:

The poor man's way: the dBase app dumps its data locally on the private server in some format at some interval. A powershell process on the server listens at a port for connections through the VPN.
Using a proper connector (ODBC?) to directly link the dBase database to my cloud app's (MySQL or Postgres, whichever integrates better if that makes a difference).

It seems that I can find ODBC driver to connect a MySQL. However this seems to be for a connection to a local database. My understanding of ODBC drivers is that each implementation is specific, some implement a protocol to allow remote connections while others may not. However I cannot find in the doc above mentions about remote connections, so this makes me think this driver may not solve my issue.
So... am I stuck with the poor man's way here? Or anyone knows of a connector (ODBC or otherwise) that I can use for this purpose? Or any completely different solution that would allow a cloud app to talk to that dBase instance?


texts: Finding Count of Occurrences & Time Gap between a Timeline in MySQL

texts:
I have a Table, like the below, which has a timeline and values (like YES/NO) that are coming from different sensors. How do I do the following:

How many times there has a been a flip-flops (for YES to NO or NO to YES), in the below example it has happened 2 times.

1 , A0001, NO,  2017-06-14 02:03:26.0
2 , A0001, NO,  2017-06-14 02:03:26.0
3 , A0001, YES, 2017-06-14 02:03:26.0
4 , A0001, NO,  2017-06-14 02:03:26.0
5 , A0001, YES, 2017-06-14 02:03:26.0
6 , A0001, NO , 2017-06-14 02:03:26.0


texts:
I have a number of tables in an existing database that i would like to migrate to a new database but with a little different structure.

For example if a current table has columns like below:

table1
id, a1, a2, a3, a4, b1, b2, b3, b4

And the new structure would be something like:

table1, table2 (new table)

table1 (patient names)
id, a1, a2, a3, a4, bfk

table2 (doctor names)
id, b1, b2, b3, b4

EDIT:

Adding hypothetical scenario of patients and doctors.

Lets say that the original table had 4 rows for 4 patients so
it would look like

 row1   id, pat1fname, pat1lname, pat1address...., doc1fname, doc1lname,....
 row2   id, pat2fname, pat2lname, pat2address...., doc2fname, doc2lname,....
 row3   id, pat3fname, pat3lname, pat3address...., doc1fname, doc1lname,....
 row4   id, pat4fname, pat4lname, pat4address...., doc1fname, doc1lname,....

Lets say that we only have two doctors and we want to put them in their own table 'Doctors'

so the tables would look like this

Original table structure
     row1   id, pat1fname, pat1lname, pat1address...., doc1fname, doc1lname,....
     row2   id, pat2fname, pat2lname, pat2address...., doc2fname, doc2lname,....
     row3   id, pat3fname, pat3lname, pat3address...., doc1fname, doc1lname,....
     row4   id, pat4fname, pat4lname, pat4address...., doc1fname, doc1lname,....

New table structure
'Patients' table
    row1    id, pat1fname, pat1lname, pat1address, 1, ....,
    row2    id, pat2fname, pat2lname, pat2address, 2, ....,
    row3    id, pat3fname, pat3lname, pat3address, 1, ....,
    row4   id, pat4fname, pat4lname, pat4address, 1, ....,
'Doctors' table
    row1    1, doc1fname, doc1lname,....
    row2    2, doc2fname, doc2lname,....

What would be a good way of linking these two new tables and converting each row from one-table-structure above to the two-table-structure i mentioned.


texts:
I have 2 windows 7 machines on a very high security domain one acting as a server, the other as a client.

Each box has a SQL Server Express 2005 database and application "A", however I'm only using one of them to store data (the server), the client machine just uses application "A" and a system data source to connect to the server machines database.

My problem is that applciation "A" is acting funny, I'd like to see how long its taking for the data to come from the Server machines database, to the client machine.

I'd normally just ping the server from the client, but due to the restrictive GPO on this high security domain, I cant...

There are obviously firewall exceptions to allow the connectivity to travel from Server -> Client.  Is there a query I can run from the client machine to give me a response time/any other useful data?


texts: Some SQL Server geospatial queries take much, much longer than others

texts:
We have a simple SQL Server table with geospatial data that looks like this:

CREATE TABLE [dbo].[Factors](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [StateCode] [nvarchar](2) NOT NULL,
    [GeoLocation] [geography] NULL,
    [Factor] [decimal](18, 6) NOT NULL,
 CONSTRAINT [PK_dbo.Factors] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)

We have something like 100k+ rows in it right now, but that's expected to grow to millions.

We run queries on it that look like this:

declare @state nvarchar(2) = 'AL'
declare @point geography = geography::STGeomFromText('POINT(-86.19146040 32.38225770)', 4326)

select top 3
    Lat,
    Lon,
    Factor,
    GeoLocation.STDistance(@point) as Distance
from dbo.Factors
where StateCode = @state and GeoLocation.STDistance(@point) is not null
order by Distance

Here's the bit that's weird though. The data in that table is spotty: we've got it for the southern parts of a state, for instance, but not for the whole state. If the point we search for is within a few hundred meters of points we've got data for (for instance, from the southern part of the state), the query returns subsecond. But if it's, say, 100 kilometers from the nearest data point (for instance, if the target point is from the northern part of the state), then the query will take up to 3 minutes or so to return. In both instances, query plans indicate that they're starting with a scan of the geospatial index, so it's not the problem that sometimes happens, that SQL Server can't figure out it's supposed to use the index in question.

My assumption is that it has something to do with how the geospatial index is laid out.

CREATE SPATIAL INDEX IX_Factors_Spatial
ON [dbo].[Factors] (GeoLocation)
USING GEOGRAPHY_AUTO_GRID
    WITH (
            CELLS_PER_OBJECT = 16,
            PAD_INDEX = OFF,
            STATISTICS_NORECOMPUTE = OFF,
            SORT_IN_TEMPDB = OFF,
            DROP_EXISTING = OFF,
            ONLINE = OFF,
            ALLOW_ROW_LOCKS = ON,
            ALLOW_PAGE_LOCKS = ON);

But I don't know that I grok the details well enough to put my finger on the problem.

Any suggestions for how to approach troubleshooting this?


texts: If A is next to B, how to automatically assert that B is next to A

texts:
I am working in Access 2010.

Let's say I have a database that tracks where packages sit in a warehouse.

Table: Packages
PackageID (PK)
PackageHeight
PackageWidth
PackageWeight
PackageOwner
(etc)

Now I want to show what packages each package is next to.

For example, A is next to B and M.

Table: PackageNextTo
ID (PK), PackageID (Packages.PackageID, FK), NextToPackageID(Packages.PackageID, FK)
1, A, B
2, A, M

So when I look up A, I find out it is next to B and M.

But when I look up B, I also want to find out it is next to A.... and when I look up M, I want to find out it is next to A and also, for example, next to T.  (but M and B are not necessarily next to each other).  Do I have to define these relationships explicity by adding records as follows:

Table: PackageNextTo
ID (PK, AutoIncrement), PackageID, NextToPackageID
1, A, B
2, A, M
3, B, A
4, M, A
5, M, T
6, T, M

OR is there a way to automatically say if x is next to y then y is next to x.

(OR a better way to structure this data altogether!!)

In human terms I see redundancy between records 1 and 3 above, and between 5 and 6 above, and so on.... but I don't know if this is redundant to a database or if the database needs this explicitly called out.

If the database requires this explicit, is there a way to run a macro or query that will automatically create (B,A) when (A,B) is defined?


texts: Table Valued Parameter not pulling the correct data for my sp?

texts:
http://sqlfiddle.com/#!3/b2643/5/1

http://sqlfiddle.com/#!3/b2643/5/0

This link above has the sample database tables I created on SQLfiddle.

I have 3 main tables in my stored procedure:

TransmittalSheet, DocumentInfo and TransmittalSiteLead. Each TS can have many documents released with it and also many site lead staffs for each Transmittal that implements at different times.
Our users run reports through the use of a VB application but its pulling the data from a SQL Server in the back end via stored procedures. Currently, they can pull all docs implemening per facility individually/ seperately, Sometimes these documents are shared or implemented in multipple facilities at the same date.

My goal is to get all the documents implementing for all facilities (5 of them) at once for the one division (West) within specific date range, then at the end I would like to take care of duplicates or I mean showing the same shared document once.

With the help of Aaron Bertrand, he was able to show me the use of Table Valued Parameter, now I get partial results, versus all documents that I see when I run the original individual stored procedure that has one variable pointing to one facility at a time, so I wasn't sure what I'm missing in my query, please advice

CREATE TYPE dbo.Facilities AS TABLE
(
  FacilityID TINYINT PRIMARY KEY
);
go

declare @F dbo.Facilities;
Insert @F Values (1), (2), (3), (4), (5), (7);

-- Facility 6 is considered the Division
go

create PROCEDURE [dbo].[GetImpDocsThree]
  @facilities dbo.Facilities ReadOnly,
  @facilitydivisionID tinyint = 6,
  @startdate smalldatetime = '06/16/2005',
  @enddate smalldatetime = '04/28/14'
AS
BEGIN
  SET NOCOUNT ON;

  ;WITH f AS
  (
    SELECT FacilityID FROM @facilities
    UNION ALL SELECT @facilitydivisionID
  )

  SELECT  di.DocumentNumber, di.DocumentVersion, di.DocumentTitle,      f.FacilityID,tsl.TransmittalSiteLeadFacility,
ts.TransmittalNumber, ts.TransmittalVersion,
FacilityImpDate = COALESCE(tsl.TransmittalSiteLeadImpDate, ts.TransmittalImpDate)
   FROM dbo.DocumentInfo AS di
   INNER JOIN dbo.TransmittalSheet AS ts
     ON di.DocumentTransmittalImp = ts.TransmittalID
     LEFT OUTER JOIN dbo.TransmittalSiteLead AS tsl ON ts.TransmittalID =   tsl.TransmittalSiteLeadTSID
   INNER JOIN f
     ON tsl.TransmittalSiteLeadFacility = f.FacilityID

-- TSSiteLeadImpDate sometimes have different dates than TransmittalImpDate

-- Select documents implementing within the date range

        Where transmittalSiteLeadImpDate
    BETWEEN  @startdate AND @enddate
     order by di.DocumentNumber

    END

-- Above runs successfully but it returns partial data as I need something like this   for the last part in the date range query as it was working for the individual sp:

/*
-- Select documents implementing within the date range
            where ((SELECT dbo.TransmittalSiteLead.TransmittalSiteLeadImpDate
            FROM dbo.TransmittalSiteLead
            --WHERE dbo.DocumentInfo.DocumentTransmittalImp = dbo.TransmittalSiteLead.TransmittalSiteLeadTSID
            where (dbo.TransmittalSiteLead.TransmittalSiteLeadFacility = f.facilityID OR
                    dbo.TransmittalSiteLead.TransmittalSiteLeadFacility = @facilitydivisionID))
                     BETWEEN  @startdate AND @enddate)

order by di.DocumentNumber

              END
*/


texts: Postgresql doesn't accept replication connection

texts:
Plain old streaming replication.
PostgreSQL: 9.2.7
Windows 8.1 64 bit

My primary and secondary clusters are on same windows machine. I have already done pg_start_backup() and everything, so both nodes have exact same data.

Now the problem with replication is "replication connection" from slave server do not connect to primary server but I can connect using same params using psql shell. What I think culprit is the connection string in slave's recovery.conf:

primary_conninfo = 'host = 127.0.0.1 port = 5432 user = postgres password = postgres'

I tried localhost, 0.0.0.0, lan IP everything but pg log says:

 FATAL:  could not connect to the primary server: FATAL:  no pg_hba.conf entry for replication connection from host "127.0.0.1", user "postgres", SSL off

Now look at my Master's pg_hba.conf:

host     all     all     0.0.0.0/0   trust
host    all             postgres             127.0.0.1/0               trust
# IPv6 local connections:
host    all             all             ::1/128                 md5
hostnossl    all     postgres    127.0.0.1/32    trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5

It like I allowed every possible connection yet slave can't connect. Can you do any help?


texts: Need help in increasing maximum table size

texts:
Two of the tables vb_post, vb_profilevisitor getting corrupted again and again.

The hosting company suggested the following:

  Looks like the problem is that the .MYD table was converted to a .TMD
  table as per the below article:

  http://www.raskas.be/blog/2008/04/08...and-tmd-files/

  ".TMD basically a temparory file of .MYD. it gets converted into .TMD
  if the data size of the table has exceeded the actual size of the
  data. in that case you can alter the table and increase the MAX size
  of the table."

  We renamed the table back to the .MYD extension, but please check on
  the MAX size of the the table.

May i know how to do it.
I want to change for two tables,

vb_post
vb_profilevisitor

This is my present values in database:

  vb_profilevisitor: present Rows: 11995 Avg_row_length: 15
  Max_data_length: 64424509439 vb_post: present Rows: 1325242
  Avg_row_length: 1514 Max_data_length: 281474976710655

Can I alter it and increase the maximum table size with the following.

ALTER TABLE `vb_profilevisitor` MAX_ROWS=98281474976710655 AVG_ROW_LENGTH=1000000000;
ALTER TABLE `vb_post` MAX_ROWS=98281474976710655 AVG_ROW_LENGTH=1000000000;

Need quick replies.


texts:
Given:
$which psql
/Library/PostgreSQL/12/bin/psql
$which createdb
/Library/PostgreSQL/12/bin/createdb

Recently I created a database:
$createdb -U postgres postgres
Password:

However, I don't understand why the server version varies from the client.
$psql -U postgres
Password for user postgres:
psql (12.1, server 9.6.2)
Type "help" for help.

How can I create the server on 12.1 as well?


texts:
I have an instance running which have mysql8 and a nodejs application server.
This query keeps popping up and freezing.
SELECT /*+ MAX_EXECUTION_TIME(2000) */  COUNT(*) FROM my_table;
Even though this query have MAX_EXECUTION_TIME mentioned in it, It keeps executing well past this limit (basically it never stops). The longest I have seen it running was around 20-30 days and even after that it didn't stopped I had restarted the instance.
Main issue is I can't even kill this query. Even after killing it, It keeps executing and never stops.
I can't even restart mysql. After trying to restart the mysql. It just shuts down and never starts back. It says it couldn't create some lock file and after following multiple workarounds nothing worked and
I had to reboot the server. I had tried twice and same scenario both time, and as this is the production server I can't perform experiments for long. Now every time this query pops up I wait for the inactive hours and restart the instance at that time.
SHOW OPEN TABLES;
Shows that this table is in use. It doesn't effect Reading, updating or inserting of data in the table. But as soon as I try to alter this table or any other table which has any reference from or to this table. It freezes whole node server as deadlock of queries occur and every query keeps waiting for the first query to end, which as already stated, never does until the instance is restarted.
Source of this query is probably adminer. As my node application doesn't have this query and it is executed from the user which I use for node and adminer.
This query always shows up with this specific table. Below is the sample output of show create table my_table; (table name and column name is changed)
CREATE TABLE `my_table` (
`id` int NOT NULL AUTO_INCREMENT,
`col1` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
`col2` int NOT NULL,
`col3` longtext COLLATE utf8_unicode_ci,
`col4` longtext COLLATE utf8_unicode_ci,
`col5` mediumtext COLLATE utf8_unicode_ci,
`col5` int DEFAULT NULL COMMENT 'in seconds',
`col6` tinyint(1) NOT NULL DEFAULT '0',
`col7` int DEFAULT NULL,
`col8` int DEFAULT NULL,
`col9` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`col9` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `test_level` (`col7`),
KEY `col9` (`col9`),
KEY `col6` (`col6`),
KEY `col5` (`col5`),
KEY `col2` (`col2`),
CONSTRAINT `my_table_ibfk_1` FOREIGN KEY (`col7`) REFERENCES `table_1` (`id`),
CONSTRAINT `my_table_ibfk_2` FOREIGN KEY (`col2`) REFERENCES `table_2` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=4078 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

No of rows in this table is around 4000.
exact mysql-version 8.0.19


texts: Table compression on 500GB table using Enterprise edition with online=on

texts: I am using MS SQL2016 enterprise edition and we have several tables that need to be compressed to save memory and backup space. Right now they are each around 500GB and I am trying to get some space to do test runs, but unfortunately we are not able to try this out. My question is first - I was thinking of using the Online=On option to allow the table to still be used while the compressing is happening, but how will that effect the backup? We have diff occurring 2x daily and a backup, with the table so large I imagine it will take days not hours to use the online approach. What have other done in this case, bit the bullet and take the table offline for 1.5 days over a weekend?


texts:
I'm looking for a solution to the following case:

I have a trace(only captures queries above 5000ms duration, which is about 20 per hour) running in MSSQL which I can query by using:

SELECT TextData, Duration, CPU, Reads, Writes
FROM fn_trace_gettable(N'C:\temp\TRACE.trc', DEFAULT)
WHERE EndTime > DATEADD(MI,-15,GETDATE()) AND Reads < 10 AND Writes < 10

I want this query to run this query every 15 minutes and the results should be written(each result in it's own event log) to the eventvwr using xp_logevent. Our monitor software will then trigger on the results of the Event Viewer.

I tried to achieve this by putting the results in a temp table, and then using a while to loop through each result. Because there are no ID's in the data, I can't keep count of the rows. Should I look at a SELECT TOP 1 query which deletes the row after processing to the Event viewer? Or is there an easier way?

Could someone point me in the right direction?
PS: This should work both on SQL 2008 R2 and SQL 2012


texts: Getting empty folder when using mongodump to back up my MongoDB

texts:
Basically, I have a problem with using mongodump to back up my MongoDB.

This is the general syntax I use in SSH:

mongodump -d myDatabaseName -o ~/backups/my_backup

This is the resulting message:

Fri Apr 22 20:39:57.304 DATABASE: myDatabaseName   to     /root/backups/my_backup/myDatabaseName

This simply generates a blank folder with no files in it whatsoever. The actual database is fairly large, so not sure what's going on.

I would also like to add that my mongodump client and my MongoDB version are both the same (version 2.4.9).

Not sure how to go about fixing this. Any help is appreciated.


texts:
I have two tables products and positions.

products:

id BIGINT PRIMARY KEY,
product VARCHAR(100),
criterium1 VARCHAR(100),
criterium2 VARCHAR(100)

positions:

id BIGINT PRIMARY KEY,
position VARCHAR(100),
criterium1 VARCHAR(100),
criterium2 VARCHAR(100)

Each table has criterium1 and criterium2.  I would like to have a result set containing joins between positions and products.  For each position I would like the corresponding product that matches either on criterium1 or criterium2; when criterium1 cannot be found or is NULL, it needs to match on criterium2.

What would the SQL statement look like?

So far I have tried:

SELECT * FROM positions pos
INNER JOIN products pro
ON COALESCE(pos.criterium1, pos.criterium2,'')
    = COALESCE( pro.criterium1, pro.criterium2,'')

However, this does not match pro.criterium1 to pos.criterium1 or pro.criterium2 to pos.criterium2.

Addition:
Sorry my requirements are a little murky. Let me try to complete paint the picture.

We are using SQL Server 2005.

products:

id   product   criterium1   criterium2
 1    pro1       AAA1         910
 2    pro2       106          BB2
 3    pro3       AB1          XXY

positions:

id  position  criterium1  criterium2
 1    pos1       NULL         910
 2    pos2       106          CCC
 3    pos3       XXX          BB2
 4    pos4       AA1          XXY
 5    pos5       NULL         123

What I am looking for is table joining using conditional matching, where I would like to match positions to products.  First I want to match on criterium1.  If I cannot find a match on criterium1 or the position criterium1 is NULL, I need to match on criterium2.

In the above example the expected result set would be:

resultset
positionId  productId
 1            1 (no match on criterium1 since NULL, match on criterium2 )
 2            2 (match on criterium1)
 3            2 (no match on criterium1, match on criterium2 )
 4            1 (match on criterium1, criterium2 match is disregarded)

I only want to match positions.criterium1 with products.criterium1 and positions.criterium2 with products.criterium2.


texts: Get id, distinct idLocal of the MIN(datetime) in MySQL

texts:
I use MySQL 5.7 and I have a table with for example this data:

   id   idLocal       time
    1     1      2016-12-15 19:18:00
    2     1      2016-12-15 19:18:00
    3     2      2016-12-15 20:30:00
    4     3      2016-12-16 20:30:00
    5     2      2016-12-15 12:15:00
    6     4      2016-12-15 12:15:00

I need extract id, distinct idLocal of the MIN(time). The sentence must return this:

   id   idLocal       time
    1     1      2016-12-15 19:18:00
    4     3      2016-12-16 20:30:00
    5     2      2016-12-15 12:15:00
    6     4      2016-12-15 12:15:00

If idLocal have two rows with the same time, only need the first row.

I try with this sentence but if idLocal have two rows with the same time return me the two rows.

SELECT  P.id, fP.idLocal  ,P.time FROM Products P JOIN (SELECT idLocal,MIN(time) as time FROM Products GROUP BY idLocal) fP ON P.time = fP.time AND P.idLocal = fP.idLocal;

How do I solve it?


texts:
Normally, what some of us do is to just host the database and web-server inside 1 machine. Meanwhile we also may focus a machine with high RAM just for the remote database on LAN and another machine for web-server.

How does it affect performance for I/O speed to high load and busy server and/or anything that might need to be put under consideration for database read/writes rate. Does it efficient and optimizing the resource well if using 2 separate machine? Or any better suggestion for high performance database optimized with the resource? How about the security?

edit: 2 server with 4 thread processor (1 Web-server & 1 Database) vs 1 Server with 4 thread processor. Does it matter (always in term of performance)? Also how does it apply like Cloud Hosting performance? It does share database too among all their cluster to distribute resources and high availability am I wrong?


texts: Get a real query plan for EXPLAIN DELETE

texts:
I have a delete query that runs a long time. I try to explain it:
EXPLAIN DELETE FROM matches
WHERE id = 1

And I get:
QUERY PLAN
Delete on matches  (cost=0.43..2.65 rows=1 width=6)
  ->  Index Scan using matches_pkey on matches  (cost=0.43..2.65 rows=1 width=6)
        Index Cond: (id = 1)

But this couldn't possibly be the complete cost, because the this table is referenced in many foreign key constraints that need to be checked, and I know for a fact that this query takes 40 seconds to complete.
How can I see the total cost of such a delete query and all the foreign key checks that need to happen before it completes?

I tried EXPLAIN (ANALYZE, BUFFERS) but that does not show me this information when the query is aborted due to the foreign key constraint in another table. It just shows the error message ("violates foreign key constraint"), but does not explain how much time it spent to get to that error message.
It's OK for it to fail (and, for my logic, revert the whole transaction), but I don't want this failure to take 2ms instead of 40 seconds. I already solved my issue, but I'm still curious if it's possible to get costs of a failed query.


texts: Best way to transfer millions of rows from one database to other

texts:
There are two databases: DatabaseA and DatabaseB with the same schema. These databases have millions of rows.

The plan is to convert them into one single database (DatabaseCommon) and multiple schemas (schemaA and schemaB).

What is the best way to transfer data, indexes, etc. from the existing databases to DatabaseCommon?

Is there a Postgres script that can handle this use-case?

We can have downtime. There will be around 20 databases. Users are restricted to their own schema.


texts:
I'm in the process of researching TDE implementation on a VLDB and would like to know if anyone has real world experience with enabling encryption on a production server.

What kind of problems (if any) can I expect? My testing shows very little impact in a test environment, but I'm concerned about how long this is going to take to setup in prod. Any feedback is appreciated.

I read somewhere that I'll need to disable Backups and database maintenance during the encryption process. Is this true?

This is very concerning, since my testing took ~4 days to complete the encryption.


texts: Can SSMS Object Explorer Details configurations be exported?

texts:
A number of employees make use of SQL Server Management Studio (SSMS). I have a configuration file for all new users for Environment (etc) configurations.

I want to deliver a uniform view for each database object type (tables, stored procedures, security, etc.).

I'm looking for a method to export an Object Explorer Details configuration or a program to configure the Object Explorer Details using a secondary source (text file, database).

I've provided an illustration of two Object Explorer Details configurations.

Object Explorer Details: View 1 (for Tables and Stored Procedures)

Object Explorer Details: View 2 (for Tables and Stored Procedures)

In advance, thank you for your attention.


texts: How to extract a SQL database design into Visio in a manageable format?

texts:
I have a database diagram that I would like to extract from SQL and manipulate it to create a user-friendly and easier-on-the-eye ERD.  The database contains over 200 tables and I would like to map the tables out to show other how it works to others.

I don't want to reverse engineer as Visio reformats the ERD and no longer looks like the model I have in database designs.

I'm aware that you can right-click and copy-to-clipboard however this captures the model as an image which means I can't manipulate it.

Is there an alternative piece of software out there that I'm not aware of?


texts: IBM Informix - cast decimal to time format

texts:
My customer uses an IBM Informix environment to track production information. In the current SharePoint 2013 he was able to show those infos with this query:

Select  stillst_grund,sum(charge_bis-charge_von) from mstillstand
where firma=1
and month(anfertigungsdat)=month(date(sysdate))
and year(anfertigungsdat)=year(date(sysdate))
and arbplatznr=2
group by 1

The OleDB connection was used for this. Unfortunately, the new SharePoint 2019 seems to miss some drivers so we switched over ODBC driver. The statement above is the only one which makes trouble. The other two dozen are working quite fine.
Meanwhile, I found, that the sum()-function (or maybe the subtraction inside of it) is causing this trouble.

Some background info to this statement: It simply shows the machine name and the duration (in format %d %HH:%mm:%ss) of its time standing still.

Besides fixing the sum()-issue there might be another solution. I found another column called charge_still. This column gives me a decimal(8,4) and seems to be the difference between charge_bis and charge_von:

Select stillst_grund, charge_still AS Ausgangswert from mstillstand
where firma=1
and month(anfertigungsdat)=month(date(sysdate))
and year(anfertigungsdat)=year(date(sysdate))
and arbplatznr=2
group by 1,2

What would you say makes more sense: Fix the first statement with the sum() or convert/cast the charge_still? Until now I didn't find a handy solution to convert a decimal to the mentioned format...


texts: Semantic of SQL Query for summing up values

texts:
I am new to SQL and not getting even simple things. I am wondering if the following code does not add a real column?

select *
sum(value) over (partition by prename order by NumberOfDays) SumValue
from (select *, row_number() over (order by NumberOfDays) rn from
tablenames) as t
order by rn

I thought with this code I would create a new column called SumValue. When I execute the code above it works fine and the column SumValue with all its values is shown but when I try to calcualte with this column, let's say:

ALTER TABLE tablenames
ADD AvValue float;
UPDATE [DatabaseXY].[dbo].[tablenames]
SET AvH = [SumValue]/[NumberOfDays]

SSMS tells me that SumValue is an invalid column name. Why is that? Even if I refresh or restart SSMS and look into the object explorer there is no column called SumValue..
I guess with my presented code I only create kinda temporary columns but how do I add those values to a real column?

Thank you so much!


texts: MySQL slave replication permission error

texts:
I am having a rather frustrating issue. I have granted replication client to a user on the slave instance of MySQL. I can login using the username and password. I can exectute "SHOW SLAVE STATUS;" but I cannot start or stop the slave thread. I get the error below when i issue "STOP SLAVE" or "START SLAVE".

ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES)

Any ideas on how to resolve this? Thanks.


texts: Optimize a query that is using sub-query in where clause

texts:
I have been trying to find a way to optimize this query by probably removing the subquery in where clause.

it currently takes 2 minutes to run this query with the where clause and 4 seconds without the where clause.

SELECT
    rateString,
    currency
  FROM myTable.trades ct
  WHERE ct.id IN
      (
        SELECT top 1 id
        FROM myTable.trades tt
        WHERE tt.currency = ct.currency
        AND tt.isExecuted = 1
        AND tt.status LIKE 'executed'
        AND tt.transactionType LIKE 'sell'
        ORDER BY tt.created_at DESC
      )
ORDER BY currency, created_at DESC

The subquery will limit the result to the top 1 row per
currency with some more where and order clause.

Everything is from a single table and no foreign key is involved


texts: How to export data in Oracle database into CSV file with UTF-8 format?

texts:
I have an CSV file with WE8ISO8859P1 encoding generated from Oracle Stored Procedure. Is there any script line to define the output of CSV file in UTF-8 encoded format?

 CREATE OR REPLACE
PROCEDURE export_main(dir VARCHAR2, file_name VARCHAR2)
  IS
 select_stmt VARCHAR2(100) := 'SELECT MTYPE || '','' || MNO FROM MAIN';
    cur INTEGER;
    file UTL_FILE.FILE_TYPE;
    row_value VARCHAR2(4000);
    ret INTEGER;
  BEGIN
    -- Open a cursor for the specified SELECT statement
    cur := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(cur, select_stmt, DBMS_SQL.NATIVE);
    ret := DBMS_SQL.EXECUTE(cur);

    -- All columns were concatenated into single value in SELECT
    DBMS_SQL.DEFINE_COLUMN(cur, 1, row_value, 4000);

    -- Open the file for writing
    --file := UTL_FILE.FOPEN(UPPER(dir), file_name, 'w', 32767);
file := UTL_FILE.FOPEN_NCHAR(UPPER(dir), file_name, 'w', 32767);
    -- Export rows one by one
    LOOP
       ret := DBMS_SQL.FETCH_ROWS(cur);
       EXIT WHEN ret = 0;

       -- Get the value
       row_value := NULL;
       DBMS_SQL.COLUMN_VALUE(cur, 1, row_value);

       -- Write the row to the file
      -- UTL_FILE.PUT_LINE(file, row_value);
UTL_FILE.PUT_LINE_NCHAR(file, TO_NCHAR(row_value));
    END LOOP;

     UTL_FILE.FCLOSE(file);
     DBMS_SQL.CLOSE_CURSOR(cur);

    EXCEPTION WHEN NOT_LOGGED_ON THEN
    DBMS_OUTPUT.PUT_LINE ('A program issues a database call without being connected to Oracle.');
  END;

Oracle database is encoded with WE8ISO8859P1 and postgres DB is encoded with UTF-8

I need to import CSV file into Postgres DB table using stored procedure where in postgres by default encoding is UTF-8


texts:
I just indexed a view that returns 126 millions rows.

There's a process that uses this view to update rows using a trigger and it's by far the most expensive process in my database.

I'm monitoring this new index usage DMVs and I'm getting a strong feeling that there's too much processing power involved in maintaining this index to balance it's usefulness.

Running the query bellow, it tells me that every 2-5 seconds there's one user seek on this index. During the same timeframe there will have been around 250-1000 user updates. Since it's creation 15 minutes ago, USER_SEEKS=386 while USER_UPDATES=102 876.

Is there a magic ratio where you can safely say it's not worth it to maintain such an index ?

SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
       I.[NAME] AS [INDEX NAME],
       USER_SEEKS,
       USER_SCANS,
       USER_LOOKUPS,
       USER_UPDATES
FROM   SYS.DM_DB_INDEX_USAGE_STATS AS S
       INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID
WHERE  OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
       AND S.database_id = DB_ID()
       and I.NAME ='<index_name_goes_here>'


texts: MySQL: carriage-return in query breaks my import

texts:
I use simple echo "select * from tab" | mysql --batch --quick --skip-column-names .... > /tmp/file from mysql to get data.
Then, import to postgres using \copy tab FROM /tmp/file DELIMITER E'\t' QUOTE E'\b' NULL AS '\N' CSV.
for the most part it works well up until a carriage-return value comes in a text field.
Something like(partial string):
id      error
122859  Error invoking external process   % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current^M\n                                 Dload  Upload   Total   Spent    Left  Speed^M\n^M  0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0^M  0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0^M\ncurl: (35) Unknown SSL protocol error in connection to file.fm:443 ^M\n<br>The error occurred on line 26 in file.

When ^M comes it breaks my import, I was initially thinking this is import issue, but clearly it is the export (mysql) side.
I saw this solution but I'm thinking if there is a more elegant solution that doesn't requires me to rewrite all my export queries. Also, replacing \r\n to \n doesn't solve all the problems in this output example.


texts: See Full Exception String In Error Message

texts:
Right now I am trying to use HTTPGETCLOB function to pull data from an API. The problem is that the resulting error message does not show the whole exception string from the server. Below is the error that I am getting. I just want to see the whole sentence in quotes after the word exception, so the HTTP response code. The database is DB2 v10.5.4 and I am using data studio version 4.1.2.

Error Code:

  Procedure or user-defined function "DB2XML.HTTPGETCLOB", specific name "HTTPGETCLOBNONXML" aborted with an exception "Server returned HTTP response cod".. SQLCODE=-4302, SQLSTATE=38501, DRIVER=4.18.60


texts: Replication taking too much time between requests

texts:
We are using transactional replication in SQL Server 2014 with one master  (publisher), 1 distributor (dedicated server), and 3 slaves (subscribers).

All writes are made to master and reading is done from one of the 3 subscribers.

My issue is that if you make a insert/update/delete and the page is refreshing the update isnt there yet. There is a 1-4 sec delay until subscribers are updated which will confuse the user as the row inserted/deleted/updated but isn't reflected on the subscribers yet...

We are considering going for peer-to-peer replication, but it seems to be an overhead with IDENTITY which goes back to write to one but it will also take too much time to replicate...

What could/should we do?


texts: What is the difference between instant client and the original Oracle client? The Oracle client is almost 800MB and the instant client is not so much. I need to install oracle client, ODBC drivers and JDBC drivers. What is the best way to go and which one should I choose? The original 800 megs client or the instant client?


texts: Auditing database administrator actions with SQL Server 2014 Standard

texts:
We have a requirement to audit database administrator access to customer databases - at a minimum, knowing which customer database they were accessing (and ideally why). Without upgrading to SQL Enterprise or installing a proxy like GreenSQL are there any alternative options open to us?

I'm thinking for example some kind of privileged script that elevates (sudo style) your privileges to access a database, which logs this to the event log, and reduces them again later.

Is there any way to achieve this that would reliably auditable? Are there any other approaches we should consider?


texts: How to select rate for all selected modules and all selected years no matter they got rate for that year or not

texts:
How to write a SQL to achieve the "goal" section?  Select rate for all selected modules and selected years no matter they got rate for that year or not.

The years and modules will be input by users, then the SQL will return the sum rate for selected years and selected modules.

For instance, Find rate for ASD,EB,GOF modules from 2008 till 2012, as the above

WITH finanimpact
     AS (SELECT Extract(YEAR FROM fiwm.create_dt) AS years,
                m.code                            AS mcode,
                SUM(fiwm.rate)                    AS rate
         FROM   master.financial_impact_wfins_mapping fiwm
                inner join master.wf_instance wf
                  ON wf.sysid = fiwm.wf_instance
                     AND wf.template_id = 4
                inner join master.wf_data_cr wdc
                  ON wdc.instance_id = fiwm.wf_instance
                inner join parameter.module m
                  ON m.sysid = wdc.module
                     AND m.sysid IN ( 4, 15, 6 )
         GROUP  BY Extract(YEAR FROM fiwm.create_dt),
                   m.code
         UNION
         (SELECT NULL,
                 m1.code,
                 0
          FROM   parameter.module m1
          WHERE  m1.sysid IN ( 4, 15, 6 )
          MINUS
          SELECT NULL,
                 m.code AS mcode,
                 0
          FROM   master.financial_impact_wfins_mapping fiwm
                 inner join master.wf_instance wf
                   ON wf.sysid = fiwm.wf_instance
                      AND wf.template_id = 4
                 inner join master.wf_data_cr wdc
                   ON wdc.instance_id = fiwm.wf_instance
                 inner join parameter.module m
                   ON m.sysid = wdc.module
                      AND m.sysid IN ( 4, 15, 6 )
          GROUP  BY Extract(YEAR FROM fiwm.create_dt),
                    m.code)) SELECT fi.years,
       fi.mcode,
       fi.rate
FROM   finanimpact fi
UNION
(SELECT LEVEL + 2008 - 1,
        '',
        0 AS id
 FROM   dual
 CONNECT BY LEVEL <= 2012 - 2008 + 1
 MINUS
 (SELECT DISTINCT lfi.years,
                  '',
                  0
  FROM   finanimpact lfi))

returned result:
=========================
2008    null    0
2009    null    0
2010    EB  34640
2010    GOF 8660
2011    EB  103920
2011    GOF 12990
2012    null    0
null    ASD 0
=========================

goal:
=========================
2008    ASD     0
2009    ASD     0
2010    EB  34640
2010    GOF 8660
2011    EB  103920
2011    GOF 12990
2012    ASD 0
=========================


texts: Are there any inherent shortcomings in this schema?

texts:
This is derived from the SQL generated by sqldbm.com that I simplified because it wasn't generating foreign keys correctly and it seems to me that there's no need to pull in every foreign key from the parent anyway (perhaps I'm mistaken!)

Is there significant value in pulling in every foreign key from the parent and making them part of the child's primary key? Is there a way to automate setting these additional keys? (I.e. a ticket row being a child of an event row which is a child of a location row and the ticket row also includes location_id and needing to make sure that gets set correctly.)

Does anyone see anything in the upper schema diagram that might shoot me in the foot down the road? Not pictured but desired is an additional many-to-many mapping to event questionnaire responses also attached to a ticket (i.e. a registrant providing answers to experience levels in a sport discipline, gear questions, etc as part of their ticket purchase). I am already planning on removing the multiple locations for costs and having option value be the only place cost is found and always having an option value supplied for each event.


texts:
Mongo v. 3.4

respective part of config file:

#network interfaces

net:
  port: 27017
  bindIp: 127.0.0.1,192.168.0.15,2001:db8:0:0:0:0:2:1 (IPs are made up)

No matter the format I've used:

[2001:db8:0:0:0:0:2:1]
[2001:db8:0:0:0:0:2:1%eth1]

I always get:

2017-05-17T09:54:54.358 I CONTROL  [initandlisten] options: { config: "/etc/mongod.conf", net: { bindIp: "127.0.0.1,192.168.0.15,2001:db8:0:0:0:0:2:1", port: 27017 }, processManagement: { fork: true, pidFilePath: "/var/run/mongod.pid" }, storage: { dbPath: "/var/lib/mongo", journal: { enabled: true } }, systemLog: { destination: "file", logAppend: true, path: "/var/log/mongo/mongod.log" } }
2017-05-17T09:54:54.384 I NETWORK  [initandlisten] getaddrinfo("2001:db8:0:0:0:0:2:1") failed: Address family for hostname not supported
2017-05-17T09:54:54.385 E NETWORK  [initandlisten] listen(): socket is invalid.
2017-05-17T09:54:54.385 E NETWORK  [initandlisten] Failed to set up sockets during startup.
2017-05-17T09:54:54.385 E STORAGE  [initandlisten] Failed to set up listener: InternalError: Failed to set up sockets
2017-05-17T09:54:54.385 I NETWORK  [initandlisten] shutdown: going to close listening sockets...
2017-05-17T09:54:54.385 I NETWORK  [initandlisten] removing socket file: /tmp/mongodb-27017.sock
2017-05-17T09:54:54.385 I NETWORK  [initandlisten] shutdown: going to flush diaglog...
2017-05-17T09:54:54.385 I CONTROL  [initandlisten] now exiting
2017-05-17T09:54:54.385 I CONTROL  [initandlisten] shutting down with code:48

How do I properly bind Mongo to listen on IPv6 address? Note that this is version 3.4, where net.ipv6 is not necessary anymore.


texts: How to drop invalid Java classes from Oracle?

texts:
I tried to load some jars into my database, but I think I messed up something, because I tried as DBA and then with some oracle user... This was my first command:

call dbms_java.loadjava('-genmissing -jarasresource -v -force -grant MYSCHEMA ./some-jar-file.jar');

and then this:

call dbms_java.loadjava('-genmissing -v -force -grant MYSCHEMA ./some-jar-file.jar');

I think I also tried with and without MYSCHEMA, because my Java source had alway some errors regarding to this jars.

My problem is, that right now I have more than 1500 invalid Java classes and I don't know how to clean this mess up:

SELECT *
    FROM user_objects
    WHERE /*object_type like '%JAVA%'
    AND */status = 'INVALID'

I tried with this trick:

SET HEADING OFF SET FEEDBACK OFF SET ECHO OFF;

SPOOL delete-java-class.sql;

select 'drop java class "'||object_name||'";' from user_objects where object_type like '%JAVA%' AND status = 'INVALID';

SPOOL OFF;

SET HEADING OFF SET FEEDBACK OFF SET ECHO OFF;

SPOOL compile-java-class.sql;

select 'alter java class "'||object_name||'" resolve;' from user_objects where object_type like '%JAVA%' AND status = 'INVALID';

SPOOL OFF;

But the drop script wasn't able to drop these classes (approx. 5 of 1500 could be deleted), I got this error:

ora-29537: class or resource cannot be created or dropped directly

The recompile script was also not able to resolve the issue, but it was able to reduce the invalid count to 800.

Can someone give me some advice? :) Thank you.


texts: Should I set a variable in the same line in which it was declared?

texts:
I frequently see the syntax

Declare @var int;
Set @var = 1;

But you can set the variable at declaration time

Declare @var int = 1;

Is there a gotcha to the latter option? Am I missing something that is internal or doesn't work in all cases where you should not declare and set the variable in the declaration?

I ask because of subtitle gotcha's that I hear which I would not expect. Like Aaron Bertrand lower casing data types which is something that msdn also does not state but has been found in the community.


texts: What does MySQL actually use to decide which index should be used?

texts:
I know there is already similar question here like How does the database decide which Index to use. But it seems that those answers are not cool and useless.

In my development and use of MySQL, I found there are multiple factors that affect how MySQL decides which index should be used to execute the SQL statements. Those factors include but are not limited to:

The estimated scan count of rows for certain index.
The order by condition and whether certain index would cause filesort.
The presence of limit 0,N condition and even the value of N would impact the result.

Any response from people who have read the source code or have developed MySQL source codes will be highly appreciated! :)

If you know someone who maybe knows the answers, please share this page with them.


texts: Converting MSSQL Stored Procedure to MySQL - Syntax Error

texts:
I am converting a MSSQL Stored Procedure to MySQL, and am running into a problem. Maybe it's because I have been staring at the code for so long, but I can't seem to figure it out:

CREATE PROCEDURE usp_AddNewUser
(IN UserName VARCHAR(50),
IN UserID VARCHAR(50),
IN Password VARCHAR(30),
IN PageAccess VARCHAR(10),
IN AdminUser VARCHAR(50))

BEGIN
Declare RowID, CountAll INT DEFAULT 0

SELECT @CountAll := COUNT(*) FROM WebUsers WHERE RTRIM(UserID) = RTRIM(@UserID);

IF @CountAll = 0 THEN
    INSERT INTO WebUsers (UserName, UserID, UserPassword, AddedBy, UpdatedBy) Values (UserName, UserID, Password, AdminUser, AdminUser);
    SELECT @RowID := @@Identity;
ELSE
    SELECT @RowID := RowID FROM WebUsers WHERE RTRIM(UserID) = RTRIM(@UserID);
    UPDATE WebUsers SET UpdatedBy = @AdminUser, UpdatedOn = NOW() WHERE RowID = @RowID;
END IF;

If  @PageAccess Like ('%4%') THEN
    INSERT INTO WebUserTypeMapping (WebUserRowID, WebUserTypeRowID) Values (@RowID, 4);
ELSE

    If  @PageAccess Like ('%1%') THEN
        INSERT INTO WebUserTypeMapping (WebUserRowID, WebUserTypeRowID) Values (@RowID, 1);
    END IF;
END IF;
END;

The syntax error is at the SELECT@CountAll line:

SELECT @CountAll := COUNT(*) FROM WebUsers WHERE RTRIM(UserID) = RTRIM(@UserID)

I've tried rewriting it several times, but no dice. There are likely additional errors in this code too that I don't know about yet since I can't get by that error.

Can anyone help me out? Not that it matters, but it would totally make my Friday... :)

Thanks!

Edit: The full error message is the following:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT @CountAll := COUNT(*) FROM WebUsers WHERE RTRIM(UserID) = RTRIM(@UserID)' at line 12

MySQL Server version: 5.5.30

Edit: When setting a different delimiter, I get an error message that literally just says "Error" -- with no other information.


texts: mongod runs with command but not with config file

texts:
I am trying to start mongod with a config file on MongoDB 3.2 on Ubuntu 14.04. I can launch mongod by issuing the command myself, but I can't get it to work with the config file.

Here's the command I'm issuing:

sudo mongod --replSet rs0 --logpath /var/log/mongodb/mongod.log --dbpath /data/db --storageEngine wiredTiger --fork

When I run this command, I can then run mongo to enter the mongo shell and successfully use the database.

I don't see a difference between the command I'm issuing and the config file, which I call with sudo mongod --config /etc/mongod.conf. When I run this, and try a command in the same mongo shell I had successfully used for the prior query, I get

2017-01-31T10:55:44.899-0800 E QUERY    [thread1] Error: error: { "ok" : 0, "errmsg" : "not master and slaveOk=false", "code" : 13435 } :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
DBCommandCursor@src/mongo/shell/query.js:689:1
DBQuery.prototype._exec@src/mongo/shell/query.js:118:28
DBQuery.prototype.hasNext@src/mongo/shell/query.js:276:5
DBCollection.prototype.findOne@src/mongo/shell/collection.js:289:10
@(shell):1:1

and here is my /etc/mongod.conf. How are these different?:

# mongod.conf

# for documentation of all options, see:
#   http://docs.mongodb.org/manual/reference/configuration-options/

# Where and how to store data.
storage:
  dbPath: "/data/db"
  journal:
    enabled: true
  engine: "wiredTiger"
#  mmapv1:
#  wiredTiger:

# where to write logging data.
systemLog:
  destination: file
  logAppend: true
  path: "/var/log/mongodb/mongod.log"

# network interfaces
net:
  port: 27017
  bindIp: 127.0.0.1

processManagement:
  fork: true

#security:

#operationProfiling:

replication:
  replSetName: rs0
#  oplogSizeMB: 1024

#sharding:

## Enterprise-Only Options:

#auditLog:

#snmp:


texts:
I was was running the query in this article:

http://sqlity.net/en/708/why-cxpacket-waits-are-not-your-performance-problem/

To see what my threads were waiting on in regards to a suspended query with a wait type of CXPACKET.

However for the SPID in question threads that are running were showing wait types of NULL with every other thread in a SUSPENDED state with a wait type of CXPACKET.

I was expecting to one of the threads having some kind of wait type other than CXPACKET, can anyone explain to me what is happening in this situation?

Thanks


texts: What are the concepts that need to be gone through for MySQL DBA?

texts:
I am a MySQL Developer, besides I am handling some DBA activities like
backup, restore, mysql installation, etc at a top level.

Now I wanted to shift to DBA, So I wanted to know in depth, like which and concepts should be gone through as a DBA.

Also, what and all to be monitored on a daily basis.

Please help me in this.


texts: Installed Oracle XE 11.2. How do I create a database?

texts:
I'm new to Oracle, so please bear with me.

I recently installed Oracle XE Release 11.2 on a Windows 2008 Server that we have. The installation was successful. During installation, I was asked to set a password for sys, and I did that. Also, the HTTP port used was 8085.

So now I'm trying to create a new database. I have Toad Data Point, but I'm not sure how to connect to the server or how to create the database.

In a browser, I went to http://OurServer:8085/ but it asks for password. I tried logging in with sys, but it doesn't let me. I also tried logging in using my company userid (the one I use to log on to my Windows PC), but it didn't work either.

What else can I do?


texts: Return list of values in conditional where clause

texts:
I'm trying to filter a table based on a parameter that determines which list of values are allowed for a certain column. For @Parameter = 1 we want MyColumn to be either FirstValue or SecondValue; for @Parameter = 2 we want MyColumn to be either ThirdValue or FourthValue; otherwise we want all values of MyColumn.

This syntax clearly does not work:

declare @Parameter int = 1; -- can also be 2 or 3

select *
from dbo.MyTable
where MyColumn in
(
    case @Parameter
        when 1 then (N'FirstValue', N'SecondValue')
        when 2 then (N'ThirdValue', N'FourthValue')
        else (N'FirstValue', N'SecondValue', N'ThirdValue', N'FourthValue')
    end
);

What would be the best way to achieve this?

Edit: This works but is really clunky, so I would like to find a more elegant solution:

select *
from dbo.MyTable
    join
    (
        values
        (1, N'FirstValue'),
        (1, N'SecondValue'),
        (2, N'ThirdValue'),
        (2, N'FourthValue'),
        (3, N'FirstValue'),
        (3, N'SecondValue'),
        (3, N'ThirdValue'),
        (3, N'FourthValue')
    ) as temp(Option, Value)
    on MyTable.MyColumn = temp.Value
where temp.Option = @Parameter;


texts: SQL SERVER symmetric keys convert from varbinary to hex

texts:
Hi currently I setup symmetric key on SQL Server 2012
-- Create the certificate which will be used to encrypt the symmetric key
CREATE CERTIFICATE ENC_CC_KEY
WITH SUBJECT = 'Certificate Used to Encrypt Symmetric Key for credit card number handling';
GO

-- Create the symmetric key
CREATE SYMMETRIC KEY ENC_CC_KEY_SYM
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE ENC_CC_KEY;
GO

i and create some table for save the data
create table newcc (cardid int, namecard varchar(25), expirate_date datetime, cardnumber varchar(100))

I insert sample data using encryptbykey
OPEN SYMMETRIC KEY ENC_CC_KEY_SYM DECRYPTION BY CERTIFICATE ENC_CC_KEY;
GO
INSERT INTO dbo.newcc
   (cardid, namecard, expirate_date, cardnumber)
   VALUES
   (4, 'Cori', '2020-06-20 23:03:28', EncryptByKey(Key_GUID('ENC_CC_KEY_SYM'), '5324626821571080', 1, 'Cori'));

    GO
CLOSE SYMMETRIC KEY ENC_CC_KEY_SYM;
GO

data successfully insert to table. But currently encrypted cardnumber as varbinary. how to encrypted cardnumber convert to hex and insert as varcharvbut still can decrypt also.
because when I insert like this
OPEN SYMMETRIC KEY ENC_CC_KEY_SYM DECRYPTION BY CERTIFICATE ENC_CC_KEY;
    GO
    INSERT INTO dbo.newcc
       (cardid, namecard, expirate_date, cardnumber)
       VALUES
       (4, 'Cori', '2020-06-20 23:03:28', convert(varchar(64),EncryptByKey(Key_GUID('ENC_CC_KEY_SYM'), '5324626821571080', 1, 'Cori'),2));

        GO
    CLOSE SYMMETRIC KEY ENC_CC_KEY_SYM;
    GO

the cardnumber can't decrypt to real cardnumber


texts:
I have a database. I can attach it to SQL Server with Management Studio and perform query on the tables. But when I run the following commands:

ALTER DATABASE UFDATA_008_2013 SET SINGLE_USER;
DBCC CHECKDB('UFDATA_008_2013',  REPAIR_REBUILD);

I will get the following errors:

  Msg 8921，Level 16，State 1，Line # 3 Check terminated. A failure was
  detected while collecting facts. Possibly tempdb out of space or a
  system table is inconsistent. Check previous errors. Msg 605，Level
  21，State 3，Line # 3 Attempt to fetch logical page (1:209549) in
  database 6 failed. It belongs to allocation unit 72057600521535488 not
  to 281474979397632.

I then try with the following commands by switch to emergency mode and invoke checkdb with REPAIR_ALLOW_DATA_LOSS, but still get the above error messages:

ALTER DATABASE UFDATA_008_2013 SET EMERGENCY;
ALTER DATABASE UFDATA_008_2013 SET SINGLE_USER;
DBCC CHECKDB('UFDATA_008_2013',  REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE UFDATA_008_2013 SET MULTI_USER;

How to fix the problem? I do NOT have a backup of the database.

Thank you very much.


texts: Order by date then Group By by Name / Type before proceeding with the rest of results

texts:
I have a data like this that came from two tables.

FirstName  LastName     LeaveType  LeaveDateFrom  LeaveDateTo
Jane       Doe          VL         2019/01/02     2019/01/02
Joe        Armstrong    BVL        2019/01/02     2019/01/02
Joe        Armstrong    BVL        2019/01/01     2019/01/01
Joe        Armstrong    BVL        2018/12/31     2018/12/31
Joan       Day          VL         2018/12/31     2018/12/31
Ivan       Reyes        VL         2018/12/28     2018/12/28
Joe        Armstrong    BVL        2018/12/27     2018/12/27
John       Doe          VL         2018/12/27     2018/12/27
Jake       Doe          VL         2018/12/27     2018/12/27
Joe        Armstrong    BVL        2018/12/26     2018/12/26

The data above is a result of the following query:

SELECT emp.Emp_Fname as FirstName,
emp.Emp_Lname as LastName, lt.LeaveType as LeaveType,
CONVERT(VARCHAR(10), lt.TimeIn, 111) as LeaveDateFrom,
CONVERT(VARCHAR(10), lt.TimeIn, 111) as LeaveDateTo
FROM py_leavetable lt
LEFT JOIN py_emp_master emp ON lt.EmpCd = emp.Emp_Cd
WHERE emp.date_resign IS NULL
AND CONVERT(VARCHAR(10), lt.TimeIn, 112) >= 20181101
AND CONVERT(VARCHAR(10), lt.TimeIn, 112) <= 20190130
ORDER BY lt.TimeIn DESC

I tried adding the name fields in the ORDER BY clause but it didn't change the results. So I tried using GROUP BY. However, I ended up needing to add all the fields because it keeps returning aggregate function error. I tried researching further and it seems I need to do "mathematical operations" for the GROUP BY to work but I don't think it is applicable for the data I have. But, I could be wrong since I'm not really very good at SQL.

Can anyone help me or point me in the right direction on how I can achieve the following results:

FirstName  LastName     LeaveType  LeaveDateFrom  LeaveDateTo
Jane       Doe          VL         2019/01/02     2019/01/02
Joe        Armstrong    BVL        2019/01/02     2019/01/02
Joe        Armstrong    BVL        2019/01/01     2019/01/01
Joe        Armstrong    BVL        2018/12/31     2018/12/31
Joe        Armstrong    BVL        2018/12/27     2018/12/27
Joe        Armstrong    BVL        2018/12/26     2018/12/26
Joan       Day          VL         2018/12/31     2018/12/31
Ivan       Reyes        VL         2018/12/28     2018/12/28
John       Doe          VL         2018/12/27     2018/12/27
Jake       Doe          VL         2018/12/27     2018/12/27

It would even be better if I'm able to do it like this but having the result above should do:

FirstName  LastName     LeaveType  LeaveDateFrom  LeaveDateTo
Jane       Doe          VL         2019/01/02     2019/01/02
Joe        Armstrong    BVL        2018/12/26     2019/01/02
Joan       Day          VL         2018/12/31     2018/12/31
Ivan       Reyes        VL         2018/12/28     2018/12/28
John       Doe          VL         2018/12/27     2018/12/27
Jake       Doe          VL         2018/12/27     2018/12/27

Am I making sense here? Can anyone help me figure this out? Thanks!


texts: AlwaysOn Manual failback not working correctly

texts:
I've set up a small fail over cluster using AlwaysOn in SQL Server 2014 and Windows 2012R2.

The cluster (SQL-CLUSTER-DEMO) just contains 2 SQL nodes (SQL-01 [Primary] & SQL-02 [Secondary]) and a file share quorum. Availability Group is set up in SQL Server no problems.

I am connecting to SQL-CLUSTER-DEMO and running transactions every second.

When I simulate failures in turn (pull the network cable from the machine, replace, pull from other machine, replace), they switch between primary and secondary as expected no problems.

The issue comes when I simulate failure on the primary and then use the Manual Fail-over wizard to revert back once it has recovered. The application I have running then starts to error with "Database is Read Only" and SQL-CLUSTER-DEMO seems to think it is pointing to the secondary.

I'm really stuck trying to figure out why automatic failure swaps perfectly, but manual fail-over doesn't seem to work... what am I missing?


texts: What normalization rules does this table break


texts: Update Schema in SSIS Export Package When Source Schema Changes

texts:
I have servers with lots of client databases, all with very similar, but not necessarily identical schemae.

Some customers want their data in MSAccess on a monthly basis.  This was getting tedious to do on a manual basis, so I figured I would create SSIS packages for these exports, add them to a job, and be done with it.

I was hoping I could just create one DTSX file in the filesystem and change the database name, but I ran into schema differences between instances already so that won't work.

One issue that occurred to me is that when our developers change the source database schema, I'm guessing the SSIS packages won't know about the new tables.

Is there an easy way to deal with this?


texts: What is the impact of setting "max text repl size (B)" to -1?

texts:
I have a table replicated via transactional replication that is throwing this error message:

  Length of LOB data (68983) to be replicated exceeds configured
  maximum 65536.

This can be resolved by adjusting the "max text repl size (B)" SQL Configuration value from the default of 65536.

I see two options:

Adjust the value higher than required for this situation
Set the value to "-1" which indicates no limit

My question is:  What is the downside of setting "max text repl size (B)" to -1?

I appreciate any feedback!


texts: Fallback to known existing records when a record is missing

texts:
Related to this question, I want to return module contents for various regions/languages. I built my site in English so I know I have all of those pieces, but since I'm getting translations from an outside agency I might be missing pieces. We want those pieces to fallback to English when in doubt.

Here are my tables:

languages

id | tag   | region        | language
--   -----   -------------   --------
1    en-US   United States   English
2    es-ES   Spain           Spanish
...

modules

id | module
--   ------
1    header
2    main
3    footer

contents

id | language_id | module_id | content
--   -----------   ---------   -------
1    1             1           This is my header
2    1             2           This is my main
2    1             3           This is my footer
3    2             1           Este es mi encabezado
4    2             3           Este es mi pie de página

See here, I am missing the 'main' module (module_id=2) for 'es-ES' (language_id=2)

Here's how I'm pulling my data:

SELECT m.module, m.content
FROM `modules` m
LEFT JOIN contents c ON m.id = c.module_id
LEFT JOIN languages l ON c.language_id = l.id
WHERE l.tag = 'es-ES' OR l.tag IS NULL

which returns

module | content
------   -------
header   Este es mi encabezado
main     null
footer   Este es mi pie de página

I want that null value for 'main' to fallback to the English version ("This is my main")
Is there a simple way to do this that I'm missing?


texts: After Update Mysql 8.0.13 to 8.0.15 MySql Server Fails with error code = 1265

texts:
After Upgrading from MySql 8.0.13 (compiled from sourcecode) to MySql 8.0.15, the MySql Server can not start.

Log:

2019-02-12T10:09:52.275336Z 1 [ERROR] [MY-013178] [Server] Execution of server-side SQL statement 'INSERT INTO routines SELECT * FROM mysql.routines' failed with error code = 1265, error message = 'Daten abgeschnitten für Feld 'sql_mode' in Zeile 7'.
2019-02-12T10:09:52.302630Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2019-02-12T10:09:52.302856Z 0 [ERROR] [MY-010119] [Server] Aborting

"Daten abgeschnitten für Feld" means "Data truncated for field" - its a german version ...

table checker does not find any errors - how can I solve this problem ?

yours sincerely
Robert


texts: Get max, min, avg value within each 1 day range in sql server


texts:
This sounds like a stupid question but when posed with it on an exam yesterday, I wasn't entirely sure how to answer...I mean I know you have to put indexes on certain fields to speed up slow queries, and I know you have to make sure to load test it to

I know it has the end goal of making the database faster for read / write access from it's users but the test had alot of ways of putting this:

Giving the same access time to all database users.
Ensuring that all resources are used effectively.

Do either of those answers really embody the purpose of database tuning?


texts:
create table #Personel
(
Ad varchar(100)
)

create table #Masa
(
MasaAdi varchar(100)
)

insert into #Personel values('deneme1')
insert into #Personel values('deneme2')
insert into #Personel values('deneme3')

insert into #Masa values('Masa1')
insert into #Masa values('masa2')
insert into #Masa values('masa3')

select Ad,MasaAdi from
(
select ROW_NUMBER() over(partition by MasaAdi order by ID,Ad ) as Sira,ID,Ad,MasaAdi
from (
select newid() as ID,Ad,MasaAdi
from #Personel
cross join #Masa
)t
) t2 where Sira = 1 order by Ad

it's creating the result as shown below.

deneme1 - masa2
deneme2 - masa3
deneme1 - masa1

well,it's to be able to deploy to different tables two times the same personal.

e.g deneme1 - masa2
    deneme1 - masa1

I don't want to do that.I want to do something as shown below.

e.g deneme1-masa2
    deneme2-masa3
    deneme3-masa1

So,I don't want to deploy to different tables two times the same personal.


texts: Redshift throws Value too long for character type character varying(100) even when values are within the range

texts:
I am aware what this error message Value too long for character type character varying(100) means. So I often look for the rows which cause the trouble and fix them appropriately as deemed fit by the requirement.

But I encountered an odd issue today where the error happens even if there isn't a rough row.

Failing insert query:

INSERT INTO training.archive_temp1 (id, booking, email, pcd_temp, property_id)
WITH x_pcd AS (
    SELECT e.id,
        e.booking,
        e.email,
        CASE
            WHEN LENGTH(e.pch) > 0 THEN (e.pch || ':' || e.pcd)
            ELSE e.pcd
        END AS pcd_temp,
        e.pcd
    FROM public.extracts_temp AS e WHERE e.id BETWEEN 274939128 AND 275083166
)
SELECT x.id,
    x.booking,
    x.email,
    x.pcd_temp,
    COALESCE(c2.property_id, c.property_id)
FROM x_pcd AS x
         LEFT JOIN public.property_codes AS c ON x.pcd_temp = c.code
         LEFT JOIN public.property_codes AS c2 ON x.pcd = c2.code
WHERE COALESCE(c2.property_id,c.property_id, 0) <> 0;

If I change x.email to x.email::varchar(100) it works.

Here's the catch.

SELECT max(length(email)) FROM training.archive_temp1;
-- returns 64

Weird. So I checked

SELECT max(length(email)) FROM (
SELECT e.id,
        e.booking,
        e.email,
        CASE
            WHEN LENGTH(e.pch) > 0 THEN (e.pch || ':' || e.pcd)
            ELSE e.pcd
        END AS pcd_temp,
        e.pcd
    FROM public.extracts_temp AS e WHERE e.id BETWEEN 274939128 AND 275083166
)
-- returns 66

If no row is crossing the character limit of 100, why does it throw the error? What is happening here?

If you need me to share results from any of your queries, please let me know. Since the rows are in 100000 range, can't share the entire data here and if I could share a minimum verifiable example for the case, I wouldn't be asking this question.


texts: What is the cause of this problem with CONVERT()?

texts:
Consider the following two statements:

PRINT CONVERT(NUMERIC(38, 0), 0x0100000001, 0);
PRINT CONVERT(NUMERIC(38, 0), 0x0100010001, 0);

Both statements return -1; isn't that incorrect since the second binary value is decimal 65,536 higher than the first value, is it not?

Surely this cannot be due to silent truncation?

If I run the following statements:

PRINT CONVERT(NUMERIC(38, 0),   0x00000001, 0);
PRINT CONVERT(NUMERIC(38, 0),   0x00010001, 0);

I am presented with the following error:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varbinary to numeric.

How can I diagnose what is happening here?

I'm running this on SQL Server 2012, v11.0.5058.  The results are the same on SQL Server 2008 R2 SP2, SQL Server 2005, and SQL Server 2000.


texts: How can I integrate a new system to a design scheme I previously created?

texts:
This is a follow-up to a question I wrote a while ago, for reference, you can look it up here.

I've developed a curriculum based database scheme that looks like this(Thanks Joel Brown)

Now that a new educational system is introduced(while the old one is still not "deprecated") I have been looking for a way to integrate both curriculum systems together without creating a whole new different system; As the two systems are fundamentally different, this have been quite a challenge for me.

Here's a picture describing the new system . . .

(English level 1, 2, and 3 are classified as courses)

See, the new system removes the whole grades system. A jr. high school student can enroll in the exact same course(English level 3, for example) as a sr. high school student.

Is there a way to make those two systems work with my current design scheme? Or should I create a whole new scheme for the new system?


texts: Best way to determine Dev/Test/Prod environment in MS SQL Server 2012+?

texts:
We have 3 MS SQL Environments: Dev, Test, and Prod. There are many Dev servers, and 1 each in Test and Prod.
What is the best way to determine the "Environment" from a query? For instance, we want to watermark Dev and Test SSRS reports if it's dev or test.
I don't want to hard code a check for Prod's server name, in case the server name changes.
I also don't want to check a "settings" table in the database, because then I'm depending on the environment's restore scripts running successfully.
Are there any other solutions that will work from a query?


texts: Is there a SQL Server 2008 R2 specific target for SSDT projects?

texts:
I am making a SQLCLR stored procedure in Visual Studio 2012. I have these options for target platform:

Is there not a SQL Server 2008 R2 specific option? Is this because SQL 2008 R2 shares the same compatibility level of 100 with SQL Server 2008?


texts: In a one-to-one relationship, where should the foreign key be placed?

texts:
I am currently designing an ERD for a project management system. I am unable to choose between two workable solutions as the best solution as I wish to embrace the best standards.

The two entities that can access the system are the clients and employees. Since they have different information, I decided to treat them as different entities. I figured that it is best to have a single table for users of the system rather having the username and password fields be found in both tables of clients and employees. Plus, the constraints I have to follow:

All users are only limited to employees and clients.
But not all employees are required to be users of the system.

Here are the following entities without relationship yet defined:

Now, there is only one-to-one relationship between users and clients, employees and users. My question is where should I place the foreign key? Should I place two foreign keys in users table namely employee_id and client_id which makes it easier to find the associated client and employee entity from the user entity and where I can define authorization rules more easily? Or should I place each foreign key on clients and employees referencing the users table since I am unsure if it is an acceptable practice to have two foreign keys in a table in which only one foreign key can be used at a time.


texts: difference between having many non clustered index with single columns and with combination of many columns

texts:
Folks

I am a newbie to DB design stuffs. I am wondering what would be the difference and performance difference between having designed

many non clustered index with single columns and non clustered index with many columns combined.
Or ideally under what scenario I can combine columns to make a single non clustered index and if so, how many columns should I combine ?
What is the criteria to choose the column for non clustered index ? (I am aware that its applied only to not frequent updation table)....
Whats the difference between 2005/2008 and 2012 SQL Server in terms of non clustered index with regard to my question and also is it 249 is the max we can create ?

Thank you in advance.


texts: Problem about listing network instances on SSMS

texts:
I have a problem about listing network instances on SSMS. When I look at the Network Instances on SSMS, it shows only local server instances. By the way, it shows all the instances on the network until today.

The only change I did is using commands below, but I don't think this issue related to this commands;

ipconfig /flushdns
ipconfig /registerdns
ipconfig /release
ipconfig /renew
netsh int ip reset
netsh winsock reset

I tried these;

I'm sure that all instances I expect to see is under the same subnet.
All instances' TCP/IP's are enabled and not hidden.
All instance' allows remote connections.
SQLServer's are running.
SQLBrowser's are running.
TCP port 1433 and UDP port 1434 are not blocked by firewall.
I can ping these servers properly.
I can connect these servers by using instance name. (Like PC3/SQLEXPRESS)

Thanks for advance.
OE


texts: Connecting SQL Server 2008 R2 to SQL Server 2000

texts:
Initially, my desktop has been installed with SQL Server 2000 Standard Edition SQL Client. Recently, I installed SQL Server 2008 R2 client and server instance. I'm using SQL Server 2008 Management Studio to replace the old SQL Server 2000 Query Analyzer and Enterprise Manager.

Yesterday I try to install SQL Server 2000 instance server but I cannot connect because when I register the server instant on Entrprise Manager I get a message that I should use the SQL Server Management Studio or SQL Server Management Objects (SMO). Where is my SQL Server 2000 instance?


texts: Factors affecting the migration of MS SQL into MySQL vice versa

texts:
What are the factors affecting the migration of database from MS SQL Server to MySQL causing corrupted files?

I transferred my database from MS SQL Server 2012 into MySQL on the same Windows PC. I used the default migration wizard and it proceed. But when I export one of the media file to my local disk, I cannot open it and it prompt that the file is corrupted.


texts: mysql join 3 table retain order of data order by used in subquery

texts:
I have a query like this

SELECT a.pmid,a.sendid,a.sendtime,a.recid,a.message,b.unread,r.avator1 ra,s.avator1 sa,s.username sun,r.username run
FROM pm a
JOIN
(
  SELECT SUM(IF(recid = '347' and hasread=0, 1, 0)) AS undread,
         LEAST(sendid,recid) user1 ,
         GREATEST(sendid,recid) user2 ,
         MAX(pmid) pmid
  FROM pm
  WHERE sendid='347' OR recid='347'
  GROUP BY user1 , user2
) b
ON b.pmid = a.pmid
JOIN
  users s ON s.userid = a.sendid
JOIN
  users r ON r.userid=a.recid
ORDER BY unread DESC, pmid DESC LIMIT 0,10

I have performance issue with this query it's taking some 8-10 seconds to complete execution if I alter by removing  order by unread desc from the past of the query

query complete execution with in 1 sec but result is wrong,not what i need.

if i move order by unread desc inside the subquery example

SELECT a.pmid,
       a.sendid,
       a.sendtime,
       a.recid,
       a.message,
       b.unread,
       r.avator1 ra,
       s.avator1 sa,
       s.username sun,
       r.username run
FROM pm a
JOIN
  (SELECT SUM(IF(recid = '347'
                 AND hasread=0, 1, 0)) AS unread,
          LEAST(sendid,recid) user1,
          GREATEST(sendid,recid) user2,
          MAX(pmid) pmid
   FROM pm
   WHERE sendid='347'
     OR recid='347'
   GROUP BY user1,
            user2
   ORDER BY unread DESC,pmid DESC) b ON b.pmid = a.pmid
JOIN users s ON s.userid=a.sendid
JOIN users r ON r.userid=a.recid
LIMIT 0,
      10

then it is still not giving result what i need.

if i run the subquery ``

SELECT SUM(IF(recid = '347'
              AND hasread=0, 1, 0)) AS unread,
       LEAST(sendid,recid) user1,
       GREATEST(sendid,recid) user2,
       MAX(pmid) pmid
FROM pm
WHERE sendid='347'
  OR recid='347'
GROUP BY user1,
         user2
ORDER BY unread DESC,
         pmid DESC

separately then its giving correct data but then if i join user table once with on either recid or sendid then result is correct but when i join user  table twice with both sendid  and recid then the result order is incorrect.

table pm structure here

CREATE TABLE `pm` (
 `pmid` bigint(40) unsigned NOT NULL AUTO_INCREMENT,
 `sendid` bigint(20) unsigned DEFAULT NULL,
 `recid` bigint(20) unsigned DEFAULT NULL,
 `subject` mediumtext DEFAULT NULL,
 `message` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `hasread` int(10) unsigned DEFAULT 0,
 `sendtime` int(10) unsigned DEFAULT NULL,
 `ack` int(10) unsigned NOT NULL DEFAULT 0,
 PRIMARY KEY (`pmid`),
 KEY `sendid_2` (`sendid`,`recid`),
 KEY `recid` (`recid`,`hasread`),
 KEY `recid_2` (`recid`)
) ENGINE=InnoDB AUTO_INCREMENT=1900982 DEFAULT CHARSET=utf8

table users structure here

 CREATE TABLE `users` (
 `userid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
 `username` text CHARACTER SET latin1 DEFAULT NULL,
 `password` text CHARACTER SET latin1 DEFAULT NULL,
 `pass` varchar(30) CHARACTER SET latin1 NOT NULL,
 `groupid` bigint(20) unsigned NOT NULL DEFAULT 2,
 `email` text CHARACTER SET latin1 DEFAULT NULL,
 `rank` varchar(30) CHARACTER SET latin1 NOT NULL DEFAULT '0',
 `validated` int(10) unsigned NOT NULL DEFAULT 0,
 `avator1` varchar(30) CHARACTER SET latin1 NOT NULL DEFAULT '0.gif',
 PRIMARY KEY (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=33744 DEFAULT CHARSET=utf8mb4

please help me.


texts: SQL Server 2008 R2 installation device error

texts:
I am trying to install SQL Server 2008 R2 to use along with Visual Studio for a website I am creating.

However when I am installing SQL Server I am constantly getting the following error:

  The services could not be started, either because it is disabled or because it has no enabled devices associated with it.

I have tried uninstalling it and reinstalling it and the problem still occurs.


texts: Do you still use MyISAM or prefer Aria storage engine?

texts:
If Aria storage engine (previously called Maria) is the "new" MyISAM, which supports transaction and automatic crash recovery:

Why still use MyISAM ?
Should changing storage engine from MyISAM to Aria be a problem? (lose index or something)


texts: How do I stop Amazon Web Service's RDS from locking out connections during backups?

texts:
I've been using RDS for years, and this only became an issue a few months ago. I was pulling my hair out trying to find the source of the issue... only to figure out it's suddenly RDS.

I've made no configuration changes or anything, but now I can see that when RDS does a backup of my MySQL 5.6.34 database, it is kicking off all the clients and doesn't accept connections and queries until it finishes.

https://forum.ghost.org/t/back-with-the-ghost-sites-failing-to-load-following-an-aws-rds-maintenance-backup-window/1983/3 I wound up finding a similar problem that someone is experiencing here, but with no solution to it figured out.

What changed and how do I resolve this?


texts:
I have two different DB instances.
Is there any way to find out from instance2 (from all_synonyms view) list of all objects using synonyms from instance2 to instance 1 ...?

I want to, for example, connect on one instance, and query all object from another instance which have created synonyms for utilization in another instance ...


texts: Encrypted stored procedure is encrypting the plan cache query plan

texts:
I'm having a really bizarre issue when querying the plan cache that relates to stored procedure encryption. However, it's the stored procedure that is querying the plan cache that is encrypted, not the queries in the plan cache themselves. Let me explain...
The procedure below simply searches the plan cache for a matching string and outputs the query plan (the last column).
    ALTER PROCEDURE spQueryPlanCache @textstring nvarchar(max)
    AS
    BEGIN
        SELECT
                databases.name,
            dm_exec_sql_text.text AS TSQL_Text,
            dm_exec_query_stats.creation_time,
            dm_exec_query_stats.execution_count,
            dm_exec_query_stats.total_worker_time AS total_cpu_time,
            dm_exec_query_stats.total_elapsed_time,
            dm_exec_query_stats.total_logical_reads,
            dm_exec_query_stats.total_physical_reads,
            dm_exec_query_plan.query_plan
        FROM sys.dm_exec_query_stats
        CROSS APPLY sys.dm_exec_sql_text(dm_exec_query_stats.plan_handle)
        CROSS APPLY sys.dm_exec_query_plan(dm_exec_query_stats.plan_handle)
        INNER JOIN sys.databases
        ON dm_exec_sql_text.dbid = databases.database_id
        WHERE dm_exec_sql_text.text LIKE @textstring

    END

This is a sample of the plan XML that is outputted in the last column:
 <StmtSimple StatementText="CREATE PROCEDURE [dbo].[spMySproc];1 (@...

Everything looks normal - my plan cache sproc has found a procedure called spMySproc in the plan cache and I can see the statement text. Great!
However.....if I add WITH ENCRYPTION to the definition of my plan cache query  procedure (spQueryPlanCache) and re-run it, the query_plan column from the plan cache that is OUTPUTTED by my encrypted stored procedure has itself been encrypted!!!
  <StmtSimple StatementText="** Encrypted Text **"

This makes no sense to me. The only circumstance where I would expect this to happen is if spMySproc was also encrypted, which it isn't! If I query the plan cache again, I can see that spMySproc is not encrypted.
Is this a known bug,  or can someone explain why this makes sense to them?
Here is a minimal, complete, and verifiable example showing the issue:
CREATE OR ALTER PROCEDURE dbo.test_proc
AS
BEGIN
    SELECT 0xAEFB1FA7285840C3FF30935AE0775433E669A38EBD60091BE1990D68819F346D;
END
GO
CREATE OR ALTER PROCEDURE dbo.spQueryPlanCache @textstring nvarchar(max)
WITH ENCRYPTION
AS
BEGIN
    SELECT deqp.query_plan
    FROM sys.dm_exec_query_stats deqs
        CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) dest
        CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) deqp
    WHERE dest.text LIKE '%0xAEFB1FA7285840C3FF30935AE0775433E669A38EBD60091BE1990D68819F346D%'
        AND deqp.query_plan IS NOT NULL;
END
GO

--output from this will contain the query plan, with the StatementText column "encrypted".
EXEC dbo.spQueryPlanCache @textstring = '%0xAEFB1FA7285840C3FF30935AE0775433E669A38EBD60091BE1990D68819F346D%';

GO
CREATE OR ALTER PROCEDURE dbo.test_enc
(
    @xml nvarchar(max)
)
WITH ENCRYPTION
AS
BEGIN
    SELECT CONVERT(xml, @xml);
END
GO

DECLARE @val nvarchar(max);

SELECT TOP(1)
    @val = CONVERT(nvarchar(max), deqp.query_plan)
FROM sys.dm_exec_query_stats deqs
    CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) dest
    CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) deqp
WHERE dest.text LIKE '%0xAEFB1FA7285840C3FF30935AE0775433E669A38EBD60091BE1990D68819F346D%'
    AND deqp.query_plan IS NOT NULL;

--output here will show the same query plan, but the STatementText won't be encrypted.
EXEC dbo.test_enc @val;

The same behavior has been exhibited on 2014, 2016, and 2019.


texts: Upgrading MongoDB ReplicaSet to 3.0 causes BSONElement: bad type 108

texts:
I followed the official guide to upgrade a MongoDB ReplicaSet from 2.6 to 3.0 on the secondary member.

Shutdown mongod (systemctl stop mongodb.service) on secondary
Installed binaries (via apt-get)
Update mongod.conf
Restart mongod: failed!

[NetworkInterfaceASIO-Replication-0] Uncaught exception in NetworkInterfaceASIO IO worker thread of type: Location10320: BSONElement: bad type 108
[NetworkInterfaceASIO-Replication-0] Fatal Assertion 28820

Looks like my databases are corrupt but mongod w/out replSet works fine with the same dbs.
Did I miss something?


texts: Automatic conversion of anonymous records returned from an UDF to well known table-type

texts:
Proprietary code (that we cannot change) has a bunch of user defined functions of the type:

create or replace function f() returns record as $$ ... $$

which we call in the following way (for example):

SELECT status, log FROM f() as (status boolean, log text);

(status boolean, log text) is a rowtype of table T. Is it possible to automatically convert the record (or setof record return type) into the T rowtype without listing the attributes? What I am looking for is of the kind:

SELECT * FROM f() as T%rowtype


texts: Replication not updating XML Schema collection

texts:
So the problem I'm facing is that replication from our Main instance to our Data Warehouse instance is failing because it isn't picking up XML schema updates. Thus, when it tries to bulk copy the data from the Main table that utilizes it, it fails because XML validation fails due to the XMLSchemaCollection not containing any of the updates.

The behavior that we've observed in the last couple months is that if the XML schema is new, then replication picks it up. However, from that point forward, it ignores it. This smells like a deficiency in the way it detects updates. But I'm hoping that someone else has figure this out.


texts:
So I'm having this table that logs all errors happened in our production code. It looks like this:

id  | date | type         | message       | file         | line
----+------+--------------+---------------+--------------+-----------
INT | DATE | VARCHAR(128) | VARCHAR(4096) | VARCHAR(255) | SMALLINT

Now, in most cases errors will be reported via a simple HTTP-API, issued from the client's server running our software. Naturally, when something happens, it happens often. So most errors get logged between 10 and 200 times, in rare cases up to 1k times.

So I end up with many records having the same value for the message field (and for type and file as well). Since I could not find any details via Google, I'm now wondering whether it would be wise to manually store the strings in another table like this:

hash        | string
------------+----------
VARCHAR(32) | VARCHAR(4096)

Assuming I would hash the values of the three fields with MD5, I could shrink their space usage down to VARCHAR(34), using the scheme

l$I'm a literal value.             for fields < 32 chars
h$md5(field)                       for fields > 32 chars

I did some benchmarking and it seems to save about 15 to 20% from my error table. To handle the concurrency with transaction, my tables use InnoDB. What is the difference between MySQL VARCHAR and TEXT data types? points out that InnoDB will store string values always outside the normal table, but is it pooling or storing each string for itself?

Additionally, I plan on using PostgreSQL, too. rolleyes :-)

Any suggestions? Is it wise to go the extra mile and do string pooling?


texts: Querying the Username in a Share Data source (SSRS)

texts:
I am running a project to go over all logins of all our SSRS 2012 datasources and I need a complete list of all datasources, INCLUDING the loginname used to connect. I couldn't find it anywhere.

I am convinced the datasource loginnames must be stored somewhere inside the database, or the RDL's themselves.

Does anyone have a clue how I can list all datasource login names of these  different datasources?

See attached pic for details:

The Content column in Catalog table only contains the connection string and whether the data source is using Windows credentials (authentications) or not the username

SELECT SDS.name AS SharedDsName
       ,SDS.[Path]
       ,CONVERT(xml, CONVERT(varbinary(max), content)) AS DEF
 FROM dbo.[Catalog] AS SDS
 WHERE SDS.Type = 5

I did found the Username of the data source with this query :

select DSID, ItemID, SubscriptionID, Name, Extension, Link,
CredentialRetrieval, Prompt, ConnectionString,
OriginalConnectionString, OriginalConnectStringExpressionBased,
UserName, Password, Flags, Version
FROM DataSource

But it's encrypted - not only the password is encrypted, but also the username itself.

I would appreciate a lead into the right direction.

Thanks in advance!

Roni.


texts: How to remove job history for a TIME PERIOD in SQL Server

texts:
I am trying to purge / delete history for all jobs that ran for a week. How could I achieve this?
Please note that I've read this article that talks about deleting history for a SPECIFIC JOB (I have 200 jobs and can't use that).
Someone tried to manually delete it from table but apparently that can have bad consequences.
I also wanted to add that Purging UP TO A DAY is NOT acceptable. I need to delete a range (say Jan/04 - Jan/15). All days up to Jan 4 and after Jan 15 must remain.


texts: PostgreSQL UUID Literal: ERROR: syntax error at or near

texts:
Whever I use a UUID as a literal, I get

SELECT a82857b6-e336-4c6c-8499;
ERROR:  syntax error at or near "-"

I thought it might be an encoding issue, but that doesn't seem to be it.


texts: SQL Server Snapshot Replication and SRDF compatability

texts:
Is there anything that breaks down if you use snapshot replication and then use SRDF for disaster recovery (DR) on the snapshot publisher?

Specifically I am asking is: On the DR system, would the DR snapshot agent understand all the tags added by the original snapshot agent? Is there any information on how to reconfigure the agent?


texts: Postgres 9.6 Physical and Logical replication

texts:
I am working with Postgres9.6 with a Master/Slave cluster replication using Streaming replication (physical).

I would like to add a new Slave server database but this database with logical replication.

I tried with some configurations but it was not possible  :(
https://github.com/guedim/postgres-streaming-replication

Here is the image of what is in my mind:


texts: SQL Availability Group and SSRS, SSIS and SSAS

texts:
I am looking at a new SQL 2016 installation that will consist on two nodes (located in the same data center):

Node 1: Production DBs

Node 2: Production DBs (replica)

I am thinking where to install my SSIS / SSRS / SSAS.  If they don't have high availability requirement, then does it make sense to install them on node 2?  The only draw back I see is the potential network congestion.

So end result is

Node 1: DB Engine, Production DBs

Node 2: DB Engine, Production DBs (replica), SSIS, SSAS, SSRS, all supporting DB (not HA)


texts: Several stays: many-to-many relationship

texts:
Could you please help me with the following problem? I'm a total novice in DB design.

I have a table with researchers who visit our university. They come for some period of time (from, till). Now, how can I save in the database if somebody came several times? I have an entry already for a particular person and need to add some other stays and be able to see them in a form.

My idea was: this must be a many-to-many relationship. Ok. Then I need a third table Researcher/Stay. And then I don't know what to do next. In Stay-table "from+till" is a key. I haven't seen examples with double-value key in a many-to-many relationship and don't know what to do next.


texts: Selecting distinct values by column value

texts:
I have the following table:

+-----------------------------+-----------+
| keyword                     | data_type |
+-----------------------------+-----------+
| red                         | ctr       |
| blue                        | ctr       |
| purple                      | ctr       |
| orange                      | ctr       |
| red                         | var       |
| blue                        | var       |
| black                       | var       |
| yellow                      | var       |
+-----------------------------+-----------+

I'd like to be able to select only those which are distinct by data_type.

For example:
If I checked by data_type: var, 'black' and 'yellow' would be returned.

If I checked by data_type: ctr, 'purple' and 'orange' would be returned.

I'd normally provide an example query of what I've done, thus far, but I don't know where to start with this one - help is much appreciated.


texts:
I am simply trying to create a database.

In my account's home directory, I type in

  sqlite3 test.db

Then once I am in sqlite, I type

  .quit

That should create the database file in the current directory.
But there is no such file!
I tried it in other directories (chmodded to 777) to no avail.

What am I doing wrong?
OS is Ubuntu 14.04 LTS. Sqlite version 3.8.2.


texts:
I have set  innodb_buffer_pool_size=100G and instances to 8, but I am getting following output

mysql> show engine innodb status \G
---BUFFER POOL 6
Buffer pool size   835482

I have been reading online that bufferpool= innodb_buffer_pool_chunk_size* innodb_buffer_pool_instances.

This is confusing, should we set the default chunk size to higher values and increasing the number of instances do not cause contention anymore.

Can anyone advise on correct parameters for 100GB bufferpool size?


texts: Need help in mysql database backup on Windows except mysqldump?

texts:
Can anyone tell me what is the easy way to backup 500Gb datasize on windows environment ? My physical memory is 672 GB RAM on server?
Which tools will be fast to back up and restore?
Note : mysqldump will take more than 1 week for 500GB datasize
Any suggestion with details and is there way any way around that it can take within 2 days?


texts: How to change data structure in mysql using mysqldump without deleting files

texts:
Essentially what I'm trying to do is sync a production server with a sandbox server, but only the table structures and stored procedures. The procedures aren't any problem since they can be overriden, but the problem is the tables. I want to sync and alter their structures on the production server using mysqldump (or any other way that you can propose) without altering any existing data.

If it helps, I only want to add more columns, not remove any existing ones. Also, I am using mysqlyog.

Is there any way to do this?


texts: How to query SQL Server Memory Manager performance counters in DMV?

texts:
Is there any system catalog views or DMVs that can be used to query (in SSMS for example) below performance counters from SQL Server:Memory Manager ?

Free Memory (KB)
Target Server Memory
Total Server Memory
Maximum Workspace Memory (KB)
Database Cache Memory (KB)
Granted Workspace Memory (KB)
Lock Memory (KB)
Log Pool Memory (KB)
Optimizer Memory (KB)
Connection Memory (KB)
SQL Cache Memory (KB)
Reserved Server Memory (KB)
Stolen Server Memory (KB)


texts: Does HOT Update re-arrange existing rows on a page to create a contiguous space?

texts:
For example, on a 8kb page:

4 rows are created, each 2kb
Row 3 is deleted
Row 1 is updated to a size of 3kb

There is no contiguous chunk of 3kb space on the page, so what happens?

Do rows on a page get re-arranged to make a contiguous chunk of space for the update?
If so, then do indexes need to be updated to point to the slightly different locations? Or does it not matter as long as they're still on the same page?


texts:
I have a large database (millions of rows in hundreds of tables) that contains sensitive data (Think SSNs, account numbers, etc). I am tasked with safeguarding the data and have chosen to encrypt only sensitive data and not every field. This encryption is handled by the application, and not the database server, so all solutions require that the encryption process stays in the application.

Because there are existing JOINs in the application, I can't just encrypt every value separately because 2 identical values will yield different encrypted strings (and thus won't JOIN). I've chosen to key the data (a hash of the value, so the key will always be the same for each unique value) so that I can store the key in place of the original data and use it to look up the encrypted data when needed.

So I need to go through the database, row-by-row, and encrypt certain fields, storing the encryption in a separate table, and then storing the key in place of the original data.

My problem is that looping over the data is very slow. 10-15 minutes per 1000 values. I've optimized the task by grouping the values so that I only have to encrypt each unique value 1 time and then update all instances of that value with the key in 1 UPDATE.

Is there a different approach (keeping all the encryption in the Application layer) that can speed up my task of encrypting the sensitive fields?


texts: RHEL 6: postmaster dead but pid file exists

texts:
I am using PostgreSQL 9.2 on RedHat 6

I'm a little confused as to why the following is happening:

When postgres is running, performing "service postgresql92-postgresql status" as root results in "postmaster (pid 1379) is running..." (as expected)
As postgres run "pg_ctl stop -D $PGDATA -m s" to stop postgres
As postgres run "pg_ctl -D $PGDATA start" to start postgres (postgres is now running correctly)
Now as root run "service postgresql92-postgresql status", output is now "postmaster dead but pid file exists"

My confusion is why am I getting the message in step 4 when postgres is running correctly? Am I stopping/starting postgres incorrectly?


texts: Unable to Restore a Database to a previous point in time from a Full Backup Set

texts:
I have a full backup set (SQL server) and I am trying to restore the Database to a previous date and time. Every time I restore it (To another database) selecting a previous date that I want from the backup set, its RESTORES the latest version of the data. I can still query for data after the point in time.
All I want to for the backup to stop at the point in time indicated (see image)

Is there something that i am missing?

DB: SQL server 2000

Tool used: Enterprise manager


texts: Limiting user access by subnet to Amazon Aurora PostgreSQL

texts:
How do I limit user access by IP? For example, assuming I have two non-superusers (user1 and user2), how can allow general access (only limited by security groups) to the instance/cluster by user1 but only allow access to a particular subnet for user2?

In standalone PostgreSQL this is handled within pg_hba.conf, which to my knowledge doesn't exist in RDS and Aurora on AWS.

pg_hba.conf equivalent:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    app_db          user1           0.0.0.0/0               scram-sha-256
host    app_db          user2           10.0.12.0/24            scram-sha-256