texts: Is it possible to build Data warehouse by myself?

texts:
I work for the insurance company little more than a year, my SQL experience around 2-3 years including SSIS, SSRS. We have approximately 1 TB of data.

Is it possible to build data warehouse by myself? Should I do that with my experience?

There are lots of materials and SQL groups I can get help from. But still, is it too complicated?

Thanks


texts: Export structure and data without a wizard

texts:
Using Sql Server 2008 R2 (but I could possibly upgrade if required).

I want to generate scripts for the database structure as well as the data for a schema.

The Generate Scripts wizard does what I want but I need to be able to automate it (so needs to be called from T-SQL or alternatively external program).

How can I do this?


texts: Get the total count of every child foreign keys

texts:
I have been trying this in SQL EXPRESS 2016. Here is my problem:

I have a tree like structure DB(Parent > child > grandchild >...) and in the lower level there is a relation with another table.

Table 1

CREATE TABLE [dbo].[Equipament]
    [ID] [nvarchar](50) NOT NULL,
    [AL] [nvarchar](50) NULL,
    [Lvl] [nvarchar](50) NULL,
    [IDParent] [nvarchar](50) NULL,

Table 2

CREATE TABLE [dbo].[Conns]
    [Conns] [nvarchar](50) NOT NULL,
    [IDEquip] [nvarchar](50) NOT NULL,
FOREIGN KEY([IDEquip])
REFERENCES [dbo].[Equipament] ([ID])

What I'm trying to get is a CTE that in a recursive way gets:

The total count of connections of each child (say its lvl 3 for
example)
For each lvl2 equipement the total sum of the of its lvl3' child
conections
For each lvl1 equipment the total sum of its childs

My attempts have failed mainly because there are no level 1 and 2 equipments in the Conns table, and cant have a LEFT JOIN in the recursive CTE.

The closest I got was to this:

;WITH QUERY AS
(
    SELECT E.ID,E.IDParent,L.conns
    FROM Equipament E
    LEFT JOIN conns L ON E.ID=L.IDEquip
    WHERE E.IDParent IS NULL

    UNION ALL

    SELECT E.ID,E.IDParent,L.conns
    FROM Equipament E
    JOIN conns L ON E.ID = L.IDEquip
    JOIN QUERY P on P.ID = E.IDParent
)
SELECT
    E.ID,
    SUM(S.Total) AS LTotal
FROM Equipament E
LEFT JOIN ( SELECT Q.ID, COUNT(Q.conns) AS Total
                FROM QUERY Q
                GROUP BY Q.ID
            ) as S
ON E.ID = S.ID
GROUP BY E.ID
ORDER BY E.ID
option (maxrecursion 0)

EDIT The fiddles requested:
dbfiddle link

With the example data the expected output should be something like:

+---+-------------------------------------+
|ID |   Total Conections Dependancy       |
+---+-------------------------------------+
|AA |   1 (CE + BA)                       |
|AB |   4 ( BB + BC)                      |
|BA |   0                                 |
|BB |   0                                 |
|BC |   4 ( CD + CB)                      |
|CA |   0                                 |
|CB |   3                                 |
|CD |   1                                 |
|CE |   1                                 |
+---+-------------------------------------+

Only the C level equipemnts have connections, and there might be a direct jump from level A to level C.

Is this possible to do? Can someone point me in the right direction?

Thank you,


texts: SQL SERVER database replication primary primary

texts:
I currently run a c# web app with one SQL server (2016) database. However due to latency with the world (our server are currently in China), we were wondering the path to follow in order to be able to have a server and a database outside mainland china but with data synchronization.
So far, it appears that we need database replication across multiples servers. I already saw that there is PRIMARY / SECONDARY database replication, but this seems not the solution since we need data synchronization between the two server (user in china will use chinese server / database and user outside china will use american server / database and since we have real time application, a data created in the chinese server / database need to be available in the american server / database and same in the other way around).
I look a bit more and saw that there is a different kind of replication such as peer to peer transactional replication (https://docs.microsoft.com/en-us/sql/relational-databases/replication/transactional/peer-to-peer-transactional-replication?view=sql-server-2017) but I also starting to realize that it looks complicated to setup this kind of things (for instance, no IDENTITY column).
I was wondering if I go in the right direction or not.
Thanks for your help.


texts: Best way to design a database with one person with different roles

texts:
We've been browsing for a while looking for an answer to this, but any of them suited exactly my needs, I hope is a relevant question.

We have a database for managing books. In this database, a same person can be author and editor, or author and translator.

We have chosen this schema:

However, wen we design the view for books, that have to have the title, the author, the translator, etc., we have to do it like this, in order to get the name of the person related to the role:

But we know that it's not the best way. I think is a relevant question, because this problem is a very common one in order to design a good and solid database, and this knowledge is not always specified in manuals.

Thank you!


texts: SQL Server Log Shipping: Where is the hottest IO during RESTORE LOG: To db or to log?

texts:
Using SQL Server 2008.

I have used SQL log shipping for years. Works great.

Working with a new standby server (recipient of log shipping), I am seeing very poor log restore performance, and wonder: Of the MDF and LDF, is one hit particularly harder than the other during log restore?

Background:
With dedicated hardware, I have no problem with this area. (To be fair, I have always been unhappy with the ldf size on the target... but what are ya going to do with a db in restore state?)

I am now experimenting with a cloud server and block storage. So far.... I am rather underwhelmed with the IOPS of block storage. The log restore operation is really slow (our main db server puts out a log file every 4 mins, and it takes 2+ mins to restore each one on the target!).

So I am debating whether:

get a faster block storage device (mucho $$)
--- But this only makes $$ sense for either the LDF or MDF... If I have to do both, it is just too expensive (ergo my question)
figure out another solve for SQL Server in the cloud
Forget the cloud for SQL Server, 'cause the iops just aren't there...

Further digging around uncovers: https://serverfault.com/questions/261631/temporary-io-boost-for-an-mssql-server

Which indicates that the hot IO is going to be to the LDF... and that would be the spot to optimize... (though "restore log" is not explicitly the agenda of this thread).

Are we agreed that boosing the IOPS of the LDF disk should boost RESTORE LOG performance?

Thank you!


texts: I want to join the below mentioned queries into single query

texts:
SELECT
      A.update_date,
      A.create_user,
      A.service_id,A.order_id,
      '10009' AS main_plan_id,
      'Prepaid Cash' AS main_plan_name,
      ' ' AS topping_plan_id,
      ' ' AS topping_plan_name,
      '0' AS Price
FROM
      ORDER_MASTER A
WHERE
      A.pending_order_type = 'Sim delivery'
AND
      A.order_status='Pending'
AND
      DATEDIFF(NOW(),update_date) > 30
GROUP BY
      A.update_date,
      A.create_user,
      A.service_id,
      A.order_id,
      main_plan_id,
      main_plan_name,
      topping_plan_id,
      topping_plan_name;

SELECT
      A.update_date,
      A.create_user,
      A.service_id,
      A.order_id,
      ' ' AS main_plan_id,
      ' ' AS main_plan_name,
      D.topping_plan_id,
      D.topping_plan_name,
      D.charge_amount AS Price
FROM
      ORDER_MASTER A
LEFT JOIN
         (
           SELECT
                 B.service_id AS service_id,
                 B.plan_id AS topping_plan_id,
                 B.plan_name AS topping_plan_name,
                 C.charge_amount charge_amount
           FROM
                 BILLING.BS_SERVICE_SUBSCRIPTION_MASTER B,
                 BILLING.BS_SUBSCRIPTION_CHARGE_DETAILS C
           WHERE
                 B.subscription_id = C.subscription_id
         ) D
ON
      A.service_id = D.service_id
WHERE
      A.pending_order_type='Sim delivery'
AND
      A.order_status='Pending'
AND
      DATEDIFF(NOW(),update_date) > 30
GROUP BY
      A.update_date,
      A.create_user,
      A.service_id,
      A.order_id,
      main_plan_id,
      main_plan_name,
      D.topping_plan_id,
      D.topping_plan_name;


texts: concurrent configuration PostgreSQL and tomcat

texts:
Postgresql has a default of 100 regarding max_connections.
Tomcat pooling has a default of 100 regarding maxActive and maxIdle.

How about configuring data sources in context.xml for Apps in Tomcat?

Given: 3 apps (war) containing 3 datasources in their context.xml
Do I have to set maxActive and maxIdle to 33 for the three datasources to meet PostgreSQL's default of max_connections = 100?

I guess yes, because in pg_stat_activity I've seen that "maxIdle" in Tomcat leads to establish "maxIdle" connections in PostgreSQL.

  Note: Using Tomcat 8.5.x and PostgreSQL 9.6.x


texts: Are duplicates in Read Commited standard behaviour?

texts:
This refers to this question: Duplicate records returned from table with no duplicates

The "read commited" isolation level assumes that one transaction could read same record differently, but it does not explicitly imply that records could be duplicated. I agree that two statements in the same transactions could return different data sets, but returning same data set with record duplication sounds insane.

As far as I know only MS SQL Server behaves this way.

Please prove or disprove if it conforms to "Read Committed" isolation level defined by ANSI SQL standard.


texts:
I am trying to use a MERGE statement to insert or delete rows from a table, but I only want to act on a subset of those rows. The documentation for MERGE has a pretty strongly worded warning:

  It is important to specify only the columns from the target table that are used for matching purposes. That is, specify columns from the target table that are compared to the corresponding column of the source table. Do not attempt to improve query performance by filtering out rows in the target table in the ON clause, such as by specifying AND NOT target_table.column_x = value. Doing so may return unexpected and incorrect results.

but this is exactly what it appears I have to do to make my MERGE work.

The data I have is a standard many-to-many join table of items to categories (e.g. which items are included in which categories) like so:

CategoryId   ItemId
==========   ======
1            1
1            2
1            3
2            1
2            3
3            5
3            6
4            5

What I need to do is to effectively replace all rows in a specific category with a new list of items. My initial attempt to do this looks like this:

MERGE INTO CategoryItem AS TARGET
USING (
  SELECT ItemId FROM SomeExternalDataSource WHERE CategoryId = 2
) AS SOURCE
ON SOURCE.ItemId = TARGET.ItemId AND TARGET.CategoryId = 2
WHEN NOT MATCHED BY TARGET THEN
    INSERT ( CategoryId, ItemId )
    VALUES ( 2, ItemId )
WHEN NOT MATCHED BY SOURCE AND TARGET.CategoryId = 2 THEN
    DELETE ;

This appears to be working in my tests, but I am doing exactly what MSDN explicitly warns me not to do. This makes me concerned that I will run into unexpected problems later on, but I cannot see any other way to make my MERGE only affect rows with the specific field value (CategoryId = 2) and ignore rows from other categories.

Is there a "more correct" way to achieve this same result? And what are the "unexpected or incorrect results" that MSDN is warning me about?


texts: Extracting data from SQL Server linked server

texts:
I am using MS SQL Server 2012, and I am trying to extract data from a Linked Server database.

In SQL Server management studio, I can see the linked server. I can browse the database on the server. I can see all tables in database.

When I try and query a linked table

select * from openquery(somedatabase,'select top 4 * from test')

I get error:

  The OLE DB provider "MSDASQL" for linked server "somedatabase" indicates that either the object has no columns or the current user does not have permissions on that object.

When I try to script the table.. right_click on the linked table, Script table as, Select to, New Query Editor window, I get same error.

Any help would be greatly appreciated.

I referred to following posts but it did not help.

http://www.sqlservercentral.com/Forums/Topic1036585-391-1.aspx

Error Querying MySQL server linked to SQL Server 2008

http://wiki.servicenow.com/index.php?title=Using_ODBC_Driver_in_SQL_Server#gsc.tab=0

Thanks.


texts: Can I install SQL Server Management Studio locally?

texts:
Can I install SQL Server Management Studio on my desktop to access a database on a SQL Server 2012 instance?

If so, where can I find the installer? Google searches only return 'express' versions whereas, I am looking for the full SQL Server Management Studio.


texts: postgres jsonb query performance is decreased by order by

texts:
I have a table with jsonb column. When I execute query below it evaluates quite fast. But then I need to add order by clause (created column is timestamp column). Execution time decreases by 1000 times.

select * from delivery where (document #> '{links}') @> '[{"type": "receipt","externalId": "9990863500764120180212"}]' limit 100;

Limit  (cost=0.00..4198.36 rows=100 width=137) (actual time=0.100..2.351 rows=100 loops=1)
  ->  Seq Scan on delivery  (cost=0.00..6843.32 rows=163 width=137) (actual time=0.099..2.338 rows=100 loops=1)
        Filter: ((document #> '{links}'::text[]) @> '[{"type": "receipt", "externalId": "9990863500764120180212"}]'::jsonb)
        Rows Removed by Filter: 1
Planning time: 0.184 ms
Execution time: 2.384 ms

select * from delivery where (document #> '{links}') @> '[{"type": "receipt","externalId": "9990863500764120180212"}]' order by created limit 100;

Limit  (cost=6423.11..6434.78 rows=100 width=137) (actual time=2243.874..2270.206 rows=100 loops=1)
  ->  Gather Merge  (cost=6423.11..6438.98 rows=136 width=137) (actual time=2243.872..2270.187 rows=100 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Sort  (cost=5423.09..5423.26 rows=68 width=137) (actual time=2237.522..2237.617 rows=251 loops=3)
              Sort Key: created
              Sort Method: external sort  Disk: 8552kB
              ->  Parallel Seq Scan on delivery  (cost=0.00..5421.02 rows=68 width=137) (actual time=0.160..2152.447 rows=48670 loops=3)
                    Filter: ((document #> '{links}'::text[]) @> '[{"type": "receipt", "externalId": "9990863500764120180212"}]'::jsonb)
                    Rows Removed by Filter: 5518
Planning time: 0.556 ms
Execution time: 2272.830 ms

I tried to create index on created field - nothing happened. How can I improve performance of this query?

UPD I've tried to use indexes (btree on created and gin on links) as @LaurenzAlbe adviced.

Limit  (cost=601.36..601.61 rows=100 width=139) (actual time=4833.531..4833.555 rows=100 loops=1)
  ->  Sort  (cost=601.36..601.76 rows=163 width=139) (actual time=4833.529..4833.544 rows=100 loops=1)
        Sort Key: created
        Sort Method: top-N heapsort  Memory: 74kB
        ->  Bitmap Heap Scan on delivery  (cost=41.26..595.37 rows=163 width=139) (actual time=29.329..4801.109 rows=146010 loops=1)
              Recheck Cond: ((document #> '{links}'::text[]) @> '[{"type": "receipt", "externalId": "9990863500764120180212"}]'::jsonb)
              Heap Blocks: exact=4128
              ->  Bitmap Index Scan on delivery_expr_idx  (cost=0.00..41.22 rows=163 width=0) (actual time=28.531..28.531 rows=146010 loops=1)
                    Index Cond: ((document #> '{links}'::text[]) @> '[{"type": "receipt", "externalId": "9990863500764120180212"}]'::jsonb)
Planning time: 1.144 ms
Execution time: 4833.618 ms


texts:
Because of having some problems, I decided to re-create all users except for root@localhost. This works fine, but the newly created user has no right to do anything. What I want is to simply give all rights to root at some local IP. I (as root@localhost) tried

CREATE USER 'root'@'10.0.3.210';
GRANT ALL ON *.* TO 'root'@'10.0.3.210';

The first command works, the second one fails with the message

ERROR 1045 (28000): Access denied for user 'root'@'localhost'

I don't get why root@localhost can't do everything, I'm sure I didn't mess with its privileges. From

SHOW GRANTS FOR 'root'@'localhost'

I get

Grants for root@localhost
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER ON *.* TO 'root'@'localhost' WITH GRANT OPTION
GRANT ALL PRIVILEGES ON `%`.* TO 'root'@'localhost' WITH GRANT OPTION

whatever this means. Am I missing a needed privilege? Can it be fixed?

I'm working with mysql Ver 14.14 Distrib 5.1.61, for debian-linux-gnu (x86_64).


texts: SQL Server using Parallel Plan for Cost less than 5

texts:
I have a pivot query that uses a parallel plan even though the cost is less than 5.

The Cost Threshold for Parallelism is set to default of 5, and MAXDOP is 0.

SELECT Column1, Column2 Column3, Column4 AS Column5, Column6 Column7, Column8 Column9, Column10 Column11
        FROM (
              SELECT Column12,
                        Column13,
                        Column1
                FROM Database1.Schema1.Object1
             ) Object2 PIVOT( SUM(Column13) FOR Column12 IN (Column2, Column4, Column6, Column8, Column10) ) AS pvtScore;

Following screenshot shows the estimated subtree cost as 1.39.

I am wondering why the parallel plan was used when the cost threshold is at 5 for parallelism to kick in.

Version: SQL Server 2008 R2


texts: Join Two queries with different declarations

texts:
I need to join two different queries to get some columns from both. The first one has a CTE.

Query1:

   WITH StatusChanges AS
(
    SELECT
       HistoryID,PartID,Status, DateAdded,
       rnAsset = ROW_NUMBER() OVER (PARTITION BY PartID ORDER BY DateAdded)
    FROM aAssetHistory
) SELECT
    A.isMarketed,A.AssetDescription,A.aPartID,A.SerialNumber,A.PartNumber,
    A.Location,A.Vintage,Ap.Fund,A.ProjectNumber,AP.ProjectName ,
    (
       SELECT Top 1 Disposition
       FROM aAssetDisposition AD
       WHERE (A.aPartid = AD.AssetID)) As Disposition, A.LocStatus ,
          CASE
             WHEN A.LocStatus='In Repair' THEN EstimatedCompletion
             WHEN A.LocStatus like '%lease%' THEN IIF(ProjectedReDeliveryDate='1/1/1900',LeaseExpiry,ProjectedReDeliveryDate)
             WHEN A.LocStatus='Stored' OR A.LocStatus='Marketed' THEN SC.DateAdded
          END 'Available',A.AssetComment,
          Cast(ISNULL((SELECT Top 1 1
                       FROM aAssetMarketingDocuments AMDocs
                       WHERE A.aPartid=AMDocs.aPartid),0) As bit
       ) Attachment
    FROM aAsset A
INNER JOIN aProject AP On A.ProjectNumber=AP.ProjectNumber
LEFT JOIN aAsset ParentA  On A.ParentACPartID=ParentA.aPartid
LEFT JOIN aEngineShop ES ON A.LocStatus='In Repair' AND ES.PartID=A.aPartid
LEFT JOIN vLeasedAsset LA ON A.LocStatus like '%lease%' AND LA.aPartID=A.aPartid
LEFT JOIN (
          SELECT SC1.PartID, SC1.DateAdded,RN= ROW_NUMBER() OVER (PARTITION BY SC1.PartID ORDER BY SC1.DateAdded DESC)
          FROM StatusChanges SC1
          LEFT JOIN StatusChanges SC2 ON SC1.PartID=SC2.PartID AND SC2.rnAsset=SC1.rnAsset-1
          WHERE (SC1.Status<>SC2.Status OR SC2.HistoryID IS NULL)) SC
ON (A.LocStatus='Stored' OR A.LocStatus='Marketed') AND A.aPartid=SC.PartID AND SC.rn = 1
WHERE ((A.isMarketed=1 AND (ParentA.isMarketed IS NULL OR ParentA.isMarketed=0)) )

Query2:

 SELECT
    A.[AssetMarketingID],B.PartID, aCompany.Company 'PotentialCustomer', A.isActive,
    ISNULL(CAST(Lease AS bit),0) 'Lease', ISNULL(CAST(Exchange AS bit),0) 'Exchange',
    ISNULL(CAST(Sale AS bit),0) 'Sale' ,DocumentStage,ContactTB.FirstName + ' ' + ContactTB.LastName 'Contact',
    A.ContactDate,A.Status,B.Amount, A.DateCreated, [Notes]
FROM aAssetMarketing A
LEFT JOIN aAssetMarketingAssets B ON A.AssetMarketingID=B.AssetMarketingID
LEFT JOIN aCompany On [PotentialCustomerID]=COid
LEFT JOIN aCompanyContact ContactTB ON [MarketingContactID]=ContactID
LEFT JOIN (
          select AssetMarketingID,max(case when DealStructure = 'Exchange' then 1 ELSE 0 end) Exchange,
          max(case when DealStructure = 'Lease' then 1 Else 0 end) Lease,
          max(case when DealStructure = 'Sale' then 1 Else 0 end) Sale
FROM [aAssetMarketingDealStructure]
GROUP BY AssetMarketingID) DS
    ON DS.AssetMarketingID=A.AssetMarketingID
ORDER BY A.ContactDate DESC


texts: How can I configure maximum number of concurrent connections WITHOUT SSMS?

texts:
I'm having an issue in a production environment and I was trying to simulate it in my local SQL Server Express instance.

I used SSMS to change the "maximum number of concurrent connections" from "0" (max = 32767) to "2".

After restarting the service, I'm able to connect to it with SSMS but when I try to open Server Properties (or "New Query" window) to change the setting back to "0", SSMS crashes.

Is there any other place I can change this setting without SSMS (e.g. registry)?


texts: SQL Server WHERE clause on CLR method (spatial) peformance

texts:
I have a query that joins to a table containing 2 million rows and need to filter on its geometry column by its geometry type (using geom.STGeometryType()). The query takes less than a second to run without this WHERE clause but over 40 seconds to run with the WHERE clause.

The execution plan seems to indicate that the filter on STGeometryType() is being run over the geometry column of the whole locations table and there's an accompanying warning that there are no statistics for the geometry column.

If I first select into a temporary table without the filter and then filter on the temporary table, I get the results in under a second. This is because it is only testing for geometry type over a result set of 25000 records. So one solution would be to do this (though it would require re-factoring the application).

I've also tried using a CTE and derived table to do all of the joins 'first' and then filter but that doesn't improve performance.

Is there a way I can structure the query so that this relatively expensive filter is applied last without the use of intermediate tables?

   select o.code as Code, ro.ReportTaxonCode as TaxonCode, t1.TaxonName as TaxonName,
   case
       when o.NumberObserved IS NOT NULL THEN o.NumberObserved
       when o.NumberObserved IS NULL then su.Mean
   end as Scale,
   ro.OriginalTaxonCode as RecordedCode, t2.TaxonName as RecordedName, s.code as SetCode, CAST(s.StartDate AS nvarchar), CAST(s.EndDate AS nvarchar), l.code as Location, l.geom
   from obsveg.obs_observations o
   inner join obsveg.obs_sets s
   on o.obs_setsguid = s.guid
   inner join obsveg.obs_locations l
   on o.obs_locationsguid = l.guid
   inner join obsveg.obs_rpt_reportobservations ro
   on o.guid = ro.obs_observationsguid
   inner join obsveg.obs_taxon t1
   on ro.ReportTaxonCode = t1.code
   inner join obsveg.obs_taxon t2
   on ro.OriginalTaxonCode = t2.code
   left join obsveg.OBS_MeasurementMethodScaleUnits mmsu
   on o.OBS_MeasurementMethodScaleUnitsGUID = mmsu.GUID
   left join obsveg.obs_ScaleUnits su
   on mmsu.OBS_ScaleUnitsGUID = su.GUID
   where ro.reportguid = '64c0a4af-ee4d-4e83-a194-2a14e8a6ab0e'
   and l.geom.STGeometryType() = 'Point'

EDIT Warning in execution plan added

EDIT Execution plan posted to pastebin
Execution plan


texts: Update a JSON column with WHERE Clause in JSON Column Postgres

texts:
I am having trouble writing a WHERE clause to update a JSON column in Postgres database:

Given a table of the form:

create table Test_Table (
    clusterId INT,
    owner VARCHAR(50),
    data JSONB
)

And three rows of data:

insert into Test_Table
values
(1, 'Jacky',{data}),
(2, 'Bob', NULL),
(3, 'Wolf',NULL);

...where the {data} in row 1 is a json document of the form...

{
    "certified": [
        {
            "Type": "New Instance",
            "Status": "Active",
            "created": "2020-02-03",
            "id_no": "f6b2197c4889"
        },
        {
            "Type": "New Instance",
            "Status": "Failed",
            "created": "2020-02-04",
            "id_no": "216430036272"
        },
        {
            "Type": "New Instance",
            "Status": "Active",
            "created": "2020-02-04",
            "id_no": "3bb515f781c6"
        },
        {
            "Type": "New Instance",
            "Status": "Active",
            "created": "2020-02-04",
            "id_no": "26ae740e9b21"
        },
        {
            "Type": "New Instance",
            "Status": "Active",
            "created": "2020-02-04",
            "id_no": "7e30a80211a2"
        }
    ],
    "in_common": [
        {
            "name": "Kodali",
            "owner": "Jet"
        },
        {
            "name": "Lakers",
            "owner": "Fantastic"
        },
    ]
}

I want to write an update statement to update the data column and change the "Status" to 'Active' where cluster_id is 1 and "id_no" is "216430036272". How do I write a WHERE clause that will update only this attribute?


texts: Business Continuity Plan -- How can I connect to DR Server if PROD site is completely Down

texts:
I have two servers in one DataCenterA (PRODUCTION SQL SERVERS AOAGs) and one server in another DatacenterB (DR BCP AOAGs).What if DataCenterA is completely Down.Now how can I let the Application stay Online.My SQL Servers has AlwaysOn Availability Groups.Can I connect to application by changing the connection string as "SQL\Instancename" in web.config file or using BCP AOAGs Listener which we created for AlwaysOn AGs BCP Listener ?
Please Guide me on this.


texts: how to copy data from SQL Server table to excel Spreadsheet thru Linked Servers

texts:
I try to copy data from a table in SQL Server 2005 into a excel spreadsheet. Since that sheet is linked to some external charts, so I have to overwrite the existing data with the updated data from SQL Server table. I want to do it through linked servers. But failed several times. Do someone knows how to do it?

Thanks in advance


texts: SQL Server PerfMon: Compilations/sec > Batch Requests/sec

texts:
I've been looking at various PerfMon metrics on one of our client's SQL Server instances trying to get a good gauge of where our database (or server) could use improvements.

One measure that has me puzzled is the ratio of two PerfMon metrics: Compilations/sec and Batch Requests/sec.

According to this post, "It's a general rule of thumb that Compilations/sec should be at 10% or less than total Batch Requests/sec".

Our application has a Windows Service that invokes scheduled calculations against the database every hour. Like clockwork, in my PerfMon CSV data, I can see the spikes in Compilations/sec and Batch Requests/sec. What I didn't expect to see was the number of Compilations/sec to exceed Batch Requests/sec.

I'm looking at 15 second samples from PerfMon centered around the time where our calculations kick off:

What does this typically indicate? Does this even make sense? Why would we be compiling more statements than executing? Am I missing something?


texts: Mysqldump error : Couldn't execute 'show table status

texts:
I have a database that I want to export. It has about 6 GB. PhpMyAdmin could not handle it, so after reading a little bit about it I am trying to do it from the console, unsuccessfully. When I run :

./mysqldump -u root -p mydatabase > /Users/myuser/Documents/mydatabase.sql

It gives me this error :

mysqldump: Couldn't execute 'show table status like 'USDT@002dXVG\_day'': Lost connection to MySQL server during query (2013)

Is someone encountered with the same problem ?


texts: SQL Server Profiler: why the RPC Starting event does use an different decimal separator from SP Starting?

texts:
Trying to diagnose an problem, I've ran into that inconsistency.
In SQL Server Profiler, the RPC Starting event uses the regional settings to write real numbers (example: in Portuguese(Brazil) 14.5 will be expressed as 14,5).
Normally that's not a problem, except where the RPC call is a stored procedure with real number parameters. So what is expected to be expressed as:

exec usp_something 'blabla', 14.5

Is expressed as:

exec usp_something 'blabla', 14,5

Which gives a lot of headaches when you have a long parameter list.
Didn't find any clue online how to change that behavior.

Someone knows a way to change that behavior?

Btw: I'm aware that the workaround is to use SP Starting event instead, which uses '.' for decimal separator.


texts: Easiest way to find the larger version number?

texts:
I have a table that stores version information in multi-dotted strings, ie '4.1.345.08', '5.0.1.100', etc. I am looking for the quickest way to find out which is larger. Is there a faster, or easier way on the eyes than what I have come up with below? One caveat is that I am 90% sure that we should ONLY find dotted numeric values here, but I can't guarantee that in the future.

DECLARE @str1 nvarchar(max)='15.0.4000.8'
DECLARE @str2 nvarchar(max)='15.6.4000.8'

SELECT
CASE
WHEN PARSENAME(@str1, 4) > PARSENAME(@str2, 4)
   THEN @str1
WHEN PARSENAME(@str1, 4) < PARSENAME(@str2, 4)
   THEN @str2
ELSE CASE
   WHEN PARSENAME(@str1, 3) > PARSENAME(@str2, 3)
      THEN @str1
   WHEN PARSENAME(@str1, 3) < PARSENAME(@str2, 3)
      THEN @str2
   ELSE CASE
      WHEN PARSENAME(@str1, 2) > PARSENAME(@str2, 2)
         THEN @str1
      WHEN PARSENAME(@str1, 2) < PARSENAME(@str2, 2)
         THEN @str2
      ELSE CASE
         WHEN PARSENAME(@str1, 1) > PARSENAME(@str2, 1)
            THEN @str1
         WHEN PARSENAME(@str1, 1) < PARSENAME(@str2, 1)
            THEN @str2
         ELSE NULL
         END
      END
   END
END


texts: Oracle - Exchange partitions between two List partitioned tables

texts:
Need some help here.
I have an ETL process which loads the data into a target table A. We have created another table B which is same as the target table in structure and this table is accessed by reporting team to generate reports. This is done to minimize the downtime for report generation. This way the reports are always accessing the latest data.
Target table B is a List partition table, partitioned on Client ID. ETL team loads data for each client in the respective partition, in Table A.
I tried doing it with Exchange partition: exchange partition mechanism to swap the segments of A and partitioned table B. But couldn’t do it as both tables are List partitioned and Oracle doesn’t like that. I created partitions so as to avoid creating multiple tables (TableA_ClientId) for each Client.
My other option: whenever data is loaded into target table A, rename the table B as table Temp, table B as table A and table A as table Temp.
Can you please suggest a better approach.


texts: SQL Server 2017 I can't make a procedure

texts:
Im quite new to this.
Im trying to make a procedure with recovery. But instead of making the procedure. It just recovers a database instead.

Use sde_sql
Go

Create Procedure RecoveryScript
as
    Alter Database SDE_SQL
    SET Single_User with
    Rollback IMMEDIATE

    use master
    go

    Restore Database SDE_SQL
    FROM DISK = 'C:\Users\HF\Desktop\SQL\SQLDatabaseBackup.bak'
    WITH REPLACE, STATS = 5


texts: As the title says, I cannot drop an empty database. In PHPMyAdmin, the request says that it times out and from running it in bash it just goes on until I cancel it with mysqladmin -u root -p kill <pid>. Running use panel; then show tables; just displays Empty set (0.001 sec) What can I do to fix this? Thank you in advance.


texts: t-sql to create tables using a declared variable

texts:
I am attempting to create multiple tables in multiple databases using declared variables for the db_name, table_name , column_name and column_type. Is this feasible ? I have to create the same tables for 14 databases. The tables are the same for the databases but there are multiple tables and multiple columns.

Sample:

DECLARE @DBName VARCHAR(250) = (SELECT [name] FROM sys.databases WHERE database_id >= 337); --Newly Created Databases
DECLARE @TableName VARCHAR(250) = (SELECT TableName FROM DB.Table.Tables WHERE Enabled = 1);
DECLARE @TableId INT = (SELECT Id FROM DB.Table.Tables WHERE Enabled = 1);
DECLARE @ColumnName VARCHAR(250) = (SELECT ColumnName FROM DB.Table.Columns WHERE Enabled = 1 AND TableId = @TableId);
DECLARE @ColumnType VARCHAR(250) = (SELECT ColumnType FROM DB.Table.Columns WHERE Enabled = 1 AND TableId = @TableId);

USE [master];

CREATE TABLE [@DBName + '.dbo.' + @TableName] (@ColumnName + @ColumnType);


texts: How to add time from a Date value to another without a varchar conversion?

texts:
Let's say I have 2 variables in a Stored Procedure, both are Dates

Foo contains '13/10/2019 00:00:00'

Bar contains '01/10/1914 16:33:11'

I want to add Bar's time to Foo's date, so that I get a Foobar variable containing 13/10/2019 16:33:11

Is there a way to achieve this without converting any of them into a varchar?


texts: Performing Backup in 2 different ways in parallel?

texts:
We wish to perform backup in SQL Server in 2 different ways in parallel. Now currently we have a 3rd party tool that is performing the Backups, Full and transaction log, now the business wants to perform also backups on disk via local SQL Server jobs.
The question is that would be possible, the LSNs chain will be affected as I know, and it won't be possible to perform valid restores, is that correct?
Even If we will perform Full backup and Transactional backup on backup server and also full backup and transaction backup to be keep on local disk via SQL jobs.
Would it be possible?


texts:
Lets say we've created a stored proc that has an optional input parameter.

Will SQL short circuit properly allowing the performance of the following two to be equivalent?
I ask because I am modeling my own proc after another, and it uses the latter method. It would be useful to know whether this was done for a reason or simply because the original author did not think of this.

select * from
<complex join>
where <various filters>
and (value IS NULL OR myvalue = @value)

OR

if (@value is not null)
begin
    select * from
    <complex join>
    where <various filters>
    AND myvalue = @value
end
else
begin
    select * from
    <complex join>
    where <various filters>
end


texts: Exporting database from SSMS to Azure is sitting on "enabling indexes" without finishing

texts:
Having created an Azure SQL server resource, I'm now exporting a database from Management Studio 2014 to the Azure resource. It all seemed to be going well, but after going through all the tables, is now sitting on "enabling indexes" and has been for about 2 hours.

Deploy Database Screenshot

The database is relatively small, only 1.5GB mdf, with a 180MB log. I have also tried exporting from SSMS to a local .bacpac file, and that also ends up sitting there for over an hour after going through the tables, but before showing the "enabling indexes" item.

It seems like SSMS is having trouble with the database for some reason, however this is a backup from production and has been in use without issues for a long time.

I'm not sure what to look for at this point. Are there some tools to check the database for errors which I can use here, to see why SSMS isn't able to finish exporting the db?


texts: How do I design a database for continents, countries, regions, cities and POIs?

texts:
I am not a database designer so I'm having trouble designing database with GIS information. The goal is to create a system with continents, countries, regions (including states, sub-regions, provinces), cities and places in cities. Each of these elements will contain some text information and related stuff. As database we are going to use PostgreSQL with PostGIS.

My question is, how do I design the database for this system? I was thinking of 2 tables polygons and points, but I'm not sure if it's good way of thinking.

Actually what we need is some hierarchical base for relationships between countries, cities, regions, etc. The main goal of the application will be collecting tourist data from many sources about specific cities, regions, countries and so on, and store it in database. Let's say we have a city Rome; we collect some info about this city into the database from couple sources. And we need to know that Rome is in the province Rome, sub-region Lazio in region Lazio, country Italy. So the system should be flexible to allow us to create such relations from the real world.

That's why I would choose two tables:

polygons - which can store countries, regions, sub-regions, provinces etc.
points - which can store cities and POIs


texts: Fill percentage of total field with MS Access SQL

texts:
I am trying to fill, with a SQL query, a field F with the percentage over the total of some field f, such that each record F(i) in field F is given by:

F(i) = f(i)/SUM_i(f(i))

Both fields f and F and located in a table MyTable.

I am however struggling to develop a query which manages to do this. My try was:

INSERT INTO MyTable (F)
SELECT f/MyField.fSum
FROM MyTable, (SELECT SUM(f) AS fSum FROM MyTable) AS MyField

But this code inserts new lines in table MyTable. I don't know how I can proceed from here, as my understanding is that the UPDATE statement is quite limited and wouldn't be useful for doing this.

Any ideas on how I could manage to do this in the simplest form possible?

I have just called the columns f and F here for illustration purposes, it is not how they are named in the actual DB.


texts: SQL Rebuild indexes data size percent a little smaller

texts:
I see a small change int the percent of data when rebuilding. I feel that this might be a usual issue but as I am new to SQL I get little worried when I see that the percent of data goes down after running the rebuild. I used the data usage report to get the percent of data.

I think what happen here is that some data got reorder during the index rebuild. Which in turn free up a small percent of space. I was not expecting this because my research did not indicate that this would happen. That is why it got me worried that there might be data loss.

I have confirmed from a good copy of the db that the number of records is either the same or garter in the copy where I rebuilt the indexes. Let me if I'm wrong.


texts: ORDER BY items must appear in the select list if SELECT DISTINCT is specified

texts:
SELECT DISTINCT *
    FROM (
        SELECT a.Title, a.ID FROM TableA a
        LEFT JOIN TableB b
        ON a.ID = b.XID) c
    ORDER BY
        CASE WHEN c.ID > 10 THEN c.Title
        ELSE c.ID
        END
    ASC

And if I move DISTINCT to nested select, then both ID and Title should be in same type, e.g: INT, but they are not.

Both ID and Title are in the selected columns, what is the problem?

Also with this change CASE WHEN 1 <> 1 query runs without problem.


texts: Postgres: Can't free space because I have no space

texts:
I have a Postgres DB that someone was brilliant enough to install in the log partition of my linux box.

It has a serious index problem, with indexes ballooning up to 50GB per. As soon as I start postgres, the log partition fills up to 100% immediately.

Now that I know the problem, I need to start Postgres to clean up the indexes. However, Postgres won't start due to no disk space, and I can't clean out the disk space without starting Postgres...

There's literally nothing else in this directory now except for the Postgres DB, so I can't free up any more space from something else.

Yikes! What do I do?


texts: high 'total_worker_time' for stored proc using OPENQUERY in SQL Server 2005

texts:
I have a stored procedure in SQL Server 2005 (SP2) which contains a single query like the following (simplified for clarity)

SELECT * FROM OPENQUERY(MYODBC, 'SELECT * FROM MyTable WHERE Option = ''Y'' ')
OPTION (MAXDOP 1)

When this proc is run I can see the plan appear in sys.dm_exec_query_stats with a high 'total_worker_time' value. However, when I run it in SQL Management Studio the statistics show a very low CPU time, as I'd expect. Although the query takes some time to return, because of the slowness of the server it is talking to, it doesn't return many rows.

I'm aware of the issue that parallel queries in SQL Server 2005 can present odd CPU times, which is why I've tried to turn off any parallism with the query hint (though I don't think that there was any in any case).

I don't know how to account for the discrepancy - can anyone give me a steer?

UPDATE: I was assuming that the problem was with the OPENQUERY so I tried looking at times for a long-running query which doesn't use OPENQUERY. In this case the statistics (gained by setting STATISTICS TIME ON) reported the CPU time at 3315ms, whereas the DMV gave it at 0.511ms. The total elapsed times reported by each method agreed.


texts: Postgres doesn't use expression index for group by

texts:
I'm doing a simple group by on a single column with a matching index and it works fine:

create index on contacts (home_city);
select home_city, count(*)
from contacts
group by home_city
limit 50;

Takes 150 ms on an 8 million row table. That's plenty fast enough. EXPLAIN ANALYZE output:

"Limit  (cost=1000.46..9256.24 rows=50 width=17) (actual time=14.201..76.630 rows=50 loops=1)"
"  ->  Finalize GroupAggregate  (cost=1000.46..207395.17 rows=1250 width=17) (actual time=14.200..62.196 rows=50 loops=1)"
"        Group Key: home_city"
"        ->  Gather Merge  (cost=1000.46..207370.17 rows=2500 width=17) (actual time=14.100..76.557 rows=97 loops=1)"
"              Workers Planned: 2"
"              Workers Launched: 2"
"              ->  Partial GroupAggregate  (cost=0.43..206081.59 rows=1250 width=17) (actual time=0.897..56.260 rows=41 loops=3)"
"                    Group Key: home_city"
"                    ->  Parallel Index Only Scan using contacts_6_home_city_idx on contacts_6  (cost=0.43..188620.82 rows=3489654 width=9) (actual time=0.026..28.866 rows=105725 loops=3)"
"                          Heap Fetches: 0"
"Planning Time: 0.099 ms"
"Execution Time: 76.680 ms"

Now I want to do it with a case-insensitive index:

create index on contacts (lower(home_city));
select lower(home_city), count(*)
from contacts
group by lower(home_city)
limit 50;

Takes 3-4 seconds. It appears to be using the wrong index:

"Limit  (cost=215869.40..215882.20 rows=50 width=40) (actual time=4253.233..4253.846 rows=50 loops=1)"
"  ->  Finalize GroupAggregate  (cost=215869.40..216175.66 rows=1197 width=40) (actual time=4253.232..4253.335 rows=50 loops=1)"
"        Group Key: (lower((home_city)::text))"
"        ->  Gather Merge  (cost=215869.40..216148.72 rows=2394 width=40) (actual time=4253.219..4253.796 rows=102 loops=1)"
"              Workers Planned: 2"
"              Workers Launched: 2"
"              ->  Sort  (cost=214869.38..214872.37 rows=1197 width=40) (actual time=4244.566..4244.630 rows=710 loops=3)"
"                    Sort Key: (lower((home_city)::text))"
"                    Sort Method: quicksort  Memory: 88kB"
"                    Worker 0:  Sort Method: quicksort  Memory: 89kB"
"                    Worker 1:  Sort Method: quicksort  Memory: 120kB"
"                    ->  Partial HashAggregate  (cost=214793.22..214808.18 rows=1197 width=40) (actual time=4241.397..4241.658 rows=1021 loops=3)"
"                          Group Key: lower((home_city)::text)"
"                          ->  Parallel Index Only Scan using contacts_6_home_city_idx on contacts_6  (cost=0.43..197344.95 rows=3489654 width=32) (actual time=0.059..2918.355 rows=2778720 loops=3)"
"                                Heap Fetches: 0"
"Planning Time: 0.118 ms"
"Execution Time: 4253.906 ms"

When I delete the index on plain home_city, it does a full table scan and takes 6 seconds.

How do I get it to use the right index? What am I missing here?

(I'm using Postgres 11.5).


texts: Updating Master table information based on Detail table status

texts:
I have one master and one detail table as following

Master:-
   BatchID
   BatchStatus
Detail
   DetailID
   BatchID
   DetailRecordStatus (Possible values 1,2,3)

I am trying to write a single a query that can update the Batch Status in Master based on detailRecordStatus values equal to 2 or 3


texts:
I have many records where 2 columns contain words which shall be uniquely concatenated. all words shall be distinct in the result.
The order of both text arrays shall be kept.

Col1: mr. spock eats 13 fish
Col2: he eats 13 fish

The idea is to concatenate only missing words.

result: mr. spock he eats 13 fish

I've tried to create arrays of both text columns with PostgreSQL's function
regexp_split_to_array
This is, what solved my problem:
CREATE OR REPLACE FUNCTION merge_strings(str1 text[], str2 text[])
  RETURNS text[]
  LANGUAGE plpgsql
AS
$body$
DECLARE
    res TEXT[];
    t1 TEXT;
    t2 TEXT;
    pos2 int := 1;
    pos3 int;
begin
    FOREACH t1 in array str1 LOOP
        pos3 := array_position(str2, t1, pos2);
        while (pos2 <= pos3) LOOP
        --iterate over words in second text-array until first found word from first text-array
            t2 := str2[pos2];
            if (t2 != t1) then
                --only use words that are not equal
                res := array_append(res, t2);
            end if;
            pos2 := pos2 + 1;
        END LOOP;
        res := array_append(res, t1);
    END LOOP;
    FOREACH t2 in array str2 LOOP
    --add all missing words from second text-array
        pos3 := array_position(res, t2);
        if (pos3 is null) then
            res := array_append(res, t2);
        end if;
    end loop;
    return res;
END;
$body$


texts:
I have 2 queries:

Query1:

SELECT DISTINCT maker FROM Product WHERE type='laptop'

Gives result {A, B, E, F, G}

and

Query2:

SELECT DISTINCT maker FROM Product WHERE type='pc'

Gives result {A, B, C, D, E}

The question is:
How can I get query1 - query2 = {A, B, E, F, G} - {A, B, C, D, E} = {F, G}

I tried:

SELECT DISTINCT maker FROM Product WHERE type='pc' AND NOT EXISTS (
    SELECT DISTINCT maker FROM Product WHERE type='b'
);

But it gave me empty record. Sorry I'm new with MySQL


texts: Looking for a tool to convert between MySql, Sql Server CE, Sql Server and SQLite

texts:
Our old product supports 4 different database types for storing data:

SQLite
MySql
Sql Server CE
Sql Server

There is also an export feature, which allows to export some data and here is the problem - it actually exports the database itself:

SQLite and Sql Server CE - the database file (*.db3 or *.sdf respectively)
Sql Server - the database backup file
MySql - the whole database directory

Stupid, right? We also came to think so. So, I had to write a tool, which given an old export file converts it to some database independent binary format.

The problem is how to unit test it? At the very least, I need 4 databases with identical data, which brings me to my question:

Is there a tool to reliably convert between the 4 databases?

Note, that I do not need a super duper tool, which knows to do all the pairwise conversions.

Until now I used http://sqlite2ce.codeplex.com/ to convert SQLite to Sql Server CE, but besides having had to fix several bugs in their code, there is a deeper issue with their conversion - all the integral types become 64 bits long after the conversion, which is not good for me.

Thanks.


texts: Mysql table with only two columns, nothing unique: Will an index increase search performance?

texts: If my table only has two columns (username, groupname) and a million or so rows, will an index improve performance at all? Wouldn't it take just as long to search the index file as it would to search the table itself? In this case nothing is unique, a username can occur multiple times with different groupname's, and vice versa. Searches will usually just be for username.


texts:
I want to create a simple login trigger.
basically if user = test , then send an email to me.

CREATE TRIGGER ServerLogon
ON ALL SERVER  WITH EXECUTE AS 'test'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'test'
    -- send email to me
    ROLLBACK
END

But I cannot execute as 'test'

it says the user does not exists or you do not have pemission
but even when I use my username or make test as a sysadmin / db owner it still says the same thing

thank you


texts: Error: The certificate cannot be dropped because one or more entities are either signed or encrypted using it

texts:
SQL Server 2008r2

I'm trying to drop a certificate I've created but i get the following error

  The certificate cannot be dropped because one or more entities are
  either signed or encrypted using it

Using this query I've identified the entities in question

SELECT SCHEMA_NAME(so.[schema_id]) AS [SchemaName],
       so.[name] AS [ObjectName],
       so.[type_desc] AS [ObjectType],
       ---
       scp.crypt_type_desc AS [SignatureType],
       ISNULL(sc.[name], sak.[name]) AS [CertOrAsymKeyName],
       ---
       scp.thumbprint
FROM sys.crypt_properties scp
INNER JOIN sys.objects so
        ON so.[object_id] = scp.[major_id]
LEFT JOIN sys.certificates sc
        ON sc.thumbprint = scp.thumbprint
LEFT JOIN sys.asymmetric_keys sak
        ON sak.thumbprint = scp.thumbprint
WHERE   so.[type] <> 'U'
AND ISNULL(sc.[name], sak.[name]) = 'Certificate_name'
ORDER BY [SchemaName], [ObjectType], [ObjectName], [CertOrAsymKeyName];

but when I try to drop the SIGNATUREs with the following statement:

DROP SIGNATURE FROM OBJECT::dbo.sp_sqlagent_notify BY CERTIFICATE Certificate_name

I get them following error.

  A signature by certificate 'Certificate_name' does not exist.

What am I doing wrong?

Thanks in advance.


texts: SQL Error (1093): Table 'j1' is specified twice, both as a target for 'UPDATE' and as a separate source for data

texts:
I get the above error while I execute the below query

UPDATE jobs AS j1 SET j1.job_last_status = 10, j1.updated_at = '2020-05-04 09:59:11'
WHERE j1.id IN (SELECT jh.job_id
FROM jobs_history AS jh
INNER JOIN jobs j ON j.id = jh.job_id AND j.job_last_status =7
WHERE jh.job_status = 7
GROUP BY jh.job_id
HAVING COUNT(jh.job_id) =2)


texts: Oracle pagination with order by field not unique performance

texts:
Usually, we will write pagination SQL like this if order by field is unique:

SELECT *
FROM (
    SELECT XX.*, ROWNUM AS RN
    FROM (
        SELECT *
        FROM T_LOG
        WHERE OP_TYPE = 'Q' ORDER BY L_DATE
    ) XX
    WHERE ROWNUM <= 500
) XXX
WHERE RN > 0;

but in my case the L_DATE field is not unique, even maybe null too. I don't want to put more fields after L_DATE (actually, this field is dynamic coming), so I put the order by after RN, like this:

SELECT *
FROM (
    SELECT XX.*, ROWNUM AS RN
    FROM (
        SELECT *
        FROM T_LOG
        WHERE OP_TYPE = 'Q'
    ) XX
    WHERE ROWNUM <= 500
) XXX
WHERE RN > 0 ORDER BY L_DATE;

In this way the page data is correct no matter whether the "order by" field is unique or not, but performance is 3 times slower than first one.

Any suggestions?

Right now, my solution is:

select *
from ( select xx.*, rownum as rn
       from (select * from  T_LOG ) xx ) xxx
where rn >0 and  rn <=500;

This way, data is always coming to the right way no matter order by fields is unique or not, even don't order by field.

The below way seems wrong:

select *
from ( select xx.*, rownum as rn
       from (select * from  T_LOG ) xx
       where rownum <= 500 ) xxx
where rn >0;

It's getting repeated data after some pages, if statements had order by unique field, it's work correctly.


texts:
I hope this makes sense.  I have a database which logs the day and time a row was inserted. I have a query which will select and count the number of insertions per hour.
I would like to pivot this so that it shows the date as rows and the hours as columns.

eg:

  DateTime      Hour1     Hour2    Hour3    |
+-----------------------------------------+
| 2016-01-01     11        10      35      |
| 2016-01-02     12        15      25      |

My current query shows as :

  DateTime      Hour       total    |
+-----------------------------------+
| 2016-01-01     1          11      |
| 2016-01-01     2          10

Query :

SELECT CAST([datetime_received] as date) AS ForDate,
       DATEPART(hour,[datetime_received]) AS OnHour,
       COUNT(*) AS Totals
FROM [claims]
where [datetime_received] between '2018-08-03 00:00:00.000' and '2018-08-03 23:59:00.000'
GROUP BY CAST([datetime_received] as date),
       DATEPART(hour,[datetime_received])

I do not have much experience with pivot queries so any help would be appreciated.

thanks


texts:
I have some queries that are parameterized, but they are still creating a new execution plan each time. I am using SQL Server 2016.

Queries are like:

(@P1 varchar(1043),@P2 varchar(6))
UPDATE table
SET FILEDATA=@P1
WHERE FILEID=@P2

This query is not using the already generated execution plan from the cache, rather it is creating a new plan each time.


texts: increasing disk space for mysql data volume

texts:
I have a master mysql server which has approached 80% of the available disk space. Total disk space size is 200GB. Though, it's not an immediate concern but I have to deal with at some point soon. the data directory is mounted on to logical volume.

--- Logical volume ---
LV Path                /dev/mysql/data
LV Name                data
VG Name                mysql
LV UUID                UNse6Y-QgCX-Ap6D-UXvr-VXs5-r8dc-g91Vhn
LV Write Access        read/write
LV Creation host, time localhost, 2012-09-19 15:22:49 +0100
LV Status              available
# open                 2
LV Size                200.00 GiB
Current LE             51200
Segments               1
Allocation             inherit
Read ahead sectors     auto
- currently set to     256
Block device           253:3

And this is mounted under /data directory.

/dev/mapper/mysql-data  /data                   reiserfs        defaults,noatime,nodiratime,notail,data=writeback 0 1

Unfortunately, the binary logs are located inside the the /data directory.

# grep -i log-bin /etc/my.cnf
log-bin                 = /data/binlog/mysql-bin.log

I am looking for the best option to increase the disk space and or moving the binary logs to
a separate disk location without having to restart mysql or with minimal downtime?

The database version is:

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  |
| version                 | 5.1.61-log          |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | redhat-linux-gnu    |
+-------------------------+---------------------+
5 rows in set (0.00 sec)

Is there any good solution? Please help.


texts: How to specify the system database location when installing SQL Server?

texts:
It seems that we can't specify a location for the system databases when installing SQL Server. I want to know whether this is true. Take a look at the image I attached.

Thanks!


texts: Sql Server Enterprise 2008 R2 Partition Switch with Xml Index

texts:
Ok, sql db partitioning and switching doesn't seem to be a highly discussed topic, but it is something I'm implementing.  I've had success in all areas of partition functions, partition schema, file group assignments, etc.  Up to this point I haven't had any issues with partition switching until a pesky xml field was found to be killing performance.  So I attempted to add a simple primary xml partition, but now all the partition switching is failing. Below is a mock of my table structure in play.

CREATE TABLE [MySchema].[DataTable](
    [PartitionKey] [varchar](12) NOT NULL,
    [Id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_DataTabe_Id] DEFAULT NewId(),
    [ClientId] [smallint] NOT NULL,
    [ProjectId] [int] NOT NULL,
    [ResponseValue] [nvarchar](255) NULL,
    [ResponseValueXml] xml NULL,
 CONSTRAINT [PK_DataTable] PRIMARY KEY CLUSTERED
(
    [PartitionKey] ASC
    , [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  =ON, ALLOW_PAGE_LOCKS  = ON)
ON PS_MySchema(PartitionKey)
) ON PS_MySchema(PartitionKey)

Some notes on this is that the partition key is a complex / computed key based on clientId, year, and another value.  The partition schema is built on a partition function that groups clients + dates, which are then put into seperate file groups (by year) - makes sliding window method easy to manage.

The general flow of things is to create a work table in the same file group, use alter table to switch the desired partition out of DataTable to the work table.  Do some stuff against the work table, and switch it back when done.  This works great... Until I add a primary xml index on ResponseValueXml.  The alter table / switch requires that only the source of the switch be allowed to have an xml index.  So, it appears I'm allowed to switch it to the work table (so long as the work table doesn't have an xml index). But I can't seem to switch it back.  If I used the DataTable as the source, it errors because the target cannot be empty.  If I use DataTable as the target, it errors about the xml index only allowed on a source table.

What am I missing here?  How am I supposed to manage this index when I'm not allowed to switch them?
Any guidance is appreciated.


texts:
I am facing a very strange issue as i have implemented a trigger on tableA which does encrypt data and insert data to another tableB and update column to 4 digit.
The issue is First insert is somehow skipped and it gets corrected from the 2nd insert onward, don't know why the first insert is skipped.
Any help would be great.

Os is Linux and MySQL version is 5.7.9

        create table tableA(c1 varchar(10));

    create table tableB(c1 varchar(60));

    delimiter //
        CREATE TRIGGER trig1 before INSERT
        ON  tableA FOR EACH ROW
        BEGIN
              IF LENGTH(LTRIM(RTRIM(new.c1))) > 4 then
                BEGIN
                insert into tableB(c1) select AES_ENCRYPT(new.c1,'test') from tableA;
                Set  new.C1 = RIGHT(LTRIM(RTRIM(new.C1)),4) ;
                 END;
           end if;
        END;
        //
delimiter ;

insert into tableA values('423423544');

select count(*) from tableA;--1
select count(*) from tableB;--0


texts: Equivalant MongoDB query of MySQL date query with arithmetic operations

texts:
How we can perform the below given MySQL query in MongoDB preferably in MongoDB-4.0:

SELECT *,ABS(TIME_TO_SEC(TIMEDIFF(now(), datefield))/3600) as hours,
(field1*field2*field2+field3*(1/(ABS(TIME_TO_SEC(TIMEDIFF(now(), datefield))/3600))))
as time FROM temp_table


texts: Postgresql Date Function Returning Range In Readable Format

texts:
I am wondering if it is possible to use postgresql's date-time , concat and case functions to give one of the following results:

June 4th to 11th 2019 (where the 1st and 2nd date are within the same month)
July 27th to July 3rd 2019 (where the 1st and 2nd date are in different calendar months but the same calendar year)
December 29th 2019 to January 4th 2020 (where the 1st and 2nd date are in different calendar months and different years)

I am setting out to create a VIEW that will be used for posting status updates (such as to Twitter) when the weekly tip is changed.  In my programming I endeavor to use the databases native functions to the most possible.  In an ideal world I wouldn't use source code to accomplish this provided postgresql is able to do so itself.

The SELECT query for VIEW that I've created so far is:

SELECT 'chronic pain'::text AS section,
    'https://rons-home.net/en/living-life-lab/tips/living-with-chronic-pain'::text AS link,
    tips_chronic_pain.reference,
    tips_chronic_pain.tip,
    tips_chronic_pain_weekly_selection.start_date,
    tips_chronic_pain_weekly_selection.end_date
FROM tips_chronic_pain
JOIN tips_chronic_pain_weekly_selection ON tips_chronic_pain.reference = tips_chronic_pain_weekly_selection.tips_chronic_pain_reference
ORDER BY tips_chronic_pain_weekly_selection.start_date DESC
LIMIT 1

I have created the following 2 tables:

Table #1 contains all the tips:

CREATE TABLE public.tips_chronic_pain
(
  reference bigint NOT NULL DEFAULT nextval('tips_chronic_pain_reference_seq'::regclass),
  tip text,
  add_date timestamp with time zone DEFAULT now(), -- UTC
  membership_reference bigint,
  CONSTRAINT tips_chronic_pain_pkey PRIMARY KEY (reference)
)

Table #2 determines which tip is currently being displayed:

CREATE TABLE public.tips_chronic_pain_weekly_selection
(
  reference bigint NOT NULL DEFAULT nextval('tips_chronic_pain_weekly_selection_reference_seq'::regclass),
  tips_chronic_pain_reference bigint,
  start_date timestamp with time zone DEFAULT now(), -- UTC
  end_date timestamp with time zone DEFAULT now(), -- UTC
  CONSTRAINT tips_chronic_pain_weekly_selection_pkey PRIMARY KEY (reference)
)

Column tips_chronic_pain_weekly_selection.start_date provides the first date
Column tips_chronic_pain_weekly_selection.end_date provides the second date


texts: Finding available schedules for multiple slots

texts:
I was asked to come up with a database schema to keep track of the slots and their schedules and query the table to find out the available time(empty schedule) for the slots.

I created three tables 1) slots which holds all the slots 2) schedule which has all the available schedules 3) slotschedule which has all the booked schedules for the corresponding slots.

This is the database diagram

The corresponding DDL design is the following one:

create table Slots
(
  Id int  PRIMARY KEY
);
create table Schedule
(
  Id int PRIMARY KEY ,
  StartTime Time,
  EndTime Time
);

create table SlotSchedule
(
  SlotId int,
  ScheduleId int,
  CONSTRAINT FK_SlotID_Id FOREIGN KEY (SlotId)
    REFERENCES Slots(Id),
  CONSTRAINT FK_ScheduleID_Id FOREIGN KEY (ScheduleId)
    REFERENCES Schedule(Id)
);

The requirement is to query the available schedules which can be booked for the slots.

The query I wrote is

select slots.SlotId,schedule.StartTime, schedule.EndTime
from Schedule schedule
LEFT JOIN
SlotSchedule slots
on slots.ScheduleId = schedule.Id and slots.SlotId = 1
where slots.SlotId is null
order by 1, 2

which results in

But when I want to find for a list of slotId say 1, 2 the result I get is this

From which I can not infer which available schedule belongs to which slot.

This schema and query work fine and I can find a list of available schedules for a particular slot. However, if I want to query a list of slots and gets it's corresponding available schedules I am not sure if that can be done by extending this design.

How do I get all the available schedule for a list of slots i.e. for slots = 1, 2, 3 rather than just 1 as in the query above.

The fiddle is present here https://www.db-fiddle.com/f/oS7mvCgoZipqKGz74wMcFE/0


texts: When creating views, why do users need direct object permissions if they already have the same permissions via a role?

texts:
I'm having a problem with permissions in Oracle 10g. I'm hoping someone can help me make sense of this.

I have a schema with a table in it. I have granted select on that table to a role.

grant select on user1.example_table to example_role;

I then grant that role to a user:

grant example_role to user2;

Then user2 wants to create a view on top of that table:

create or replace view user2.example_view as
select *
from user1.example_table;

That throws an error however:

ORA-01031: insufficient privileges

Why though? If they have select permission via the role, why can they not then create a view on that object?

I found that I had to grant the object directly to the user before it would work.

grant select on user1.example_table to user2;

Is there anyway not to have to do this? I wanted to use roles, because I have a lot of tables and a lot of users, and don't want to have to maintain a million different grants to individual users.


texts: SQL Replication components install in 2016 standard edition

texts:
i would like to install the components of Replication in sql server where the initial installation version is 2016 standard with SP1 (KB 3182545) - 13.1.4001.0 but now the SQL version is 2016 standard (SP1-CU7-GDR) (KB4057119) - 13.0.4466.4.

Question: Will this be good to go ahead and install components with initial setup file ?


texts: Count how many locks required for a MySQL (InnoDB) query

texts:
I'm performance tuning a very data-heavy mysql application. It has a purge system that often seems to cause lock timeout errors for users. Based on my research I have a few adjustments to the query to make to ensure we're locking as few records as possible... but

Is there any way to get a count of how many records mysql will lock for a given query? I want to be able to see evidence that my adjustments are requiring fewer locks.

In this specific case, I have some subqueries for foreign key data since I can't use LIMIT and JOINs. I'm wondering what impact, if any, the subquery has on the delete query locking.

I'm also considering the "primary key chunking" described here.


texts: deduct 60 minutes/ 1 hour from punch time (Start time) of attendance

texts: Suppose there are 300 employees different kinds of shifting duty they are doing but on 15th October Fingertec machine's clock was stop, so now I want to deduct 60 minutes or 1 hour from their in time. How can I update this in SQL query? Please help me.


texts: Mysql replication, error 13 (permission denied) with slave_load_tmpdir

texts:
I've setup a virtual machine on Azure with Ubuntu 16.10 and Mysql 5.7 for a database replication Master/Slave from another server.

I followed this article: https://www.opsdash.com/blog/mysql-replication-howto.html

All seems to works correctly, replication works but I've serious problems with the use of DATA LOAD INFILE. If I use this command, the replication doesn't works.

I tried with relative varible "slave_load_tmpdir", creating a new directory '/var/slavetmp' and updating my.cnf with these values, but this is the error in mysql with "SHOW SLAVE STATUS":

  Unable to use slave's temporary directory /var/slavetmp - Can't read dir of '/var/slavetmp/' (Errcode: 13 - Permission denied)

Have any ideas?

Thanks in advance


texts: Update table where table name in select query itself

texts:
I am having following select query

 (select   article.articleId ,'MUSIC' as type ,createddate
  FROM     MUSICarticle
  where    article.status=2
  and      article.createdBy=101
  order by createdDate ASC limit 1)
 UNION
 (select   article.articleId, 'SPORTS' as type ,createddate
  FROM     SPORTSarticle
  where    article.status=2
  and      article.createdBy=101
  order by createdDate ASC limit 1)
 UNION
 (select   article.articleId ,'DANCE' as type  ,createddate
  FROM     DANCE article
  where    article.status=2
  and      article.createdBy=101
  order by createdDate ASC limit 1)
 UNION
 (select   article.wikiTopicId as articleId ,'SINGING' as type ,createddate
  FROM     SINGING article
  where    article.status=2
  and      article.createdBy=101
  order by createdDate ASC limit 1)
 order by 3 ASC limit 1

and having output

articleid |  type   |        createddate
----------+---------|-------------------------
    1     |  MUSIC  | 2017-04-28 12:53:55.649

Now have to update MUSIC table where id =1 .


texts: How to remove specials characters, but replace spaces?

texts:
I have a string with spaces & special characters: ABC%$! DEF*& GHI
I want to remove all the specials characters, but replace the spaces with an underscore:  ABC_DEF_GHI
I have:
REGEXP_REPLACE(c.category_name, '[^\w]+','','g')

But that removes all the characters, including spaces.


texts: How can I limit a user to only be able to run procs in a schema, which access another db?

texts:
In a SQL Server 2012 SP2 Standard Edition instance I have a user in database A that needs to be able to run stored procedures in schema foo within that database. They shouldn't be able to do anything else. For example, they should not be able to SELECT directly from any view or table in either database.

The stored procedures SELECT from tables in databases A and B. I created the procedures WITH EXEC AS OWNER. Database A is owned by sa, which has the  sysadmin role and should therefore have access to both databases. I ran this:

USE [master]
GO
CREATE LOGIN [foo] WITH PASSWORD=N'foo', DEFAULT_DATABASE=[A]
GO
USE [A]
GO
CREATE USER [foo] FOR LOGIN [foo]
ALTER ROLE [db_executor] ADD MEMBER [foo];
GO
GRANT EXECUTE ON SCHEMA :: [Bar] TO [foo];

I then connected as [foo] and executed the procedure, but received the error:

  Msg 916, Level 14, State 1, Procedure GetReport, Line 10 The server
  principal "sa" is not able to access the database "A" under the
  current security context.

Setting TRUSTWORTHY ON seems to open things up much more than is acceptable. Is there another solution?


texts: How to make a relation database match a dynamic composition of data?

texts:
I have a database which has a table that contains resources. It does not matter what the resource are, they have a fixed number of properties so perfect for a table.

I would like to have consumers that must be stored in the database. My problem, as far as I am concerned, is that these consumers consume a certain amount of resources that is not constant. Furthermore these consumers can also be Used by other consumers.

I realize that a graph database, like e.g. TinyVirtuoso, can seem like something that is optimal, but for the moment I need it in a relational database format.

So far I was able to come up with the following ideas:

If I were to make assumptions about the number of resource a consumer can have.

Make a table with the maximum number of resources for each consumer,

If no assumptions for the number of resources, then I suppose just a normalized design will have to do.

So I would like to know if there is a smarter way to do this, or this is just about it.


texts:
I have a logon screen that has a function that looks like this (trimmed some parts for conciseness)
<script runat=server>
    void Login_Click(object sender, EventArgs e)
    {
        LdapAuthentication adAuth = new LdapAuthentication();
        try
        {
            string user = adAuth.authenticate(txtUsername.Text, txtPassword.Text);
            //cut out functionality for conciseness

        }
        catch(Exception ex)
        {
            errorLabel.Text = "Error authenticating. " + ex.Message;
        }
   }

But it always gives me the catch exeption for administrator.
Here are my sql statements in my config
 <connectionStrings>
    <add name="PIC_Program_1_0Context" connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=InventoryDB;Persist Security Info=True;User ID=Administrator;Password=adminpassword;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />
  </connectionStrings>

I made sure that Administrator is a valid user in my db and has all the rights

But it also gives me the error of
Error authenticating. Login failed for user 'Administrator'.
Why is this?


texts:
I have a Linux VM (with CentOS 64 bit) where I installed Oracle Database 11g Express Edition. The database is running, I can use sqlplus and I can create tables and stuff. However, when I run a certain SQL script which inserts a huge amount of random data (~2 million rows) I get this error:

ORA-30009: Not enough memory for CONNECT BY operation

I already tried to increase PGA_AGGREGATE_TARGET with the command below. As far as I read this should solve the problem (but it doesn't) since it increases the memory.

ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 40M scope = both;

However, the problem is that I can not set PGA_AGGREGATE_TARGET higher than ~40M (which seems to be not sufficient). If I try to set it up to 100M or more I get another error:

ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-47500: XE edition memory parameter invalid or not specified

Any idea how I can solve this problem? It is perfectly fine for me to re-install the whole database or whatever.

PS.: I asked the same question on https://stackoverflow.com/ since I wasn't sure whether it is programming or database administration.


texts: What's your experience to deal with the offboarding account?

texts:
I am thinking about doing the cleanup after the employee offboarding. Such as check across the multiple SQL instances and remove the offboarding account and the user on the database level.
Since it's across multiple SQL instances, I am considering using the dbatools PowerShell module. But not sure if it's a good way.
Anyone have a good experience to share with me?
Many thanks,


texts: How can I calculate database disk usage in Oracle?

texts: I'd like to get the real disk usage of a database, i.e. the amount of megabytes or gigabytes (could be any) that the tables use with all of their respective rows.


texts: Attendance and payroll management design

texts:
I am developing an Android app for the first time. For the database I am using SQLite . I am new to the concept of relational database. I am creating an app for attendance and payroll management. I am planing to add 3 tables:

employee
attendance
salary

In the first table, I entered the details of employees where the joining date and end date is defined. How to calculate the attendance based on the specified date? I will be using sysdate/timestamp for each day attendance as column and value will be boolean.

Also I will have a salary table. How to link it with the employee table to get emp_id (foreign key ) and name (how to link name)?

Please help me with the query and understanding concept of relations of the tables and also if any database design you all can recommended would help.


texts:
I have a existing schema in my database called SCOTT, how can I create a new schema with exactly the same privileges which SCOTT schema is having?

Is there any create like or some command to create schema like SCOTT?

Thanks


texts: Auto-calculated field which includes letters and numbers

texts:
I'm currently creating a workflow form to allow a new part creation, as a part of this I need to write an SQL statement which must be a stored procedure to auto populate the Part code field. I have done this previously for both customer and supplier using the following method:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Proc [dbo].[Udef_Wff_SUPCR_AutoSuppCode_SP]
    @FormID                 Int
    ,@UserID                VarChar(5)
As
Declare
    @SPC_IntSupCode         Int
    ,@INS_IntSupCode            Int

Select @INS_IntSupCode = MAX(SUCODE)
From PL_ACCOUNTS Where ISNUMERIC(SUCODE) = 1

Select @SPC_IntSupCode = MAX(SUCODE)
FROM SPECIALLive.dbo.PL_ACCOUNTS WHERE ISNUMERIC(SUCODE) = 1

If @INS_IntSupCode >= @SPC_IntSupCode
Begin
    Select Convert(VarChar(10), (@INS_IntSupCode+1))
End
If @INS_IntSupCode <= @SPC_IntSupCode
Begin
    Select Convert(VarChar(10), (@SPC_IntSupCode+1))
End

For both supplier and customer the data is identical in 2 databases and also the ID field is numeric. However my part ID's are in the format of

M10000001
M10000002
M10000003

and I will be updating the databases individually as the data contained will be different. As the database already contains entries I will need to locate the last entry and add 1 to the numeric part of the code. This is what I have come up with so far:

USE [INSULATIONLive]
GO
/****** Object:  StoredProcedure [dbo].[Udef_Wff_ParCR_AutoPartCode_SP]Script Date: 07/06/2016 09:06:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Proc [dbo].[Udef_Wff_ParCR_AutoPartCode_SP]
    @FormID                 Int
    ,@UserID                VarChar(5)
As
Declare
    @I          Int
    ,@PC        Varchar(16)

select @PC = Max(PMA_PART_CODE)
From [dbo].[ASC_PMA_TBL]
set @I = right(@PC,7)

Begin
select convert (varchar(16), @I +1,7)
end

No idea if it will work yet, any helpful comments much appreciated or alternative methods of achieving this


texts: How will large index INCLUDE fields affect system performance?

texts:
This question is about SQL Server index performance with a varchar(2000) as an INCLUDE in a covering index.

I’m trying to improve performance in an slow and unstable database application. In some cases, data is accessed through large varchar strings, with the queries including multple string operations like SUBSTRING(), SPACE(), and DATALENGTH(). Here is a simplified example of access;

update fattable set col3 =
   SUBSTRING(col3,1,10) + '*' +
   SUBSTRING(col3,12,DATALENGTH(col3)-12)
from fattable where substring(col3,10,1) = 'A' and col2 = 2

The schema looks like this:

CREATE TABLE [dbo].[FatTable](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [col1] [nchar](12) NOT NULL,
    [col2] [int] NOT NULL,
    [col3] [varchar](2000) NOT NULL, ...

The following index has been defined, with a covering field on the large text column.

CREATE NONCLUSTERED INDEX [IndexCol2Col3] ON [dbo].[FatTable]  ( [col2] ASC )
    INCLUDE( [col3] )

From what I’ve read it is BAD to put large data fields in an index. I’ve been reading several articles, including http://msdn.microsoft.com/en-us/library/ms190806.aspx which discuss the impact of paging and disk size on index performance. This being said, the query plan definitely uses the covering index.  I don’t have enough information to determine how much this is actually costing me in terms of system load. I do know that overall, the system is performing poorly, and I am concerned that this is one of the issues.
Questions:

Is putting this varchar(2000) column in the index INCLUDE ever a good idea?
Since the INCLUDE fields are stored in leaf nodes, do they have much
impact index performance?

Update: Thanks for the excellent replies! This is an unfair question in some ways - as you guys say, there is no absolute right answer without actual statistics and profiling. Like so many performance issue, I guess the answer is "it depends".


texts: Availability Groups automatic failover is not so automatic

texts:
Behavior when I stop SQL Server Service :

The availability group fails (resolving on SECONDARY SQL SERVER and fail state on Cluster management).
SQL Server does not connect with the Cluster ip (I'm using a fixed cluster ip inside the software for it to connect on both SQL1 and SQL2. using x.x.x.10 should connect me on both x.x.x.9 (SQL1) and x.x.x.8(SQL2) because of the windows failover cluster.

I need to manually move AG to secondary ( I connect on SQL2 and right click > failover).

To return everything as it was, I need to start SQL1 SERVICE manually, connect on SQL1 SSMS and right click > failover manually. Stop NODE2 service on cluster management so it turns SQL1 node primary again.

Behavior when I stop the primary cluster node:

The availability group goes to SQL2/NODE 2 (Secondary is now primary).
Primary AG is not Resolving...
SQL Server connect with the cluster ip ( I'm talking about SSMS . connecting with ip X.x.x.10,  connects me with x.x.x.8 that is the secondary node, after killing node x.x.x.9 ( Primary ).
When I start the cluster node (primary) again, it doenst automatically returns do SQL1 (primary). connecting with x.x.x.10 still connects me with the secondary node.
If I want to make SQL1 primary again, I need to **stop** node2 cluster service and start it again. this way, SQL1 (primary) is primary again connecting with Cluster IP.

This is totally not the automatically I think I would have. I thought that if SQL Server dies, all connections goes to the secondary automatically, but it just failover if the primary NODE is down (not sql server service). and then to return to the primary, I need to STOP SQL2 cluster service, so the primary becomes primary again.

Is there something that I'm missing?

We are not using a Failover Cluster with shared storage. There are 2 servers with their own disks.


texts: Convert CreateLSN of DBCC LOGINFO into time

texts:
If I run the undocumented  DBCC LOGINFO against my database one of the fields returned is CreateLSN.  This is the LSN in the format 1629000000070500011 at the time the VLF was created.  Now it is a long time later and the auto grow values of Disk Usage report are long gone.  I want to convert the LSN to a date/time

For a one off solution I tried this

select sys.fn_cdc_map_lsn_to_time ( convert(binary,1629000000070500011) )

But I get the error

  Invalid object name 'cdc.lsn_time_mapping'

I feel like I am missing something simple, but it is alluding me.

sys.fn_cdc_map_lsn_to_time (Transact-SQL)


texts: Changed server name, now maintenance plan fails

texts:
I am using SQL Server 2008 R2.  When I try to execute a maintenance plan I get the following error:

  Message [298] SQLServer Error: 15404, Could not obtain information
  about Windows NT group/user 'WIN-6QFI9JAK804\sqladmin', error code
  0x534. [SQLSTATE 42000] (ConnIsLoginSysAdmin)

I ran the following queries:

sp_dropserver 'WIN-OldName'
GO
sp_addserver 'VM-MSSQL-2008R2', local
GO

I then restarted the MSSQLSERVER service and the agent service.

When I run select @@servername I do get VM-MSSQL-2008R2 returned however the original error persists when I try to execute the maintenance plan again.

Is there another step that I haven't performed yet?

UPDATED:

I ran:

select * from msdb.dbo.sysssispackages

and noticed that the only plan is called Backups.  So I instead ran the query:

UPDATE msdb.dbo.sysssispackages
  SET ownersid = SUSER_SID('VM-MSSQL-2008R2\sqladmin')
  WHERE name = 'Backups'

I ran the following two selects to make sure that the are the same:

select SUSER_SID('VM-MSSQL-2008R2\sqladmin')
select ownersid from msdb.dbo.sysssispackages WHERE name = 'Backups'

And they both return the same value.

When I run the plan, I get the same error message as before.

(I have created a new Backup Plan, but I'd still like to figure out why this one is working just for knowledge's sake)


texts: Impoving performance of query by using greater than on a primary key

texts:
I have a Reporting table where i store description

tableA

sno   | Project |name     | description      | mins |
1     | prjA    |nameA    |ABC -10% task done|  30  |
...
3000  | prjA    |nameB    |ABC -70% task done|  70  |

i want to query the description field and save in another table

tableB

id | valueStr | total_mins  | last_sno
1  |  ABC     | 100         | 3000

if there is no entry in second table , i create a entry with default values

if there is and entry in second table , i update 2nd table , with the total_mins and increment the last_sno to that value say 3300 , so that the next time i query this table i get values from second table and based on the last_sno

Query

SELCT last_sno FROM tableB where valueStr ='ABC'

the first 3 characters in the description field

SELECT max(sno), sum(mins) FROM tableA
 where sno > last_sno and description like 'ABC%'

Since the first table has million of rows so, i search the first table with sno > last_sno , so that should help performance right ?

but the explain shows that it scans the same no of rows , when i query the first table from the first sno


texts:
What is the performance difference between local text index and domain text index in oracle? Also what is the cost of rebuilding the domain text index when I have to split just one partition? Does the whole text index being rebuilt?

Thanks,

Sean Nguyen


texts: Stored Procedures under Source Control, best practice

texts:
I am currently using Tortoise SVN to source control a .NET Web Application. What would be the best way to bring our SQL Server stored procedures into Source Control? I am currently using VS 2010 as my development environment and connecting to an off-premise SQL Server 2008 R2 database using SQL Server Data Tools (SSDT).

What I have been doing in the past is saving the procs to a .sql file and keeping this files under source control. I'm sure there must be a more efficient way than this? Is there an extension I can install on VS2010, SSDT or even SQL Server on the production machine?


texts:
I am trying to pick the MySQL charset combination which is correct.
Unfortunately, at least on my screen, the list of options is very narrow and doesn't show the full text of the options.
I want to pick option utf8mb4_unicode_ci, but I literally can't see to the end of the option to know whether I'm picking the right option.
I guess I could maybe work it out by looking at a list of all possible options and then sorting them alphabetically, but this seems a very complicated way of doing it.


texts: how to get child categorys based on categoryid

texts:
this is my table structure
i need to find child categoryids. If CategoryID 1 then 1,2,3,4,10,12,13,14,15,16 when CategoryID 2 then 2,10,12,13,14,15,16


texts:
In a Mondrian Schema, I have a fact table for Sales, a dimension table Time and a dimension table Client.

I need to create a Saiku report where i can show how many clients joined per year. I have a the Time dimension and a join_date in Client (which DO NOT link to the Time Dimension key, is just a date field),.

If I create a cube adding Join_date as a Level of the Client Table, I wouldn't know how to filter that latter in the Saiku.

Does anyone know how is the best way to add JOIN_DATE on client? I just need a report: Clients that have joined to the company by year.

And how should <Property> work in cubes? When i tested using this <Property>, it was not visible in Saiku...


texts:
In short: my goal is to reset mysql root password on ubuntu.

Background: I set up a ubuntu desktop and installed LAMP stack last week. I then went to do something else, and just got back to carry on today. Either I did not set mysql password, or I have forgotten what I typed in.

So I tried to reset it.

I stopped mysql by

sudo stop mysql

I tried to start mysql using command line options, but it seems start only takes service name(??)

So I ran:

sudo /etc/init.d/mysql start --skip-network  --skip-grant-tables

Went into mysql, fine. Then

mysql> UPDATE mysql.user SET Password=PASSWORD('newpasswd') WHERE User='root';
ERROR 1142 (42000): UPDATE command denied to user ''@'localhost' for table 'user'
mysql> show grants for ''@'localhost';
+--------------------------------------+
| Grants for @localhost                |
+--------------------------------------+
| GRANT USAGE ON *.* TO ''@'localhost' |
+--------------------------------------+
1 row in set (0.00 sec)

If ''@'localhost' has USAGE on all-schemal.all-tables, I'd think I can just update the password like this... Where did I miss?

I used the instruction in mysql official website, the last part. I also tried the unix one but with no luck. And I was unsure when it says 'unix', does it mean all *nix system or just unix???

Anyway, any thoughts are welcome! many thx!!!


texts: How to link a MySQL Table with a binary key into Access?

texts:
In MS Access I've created a linked table via ODBC to a MySQL table with a binary(16) primary key column (UUID). MS Access only shows the first row, all other rows have #deleted entries. I guess the reason for this is the binary field. I've checked the ODBC driver properties without success. Do you have a solution to this problem?

Details: I'm using Access 2010 64 bit and "MySQL ODBC 5.2a Driver". Opening the linked table looks like this:

The MySQL table has following Definition (reduced):

+---------------+---------------+------+-----+------------------+-------+
| Field         | Type          | Null | Key | Default          | Extra |
+---------------+---------------+------+-----+------------------+-------+
| LNEr          | binary(16)    | NO   | PRI |                  |       |
| Datum         | datetime      | YES  |     | NULL             |       |
| StatNr        | int(11)       | YES  |     | NULL             |       |

MS Access generates following Definition:


texts:
This the tags column where all terms should be listed only once. Would like to remove all additional words from this column. For example the below contant:

  Lakshmi Bar & Restaurant, Dining & Leisure, Restaurants, Restaurants- Multi Cuisine, Hotel, , East#West, Malad East, Dafftary Road, Mumbai, Maharashtra, Features, , Lakshmi Bar & Restaurant, Dining & Leisure, Restaurants, Restaurants- Multi Cuisine, Hotel, , East#West, Malad East, Dafftary Road, Mumbai, Maharashtra, Cuisines, , Lakshmi Bar & Restaurant, Dining & Leisure, Restaurants, Restaurants- Multi Cuisine, Hotel, , East#West, Malad East, Dafftary Road, Mumbai, Maharashtra, Services, , Lakshmi Bar & Restaurant, Dining & Leisure, Restaurants, Restaurants- Multi Cuisine, Hotel, , East#West, Malad East, Dafftary Road, Mumbai, Maharashtra, Meal For Two, , Lakshmi Bar & Restaurant, Dining & Leisure, Restaurants, Restaurants- Multi Cuisine, Hotel, , East#West, Malad East, Dafftary Road, Mumbai, Maharashtra, Last Order/Booking (P.M), , Lakshmi Bar & Restaurant, Dining & Leisure, Restaurants, Restaurants- Multi Cuisine, Hotel, , East#West, Malad East, Dafftary Road, Mumbai, Maharashtra, BookUrTableId,

Should show as below:

  Lakshmi Bar & Restaurant, Dining & Leisure, Restaurants, Restaurants- Multi Cuisine, Hotel, East, West, Malad East, Dafftary Road, Mumbai, Maharashtra, Features, Cuisines, Services, Meal For Two, Last Order/Booking (P.M), BookUrTableId

Where i tried to run the query and insert this into a table it gave an error as the content in this column was going too long and stops the execution of the query.

Also it would be preferred if i can get this done in mysql rather than using php or any other programming language.

Please help.


texts: OPTION FORCE ORDER improves performance until rows are deleted

texts:
I have a somewhat complex SQL Server 2008 query (about 200 lines of fairly dense SQL) that wasn't performing as I needed it. Over time, performance dropped from about .5 seconds to about 2 seconds.

Taking a look at the execution plan, it was pretty obvious that by reordering the joins, performance could be improved. I did, and it did... down to about .3 seconds. Now the query has the "OPTION FORCE ORDER" hint, and life is good.

Along comes me today, cleaning up the database. I archive about 20% of the rows, taking no action in the relevant database except deleting rows... the execution plan gets TOTALLY hosed. It completely misjudges how many rows certain subtrees will return, and (for example) replaces a:

<Hash>

with

<NestedLoops Optimized='false' WithUnorderedPrefetch='true'>

Now the query time spikes from about .3s to about 18s. (!) Just because I deleted rows. If I remove the query hint I'm back to about 2s query time. Better, but worse.

I've reproduced the issue after restoring the database to multiple locations and servers. Simply deleting about 20% of rows from each table always causes this issue.

Is this normal for a forced join order to make the query estimates to be
completely inaccurate (and thus query times unpredictable)?
Should I just expect that I'll have to
either accept sub-optimal query performance, or watch it like a hawk
and frequently manually edit query hints? Or maybe hint every join as well?
.3s to 2s is a big hit to take.
Is it obvious why the
optimizer blew up after deleting rows? For example, "yes, it took a sample
scan, and because I archived most of the rows earlier in the data
history the sample yielded sparse results, so it underestimated the need for
a sorted hash operation"?

If you'd like to see execution plans, please suggest a location I can post them. Otherwise, I've sampled the most stunning bit. Here's the fundamental mis-estimate, numbers in parens are (estimated:actual) rows.

                             /  Clustered Index Scan (908:7229)
Nested Loops (Inner Join) --<
                             \  NonClustered Index Seek (1:7229)

Note the inner loop is expected to scan 908 rows, but instead scans 52,258,441. If it had been accurate, this branch would have ran about 2ms, rather than 12sec. Before deleting the rows, this inner join estimate was only off by a total factor of 2, and was performed as a hash match on two clustered indexes.


texts:
I encounter the following errors after reinstalling MySQL Server. These seem to affect only one out of the several tables in the schema. The following errors are generated from MySQL Workbench.

When running CHECK TABLE myschema.mytable, error is returned:

  Table 'myschema.mytable' doesn't exist

Trying to do ALTER TABLE, the following error box pops up:

  Error getting DDL for object, Table 'myschema.mytable' doesn't exist

When I inspect the table, the Columns info (data type etc) and FKs info is there but the Indexes are gone. Inspection shows that data path directory is pointing to the correct .ibd file.

For the other tables, both the data and the metadata can be accessed. Some of these tables contain FKs which reference the affected table and they can still be read.

I can't be sure of what exactly is causing the errors. I reinstalled MySQL Server and copy-and-pasted the data directory (the .frm and .ibd files) after the installation. I read elsewhere that this might have caused the problem by corrupting the .ibd.

Is there any way to recover the table (or the whole schema altogether), or recover the DDL which the error suggests to be missing?

Win 7, MySQL Server 5.6.28


texts: pg_stat_activity entries with no corresponding process on the client

texts: If I see an entry in pg_stat_activity for a client_addr:client_port combination where there is no process bound to that port on the indicated client, what does that mean?  Just that the client process exited before the query completed?


texts:
I'd like to try new NoSQL feature in MySQL 5.6 but I am using MariaDB 10.0.2 on Debian 6 and don't fancy coming back to MySQL.

I'm wondering whether the memcached plugin has been added to MariaDB? And if not whether one can still use it as an addon?

And if not, can I use the existing Cassandra plugin of MariaDB to the same effect?


texts:
I want to store large files in a DB2 database and I need more information about which is the best technique to use as I am new in this IBM product (previous experience with T-SQL only).

The situation:

I am using DB2 C-express 10.1 (I do not know how to use the compression feature but sure that this distribution has not got it - this is not problem I can change the distribution if it may help me.
OS - windows 7
Data files types - ms word/pdf/ppt ( I think they will be store as
binary objects)

So, could some one advice for best technique which I can use (talking about both security and performance)?

I suppose I am looking for something like MS File-Stream Enable Database with BLOB objects.


texts: How do triggers affect last_insert_id()?

texts:
I am unable to test this right now, however I'm curious about the answer.

If I have a trigger on table A that writes a new record to table B, will LAST_INSERT_ID() tell me the updated ID for A or B?  I'm curious about both MySQL and PHP, but my understanding is that PHP simply calls MySQL's implementation.

How do I get the last inserted ID for B if it normally returns A, and vice-versa?


texts: Get column difference between same table in different schemas

texts:
Suppose I have the following tables:

schema_a.products

+----+------+------+-------+
| id | code | name | price |
+----+------+------+-------+

schema_b.products

+----+------+------+
| id | code | name |
+----+------+------+

I'm trying to get the column differences between the two tables. So I'd like the query to return the price column (as it exists in schema_a.products but not schema_b.products)

I thought I could select the column names from INFORMATION_SCHEMA for schema_a.products and then omit any column names selected from schema_b.products, like so:

SELECT column_name
FROM information_schema.columns
WHERE table_name = "products"
AND table_schema = "schema_a"
AND column_name NOT IN (
  SELECT GROUP_CONCAT("\"", column_name, "\"")
  FROM information_schema.columns
  WHERE table_name = "products"
  AND table_schema = "schema_b"
);

However this just returns all of the column names. If I run the subquery separately, and then replace it with the result, it works fine.

SELECT column_name
FROM information_schema.columns
WHERE table_name = "products"
AND table_schema = "schema_a"
AND column_name NOT IN ("id", "code", "name");

What am I missing?


texts: Why does OBJECT_ID return NULL in a computed column?

texts:
I'm trying to produce a usage report for some stored procedures in my database. The view view sys.dm_exec_procedure_stats contains some of what I need, so I decided to start here.

SQL Server keys procedure metadata by object ID. I know only the procedure names, so first I have to map the names to IDs using the OBJECT_ID function.

There are several procedures to check, so I want to put their names in a temp table before processing them.

I thought it would be cute to get the object id from a computed column in the temp table, so I created a table like this:

USE msdb;
GO

CREATE TABLE #procs (
  name SYSNAME,
  [object_id] AS OBJECT_ID(name)
);

INSERT INTO #procs (name) VALUES ('dbo.sp_send_dbmail');
INSERT INTO #procs (name) VALUES ('dbo.sp_add_job');
INSERT INTO #procs (name) VALUES ('dbo.sp_start_job');

With that table in place, the obvious way to get the execution counts looked like this:

SELECT #procs.name, execution_count
FROM sys.dm_exec_procedure_stats AS procstats
INNER JOIN #procs ON #procs.[object_id] = procstats.[object_id]
ORDER BY execution_count DESC;

But it returns an empty result set!

This query shows why the result set is empty:

SELECT name, [object_id]
FROM #procs;

The object_id column is NULL, so an inner join will produce no rows:

name                 object_id
-------------------- -----------
dbo.sp_send_dbmail   NULL
dbo.sp_add_job       NULL
dbo.sp_start_job     NULL

It's not that computed columns are broken for temp tables. If I add a computed column to reverse the name, it produces the correct value when I select from it:

ALTER TABLE #procs ADD reverse_name AS REVERSE(name);

SELECT name, [object_id], reverse_name
FROM #procs;

Result:

name                 object_id   reverse_name
-------------------- ----------- ---------------------
dbo.sp_send_dbmail   NULL        liambd_dnes_ps.obd
dbo.sp_add_job       NULL        boj_dda_ps.obd
dbo.sp_start_job     NULL        boj_trats_ps.obd

Why does OBJECT_ID return NULL here?