How to copy database from server to local machine in SQL Server Management studio? I am pretty much new to SQL Server Management Studio. I connect to SQL Server using Windows authentication. But not sure if my work affect the server database. How can I make sure not to mess with the updates and commits to the data?

You probably want a local development copy of the database so that your queries and schema changes don't affect production data. Please check with your management first to make sure you don't have policies against developers having access to (or their own copies of) production data, which may be sensitive for a variety of reasons depending on your industry.

Assuming you are okay to do so, back up the database from the server, and restore it on your workstation. Make sure to use COPY_ONLY for the backup, to avoid interfering with any existing backup/recovery plans.

BACKUP DATABASE dbname TO DISK = 'E:\somefolder\file.bak'
  WITH INIT, COPY_ONLY;

Copy that file from the server to your machine.

Then restore it on your machine; you will likely need to use WITH MOVE in order to put the files in a valid location:

RESTORE DATABASE dbname FROM DISK = 'C:\temp\file.bak'
  WITH REPLACE, RECOVERY,
  MOVE 'dbname_data' TO 'C:\...\dbname.mdf',
  MOVE 'dbname_log' TO 'C:\...\dbname.ldf';


How to conditionally create JSON array in postgres with group by clause? I am using Postgresql 13.0 and have a table category as follows:
 cat_id | cat_name | piece | weight | is_tag_stock
--------------------------------------------
  4     |   cat1   | 20    | 10     | true
  5     |   cat2   | 30    | 15     | false
  4     |   cat1   | 40    | 5      | false
  4     |   cat1   | 50    | 30     | false

How do I group by in such a way that for one category, the query yields one record, and based on is_tag_stock, it should form JSON?  Also, the JSON should contain the summation of the piece and weight field grouped by cat_id and cat_name. If is_tag_stock is true, then the piece and weight should be added to the tagged field else in the untagged field.
NOTE: For one cat_id, there would be only one cat_name. cat_name is included in the table just to avoid join.
Expected Output
 cat_id | cat_name | tagged                      | untagged
------------------------------------------------------------------------------
  4     |   cat1   | {"piece":20,"weight":10}    | {"piece":90,"weight":35}
  5     |   cat2   | {"piece":0,"weight":0}      | {"piece":30,"weight":15}

Use conditional aggregation to get the sums depending on is_tag_stock and jsonb_build_object() (or json_build_object()) to create the encapsulating JSON.
SELECT cat_id,
       cat_name,
       jsonb_build_object('piece',
                          sum(CASE
                                WHEN is_tag_stock THEN
                                  piece
                              ELSE
                                0
                              END)) tagged,
       jsonb_build_object('piece',
                          sum(CASE
                                WHEN NOT is_tag_stock THEN
                                  piece
                                ELSE
                                  0
                             END)) untagged
       FROM elbat
       GROUP BY cat_id,
                cat_name;

db<>fiddle


Replicating MongoDB server on windows I have a main server(windows-27017) and on that i replicated my data on different port (37017) but same server. Now i am trying to add my notebook as a replicate server on mongodb and installed mongodb services on it on port 27017. Now i have three server one main and two replicate. The main server and the port server is working perfectly but the the notebook server is not working. I checked status of the main server and found this

Wed Oct 30 12:25:16.278 trying reconnect to 127.0.0.1:27017
Wed Oct 30 12:25:16.279 reconnect 127.0.0.1:27017 ok
rs1:PRIMARY> rs.status()
{
        "set" : "rs1",
        "date" : ISODate("2013-10-30T03:25:25Z"),
        "myState" : 1,
        "members" : [
                {
                        "_id" : 0,
                        "name" : "192.168.10.99:27017",
                        "health" : 1,
                        "state" : 1,
                        "stateStr" : "PRIMARY",
                        "uptime" : 6832,
                        "optime" : Timestamp(1383103516, 1),
                        "optimeDate" : ISODate("2013-10-30T03:25:16Z"),
                        "self" : true
                },
                {
                        "_id" : 1,
                        "name" : "192.168.10.99:37017",
                        "health" : 1,
                        "state" : 2,
                        "stateStr" : "SECONDARY",
                        "uptime" : 9,
                        "optime" : Timestamp(1383103516, 1),
                        "optimeDate" : ISODate("2013-10-30T03:25:16Z"),
                        "lastHeartbeat" : ISODate("2013-10-30T03:25:24Z"),
                        "lastHeartbeatRecv" : ISODate("2013-10-30T03:25:23Z"),
                        "pingMs" : 0,
                        "lastHeartbeatMessage" : "syncing to: 192.168.10.99:2701
7",
                        "syncingTo" : "192.168.10.99:27017"
                },
                {
                        "_id" : 3,
                        "name" : "192.168.10.97:27017",
                        "health" : 1,
                        "state" : 2,
                        "stateStr" : "SECONDARY",
                        "uptime" : 9,
                        "optime" : Timestamp(1383099655, 1),
                        "optimeDate" : ISODate("2013-10-30T02:20:55Z"),
                        "lastHeartbeat" : ISODate("2013-10-30T03:25:24Z"),
                        "lastHeartbeatRecv" : ISODate("1970-01-01T00:00:00Z"),
                        "pingMs" : 1,
                        "syncingTo" : "192.168.10.99:27017"
                }
        ],
        "ok" : 1
}

I wrote a C program and when i am trying to send the data..it is only being sent to main server and and the port server but no to the notebook server. Can anyone please tell me how i can access notebook server from the main server

Another thing to try is to run rs.status() on the notebook server and compare the state it sees with the state you saw on the PRIMARY node. The differences there may be descriptive.

The thing with Mongo is that it's very stupid by design, it relies on the OS to do everything except run the database itself (it even doesn't bother implementing a caching layer, relying on the OS block cache for that). So when I see connectivity problems like you describe, I start thinking about OS level problems like firewalls.

There are two ways to configure a pair of MongoDB instances on different servers to not talk to each other:

Configure them to listen to localhost.
Configure one to require authentication, but not the other.

If you're not doing either of those (it isn't the first, and the second is unlikely), chances are good the Windows firewalls haven't been set up to allow the nodes to talk to each other. The main and port servers can do so since they're on the same node, but the notebook server would be on the other side of a firewall. Look to that to ensure you can communicate on the ports.


Allowing non-privileged users to cause privileged code to run Context:

Our SQL server is used for teaching, and we need to be able to automatically create databases for the students on demand and in real-time.  We also need the students to be able to delete databases that they created.  The students cannot be given administrator privilege, or any other level of access that would potentially allow them to break the server.

We had a hacky but working solution on our old server, based on an extended stored procedure: there was a "management database" whose job was to track and manipulate the student's databases, and an SQL Server Agent job that was constantly running.  The extended stored procedure allowed the job to go to sleep, and to be woken up, at which point it created or deleted databases as indicated by entries in a table, and subject to various business rules, then went back to sleep again.  (I can expand on this if necessary, but hopefully you get the idea.)

We have decommissioned the old server because it was running SQL Server 2008 R2, and will be building a new one for the upcoming trimester running SQL Server 2019.  From the documentation I've found, extended stored procedures are deprecated but still supported, so in principle I could just reuse the existing solution.  However, I would like to do something saner if possible.

Question:

What I think I need to ask is how I can best allow a non-privileged user to cause a stored procedure, which runs in a sufficiently privileged context to dynamically create and delete databases, to run, or failing that, to wake up from sleep.

However, I would be happy to receive suggestions of other possible solutions to my underlying problem of allowing a non-privileged user to create and delete databases on demand, subject to business rules.  (For example, we require the databases to be prefixed with the student's username.  We also need to configure the database security when it is created so that the student has access to it.  And of course the student should only be able to delete their own databases, not other people's.)

If I remember correctly (and based on some quick research) triggers and the like won't work because they run in the context of the user that triggered them, e.g., see this post.  But perhaps a signed stored procedure would work?  I'm looking for the simplest, sanest options here, thinking forwards to whoever winds up looking after this server after me and looking for solutions that will be understandable and maintainable.

Solutions whose server-end can be implemented entirely in SQL are preferred, but this is not an absolute requirement.

Additional notes, in response to comments:

Students are authenticated via Windows logon, and access is granted based on membership in a Windows group.
On the old server, we did not give the students dbo access to their own databases, because we were not sure of the security implications.  We did give them the db_ddladmin, db_datareader, and db_datawriter roles.

perhaps a signed stored procedure would work? I'm looking for the simplest, sanest options here, thinking forwards to whoever winds up looking after this server after me and looking for solutions that will be understandable and maintainable.

Yes. A signed stored procedure absolutely would work, and would work well. As individual requirements go, "simplest" is not always overlapping with "sanest"; all too often people choose the simplest approach, giving up far too much sanity (which is understandable given that the simplest approach is, by definition, the easiest to understand, and the "saner" approach isn't obviously saner (at least not at first)). This is where Module Signing comes in. While not being the simplest approach, it is by far the sanest, all things considered. Unfortunately, it is also not well understood, and so appears to be more confusing / difficult than it really is.

The general concept is:

Create the stored procedure (in the "management" DB) that creates the student's database:

Have a parameter for the project name (remember: max DB name can be 128 characters, and you need to reserve some number of those for the students logon name as a prefix of the DB name, so depending on how long those get, size this appropriately, maybe 20 - 30 characters max, hence NVARCHAR(20ish))
Create the database with the desired naming convention (including project name). You can get their logon name from a variety of system variables and built-in functions, though ORIGINAL_LOGIN() might be the best option. You might need to do simple string manipulation as the name will be prefixed with the machine/domain name.
USE the new DB
Create a user for that student's account:

DECLARE @User NVARCHAR(258) = ORIGINAL_LOGIN();
SET @User = QUOTENAME(manipulated_@User);
EXEC (N'CREATE USER ' + @User);

Add permissions / roles for student's account:

EXEC (N'ALTER ROLE [db_datareader] ADD MEMBER ' + @User);
...

Create the stored procedure (in the "management" DB) that drops the student's database:

Have a parameter for the project name (remember: max DB name can be 128 characters, and you need to reserve some number of those for the students logon name as a prefix of the DB name, so depending on how long those get, size this appropriately, maybe 20 - 30 characters max, hence NVARCHAR(20ish))
Drop the database having the desired naming convention (including project name). 1. Grant EXECUTE on these modules to whatever user and/or roles need to perform these actions (could be "student" AD group, or [public] as noted in the chat room

Create a certificate (in the "management" DB)
Sign the stored procedures using that certificate (using ADD SIGNATURE)
Copy the certificate to the [master] database (i.e. create a certificate in [master] using the public key of the certificate used to sign the stored procedures).
Create a login from the certificate copied to [master]
Grant whatever instance-level permissions are necessary to that certificate-based login (which can include adding it to instance-level roles).

For some examples, please see:

How to maintain database ownership restoring across domains?
Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level


Kerberos Authentication Issue I'm getting these errors when testing a linked server from SQL Server 2012 to SSAS 2012 (both servers are Windows 2012 and clustered)

"An error was encountered in the transport layer" and "The peer prematurely closed the connection"

I've spent 2 days on this now and has been swung towards Kerberos. I've enabled Kerberos in AD for all of the computer names involved which are host names, SQl cluster names and windows cluster name and have SETSPN for the SQL Server cluster name and SQL Server cluster name FQDN against the service account used.

The same service account is running SQL and SSAS and is a SysAdmin on each in security. The linked server works fine if I RDP onto Server1 and test the linked server to Server2 but it won't work from anywhere else - there are no firewalls in between these servers and windows firewall is disabled on both.

To create the linked server on Server1 (SQL Server) I have followed all links on other msdn forum posts and this is what I used in order to connect to Server2 (SSAS)

EXEC master.dbo.sp_addlinkedserver
@server = N'LinkedServerTest',
@srvproduct='',
@provider=N'MSOLAP',
@datasrc=N'Server2',
@catalog=N'TestCUBE'

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'Server2',
@useself=N'True',
@locallogin=N'Domain\ServiceAccount',
@rmtuser=NULL,
@rmtpassword=NULL

Really hit a brick wall with this now any help is appreciated. Just to add SQL browser is running on both and both servers are using default instances, Server1 is SQL Server etc and Server2 is literally SSAS installed, nothing else.

Just to add to this I've installed the MSOLAP.5 provider as it's part of the SQL Server 2012 SP1 feature pack and it still doesn't change my situation and we use the same service account for Server1 (SQL Server Service) and Server2 (SSAS Service) here

Definitely sounds like a double hop (Kerberos) issue. When you're rdp'd into the box, then the cube is only 1 hop away so I'd guess that's why it works.

Start a profiler trace on the analysis services server, then query the linked server. Check the NTUsername field in the trace. If Kerberos Delegation is failing, the NTUsername will be something like "ANONYMOUS LOGON".

From Server1, what does the following return?

select auth_scheme from sys.dm_exec_connections where session_id=@@spid

What Delegation permissions did you set up in AD for the machines?

Does the spn match the server name you are trying to connect to? If you have alias's, cnames, etc, you'll need an spn for each that you wish to connect to via kerberos.

Also, try out the Kerberos Configuration Manager to verify your settings. This can be particularly effective for reviewing what SPNs actually exist.


Oracle reorganize lob segement On an Oracle 12c database I want to reorganize a LOB segment. I used to following sql to generate the alter move command:

    select 'ALTER TABLE '||s.owner||'.'||l.table_name||' move to('||l.column_name||') store as (TABLESPACE NAME1);'
      from dba_segments s, dba_lobs l
     where s.segment_name = l.segment_name
       and s.tablespace_name = 'NAME1'
       and segment_type='LOBSEGMENT'
       and partition_name is null;

The lob segment I want to rebuild is about 200Gb. Can I use the same tablespace the lob is in? The total free space in that tablespace needs to be 200Gb? During moving the lob is locked?

Can I use the same tablespace the lob is in?

Yes.

  The total free space in that tablespace needs to be 200Gb?

No. Depends on the amount of data in your LOB segment. If your segment is 200 GB, but you have only 5 GB data in it (because the rest of the data was deleted), then you do not need 200 GB.

  During moving the lob is locked?

Not necessarily. Starting with 12.2, alter table has the move online option:

alter table mytable move online lob (lob_column) store as (tablespace users);

The above option is not available in 12.1.


How to do a mysqldump with a use database in the dump So the command I normally use to get a database sent to another server is:
mysqldump -u user -p --add-drop-tables database *file*

But I always have to go into the file and add a line at the top:
use database;

So that I use the file to import to a different server. Is there a flag in mysqldump to automatically add the use database?

The flag is --databases

mysqldump -u user -p --add-drop-tables --databases database > *file*

So if you have more than one schema names you need to set which databases with the --databases flag. Then it inserts which one is necessary to be used.


PostgreSQL window functions very slow on big table I have a PostgreSQL table constructed as

device_id | point     | dt_edit
----------+-----------+----------
100       | geometry  | timestamp
101       | geometry  | timestamp
100       | geometry  | timestamp
102       | geometry  | timestamp
102       | geometry  | timestamp
101       | geometry  | timestamp

I need to select the last 2 records ordered by dt_edit from unique device_id. This query works very slow, on 1 billion records - 500 seconds:

 SELECT dt.device_id,
    dt.point,
    dt.dt_edit
   FROM ( SELECT gps_data.device_id,
            gps_data.point,
            gps_data.dt_edit,
            rank() OVER (PARTITION BY gps_data.device_id
                         ORDER BY gps_data.dt_edit DESC) AS rank
           FROM gps_data) dt
  WHERE dt.rank <= 2

Any ideas?

If we can assume a device table holding all devices of interest. Example:

CREATE TABLE device (device_id int, device text);
INSERT INTO device (device_id, device) VALUES
  (100, 'a')
, (101, 'b')
, (102, 'c');

The query can be very simple:

SELECT d.device_id, g.point, g.dt_edit
FROM   device d
,      LATERAL (
   SELECT point, dt_edit
   FROM   gps_data
   WHERE  device_id = d.device_id
   ORDER  BY dt_edit DESC NULLS LAST -- more items?
   LIMIT  2
   ) g;

Assuming a much smaller cardinality in table device than in table gps_data, i.e. many rows per device in the latter.

If dt_edit is not UNIQUE, add more items to ORDER BY to disambiguate the sort order.

All you need for this to be fast is a multicolumn index;

CREATE INDEX gps_data_foo_idx
ON gps_data (device_id, dt_edit DESC NULLS LAST); -- more items?

The NULLS LAST modifier is useful if the column can be NULL, but does not hurt in any case. Either way, the query must match the index.

PostgreSQL sort by datetime asc, null first?

If you don't have a device table, I would suggest to create one (and keep it current).
But there are ways to make this fast even without device table ...

Details:

Optimize GROUP BY query to retrieve latest record per user
How do I efficiently get "the most recent corresponding row"?
SELECT DISTINCT on multiple columns


Can't connect through sqldeveloper because locale not recognized even though my language/regional settings are English/US On a certain machine I consistently cannot connect to an oracle db due to the locale not being recognized:

Other people have noted the locale or language settings may not be compatible with the oracle db driver but my laptop is US/English and I can connect to this same db w/o a problem from similar laptops. Moreover, I tried uninstalling sqldeveloper and wiping out all sqldeveloper folders under /AppData/Roaming/ so that I could not reimport settings during the reinstall but to no avail. Oracle db is 18c but this happens with 12c as well.  Sqldevelopers can be any version (ie 3.x, 4.x, 18.x, 19.x...yes, I've tried them all :( )

Try adding the following to your sqldeveloper.conf:

AddVMOption -Duser.language=en
AddVMOption -Duser.region=US


Efficient query plan for selecting all data duplicated on several columns The flavour in question is PostgreSQL, but it is a generic enough query pattern that your techniques for optimising against other RDBMs should help as well.

The purpose of the query is to show all records from a table that are duplicated along several columns (user selectable).

  SELECT usr_id,
         team_name, team_streetno, team_city,
         usr_firstname, usr_lastname, usr_jobtitle, usr_department, usr_email
    FROM team c
    JOIN usr p on p.team_id=c.team_id
    JOIN
    (
        select match_firstname, match_lastname, match_jobtitle, match_department, match_email ,
               match_team_name, match_team_streetno, match_team_city
          FROM usr
          JOIN team on usr.team_id=team.team_id
      GROUP BY match_firstname, match_lastname, match_jobtitle, match_department, match_email,
               match_team_name, match_team_streetno, match_team_city
        HAVING count(usr_id)>1
    ) AS sub
      ON sub.match_team_name = c.match_team_name
     AND sub.match_team_streetno = c.match_team_streetno
     AND sub.match_team_city = c.match_team_city
     AND sub.match_firstname = p.match_firstname
     AND sub.match_lastname = p.match_lastname
     AND sub.match_jobtitle = p.match_jobtitle
     AND sub.match_department = p.match_department
     AND sub.match_email = p.match_email
ORDER BY usr_firstname, usr_lastname, usr_jobtitle, usr_department, usr_email,
         team_name, team_streetno, team_city

The EXPLAIN output looks like this:

I tried this variation to make arrays out of the duplicated usr_id's and JOIN back to the usr table using = ANY; that increased the duration by about 15%.

 SELECT u.usr_id
     , t.team_name, t.team_streetno, t.team_city
     , u.usr_firstname, u.usr_lastname, u.usr_jobtitle, u.usr_department, u.usr_email
  FROM (
      SELECT array_agg(u1.usr_id) dups
        FROM usr u1
        JOIN team t1 ON u1.team_id = t1.team_id
    GROUP BY match_firstname, match_lastname, match_jobtitle, match_department, match_email
           , match_team_name, match_team_streetno, match_team_city
      HAVING count(usr_id)>1
       ) AS dup
  JOIN usr u on u.usr_id = ANY (dup.dups)
  JOIN team t on t.team_id = u.team_id
ORDER BY u.usr_firstname, u.usr_lastname, u.usr_jobtitle, u.usr_department, u.usr_email
       , t.team_name, t.team_streetno, t.team_city

Look forward to your suggestions.

Consolidating comments

Tried WITH TMP AS (... ,count(*) over (partition by...) c FROM ..) WHERE c>1, which fared worse than the arrag_agg/ANY approach. Seems like windowing functions in PostgreSQL is not that great, or it needs to be rewritten in another form.

For context, consider census data. If someone else filled it for you, he may fill in Tom one year, Thomas or Tomas the next. The "match" columns are derived from the base columns.

EXISTS has been tried, but didn't perform as well as the code shown above.

Updated after OP supplied test case voiding much of this answer.
Postgres version
There is a good chance Postgres 9.2 will perform better. A lot of improvements for big data have gone into it.
Simplified query
First off, you can simplify your query quite a bit:
SELECT u.usr_id
      ,t.team_name, t.team_streetno, t.team_city
      ,u.usr_firstname, u.usr_lastname, u.usr_jobtitle, u.usr_department, u.usr_email
FROM   usr  u
JOIN   team t USING (team_id)
JOIN (
   SELECT u.match_firstname, u.match_lastname, u.match_jobtitle, u.match_department
         ,u.match_email, t.match_team_name, t.match_team_streetno, t.match_team_city
   FROM   usr  u
   JOIN   team t USING (team_id)
   GROUP  BY u.match_firstname, u.match_lastname, u.match_jobtitle, u.match_department
            ,u.match_email, t.match_team_name, t.match_team_streetno, t.match_team_city
   HAVING count(usr_id) > 1
   ) AS sub USING (match_firstname, match_lastname, match_jobtitle, match_department
                  ,match_email, match_team_name, match_team_streetno, match_team_city)
ORDER  BY u.usr_firstname, u.usr_lastname, u.usr_jobtitle, u.usr_department, u.usr_email
         ,t.team_name, t.team_streetno, t.team_city;

I removed the JOIN to the table team from the subquery, guessing that all the columns stem from table usr (my request for table definitions went unanswered ...) That should help a bit.
Turns out, some of the columns belong to table team.

I replace the verbose equijoin conditions with the much shorter USING syntax. That's also probably slightly faster.

Replace count(usr_id) > 1 with count(*) > 1. Does the same here (since usr_id is the PK, ergo NOT NULL), slightly faster.

I took the test case @RichardTheKiwi posted in the comments and created an sqlfiddle from it.
I also ran a local test with temporary tables on PostgreSQL 9.1.6.
Only slightly faster than the query in the question:
1338 ms for 15137 rows (out of 70k).
EXISTS semi-joins might be a hot contender if you have many duplicates. With only a few, not so much. You already tried and ruled that out. Are you positive you tried optimal syntax?
Window function count()
You could also try if count() as window function is faster - without CTE and without additional joins. But I doubt it. I sure does make the query shorter, though:
SELECT *
FROM  (
   SELECT u.usr_id
         ,t.team_name, t.team_streetno, t.team_city
         ,u.usr_firstname, u.usr_lastname, u.usr_jobtitle, u.usr_department, u.usr_email
         ,count(*) OVER (PARTITION BY
            u.match_firstname, u.match_lastname, u.match_jobtitle, u.match_department
           ,u.match_email, t.match_team_name, t.match_team_streetno, t.match_team_city) AS ct
   FROM  usr  u
   JOIN  team t USING (team_id)
   ) x
WHERE  ct > 1
ORDER  BY usr_firstname, usr_lastname, usr_jobtitle, usr_department
         ,usr_email, team_name, team_streetno, team_city;

50% slower in my local test.
Multi-column index?
This is largely obsolete after I have seen the test case.
Indexes are obviously hard to use here, since the columns to check for dupes vary. You might be able to profile user behavior and identify the most common combinations to support those with an index. There is a more radical possibility here, though:
Postgres can use multi-column indexes, even if not all index columns are used. Performance is much better if one or more leading columns of the index are used in the condition, but it still works if not. Compare:
Working of indexes in PostgreSQL
If we can assume that most use cases will check on, for instance,  (match_firstname, match_lastname), a multi-column index like the following might help. Not sure, you'll have to test:
CREATE INDEX usr_match_multi_idx
ON usr (match_firstname, match_lastname, match_jobtitle, match_department, match_email);

Columns of the index in order of decreasing usefulness. Order of columns in your GROUP BY / PARTITION BY clause is not relevant.
Index did not help in my local test - was used, but made the query ~ 20 % slower.
Partial indexes?
If the table is read-only or at least rarely written
and many of your columns often have unique values, you could take this a step further. Rows with a unique value in one of the used match_* columns can be excluded right away. For columns with many unique values it would pay to either add a column match_email_mult boolean which you set TRUE if match_email is not unique for this row. Or, if you have many columns, you could use a bit(n) column or use an integer columns for an even small footprint on disk. More under this related question on SO.
Don't bother with columns with only few unique values.
CREATE INDEX usr_match_email_mult_idx ON usr (usr_id)
WHERE match_email_mult;

Repeat for all qualifying columns. Then add conditions to your query:
WHERE match_firstname_mult
AND   match_lastname_mult
AND   ...

Postgres can combine a number of bitmap index scans rather quickly. If that can weed out a large portion of the table to begin with, the query planner might switch from a sequential scan to bitmap-index scans ...
Not tested.


Neo4J community edition DB size limit? The Neo4J site states that the community version of Neo4J has a limit in the graph size, but does not state the limit of the community version.

https://neo4j.com/editions/

What is the size limit of the community version?

I contacted uk@neo4j.com to ask for clarification and the answer they gave is that:

  There is a limit on node count and relationship count of 34Bn each

and

  The 34bn limit doesn't apply to Neo4j Enterprise

The release announcement for 3.0 mentions a 34bn limit that no longer applies:

  Dynamic pointer compression expands Neo4j’s available address space as needed, making it possible to store graphs of any size. That’s right: no more 34 billion node limits!

So my guess is that 'Dynamic pointer compression' is an Enterprise Edition feature.


Search multiple values in multiple columns I have a table results with this fields:

id
results_id
number_one (int)
number_two (int)
number_three (int)
number_four (int)
number_five (int)

Each number_* column can contain a number from 01 to 90.
I have a search form with 90 checkboxes. A user can tick up to 5 checkboxes.
Once a user select n checkbox, I need to build a query that look for a row that contains all those values, but a number can be in any of the five columns.
Example:
a user ticks checkbox 02, 12 and 20. I need to retrieve all the rows with all three number but I a number can be in any of the number_* column. The 20 can be in column number_one, 02 could be in column number_three and 12 in column number_four.
How can I build this query?
Also, would it be better to use one column instead of five columns?
results table:

id
result_id
numbers (string)

Column numbers contains 02-12-20-57-84 where - is the separator.
Example:
a user ticks checkbox 02, 12 and 20. so my query would be
SELECT *
FROM results
WHERE numbers LIKE "%02%"
    AND numbers LIKE "%12%"
    AND numbers LIKE "%20%";

UPDATE: I am using MySQL at the moment with this setup:

id
results_id
number_one (int)
number_two (int)
number_three (int)
number_four (int)
number_five (int)
date
city (foreign key)

I am planning to move look for optimisation and faster alternatives. I can migrate to any other database just for this specific table. I can also change table structure if needed.
There are around 150k records in that table doing a query similar to:
SELECT *
FROM TableName
WHERE '02' IN (Number_1, Number_2, Number_3, Number_4, Number_5)
  AND '12' IN (Number_1, Number_2, Number_3, Number_4, Number_5)
  AND '20' IN (Number_1, Number_2, Number_3, Number_4, Number_5)

Will a migration to another DBMS type bring benefits?

Personally I would create your table

id
result_id
value

But with a single value in the value column.  Now you can index on the value column and that should dramatically speed things up.  Your query would then look like this:

SELECT result_id
FROM TableName
WHERE VALUE IN ('02','12','20')
GROUP BY result_id
HAVING COUNT(1) >= 3

One of the benefits of this is that you can now easily check for 1 match, 2 matches etc.  You can also increase the number of items they could check to 50 and your table structure wouldn't have to change.

That being said right now that won't work for you.  Right now I would use something like this:

SELECT *
FROM TableName
WHERE '02' IN (Number_1, Number_2, Number_3, Number_4, Number_5)
  AND '12' IN (Number_1, Number_2, Number_3, Number_4, Number_5)
  AND '20' IN (Number_1, Number_2, Number_3, Number_4, Number_5)


High Level Permissions For an Application Owner I'm the primary DBA for a 100-user data warehouse with approximately a dozen developers and analysts regularly contributing additions to the data model and codebase (mostly stored procedure and fact table additions). I'm following a relatively traditional deployment process focusing on regularly-scheduled code reviews and deployment windows with change control tickets twice a week with me an a colleague DBA handling the deployments. I've used SQL Audit, default trace data and RedGate DLM Dashboard to keep tabs on all the schema changes.

The tempo has increased steadily in the last two years and some of the lead analysts would like to go towards a DevOps deployment method using automation. The director and app owner is the most accomplished developer creating assemblies for the advanced ETL,  looking to perform deployments independent of me and my colleague, asking for sysadmin privs when we're backlogged or short-handed during vacation.

I've declined requests to share sysadmin privileges with the director because of the risk of setting a precedent for other developers, the liability if access is opened too broadly in error and the risk of a deployment error which will get me paged at 3am when I'm off-call. On the flip side, I'm aware of the business pressure on the developers to get new content to market faster, even if it's not properly tuned. Finally, the political and departmental element of reporting to the director is present; it's easier to say 'No' if you're a DBA in a separate unit, but harder if the director signs your paycheck.

I've told my director in the past that in the event of a breach or system failure, all the sysadmins need to be at the IT Security board of inquiry to explain what happened, which has given him pause in the past, but the business risk of delaying content is getting stronger. What options can I offer to the director that go short of sysadmin rights that give him more latitude to deploy but have the proper level of auditing in depth? I'm familiar with the various server and database fixed roles, but I'm looking at departmental protocol options as well.

A short answer

I had similar issues when I was a BI DBA, trying to control deployments without blocking the business needs.
And generally prevent yet another badly coded csv-parsing function to add to the current 13 or so already pushed to live.

You don't need sysadmin to deploy
Depending on exact need, you need one of ddl_admin/ALTER SCHEMA/db_owner

This way the scope of a screw up is limited to the database and/or schema.

ALTER SCHEMA is very useful for limiting changes and I've done this before.

Do these folk needs to modify security for example? Most likely not.

I'd also implement some kind of DDL auditing (I have a server level DDL trigger) that writes to a DBA-only database. This way all DDL is logged


Storing prices in SQLite, what data-type to use? I am using SQLite and need to store prices. SQLite's REAL data-type says it uses floating-point which is unacceptable storage for prices. Is there a data-type besides TEXT that I can use to store prices numerically so they sort correctly?

Use an integer & store the prices as the lowest common unit. So, for dollars and cents you'd store it in cents. EG: $1.00 would be stored as 100.

In my experience it's standard practice.


MySQL Database filesystem standard Our client is asking some document regarding MySQL database filesystem best practices, could you please share information about that.

MongoDB recommends XFS in official docs. But MariaDB (and MySQL have no file system best practises/parameter tuning).

Quote from What is the best Linux filesystem for MariaDB?

  Trying to figure out which filesystem gives the best performance may
  be fun, but the filesystem won't make a large difference in the
  performance of your MariaDB server. Your hardware is the most crucial
  factor in eking out the most speed. Fast hard drives, discrete drive
  controllers, lots of fast RAM, a multi-core processor, and a fast
  network have a larger impact on performance than the filesystem. You
  can also tailor your MariaDB configuration options for best
  performance for your workloads.

Don't forget to mount fs with noatime.


SQL Server Linked Server Best Practices - Step by Step Hello and thanks for stopping by.  I'm an accidental DBA looking for some guidance in creating Linked Servers the correct way.  MS provides complete descriptions of the datatype and descriptions of all of the various parameters for sp_addlinkedserver, sp_addlinkedsrvlogin and sp_serveroption but no guidance on HOW to align the various options as Best Practices for a given situation.

I have examples from the other DBA's who simply used the 'sa' password but my research indicates I should be using bespoke logins tailored to their Linked Server use.  The Problem is I'm so far unable to find the right combination and sequence (order of ops) to correctly create all of the parts and pieces resulting in a Linked Server that allows limited communication between two servers.

Goal: Create a Linked Server between a Source server that will allow a job step from the source server to check certain conditions and if TRUE, invoke sp_start_job on Destination server. ...and nothing more.

On advice, I've created two SQL Auth Logins of the same name/pw on both Source and Destination, both with limited 'public' permissions.

I've created Linked Servers attempting to map the local login to the remote login (thinking if I got that far, I'd carefully tinker with the permissions of the Destination login to find the permission to allow it to exec sp_start_job).

But so far, my only reward has been a series of failure notices of various types.

There are a TON of online documents explaining what each various proc/param does but I'm having a difficult time finding some sort of over-view explaining how combinations of procs/params lead to different desired outcomes.

I'm hoping for some useful advice, reference to some 'yet to be discovered' tutorial or maybe even a Step by Step instruction on how to achieve my goal and develop a little self respect.  (so far, this task has done nothing but bruise my ego!)

Thank you for your time.

I would start by using Windows Auth and @useself='True', which will use the source server's SQL Server Agent Service Account to make the remote connection.  If you get a login failure, look in the target server's SQL Server Error Log to determine what Windows principal to create a login for on the target server.  So the linked server definitaion would be something like:

USE [master]
GO

EXEC sp_addlinkedserver @server = N'TargetServer', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'targetserver.mydomain.com'

EXEC sp_addlinkedsrvlogin @rmtsrvname=N'TargetServer',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO

EXEC sp_serveroption @server=N'TargetServer', @optname=N'collation compatible', @optvalue=N'true'
GO

EXEC sp_serveroption @server=N'TargetServer', @optname=N'data access', @optvalue=N'true'
GO

EXEC sp_serveroption @server=N'TargetServer', @optname=N'rpc out', @optvalue=N'true'
GO

EXEC sp_serveroption @server=N'TargetServer', @optname=N'remote proc transaction promotion', @optvalue=N'false'
GO

Impersonating the caller is more complicated with remote connections to the SQL Server where the Linked Server is defined, as you have to properly configure Kerberos Constrained Delegation.  But when used from a SQL Agent Job, it's not a "double hop" and so is a really simple configuration.


How to convert timestamp to string in Pentaho Kettle? I have a timestamp field in my input table like this: yyyy-mm-dd hh24:mm:dd,  I want  output sting like this: yyyymmdd.

Just use the select values step. Convert the data type to a string and specify the mask:

yyyyMMdd

Note the mask is a java simpleDateFormat and you can google that for more details. It is case SENSITIVE.

The mask in PDI is only used for 3 things:

Converting from the real data type to a string
Display purposes when previewing data
Any other time you convert to "text" i.e. CSV output, or bulk loaders.

If you are sending it to a target database as a timestamp then it will be whatever that target database uses as default format.


How to update MySQL with CentOS 6 in most unintrusive way - in regard to perl and PHP packages? For my few hobby projects (web games + forums) I have been using CentOS 5 (then 6) with Drupal and PostgreSQL plus few custom PHP and Perl scripts written by mysef.

Since PostgreSQL version delivered with CentOS package has been a bit dated, I always used the PGDG packages:

# rpm -qa | grep -i pgdg

pgdg-centos93-9.3-1.noarch
postgresql93-9.3.5-1PGDG.rhel6.i686
postgresql93-libs-9.3.5-1PGDG.rhel6.i686
postgresql93-server-9.3.5-1PGDG.rhel6.i686

As a web developer this has been a very pleasant experience, since (by some great magic) all the other CentOS packages (like php-pgsql and perl-DBD-Pg) just worked with the PGDG packages.

Now I have decided to switch to WordPress for my new projects and am forced to switch the database too:

I have to use MySQL or MariaDB with CentOS 6.5.

So my question is: if anybody can recommend a similarly comfortable package repository for MySQL/MariaDB - which wouldn't mess up any other CentOS packages and which would update itself (with yum update)?

And please do not suggest something like Fedora or EPEL repositories, because other than for MySQL/MariaDB I would like not to add any additional packages to have my server as "stable" as possible.

For Official MySQL packages: Oracle yum repository for MySQL.
For MariaDB Packages: MariaDB yum repository.

After that, you will be able to update MySQL via yum up to the latest stable version. They only include the server, client and the minimal MySQL libraries.

For details, I've recently written a step-by-step guide on How to install MySQL 5.6 on CentoOS 7 (most of it applies to CentOS 6, except for the initial rpm version and the start commands). It is a very common task because Red Hat/CentOS has very ancient versions of MySQL (and PostgreSQL).


Whether Logfile Shrinking is good or bad one for Performance in SQL? I am using SQL Server 2008 R2 database and on our SQL Server instance we have more than 70 databases. In a few databases I can see that the transaction log file size is more than 10 GB. I am shrinking the Tlog file size on a daily basis using the following script:

USE ABC;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE ABC
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (ABC_log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE ABC
SET RECOVERY FULL;
GO

Is there any recommended way to shrink the transaction log file size?

Shrinking of log file ON DAILY BASIS like one you do with query you posted is seriously bad and you should NOT do that. The main reason is that after you have shrinked the file it will grow again when new transaction comes and since INSTANT FILE INITIALIZATION is not there for Log files, its only for data files, when log file will grow DB engine will actually have to zero out space to write information so instead of overwriting free space it has to be overwritten with zero values (0×0) and then information can be written. This process takes time and thus delays processing of query

Nice explanation has been done in This link

Please note that instant file initialization is applied to some extent for Tempdb log files even though this is correct you should not shrink tempdb log files on daly basis.

A more simple thought, why to shrink if space has to eventually grow when log informations will be written and log will grow. Its a bad practice, internet is filled with advise not to shrink data files but shrinking log file is equally bad

EDIT: Needless to say( as already pointed out) you are breaking chain of logs by changing recovery model to simple. You loos epoint in time recovery. If point in time recovery is not required kep recovery model of database as simple and log truncations would be handled by DB engine if something is not holding logs and need it in transaction.


SQL Server TempDB Growth issue We have an OLTP database (running SQL Server 2008 R2) that has 6 tempdb files.

All of them have an Autogrowth by 10%, restricted growth to 10000mb. At one point, someone changed one of the growths to restrict by 120000mb which caused that tempdb file to grow exponentially larger than the others. We are trying to track down when this change could have occurred.

Is it possible to see, either, when a setting was changed or a history of tempdb growth?

One issue is that the issue has been fixed and the tempdb file is back down to normal size.

If you have the default trace configured, and if the change was recent enough, you might be able to find the change.  The default trace only maintains a certain amount of trace, but I have found unexpected changes by examining its contents.

See: http://msdn.microsoft.com/en-us/library/ms175513(v=sql.105).aspx

This describes how to read the default trace so that you can research problems.

There is no backward looking tool to find database growth and other changes.

If you want to have a persisted history of changes you will need to periodically query the needed data and store it in a database table.  With SQL Server 2012 Change Tracking can also answer questions like this.


View designer strange join syntax I have a data warehouse with a lot of views built in with the SSMS view designer.
Viewing the syntax, the FROM clauses are unintelligible to me because the ON statements do not follow straight after the relevant JOIN <table>. Often the principal table of the query is RIGHT joined and a random "type" table is specified in the FROM. So I have a situation like:

SELECT *
FROM tableC
LEFT JOIN TableB
RIGHT JOIN TableA
ON TableA.ID = TableB.ID
ON TableB.TypeID = TableC.TypeID
WHERE ....

Changing the order of the various JOIN and ON clauses to what I would have hand written changes the results in unexpected ways. And it would seem that most SQL prettyfiers choke on this ugly syntax but SSMS doesn't. I haven't been able to find rhyme, reason or documentation to the help unravel what is going on.

Any suggestions or links for figuring out and fixing view designer queries please?

This is just Standard SQL join syntax with the optional parentheses removed:

SELECT *
FROM tableC
LEFT JOIN
(
    TableB
    RIGHT JOIN TableA
        ON TableA.ID = TableB.ID
)
ON TableB.TypeID = TableC.TypeID

If you don't like the syntax generated by the SSMS view designer (which is buggy and rarely updated anyway), simply write the views by hand using regular T-SQL.

Some people refer to this syntax style as 'nested join syntax'. Example blog post.


Relation candidate keys I have the following relation:

R = ABCDE
F: AB->DE, AC->B, DE->C, AE->C, C->AB

I need to determine this relations candidate keys.

Now, if I understood the defintion correctly:

  In the relational model of databases, a candidate key of a relation is
  a minimal superkey for that relation; that is, a set of attributes
  such that the relation does not have two distinct tuples

The candidate keys would be:
C, AB, AE, DE.

Is this correct or are there more?

A superkey (and therefore a candidate key) that does not contain all attributes must contain at least an attribute set that is the left side of a functional dependency. Otherwise it would not be possible to generate an additional attribute from the attribute set of this superkey and this contradicts the definition of a superkey.

But a subset of ABCDE containing at least one of C,AB,AE or DE is already a superkey and to be minimal it is necessary that it contains only one of these subsets.

So the only subsets that must me investigated are that that are supersets of AC. But a superset of AC cannot be a minimal superkey because C already is a key.

Therefore the only candidate keys are C,AB,AE and DE


mongodb - Replica set creation error: Quorum check failed because not enough voting nodes responded I am configuring a mongodb replica set. From my current primary node, when rs.add('host1:27017'), it yields this error

Quorum check failed because not enough voting nodes responded; required 2 but only the following 1 voting nodes responded; the following nodes did not respond affirmatively; failed with Server min and max wire version are incompatible (7,7) with client min wire version (6,6)

On my host1 and host2 machines, I already added replication option with the same replSetName, bind_ip, and exposed firewall. As a proof, through command line mongo --host host1 I can still connect to host1's mongo instance. The telnet command also yields the same successful connection. I don't know how to fix this.

The answer is that all 3 machines must have the same version 4.0


Table with only one column because of many-to-many relationship? Right now I have three tables: person, alias, and person_alias. As suggested by the names of tables, one person can have multiple aliases and one alias can be used by many people, and hence I add a joining table person_alias during the process normalization.

However, the table alias essentially only contains one column (if I do not choose to use numeric surrogate key). In addition, let's say the table person also contains a bunch of other useful information that is irrelevant to the question here.

I am not a bit stuck at justifying setting up the entity relationship map this way. What is the problem that I delete the table alias all together? Or do I misunderstand many-to-many relationship/normalization?

The alias table will save space because you do not repeat duplicated aliases in the person_alias table.

If in most of the cases aliases are unique, then this won't matter much, but if you have a lot of duplicated aliases (say every second person uses 'Zaphod Beeblebrox' as a alias) then this could save a lot of space.

However, if you allow a single person to rename their alias without affecting all other (duplicated) aliases, then removing the alias table completely could makes sense.

You could de-normalize further and store all aliases for a person in an array column in the person table removing the person_alias table as well. But in that case it will be quite complicated to define a unique index that ensures that every alias is unique for that person. If you (almost) always retrieve all aliases for a person together with that person that might be slightly more efficient.

But if you want to ensure uniqueness of the aliases per person in the database, stick with the person_alias table.


How to write sql statement for following scenario? So I currently have an sql statement:

Select room_no, category from help_desk where room_no = '133' OR room_no = '120';

and it returns:

How would I write an sql statement that would return it like this

You need to use three things

GROUP BY
GROUP_CONCAT
DISTINCT within GROUP_CONCAT

Here is such a query

Select room_no, group_concat(distinct category) categories
from help_desk where room_no in (133,120) group by room_no;


You can use CONCAT function to add this two strings on this way:

create table video(link varchar(200), title varchar(200));

insert into video values ('y_LjhCEhWiKXk', 'Bruno Mars - Just The Way You Are [OFFICIAL VIDEO]');

select title,
       if(substr(link, 1, 2)='y_',
          concat('https://www.youtube.com/watch?v=', substr(link,3)),
          concat('https://vimeo.com/', substr(link,3))) as link
from   video;

title                                              | link
:------------------------------------------------- | :------------------------------------------
Bruno Mars - Just The Way You Are [OFFICIAL VIDEO] | https://www.youtube.com/watch?v=LjhCEhWiKXk

dbfiddle here


Joining a table on an id but need to copy this data to other ids I have an issue with a postgres database that I am not sure how to handle.
These are the tables (simplified obviously)

Users

+--+---------+
|id|user info|
+--+---------+
|10|{data}   |
|11|{data}   |
+--+---------+

Devices

+--+------+----------+
|id|userid|other info|
+--+------+----------+
|1 |10    |{data}    |
|2 |10    |{data}    |
|3 |11    |{data}    |
+--+------+----------+

Device_Preference

+--+------+------------+
|id|userid|preferenceid|
+--+------+------------+
|1 |10    |8           |
|2 |10    |9           |
|3 |11    |8           |
+--+------+------------+

Preferences

+--+---------------+
|id|preference info|
+--+---------------+
|8 |{data}         |
|9 |{data}         |
+--+---------------+

Preferences are linked to user, not device so I am looking for a query that gives me the preferences for every device, like this:

+--------+------+----------+
|deviceid|userid|prefenceid|
+--------+------+----------+
|1       |10    |8         |
|1       |10    |9         |
|2       |10    |8         |
|2       |10    |9         |
|3       |11    |8         |
+--------+------+----------+

I think a 'select Devices d1 join Devices d2 on d1.userid=d2.userid' is needed but I am not sure exactly how to proceed.

SELECT d.id deviceid, u.id userid, dp.preferenceid
FROM Users u, Devices d, Device_Preference dp
WHERE u.id = dp.userid
  AND u.id = d.userid
ORDER BY 1,3

fiddle


how to create ambari database backup without enter password We configure the file .pgpass as follows in order to create database backups without entering a password:

-bash-4.2$ more .pgpass
localhost:5432:postgres:ambari:bigdata
-bash-4.2$ pwd
/var/lib/pgsql
-bash-4.2$ whoami
postgres
-bash-4.2$ more .pgpass
localhost:5432:postgres:ambari:bigdata
-bash-4.2$

But after that still pg_dump insists to enter the password - why?

Where am I wrong?

pg_dump -U  ambari  -f ambari.sql
Password:

You are trying to connect to a database called ambari this way (when specifying only the -U parameter, the DB name is taken from its value).

From the .pgpass file it looks you want to connect to the postgres DB.  Specify -d postgres, too, or set

localhost:5432:*:ambari:bigdata

in your .pgpass file.  (As it turns out, there is an ambari DB, so the connection parameters look all right.)

Note that by default this file has to be in the OS user's (in this case postgres) home directory.  Alternatively, you can specify its location in other ways.


Does MariaDB require aliases for subquery table locks? MySQL suffers from a bug (which they claim is not a bug) that prevents a table lock from working on a sub-query unless you create a table alias for each mention of the table name.

See: https://bugs.mysql.com/bug.php?id=31080

They say:

  "You cannot use a locked table multiple times in a single query. Use
  aliases instead, in which case you must obtain a lock for each alias
  separately"

I looked at the MariaDB lock tables manual page on their site, but it is unclear if this limitation has been fixed in Maria, or is still present.

Since my application builds queries dynamically, trying to work around this MySQL bug would create very messy code and a lot of unnecessary work to create pointless table aliases. If I can just swap the whole MySQL DB for Maria without compatibility issues, that would save a ton of time.

Really peculiar behaviour, unfortunate this limitation still exists atleast in my version of MariaDB:

MariaDB [test]> select @@version;
+-----------------+
| @@version       |
+-----------------+
| 10.0.23-MariaDB |
+-----------------+
1 row in set (0.00 sec)

MariaDB [test]> create table t ( x int not null );
Query OK, 0 rows affected (0.05 sec)

MariaDB [test]> lock tables t read;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select * from t t1, t t2;
ERROR 1100 (HY000): Table 't1' was not locked with LOCK TABLES

Locking the aliases does not seem to help

MariaDB [test]> lock tables t read, t1 read, t2 read;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select * from t t1, t t2;
ERROR 1100 (HY000): Table 't1' was not locked with LOCK TABLES

Weird indeed.


setting up a local database to be updated from a master database I'm looking for an efficient way to keep multiple databases (on different servers) sync with a master database (also on a different server). Mind you I only need a one way sync (from master to all others).

As I was searching I came across multiple solutions.

replication : Which (from what I read) is unreliable, because if the slave servers or down temporarily or anything like that, they end up missing information. (Please correct me if I'm wrong)
Injection   : Which I also think is unreliable, not to mention a security problem because you're directly communicating with the slave databases.

Since I didn't like what I saw, I decided to make my own system (ultimately not liking it either) My system was to make a new table called ORDERS , and would basically hold values that could be translated as "Delete item with id 32 in table someTable" and on the slave side they would follow each of these instructions.

What do you think is the best way? The database is really small (with at most 3000 entries), so I want a really simple solution. Thanks

Personally, I would go with MySQL Replication. I would go about using it as follows:

First, I would make should every table is InnoDB in all databases (except mysql and information_schema).

Here is a two commands to create a script to convert all MyISAM tables into InnoDB (with the exception of MyISAM tables that have FULLTEXT indexes), and run that script:

mysql -h... -u... -p... -A --skip-column-names -e"SELECT CONCAT('ALTER TABLE ',db,'.',tb,' ENGINE=InnoDB;') FROM (SELECT A.db,A.tb,A.tbsize FROM (SELECT table_schema db,table_name tb,(data_length+index_length) tbsize FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) A LEFT JOIN (SELECT table_schema db,table_name tb FROM information_schema.statistics WHERE index_type='FULLTEXT') B USING (db,tb) WHERE B.db IS NULL) AA ORDER BY tbsize" > /root/ConvertMyISAM2InnoDB.sql
mysql -h... -u... -p... -A < /root/ConvertMyISAM2InnoDB.sql

Second, I would use periodic mysqldumps. Since the database is very small, you could run the following script every 24 hours:

echo "STOP SLAVE;" > /root/MySQLData.sql
mysqldump -hmasterhostaddr -u... -p... --single-transaction --master-data=1 --routines --triggers --all-databases >> /root/MySQLData.sql
echo "START SLAVE;" >> /root/MySQLData.sql
mysql -hslavehostaddr -u... -p...  -A < /root/MySQLData.sql

You can also run the script on demand to repair any slave.

You can also run this script against a live master since all user tables are InnoDB and you perform the mysqldump as a single transaction. Thus, a slave is loaded with a point-in-time snapshot.


Microsoft Access Security So I get how to connect Access to SQL Server, and I understand that the tables have to be linked, but my problem is security. If I link the tables from SQL Server into Access, then anyone who comes into the Access forms to enter data can ultimately get at the tables via the link can't they? I know that newer Access versions have no user security, but is there any way to restrict what data they see in some of the tables, or do I need to do that on the back end in SQL Server Express?

The easiest way to do that is on the back end.  Define a user that has only the access you want and then update the ODBC DSN that Access uses to that user account.


Postgresql upgrade generated vaccumdb script not vacuuming? When performing an upgrade, postgresql generates a script with the following command in it:

"/usr/lib/postgresql/11/bin/vacuumdb" --all --analyze-in-stages

On the page: https://www.postgresql.org/docs/11/app-vacuumdb.html

The following options are listed:

  -a
  --all

  Vacuum all databases.

  --analyze-in-stages

  Only calculate statistics for use by the optimizer (no vacuum), like --analyze-only. Run several (currently three) stages of analyze with different configuration settings, to produce usable statistics faster.

  This option is useful to analyze a database that was newly populated from a restored dump or by pg_upgrade. This option will try to create some statistics as fast as possible, to make the database usable, and then produce full statistics in the subsequent stages.

The page also states:

  vacuumdb is a wrapper around the SQL command VACUUM. There is no
  effective difference between vacuuming and analyzing databases via
  this utility and via other methods for accessing the server.

However, after running this script and then going into a database and running:

VACUUM VERBOSE;

The output of that is showing me many unused item pointers, index row versions being removed and per index how many rows it removed.

So it looks to me like that command from the generated script didn't vacuum the tables at all.

Is this right? Or is something else going on?

EDIT:

I just now ran:

"/usr/lib/postgresql/11/bin/vacuumdb" --all --echo --verbose

And it's showing me the same output as VACUUM VERBOSE;, which didn't get printed while running the command generated by the upgrade, even with echo and verbose on.

So it seems to me like it's only executing --analyze-in-stages. Is this a bug that should be reported or am I seeing things wrong here?

EDIT: To clarify, --all clearly is documented to mean Vacuum all databases. And when I run it alone, it does in fact do that. But the second I add an extra option like --analyze-in-stages it skips vacuuming entirely.

I would expect it to perform all provided flags and not ignore --all(which is a vacuum command and it does in fact perform a vacuum if it is provided to vacuumdb without --analyze-in-stages.

That's not a VACUUM script, it is an ANALYZE script. That's why it isn't vacuuming. The confusion arises because vacuumdb can also be used for ANALYZEing a database.

After pg_upgrade you have no table statistics at all, so most queries will perform very badly. That's why it is essential to mend that as soon as possible.

Since pg_upgrade copies (or links) the table files, there will be as many dead tuples after the upgrade as there were before. That is not alarming, and autovacuum will take care of it in due course.


user mapping hide password I would like to perform queries over different postgresql11-databases. Therefore I created a data wrapper and a user mapping as following:
CREATE server 'test' foreign data wrapper postgres_fdw
OPTIONS (dbname 'name_db', host 'ip_adress');

CREATE USER MAPPING FOR "Userame"
SERVER 'test' OPTIONS (user 'Username', password '******');

Afterwards it is possible to query with db_link left join etc.. so far no problem. My question is now, how can I hide the passwords for the different users that are visible in foreign data wrappers-postgres_fdw-'test'-user mapping-options ???

Create a .pgpass file on the postgres server in the $HOME directory of the postgres Linux user. You can then store the password of that database user in that file.
Then you can remove the password option from the user mapping, as Postgres (or more precisely: libpq) will pick up the password from the password file.
Don't forget to set password_required to 'false' on the user mapping unless the user mapping is for a superuser (which is an altogether awful thing to do).


Grant a database role in current database with permission to execute a SP in msdb I have created a role in my current SQL Server 2012 database with db_datareader, db_datawriter, and execute permissions. A procedure in the current db calls sp_start_job in msdb.

How can I grant access to my database role to execute the procedure in msdb?

I tried executing the procedure in my current database which calls sp_start_job as owner and the user who is a member the user defined role is still not able to execute the procedure.

I have made the server role public for the login, and mapped it to msdb database, but I'm not able to grant permission. The command I'm trying to execute is:

GRANT EXECUTE ON OBJECT::[msdb].[dbo].[sp_start_job] TO [db_executor]

When I execute from msdb, the error I get is:

  Cannot find the user 'db_executor', because it does not exist or you do not have permission'.

When I execute from the database in which the user defined role, db_executor is created, it throws this error:

  You can only grant or revoke permissions on objects in the current database.

I don't want to add users to SQLAgentOperatorRole. The idea is to group all users in one role within the app's database and the members of this role should be able to run a procedure in the database which calls sp_start_job.

Microsoft created special roles that allow this sort of thing

"SQL Server Agent Fixed Database Roles"

Granting EXECUTE on sp_start_job won't work, because permissions and the checks made when starting a job are linked to role membership only.

USE msdb
ALTER ROLE SQLAgentOperatorRole ADD MEMBER DR_user;


Report Server tempdb's chunk data table is growing very large on daily basis In SQL Server 2005, reportservertempdb chunk data table is growing too much in gbs. Need to know why? We are truncating it on a daily basis, still it grows too much. Database is in simple mode. Need to clarify why is it growing so much.

Most of all facing same issue: Report server tempdb growing very fast, It's better to schedule a job which purges the chunk table (can add required tables if need) and make sure that the scheduled time will have a light load on the server (lets start for every 8 or 12 hrs)

try with below query for scheduling job:

DECLARE @RowCount INT
SET @RowCount = ( SELECT    COUNT(1)
                  FROM      Segment WITH ( NOLOCK )
                )

WHILE ( @RowCount > 0 )
    BEGIN
        DELETE TOP ( 1000 )
        FROM    Segment
        SET @RowCount = ( SELECT    COUNT(1)
                          FROM      Segment WITH ( NOLOCK )
                        )
        PRINT @RowCount
    END

Then Truncate table and Shrink files (both data and log files)

TRUNCATE TABLE Table_name

-- Shrink the truncate tables data and log file to a specified target size (to 1 MB).

DBCC SHRINKFILE (TempDB', 1)
GO

DBCC SHRINKFILE (TempDB_log', 1)
GO

Edit:

Table grows significantly may occur due to the following reasons:

Check the report execution
Report snapshots are too large to process for reporting services.

Because of this issue, as the time progresses it left lots of orphaned snapshots which in turn resulted in growth of the database.

You can use following query for obtaining those reports with the largest size contribution to ReportServerTempDB:

--Reports that are largest contributors to ReportServerTempDB size through tables Segment, ChunkSegmentMapping, SegmentedChunk and SessionData:
SELECT
   sd.ReportPath,
   COUNT(s.SegmentId) as CountOfSegmentId
FROM
   [ReportServerTempDB].dbo.Segment s
INNER JOIN
   [ReportServerTempDB].dbo.ChunkSegmentMapping m
ON
   m.SegmentId = s.SegmentId
INNER JOIN
   [ReportServerTempDB].dbo.SegmentedChunk c
ON
   c.ChunkId = m.ChunkId
INNER JOIN
   [ReportServerTempDB].dbo.SessionData sd
ON
   sd.SnapshotDataID = c.SnapshotDataId
GROUP BY
   sd.ReportPath
ORDER BY
   CountOfSegmentId DESC;

Note:

  Make sure temp DB is Simple - recovery model


Detect if `pg_crypto` in Postgres was built with strong random feature enabled The source code for pg_crypto module mentions an option to build without cryptographically-strong random generation.

/*

Generate random bits. pg_backend_random() will do here, we don't promis

UUIDs to be cryptographically random, when built with

--disable-strong-random.

*/

Discussed further in this thread.
In the particular build I may be using, how can I detect if the strong random feature is enable or disabled?

--disable-strong-random is a PostgreSQL configuration option available in the ./configure script in the source code. You could check to see if your version of PostgreSQL and the associated contrib modules were built with it by using pg_config --configure and looking to see if that particular flag was passed in as part of the config process. Though as it says in the thread that was linked, no reasonable production platform would use that flag, so odds are that you've got a cryptographically random build.


Merge Statement + Replication difference for SQL Server 2012 We have a SSIS package that will pull from a source database and run a merge statement to put the data into a "Master" database.  (Eventually the "Master" database will be the source of truth, but we are a while away from that.)

After the Master Database gets the data, it replicates it down to several other databases.

With Sql Server 2008 R2 this all worked great.

But with SQL Server 2012 we are noticing that there are a bunch of delete statements for these tables being replicated.

The Merge Statement in the SSIS Package does not delete anything.

when NOT MATCHED BY SOURCE then
    update set IsActive = 0

So I am left to wonder why this is happening.  Could SQL Server 2012 be translating my MATCHED Update to a delete and insert? (that seems insane, but I can't seem to figure out why this is happening).

NOTE: We turned off replication of delete statements and the problem stopped, but I want to know what is going on.

Why would I be seeing so many deletes when the source seems unchanged?

Is there a way I can see the transactions posted against a table?

NOTE: I tried doing a SQL Trace, but it did not show any deletes.  Only the merge I described above.

Can you check the article property -->Statement Delivery --> Update delivery format -- what is it set to ?

One more thing to add -- please verify if any column that is part of a unique constraint is updated. If this is true, this means SQL Server is implementing the Update as a Deferred Update, which means as a pair of DELETE/INSERT operations. This "deferred update" causes replication to send a pair of DELETE/INSERT statements to the subscribers. refer : here

To make sure that the Updates are NOT executed as a pair of Delete and Insert statements you can enable trace flag 8207 on Publisher Server.


What scope(s) is "hasMany" a part of? I realize, when talking about structures, obviously if you say "post hasMany comments" there are many records about comments relating to each post.

What I don't understand is what context these sort of terms are used in. I'm thinking mainly only in DBAL or talking about design. In any case, when one thing "hasMany" of another how and where does it show up?

In any case, when one thing hasMany of another how/where does it show up in application and database logic?

The only way I can think to answer this question is with another example of hasMany that may or may not answer your question.

err, except I should have called the table "comment" but I think the point stands.
I don't know that this really helps you understand anymore than what you already had.
So I'll also share a query that might demonstrate how this works:
declare @var char(5)
set @var = 'query'

select
   c.id
  ,c.text
  ,c.author
from comment c
inner join post p
   on p.id = c.parentid

which we expect to return many rows, because one post generally has many comments (in theory, of course not all posts will have comments)

Update:
So here's the thing about relations: They are EASY to model via diagram (see the very first tool I pulled out to answer this question) but they are HARD to diagram via text. So instead of trying to define whether we mean 1:M or 0:N, we just write it in "near English" and say what we intend. Here's some alternatives:
one to one           hasOne               post hasOne author (not true for CW ;])
one to many          hasMany              The one in question
many to one          belongsTo            parent child, comments belongTo post
many to many         hasAndBelongsToMany  This describes a complex relationship
many to many to many hasManyAndBelongsToMany

As you see they can get quite complicated. This would ... once again! ... indicate a need to refactor your business logic into simpler and smaller chunks.
These can also be called "association types" and may be indicated by verbage like "WrittenBy" (a many to one) or "IsOwnedBy" (one to one? - context is key)
Before I walk away from the "hasMany" concept for the last time (well this is an update, more will likely be written) I want to mention that while it's useful for "plain English descriptions", it's also used in ActiveRecord styled installations. Take, for instance, Ruby. http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html The rest of this post (so far) is taken from that page:
class Project < ActiveRecord::Base
  belongs_to              :portfolio
  has_one                 :project_manager
  has_many                :milestones
  has_and_belongs_to_many :categories
end

This allows the ActiveRecord ORM to create the data model and to maintain the relationships as specified by the business logic within the program. But I don't write Ruby or Rails so that's as far as I can tell you, I just know that it exists there.


How to alter system on AWS RDS? I need to run

ALTER SYSTEM SET track_activity_query_size = 16384;

So I can see the contents of some long running queries (I can only see the beginning at the default size), but when I try to do that in pgAdmin on my RDS instance I get the error

ERROR:  must be superuser to execute ALTER SYSTEM command

How do I run that alter system query?

The RDS Postgres instance has a parameter group. Check in RDS colsole what parameter group is defined: RDS -> Databases -> choose your db -> Configuration -> parameter group. If it's the default one, create a new one and change track_activity_query_size to the value you need. Apply the new parameter group or change the actual one. The parameter is static so a restart is needed in any case.

Check this link for more details, please:
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html


Why Does ad_zd_table.upgrade Convert Triggers To Point at the # View? Why does the ad_zd_table.upgrade function (used to notify editioning of modifications in a table schema) automatically change the database triggers to point at the # view of that table? (In our case the result is that the triggers stop firing. I checked and they are editioning views that in theory support triggers, but they aren't working.) Is there a way to work around this problem?

I believe now this is by design. The solution is to point the source code for insert/update/delete statements at the editioned # view of the table. Then the triggers work. Alternately, you can create a synonym and point the insert/update/delete statements at the synonym. I'm not sure which is a better solution--although everything I see on the web is recommending the synonym approach. (Specifically putting the synonyms in the APPS schema. You can't put a synonym with the same name as the table in the same schema as the table, but I'm not sure why the APPS in particular is recommended, or why the synonym approach is recommended. If anyone knows the answer to that please comment.)

For more info:
Oralce: Why Do Triggers on Editioning Views in My Custom Schema Fail?


TNS Issue with error I have a Issue in Oracle I have made two database one for Demo and one for Dev  for same mount point. When I connect with Demo this can be connected but when I try to connect with DEV its not able connect. I have given to add the listner details as given below  but when I am tnsping with DEV its giving TNS 12541 error. I try to find out but not able to connect.

[oracle@CVM-1429 ~]$ sqlplus sys/sys as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Oct 8 18:11:55 2016

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name from v$database;

NAME
---------
CSMEC

SQL>

Some more information:

[oracle@CVM-1429 ~]$ ps -ef|grep pmon
oracle    1286     1  0 Oct07 ?        00:00:10 ora_pmon_CSDEV
oracle   17032     1  0 Sep27 ?        00:01:40 ora_pmon_CSMEC
oracle   30817 30328  0 18:12 pts/2    00:00:00 grep pmon
[oracle@CVM-1429 ~]$
[oracle@CVM-1429 admin]$ pwd
/u03/oracle/app/oracle/product/12.1.0/dbhome_1/network/admin

The content of the listener.ora:

LISTENER=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=10.56.184.32)(PORT=1525))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))
    ))
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=CSMEC)
      (ORACLE_HOME=/u03/oracle/app/oracle/product/12.1.0/dbhome_1/)
      (SID_NAME=CSMEC))
   ))

LISTENER1=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=10.56.184.32)(PORT=1530))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))
    ))
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=CSMEC)
      (ORACLE_HOME=/u03/ORCDEV/oracle/app/oracle/product/12.1.0/dbhome_1/)
      (SID_NAME=CSDEV))
   ))

The tnsnames.ora:

# tnsnames.ora Network Configuration File: /u03/oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

CSMEC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.56.184.32)(PORT = 1525))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CSMEC)
    )
  )

CSDEV =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.56.184.32)(PORT = 1530))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CSDEV)
    )
  )

The result of the tnsping:

[oracle@CVM-1429 admin]$ tnsping CSDEV

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 08-OCT-2016 18:20:18

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.56.184.32)(PORT = 1530)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CSDEV)))
TNS-12541: TNS:no listener

I do not see any proof of the listener running.

This does not seem to be correct:

LISTENER1=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=10.56.184.32)(PORT=1530))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))
    ))
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=CSMEC)
      (ORACLE_HOME=/u03/ORCDEV/oracle/app/oracle/product/12.1.0/dbhome_1/)
      (SID_NAME=CSDEV))
   ))

If you want static registration for LISTENER1, then use SID_LIST_LISTENER1. Your tns entry contains SERVICE_NAME=CSDEV, and you try to register the database with SERVICE_NAME=CSMEC (GLOBAL_DBNAME=CSMEC).

Fix the above and start the listener:

lsnrctl start listener1

Then try again.


build users availability report/Table (using sql) I got a question from our product team to build a "Table availability of customer service representatives (user)". f

For example : user_A was available to accept a call from 12:00 to 12:03, so I have in the database a table with UserName = user_A,StartDatetime = 20151110 12:00,EndDateTime = 20151110 12:03.

From this data I need to create an availability report that can show which users were available in minute interval (every minute) along the all day .

I think in think the use of a tally table would work in this case. There are 1440 minutes in a day, so a tally table would need a minimum of 1440 records in order to work. Using the tally table, you can utilize the DATEADD function to add one minute between your start times and your end times. This would pull back one record per minute for the interval.

/* create the tables variables */
DECLARE @Tally TABLE
    (MinuteOfDay INT)

DECLARE @Users TABLE
    (UserName  VARCHAR(100)
    ,StartDatetime DATETIME
    ,EndDateTime DATETIME
    )

/* Populate the user table */
INSERT INTO @Users
        ( UserName ,
          StartDatetime ,
          EndDateTime
        )
VALUES  ( 'User_A' , -- UserName - varchar(100)
          '11-10-2015 12:00' , -- StartDatetime - datetime
          '11-10-2015 12:03'  -- EndDateTime - datetime
        )

/* Populate the tally table with 1 minute for 24 hours */
INSERT INTO @Tally
        ( MinuteOfDay )
SELECT TOP 1440
    ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) -1 AS Row_No
FROM sys.all_objects AS ao

/* Query to pull the result for each minute */
SELECT * FROM @Users AS u
INNER JOIN @Tally AS t ON DATEADD(MINUTE, t.MinuteOfDay,u.StartDatetime) BETWEEN u.StartDatetime AND u.EndDateTime


Special function slow in where but not in select I have kind of a special problem using a levenshtein algorithm in MySql.
But I don't think that it's special for levenshtein.
This query:

SELECT *,levenshtein(word,'Facbook') FROM Words WHERE length(word) between 6 and 8 and levenshtein(word,'Facbook') <=1 is pretty slow with around 5000 rows it takes about 1s.

But on the other hand this one:

SELECT *,levenshtein(word,'Facbook') FROM Words WHERE length(word) between 6 and 8 is kind of fast (0.02s).

The explain statments looks nearly identical only the number of rows differs a single bit.

The explain table for the first query:

 | id select_type table type possible_keys key key_len ref rows Extra                    |
 ----------------------------------------------------------------------------------------
 | 1  SIMPLE      Words index NULL         word   302  NULL 4711 Using where; Using index|

The second explain:

 | id  select_type table type   possible_keys key key_len ref rows Extra                  |
 ------------------------------------------------------------------------------------------
 | 1   SIMPLE      Words index  NULL          word 302    NULL 4621 Using where; Using index

Btw I have an index on the word column.
Now the question is why is the first one so much slower? It only has to use the second query and "print out" only the words that matching the second where clause: levenshtein(word,'Facbook') <=1 Right?

Strange:
I used PHPMyAdmin to check the time and now I used a simple php file to send the query and now both queries took 1s.
So maybe this question is totally bullshit...

Here's the difference between the two statements:

The first statement calls the levenshtein function only on the retrieved resultset.

The second statement calls the levenshtein and the length function as a so called "predicate" for every row in the table to retrieve the resultset. Even if levenshtein is only evaluated if the first condition is true, then there's the overhead of doing additional comparisons.

Thus, the second query is naturally slower.

IMHO this is a CPU bound problem. To make this query faster, either supply more predicates that can be evaluated (means: add more restrictions to the where clause), add more CPU power and try parallel selects (if mysql supports that).


Get value from one field, and paste it in to a new column I'm relatively new to Microsoft Access, and just a beginner with SQL programming.

I have a table in MS Access. It is an import from a .txt file. In the first column There is a String with a Number or Code in it. This number always Comes after a string "Arbeitslpatz "(The first character starts at string Position 17) and always changes every 76 rows. (Total rows 29790). What I want to achieve is to copy this number (for example 101100) and paste it in a new column in the first 76 rows. Then comes the next number for the next 76 rows, and so on. (I later want to to a lookup function with SQL WHERE functions and return some desired values, and for that I need to have these "Arbeitsplatz numbers" in a new row.
I already assigned an ID to the file, but I didn't manage to get farther. (I experimented with VBA Loops, but didn' really succeed)
Either SQL or VBA Code would be appreciated.

I don't know how well the screenshots can be seen &/ if I provided enough Information...

Thank you all for your help

This is an Image from the orignial text file which I linkt to the database in Access:

Edited OP code:

Function Macro1()
'' dim variables
Dim tmp As String, temp() As String
Dim rs As Recordset
'' open source for reading
Open "J:\80_Applications\SAPGuiScripting\01_TXT_Files\CM01_Total.csv" For Input As #1
'' attach recordset to table
Set rs = CurrentDb.OpenRecordset("CM01_Total_1")
'' loop source - read line-by-line until its end
Do Until EOF(1)
  '' read one line
  Line Input #1, tmp
  '' look if it is a header, if yes, extract and store the number
  If Left(tmp, 12) = "Arbeitsplatz" Then num = Trim(Mid(tmp, 17,8))
  '' look if it is a data line
  If InStr(tmp, "|") > 0 And InStr(tmp, "Woche") = 0 Then
    '' divide the line to separate fields
    temp = Split(tmp, "|")
    With rs
      '' add new, empty, record
      .AddNew
      '' set values into fields
      !Arbeitsplatz = num
      !Woche = temp(1)
      !Bedarf = temp(2)
      !Angebot = temp(3)
      !Belast = temp(4)
      ![freie_Kap] = temp(5)
      !Einh = temp(6)
      '' save editions
      .Update
    End With
  End If
'' loop to reading the next line
Loop
'' close source file and recordset
Close #1
rs.Close
Set rs = Nothing
End Function

PS. Error handler must be added.


How Can I Isolate A Merge Replication Timeout I am using merge replication in SQL 2012 with web sync.

When I create my subscription I am getting a timeout. The error message is not clear about cause of the timeout, but what I do know is if I increase the QueryTimeout parameter on the merge agent from default of 300 to 1200 it works.

How can I find the SQL that is timing out? I have used SQL profiler, but it isn't clear which query is timing out. In SQL profiler I selected the option 'Attention' under 'Errors and Warnings', but didn't see anything about a timeout.

Add the Merge Agent parameters -OutputVerboseLevel 4 -Output C:\TEMP\mergeagent.log to the Run Agent Merge Agent job step to find out where it is timing out.


Database Backups vs Server Backups At my current job, I recently discovered that our MS SQL database servers had no backup devices set up for them.  When I asked other IT members about this, the response was that the servers themselves were being backed up regularly, including the database files, so taking SQL server backups would be redundant.

I'm sure this is a very elementary question, but it got me thinking: why does MS SQL server, as well as other RDBMS systems, have their own backup system?  Most other applications don't have this.  My first thought is that only the RDBMS can perform integrity checks on the database files.

What other reasons are there?  I think we need to change our backup and restore procedures, but I need to make sure I have a solid case for doing so, since it will require additional investments.

If SQL Server is running, the "system" backups will either (a) not include the SQL Server files, or (b) they may not be in a consistent state1.

Ensure SQL Server native backups are taking place on a regular basis, and test the restore procedure.

Also, note the lack of backup devices does not necessarily mean there are no SQL Server native backups taking place.  You need to look at the dbo.backuphistory table in the msdb database to see if backups have taken place.  One way to look at the backup history is to run the following query:

/*
    You can use this variable to either (a) show all database backup
    history, or just history for a single database.
    If you leave it as-is, it will display history for all databases
*/
DECLARE @DBName SYSNAME;
SET @DBName = DB_NAME();
SET @DBName = NULL;

SELECT DatabaseName = bs.database_name
    , BackupStartDate = bs.backup_start_date
    , CompressedBackupSize = bs.compressed_backup_size
    , ExpirationDate = bs.expiration_date
    , BackupSetName = bs.name
    , RecoveryModel = bs.recovery_model
    , ServerName = bs.server_name
    , BackupType =
        CASE bs.type
        WHEN 'D' THEN 'Database'
        WHEN 'L' THEN 'Log'
        ELSE '[unknown]'
        END
    , LogicalDeviceName = bmf.logical_device_name
    , PhysicalDeviceName = bmf.physical_device_name
FROM msdb.dbo.backupset bs
    INNER JOIN msdb.dbo.backupmediafamily bmf
        ON [bs].[media_set_id] = [bmf].[media_set_id]
WHERE bs.database_name = @DBName
    OR @DBName IS NULL
ORDER BY bs.backup_start_date DESC;

1 - see https://msdn.microsoft.com/en-us/library/ms175536.aspx for details about running the SQL Server VSS Writer service to ensure consistent backups when running backups outside of SQL Server.


After modifying the throughput of a DynamoDB table, where can I see the new throughput value before it gets active? After modifying the throughput of a table in DynamoDB, the "Modify Throughput" is greyed out:

Where can I see the new throughput value before it gets active? Sometimes it takes a while for a throughput to get applied, especially for large values, and I would like to check whether the person (myself or colleague) who changed the throughput didn't make a mistake.

I came across similar problem. So thinking to make some sort of series of describe table

DescribeTableResult describeTableResult = client.describeTable('tableName')

And put it in while loop until settings are applied. That could give rough idea how much time does it take to apply settings.


Does SSMS follow the same support lifecycle as SQL Server? I've found information about the support lifecycle for SQL Server (such as this page for SQL Server 2008/2008R2), but haven't found any information about SSMS. Does SSMS follow the same support lifecycle?

In the past, the releases lined up - each new SQL Server was accompanied by a new Management Studio.

However, in June of 2015 (concerning SQL Server 2016 and onwards) MS announced separate life cycles:

  With the release of SQL Server 2016 Community Technology Preview (CTP)
  2.1, customers can for the first time experience the rapid preview model for their on-premises SQL Server 2016 development and test
  environments and can gain a faster time to production.

  In addition, we are delighted to announce our first “preview” release
  of SQL Server Management Studio!  This is our first effort to release
  SQL Server Management Studio (SSMS) in a mechanism outside of the SQL
  Engine releases.  Our goal is to update this frequently with new
  features, fixes and support for the newest SQL Server features in SQL
  Server Engine and Azure SQL Database.

From the MSDN Blog on SQL Server Releases.

Based on your edit to be about support life cycles, I'm not sure that question has an answer since this new direction has been implemented (concerning future releases). As for past SSMS releases, I don't think I know of any updates or support to SSMS after the corresponding SQL Server version has been phased out - so I'd say it's safe to assume they align.


How to identify who made a change I noticed the following in the sql server log, is there any way to determine the login/user that initiated this change. It appears a database was set to single user mode and back to multi user. Here is the log snippet

Date       10/10/2017 8:55:27 PM
Log         SQL Server (Current - 10/11/2017 8:00:00 AM)
Source      spid139
Message     Setting database option MULTI_USER to ON for database 'DBNAME'

SQL server has "default trace" running by default.  You need to search the trace files using something like this:

--Find current trace file name
--Select * from sys.traces

SELECT
     loginname,
     loginsid,
     spid,
     hostname,
     applicationname,
     servername,
     databasename,
     objectName,
     e.category_id,
     cat.name as [CategoryName],
     textdata,
     starttime,
     eventclass,
     eventsubclass,--0=begin,1=commit
     e.name as EventName
FROM ::fn_trace_gettable('H:\MSSQL10_50.ODS01\MSSQL\Log\log_217.trc',0)
     INNER JOIN sys.trace_events e
          ON eventclass = trace_event_id
     INNER JOIN sys.trace_categories AS cat
          ON e.category_id = cat.category_id


MySQL Table Schema Design - Relationship with Junction or User Table I am trying to determine the best approach to create a relationship between my notifications table and tables with user information within my current db architecture. The purpose of the notifications table is to allow users to send a notification (to request information) to other users within a specific organization and I'm not sure the ideal relationship and tables that should be involved. Right now my thought is to join on my member table, which is a junction between user and organization and have that member_id as the fk. However, I don't know if this will be beneficial to the information I am really trying to receive with the queries I plan to run. Here are the high level queries I plan on running:

query 1: query all users (with id, first_name, last_name, email) that are within the same organization as the logged in user

query 2: create a record for a notification and pass in the requester user_id (logged in user) and receiver user_id (user within organization being sent a request)

query 3: query current notifications (with first_name, last_name, email)

Here are the current tables in play:

User:

CREATE TABLE `user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) DEFAULT NULL,
  `last_name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `organization_id` int(11) DEFAULT NULL,
  `authentication_token` varchar(255) DEFAULT NULL,
  `reset_password_token` varchar(255) DEFAULT NULL,
  `reset_password_expires` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `email` (`email`),
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

Organization:

CREATE TABLE `organization` (
  `organization_id` int(11) NOT NULL AUTO_INCREMENT,
  `organization_name` varchar(255) DEFAULT NULL,
  `admin` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`organization_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

Member (Junction Table between User and Organization):

CREATE TABLE `member` (
  `member_id` int(11) NOT NULL AUTO_INCREMENT,
  `member_email` varchar(255) DEFAULT NULL,
  `organization_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `team_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`member_id`),
  UNIQUE KEY `member_email` (`member_email`),
  UNIQUE KEY `member_user_id_organization_id_unique` (`organization_id`,`user_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `member_ibfk_1` FOREIGN KEY (`organization_id`) REFERENCES `organization` (`organization_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `member_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

Notification Table (Currently a One-to-Many on the Member table):

CREATE TABLE `notification` (
  `notification_id` int(11) NOT NULL AUTO_INCREMENT,
  `requester` int(11) DEFAULT NULL,
  `receiver` int(11) DEFAULT NULL,
  `message` text,
  `status` varchar(255) DEFAULT NULL,
  `archived` tinyint(1) DEFAULT NULL,
  `member_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`notification_id`),
  KEY `member_id` (`member_id`),
  CONSTRAINT `notification_ibfk_1` FOREIGN KEY (`member_id`) REFERENCES `member` (`member_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query 1:

SELECT them.user_id, them.first_name, them.last_name, them.email
    FROM user AS me
    JOIN user AS them  ON me.organization_id = them.organization_id
    WHERE me.user_id = ?

Query 2:

INSERT INTO notification (requester, receiver, ...)
    VALUES
    (?, ?, ...)

(I assume you have your id, his id, and other stuff to supply; do not supply notification_id, it will be generated.)

Query 3 is phrased imprecisely:

SELECT first_name, last_name, email,
       timestamp,   -- Don't you need a date&time in the table?
       ...   -- don't you want more fields?
    FROM notifications
    WHERE email = ?  -- Leave out if you want all users; change to search by name
    ORDER BY `timestamp` DESC  -- I assume by 'current' you mean 'recent'?
    LIMIT 10;


Excel Data Connection to SQL Server using Windows Auth from untrusted machine We have an Excel spreadsheet that uses a Data Connection to a SQL Server database. The database server is joined to our domain, and Windows Authentication is enabled.

Here is a sample of the connection string (with revealing info obfuscated):

Description=DataConnection;DRIVER=SQLServer;
SERVER=10.x.y.z;UID=domain_user;Trusted_Connection=Yes;
APP=Microsoft Office 2010;DATABASE=DatabaseName

And the Authentication Settings are set to "Windows Authentication"

This works perfectly fine from a domain-joined / trusted machine.  It doesn't even ask for credentials; it just works flawlessly when I hit Refresh.

It will work from a non-domain / un-trusted workstation, if I use SQL Authentication.  But we are trying to move away from that.

Likewise, it will from a non-domain machine using Windows Authentication if I use "runas" to launch Excel with domain credentials, as follows:

runas /netonly /user:domain\user %path-to-excel%\excel.exe

However, using "runas" is a kludge, and our employees will almost certainly have issues... even if I were to create batch files to help automate it.

When launching Excel normally (without using "runas") from a non-domain machine, it gives this error when I hit Refresh:

Upon hitting OK, I have the option to un-check "Use Trusted Connection" and manually enter a user/password.  I put in a valid user that would be able to connect fine from a domain-joined machine, or that worked fine using "runas".  This is the error it gives with that:

At this point, I have not a clue what to do to get Excel to successfully connect with domain credentials from a non-domain machine.

Ideas?

Our solution ended up being ShellRunas.

https://technet.microsoft.com/en-us/sysinternals/cc300361.aspx

By creating custom shortcuts to launch Excel using the /netonly switch, we can have them enter their domain credentials and then open the needed spreadsheets.  It's not 100% transparent, but it's good enough.


How to convert an age interval into a decimal year number in PostgreSQL I try to convert an interval like "29 years 6 mons 22 days 21:17:52.860842" into a decimal year reprensentation like 29,55 years in PostgreSQL.

SELECT EXTRACT(EPOCH FROM INTERVAL '29 years 6 mons 22 days 21:17:52.860842')/31557600;

where 31557600 is roughly the number of seconds in a year.