Table size without indexes in sql server Using the following query I can get the size of the indexes for a table:
SELECT
    i.name              AS IndexName,
    SUM(page_count * 8) AS IndexSizeKB
FROM sys.dm_db_index_physical_stats(db_id(), object_id('schema.table'), NULL, NULL, 'DETAILED') AS s
JOIN sys.indexes AS i
ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
GROUP BY i.name
ORDER BY i.name

If I execute sp_spaceused 'schema.table' does the size without indexes correspond to data column?
Any way to get the size of the table without indexes in sql server? (SELECT statement would be nice as I'd like to plug this query into a bigger query).
If I execute Get size of all tables in database, the sizes for tables are almost the same as the sizes for indexes (suggesting that something is wrong).

There are multiple ways of finding size of a table in the database. First and easiest way is to right click on Table and check the storage tab and this gives you Data space and Index Space. Data space is nothing but size of data in the table and similarly index space gives you size of index in the mentioned table.

The above information would exactly match with the command sp_spaceused 'schema_name.table_name' We can cross check the same for the Users Table(in dbo schema and StackOverFlow Database).

Here, if you add data, index size and unused(in KB), you will get reserved space by table.
You may also check the size of table and index by running command sp_blitzindex(from Mr. Brent Ozar). Command would be as below:
exec sp_BlitzIndex  @SchemaName = 'dbo', @TableName = 'Users'

Here, you may check the Size column. Clustered index size will give you Table size and you can also find size of each and every index as well as their usage(if interested).
Moreover you can also check table size of all of them by getting into Reports as below:

You may also run below select statement to get full size and index size of all the tables:
;with cte as (
SELECT
t.name as TableName,
SUM (s.used_page_count) as used_pages_count,
SUM (CASE
            WHEN (i.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
            ELSE lob_used_page_count + row_overflow_used_page_count
        END) as pages
FROM sys.dm_db_partition_stats  AS s
JOIN sys.tables AS t ON s.object_id = t.object_id
JOIN sys.indexes AS i ON i.[object_id] = t.[object_id] AND s.index_id = i.index_id
GROUP BY t.name
)
select
    cte.TableName,
    cast((cte.pages * 8.)/1024 as decimal(10,3)) as TableSizeInMB,
    cast(((CASE WHEN cte.used_pages_count > cte.pages
                THEN cte.used_pages_count - cte.pages
                ELSE 0
          END) * 8./1024) as decimal(10,3)) as IndexSizeInMB
from cte
order by 2 desc

If you want only the table sizes then execute the following:
SELECT
t.name as TableName
, SUM (CASE WHEN (i.index_id < 2)
          THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
          ELSE lob_used_page_count + row_overflow_used_page_count
     END) * 8 as table_size_kb
FROM sys.dm_db_partition_stats  AS s
JOIN sys.tables AS t ON s.object_id = t.object_id
JOIN sys.indexes AS i ON i.[object_id] = t.[object_id] AND s.index_id = i.index_id
where t.object_id = OBJECT_ID('schema.table')
GROUP BY t.name

Hope this helps.


Oracle strong ref cursor with dynamic sql I'm using Oracle 10g. I have a query in a stored procedure that selects entire rows from a table. Currently it returns a strongly typed ref cursor (tablename%rowtype). Now I have to add some dynamic WHERE clauses to this query, but dynamic SQL doesn't support a strong ref cursor. It has been asked on SO before. My question is: is there a workaround? Use some kind of wrapper or conversion or DBMS magic to convert from the weak cursor to the strong cursor? I can guarantee that the results match the table rowtype.

There are probably better ways to achieve your underlying goal, but if you really want values from dynamic SQL into a strongly typed refcursor I guess you could do something like this:

create table TESTTABLE(TESTID number not null);
create global temporary table TEMPTESTTABEL(TESTID number not null);

create or replace procedure testprocedure is
  type testtablerefcursor is ref cursor return TESTTABLE%rowtype;
  type testtabletable is table of TESTTABLE%rowtype;
  mycursor testtablerefcursor;
  mysyscursor SYS_REFCURSOR;
  mytesttable testtabletable;
begin
  open mysyscursor for 'select * from TESTTABLE';
  fetch mysyscursor bulk collect into mytesttable;
  close mysyscursor;
  forall i in mytesttable.first..mytesttable.last
   insert into TEMPTESTTABEL values mytesttable(i);
  open mycursor for select testid from TEMPTESTTABEL;
end;
/


Dropping a large table On our production db there is a table 180 GB,  around 2 millions records, I want to re-create it as a new empty table in a new filegroup and delete the existing table. What is the best practice to perform this task without affecting the DB performance?

My time concern will be in renaming the table and dropping it.

I want to add this is a very high load DB so I don't want to get any locks or long running queries.

Create the new table
Drop the old table and rename the new one in a transaction:

BEGIN TRANSACTION;
  DROP TABLE dbo.OldTable;
  EXEC sys.sp_rename N'dbo.NewTable', N'OldTable', N'OBJECT';
COMMIT TRANSACTION;

If you have concerns about how long the drop itself will take, you can do it this way instead:

BEGIN TRANSACTION;
  EXEC sys.sp_rename N'dbo.OldTable', N'garbage',  N'OBJECT';
  EXEC sys.sp_rename N'dbo.NewTable', N'OldTable', N'OBJECT';
COMMIT TRANSACTION;
DROP TABLE dbo.garbage;

(The drop doesn't technically have to be a part of the transaction, and shouldn't cause any blocking in any case, so now how long the drop actually takes is largely irrelevant, because there is no chance for it to block anyone.)

If you have indexes, foreign keys etc. you'll also have to deal with those, but the above is about the fastest you're going to get because it's mostly metadata (at worst the DROP will be blocked by existing activity).

Also see Schema Switch-A-Roo part 1 and part 2 if your situation is more complex.


Massive random reads on a huge mongodb database but no cache misses? I do some performance testing for MonogDB. I have lots of RAM (2TB) and a database which is completely in RAM with 250GB, so for sure all indexes are too (actually there is only one on _id since I ycsb tests).

But what can be the reason that I do see almost no cache misses? I would expect plenty since I have ycsb setup to randomly access the db with a recorccount of 1 000 000 ...  This can not fit into the cache even with L4 = 600 MB.....

My guess is that YCSB isn’t fetching the whole document. Like a covered query, so if all the data that it needs is delivered by the data in the index, you don’t have to get anything from disk, so no cache misses. Another factor is that any page evicted from WT cache would be in the FS cache, since WT on MongoDB only supports buffered I/O.

YCSB doesn’t really do a good job testing MongoDB performance. If you’re using the default workloads, e.g. workloada(50%query/50%updates) or workloadc(95%query/5%updates), as soon as your indexes are loaded in memory no data you will accessed on disk. By default YCSB is querying on _id and getting only one document per request. You could force YCSB to bring create a cursor with [x]thousand documents and make it iterate until the cursor is exhausted.  Here is the command if you wanna try it:

./YCSB/bin/ycsb -s -P ./YCSB/workloads/workloadc -p mongodb.url=“mongodb://192.168.13.101:27017,192.168.13.102:27017,192.168.13.103:27017/ycsb1” -p operationcount=120000000 -p maxexecutiontime=1800 -p readproportion=0 -p scanproportion=1 -p maxscanlength=8000 -threads 16


How to Restrict INSERT operations on table for DB2? I have a table OAUTH20_TOKEN_CACHE for which I have restricted INSERT operations by running below command.
db2 REVOKE INSERT ON HVD121.OAUTH20_TOKEN_CACHE FROM USER HVD121

This could be seen
INSERTAUTH = 'N' for particular grantee 'HVD121' in the 'SYSTABAUTH' table,but still I could see INSERT operations succeeding for the particular table wherein the connection is established using user 'HVD121.

Is this correct approach??

User in DB2 can have privileges granted via user's groups and roles as well.
DB2 stores permissions on objects, not restrictions. You can't revoke a privilege on an object from a user, if this user hasn't been granted this permission explicitly (you get SQL0556N on such a revoke statement). The reason you see INSERTAUTH = 'N' in SYSCAT.TABAUTH (SYSIBM.SYSTABAUTH) is that this user has some other *AUTH permission(s) on this table, but not INSERT.

So, you can't revoke a privilege from a user explicitly, if this user has this privilege via its groups or roles (we don't consider LBAC here).

2 checks should be done to understand why some particular user has some privilege on some object.
Change the constants 'MYUSER', 'MYSCHEMA', 'MYTABLE' below accordingly.

1) If the user has DATAACCESS database authority:

SELECT P.GRANTEE, P.GRANTEETYPE
FROM SYSCAT.DBAUTH P
CROSS JOIN TABLE(VALUES 'MYUSER') A (AUTHID)
JOIN TABLE (
SELECT GROUP, 'G' FROM table(AUTH_LIST_GROUPS_FOR_AUTHID(A.AUTHID))
  UNION ALL
select ROLENAME, 'R' from table(AUTH_LIST_ROLES_FOR_AUTHID(A.AUTHID, 'U'))
  UNION ALL
SELECT * FROM TABLE(VALUES ('PUBLIC', 'G'), (A.AUTHID, 'U')) T (AUTHID, AUTHIDTYPE)
) U (AUTHID, AUTHIDTYPE) ON U.AUTHID=P.GRANTEE AND U.AUTHIDTYPE=P.GRANTEETYPE
WHERE P.DATAACCESSAUTH='Y';

2) If the user has the privilege on the table:

SELECT
  P.PRIVILEGE, P.OBJECTTYPE, P.OBJECTSCHEMA, P.OBJECTNAME
, U.AUTHID, U.AUTHIDTYPE
FROM SYSIBMADM.PRIVILEGES P
CROSS JOIN TABLE(VALUES 'MYUSER') A (AUTHID)
JOIN TABLE (
SELECT GROUP, 'G' FROM table(AUTH_LIST_GROUPS_FOR_AUTHID(A.AUTHID))
  UNION ALL
select ROLENAME, 'R' from table(AUTH_LIST_ROLES_FOR_AUTHID(A.AUTHID, 'U'))
  UNION ALL
SELECT * FROM TABLE(VALUES ('PUBLIC', 'G'), (A.AUTHID, 'U')) T (AUTHID, AUTHIDTYPE)
) U (AUTHID, AUTHIDTYPE) ON U.AUTHID=P.AUTHID AND U.AUTHIDTYPE=P.AUTHIDTYPE
WHERE P.OBJECTSCHEMA = 'MYSCHEMA' AND P.OBJECTNAME='MYTABLE' AND P.OBJECTTYPE='TABLE'
AND P.PRIVILEGE IN ('INSERT', 'CONTROL')
ORDER BY 2, 3, 4;

@Redhat: Show the results of the queries above for MYSCHEMA=HVD121, MYTABLE=OAUTH20_TOKEN_CACHE, MYUSER=HVD121.


Master slave replication in mysql seconds behind master goes on increasing In replication at slave end seconds_behind_master goes on increasing.
The dump file for upload is of 6.7 gb large, can it be the reason for the lagging of seconds behind master?

Just check if you have same time and timezones on both the servers, i.e., Master as well as Slave.

++++++++
Here is an idea

In order for you to know that MySQL is fully processing the SQL from the relay logs. Try the following:

STOP SLAVE IO_THREAD;
This will stop replication from downloading new entries from the master into its relay logs.

The other thread, known as the SQL thread, will continue processing the SQL statements it downloaded from the master.

When you run SHOW SLAVE STATUS\G, keep your eye on Exec_Master_Log_Pos. Run SHOW SLAVE STATUS\G again. If Exec_Master_Log_Pos does not move after a minute, you can go ahead run START SLAVE IO_THREAD;. This may reduce the number of Seconds_Behind_Master.

Other than that, there is really nothing you can do except to:

Trust Replication
Monitor Seconds_Behind_Master
Monitor Exec_Master_Log_Pos
Run SHOW PROCESSLIST;, take note of the SQL thread to see if it is processing long running queries.
BTW Keep in mind that when you run SHOW PROCESSLIST; with replication running, there should be two DB Connections whose user name is system user. One of those DB Connections will have the current SQL statement being processed by replication. As long as a different SQL statement is visible each time you run SHOW PROCESSLIST;, you can trust mysql is still replicating properly.


Why postgresql pg_dump exports a view as a table? I am using PostgreSQL 9.3 pg_dump tool for extracting only the public schema definition using:

pg_dump -s -n public -h host -U postgres --dbname=db > ./schema.sql

but when I check schema.sql one of our views appears in a CREATE TABLE statement instead of a CREATE VIEW statement.

But, if I pg_dump the specific view using:

pg_dump -s -t myview -h host -U postgres --dbname=db > ./schema.sql

then schema.sql contains the actual view definition.

So, why is this happening? Thank you guys!

Internally, a view is just a table with a rule, so this makes sense.

See here: https://postgresql.org/docs/9.5/static/rules-views.html

  Views in PostgreSQL are implemented using the rule system. In fact,
  there is essentially no difference between:

CREATE VIEW myview AS SELECT * FROM mytab;

  compared against the two commands:

CREATE TABLE myview (same column list as mytab);
CREATE RULE "_RETURN" AS ON SELECT TO myview DO INSTEAD
    SELECT * FROM mytab;

  because this is exactly what the CREATE VIEW command does internally.
  This has some side effects. One of them is that the information about
  a view in the PostgreSQL system catalogs is exactly the same as it is
  for a table. So for the parser, there is absolutely no difference
  between a table and a view. They are the same thing: relations.


Calling SQL Loader From SQL Plus I was wondering if there was a way I can execute a SQL Loader script from SQL Plus.

We are using Oracle 10g.

You should be able to run it via the HOST command:

SQL> host /path/to/ora/bin/sqlldr parfile=...


Database Migration - Can it be this easy? We have an old SQL Server 2008 server that needs to be replaced, and as the sole developer (and by extension DBA), I have been asked to figure out the best way to move the 5 databases to a new server.  As someone more familiar with T-SQL than SSIS, I'm not sure the way to go, but have come up with a plan that seems to allow quick and mostly painless (for the users at least) migration to the new server.  My high level plan so far is:

Setup the new server (applications, patches, etc).  We may stay with 2008, but I'm going to recommend 2012 or 2014 to bring us up to date.
Enable database mirroring or replication from the old server to the new one.  Wait for the databases to copy over.
With all users logged out, shut down old server and rename new server to old server's name.
Shut down replication, go live.

Yes, I realize there are a lot of steps in here, and plenty of caveats (users, security roles, etc), but if I'm thinking about this right, then most of this will be taken care of automatically.  My question is this (two part): Is this a valid plan to get everything moved over?  If not, what would be better?  I'm trying to minimize downtime for the users and overtime for us.

High level your approach will work but there is an additional step you will need to do along with a gotcha.

First off the gotcha - you will need to ensure that the instance names are identical on each server (although you can have a different server names). This isn't an issue though if you are just using default instances.

Once you cut over the databases you can then rename the new server to the old name. Once renamed you will need to rename the server name within SQL Server (again not to be confused with the instance name) using the steps outlined in this article.

https://msdn.microsoft.com/en-us/library/ms143799.aspx

I would either use log shipping or mirroring for the 5 databases and then manually copy across each instance level object (logins, jobs, linked servers, mail profiles, etc) and goes without saying that the applications should be tested before hand to make sure you are aware of all the instance level components and don't forget anything.

I have used approaches like this before to migrate clustered instances with numerous databases. This approach has more work up front getting everything set up and ready but it makes the cut over relatively quick and painless for the client applications.


Is there a way to check which computer or IP used a particular script? As the Title suggest, is there a way to check what IP used a particular SQL Script?

The scenario is, there is a recreation script in a email and we hypothesize that somehow, that recreation script was used and the data inside the database was removed. We want to know which computer used the script. we know that when was the script was used. But somehow the user name we used is SA which can be used by other programmers as well (sorry for this).

Is there a script or a utility that can tell us the said information?

Sample of said script:

if  exists
            (
                select  *
                    from    sysobjects
                    where   id  =   object_id   (   N'dbo.DSTRNP'   )
                        and OBJECTPROPERTY  (   id, N'IsUserTable'  )   =   1
            )
    drop    table   dbo.DSTRNP
go

create  table   dbo.DSTRNP
    (
        TEST    char(12)    not null
            ,   constraint  pk_c_DSTRNP primary key clustered
            (
                TEST
            )
    )
go

You could try reading the default trace to view all Host Names for queries which performed an object DROP (since you mention a drop table).
Default trace should contain log record about it.

SCRIPT:

This script reads events from the default trace, if it is enabled.
Then it displays events for when any object changes occured (CREATE, DROP, ALTER).

USE tempdb
GO

DECLARE @DatabaseName nvarchar(200)
SET @DatabaseName = 'Your database' -- <<<======Specify the name of the database here, else comment this line to get details of databases

DECLARE @enable int
SELECT top 1 @enable = convert(int,value_in_use) FROM sys.configurations WHERE name = 'default trace enabled'

IF @enable = 1 --default trace is enabled
BEGIN

    DECLARE @d1 datetime;DECLARE @indx int ;
    DECLARE @curr_tracefilename varchar(500);
    DECLARE @base_tracefilename varchar(500);
    DECLARE @temp_trace TABLE (obj_name nvarchar(256) COLLATE database_default,database_name nvarchar(256) COLLATE database_default,start_time datetime,
                  event_class int,event_subclass int,object_type int,server_name nvarchar(256) COLLATE database_default,
                  application_name nvarchar(256) COLLATE database_default,ddl_operation nvarchar(40) COLLATE database_default,
                  SessionLoginName nvarchar(512) COLLATE database_default, LoginName nvarchar(512) COLLATE database_default, HostName nvarchar(512) COLLATE database_default);

    SELECT @curr_tracefilename = PATH FROM sys.traces WHERE is_default = 1 ; SET @curr_tracefilename = reverse(@curr_tracefilename)
    SELECT @indx  = PATINDEX('%\%', @curr_tracefilename); SET @curr_tracefilename = reverse(@curr_tracefilename)
    SET @base_tracefilename = LEFT( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc';

    INSERT INTO @temp_trace SELECT ObjectName,DatabaseName,StartTime,EventClass,EventSubClass,ObjectType,ServerName,ApplicationName,'temp'
        ,SessionLoginName,LoginName,HostName
    FROM ::fn_trace_gettable( @base_tracefilename, default ) WHERE EventClass in (46,47,164) AND EventSubclass = 0 AND DatabaseID <> 2

    --------------------------------------------
    AND (DatabaseName = @DatabaseName OR @DatabaseName IS NULL)

    --------------------------------------------

    UPDATE @temp_trace SET ddl_operation = 'Object:Created' WHERE event_class = 46
    UPDATE @temp_trace SET ddl_operation = 'Object:Deleted' WHERE event_class = 47
    UPDATE @temp_trace SET ddl_operation = 'Object:Altered' WHERE event_class = 164

    SELECT @d1 = min(start_time) FROM @temp_trace

    SELECT start_time AS [Event Time]
        , server_name AS [Server Name]
        , database_name AS [Database Name]
        , HostName AS [Host Name]
        , application_name AS [Application Name]
        , SessionLoginName AS [Session Login Name]
        , LoginName AS [Login Name]
        , obj_name AS [Object Name]
        , ddl_operation AS [DDL Operation]
    FROM @temp_trace WHERE object_type not in (21587)
                  --AND LoginName = 'suspect login'
    ORDER BY start_time  DESC

END

Useful links:

MSSQLTips: Using the SQL Server Default Trace to Audit Events
brentozar: Default Trace Contents


2 fast SQL queries get slow when bunched as subquery on a very large table I'm trying to use a subquery to speed up a query on a very large table right now 30 million row it most likely going to end up close to 1 billion rows.

My table lnmntr is as follow

Lnmntr_ndx:  auto increment index,
Val: Numeric,
T_stamp: Time_Stamp indexed,
Row_id: Numeric

So my main query looks like this. This basically execute in no time. It select the 500 row following an autoincrement index

SELECT *
FROM CLD_DB.lnmntr
WHERE lnmntr_ndx >= 18411001
ORDER BY lnmntr_ndx ASC
LIMIT 500;

My subquery is used to find 1 row with the correct val. I've added a between timestamp here because I know around what time I'll find the row I need and since it's indexed it speed up thing. This query executes in about .4 seconds

SELECT Lnmntr_ndx
FROM CLD_DB.lnmntr
WHERE (t_Stamp BETWEEN '2016-06-05 16:22:04' AND '2016-06-05 16:23:04')
  AND (Row_id IN (0,500,1000,1500,2000,2500,3000,3500,4000,4500,5000,
                  5500,6000,6500,7000,7500,8000,8500,9000,9500)
  AND Val = 49092400);

but when I bunch the 2 queries together, I end up with a 20 second query

SELECT *
FROM CLD_DB.lnmntr
WHERE lnmntr_ndx >= (SELECT Lnmntr_ndx
                     FROM CLD_DB.lnmntr
                     WHERE (t_Stamp BETWEEN '2016-06-05 16:22:04' AND '2016-06-05 16:23:04')
                       AND (Row_id IN (0,500,1000,1500,2000,2500,3000,3500,4000,4500,5000,
                        5500,6000,6500,7000,7500,8000,8500,9000,9500)
                       AND Val = 49092400)
                     )
ORDER BY lnmntr_ndx ASC
LIMIT 500;

I could run the 2 queries back to back to speed up thing but it would be much easier to run a single query. And id really like to know why they get this slow when bunched together

I'm not an expert on mysql/mariadb, but I suspect that it is running the sub-query one for every row in CLD_DB.lnmntr, or at least every row it needs to until it finds 500 that match the condition (which might just be 500 or might be far more depending on your data). Some SQL engines are bright enough to see that the inner query is invariant within the context of the current statement and only run it once, but IIRC mariadb's query planner doesn't do that sort of thing.

Assuming you have an index on CLD_DB.lnmntr your first query will result in a seek to the first point where lnmntr_ndx >= 18411001 then an ordered partial scan until 500 rows are found (or it hits the end). Even if the inner query were pretty much instantaneous so there would be very little difference between running it once and running it some hundreds or thousands of times, if it needs to be run for each row then you are forcing it to scan down the index from the beginning until 500 rows after the point where lnmntr_ndx is greater than the value.

So it could be either or both of these factors. To confirm which use EXPLAIN to look at the query plan, as suggested by Nick, and edit your question to add the resulting information. It would also be useful to provide the relevant table definitions (columns and their types, and the indexes & keys).


What sort of exec requests don't have a sql handle? (sys.dm_exec_requests) I have crafted a query against SQL Server 2012 that looks like the following:

SELECT r.session_id, r.sql_handle, t.text -- more details excluded for brevity
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t

Now, this helpfully shows me what is running on my server -- and using a query like this I've been able to find things that consuming more resources than they should.

However, it bothers me that (because of the cross apply, I believe) the entries in sys.dm_exec_requests that don't have a sql_handle value, aren't included. I also worry that at times, the number of requests returned (typically about a dozen) is below what I would expect based on load.

I understand (or think I do) that this is the most finite snapshot in time view I could look at, and SQL can only do so many things at once, but I also wonder, what is my query missing to present me a picture of "what is using my server right now"?

The issue with your current query is that you are only looking for queries that could be running at that very moment. To get a good full picture of your server state, you could try to run SQL Server Profiler or check for connections with a built-in stored procedure likesp_who. These approaches are more complicated and time-consuming than using a stored procedure that's already been developed and tested by other DBAs.

I suggest that you install Adam Machanic's free stored procedure, called sp_whoisactive. The most recent version, should be sufficient to help you dig into activity on your server. This procedure will also give you more detailed information than writing a query referencing one or two DMVs. From the documentation, sp_whoisactive uses 15 DMVs.


Replace space (" ") with no space ("") in one column I have a table like this:

ID | Propinsi | Kota |
_________________________
 1 | Aceh     | Denpasar
 2 | Aceh     | Banda Aceh
 3 | Sumatera | Asahan

This table has many rows. The problem is I want to replace the space before the text in column Kota for all rows like this:

ID | Propinsi | Kota |
_________________________
 1 | Aceh     |Denpasar
 2 | Aceh     |Banda Aceh
 3 | Sumatera |Asahan

I searched Google, the function replace in MySQL only affects one row:

SELECT REPLACE(string_column, 'search', 'replace') as Kota

Can someone fix my problem?

Try this:

To show without space:

select trim(kota) from yourtable

To change your data:

update yourtable set kota = trim(kota);

TRIM function is different to REPLACE. REPLACE substitutes all occurrences of a string; TRIM removes only the spaces at the start and end of your string.

If you want to remove only from the start you can use LTRIM instead. For the end only you can use RTRIM.


How to have multiple condition on one column I have table with students - name, id
and table with grades - id, grade value, discipline.
Each students has few grades. I want to select only the students with grade = 2 and grade = 6 in the same time.
SELECT NAME

FROM STUDENTS, GRADES

WHERE STUDENTDS.ID = GRADES.ID

AND GRADE = 2 AND GRADE = 6

The logical condition can't be valid there. Which is the function that I need?

SELECT NAME
FROM STUDENTS s
WHERE EXISTS (SELECT 1 FROM GRADES WHERE ID = s.ID AND GRADE = 2)
    AND EXISTS (SELECT 1 FROM GRADES WHERE ID = s.ID AND GRADE = 6)

or if you don't have multiple records for some grades for particular users:
SELECT NAME
FROM STUDENTS
JOIN GRADES ON STUDENTS.ID = GRADES.ID
WHERE GRADE = 2 OR GRADE = 6
GROUP BY ID, NAME
HAVING COUNT(1) = 2


The INSERT/UPDATE/DELETE permission was denied on the object 'TheTable', database 'TheDb', schema 'dbo' I have two SQL Server user accounts on a database that behave differently on INSERT / UPDATE / DELETE. SQL Server version is 2008 R2 (10.50.4042, 64 bits).

UserA can do these commands (on any table). It is a SQL Server 'local' account (a SQL Server login, NOT a Windows Account on the local server).

DOMAIN\UserB, cannot. It comes from a Windows AD account. It is a member of Administrators group on the database server.

It get the error :

  The INSERT/UPDATE/DELETE permission was denied on the object
  'TheTable', database 'TheDb', schema 'dbo'

As far as I know, the two accounts have the same roles.
One of these roles has the permission to INSERT/UPDATE/DELETE on the tables.
The accounts are not member of the role db_denydatawriter.

I tried to grant the permission explicitly for UserB :

GRANT SELECT, UPDATE, DELETE, INSERT on TheTable TO [DOMAIN\UserB]

But I still have the error.

If I add sysadmin role on UserB, of course it has the permission, but I do not want to let it have this role.

So, to me, the only difference left is local account / Windows AD account.

Do you know a way to have more information about a potential Windows AD account specific permission?

After much more research, I suspect that UserB has a role with no permission. This Stack Overflow answer put me on the track. Here is a query showing that:

-- Users that are members of roles without any permission :
select *
from sys.database_role_members members
left outer join sys.database_principals memberprinc on memberprinc.[principal_id] = members.[member_principal_id]
left outer join sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
where members.role_principal_id in
(
    -- Roles without any permission :
    select principal_id
    from sys.database_principals roleprinc
    where not exists
    (
        select *
        from sys.database_permissions perm
        where perm.[grantee_principal_id] = roleprinc.[principal_id])
)
and memberprinc.name = 'DOMAIN\UserB'

But I am not able to edit these tables (even with sysadmin role), which is understandable.

  Msg 259 Ad hoc updates to system catalogs are not allowed.

I noticed another difference between UserA and UserB: On TheDb properties, tab Permissions, sub-tab Effective:

I found (from SQL Server Profiler) that this list comes from the following query:

EXECUTE AS LOGIN = N'DOMAIN\UserB';
SELECT
    permission_name AS [Permission]
FROM fn_my_permissions(N'[TheDb]', N'DATABASE')
ORDER BY permission_name;
REVERT;

Do you know how to track the origin of theses permissions ?

As suggested in comments, I listed the DENY permissions for UserA and UserB:

SELECT [dp].[name] AS [user_name], [dpr].[name] AS [role_name], [per].[permission_name], [per].[state_desc], OBJECT_NAME([major_id]) AS [object_name]
FROM [sys].[database_principals] AS [dp]
INNER JOIN [sys].[database_role_members] AS [drm] ON [dp].[principal_id] = [drm].[member_principal_id]
INNER JOIN [sys].[database_principals] AS [dpr] ON [drm].[role_principal_id] = [dpr].[principal_id]
INNER JOIN [sys].[database_permissions] AS [per] ON [dpr].[principal_id] = [per].[grantee_principal_id]
WHERE [dp].[name] in ('DOMAIN\UserB', 'UserA') AND [per].[state_desc] = 'DENY'
ORDER BY [dp].[name];

The two users have the very same DENY permissions on two objects:

user_name   role_name   permission_name state_desc  object_name
---------------------------------------------------------------
UserA        RoleC      REFERENCES      DENY        ObjectD
UserA        RoleC      REFERENCES      DENY        ObjectE
DOMAIN\UserB RoleC      REFERENCES      DENY        ObjectD
DOMAIN\UserB RoleC      REFERENCES      DENY        ObjectE

Does anyone have some more clues?

SELECT SUSER_NAME() does return DOMAIN\UserB when I log in as DOMAIN\UserB.

Try with the below script. Its works for me.

GRANT SELECT ,UPDATE,DELETE,INSERT ON OBJECT::YourTable
    TO [Domain\User];
GO

I tried to the mentioned permission to a user Anoop,in the database MyLab.

GRANT SELECT ,UPDATE,DELETE,INSERT ON OBJECT::Account
    TO [*MyDomain*\anoop];
GO

And below is the screen shot after giving the permission.


Selecting an intersection with a fixed given percentage between two tables I working with PostgreSQL and I have two tables:

Serieses

SeriesID
716
718
214
210

Viewers

ViewID    SeriesID
10          716
12          718
10          718
15          770
10          210
12          214
12          210

How can I create a query which for each viewer if it has series intersection more than 70% with table serieses I add it to the final result?

In our example we will get:

Result:

ViewerID
10
12

NOT tested in Postgres, but it should be similar to:

SELECT V.ViewerID
FROM   Viewers AS V
GROUP BY V.ViewerID
HAVING CAST(COUNT(*) AS NUMERIC)
       >
       (0.7 * CAST((SELECT COUNT(*) FROM Serieses) AS NUMERIC))


Transpose rows data into columns in MYSQL Please help me with the scrip that can change table1 into table2 given in the post. I wish to change the rows data into columns

Please provide Data as Text, your image, is for an ocr not really good enough

CREATE TABLE table1 (
  `regno` VARCHAR(6),
  `subject` VARCHAR(16),
  `grade` VARCHAR(2)
);

INSERT INTO table1
  (`regno`, `subject`, `grade`)
VALUES
  ('7007gd', 'mathematics', 'C6'),
  ('7007gd', 'english language', 'CS'),
  ('7007gd', 'economics', 'C5'),
  ('7007gd', 'government', '83'),
  ('7007gd', 'biology', 'C6'),
  ('7010gd', 'mathematics', 'C6'),
  ('7010gd', 'english language', 'C5'),
  ('7010gd', 'economics', 'C5'),
  ('7010gd', 'government', 'C5'),
  ('7010gd', 'biology', 'C5'),
  ('70151f', 'mathematics', 'C6'),
  ('70151f', 'english language', 'C5'),
  ('70151f', 'economics', 'C6'),
  ('70151f', 'government', 'C5'),
  ('70151f', 'biology', 'C5'),
  ('70151f', 'chemistry', 'C5'),
  ('70151f', 'physics', '83');

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
               CONCAT('MAX(IF(`subject` = "', `subject`,'", `grade`,"")) AS "',`subject`,'"')
              ) INTO @sql
FROM table1;

SET @sql = CONCAT('SELECT  `regno`,  ', @sql, '
                  FROM table1 s
                 GROUP BY s.`regno`
                 ORDER BY s.`regno`');
#SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE stmt;

regno  | biology | chemistry | economics | english language | government | mathematics | physics
:----- | :------ | :-------- | :-------- | :--------------- | :--------- | :---------- | :------
7007gd | C6      |           | C5        | CS               | 83         | C6          |
7010gd | C5      |           | C5        | C5               | C5         | C6          |
70151f | C5      | C5        | C6        | C5               | C5         | C6          | 83

db<>fiddle here


Error during sp_refreshsqlmodule after altering table type I am trying to add a column to a user-defined table type.
I have done some research and here is what I have now:

  -- First, rename existing table type to something else
EXEC sp_rename 'TT_MY_TABLE_TYPE', 'TT_MY_TABLE_TYPE_1'

-- Create the new table type
CREATE TYPE [dbo].[TT_MY_TABLE_TYPE] AS TABLE(
    [MY_FIELD] [varchar](20) NULL
)
GO

-- Do a refresh of the SP/views so that the SP/views will refer to the new table type
-- Save the list of dependencies to a temporary table
SELECT  'sp_refreshsqlmodule ' + quotename('MySchemaName.' +object_name(referencing_id), '''') AS SQL_CMD
INTO    #TEMPSQL
FROM    sys.sql_expression_dependencies
WHERE   referenced_class_desc = 'TYPE' and referenced_entity_name =  'TT_MY_TABLE_TYPE';

DECLARE @sql NVARCHAR(1000)
-- Do a loop for the list of dependencies, use dynamic SQL to execute the SQL commands
DECLARE c_Cur CURSOR FOR
    SELECT  SQL_CMD
    FROM    #TEMPSQL

OPEN c_Cur
FETCH NEXT FROM c_Cur INTO @sql
WHILE (@@FETCH_STATUS = 0)
BEGIN
    EXEC SP_EXECUTESQL @statement = @sql
    FETCH NEXT FROM c_Cur INTO @sql
END

CLOSE c_Cur
DEALLOCATE c_Cur

-- Drop the old table type
DROP TYPE TT_MY_TABLE_TYPE_1

DROP TABLE #TEMPSQL

Unfortunately, while refreshing stored/procedures (their dependencies) to reflect the new type I am getting the following error:

Procedure sp_refreshsqlmodule_internal, Line 85 [Batch Start Line 0]
Operand type clash: TT_MY_TABLE_TYPE is incompatible with TT_MY_TABLE_TYPE_1.

I expect that the error is something else since all I did was adding one column to the new type.

Could you help me identify, what is the root cause of this problem?

To anyone, that may come across this problem:
The issue was  nested dependencies.
You need to run sp_refreshsqlmodule starting from the bottom of the dependency hierarchy. If you try to refresh the Stored Procedure, that uses this table type and passes it further, you will get this operand type clash error


Can a production Oracle DB server also have a dataguard active standby for a different production database? If you have a production oracle database (PRIM1) protected using Oracle Dataguard to a standby (STBY1) physical server in a different data centre, the standby server needs to be a similar specification to the primary server, so that in the event of a disaster it can become the primary.

This setup means that you have redundant hardware 'just in case'.

If you have another production database (PRIM2), could you install this on the same physical server as STBY1 of the first application and install the standby for this application (STBY2) on the primary server of the first application (PRIM1).

This setup would mean maximising use of the hardware and in the event of a failover, both primary servers might be running on the same machine so you might need additional memory and/or CPU available to provision in the event of a disaster but you would be using the hardware most of the time.

Any thoughts on the issues a setup like this would create ?

Yes, no issue at all. This is done over and over. For instance two servers taking each others database(s) in standby mode.


backup and restore app for databases I'm looking for an open source app to backup sql server / mariadb / postgres.
Do you know which app can do that ?
I want to schedule backups with the app.
yes it's possible with only one app . I used to schedule backups for my local databases servers ( SQL Server, mariaDB, Postgres) on SQLServerBackupAndFTP (it's an app) but unfortunately it's not free. that's why i'm looking for an open source tool.
Thank you guys.

I'm looking for an open source app to backup sql server / mariadb / postgres.

One app?  Singular?
You won't find one.
Every DBMS does things ever so slightly differently from all the others.  Most significantly, each and every one has its own utilities for backing up and restoring its databases.
There is no "one size fits all" solution that will do everything.
As the Database Administrator, you have to know and understand each one of them.

I want to schedule backups with the app.

Scheduling needs a scheduling program.  Almost any one will do.
Backing up each DBMS needs the utilities for that DBMS.
Restoring any database needs the utilities for that DBMS and is, usually, a manual activity.


Understanding config variables in MySQL I have a database that is hosted on AWS Aurora. Now let's say it has been assigned particular memory, RAM, buffer pool size, etc.

I also have my.ini file on my local machine. What happens if I change the pool size to 16M in my.ini? Would that affect the performance of my query when I run on my machine? Or will it still use the buffer size set up in aurora?

My current variables are like this:

show variables;
innodb_buffer_pool_size 20915945472

Is this number from the aurora? My local my.ini file reads like this:

innodb_buffer_pool_size=2.5G

Settings

There is a pecking order of what config files are used.  I hesitate to say exactly what Aurora does, since it is offshoot of MySQL.  First will be /etc/my.chf of /etc/my.ini; last will be .my.cnf in your home directory.  (Note the leading dot.)  Later values will override earlier values.

Be sure to put things in the [mysqld] group in each file.

Be aware that the various settings become visible at different times -- after restart; after re-login; etc.  Also, note that some are "global", some are "session" (and initialized to the global value); etc.  Also note that some can be changed dynamically with a SET command; but, again, there are various variants due to global/session.

Buffer_pool

The main use of memory is for the buffer_pool.

Bigger = faster
Too big --> swapping, and slowdown, or even crash

The default for Aurora is probably optimal for the amount of RAM in the server.  Do not change it.  In the absence of a decent default, set it to about 70% of available RAM for MySQL.


mysql - Access denied for user 'user'@'localhost' (using password: NO) I'm trying to fix the infamous "Access denied" error, but I don't understand why my fix doesn't work. Can someone please help me?

EDIT: This is the sequence of steps I took to fix the problem. It is still not working. Can anyone tell me what I'm doing wrong?

$ sudo service mysql stop
mysql stop/waiting
$ sudo service mysql start
mysql start/running, process 5718
$ sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &
[1] 5758
$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 85
Server version: 5.5.32-0ubuntu0.12.04.1 (Ubuntu)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> UPDATE mysql.user SET Password=PASSWORD('test')
    -> WHERE User='root';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 4  Changed: 0  Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

$ rake db:create
Access denied for user 'root'@'localhost' (using password: NO)Please provide the root password for your mysql installation

This isn't a database problem at all. The tipoff is the "using password: NO" in the error message. Ensure that you're sending a password in your authentication as follows:

Command line

The correct syntax for the CLI client is:

$ mysql -uroot -ptest

Or you can just use -p (with no value), which will prompt you for a password. Note that there's no space between the flag (-u/-p) and the value (myuser/mypassword).

Rails

In your database.yml file, you should have:

adapter: mysql
database: my_db
hostname: 127.0.0.1
username: root
password: test
socket: /tmp/mysql.sock
pool: 5
timeout: 5000

Running rake db:create should now properly create your database.

See: ruby – How do I set up the database.yml file in Rails?


How to convert html entities in Oracle? I'm trying to decode html containing html entities.
I've tried dbms_xmlgen.convert and utl_i18n.unescape_reference but the results are less than satisfactory.

SET ESCAPE ON;
SELECT
   'dbms_xmlgen.convert' AS method,
   dbms_xmlgen.convert('\&hearts;', 1) AS hearts,
   dbms_xmlgen.convert('\&amp;',    1) AS amp_ent,
   dbms_xmlgen.convert('\&#0038;',  1) AS amp_dec,
   dbms_xmlgen.convert('\&#x0026;', 1) AS amp_hex,
   dbms_xmlgen.convert('\&#28608;\&#20809;', 1) AS chinese_laser
FROM dual
UNION ALL
SELECT
   'utl_i18n.unescape_reference',
   utl_i18n.unescape_reference('\&hearts;'),
   utl_i18n.unescape_reference('\&amp;'),
   utl_i18n.unescape_reference('\&#0038;'),
   utl_i18n.unescape_reference('\&#x0026;'),
   utl_i18n.unescape_reference('\&#28608;\&#20809;')
FROM dual;

The results I get are:

METHOD                        HEARTS      AMP_ENT     AMP_DEC     AMP_HEX     CHINESE_LASER
----------------------------------------------------------------------------------------------
dbms_xmlgen.convert           &hearts;    &           &#0038;     &#x0026;    &#28608;&#20809;
utl_i18n.unescape_reference   ¿           &           &           &           ¿¿

My actual problem involves Chinese characters, processed by a Java program to create PDF reports. I have no easy access to the Java code, but I do have control over the query used by the program.

An example of Chinese characters that I use for testing is 激光, which Google Translate tells me means 'laser' and which I receive encoded as &#28608;&#20809;. These aren't decoded properly, as can be seen in the example above.

I realise that in the second row, the inverted question marks seem to indicate that the entities were converted, but can't be displayed properly. However, is this Oracle itself doing this, or the client (I tried in both SQL+ and Toad)? When I plug utl_i18n.unescape_reference into the query used by the Java program, it works for entities like &#177; (±), but again, not for the Chinese characters.

How can I have all entities properly decoded?

Should I use another function? (These were recommended somewhere on the Internet).
Should I change some settings? (Relevant settings shown below).

Relevant information

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
SQL*Plus: Release 10.1.0.5.0

NLS_LANGUAGE                           AMERICAN
NLS_TERRITORY                          AMERICA
NLS_CHARACTERSET                       WE8MSWIN1252
NLS_NCHAR_CHARACTERSET                 AL16UTF16

The chinese characters are decoded correctly with utl_i18n.unescape_reference.  In fact they just aren't displayed properly in your query result which might not support those special characters.

You can confirm that with this SQL Fiddle.

It is the client which is respsonsible to display the characters appropriately.  If the client can not display a character it may show an upside down question mark, something else or just plain garbage.

So by now, the real question is where do you want to display this string...


MySQL unknown column in subquery I'm running a fantasy game where the ratings for each player are calculated in the following way:

Each round (gameweek) a player plays zero, one or more games. The rating for that round is the average rating scored in all games (stored in match_player)
The total season points for each player is the sum of all season round ratings.

I'm trying to list the season points for each player with this query:

SELECT
    `player`.`name`,
    COALESCE(
        (
        SELECT SUM(points)
        FROM
            (
            SELECT ROUND(AVG(`match_player`.`points`)) AS `points`
            FROM `match_player`
            JOIN `match` ON `match`.`id` = `match_player`.`matchID`
            JOIN `round` ON `round`.`id` = `match`.`roundID`
            WHERE `match_player`.`playerID` = `player`.`id`
               AND `round`.`competitionID` = 1
               AND `round`.`season` = 2018
               AND `match`.`status` = 'finished'
            GROUP BY  `match`.`roundID`
        ) AS roundPoints
    ), 0) AS seasonPoints
FROM `player`

Byt MySQL is throwing this error:

#1054 - Unknown column 'player.id' in 'where clause'

Is there any way to group by round, calculate average rating and the sum all values for each player?

SQL Fiddle: http://sqlfiddle.com/#!9/bebd2/1

Alternative approach:

SELECT
        `player`.`name`,
        COALESCE(
           SUM(
                (
                  SELECT ROUND(AVG(`match_player`.`points`)) AS `points`
                FROM `match_player`
                JOIN `match` ON `match`.`id` = `match_player`.`matchID`
                JOIN `round` ON `round`.`id` = `match`.`roundID`
                WHERE `match_player`.`playerID` = `player`.`id`
                   AND `round`.`competitionID` = 1
                   AND `round`.`season` = 2018
                   AND `match`.`status` = 'finished'
                GROUP BY  `match`.`roundID`
              )
        ), 0) AS seasonPoints
    FROM `player`

This time, the error returned is:

Subquery returns more than 1 row

http://sqlfiddle.com/#!9/bebd2/2

I've updated the SQL Fiddle with the reference answer

1) Referenced answer seems to have a misprint: -- Player 1 (Round 1: 5pts ((10+5)/2), Round 2: 6pts), whereas (10+5)/2 = 7.5 (or 8 while rounding to integer).

2) Look at

SELECT `player`.`name`, `match`.`roundID`, ROUND(AVG(`match_player`.`points`)) AS `points`
FROM player
JOIN `match_player` ON `match_player`.`playerID` = `player`.`id`
JOIN `match` ON `match`.`id` = `match_player`.`matchID`
JOIN `round` ON `round`.`id` = `match`.`roundID`
WHERE `round`.`competitionID` = 1
  AND `round`.`season` = 2018
  AND `match`.`status` = 'finished'
GROUP BY `player`.`name`, `match`.`roundID`
;

It fits your reference answer.


How to modify the below script to take tables and its sizes for a particular databse to send via mail to a recipient? SELECT top 10
    t.NAME AS TableName,
    (SUM(a.total_pages) * 8)/1024.0/1024.0 AS TotalSpaceGB
    FROM sys.tables t
INNER JOIN
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY
    t.Name, s.Name
ORDER BY
    TotalSpaceGB desc

My guess is the query in your question looks like PostgreSQL information_schema. The current database is assumed when you connect to it.

I believe you want the MySQL equilavent

Here is my suggestion for the Top 10 tables in the Current Database.

SET @lineitem = 0;
SET @topitem = 10;
SELECT line,tbl,units,dat,ndx,siz FROM
(SELECT
    (@lineitem:=@lineitem+1) line,tbl,
    CONCAT(SUBSTR('KMGTP',ex,1),'B') units,
    FORMAT(dt/POWER(1024,ex),2) dat,
    FORMAT(nx/POWER(1024,ex),2) ndx,
    FORMAT(sz/POWER(1024,ex),2) siz
FROM (SELECT *,FLOOR(GREATEST(1,
    LOG(GREATEST(dt,1))/LOG(1024),
    LOG(GREATEST(nx,1))/LOG(1024),
    LOG(GREATEST(sz,1))/LOG(1024))) ex FROM
(SELECT table_name tbl,data_length dt,
index_length nx,data_length+index_length sz
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY sz DESC) AAA) AA) A
WHERE line <= @topitem;

If you have a specific database in mind, like mydb, you would changed the subquery WHERE clause

SET @lineitem = 0;
SET @topitem = 10;
SELECT line,tbl,units,dat,ndx,siz FROM
(SELECT
    (@lineitem:=@lineitem+1) line,tbl,
    CONCAT(SUBSTR('KMGTP',ex,1),'B') units,
    FORMAT(dt/POWER(1024,ex),2) dat,
    FORMAT(nx/POWER(1024,ex),2) ndx,
    FORMAT(sz/POWER(1024,ex),2) siz
FROM (SELECT *,FLOOR(GREATEST(1,
    LOG(GREATEST(dt,1))/LOG(1024),
    LOG(GREATEST(nx,1))/LOG(1024),
    LOG(GREATEST(sz,1))/LOG(1024))) ex FROM
(SELECT table_name tbl,data_length dt,
index_length nx,data_length+index_length sz
FROM information_schema.tables
WHERE table_schema = 'mydb'
ORDER BY sz DESC) AAA) AA) A
WHERE line <= @topitem;

Give it a Try !!!


Can you set up mirroring for sql server reporting services? I have mirroring set up for our SQL Server 2008 R2 Standard database server.

Questions

How do I ensure high availability for reporting services if the server goes down?
Is there a kind of mirroring available for that as well?

Mirroring is for individual database.  For reporting services, you can user web server farms (AKA scale out deployment).  It will allow multiple servers in a cluster.  You will need SQL Server Enterprise Edition for this feature, though, as well as a load balancer (Windows has this or NW hardware like BigIP, etc.).  See here for more info:

http://msdn.microsoft.com/en-us/library/ms156453.aspx


Specify Import Row Range for SQL Server Task Wizard I would like to import a csv file into an empty table in SQL Server Developer 2016, using Task Wizard.  I know with BULK INSERT there are options to specify firstrow and lastrow; does Import Wizard have a similar feature?

The wizard only has the option to skip the first x rows...more complex than that would need to be a regular SSIS package, or some pre-processing of the file before the wizard picks it up.

Alternatively, import the whole file to a staging table and then trash the rows you don't need, or push the ones you do want into the correct destination table in a T-SQL statement.


SSRS Scale-Out Deployment: cannot decrypt the symmetric key I have a single 2008 ssrs instance (#1) up and running and need to add a 2nd server also running ssrs 2008 (#2). The error message on the 2nd server is:

  The report server cannot decrypt the symmetric key that is used to
  access sensitive or encrypted data in a report server database. You
  must either restore a backup key or delete all encrypted content.

Searching on that error, I find plenty of links and posts about backing up and restoring keys, but that seems to move the problem from server #2 to server #1. Server #2 will start working, but #1 will get the same error.

I found this regarding rskeymgmt.exe , but that seems to have no impact. The rskeymvmt command reports 'success', but even after restarting both instances of ssrs on each machine, nothing changes.

I have also attempted to follow these steps, but my #2 instance never shows up in the scale-out deployment tab in the configuration manager.

Any suggestions?

I work with Roger and the issue turned out to be after a little work that the second server was a cloned image that had been OBEd so it was a "different" server from the server SID and identity. However, the install of SQL 2008R2 SSRS was already on the server. The SSRS install creates a unique InstallationID (GUID) and this is used to identify the SSRS instance in a scale out of SSRS. So both servers have the same InstallationID and that means they both appear to be the same server and thus the key is the same for both and it cannot backup or restore the key since it conflicts with an existing key in dbo.keys in ReportServer DB. The InstallationID can be found in rsreportserver.config file, it will also be in the registry in many, many locations and trying to find and fix them is not a feasible choice.

The solution is to remove and then re-install the SSRS instance on the second server so that it will have a new InstallationID and crypto key. Then it can be added to scale out installation.


What happens if a delete query times out SQL Server for php? Technology

I am using the mssql extension to php (on linux) to send queries to SQL Server.

The Setup.

I have a table that builds up disposable data rather quickly. It took me longer than expected to build a cron to clean out this data every hour so it has been accumulating over months to be well over 1G of data not sure how many rows but probably over 1 million.

The Problem

So I ran a delete query for anything older than 1 day.

DELETE FROM CacheForms WHERE TimeStamp < 1503454349

However after 60sec the query times out and the script fails. (update the same happens at 10min.)

The Question

When this delete query times out (a php concept) what happens to the database? Does the database restore the deleted data from transaction log? or is the data truly deleted? Or does the query keep running in sql server?

When a timeout occurs, the client API sends an attention signal to SQL Server to cancel the executing query. Upon receipt, SQL Server cancels the query and rolls back the transaction. Data modification statements are run in autocommit mode by default so each statement is all-or-none.


Database Backup from 2008 R2, Restore on 2012 Failover Cluster Instance I have a production database server having sql server 2008r2. This is an old server and considering our load, we are moving to a better hardware configuration, Now the new environment is a cluster environment on Win Server 2012 with single installation of SQL SERVER 2012.

My understanding of failover cluster environment is that same instance of sql server is there on both nodes and they get data from a shared SAN. Primary node remains active while secondary node remains idle till the time the primary node fails. Once the primary node fails, windows brings the secondary node as active.

Now i want to restore backup of 2008r2 database to this new sql server 2012 environment. Does this failover cluster impose any restrictions on this backup restore or will it be a normal backup restore operation. Please share your thoughts.

Does this failover cluster impose any restrictions on this backup restore or will it be a normal backup restore operation.

It will be a normal backup and restore operation (provided there are no other issues with the actual backup and/or restore).

The fact that you are going from a standalone instance to a failover cluster instance will not negatively affect an otherwise normal backup and restore.

The same rules will apply here, of course, as well as any other backup/restore operation regardless of standalone or FCI.


Should address be in a separate table? I have a database that is for managing rental properties

It has a property table that has details about the property such as bedrooms, sq feet, price, etc, it also has a column called address_id which is a foreign key to the address table which contains the address like street name, city, etc

Is there any benefit of having the address in another table? Or can I move the address columns to the property table?

It depends on whether the relationship between property and address is 1:1.

Put another way: it depends whether two properties may reside at one address, or one property may have two addresses.

I can imagine, for example, a large worksite with multiple buildings. The address for the worksite might be 50 Smith St (one address). And I can imagine your property table having two rows linking to that address: one where property.buildingName = 'Workshed' and another where property.buildingName = 'Reception' (although I'm not sure you would rent a workshed separately to a reception, and if you did, they would probably be given separate addresses anyway).

If the above modeling of two properties at one address is possible with your business data, then I advise against merging your two tables.

On the other hand, if there is only ever 1 property per address, I think there is no real issue with merging your tables.

Other considerations:

If your database was managing millions of rental properties, there may be performance differences between the two designs. This would be an avenue to explore further.
If your application currently references two separate tables, there will be work converting your application to reference just the single table. This work might be trivial (if, say, currently, all references query a view and you only have to update your view) or monumental (if, say, you have 200 reports querying these tables and your data is synchronised with 5 other systems whereby you have to update your sync process and perform regression testing across all of those systems, even if they all belong to business clients). The prospective cost of the work to merge tables should also be taken into account.
If this is simply an exercise - for example, for a course on SQL - then either design might be fine, so long as you clarify the limitations of each (eg. only one property per address).

Here is a tutorial that introduces you to relational database normalisation - which is basically the process you need to go through in order to make your decision.


Postgres: count(*) vs count(id) I saw in the documentation the difference between count(*) and count(pk).  I had been using count(pk) (where pk is a SERIAL PRIMARY KEY) not knowing about the existence of count(*).

My question is about Postgres' internal optimizations. Is it smart enough to pick up that a SERIAL PRIMARY KEY is going to exist in every row and never be false and just count rows or will it do redundant predicate checks for each row?  I agree that this is probably too much of a pointless optimization but I'm just curious.

I took a look at the output of EXPLAIN and EXPLAIN VERBOSE for count(*), count(id) and count(id > 50) to see if EXPLAIN mentioned checking the predicates in its output. It doesn't.

I got consistent results in my repeated tests with various versions over the last years:
count(*) is slightly faster than count(pk). It is also shorter and most of the time it better fits what is tested: the existence of a row.

Concerning:

  Is Postgres smart enough to pick up that a SERIAL PRIMARY KEY is going
  to exist in every row and never be false

The only relevant thing is the NOT NULL constraint. The PRIMARY KEY is NOT NULL automatically, serial or never false are orthogonal to the question.

With count(col), if PostgreSQL was trying to be smart and check the system catalog whether a column was NOT NULL and fall back to an equivalent count(*), you'd still have one more look-up on a system table than with count(*).

As for EXPLAIN output, there is a hint:

EXPLAIN SELECT count(*) FROM ...

Aggregate  (cost=4963.38..4963.43 rows=1 width=0) ...

EXPLAIN SELECT count(pk) FROM ...

Aggregate  (cost=4963.38..4963.43 rows=1 width=4) ...

Meaning, count(col) is not converted to count(*), even if it's defined NOT NULL.


H2 Database - Alter multiple databases I need to alter a table in H2 db. But we have millions of databases with the same same table. What may be the feasible way to alter such databases?

Database migration

Database migration or schema migration are the evolving terms for the process of managing and deploying changes to the structure of your database.

The idea is that you never make ad-hoc changes to your database. Instead, you make a disciplined practice of accumulating scripts (SQL, XML, etc.), and optionally executable code, to implement each stage of your database’s evolution: adding tables, adding columns, altering columns, etc.

Tools such as Flyway and Liquibase automate the process of managing and executing your collection of scripts and optional code. If you deploy such a tool along with your database or app, the tool can automatically detect which scripts and/or executables need to be run to update the currently-used database to the latest and greatest.


Case-insensitive collation still comparing case-sensitive I am currently trying to create a table with a text column which will compare case insensitive by default. This is because we have a third party program that executes a search on our database. The SELECT statements used by this program can not be altered.

The abstract problem is that we somehow need this search to be case insensitive but it currently is case sensitive.

I read that Postgres 12 does support non-deterministic collations which allow for this behavior.

I have a Postgres Server (Version PostgreSQL 12.1, compiled by Visual C++ build 1914, 64-bit) installed on a German Windows machine.

So for testing purposes, I created a new database to test on:

CREATE DATABASE collation_test
    WITH
    OWNER = postgres
    ENCODING = 'UTF8'
    CONNECTION LIMIT = -1;

In this database I created the following collation I found in an article about these collations

CREATE COLLATION collat_ci (
  provider = 'icu',
  locale = 'und-u-ks-level2',
  deterministic = false
);

After this, I needed a table to test this collation with

CREATE TABLE public.person
(
    "Id" bigint NOT NULL,
    "Name" text COLLATE public.collat_ci,
    PRIMARY KEY ("Id")
);

ALTER TABLE public.person
    OWNER to postgres;

INSERT INTO person VALUES
(1, 'Robin'),
(2, 'robin');

So now I tried the following select query:

SELECT
(
    SELECT "Name" FROM person p1 WHERE p1."Id" = 1
)
= 'Robin';

Which returned true as expected as the text in the database and the literal given match completely.

But if I try the same with a lower case r which I would expect to match because of my collation but it still returns false.

SELECT
(
    SELECT "Name" FROM person p1 WHERE p1."Id" = 1
)
= 'robin';

When trying to compare both inserted rows' name to each other I still got false as a result:

SELECT
(
    SELECT "Name" FROM person p1 WHERE p1."Id" = 1 --'Robin'
)
=
(
    SELECT "Name" FROM person p2 WHERE p2."Id" = 2 --'robin'
);

Does anyone know why my comparison does not behave as expected and how to get it to do so?

The version of ICU shipped with the Windows builds is a pretty old one, so maybe that's the reason.

Try

CREATE COLLATION collat_ci (
  provider = 'icu',
  locale = '@colStrength=secondary',
  deterministic = false
);

This should work with older ICU versions.


Is there a way to grant SELECT for every database (including new ones) at a server level? I have a unique (well, I think it is unique) situation where databases (yes, entire databases) can be created more or less dynamically.

Ignoring the obvious issue with that, I need to be able to allow an AD account to read any of the data in any of the databases.

Acknowledging the security issue surrounding all of this, is there a way to grant this just once at the server level, rather than having to grant constantly when a new database pops up?
I would prefer to just keep it at a datareader level.

Specifically:
1. databases are created dynamically without advance knowledge of the DBA 2. Reporting jobs iterate through these databases and fail if they encounter a new one.

I am using SQL Server 2008R2 and SQL Server 2014.

Since you do not know the schema or tables names to grant them to, no to specifically doing it. You can add your AD account to the db_datareader role within the model database, and this should be copied into each database created on that instance.

In addition, if databases happen to get created via restore or anything other than create database statement you can use Policy Based Management rules to easily verify if your AD account is a member of that DB role and if not take an appropriate action.


Finding the odd row between two tables in Oracle I have two tables with the following schema

id, fruit

In TableA the values are like this:

A01, Apple
A02, Orange
A03, Pineapple
A03, Pineapple
A05, Banana

In TableB the values are like this:

A01, Apple
A02, Orange
A03, Pineapple
A04, Pineapple
A05, Banana

How can I reconcile these two tables to return just the duplicate row A03 in TableA? Like a one-to-one comparison of the tuples and returning the odd tuple.

I tried the minus query like below

Select * from TableA
minus
Select * from TableB

But that's not returning the result set I expected.

One-to-one based on what?

You have no reliable and permanent way of identifying/sorting rows in TableA. You have A03, Pineapple twice. Which will be the 3rd and which will be the 4th?

Sure, you can do the below, but forget this right away and fix your data. Rowids can change when moving data so the order is never guaranteed.

create table tablea(id varchar2(3 char), fruit varchar2(9 char));
create table tableb(id varchar2(3 char), fruit varchar2(9 char));

insert into tablea values ('A01', 'Apple');
insert into tablea values ('A02', 'Orange');
insert into tablea values ('A03', 'Pineapple');
insert into tablea values ('A03', 'Pineapple');
insert into tablea values ('A05', 'Banana');

insert into tableb values ('A01', 'Apple');
insert into tableb values ('A02', 'Orange');
insert into tableb values ('A03', 'Pineapple');
insert into tableb values ('A04', 'Pineapple');
insert into tableb values ('A05', 'Banana');

commit;

select
  a.id ida, a.fruit fruita,
  b.id idb, b.fruit fruitb
from
  (select t.*, row_number() over (order by id, rowid) as rn from tablea t) a
join
  (select t.*, row_number() over (order by id, rowid) as rn from tableb t) b
on (a.rn = b.rn and (a.id != b.id or a.fruit != b.fruit));

IDA FRUITA    IDB FRUITB
--- --------- --- ---------
A03 Pineapple A04 Pineapple


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

You may give it a try using percona backup alpha version for Windows - Download_Link
.

Below are the steps after installation in Windows Bash.

C:\Users\lorainne>bash

C:\Users\lorainne$ xtrabackup –user=root –password=secret -host=127.0.0.1 –port=3306 –backup –target-dir=\test

I haven't tested this but this seems to be a working command, ensure that you have https://msdn.microsoft.com/en-us/commandline/wsl/release_notes - Build 14332 fixed to have prctl system call supported on Windows.


Encoding problems with sqlplus, Linux I use sqlplus and all non-Latin characters appear as '?' when I run select queries. When I run insert or update queries with non-Latin characters I get question marks on a web page.

select * from nls_database_parameters; gives NLS_CHARACTERSET: AL32UTF8.
I've exported the NLS_LANG variable: export NLS_LANG=RUSSIAN_CIS.AL32UTF8, but this didn't make any changes.

What is the problem? Of course I use UTF-8 working in Linux terminal and I can see the script file contents executing cat script.sql.

If I execute alter session set nls_language='russian' I also face with question marks in ora-xxxxx messages.

since you confirmed the character set in the database is AL32UTF8, you need to make sure the fonts is installed on the Linux machine, and install the message libraries. that will make ORA_NLS33 has been set appear when you execute alter session set nls_language=


Need advice for a 'tag' table design My website has different content types. All content types should be tag-able (except if specified in the model, whatever). I can think of 2 options for my design:

2 tables option : 1 table for the tags (+one other, its i18n content) plus 1 table for the association (content_type , content_id, tag_id).

Pros :

Creation is faster
model parts for the tag is the same (so inherited model)
same thing for controlers.

Potential Cons :

Will the performance be poor?
will the table be too large soon ?
How to query for tags clouds (related to all grouped contents)?

many tables options :
1 table for tags, an association table per type of content.

Pros :

Tables will be shorter

Cons :

Have to customize each model or write a logic/footprint to query the right table
ugly long request / php code to join tags of all content types

Additional Facts :

Front side will have cache, tags are set by content writers.
There are 10 content types.
There won't often be new types of content.
Content count is ~500 , will ~double in 3 years.
MySQL engine InnoDB.
built with yii framework.
will query tags for a defined content type
will query tags for any type of content (together, one big tag cloud for example).

So my idea was to use the first design (2 tables). But I'm not a db expert ...
Would you recommand the second option ? Why ?

I would go for option 1 (two tables).

If you only plan to have 1000 content items (and in 3 years, not now), I don't think you will have many performance problems unless each content item is tagged with a dozen tags where most are unique to that item. And even then I don't think you'll see much of a performance problems. It also simplifies your structure if you have 1 tag table for every content type and then you decide to add/remove/change content types later.

If you're really not sure, do you have the time/resources to build two sample DBs, one based on each structure, populate them with what you think is a realistic load (you can write a script/program to generate & insert test data, right? with "boring" tags and content such as "content_1..content_2000 and tag_1... tag_5000) and actually test which performs better?


How Do I Filter a Group of Rows based on one Column's Value in TSQL? I am trying to make a SQL query in Server 2008 R2 that selects the rows only for employees who have over 40 hours in a pay week and a pay description that doesn't include "over time".  So far I have the following:

WITH RESULTS AS
(
SELECT
      [FullName]
  ,[PayHours]
  ,[SSN]
  ,[EffectiveDate]
  ,[PayDescription]
  ,[OrderNumber]
  ,[WeekStart]
  ,[WeekEnd]
  ,SUM(PayHours)OVER(Partition BY FullName,WeekStart) AS TotalHours

  FROM [viewReportPotentialOverTime]

  WHERE EffectiveDate BETWEEN '01/01/2015 00:00:00' AND '10/23/2015 23:59:59'
 Group By FullName,SSN,WeekStart, WeekEnd, EffectiveDate, OrderNumber, PayDescription, PayHours

  )
SELECT * FROM RESULTS
Having TotalHours > 40 --AND PayDescription !='Overtime Pay'
Order By FullName, WeekStart, EffectiveDate

This gets me close to what I need.  The problem I'm having is filtering out the rows for employees that do include a PayDesciption of 'Overtime Pay'.  When I uncomment the !='Overtime Pay' then it filters out just the one row for that pay week that was 'Overtime Pay' and leaves all of the Regular Pay rows. However, what I need is for it to filter out all rows for that pay week (which I used the WeekStart column to determine the pay weeks)for that employee. I'm using this as an audit to make sure whenever someone worked over 40 hours in a week that they were paid OT.

I've also tried a similar version using the HAVING clause, but the problem I run into there is that you can't have a windowed function in a HAVING clause.

Do you really need the GROUP BY, are those rows not distinct?

You can add a conditional CASE to find rows with 'Overtime Pay'

WITH RESULTS AS
(
SELECT
      [FullName]
  ,[PayHours]
  ,[SSN]
  ,[EffectiveDate]
  ,[PayDescription]
  ,[OrderNumber]
  ,[WeekStart]
  ,[WeekEnd]
  ,SUM(PayHours)
   OVER(Partition BY FullName,WeekStart) AS TotalHours
  ,MAX(CASE WHEN PayDescription ='Overtime Pay' THEN 1 ELSE 0 END)
   OVER(Partition BY FullName,WeekStart) AS OvertimePayFlag

  FROM [viewReportPotentialOverTime]

  WHERE EffectiveDate BETWEEN '01/01/2015 00:00:00' AND '10/23/2015 23:59:59'

   -- really needed?
  Group By FullName,SSN,WeekStart, WeekEnd, EffectiveDate, OrderNumber, PayDescription, PayHours
  )
SELECT * FROM RESULTS
WHERE TotalHours > 40  -- no HAVING needed
  AND OvertimePayFlag = 1
Order By FullName, WeekStart, EffectiveDate


Debug why Postgres wants to use an uninstalled extension for a function I recently moved a Postgres function app_user_id from an old, custom extension called intpair into a new, custom extension called heap_exts. This worked well except for machines I restored from a Postgres backup.
Here's the error:
db=> select app_user_id(1, 2);
ERROR:  could not access file "$libdir/intpair": No such file or directory

intpair is not installed and heap_exts is installed as shown by the output of \dx:
-[ RECORD 3 ]-------------------------------------------------------------
Name        | heap_exts
Version     | 1.0
Schema      | public
Description |

-- No mention of intpair

The function app_user_id is listed in the system catalogs as being owned by heap_exts:
SELECT e.extname, ne.nspname AS extschema, p.proname, np.nspname AS proschema
FROM pg_catalog.pg_extension AS e
INNER JOIN pg_catalog.pg_depend AS d ON (d.refobjid = e.oid)
INNER JOIN pg_catalog.pg_proc AS p ON (p.oid = d.objid)
INNER JOIN pg_catalog.pg_namespace AS ne ON (ne.oid = e.extnamespace)
INNER JOIN pg_catalog.pg_namespace AS np ON (np.oid = p.pronamespace)
WHERE d.deptype = 'e'
  AND e.extname IN ('heap_exts')
  AND p.proname = 'app_user_id'
ORDER BY 1, 3;

-[ RECORD 1 ]----------
extname   | heap_exts
extschema | public
proname   | app_user_id
proschema | public

I initially migrated the function from the old extension intpair into the new extension heap_exts with the following code:
-- version 0.1 in heap_exts--0.1.sql
ALTER EXTENSION intpair  DROP FUNCTION app_user_id(bigint, bigint);
ALTER EXTENSION heap_exts ADD FUNCTION app_user_id(bigint, bigint);

However, for the new machine restored via backup from the old machine, I created the app_user_id function directly because the old extension intpair no longer exists:
CREATE FUNCTION app_user_id(BIGINT, BIGINT) RETURNS app_user_id
AS 'MODULE_PATHNAME', 'app_user_id_make' LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;

Why is Postgres trying to look in the intpair extension for the app_user_id function instead of looking in heap_exts?

Why is Postgres trying to look in the intpair extension for the app_user_id function instead of looking in heap_exts?

It sounds like you still have (or had) an entry in pg_proc for app_user_id(bigint, bigint) which points to the old function definition (probin would be $libdir/intpair).
Assuming there is an entry there, I'd guess it was somehow not properly associated with the old intpair extension (maybe manually added, or the code pre-dates PostgreSQL's extension functionality and the function was missed from CREATE EXTENSION FROM unpackaged or something) so was not removed when the extension was dropped, so is still present in your restored instance - but the associated extension library file no longer exists, of course.
The question is whether that pg_proc entry has somehow ended up associated with your new extension, or you have two entries in pg_proc for the function.
What does this modified version of your above query report?
SELECT e.extname, ne.nspname AS extschema, p.proname, np.nspname AS proschema, p.probin
FROM pg_catalog.pg_extension AS e
INNER JOIN pg_catalog.pg_depend AS d ON (d.refobjid = e.oid)
INNER JOIN pg_catalog.pg_proc AS p ON (p.oid = d.objid)
INNER JOIN pg_catalog.pg_namespace AS ne ON (ne.oid = e.extnamespace)
INNER JOIN pg_catalog.pg_namespace AS np ON (np.oid = p.pronamespace)
WHERE d.deptype = 'e'
  AND p.proname = 'app_user_id'
ORDER BY 1, 3;


MariaDB Galera Cluster - first node won't reconnect after restart... Ever Firewalls disabled, replication worked fine, IP's accessible..

All config copied from here:
https://www.digitalocean.com/community/tutorials/how-to-configure-a-galera-cluster-with-mysql-5-6-on-ubuntu-16-04

As soon I try to restart the first node it won't reconnect.

I cannot restart the whole cluster as it supposed to be serving content 24/7 with at least 3 nodes available at all times...

Any idea how I can setup Galera so it would survive a restart of ANY node? (restart of a cluster is a pretty much fatal crash as leads to losses going in xx k of $ and the main reason behind selecting Galera was the low cost vs Microsoft (which can survive without a problem any number of resets while setup correctly)...

As I am trying to push Galera instead of Windows, I have to prove that indeed it is a commercially viable solution (which means that it have to survive main node restart, especially that we have dozens of "main" servers so "first" node is strictly theoretical).

I was testing in ~ 2 weeks ago and unfortunately, it ended up with a restart and data loss (due to having test servers offline)

With not so much detail in the question, I can't give a detailed answer, but I'd recommend starting with MariaDB's documentation about Galera Cluster since you're using MariaDB. See e.g. Getting Started with MariaDB Galera Cluster. The DigitalOcean documentation you referred to is for MySQL 5.6, so there may be details in there that works slightly differently on MariaDB.

Maybe the most crucial question about your specific problem is: What error message are you getting? And I mean the actual error message in the MariaDB error log, which is not necessarily what systemctl will show you in the shell when you run the start/restart/status commands. Once you have this, try your favourite search engine to find the answer. If that doesn't work, come back to DBA.SE and ask a more specific question which includes the error message(s).

Galera Cluster is an excellent technology, but if you don't have much experience with it yet, make sure to read carefully about its limitations, and make sure they're not in conflict with your particular use-case. There is a lot to learn, so be prepared to invest sufficient time and effort.


Can't start mongodb server using 'sudo service mongod start' I'm trying to set up a dummy mongodb server on my local device so I can play around with it and make mistakes so that I can feel confident editing the actual server. I use the following code to start the mongodb server:

 sudo service mongod start

However executing sudo service mongod status outputs the following:

   ● mongod.service - MongoDB Database Server
       Loaded: loaded (/lib/systemd/system/mongod.service; disabled; vendor preset: enabled)
       Active: failed (Result: exit-code) since Wed 2018-08-29 15:46:44 ACST; 754ms ago
         Docs: https://docs.mongodb.org/manual
      Process: 14604 ExecStart=/usr/bin/mongod --config /etc/mongod.conf (code=exited, status=62)
     Main PID: 14604 (code=exited, status=62)
          CPU: 1.889s

    Aug 29 15:46:42 david-Aspire-A515-51G systemd[1]: Started MongoDB Database Server.
    Aug 29 15:46:42 david-Aspire-A515-51G mongod[14604]: 2018-08-29T15:46:42.435+0930 I CONTROL  [main] Automatically disabling TLS 1.0, to force-enable TLS 1.0 specify --sslDisabledProtocols 'none'
    Aug 29 15:46:44 david-Aspire-A515-51G systemd[1]: mongod.service: Main process exited, code=exited, status=62/n/a
    Aug 29 15:46:44 david-Aspire-A515-51G systemd[1]: mongod.service: Unit entered failed state.
    Aug 29 15:46:44 david-Aspire-A515-51G systemd[1]: mongod.service: Failed with result 'exit-code'.

Tried adding sudo service mongod start --sslDisabledProtocols 'none' to no avail.
Any help would be much appreciated

Running following code in terminal fixed my issue for error 62:

mv /var/lib/mongodb /var/lib/mongodb_backup
mkdir /var/lib/mongodb
chmod 700 /var/lib/mongodb
chown mongodb:daemon /var/lib/mongodb
systemctl restart mongodb


Running pg_dump on a hot standby server? Disclaimer: I admittedly haven't tried this yet, but I'm not sure I would know if it wasn't working correctly, so I wanted to ask.

I would like to run a nightly backup job (via pg_dumpall) from a hot standby server running streaming replication, to avoid putting that load on the primary. I've only seen mention of some gotchas people have run into, e.g. here and here, but very little guidance. It's okay if the backup lags behind the primary slightly, as long as it's consistent (which it should be).

My questions are:

Do I really want to do this, or should the backup be done on the primary server? Why?
When doing a dump on the standby, what settings do I need and procedure should I use to do that correctly? e.g. must I stop replication for the duration of the backup?

AFAIK, running pg_dump on a hot standby is one of the major things standbys are useful for. It's perfectly safe, though it isn't perfectly reliable - the dumps can fail if the standby aborts the transaction when it's falling too far behind the master.

The only thing you really need to watch is to make sure the standby is current and is keeping up. If the standby lost its connection to master and fell too far behind, you don't want to be merrily backing up a three week out-of-date standby.

You will need to allow the standby to fall quite far behind the master during the backup, since it'll otherwise have to cancel your pg_dump transaction in order to continue replaying WAL. See the documentation on hot standby, particularly the "handling query conflicts" section, and the max_standby_archive_delay and max_standby_streaming_delay parameters.

Note that the master must be willing to keep enough WAL archives to allow the slave to catch up again.


Permission denied in Postgres I added the user myuserto Postgres.

Then I added the database mydatabase in the pgAdmin III GUI and restored from a backup file. So the owner of mydatabase is the superuser postgres.

Then I tried to give all rights to access and modify mydatabase to myuser. I logged into psql as user postgres:

psql -d template1 -U postgres

and then I ran this query:

GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser

Now I can use myuser to log, but if I try a simple query I get this error:

ERROR:  permission denied for relation table_name

Am I missing something? Can you help me solve that?

You've granted CREATE, CONNECT, and TEMPORARY privileges on the database to myuser but you haven't granted SELECT and INSERT table privileges yet. You'll need something like:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser;

In addition you need privileges on sequences if you have any serial columns or other column defaults drawing from sequences. Generally, the USAGE privilege is be enough for INSERT operations to work, but since you asked to "give all rights":

GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO myuser;

Details on GRANT in the manual.


sp_Blitz - RegOpenKeyEx() returned error 2 I'm running the latest sp_Blitz script on a SQL 2016 Std server on a Windows Server 2016 physical box. I'm getting the following error. What could be causing this and how do I troubleshoot it?

RegOpenKeyEx() returned error 2, 'The system cannot find the file
specified.' Msg 22001, Level 1, State 1 Warning: Null value is
eliminated by an aggregate or other SET operation. Msg 0, Level 11,
State 0, Line 0 A severe error occurred on the current command.  The
results, if any, should be discarded.

My guess is that it's related to this Github issue. Another user reported it, but because I can't quickly reproduce it, and the reporting user didn't want to submit code to fix it, we ended up closing the issue.
If you'd like to help troubleshoot and fix that issue, you can make notes in that Github issue, and use the First Responder Kit's Contributing Guide to get started with the code.


"Recover" data from running PostgreSQL instance; data directory was deleted I accidentally removed data directory /var/lib/pgsql/9.1/data from running postgresql server.

I guess I'm going to lose my data when server will be restarted.

I'm wondering if postgresql has any tools to recover data from running server instance or I have to use system data recovery tools?

I am afraid your chances are somewhere between infinitesimally small and zero.

There are a lot of files in that directory and it's sub-directories. Any which were actually open by one of the postgresql backend processes at the time you deleted the directory, and have not been closed since, are still present in the filesystem, but any that were not are long gone. So the best you would be able to hope for is to recover some of the files, and the database is unlikely to be able to start up properly with missing files.

Assuming a Linux system, you can see which files are actually open (and therefore in theory at least still recoverable) using this command:

lsof -u postgres | grep /var/lib/pgsql

You will see entries such as:

postmaste 2418 postgres 5u REG  253,0 8192 274804 /var/lib/pgsql/9.2/data/global/5445770

The second column is the PID of the process, and the numeric part of the 4th column is the FD (file descriptor) number. The file can be recovered using a command like:

cp /proc/{PID}/fd/{FD} {dest_file}

replacing the {PID}, {FD} and {dest_file} as appropriate.

However, as I said, your chances of recovering ALL the lost files, or even enough of them to be able to start the database up again afterwards, are very very slim. Even the ones you do recover may be corrupted, as the other process which has them open will be writing to them at the same time.

Instead, it might be worth trying pg_dump. It is likely to fail to dump the whole database, but if you are lucky you might recover some of it that way. If there are some tables which are more important to you than others, and it appears that the running database is still able to access them, then you might try using pg_dump to dump only those tables.

It is possible (but I would say quite unlikely) that you could restore the underlying files using system file recovery tools.


SSIS: What services to restart so Folder Permissions Propogate to username executing pacakge? I created an SSIS package that needs access to a folder on a shared drive.

The package runs successfully on my local machine but I get an access denied error when I run it on a different server using a different username.

I created a ticket for IT to give access permissions to the user name that is executing the package but the package was still failing with same access to the path is denied error.

I was told by IT that I would need to restart the services that the (windows) username was using so that the changes would propagate.

My questions is, what services would I need to restart so that the username will have the new access rights propagated to it so that my package will run (with access to the folder)?

Do I need to restart the server that the package resides on? Does the server that contains the shared folder need to be restarted? Does some SQL Server service need to be restarted? What is the easiest way to have the permissions propagate to the username?

We were able to get the changes to propagate by restarting just the 1 service that was being used by our DWH (under the username we wanted to give access to). So I guess the answer to my questions is that you only have to restart the 1 specific service that the username invokes when running the package. Restarting the entire server was not necessary.

I would have liked to have written a more detailed answer but the process was done by someone else and I was just watching as they did it so I did not get a chance to see exactly what service they restarted but I recall seeing Data Warehouse in the service name.


Select Into a table on server1 from server2 without using 'Linked Servers' I am trying to get all the records from one table into another table but these tables (which are identical) are on separate servers.

Any help would be appreciated.

Thanks

If your goal is just a one time transfer, the quickest method would be to use SQL Server Import and Export Wizard (Right click on the database -
    Tasks - Export Data)

And here is SQL Server Management Studio Express (64-bit) if you don't have one already.


Oracle 9i historical long queries since instance startup please I would like to know any query that shows the queries or the top 10 queries that take more time to finish since the beginning of the instance with user and the sql sentence, I have seen several that show those that are currently running and active but can not find any that show a historical of these queries since the instance was started, it is usually very simple from Toad with ADDM/AWR Reports but the database in question is Oracle 9i and does not give that option. Thank you

Dynamic performance views are not guaranteed to have all SQL information since instance startup because of the limited amount of memory.

Oracle 9i does not have AWR.

You can use Statspack, but it is not installed by default.

Install it as SYSDBA with:

@?/rdbms/admin/spcreate

Creating a snapshot (does not happen automatically, this needs to be scheduled manually or with spauto.sql):

EXECUTE statspack.snap;

Creating a report:

@?/rdbms/admin/spreport

More details can be found in the documentation following the above link.


Oracle ASM : Alter asm_diskstring when ASM Instance is Down, I am beginner for Oracle ASM
I know we can alter asm_diskstring with following ways

SqlPlus
ASMCA

but these requires +ASM to be UP and running. Is there way to set asm_diskstring when +ASM instance is down ?

Thanks,

You can create pfile to set asm_diskstring.

$ vi asmpfile.ora
instance_type=asm
asm_diskstring=path_to_asmdisks

Then can start asm instance using this pfile and later can create spfile from pfile.


Use both b-tree and BRIN indexes in PostgreSQL I have rather large tables (> 1000 M rows) where I have to do quick lookups. They are typically indexed with composite indexes to enable index-only scans like this CREATE INDEX table (study, analysis, gene, sample) INCLUDE (value) where a study can contain several analyses, which contain many genes, each of which contains measurements for some samples. One type of query would be to get the value for a given gene in all samples in an analysis. This would return ~500 rows. Another type of query would return values for all genes and samples in an analysis. This would return millions of rows. I wonder if it would make sense to also include a BRIN index on study, analysis to speed up such queries. It would then be redundant with the existing b-tree index. Is this bad practice that just adds overhead and complexity, or do you think it will be used by the scheduler like I want?
EDIT: I forgot to mention that the results will typically not be cached, so it would have to read from disk.

Your index on (study, analysis, gene, sample) INCLUDE (value) would still be usable for a query like select gene, sample, value from t where study='PNAS908' and analysis = 'the correct one'.  And will probably be better than BRIN, or at last not worse by enough to matter.
Note that if "analysis" is just labelled in a low-entropy way within a study (a, b, c, 1, 2, 3, log, linear, sqrt, intervention, SOC, placebo, ...), such that the same analysis identifier is reused from study to study, then the multi-column BRIN index is unlikely to do what you want.  The columns in a BRIN index are not hierarchical like they are within a btree index, they are each independent.


Empty Strings: Why or when is '' equal to ' '? Who can explain why

select case when '' = ' ' then 1 else 0 end, LEN(''), LEN(' '), DATALENGTH(''), DATALENGTH(' ');

yields

----------- ----------- ----------- ----------- -----------
1           0           0           0           1

The funny consequence of this is that in

create table test ( val varchar(10) );
insert into test values( '' );
update test set val = ' ' where val = '';

the update will indeed replace the empty string by a blank, but the where clause keeps to be true and repeated executions of the update statement tell

(1 row(s) affected)

Trailing blanks explained:

  SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2,
  , General rules #3) on how to compare strings
  with spaces. The ANSI standard requires padding for the character
  strings used in comparisons so that their lengths match before
  comparing them. The padding directly affects the semantics of WHERE
  and HAVING clause predicates and other Transact-SQL string
  comparisons. For example, Transact-SQL considers the strings 'abc' and
  'abc ' to be equivalent for most comparison operations.

  The only exception to this rule is the LIKE predicate. When the right
  side of a LIKE predicate expression features a value with a trailing
  space, SQL Server does not pad the two values to the same length
  before the comparison occurs. Because the purpose of the LIKE
  predicate, by definition, is to facilitate pattern searches rather
  than simple string equality tests, this does not violate the section
  of the ANSI SQL-92 specification mentioned earlier.

Here's a well known example of all the cases mentioned above:

DECLARE @a VARCHAR(10)
DECLARE @b varchar(10)

SET @a = '1'
SET @b = '1 ' --with trailing blank

SELECT 1
WHERE
    @a = @b
AND @a NOT LIKE @b
AND @b LIKE @a

Here's some more detail about trailing blanks and the LIKE clause.


Configure RAM for SQL Server and NUMA Accidental DB admin here.

Question:

Would you still manually limit max RAM for SQL Server even though the sole purpose of that server is to serve SQL Server database engine only?

I have Microsoft Windows 2012 R2 Server, SQL Server 2012 Std, with 32GB RAM.

Admin constantly login to the SQL Server to use its console and all.

Secondly, how do you check if this Windows server is NUMA enabled?

Would you still manually limit max RAM for MSSQL eventhough the sole purpose of that server is to serve MSSQL only?

That is bit debatable topic considering you have SQL Server 2012 here. If you would have asked same question about SQL Server 2005/2008, which was on Windows Server 2003/2008, I would strongly suggest to set a max server memory limit due to bugs which were there in Windows operating system(2003/2008). But memory configuration and OS performance changed significantly from SQL Server 2012 onwards and Window Server 2008/2012 r2 onward respectively, if you ask my opinion, You can leave max server memory configuration to default if you have enough RAM on system and you are just using database engine features. If you ask MS engineers about this they would also say that SQL Server is coded well to manage memory by itself and if you have system dedicated just to SQL database engine you can leave SQL Server max server memory to default SQL Server will manage it efficiently.

Anyways if you still want to configure you can read Similar question and answers given On this SE Thread

There are situation where max server memory is required I have outlined that in This SE thread

  Secondly, how do you check if this Windows server is NUMA enabled?

A little search would lead you to this Blogs.msdn article. It has various pictorial description when SQL Server is NUMA aware and is using it.


Oracle Undo tablespace users Can anybody help me on "what all users are using the undo tablespace and how much" in oracle database 11g r2. any pointers would be much appreciated.

This shows you the amount of undo size used by each session that is currently active.

select s.sid,
       s.username,
       sum(ss.value) / 1024 / 1024 as undo_size_mb
from  v$sesstat ss
  join v$session s on s.sid = ss.sid
  join v$statname stat on stat.statistic# = ss.statistic#
where stat.name = 'undo change vector size'
and s.type <> 'BACKGROUND'
and s.username IS NOT NULL
group by s.sid, s.username;


How to restore a .backup file from a single table on postgres? I have a .backup dump from a single table, but it didn't work restore like a normal backup from an entire db. I'm creating this backup file with the following batch script bin\pg_dump -h localhost -p 5432 -U postgres -F c -b -v -f %BACKUP_FILE% --table <schema.table-name> <db-name> And i'm restoring it with the pgadmin restore option, i didn't get any error message, the restore run normally, but the table don't show on my db/schema.

I am not sure what you tried so far, please post error message for better understanding,

Try below and post a comment if this works for you or not.

you can use pg_restore to restore a single table :

pg_restore -t my_table -d database_name database.dump

If the dump is not too big, the easiest thing to do is restore the full backup to a temporary database (gzcat backup_file.gz | psql -h host -U user database_name) dump the one table (pg_dump -t my_table), then restore it.


InnoDB file per tablespace I am using following syntax to create an InnoDB table:

create table InnoDB_Test(a INT)
DATA DIRECTORY='/var/log/storage/MySQL'
INDEX DIRECTORY='/var/log/storage/MySQL' engine=InnoDB;`

I want to create table which can store data over mounted partition(/var/log/storage i.e. /dev/sda6), but it is storing the data in /var/lib/mysql.

Files related to table i.e. .idb and .frm files are created there. It is not creating .isl file and neither the directory which is to be created in the directory mentioned.

But if I create the table with MyISAM engine then it is working fine. All files are created and data is getting stored in mounted partition.

I have enabled innodb_file_per_table, have_symlink global variables.

I am using 5.5.44-MariaDB MariaDB Server, CentOS 7.1

My partitions are as Follows:

/dev/sda2       6.5G  3.6G  2.6G  59% /
/dev/sda1       395M   32M  338M   9% /boot
/dev/sda6       5.3G   22M  5.0G   1% /var/log/storage`

So please tell me how to store data of InnoDB table in mounted partition?

What you did applies to tables one at a time.  Instead...

Plan A:  Change datadir when installing.  (See documentation for details and caveats.)

Plan B:  Symlink .../mysql/data to some place in the other partition.

(And remove DATA_DIRECTORY, etc from CREATE TABLE.)