texts:
I'm getting the following error :

Conversion failed when converting the varchar value 'Jan  1 1900 12:00AM' to data type int.

I have tried casting the problematic field which is in my third CTE(CTE4)

WHEN max(Date_Last_Data_Changed )= '1900-01-01 00:00:00.000' THEN 'Has never been changed'

I have looked at different places

Conversion failed when converting the varchar value to data type int.

Conversion failed when converting the varchar value 'simple, ' to data type int

But still was not able to figure out this problem

 ;with CTE1 AS (
    Select Year_Last_Accesed,
    (Count(Year_Last_Accesed)* 100 / (select Count(*) From _Admin_Database_Objects_Analysis_Tbl)) as Percentage_Result
    From _Admin_Database_Objects_Analysis_Tbl
    Group by Year_Last_Accesed
    ),

     CTE2 AS(
    Select Year_Last_Accesed,
    (Count(Year_Last_Accesed)* 100 / (select Count(*) From _Admin_Database_Objects_Analysis_Tbl)) as [10 Years older Tables]
    From _Admin_Database_Objects_Analysis_Tbl
    Where Date_Created > DATEADD(year,-10,GETDATE())
    Group by Year_Last_Accesed
    )

    ,
    CTE4 AS (

    SELECT Object_Name,
           MAX(Date_Last_Data_Changed) as [Last Time Data Changed]
           ,CASE
                WHEN max(cast(Date_Last_Data_Changed as varchar(255))) >1900 THEN 'Has Been changed recently'
                WHEN max(Date_Last_Data_Changed )= '1900-01-01 00:00:00.000' THEN 'Has never been changed'
                end as New_Date_Last_Data_Changed
           FROM _Admin_Database_Objects_Analysis_Tbl
           GROUP BY Object_Name

    ),

    CTE5 AS (
        SELECT
        ct4.New_Date_Last_Data_Changed
        ,(Count(New_Date_Last_Data_Changed)* 100 / (select Count(*) From _Admin_Database_Objects_Analysis_Tbl)) as [Data Changed %]

        FROM _Admin_Database_Objects_Analysis_Tbl ct5
        Inner Join CTE4 ct4
        on ct4.Object_Name = ct5.Object_Name
        Group by ct4.New_Date_Last_Data_Changed
    )
    Select Percentage_Result,
            case
            When c1.Year_Last_Accesed  = '1900' then 'Never Used'
            When c1.Year_Last_Accesed  = '2018' then '2018'
            end as Year_Last_Accesed
            ,[10 Years older Tables]

     FROM CTE1 c1
     INNER JOIN CTE2 c2
     ON c1.Year_Last_Accesed=c2.Year_Last_Accesed
     INNER JOIN CTE5 CT5
     ON ct5.New_Date_Last_Data_Changed = c1.Year_Last_Accesed


texts: operand should contain 1 column Use Variables in SQL statement in Python

texts:
Trying to insert four columns and multiple rows with variables.
The table in mysql has 8 columns in total.

Four columns to insert is as following:

report_type= tuple(("ada","asda","frfe","adaaa","adaddd","fdgg"))
request_id = tuple(("12532532","22532432","3253263","424323","243255","6234532"))
situation = tuple(['IN_PROGRESS_' ]* len(request_id))
country =tuple(['us']* len(request_id))

The module I'm using is pymysql, and python version is 2.7
inserting code is as following：

sqlstr ="INSERT into info_request(request_id,country,report_type,situation) VALUES(%s, %s, %s, %s)"
cursor.execute(sqlstr, (request_id,country,report_type,situation))

These query supposed to be work, but It returns Internalerror: (1241, u'Operand should contain 1 column(s)')
I searched on this error for the whole day, but still can't solve it.
Please help.


texts: Can I identify duplicates in a large MySQL insert?

texts:
I currently have a PHP script that inserts thousands to millions of rows into an InnoDB table.  To prevent duplicates, the table has a UNIQUE index set up on the combination of four columns, and I use INSERT IGNORE to allow the insert to continue regardless of duplicates.

For performance reasons, rather than inserting one row at a time, I batch them up into 2000 rows per query.  I want to know which individual rows are ignored due to a key violation, and the only way I can think to do it is to insert one row at a time and then check the value of mysqli_affected_rows after each insert, but that feels inefficient and I will lose the advantage of batching my inserts.  Alternatively I could remove the UNIQUE index and retrospectively check for duplicates using some SQL at the end.

Any other suggestions?

Many thanks.


texts: Need to include FKs in SQL Indexed View clustered index?

texts:
I'm using a SQL Server Indexed view to improve performance of a join that filters a (very) large table by a category on a small table. eg:

CREATE VIEW BigTableSubset WITH SCHEMABINDING
AS
SELECT b.ID, b.SomeValue from BigTable b
INNER JOIN SmallTable s on (b.CategoryFK = s.CategoryPK)
where s.Type = 'Blah'

CREATE UNIQUE CLUSTERED INDEX PK_BigTableSubset ON BigTableSubset
(ID)
INCLUDE (SomeValue)

(BigTable.ID is the PK on BigTable)

Query performance is substantially increased (since the subset of bigtable required is already stored), but I'm concerned to ensure that SQL can update the index itself in the most optimal way when either of the source tables is updated.

So the question is: do I need to include SmallTable.CategoryPK in the view's clustered index to ensure this, or will SQL do this automagically as part of the index structure? Or to put it another way, how does SQL identify what pages in the index need updating every time a 'left hand' (SmallTable) table updates, and do I need to do anything to facilitate that?


texts: Thoughts about Oracle backup procedure for 11.2 (beginner question)

texts:
Two days ago we "found out" that our old, not taken care of, horribly neglected, Oracle DB server was extremely important to our business.

The only person who knew anything about this server was fired 2 years ago, so it's been running since without maintenance.

Being both relatively new in the office as well as in charge of the whole server infrastructure, I need to get to know this server, with zero Oracle experience.

My questions to you are:

Backups: I've read up a lot about RMAN, Import/Export etc. but I'm having a hard time finding a good overview.  What would I need to backup and how do I restore said data if the machine blows up?
Maintenance: is there something about maintenance that I specifically need to know?
Labbing: is it possible for me to download a copy of this and learn on my local linux machine?

If you think these tasks, for me without any experience, are to daunting, you're free to say so because we've been looking at hiring a consultant for this specific situation. Obviously I'd rather like to learn and solve it myself.

Any pointers to documentation, manuals etc. are very much appreciated!

[I'm not too sure if this is a DBA or ServerFault question actually, I believe it's a very thin line between the two but if you feel this thread is misplaced, feel free to move it.]


texts: How can I drop an index on all partitions?

texts:
If I create an index on a partitioned table, each partition gets its own index. These are not dropped if I drop the main index.

Is there an easy way to do this?

I have written a function that looks for all the indexes that match the top level index name with a partition suffix, and that works fine for indexes that were created on partitions.

However, when Greenplum adds a new partition through a default partition split, it generates a new index for that partition with a totally different naming convention.

Existing partitions get a name like indexname_1_prt_partitionname

New partitions get a name like tablename_1_prt_partitionname_indexcolumn

Any idea how I can identify that the new partition index is part of the parent index, when the name doesn't match? Or do I just call my index deleter twice with two different patterns to match?

I will be using either Bell's query below, or this one which is adapted to take a partition index (in case we have already deleted the head index):

SELECT child_index.indexrelid::regclass
  FROM pg_index AS partition_index
    -- Find the partition that the partition index is on
    INNER JOIN pg_partition_rule parindex_rule ON parindex_rule.parchildrelid = partition_index.indrelid
    -- Follup up to the partitioning scheme
    INNER JOIN pg_partition ON pg_partition.oid = parindex_rule.paroid
    -- Follow the links through to the individual partitions
    INNER JOIN pg_partition_rule ON pg_partition_rule.paroid = pg_partition.oid
    -- Find the indexes on each partition
    INNER JOIN pg_index AS child_index ON child_index.indrelid = pg_partition_rule.parchildrelid
      -- Which are on the same field as the named index
      AND child_index.indkey = partition_index.indkey
      -- Using the same comparison operator
      AND child_index.indclass = partition_index.indclass
  -- Filtered for the index we're trying to drop
  WHERE partition_index.indexrelid = 'schema.partitionindexname'::regclass


texts: help me to migrate single myslq5.6 server to mysql5.6 Cluster, follow this error!

texts:
I try to migrate a single myslq5.6 server to mysql5.6 Cluster. I do not understand why I get the follow this error:

  ERROR 1297 (HY000) at line 1 in file: 'load_dept_emp.dump': Got temporary error 233 'Out of operation records in transaction coordinator (increase MaxNoOfConcurrentOperations)' from NDBCLUSTER


texts:
I'm new to Oracle and database administration in general.

As context, I want to create a Java class that will give me the information I would get using DESC SOME_TABLE.

I cannot find any specific way to do that in Java, however I found that ALL_TAB_COLUMNS could give me similar info.  I tried it in SQL Developer to see how different the output was.  It turns out the results are a lot more different that I was expecting.

I was hoping someone could walk me through how to interpret the following:

desc SOME_TABLE;

select
       COLUMN_NAME
     , DATA_TYPE
     , DATA_LENGTH
     , NULLABLE
  from ALL_TAB_COLUMNS
 where TABLE_NAME='SOME_TABLE'
 order by column_id;

Gives the output:

Name            Null    Type
--------------- ------- ----------------------
UIDPK                   NUMBER(20)
NAME                    VARCHAR2(255)

2 rows selected

COLUMN_NAME      DATA_TYPE     DATA_LENGTH    NULLABLE
--------------- -------------- -------------- --------
UIDPK            NUMBER        22              N
UIDPK            NUMBER        22              N
UIDPK            NUMBER        22              Y
NAME             VARCHAR2      255             N
NAME             VARCHAR2      255             Y
NAME             VARCHAR2      255             N

6 rows selected

Why is each column repeated 3 times?  Why is the datatype and length different on UIDPK and why is NULLABLE not the same?


texts:
MS SQL 2012 R2 SP3 is out of support.  We were having issues with DB Cluster which is 2012 R2 SP3 and Microsoft said it’s not supported version. We need to go for SP4 to have support.
I suppose it’s not just MS SQL version, Service Pack is also associated.
Can you correct me here?


texts: Using Window Function instead of Select sub-query for counting

texts:
I have to count the number of Orders a table, per Employee, for two criteria -

"Late Orders".
"Total Orders"

The query includes Orders table and Orders table. They are joined together via the EmployeeID field. The whole schema is found here.

This can be done the following way -

    Select E.EmployeeID
        ,E.LastName
        ,(Select Count(*) from Orders where Orders.EmployeeID = E.EmployeeID) as AllOrders
        ,count(OrderID) LateOrders

from Orders inner join Employees E on E.EmployeeID = Orders.EmployeeID
where CONVERT(date,RequiredDate) <= CONVERT(date,ShippedDate)
group by E.EmployeeID, E.LastName
order by EmployeeID

However I want to replace the following row with a window function -

    ,(Select Count(*) from Orders where Orders.EmployeeID = E.EmployeeID) as AllOrders

What I tried to do didn't work, with the following error -

Count(*) over(Partition By Orders.EmployeeID)

Column 'Orders.EmployeeID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I'd like to practice window functions. Can I use it here?

Here's the output -


texts: Where are database specific settings stored?

texts:
Postgres documentation says ALTER SYSTEM writes the given parameter values to the postgresql.auto.conf file, which is read in addition to postgresql.conf.

I am curious about where the alter database set configuration parameter details are stored.


texts: Why does ALTER AUTHORIZATION on a table drop all permissions on the target?

texts:
This fact is documented here:
ALTER AUTHORIZATION (Transact-SQL)

If the target entity is not a database and the entity is being
transferred to a new owner, all permissions on the target will be
dropped.

I had no idea about this until I faced the fact.
One day there was a need to give EXECUTE permission on every existing/future stored procedure to some user, this user is a member of db_datareader database role only and he should not be able to modify any data, but since the ownership chain in place once he has an EXECUTE permission he would be able to modify data through the stored procedures. So I had to break the ownership chain and generated a script that changed authorization on all the existing tables to some other user.
As soon as the authorization was changed, all the permissions on all the tables in that database disappeared. I checked the documentation mentioned above and found out that this behaviour is documented.
But it's quite unexpected (to me, at least) so I  wonder if someone has any explanation. Why did they do so? Why is there the need for dropping all the permissions on some table if the authorization on it was changed?


texts:
Question on what actually the "Maintenance Cleanup Task" does.   When the process executes I see xp_delete removing files but does the task also remove any entries from tables within MSDB?

The reason I ask is the backup appliance we use for SQL, and all other enterprise backups seems to be having issues when we try to delete backup files using the "Maintenance Cleanup Task" running off 50-60 sql instances at roughly the same time.  While I know this is configuration/hardware/vendor issue with our backup appliance I just want to know if this maintenance task does other things that wouldn't be accomplished by a PS script removing old backup (bak/trn) older than a specific date.    Looking at stop gap options till we can work with the appliance vendor to come up with a better long term fix.

I know we could spread out the tasks to clean up old files but the main question is scripting the deletion of an old backup not accomplishing everything the "maintenance cleanup task" does.

Thanks,
Jeff


texts:
I am creating a procedure that will allow me to auto increment a primary key
my way.

They key has a structure of M or V followed by a number.

The key for the table is defined as varchar(100).

The procedure gets the largest existing key, strips the first character and the next step is to add 10 to the number and make a new key using CONCAT.

How do I convert the number string I created with SUBSTRING to a numeric (int) unsigned so I can add 10 to it?

SUBSTRING returns the numeric string to a field mfg_ven_num declared as
varchar(100).

Do I need something like cast(mfg_ven_num as int)? if I do not comment it out I get a syntax error.

This is the code:

CREATE DEFINER=`pete`@`localhost` PROCEDURE `gen_mfg_ven_id`(out mv_id varchar(100), IN mv_flag char(1))
BEGIN
declare cnt int default 1000;
declare id_auto int default 0;
declare mfg_ven_name varchar(100) default "Manufacture not Assigned";
declare last_id VARCHAR (100) default NULL;
declare mfg_ven_num varchar(100) default null;

select count(*) from mfg_ven into cnt;

  if cnt = 3 then

      --default Wanufacture

       insert into mfg_ven (mfg_ven_id, mv_flag,id_auto,mfg_ven_name)
       values ('M1099', 'N', 1000, ame);

       -- default Vendor

       insert into mfg_ven (mfg_ven_id, mv_flag,id_auto,mfg_ven_name)
       values ('V1000', 'v', 1000, 'Vendor Not Assigned');

  else
       select max(mfg_ven_id) from mfg_ven into last_id;
       SELECT SUBSTRING(last_id, 2) into mfg_ven_num; # strip first character
       #cast(mfg_ven_num as int unsigned); # Change frow string to numeric
       set mfg_ven_num = mfg_ven_num + 10;

       select concat(mv_flag, mfg_ven_num) into mv_id;

end if;

END


texts: How to select one row from a table and sum another table

texts:
I have this minimal example in Oracle DB

create table a (name varchar2(10));
create table b (x number, y number);

and I wish to do this

select (select name from a where rownum <=1), sum(x) from b;

but I get

ORA-00937: not a single-group group function

How can I get the result I want, that is, name from table a and sum from b?

Edit (add example)

Table A

name
=====
foo
bar

Table B

x    y
=======
1    2
10   20

Should give

foo 11


texts: Why does SQL Server Management Studio hang (Not Responding) when connecting to Amazon RDS?

texts:
When I use SSMS to connect to a database hosted on Amazon RDS, the IDE hangs (as in goes to "Not Responding) after login. I enter SQL Authentication credentials and click Connect and the login window closes as it would for successful authentication. From there I get the "wait" cursor spinning indefinitely and the window status eventually changes to "Not Responding." The only thing I can do from there is force the application to close.

I have checked logs through Event Viewer and there are no entries for the 5 minutes before or after the error.
I can connect to local SQL Server databases with no errors or problems
I have been able to connect to this database within the past month through SSMS using the same credentials
I can connect to this database now using Visual Studio's IDE using the same credentials
SSMS version is 11.0.3128.0


texts:
We have a production database running in snapshot isolation level.
The application have a functionality where the user can selected a few records and command it to be processed.
A lot is done while the process run (executes SP, create temp tables,run CURSORS (sic) etc).
All those things takes several minutes to run.

This morning a user selected a big chunk of records and got this message:

  Snapshot isolation transaction failed in database 'Master' because the
  object accessed by the statement has been modified by a DDL statement
  in another concurrent transaction since the start of this transaction.
  It is disallowed because the metadata is not versioned. A concurrent
  update to metadata can lead to inconsistency if mixed with snapshot
  isolation

After a few minutes the user tried again , this time with small chunks of records and was able to workaround the problem.

Maybe it's a performance problem but I was puzzled by the Master DB being the place where the problem ocurred.
I can expect it to happen in the production DB or even in the TempDB.

My Question is? What is the role of the Master DB in this problem? Can it be just a misleading error message anyway?


texts: Why shouldn't I have one table for multiple relationships?

texts:
Assuming I have multiple relations in my database, for example Store, Employee and Sale, and I want to connect pairs with a simple binary relationship. Personally I would create tables named Employee_Store and Employee_Sale with a natural key composed of the foreign keys.

Now, my colleague insists on creating one table for multiple relationships. For the above example there could be a table called EmployeeLinks:

EmployeeLinks(
    IdLink int PK,
    IdEmployee int FK null,
    IdStore int FK null,
    IdSale int FK null,
    LinkType int not null
)

Please help me with good reasons why this is not a good idea. I have arguments of my own but I would like to keep them private and hear your unbiased opinions.

EDIT:

Initially the table above would have no primary key (!). Because the foreign keys allow null a surrogate key is the only option.


texts: SQL Server linked server losing Windows authentication credentials

texts:
We are seeing a sporadic authentication issue between two SQL Server instances with linked servers where the Windows authentication credentials are sometimes lost on the remote server.  When this occurs, we see logon failures in the remote server's SQL logs saying:

Source     Logon
Message
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Could not find a login matching the name provided. [CLIENT: 10.xxx.xxx.xxx]

The origin server has a linked server pointing to the remote instance with pass-through authentication.  I.e. on the Security page of the linked server properties, we have selected "For a login not defined in the list above, connections will: Be made using the login's current security context".
The linked server is set up as:
EXEC master.dbo.sp_addlinkedserver @server = N'KITTYHAWK', @srvproduct=N'SQLSERVER', @provider=N'SQLNCLI11', @datasrc=b.xyz.com', @catalog=N'Kittyhawk'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'KITTYHAWK',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

The domain account the application process runs as has db_datareader access via an Active Directory group present on the remote server (as well as the source server).  The application connects to the origin server with connection string Data Source=a.xyz.com;Initial Catalog=MPR;Integrated Security=SSPI;
Both instances are on SQL Server 2016 (13.0.5850.14) and are in the same AD domain.
We have verified the SPNs for both servers are set up and include the SQL Server service accounts and domain names used in all the connections (app to source instance, and source instance linked server to remote).
The application works most of the time, so the setup seems valid.  Plus users who have access to both databases like myself can query across the linked server no problem.
The only type of pattern we have noticed is when this issue occurs, we see many instances of it in the remote server's log at the same time.  For example, the last instance had 13 logon errors in the same second, and none before that for 3+ hours.  The application usage does make a lot of queries across the linked server in processing, and automatic retries within the application usually succeed a second or so later.
Is there any type of Active Directory/Kerberos authentication usage limits and how would we check this?
Any other possible causes?


texts: Calculate total attendance for each workshop

texts:
I want to get overall attendance in each workshop. NumberOFWeeks column holds the number of weeks the workshop runs. for example Workshop 101 runs for 6 weeks meaning 6 days.

So far I have done this which gives me the total number of entries but how can I only include results for selective days. For example only count attendance on 01/04/2015, 02/04/2015, 05/04/2015 only

  SELECT
  COUNT(attendance.AttendanceID) as 'Total Attendance'
  FROM
  student
  INNER JOIN attendance ON attendance.StudentID = student.StudentID
  WHERE
  student.WorkshopID = '101'


texts: Mysql syntax error when i add on completion preserve

texts:
I have written this event in mysql

DELIMITER $$
CREATE EVENT le_drop_database
ON SCHEDULE
   EVERY 1 WEEK
ON COMPLETION PRESERVE
STARTS CURRENT_DATE + INTERVAL 4 - WEEKDAY(CURRENT_DATE) DAY + INTERVAL '10:00' HOUR_MINUTE DO
BEGIN
   DROP DATABASE IF EXISTS carbon;
END $$
DELIMITER ;

but everytime i run it,it says i have a syntax error right syntax to use near 'STARTS CURRENT_DATE + INTERVAL 4 - WEEKDAY(CURRENT_DATE) DAY + INTERVAL '10:00' ' at line 5 */

Line five happens to be ON COMPLETION PRESERVE.If i delete line 5,the event is added successfully but if i do that the event will not be what i wanted.

Why is it a syntax error yet everything looks fine?.


texts: Oracle replicat is abending when oracle is having special char while loading to teradata

texts:
I am doing replication from oracle to teradata where oracle is having below charter in one column.
->->->
ACSII value for this char is 26 .
I need to replace it with null . In the charmap we have mentioned like:-

SOURCECHARSET AL32UTF8
TARGETCHARSET UTF-16
\xef\xbf\xbd  \x20

and included CHARMAP in the parameter file but do not know why this is not happening .
I have to mannually update/insert the record in the teradata target Database each time when this
collision happens .

Please let me know in case we can handle this automatically and prevent my replicat from ABEND.


texts: Shrink multiple database files using sp_MSForEachDB

texts:
We have recently cleared out old data from our production database. The database is 3 TB with 1.4 TB empty this however poses a problem on the development and QA instance as we are not utilizing space by having 6 - 8 databases with 1.4 TB empty space especially with the space constraints we have on development. I want to setup a job to shrink the development databases using the below code

EXEC sp_MSForEachDB ' USE [?]; DBCC SHRINKFILE  (''?'' , 10)'

EXEC sp_MSForEachDB ' USE [?]; DBCC SHRINKFILE (''?'' , 0, TRUNCATEONLY)'

I am getting the following error, "Could not locate file 'Test' for database 'Test' in sys.database_files. The file either does not exist, or was dropped."
The database has multiple data files. How could i improve my code to accommodate
the multiple data files.


texts: Sql server partitioned table: what happens when an update of a record changes the destination partition?

texts:
I my company we are facing performance issues on a big Sql server DB (running on Azure, total size > 150GB).
We have one big table (1,5GB, 3 millions records) that has several quite slow "select".
Those records have an "insert date" and I know that it is easy to make partitions based on date.
But I am exploring a different idea.
I have a "Status" column that can have different values, one of them is "Closed" and different other values that I'll call "Open".
My idea is to add a calculated persisted column with a value that can be "Open" or "Closed", i'll call it OpenClosedStatus.
"Closed tickets" continue to be accumulate and are rarely (if ever) read again, instead "open" tickets are normally less than 100 so having a dedicated partition should allow extremely fast read of their values.
Please note: the record is inserted once, selected potentially several hundred times and finally updated once (the update sets the "closed" status).
It is very complex to make proper indexes to speed up the several selects on the "Open" tickets because we have hundreds of instances of different programs that read "open" tickets accordingly with different criteria, therefore I am considering to partition them accordingly with the OpenClosedStatus.
This idea implies that a ticket is always inserted in the "Open" partition, then it should be transferred to the "Closed" partition when the status is updated as closed. The "OpenClosedStatus" will always be used in the WHERE clause of our queries, at least when searching for "open" tickets (therefore going on the small partition).
I do not find a reference of this "dynamic change" of partition on update of the record, I am therefore looking for advice for or against this atypical usage or partitions.


texts:
I'm using Berkeley DB (BDB) as a persistent store for a JMS queue.  When I consume an entry from the queue the underlying BDB files do not immediately shrink, but do eventually.  I'm running into issues with the BDB files taking up lots of space on the file system while retrieval performance degrades.

My entry size varies considerably but it is not uncommon to have 400,000 messages of around 32kb each in the persistent queue.

I'd like to understand how BDB manages the files so that I can throttle the number of entries for file size/retrieval performance.  Or so I can rule out BDB as my persistent store mechanism.

I am probably searching for the wrong terms but have not found what I'm looking for in the Oracle documentation or The Berkeley DB Book.  I would not be surprised if BDB doesn't want me messing with its internals but I would be surprised if (at least) an overview of how it does handle its internals is not available.


texts: In an ER diagram, do all entities need to be connected?

texts:
I am building a model DB of an online store. I have obvious tables such as Customers, Stores, Products, etc...

I made an ER diagram, but I realized that my Employees table is sitting out by itself and is not in a relationship with anything else.

Questions

Is this allowed/normal?
Or must I create a relationship with something?


texts:
I have this query to paginate the results and it was working fine on SQL Server 2012. However I had to move my database to SQL Server 2008 and now my stored procedure is not working. I did some research and got to know that OFFSET does not work in SQL Server 2008. What alternative should I use now? How i achieve the same functionality with SQL Server 2008?

Here is my stored procedure:

CREATE PROCEDURE [dbo].[sp_JobSearch]
    @EnteredKeyword nvarchar(200) = '',
    @EnteredLocation nvarchar(200) = '',
    @PageNumber INT = 1,
    @PageSize   INT = 40
AS
BEGIN
    SELECT
        MasterJob.Title, MasterJob.CompanyName,
        MasterJob.ShortDesc, MasterJob.Url,MasterJob.PostedTime,
        MasterJob.Location, JobBoard.JobBoardName
    FROM
        MasterJob
    LEFT JOIN
        JobBoard ON MasterJob.JobBoardId = JobBoard.JobBoardId
    WHERE
        (MasterJob.Title LIKE '%' + @EnteredKeyword + '%')
        AND (MasterJob.Location LIKE '%' + @EnteredLocation + '%')
    ORDER BY
        [MasterJobId]
        OFFSET @PageSize * (@PageNumber - 1) ROWS
        FETCH NEXT @PageSize ROWS ONLY;
END


texts: Group by range DATIME of 2h up or down - Mysql

texts:
Well, I have a table with some columns that some of these are repeated, but the time is not exactly the same, I need to do a group by that has a range of 2 hours.

Notice that all the fields marked in black repeat, but the datetime field may differ by a few minutes, so I need to make a group by joining everyone within that two-hour interval.

It would be something like: SELECT * FROM table GROUP BY sinistro, prefixo, data AND horaIfull_oco range 2h up or down

He would join all those who had a maximum of two hours apart. I have already researched and found nothing, if anyone knows how to do.


texts: I have 8 tables which use UUID Primary Keys and 7 of them have UUID Foreign Keys. Would it be better for performance to use BIGSERIAL instead of UUID?


texts:
Can anyone recommend a good SQL server Monitoring Tool?
One which will provide info on, server space usage, CPU usage , expensive queries run etc.


texts: Create a row based on a condition (column value)

texts:
I have a table that contains the following data;

EmpNo | GivenName | PreferredName
---------------------------------
1     | Paris     | Paris
2     | Ashley    | Ash

I am trying to get an output where, if the GivenName and PreferredName values are same, then the row should be returned as it is, but if they are different then the row should be returned twice (if possible, the row returned twice should have a NULL in PreferredName in the first occurence and GivenName a NULL in the second). Something like this:

EmpNo | GivenName | PreferredName
---------------------------------
1     | Paris     | Paris
2     | Ashley    | NULL
2     | NULL      | Ash

I have no idea where to begin. I tried using CASE statement and FULL OUTER JOIN to no avail. I have as well tried using the code below, and it is giving me the answer for the first part of my question:

SELECT [EmpNo]
      ,[GivenName]
      ,[PreferredName]
FROM [Emp] E
WHERE [GivenName]= [PreferredName]
UNION ALL
SELECT [EmpNo]
      ,[GivenName]
      ,[PreferredName]
FROM [Emp]


texts: SELECT from Clustered Columnstore results in LOB Reads

texts:
I am performing a SELECT from the following Clustered Columnstore Index which contains 5.2 Billion Records. When I review the output of STATISTICS IO, I am seeing LOB Logical reads.

My question is why is the query performing LOB Logical Reads when none of the data types in the table are LOB values?

I am working within a SQL Server 2014 SP3 Enterprise Edition environment.

Table Definition:

CREATE TABLE [dbo].[ColumnstoreTableA](
    [RollupID] [bigint] IDENTITY(1,1) NOT NULL,
    [DT_DIMID] [int] NOT NULL,
    [TC_DIMID] [int] NOT NULL,
    [C_DIMID] [int] NOT NULL,
    [CD_DIMID] [int] NOT NULL,
    [AC_DIMID] [int] NOT NULL,
    [UA_DIMID] [int] NOT NULL,
    [UL_DIMID] [int] NOT NULL,
    [JP_DIMID] [int] NOT NULL,
    [RTT_DIMID] [int] NOT NULL,
    [RA] [int] NOT NULL,
    [Impressions] [int] NOT NULL,
    [Clicks] [int] NOT NULL,
    [Conversions] [int] NOT NULL,
    [Sends] [int] NOT NULL,
    [CreationDate] [datetime2](0) NOT NULL DEFAULT GETDATE(),
    [ModifyDate] [datetime2](0) NOT NULL DEFAULT GETDATE(),
    [Z_DIMID] [int] NOT NULL,
    [R_Actual] [money] NULL,
    [JP_DIMID2] [int] NULL
) ON [PRIMARY]

SELECT Statement:

SELECT  [T].[Impressions]
FROM    [dbo].[ColumnstoreTableA] AS [T]

STATISTICS IO Output:

  Table 'ColumnstoreTableA'. Scan count 5, logical reads 127, physical
  reads 0, read-ahead reads 0, lob logical reads 3719, lob physical
  reads 0, lob read-ahead reads 0.


texts: Cannot generate reports from SQL Management Data Warehouse

texts:
I'm running SQL Server 2008 R2 and have installed the MDW on one server and have a Data Collector collecting and uploading the server activity, query results, and Disk activity data to the MDW. When I select any of the reports from the MDW with Data Collection > Reports > Management Data Warehouse I receive the error:

  Exception has been thrown by the target of an invocation - > Object reference not set to an instance of an object.

This occurs for all 3 reports and after I've waiting some time and data has been uploaded from the data collector. I do not have SSRS running, but read that isn't necessary.

Any suggestions?


texts:
I'm hoping someone can help me understand some EBS Bottlenecks that we are experiencing with a new Windows SQL Server instance we're configuring. We're running some tests by conducting a full backup so we can see how the I/O performs. However, the backup causes I/O blocking issues.
Its an r3.4xlarge instance which should have 250MB/s throughput and we've set up EBS with 7500 IOPs
However, we're only ever achieving a maximum of the following according to Cloudwatch:

Read Bandwidth: 140,486 KiB/s
Write Bandwidth: 22,448 KiB/s
TOTAL: 162934 KiB/s or 166 MB/s
Read Ops: 3468 OPS/s
Write Ops: 592 OPS/s
TOTAL: 4060 OPS/s

We cant understand why we're never achieving maximum IOPs or maximum throughput. Where else could it be bottlenecking?


texts:
Query Q1 is executed which selects from table a.

a is locked for writing (L1) while the Q1 runs.

When query Q2 attempts to write to table a, table a is immediately locked  for reading (L2). Q2 is in the 'waiting for table level lock' phase, i.e. it hasn't started executing the update but L2 is already effective.

As a result, L2 will be held from the start of Q2 all the way while Q1 executes plus, when Q1 is finished, while Q2 executes.

This doesn't make sense. Shouldn't L2 become effective only after Q2 is not waiting on L1 any more?


texts:
Editing because I made a mistake transforming from my real example with lots of irrelevant details to the boxes example:
I have a table BOXES like this:
box_id  | color | dimension | value
1          blue     length      1
1          blue     width       2
2          blue     length      3
2          blue     width       4
3          red      length      5
3          red      width       6
4          red      length      7
4          red      width       8
5          green    length      9
5          green    width       10
6          green    length      11
6          green    width       12

And I want to output average dimension for each color, like this:
color | avg_length | avg_width
blue        2           3
red         6           7
green       10          11

Is there a SQL query to do so? I thought of doing 3 different queries like
WITH avg_len as (
  SELECT AVG(length) as l,color FROM `boxes`
), avg_width as (
  SELECT AVG(width) as w,color FROM `boxes`
), avg_height as (
  SELECT AVG(height) as h,color FROM `boxes`

but not sure how to tie them together. In my real data, there are thousands of "boxes" and a dozen or so "colors".


texts: Is there a way to view the body / contents of a system function in sql server 2008

texts:
For the sake of leaning and exploring, I'd like to look at how some of the system functions are built. Is there a way to do this in sql server 2008 or R2?

I've tried sp_helptext 'Sum' but I get an error saying that the resource cannot be found.


texts: Determine Which Parameters Passed Into Stored Procedure

texts: Is there a way to determine (from within a stored procedure) which parameters were passed into a stored procedure (vs. those which just took the default value)?


texts: How do I identify the program that called a stored procedure?

texts:
As part of an application database design spec, we've been asked to block execution of a set of stored procedures if they have been fired directly from SSMS / osql / sqlcmd and so on; that is, they must be permitted to run only from within the application itself.

When our team questioned if this is even possible, we were shown a demo that limited queries to specific systems (actually it let the query run, but logged to a table where it was running from). However, the procedure that did that is encrypted, so we could not find out how it was done.

How do we accomplish this?


texts: Selecting next month first Friday when current month last day not equal to Friday

texts:
Hi I have my data as below.

Year    Period  Start_Date  End_Date
2019    6        1-Jun-19    30-Jun-19
2019    7        1-Jul-19    31-Jul-19

Period will be the month number

start date and end date will be the month start and end dates.

I want to fetch the data as below based on above.

Period  Sequence_no Period_end_dt
6        1           07-Jun-19
6        2           14-Jun-19
6        3           21-Jun-19
6        4           28-Jun-19
6        5           05-Jul-19 ( when ever month last day is not equal to Friday i need to display next month first Friday in current month).
7        1           05-Jul-19
7        2           12-Jul-19
7        3           19-Jul-19
7        4           26-Jul-19
7        5           26-Aug-19( when ever month last day is not equal to Friday i need to display next month first Friday in current month).


texts:
Last week, our MySQL database got overwhelmed with a lot of connections doing INSERT / SELECT queries.

I have talked to the developers who say it will take weeks to fix this issue.

In the mean time, I would love to have a way to limit the amount of live connections to the MySQL database if I see it getting out of control.

There is the option max-connections but I am not sure if I can throttle this so that if I the database is getting swamped, I would just set global max-connection = 100, and it would stop accepting new connections.

Is this a viable solution?  What have you done before when your database gets overwhelmed?


texts: How to install pgstattuple extension in postgres 10.6?

texts:
Can someone let me know how to install pgstattuple extension in postgres 10.6 running on Ubuntu 16?

I have installed postgres-contrib and can see the following .sql files under extension folder

ubuntu@vrni-proxy:~$ ls /usr/share/postgresql/10/extension/pgstattuple*
/usr/share/postgresql/10/extension/pgstattuple--1.0--1.1.sql  /usr/share/postgresql/10/extension/pgstattuple--1.3--1.4.sql  /usr/share/postgresql/10/extension/pgstattuple.control
/usr/share/postgresql/10/extension/pgstattuple--1.1--1.2.sql  /usr/share/postgresql/10/extension/pgstattuple--1.4--1.5.sql  /usr/share/postgresql/10/extension/pgstattuple--unpackaged--1.0.sql
/usr/share/postgresql/10/extension/pgstattuple--1.2--1.3.sql  /usr/share/postgresql/10/extension/pgstattuple--1.4.sql


texts: Insert in child table prevents lock on parent table

texts:
I'm a software developer, and although I've written a query or two, I'm not DBA, so please bear with me if I use the wrong terminology or plainly sound like a n00b.

We've recently had some big issues, which was tracked down to a foreign key which didn't have a supporting index. When deleting from the parent table, it needs to get a lock on the child table.
This makes sense, and it well described.

When diving in to this, I also found out that inserts on a child of the child (grandchild) will use the child in such a way, that no table lock can be aquired until the transaction is closed. For this behaviour I can't find much documentation. The closest I get is this forum post which basically says 'it is how it is', without providing much context.

So, let me share an example to reprocuce this. I got a Task table, which contains tasks for employees to perform (like call a customer, find a product). These tasks can be linked to a number of entities, including on OrderLine. OrderLine, obiously contains lines of orders with a products, quantity and so on.
Then there is a logging table, ActionHistory, which contains information about all kinds of changes which happened on various entities, like product, orders, customers, and tasks.

So basically, ActionHistory is a child of Task, which in turn is a child of OrderLine. Script to create this situation:

-- CREATE Parent table (orderline)
create table TBL_OrderLine(
  OrderLineID integer
);
alter table TBL_OrderLine
  add constraint PK_OrderLine primary key (OrderLineID);

-- CREATE Child table (Task)
-- Task gets a new column, orderlineid, which has an FK, but is not indexed.
-- Because of this, the table is locked
create table TBL_Task(
  TaskID integer,
  OrderLineID integer
);
alter table TBL_Task
  add constraint PK_Task
    primary key (TaskID);
alter table TBL_Task
  add constraint FK_Task_OrderLine
    foreign key (OrderLineID)
    references TBL_OrderLine(OrderLineID);

-- CREATE Log table (child of child, Action history)
-- Action history (since only recently, by the way) has an FK to Task, which is propertly indexed.
create sequence SEQ_ActionHistoryID;

create table TBL_ActionHistory(
  ActionHistoryID integer,
  TaskID integer,
  Message varchar2(4000)
);
alter table TBL_ActionHistory
  add constraint PK_ActionHistory
    primary key (ActionHistoryID);

alter table TBL_ActionHistory
  add constraint FK_ActionHistory_Task
    foreign key (TaskID)
    references TBL_Task(TaskID);

create index IDX_ActionHistory_TaskId on TBL_ActionHistory(TaskID);

-- Insert a bunch of orderlines
insert into TBL_OrderLine values (1);
insert into TBL_OrderLine values (2);
insert into TBL_OrderLine values (3);

-- Insert a bunch of (unrelated) tasks.
insert into TBL_Task values (101, null);
insert into TBL_Task values (102, null);
insert into TBL_Task values (103, null);

commit;

The FK on TBL_Task.OrderLineID isn't indexed.
The FK on TBL_ActionHistory.TaskID does have a proper index.

In session 1, I add something (completely unrelated) to my action history. It doesn't even have a TaskID.

insert into TBL_ActionHistory(ActionHistoryID, Message)
values (201, 'Something');

Then, in session 2, I try to remove an order line.

delete from TBL_OrderLine where OrderLineId = 2;

This second statement will wait until the transaction of the first statement is closed. This indicates that the insert into ActionHistory is blocking the other session from getting a lock on the Task table.

This surprised me, because nothing is being modified in the Task table at all. It may be used just for checking, but after that, it could be done. The new ActionHistory record is not referencing any row in the Task table, so no data lock needed there, so why is this happening?

I hope someone can provide not only the theory, but also a reference to Oracle documentation that explains this.
And of course I'm open to suggestions to tweak this even more apart from just adding the index.

We're using Oracle 11g (11.2.0.4.0).


texts: master-master broke because error code=1451 ;

texts:
I have a master-master replication setup using MariaDB 10.5.8 regarding replication I have these common settings in both servers:
log_slave_updates        = 1
gtid_strict_mode         = 1
gtid_ignore_duplicates   = 1
sync_binlog              = 1
sync_relay_log           = 1
sync_relay_log_info      = 1
sync_master_info         = 1

But some times depending on a query created by MySQL Workbench when dropping all tables I get this error the one breaks the replica:
 Error on master: message (format)='Cannot delete or update a parent row: a foreign key constraint fails (%.192s)' error code=1451 ;

Any idea how I could prevent breaking the replica or fixing the problem without the need to do a full restore of an existing backup?


texts: Insert Data in seconds between 30 minutes time interval

texts:
I want to divide my time interval from the start time to end time.

The elapsed time should be divided across 30 minute time intervals so that for this input

08:05:13   12:09:49

I should get following output.


texts:
Is it possible with Oracle auditing to find out if the role can be revoked from particular user without loosing any privileges that he actually uses?

Something like AUDIT webadmin_role BY webapp_user WHENEVER SUCCESSFUL?


texts: Dimension Modelling Senario Data Warehouse Design

texts:
I'm looking for some advised on how to model the following scenario in our data warehouse.

We have the following entities

Staff
Mobile Devices
Computers
Mobile Bills

If feels like its simpler to model without fact tables for Mobile Devices and Computers, because essentially there are no measures, Option 1 shows this. Option 2 shows the use of Fact tables.

What's would your advise be on the best practice in this scenario? Any input would be appreciated, thanks.


texts: Trying to Install Oracle Express 18c on Windows 10/x64 machine.  Constantly hangs on "Creating Oracle Listener".   Looking in the install log file does not show any error. Log files are huge, but can post somewhere if needed.  Any suggestions?   Thanks.


texts: How to achieve this sql server replication scenario?

texts:
I want to achieve below illustrated scenario via sql server replication.

Client A and Client B are client databases containing same article Table A. This article needs to be published by Client A and Client B whose changes need to be merged at the server (direction of arrows represent data flow).

Server is the destination database where article Table A should contain the changes made by both Client A and Client B. The trio consisting of Server, Client A and Client B are running Sql Server 2008 R2.

This is what I've tried in vain. Made Client A and Client B publishers and initially subscribed Server to Client A (went well). Next I tried subscribing Server to Client B (went haywire, found this msdn post where I figured out the reason behind the failure )

I still need to achieve the above diagrammed scenario. Is this even possible? If yes, any ideas how?

Thanks for your time.


texts: Queries running on snapshot database that causes high redo queue

texts:
I am trying to troubleshoot mirroring issue where we are getting threshold exceeded for unrestored log too high on mirror server.

On the mirror server we have database snapshot created for the database participating in database mirroring . Now for past week we are observing that counter high redo queue goes on increasing rapidly to 30-40 GB and after 4-5 hours start normalizing.

While troubleshooting i came to know that users are querying on snapshot database. Many of them running for last 2-3 hours and some for hour and some for mins. But i have seen queries which are running for over 5 hours kept running and this redo queue starts lowering down may be because of other queries getting completed.

How can i identify or troubleshoot further to find that culprit set of queries which are actually causing unrestored log to go that high

Update@ Database mirroring is in SYNC mode


texts:
I am trying to see all of my backups are compressed or not. I ran below query to check but not getting good result:

select top(10) database_name,
case when  backup_size = compressed_backup_size then 'Compressed'
else 'Not compressed'
end as Compression,
backup_finish_date
from msdb.dbo.backupset
where database_name ='MyDAtabase'
order by backup_start_date desc

I am using:

  Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)
  Jun 28 2012 08:36:30   Copyright (c) Microsoft Corporation
  Standard Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)

By default we setup compression, but once I ran that query I got result database is not compressed.


texts:
In the postgresql.conf file for PostgreSQL version 13, the archive_cleanup_command comment explains the command in the following way:

#archive_cleanup_command = ''         # command to execute at every restartpoint.

The documentation here and here have no mention of a 'restartpoint'. This raises the following questions:

What is a restartpoint?
For example: is restartpoint just the same word for a checkpoint? Do the two mean the exact same thing?
When is a restartpoint created?
For example: if the restartpoint is just a checkpoint then the check point will be created every 5mins or whatever the setting for checkpoint_timeout is in postgresql.conf file.
When is the archive cleanup command run?
For example: The archive cleanup command is run every time the archive_timeout (set in the postgresql.conf file) is reached. If the archive timeout is set to 1hr, then the archive_cleanup_command runs every 1hr.


texts: Getting Batch Requests Per Second On a Particular Database

texts:
Does anyone know how to find Batch Requests/Sec on particular database (database specific, not server level)?

This is how to find Transactions/Sec on a particular database.

DECLARE @Counter bigint
SELECT @Counter = cntr_value FROM sys.dm_os_performance_counters
WHERE counter_name = 'Transactions/sec'
AND object_name='SQLServer:Databases'
AND instance_name ='VirendraTest' -- Your DB Name

-- Wait for 1 second

WAITFOR DELAY '00:00:01'

SELECT cntr_value = @Counter FROM sys.dm_os_performance_counters
WHERE counter_name ='Transactions/sec'
AND object_name ='SQLServer:Databases'
AND instance_name ='VirendraTest' -- Your DB Name


texts: Wrong result with Oracle left join restrictions

texts:
all!

I'd like clarifications about how Oracle handles left joins that contains more than one join restriction. See the query below:

select
  category.category_id
  , category.parent_category_id
  , lpad(' ', 4 * (category.c_level - 1)) || nvl(context.category_name, category.category_name) as name
  , nvl(context.sequence_num, category.sequence) as sequence
  , nvl(context.is_suppressed, 'N') as is_suppressed
  , decode(category.syndicate_url, null, 'N', 'Y') as is_goto
  , category.start_date
  , category.end_date
  , decode(category.is_leaf, 0, 'N', 'Y') as has_children
  , category.c_level
from (
  select
    category_id
    , parent_category_id
    , category_name
    , sequence
    , start_date
    , end_date
    , syndicate_url
    , connect_by_isleaf as is_leaf
    , level as c_level
  from
    category
  start with
    category_id = 118
    connect by
      parent_category_id = prior category_id
      and level <= 3
) category
  inner join category_destination_channel channel on channel.category_id = category.category_id
    and channel.destination_channel_id = 1
    and channel.publish_flag = 'Y'
  left join contextual_category context on context.category_id = category.category_id
    --and context.is_suppressed <> 'Y'
    and context.context_type = 'DESKTOP'
where
  category.start_date <= sysdate
  and category.end_date >= sysdate
  and nvl(context.is_suppressed, 'N') <> 'Y'

In my database there are two categories that have corresponding contextual_category records with is_suppressed values equal to 'Y'.

When I run the query as provided (note that and context.is_suppressed <> 'Y' under the left join is commented out and an equivalent condition is provided in the where clause) the result is correct, i.e. those two categories that are suppressed do not show up.

If I uncomment the left join condition and comment out the where condition (and nvl(context.is_suppressed, 'N') <> 'Y') then the result set contains those two categories that are suppressed. Worse, the value of is_suppressed in the result set for those categories is 'N'.

What is the cause for this inconsistent behavior? Is it not a good ideia to, when using left join, move the restrictions from where clause to the join clause (since in the where clause I would need to use something like nvl function to handle the null cases)?

Thank you very much!

Edit

Sorry about the huge query... Basically I have two tables: category and contextual_category. The first contains the default data for categories: id, parent id, sequence number, name, start and end dates. contextual_category contains "overrides" of the default data for different context. For example:

category
========
id parent_id    name         sequence    start_date    end_date
----------------------------------------------------------------
1  null         Root         100         2015-9-1      2015-9-30
2  1            Child        100         2015-9-1      2015-9-30

contextual_category
===================
id    category_id    name     sequence  is_suppressed  context
----------------------------------------------------------------
100   2              CDesk    300       'Y'            'DESKTOP'
101   2              CMobi    13        'N'            'MOBILE'

Basically what I want is to use values on contextual_category (for name, sequence and is_suppressed) when the record exists, else use the "default" values defined in category.

select
    (...)
from
    (...) category c
    inner join category_destination_channel (...)
    left join contextual_category cc
        on cc.category_id = c.category_id
        and cc.context_type = 'DESKTOP'
        and cc.is_suppressed <> 'Y'
where
    (...)

See that a category can or cannot have one or more corresponding records in contextual_category (one record for each "context"). With the query above I'd like to retrieve active (not suppressed) categories for the DESKTOP context. The result I expected was only Root but it returned both Root and CDesk, even tough this category is suppressed in the DESKTOP context.


texts: Using sys.dm_os_performance_counters and DIFF to display logins per second

texts:
PROBLEM

Running my script below i can see the number of logins as an accumulated number to my instance:

SELECT cntr_value AS [LoginsPerSec]
    FROM sys.dm_os_performance_counters
    WHERE
    object_name = 'SQLServer:General Statistics'
    AND counter_name = 'Logins/sec'

Running it again 1 second later returns an increased value.

The difference between the two appears to indicate the increased number of logins within that period of time (second) between the two executions.

QUESTION1

How do I execute this twice within the same script to retrun the DIFF between the two values ?

Simply need the DIFF value only as the result therefore allowing me to plot the value on a live chart.

Currently doing this for active connections using:

SELECT count(dbid) as NoOfConnections
FROM sys.sysprocesses
WHERE dbid = 5
GROUP BY dbid

QUESTION2

Can you recommend any other performance counters visable via sys.dm_os_performance_counters that would allow me to monitor where my CPU resource is being used by SQL server ?

Thanks for any help

Scott


texts: Why the length of JOBHISTORY field is 172 here?

texts: It should be 150. Is Varray allocating some extra space?


texts: Enabling SQL Instant File Initialization when Restoring to a Network Path

texts:
We have a large Read Only SQL DB (SQL 2012) that we’re restoring to a NAS as a current duct tape solution. I had wondered why the restore process seemed to be hanging.

Part of my guidance along the way has come from:

http://www.brentozar.com/archive/2012/01/sql-server-databases-on-network-shares-nas/
and
http://www.brentozar.com/blitz/instant-file-initialization/

I believe I’ve identified that Instant File Initialization may not available when creating or restoring to a UNC path (or may need additional setup). SQL code below demonstrates that

DBCC TRACEON(3004,3605,-1)
GO
CREATE DATABASE [DestinedForGreatness]
GO
DROP DATABASE DestinedForGreatness
GO
EXEC sp_readerrorlog
GO
CREATE DATABASE [DestinedForGreatness]
ON PRIMARY
( NAME = N’DestinedForGreatness’, FILENAME = N’\\LittleBlackBox\SQL2012data\MSSQL\Data\DestinedForGreatness.mdf’ )
LOG ON
( NAME = N’DestinedForGreatness_log’, FILENAME = N’\\LittleBlackBox\SQL2012data\MSSQL\Data\DestinedForGreatness_log.ldf’)
GO
DROP DATABASE DestinedForGreatness
GO
EXEC sp_readerrorlog
GO
DBCC TRACEOFF(3004,3605,-1)

I can see in the first instance when creating a DB locally it doesn't 0 fill the MDF from the errorlog.  In the second instance it does.

I’d be interested to know if there’s any workaround for this to take advantage of IFI.  Would application of the Windows security settings on the server I'm restoring to resolve this?


texts: Why is SQL Server spliting it's (JSON) response into multiple rows?

texts:
I am trying to build a query that results in a JSON object generated by SQL Server. I've found that I can use subqueries to populate a field (in this case the questions field) with a JSON string that that contains a list of questions.
Below is the query:
SELECT
    quizzes.id AS 'id',
    quizzes.name AS 'name',
    quizzes.description AS 'description',
    quizzes.instructions AS 'instructions',
    author.id AS 'author.id',
    author.midas AS 'author.midas',
    author.first_name AS 'author.first_name',
    author.last_name AS 'author.last_name',
    author.email AS 'author.email',
    author.tel AS 'author.tel',
    author.department_name AS 'author.department_name',
    author.created_at AS 'author.created_at',
    author.last_updated AS 'author.last_updated',
    course.id AS 'course.id',
    course.name AS 'course.name',
    course.description AS 'course.description',
    course.crn AS 'course.crn',
    instructor.id AS 'course.instructor.id',
    instructor.midas AS 'course.instructor.midas',
    instructor.first_name AS 'course.instructor.first_name',
    instructor.last_name AS 'course.instructor.last_name',
    instructor.email AS 'course.instructor.email',
    instructor.tel AS 'course.instructor.tel',
    instructor.department_name AS 'course.instructor.department_name',
    instructor.created_at AS 'course.instructor.created_at',
    instructor.last_updated AS 'course.instructor.last_updated',
    course.created_at AS 'course.created_at',
    course.last_updated AS 'course.last_updated',

    (
        SELECT
            questions.id AS 'id',
            questions.text AS 'text',
            question_types.id AS 'type.id',
            question_types.name AS 'type.name',
            question_types.created_at AS 'type.created_at',
            question_types.description AS 'type.description',
            question_author.id AS 'author.id',
            question_author.midas AS 'author.midas',
            question_author.first_name AS 'author.first_name',
            question_author.last_name AS 'author.last_name',
            question_author.email AS 'author.email',
            question_author.tel AS 'author.tel',
            question_author.department_name AS 'author.department_name',
            question_author.created_at AS 'author.created_at',
            question_author.last_updated AS 'author.last_updated',
            questions.is_graded AS 'is_graded',
            questions.score_value AS 'score_value',
            questions.created_at AS 'created_at',
            questions.last_updated AS 'last_updated'
        FROM
            questions
        LEFT JOIN users AS question_author ON question_author.id = questions.author
        LEFT JOIN question_types ON question_types.id = questions.type
        WHERE
            questions.quiz = quizzes.id FOR JSON PATH, INCLUDE_NULL_VALUES
    ) AS 'questions',

    quizzes.created_at AS 'created_at',
    quizzes.last_updated AS 'last_updated'
FROM
    quizzes
    LEFT JOIN users AS author ON quizzes.author = author.id
    LEFT JOIN courses AS course ON quizzes.course = course.id
    LEFT JOIN users AS instructor ON course.instructor = instructor.id FOR JSON PATH,
    INCLUDE_NULL_VALUES;

The problem is: when I execute this query it responds with two rows where the resulting JSON string is split in two. Obviously this is undesirable.
After investigation, I found that if I remove the LEFT JOIN's, then the query responds as it should (only one row with the entire string intact).

Below is an example of the following behavior:
SELECT n = sc1.name FROM sys.syscolumns sc1 FOR JSON AUTO

As can be seen above, 11 rows are being returned.

And the length of the JSON output is about 20,000 characters.

I am using the following version of SQL Server
Microsoft SQL Server 2019 - 15.0.4073.23 (X64)
Developer Edition (64-bit) on Linux (Ubuntu 18.04.5 LTS) <X64>

Why is this happening? How can I fix it?


texts: Why doesn’t a MySQL fulltext search “IN BOOLEAN MODE” fail when referencing a missing column?

texts:
First, the background on how I discovered this issue. This happened when using MySQL 5.7 and the DB — as well as the related table — are using MyISAM.
I was debugging some code — and related MySQL queries — on a website I manage and found the bottleneck: It turns out a fulltext search was running a MATCH referencing a column that was not a part of the fulltext index for the table with 100,000+ rows. Hooray! I rebuilt the fulltext index adding the missing column, and suddenly a query that took 4.7 seconds to run ran in 0.0007 seconds!
But here is what baffles me: Why didn’t a MySQL fulltext search IN BOOLEAN MODE fail when referencing a missing column? Instead — in this case — it just ran slowly but gave correct results. Why didn’t it just fail? Is this a bug or a feature?
I mean, when I ran the same query in other fulltext modes such as these:

AGAINST ('Happy' IN NATURAL LANGUAGE MODE)
AGAINST ('Happy' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION)
AGAINST ('Happy' WITH QUERY EXPANSION)

I immediately got this MySQL error:
#1191 - Can't find FULLTEXT index matching the column list

To me, a failure like that would have been more useful than MySQL seemingly doing a full table search. Instead, the query was simply degraded to being accurate but extremely slow.
The initial fulltext index was built something like this; note the missing MOOD column:
ALTER TABLE `bigmoods`
  DROP INDEX `bigmoods_fulltext_idx`,
  ADD FULLTEXT `bigmoods_fulltext_idx`
  (
    `FIRSTNAME`,
    `LASTNAME`
  );

The query was something like this:
SELECT
  SQL_CALC_FOUND_ROWS
  FIRSTNAME,
  LASTNAME,
  MOOD,
  MATCH (
    `FIRSTNAME`,
    `LASTNAME`,
    `MOOD`
  )
  AGAINST ('Happy' IN BOOLEAN MODE)
  AS search_score
FROM
  bigmoods
WHERE
  MATCH (
    `FIRSTNAME`,
    `LASTNAME`,
    `MOOD`
  )
  AGAINST ('Happy' IN BOOLEAN MODE)
GROUP BY
  LASTNAME
ORDER BY
  LASTNAME ASC
LIMIT 0,100
;


texts: Oracle ROWID, can we assume it is sequential in a insert-only scenario?

texts:
How safe is it to assume that ROWID is sequential or ascending when a table is only inserted to? and why?

I had a situation where I had to verify whether new records were added to table which has no sequence or date columns to use as reference. I found that ROWID of a recently added record was higher than all the records in question. Therefore I assumed that they were older than the reference record. However looking around the net there seems to be some mixed information concerning ROWID, some even claim that ROWID is not even guaranteed to be constant!

I know that relying on the ROWID for order is probably not a good idea, but that got me thinking : what are the guarantees provided concerning the order and variation of ROWID values, both when DELETE statements are used and  when only inserting. Is there an official document by oracle detailing these points? Also, is ROWID unique at table level, or database level?


texts: Join on primary keys to primary key vs primary key in range

texts:
Assume we have an #RelevantOrders table and an OrderRow table.

create table #RelevantOrders(Order_Id int primary key)

create talbe OrderRow (Order_Id int, OrderRow_Id, [lots of data columns], primary key (Order_Id, OrderRow_Id)

Now what I by instinct did initialy do was a simple join

select OrderRow.* from #RelevantOrders as ro join OrderRow on OrderRow.Order_Id = ro.Order_Id

Now, due to the data, the Order_Id in #RelevantOrders are several ranges of Ids, ie. 1,2,3,4,5,100,101...120, 240..250 ... hope i am being clear that.

So what happens, if there are 1000 Relevant orders, execution plan does 1000 index seeks on OrderRow table, 1 per each Order_Id. This leads quite 'big' scan count and logical reads (~5000).

SO it got me thinking, i would expect that instead of doing 1000 index seeks, it would do ~80 (number of continuous ranges) index seeks.

So i tried an alternative:

create table #RelevantOrderRanges(f int, t int)

select OrderRow.* from #RelevantOrderRanges
join OrderRow on OrderRow.Order_Id between f and t

The good news is ammount of index seek and logical reads drops to 80 index seeks (equal to amount of ranges) and 609 logical reads. Much better. The only issue i have here and thats due to my experience with it causing to gobble up memory is the estimated number of rows and estimated data size, which goes up to about 3.5GB in this case instad of 49KBs.

Now i know that in case number of ranges approaches number of orders any gain is lost.

So this got me thinking and i have two questions:
1) am i overthinking the estimates too much? I know more testing is required but i am a bit afraid the estimates would bite me later due to memory hog

2) is there any way to 'better explain' SQL what to expect? Indices, hints, statistics.... ?

Thanks for discussion

PERFORMANCE UPDATE:
TC 1:
Order_Id count: 9045
Range count: 5499
Total rows produced:

Range based approach:
~800ms CPU, ~800ms ellapsed time
Scan count: 5,500
Logical reads: 37,896

ID join approach:
~900ms CPU, ~900ms ellapsed time
Scan count: 9045
Logical reads: 56774

TC 2:
Order_Id count: 19684
Range count: 12849
Total rows produced: 124561

Range based approach:
~718ms CPU, ~770ms ellapsed time
Scan count: 12849
Logical reads: 74127

ID join approach:
~950ms CPU, ~950ms ellapsed time
Scan count: 19684
Logical reads: 110352


texts: Why is the secondary selective index not used when the where clause filters on `value()`?

texts:
Setup:

create table dbo.T
(
  ID int identity primary key,
  XMLDoc xml not null
);

insert into dbo.T(XMLDoc)
select (
       select N.Number
       for xml path(''), type
       )
from (
     select top(10000) row_number() over(order by (select null)) as Number
     from sys.columns as c1, sys.columns as c2
     ) as N;

Sample XML for each row:

<Number>314</Number>

The job for the query is to count the number of rows in T with a specified value of <Number>.

There are two obvious ways to do this:

select count(*)
from dbo.T as T
where T.XMLDoc.value('/Number[1]', 'int') = 314;

select count(*)
from dbo.T as T
where T.XMLDoc.exist('/Number[. eq 314]') = 1;

It turns out that value() and exists() requires two different path definitions for the selective XML index to work.

create selective xml index SIX_T on dbo.T(XMLDoc) for
(
  pathSQL = '/Number' as sql int singleton,
  pathXQUERY = '/Number' as xquery 'xs:double' singleton
);

The sql version is for value() and the xquery version is for exist().

You might think that an index like that would give you a plan with a nice seek but selective XML indexes are implemented as a system table with the primary key of T as the lead key of the clustered key of the system table. The paths specified are sparse columns in that table. If you want an index of the actual values of the defined paths you need to create a secondary selective indexes, one for each path expression.

create xml index SIX_T_pathSQL on dbo.T(XMLDoc)
  using xml index SIX_T for (pathSQL);

create xml index SIX_T_pathXQUERY on dbo.T(XMLDoc)
  using xml index SIX_T for (pathXQUERY);

The query plan for the exist() does a seek in the secondary XML index followed by a key lookup in the system table for the selective XML index (don't know why that is needed) and finaly it does a lookup in T to make sure there actually are rows in there. The last part is necessary because there is no foreign key constraint between the system table and T.

The plan for the value() query is not so nice. It does a clustered index scan of T with a nested loops join against a seek on the internal table to get the value from the sparse column and finally filters on the value.

If a selective index should be used or not is decided before optimization but if a secondary selective index should be used or not is a cost based decision by the optimizer.

Why is the secondary selective index not used when the where clause filters onvalue()?

Update:

The queries are semantically different. If you add a row with the value

<Number>313</Number>
<Number>314</Number>`

the exist() version would count 2 rows and the values() query would count 1 row. But with the index definitions as they are specified here using the singleton directive SQL Server will prevent you from adding a row with multiple <Number> elements.

That does however not let us use the values() function without specifying [1] to guarantee the compiler that we will only get a single value. That [1] is the reason we have a Top N Sort in the value() plan.

Looks like I am closing in on an answer here...


texts: Python Postgres psycopg2 ThreadedConnectionPool exhausted

texts:
I have looked into several 'too many clients' related topic here but still can't solve my problem, so I have to ask this again, for me specific case.

Basically, I set up my local Postgres server and need to do tens of thousands of queries, so I used the Python psycopg2package. Here are my codes:

import psycopg2
import pandas as pd
import numpy as np
from flashtext import KeywordProcessor
from psycopg2.pool import ThreadedConnectionPool
from concurrent.futures import ThreadPoolExecutor

df = pd.DataFrame({'S':['California', 'Ohio', 'Texas'], 'T':['Dispatcher', 'Zookeeper', 'Mechanics']})
# df = pd.concat([df]*10000) # repeat df 10000 times

DSN = "postgresql://User:password@localhost/db"
tcp = ThreadedConnectionPool(1, 800, DSN)

def do_one_query(inputS, inputT):
    conn = tcp.getconn()
    c = conn.cursor()

    q = r"SELECT * from eridata where "State" = 'California' and "Title" = 'Dispatcher' limit 1;"

    c.execute(q)
    all_results = c.fetchall()
    for row in all_results:
        return row
    tcp.putconn(conn, close=True)

cnt=0
for idx, row in df.iterrows():

    cnt+=1
    with ThreadPoolExecutor(max_workers=1) as pool:
        ret = pool.submit(do_one_query,  row["S"], row["T"])
        print ret.result()
    print cnt

The code runs well with a small df. If I repeat df by 10000 times, I got error message saying connection pool exhausted
. I though the connections I used have been closed by this line:

tcp.putconn(conn, close=True)

But I guess actually they are not closed? How can I get around this issue?


texts: Possible to use libmysqlclient-dev.lib functions (native c client) with mySQL Router?

texts:
I've setup the mysql innodb cluster with some difficulty, and have mysqlrouter running and listening on ports with the correct IP address. I then have a C program trying to connect to mysqlrouter to submit queries to, but the function, mysql_connect() gives me an error: "No such file or directory".

It's likely it was looking for a mysqld-specific file. Has anyone managed to setup communications between a C program and mysqlrouter?

Edit: Fixed, have to setup permissions for the IP address mysqlrouter is on
https://dev.mysql.com/doc/mysql-router/8.0/en/mysql-router-general-using-developing.html


texts: Using IF and other conditions in T-SQL (trying to import data from Oracle using SQL Server SSIS)

texts:
I am using SQL Server 2012 SSIS package to fetch data from Oracle and insert it into SQL Server 2012.

I am doing it for the first time and every thing is fine i.e. all data from a particular table is being inserted into table in SQL Server.

But I want to place a condition i.e. if some record is new, only then add it in SQL Server. For e.g. there is an ID field in Oracle table which I can check in SQL Server table so if that particular ID is already present in SQL Server table then do not insert it.

I think this IF condition cannot be used in regular SQL and I have to use T-SQL which I have never used so how do I write this simple condition?  Suppose following is table structure:

Oracle
Table: OracleTable
Fields: ID, Name

SQL Server
Table: SQLTable
Fields: ID, Name

So it will be something like:

SELECT * FROM OracleTable;
If (Oracle.ID != SQL.ID)
    INSERT INTO SQLTable(rest of the query here)

Or may be there is some option available in SSIS which will allow me to do this without writing query?


texts:
In SQL*Plus, is there a way to save current COLUMN settings similar to STORE which saves SET commands in a script?

COLUMN can be used to list the settings, but they are not in script format.


texts:
I'm using Oracle10g and I have a problem that is challenging me. The application has a timeout of 45 seconds. It accesses a user from my database and always performs the same action. The problem is that the application when a session ends by timeout, ends only in the level of application not in the database.

This causes the database to continue processing and perform the action successfully, however the application execute another call causing duplication of data. (A primary key is determined by sequence)

What is my idea? End the session when the execution of certain instruction exceed 45 seconds in the database, from this specific user. The problem is that the application uses a connection pool, and the application treats those who enter or not the database.

I can not implement it for profile, because the connections are on the bench for days, and only when necessary to make the appointment.

I need to know how long it is taking a particular query to run. My need is to know when a user, it only makes a single query in the database, it times out the application, which is 45 seconds.

Where can I get this information? With it I could determine which session closed, since a profile would be unworkable.

I apologize for my bad English. I'm still learning.

Cheers
Rainier Morilla


texts:
I was running some code when my laptop shut down. Now everytime I connect to the server, I get an error message as follows:

ambangra@access1:~$ LOG:  database system shutdown was interrupted; last known up at 2014-04-03 04:27:57 SGT
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  redo starts at 0/1864240
LOG:  record with zero length at 0/18668CC
LOG:  redo done at 0/18668A4
LOG:  last completed transaction was at log time 2014-04-03 04:19:34.929757+08
FATAL:  xlog flush request 0/18B02DC is not satisfied --- flushed only to 0/18668CC
CONTEXT:  writing block 0 of relation base/16384/12547_vm
LOG:  startup process (PID 14919) exited with exit code 1
LOG:  aborting startup due to startup process failure

Does it mean the code is still running? I am unable to connect at all. Any help would be greatly appreciated. Thanks!


texts: Is there any way to get the optimization time in MySQL?

texts: I'd like to see the time optimizer takes to optimize a query. Is there any way to get that time?


texts:
Generally, a table design with repeated ambiguous columns is bad design:

CREATE TABLE persons(
    personid int primary key,
--  …,
    phone1 char(10),
    phone2 char(10),
    phone3 char(10)
);

while a table with distinct columns is better design

CREATE TABLE persons(
    personid int primary key,
--  …,
    mobile char(10),
    homephone char(10),
    fax char(10)
);

Understood and Agreed.

The problem is that as far as I can tell, the first design scrapes through the most definitions of normalisation, since technically the columns have different names. Databases neither know nor care what the names mean, and it has always up to human interpretation as to what to put where.

Clearly, he problems with the first design include:

the arbitrary number of columns is often too many or to few for some rows; this may even require altering the table to accommodate.
because the names are ambiguous

it is possible that some of the earlier columns are empty while the latter ones are populated
it makes searching difficult since you don’t know which column to search

I also know that if there are genuinely multiple values then the data should have appeared in a separate table.

The question is:

is there a correct name in the literature for the anomalies caused by the first design?
is there a “normal” form which precludes the first design?


texts: I can't start mysqld.service -> status : Data Dictionary upgrade from MySQL 5.7 in progress in centos 7

texts:
I've installed mysql 8 on centos 7

and when i want to start the mysqld service , it says :

Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details

and when i run systemctl status mysqld.service it says:

mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor
preset: disabled)
Active: failed (Result: exit-code) since Mon 2020-01-13 06:01:53 EST; 6min ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 5532 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS (code=exited, status=1/FAILURE)
Process: 5504 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 5532 (code=exited, status=1/FAILURE)
Status: "Data Dictionary upgrade from MySQL 5.7 in progress"

Jan 13 06:01:48 server1.lifeandme.net systemd[1]: Starting MySQL Server...
Jan 13 06:01:53 server1.lifeandme.net systemd[1]: mysqld.service: main process exited, code=exited, status=1/FAILURE
Jan 13 06:01:53 server1.lifeandme.net systemd[1]: Failed to start MySQL Server.
Jan 13 06:01:53 server1.lifeandme.net systemd[1]: Unit mysqld.service entered failed state.
Jan 13 06:01:53 server1.lifeandme.net systemd[1]: mysqld.service failed.

I also tried restart the service and cleaning log files under /var/lib/mysql , but no success

i did the following commands to install lamp on server (history related to lamp installation)

yum install httpd

systemctl start httpd.service

systemctl enable httpd.service

yum install mariadb-server mariadb

systemctl start mariadb

mysql_secure_installation

systemctl enable mariadb.service

yum install php php-mysql

systemctl restart httpd.service

yum remove php php-server

yum clean packages

yum install epel-release

yum install http://rpms.remirepo.net/enterprise/remi-release-7.rpm

yum install yum-utils

yum-config-manager --enable remi-php72

yum install php72

yum remove mariadb mariadb-server

systemctl restart httpd.service

yum localinstall https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm

yum install mysql-community-server

systemctl enable mysqld

systemctl start mysqld =>  ran to error


texts: How do I give root@localhost permission to grant privileges in MySQL?

texts:
Whenever I try to Grant Privileges as root@localhost, it says permission denied (using password=Yes).

My root user is the one that I am logged in as.   I verify this by select user();

It responds with 'root'@'localhost'.

I have also checked out this stack overflow post which seems to be the same issue:

https://stackoverflow.com/questions/28796510/cannot-grant-permissions-in-mysql-using-root-or-any-account-access-denied-f

and this one

https://stackoverflow.com/questions/1559955/host-xxx-xx-xxx-xxx-is-not-allowed-to-connect-to-this-mysql-server

I am having no luck.  If I Show GRANTS;, It lists all GRANT privileges: SELECT, Insert, Update, DELETE, CREATE, DROP, etc.

How do I make sure that ROOT has GRANT option itself?
Why would my root user not have all privileges?  I am using mysql version: 5.5.38-0+wheezy1 on Debian.


texts: I am planing to automate to take incremental backups on a range of sql servers from 2000 to 2012, on a variety of windows too.  This will be done with a vbs script that runs on task scheduler.  I want to know if there is a problem to occasionally start a new incremental backup when the previous one is still running or I must be sure that there is a lapse of time doing nothing.  The rest of details are covered, I keep all the files, and I test (restoring on other server) all the backups.


texts: MS SQL: How to get Employee Status from two seperate tables

texts:
I am new to MS SQL so please bear with me.

I want to get the Company Code and Company Short Name of an employee DEPENDS on it's each employee status and stored it into one table.

If Emp_Status = FT, it will get Co_Code and Co_Shortname from Table 1. ELSE if Emp_Status = PT, it will get Co_Code and Co_Shortname from Table 2 where USERNAME is equals to each other.

I have two tables.

Table 1:
EmpID  |Co_Code  |Co_ShortName |Emp_Status
WC11    |01          |AA1        |FT
KC22    |02          |BB1        |PT
MA33    |03          |CC1        |FT

Table 2:
EmpID  |Co_Code |Co_ShortName |Emp_Status
WC11     |04          |DD1        |FT
KC22     |05          |EE1        |PT
MA33     |06          |FF1        |FT

Output will be

EmpID  |Co_Code  |Co_ShortName |Emp_Status
WC11    |01          |AA1        |FT
KC22    |05          |EE1        |PT
MA33    |03          |CC1        |FT

I am not sure if I need to use CASE or IF statement as I am getting other fields from table 1.

I know this isn't correct but this is what I have:

SELECT EmpID ,  CASE
        WHEN Table1.EmpStatus = 'FT'
        THEN (SELECT Co_Code from Table 1)
        WHEN Table1.EmpStatus = 'PT'
        THEN (SELECT Co_Code from Table 2 where Table1.EmpID = Table2.EmpID)

    END

Please help


texts: Why does SESSION_USER return dbo instead of SQL login?

texts:
I have a trigger on a GP table that records when a critical field is changed and who changed it. A sample of the trigger follows.

CREATE TRIGGER [dbo].[TRACKAPP_UPDATE] ON [dbo].[Table]
AFTER UPDATE
AS
DECLARE @MDFUSRID   char(15)
DECLARE @SOPNUMBE   char(32)
DECLARE @DOCSTATUS  INT

BEGIN
BEGIN
IF UPDATE(DOCSTATUS)
BEGIN
    SELECT @MDFUSRID = SESSION_USER
    SELECT @SOPNUMBE = SOPNUMBE FROM inserted
    SELECT @DOCSTATUS = DOCSTATUS FROM INSERTED

In 99% of the cases this returns the correct name of the user who changed the field. If any of the ERP people change a field (myself included) the user shows a dbo. I have always assumed this is an access issue. However, we have found a couple of users who also return as dbo rather than their name.

Do you have any suggestions as to where I should start looking?


texts:
I have a table column containing IP addresses, datatype is varchar(255). I want to switch to inet datatype now. Is there a way to convert existing addresses in IP column to inet datatype? Tried:

ALTER TABLE request_logs ALTER COLUMN ip type inet USING ip::inet;
ALTER TABLE request_logs ALTER COLUMN ip type inet USING CAST(ip AS inet);

But no luck, getting:

PG::DatatypeMismatch:
ERROR:  default for column "sign_up_ip" cannot be cast automatically to type inet

All values are '127.0.0.1'. Postgres version is 9.6.9.


texts: What are best practices/important steps when the Windows Server machine hosting SQL Server 2008 R2 is moved to another domain?   What steps definitely/might need to be done for SQL Server before and after the new domain is joined?


texts:
I created a Report Model which will be consumed by the End users to create ad hoc reports using Report builder tool. The Report Model can be created using the Report Model Project in Business Intelligence Studio. It has 3 stages to create the Report Model.

Data Source
Data Source Views(.DSV)
Report Model(.SMDL)

How to create Report Model for multiple data sources so that the end user can access different databases (in one report model) while creating the report using Report Builder tool?

Any possibility to change the datasource of Report Model in RunTime?


texts: While importing 150 GB file on Amazon RDS, why connection is failing?

texts:
I am having Postgres database dump file of 150 GB on Amazon EC2 instance. While dumping the data on RDS from EC2 server I am getting error.

The output of the command is as given below. It's giving error

1. psql connection not open
2. connection to server was lost
on RDS dump postgres copy command

Command Output:
SET
SET
SET
SET
SET
SET
SET
SET
ALTER TABLE
ALTER TABLE
ALTER SEQUENCE
ALTER TABLE
psql:filename.sql:1396266: connection not open
psql:filename.sql:1396266: connection to server was lost

Application Configuration:

Ruby 1.9.3
Rails 3
PostgreSQL 9.3

Please help why it is breaking while copying the data. As the connection was established and command is running. Suddenly while executing the copy command it is breaking.

Command/script that I am using to dump data the data is below:

psql -h instance.id.region-2.rds.amazonaws.com -p 5432 -U username -W -d database_name -f filename.sql

Workaround for finding issues as below:

I took head 100 rows and tail 200 rows and made 1 file from the big 150 GB file it converted to 56KB. when I run this command, it's dumped successfully.

So the file size is causing problem. The same command is running for small size file for dumping data on RDS.

How can I resolve this issue?


texts: Notification email when deadlock is encountered in Oracle alert log

texts:
How to send a notification email when a deadlock is encountered in alert log on any node like the deadlocks encountered below? (an alert any time a deadlock is detected in alert log)

Node 1:

Mon Sep 08 16:23:59 2014
Global Enqueue Services Deadlock detected. More info in file
 /u01/oracle/diag/rdbms/luma/LUMA1/trace/LUMA1_lmd0_4814.trc.

Node 2:

Mon Sep 08 16:37:47 2014
Global Enqueue Services Deadlock detected. More info in file
 /u01/oracle/diag/rdbms/luma/LUMA2/trace/LUMA2_lmd0_2602.trc.

I am using DB Control (11g).


texts: How to log ALL queries made by users in SQL Server 2017 Standard?

texts:
Is there a possibility to do this? All queries and actions performed in any database need to be logged, because of in house compliance.

I need information (and/or source) if we need MS SQL 2017 Standard or Enterprise in order to ensure that the DB Admin's activities (run, modify, etc queries...) are logged and not only the activities performed by non-admin users.


texts: Take 1 row of data from MySQL and Split the Results into a Table

texts:
I have a mysql database that looks like this:

proj_id | job1_name | job1_type | job1_time | job2_name | job2_type | job2_time | ...
--------+-----------+-----------+-----------+-----------+-----------+-----------| ...
      1 | John      | Roof      |        10 | Jane      | Floor     |         2 | ...

Actual table has these three fields (name, type, and time) for up to 5 jobs.

I need the results to display in a table like this:

proj_id |  Name  |   Type   | Time
--------+--------+----------+------
      1 | John   | Roof     |   10
      1 | Jane   | Floor    |    2

How can I accomplish this?

thanks for all the help!!

Jay


texts: Error ERROR 1153 (08S01) during mysqldump restoration on Amazon RDS

texts:
I am trying to import a table on an Amazon RDS server using mysqldump. During restoration, i get the following error message:

ERROR 1153 (08S01) at line 925: Got a packet bigger than 'max_allowed_packet' bytes

I then add --max_allowed_packet=128M to the restoration command, and now get the following error:

ERROR 2006 (HY000) at line 925: MySQL server has gone away

Here is the mysqldump restoration command:

mysql --max_allowed_packet=128M -uroot -p -h db-name.123456789012.us-east-1.rds.amazonaws.com dbname < /srv/tmp/dbname.sql

And when i try to set the max_allowed_packet and net_buffer_length globally during the current session without having to restart the RDB instance, i get the following error:

ERROR 1227 (42000) at line 1: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

Attempt to add 'SUPER' privilege to the user also fails.


texts:
How do I find the creation date of a user in MongoDB?  As background, I'm used to doing this in relational databases like Oracle or MSSQL.

I've checked the admin.system.users table, as I thought I might be able to decode the _id in case it were a generated ObjectID, but no luck there, as it uses the {db}.{username} as the _id.

I could probably stuff the current timestamp into the customData field at user creation, but that would not be as reliable as the data dictionary queries I'm used to from the RDBMS cousins.  I would need to make sure all other DBAs follow the practice (not too hard, but prone to human error), but also make sure any 3rd party tools that do user administration (IDM) functionality follow suit (not easy).


texts:
I am using the default SQL Server Maintenance task to reorganize index only which was running until I got the below error.

Executing the query "ALTER INDEX [PK_abc] ON " failed with the
following error: "A severe error occurred on the current command.  The
results, if any, should be discarded.". Possible failure reasons:
Problems with the query, "ResultSet" property not set correctly,
parameters not set correctly, or connection not established correctly.

I have checked DBCC check db and no errors reported. Can this be due to low Transaction Log space as reorg don't consume space specially on data disk or I need to look at other direction. Any guidance would be greatly appreciated.


texts:
I have a lot of Excel datasheet (in one file)

I want to batch input to Access

but I can't find a sample code to do it

the name of datasheet in access is same as in excel

is there any code of VBscript can do it?

maybe in access or excel vba?

or any simple software?


texts:
Can I set alerts for an autogrow event in SQL Server (2005 or 2008 R2)?

I've searched around and haven't found the 'error number' for autogrowth, if there is one.

In case this isn't possible, how else do you suggest setting up alerts for autogrow?

I've thought about configuring SQL Server notifications with Service Broker as an alternative, but I'm open to suggestions.


texts:
What is the reasoning behind using the GO statement after every SQL statement? I understand that GO signals the end of batch and/or allows the reputation of statements but what advantage does it have using it after every statement.

I am just curious as a lot of Microsoft documentation etc. have started using it after every statement  or maybe I've just started to notice.

Also what's considered best practice?


texts:
I just ran across common_schema, a utility for MySQL database administrators. It is a SQL script that creates a schema called common_schema in your database and plenty of stored procedures.

I need it for a corporate database that stores confidential information. How can I make sure it is safe? It is stored on Google Code and doesn't have a checksum or even less a signature. I'm just supposed to do some

mysql -u root -p < common_schema-2.2.sql

But somehow it doesn't feel right to dump 31K SQL lines into my DB. Am I missing something? Any ideas? Its author is Shlomi Noach, an active StackExchange user.


texts:
I want to import data from one database to another.  On the destination database, I don't have permission to restore a backup file to it, so I have only one option which is to import data.

I used the wizard and I set the option of allow identity insert, but after it ran I faced a problem with foreign keys insertion. Is there anything I missed or shall I drop the foreign keys and recreate it after import?

I really wonder why Microsoft made it complicated.


texts: How join 2 table with calculation in the relation ID

texts:
I'm using SQL Server 2012 for my RDBMS I have 3 main table Tb_Template,Tb_Value, and Tb_Mapping.
I use Tb_Mapping to join Tb_Template and Tb_Value. But the relation to Tb_Value have calculation Val_Now & Val_Before.
What I try is make Value_ID in Tb_Mapping is save query like this (example for ID 3 in Tb_Mapping)
SELECT
    (SELECT Val_Now FROM Tb_Value WHERE ID = 'VAL4') +
    (SELECT Val_Now FROM Tb_Value WHERE ID = 'VAL5')

Is there another way to make get result like View_Result
Table Tb_Template
ID    Description
-----------------
1        Desc1
2        Desc2
3        Desc3
4        Desc4
5        Desc5

Table Tb_Value
ID    Val_Now    Val_Before
----------------------------
VAL1     1            0
VAL2     5            3
VAL3     1            1
VAL4     3            4
VAL5     0            2

Table Tb_Mapping
ID   Template_ID      Value_ID
-------------------------------
1       1               VAL1
2       2               VAL2
3       3             VAL4+VAL5
4       4               VAL3
5       5        VAL1+VAL2-VAL4-VAL5

View_Result:
Description   Val_Now Val_Before
----------------------------------
   Desc1          1         0
   Desc2          5         3
   Desc3          3         6
   Desc4          1         1
   Desc5          3        -3


texts: Can we back up mysql databases by copying files?

texts:
I tried to reinstall mysql on my machine, but it keeps failing. Everytime it fails, it does this locking and that locking and future effort would fail again.

Finally I decided to do truly clean install

Delete everything
Reinstall

The procedure is listed here : http://stuffthatspins.com/2011/01/08/ubuntu-10-x-completely-remove-and-clean-mysql-installation/

I do not want to lose data already there. Yea, it's backed up but I kind of like it there.

So, can I just mv content of /etc/mysql and then copy it back after I reinstall everything?

My programmer said it wouldn't work

If not, what else I should know?

I sort of forget where mysql store data. It's the default for ubuntu I guess.

Update: I tried that on some local databases using available computers. The Myisam table get transferred. However the innodb tables do not.

Also http://www.chriscalender.com/?tag=innodb-error-tablespace-id-in-file shows that innodb won't get transferred as easily by just copying files. Am I missing something?

Another Update: The answer is yes we can. We did it :D

Update guys.

The short answer is yes we can. My programmer just did it. The key is to copy thew whole file and realize that innodb store database in one big file. Not sure why.

The other answer is better so I pick that (I don't think it's objective to pick my own answer). I just want to share that it works.


texts:
$sql = "SELECT Kill FROM tbl_pvporderview";

Problem is that I end up with: Incorrect syntax near the keyword 'Kill'.

Because kill is a T-SQL command... any way to bypass it?

I can't change the column name because it's used by the software a lot and I cant change the software that's using the database.

So it simply fails if I use sqlserv to select data from that column. '' or "" wont help.

The complete statement would be:

$sql = "SELECT serial,Kill FROM tbl_pvporderview WHERE Kill > (?) ORDER BY Kill DESC ";


texts: Accidentally turned down SQL memory too low to log in, how do I fix?

texts:
I turned down the Maximum Memory of the my SQL Server instance to 15 MB... Now I can't log in to increase it. How can I increase the maximum memory without logging in?

Version is SQL 2008 R2


texts: When converting dynamic SQL (pivot query) to xml output, why is the first digit of the date converted to unicode?

texts:
I am using this great example https://dba.stackexchange.com/a/25818/113298 from Bluefeet, to create a pivot and transform it to xml data.

Declaring the param

DECLARE @cols AS NVARCHAR(MAX),  @query  AS NVARCHAR(MAX);

Next there is a CTE with a lot of code, the endresult of the CTE is put in a temp DB (same as in the example)

SELECT
B.[StayDate] -- this is a date dd-mm-yyyy
, B.[Guid]
INTO #tempDates
FROM BaseSelection B

Generating the cols (same as the example)

SELECT @cols = STUFF((SELECT distinct ',' +QUOTENAME(convert(char(10), [StayDate] , 120))
FROM #tempDates
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'');

The result set is what i should expected

set @query =
   'SELECT [Guid],' + @cols +'
    FROM
    (
        SELECT
            [StayDate]
           ,[Guid]
        FROM #tempDates
    ) A
    pivot
    (
        count([StayDate])
        for [StayDate] in (' + @cols +')
    ) p
    '
EXEC sp_executesql  @query ;

When I try to transform it to XML my attributes are only partially converted

set @query =
   'SELECT [Guid],' + @cols +'
    FROM
    (
        SELECT
            [StayDate]
           ,[Guid]
        FROM #tempDates
    ) A
    pivot
    (
        count([StayDate])
        for [StayDate] in (' + @cols +')
    ) p
    for xml auto
    -- when using for XML path i will get a error
    -- FOR XML PATH(''''), ROOT(''root'')
    -- Msg 6850, Level 16, State 1, Line 3
    -- Column name '2016-12-17' contains an invalid XML identifier
    -- as required by FOR XML; '2'(0x0032) is the first character at fault.
    '
EXEC sp_executesql  @query ;

resultset

<p Guid="3C3359E3-CFE5-E511-80CA-005056A90901"
  _x0032_016-12-17="2" --> should be 2016-12-17="2"
  _x0032_016-12-18="2" --> should be 2016-12-18="2"
  _x0032_016-12-19="2" --> should be 2016-12-19="2"
/>

Have I missed something, why is only a portion of the date converted to unicode?

How can i fix this?


texts: How can I send an email when objects get invalidated?

texts:
I have couple stored procedures and a package.

Whenever this stored procedure and package goes invalid, I need to send an email.

How can I achieve this?

Can you please give me an idea how this can be done.


texts:
I'm looking for a tool that will let me precompute expensive aggregations some time after rows have been inserted into a MySQL database.  For example, if I often run a SUM() on a column, I'd like to instead store a running sum in a separate table that can be queried instead of the raw data.

My use case is for heavily-read live stat data (graphs, etc.).

Are there more extensive options beyond materialized views?  The thought behind finding a system like this is to reduce the amount of raw sql required to create materialized views as the users of the system would like to specify aggregations without SQL (as new aggregations and stats are being created constantly).