SQL Server 64-bit - Import from Excel I have used SQL Servers Import Data wizard to import data from an Excel spreadsheet.

The Import Data Wizard worked fine to import the data.

It also worked fine to save the SSIS package.

I can then log in to Integration Services on the server and run the package from within Integration Services.

However, when I try to run the package from SQL Server Agent, I get the error message:

  The Excel Connection Manager is not supported in the 64-bit version
  of SSIS, as no OLE DB provider is available.

I've seen suggestions to set the "Use 32 bit runtime" option, but this had no effect.  I've seen suggestions to set something similar in BIDS, but I didn't use BIDS to generate this package.  I've seen options to use the 32-bit version of DTExec but I don't think the 32-bit version is installed on the server.

SQL Agent Job definition:

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'import data',
        @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'SSIS',
        @command=N'/SQL "\"\ExcelImportPackage\"" /SERVER MyDBServer /X86  /CHECKPOINTING OFF /REPORTING E',
        @database_name=N'master',
        @flags=0

Given that the package works both from the Wizard and from Integration Services I believe the server has everything needed to run the package from SQL Server Agent, but I cannot see how to do it.

Just as a work around (since you said running the package manually works): you should be able to run the dtsx package using T-SQL via a job or make a stored proc to be called by a job, as this article explains.

For a package in the server:

DECLARE @SQLQuery AS VARCHAR(2000)

DECLARE @ServerName VARCHAR(200) = 'ARSHAD-LAPPY'

SET @SQLQuery = 'DTExec /SQL ^"\DataTransfer^" '
SET @SQLQuery = @SQLQuery + ' /SET \Package.Variables[ServerName].Value;^"'+ @ServerName + '^"'

EXEC master..xp_cmdshell @SQLQuery
GO

For a package in the file system:

DECLARE @SQLQuery AS VARCHAR(2000)

DECLARE @ServerName VARCHAR(200) = 'ARSHAD-LAPPY'

SET @SQLQuery = 'DTExec /FILE ^"E:\DataTransfer.dtsx^" '
SET @SQLQuery = @SQLQuery + ' /SET \Package.Variables[ServerName].Value;^"'+ @ServerName + '^"'

EXEC master..xp_cmdshell @SQLQuery
GO


troubleshooting login failure when AD group seems to be fine I am using the procedure xp_logininfo to find out what groups a user belongs to.

I have created an AD group called SQLDBA, and added myself to it,
then created a login and granted sysadmin to the group.

EXEC xp_logininfo 'mydomain\myname','all';
    GO

But still when I try to login to that SQL Server instance, using windows authentication, it says I don't have a login?

Where to look at in order to troubleshoot this?

the error message:

18456 (login failed) needs the STATE value to know why it failed. Look in the error log. – Sean Gallardy

Are you sure that if you added yourself tot he group that this sql server is connecting to a domain controller that has the updated info? If you have multiple domain servers it may take some time to have that info replicate a across to all servers. – Jeff A

This is the most comprehensive guide I have seen so far:

Troubleshooting Error 18456 by Aaron Bertrand

You need the state first to use that document. – SqlWorldWide

After you created the AD group and added yourself to it, did you logoff/login? AD Group permissions are only enumerated at login (e.g. the Kerberos Access Token). – John Eisbrener

Community Wiki answer compiled from question comments. Community wiki posts have been donated to the community in hopes that others will edit them to keep them up to date, to add useful information, and generally improve their quality.


unable to add limit to the subquery its my first question, please bear me.
I have following tables

Item

id
name
description

itemPurchaseDetail

id
item_id(FK)
branch_id(FK)
purchaseRate
date

Stock

id
item_id(FK)
branch_id(FK)
quantity

I have to get all the items in specific branch(let suppose branch_id=1), its quantity in stock, its name, its description and its last purchase rates. This bold part is smashing my head.

LINK to fiddle: fiddle

select   i.id,
         i.name,
         i.description,
         s.quantity stock,
         (select purchaseRate
          from itemPurchaseDetail
          where item_id = i.id
          order by PurchaseDate desc
          limit 1) lastPurchaseRate
from     item i
join     (select distinct item_id, branch_id
          from itemPurchaseDetail) pd
on       pd.item_id = i.id
join     stock s
on       s.item_id = i.id
and      s.branch_id = pd.branch_id;

id | name  | description | stock | lastPurchaseRate
-: | :---- | :---------- | ----: | ---------------:
 1 | item1 | desc 1      |   100 |              222
 2 | item2 | desc 2      |   200 |              444
 3 | item3 | desc 3      |   300 |              666

dbfiddle here


Does 'update global indexes' not work if the index is already unusable? I have a partitioned table in Oracle 11g with a PK (global index). If I truncate one of the partitions with

ALTER TABLE tbl1 TRUNCATE PARTITION p1;

the global index has the status UNUSABLE.

If I use

ALTER TABLE tbl1 TRUNCATE PARTITION p1 UPDATE GLOBAL INDEXES;

the global index is correctly rebuild and has the status VALID.

But:
It seems, that the index is not rebuild if it already is in an unsuable state. Is that correct?

To verify:

Create a table with 3 range partitions and a global index
Insert data into all 3 partitions
truncate partition WITHOUT updating global indexes
index is in state unusable
truncate a second partition WITH updating global indexes
index is still unusable

From my understanding, after the second truncate partition, the index should be usable again.

The update [global] indexes option does not rebuild indexes after whatever operation you ran. It maintains indexes during the operation (see Updating Indexes Automatically), so that they remain available throughout.

After your first truncate, the index needs to be rebuilt - it can't be updated, it's already "broken" and unavailable. Oracle won't rebuild it automatically for the next operation you do on the table, even if you include the update clause.


"Measure Type Dimensions" in "Accumulating Snapshot" fact table I have an accumulating snapshot fact table that tracks the entry and exit of containers in a terminal.
The containers can enter and exit in 3 different ways, so I thought to create a specific dimension table that lists these 3 possible ways (train, vessel or truck).
Then I read this article which basically says that this technique is wrong, but I can't understand why.
First article:

Sometimes when a fact table has a long list of facts that is sparsely populated in any individual row, it is tempting to create a measure
type dimension that collapses the fact table row down to a single
generic fact identiﬁed by the measure type dimension. We generally do
not recommend this approach. Although it removes all the empty fact
columns, it multiplies the size of the fact table by the average
number of occupied columns in each row, and it makes intra-column
computations much more difficult. This technique is acceptable when
the number of potential facts is extreme (in the hundreds), but less
than a handful would be applicable to any given fact table row.

I understand that if a "Measure Type Dimension" is implemented for a transaction fact table it can create problems like this other article says, but I can't see any downside if used for an accumulating snapshot fact.
Second article: (some downsides of implementing a "Measure Type Dimension")

[...] If we go with a "Measure Type Dimension" we will lose this analytic ability. If one measure is not compatible with the other
measures, we can’t add them up.
[...] The more number of passes our SQL need to run to produce a report, the slower the report.
[...] On the BI tool, if you don’t put the measure type filter, you are risking the user getting “rubbish information”. From usability
point of view, this design is a rubbish.

Response to Mark Storey-Smith's answer
Very nice approach, i would have never thought about that.
Another thing: every entry and exit of a vehicle that brings container into the terminal has a unique ID which gives me other informations like: expected arrival of the vehicle, actual arrive, if it's a vessel the dock, if it's a truck the tollbooth and many other informations...
These are 3 different fact tables and they must be linked somehow to the container fact table.
I thought that the ID of the voyage is a degenerate dimension, so it would directly go into the container fact table. So, my doubt is: should i add 6 different fields in the container fact table (vessel_voyage_in_key, vessel_voyage_out_key, train_voyage_in_key, train_voyage_out_key, truck_voyage_in_key, truck_voyage_out_key) or just 2 other fields (voyage_in, voyage_out) that dynamically links to the various voyage tables?
I hope my doubt is clear, thank you.

I believe the guidance is referring to a wide fact table where the majority of measure values are null:

CREATE TABLE dbo.SparseFact
(
    Dim1Key     INT NOT NULL
    , Dim2Key   INT NOT NULL
    , Dim3Key   INT NOT NULL
    , Dim4Key   INT NOT NULL
    , Dim5Key   INT NOT NULL
    , Value1    INT NULL
    , Value2    INT NULL
    , Value3    INT NULL
    , Value4    INT NULL
    , Value5    INT NULL
    , Value6    INT NULL
    , Value7    INT NULL
    , Value8    INT NULL
    ..
    , Value101  INT NULL
    , Value102  INT NULL
    , Value103  INT NULL
);

The suggestion is that some people will see all the nulls and decide to do this instead:

CREATE TABLE dbo.DontDoThisFact
(
    Dim1Key             INT NOT NULL
    , Dim2Key           INT NOT NULL
    , Dim3Key           INT NOT NULL
    , Dim4Key           INT NOT NULL
    , Dim5Key           INT NOT NULL
    , MeasureTypeKey    INT NOT NULL
    , Value             INT NOT NULL
);

Not good.

In your scenario I think I'd be looking at something like this, which is very different to the scenario described in the articles you referenced.

CREATE TABLE dbo.InventoryFact
(
    ContainerKey        INT NOT NULL
    , TransportTypeKey  TINYINT NOT NULL
    , EntryDateTime     DATETIME NULL
    , ExitDateTime      DATETIME NULL
);

CREATE TABLE dbo.TransportType
(
    TransportTypeKey    TINYINT IDENTITY(1,1) NOT NULL
    , EntryTransport    CHAR(10) NOT NULL
    , ExitTransport     CHAR(10) NOT NULL
);

INSERT
    dbo.TransportType
SELECT
    EntryTransport
    , ExitTransport
FROM
    (
    SELECT EntryTransport = 'Train'
    UNION
    SELECT EntryTransport = 'Truck'
    UNION
    SELECT EntryTransport = 'Vessel'
    UNION
    SELECT EntryTransport = 'N/A'
    UNION
    SELECT EntryTransport = 'Unknown'
    ) en
CROSS JOIN
    (
    SELECT ExitTransport = 'Train'
    UNION
    SELECT ExitTransport = 'Truck'
    UNION
    SELECT ExitTransport = 'Vessel'
    UNION
    SELECT ExitTransport = 'N/A'
    UNION
    SELECT ExitTransport = 'Unknown'
    ) ex;

To the additional questions...

I would add ExpectedEntryDate, ExpectedExitDate to the Container/InventoryFact. Less certain, without visibility of all the data elements, I would probably put EntryVoyageId and ExitVoyageId in a separate junk dimension together as one row along with any other degenerate data items (identifiers for the truck, train etc).

I would add 3 new dimensions for VesselVoyage, TruckVoyage and TrainVoyage and 6 Voyage keys (inbound/outbound) to this one fact (it's 6 new keys, not 6 additional rows). You then have the option of placing Dock and Tollbooth in the appropriate Voyage dimension. If you keep the generic data in these dimensions (VesselFlag, TruckCapacity) and the specific in a junk dimension (VesselName, VesselMMSI) they won't explode in size.


SSMS closes after installing visual studio Good Morning.
I have a problem, first I made the installation of SQL Server Express, followed by SSMS and finally Visual Studio Community.

Before installing visual studio, SMMS worked perfectly, when you install visual studio, SSMS is sorry, the logo appears and opens the window to connect to the server but it immediately closes. Also if I try to uninstall SSMS the same thing happens with the installer, it closes when it appears.

SSMS V. 17.9.1

Thank you

This sounds like a problem with your Visual C++ Redistributable not being the version required by SSMS. Check which version of the VC++ Redistributable is required by SSMS and if you have a later version already installed, then uninstall it. If you have an older version, then download the version required by SSMS and install it manually. Normally I find this problem when developers install Visual Studio before installing SSMS (as Visual Studio comes packaged with a newer version than SSMS does). You can upgrade the VC++ Redistributable after installing SSMS if you need it.


How can I investigate why Sql Server is chosing the "wrong" Index? I have a Transaction table with about 200 million records, one primary key clustered on Id and 2 indexes:

IX_SiloId_ChangedTime_IncludeTime
IX_SiloId_Time_IncludeContent

I run these 2 statements before I proceed with the actual query to update statistics
Update STATISTICS dbo.[Transaction] IX_SiloId_ChangedTime_IncludeTime WITH FULLSCAN
Update STATISTICS dbo.[Transaction] IX_SiloId_Time_IncludeContent WITH FULLSCAN

This is my query:
DECLARE @Query SiloTimeQueryTableType -- (SiloId, Time) with primary key clustered on SiloId
INSERT INTO @Query VALUES
(1, '2020-12-31'), -- 1000 total values, though it's still the same problem with just one

SELECT  t.*
FROM    [Transaction] t
INNER JOIN @Query q
    ON t.SiloId = q.SiloId
WHERE
    t.Time >= q.Time

Now what happens is for whatever reason Sql Server choses IX_SiloId_ChangedTime_IncludeTime. It then takes forever. If I use WITH (INDEX(IX_SiloId_Time_IncludeContent)) I get the result right away.
The correct index is quite obvious here, but yet SQL Server choses the one that is not even indexed on Time.
I cannot understand this behaviour, but from what I read it is best to avoid hints for Indexes, though I made this Index with this query in mind.
So the question is: what can I do to try to figure out why SQL Server prefers the "wrong" index even though a much better one exists and I just run full statistics update?
Query plan for the forced index (here from the temp table instead of TVP to check if this changes anything as the answer suggested, the result seems to be the same):

Query plan without forced index:

https://www.brentozar.com/pastetheplan/?id=rJOt3G00P
https://www.brentozar.com/pastetheplan/?id=ByFshGAAP (this one is live, as it takes too long)

It's could be the lack of column-level statistics on the table variable.  Try the same approach, but instead using a real temp table instead, like this:
CREATE TABLE #Query
(
    Id int NOT NULL,
    Time datetime NOT NULL
)

INSERT INTO #Query VALUES
(1, '2020-12-31');

ALTER TABLE #Query
ADD CONSTRAINT PK_Query PRIMARY KEY (Id);

SELECT  t.*
FROM    [Transaction] t
INNER JOIN #Query q
    ON t.SiloId = q.SiloId
WHERE
    t.Time >= q.Time

However, looking at the actual execution plan provided, the problem is definitely due to a bad estimate.  Check this out:

For some reason, the optimizer thinks there will be 25,000,000 rows coming out of that join, when in reality there are only 4,155 rows.  The optimizer is not going to choose to do 25,000,000 key lookups (since the index doesn't include all the columns), so you end up with a scan of the clustered index.  This is referred to as "the tipping point" on various blogs and such.
The "simplest" option would be to make the index covering (include all the columns in the table), or only select columns that are in the index.  This avoids the key lookup, and you should get this plan naturally.  However, that might not be practical (not sure what the data type of the other columns in your table are, etc).
The better option would be to somehow fix the estimate.  You could try adding an index on Time to the temp table, in case that gives you better stats (after loading the table):
CREATE NONCLUSTERED INDEX IX_Time
ON #Query (Time);

You could also try rewriting the query in different forms to see if you get different results / better estimates, like this:
SELECT  t.*
FROM    [Transaction] t
WHERE EXISTS
(
    SELECT null
    FROM #Query q
    WHERE
        q.SiloId = t.SiloId
        AND t.Time >= q.Time
)

Or see how the legacy cardinality estimator does with this query by adding this hint to the end of it:
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'))

So the question is: what can I do to try to figure out why SQL Server prefers the "wrong" index even though a much better one exists and I just run full statistics update?

In general, it would be useful to use index hints to "force" the index you want, and then compare the difference in costs or estimates between that plan and the natural one.  That can provide clues about why the desired index isn't getting chosen.

FORCE_LEGACY_CARDINALITY_ESTIMATION though chose the correct index right away! Don't really know what that means though... There is some bug in a new one?

I wouldn't say it's a bug - they just have very different approaches to producing estimates. The legacy CE still works better in certain situations compared to the new CE.
If you want a deep dive into one specific difference, which also provides an approach for looking at the stats and how estimates are produced, check out Paul White's blog post: SQL Server Join Estimation using Histogram Coarse Alignment


What is the quickest way to reset a column to a specific value? I have 30 million rows inside a sql server database where I've added a simple tagging column. One character allowing null. I did add an index for the column.

I use it to keep track of which rows have been processed for a data extraction. During the initial development I will be restarting the extraction process from scratch (several times) and need to reset the value to '1'.

Being a neophyte database developer (who am I kidding...), my first attempt used a simple update script to reset the value of '2' (the I've processed this row) to a '1'. The first time I ran the script it executed in about 5 minutes. The last time I executed - it took 45 minutes. There has to be a better way.

Is it possible for me to speed this up by completely removing the column and recreating it with a default value of '1'? Will the speed stay consistent?

-- UPDATE ---

The next time I need to do this I'm going to try the following:

ALTER TABLE acmewidgets DROP COLUMN LegacyExtract;
ALTER TABLE acmewidgets
        ADD LegacyExtract CHAR NOT NULL DEFAULT (1);

Used the following and it executes in 41 seconds !

USE AcmeDatabase

DROP INDEX IF EXISTS IX_LegacyExtract ON AcmeWidgetTable

ALTER TABLE AcmeWidgetTable DROP CONSTRAINT IF EXISTS DF_Legacy_Extract

ALTER TABLE AcmeWidgetTable DROP COLUMN IF EXISTS LegacyExtract

GO

ALTER TABLE AcmeWidgetTable CHAR NOT NULL CONSTRAINT DF_Legacy_Extract DEFAULT (0)

GO

CREATE NONCLUSTERED INDEX [IX_LegacyExtract] ON [dbo].[AcmeWidgetTable]
(
    [LegacyExtract] ASC
)


Replicate tables to different database name Our QA environment has all it's database names suffixed with "test".  For example dbname1 in production would have a counterpart dbname1test in QA.  (This is largely to help protect against prod/qa configs getting mixed up).

There are a handful of tables I would like to replicate the actual production tables into QA.  I'm not sure how I would go about telling it to say "replicate from dbname1 there to dbname1test there"

Is this even possible?

Yes, there is. Put this in your my.cnf file:

replicate-rewrite-db="dbname1->dbname1test"

See Replication Slave Options and Variables: replicate-rewrite-db


Increasing timeout period for schema compare I need to increase the timeout period for schema comparison.

I'm using Visual Studio 2012 to compare two SQL Server 2008 R2 databases on Windows Server 2008.

I believe this is something I need to set in the Windows registry but I can't find where.

You have to increase the query timeout allowed on the connection. Unfortunately there is no user-interface-exposed way to do this. You have to search the Windows Registry for the key "QueryTimeoutSeconds" and increase the value. I increased mine from 60 to 360 and that made my schema compare timeouts disappear in Visual Studio 2012 and Visual Studio 2013.


How to use "regular expression" to separate specific strings in Oracle I have a string '(1:30,2:4,52:0,8:1)', and I need to use a regular expression to have this output:

field1   field2  level
  1        30      1
  2         4      2
  52        0      3
  8         1      4

The query I've wrote so far is:

select distinct trim(regexp_substr('1:30,2:4,52:0,8:1','[^:,]+',1,level)) repfield,level lvl
from dual
connect by regexp_substr('1:30,2:4,52:0,8:1', '[^:,]+', 1, level) is not null
 order by lvl

Simple method:

col field1 format a6
col field2 format a6
col lvl format a3
variable B1 varchar2(32);
exec :B1 := '1:30,2:4,52:0,8:1';
select
  regexp_substr(regexp_substr(:B1, '[^,]+', 1, level), '[^:]+', 1) field1,
  regexp_substr(regexp_substr(:B1, '[^,]+', 1, level), '[^:]+', 2) field2,
  level lvl from dual
connect by regexp_substr(:B1, '[^,]+', 1, level) is not null;

FIELD1 FIELD2 LVL
------ ------ ---
1      30       1
2      4        2
52     2        3
8      1        4

Or with fewer regexp_substr calls:

col field1 format a6
col field2 format a6
col lvl format a3
variable B1 varchar2(32);
exec :B1 := '1:30,2:4,52:0,8:1';
select
  regexp_substr(:B1, '[^:,]+', 1, level*2 - 1 ) field1,
  regexp_substr(:B1, '[^:,]+', 1, level*2) field2,
  level lvl from dual
connect by regexp_substr(:B1, '[^,]+', 1, level) is not null;

FIELD1 FIELD2 LVL
------ ------ ---
1      30       1
2      4        2
52     0        3
8      1        4


Storing multiple settings for multiple users in a SQL Server table I'm using SQL Server 2012 to store the account settings for multiple accounts (each user account will have a set of settings). These settings could have any number of fields (has not been completely set and could grow) but each user account will have the same set of settings (different values of course).

Is it better (faster with a smaller footprint and more efficient) to:

give each setting its own column and each row to a user? keep in mind that there could be hundreds of settings variables. (i.e. userid, setting1, setting2, setting3...etc)
Relate each individual setting to a user and making it a name value pair table
(i.e.  userid, settingsName, settingValue)

Already maintainability will ask for a structure like shown below.
An additional set of templates would support fast capture of new data (Structure like UserSettings with a templateid instead of userid)

CREATE TABLE [dbo].[Settings](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [ValueType] [int] NOT NULL,
 CONSTRAINT [PK_Settings] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[UserSettings](
    [userid] [int] NOT NULL,
    [settingsid] [int] NOT NULL,
    [value] [sql_variant] NULL,
 CONSTRAINT [PK_UserSettings] PRIMARY KEY CLUSTERED
(
    [userid] ASC,
    [settingsid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


Multi-Statement Table-Valued Function with a Cursor: How to Check If The Cursor is Still Open On Error? I have a stored procedure preparing a dataset using an employee listing as base query and performing a CROSS APPLY to a MSTVF to build a small resultset per employee. In the MSTVF, a cursor is used to build the interim result set (performing complex math using running totals and case logic). MSTVFs don't allow TRY...CATCH blocks so I can't trap an error directly and use CURSOR_STATUS() to close and deallocate the cursor, or can I? What can I do in the declaration of the cursor or in the CATCH block of the stored procedure to check for an open cursor in the MSTVF?
Additional Context after Earlier Comments (Functional Logic)
The MSTVF code in question builds a set of instructions to submit to a timekeeping app's API engine to open a timecard, adjust one or more paycode balances with offsetting transactions and close a timecard. Basic workflow below...

MSFTVF takes an employee ID and pay period date ranges as params and returns a 10-column table variable

A variable is set with a sum of specific paycode balances. This sum is to be washed via debits and credits via the API. This is the pending balance.

A cursor of qualifying transactions to wash is declared in priority order.

While there is a pending balance...
3a) A debit matching credit transaction is inserted into a table variable (for the API call records)
3b) The amount of the debit/credit is the smaller of the current transaction or pending balance (handled via IF statement)
3c) If the transaction is more than one pay period in the past, another API call record is inserted that would flag the transaction for a historical correction.

When no balance remains, the while loop exits out
4a) If there are no xactions in the cursor left and still there is a pending balance, a final set of API calls are inserted to wash this balance.

The API table variable is then queried to insert API call records for final transactions at the timecard level and housekeeping calls (unlock, retotal, lock). If no records are present by the end of 4), nothing is generated.

The API table variable is aggegrated on paycode and transaction type and inserted into the MSTVF table variable and returned back to the calling procedure.

The use of a MSTVF was to encapsulate the logic generating all the API calls. To scale to an ILTVF, I figure after calculating the pending balances in 1) in the calling stored procedure, the ILTVF would run a query with a windowing function for SUM() for the running totals. On first blush, maybe the windowing query could be used in a CTE to reproduce steps 3) and 4) with a multi-section UNION statement. I figure the ILTVF result would be stored in a temp table in the calling procedure and steps 5) and 6) run to build in the housekeeping and store the aggregated result for the next ETL step (API execution).

Use a cursor variable (details here), then you don't have to worry about closing and deallocating (which, as David points out, isn't necessary inside a TVF, but it - and/or LOCAL - is required in other contexts). Just change:
 DECLARE c CURSOR ... FOR SELECT ...
 ...
 OPEN c;
 FETCH NEXT FROM c INTO ...

 ...

 CLOSE c;
 DEALLOCATE c;

To:
 DECLARE @c CURSOR;
 SET @c = CURSOR READ_ONLY FORWARD_ONLY FOR SELECT ...
 ...
 OPEN @c;
 FETCH NEXT FROM @c INTO ...

 ...

Work on the logic that you think requires a cursor and conditional/flow logic, so that you can eliminate the MSTVF and re-write it as an inline TVF. While it wasn't true before SQL Server 2012, I can assure you that most cursor logic in SQL Server 2016 can be rewritten using window functions. There are still isolated cases where a cursor is a better choice (not always for performance reasons), but it's doubtful that a TVF is one of them.


Query To Return Data From MAX(ID) I need to return only the 1 row of the MAX(ID), but my query is not returning such.

This is the result set that I get returned

id      bbid    indate      st  val
200817  16543   01/01/16    1   65.4
2017    16543   01/01/16    1   36.52
1995    16543   01/01/16    1   18.51
20932   1234    03/01/16    1   55.3
22324   1234    03/01/16    1   16.52
55      8232    05/01/16    2   100

This is what I WANT returned

id      bbid    indate      st  val
200817  16543   01/01/16    1   65.4
22324   1234    03/01/16    1   16.52
55      8232    05/01/16    2   100

(Only one row per bbid, and if bbid has multiple entries then pull the data from MAX(ID))

Here is sample DDL to help troubleshoot

Create Table #One
(
  id int
  ,bbid int
  ,val varchar(100)
)

Create Table #Two
(
  bbid int
  ,st int
  ,indate datetime
)

Insert Into #One Values
('200817','16543','65.40')
,('2017','16543','36.52')
,('1995','16543','18.51')
,('20932', '1234', '55.30')
,('22324', '1234', '16.52')
,('55', '8232', '100')

Insert Into #Two Values
('16543', '1', '2016-01-01')
,('1234', '1', '2016-03-01')
,('8232','2', '2016-05-01')

And this is the query I wrote that produced the incorrect results...

Select
t1.id
,t3.bbid
,t3.indate
,t3.st
,t1.val
FROM #One t1
JOIN
(
    Select
    id
    ,MAX(ID) As "Maxid"
    FROM #One
    GROUP BY ID
 ) t2
ON t1.id=t2.Maxid
INNER JOIN #Two t3
ON t1.bbid = t3.bbid

How should this query be altered to return my desired result set'?

You can use a common table expression (cte).

;with cte as (
select
     row_number() over(partition by bbid order by #one.id desc) as rn ,id from #one
)
select
    t1.id
    ,t3.bbid
    ,t3.indate
    ,t3.st
    ,t1.val
from
    #One as t1
join #Two as t3
    on t1.bbid = t3.bbid
join cte
    on cte.id = t1.id
    and cte.rn = 1
order by t1.id desc


How to get the timestamp column in only milliseconds from PostgreSQL? I have a column "created" with type timestamp without time zone default now() in a PostgreSQL database.

If I select colums, it has a nice and readable format per default:

SELECT created FROM mytable;

         created
---------------------------
2011-05-17 10:40:28.876944

But I would like to get the timestamp in only milliseconds (as a Long). Something like this:

SELECT myformat(created) FROM mytable;

     created
-----------------
2432432343876944

How can I get the timestamp column in only milliseconds from PostgreSQL?

Response to Jack:

I do get the same difference as you (-3600), but if I use timestamp with time zone I can see that the "error" or difference is because '1970-01-01' gets time zone +01.

create table my_table_2(created timestamp with time zone);
CREATE TABLE
insert into my_table_2 (created) values (now()), ('1970-01-01');
INSERT 0 2
select created, extract(epoch from created) from my_table_2;
            created            |    date_part
-------------------------------+------------------
 2011-05-18 11:03:16.909338+02 | 1305709396.90934
 1970-01-01 00:00:00+01        |            -3600
(2 rows)

Is the difference a bug? I may be because of "Daylight saving times" at the moment?

Also interesting while using to_timestamp() to insert timestamp 0 and 1.

insert into my_table_2 (created) values (to_timestamp(0));
INSERT 0 1

insert into my_table_2 (created) values (to_timestamp(1));
INSERT 0 1
select created, extract(epoch from created) from my_table_2;
            created            |    date_part
-------------------------------+------------------
 2011-05-18 11:03:16.909338+02 | 1305709396.90934
 1970-01-01 00:00:00+01        |            -3600
 1970-01-01 01:00:00+01        |                0
 1970-01-01 01:00:01+01        |                1

Use EXTRACT and the UNIX-Timestamp

SELECT EXTRACT(EPOCH FROM TIMESTAMP '2011-05-17 10:40:28.876944') * 1000;

would give

  1305621628876.94

Multiply it by 1000 to turn it into milliseconds. You can then convert it to whatever you want (decimal would be a good choice). Don't forget to keep the timezone in mind. JackPDouglas has such an example in his answer. Here is an excerpt from his answer (created being the column with your timetamp) that illustrates how to work with timezones:

SELECT EXTRACT(EPOCH FROM created AT TIME ZONE 'UTC') FROM my_table;


Filter and append data to row My query and my data set (copied from sqlfiddle):

CREATE TABLE one (
    id int4 primary key,
    p_id int4,
    k_id int4
);
CREATE TABLE two(
   id int4 primary key,
   p_id int4,
   k_id int4,
   t_id int4,
   pos int4
);

INSERT INTO one(id, p_id, k_id) VALUES
    (1, 1, 1),
    (2, 1, 2),
    (3, 1, 3),
    (4, 1, 4);
INSERT INTO two(id, p_id, k_id, t_id, pos) VALUES
    (1, 1, 1, 1, 1), -- t_id = 1 and pos = 1
    (2, 1, 2, 1, 2),
    (3, 1, 3, 1, 1), -- t_id = 1 and pos = 1
    (4, 1, 4, 1, 3),
    (5, 1, 1, 2, 3), -- shares p_id and k_id with row 1
    (6, 1, 2, 2, 1),
    (7, 1, 3, 2, 5), -- shares p_id and k_id with row 3
    (8, 1, 4, 2, 6);

Table one is a join table, I've added it because in the future I might add more columns into it, and then it will be easier to modify this report.

What I want is to be able to filter my dataset like so:

Get all the rows where pos = 1 for t_id = 1

Expected output:

p_id, k_id, stats
1,     1,    [{p_id: 1, k_id: 1, t_id: 1, pos: 1}, {p_id: 1, k_id: 1, t_id: 2, pos: 3}
1,     3,    [{p_id: 1, k_id: 3, t_id: 1, pos: 1}, {p_id: 1, k_id: 3, t_id: 2, pos: 5}

As you can see I want to append to my results the data for others t_ids where p_id and k_id are the with the found results.

Update:

What will be the best way of removing duplicates?

Adding (9, 1, 1, 1, 20) to my dataset will result in having the same type twice in a row;

One solution to get only uniq types and the smallest pos values is:

WITH uniqt AS (
  SELECT   p_id, k_id, t_id, min(pos) as pos
  FROM     two
  GROUP BY 1, 2, 3

)

Because I am selecting from table one 50 rows (limit 50) and then join a table with SELECT like in @Erwin Brandstetter example, having this CTE will slow down my query?

You could use an inner join to filter for rows that share a (p_id, k_id) value with rows that have a (t_id, pos) of (1,1) (sqlfiddle):

select  base.p_id
,       base.k_id
,       json_agg(base order by base.p_id, base.k_id, base.t_id, base.pos) as stats
from    (
        select  p_id
        ,       k_id
        ,       t_id
        ,       pos
        from    two
        ) base
join    two as filter
on      filter.t_id = 1
        and filter.pos = 1
        and filter.p_id = base.p_id
        and filter.k_id = base.k_id
group by
        base.p_id
,       base.k_id;

Or an equivalent way using an exists subquery (sqlfiddle):

select  base.p_id
,       base.k_id
,       json_agg(base order by base.p_id, base.k_id, base.t_id, base.pos) as stats
from    (
        select  p_id
        ,       k_id
        ,       t_id
        ,       pos
        from    two
        ) base
where   exists
        (
        select  *
        from    two as filter
        where   filter.t_id = 1
                and filter.pos = 1
                and filter.p_id = base.p_id
                and filter.k_id = base.k_id
        )
group by
        base.p_id
,       base.k_id;

Updated to use json_agg from @ErwinBrandstetter's answer.


How can I get a copy of the SQL generated by Management Studio? If I save a table in Management Studio's Table Designer for example, I believe it runs DDL SQL in the background. Is it possible for me to grab this SQL so that I can save it to a file?

Modern versions of SSMS (2008+ at least) allow you to generate a change script once you have made changes to a table using the graphical designer. You go to the menu item Table Designer > Generate Change Script.

That said, I agree with Thomas. In the long term, you should be proficient at T-SQL so that you can write ALTER TABLE scripts without the help of the wizard / graphical designers. They often do things you don't want to do.


MySQL dump restore - how to retain comments So I have that complex MySQL dump as a backup. Unfortunately, that backup is complex and we use evil triggers.

Some of our evil triggers had a comment lines starting with the # character.

When I backup the database, the resulting dump save the # lines. The problem is when I am doing the restore. The line starting with # are replaced by empty lines.

So I am looking for a way to restore the dump WITH the lines starting with the pound.

OMG I know your problem !!!

Here is the problem: The mysql client filters out comments.

-c, --comments      Preserve comments. Send comments to the server. The
                    default is --skip-comments (discard comments), enable
                    with --comments.

Just load the mysqldump file like this:

mysql -u... -p... --comments < dumpfile.sql

Give it a Try !!!


Oracle 12c and Jobs running one hour apart after changing time for hourly use I have an Oracle 12c database in which I have detected that the scheduled jobs are running one hour apart after the change of time on the server, checking in the database I see that the SYSTIMESTAMP differ with the DBTIMEZONE

SQL> select dbtimezone, sessiontimezone from dual;

DBTIMEZONE

SESSIONTIMEZONE

-04: 00
-05: 00

In the operating system I run:
date + "% Z% z"

CST -0500

Is it correct that these values of SYSTIMESTAMP and DBTIMEZONE are different?, when the time change is made is it necessary to modify something in the database?

If you found the timezone of your database to be incorrect and you are sure this causes the issue (I have seen this before), you can change it with.

set_time_zone_clause::=

For example:

SQL> select property_value from database_properties where property_name = 'DBTIMEZONE';

PROPERTY_VALUE
--------------------
+01:00

You can set a named time zone like:

SQL> alter database set time_zone = 'Europe/Budapest';

Database altered.

Or a fixed value:

alter database set time_zone = '-05:00';

I used the former. Then restart the database:

SQL> shu immediate
Database closed.
Database dismounted.
startup
ORACLE instance shut down.
SQL> ORACLE instance started.

Total System Global Area 1073737800 bytes
Fixed Size                  8904776 bytes
Variable Size             285212672 bytes
Database Buffers          771751936 bytes
Redo Buffers                7868416 bytes
Database mounted.
Database opened.
SQL> select property_value from database_properties where property_name = 'DBTIMEZONE';

PROPERTY_VALUE
--------------------
Europe/Budapest

SQL>

  set_time_zone_clause

  This clause has the same semantics in CREATE DATABASE and ALTER
  DATABASE statements. When used in with ALTER DATABASE, this clause
  resets the time zone of the database. To determine the time zone of
  the database, query the built-in function DBTIMEZONE. After setting or
  changing the time zone with this clause, you must restart the database
  for the new time zone to take effect.

Keep in mind this may not always work, because:

  Oracle Database normalizes all new TIMESTAMP WITH LOCAL TIME ZONE data
  to the time zone of the database when the data is stored on
  disk.Oracle Database does not automatically update existing data in
  the database to the new time zone. Therefore, you cannot reset the
  database time zone if there is any TIMESTAMP WITH LOCAL TIME ZONE data
  in the database. You must first delete or export the TIMESTAMP WITH
  LOCAL TIME ZONE data and then reset the database time zone. For this
  reason, Oracle does not encourage you to change the time zone of a
  database that contains data.

  For a full description of this clause, refer to set_time_zone_clause
  in the documentation on CREATE DATABASE.

Also, you should apply the latest DST patch as well, so the database knows when to set the time forward or backward for daylight saving.


SQL Server - Basic Availability Groups used for Publisher, Distributor and Subscriber databases? I see recently support has been added so that the Distributer can be hosted with AlwaysOn AG.

https://blogs.msdn.microsoft.com/sql_server_team/replication-enhancement-distribution-database-in-availability-group/
https://docs.microsoft.com/en-us/sql/relational-databases/replication/configure-distribution-availability-group?view=sql-server-2017

Can the Publisher, Distributor and Subscriber databases be hosted in a High Availability configuration using Basic Availability Groups?

Given that basic availability groups only support a single database, if BAG can support this, I assume it would require three sets/clusters of two nodes ~ 2 publishers, 2 distributors and 2 subscribers.

The short answer is Yes.

If you can do it in an AG, and you can do it in SQL Server standard - you should be able to do it in a Basic Availability Group. The only gotcha was the distributor which wasn't able to be on an AG - the Replication couldn't work with the listener right - until the releases described in your links.

So you would basically have three AGs. Each with one DB. The distributor would be in one. The Subscriber in another. The Publisher in another.

You have to have your distributor on a separate SQL Server instance. So this would be mean two instances participating in a Basic Availability Group for the distributor, and then two for the Publisher and, most likely, two for the subscriber. Because of the limitations in the Basic Availability Group.

You could have more than on AG on an instance and have one DB in each and that would be good. but your limit here is 1.) You are doing replication, likely for a reason so your publisher and subscriber would likely be on different instances. 2.) The distributor has to be separate from publisher and subscriber.


T-SQL How to Pivot on the Values of a Table? How do you pivot a table on the value in the field? I have to admit that while I can write some pretty nifty queries, have my CTEs down, and enjoy a wide knowledge of T-SQL programming, Pivots get into a wonky area that I am uncomfortable and unfamiliar with.

Let me describe my issue, and see if you can help.

I have the following tables:

CREATE TABLE [dbo].[Itinerary]
(
   [ItineraryID] [int] IDENTITY(1,1) NOT NULL primary key,
   [RoadTripID] [int] not NULL,
   [ItineraryDate] [date] not NULL,
   EstimatedMiles int not null,
   Destination varchar(64) not null,
   DestinationState varchar(2) not null,
   DayTypeID int not null
)

Create Table dbo.DayType
(
   DayTypeID int primary key identity,
   DayTypeCode varchar(2) not null,
   DayTypeDescription varchar(32) not null
)

DayType is a helper table with nine values:

Insert into DayType
values
  ('XX', 'Planning/Prep'),
  ('SE', 'Short Event'),
  ('LE', 'Long Event'),
  ('WD', 'Writing Day'),
  ('TD', 'Tech Day'),
  ('HD', 'Hiking Day'),
  ('PD', 'Photography Day'),
  ('LD', 'Layover Day'),
  ('LH', 'Long Haul')

When I enter a row into Itinerary it looks like this:

insert into itinerary
values(1, '11/27/2014', 361, 'Amarillo', 'TX', 1)

The last piece of the puzzle (and the question itself) is that I want to report out on how many of each type of day I've planned.

I am currently using the tried-and-true-(and awful) method:

Select
   s.ItineraryID, R.ShortName as RoadTripName,
   sum(EstimatedMiles) as TotalMiles,
   count(itineraryid) as TotalDays,
   (select count() from Itinerary i1 with (nolock) where daytypeid = 1 and i1.itineraryid = s.itineraryid) as 'Planning/Prep',
   (select count() from Itinerary i2 with (nolock) where daytypeid = 2 and i2.itineraryid = s.itineraryid) as 'Short Event',
   (select count() from Itinerary i3 with (nolock) where daytypeid = 3 and i3.itineraryid = s.itineraryid) as 'Long Event',
   (select count() from Itinerary i4 with (nolock) where daytypeid = 4 and i4.itineraryid = s.itineraryid) as 'Writing Day',
   (select count() from Itinerary i5 with (nolock) where daytypeid = 5 and i5.itineraryid = s.itineraryid) as 'Tech Day',
   (select count() from Itinerary i6 with (nolock) where daytypeid = 6 and i6.itineraryid = s.itineraryid) as 'Hiking Day',
   (select count() from Itinerary i7 with (nolock) where daytypeid = 7 and i7.itineraryid = s.itineraryid) as 'Photography Day',
   (select count() from Itinerary i8 with (nolock) where daytypeid = 8 and i8.itineraryid = s.itineraryid) as 'Layover Day',
   (select count(*) from Itinerary i9 with (nolock) where daytypeid = 9 and i9.itineraryid = s.itineraryid) as 'Long Haul'
from
   RoadTrip r
inner join
   Itinerary s on r.roadtripid = s.roadtripid
group by
   s.itineraryid, r.shortname

which returns:

Obviously (hopefully!) there needs to be a better way. How can I pivot those nine values, and their total count each into this query?

Try this Query;

Select s.ItineraryID, R.ShortName as RoadTripName,  sum(EstimatedMiles) as TotalMiles, count(itineraryid) as TotalDays,
SUM(case DayType.DayTypeCode when 'XX' then 1 else 0 END ) as 'Planning/Prep',
SUM(case DayType.DayTypeCode when 'SE' then 1 else 0 END ) as 'Short Event',
SUM(case DayType.DayTypeCode when 'LE' then 1 else 0 END ) as 'Long Event',
SUM(case DayType.DayTypeCode when 'WD' then 1 else 0 END ) as 'Writing Day',
SUM(case DayType.DayTypeCode when 'TD' then 1 else 0 END ) as 'Tech Day',
SUM(case DayType.DayTypeCode when 'HD' then 1 else 0 END ) as 'Hiking Day',
SUM(case DayType.DayTypeCode when 'PD' then 1 else 0 END ) as 'Photography Day',
SUM(case DayType.DayTypeCode when 'LD' then 1 else 0 END ) as 'Layover Day'
from  RoadTrip r inner join Itinerary s on r.roadtripid = s.roadtripid
  inner join DayType on s.DayTypeId = DayType.DayTypeId
group by s.ItineraryID,  R.ShortName

Or another way using Pivot

Select ItineraryID, RoadTripName, EstimatedMiles,[XX] as 'Planning/Prep',[SE] as 'Short Event',[LE]  as 'Long Event',[WD] as 'Writing Day',[TD] as 'Tech Day',[HD] as 'Hiking Day',[PD] as 'Photography Day',[LD] as 'Layover Day'
FROM
(SELECT i.ItineraryID, r.RoadTripName,  dt.DayTypeCode, EstimatedMiles, 1 as daycount
    FROM RoadTrip r
    inner join Itinerary i on r.roadtripid = i.roadtripid
     --Itinerary i
    inner join DayType dt on dt.DayTypeId = i.DayTypeId
) ps
PIVOT
(
COUNT(dayCount)
FOR DayTypeCode IN ( [XX],[SE],[LE],[WD],[TD],[HD],[PD],[LD])
) as Pvt


Users running expensive queries I am currently monitoring the expensive queries from the activity monitor for SQL server.

How do I see who is running the different queries?

A much better way to monitor is using sp_whoisactive - from Adam Machanic. I use sp_whoisactive with below parameters :

You can even log the output to a physical table for future analysis. It allows you to capture query plan, blocking, etc as well.

EXEC sp_WhoIsActive
    @filter = '',
    @filter_type = 'session',
    @not_filter = '',
    @not_filter_type = 'session',
    @show_own_spid = 0,
    @show_system_spids = 0,
    @show_sleeping_spids = 1,
    @get_full_inner_text = 1,
    @get_plans = 1,
    @get_outer_command = 1,
    @get_transaction_info = 0,
    @get_task_info = 1,
    @get_locks = 0,
    @get_avg_time = 0,
    @get_additional_info = 0,
    @find_block_leaders = 0,
    @delta_interval = 0,
    @output_column_list = '[dd%][session_id][sql_text][sql_command][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks][%]',
    @sort_order = '[start_time] ASC',
    @format_output = 1,
    @destination_table = '',
    @return_schema = 0,
    @schema = NULL,
    @help = 0


Ola Hallengren DatabaseBackup error on locating Windows directory Need help in resolving the issue with backing up a DB using Ola Hallengren's stored procedure to a shared folder.

I have a procedure that is executed at SQL Server startup. It runs Ola Hallengren's DatabaseBackup procedure. The SQL Server service runs under NT Service.

The share has permissions for the machine where SQL runs.

Now, if execute DatabaseBackup manually it goes fine. When it executes automatically at startup it fails with the following error:

  Msg 50000, Level 16, State 1, Procedure DatabaseBackup, Line 786
  [Batch Start Line 0] The directory \\*** does not exist

If I alter the procedure with an Execute As DBO clause, it fails with the same error.

I also tried to Alter the SP with Execute As Self clause - the same error again.

So the question is:

What should I do to make it work?

The file path is provided as a UNC path (e.g. \\machine2\backup\)
This is SQL Express, so I don't have access to SQL Agent jobs.
Therefore, this is a stored procedure configured to run when the SQL Server instance starts up, not a SQL job set to run when SQL Agent starts.

Community wiki answer:

Use a domain account with access rights on the share, then this should work. NT_Service will not be able to reach it.

If using a domain account is not possible for some reason, you can try granting the machine account permission to the share. The machine account follows the following format: DOMAIN\MachineName$. The $ is needed to denote it's a computer and not a user.


Transactional replication upgrade from SQL Server 2017 We would like to inplace upgrade of transactional replication topology from SQL Server 2017 to SQL2019 with minimal downtime. The following is the setup. All the all current instances are on SQL Server 2017 enterprise

Server A has 2 databases serving as publisher
Server B has 2 databases serving as subscriber
Server C is the remote distributor.

From inplace upgrade perspective, we have to first upgrade distributor --->publisher--->subscriber.
I would like to know the rollback steps for distributor database if inplace upgrade fails to SQL Server 2019 and we have to revert to SQL Server 2017 with minimal possible downtimes.
Requesting some insights on procedure to do the same.
Kevin

Rolling back an in place upgrade on a distributor is pretty similar to any failed in place upgrade. Which is to say, it's not usually quick and simple.
If the failure is during the install, you'll need to uninstall, reinstall, and revert to backups. With replication, msdb is very important, in addition to the distribution database, in order to restore all of the jobs. VM-level snapshots can be very useful here, as that can make reverting easier, without having to do messy uninstall/reinstall. Depending on your VM configuration, VM snapshots might save you from having to restore databases, too.
If the failure/reason to rollback happens after upgrade, when you've re-enabled any of the agents, then restoring from backup won't be enough. Data that is being distributed to subscribers or read from the publisher logs after upgrade, before rollback would cause data inconsistencies. In that case, you'll also need to re-snapshot your publications on rollback.


Granting a user read-only access on [dbo] but full access on their own schema I created a user at the database level. I created a schema [jm] for them to use. I would like them to be able to create whatever database objects only within their own schema - but limit their abilities in [dbo] to purely read-only.

How can I accomplish that?

The correct grants are something like:

create user joe without login
go
create schema joe_schema
go

grant select on schema::dbo to joe;
alter authorization on schema::joe_schema to joe;

grant create table,
      create procedure,
      create view,
      create function,
      create type
      to joe;

It's pretty important that you don't grant the user the ability to alter a schema owned by another user, especially one owned by dbo.  If you do ownership chains will allow the user to skip permissions checks on the schema owner's objects in all schemas.


SQL Server alerts not sending notification emails to the operator I have setup few SQL Server alerts and added Operator to notify if any alert occurred.

I just noticed that couple of alerts did occurred but we didn't receive any alert email. I did send a test notification email to the operator and it worked and we received email.

Any idea why we are not getting email alerts when actual alert occurred?

Nothing in Database Mail Log.

The SQL Server is part of the Availability Group and I'm only providing email name in operator not doing any 'Net send address' or 'Pager e-mail name'. Also all the alerts are listed on the Properties of the Operator.

Alert History got 'Number of occurrences :266' and Date of last alert is yesterday 29th June but I didn't receive alert email at all. Even Operator history got no history at all (Never e-mailed).

Check SQL Server Agent Properties: on the "Alert System" page, is "Enable mail profile" checked?

Sometimes with new installations, after I set up options on that page I've found notification emails for alerts aren't sent. A restart of the SQL Agent service often addresses that issue for me. - dave-mason

An article that explains more details: How to setup SQL Server alerts and email operator notifications by David Bird - joshua-washburn

Helpful debugging queries: Notes on debugging Database Mail problems by Kenneth Fisher - erik-darling


Flip a flag from one row to another in a single query Given this table:

CREATE TABLE rollouts
(
    id           UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    device_id    TEXT        NOT NULL,
    is_active    BOOLEAN     NOT NULL DEFAULT FALSE
);

How can I flip the is_active flag from one row to another for a given device_id and id?

Eg, go from

ID    DEVICE    IS_ACTIVE
1     D1        TRUE
2     D1        FALSE

to

ID    DEVICE    IS_ACTIVE
1     D1        FALSE
2     D1        TRUE

in  single query?

Because this won't clear the flag for ID=1:

UPDATE table SET is_active = true WHERE device=d1 AND ID=2

Just flip it for both:

update the_table
   set is_active = not is_active
where device = 'd1'
  and id in (1,2);

For a more general approach see:
How can I swap two values from particular column in a table in Postgres?


Using SQL Server Import\Export Wizard with Ms. Access as a source DB I need to copy this Ms. Access database that we have to test it in SQL Server 2008 R2, is the Import\Export wizard will let me copy all tables, queries, forms... without affecting the live database?
I mean once I click "Finish" at the end of the wizard, is this just moving everything from origional data source or just copying it?

Any links that I should check on what kind of obstacles that I may face with large databases, much appreciated.

There are several good options for migrating data from Microsoft Access into SQL Server:

SQL Server Migration Assistant

SQL Server Migration Assistant for Microsoft Access provides a reliable method for migrating database objects from Access into SQL Server.  Optionally, it provides a way to link SQL Server tables into an Access back-end to enable you to continue to use an Access-based front-end with the SQL Server back-end data.
Import/Export Data Wizard in SQL Server Management Studio

Importing data via SQL Server Management Studio is a straight-forward method for copying data from an Access database into a SQL Server database.  Running the wizard allows you to pick an Access database, then choose individual objects in Access to import into SQL Server.  Tables and Views (queries) can be copied from Access into SQL Server.  The source data is not modified in any way.
Microsoft Access Upsizing Wizard.

The Upsizing Wizard moves database objects and the data they contain from an Access database to a new or existing SQL Server database.

There are three ways to use the Upsizing Wizard:

Upsize all database objects from an Access database to an Access project so that you can create a client/server application. This approach requires some additional application changes and modification to code and complex queries.
Upsize only data or data definitions from an Access database to a SQL Server database.
Create an Access database front-end to a SQL Server database back-end so that you can create a front-end/back-end application. This approach requires very little application modification since the code is still using the Access database engine (ACE).

If you are simply importing one or two tables, it is likely easiest to use the Import Data wizard in SSMS (option 2, above), whereas if you are attempting to migrate an entire application's data into SQL Server, I'd recommend using the SQL Server Migration Assistant (option 1, above).

For very large databases, migration can take a significant amount of time, and can use significant temporary space in SQL Server; ensure you have enough free space, at least temporarily, on the SQL Server to store 2 copies of your Access data.  You'll want to plan to prevent users from updating the data in the Access database while doing the migration, and directly afterwards, in order to ensure the data stored in SQL Server is the most current version.  Do this migration during a period of planned outage.  Plan the outage based on tests you perform of this migration in a test environment.


How do I get rid of error #1064 Table structure for table tbl_appointment

CREATE TABLE IF NOT EXISTS `tbl_appointment` (
`ap_id` int( 11 ) NOT NULL ,
`ap_starting` time( 6 ) NOT NULL ,
`ap_ending` time( 6 ) NOT NULL ,
`ap_day` varchar( 255 ) NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = latin1;

MySQL said: Documentation

  1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(6) NOT NULL,
    ap_ending time(6) NOT NULL,
    ap_day varchar(255) NOT NULL
  )' at line 9`

I tried to import the SQL file to phpmyadmin. the file works fine on later version of XAMPP from what I have. I tried to edit the SQL file based on the error but to no avail.

The microsecond fractional part of your DDL (I.e. The (6)) is only supported from MySQL 5.6 onward. Most likely a compatibility issue I'm afraid.


MySQL sql_mode for remote access I have setup MySQL with the default conf /etc/my.cnf as following:

[mysqld]

user=root

innodb_file_per_table

innodb_file_format=Barracuda

innodb_large_prefix

log_bin_trust_function_creators

sql-mode='NO_AUTO_CREATE_USER'

max_sp_recursion_depth=10

show_compatibility_56=ON

performance_schema

character-set-server=utf8mb4

[client]

socket=/home/abc/mysql_home/socket

[mysql]

default-character-set=utf8mb4

Accordingly, when I run select @@global.sql_mode, @@session.sql_mode, @@sql_mode; from Linux machine, I see :

NO_AUTO_CREATE_USER | NO_AUTO_CREATE_USER | NO_AUTO_CREATE_USER

1 row in set (0.00 sec)

Where as if I connect to the same instance and same user from the Windows machine (SQL developer), I see the following :

 NO_AUTO_CREATE_USER |STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER |STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER

1 row in set (0.00 sec)

Basically, mode STRICT_TRANS_TABLES is getting added for the session variable though I have not made any session level updates. This MySQL strict mode is causing further issues.

Would like to know how this STRICT_TRANS_TABLES is getting added by default only for the remote connection?

Commands used for creating the user :

CREATE USER 'USER_MYSQL'@'localhost' IDENTIFIED BY 'USER_MYSQL';
GRANT ALL PRIVILEGES ON *.* TO 'USER_MYSQL'@'localhost' WITH GRANT OPTION;
CREATE USER 'USER_MYSQL'@'%' IDENTIFIED BY 'USER_MYSQL';
GRANT ALL PRIVILEGES ON *.* TO 'USER_MYSQL'@'localhost' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'USER_MYSQL'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Using MySQL 5.7 version.

/etc/my.cnf is not the only place to get such things.  It sounds as if you have a .my.cnf / my.ini file in your 'home' directory on Windows.

Which version of 5.7 are you using?  (There were several changes to sql_mode.)

What client are you using?  Perhaps it is injecting changes to sql_mode to mess with your head.


Database design Problem for the web-app Hello I am in a problem of designing a web-app database schema.

Suppose I have 10 shops and every shop gives deals on every different day Sunday, Monday and so on and for limited time period like some shops give deals from 4 to 5 pm, some 8 to 10 pm, 4 to 10 pm.

Apart from that I have few categories on which a shop will give the deals like beer, wine, foods etc.  For example, suppose categories are 1, 2, 3, 4, & 5.

Shop A gives deals on 1 and 2 on Sunday from 4 to 6 pm
Shop A gives deals on 2 and 3 on Mon from 7 to 8 pm
Shop A gives deals on 1 and 5 on Wednesday from 7 to 8 pm

Shop B gives deals on 1 and 2 on Sunday from 4 to 6 pm
Shop B gives deals on 2 and 5 on mon from 7 to 8 pm
Shop B gives deals on 1 and 4 on sturday from 7 to 8 pm

Shop C gives deals on 1 and 3 on Sunday from 5 to 6 pm
Shop C gives deals on 3 and 5 on wed from 5 to 8 pm
Shop C gives deals on 2 and 4 on fri from 4 to 8 pm

There is a possibility that any shop can provide the same type of deals every day or maybe 3 or 4 or 5 days of the week and the rest of the days are different like

Shop D  gives deals on 1 and 3 on Sunday, Mon, Tue and so on from 5 to 6 pm.

Shop E gives deals on 1 and 5 on Sunday, Mon, Tue and Fri from 5 to 6 pm.
Shop E gives deals on 2 and 4 on Wed and Thu from 4 to 6 pm.

In this case there will be redundancy, duplicity of the same type of data that I also want to stop.

Now I am totally confused how should I design the database for this problem?

Help will be appreciated.

Thanks

From what I understand of your problem your "duplicity" is fine.  If your saying that all of the shops act totally independently then yes in your example shop E and shop D might have the same data but the data is not related so it might look like a duplicate, but I would not consider that a duplicate.

Another example, I am answering this question now, but I expect someone else is going to hit the submit button to a different question at exactly the same time, so in their "answer" table (or whatever) there will be two rows with "duplicate" data on it (the time of answer would be the same), is this a problem..... no because the data is unrelated, its simply a record of what happened.

The only time I would suggest normalising this further is if you had to attach extra information to the time which would be the same (so if there was information that would always be the same whenever any shop does any deal that starts at 6pm on a Sunday).  A counter argument to this could be do you have shops in different time zones?  If yes then 6pm could mean 6pm local time, but not at the same time, in this case what possible extra information could there be which would always be the same in every country, time-zone, shop and deal.


Hierarchical Structure Every single user has say, 3 of GROUP_A, 10 GROUP_B's per GROUP_A, and 20 GROUP_C's per GROUP_B. And each of the 20 GROUP_C's involve lots of inserts/deletes...

I'm not an expert, but I've done research but it's all theoretical at this point of course, and I don't have hands on experience with the implementation that's for sure. I think my options are something like 'adjacency lists' or 'nested sets'?

Any guidance into the right direction would be very much appreciated!

One way to define your tables:

Users: user_id (primary key), name, etc

Group_A: user_id, group_a_id, group_a_name

Group_B: group_a_id, group_b_id, group_b_name

Group_C: group_b_id, group_c_id, group_c_name

Items: group_c_id, item_id, item_name, details


Difference Between SP with BEGIN/END and without BEGIN/END I have found some SP(stored procedure) written as,

CREATE PROCEDURE [dbo].[XXX]
(
   -- Parameters
)
BEGIN

--- Actual Work

END

and some as

CREATE PROCEDURE [dbo].[XXX]
(
   -- Parameters
)

--- Actual Work

What makes it difference?

There is no difference. That is, it's optional.

What matters is the "end of the batch" which is denoted in SQL Server Management Studio by GO


Modeling Optional Foreign Key from Composite Primary Key field (Oracle Data Modeler) I am trying to add an optional foreign key in Oracle SQL Developer Data Modeler v4.1.0.873(Build 873), as per diagram below:

The issue is that I can only make such a relationship if it is mandatory (note solid black line - I need it to be dotted).  The 'Mandatory' checkbox cannot be unchecked, or where it can be, I get:

  MKT_DATA_BONDS__FK: FK cannot be optional because some columns are
  part of the PK

How do I show this relationship in the model?  (We don't want the BONDS table to contain all bonds).  I tried adding an index (on BOND_ID) but wasn't able to reference that either.

You have the foreign key in the wrong table. Your defining entity is in the MKT_DATA_BONDS table. The refering entity is in the BONDS table. Each BOND must be defined in MKT_DATA_BONDS. The "optional" part is carried out by the fact that not all market bonds need be represented as a company bond.

To put it in OO language, market bond is the super or parent class. A company bond is one (of possibly several) sub or derived classes. Subclasses know about super classes, but the super class doesn't know about (or refer to) any subclass.


Choosing shard key with compound index field I have a compound key with field articleId_1_host_1_timeStamp_1_isIndia_1_location_1.

Cardinality of fields is

articleId has 20000 different value
host has 17 value
timestamp is increasing field with time

I want to use articleId_1_host_1_timeStamp_1 as shard key.

Could you please suggest if I can use this as shard key with timestamp field in it? Also do I need to create a different index using these three fields or can I use the above compound key with shard key of of first three fields?

You should be able to use the index listed to cover the shard key. It is a super set of your shard key fields.

The shard key listed should be fine for distributing write load, given you don't expect any individual articleId/host pairs to take the bulk of your writes at a given point in time.

I would be concerned about this shard key for reads. In order to target a single shard for a query, you need to include the shard key values. My guess is your queries do not include timestamp. Without timestamp your queries will be sent to every shard which is inefficient. With scatter gather reads, your hamper your ability to scale reads by adding shards.


How can I query matching MongoDB documents and return a subset of fields from an array of embedded documents? I have documents similar to the following:

db.etudiants.insert(
    {
        nom:'emile',
        prenom:'zola',
        cours_reussis:[
            {
                intitule: ['PAI'],
                Periodes: NumberInt(60),
                note: 60,
                date_reussite: NumberInt(2014),
                dispense: true,
                nom_ecole:'EPFC'
            },
            {
                intitule:'TGP',
                Periodes: NumberInt(40),
                note:75,
                date_reussite: NumberInt(2013),
                dispense: true,
                nom_ecole: 'HEB'
            },
            {
                intitule: 'SO2',
                Periodes: NumberInt(60),
                note: 70,
                date_reussite: NumberInt(2015),
                dispense: true,
                nom_ecole: 'ISFCE'
            },
            {
                intitule: 'IBD',
                Periodes: NumberInt(60),
                note: 80,
                date_reussite: NumberInt(2015),
                dispense:true,
                nom_ecole:'ISFCE'
            }
        ]
    }
)

How can query matching documents and return nom_ecole: 'ISFCE' and year (date_reussite) from the cours_reussis array?

If you want to return a subset of matching array elements in a MongoDB document, the best approach to do so is using the Aggregation Framework. The Aggregation Framework processes data similar to a Unix pipeline: the output of each aggregation stage is passed as input to the next:

db.etudiants.aggregate([
    // Find relevant documents: this can take advantage of an index, if applicable
    { $match: {
        'cours_reussis.nom_ecole': 'ISFCE',
    }},

    // Expand cours_reussis array into a stream of documents
    { $unwind: '$cours_reussis' },

    // Filter based on matching array elements
    { $match: {
        'cours_reussis.nom_ecole': 'ISFCE',
    }},

    // Project desired subset of fields
    { $project: {
        '_id': "$_id",
        'nom': '$nom',
        'prenom': '$prenom',
        'cours_reussis' : { 'nom_ecole': 1, date_reussite: 1 }
    }},

    // Regroup data as per the original document _id
    { $group: {
        _id: "$_id",
        nom: { $first: "$nom" },
        prenom: { $first: "$prenom" },
        cours_reussis: { $push: "$cours_reussis" }
    }}
])

You can debug the aggregation pipeline by starting with a single stage and then adding on additional stages one at a time assuming you are seeing the expected output.

Sample output from MongoDB 3.2:

{
  "_id": ObjectId("58462f40f8475aba9f391216"),
  "nom": "emile",
  "prenom": "zola",
  "cours_reussis": [
    {
      "date_reussite": 2015,
      "nom_ecole": "ISFCE"
    },
    {
      "date_reussite": 2015,
      "nom_ecole": "ISFCE"
    }
  ]
}


SQL Server maintenance plan not working after renaming administrator I have renamed the Windows administrator account to TEST using Local Group Policy editor. After that, the SQL Server maintenance plans are not working anymore:

  Unable to determine if the owner of job has server access

I have tried methods below but no luck yet:

I have tried to add a login to SQL Server for TEST account, but it says that this login already exists. I can not see the name of TEST in the Login tab, but the administrator exists already
I have deleted the Maintenance plans and created new ones but no change happened

Please let me know if there is another way to connect the Maintenance plans to the new administrator name.

I have solved the problem using SQL Server Management Studio (SSMS):

Expanding Server --> security
Expanding Logins
Right click on administrator account and simply rename it to the new name I have set before in Local Group Policy.
Maintenance plans works fine!


If there are duplicate values do you want two(+) results or just one?

If you want ALL results:

SELECT Time FROM a
UNION ALL
SELECT Time FROM b
UNION ALL
SELECT Time FROM c

If you want just UNIQUE results:

SELECT Time FROM a
UNION
SELECT Time FROM b
UNION
SELECT Time FROM c


How can I improve this update? I'm working with SQL Server 2014 Express edition and I want to upload some OpenStreetMaps data.

Data is a 172 Mb XML and it loads ok. The problem is with this script:

UPDATE ways
SET geog4326 = 'LINESTRING(' + STUFF((
SELECT ',' + CAST(CAST(n.Longitude AS decimal(18,9)) AS varchar(32)) + ' ' + CAST(CAST(n.Latitude AS decimal(18,9)) AS varchar(32)) AS [text()]
FROM
ways w JOIN waynodes wn ON w.wayid = wn.wayid
JOIN nodes n ON wn.nodeid = n.nodeid
WHERE wn.wayid = w.wayid
ORDER BY w.wayid, orderid
FOR XML PATH(''), TYPE
).value('/', 'NVARCHAR(MAX)'),1,1,'') +')'

I get an .NET framework Out of memory on SQL Server Management Studio and also running it from SQLCMD.
ways table has 101,393 rows and waynodes has 1,018,575 rows.

Do you know how can I improve this script?

By the way, I get this script from this tutorial.

I have no index and no plan execution. I'm only following the tutorial and I get out of memory error.

As Ypercube said in his comment you seem to have an uncorrelated subquery.  This means that for each way you are trying to build a line containing 1,000,000 odd nodes.

Also it it would be best to put a Geography constructor around the result of the subquery.

The tutorial's update statement that you referenced has got a correlated subquery, because the WHERE clause is wn.wayid = ways.wayid
Changing your update query to this should fix the issue.

UPDATE ways
SET geog4326 = Geography::STGeomFromText('LINESTRING(' + STUFF((
    SELECT ',' +
        CAST(CAST(n.Longitude AS decimal(18,9)) AS varchar(32)) + ' ' +
        CAST(CAST(n.Latitude AS decimal(18,9)) AS varchar(32)) AS [text()]
    FROM ways w
        JOIN waynodes wn ON w.wayid = wn.wayid
        JOIN nodes n ON wn.nodeid = n.nodeid
    WHERE wn.wayid = ways.wayid -- Changed to ways to correlate
    ORDER BY w.wayid, orderid
    FOR XML PATH(''), TYPE
    ).value('/', 'NVARCHAR(MAX)'),1,1,'') +')',4326)
WHERE EXISTS (SELECT 1 FROM waynodes wg WHERE ways.wayid = wg.wayid GROUP BY wg.wayid HAVING COUNT(*) > 1)

Personally I prefer to construct my update statements similar to the following.

UPDATE z
SET geog4326 = Geography::STGeomFromText('LINESTRING(' + STUFF(CAST(x.linestring AS VARCHAR(MAX)),1,1,'') +')',4326)
FROM ways z
    CROSS APPLY (
        SELECT ',' +
            CAST(CAST(n.Longitude AS decimal(18,9)) AS varchar(32)) + ' ' +
            CAST(CAST(n.Latitude AS decimal(18,9)) AS varchar(32)) AS [text()]
        FROM ways w
            JOIN waynodes wn ON w.wayid = wn.wayid
            JOIN nodes n ON wn.nodeid = n.nodeid
        WHERE z.wayid = w.wayid
        ORDER BY w.wayid, orderid
        FOR XML PATH('')
    ) x (linestring)
WHERE EXISTS (SELECT 1 FROM waynodes wg WHERE z.wayid = wg.wayid GROUP BY wg.wayid HAVING COUNT(*) > 1)

I've tested a SELECT version of my update on some made up data.
SQLFiddle

 WITH ways AS (
 SELECT *
 FROM (VALUES
    (1),(2),(3)
    ) W(wayid)
 ),
 waynodes AS (
 SELECT *
 FROM (VALUES
    (1,1,1),
    (1,2,2),
    (1,3,3),
    (2,1,4),
    (2,2,5),
    (2,3,6),
    (3,1,7),
    (3,2,8)
    ) WN(wayid, orderid, nodeid)
 ),
 nodes AS (
 SELECT *
 FROM (VALUES
    (1, 0, 0),
    (2, .1, 0),
    (3, .1, .1),
    (4, .1, .1),
    (5, .2, 0),
    (6, .3, 0),
    (7, 0, 0),
    (8, 0, .2)
    ) N(nodeid, longitude, latitude)
 )
SELECT Geography::STGeomFromText('LINESTRING(' + STUFF(x.linestring,1,1,'') +')',4326)
FROM ways z
    CROSS APPLY (
        SELECT ',' + CAST(CAST(n.Longitude AS decimal(18,9)) AS varchar(32)) + ' ' + CAST(CAST(n.Latitude AS decimal(18,9)) AS varchar(32)) AS [text()]
        FROM ways w
            JOIN waynodes wn ON w.wayid = wn.wayid
            JOIN nodes n ON wn.nodeid = n.nodeid
        WHERE z.wayid = w.wayid
        ORDER BY w.wayid, orderid
        FOR XML PATH('')
        ) x(linestring)


How to stop logging messages in error log How do i stop logging unwanted messages in SQL error logs, I enabled DBCC TRACEON (3226,-1) to avoid logging successful backups, likewise is there any other trace flags or any message id's to disable in msdb.dbo.sp_altermessage to stop them
Below is the example of unwanted messages i want to stop logging

Thanks in Advance

How do i stop logging unwanted messages in SQL error logs […] likewise is there any other trace flags […]

No, there are no trace flags to turn this logging off.

If you'd like to give some feedback on making a trace flag or have this disabled by default, put in a suggestion on User Voice with this specific example :)


What are the side effects of raising the PROCESSES system parameter? We have a legacy application (ERP) that, because of technical issues, spawns a new OS process (.exe - ms windows clients) for each screen requested by the user. Also, each company branch (> 20) has its own schema, and the application deals using 1 connection for each. Since each user may open as many screens they deserve to, it is quite common that some users reach > 30 sessions.

That will result in opening many connections to Oracle.
In last report, we got 861 sessions for 87 users (~10 sessions/user).

My customer's DBA is saying that this will knock the database performance down, and demand heaps (really huge amounts) from the server's RAM & CPU.

Does this statement make sense?

So:

What are the side effects of raising the PROCESSES system parameter?
What is the cost of a open process (even if idle) in the server?

At least it has you have to increase most of the OS kernel parameters(like number of file handles). Most of them have a formula where use PROCESSES is an input. Also each server process allocates some RAM - the about is not big, but when multiplied by one thousand ...

Idle processes do not have any major overhead, but when DB server starts swapping RAM it's over. All the performance is away.


How to get the size of a spatial index? I want to see the size of a spacial index. I am usually using one of the following code blocks to get the indexes sizes of a given table, but none of them is working for a spatial index.

DECLARE @DataSource TABLE
(
    [TableName] SYSNAME
   ,[IndexName] SYSNAME
);

INSERT INTO @DataSource ([TableName], [IndexName])
VALUES ('[dbo].[Table]', 'Index');

SELECT OBJECT_NAME(INX.[object_id]) AS TableName
      ,INX.[name] AS IndexName
      ,INX.[index_id] AS IndexID
      ,8 * SUM(AU.[used_pages]) AS 'Indexsize(KB)'
FROM [sys].[indexes] AS INX
INNER JOIN [sys].[partitions] AS PAR
    ON PAR.[object_id] = INX.[object_id]
    AND PAR.[index_id] = INX.[index_id]
INNER JOIN [sys].[allocation_units] AS AU
    ON AU.[container_id] = PAR.[partition_id]
INNER JOIN @DataSource DS
    ON INX.[object_id] = OBJECT_ID(DS.[TableName])
    AND INX.[name]  = DS.[IndexName]
GROUP BY INX.[object_id]
        ,INX.[index_id]
        ,INX.[name]
ORDER BY OBJECT_NAME(INX.[object_id])
        ,INX.[index_id]

OR

CREATE TABLE #CompressionResults
(
     [object_name] SYSNAME
    ,[schema_name] SYSNAME
    ,[index_id] INT
    ,[partition_number] INT
    ,[size_with_current_compression_setting (KB)] BIGINT
    ,[size_with_requested_compression_setting (KB)] BIGINT
    ,[sample_size_with_current_compression_setting (KB)] BIGINT
    ,[sample_size_with_requested_compression_setting (KB)] BIGINT
)

INSERT INTO #CompressionResults
EXEC sp_estimate_data_compression_savings 'dbo', 'A', NULL, NULL, 'ROW' ;

SELECT CR.[object_name]
      ,IDX.[name]
      ,[size_with_current_compression_setting (KB)] / 1024  AS [current size (MB)]
      ,[size_with_requested_compression_setting (KB)] / 1024  AS [size after compression (MB)]
      ,([size_with_current_compression_setting (KB)] - [size_with_requested_compression_setting (KB)]) / 1024 AS [saved size (MB)]
FROM #CompressionResults CR
INNER JOIN [sys].[indexes] IDX
    ON CR.[index_id] = IDX.[index_id]
    AND OBJECT_ID(CR.[object_name]) = IDX.[object_id]
UNION ALL
SELECT CR.[object_name]
      ,'ALL'
      ,SUM([size_with_current_compression_setting (KB)]) / 1024  AS [current size (MB)]
      ,SUM([size_with_requested_compression_setting (KB)]) / 1024  AS [size after compression (MB)]
      ,SUM([size_with_current_compression_setting (KB)] - [size_with_requested_compression_setting (KB)]) / 1024 AS [saved size (MB)]
FROM #CompressionResults CR
GROUP BY CR.[object_name]

DROP TABLE #CompressionResults

You need a slightly different query, since spatial indexes are tied to internal tables, not the base object. This does not aggregate or account for partitions, nor did I tweak it to drop into your current code; just meant to point you in the right direction. You can leave off either where clause for discovery obviously. And the joins to indexes and objects are only necessary if you need to output the table name, or index name, or individual rows per index - if you just want a sum of row counts or other data from the partition-related DMVs for all special indexes for a table - which will include FTS and XML indexes - you can leave out both joins).

SELECT
  [internal_table_name] = N'sys.' + t.name,
  table_name = o.name,
  index_name = i.name,
  i.index_id,
  p.*
FROM sys.internal_tables AS t
INNER JOIN sys.objects AS o
ON t.parent_object_id = o.[object_id]
INNER JOIN sys.indexes AS i
ON t.[object_id] = i.[object_id]
INNER JOIN sys.partitions AS p -- dm_db_partition_stats probably more useful
ON t.[object_id] = p.[object_id]
AND i.index_id = p.index_id
WHERE o.name = N'<Name of table with spatial column(s)>'
  AND i.name = N'<Name of spatial index>';


Always Encrypted: How to insert or update encrypted columns? I have configured 'Always Encrypted' on one of the columns of a table of my SQL Server database. I am able to select and view data from client SSMS after passing the 'Column Encryption Setting = Enabled' option.

But when I am trying to insert data into the table, following error comes:

  Msg 206, Level 16, State 2, Line 1 Operand type clash: varchar is
  incompatible with varchar(8000) encrypted with (encryption_type =
  'RANDOMIZED', encryption_algorithm_name =
  'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name =
  'CEK_Auto1', column_encryption_key_database_name = 'TEST')
  collation_name = 'SQL_Latin1_General_CP1_CI_AS'

I am querying simple insert TSQL statement here. What changes should I need to make for it to work?

Also, what changes need to be made to the application or application code if, we want to update or insert the encrypted column via application?

You need to use variables for the values, so that SSMS can parameterize the query against the API that it is using. Something like:

    DECLARE @fname varchar(30) = 'Kula'
DECLARE @ename varchar(30) = 'Kalle'
DECLARE @pnr varchar(11) = '752312-4545'
DECLARE @age tinyint = 45

insert into dbo.Personer2 (Förnamn, Efternamn, Personnummer, Ålder)
VALUES (@fname, @ename, @pnr, @age)

SELECT * FROM Personer2


How can I implement a sequence for each foreign key value? In a PostgreSQL (9.3) database, I have a one-to-many relationship between Accounts and Customers, so within the Customers table, I have a globally-unique id for the primary key of each Customer, plus the corresponding Account id.  But I would also like to assign each one an Account-specific id - just a number starting at 1 for each Account's first Customer and incrementing for each additional Customer - simply as an easy way for users in an Account to reference a Customer in the UI.

I can think of two possible solutions:

Use the max() aggregate function to obtain the last Customer id used for the Account and increment it

Pros

Relatively simple (conceptually, at least)

Cons

Can result in an id being reused if the newest Customer is deleted
Need to handle concurrent inserts calculating the same id for the new records

Use a sequence

Pros

Specifically designed to handle incrementing the value
Remembers the last-used value, even if the corresponding record is deleted

Cons

Requires a separate sequence to be created for each new Account somehow (and then be deleted if/when the Account is deleted), otherwise each Account would end up with non-contiguous values in its Customer id sequence

UPDATE

To clarify the last point, some gaps in the sequence of ids are always going to occur; as soon as a Customer is deleted, for example, but those are to be expected.  Aside from that though, a user adding a couple of new Customers would generally expect them to have consecutive ids even if they were added days apart.

OTOH, using a single sequence for all Customers could almost certainly see regular, unexplained gaps introduced by the ids assigned to the Customers created for other Accounts in the meantime.

I'm not a Postgres expert, so I'm concerned about how robustly I could implement either of these, especially as I've struggled to find any examples to follow.

Is there a generally accepted way of doing this?

How about using a column in the table account called max_customer_id and updating that every time there's a new customer? That should be the simplest solution available that I can think of.

With this solution you have to take care of the concurrency. Take a look at the document PostgreSQL Concurrency issues:

  If two concurrent transactions try to
  insert rows having the same key value, then the second one will block until
  the first one finishes. If the first transaction commits, the second one must
  abort because of the uniqueness constraint; but if the first one aborts the
  second one can proceed.

  If you want to assign a serial number to each row in a table, you might try
  INSERT INTO mytable (id, ...)
  VALUES( (SELECT MAX(id) + 1 FROM mytable), ...);
  This will not work safely unless you take an explicit lock on the whole table,
  which will prevent concurrent insertions. (It’ll also be quite slow, because
  MAX scans the whole table in PostgreSQL.)

Alternatively you could place the value in a separate table. This table would contain the account_id and maximum account_specific_id in use for that account. That way you could handle the concurrency easier with UPDATEs. Same source:

  A variant is to use a single-row
  table to hold the next ID number to assign:
  SELECT next FROM mytable counter FOR UPDATE;
  UPDATE mytable counter SET next = $next + 1;
  INSERT INTO mytable (id, ...) VALUES($next, ...);
  This works (as long as you use FOR UPDATE), but you still have the
  problem that only one insertion transaction can proceed at a time. The
  implicit write lock on the counter row is the bottleneck.
  [The author goes on recommending sequences.]

The number 2 option with sequences is not however recommended in this case as new sequences are not meant to be routinely inserted in a production environment.


The MS documentation on FILESTREAM makes it sound like it should only be used for archiving unstructured data. Am I misunderstanding this? I am building an application that will store small image files (probably less than 1 MB typically) associated with records. I am considering the FILESTREAM option in MSSQL, but I'm a little confused about the documentation.

The documentation at Access FILESTREAM Data with Transact-SQL states:

  You can use Transact-SQL to update the data in the file system file; although, you might not want to do this when you have to stream large amounts of data to a file.

If I need to update a file with a new version of it, what other option is there other than using the T-SQL "Update" command?

The documentation at Create Client Applications for FILESTREAM Data even goes further and says you shouldn't Insert or Delete data in situations where the filestream column can be null:

  Avoid insert and delete operations in tables that contain nonnull FILESTREAM columns. Insert and delete operations can modify the FILESTREAM tables that are used for garbage collection. This can cause an application's performance to decrease over time.

If you shouldn't insert or delete files in a table where the filestream can be null, what do you do in situations where not all records will have a file but there's a lot of transactions (including inserting and deleting) going on with that table? I mean, how else are you going to insert data to later read without...well...inserting it?

It just sounds like the FILESTREAM option is more for archiving files (or unstructured data) that once it's saved, should really only be referenced through read operations.

I know I must be missing something, but the documentation seems a little confusing to me.

Is there a better tutorial or documentation out there for FILESTREAM?

Your understanding of the feature is correct.

  It just sounds like the FILESTREAM option is more for archiving files (or unstructured data) that once it's saved, should really only be referenced through read operations.

Yes. However, you're welcome to perform updates and deletes, keeping in mind that it can have a negative effect on performance.

For your use case, with small files of around 1 MB in size, you probably won't run into the issue as quickly.

You can also check out FileTable, which exposes an SMB share via FILESTREAM, if you're going to be doing modifications to the images.


SELECT DISTINCT
    DATE_TRUNC('day', Createdtime) "Created date",
    MIN(Createdtime) OVER (PARTITION BY DATE_TRUNC('day', Createdtime)) "1st record",
    FIRST_VALUE(Value)  OVER (PARTITION BY DATE_TRUNC('day', Createdtime)
                              ORDER BY Createdtime ASC) "1st Value",
    MAX(Createdtime) OVER (PARTITION BY DATE_TRUNC('day', Createdtime)) "last record",
    FIRST_VALUE(Value)  OVER (PARTITION BY DATE_TRUNC('day', Createdtime)
                              ORDER BY Createdtime DESC) "last Value",
    FIRST_VALUE(Value)  OVER (PARTITION BY DATE_TRUNC('day', Createdtime)
                              ORDER BY Createdtime ASC)
    -
    FIRST_VALUE(Value)  OVER (PARTITION BY DATE_TRUNC('day', Createdtime)
                              ORDER BY Createdtime DESC) "diff in Value"
FROM sourcetable


What does "unpackaged" mean in PostgreSQL extensions? I've read the docs and a few blog postings, but I still don't get what the "unpackaged" tag in an extension file name is for.

When would I use it and not use it?

If you had, say, a PostgreSQL 9.0 database using hstore, you would've loaded the hstore support by running the contrib's .sql script.

It was a major pain to dump and reload such databases, and especially to upgrade the contrib to a new version.

When extensions were introduced, there was a need to be able to convert the "unpackaged" contrib, i.e one loaded from an sql script, into an extension that could be recognised by the system, versioned, and upgraded using the extension control system.

That's what from unpackaged is for. It registers an already-installed extension that was set up manually from a script.

For more detail, see the documentation.


SELECTs in Database Change Log and Source Control We are overhauling the way that we store our database in source control and keep a change log of it. I was reading the following article: http://thedailywtf.com/articles/Database-Changes-Done-Right, and in the Short Section of "The Taxonomy of Database Scripts" it describes the three types of scripts (QUERY, OBJECT, and CHANGE). I like the idea of generalizing scripts into these three categories but I'm wondering about the QUERY type. Questions:

Why would someone want to put a SELECT statement into source control
outside of an object?
The database will change afterwards and make the QUERY script
unusable, what then?
The data may change returning a different result set, this would
defeat purpose of source control, what then?
Would the original result set have to be saved to solve the 2nd and
3rd issues?
What is an example of a SELECT statement that might be put into
source control?
Wouldn't a INSERT statement work better and store the results in a
table as in baselines?

I just can't see the purpose of storing SELECT statements into source control.

If there is a purpose could someone please answer the above questions and maybe state the pros and cons of storing a SELECT statement into source control?

Well, in the article you reference, he specifically states "The first category of scripts fall out of the realm of database changes."  That said, I've worked with plenty of legacy codebases that had select statements in source control; they were just in the application code.  (So, subject to all the problems you mention and hidden from the DBA.)

Before answering your question about selects specifically, It seems important to mention that the whole point of having a source control system is to be able to trace back where things went wrong.  That's why people might choose one over, say, an archive of daily backups.  In your second bullet point, you mention that your select statement might become invalid, but if you have a version control system you would be able to check out a previous version where the select statement still matched the schema.  Once you figured out what it was intended to do, you could check in a corrected version of that statement at a later version.

I guess I've now answered your first through third points.  It's unlikely (though entirely possible) that a select statement sitting in it's own sql file would be used by some other piece of software, but consider that a select statement sitting inside a stored procedure is equally able to become out-of-synch with schema.  Expanding on point 3, it's precisely because we recognize that this is a problem that we have source control, it does not defeat the purpose of SC, dealing with that problem is the purpose.

I can't really think of a case where storing the results would be helpful, but if you were worried you'd be unable to interpret it in the future, perhaps a description in a comment would be useful?

If you have select statements that are consumed by application code, powershell scripts, etl, reports, or possibly even by the ops team for routine tasks, these all seem like good candidates for being considered a part of your codebase.  Remember, the purpose of the source control is not as a backup strategy or as logging, it is to be able to bring up an internally consistent version of your code at a particular point in time.  That is why the answer to your last point is no.  this is about the code and the schema, not so much about your data.  The article that prompted this does a good job of explaining why it's challenging to put anything but purely reference data in your source control system.


How to change "Initial File Size" before restoring I have backup of size 10GB. When I restore DB on server it will take around 100GB due to initial file size set to 100GB. I could change it and shrink to around 14GB.

Now I want to restore db on my local machine, but the problem is I don't have enough free space.

Is there any option to restore db/override this setting? I do not want to recreate DB from scripts and then move data using SSIS/Redgate Compare or similiar.

I don't believe this is possible, typically the database would need to be shrunk before or after a restore.

If this were me I'd restore the database on another server, maybe on a dev server, shrink the files, and then copy and restore locally.


Query that Searches Duplicate Values Based on a Specific Value I've been searching through forums all morning and haven't been able to make much progress. The system I work with has a particular table that will assign two unique values to a row - we'll say ID01, and ID02. ID01 is generated by the system locally, while ID02 is generated by an external interface that connects to our system. This normally works fine, except in the scenario that the interface might be running duplicates; in this case, it generates a unique ID02 from both instances of the interface, but both have the same ID01 - resulting in a duplicate entry in the user interface.

My goal is to write a query that can be run against the database that will show me all rows on the given table that have the same ID01, but a different ID02. I started with this, as this was what most of the forum questions I could find marked as the answer to similar questions:

SELECT Count(ID01), ID01, ID02
FROM Table
GROUP BY ID01, ID02
HAVING (COUNT(ID01) > 1)
ORDER BY ID01

The result I get back doesn't work the way I need it to, however - it gives me a list where both ID01 and ID02 are duplicate at the same time, which our system already corrects for automatically. I need it instead to show me every row where ID01 is the same (both the first and duplicate instance or instances), but ID02 is different. If it makes any difference, this is being done in SQL Server 2008.

Any help would be much appreciated.

You can eliminate the duplicates from your query by using the ROW_NUMBER() aggregate:

IF OBJECT_ID('tempdb..#Table') IS NOT NULL
DROP TABLE #table;
CREATE TABLE #Table
(
      ID01 INT NOT NULL
    , ID02 INT NOT NULL
);

INSERT INTO #Table (ID01, ID02)
VALUES (1, 1)
     , (1, 2) --problematic
     , (1, 3) --problematic
     , (1, 4) --problematic
     , (2, 1)
     , (2, 1)
     , (2, 2) --problematic
     , (3, 1)
     , (3, 1)
     , (4, 1);

;WITH cte AS (
    SELECT DISTINCT
          t1_ID01 = t1.ID01
        , t1_ID02 = t1.ID02
        , rn1 = ROW_NUMBER() OVER (PARTITION BY t1.ID01 ORDER BY t1.ID01, T1.ID02)
        , rn2 = ROW_NUMBER() OVER (PARTITION BY t1.ID01, t1.ID02 ORDER BY t1.ID01, T1.ID02)
    FROM #Table t1
    )
SELECT *
FROM cte
WHERE cte.rn1 > 1
    AND cte.rn2 = 1;

The first ROW_NUMBER() function, rn1, is used to select rows where there are multiple ID02 values for each individual ID01 value.  The second ROW_NUMBER() function, rn2, is used to preserve the case where ID01 and ID02 have multiple duplicate values, "which our system already corrects for automatically".

That pattern can be leveraged to remove the invalid rows from the source table, by using the DELETE FROM <cte> syntax:

;WITH cte AS (
    SELECT t1.ID01
        , t1.ID02
        , rn1 = ROW_NUMBER() OVER (PARTITION BY t1.ID01 ORDER BY t1.ID01, T1.ID02)
        , rn2 = ROW_NUMBER() OVER (PARTITION BY t1.ID01, t1.ID02 ORDER BY t1.ID01, T1.ID02)
    FROM #Table t1
    )
DELETE
FROM cte
WHERE cte.rn1 > 1
    AND cte.rn2 = 1;

SELECT *
FROM #Table;

The output; first the select, then the table after problematic rows have been removed:


Filter Condition Difference - Where Clause vs Join Condition Quick easy filter question.

What would be the difference in output, or what impact would it have moving a filter condition out of a WHERE clause into the Join condition.

For example:

Select a1.Name, a2.State
from student a1
left join location a2 on a1.name_id = a2.name_id
where a1.name LIKE 'A%'
and a2.state = 'New York';

To This:

Select a1.Name, a2.State
from student a1
left join location a2 on (a1.name_id = a2.name_id) and a2.state = 'New York'
where a1.name LIKE 'A%';

Thanks all.

1) will show student names that begins with "A" and whose location is New York.
2) will show all student names that begin with "A" , 'New York' if student state is New York, or null in other cases ( there is no corresponding state, or student state is not New York)

Difference between (1) and (2) - (1) will not have non-New York students.


Unable to create a new listener in oracle 11g In net manager i get an error message "A valid logfile is required" when i click on listener and choose '+' button. and then it keeps on giving me that error when i click on any other tab or button in net manager.

I found this.

It basically says:

  For Windows goto command prompt and type lsnrctl and hit enter

LSNRCTL> SET LOG_DIRECTORY X:/oracle/admin/
LSNRCTL> SET LOG_FILE XXXX.log

  For linux or unix connect as oracle user and then type lsnrctl and hit enter

LSNRCTL> SET LOG_DIRECTORY /XXX/oracle/admin
LSNRCTL> SET LOG_FILE XXXx.log

  after doing this now configure the listner if not configured from netmanger and start it.you should have permissions to directory which u are giving in set log_directory command.

(original grammar, spelling and punctuation preserved)

EDIT 14-Apr-2016
That link appears to be dead now, use this one for reference instead.