texts:
I have a stored procedure that I want to 1st gather data between a @startdate & @enddate then I want to compare that data to the previous year.

I tried the below syntax (for testing obviously) to ensure I was getting my desired dates output before I added to my production syntax.  However, this gives me an error of

  Msg 102, Level 15, State 1, Line 11
  Incorrect syntax near '–'.
  Msg 102, Level 15, State 1, Line 14
  Incorrect syntax near '–'.

This is the syntax I tried.  What would I alter in order for my required dates to be output?

Declare @startdate date = '20170101'
    ,@enddate date = '20170212'

--one year prior to @startdate
Select CAST(DateAdd(yy, -1, @startdate) As Date)

--One year prior to @enddate
SELECT CAST(DateAdd(yy, -1, @enddate) As Date)

--This will get first day of prev yr
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0,
        DATEADD(YEAR, –1, @startdate)), 0)

--This will get last day of prev yr
SELECT DATEADD(MILLISECOND, –3, DATEADD(YEAR,
        DATEDIFF(YEAR, 0, DATEADD(YEAR, –1, @enddate)) + 1, 0))

EDIT
What I want to return is

01/01/2017
02/12/2017
01/01/2016
12/31/2016


texts: How to Set the Transaction Isolation level in a Table Value Multi Function

texts:
How do I set the transaction isolation level within in a table value multi function? I know it can be done in a stored procedure.

create function dbo.Test()
RETURNS @TestTable Table (test varchar(1000))
AS
begin

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

I am receiving error below:

Msg 443, Level 16, State 15, Procedure abcde, Line 12
Invalid use of a side-effecting operator 'SET TRANSACTION ISOLATION LEVEL' within a function.


texts: I recently upgraded my SQL Server 2014 hardware. The CPU profile is looking weird. I have 24 CPUs, all CPUs are VISIBLE ONLINE, MAXDOP is 0. The SQL CPU usage is peaking at 60% and not going beyond that although the queries are highly CPU intensive and mostly in running/runnable state. I have checked the session log dumps that the Total Request Time is only slightly above the CPU time so have ruled out io waits, etc. Also checked the OS CPU usage is tracking only slightly higher than the SQL CPU usage. Wondering why CPU is not fully utilized. Previous hardware had 16 CPUs and used to saturate at ~95%


texts: SQL Server doesn't send intermediate SSL certificates

texts:
I am connecting to SQL Server (2008 R2) over open Internet and properly encrypting the connection is essential. I have COMODO certificate with two intermediate certificates between root CA and server certificate.

Now the problem is that SQL Server responds with truncated certificate chain where only the server certificate is included and intermediate certs are skipped. The client (JDBC in this case) refuses to validate server certificate due to incomplete certificate chain. I checked that the missing chain is the problem by running the program with -Djavax.net.debug=ssl,handshake.

Server (Windows Server 2008 R2) running the SQL Server has server certificate and its complete chain in its certificate database. SQL Server account (Network Service) was granted access to the private key. There's no option to grant access to intermediate certs, so I assume those are already available to all accounts on the computer.

Workaround #1: VPN

VPN is unfortunately out of question. It's very expensive to make VPN secure, highly available, and performant. VPN proved to be an obstacle to cloud service integration. So decision was made some time ago to kill it. The legacy VPN in the company is a performance and reliability nightmare.

Workaround #2: stunnel

I tried to sidestep the issue by wrapping SQL Server with stunnel, but SQL Server apparently uses some wrapped/modified SSL in order to support both encrypted and unencrypted connections on the same port. The JDBC client expects such non-standard SSL and fails to connect through stunnel. I could proxy the connection through stunnel client (essentially introducing point-to-point VPN), but this is unwieldy especially on developer machines.

Workaround #3: Azure SQL Database

I would really love to switch to Azure SQL Database, but other stuff in the legacy datacenter has grown used to the high LAN bandwidth to the SQL Server, so moving to Azure is a long-term prospect at best. I have to find a way to access SQL Server inside the datacenter securely.

Workaround #4: CA override in Java

So the last option I am considering is to embed the intermediate certificates in source code and override Java's default CA database (truststore), which can be done on per-connection basis. But then why did I purchase certificate from public CA? I could have just as well used self-signed certificate this way.

All Microsoft documentation tells me that SQL Server should be able to use certificate issued by public CA. So how do I make it do so?


texts: SQL Server 2014 Management Studio Backup error

texts:
I had installed Microsoft SQL Server 2014 Management Studio and had been working on a database for sometime now. My laptop had Windows 7 (64 bit) installed. And I had been taking regular backup on that machine and also stored the backup on an external drive.

Due to some reason my laptop stopped working. So now I started working on a different laptop. This other laptop has Windows 7 (32 bit) installed on it. I installed Microsoft SQL Sever 2014 Management Studio and restored my database using the .bak file.

I have been working on this database for sometime now and now the problem is when I try to take backup of my database I get this error:

Can someone please tell me how to resolve this error and how can I again take backup on this Windows 32 bit laptop?

Thanks


texts: From SQL Server to NuoDB: Common Table Expressions, Stored Procedures and Bulk Uploads

texts:
I am currently evaluating NuoDB as a replacement for SQL Server. The application is written in C#, .NET 4.0.

Now I am using features of SQL Server like:

Common Table Expressions (I have a table with a reference to itself composing a n-level parent-chilren hierarchy)
Stored Procedures
Bulk Uploads

I didn't find enough reference on NuoDB to understand if it is suitable to do these things:

Large select operations with hierarchy logics (now I use a recursive CTE on the 'items' table to accomplish this)
Complex updates/inserts thru some kind of algorithm (stored procs would be ok)
Bulk insert/update of 100.000+ elements (I am using bulk upload feature of SQL Server  .NET client and it works ok)

Besides this, I am also interested in partitioning, anybody has some experience in partitioning with NuoDB?

Thanks


texts:
I have a table with products with names in different languages:

product-id | lang-no | name

Now I want to generate product lists with names in each language. Unfortunately I don't have all names in all languages and must fall back to other languages if the right one isn't availabe.

Getting a list with a name in any language is easy:

SELECT DISTINCT product-id, name FROM mytable;

Is it defined which row gets picked by the database for the distinct product-id ? (MySQL in my case.)

How can I generate a list for language 1 and one for language 2 ?

Bonus: Can I define which language to fall back on ?

Update:

So my initial query is wrong. (Thanks!)

My list needs to include all products.

SELECT DISTINCT product-id FROM mytable

Now I need to add the names, preferrablx in one language, but no one language covers all products.


texts: Need to figure out what FKs point to a specific table

texts:
In SQLServer 2008 r2.  I need to figure for a specific table what FK's in other tables point to it.

I try:

SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

But this does not give me what I need.

Any tips?


texts: Monitor if sqlserver is up from distant server using sqlcmd

texts:
I want to monitor if my sqlserver is up from a distant machine.

I currently send a simple "select @@version" query with sqlcmd and then validate if that query returned the expected part of text :

sqlcmd -S np:\\<servername>\pipe\sql\query -i version.sql | findstr /I "expected string"

then I proceed to validate conditions based on this event.

Is there a better way to monitor db up events and db down events ?

Thanks


texts: Full outer joins, coalescing, indexes and views

texts:
I have two tables, let's call them TableA and TableB.

TableA               TableB
------               ------
ID                   ID
Date                 Date
Foo                  Bar

Both tables have an index on their date field.
Now, I want to create a view that has a full outer join between the two tables on the Date field, to show me the Foo value for that date (if there is one) along with the Bar for that date.

create view vFooBar as
select isnull(a.Date, b.Date) Date, a.Foo, b.Bar
from TableA a
full outer join TableB b on a.Date = b.Date

Then I run a query:

select * from vFooBar where Date = '20141201'

This works... but the coalescing of a.Date and b.Date fools the compiler into not using the indexes, and I get a horrible execution plan involving table scans.

How can I create this view in such a way that querying it will still use my indexes?


texts: SQL Server - granting permissions to an entire schema vs. object?

texts:
I'm very green when it comes to the world of database permissions management in SQL Sever.

Let's keep an example simple.

Say account 'admin' is the owner of schemas A, B, and C.

There another account 'minion' that you want to have full rights (update/delete/insert/select/alter) on any object (table/view) created under schemas A, B, and C.

Is this possible? Or do you have to execute a grant statement each and every time you add a table/ view under these schemas? (seems a bit silly to me).


texts: How to list all users that have a specific Temporary Tablespace like as default

texts: I'd like how list all users that have a specific Temporary Tablespace Default.


texts:
I'm new at SQL in general, so I'm have a few problems with some of my stored procedures I'm designing. My procedure takes in 3 parameters. A location (or table name basically, a varchar), a room number (varchar), and a phone number (char) in the form of 'xxx-xxxx'. Here is the code for the procedure.

ALTER PROCEDURE [dbo].[AddPhoneNumberToRoom]
    @Location    varchar(25),
    @Room        varchar(10),
    @PhoneNumber char(8)

AS
BEGIN
SET NOCOUNT ON;

    DECLARE @String varchar(100);
    SELECT @String = ' UPDATE ' + @Location +
                     ' SET PhoneNumber = ' + @PhoneNumber +
                     ' WHERE Room = ' + @Room;
    EXEC(@String);
END

The error I receive is:

  The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_Apartments_PhoneNumbers".  The conflict occurred in database "CMPhone", table "dbo.PhoneNumbers", column 'PhoneNumber'."

The phone number is in the PhoneNumber table, so when I do:

UPDATE Apartments SET PhoneNumber='123-4567' WHERE Room='2'

It works just fine. The PhoneNumbers table also looks like this, just has the one number in it.

PhoneNumber
-----------
123-4567


texts: An expression of non-boolean type specified in a context where a condition is expected, near 'List'

texts:
myconn.Open()
        Dim command As New SqlCommand("Select * from Food where Food List = @list", myconn)
        command.Parameters.Add("@list", SqlDbType.VarChar).Value = txtbox1.Text
        Dim adapter As New SqlDataAdapter(command)

Dim table As New DataTable()
        adapter.Fill(table)

the error i get will show at the adapter.fill(table) saying i have non boolean type at(Food List) how do i fix this any idea?beginner at vb


texts: A column of database type "geometry" results in two extra columns hidden from ssms

texts:
If I create a table like
create table test2 (foo geometry)

foo will show up in the table designer as expected

but when I query sys.columns for column information...
select
t.name as TABLE_NAME,
c.name as COLUMN_NAME,
c.precision as PRECISION,
c.scale as SCALE,
c.max_length as MAX_LENGTH,
c.is_nullable as IS_NULLABLE,
ty.name as DATA_TYPE
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.types ty on c.system_type_id = ty.system_type_id and ty.NAME != 'sysname'
ORDER BY c.column_id

...I also got a columns of the types hiearchyid and geography (using the same column name).

Why?
Can I change my sys.columns query to filter out those pseudo columns?
(I am using that query to recreate a create table query).


texts: SSDT - Deprecated table trigger preventing publish

texts:
There is a table trigger that was removed in the past that is blocking SSDT publish preview:

This deployment may encounter errors during execution because changes to [table] are blocked by [trigger]'s dependency in the target database.

but in order to remove it from the database it must either be manually dropped (since the publish preview is failing so not even predeploy scripts get run), or the option "drop objects in target not in source" must be enabled.

([trigger] is on a different table than [table]. The table containing [trigger] is having a column removed and [table] is having a column added. Neither column is even referenced by [trigger]!)

We cannot enable this advanced publish setting in general because of other migration scripts that exist in the postdeploy section that rely on deprecated objects, and we cannot have our integration team use this option either because it risks data loss on PROD. But we like to use this option periodically to (manually) test for leftover references to objects that were removed, because the build does not throw errors about them due to deferred name resolution (which presumably exists to enable what I consider a pungent code smell to create non-temporary objects at runtime), as the error can go unnoticed otherwise until a newer system that never had the object introduced is finally tested.

How is this situation fully resolved without pre-model scripting?


texts:
In the below expression I am wanting to evaluate several things. I want to turn the value red if the material % is greater than 89.9 %. If it is not then look at the following. is the invoice amount less than 75 dollars and the material % is greater than 20% then turn orange.

=SWITCH(Fields!Matl_.Value > 89.9, "RED", Fields!IHDAR_AMT1.Value < 75 and Fields!Matl_.Value > 20, "ORANGE", Fields!IHDAR_AMT1.Value < 76 and Fields!IHDAR_AMT1.Value > 150 and Fields!Matl_.Value > 30, "ORANGE", Fields!IHDAR_AMT1.Value < 151 and Fields!IHDAR_AMT1.Value > 250 and Fields!Matl_.Value > 40, "ORANGE",  Fields!IHDAR_AMT1.Value < 226 and Fields!IHDAR_AMT1.Value > 375 and Fields!Matl_.Value > 50, "ORANGE",  Fields!IHDAR_AMT1.Value < 376 and Fields!IHDAR_AMT1.Value > 625 and Fields!Matl_.Value > 60, "ORANGE",  Fields!IHDAR_AMT1.Value < 626 and Fields!IHDAR_AMT1.Value > 850 and Fields!Matl_.Value > 67.5, "ORANGE",  Fields!IHDAR_AMT1.Value < 851 and Fields!IHDAR_AMT1.Value > 1500 and Fields!Matl_.Value > 7500, "ORANGE",  Fields!IHDAR_AMT1.Value < 1501 and Fields!IHDAR_AMT1.Value > 2500 and Fields!Matl_.Value > 80, "ORANGE",  Fields!IHDAR_AMT1.Value < 3501 and Fields!IHDAR_AMT1.Value > 4500 and Fields!Matl_.Value > 84, "ORANGE",  Fields!IHDAR_AMT1.Value < 4501 and Fields!IHDAR_AMT1.Value > 5000 and Fields!Matl_.Value > 85, "ORANGE")

The way i have it now looks like it is stopping after looking at just this:

=SWITCH(Fields!Matl_.Value > 89.9, "RED", Fields!IHDAR_AMT1.Value < 75 and Fields!Matl_.Value > 20, "ORANGE"

Any help is very appreciated!

Tyler


texts: Grouping by more than 1 column using Partion By or any other method - Sql Server

texts:
I have the following tables in my SQL Server database:

Product
-Id
-Name
-Price
-TypeId (Foreign Key)
-SubTypeId (Foreign Key)

Type
-Id
-Name

SubType
-Id
-Name
-TypeId (Foreign Key)

I currently have a screen which displays products grouped by Type and SubType like this:

+------------+----------+-----------+
|    Type    | Sub Type |  Product  |
+------------+----------+-----------+
| Electrical | TV       | TV1       |
|            | TV       | TV2       |
|            | TV       | TV3       |
|            | TV       | TV4       |
|            | Hoovers  | Hoover1   |
|            | Hoovers  | Hoover2   |
| Furniture  | Bedroom  | Wardrobe1 |
|            | Bedroom  | Wardrobe2 |
|            | Dining   | Table1    |
|            | Dining   | Table2    |
+------------+----------+-----------+

Using windowing function i have managed to display the Type only once, my query:

    SELECT CASE WHEN RowNum = 1 THEN TypeName ELSE NULL END AS TypeName, SubTypeName, ProductName
    FROM (
    SELECT Type.Name AS TypeName, SubType.Name AS SubTypeName, Product.Name AS ProductName, ROW_NUMBER() OVER(PARTITION BY Type.Name ORDER BY Product.Name) AS Rownum
    FROM Product
        INNER JOIN Type ON Type.Id = Product.TypeId
        INNER JOIN SubType ON SubType.Id = Product.SubTypeId
    GROUP BY Type.Name, SubType.Name Product.Name
    ) X

What i want to do is have something similar for the sub type column so sub types are only displayed once like this:

+------------+----------+-----------+
|    Type    | Sub Type |  Product  |
+------------+----------+-----------+
| Electrical | TV       | TV1       |
|            |          | TV2       |
|            |          | TV3       |
|            |          | TV4       |
|            | Hoovers  | Hoover1   |
|            |          | Hoover2   |
| Furniture  | Bedroom  | Wardrobe1 |
|            |          | Wardrobe2 |
|            | Dining   | Table1    |
|            |          | Table2    |
+------------+----------+-----------+

How can i adjust or change my query to make this work?


texts: Current state of INTEGER vs NUMBER in Oracle

texts:
I am using Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production and I am trying to decide on using INTEGER or NUMBER for a whole number field that will hold a LONG from Java.

For reference:

Long.BYTES = 8
Long.SIZE = 64
Long.MAX_VALUE = 9223372036854775807 / 2^63-1

I was lead to a relatively old blog post from an answer on Stack Overflow that suggests that you should never really use INTEGER because it is has some signification performance issues compared to using NUMBER. Even for just ID columns.

  INTEGER is always slower then NUMBER. Since integer is a number with
  added constraint. It takes additional CPU cycles to enforce the
  constraint. I never watched any difference, but there might be a
  difference when we load several millions of records on the INTEGER
  column. If we need to ensure that the input is whole numbers, then
  INTEGER is best option to go. Otherwise, we can stick with NUMBER data
  type.

This article also states that "INTEGER is equivalent to NUMBER(38,0)" which I already knew. What I have not been able to determine if it is equivalent with the possible performance concerns that are raised.

I do want to ensure that all the values are nothing but whole numbers.
I will be working with millions of rows on a regular basis as well as joining on this column because it will be a foreign key to hundreds of tables.

The Oracle documentation says that

INTEGER, INT and SMALLINT all map to NUMBER(p,0)

So I am planning on using NUMBER(38,0) like I always have, lots of legacy databases and tables I am importing data from actually store this ID information in a VARCHAR(50) and the data is tremendously horrid trash because of people adding prefix/suffix characters to indicate state; like z343234 means disabled. So I am trying to clean this data up in the new systems and not allow the perversions that the previous developers/business analyst made. I am not concerned with saving the corrupt flags and other data, just the numeric part.

I work with so many different database engines I can not keep up with what the latest idioms are.

Are these performance issues actually issues to be concerned about 10 years later?


texts: Cannot SUM datetime field in SQL Server migrated from Access

texts: I am new here and am new to SQL Server (Express). I used the SSMA tool and it converted Access date/time fields to datatime2. The data contains duration data so there is no need for a date. I want to SUM the column but I need to change to a Time or numeric data type due to 8117# errors when I SUM. SQL Server will not allow a conversion using the designer / ALTER commands as there are always implicit/explicit conversion errors. Thank you for any possible solutions you may have to offer.


texts: Most efficient way to get stats on an entire SQL Server database

texts:
What I'm looking to do is review our databases and lock down any auto-shrink settings, as well as get a handle on which databases/tables are highly fragmented.

Is there a particular script I can run to get a good idea per database?

I know I can run the following on a per table level (SQL Server 2005 at least):

DBCC SHOWCONTIG ('DB.TABLE');

But what can I run to show me all tables in a database?

Thanks


texts:
Is there any way to use Postgres Listen/Notify feature to deliver a message to a channel and have only one listener consume this message?

The purpose for this is that I have multiple 'worker' apps all listening to the same Postgres channel. But I only want the work done once per message received through the notification channel.

If Listen/Notify is not the correct feature in Postgres, is there a seperate feature I should be using?

Ideally I would like to do this without using any additional extensions.


texts: MySQL, IF statement, variables, and NULL

texts:
After struggling for too long with something that should have been simple, I had a question to ask, but while writing it, I kept looking for solutions and I finally found the solution. I am posting it here anyway in case someone else has the same problem I had.

Description: get max value of a field from database. If that value is null, make it 1, otherwise increase it by one. Insert new record with that value.

Code I was trying:

select @nextNumber := max(number)+1 from test;
if @nextNumber == NULL then @nextNumber := 1;
insert into test (number) values (@nextNumber);

Turns out, the if statement can't stand on its own, at least not here.

Will put working code as an answer.


texts: How to compact the current MS Access database from VBA function

texts:
I want to be able to run the 'compact and repair' process from within a VBA module in the database.

I have a batch process that I run occasionally, it drops a few old tables, re-imports them from other databases, renames a couple of fields, does a few updates and makes a few other minor changes. The process isn't rocket science, but there are several steps so it really does needs to be automated.

The problem is that a couple of the steps (the UPDATEs) temporarily increase the size of the database which can cause problems with subsequent imports.

If I do the process manually (including compacting) then everything works fine and I end up with a 800MByte database. If I use my automated VBA script (without compacting) then it crashes halfway through when the database busts the 2Gbyte limit.

I've found several threads on this subject, but they're all three of four years old (or more) and the methods they describe don't seem to work anymore.

Are they're any solutions that work with Office 365 (version 1720)?

The 'auto compact' causes the database to compact on closing, it does NOT allow the compaction of the database to be added between steps.

I've tried this:

Public Sub CompactDb2()
  Dim control As Office.CommandBarControl
  Set control = CommandBars.FindControl(Id:=2071)
 control.accDoDefaultAction
End Sub

And this:

Public Sub CompactDb1()
    CommandBars("Menu Bar").Controls("Tools").Controls("Database utilities"). _
    Controls("Compact and repair database...").accDoDefaultAction
End Sub

And this....

Public Sub CompactDb3()
    Application.SetOption "Auto compact", True
End Sub

Amongst other


texts: What exactly should I backup in MongoDB when using replica sets?

texts:
I have several instances of MongoDB replica set servers with a primary and a failover machine for each replica set.

The primary machine makes daily backups by creating an LVM snapshot and copying all the data files to the backup location.

Compared to the size of the data those databases contain (one contains only a few documents which can hardly waste more than 1 MB of stored in JSON format), each backup is huge (3.1 GB), with large files being in journal directory, as well as local.1.

What files should I actually keep in backups?

All journal/prealloc.* ones should not be part of backup, which reduces the backups by 2 GB.

What about others:

The journal/j._*: I suppose they contain the actual journal data, but they are 1 GB large. Is there a way to make them smaller or remove them?
local.*: do I need them to keep replica set configuration?


texts: Methods of speeding up a huge DELETE FROM  with no clauses

texts:
Using SQL Server 2005.

I am performing a huge DELETE FROM  with no where clauses.  It's basically equivalent to a TRUNCATE TABLE statement - except I'm not allowed to use TRUNCATE.  The problem is the table is huge - 10 million rows, and it takes over an hour to complete.  Is there any way of making it faster without:

Using Truncate
Disabling or dropping indexes?

The t-log is already on a separate disk.

Any suggestions welcome!


texts:
I'm new to Mysql, and i'm reading this in a book: "A large log buffer allows large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have big transactions, making the log buffer larger saves disk I/O."

Ok...Perfect.. so i'm going to issue this statement: set global innodb_log_buffer_size=xxx;
Mysql reply to me with: "ERROR 1238 (HY000): Variable 'innodb_log_buffer_size' is a read only variable"

So, How can i make it bigger in case i have big transaction, and need to increase the  innodb_log_buffer_size?

I use mysql 5.7 on Windows.

Thank you.


texts: Why has replication stopped working with "ASN: bad other signature confirmation" error?

texts:
We have had replication running for over two years, from a master to a slave, using SSL. Yesterday, it suddenly stopped working.
Neither the master or slave have undergone updates and none of the configuration has changed on either.
But now replication is behind and we're getting the following message when I SHOW SLAVE STATUS\G;:
Last_IO_Error: error connecting to master 'replication@12.34.56.78:3306' - retry-time: 60  retries: 2

I tried connecting on the command line, specifying the same certificates (ca.pem, client-cert.pem and client-key.pem) and using the same user setup for replication, but that didn't work, giving me the following error:
ERROR 2026 (HY000): SSL connection error: ASN: bad other signature confirmation

I have checked the CA to make sure it hasn't expired.
I can connect without SSL, simply by using a standard mysql -u replication -p -h 12.34.56.78 call without specifying any SSL information.
I tried to see if replication would work without SSL off the back of that. I ran:
RESET SLAVE;
CHANGE MASTER TO MASTER_USER='replication', MASTER_HOST='12.34.56.78', MASTER_PASSWORD='*********', MASTER_SSL_CIPHER='', MASTER_SSL_CA ='', MASTER_SSL_CERT='', MASTER_SSL_KEY='', MASTER_SSL=0, MASTER_LOG_FILE='mysql-bin.001484', MASTER_LOG_POS=22423344;

But now when I start the slave again I am getting the same error connecting to master error as above.
Why is my connection suddenly broken? And why can't I connect without SSL if I can connect via the command line successfully?


texts:
My SQL Server version is

  Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64) Jun 11 2012 16:41:53

and the query below just runs without producing an error or updating.  The table [dbo].[Data] will produce roughly 15 entries that would need to be updated (has less than 1,000 rows in the table itself while [dbo].[Domains] has just under 9000 rows to check against.

Is there a better/quicker way for me to write this query so the appropriate rows will be updated?  Below is sample DDL that has the same data structure as the actual production table I would be using this query with.

 CREATE TABLE [dbo].[Domains](
 [DOMAIN] [varchar](max) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Domains] ([DOMAIN]) VALUES (N'gmail.com')
INSERT [dbo].[Domains] ([DOMAIN]) VALUES (N'hotmail.com')
INSERT [dbo].[Domains] ([DOMAIN]) VALUES (N'aol.com')
INSERT [dbo].[Domains] ([DOMAIN]) VALUES (N'yahoo.com')

CREATE TABLE [dbo].[Data](
  [email] [nvarchar](75) NULL
  ,[accept] [nvarchar](20) NULL
 ) ON [PRIMARY]
 GO
 SET ANSI_PADDING OFF
 GO
 INSERT [dbo].[Data] ([email]) VALUES (N'1234@gmail.com')
 INSERT [dbo].[Data] ([email]) VALUES (N'1234@hiho.com')
 INSERT [dbo].[Data] ([email]) VALUES (N'1234@aol.com')

 UPDATE dt
 SET [accept] =
        case
            when EXISTS (SELECT TOP 1 [DOMAIN]
                         FROM [dbo].[Domains] dmn
                         WHERE [dmn].[DOMAIN] = SUBSTRING([dt].[email],     CHARINDEX('@', [dt].[email]) + 1, LEN([dt].[email])))
            THEN 'NO'
            ELSE 'Yes'
        END
FROM [dbo].[Data] dt


texts: Find Employees With Multiple Managers Listed

texts:
We outsourced a project and are paying for it!  In our table which holds roughly 1500 employees a manager is assigned, but some employees have duplicate managers assigned.  How can I using SQL Server 2008 R2 return only each employee who has more than 1 manager assigned (so query window should return the employee and both managers assigned)?

Sample DDL to show table structure and how bad it is messed up atm

Create Table #DuplicationIdentifier
(
  empName varchar(500)
  ,empID varchar(25)
  ,managerID varchar(25)
)

Insert Into #DuplicationIdentifier VALUES
('Blue Bell', 'bb12', 'fj12')
,('Joe Jones', 'jj30','bb41')
,('Mark Jones', 'mj91','ll98')
,('Surge Drink', 'sd22','ll98')
,('Paul Pills', 'pp23', 'ss10')
,('Red Color', 'rc84','ss10')
,('Pink Flamingo', 'pf04','bb41')
,('Blue Bell', 'bb12','bb41')
,('Joe Jones', 'jj30','ss10')
,('Red Color', 'rc84', 'rd22')
,('Pink Flamingo', 'pf04','fj12')


texts: mysql Wrong bytesec: 0-0-0 at linkstart: 0

texts:
I have several MySQL (MyISAM) tables that have been corrupted. Running CHECK TABLE mytable returns:

> +-----------------+-------+----------+----------------------------------- |

> clldata.mytable | check | warning  | Table is marked as crashed

> clldata.mytable | check | warning  | Size of datafile is: 4654864 Should be: 4602520

> clldata.mytable | check | error    | Wrong bytesec: 0-0-0 at linkstart: 0

> clldata.mytable |
> check | error    | Corrupt
> |
> +-----------------+-------+----------+-------------------------------------------------------+

4 rows in set (0.01 sec)

Running REPAIR TABLE mytable results in truncating about 90% of the rows that were originally in the table (and the size of the .MYD file also shrinks about 90%). And most of the remaining rows in mytable are clearly actually from one of the other corrupted tables.

Is there any hope of recovering data from such a table?


texts: Converting 3 Master + 1 Slave to 4 Master - Any advice?

texts:
so we have a beautiful 3 master 1 slave cluster running.

we would like to convert this to a 4 master cluster.

here is how the topology currently looks

s1

enslaved by s3

s2

enslaved by s1

s3

enslaved by s2

As it stands now, all servers are fully in sync and used by a live running application.

Question Part 1: What mysql commands need to be executed on each of the servers to reset the current binlog position to 0 and sync this action to whatever server is enslaved by the one where the commands are executed. Just to clean things up prior to making the switch.

Question Part 2: We can easily change the source code running to ignore one of the servers. Each of the nodes can support the whole application running live, so we can take each of the servers down one by one. What commands need to be executed to basically do the following:

Pause updating the node from a master. But make it so when switching to the new master, the syncing of the database will continue as left off. So I am assuming that ultimately 2 servers will need to go down at this point. One of which being the slave that's switching from slave to master/slave. I should also mention, we have the ability, and use it often, to basically pause the inserting / updating of 'the main table'. Most of the other data is non-critical items such as logging, etc. So even if a few rows (or even some full tables) are lost due to the servers being out of sync its OK.

So that pretty much sumarizes it. We want to convert a running and live 3 + 1 combo to a 4 combo. All 4 servers are in sync, we have a few tools available on the code side to make taking 1 to 2 servers down at a time safe.

We should be able to do this without any downtime.

Any and all advice appreciated.

Thank you kindly.


texts: Why is it not possible to "SELECT" Data as parameters for certain clauses like LIMIT?

texts:
EXAMPLE:

CREATE TABLE t (A INT);
INSERT INTO t VALUES(1),(2),(3),(4),(5);

This works:

SELECT A FROM t WHERE A=(SELECT 1)

This does not:

SELECT A FROM t LIMIT (SELECT 1);

This works:

SELECT A FROM t WHERE A=substring((SELECT 123),1,1);

This does not:

SELECT A FROM t WHERE A=1 PROCEDURE ANALYSE((SELECT 1),10000);

What makes some clauses and functions support SELECT statements and others not support it. I would think that running (SELECT 1) returns a 1 as a parameter and as long as the parameter is the expected type it should work. This seems to be true in some cases, but not others. Why is that?


texts:
Which one is correct?

With transaction 1 as Repeatable Read, other transactions can not update a row after it has been selected by transaction 1.
With transaction 1 as Repeatable Read, other transactions can update a row, but transaction 1 does not take into account those changes.

I before believed that (1) is right. But when I tested sample transactions on machine, (2) is correct. I am using MySQL version 5.1.2.re Community.


texts: Amazon Redshift - The difference between Query Slots, Concurrency and Queues?

texts:
We are building a business intelligence system, and we have a huge PostgreSQL Database (DB) where we make all the info processing, and a Redshift Data Warehouse (DWH) where we store the data and execute queries.

The backend is built in Java Server Faces (JSF), and before, the queries were made lineal. Some views were taking more than a minute to finish queries and load the info in screen, so we decided to use threads in Java and make the queries asynchronous.

So we have prepared the necessary queries for each view to be run parallel from our EC2 app machine, to our Redshift DWH, and run the threads but views still take long to load, sometimes even longer.

We found in the documentation:

http://docs.aws.amazon.com/redshift/latest/dg/cm-c-executing-queries.html

http://docs.aws.amazon.com/redshift/latest/dg/c_troubleshooting_query_performance.html

http://docs.aws.amazon.com/redshift/latest/dg/r_wlm_query_slot_count.html

That redshift by default receive 5 queries at same time, but that is a setting we can change.

There are 3 main things to take into account: query slots, concurrency and queues. We have understood this:

A queue is like a thread in Java. A query arrives and is designated to the "less loaded" queue, and it waits for its turn to be resolved. We can have as many queues as we like. A queue has some memory allocated (we guess divided equally?)
In a queue we can assign user groups or queries groups. But in short-term, that's a lot of classification work in our queries we can't do right now.

A concurrency is the amount of queries that a queue can run in parallel. By default is 5.

And a query slot is the amount of memory a query can use. It's related to concurrency as we understood it. The more concurrency a queue has, the less memory in each query slot it has.

We have tried to have 3 queues, each one with concurrecy 5, but performance still slow.

So, if we have understood correctly, having that some viewes make up to 25-28 queries, and the total amount of loading time is around 60s, how can we leave the settings the queries can be resolved faster?


texts: Management Studio 2012 Backward Compatibility

texts:
Two quick questions for anybody that's tried this:

Will Management Studio 2012 install and run side-by-side with, say, Management Studio 2008? I vaguely recall not being able to do this with 2008 and 2005.
Does Management Studio 2012 fully support connecting to and administering SQL Server 2008? Does Intellisense still work against older versions?


texts: Database in simple recovery to save space

texts:
This old SQL Server 2000 instance has a 14Gb disc for data and a 30Gb disc for backups.

Even though it is old, it still has tons of users doing transactions so the log file of the only database that is on this server fills the data drive everyday.

I figured I would put it in simple recovery and flush the transaction log, but I wonder what it impacts.

Without a T-log you cannot use your backup to recover to any other point in time than the time of the full backup, right ? What are the most important impacts apart of this I should be aware of when doing such a change to a database ? What else than losing a day of data (it is backup up daily to tape) might happen when I'm not generating t-logs ?

Thanks in advance


texts:
Some time ago, I've built an webapp on top of WAMP server. At that time, I didn't do any export/dump of my MySQL DB but I've did a copy of the WAMP folder. Does anyone have any ideas about how I can restore my DB from the 'mysql' folder of WAMP?

I have the *.frm files and lots of mysql-bin.* files

Do you have any advice for me?


texts: Oracle - Query to join fact table to bridge table and show bridge group rows in one row?

texts:
I have a data warehouse with a One-To-Many relationship:

"Fact" table contains: Product_Group_ID
"Product_Bridge" table contains: Product_Group_ID, Product_ID
"Product" table contains: Product_ID, Product_Name, other product attributes..

I've inserted the data into the relevant tables, but how can I query the database and retrieve all products from a product group in the one row result? I've never used a bridge table before and could not find any decent/helpful examples online for how to actually query between the bridge/fact/dimensions.

i.e. the desired result would look like:

product_group_ID product_ID product_name product_ID_1 product_name1 product_ID_n product_name_n

Any suggestions would be greatly appreciated.


texts: Login to SQL Server cross domain AD Group user

texts:
Our MS SQL Server is on another trusted domain (Trusted Domain C). We created a Universal Group within Domain A with users from different forest domains and added the AD group onto the SQL Server. When the user from Child Domain B tries to connect, they get the error: "Login failed for user".
If I add the user directly instead of the AD Group in SQL Server, they can login without problems. Furthermore, if I create a AD group in Child Domain B, include all the users from that domain and add it to SQL Server, they have access.
Domain Forrest

Domain A

Child Domain B

Trusted Domain C

I'm on SQL Server 2016 (13.0.4001.0)
SQL Server error logs displays the error for that particular user: Reason: Could not find a login matching the name provided.


texts: Why is setting open_files_limit making MySQL slow?

texts:
We noticed that adding open_files_limit in /etc/my.cnf is making server slow.

Reference : What variable(s) to look to maintain MySQL speed of scanning through all database tables while it starts?


texts: Issue shrinking dev sql server DB's log file

texts:
I have a development db that's running on sql 2014 in 2012 compatibility that I'm having an issue with trying to shrink the log file.    The mdf is 60+gb and the log is 80+gb but since it's dev like to shrink this down.    I've tried shrinking the log after backing up the db from both the gui and command line but doesn't work.  I switched the db to simple recovery model, backing up and trying to shrink multiple times but still nothing. I've also tried dbcc shrinkfile to either truncate the file or set it to a specific size but nothing..  when I run dbcc loginfo I do see entries with the status '2' but backing up the db/log in various attempts doesn't seem clear this out like normal.    Since its dev I even tried deattaching and reattaching without the log and it throws an error, if I try to create the db (that's still unattached) using 'for attach' or 'for attach_rebuild_log' it will either fail or create the log file with the same size and issues as before.   I checked the log_reuse_wiat_desc and it shows 'XTP_CHECKPOINT' which I believe is an in memory setting so wondering if the developer was testing any in memory objects if it could have caused this issue?

Sure appreciate any thoughts on this!  again since it's a dev db, that's backed up, its not a show stopping issue but like to figure this out in case it happens in prod.

Thanks,
Jeff


texts: Profiler: Only want to see Provider Errors

texts:
I'm replaying a trace in Profiler, and I'd like to only see the Provider Errors.  Granted, there are ~30,000 of them but I'm not concerned at the moment about row update conflicts, foreign key or unique index issues.

Currently, I'm output the trace from the replay to file.  I've tried creating a table from the result trace file, but either I'm missing something or the Provider Errors are not being imported into the table:

SELECT *
  INTO MyTraceTemp
  FROM ::fn_trace_gettable('c:\x\MyTrace.trc', default)

This is using Profiler against SQL Server 2008R2, if that matters.


texts: How Should I Configure Memory for Multiple Instances of SQL Server?

texts:
We have a set of SQL Server instances (2008 R2) that run on the same virtual server.  Each instance represents a stage in our development cycle (Dev/Test/Stage/etc.).  The instances all need to be online at any given time, but load tends to be isolated to one instance as a time, depending on where we are in the release lifecycle.

SQL Server seems to grab and hold whatever amount of memory we set as the max.  What I'd like to know if there is any way of configuring the instances to be "smart" about memory consumption so they are not blocking each other from getting memory when needed.


texts: Is there a T-SQL equivalent for punctuation as [0-9] is for numbers and [a-z] is for letters?

texts:
Is there a T-SQL equivalent of the [0-9] and [a-z] patterns that will let me pull values from a column that contains punctuation?

For example:

Create Table #Test
(
Value   VarChar(10)
)
Insert Into #Test
Values ('123a'), ('456b'), ('12ABC'),('AB!23'),('C?D789')

Select      *
From        #Test
Where       Value like '[0-9][0-9][0-9][a-z]'

This would return values where the first 3 characters are numbers between 0 and 9 and the last character will be a letter between a and z, so would return things like 123a and 456b but wouldn't return a value of 12ABC.

I want to know if there is an equivalent for punctuation as [0-9] is for numbers and [a-z] is for letters so that it would return AB!23 and C?D789?

If I could use a regular expression I might use the expression ^[a-zA-Z0-9]*$ to match alphanumeric characters in a string.

Where       Value like '^[a-zA-Z0-9]*$'

Is there a SQL equivalent for this?

I know this kind of thing that can be done in RegEx but I need it in T-SQL, I am not able to load any custom assemblies onto this server so can't use regular expressions.

The real column is varchar(200). The collation is Latin1_General_CI_AS. I am using SQL Server 2012 Standard Edition.


texts: how can i get an accurate count based on max date when joining 3 tables when one of the join fields is many to 1?

texts:
So, I have 3 tables that I am attempting to get counts for based on a groupid, and a task code.  There are a few issues I am having as some of the relationships are many to one, which I think is somehow inflating my counts.  I will list my 3 tables with the pertinent attributes.
task_table contains:
task_code - would like to get the counts of each one in a group id, would like to use the latest instance basedon event date.
sol_id -used to join to worktable; many sol_id to one m_id is possible
edate -need to use to get one record
cur_id - where cur_id = 1 in the where clause
worktable contains:
sol_id - used to join to task_table
m_id - used to join to grouptable
grouptable contains:
m_id
groupid- used to group the task_code to get count
I'd like the end result to look like:

group_id
task_count
task

5555
45
A

5555
4
N

5624
67
A

5624
23
O

5624
42
X

I have been attempting to run a number of queries, but the counts I am getting back do not look correct.  I am concerned that it is somehow returning more than one instance of the m_id somehow?  Here is the query in question:
select  c.groupid, count(c.groupid) group_count, a.task_code from task_table a
join worktable b
on a.sol_id = b.sol_id
join grouptable c
on b.m_id= c.m_id
where   a.cur_id = 1 and a.task_code is not null
group by c.groupid, a.task_code;

If I add 'edate = (select max(edate) from task_table)' in the where clause, it returns an empty table.
I am unsure how to incorporate edate to get only the newest record that fits the criteria in the where clause.  The reason I think I want to use this is because there could be more than one sol_id that is associated with a m_id, so i'd just like to include only the newest record with a cur_id in the count. Thank you for your time.
sample data
task_table

task_code
sol_id
edate
cur_id

A
23
6/7/09
1

A
24
6/4/09
1

A
23
6/10/09
0

B
45
6/2/09
1

B
42
6/3/09
1

C
34
10/8/10
0

C
83
9/10/09
1

work table

sol_id
m_id

23
1234

24
1234

45
1832

42
1343

83
7623

group table

m_id
group_id

1234
A76

1832
Y23

1343
A76

7623
Y23

looking at these tables, the result should look like the following

group_id
task_count
task

A76
2
A

Y23
1
C

( A76 should only count sol_id 23 and 42) ( Y23 should only count sol_id 83)


texts: Getting past corrupted binary log "Error in Log_event::read_log_event():"

texts:
I have a binary log that mysqlbinlog chokes on with the error in the title.

The file itself has much more activity after the cited position.

Doing some basic confirmation it's not all garbage by running it through the strings command shows theres legit traffic until the end of the file when it got rotated.

I've seen a similar post about using hexdump to get past an error related to event too large, but in my case mysqlbinlog chokes to continue to get further information.   I'm not familiar enough with the binary format to look for what might be a position of a next event it would recognize.

It gives a starting position it can't get past so I have a script running to basically mysqlbinlog --start-position=X incrementing X by one until it returns with a 0 exit code but that looks like it's going to take a month to completely get through everything at this rate.

I tested the POC of this idea on "good parts" by starting it at weird offsets and it returned correctly at the next one it found w/o error.

I'm running percona 5.6.20 for this instance.

I realize this report might be lacking in information needed to answer the question so I'm happy to edit with comment requests as needed.


texts:
I want to flush shared pool because of error like

ORA-04031: unable to allocate 32 bytes of shared memory

So I want to flush shared pool using

alter system flush shared_pool;

Connected as sysdba using

hwflow08:flman800 > sqlplus "conn as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Jan 28 05:39:56 2015

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Enter password: Connected.

While executing alter system command we faced ORA-01012

SQL> alter system flush buffer_cache;
alter system flush buffer_cache
*
ERROR at line 1:
ORA-01012: not logged on

Can anyone please guide how can I flush the Shared Pool ? (I dont want to shutdown the DB nor want to increase size of shared pool)

Previously Same issue Came :

I left with option to SHUTDOWN DB. So after STARTUP , when i connected through SYSDBA and executed same query alter system flush shared_pool; That time it works perfectlly fine. So question remains as why ORACLE is not allowing flush shared pool when it is out of memory ? What is efficient way to use alter system flush shared_pool;

EDIT :
Seems like its a 10g BUG


texts:
I'm working with Microsoft Reporting Services 2008.  In our database, we have a small group of confidential students that need to be taken into account for several queries.  If they are a confidential student, the database needs to essentially return nothing.  Currently, we do something along the lines of:

select
  case
    when CONFIDENTIALITY_IND = 'Y' or :EmployeeClass = 'XA'
      then 'CONFIDENTIAL'
    else db.table.name
  end as name
from
  db.student

This isn't a big deal for such a small query, but most of them return many fields (often 20+) and I'm not a fan of so many case statements for obvious reasons.  Because of how Reporting Services work, I can't do a general replacement either, it must be done on each and every field.

Is there a more efficient method where I could replace every value in the row with 'Confidential' in a single case statement, or at least something more elegant?

Edit:  To clarify, that there isn't just this one field in the select.  I only wrote one for the example, but in production, some reports are looking at displaying a huge amount of columns.  I'm trying to avoid making two comparisons on every column for reports that could return rather large sets of data.


texts:
I have just installed Ubuntu on my server and MySQL.

I can't make any change in MySQL (seeting password, creating database, etc.) as I ALWAYS get an Access denied error message. I have followed the MySQL manuals re passwords, but to no avail.

The user name I use is the account name created when Ubuntu was installed.

At some point, I am told I need SUPER privileges to do this or that but I can't change the privileges since I get the same error message. I can't even see the GRANTS as I also get the same access denied message.
mysql version 14.14 Dist. 5.5.32, for debian-linux-gnu

Any idea on how to solve this?

To Arka:

Editing my own question is the only option I have to post this:

mysql> status

mysql Ver 14.14 Distrib 5.5.32, for debian-linux-gnu (i686) using readline 6.2
Connection id: 63
Current database:
Current user: charles@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.5.32-0ubuntu0.12.04.1 (Ubuntu)
Protocol version: 10
Connection: localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/run/mysqld/mysqld.sock
Uptime: 6 hours 58 min 20 sec
Threads: 1
Questions: 165
Slow queries: 0
Opens: 211
Flush tables: 1
Open tables: 51
Queries per second avg: 0.006

Now, I have not been able to create a single database since the installation of the server this morning. I hope that helps. Thanks. :)


texts: SQL Server Backup Database not .bak file format

texts: I've been using SQL Server 2012 for as long as I can remember. When I do a backup it's always saved as .bak file format. However, there's this other server that has SQL Server 2012 and when I do a backup, it's not saved as .bak but just a generic 'file' file format. When I try to restore this file it works just fine. I'd just like to know why it didn't save as .bak file. In other servers that I've worked on it's always .bak. Thank you so much.


texts: SSMS Intellisense - Data Dictionary Incorporation - Show Object Descriptions in Popup?

texts:
Can anyone point me in the right direction for an SSMS intellisense replacement that will show the object descriptions in the popup?

This will help speed up onboarding as well as making myself familiar when dev hands me a new beast to manage.  Object names should always be fairly descriptive, but sometimes what made sense 10 years ago makes little now, and cross-referencing a dictionary doc is a productivity barrier.

Mockup example using AdventureWorks:


texts: SQL Server Wildcardless LIKE Optimization

texts:
In a web application using SQL Server 2008 R2 as the backend, I have several fields, that if present, add WHERE clauses to the query.

If I was to have all queries generated as like clauses, such as:

AND Uri LIKE @Uri

If Uri contains no % , is the optimizer smart enough to treat that as WHERE Uri = @Uri or do I have to handle that application side?

I realize that the optimizer could change out from under me someday and this is a bit sloppy, but I am still curious.

Example Plans

I have done a one off test that seems to suggest that this is maybe the case with the >= ... <= conditions:

Vs. Where Clause:

So they both trigger index Seeks on that contrived Index. Is >= ... <= the same as = in this case, and will this always happen?


texts: Database Migration with Liquibase using different DBMS

texts:
Our customer is using Oracle and we want to use MySQL for development. Therefore our MySQL Scheme has to be in sync with Oracle. I tried to use Liquibase, but I get problems at the time of applying a changeset because of the db specific sql and different column types like NUMBER <-> BIGINT or VARCHAR <-> VARCHAR2

Am I using these tools wrong? How to solve this type of problem?


texts: I cannot login to a user I just created in a PDB

texts:
Just installed Oracle 12c client and server for the first time (on same machine) so please bear with me as I'm quite new to this. It's in the multitenant mode.

So I went to the server's BIN directory and ran sqlplus / as sysdba. The SID is TESTDB and looking at show pdbs; I could see that there is one PDB called BPDB1. So I switched to it with alter session set container = BPDB1 and opened it with alter database open. Now I created a user with CREATE USER testtest IDENTIFIED BY password which seemed to work.

After that, I thought I can login to the PDB from the client's BIN directory using sqlplus testtest/password. But that just gives me ORA-01017 invalid username/password; login denied.

I wonder if this is because the client doesn't know which PDB to connect to? How would I tell it this?

By the way, not sure if this is relevant but I tried setting ORACLE_HOME to the root of my client installation, but if that I got some other errors every time I try to start sqlplus. It hinted that ORACLE_HOME was set incorrectly, so I just unset that variable to continue.


texts: Databases in psql Don't Show up in PgAdmin4

texts: I'm using Ubuntu 18. I have PostgreSQL 11 and PgAdmin4 and I connect to PgAdmin4 from my browser by typing localhost/pgadmin4/. Recently, I found that the url doesn't work and internal server error appears. I suppose it was because of some update. Then I connected to psql from my terminal by psql -d analysis -U  command. When I connected to psql by terminal I could see my tables I created before. When I ran pgadmin4 by terminal, a page was opened in my browser and I logged into it by typing my password, but there were no servers and tables I created before. Because working with psql on large tables are problematic, I want to use PgAdmin4 as before and see my tables, how can I make PgAdmin4 to show my servers and tables again? Thanks.


texts: Why does database recovery take so long?

texts:
On my SQL Server 2012 x64 Std instance I had a large transaction fail due to the transaction log filling up yesterday, it couldn't be rolled back and SQL Server has restarted the specific DB to perform recovery.

The DB is around 750GB with the tlog hitting 170GB (very large ETL job was running). The job was only running for a few hours but the recovery so far has taken >24 hours (70% complete, on phase 3 of 3).

What I can't understand it why it's taking so long? There doesn't seem to be any pressure on the disks, sys.dm_exec_requests shows it waiting on PAGEIOLATCH_EX/SH, which I'd expect. I could've restored the entire DB in this time...

If anyone can shed any light, it would be greatly appreciated.

EDIT: As requested, getting the output of the error log:

Recovery of database 'MyDB' (6) is 77% complete (approximately 28100 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
Recovery of database 'MyDB' (6) is 77% complete (approximately 28095 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
Recovery of database 'MyDB' (6) is 77% complete (approximately 28099 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
Recovery of database 'MyDB' (6) is 77% complete (approximately 28102 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
Recovery of database 'MyDB' (6) is 77% complete (approximately 28097 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.

Also, the sys.dm_exec_requestsinfo you're after:

session_id  command status  percent_complete
35  DB STARTUP  background  86.06061

IFI and "Perform volume maintenance tasks" enabled/granted.


texts:
I have the following table:

id   | name   | action | count
------------------------------
 1   | Value1 |    0   | 27
 1   | Value1 |    1   | 49
 2   | Value2 |    0   | 7
 2   | Value2 |    1   | 129
 3   | Value3 |    0   | 9
 3   | Value3 |    1   | 7

I need to make the 'action' column appear twice, with the count value of each line in it, something like this:

id   | name   | action1 | action2
---------------------------------
 1   | Value1 |    27   | 49
 2   | Value2 |    7    | 129
 3   | Value3 |    9    | 7

How can I do this? Here's my script:

SELECT m.id,
    t.name,
    m.action,
    count(m.id) AS count
FROM table1 m
LEFT JOIN table2 t ON (m.id = t.id)
WHERE m.status != '2'
GROUP BY m.id,
    t.name,
    m.action
ORDER BY 1, 3


texts:
I have a strange problem with EMS SQL Manager.

I'm using MySQL 5.5 with linux servers. One of my developers need to edit store procedure, so I grant him:

GRANT CREATE ROUTINE, ALTER ROUTINE ON `testdb`.* TO 'testuser'@'192.168.13.11'

When he clicked edit store in EMS SQL Manager, what he got is:

SELECT command denied to user 'testuser'@'192.168.13.11' for table 'user'

I find out that EMS SQL Manager ask for select privilege in some tables, so I need to grant this user some extra permissions.

GRANT SELECT ON `mysql`.`user` TO 'testuser'@'192.168.13.11';
GRANT SELECT ON `mysql`.`db` TO 'testuser'@'192.168.13.11';
GRANT SELECT ON `mysql`.`tables_priv` TO 'testuser'@'192.168.13.11';
GRANT SELECT ON `mysql`.`columns_priv` TO 'testuser'@'192.168.13.11';
GRANT SELECT ON `mysql`.`procs_priv` TO 'testuser'@'192.168.13.11';

So my question is, WHY EMS SQL Manager need those privileges?
I can create, drop procedure in mysql command line client without problem.

Update

After some investigation, I still do not understand EMS SQL Manager.
This is the action EMS SQL Manager take when click the Compile button.

Executed  : 5/8/2013 3:10:40 PM
Operation : CONNECT
Result    : "OK."

Executed  : 5/8/2013 3:10:40 PM
Operation : show variables like 'character_set_results'
Result    : "OK."

Executed  : 5/8/2013 3:10:40 PM
Operation : SELECT * FROM mysql.user ORDER BY User, Host
Result    : "SELECT command denied to user 'ems'@'192.168.13.11' for table
'user'"

Executed  : 5/8/2013 3:10:40 PM
Operation : DISCONNECT
Result    : "OK."

Executed  : 5/8/2013 3:10:41 PM
Operation : select p.`db`, p.`name`, p.`type`, p.`specific_name`,
p.`language`, p.`sql_data_access`, p.`is_deterministic`, p.`security_type`,
p.`param_list`, p.`returns`, p.`body`, p.`definer`, p.`created`,
p.`modified`, p.`sql_mode`, p.`comment` from `mysql`.`proc` as p where
p.`type` = 'PROCEDURE' and p.`db`='reportingdb' and
p.`name`='100_rpt_campaign_ip'
Result    : "OK."

Executed  : 5/8/2013 3:10:41 PM
Operation : SHOW CREATE PROCEDURE `reportingdb`.`100_rpt_campaign_ip`
Result    : "OK."

So my question, What is the Compile button trying to do? Why does it want to select mysql.user? I think that editing a stored procedure is two steps of drop and re create it, why does it want to do more?


texts: Unbelievably slow and unusable Query Store

texts:
I'm beginning with Query Store and it's with problems :-( No report of Top Resource Consuming Queries is ever generated. It just keeps saying "Waiting" for hours and hours. Query on the background (see below for an example) is not able to finish and is consuming lot of CPU. No matter how am I changing configuration options, it keeps waiting and waiting (even last hour report). Is this the reality of Query Store all over? Sounds being such a great tool but is totally unusable in real?
My Query Store size is about 1,7 GB. I'm collecting 7 day behind, in 1 hour interval and Auto Capture mode - so it seems to be reasonable settings, to me.
This is an example of a query on the background which is never finished:
SELECT TOP (@results_row_count)
    p.query_id query_id,
    q.object_id object_id,
    ISNULL(OBJECT_NAME(q.object_id),'') object_name,
    qt.query_sql_text query_sql_text,
    ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))*0.001,2) total_duration,
    SUM(rs.count_executions) count_executions,
    COUNT(distinct p.plan_id) num_plans
FROM sys.query_store_runtime_stats rs
    JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id
    JOIN sys.query_store_query q ON q.query_id = p.query_id
    JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE NOT (rs.first_execution_time > @interval_end_time OR rs.last_execution_time < @interval_start_time)
GROUP BY p.query_id, qt.query_sql_text, q.object_id
HAVING COUNT(distinct p.plan_id) >= 1
ORDER BY total_duration DESC

Any ideas how to make it operational?
EDITORIAL:
I have opened a case with Microsoft about this. We didn't solve it but came to a workaround. If I change Query Store Capture Mode to All (not using Auto mode) - then things go fine and the reports are generated in a timely fashion.
EDITORIAL #2
This has come to a completely different issue. The problem is not the Capture Mode but Legacy Cardinality Estimator which the database is running under.
Please vote for this Azure Feedback: https://feedback.azure.com/forums/908035-sql-server/suggestions/37971781-slow-or-non-operational-query-store-under-legacy-c


texts:
I have some questions, that I like to know to my MSc research.

Lets consider following situation:
I have two small tables A and B.
And two big tables C and D.
I want to put A and B in TimesTen and C and D (because they are to big) in standard Oracle database.
Can I configure system that way, that queries to A and B will be RAM-only queries and queries for C and D will be standard database queries?
I want this to work transparent to user: user makes query and don't know, if he is querying TimesTen or standard Oracle.
If above is possible: What about queries in which user joins A and C? Will A be read from memory or from disk?

I don't need to know how to do it. Only if its possible.


texts: Row level security and physical user creation is it an overhead

texts:
The database that I am currently working has RLS implemented as suggested in Microsoft blogs and a few user blogs.

Example: creating a physical user and EXEC AS USER and selecting from tables for the user selected and REVERT

CREATE USER User1 WITHOUT LOGIN;
CREATE USER User2 WITHOUT LOGIN;
CREATE USER User3 WITHOUT LOGIN;

What if we exhaust the limit for physical users (I think it is server_prinicipal_id whose limit is int), what if your application supports millions of users whose data needs to be restricted based on what they can see?

Is creating physical user for every logical user not an overhead?
Is that a best practice?
Is there a performance overhead?


texts: dependant subquery processed despite Extra Impossible WHERE noticed after reading const tables

texts:
in this query https://www.db-fiddle.com/f/snzSLd8y1oRNRqdsFpNdHa/0

SELECT test0.id,
(SELECT GROUP_CONCAT(test2.one_info) FROM test2 WHERE test2.one_id = test1.id) AS GC
FROM test0
INNER JOIN test1 ON test1.zero_id = test0.id
WHERE test0.id = 1 AND test1.id = 1 AND test1.rand = 5;

test1.rand=5 exists so the execution plan is

| id  | select_type        | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
| --- | ------------------ | ----- | ---------- | ----- | ------------- | ------- | ------- | ----- | ---- | -------- | ----------- |
| 1   | PRIMARY            | test0 |            | const | PRIMARY       | PRIMARY | 4       | const | 1    | 100      | Using index |
| 1   | PRIMARY            | test1 |            | const | PRIMARY       | PRIMARY | 4       | const | 1    | 100      |             |
| 2   | DEPENDENT SUBQUERY | test2 |            | ref   | IND           | IND     | 5       | const | 4    | 100      | Using index |

but when i change it to something that does not exist like test1.rand=10 it give this exectuion plan

| id  | select_type        | table | partitions | type | possible_keys | key | key_len | ref   | rows | filtered | Extra                                               |
| --- | ------------------ | ----- | ---------- | ---- | ------------- | --- | ------- | ----- | ---- | -------- | --------------------------------------------------- |
| 1   | PRIMARY            |       |            |      |               |     |         |       |      |          | Impossible WHERE noticed after reading const tables |
| 2   | DEPENDENT SUBQUERY | test2 |            | ref  | IND           | IND | 5       | const | 4    | 100      | Using index                                         |

and despite Extra: Impossible WHERE noticed after reading const tables and it returning no results it still used select_type: DEPENDANT SUBQUERY and got rows: 4

how to fix this problem and make it not return anything at all to save performance?


texts: Oracle auditing: Is it possible to log when a permission is being used?

texts:
Does Oracle offer a way to log instances where a given permission is evaluated?
Suppose I GRANT SELECT ANY DICTIONARY or something similar to a role, I am looking for a way to log every instance where the internal permissions engine of the database evaluates that exact permission for that role. Think of the audit.log on Linux systems that logs applications of SELinux policies.
I am also open to any workarounds achieving something similar.


texts: How does clustering on a field other than autoincrement id affect inserts?

texts:
I have a table with an id column which is auto-incremented, and various other informational columns. Rows are inserted into this table very frequently. When the data is read back, the majority of queries filter by a foreign key and a date range.

Currently, there is a clustered index on the id column, and a non-clustered index on the two columns of importance (TrackerId and DateRecorded). If I were to swap the indexes, our queries will run a lot faster. Would this negatively affect insert times?


texts: Fastest query for selecting arrays that contain duplicates

texts:
I'm using Postgres 9.5 and I have a column, named phones, of type text[].
I need to find all rows where this column contains duplicates.

I've found this extremely useful set of functions https://github.com/JDBurnZ/postgresql-anyarray and I could use this particular function https://github.com/JDBurnZ/postgresql-anyarray/blob/master/stable/anyarray_uniq.sql

select * from user_info
where anyarray_uniq(phones) <> phones

I was wondering though if there is a faster way of achieving what I want.
Maybe unnesting the array and using the window functionality would be better? Although I can find my way around SQL, I'm new to Postgres' specific best practices, so any help is welcome.

Is this better suited for CodeReview?


texts:
I am facing following error and unable to fix it. So far I have disabled binlog checksum on the RDS.

[Warning] Slave I/O: Notifying master by SET @master_binlog_checksum= @@global.binlog_checksum failed with error: Unknown system variable 'binlog_checksum', Error_code: 1193
2015-06-29 11:01:01 8026 [Warning] Slave I/O: Unknown system variable 'SERVER_UUID' on master. A probable cause is that the variable is not supported on the master (version: 5.5.41-0ubuntu0.12.04.1-log), even though it is on the slave (version: 5.6.22-log), Error_code: 1193


texts: column identifier versus separate table with related data with different purposes

texts:
Very new to database management here. I'm trying to figure out what rule of thumb I should be following when it comes to making separate tables for data rather than just labeling it as different within the same table

say I have a fleet of robots. I could have tables that differentiate foodmaking robots from cleaner robots, killer robots, etc. or I could have a table of robots with a field that specifies the type of robot it is. which is better and why?


texts: Is there a way to recover a dropped mysql database?

texts: I accidentally dropped a MySQL database on my server.  Are there any ways to recover a dropped database?


texts: ORA-39165 Schema was not found in export

texts:
I'm getting the following error on a data pump export:

ORA-39165: Schema OFDAT was not found.

First I verified that I had full export permission:

GRANT EXPORT FULL DATABASE TO OFDAT;

Then I tried the admin option in case that would do it:

GRANT EXPORT FULL DATABASE TO OFDAT WITH ADMIN OPTION;

I looked at the error more closely in oerr:

[oracle@OptTekOracleDB dpdump]$ oerr ora 39165
39165, 00000, "Schema %s was not found."
// *Cause:  If exporting or importing over the network, either the user
//          specified a schema name that was not found in the source database
//          or else the user lacked the proper EXP_FULL_DATABASE or
//          IMP_FULL_DATABASE role that would allow them to access another
//          schema.  For importing from files, the user specified a schema
//          name not found in the dump file set.
// *Action: Retry the operation using the correct schema name.

Aha! I thought.  Even though I have the grant, this is saying I need the role.  So I added the role to the user:

GRANT EXP_FULL_DATABASE TO OFDAT;

I am still getting the same error, schema was not found. Can't think of anything else to try.

@phil - here is my expdp parameter file:

DUMPFILE="dev_ofdat_2-1-3-0_afterbackfill.dmp"
LOGFILE="exp_dev_ofdat_2-1-3-0_afterbackfill.log"
DIRECTORY=DATA_PUMP_DIR
COMPRESSION=METADATA_ONLY
CONTENT=ALL
SCHEMAS=('OFDAT')

Very simple.  The command line to kick it off is:

expdp ofdat PARFILE=exp_dev_ofdat_2-1-3-0_afterbackfill.dat


texts:
We are experiencing deadlocks on SQL Server 2014 and I am trying to capture as much information about them as I can.

There is already Extended Events Session that captures xml_deadlock_report events and I can see deadlock graph in SSMS.

Currently there are no trace flags enabled on this SQL Server. Will enabling flags 1204 and 1222 help to capture any additional information? It seems these flags don't apply to Extended Events but I am not 100% sure.


texts: Is it possible to lower the CPU priority for a job?

texts:
I want to run a background job in SQL Agent which will cleanup a lot of data and will run for multiple days.
Basically, 400 million records will be deleted from one single table.
The delete runs in a loop, so always batches of around 100.000 records are deleted.
During the delete process, nothing else will be modified in this table.
Is it possible to lower the CPU priority for such a job, so that it does not slow down the system?


texts: Very slow WHERE IN query on very large table - postgresql 9.6

texts:
My table (with 140 000 000 rows) :

CREATE TABLE attendees(
  attendee_id                 VARCHAR NOT NULL,
  name                        VARCHAR NOT NULL,
  status                      VARCHAR NOT NULL,
  event_id                    VARCHAR NOT NULL,
  PRIMARY KEY (attendee_id, event_id)
);

CREATE INDEX attendees_event_id ON attendees(event_id);
CREATE INDEX attendees_attendee_id ON attendees(attendee_id);

My query :

SELECT event_id FROM attendees
where attendee_id IN (SELECT attendee_id FROM attendees where event_id=$eventId)

This query can take several minutes.
I saw here: Optimizing a Postgres query with a large IN I can change the IN by IN (VALUES (..)...) but I don't know how to change this sub query :

SELECT attendee_id FROM attendees where event_id=$eventId

to have (VALUES (..)...)

So, my question is, what is the best way to make this query faster ?

edit (I run the explain (analyze, verbose) on smaller table):

Hash Join  (cost=5296.72..14097.38 rows=209116 width=16) (actual time=177.516..362.851 rows=121564 loops=1)
  Output: attendees.event_id
  Hash Cond: ((attendees.attendee_id)::text = (attendees_1.attendee_id)::text)
  ->  Seq Scan on public.attendees  (cost=0.00..5051.21 rows=230721 width=33) (actual time=0.572..90.302 rows=230721 loops=1)
        Output: attendees.event_id, attendees.attendee_id
  ->  Hash  (cost=4664.54..4664.54 rows=50574 width=17) (actual time=176.611..176.611 rows=49650 loops=1)
        Output: attendees_1.attendee_id
        Buckets: 65536  Batches: 1  Memory Usage: 2917kB
        ->  Bitmap Heap Scan on public.attendees attendees_1  (cost=1288.37..4664.54 rows=50574 width=17) (actual time=14.141..151.031 rows=49650 loops=1)
              Output: attendees_1.attendee_id
              Recheck Cond: ((attendees_1.event_id)::text = '1193751294041282'::text)
              Heap Blocks: exact=595
              ->  Bitmap Index Scan on attendees_event_id  (cost=0.00..1275.73 rows=50574 width=0) (actual time=13.265..13.265 rows=49650 loops=1)
                    Index Cond: ((attendees_1.event_id)::text = '1193751294041282'::text)
Planning time: 1.611 ms
Execution time: 371.431 ms


texts: I recently started using SQL Server 2008 express and it has a really nice joins interface, whereby you can visually create a join.  Is this possible on PHPMyAdmin?


texts: SQL Visual Studio Find and Replace in Publish Profile

texts:
We have source control in Visual Studio Database Projects (SSDT). To make cross database references, we utilize something like this below in Project References.

We need Project references, since Otherdatabase, can choose between two options at our company. Most of the time, its 90% Database A, and 10% Database B.

This is a headache for developers, to write code with $ and brackets []. Is there any work around for this?
Eg, Say I write my code in DatabaseA default,

from [$(OtherDatabase)].dbo.PurchaseOrder
from DatabaseA.dbo.PurchaseOrder

Is there a method to have Multiple publish profile scripts, Can I write a publish profile script to find all references to Database A, and replace with Database B? Maybe like a simple Ctrl-Find and Replace? How do I incorporate this kind of command, batchfile, stored procedure or Powershell in publish profiles?

Searching for an efficient way; I may use this answer below, however DatabaseA needs to reside in same server, and then I would drop and recreate which may not be optimal.

Find and replace database name in views and stored procedures

Aware of synonyms trying to avoid this route, and conduct find/replace


texts: How many rows can SQL Server process in a single INSERT statement into a table?

texts:
To illustrate my question, the following is a query detecting order's id which have not been inserted in a data warehouse and inserts them:

With NewOrders
As
(
    Select OrderID From Orders
    Except
    Select OrderID From FactOrders
)
Insert Into FactOrders(OrderID, OrderDate, CustomerId)
    Select OrderID, OrderDate, CustomerId From Orders
    Where OrderID in (Select OrderID from NewOrders);

Say the query is run for the first time and Orders contains 400 million rows or more:

Can SQL Server handle this number of rows in one single INSERT statement?

If not, how should I proceed? Should I limit the number of rows fetched in the INSERT statement? How many rows can the engine handle in a single INSERT statement?


texts: Is Big data solutions a good option for 500 million temporal records?

texts:
So I have about 250k items x 2000 days (2010 to 2019) ~= 500 million records.

For each item I have variable number of fields. To begin with we have 50 fields defined for each item but in the future we want the ability to add more fields.

With that said, we thought we could pack all of the fields of each item in a JSON blob. Postgres allows us to query the JSON blobs. We did some prototyping and it took around 3 seconds for the QC queries on about 1 year of data with a smaller subset of items.

Since we are in Prototyping stage, I was thinking of building a Prototype using HBase. Since writing a spark job to bulk load data into it would take atleast me a couple of days I wanted to check online if HBase is a good option for this problem.

Also are there any other RDBMS solutions we should consider for this problem. MongoDB is a no-go because of some bureaucratic reasons.

PS: our QC queries are mostly, given a date get me all the items. Given an item get me all the data available for that item on all dates. Occasionally get me all the data for a particular field for a particular item.


texts: Replication slowness: massive MSrepl_commands table?

texts:
Background: I have a SQL Server 2005 transactional replication setup that pushes normal traffic fine, but occasional larger processing batches have been a problem.

Obviously the changes take longer to push, but even after that completes, the MRrepl_commands table gets full of old transactions and all subsequent activity involving replication is very slow (until the old records get eventually flushed away by the distribution cleanup job).

Right now there are 500 million rows in distribution.dbo.MSrepl_commands. So even the distribution job that tries to locate pending commands to send to the subscriber is very, very slow.

What should I do? Is this a problem with the history retention period or something?

I vaguely recall a thread where someone mentioned adding a non-clustered index to MSrepl_commands, but I'm not sure if that's a good idea or not. The missing index dmvs suggested an index on ([publisher_database_id], [article_id]) INCLUDE ([xact_seqno]), but I'm unsure if that's advisable, or if that would slow down other aspects of replication.

EDIT: Looks like the 500m rows are not recent like I thought, they go back 4 months, so for some reason my cleanup job is not working. Currently investigating the "subscription expiration" setting, it is set to "never expire", which I don't think is correct.


texts:
I have the following code in MariaDB that was working perfectly well yesterday. It calculates a rank and orders by the data by another column:
SET @c = (SELECT COUNT(*) FROM myTable);
SET @rownum = 0;
UPDATE myTable
SET rank = (100 / @c) * (@rownum:= 1 + @rownum)
ORDER BY anotherColumn DESC LIMIT 100000;

Today I executed it and the first time through it gave the wrong ranking values. I don't think the SET @rownum = 0 had worked. Now it seems to be working fine again.
Does anyone know why it would work perfectly yesterday and then stop working today and behave so strangely?
EDIT: I believe the @c is the variable that isn't updating when I add a new row. Does anyone know how to force it to recalculate @c based on the current data in the table?


texts: Trying to deploy sp_blitzIndex - but fails

texts:
I'm trying to deploy the sp_blitzIndex procedure from Brent Ozar. I'm running it in a query window, in SSMS and get the following error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'tempdb..SQLServerCheckup_2A98B846_4179_496B_AAF8_60B405E3ED68_waits'.
Starting run. sp_BlitzIndex(TM) v7.99999 - December 11, 2020
This is being done on the master DB. I'm running SQL Server 2019 developer edition on my local machine.
Any suggestions on a fix?


texts:
Everything was working fine until this moment I want to create a new database and I'm receiving this error

db_create.php: Missing parameter: new_db (FAQ 2.8)

Also when I was trying to export my database I also receive the following error

export.php: Missing parameter: what (FAQ 2.8)
export.php: Missing parameter: export_type (FAQ 2.8)

When I looked it FAQ 2.8 from the suggested link in PHPMYADMIN

  2.8 I get "Missing parameters" errors, what can I do?

  Here are a few points to check:

  In config.inc.php, try to leave the $cfg['PmaAbsoluteUri'] directive    empty. See also FAQ 4.7.
  Maybe you have a broken PHP installation or you need to upgrade your    Zend Optimizer. See http://bugs.php.net/bug.php?id=31134.
  If you are using Hardened PHP with the ini directive    varfilter.max_request_variables set to the default (200) or another
  low value, you could get this error if your table has a high number
  of columns. Adjust this setting accordingly. (Thanks to Klaus
  Dorninger for the hint).
  In the php.ini directive arg_separator.input, a value of ";" will    cause this error. Replace it with "&;".
  If you are using Hardened-PHP, you might want to increase request    limits.
  The directory specified in the php.ini directive session.save_path    does not exist or is read-only.

I did tried with php.ini to make sure that I've session.save_path = "/tmp"

I'm using Mac Xserver and running with MAMP I did tried everything to restart my server and nothing help.

Can someone please make a suggestion on the same?

My apologies if I have posted this in the wrong place!


texts: Should I create a new table for store information of an associated object with object need to create

texts:
I'm not sure if topic can get you understand what I want to explain.
Follow guide in my old question, now I have these tables and its columns:

Course: name,...
Topic: name, course_id
Question: content, topic_id
GeneralExam: name, description, semester, number_question (total number of questions of general exam), duration, used (boolean), user_id, course_id
GeneralQuestion: general_exam_id, question_id

To sum up association:

Course - Topic: one-to-many
Topic - Question: one-to-many
Course - GeneralExam: one-to-many
Question - GeneralExam: many-to-many, join table: GeneralQuestion

Now, When creating new General Exam (GE) for a course, I want to specific a number of questions for each topic of that course, so that I can get questions for that GE ( I want to do(specific) it on form, I had a form for create new GE now). So, should I create new table to store a number of questions of each topic of course when creating new GE? If I should, how table I should design?

I image table I should design will like this (I can't get name of table now):

NameOfTable: general_exam_id, course_id, topic_id, number_question

Is this table good?


texts: SQL Server 2008 R2 Restore constantly hanging

texts:
I have a SQL Server 2008 R2 SP2 test server that I have to refresh the databases on fairly often, but every time I have to do a restore on, it will hang on me. I use a custom script to check the progress of where the restore is at, and it hangs at various times. One time it may complete 56%, the next time 64%, the next time 17% and so on. It does not stop at consistent times. And when I say that it hangs, it just stops writing. As far as SQL Server is concerned, the restore is still running though. So its like it still doing its thing, but nothing occurs. If you leave the restore running without killing the process it will run forever. I have let it run for 12 hours before with no progress

Eventually, I can get the restore to complete after trying again over and over, and eventually it will go one time and finish. But it takes what should only be an hour, and turns it into 5 hours of constantly retrying.

So here is some information on the server -

It is a Virtual Machine,

Windows Server 2008 R2 Standard,
SQL Server 2008 R2 sp2 Standard,
32 GB RAM,
4 Proc
Here are the database sizes that are the problem. While not really, they are not overly large either.

Database A - 71 GB,
Database B - 11 GB,
Database C - 27 GB

Here is what I have done:

I have checked all the logs (SQL Server and Windows) to look for any errors or any messages that looked suspicious. There is nothing.

I have tried to run the restores at nearly every hour of the day, but it does not matter, it will hang at any point.

I have tried dropping the databases\deleting the .mdf & .ldf files and recreating the databases and then restoring, still get the issue

I have traced the restore to see if anything is occurring. Nothing.

I have made sure all connections where killed before starting the restore. Also, have placed the databases in single user mode before starting the restore.

I have no problems restoring the exact same backups on other servers, so no issue with the backups

Storage guys looked at the datastore\LUN and checked NAR files and said everything looked good.

They also migrated the vmdk to another LUN, but we still got the issue.

Storage guys sent the NAR files from the Array to EMC for examination and they say no issues with the storage

VMWare guys looked at the Virtual Machine and saw no issue with it.

I also disabled all the Anti-virus software and then tried it again, still will hang

I have ran the restores using the GUI, writing out the script, and using custom scripts, same result

Ran a repair on SQL Server

Nothing unusual when looking at the Wait Stats. Shows same thing as you would see when doing a successful restore.

Any thoughts on anywhere else to look or possible reasons here. Or any tracking or counters that you can recommend to pinpoint the issue.

My thoughts are an issue with storage or the server, but the Ops guys are adamant there is no issue with either of those.

Also, rebuilding this server at this time is NOT an option. Even though it is Test, it is part of a farm of servers that makes up a Claims environment. We will eventually rebuild this entire environment later this year, but it is a large undertaking.

The next step is getting on the phone with Microsoft, but waiting on the Ops guys to take one more look. So thought I would check here.

Thanks for your help!

Results of requested waitstat script -


texts: Why did the "Bypass Traverse Checking" permission fix my issue?

texts:
I was having an issue getting SQL Server Reporting Services 2008R2 SP3 working under a domain service account on Windows Server 2008R2. It started with this What am I missing for my SSRS Service account local server permissions? and I have progressed from there some.

I have a domain account setup as the service account for SSRS and the service is running. However on the /ReportServer and /Reports pages I am getting page cannot be displayed on my local server. Looking at the logs under "C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\LogFiles" I saw two errors

library!WindowsService_0!e7c!04/25/2017-08:21:37:: e ERROR: Throwing Microsoft.ReportingServices.Library.ReportServerDisabledException: , Microsoft.ReportingServices.Library.ReportServerDisabledException: The report server cannot decrypt the symmetric key that is used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. ---> System.Runtime.InteropServices.COMException (0x80070008): Not enough storage is available to process this command. (Exception from HRESULT: 0x80070008)
   at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)
   at RSManagedCrypto.RSCrypto.ImportSymmetricKey(Byte[] symKeyBlob)
   at Microsoft.ReportingServices.Library.ConnectionManager.b__2()
   --- End of inner exception stack trace ---;

Which made sense since I had changed the account that was being used. I simply deleted encrypted content to address that since it was a new SSRS instance anyway. The second error was the one that took me for a loop

rshost!rshost!f64!04/25/2017-08:21:38:: e ERROR: Failed to initialize listener for type 2.
servicecontroller!DefaultDomain!b7c!04/25/2017-08:21:38:: e ERROR: Error creating HTTP endpoint. System.UnauthorizedAccessException: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
   at Microsoft.ReportingServices.HostingInterfaces.IRsUnmanagedCallback.CreateHttpEndpoint(RsAppDomainType application, String[] urlPrefixes, Int32 cPrefixes, String[] hosts, Int32 cHosts, Boolean wildCardPresent, String virtualDirectory, String filePath, Int32 authType, Int32 logonMethod, String authDomain, String authRealm, Boolean authPersist, Int32 extendedProtectionLevel, Int32 extendedProtectionScenario, Boolean enabled)
   at Microsoft.ReportingServices.Library.ServiceAppDomainController.SetWebConfiguration(RunningApplication rsApplication, Boolean enabled, String folder)

That was difficult to figure out on my own. Research for the general error "Access Denied" in this context led to many different avenues but none that address my specific problem. Most stemmed from not using Reporting Configuration Manager to set up the service account. I did in my case and was able to verify the actions it took were done correctly. RsExec role, URLReservations using new account etc.

I ran into a thread that said...

  The service account wasn't a part of the "Bypass Traverse Checking" local security policy. Once the account was added to that policy, the issue was resolved.

Much to my surprise that actually fixed the issue. My problem is that the documentation for account permissions on the SSRS account do not mention this permission is required. They only state that "Log On As A Service" is required.

All my other SSRS environments, which I inherited, use a domain admin as the service account so I have nothing to compare to. I am trying to get away from that setup.

Is this just a flaw in MS documentation or did I cover up the problem (XY)? I do not understand how "Bypass Traverse Checking" fixed "ERROR: Failed to initialize listener"


texts:
I have an application that saves gps data every second and the user is able to query a variable timespan of data at once. So a select statement potentially needs to return many thousand rows. How is a small EC2 instance going to handle such requests? Would it help if I saved the coordinates of several hundred rows as an array in a single row like this:

instead of single rows:

timestamp, lon, lat
1,11,12
2,11,12
3,11,12
4,11,12
...

batches:

timestamp-start,timestamp-stop,lon,lat
1,300,[11,43,23,...],[11,43,23,...]
301,600,[11,43,23,...],[11,43,23,...]

or is the performance equal for many rows with little data and few rows with a lot of data.


texts:
I'm new to mysql. So, sorry if you find it an easy/silly question.
I have a quick question since I encountered a strange scenario. I want to make sure does it actually happens  or did I do the right thing.
I was taking backup of a table from a prod replica and what I found that  after some time it locked production master database table. We started getting 'Too many connections' error on live. I checked mysql documentation for this and found: That you should stop replication before starting the dump from replica to ensure that the dump contains the consistent set of data.
https://dev.mysql.com/doc/mysql-replication-excerpt/5.7/en/replication-solutions-backups-mysqldump.html
But, One thing I'm unable to get that yeah..from documentation I can conclude it's good practice to stop. But, nowhere it's being mentioned in the way that 'you have to stop the replication otherwise - it can make your master unreachable. Did anyone face the same issue?
Also, one more thing - I couldn't get- dump operation always make schema level lock -not to lock the whole table. So, how could this happen.
Thanks for any advice/suggestion/reason in advance!


texts: SQL Server 2012; Incorrect PLE buffer manager value for NUMA server

texts:
We are receiving PLE alerts from SCOM for several instances.

These instances are on hardware servers, and use NUMA nodes. SQL Server 2012 SP1 CU7 Enterprise Edition is installed. Memory is fixed and CPU affinity is configured.

Looking at the buffermanager PLE value, I see that there are indeed regular drops, but looking at the buffer node values, PLE values are OK.

select *
from sys.dm_os_performance_counters
where counter_name = 'Page life expectancy'

Following the calculation found on SQLSkills site, the result for the buffer manager PLE is completely wrong.

Besides that, I also noticed that the numa nodes listed in sys.dm_os_performance_counter don't seem to be correct.

For this specific instance we use numa node 2 and 3. Sys.dm_os_schedulers shows the correct information. But according to sys.dm_os_performance_counter numa nodes are 0 and 2.

License is OK, sys.dm_os_sys_info shows 48 CPUs.
The errorlog is showing the correct information regarding the licensing.

I would expect the buffer manager PLE value would be in relation to the buffer node values. Also, the numa nodes shown in sys.dm_os_performance_counters should reflect the situation in sys.dm_os_schedulers.

Am I interpreting these numbers incorrectly or is there something wrong in SQL Server?

@Andrew

These are the results for the query:

The reason we are behind on service packs is related to internal company politics. However, we plan to upgrade to SP3 in the near future.
I did some additional checks, and it seems all instances that have the PLE issue are on SP1 CU7. Other instances are on SP2 CU6. And for those who are on SP2 CU6 the buffer manager value matches with the buffer node values.


texts: Can I configure MySQL Replication as Master - Master - Slave?

texts:
I am considering how to configure our MySQL replication. Hope I can get any advices from community.

In fact, I am using MySQL 5.7, and have

01-Master
02-Slave
03-Slave

My Applications are using only 01-Master. They don't know 02-Slave, and 03-Slave.

Next, I want to promote 02-Slave to 02-Master. But want to keep 01-Master. So, I have:

01-Master
02-Master
03-Slave

Then I will set 01-Master for some Apps and 02-Master for other Apps.

But, I am not sure about how to position 03-Slave? Can I do that?
Please give me any advices.
Thanks a lot!


texts: Occasional slow insertion speed because of FK trigger

texts:
I have two tables in master-child relationship (several children per master).

In one use-case a master with all of its children needs to be copied as new master. For this I'm using two select+insert queries -- one for making copies of the master records and other for copying the child records.

Problem: The query for copying child records is sometimes fast (50 ms) and sometimes very slow (1500 ms), even with exact same database state (I'm clearing all tables before each test). I can't find any patterns. Sometimes it's slow several times in a row, sometimes it's fast several times in a row, sometimes it's fast, slow, fast, slow. Sometimes it's fast right after database restart, sometimes it's slow. I'm testing with single concurrent client.

I got the results of explain analyze for both queries -- the whole difference came from the foreign key connecting child table with the master table:

Trigger for constraint fk_attribute_value_element_id: time=1109.641 calls=1436

vs

Trigger for constraint fk_attribute_value_element_id: time=9.779 calls=1436

It looks like Postgres, while validating new child records, sometimes is able to look up (recently inserted) parent records easily and sometimes not.

Any idea how to debug this further? How to make the inserts always fast?

I have PostgreSQL 9.6.11 in Ubuntu 18.04.

I'm afraid I can't reproduce the problem with simpler and/or smaller datasets.

Here is the first query, which copies masters (this query is always fast):

insert into element
    (id, code, parent_code, hierarchy, version_id, version)
select
    nextval('element_seq'),
    e.code,
    e.parent_code,
    e.hierarchy,
    :new_version_id,
    e.version
from element e
where e.version_id = :old_version_id

Here is the second query, which copies children (sometimes too slow):

insert into attribute_value
    (id, language, value, attribute_description_id, element_id, version)
select
    nextval('attribute_value_seq') as id,
    old_av.language,
    old_av.value,
    old_av.attribute_description_id,
    new_e.id as element_id,
    old_av.version
from element old_e
join element new_e on new_e.version_id = new_version_id and new_e.code = old_e.code
join attribute_value old_av on old_av.element_id = old_e.id
where old_e.version_id = :old_version_id

Here are explain (analyze, buffers) results for the second query (left=slow, right=fast): https://www.diffchecker.com/lBA6sgaf

The slow FK is relating attribute_value.element_id with element.id.


texts:
I'm using  Microsoft SQL Server 2008.My question is:How to drop multiple tables with common prefix in one query?

something like that table names:

LG_001_01_STLINE,
LG_001_02_STFICHE


texts:
I'm working on a project, I'm not an advanced backend/database dev, but I always like to understand the 'why's, pros and cons' of things. So I have this table and some times I use Foreign Keys to relate one table to another.
One thing my client asked me recently was to implement a way to know wich user created each record on the table. Doesn't matter if it's a product, category, address, purchase, client, whatever. He wants to know who created those records.
To do that, I implemented one more field on these tables and I called it author_id, this id is reference the id on the main user table.
The problem is, If I go to the table tb_product and create one FK pointing to tb_user.id I can't do the same on the tb_category. Same applies to all the other tables that are referencing the user ID to be the author of that record.
I could go around this issue by changing the name of the following FK declarations, ex:

fk_product_author_id
fk_category_author_id
fk_address_author_id
and so on...

But my question is.. Is it ok to do that? Is it the correct way to declare all those fk? Or should I do something completely different?


texts:
Currently we are using pg_partman for partitioning our tables in PostgreSQL 9.4. We are planning on upgrading to PostgreSQL 10. I read that PostgreSQL 10 has better support for partitioning.

While I read the documentation on it, is there a standard process to automate creation of the partitions? Do we still need to use pg_partman to automate creation of the partitions or is there another way?

I am not a PostgreSQL DBA but I need to maintain the database so I am looking for any standard practices for PostgreSQL partitioning.


texts:
Each user/person could know one or more languages.

All I can think is a table like

+----------+------+-----+------------+-----+-----+-----+-------+
| PersonID | Java | PHP | Javascript | C++ |  C  | CSS | HTML  |
+----------+------+-----+------------+-----+-----+-----+-------+
|        1 | Yes  | Yes | No         | Yes | No  | Yes | No    |
|        2 | No   | Yes | Yes        | No  | Yes | No  | No    |
|        3 | Yes  | No  | Yes        | Yes | Yes | Yes | No    |
+----------+------+-----+------------+-----+-----+-----+-------+

Considering I'm going to need at least 100 columns for all the languages, is it normal to have that many columns? Something tells me this is the wrong approach.

Thank you very much and sorry about my english!