Why does MSSQL choose scan in execution plan? Here is my query (It is a Microsoft Axapta Query):

(@P1 bigint)
SELECT TOP 1 T1.JOURNALNUM,T1.LINENUM,T1.ACCOUNTTYPE,T1.COMPANY,T1.TXT,
T1.AMOUNTCURDEBIT,T1.CURRENCYCODE,T1.EXCHRATE,T1.TAXGROUP,
T1.CASHDISCPERCENT,T1.QTY,T1.BANKNEGINSTRECIPIENTNAME,
-- *Snipped lots of columns in T1* --
T1.MODIFIEDDATETIME,T1.RECVERSION,T1.PARTITION,T1.RECID
FROM LEDGERJOURNALTRANS T1
WHERE (((PARTITION=123123123) AND (DATAAREAID=N'test')) AND (REVRECID=@P1))

Current execution plan :

Current plan

Actually, there is a appropriate index on table.

Index columns : (PARTITION,DATAAREAID,REVRECID)

Fragmentation :

I tried index force. This execution plan (index seek+key lookup) is faster than after plan (index scan):

Force Index Plan

And I tried to :

UPDATE STATISTICS
Changed it the column order, for example
(REVRECID,PARTITION,DATAAREAID)

Why does MSSQL choose clustered index?

Estimates, a huge amount of columns selected and predicate pushdown

The query's estimates are not accounting for the residual predicate on the scan being of a higher cost than the seek + key lookup to get all these extra columns from the clustered index. This results in the clustered index scan + residual predicate being chosen instead of the index seek.

  My version is Microsoft SQL Server 2016 (RTM-GDR)

These estimates on predicate pushdown where improved in SQL server 2016 SP1

Update to improve diagnostics for query execution plans that involve residual predicate pushdown in SQL Server 2016

  To improve diagnostics for the scenario that's described in the
  "Symptoms" section, SQL Server 2016 Service Pack 1 (SP1) introduces a
  new showplan XML attribute, Estimated Rows Read. This attribute
  provides the estimated count of rows will be read by the operator
  before the residual predicate is applied. This update is a complement
  to KB 3107397.

This adds the  EstimatedRowsRead="" to the query plan XML, in your case this would be close or matching the residual predicate if the scan is chosen.

This should fix your issue

Residual predicate example

Reading 1.2M rows to return 0

Index scan query Estimated total cost

EstimatedTotalSubtreeCost="0.00449281">

Index seek query Estimated total cost

EstimatedTotalSubtreeCost="0.00672858">

Which is higher than the index scan estimations due to not accounting for the residual predicate, and that is why the lesser performing plan was chosen.

The main solution

The main solution would be upgrading to at least SP1 to add the:

Update to improve diagnostics for query execution plans that involve residual predicate pushdown in SQL Server 2016

You should patch sooner and more often, since SP2 CU6 is out as of March 19, 2019, this would be a far better choice.

Another side note, SP1 for SQL Server 2016 adds many additional features such as In memory OLTP, Compression, Columnstore indexes, ....

Other workarounds that may or may not be worth mentioning

Selecting less columns if they are not needed
Adding all these columns to the NC index's included columns
You could try disabling row goals with OPTION(QUERYTRACEON 4138) (maybe)
Using the WITH(INDEX)) Hint

Comparison with SQL Server 2016 SP1

when running a query alike yours, forcing the clustered index to be used on a SQL2016 SP1 version:

The estimated subtreecost is much higher.

EstimatedTotalSubtreeCost="93.6951"

Where your estimated subtreecost for the clustered index scan

<RelOp AvgRowSize="4788" EstimateCPU="1.36996" EstimateIO="185.267" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="1" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.00448209" TableCardinality="1245280">

Is low

EstimatedTotalSubtreeCost="0.00448209"

With the main difference being

EstimatedRowsRead="1000000"

shown when executing the query on the SQL 2016 with SP1 applied.

And when testing with the NC index specified

CREATE INDEX IX_PARTITION_DATAAREAID_REVRECID
ON dbo.LEDGERJOURNALTRANS(PARTITION,DATAAREAID,REVRECID);

<RelOp AvgRowSize="980" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1000000">

the EstimatedTotalSubtreeCost for the index seek (not total for entire plan) is also low:

 EstimatedTotalSubtreeCost="0.0032831

and the total estimated subtree cost for my test query is very close to yours

EstimatedTotalSubtreeCost="0.00657048">


migrating from one SQL cluster to another cluster I have 2 existing SQL clusters. Both 2008 R2. Each have unique instance names.

Server1\inst1

ServerA\reports.

We are decommissioning Server1 cluster and need to migrate DBs over to ServerA\reports

So I don't have to change application and SSRS report connection strings is it possible to add a SQL alias of Server1\inst1 to ServerA\reports.

If possible this would allow you to connect using either Server1\inst1 or ServerA\reports.

Thanks,

-Jess-

Rename New Server

Is ServerA\reports running existing software that will break if the name changes? If not, after decommissioning Server1\inst1 you could rename the entire failover cluster instance and network name to reflect the decommissioned instance.

Alias

You can create an alias in SQL Server Configuration Manager on the server that is running SSRS with the alias name Server1\inst1 pointing to ServerA\reports. SSRS should be able to use this to connect with reports.

Shared Data Source

Alternatively, if you alter your reports to use a single shared data source per database you can have a single data source for each in SSRS that you can update. I do this to alter between a primary and backup set of reporting data as a form of local redundancy.

Edit: Reference for creating an alias: https://msdn.microsoft.com/en-us/library/ms190445.aspx

Note that you'll need to create the alias on the 'client' server rather than the server hosting SQL, so in your case on the server running SSRS where the connection is initiated from.


Square root in SQLite studio SQLite studio does not support square root function, so I have defined it in function editor as

proc SqrtA {num} {  set ans [expr {sqrt($num)}] return $ans }

with function nameSqrtA and one input argument, lang. tcl but when I am using it in query editor, I am not getting the square root.

For example I had tried for:

select SqrtA(9);

Can anyone please help me, in how to include square root function in SQLite studio?

You should not define a Tcl procedure inside body of SQL function. Instead use only following line as function implementation (and nothing more):

expr {sqrt([lindex $argv 0])}

If you define a proc inside function definition you will be creating a new Tcl procedure each time you call the SQL function, which makes no sense, especially when you don't call that procedure in the end.

Anyway, stick to the simple implementation as mentioned above. That should do the work.


Cannot read property Collation.This property is not available on SQL Server 7.0 I'm not able to add administrator role in SQL Server 2008 R2.

When I try to add new user it throws me the following error:

  Cannot read property Collation.This property is not available on SQL Server 7.0. (Microsoft.SqlServer.Smo).

I ran into this today. I tried to connect to a SQL Server 2014 instance while using SQL Server Management Studio 2008 R2. This is exactly the same error I got:

  Cannot read property Collation.This property is not available on SQL Server 7.0. (Microsoft.SqlServer.Smo).

Installing SSMS 2014 did the trick; I can connect and add users fine.


Joining results of two mySQL queries I have two mySQL queries. Individually, they give me what I'm looking for. However, I prefer to combine the results into a single set.

First query. Simple selection from table 'profile'.

SELECT id, first, last, TIMESTAMPDIFF(YEAR, dob, NOW()) AS age
FROM profile WHERE active = 'Y'

Results:

|id               |first    | last | age|
|160808213759VYO  |Peter    |Pan   | 31 |
|160808214828NGO  |Lion     |King  | 30 |
|160808215315TRA  |Buck     |Roger | 29 |

Second query. For a given id there are multiple entries (description). I
want to pick out the first entry for a given id only and it's working correctly.

SELECT r.id, (SELECT r1.description FROM reference r1
WHERE r1.id = r.id AND r1.active = 'Y' ORDER BY r1.description ASC LIMIT 1)
AS image
FROM reference r WHERE r.type = 'I' AND r.active = 'Y'
GROUP BY r.id
ORDER BY r.id, image;

Result:

|id             |image                         |
|160808213759VYO|   img/160808213759VYO_003.jpg|
|160808214828NGO|   img/160808214828NGO_002.jpg|
|160808215315TRA|   img/160808215315TRA_001.jpg|

How can I join these two queries to get this:

|id             |first   |  last |  age|   image                       |
|160808213759VYO|   Peter|  Pan  |  31 |   img/160808213759VYO_003.jpg |
|160808214828NGO|   Lion |  King |  30 |   img/160808214828NGO_002.jpg |
|160808215315TRA|   Buck |  Roger|  29 |   img/160808215315TRA_001.jpg |

Any help will be much appreciated.

I figure something like:

SELECT p.id, p.first, p.last, TIMESTAMPDIFF(YEAR, p.dob, NOW()) AS age
     , MIN(r.description) as image
FROM profile p
JOIN reference r
    ON r.id = p.id
WHERE r.type = 'I' AND r.active = 'Y'
  AND p.active = 'Y'
GROUP BY p.id, p.first, p.last, p.dob

;

should work


Removing/overwriting sensitive data located in already deleted records in ibdata files As far as I know, using InnoDB tables with innodb_file_per_table = off does not prevent deleted data from being visible in the ibdata1 file.

I know this is by-design, and that I could switch the system setup to use innodb_file_per_table = on, which would allow the system to shred the ibdata* files containing the sensitive information, however there is "no server downtime allowed"

Could I simply overwrite the sensitive information in the ibdata1 file somehow, so that some erroneous this is sensitive reads this is xxxxxxxxx afterwards.

I'm not a DBA and neither know whether this would work out at all, or will destroy some checksums, crash the server, or whatever.

So my question is, what can I do to prevent leaking in the meantime without affecting the stability of the running server?

Switching to innodb_file_per_table is not enough because ibdata1 would still be the same size.

Any tables that were inside ibdatat1 and you ran OPTIMIZE TABLE against, it would simply manifest the table and its indexes to a .ibd file. You would still have the footprint of the table inside ibdata1. You are much better off restructuring InnoDB so that no data and index pages reside in ibdata1

Think about it: With innodb_file_per_table off, what goes inside ibdata1 besides data and indexes?

Data dictionary
Double Write Buffer (Background write to prevent reliance on OS caching)
Insert Buffer (Managing changes to non-unique secondary indexes)
Rollback Segments
Undo Space
Pictorial Representation

Some of these may eventually overwrite old data and index pages, but how long is eventually? Would it not be better to completely compress ibdata1? Basically, it would entail:

doing a mysqldump of all databases
deleting ibdata1
making sure innodb_data_file_path is the default (ibdata1:10M:autoextend) in /etc/my.cnf
starting up mysqld to let it recreate ibdata1
reloading the mysqldump.

Please see my past links on how to do so...

Oct 29, 2010 : My Original Post in StackOverflow
Apr 01, 2012 : Is innodb_file_per_table advisable?
Mar 25, 2012 : Why does InnoDB store all databases in one file?
Feb 03, 2012 : Scheduled optimization of tables in MySQL InnoDB
Nov 26, 2011 : ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full


Can large data deletion affect Innodb buffer pool performance? At the beginning of this year we lunched a lot of massive data deletions (millions of rows per week) in our production database. Also, there is an automatic integration of new data (insert/updates) of 10 to 25 .CSV files per day with a global/average size of 400MB.
We noticed that reading performance on data even with full using of index became too slow. We have about 122GB of daya with 60GB of RAM and I configured 48GB for the Innodb buffer pool (please check details in images).
Is the deletion affect buffer pool performance ? What is the best/fast way to resolve that issue ?
Thank you for your help.

**

(UPDATED)

**
It's not really a fragmentation issue, I added a 3rd image for more information.
(updated)
It is not indexes (the use is about 90% - 100%) but the bad way the app is using the db model made is too bad.

I restarted yesterday the server with making some changes in the configuration file :
innodb_buffer_pool_size = 48
innodb_buffer_pool_instances = 32
innodb_read_io_threads  = 16
innodb_write_io_threads 8

The buffer pool is used, no more slowness noticed until now (after some refresh).
However, still not understand the reason of the impact of deletion on the buffer pool. Thus I believe that the bad way the app is using queries via ORM is the source of this issue.


Data warehouse vs Data mart (sql server) I have a very basic question. My understanding is Data mart is essentially a database for a business segment per say and data warehouse is a warehouse of multiple data marts and other sources of data combined in a way that allows ease of analysis and reporting.

My question is: In case of a single database/data mart. Is it a good idea to create a data warehouse with a different schema (e.g. star or snowflake), just to enhance analysis efficiency?
If not, should the current database be modeled as a data warehouse or a data mart?

This is a pretty good question. This topic, many times, gets over-thought. It really all comes down to business needs.

If the need to compartmentalize data in to different subject areas (Finance, Sales, Manufacturing, Inventory, etc...) If this is far out of scope for the business cases, then you should probably stick with your standard database schema.

From my view point, a single data mart is not terrible useful except for in fringe cases where a single department has needs for a data warehouse like reporting infrastructure... Then a data mart might be useful.


Refresh (replicate) MySQL database from server to server I'm new to MySQL but I have experience with other major RDBMS like Oracle and MSSQL.
I'm trying to find out what are some of the widely used methods (or best practices) when it
comes to refreshing one or more MySQL databases between two servers on a nightly basis.
For example:    refresh the "QA" server from "PROD". I would like to develop an automatated
process to handle this refresh/reload and put it on a scheduled job and have it run nightly.
The current setup is running under Linux.

I would like to get some feedbacks or suggestions from those who have done this before
or provide me with a few pointers.

Thank you

I would imagine that a nightly "refresh" between two databases like you say would involve dumping one database and importing the dump into the other database. That would lead to major performance drags every time you do that process.

Personally, I don't think that is a great idea when there are tools that allow you to replicate data between database servers. I am afraid I don't entirely understand what your use case entails, but I would recommend reading up on MySQL Replication


ola-hallengren running full backups and log backups at the same time-- restore issues? I recently ran across an issue trying to restore a db and log files.  DB restored without issues.  About 10 log files in, I received a message stating the LSN was out of sync and could not finish.  I used a tool that puts them in order, so that's not my issue.   I have my log backups set to run in 15 minute intervals.  This large database takes a couple of hours to perform a full backup. Could the log backups happening simultaneously during the full backup cause this situation?
The end result was not bad this time, but this is alarming.  I am trying to maintain a RPO of 15 minutes.
This little test resulted in an 8 hr RPO.... unacceptable.
Regards,
Tim

Could the log backups happening simultaneously during the full backup
cause this situation?

No, simultaneous full and log backups have been possible since SQL Server 2005, as Paul Randal states here in a post on concurrent log / full backups.

Here’s a question that came in – what changed in SQL Server 2005 that
allows concurrent log and full backups?
In SQL Server 2005, the restriction was lifted, but there’s a subtle
twist. You can do concurrent log backups with fulls BUT the log is not
cleared when the log backup ends. The clearing of the inactive portion
of the log is delayed until the full backup completes. This could
cause you to have disk space problems if your log generation rate is
huge and you’re relying on very frequent backups to manage the log
size.

Also stated here regarding the many myths regardings backups.

I used a tool that puts them in order, so that's not my issue

If it is not the tool's wrong doing as you mention then I would try to look if there are any other log backups being taken in your backup history dmv's in the MSDB database.
Double check the restore procedure as well so that you are certain that you have all the log files (for example different paths after failing over the Always On Availability Group if the database is participating in an Availability Group, however Ola's procedure should take care of that).


When to Use Functions over Stored Procedures What advantages/circumstances do functions have over stored procedures? Want to justify use for function. It seems stored procedures benefit in every way, from transaction handling, input, multiple values, with insert into...exec. So when are ideal situations to apply functions rather than sps? This is not an opinion based question or recommendation, or requesting to use sps all the time, just want to see benefits of functions.

I see Number 5 is function advantages, but there are many ways around this in sprocs.

from a quick search i found the following article
as to the benefits of a udf vs an sps:

Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.

you can use this to limit the use of a query to a select statment only via the function

Procedures can't be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.
Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.

this might have an effect on the query load because for each row returned it will execute your udf.

Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.
Inline Function can be thought of as views that take parameters and can be used in JOINs and other Rowset operations.

In my opinion, it's really a matter of the relevant use case.
You can use a function when you need to manipulate the data per row, or when you want to be on the safe side that a DML query won't run for a user that has permissions to run DML queries.


How to represent it with UNIQUE indexes and foreign keys? "Group" is a group of pupils. "Topic" is a topic of lessons.

To every group corresponds exactly one topic. (So there may be multiple groups per topic.)

To a pupil corresponds multiple groups, but maximum one group per topic.

How to implement this in terms of UNIQUE indexes and foreign keys in MySQL?

This is a common problem. The "trick" is to:

add in Groups - besides the Primary Key - a redundant Unique constraint on (Topic, Group).
this allows you to add the Topic attribute in the Participates relation and
forces you to modify the Foreign Key constraint from Participates to Groups so it includes Topic, not only Group.
then you can add a Unique constraint on Participates (Pupil, Topic) (meaning a pupil can participate only once in a topic, not twice or more, which is the wanted result.)

Topics
  Topic  PK

Lessons
  Lesson PK
  Topic         FK -> Topics

Groups
  Group  PK UQ1
  Topic     UQ1 FK -> Topics

Pupils
  Pupil  PK

PupilParticipatesInGroup
  Pupil  PK UQ1 FK1 -> Pupils
  Group  PK     FK2 -> Groups
  Topic     UQ1 FK2

Appendix

 Abbreviation   Constraint
 ------------   -----------
   PK           Primary Key
   UQ           Unique
   FK           Foreign Key

Note that the design is in 3NF and that the redundant Unique constraint on Topics is only needed for the implementation in most DBMS (so the Foreign Key is enforced.)

The only other issue is the Group -> Topic dependency in the Participates relation which makes the schema to violate BCNF.

In the linked page, in paragraph Achievability of BCNF, you'll see that it has been proven that not all tables can be decomposed into tables that satisfy BCNF and preserve the dependencies that held in the original table. The provided example is striking similar to the Participates table here:

  In some cases, a non-BCNF table cannot be decomposed into tables that satisfy BCNF and preserve the dependencies that held in the original table. Beeri and Bernstein showed in 1979 that, for example, a set of functional dependencies {AB → C, C → B} cannot be represented by a BCNF schema.[6] Thus, unlike the first three normal forms, BCNF is not always achievable.

Lets make one more change nonetheless, in order to remedy that (if we can!):

Lets examine what happens if we remove (as if it never existed) the Primary Key (Group) constraint from Groups (and put the Unique in its place.) Lets also change the Primary key in Participates - notice - to be exactly the Unique constraint that is our target from the beginning. The design becomes (first two tables omitted as they remain intact):

    Groups
      Group  PK
      Topic  PK     FK -> Topics

    Pupils
      Pupil  PK

    PupilParticipatesInGroup
      Pupil  PK     FK1 -> Pupils
      Group         FK2 -> Groups
      Topic  PK     FK2

No redundant columns at all. Magic! The Participates is a common many-to-many relation between Groups and Pupils (as our model wants) and the two Foreign Keys are referencing the Primary Keys of the corresponding tables.

So, what happened? Magic? (not really, no) And isn't the Group still a candidate key in Groups? (removing it was only a thought experiment anyway.) Don't we lose something by omitting this constraint from the actual tables?

The issue - and why the above is solving it - is that the Unique constraint you are trying to model needs the Topic attribute in the Participates relation. So, you have to use a candidate key in Groups which is compound and includes Topic. It doesn't necessarily have to be (Topic, Group). It could be some other combination. What other attributes does Groups have?

If, for example, there is an attribute OrderNo in Groups and (Topic, OrderNo) is unique, then we could have this design (at last!) where there is no redundancy, no superfluous attributes, all constraints are there and the schema is in BCNF:

    Groups
      Group   PK
      Topic      UQ1 FK -> Topics
      OrderNo    UQ1

    Pupils
      Pupil   PK

    PupilParticipatesInGroup
      Pupil   PK     FK1 -> Pupils
      Topic   PK     FK2 -> Groups
      OrderNo        FK2


Exporting SQL Server DB from my laptop to AWS RDS I am following this tutorial from AWS to export a SQL Server database that currently sits on my laptop to Amazon Web Services RDS. In particular I am trying the Generate and Publish script wizard followed by bulk copy.

The script that gets generated starts like this:

CREATE DATABASE [Test]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'Datapump_Test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Datapump_Test.mdf' , SIZE = 1127424KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'Datapump_Test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Datapump_Test_log.ldf' , SIZE = 2160960KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

So my question is what are those paths for (i.e. the C:\..... lines) and are they pointing to files on my laptop? And if so, should I change them when I run this script on the RDS DB? I find that if I leave them there it will error, but if I manually create the DB in SSMS and then comment out these lines and run the script, I still get many errors, but it does create all the table schemas, user and indices etc... so is this the correct approach? If it is, it seems strange that the link I posted above doesn't mention this. This makes me think I've done something wrong. Does anyone have any insight on the matter?

The paths in the generated script are the  locations where the data and log files should be on your laptop.

If you copy paste the script to amazon AWS that will be the location where the files are created when you create the database.

If that path doesn't exist on the Amazon instance you will get errors.

You could comment out the lines and then the files will be created in the default locations for the instance, with default sizes and a filename generated from the database name.

CREATE DATABASE [Test]
 CONTAINMENT = NONE
 /* ON  PRIMARY
( NAME = N'Datapump_Test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Datapump_Test.mdf' , SIZE = 1127424KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'Datapump_Test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Datapump_Test_log.ldf' , SIZE = 2160960KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)*/
GO

As you indicate some of the other errors are related to things like roles and permissions there is an advanced settings dialog where you can change some options, and you can also select which objects to script (such as users)

See this link How to: Generate a Script (SQL Server Management Studio) for an overview of the wizard.

For example if you select "select specific options" in this page you can deselect "Users"

On the next page you can click an advanced button

Which opens a dialog with a lot more options, allowing to script things like permissions and triggers and stuff like that.


CASE WHEN with GROUP BY I have a table that looks like this (oversimplified case of the actual case to make my point)

CREATE TABLE sales (
   id int,
   currency VARCHAR(5),
   price int
   shop_id int
);

Now I need to make a query that will give me something like this table, grouping by shops yet giving sum of sales per currency
My problem is when I make currency column non aggregate it fails because my query becomes non standard. When I add currency to group by then each currency creates new row, something I don't want.
I have run out of option and would appreciate any help or pointer to the right direction.

I use MySQL but would appreciate if I would get the solution that works with more databases (PostGreSQL being at the top of the list after MySQL)

Query that works with undesired results

SELECT   shop_id,
         (CASE WHEN currency= "GBP" THEN SUM(price) ELSE 0 END) AS POUND,
         (CASE WHEN currency= "USD" THEN SUM(price) ELSE 0 END) AS USDOLLAR
GROUP BY shop_id, currency

The data that should be produced by the query

shop_id     |   POUND   |   USDOLLAR
-----------------------------------------
    1       |   400     |   300
-----------------------------------------
    2       |   250     |   100
-----------------------------------------
    3       |   400     |   100
-----------------------------------------

You almost get it, try by putting SUM() at the beginning of the CASE statement.

SELECT   shop_id,
         SUM(CASE WHEN currency= "GBP" THEN price ELSE 0 END) AS POUND,
         SUM(CASE WHEN currency= "USD" THEN price ELSE 0 END) AS USDOLLAR
GROUP BY shop_id


Process was intentionally killed - now the process is still showing in "show processlist " We had a MariaDB long-running process (40 hours), so I killed the thread. But, the thread is still there, with:

showing Command as Killed.
a State of Unlocking tables.
Info is insert into ...

What does this mean? More importantly, how do I get rid of it? It's been over 31 hours since the thread entered the "Killed" state.

UPDATE: We restarted the container running MariaDB. There was no indication of anything wrong but... Would it make sense to recast this thread to be now about how to check for errors in the database (My MariaDB skills are rudimentary) or should I start a new thread?

The process is undoing its changes after being killed (that is why killing write processes on a long running transaction can be dangerous- it can lead to a long undo process with blocking).

The rule of thumb is that InnoDB's UNDO can take multiple times the time it took to write. If it is unsustainable, you can stop the server when ther is no more write activity and restart it skipping pending UNDOs (so it doesn't start again). This is done with the start option:

innodb_force_recovery = 3

Not ideal as a normal start option, as it could make you lose data. Generating a backup before the unclean restart (or in general) is always a good idea, even if technically safe assuming no other writes or undos are ongoing.


Mysql dump as small bulk insert In mysql (8.0), there is a table with over 10 million rows, I want to dump it as bulk insert sql.
I wish each insert sql contains maybe 1k or 10k rows, so that could execute the sql file quickly, overall.
So how to do such a dump ?

mysqldump has a"WJERE" clause in which can specify which data or number you want
mysqldump --opt --where="1 limit 1000000" database table > yourdumplimited.sql

or you can LIMIT byy time
 --where "your_date BETWEEN '2020-01-01' AND '2020-06-30'"

You can divide your table into fragmentss which you need.


Setting up sp_AllNightLog using Azure Blob Storage Has anyone setup Brent Ozar's sp_AllNightLog using Azure Blob Storage? Trying to start incorporating both but not sure of the steps or how to get started. Any tips or links to similar operations?

Brent Ozar's sp_AllNightLog uses Ola Hallengren's backup script.

  Inside each of those Agent jobs, they call sp_AllNightLog @Backup = 1, which loops through the table to find databases that need to be backed up, then call Ola Hallengren’s DatabaseBackup stored procedure.

So you just need to configure Ola's script to write to Azure Blob Storage.

  Striping of backups to Azure Blob Storage

  The SQLCAT - team has a blog post about how you can optimize performance when backing up to Azure Blob Storage. You can stripe the backups to multiple files, and use the options MAXTRANSFERSIZE, and BLOCKSIZE.

EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES',
@URL = 'https://myaccount.blob.core.windows.net/mycontainer',
@BackupType = 'FULL',
@Compress = 'Y',
@Verify = 'Y',
@NumberOfFiles = 8,
@MaxTransferSize = 4194304,
@BlockSize = 65536

This is the important part:

@URL = 'https://myaccount.blob.core.windows.net/mycontainer'


Could not access database I have been trying to setup a local instance for a new application.  I am new to MYSQL and I cannot understand how to actually connect this database to the actual web application sitting in the /var/www folder in my computer.

I have created a  database in command line terminal:

mysql> create database mydatabase
    -> ;
Query OK, 1 row affected (0.00 sec)

mysql> create user 'host'@'website.local' identified by 'hmypassword';
Query OK, 0 rows affected (0.00 sec)

mysql> create user 'host'@'%' identified by 'mypassword';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on database.* to 'host'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

mysql -u host -h 127.0.0.1 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 52
Server version: 5.5.37-0ubuntu0.14.04.1 (Ubuntu)

How to actually connect this database to the website? I also don't understand how to make a schema for this database.  Whenever I try to refresh my web browser I get the following message:

Could not access database!

SQLSTATE[HY000] [2002] Connection refused

You create database mydatabase and then you grant on database.*
The correct is:

grant all privileges on mydatabase.* to 'host'@'%';


How to go about extending volumes on SQL Server boxes? Here is a question for the sys ops out there (or those who have gone through this experience before).

My company wants to allocate additional disk space on existing SQL Server boxes in production. We are wondering whether extending a volume ("Extend Volume..." in Disk Management) can be done ONLINE or whether the SQL Server Service needs to be shut down.

Any reason why SQL Server Service should be stopped to do this or is it OK to just go an perform this operation during the day?

Just in case the system specs are relevant. This is a real box (no virtualization) running Windows 2012 R2 and SQL Server 2008 R2.

Thanks!

You can extend disks while the database is online, but I will recommend you plan for a brief 15 - 30 minute outage during which you can take the services offline.

I've extended disks hundreds of times while databases were online and running without issues.  However one time, and only one time at this point in my career, the operation caused corruption in some SharePoint Content databases that were running and I lost a fair chunk of my hair getting things back up and working.

If you want/have to do this while the database is running, the key is that while you're extending the disks, you do NOT want any file growth activities occurring lest they try to grab some of the not-quite-ready new space.  I think this is what happened in my situation as our Sharepoint environments were treated as a "better" fileshare for the company and I'm pretty sure an auto-growth event occurred during the disk extend operation.

For what it's worth, MS also recommends you turn off the services while performing this operation.  After the disks are extended also be sure to run a DBCC CHECKDB to be certain no corruption occurred.


Alternative of replication in internet disconnection period of time I'm looking for an alternative of merge replication that work without internet connection, something like red gate sql data compare but not in uniq direction.

Sync Framework is designed explicitly to synchronize occasionally connected data sets.


Transfering records with foreign key referring to new value In an SQLite DB, I have to transfer some records from two tables (old_warnings and old_acknowledges) to other two tables(new_warnings and new_acknowledges).

Transfering warnings is not a problem.

INSERT INTO new_warnings (event, comment, value)
SELECT event, comment, value
FROM old_warnings
WHERE old_warnings.event = 'one';

However, the acknowledges tables have the reference to the warning (warning_id:integer) it refers to and if I transfer the acknowledges first, I end up having the old warning_id in the table. But, if I transfer the warnings first, I don't have any reference to the new id to insert in the new_acknowledges table.

Any idea of how to handle this situation?

There could be few approaches you can take to achieve this... based onhow big is the data set.

Use of cursors
Using cursor you can insert each row is new warning table capture the new_warningid created for that row and use it to insert respective Acknowledges referring to old_warning id and replacing with new_warningid.
use of temporary column in New_warning table which will hold old_warningid for lookup.
Add a column "old_warningid" to new_warnings table and use that column as lookup while inserting rows in new_acknowldges tables to get corresponding "new_warningid".once the migration is complete you can drop the columns from new_warnings table.


"1292 Incorrect datetime value" when updating an "empty" datetime field I have an updated_at field that was not initially set with default datetime value on the server. For a short period, a bunch of records got inserted with an "empty" value and are showing up at 0000-00-00 00:00:00 in the updated_at column. I wrote am update command to copy the values of the created_at field to updated_at and it is updating the field correctly, however, when I run it in MySQL Workbench, I get the following warning:
6 row(s) affected, 1 warning(s): 1292 Incorrect datetime value: '' Rows matched: 6  Changed: 6  Warnings: 1

My query:
UPDATE mytable
SET updated_at=created_at
WHERE updated_at='' and id >0;

I'm not sure where that warning is coming from since the created_at times are all valid and the updated_at fields are being set with the correct data.

Congratulation on your commitment to clean dates.
Temporary disabling the NO_ZERO_DATE sql_mode will enable this cleanup.
Just to be sure, use 0000-00-00 00:00:00 as the date too.
SET @@SQL_MODE = REPLACE(@@SQL_MODE, 'NO_ZERO_DATE', '');
UPDATE mytable
SET updated_at=created_at
WHERE (updated_at='' OR updated_at='0000-00-00 00:00:00') and id >0;


How to "insert into select from" skipping exceptions 1st post on SE/DBA; i am actually more of a DB developer but did not find a better suited forum, let me know if I picked the wrong one. I am new to PG but know Oracle.

I will need to report on JSON data, which is currently held in TEXT columns on Postgres 9.5. I plan to copy this data to proper JSONB fields so i can use the JSON parsing and SQL extensions of PG. Thus something akin to:

insert into T( id, jblob )
select id, jblob::jsonb from S;

Now this will fail if some of the existing rows are not valid JSON.
In Oracle I would either

insert into T select from S LOG ERRORS REJECT LIMIT UNLIMITED

which loads all it can and provides a log of failed rows to be reviewed (my 1st choice)

iterate over a  cursor and log errors myself in an exception handler

which is more work and usually slower

What is the best/most idiomatic way of achieving this with PostgreSQL (even in general, not necessarily for JSON content) ?

I tried "on conflict do nothing" but either used it wrongly or it is only meant to avoid duplicates and still raises an error in my case (with failed JSON casts).

Unfortunately there is no is_json() or similar type check, but you can write your own:

create or replace function is_valid_json(p_text text)
   returns boolean
as
$$
begin
  if p_text is null then
    return true;
  end if;

  if p_text::json is not null then
    return true;
  end if;
exception
  when others then
    return false;
end;
$$
language plpgsql;

Then you could do:

insert into T( id, jblob )
select id, jblob::jsonb
from S
where is_valid_json(jblob);

Or do the test and cast in one go:

create or replace function cast_if_possible(p_text text)
   returns jsonb
as
$$
begin
  return p_text::json;
exception
  when others then
    return null;
end;
$$
language plpgsql;

and

insert into T( id, jblob )
select id, cast_if_possible(jblob) as jblob
from S;

That would store NULL values for invalid JSON values. If you don't want that you would need to add condition to exclude those.

If you have a substantial number of (invalid) duplicate values in the table, then declaring the function as stable then Postgres can cache the results of the function call, which might improve performance.

However this is not going to be fast as the exception handler and PL/pgSQL have a substantial performance overhead.


simple SELECT EXISTS takes about two hours to execute I have mysql server hosted. Now, If I issue simple SQL statement
SELECT EXISTS (SELECT * FROM ORDERS WHERE USER_ID='c85bc9f7-704f-418e-98af-a1528622dd42' AND PLACE_ID='2' AND PRODUCT_ID='2bc7277f-c7c3-43bf-9598-9466c96a8f12')
it takes about 2 hours to execute. What could be wrong? The ORDERS table is tiny, it has about 10-50 records average per day.
The structure of table ORDERS is:

-- phpMyAdmin SQL Dump
-- version 4.2.11
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 30, 2015 at 01:34 PM
-- Server version: 5.5.40-cll-lve
-- PHP Version: 5.5.17

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `test`
--

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

--
-- Table structure for table `ORDERS`
--

CREATE TABLE IF NOT EXISTS `ORDERS` (
  `USER_ID` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `PLACE_ID` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `PRODUCT_ID` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `PRODUCT_QUANTITY` bigint(3) unsigned zerofill DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `ORDERS`
--

INSERT INTO `ORDERS` (`USER_ID`, `PLACE_ID`, `PRODUCT_ID`, `PRODUCT_QUANTITY`) VALUES
('c85bc9f7-704f-418e-98af-a1528622dd42', '2', '2bc7277f-c7c3-43bf-9598-9466c96a8f12', 001),
('c85bc9f7-704f-418e-98af-a1528622dd42', '2', '4782e465-fb0a-46a0-9323-aeb3b05bb10e', 001),
('c85bc9f7-704f-418e-98af-a1528622dd42', '2', 'b2bd1c75-6b01-4105-a626-438e365c95f5', 001);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `ORDERS`
--
ALTER TABLE `ORDERS`
 ADD PRIMARY KEY (`USER_ID`,`PLACE_ID`,`PRODUCT_ID`);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Here is screenshot of situation:

I think the problem stems from the one and only nullable: PRODUCT_QUANTITY. Why ?

Look at your query

SELECT EXISTS (SELECT * FROM ORDERS
WHERE USER_ID='c85bc9f7-704f-418e-98af-a1528622dd42'
AND   PLACE_ID='2'
AND   PRODUCT_ID='2bc7277f-c7c3-43bf-9598-9466c96a8f12');

The subquery has SELECT * and the ORDERS table has 4 columns

3 with NOT NULL
1 with DEFAULT NULL

Based on the MySQL Documentation on Subquery Optimization, the MySQL Query Optimizer performs subquery optimization by means of converting a subquery as follows:

  The MySQL query optimizer has different strategies available to
  evaluate subqueries. For IN (or =ANY) subqueries, the optimizer has
  these choices:

  Semi-join
  Materialization
  EXISTS strategy

  For NOT IN (or <>ALL) subqueries, the optimizer has these choices:

  Materialization
  EXISTS strategy

In your particular case, you explicitly wrote the SELECT EXISTS (SELECT * ...). So, you are more responsible to optimize the subquery. Consequently, you should expect the MySQL Query Optimizer to start messing with you since it did not participate in the construction of the EXISTS strategy.

From the MySQL Documentation on Optimizing Subqueries with EXISTS Strategy, this is how checks on done for your SELECT:

  Becomes:

EXISTS (SELECT 1 FROM ... WHERE subquery_where
                      AND oe_1 = ie_1
                      AND ...
                      AND oe_N = ie_N) The following discussion assumes a single pair of outer and inner expression values for simplicity.

  The conversion just described has its limitations. It is valid only if
  we ignore possible NULL values. That is, the “pushdown” strategy works
  as long as both of these two conditions are true:

  outer_expr and inner_expr cannot be NULL.
  You do not need to distinguish NULL from FALSE subquery results. (If the subquery is a part of an OR or AND expression in the WHERE clause,
  MySQL assumes that you do not care.)

The bulletpoint that says outer_expr and inner_expr cannot be NULL might be the problem. The inner_expr could present possible rows with a NULL in one of the columns in spite of all the data being non-null. To me, such a check is unnecessary.

The same document says the following in the evaluation process:

  For proper evaluation, it is necessary to be able to check whether the SELECT has produced any rows at all, so outer_expr = inner_expr cannot be pushed down into the subquery. This is a problem, because many real world subqueries become very slow unless the equality can be pushed down.

To make a long story short, slowness is being imposed because your SELECT EXISTS (SELECT * ...) is not optimal and the MySQL Query Optimizer is still abiding by its preset rules. There are checks being done on possible null results from the subquery even though you know there will be a non-null result.

SUGGESTION

Rather that doing a deeper dive into what makes your query's execution slow, I would rewrite the query as follows:

SELECT COUNT(1) FROM ORDERS
WHERE USER_ID='c85bc9f7-704f-418e-98af-a1528622dd42'
AND   PLACE_ID='2'
AND   PRODUCT_ID='2bc7277f-c7c3-43bf-9598-9466c96a8f12');

What are the advantages of rewriting the query this way ?

Subquery evaluation is completely eliminated
Return value is always non-null, either 0 or 1
Checking for 0/1 should be just as good as checking forTRUE/FALSE

GIVE IT A TRY !!!


Advanced Replication vs Oracle Streams I'd like to know what is advantages of Streams over Advanced Replication. I wanted to use updatable materialized views on my project, but on one forum I read comment that if we don't use very old version of Oracle, it's better to use Streams.

Can you tell me what benefits I'll get if I use Streams, like simpler configuration, better conflicts resolving configuration, more stable work of replication or something else? Client database should be able to work offline.

The two major benefits tend to be efficiency and flexibility.

Streams is a much more efficient architecture.  Rather than synchronously capturing changes in materialized view logs which adds overhead to the transaction, Streams mines the redo logs that are generated by the change in order to generate the change vector to send to the target system.  That also allows the change to be replicated to the other system(s) much more quickly.

Streams is a much more flexible architecture.  You can write your own custom apply processes which makes it relatively easy to inject additional processing or logging, to ignore certain changes, etc.  You can send a Streams logical change record (LCR) to a JMS queue or to a non-Oracle system (though, of course, you'd need to write code to apply the LCR on the non-Oracle system).


mysql replication master - slave I have a setup of two databases, master - slave.
Slave was stooped for about two weeks and old binlogs binlogs purged from master but copied on another fs before purging.

Slave can't catch up the master because index don't contain anymore the names of the purged binlogs.

I've already copied old binlogs to the correct place but now I need to edit the .index file on master.

Is there a tool that can be used to tell mysql too add binary logs back to index files (somethinf like unpurge :) or add binary log) or can index file be edited without shutting down mysql ?

Thanks a lot.

I have never tried but I am quite positive about editing index file. You can add filenames to index without restarting.

That said you actually don't need to. You can copy over the files to slave and play the sql.
Generate sql using mysqlbinlog utility.


What is the difference between Federated and Decentralized Data Warehouse? I can not find any clear definitions or explanations of any of these. Both seem decentralized. It appears that in Federated DWH, the data is distributed and not integrated into a single repository and accessed from distributed sources.

While in Decentralized DWH implementation the data is integrated into one central repository.

Please explain the difference between these two implementations.

A decentralised data warehouse is essentially a collection of data warehouses maintained by individual regions or business units but made available centrally.  These may be on the same physical server, share reporting tools, or be made available across the organisation in some other way.  There may also be centralised components such as master data management.  This is normally done because centralised data warehouses get unwieldy beyond a certain size of organisation.  A data warehouse has to be responsive to change and if it is too unresponsive then individual departments will start building their own solutions.

You can see this in investment banks, where the tendency is to do data warehouses to meet specific requirements (e.g. a particular regulatory initiative or some type of financial reporting) rather than to build a centralised warehouse across the whole business.  A company the size of a large bank is too complex to do the requirements for a fully centralised EDW in a reasonable length of time.

A federated data warehouse adds a master consolidation layer across the decentralised data warehouses.  Typically this will only house a narrow vertical slice of the data, as its purpose is to consolidate key metrics across the whole business for company or group level reporting, rather than to provide a generalised MI platform for all departments.  The departments are left to produce their own EDW or MI systems but are required to furnish the data sets needed to populate the central consolidation layer.

This architecture gives you the best of both worlds.  Central management can see their metrics across the whole organisation, and the departments can arrange M.I. solutions to meet their needs.  Central management only need to impose the data requirements needed by their analytics and MI as feeds provided by the departmental systems.  If they need more in-depth reporting or analysis on a specific department then this can be furnished by the departmental systems.

The article linked below discusses federated data warehouses in more depth.

http://www.zentut.com/data-warehouse/federated-data-warehouse-architecture/

This article discusses data warehouse topologies in more depth.

https://www.ibmbigdatahub.com/blog/data-warehouse-architectures-multinational-organizations-part-1

https://www.ibmbigdatahub.com/blog/data-warehouse-architectures-multinational-organizations-part-2


odbc connection from windows machine to mysql server running on ubuntu? I was able to install mysql server on an ubuntu machine and set up a test data base, created a user and granted that user access to the database.
I also changed the binding address to the IP of the server and opened up port 3306.

I am also able to access the mysql database from another remote linux machine using the mysql client.

My question is, is it possible to access the mysql server from a windows machine using the odbc data source administrator with SQL server native client 10.0? is it even possible to do this?

It isn't possible as SQL Server Native Client is a client library for SQL Server, not for MySQL. If you want to access a MySQL server through ODBC, then you need the MySQL ODBC driver (Connector/ODBC).


Create a trigger to fill a field according to acondition in sql I want to create a trigger to fill a field according to acondition as below description
For example:
I have a table for all insps that have attachments named: “inspectionAttachments” and I have another table “inspections” that contains all insps and I want to fill for example column/field named “shop” in the inspections table to define which insps that have attachments or not
So, I want to fill the shop field with Yes if at least this insp has one attachment and fill this field with No if there is no attachement
How I can write the trigger to make what I want
Thanks for any help

Best,
Lubna

The following solution assumes rows will be added to the inspections table first (with a default value of NO in the Shop column. It is also assumed that only one attachment can exists for a given inspectionid
Two triggers have been defined on the attachments table.
The insert trigger on the attachments table will update the inspections table with YES in the Shop column to reflect the added attachment.
The delete trigger on the attachments table will update the inspections table with NO in the Shop column to reflect the deleted attachment.
--demo set up
DROP TABLE IF EXISTS Inspections;
DROP TABLE IF EXISTS Attachments;
go
    CREATE TABLE Inspections (
    InspectionId INT
    ,Shop CHAR(5)
    );
GO

CREATE TABLE Attachments (
    InspectionId INT
    ,Attachment CHAR(5)
    );
GO

--create insert trigger
CREATE TRIGGER Attachments_Insert ON Attachments
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE i    --attachment found for inspection
    SET i.Shop = 'YES'
    FROM Inspections i
    JOIN inserted ins ON ins.InspectionId = i.InspectionId
END;
GO
--create delete trigger
CREATE TRIGGER Attachments_Delete ON Attachments
AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE i    --attachment deleted for inspection
    SET i.Shop = 'NO'
    FROM Inspections i
    JOIN deleted del ON del.InspectionId = i.InspectionId

END;
GO

--test trigger
--insert test rows into inspections table with default of NO in Shop column
INSERT INTO Inspections (InspectionId,Shop)
VALUES (1,'NO');

INSERT INTO Inspections (InspectionId,Shop)
VALUES (2,'NO');

--insert row into attachement
INSERT INTO Attachments (
    InspectionId
    ,Attachment
    )
VALUES (
    1
    ,'xyz'
    );

--verify results - one inspection row should have YES for Shop column
SELECT *
FROM Inspections;

--testing delete of attachment - should update existing inspection to NO
delete from Attachments where InspectionId=1

--verify results
SELECT *
FROM Inspections;


PowerShell and SQL Server export XML I have a stored procedure that ends with FOR XML EXPLICIT and I need to put the xml file in a folder.

I've set up the following PowerShell, and run the script providing the two parameters.

The name of the script is xml.ps1

./xml "xml" "c:\test.xml"

param
(
    [string]$outputType,
    [string]$filename
    )

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=SLISQL;Database=Development_App;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "dbo._REX_NewSP"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]
if ($outputType  -eq "Text")
{
$DataSet.Tables[0] | format-table -auto > $filename
}

if ($outputType  -eq "xml")
{
$DataSet.Tables[0] |Export-Clixml $filename
}

I see the xml output in the PowerShell screen, but no text.xml file is output.

I turned your PS into a function, just changing the calls, ran it through the PS ISE, and it worked just fine for me in testing, outputting the data to the location. I would ensure that you have the ability to write to the root of the C: drive. Typically this can require admin permissions. It's possible you are swallowing the error somewhere.

function ProcToXmlFile {
param
(
    [string]$outputType,
    [string]$filename
    )

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=localhost;Database=master;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "select name from sys.databases for XML AUTO"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]
if ($outputType  -eq "Text")
{
$DataSet.Tables[0] | format-table -auto > $filename
}

if ($outputType  -eq "xml")
{
$DataSet.Tables[0] |Export-Clixml $filename
}
}

ProcToXmlFile "xml" "c:\temp\test.xml"

Created the file in the correct location with the contents:

<Objs Version="1.1.0.1" xmlns="http://schemas.microsoft.com/powershell/2004/04">
  <Obj RefId="0">
    <TN RefId="0">
      <T>System.Data.DataRow</T>
      <T>System.Object</T>
    </TN>
    <ToString>System.Data.DataRow</ToString>
    <Props>
      <S N="XML_F52E2B61-18A1-11d1-B105-00805F49916B">&lt;sys.databases name="master"/&gt;&lt;sys.databases name="tempdb"/&gt;&lt;sys.databases name="model"/&gt;&lt;sys.databases name="msdb"/&gt;&lt;sys.databases name="HealthCheck"/&gt;&lt;sys.databases name="RestoreAttachTEST"/&gt;</S>
    </Props>
  </Obj>
</Objs>

Here's an updated version (a little hacky, I know) that writes the header to a file, and then just the XML data that is produced. See if this gets you a little closer.

function ProcToXmlFile {
param
(
    [string]$outputType,
    [string]$filename
    )

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=localhost\sql16;Database=master;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "select name from sys.databases for XML AUTO"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0] | Format-Wide -AutoSize
$Headerstring = '<?xml version="1.0" encoding="UTF-8?>'
$Headerstring | Out-File $filename
if ($outputType  -eq "Text")
{
$DataSet.Tables[0] | Format-Table -HideTableHeaders | out-file $filename -Width 5000 -Append
}

if ($outputType  -eq "xml")
{
$DataSet.Tables[0] |Export-Clixml $filename
}
}

ProcToXmlFile "text" "c:\temp\test.xml"


Is it possible to have unique + null I have a need to have a database table that has a column that is either null, or unique values. I know I can control that in code, but is there a way to do that with an index as a backup? Or is that just really bad DB design? :) For example, I don't want to require an email address, but if they do enter one, I don't want to allow duplicates. Easy enough to check with my code, but I would like to configure Mysql to enforce it. Maybe a trigger returning false if the rules are violated?

For all engines, a UNIQUE index permits multiple NULL values for
  columns that can contain NULL.

http://dev.mysql.com/doc/refman/5.7/en/create-index.html

It is easily testable, just try to insert multiple NULL values in dummy table.


TSQL- Column DataType Length Can anyone please explain me the below one. I used the below query in getting the table columns info, but the max length seems to be x*2 of the actual col length. I mean if I have a col with datatype nvarchar(10) the output of my query has max_length value as '20'. I'm little confused here.. A'm I looking at the right one?

SELECT
    c.name  ColName,
    t.Name 'Data type',
    c.max_length 'Max Length',
    c.precision ,
     c.scale ,
    c.is_nullable,
    ISNULL(i.is_primary_key, 0) 'Primary Key'

FROM
    sys.columns c
INNER JOIN
    sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
    c.object_id = OBJECT_ID('Table Name')

The column max_length is the maximum column length in bytes.  For the National-Character set string types (NCHAR and NVARCHAR), each character requires two-bytes, so NCHAR(10) would have a max_length value of 20.


need help in the update query table name:- **t_ci**
s39   column_name
        176

i want to have my table like this

s39    column_name
176     176

But this query works

UPDATE t_ci SET s39=s345  WHERE blueprint_id=1234;

But I dont know the column_name for example s345. This column_name will be the output of a different query

SELECT column_name FROM `t_core_properties` WHERE property_name='Virtual IP Address'

I tried this query.

UPDATE t_ci SET s39=(SELECT column_name FROM `t_core_properties` WHERE property_name='Virtual IP Address')
WHERE blueprint_id=1234;

this comes as

s39    column_name
s360     176

But it adds only the column_name not the value inside that.

I tried using the Mysql User defined Variables

SET @column = (SELECT column_name FROM `t_core_properties` WHERE property_name='Virtual IP Address');
UPDATE t_ci SET s39=@column WHERE blueprint_id IN(SELECT id FROM `t_core_blueprints` WHERE NAME='VMWare VCenter');

I get the same result as before.

The task can be solved using dynamic SQL. Something like:

CREATE PROCEDURE assing_value(propertyname TEXT, blueprintid BIGINT)
BEGIN
SELECT CONCAT('UPDATE t_ci SET s39=', column_name, ' WHERE blueprint_id=', blueprintid)
  INTO @sql
  FROM t_core_properties
  WHERE property_name=propertyname;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE stmt;
END;

and then

CALL assing_value('Virtual IP Address', 1234);

or the same in plain code:

SET @column := ( SELECT column_name
                 FROM `t_core_properties`
                 WHERE property_name='Virtual IP Address');
SET @sql := CONCAT('UPDATE t_ci SET s39=',
                    @column,
                   ' WHERE blueprint_id IN (',
                   ' SELECT id',
                   ' FROM `t_core_blueprints`',
                   ' WHERE NAME=\'VMWare VCenter\')');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE stmt;


Change expiry date of TDE certificate of User Database without changing Thumbprint I have been searching through internet on how to change expiry date of TDE certification of a user database at SQL Server 2014 SP2 and got so many articles and most of them are suggesting to drop and create another certificate with a different expiry date.

My concern is regarding backup of Database which was taken before changing Expiry Date. When I drop certificate and create a new one with different expiry date, thumbprint of certificate changes and hence unable to restore the backup which was taken prior to change.

Below is the error screen-shot:

Appreciate if I can get some expert advise on this issue with details.

Referencing the information in Updating an expired SQL Server TDE certificate, the post explains that it is easy to create a new certificate with an updated expiration date for use in TDE.

USE [master]
GO

CREATE CERTIFICATE NewTDECert
WITH SUBJECT = 'New TDE DEK Certificate',
EXPIRY_DATE = '20181231';
GO

USE [YourDatabase]
GO
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER CERTIFICATE NewTDECert;
GO

After executing the above commands, your database now uses the new certificate with regards to TDE.

However - the post makes it very clear that you still need to keep the old certificate in case you want to restore the database from a backup created when the old certificate was being used.

From that post (highlighting mine):

  It is up to you whether you want to drop the old certificate from the
  SQL Server instance. You should always keep a backup of the old
  certificate in case you need to restore a TDE enabled database using
  an older backup that used the old key.

Additonally (from that post),

  It is paramount to backup the TDE certificate after any certificate
  changes as this is required to restore the database to another SQL
  Server instance. We can issue a backup certificate command for the new
  certificate as shown below.

USE [master]
GO

BACKUP CERTIFICATE NewTDECert
TO FILE = '\\SQLP2\temp\NewTDECert.cer'
WITH PRIVATE KEY (FILE = '\\SQLP2\temp\NewTDECert.pvk',
ENCRYPTION BY PASSWORD = 'str0ngPa$$w0rd');
GO

Additional information can be found in Replacing an expiring SQL Server encryption key.  (Highlighting mine).

  To rotate the certificate for TDE, add the new certificate as above,
  then execute the command ALTER DATABASE ENCRYPTION KEY with the
  ENCRYPTION BY SERVER CERTIFICATE clause:

ALTER DATABASE ENCRYPTION KEY
    ENCRYPTION BY SERVER CERTIFICATE newCertificate;

  SQL Server re-encrypts the database encryption key with the new
  certificate, and drops the encryption by the old certificate when it’s
  finished. And as before, the data itself isn’t re-encrypted, so the
  process finishes almost immediately.

  One caution: Always keep at least one backup copy of every certificate
  you use. If you ever need to restore a database that used encryption,
  you’ll need the certificate that was in effect at the time the backup
  was created. Make a habit of creating a certificate backup immediately
  after creating it in SQL Server. Store the backup in a safe place;
  also keep a copy of the passphrase you use to encrypt the certificate
  backup, preferably in a different safe place for security. Retain
  these forever, or until the last database backup that may possibly use
  them has been purged.


Performance: CTE in an "IN"-Operator I've got a query with a CTE (getting all children of a parent) followed by a main statement using the "IN"-Operator to filter for the CTE's output.

Roughly looks like this:

WITH cte_table AS (...)

SELECT (stuff)

FROM tables JOIN more tables

WHERE ID IN

(SELECT ID FROM cte_table)

The CTE runs fast by itself. The main query runs fast if fed with the CTE's output manually. Fast means that it takes no noticeable time. But when I combine both it takes almost 20 seconds.

Any ideas for solutions? Or, what is probably more important to me, the reason why it could take that long?

You could try refactoring the query to use equivalent, but different, syntax.

IN is more-or-less an inner join. So your query could become

...
FROM cte_table JOIN tables JOIN more tables
...

For your query a row has to be in the CTE output to be returned. The same applies to this inner join syntax. The two queries are equivalent.

Although you and I know these two are interchangeable the query optimizer doesn't always realize this. So by giving it a different place to start from you can (sometimes) end up with a different plan.

Really need to see the table definitions and the "slow" plan for a proper answer, though.


Is it possible to reliably number rows in some order conforming to SQL standard? For example, if I have a table with single column numbers and I want to 1) sort them in asc/desc order 2) (re) number them from 1 to n, using that order (so I can say that this number is ith biggest/smallest).

If the DB doesn't have row_number function (MySQL, for example), how would one do that?

If I understood correctly, the order in which SELECT return the rows in unspecified and could be inconsistent.

And something like this (proposed in many answers to the similar question)

SET @num = 0;
SELECT number, @num := @num + 1 as num FROM ... ORDER BY number

Is wrong, since it seems that there is no guarantee regarding the time when ORDER BY would be evaluated. I.e. it can sort the numbers first, and then number them using @num, or it could do the opposite and sort after all the rows were numbered. Am I correct?

And using inner selects won't help, since it's not guaranteed that the order will be preserved.

MariaDB 10.2.2 has a bunch of Windowing Functions.  In theory they should be 'standard-compliant'.

An example: (also demonstrates a sequence number generator)

SELECT ROW_NUMBER() OVER(ORDER BY seq) AS row_num,
       seq*seq
    FROM seq_10_to_20_step_2;

+---------+---------+
| row_num | seq*seq |
+---------+---------+
|       1 |     100 |
|       2 |     144 |
|       3 |     196 |
|       4 |     256 |
|       5 |     324 |
|       6 |     400 |
+---------+---------+
6 rows in set (0.00 sec)


DBMS_CRYPTO Privilege issue I am trying to grant DBMS_CRYPTO privilege to one of the user in DB and referred all the forums and Oracle help page to do that. I have followed below steps to grant privilege..

SQL> grant execute on SYS.DBMS_CRYPTO to ABC1;

Grant succeeded.

SQL> commit;

Commit complete;

After running the above steps, I logged into database as ABC1 and verified the privileges he own. He doesn't have DBMS_CRYPTO privilege.

Also, I ran below sql to see how many users have this privilege and surprisingly, I dont see my user in the list.

SQL> select * from dba_tab_privs where table_name = 'DBMS_CRYPTO' and owner = 'SYS';

GRANTEE     OWNER TABLE_NAME   GRANTOR PRIVILEGE GRANTABLE HIERARCHY
SYSMAN      SYS   DBMS_CRYPTO   SYS    EXECUTE   NO        NO
APEX_030200 SYS   DBMS_CRYPTO   SYS    EXECUTE   NO        NO
OWBSYS      SYS   DBMS_CRYPTO   SYS    EXECUTE   YES       NO

My oracle version: 11.2.0.4.0, Can anyone guide me to see what I am missing in getting this privilege?

Thanks

When you issued the grant you were not connected to the same database as when you connected as abc1.


MySQL 2 Million Rows, longtext and varchar indexes I'm trying to find a solution for this, with no success.

I have a table with ~2M rows, and I need to do a WHERE with 3 expressions using OR.

name LIKE '%PARAM%' OR text1 LIKE '%PARAM%' OR text2 LIKE '%PARAM%' ORDER BY views DESC

name is a varchar

text1 and text2 are longtext (~6000 characters each)

text1 and text2 are indexed FULLTEXT
name is indexed BTREE, views too.

The table is MyISAM, it don't have many updates, but many selects;

The query takes ~12sec to complete.

Sorry if this question arleady exists, but I could not found this.

Thank u

Each run of this query will scan the whole table for two reasons:

The index on name will not be used as the value starts with a wild char %
You are not using the proper functions that utilize the fulltext index on the other two fields.

Proposed solution:

add a fulltext index on name field
Use the proper functions to search. in this case, it is match : https://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html#function_match

Notice: Depending on your version of MySQL, using OR to combine conditions could be a performance hit. It is not the case in 5.6 +


Select max(pk) instead of sequence.nextval I have a client/superior who keeps suggesting that I select max(primary_key)
when I insert a new row.

I gut says it's VERY wrong but I don't have exact reasons to support my suggestion.
The only thing so far I can think of is when two people are trying to insert into the same table and getting data mixed up.

What are the reasons one should not rely on selecting max(primary_key) + 1
to generate a pk value ?

The presentation of the data should not be the determining factor in how the data is stored - especially in the case of a primary key.  That would be like saying that all dates need to be stored in the database as strings so that a particular date format could be displayed.

To make the select max(pk) + 1 work in a system with more than one user is to programmatically force only one session to get the next PK value at a time - seriously compromising your concurrency. Either that or you will have to write code to intercept the PK failure and try again.

The data displayed can be formatted in the presentation layer in any way that makes sense.  You can do a to_char to add the leading zero's in the select if needed - another string in the record could be appended - whatever would make it look more like a "code".  In our environment we just use the generated sequence as the identifier - its more important that it is generated efficiently and is unique than how it looks.


OLAP linked server fails connection test I am trying to create a new linked server to an Analysis Services database and I would like to use this linked server to import data into a SQL Server table.
example:
SELECT *
into raw.example
FROM openquery( Test1, 'MDX CODE/etc etc'

Below is a screenshot of what I have done (just putting in examples/test names instead of the names I need to use). The data source is pointing to a valid server name, which I know I have access to.

After I click "ok" to create this linked server this message comes up:

The linked server has been created but failed a connection test. Do you want to keep the linked server?
OLE DB provider 'MSOLAP' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode. (Microsoft SQL Server, Error: 7308)

I am not sure why it isn't connecting. Is it a server issue where I have to ask for permission to access it, or do I somehow need to revert away from the provider being configured to run in a single-threaded apartment mode?
I am doing this on my local SQL Server Express Edition machine, whereas before my script and OPENQUERY/MDX query worked on a remote desktop server etc.
I have looked at other posts but those solutions do not solve my problem.
If it matters, the MDX I'm trying to execute was generated by XLCubed.

Enable OLEDB Driver in SQL Server

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

If you are still facing the error after running above statement, please post error message


Best way to store deleted rows I have a table with operations that can be deleted by users.

What happens, is that I want to store deleted rows for some time, to serve as recover in case any client regrets the deletion.

One of the columns is id_user, so I had two ideas:

create another table equal to the first one to store the deleted rows (the problems is to keep them equal, so any changes in the first one I have to remember to do on the second);

or

update the rows setting id_user=-id_user, so that the records are not listed to this user but they are still related and I could recover them if I wanted (the problem is to store different stuff [valid and backup records] in the same table);

What is the best aproach?

PS: These records are called in a huge bunch of places in my application, adding another column implies in changing my aplication and that is out of question.
If that ended up being the only solution I'd rather not to add this 'disaster recovery feature'.

I usually set it as a trigger and store changes in a separate table. Keep the id_user as a FK in the table and throw in a date field to record when the change was made. Then you can recover by date even if you don't know the exact date.


ODBC Error for Oracle Client 12.2.0.1 I installed Oracle Client 12.2.0.1 (x32) on a Win7 x64 machine.  When I tried to create an ODBC by invoking "C:\Windows\SysWOW64\odbcad32.exe" and picking the "Oracle Client" from the list and clicked "Next", I immediately get this error message:

"... ODBC driver could not be loaded due to system error code 126: The specified module could not be found C:\app\client\oracle\product\12.2.0\client_1\BIN\SQORAS32.DLL)"

I have been Googling around and people were able to resolve this issue by installing Visual C++ Redistributable 2013 for Oracle Client 12.2.0.1.  I downloaded and installed but still get the error.

https://www.microsoft.com/en-us/download/details.aspx?id=40784

Does anyone have any idea what my problem could be?

After bashing my head for a good while, I could make the ODBC 32 bits run. It was the VC ++ 2013 Redistributable.

My install is version 12.2.0.1.0. I can't believe how stupidly hard it is to get the simple ODBC. You must uncompress the Instant CLient plus ODBC in the same folder and then manually install VCRedist to get this running. GEEEZ.

UPDATE:
Adding a few extra details on my exact steps:

Download Oracle Instant client from the download portal:
https://www.oracle.com/database/technologies/instant-client/downloads.html

Make sure you download the version that you need: X64 or X32. NOTE: DO NOT MIX THE x64 and X32 folders!!!

After you choose the x64 or x86 version, you will be presented with a huge list of packages. You want to make sure you scroll down to the proper version, in my case, it was 12.2.0.1.0.

You must download 2 packages:

Basic Package
ODBC Package

If you are installing x32 and x64, you must then download 4 packages:

Basic Package x32
ODBC Package x32
Basic Package x64
ODBC Package x64

Then, download and install VCredist 2010 and VCredist 2013. Here is a good resource to download them, for x32 you must install x32 bits VCRedist.
https://www.itechtics.com/microsoft-visual-c-redistributable-versions-direct-download-links/

Then, you must uncompress the Basic and ODBC package in the same folder. DO NOT MIX x32 and x64, you must create separate folders if you are going to install both bit parity versions.

Then, open CMD as administrator, navigate to your uncompressed folder and run odbc_install.exe.

Then go to ODBC Datasources (Start menu, type ODBC) and add a new DSN, you will be presented with the Oracle ODBC screen.
Here is a tutorial to create the DSN:
http://dba.fyicenter.com/faq/oracle/Define-DSN-with-ODBC-Manager.html

That's it! the key part here is the VCRedist, if you don't have it, you will have the error stated by the Original Poster.

I hope this helps.


SQL Server Row Count sp_spaceused rows discrepancy I am researching a performance problem with an application running on SQL Server 2005.  I've conducted performance tuning, and I realize that a key table is poorly structured(poorly chosen clustered index).  Unfortunatly, I've been asked to hold off on structural changes until some scheduled down time so I must improve performanc without fixing the root structural problem.

Thus, my question is related to the rowcount returned in sp_spaceused.  I understand that this value may not be current, but it should be close.  The discrepancy is 6 rows returned vs. an actual of over 18k rows.   I suspect that this discrepancy is causing some interesting execution plans (index scans instead of index seeks).  All other indexes on the table appear to have a correct row count.

Can anyone help explain this? Is this taken into consideration when selecting an execution plan?  If so, please provide some direction to updating this value?  I've tried rebuilding indexes and updating statistics.

EXEC sp_spaceused @objname=N'dbo.tblEMPLOYEES_MASTER';

Returns rows:  6

SELECT
      OBJECT_NAME(p.object_id)    AS ObjectName,
      i.name                      AS IndexName,
      p.index_id                  AS IndexID,
      NULL                        AS PartitionScheme,
      p.partition_number          AS PartitionNumber,
      fg.name                     AS FileGroupName,
      NULL                        AS LowerBoundaryValue,
      NULL                        AS UpperBoundaryValue,
      NULL                        AS Boundary,
      p.rows                      AS Rows
FROM sys.partitions     AS p
JOIN sys.indexes        AS i
      ON i.object_id = p.object_id
      AND i.index_id = p.index_id
JOIN sys.data_spaces    AS ds
      ON ds.data_space_id = i.data_space_id
JOIN sys.filegroups           AS fg
      ON fg.data_space_id = i.data_space_id
WHERE
      OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
ORDER BY
      ObjectName,
      IndexID,
      PartitionNumber;

All other indexes(non-clustered) on the table return a 18,163 row count, however the clustered index (IndexID of 1) has a row count of 6.

Thanks everyone for your feedback!

Yes, this can potentially affect plan choice. Does running DBCC UPDATEUSAGE make everything right again? e.g.

DBCC UPDATEUSAGE('yourdb', 'dbo.tblEMPLOYEES_MASTER');

Make everything right again? That doc says:

  Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure.


Identity Columns or UDF that explicitly generates a unique id? I am in middle of a debate about whether it is better to make a PRIMARY KEY out of an Identity Columns, our out of a UDF that explicitly generates a unique id.

I am arguing for the Identity Column.
My partner is arguing for generating the values manually, he claims

by putting the UDF on another table where we can have a UDF

lock the resource
increment an ID table with one field called ID_Value by 1
use this as a global unique identifier

Or have the table do an id+1 when inserting
That it's simpler to move data between servers and/or environments not having the identify constraint; moving from one DB where there is data to another similar DB with lets say staging or dummy data. For testing in non production we may want to pull all records from yesterday down to staging for testing.

Which implementation makes more sense?

Your colleague is an idiot.

The solution won't be scalable, the UDF isn't concurrent (same reason as this). And how do you deal with multi-row inserts: this would require a UDF call per row

And migrating to other RDBMS doesn't happen often in real life... you may as well not use SQL Server now and use sequences on Oracle and hope you don't migrate away.

Edit:

Your update states that moving data is for refreshing non-production databases.

In that case, you ignore the identity columns when refreshing. You don't compromise your implementation to make non-prod loading easier. Or use temp tables to track the identity value changes.

Or use processes: we refresh our test system every night from production which avoids the issue entirely. (And ensures our prod backup can be restored too)


Backups jobs are successful but cannot see differential backup in the media I have a backup job running on my Sql server that takes a complete backup at start of month and diff every two hour between 9 to 5.Now the problem backup jobs are running and successful but I cannot see differential backup in the media.

Backup job
USE [msdb]
GO

/****** Object:  Job [Backup]    Script Date: 04/06/2018 12:31:21 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 04/06/2018 12:31:21 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Backup',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=2,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'No description available.',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'Server\user',
        @notify_email_operator_name=N'AdminOperator', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Step-1]    Script Date: 04/06/2018 12:31:21 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step-1',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'DECLARE @File varchar(1000)
DECLARE @i int
declare @m as varchar(2)
declare @d as varchar(2)
declare @dt as varchar(8)
DECLARE @strSQL nvarchar(1000)
--if (day(getdate())<10)
--  set @d = ''0'' + cast(day(getdate()) as varchar(2))
--else
--  set @d =  cast(day(getdate()) as varchar(2))

if (month(getdate())<10)
    set @m = ''0'' + cast(month(getdate()) as varchar(2))
else
    set @m =  cast(month(getdate()) as varchar(2))
Set @dt = cast(year(getdate()) as varchar(4)) +  @m

SET @File = ''D:\SQLBackups\SQLBackups_database\database'' + @dt + ''.BAK''

EXEC master..xp_fileexist @File, @i out
 IF @i = 1 BEGIN     --FILE EXISTS
    set @strSQL = ''BACKUP DATABASE database  TO  DISK = N'''''' + @File + ''''''  WITH  DIFFERENTIAL''
 END
 ELSE BEGIN --FILE NOT EXISTS
    set @strSQL = ''BACKUP DATABASE database  TO  DISK = N'''''' + @File + ''''''  WITH  INIT''
 END

EXEC sp_executesql @strSQL

',
        @database_name=N'database',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Schedule-1',
        @enabled=1,
        @freq_type=8,
        @freq_interval=62,
        @freq_subday_type=8,
        @freq_subday_interval=2,
        @freq_relative_interval=0,
        @freq_recurrence_factor=1,
        @active_start_date=20100823,
        @active_end_date=99991231,
        @active_start_time=90000,
        @active_end_time=190000,
        @schedule_uid=N'50451ca4-7f63-45ce'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Log:

This part of code clearly states that you are appending the diff backup

Set @dt = cast(year(getdate()) as varchar(4)) + @m SET @File = ''D:\SQLBackups\SQLBackups_database\database'' + @dt + ''.BAK'' EXEC master..xp_fileexist @File, @i out IF @i = 1 BEGIN --FILE EXISTS set @strSQL = ''BACKUP DATABASE database TO DISK = N'''''' + @File + '''''' WITH DIFFERENTIAL'' END ELSE BEGIN --FILE NOT EXISTS set @strSQL = ''BACKUP DATABASE database TO DISK = N'''''' + @File + '''''' WITH INIT'' END

Instead of carving out your own solution which clearly is not smart e.g. exclude offline db, take log backups etc is not good .. use Ola's backup solution.

Also, make sure you test your restore plan by actually restoring the dbs and trying a point-in-time recovery where possible.


DB comparer field by field for all tables I have a liveDB and I have a development DB. Now when I make a change to development DB, I want to find out the changes later when I am deploying my application on server. For eg. I may have added fields and tables and so on. I want to be able to detect those changes.

Could you please let me know some famous tools that are free and does this job for me so I don't have to remember or write the changes in notepad and later on apply them.

There are many free tools - native and opensource available for comparing schema between 2 databases:

Open DBDiff ==> This is on Codeplex and works great.
SQL Admin Studio ==> This is now a free tool.
Hidden Gem from SQL 2005 and up :  tablediff.exe (you can find this in the COM directory of your SQL Server install folder) Compare schemas: Regular or Strict
Powershell - You have to write your own code or build up on existing ones
Since you are using SQL Server 2012, SSDT is also an option.

From my experience, its worth to invest in Redgate's Schema and data compare tools as they have commandline options and they integrate well with Powershell as well.


How to remove the WITH ENCRYPTION from the code of the procedure - via T-SQL I have many procedures that I use the option WITH ENCRYPTION when I create them.

You can see this on the picture below, as an example:

the problem with this is that when I want to save my previous version of my stored procedure, I cannot get the expected result, as you can see on the picture below.

the way I am backing up the code of the stored procedures is on the link below:
How to backup the current code of a stored procedure and its permissions as well?

select 'Proc' = SCHEMA_NAME(p.schema_id)+'.'+p.name
    , 'Type' = per.state_desc, 'Permission' = per.permission_name
    , 'Login' = pri.name, 'Type' = pri.type_desc
    , *
From sys.procedures as p
left join sys.database_permissions as per on p.object_id = per.major_id
left join sys.database_principals as pri on per.grantee_principal_id = pri.principal_id
where ...

is there a way to remove the WITH ENCRYPTION from the code of a stored procedure, before I save it?

Provided I have ownership of the procedure.

There is a nearly duplicated question on the link below
How to view an encrypted view or stored procedure

however, on this question, I own the database. I am sysdba. I can use any resources, but I would like a solution via T-SQL.

As noted in Sebastian Meine's answer to the question How to view an encrypted view or stored procedure, there is a T-SQL solution to perform the decryption. The code to do so can be found on Sebastian's blog.

The solution requires connecting with the DAC, so you have very few options to do so from a T-SQL stored procedure. One possible way is using a linked server:

DECLARE @srv nvarchar(4000);
SET @srv = 'ADMIN:' + @@SERVERNAME; -- gather this server name

-- Create the linked server
EXEC master.dbo.sp_addlinkedserver
    @server     = N'DAC_LOOPBACK',
    @srvproduct = N'SQLServ', -- it’s not a typo: it can’t be “SQLServer”
    @provider   = N'SQLNCLI', -- change to SQLOLEDB for SQLServer 2000
    @datasrc    = @srv;

-- Set the authentication to "current security context"
EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname  = N'DAC_LOOPBACK',
    @useself     = N'True',
    @locallogin  = NULL,
    @rmtuser     = NULL,
    @rmtpassword = NULL;

EXEC master.dbo.sp_serveroption
    @server=N'DAC_LOOPBACK',
    @optname=N'rpc',
    @optvalue=N'true'
EXEC master.dbo.sp_serveroption
    @server=N'DAC_LOOPBACK',
    @optname=N'rpc out',
    @optvalue=N'true'
GO

Once you have the loopback linked server using the DAC, you can execute Sebastian's stored procedure to gather the encrypted text. Unfortunately, the procedure returns an XML column (which cannot be returned over a linked server), so you will have to change the last SELECT to return nvarchar(max):

SET @cmd = N'SELECT CAST(@plain AS NVARCHAR(MAX)) AS [object_definition for ' +
    REPLACE(@object_name, ']', ']]') + ']';

Now you can call the procedure using the linked server:

EXEC DAC_LOOPBACK.SomeDatabase.dbo.ObjectEncryptionCracker
   'SomeEncryptedProcedure'

Do I recommend this method? Hell, NO!
The proper way to keep track of changes in your stored procedures is source control, as you already have been told in your original question. Don't rely on this code for backing up your code (it's unsupported and uses several hacks). Do yourself a favor and put your database objects under source control.


Unattended/silent install of Oracle XE Express fails I am experimenting with silent installs of Oracle 10g XE on a fresh XP VM.  I have an iss file, copied from the standard one from oracle, called "foo.iss"  Here is my command:

>OracleXEUniv.exe /s /f1”C:\xe\foo.iss” /f2”C:\xe\foo.log”

I just followed what I found on the Oracle support site.  This command causes a temp folder to be created, which contains an installer file: "Oracle Database 10g Express Edition.msi"

So it looks good.  But the process then just silently fails.  No log is created in the install folder (C:\x) except for setup.log which only has:

[ResponseResult]
ResultCode=-3

In C:\windows is the larger log file, OracleDatabaseXEServerInstall.log

Is has alot in it, and ends with:

1: Standard project type, let scripting engine clean up setup files...skipping action
Action ended 17:43:27: ISSetupFilesCleanup. Return value 1.
MSI (c) (34:BC) [17:43:27:420]: PROPERTY CHANGE: Adding ISSETUP_UISEQUENCE_PROCESSED property. Its value is '1'.
MSI (c) (34:BC) [17:43:27:420]: Note: 1: 2262 2: Upgrade 3: -2147287038
1: Not using skins for this installation.  Could not find skin file .
MSI (c) (34:BC) [17:43:27:650]: Note: 1: 2262 2: DuplicateFile 3: -2147287038
MSI (c) (34:BC) [17:43:27:700]: Note: 1: 2262 2: ISRequiredFeature 3: -2147287038
MSI (c) (34:BC) [17:43:27:920]: Note: 1: 2205 2: C:\DOCUME~1\dlwiii\LOCALS~1\Temp\_is33\Oracle Database 10g Express Edition.msi 3: ISAlias
MSI (c) (34:BC) [17:43:27:920]: Note: 1: 2228 2: C:\DOCUME~1\dlwiii\LOCALS~1\Temp\_is33\Oracle Database 10g Express Edition.msi 3: ISAlias 4: SELECT * FROM ISAlias
1: Ready to launch program block.
1: User aborts the installation, ready to launch __OnAbort.
MSI (c) (34:BC) [17:43:30:434]: Destroying RemoteAPI object.
MSI (c) (34:B8) [17:43:30:454]: Custom Action Manager thread ending.
=== Verbose logging stopped: 8/27/2011  17:43:30 ===

Any idea what I am doing wrong?  The command line looks suspect, as when I run the install program with just /? I get the following, which shows no /f1 nor /f2 options at all:

I am simply playing with this, but would really like to solve this, as unattended installs look like a great option to ensure consistent database deployments.

Cheers,
Daniel

Never mind, I had mistyped this line:

OracleXEUniv.exe /s /f1"c:\xe\foo.iss"  /f2"c:\xe\foo.log"

Omitted the second set of quotes.  Looks like it worked just fine.


How to get online the database which has missing/corrupted FILESTREAM files? I've corrupted the files related to FILESTREAM in a database.

The .mdf and .ldf files are still intact, but when I try to get the database online, it complains, saying that the files related to FILESTREAM are incorrect.

I don't care about the data stored in FILESTREAM, but I do care about the other data. Can I get it back from .mdf and .ldf files? How?

When I execute:

sp_attach_db @dbname = 'Demo',
             @filename1 = N'<File path>.mdf',
             @filename2 = N'<File path>.ldf'

the response is:

Msg 5120, Level 16, State 105, Line 1
Unable to open the physical file "<Location>". Operating system error 2: "2(The system cannot find the file specified.)".
Msg 5105, Level 16, State 14, Line 1
A file activation error occurred. The physical file name '<Location>' may be incorrect. Diagnose and correct additional errors, and retry the operation.
Msg 1813, Level 16, State 2, Line 1

I finally found how to do it. I've found some very old backups. The data changed meanwhile, and it was important to have an up-to-date version of this data, which meant that it wasn't possible to just restore the old backup. Instead, I did the following, thanks to Michael Eklöf on ServerFault:

Copy the current .mdf and .ldf files.
List the filegroups of the old backup:

use [master]
go

restore filelistonly
from disk = 'E:\Database backups\Hello_backup_2012_03_10_202359_9203520.bak'
go

Restore the database from the old backup, with the names of filegroups from the previous result:

restore database [Hello]
from disk = 'E:\Database backups\Hello_backup_2012_03_10_202359_9203520.bak'
with move 'Hello' to 'D:\Database\Hello.mdf',
move 'Hello_log' to 'D:\Database\Hello_log.ldf',
move 'Hello_files' to 'D:\Database\Hello_files'
go

Set to offline the filegroup (in my case, the FILESTREAM) which is missing in the new version of the database:

alter database [Hello]
modify file (name = 'Hello_files', offline)
go

Take the database offline.
Replace the .mdf and .ldf files created by the server at step 3 by the files copied at step 1.
Take the database online.

There are still consequences of the missing filegroup. Some operations require the database to be complete; on of those critical operations is the backup. In other words, full backup is impossible to do with a database restored through the six steps above.

In order to get the backup ability back, I've copied the structure of the database (through Tasks → Generate Scripts...) excluding the tables which relied on filestream and then copied the data itself.


Manage parent/child table I need to realize a software to manage some projects. Every project starts after a offer approvation.
A offer could have many revision, but only the last one offer is the parent of the project.

Example:

price_quotation_a => project

OR

price_quotation_a
price_quotation_b
price_quotation_c => project

My idea is to realize only one table, with a flag (type) to distinguish order from project. A second column (parent) to specify for every order wich offer is the parent.
The problem is that offer and project have the same data.

+----+------+--------+------+----------+--------------+--------------+
| id | type | parent | name | revision | project_name | project_desc |
+----+------+--------+------+----------+--------------+--------------+
|  1 |    0 |      0 | O001 | A        | Project01    | Project_desc |
|  2 |    0 |      0 | O001 | B        | Project01    | Project_desc |
|  3 |    1 |      2 | P001 | NULL     | Project01    | Project_desc |
|  4 |    0 |      0 | O002 | A        | Project02    | Project_desc |
|  5 |    1 |      4 | P002 | NULL     | Project02    | Project_desc |
+----+------+--------+------+----------+--------------+--------------+

Operatos must insert worked hours both for order or offer so i need a list with both entity. Something like:

+----+-------------+----------+----------------+-------+
| id |    date     | operator | order/offer ID | hours |
+----+-------------+----------+----------------+-------+
|  1 | 01-010-2002 |       12 |              1 |     5 |
|  2 | 01-010-2002 |       12 |              2 |     6 |
|  3 | 01-010-2002 |        5 |              1 |     4 |
+----+-------------+----------+----------------+-------+

Shout if I haven't bee clear. It's the right way?

-- Employee (aka operator) EMP exists.
--
employee {EMP}
      PK {EMP}

-- Customer CST exists.
--
customer {CST}
      PK {CST}

-- On date RFQ_DTE, request for quotation
-- number RFQ_NO was received from customer CST.
--
rfq_in {CST, RFQ_NO, RFQ_DTE}
    PK {CST, RFQ_NO}

FK {CST} REFERENCES customer {CST}

-- Project PRO was started on date PRO_DTE
-- in response to request for quotation
-- number RFQ_NO from customer CST.
--
project {PRO, CST, RFQ_NO, PRO_DTE}
     PK {PRO}
     AK {CST, RFQ_NO}

FK {CST, RFQ_NO} REFERENCES rfq_in {CST, RFQ_NO}

-- Offer revision REV for project PRO
-- was created on date OFR_DTE.
--
offer {PRO, REV, OFR_DTE}
   PK {PRO, REV}

FK1 {PRO} REFERENCES project {PRO}

-- On date OW_DTE employee EMP spent OW_HRS hours
-- working on offer revision REV for project PRO.
--
offer_work {PRO, REV, EMP, OW_DTE, OW_HRS}
        PK {PRO, REV, EMP, OW_DTE}

FK1 {PRO, REV} REFERENCES offer {PRO, REV}

FK2 {EMP} REFERENCES employee {EMP}

-- Offer revision REV for project PRO
-- was accepted and approved on date APR_DTE.
--
accepted {PRO, REV, APR_DTE}
      PK {PRO}

FK {PRO, REV} REFERENCES offer {PRO, REV}

-- On date PW_DTE employee EMP spent PW_HRS hours
-- working on project PRO.
--
project_work {PRO, EMP, PW_DTE, PW_HRS}
          PK {PRO, EMP, PW_DTE}

FK1 {PRO} REFERENCES accepted {PRO}
FK2 {EMP} REFERENCES employee {EMP}

Create a view to report work hours.

CREATE VIEW work_hours
AS
SELECT OW_DTE AS DTE
     , EMP
     , OW_HRS AS HRS
     , PRO
     , 'O'    AS WRK_TYPE
     , ('offer revision ' || REV) AS DETAIL
FROM offer_work
UNION
SELECT PW_DTE AS DTE
     , EMP
     , PW_HRS AS HRS
     , PRO
     , 'P'    AS WRK_TYPE
     , 'some project work' AS DETAIL
FROM project_work
;

Note:

All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key (Unique)
FK = Foreign Key