Should the host machine always be restarted after upgrading an SQL Server 2008 instance? My organization recently upgraded all its production SQL Server 2008 instances to Service Pack 3, Cumulative Update 1. It's my responsibility to make sure that our development database environments are running the same version as production does, while minimizing downtime and disruption for our developers.

Each development environment contains six SQL Server instances. We have eight development environments shared equally between two machines. For each instance, I have to install Service Pack 3, then install Cumulative Update 1. Each installer restarts the instance's Windows service during the upgrade process, so I have to give advance warning to developers using the instance. The installer takes a long time to set itself up, so I'm upgrading each environment as a batch to quicken the process.

Because one machine hosts several instances, a restart potentially disrupts all the developers using the machine, even if their instance is not being upgraded. Therefore, I want to avoid unnecessary restarts. But only on the first run did the install tell me "You must restart the computer to complete SQL Server Setup".

Where the installer does not tell me that I must restart the machine, I don't want to a restart unnecessarily. My senior fellow DBA told me that I should restart the host machine anyway, to keep the environment 'clean'.

What bad things could happen if I don't restart the host machine after upgrading an instance?

There can be some memory usage downgrade, especially if Sql Server uses Large Page Extensions, otherwise - all the issues are minimal and negligible.


SSIS changes SQL Server source data type from date to WSTR I want to work with a table from SQL Server in an SSIS data flow. One of the columns of the table is date type. When I import it to SSIS using OLE DB source component, SSIS changes the metadata of this column to WSTR.

Do you have any idea how to fix it? I think that it never happened before. I know I can force SSIS to change the data type in advanced editor, but shouldn't SSIS "know" that this is date type column and convert it into DT_DATE?

I use SQLOLEDB.1 as the provider. It's SQL Server 2012 and the data type of the column is just date.

You are using SQLOLEDB.1 which is a pre-SQL 2008 version of the driver (and deprecated), and the date data type was introduced in SQL 2008.

I suspect the provider isn't detecting the data type as it should, and you should probably switch to the more recent SQL Native client provider.

To illustrate this point, I created an SSIS package with OLE DB connection managers using both SQLNCLI11.1 and SQLOLEDB.1. The Data Flows will both use a  query of SELECT CAST('2017-02-27' AS date) AS DateType;

Looking at the metadata, we see SQLNCLI correctly determines the date datatypes

The SQLOLEDB is unable to determine the date datatype and falls back to the "safe" type of unicode string


Select Sum from two joined tables There are structures:

CREATE TABLE `invoices` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`date` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `invoices` VALUES (1,'2018-09-22');

CREATE TABLE `products` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`invoice_id` int(10) unsigned NOT NULL,
`amount` decimal(10,2) unsigned NOT NULL,
`quantity` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `products` VALUES (1,1,150.00,2),(2,1,60.00,3),(3,1,50.00,1);

CREATE TABLE `payments` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`invoice_id` int(10) unsigned NOT NULL,
`amount` decimal(10,2) unsigned NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `payments` VALUES (1,1,400.00,'2018-09-23'),(2,1,80.00,'2018-09-23');

I have this query:

select i.id, sum(pr.amount * pr.quantity) as productAmount,
sum(pm.amount) as paymentAmount
from invoices as i
left join products as pr on pr.invoice_id=i.id
left join payments as pm on pm.invoice_id=i.id
group by i.id

and have this result:

+----+---------------+---------------+
| id | productAmount | paymentAmount |
+----+---------------+---------------+
|  1 |       1060.00 |       1440.00 |
+----+---------------+---------------+
1 row in set (0,00 sec)

However, I want to get the following result:

+----+---------------+---------------+
| id | productAmount | paymentAmount |
+----+---------------+---------------+
|  1 |        530.00 |        480.00 |
+----+---------------+---------------+
1 row in set (0,00 sec)

I want sum amount of products and sum amount of payments grouped by invoice.id.
What should be the query in this case?

By the addition join on payments, of which there are two, the productAmount got doubled:

select i.id, sum(distinct pr.amount * pr.quantity) as productAmount,
sum(distinct pm.amount) as paymentAmount
from invoices as i
join products as pr on pr.invoice_id=i.id
join payments as pm on pm.invoice_id=i.id
group by i.id;

Also replaced LEFT JOIN with JOIN as you don't care about non-matching entries.


How to test MySQL master-slave replication latency and get the exact time of slave library latency (accurate to milliseconds) How to test MySQL master-slave replication latency, I need to get the exact time of slave library latency (accurate to milliseconds)

INSERT INTO latency (master, slave) VALUES
    (NOW(3),      -- same time on Master and Slave
     SYSDATE(3))   -- time at which this query was run on each

Then diff them to see the latency.

Caveats:

Running with --sysdate-is-now would prevent the trick from working.  Test via SELECT NOW(6), SYSDATE(6);.
I have not tested this.
SYSDATE is "unsafe for replication" (but we are taking advantage of that!); however, the binlog_format may impact it.


Getting all undeclared variables from query Imagine you try to run the following query:

SELECT *
FROM dbo.Customers c
WHERE id = @id
    AND dateTimeCreated = @date

You'll get an error message along the lines of Must declare the scalar variable "@id".

As I understand it, this happens during the initial parsing of the query, before any code is executed.

Is it possible to get SQL Server to report all the missing variables at once, or is this just how the query parser works?

Error list

The error list might give you what you need:

You could open View --> Error List in ssms

To get all the build + intelliSense errors:

Differences between error list & the messages tab on query execution:

Error list

  The Error List contains all syntax and semantic errors found by
  IntelliSense in the Database Engine Editor. This list of errors is
  dynamically updated as you edit Transact-SQL scripts. The list
  includes all errors that the editor has found in each Transact-SQL
  script. The editor does not stop parsing a file after encountering
  errors in a script. In SQL Server 2008, IntelliSense in the Database
  Engine Editor does not support all Transact-SQL syntax elements. The
  Error List contains only errors from the Transact-SQL syntax that is
  supported by IntelliSense.

Messages Tab

  The Messages tab at the bottom of the Database Engine Query Editor
  window displays all errors and messages that are returned by the SQL
  Server Database Engine when a Transact-SQL script is executed. This
  list does not change until you execute the script again. The Database
  Engine stops parsing a batch after it finds one or two compile errors;
  therefore, the Messages tab might not list all errors in a script.
  Source

So to answer the question:

  Is it possible to get SQL Server to report all the missing variables
  at once, or is this just how the query parser works?

Using general query excecution this is not possible as the parsing stops after one or two compile errors, but you could use the error list.


Prevent Data Sources from being deployed outside designated folder Before I just go ahead and put a really awful check constraint on the Catalog table, I wanted to solicit some better ideas first.

I want to ensure that all shared data sources on our report server are deployed to "/Data Sources". Every now and then, we get one mistakenly deployed to some other directory (particularly if it's a report upgraded from SSRS 2000, which didn't allow specifying a different data source deployment location).

I can put an ugly check constraint on Catalog (Type != 5 OR ParentID = 'GUID of /Data Sources directory', or similar) if it comes down to it, but if there's a better option, I'd rather use that.

Why not change permissions so that folk can't deploy datasources to anything other than this folder?

So, remove "Manage data sources" from all folders except for /Data Sources. This can be done at root level and then set custom permissions on /Data Sources

You may need to setup a custom role for this if you can't change the existing ones.


Understanding pagefile.sys % Usage performance monitor (perfmon) counter correctly Lately we run into performance inssues and I started tracking some recommended sql, memory, disk and cpu counters. I informed myself about the recommended values and numbers to look at.
One of the counters is :

pagefile.sys % Usage

We had really small values like 0,5 in the beginning of collecting metrics. When it came to performance problems we reached numbers like 83,69

Does that mean 83,69% was swapped from memory to disk from the available page file? I am kind of new to this and would love to understand what's going on in order to prevent.

pagefile.sys % Usage shows total system committed not what currently is utilized. This value can increase due to load when system finds out it has to back process with more page file.
Have a look at below link for detailed explanation

http://blogs.technet.com/b/perfguru/archive/2008/01/08/explanation-of-pagefile-usage-as-reported-in-the-task-manager.aspx

I would like to know what problem you are facing


Find jobs that succeeded, but had steps that failed Is it possible to find jobs that succeeded, but that had steps that failed like the desired output shows?  I'm new to this side of development and I'm not sure about all of the system tables, etc.  If it is possible, what tables would you use to construct the query?

I'm not sure where to post 'is this possible' type questions.  If this is the wrong place, just let me know and I can take it down.  Thanks for your help.

Desired Output

You need to analyze data in the jobstephistory table.

You can use following query, it is slightly modified script from Analyzing SQL Agent Job and Job Step History in SQL Server by Atif Shehzad:

--Script # 1: To generate steps history of all jobs USE msdb GO

SELECT j.name JobName,h.step_name StepName,
CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate,
STUFF(STUFF(RIGHT('000000' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunTime,
h.run_duration StepDuration,
case h.run_status when 0 then 'failed'
when 1 then 'Succeded'
when 2 then 'Retry'
when 3 then 'Cancelled'
when 4 then 'In Progress'
end as StepExecutionStatus,
case jobsts.run_status when 0 then 'failed'
when 1 then 'Succeded'
when 2 then 'Retry'
when 3 then 'Cancelled'
when 4 then 'In Progress'
end as JobExecutionStatus,
h.message MessageGenerated
FROM sysjobhistory h inner join sysjobs j
ON j.job_id = h.job_id and h.step_id !=0
inner join sysjobhistory jobsts
on j.job_id = jobsts.job_id and jobsts.step_id =0
WHERE
jobsts.run_status = 1
and h.run_status != 1
ORDER BY j.name, h.run_date, h.run_time
GO


How to optimize performance for dimension attributes? I'm looking to enhance the cube processing time and someone suggested me to set "AttributeHierarchyEnabled='False'" for attributes which were not used at user defined hierarchies but I'm not clear with the "AttributeHierarchyEnabled" property and it's functionalities.

Can anybody just explain me when to set

  AttributeHierarchyEnabled='True'

and

  AttributeHierarchyEnabled='False'

and it's effects ?

Also I would like to know the use of "AttributeHierarchyVisible" and "IsAggregatable" properties.

Please give me some information about it which is going to be very useful for me.

MSDN states:

  The value of the AttributeHierarchyEnabled property determines whether an attribute hierarchy is created. If this property is set to False, the attribute hierarchy is not created and the attribute cannot be used as a level in a user hierarchy; the attribute hierarchy exists as a member property only. However, a disabled attribute hierarchy can still be used to order the members of another attribute. If the value of the AttributeHierarchyEnabled property is set to True, the value of the AttributeHierarchyVisible property determines whether the attribute hierarchy is visible independent of its use in a user-defined hierarchy.

IsAggregatable

  By default, an (All) level is defined for all attribute hierarchies. To disable the (All) level for an enabled attribute hierarchy, set the value for this property to False.

  If we set AttributeHierarchyEnabled to False, we cannot add that Attribute to a user defined hierarchy, we restrain SSAS from making any MOLAP-storage optimizations for this attribute. When we use such attributes in an MDX query as Attribute Hierarchy, we get no results. Although we can still use this as a member property in a query using

 Member_Expression.Properties(Property_Name [, TYPED])

According to Dustin Ryan's post:

  This will prevent end users from slicing with the attribute, reducing storage requirements and increasing performance.

If we set AttributeHierarchyVisible to False, the Attribute Hierarchy is active but stays hidden when a user browses the cube while you can still use it in MDX query.

If we set IsAggregatable to False, SSAS does not create the aggregated All member for us for that attribute.

Relevant MSDN thread that might be useful:
Dimension attribute with AttributeHierarchyEnabled=true and AttributeHierarchyVisible=false


Delete unused indexes from Microsoft Dynamics CRM Application So I found tons of unused indexes from our CRM application with this query from Pinal:

SELECT
o.name AS ObjectName
, i.name AS IndexName
, i.index_id AS IndexID
, dm_ius.user_seeks AS UserSeek
, dm_ius.user_scans AS UserScans
, dm_ius.user_lookups AS UserLookups
, dm_ius.user_updates AS UserUpdates
, p.TableRows
, 'DROP INDEX ' + QUOTENAME(i.name)
+ ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement'
FROM sys.dm_db_index_usage_stats dm_ius
INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID
FROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) p
ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1
AND dm_ius.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC
GO

I was reading about the CRM application and how it creates it's own indexes and etc.

I don't know if this is the right place to ask a question about Microsoft CRM but, is it safe to drop those indexes with 0 seeks, lookups and scans with tons of updates and rows?

I can find problems it they hard coded that index inside the source code, but, is this a good pratice that microsoft uses?

Note: I can close this post if this is not the place to ask this question.

I  don't know if this is the right place to ask a question about
  Microsoft CRM but, is it safe to drop those indexes with 0 seeks,
  lookups and scans with tons of updates and rows?

That will depend on multiple factors only you will know. (Both SQL Server & Microsoft
Dynamics CRM related)

SQL Server

When was the server last restarted?

Make sure this index usage data has enough history to go on as it will reset after each instance restart.

Also, since you are using sql server 2012, the data could have been reset after an index rebuild if your version is below SP2+CU12 or SP3+CU3. (Which hopefully is not the case)

More on that in this blogpost by Kendra Little

If either the data is reset or the restart was recent, consider logging the information to a table and reporting on these results instead.

You  could opt to do this regardless.

Are there rarely used queries that need these indexes?

This is harder to track and is also more unlikely, but could still happen.

For more concerns in general to dropping indexes, read this and this or many more  (online) sources.

Microsoft Dynamics CRM

Is it an out of the box or user created index?

According to this post there are indexes you can and can't remove.

  ... Be careful not to create too many indexes, which can affect the insert
  and update performance. Balance indexing needs according to business
  requirements, indexes that have a lot of writes and no reads (or very
  few) are not efficient and are considered a burden to the system
  (redundant indexes).  So it is a good idea when working with missing
  indexes to give it some time (depending on your operations cycle maybe
  a month) and off set them against a redundant indexes query.  However,
  please remember removing or editing OOB indexes is NOT supported.

This simply means that you cannot drop the indexes that are included by MS Dynamics CRM (Out of the box).

You can drop your own / user created indexes after veryfing these are, in fact, user created indexes.

As always, document any changes you make to these user created indexes and monitor the impact.


Best practices on common person fields (Name, email, address, gender etc...) What are the most common best practices on length and data type on common fields like:

First Name
Last Name
Address
Email
Sex
State
City
Country
Phone Number

etc....

I would tend to be very suspicious of any set of universal best practices because, for most of these fields, the devil is in the details.  Just because the information is relatively common doesn't mean that your application uses the data in exactly the same way that other applications use it.  That means your data model may need to be slightly different.

First & last name: Why are you capturing the name?  If you have a requirement to capture a person's full legal name (i.e. you are preparing legal documents or birth certificates), you probably want to allow more space for people to type than you would if you're just asking for a person's name so you have something to call them in your new web app.
Address: What are you going to do with the address?  What sort of addresses are you storing?  If you are storing the address of a property in the United States that you're creating a mortgage on, you likely care very much about getting a fully standardized address in which case the data model will probably want to hew very closely to whatever your address standardization tool returns.  If you just want people to be able to type in an address to deliver a product, a couple lines for freeform text is probably sufficient.  The length of the lines there may depend on the requirements of the downstream processes that do things like print address labels.
State: Assuming you can identify the valid state values, it probably makes sense to create a STATE table and create a foreign key relationship between the STATE and ADDRESS tables.  But the ability to identify the valid values implies that you're limiting the set of valid addresses at least to a particular set of countries.  That's fine for many sites but then you've got to do a bit of work to support a new country.
City: If you are dealing with data where there are potentially city-level regulations in place (i.e. where there are different sorts of tax rates that are applied based on the city), you may want to treat it much like the state and have a CITY table with the valid cities and a foreign key relationship between the CITY and ADDRESS tables.  On the other hand, if you're just trying to get a product delivered and you don't much care if you have various versions of the same city in your table, letting the user free-form enter text is sufficient.  Of course, if you are storing foreign keys, you'll have a fair amount of work to make sure that you have all the valid values.  But there are products where the whole point is that the company has already done that work (i.e. sales tax databases).
Phone: What are you doing with phone numbers and why?  Some applications will want to take in phone numbers in whatever format the user decides to enter them and preserve that formatting for all subsequent queries.  This would be common if you are designing a personal address book where users have their own preferences for how phone numbers are stored and displayed.  Other applications would want to ignore the formatting that is entered, extract only the numeric characters, and then format the data on retrieval so that all phone numbers have similar formatting.  If you're catering to businesses, you may want a separate field for users to enter an extension.  If you are trying to support an outbound calling process, you may want to store the area code and country code in separate columns because you'll want to make sure that you have time zone specific windows for calling people in different area codes (making a call to someone in the Eastern time zone at 10am is going to go over much better than making that same call to someone in the Pacific time zone where it is 7am).
Gender: For a great many applications, it's perfectly reasonable to store a gender code ('M' or 'F') in a table.  On the other hand, there are cases when you may want additional options (Other, Intersex, Transgendered) or where you need to store something like the gender at birth and the current gender.


Low CPU Utilization but High Signal Waits I have a server with 16 CPUs that is configured with a max degree of parallelism of 8 and a max worker threads setting of zero.

For a given hour, my signal waits were 20% yet my OS CPU utilization during that time never went over 25%. Can someone explain why my signal waits were so high?

My vendor has a best in class scoring system that expects us to be at 10% or less signal waits, or we get dinged. How can I go about fixing this (without adding additional CPUs)?

We do not have more than 8 CPUs per NUMA node, so Trace Flag 8048 does not apply.
The largest instance waits are CXPACKET (70%),then PREEMPTIVE_OS_PIPEOPS (20%)
cost threshold for parallelism is set to 50. Should I raise it? To what?
This is a physical machine (not a VM), dedicated to SQL Server.
I am using a monitoring tool to identify the most frequently run queries and procedures. Do I want to look at high CPU, high I/O, or high duration? Normally our app is I/O intensive so I tune high I/O. But since the issue is signal waits, do I need to look at high CPU?
I was hoping to avoid Max Vernon's recommendation to lower MAXDOP to 4, because the app does some warehouse style queries that need the extra threads.

Community Wiki answer generated from comments on the question by Aaron.

Unless you are having performance issues, the high percentage of CXPACKET waits may only be an indicator that a large portion of the queries are going parallel and not actually a problem.

What is the CXPACKET wait type and how do you reduce it?

High CPU could be a indicator, but from what you have explained I would say look at queries with long durations but low CPU to start with. The CXPACKET wait is sometimes associated with a query waiting for all of the threads to finish before it can merge the results (data skew).

If you are able to modify the queries and procedures, you could set a higher MAXDOP for the warehouse tasks that need it, and set the global MAXDOP lower. However, I would only do that as a last resort. You only really want to make those types of explicit hints when you've exhausted all of the possibilities or you can't make changes to the code, queries or database schemas.


32 bit vs 64 bit system - Informix Client SDK If I create an Informix SSIS package in my 32 bit system using 32 bit Client SDK and deployed it in 64 bit server. Will it create issue?

If a package is using 32-bit components (e.g. OLEDB libraries), you will need to choose the "32-bit runtime" option on the Advanced tab when running the package on a 64-bit server.

(I know this is an old question; just adding an answer for posterity.)


SQL Server Log Entry Gathering? I'm trying to better understand how SQL server does log buffering and flushing in order to better balance costs and performance.  I know that unless you use delayed durability, log entries for a transaction will be flushed all the way to disk at commit time.  However, what I'm really curious about is what else might get flushed along with that.  For example, if the log disk is already busy flushing the entries for a previous transaction (and thus can't flush for the current transaction yet), will entries for other transactions also get flushed to disk at the same time, or will the entries for each transaction get flushed serially.  Given that in most cases the actual flushing to disk is by far the most time consuming operation and the fact that for logging operations, the time to flush is affected only minimally by the size of the flush (at least for the frequent case of many small transactions), it would seem like a good optimization for the log processor to pipeline flushes such that while one flush is happening, entries from any committed transactions awaiting a flush are gathered together and flushed together rather than one at a time.  This would improve throughput with little impact on the latency of the first transaction (it might even improve average latency).  Does anyone know if such an optimization has been made in SQL Server?  If it has been made in a specific release, can you identify the release and where you found that information?

However, what I'm really curious about is what else might get flushed along with that.

Any log lower than the LSN currently requesting to be flushed will be flushed as well. Log records are put into the log buffer in order and all transactions are serialized to the log buffer for that database. Note that individual log records aren't flushed, they are put into log blocks and log blocks are flushed.

  Does anyone know if such an optimization has been made in SQL Server?

It's called group commit, and it stalls the normal commit for a few ms if it can group more together for better overall performance (roughly-ish speaking). I believe it was added in 2008R2.


Dynamic Data Masking Doesn't Seem To Work Correctly With ISNULL Here is the code to reproduce the issue:
CREATE TABLE [dbo].[EmployeeDataMasking](
    [RowId] [int] IDENTITY(1,1) NOT NULL,
    [EmployeeId] [int] NULL,
    [LastName] [varchar](50) MASKED WITH (FUNCTION = 'partial(2, "XXXX", 2)') NOT NULL,
    [FirstName] [varchar](50) MASKED WITH (FUNCTION = 'partial(2, "XXXX", 2)') NOT NULL,
 CONSTRAINT [PK_EmployeeDataMasking] PRIMARY KEY CLUSTERED
(
    [RowId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
) ON [PRIMARY]
GO

Insert Into dbo.EmployeeDataMasking (EmployeeId, LastName, FirstName)
VALUES( 1,'Smithsonian','Daniel'),( 2,'Templeton','Ronald')

-- Partial data masking works correctly here
Select
    EmployeeId,
    LastName,
    FirstName
From dbo.EmployeeDataMasking

-- Partial data masking does not work correctly here. Full masking is used.
Select
    EmployeeId,
    ISNULL(LastName,'') as LastName,
    ISNULL(FirstName,'') as FirstName
From dbo.EmployeeDataMasking

It appears, when using ISNULL, the partial masking is being converted to a full (default) mask. Is this the expected behavior?
Update: I discovered that COALESCE does not exhibit this problem. These queries return the expected results:
Select
    EmployeeId,
    COALESCE(LastName,'') as LastName,
    COALESCE(FirstName,'') as FirstName
From dbo.EmployeeDataMasking

Using Azure SQL Database

As a test, I created a custom user function that does nothing except pass the string back to the caller; it also exhibits this behavior.
Be aware, this code will drop users and objects, so only run it in a test database.
First, we create the required objects, users, and insert some data:
IF OBJECT_ID(N'dbo.EmployeeDataMasking', N'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.EmployeeDataMasking;
END
IF OBJECT_ID(N'dbo.SomeFunc', N'FN') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.SomeFunc;
END
GO

IF EXISTS (
    SELECT 1
    FROM sys.database_principals dp
    WHERE dp.name = N'SomeUser'
    )
BEGIN
    DROP USER [SomeUser];
END
CREATE USER [SomeUser] WITHOUT LOGIN;
ALTER ROLE db_datareader ADD MEMBER [SomeUser];

CREATE TABLE [dbo].[EmployeeDataMasking](
    [RowId] [int] IDENTITY(1,1) NOT NULL,
    [EmployeeId] [int] NULL,
    [LastName] [varchar](50) MASKED WITH (FUNCTION = 'partial(2, "XXXX", 2)') NOT NULL,
    [FirstName] [varchar](50) MASKED WITH (FUNCTION = 'partial(2, "XXXX", 2)') NOT NULL,
 CONSTRAINT [PK_EmployeeDataMasking] PRIMARY KEY CLUSTERED
(
    [RowId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
) ON [PRIMARY]
GO

GO
CREATE FUNCTION dbo.SomeFunc(@i varchar(50))
RETURNS varchar(50)
AS
BEGIN
    RETURN @i;
END
GO
ALTER AUTHORIZATION ON dbo.SomeFunc TO [SOmeUser];
GRANT EXECUTE TO [SomeUser];
GRANT SHOWPLAN TO [SomeUser];

Insert Into dbo.EmployeeDataMasking (EmployeeId, LastName, FirstName)
VALUES( 1,'Smithsonian','Daniel'),( 2,'Templeton','Ronald')

Next, we'll query the data from the perspective of the non-privileged user:
EXECUTE AS USER = N'SomeUser';
Select
    EmployeeId,
    ISNULL(LastName,'') as LastName,
    ISNULL(FirstName,'') as FirstName,
    dbo.SomeFunc(LastName) as LastName,
    dbo.SomeFunc(FirstName) as FirstName,
    COALESCE(LastName,'') as LastName,
    COALESCE(FirstName,'') as FirstName
FROM dbo.EmployeeDataMasking
REVERT

The results:
╔════════════╦══════════╦═══════════╦══════════╦═══════════╦══════════╦═══════════╗
║ EmployeeId ║ LastName ║ FirstName ║ LastName ║ FirstName ║ LastName ║ FirstName ║
╠════════════╬══════════╬═══════════╬══════════╬═══════════╬══════════╬═══════════╣
║          1 ║ xxxx     ║ xxxx      ║ xxxx     ║ xxxx      ║ SmXXXXan ║ DaXXXXel  ║
║          2 ║ xxxx     ║ xxxx      ║ xxxx     ║ xxxx      ║ TeXXXXon ║ RoXXXXld  ║
╚════════════╩══════════╩═══════════╩══════════╩═══════════╩══════════╩═══════════╝
As you can see, the custom user function, dbo.SomeFunc exhibits the same behavior as ISNULL.  At this point, I'm unsure why, since COALESCE does not exhibit the unwanted behavior.


Difference between OCCI and Instant Client package Oracle I am a bit confused. I want to use Oracle Call Interface in C++ to interact with databases. This link: http://oradim.blogspot.co.uk/2009/07/getting-started-with-occi-windows.html says you need such packages:

OCCI 11.1.0.6 (Visual C++9 (VS 2008)[Windows 32-bit])
Instant Client Package - Basic: instantclient-basic-win32-11.1.0.6.0.zip
Instant Client Package - SDK: instantclient-sdk-win32-11.1.0.6.0.zip

The 2nd and 3rd I have downloaded from here: http://www.oracle.com/technetwork/topics/winsoft-085727.html.

My question is basically: What is the number 1 (OCCI 11.1.0.6) item on the list and where can I download it from? I have been looking for solution for this problem, but could not figure it out. Help appreciated. Is the first item separate from 2nd and 3rd items?

I googled 'occivc9win32_111060.zip' and found "OCCI Downloads - Oracle" :
http://www.oracle.com/technetwork/database/occidownloads-083553.html

Maybe you have to register for the download.


What is SQL Server doing in the background once a Backup/Restore is complete? When I run a backup or restore through the SSMS GUI, the dialog shows Executing with 20% increments rather than every 1% tick. To get round this, we can use DMV's. Thing is, when the query shows 100% complete, the SSMS GUI remains for a good 5 or so minutes before the 'Restore complete' dialog appears.

Question is, what is SQL server doing during this time? Some kind of integrity check? Index rebuild? Activity monitor shows 0 R/W on the .MDF whilst the GUI dialog persists.

Database restore process is performed in 3 steps:

copy -Copy data from the backup
redo - commit completed transactions in T-Log
Undo - Rollback incomplete transactions

Restore process doesn't perform any maintenance tasks like index rebuild etc.

  When I run a backup or restore through the SSMS GUI, the dialog shows
  Executing with 20% increments rather than every 1% tick.

If you have used T-SQL statements to backup your database, you would have been aware of STATS parameter. It take int as input and prints what % of the backup have been processed.

Ex:-

If we use STATS=5 then it'll print restore status after every 5% completion. Like

5%
10%
15%
..

Post database restore it's good practice to execute DBCC CHECKDB. I would prefer to use PHYSICAL_ONLY.

When a log is restored, it also takes some time to zero out the log file.  If the log file is large, this can take several minutes.


Can I activate PITR after the DB has been used My Postgres-Server is running for quite a while and contains probably 4-5 GB of data.

Backups are done using dumps, and this works ok. Though not most efficient, it just works.

Due to future growth I like to activate PITR backups (like RMAN in Oracle).

Is that possible and/or recommended? Or would it be better for me to recreate the DB with the correct settings upfront?

No need to recreate the database.  Set up archive_mode and archive_command, restart the server, create a base backup, and there you go.  (Details vary depending on version.)

It is quite common to "upgrade" from pg_dump to PITR backups as the database grows.


Deny users permission to create tables in master I have noticed that I can create tables, schemas, stored procedures etc. the master database.

I don't know what is granting this permission. It's annoying, because when we open a *.sql file in SSMS, it's related by default to master. If a CREATE operation is executed without noticing that and setting the correct database context, the table is created in master.

If we didn't have the permission, we'd get an error, notice it and change the database context. With the permission, the table is created and we only see the mistake later, and need to drop it and recreate. I noticed that there are already some tables created by mistake in master.

I want to revoke that permission from every human user, but I fear breaking something. I don't know if some SQL Server routine needs permission to create there.

What's the best way to revoke create permission from master without breaking SQL Server?

You'll need to determine how the logins have access to run DDL in master.  The following query will return a list of logins with server-level role membership:

SELECT ServerName = @@SERVERNAME
    , RoleName = roles.name
    , MemberName = members.name
    , IsEnabled = CASE WHEN members.is_disabled = 1 THEN 0 ELSE 1 END
FROM sys.server_role_members srm
    INNER JOIN sys.server_principals roles ON srm.role_principal_id = roles.principal_id
    INNER JOIN sys.server_principals members on srm.member_principal_id = members.principal_id
WHERE members.name NOT LIKE 'NT %' --exclude builtin principals
ORDER BY roles.name
    , members.name;

You can use xp_logininfo to understand how a particular Windows account has access to SQL Server:

EXEC xp_logininfo @acctname = 'DOMAIN\Username', @option = 'all';

If the people in question have membership in the sysadmin role, you won't be able to prevent them from running DDL statements in master (or anywhere else for that matter).

Presuming the people are not members of the sysadmin server-role, then DENY permissions take precedence over permissions that have been GRANTed.

You could create a Windows security group, named "NoMasterDDL" and add your developers to that group.  Create a login for the group, then DENY permissions on the DDL statements you don't want people to perform in master.

USE master;
CREATE LOGIN [DOMAIN\NoMasterDDL] FROM WINDOWS;
CREATE USER [DOMAIN\NoMasterDDL] FOR LOGIN [DOMAIN\NoMasterDDL];
DENY ALTER ON DATABASE::master TO [DOMAIN\NoMasterDDL];

The above will prevent members of the [DOMAIN\NoMasterDDL] group from performing any DDL type statements, such as CREATE TABLE.

As I said in answer to your previous question, and AMtwo mentioned in a comment:

If a user is currently a member of sysadmin, any DENY permissions will have no effect. Particularly in a development environment, developers might have such access. If so, the CONTROL SERVER permission can be used instead; it is the same as sysadmin, but obeys DENY permissions.

So, you can grant CONTROL SERVER instead of making them members of the sysadmin server role, then use DENY ALTER as shown above to prevent DDL in the master database.


What is Report ID used for in SSRS reports? While looking the XML file of my solution I saw this

Does this ID have any practical use or is only for internal use?

I assume that is a local .rdlc file.

IMHO it has not practical use, in fact you can copy & paste as a new report using the same ReportID and it works.

Is it safe to create new .RDLC report as a copy of existing .RDLC report?

If you have a look at: RDL XML Schema for Version 2016/01 you won't find any reference to the <ReportID> tag, or if you get the RDL definition of any report stored in SSRS database, (Where does a published RDL file sit?), you won't be able to find this tag into the file definition.


Setting up a SQL Server cluster for the first time, some questions! I am a SharePoint architect but looking to implement AOAG with SharePoint in an Azure lab environment.

I'm building the first node in a cluster and I'm a little confused:

I have my cluster object in AD, the installation asks me for a network name for SQL Server. What's the difference between this and the cluster object name?
Likewise, also the cluster resource group name.
Lastly, what's the IP address specified for?

The network name is a virtual client access point for the instance of SQL Server on the failover cluster, and it needs to be unique on the domain.  This will be how you will connect to the instance.

The Cluster resource group is the name by which everything is grouped in the Windows Failover Cluster Manager, you will typically want this named similarly to what you named the instance.

The IP you provide will be tied to the network name you provided and needs to be unique as well.


MySQL Count rows from another table for each record in table SELECT
  student.StudentID,
  student.`Name`,
  COUNT(attendance.AttendanceID) AS Total
FROM
  student
LEFT JOIN attendance ON student.StudentID = attendance.StudentID

I am trying to count the last row but instead it counts all the results and returns one result

I am getting something like

as there are multiple records because there are two entries in Attendance ID for K1052280 I want to count those and return the number. Something like

You are just missing the GROUP BY

YOUR QUERY WITH GROUP BY

SELECT
  student.StudentID,
  student.`Name`,
  COUNT(attendance.AttendanceID) AS Total
FROM
  student
LEFT JOIN attendance ON student.StudentID = attendance.StudentID
GROUP BY student.StudentID,student.`Name`;

SAMPLE DATA

DROP DATABASE IF EXISTS alishaikh; CREATE DATABASE alishaikh;
USE alishaikh
CREATE TABLE student
(
  StudentID CHAR(8) NOT NULL,
  Name VARCHAR(40),
  PRIMARY KEY (StudentID)
);
INSERT INTO student (StudentID,Name) VALUES
('k1052280','Ali Shaikh'),('k1052287','McKenzie Roth'),
('k1052288','Dacey Sullivan'),('k1052294','Zelda Cantu'),
('k1052295','Kimberly Melton'),('k1052296','Tatianna Cantrell'),
('k1052297','Morgan Thornton'),('k1052298','Allistair Barlow'),
('k1052299','Troy Fulton');
CREATE TABLE attendance
(
  AttendanceID INT NOT NULL AUTO_INCREMENT,
  StudentID CHAR(8) NOT NULL,
  PRIMARY KEY (AttendanceID),
  KEY (StudentID)
);
INSERT INTO attendance (StudentID) VALUES
('k1052280'),('k1052280'),('k1052287'),('k1052287'),
('k1052288'),('k1052295'),('k1052295'),('k1052295');

SAMPLE DATA LOADED

mysql> DROP DATABASE IF EXISTS alishaikh; CREATE DATABASE alishaikh;
Query OK, 2 rows affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
mysql> USE alishaikh
Database changed
mysql> CREATE TABLE student
    -> (
    ->   StudentID CHAR(8) NOT NULL,
    ->   Name VARCHAR(40),
    ->   PRIMARY KEY (StudentID)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO student (StudentID,Name) VALUES
    -> ('k1052280','Ali Shaikh'),('k1052287','McKenzie Roth'),
    -> ('k1052288','Dacey Sullivan'),('k1052294','Zelda Cantu'),
    -> ('k1052295','Kimberly Melton'),('k1052296','Tatianna Cantrell'),
    -> ('k1052297','Morgan Thornton'),('k1052298','Allistair Barlow'),
    -> ('k1052299','Troy Fulton');
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE attendance
    -> (
    ->   AttendanceID INT NOT NULL AUTO_INCREMENT,
    ->   StudentID CHAR(8) NOT NULL,
    ->   PRIMARY KEY (AttendanceID),
    ->   KEY (StudentID)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO attendance (StudentID) VALUES
    -> ('k1052280'),('k1052280'),('k1052287'),('k1052287'),
    -> ('k1052288'),('k1052295'),('k1052295'),('k1052295');
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

YOUR QUERY WITH GROUP BY EXECUTED

mysql> SELECT
    ->   student.StudentID,
    ->   student.`Name`,
    ->   COUNT(attendance.AttendanceID) AS Total
    -> FROM
    ->   student
    -> LEFT JOIN attendance ON student.StudentID = attendance.StudentID
    -> GROUP BY student.StudentID,student.`Name`;
+-----------+-------------------+-------+
| StudentID | Name              | Total |
+-----------+-------------------+-------+
| k1052280  | Ali Shaikh        |     2 |
| k1052287  | McKenzie Roth     |     2 |
| k1052288  | Dacey Sullivan    |     1 |
| k1052294  | Zelda Cantu       |     0 |
| k1052295  | Kimberly Melton   |     3 |
| k1052296  | Tatianna Cantrell |     0 |
| k1052297  | Morgan Thornton   |     0 |
| k1052298  | Allistair Barlow  |     0 |
| k1052299  | Troy Fulton       |     0 |
+-----------+-------------------+-------+
9 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!


Redundant MySql Configuration I'm trying to figure out the best way to configure MySql so that I can have redundancy - so for example - I would have two servers, both through the same host, but at different datacenters in the event one dc has limited to no availability.

In my perfect world scenario I would do updates to Server 1- reboot and not worry about the DB going down as Server 2 would still be online. When Server 1 comes back, I could update Server 2, reboot, etc etc. You get the idea. Basically - having one Server pick up and take over if the other goes offline - or vice versa.

I've read about Master/Slave setup - however it doesn't sound like this will solve my problem as Slaves are only written to by the master.

I've also read about Master Master setup - which I had originally thought was the ticket - however, after further research i came across articles basically stating that writing to Server 2 while Server 1 was done in a Master - Master setup could be detrimental (I can't remember the exact reasoning) - so that scared me away from using the Master Master setup.

I'm not sure if what I want to accomplish is possible - in my ongoing research I can't seem to find a solid - "this is what you want to do" article. Would love any advice on how I could accomplish what i'm looking for (if its possible).

Thank you in advance.

What you are looking for is in fact possible. What you are looking to do (like many businesses) is build an automatic failover which is often called a hot standby. A hot standby is just a backup slave server that can be switched to either automatically or manually when you need to do maintenance or if your master server fails.

MySQL has some great documentation on setting that up here

You may also need to take a look at replication methods for replicating your data to the slave.


Slow query with JOIN I have a stored procedure which runs pretty slow (6 sec) using the following JOIN:

JOIN tariffs t ON
(LEFT(`cdrs`.`cnumber`,7) = t.numberrange
OR LEFT(`cdrs`.`cnumber`,8) = t.numberrange)

Without the above JOIN the query runs at 1.5 sec.
Any way how to improve the performance? Full stored procedure below:

CREATE DEFINER=`xxx`@`%` PROCEDURE `GetHourStats`(IN _ID INT, IN _YEAR INT, IN _MONTH INT, IN _DAY INT)
BEGIN

set @_START = UNIX_TIMESTAMP(date(_YEAR * 10000 + _MONTH * 100 + _DAY * 1));
set @_END = UNIX_TIMESTAMP(date_add(date(_YEAR * 10000 + _MONTH * 100 + _DAY * 1), interval 1 day));

SELECT h.idhour, h.`hour` as 'hour', innumber, count(*) as `count`, sum(talktime) as `duration` FROM (
     SELECT
        `cdrs`.`dcustomer` AS `dcustomer`,
        (CASE
            WHEN (LEFT(`cdrs`.`cnumber`, 2) = "01" OR LEFT(`cdrs`.`cnumber`, 2) = "02") THEN '01-02'
            WHEN (LEFT(`cdrs`.`cnumber`, 2) = "03") THEN '03'
            WHEN (LEFT(`cdrs`.`cnumber`, 2) = "05") THEN '05'
            WHEN (LEFT(`cdrs`.`cnumber`, 2) = "06") THEN '06'
            WHEN (LEFT(`cdrs`.`cnumber`, 2) = "07") THEN '07'
            WHEN (LEFT(`cdrs`.`cnumber`, 3) = "080") THEN '080'
            WHEN (LEFT(`cdrs`.`cnumber`, 3) = "084") THEN '084'
            WHEN (LEFT(`cdrs`.`cnumber`, 3) = "087") THEN '087'
            WHEN (LEFT(`cdrs`.`cnumber`, 2) = "09") THEN '09'
        END) AS 'innumber',
        FROM_UNIXTIME(`cdrs`.`start`) AS `start`,
         (`cdrs`.`end` - `cdrs`.`start`) AS `duration`,
         `cdrs`.`cnumber` AS `calling`,
         `cdrs`.`talktime` AS `talktime`
    FROM `cdrs`
    JOIN tariffs t ON (LEFT(`cdrs`.`cnumber`,7) = t.numberrange OR LEFT(`cdrs`.`cnumber`,8) = t.numberrange)
    WHERE `cdrs`.`start` >= @_START and `cdrs`.`start` < @_END
    AND `cdrs`.`stype` = _LATIN1'external'
    AND `cdrs`.`talktime` >= 5
    AND `cdrs`.`status` = 'answer'
    AND CHAR_LENGTH(`cdrs`.`cnumber`) = 11
    GROUP BY callid
   ) cdr

   JOIN customers c ON c.id = cdr.dcustomer
   LEFT JOIN hub.hours h ON HOUR(cdr.`start`) = h.idhour

    WHERE (c.parent = _ID or cdr.dcustomer = _ID or c.parent IN
        (SELECT id FROM customers WHERE parent = _ID))

   GROUP BY h.idhour, cdr.innumber
   ORDER BY h.idhour;

END

Q: How can I make the above stored procedure run faster?

I agree with the comment about table definitions, but in a few moments you will see this does not depend on the tables.

You use SELECT from derived table cdr for JOIN and for WHERE

JOIN customers c ON c.id = cdr.dcustomer
   LEFT JOIN hub.hours h ON HOUR(cdr.`start`) = h.idhour

    WHERE (c.parent = _ID or cdr.dcustomer = _ID or c.parent IN
        (SELECT id FROM customers WHERE parent = _ID))

Derived tables do not have indexes and so these operations always will be full-scan. It may take 6 seconds. That is not too much.

The other JOIN uses derived columns:

FROM `cdrs`
    JOIN tariffs t ON (LEFT(`cdrs`.`cnumber`,7) = t.numberrange OR LEFT(`cdrs`.`cnumber`,8) = t.numberrange)

This is not the best idea.

Again, group by does not use indexed data:

GROUP BY h.idhour, cdr.innumber

cdr - does not have indexes.

Possibly faster will be to store intermediate data in a table with indexes.


Backup Files growing quickly on SQL Server 2016 Always On Cluster I have a two-node SQL Server 2016 Always On Cluster.  I also have a job that updates about 200 records in one table every 3 minutes.  Even when there are no updates, it does change a TimestampDt field with the current time.

Previously my backup files were growing at a manageable 10-20 MB per day.  However, now that I started making the TimestampDt field updates every 3 minutes (this was a recent change) my backup files are growing at a size of 200 MB per day.

I think that the backup is including the entire transaction log.

Here is the backup SQL Query:

SET @BackupUrl = CONCAT('https://backup.blob.core.windows.net/databasebackup/dbname-',GETDATE(),'.bak');
BACKUP DATABASE dbname
   TO URL = @BackupUrl
   ;

What is the recommended approach here?  Should I purge the transaction logs somehow on backup?  If so, how can I do that safely in an Always On Availability Group clustered environment?

Any help would be greatly appreciated.

What type of backup are we talking about?

Full? This only include the log records produced while the backup command is running (basically).

Differential? Same as above, as for the log records. As for data, it include the modified extents since last full backup. I.e., it will grow and grow until next full backup.

Log? It includes the log records since last log backup.

Also, what options did you specify for the backup command? If you post the backup command you use, we have some something to go on...


Mysterious conditional where clause index choices Have you got an explanation, why query optimizer is choosing different indexes and modes in this example?

/* crete objects and data for testing */

-- table
CREATE TABLE #Test (
    ID INT IDENTITY PRIMARY KEY
    ,CustNo INT NULL
    ,CustNo2 INT NULL
    );

-- populate with data
WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
INSERT INTO #Test (CustNo)
SELECT TOP (1000) n FROM Nums ORDER BY n;

-- index on CustNo
CREATE NONCLUSTERED INDEX IX__#Test__CustNo ON #Test (CustNo ASC);

/* running test */

-- variables
DECLARE @Step INT = 1;
DECLARE @FindNo INT = 5;

-- #1 - uses CX index scan
SELECT CustNo
FROM #Test
WHERE (@Step = 1 AND CustNo = @FindNo)
    OR (@Step = 2 AND CustNo2 = @FindNo);

-- #2 - uses NCX index seek
SELECT CustNo
FROM #Test
WHERE (@Step = 1 AND CustNo = @FindNo)
    OR (@Step = 2 AND CustNo2 = @FindNo)
OPTION (RECOMPILE);

-- #3 - uses NCX index seek
IF @Step = 1
    SELECT CustNo
    FROM #Test
    WHERE CustNo = @FindNo;

What you're seeing is constant folding.

More info at this link:

  Constant folding is a technique the optimizer uses to remove any unnecessary code to help improve performance. Constant Folding does this by removing unnecessary variables and simplifying the query before compiling the plan.

OPTION (RECOMPILE) allows constant folding of @Step and @FindNo in your second query. Since @Step is known to equal 1, the line OR (@Step = 2 AND CustNo2 = @FindNo) will be optimized away. Notice that this affects estimated rows.

If you want to see a more drastic example of this, try the below code. Notice how constant folding (enabled by option recompile) allows the second query plan to completely avoid the union, since the optimizer knows no rows could be returned.

CREATE TABLE #t (
id int)

INSERT #t
VALUES (1)

DECLARE @v char(5) = 'false'

SELECT TOP 1 *
FROM #t
UNION ALL
SELECT TOP 1 *
FROM #t
WHERE @v = 'true'

SELECT TOP 1 *
FROM #t
UNION ALL
SELECT TOP 1 *
FROM #t
WHERE @v = 'true'
OPTION (RECOMPILE)


I can't connect to my servers SQL database via an IP Address I have setup a server that runs Windows Server 2008, and has SQL Server 2008 Express installed.

I can connect to the machine's SQL Server Express database via the MACHINENAME/SQLEXPRESS.

However when we come to connecting through any software or script using an IP Address it won't allow the connection.

I have tried:

Turning off the Firewall.
Allowing Remote Connections for the SQL Database.
Enabling TCP/IP within the SQL Configuration.

When we attempt to connect via the software 'SQL Server Management Studio', we get the following message:

Error Message:

  An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) (Microsoft SQL Server, Error: 10061)

  A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - An established connection was aborted by the software in your host machine.) (Microsoft SQL Server, Error: 10053)

Can you please let me know when your free so we can take a look because I seem to be getting know where, I’ve amended the details as per some information UK Fast sent me but they have said "It’s not within the support remit", so they can’t help any further.

I look forward to hearing from you.

Your SQL Server is installed as named instance, so first of all try connecting to your server using the following server name: IP Address\SQLEXPRESS.
When you install SQL Server as named instance it uses dynamic TCP/IP ports by default, so it is not possible to connect to it whitout specifying instance name (just IP address). If you need to connect to your server without using instance name you have to reconfigure your server to use static TCP port. To do it please perform the following:

open SQL Server Configuration Manager;
switch to the SQL Server Network Configuration | Protocols for SQLEXPRESS;
double-click the TCP/IP protocol;
select the Yes value in the Enabled field;
switch to the IP Addresses tab;
find the IPAll section;
clear the TCP Dynamic Ports field in that section;
specify the 1433 value in the TCP Port field:

restart your server
Try to connect to your server using just its IP address.


changing IP on sql server linked server I have a remote linked server that got moved to a different IP address.
After dropping the server with sp_dropserver and add it with new URL with sp_addlinkedservers, will I have to add everything back again (username, password, etc.)?
Does sp_dropserver destroy the associated data ? Or that data is linked to the ID and kept? The server and respective SW is the same, only IP changes.

Yes, if you've dropped the linked server then you'll need to setup everything again from scratch.  You could script it out first, all you'll need to do then is change the IP address and password in the script and run it back in again.


Powershell Run Stored Procedures in Parallel in Database We want to run all stored procedures in parallel. The powershell script finds a list of stored procedures in the database (only have 5 right now), and runs them simultaneously. This code seems to be working. Is there anymore efficient or recommended method to conduct this, or does the code seem valid?

Workflow TestRunParallelExecute
{
    $ServerName = "localhost"
    $DatabaseName = "testrun"
    $Procedure_Query = "select name from sys.procedures"
    $Procedure_List = (Invoke-Sqlcmd -Server $ServerName -Database $DatabaseName -Query $Procedure_Query)

    ForEach -Parallel ($Procedure in $Procedure_List.Name)
    {
         Invoke-Sqlcmd -Server $ServerName -Database $DatabaseName -Query $Procedure
    }
}
TestRunParallelExecute
cls

I read many resources below, wanted to come up with easy way using Powershell new ForEach -Parallel function.

Run stored procedures in parallel

https://stackoverflow.com/questions/29193805/executing-stored-procedures-in-parallel

https://stackoverflow.com/questions/48803101/run-several-stored-procedure-in-parallel-in-sql-server

Regarding your question:

  Is there anymore efficient or recommended method to conduct this, or
  does the code seem valid?

I'm not aware of a more efficient or recommended method, but I believe I did validate the basic code you are using.

I created 5 identical stored procedures (SP1 - SP5) that each have a WAITFOR '00:01:00' to force the procedure to wait for 1 minute before exiting.

DROP PROCEDURE IF EXISTS [dbo].[sp1];
go
CREATE PROCEDURE [dbo].[sp1]
AS
BEGIN
    SET NOCOUNT ON;

    waitfor delay '00:01:00'
END

I tweaked your original Powershell code to only select stored procedures where the name was like SP% to make sure that only SP1 - SP5 would be selected.

I started your Powershell script and immediately went back into SSMS to execute sp_WhoIsActive so I could see whether the stored procedures were being executed in parallel and noted that all 5 stored procedures were executing.


Can You Access A Replication Database I am using SQL server 2008 R2. I have a live database, and a replicated copy of that live database on a different server.

It is using Transactional Publication replication.

When I take our software and point it at the replicated copy it doesn't work. At first it seemed like the database didn't have the correct logins under security.

BUT - that doesn't seem to be the problem. It is more likely that the database is locked down somehow because of the replication? Can someone please point me to some info to confirm that.

In management studio on the server when I am logged in as a local administrator, when I click one of the replicated databases it just gives me the error,

'The database blah is not accessible'

How can I check that the replication is working? Will I be able to back up the database and restore it somewhere else, any other suggestions welcome.

Turns out to be a permissions error. The permissions looked right from a database server point of view (Security -> Logins). These permissions as far as I can tell were moved over as part of the replication.

But when I looked at the individual databases the security under them was missing (database -> Security -> Users).

Under the database security the only users were dbo, guest, INFORMATION_SCHEMA, and sys.


AlwaysOn Availability Groups Dev, QA, and Staging Environments Our company would like to implement Always On Availability Groups. The Final PRODUCTION Plan is for Primary Replica in one state, and Secondary Replica in another state location. The secondary replica will be Read-only Asynchronous.

When creating the Devops Previous Instances, do you typically setup Alwayon Availability Groups in the  Dev, QA, or Staging Environment. So Should I setup another SQL Server Instance/Availability Database for any environment? I believe our Test Staging deployments should always be setup to closely be mirroring Production, and at the very Least, I should setup AlwaysOn for Staging Env (we can skip Dev and QA if required). Financial and  getting resources approved is always a balance in any company.

By the way, the QA or Staging Secondary Replica data would transfer from the QA or Staging Primary Data Replica

Thanks,

It's a balancing act between risk and cost. The least risk highest cost is to have the exact same everything between production and your development environments (Dev, QA, Staging, Whatever you want to call them). Everything the same means same hardware, same san, same network, same QoS (if in place), same everything.

The highest risk, lowest cost is to have the minimum possible for dev (less environments (or no environments, I've seen developers just use production), less hardware, no replication, etc). and have an educated guess for what will happen in production.

Risks involve things like how long will it take for a task to finish on live AND replicate, what does that do to the log file? Will this script work in an availability group? Can it complete in the time required for a maintenance window? Will the replication complete within a maintenance window? What happens when we do Windows Patching?

Costs involve hardware, but also time. A lot of companies have some sort of process to restore (and sanitise) data from production back to dev/test environments. How long will it take to restore back from a live environment to a dev environment (assuming you do)? How does that process work? How is it different between a single instance dev environment and an AAG environment?
 What's the cost in maintaining and developing against multiple AAGs?

As you can see there are many different things going in to your question, and it's not a one case fits all scenario. The less your dev environment looks like live, the cheaper it is to build and maintain, but the risk is you don't know what is going to happen when something goes live.

My preference is to always have 1 environment that replicates live as close as possible, and that everything you do on live is tested there first. From there you need to work out the risk/cost ratio, or to put it another way, how much is the business willing to spend to reduce it's risk, and what risk is acceptable (there's always risk).


Oracle - Is it possible to maintain transaction across multiple schemas? I got this database in oracle which includes 4 schemas. Each schema represents a logical layer. Each transaction (Stored Procedure) in the upper layer breaks into a couple of transactions in the lower layer. So there is this scenario:

I execute a SP in the upper layer which results in some inserts in both layers but for some reason one of the transactions in the lower layer fails. Is it possible to rollback the whole thing?

yes you can, just be sure that the transactions are defined by the uppper layer - the client. A transaction has to be Atomic, so has to succeed (and commit) or has to fail (and rollback).


Postgres columns of latitude and longitude from varchar to numeric I have a table with latitude and longitude columns and they are in varchar. I have to convert them to numeric because that is how the fields are in the master table.

Is there a way to change all the values in the columns at one time from varchar to numeric in PostgreSQL?

I am struggling because since they are lat/long values they vary in length, have decimals and the longitude is negative.

Table Name

Latitude      | Longitude
+-----------------------------+

35.0528620000 | -119.375136000
+-------------|---------------+

and they go on like this. I have tried ALTER TABLE, but that just gives me an an error, CAN NOT BE CAST
and I tried http://www.postgresql.org/docs/9.3/static/functions-formatting.html
to_char

EDIT: I was able to get the fields almost to the same length. Latitude is not 13 char and Longitude is 14 including the negative sign.

EDIT: The alter table error in Navicat:

[Err] ERROR:  syntax error at or near "ALTER"
LINE 1: EXPLAIN ALTER TABLE "allwells_CA_copy"

In Postgres:

ERROR: syntax error at or near "ALTER" LINE 1: ..., VERBOSE off, COSTS on, BUFFERS off, TIMING off )ALTER TABL... ^ ********** Error ********** ERROR: syntax error at or near "ALTER" SQL state: 42601 Character: 71

I've been switching between PG Admin and Navicat in an attempt to make the change.

You need the USING clause to ALTER TABLE ... TYPE ..., e.g.:

ALTER TABLE mytable
  ALTER COLUMN "Longitude" TYPE NUMERIC(14, 11)
    USING ("Longditude"::NUMERIC(14,11)),
  ALTER COLUMN "Latitude" TYPE NUMERIC(14, 11)
    USING ("Latitude"::NUMERIC(14,11));

assuming you want a numeric with precision 14 and scale 11 and your columns really do have an upper case first letter.

For more details see the manual on ALTER TABLE.

after edit:

You seem to be using a broken client that's trying to prepend an EXPLAIN to the ALTER TABLE. You can't EXPLAIN an ALTER TABLE statement.


Inserting Data using Cursor but check if the data already in the table I want to check if data already exist in a table before I do an insert using a cursor. So the cursor will pick a record, check that record doesn't exist in the table. IF it Doesn't EXIST then Insert, If it does EXIST jump that row (Avoid the insert) and continue to FETCH the next row.
What is the best way to do this? Is this even possible with a cursor?
Thanks everyone for the rapid response. Just to add some meat to the bone. I am querying customer data to send retention emails to customers that have been inactive for a certain number of days. For example customers that haven't purchased anything for 30 days since their first purchase will receive an email just to check up on them.
Once I have this data, I'm passing it to a Stored Procedure (don't have control over this SP) that requires it to be in a certain format and from there an email is sent out. I also have a log table that tracks all the emails data that I send to this SP. I want to check this log table for the customerID and the email template that I have sent to the SP in the past to avoid sending the same email template twice. Because I have to send the data in a certain format to the SP, I am forced to use a cursor so that I can format each row before I send it out. So I want to check the data at that point where am formatting it.
Snippet of the script:
OPEN CURSOR_CustomerID;
FETCH NEXT FROM CURSOR_CustomerID
INTO @CustomerID,@Name, @OrderNumber,@Language, @FromEmail, @Email,@templatetouse
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @MessageDetails ='
    [ {"Key":"&lt;*CustomerID*&gt;","Value":"'+LTRIM(@CustomerIDr)+'"},
    {"Key":"&lt;*Name*&gt;","Value":"'+RTRIM(@Name)+'"},
    {"Key":"&lt;*OrderNumber*&gt;","Value":"'+RTRIM(@OrderNumber)+'"}   ]'
EXEC SendAnEMail @Email,NULL,NULL,@language,@FromEmail,@Templatetouse,@CustomerID@MessageDetails

Exec Insert_Email_Log @PCustomerID, @Name, @Email, @Templatetouse
FETCH NEXT FROM CURSOR_CustomerID
INTO @CustomerID,@Name, @OrderNumber,@Language, @FromEmail, @Email,@templatetouse
END
CURSOR_CustomerID;
DEALLOCATE CURSOR_CustomerID;
END

Is there a specific business reason you must insert each row one by one? Would something like this work?
INSERT INTO SomeTable
(
    Column1
    ,Column2
    ,Column3
)
SELECT  Column1
        ,Column2
        ,Column3
FROM    SomeOtherTable t
WHERE   1 = 1
        AND NOT EXISTS (
                            SELECT  1
                            FROM    SomeTable t2
                            WHERE   1 = 1
                                    AND t.Column1 = t2.Column1
                                    AND t.Column2 = t2.Column2
                                    AND t.Column3 = t2.Column3
                        )


mysql - unique index with nulls - actual solution anyone? CREATE TABLE IF NOT EXISTS b2c_constants (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(64) NOT NULL,
    is_deleted BOOL DEFAULT FALSE,
    UNIQUE (name)
) ENGINE InnoDB CHARSET utf8 COLLATE utf8_unicode_ci

CREATE TABLE IF NOT EXISTS b2c_constant_bindings (
    constant_id INT UNSIGNED NOT NULL,
    company_id INT UNSIGNED NOT NULL,
    object_id INT UNSIGNED DEFAULT NULL,
    property_id INT UNSIGNED DEFAULT NULL,
    value VARCHAR(255) NOT NULL,
    UNIQUE (constant_id, company_id, object_id, property_id),
    FOREIGN KEY (constant_id) REFERENCES b2c_constants (id) ON UPDATE RESTRICT ON DELETE CASCADE,
    FOREIGN KEY (company_id) REFERENCES companies (id) ON UPDATE RESTRICT ON DELETE CASCADE,
    FOREIGN KEY (object_id) REFERENCES b2b_objects (id) ON UPDATE RESTRICT ON DELETE CASCADE,
    FOREIGN KEY (property_id) REFERENCES b2b_properties (id) ON UPDATE RESTRICT ON DELETE CASCADE
) ENGINE InnoDB CHARSET utf8 COLLATE utf8_unicode_ci

The problem is with the unique key in the bindings table. If I have data like:

constant_id   company_id   object_id   property_id   value
1             1            null        null          foo
1             1            1           null          bar
1             1            1           1             baz

You can duplicate the first two rows without any errors infinitely, which is obviously not desirable.

The idea with this structure is to allow to bind constants globally per company, per object in company, and per property in company, but allow only unique constants for each of those.

Is there a way to solve this in the database while keeping the foreign keys and a simple table structure? I know that I could do type ENUM ('company', 'object', 'property'), type_id INT, but with that I lose the foreign keys as well as the required company ID for all constants.

[This is not tested as it requires MySQL 5.7.6 or above]

While I agree with the comments above, I still have an idea that can be tried, which I think is not the best in terms of performance, but it solves the issue you're describing.

The idea is to add a key that deals with NULL as a concrete value, like '0', or any other value. Then, uniquely index the combination of the fields that you want to be unique.

MySQL 5.7.6 supports generated columns.

ALTER TABLE b2c_constant_bindings
ADD unique_md5 char(32) AS
    (MD5(CONCAT_WS('X', ifnull(constant_id, 0), ifnull(company_id, 0), ifnull(object_id, 0), ifnull(property_id,0))))
    UNIQUE;

Instead of using CONCAT, I am using CONCAT_WS to avoid having same results in cases similar to CONCAT(1, 23) and CONCAT(12, 3) (ss per @Rick James' notice).


Mysql dump with View dependent on user I use simple mysqldump with only parameters are login and db details. No option of anykind is set. Now, when I recieve the dump file, it export the view in steps (I am sure you all know it, but just for my clarification)

Placeholder table in top of file with other tables.
then at end, it drop this table and create View with definer.

This works good if I have to upload this database back to original server. But how I handle this when I have to import this data in development server where I do not get the same username as defined in production database? Is there any option I can use to elimmiate the "definer" in view. Why doesn't it simply use "Create View as..." syntax?

I try to read about it, but doesn't found any good information, Please enlight me on the topic thanks.

The reason for the definer clause is that view can act as protective layers above the tables. For example, instead of limiting users to be able to read specific columns from a table with otherwise sensitive data, one can create a view which only presents allows columns, and let users select from that view.

Sometimes you may allow users to read a view even though you forbid them from any access to the underlying tables, in which case security=definer and definer=some_user_usually_root.

Other times views just help out in simplifying common complex joins & aggregations, and have nothing to do with security. In which case you want the users of this view to use it under same set of privileges as they would for underlying tables. In which case security=invoker.

So far I've explained why the security stuff is even there. Now for your second question: what should you do if you don't have the definer user in your new database: for this reason it is common to make the definer root@localhost, which is an account typically found everywhere.

In your case I'm guessing that's not what you have, in which case you'll need to modify the dump file before loading into MySQL. Not to worry, you can do it on the fly with sed. There's no built-in mechanism to fix this for you.


SELECT LEFT JOIN but only the record that every matched value is null Suppose that I have two tables name and location

name    |   location_id
'A'     |   NULL
'A'     |   NULL
'B'     |   NULL
'B'     |   2
'C'     |   3
'C'     |   4

location_id |   location_name
1   |   Australia
2   |   Belgium
3   |   Croatia
4   |   Chile
5   |   Denmark

CREATE TABLE location (
    location_id INT PRIMARY KEY,
    location_name VARCHAR(64)
);

INSERT INTO location VALUES
    (1, 'Australia'),
    (2, 'Belgium'),
    (3, 'Croatia'),
    (4, 'Chile'),
    (5, 'Denmark');

CREATE TABLE name (
    id INT PRIMARY KEY,
    fname VARCHAR(64),
    location_id INT,
    FOREIGN KEY (location_id) REFERENCES Location(location_id)
);

INSERT INTO name VALUES
    (1, 'A', NULL),
    (2, 'A', NULL),
    (3 ,'B', NULL),
    (4 ,'B', 2),
    (5 ,'C', 3),
    (6 ,'C', 4);

I want to select only the record that every matched value is null. That is only A will appear because it does not match any location_id but B should not appear because it does match with 2 or Belgium.

SELECT fname, location_name
FROM name n LEFT JOIN location l
USING (location_id)
WHERE location_name IS NULL

Query result:

name    |   location_name
'A'     |   NULL
'A'     |   NULL
'B'     |   NULL

The desired result:

name    |   location_name
'A'     |   NULL
'A'     |   NULL

Can someone guide me how do I write the WHERE statement that will give me the desired result? Thanks!

If you want just rows which has NULL values, you can use this;

SELECT fname,location_id
FROM name WHERE fname NOT IN
(SELECT fname FROM name WHERE location_id IS NOT NULL)

The last row in code eliminate rows which have a value different than NULL


MySQL: Should I keep a running total column in one table instead of summing a second table? I have 3 tables named users, achievements, and  userAchievements. The users table contains information like username, password, e-mail, etc... and the achievements table contains a list of possible achievements and how many points they are worth, and userAchievements lists all achievements completed by all users.

users                        userAchievements           achievements
--------------------------   ------------------------   ------------
username | email             username | achID           achID | points
johnb     john@aol.com       johnb      ach001          ach001  5
daver     dave@yahoo.com     johnb      ach002          ach002  10
miket     mike@hotmail.com   daver      ach001          ach003  15

In my app, once the user is logged in, some information about them from the users table is displayed along with their total achievement points. I'm doing this using two SELECT queries, one to pull information from users, the other to sum the points of all the achievements they have completed. In order to cut it down to one SELECT query, I'm thinking of adding an "achievementTotal" column to users which gets added to by the point value every time an entry in userAchievements is made.
Is it possible to get everything I need with just one SELECT query without doing that? Or is there an even better way to implement what I am trying to do?

SELECT username, email, achID, SUM(points)
FROM users
NATURAL JOIN userAchievements
NATURAL JOIN achievements
WHERE username = 'johnb'
GROUP BY username, email, achID
WITH ROLLUP
HAVING GROUPING(username) + GROUPING(email) + GROUPING(achID) IN (0, 3)

fiddle

  is there a way to get the output like this? 3 columns "username | email | SUM(points)" with 1 row of "johnb | john@aol.com | 15"

SELECT username, email, SUM(points)
FROM users
NATURAL JOIN userAchievements
NATURAL JOIN achievements
WHERE username = 'johnb'
GROUP BY username, email;


Multi-row insert in many to many relationship in PostgreSQL I have got three below tables:

create table r_users(id int primary key, name varchar(30));
create table r_codes(id int primary key, name varchar(10));
create table user_code
(
user_id int,
code_id int,
constraint pk_user_code primary key(user_id, code_id),
constraint fk_user foreign key(user_id) references r_users(id),
constraint fk_code foreign key(code_id) references r_codes(id)
)

Now I have some users and codes in the tables:

insert into r_users values(1, 'John'), (2, 'Roy'), (3, 'Sam'), (4, 'Lewis');
insert into r_codes values(1, 'A'), (2, 'B'), (3, 'C'), (4, 'D'), (5, 'K');

Now I want to insert the codes A, B, C, K to users John, Roy and Lewis such that the mentioned codes are inserted for each of the mentioned users as below:
The desired result is:

user_id | code_id
1       |     1
1       |     2
1       |     3
1       |     5
2       |     1
2       |     2
2       |     3
2       |     5
4       |     1
4       |     2
4       |     3
4       |     5

Any help is appreciated in advance.

You can obtain a Cartesian product using a LATERAL JOIN.

Have a look at Postgres docs.

select u.id, c.id
from   r_users u
left join lateral (select id from r_codes where id in (1, 2, 3, 5)) c on true
where  u.id in (1,2,4);

id | id
-: | -:
 1 |  1
 1 |  2
 1 |  3
 1 |  5
 2 |  1
 2 |  2
 2 |  3
 2 |  5
 4 |  1
 4 |  2
 4 |  3
 4 |  5

insert into user_code
select u.id, c.id
from   r_users u
left join lateral (select id from r_codes where id in (1, 2, 3, 5)) c on true
where  u.id in (1,2,4);

12 rows affected

select * from user_code;

user_id | code_id
------: | ------:
      1 |       1
      1 |       2
      1 |       3
      1 |       5
      2 |       1
      2 |       2
      2 |       3
      2 |       5
      4 |       1
      4 |       2
      4 |       3
      4 |       5

db<>fiddle here

As @Lennart has pointed out, in this case you can get the same result by using left join.

insert into user_code
select    u.id, c.id
from      r_users u
left join r_codes c
on        c.id in (1, 2, 3, 5)
where     u.id in (1,2,4);

12 rows affected

select * from user_code;

user_id | code_id
------: | ------:
      1 |       1
      1 |       2
      1 |       3
      1 |       5
      2 |       1
      2 |       2
      2 |       3
      2 |       5
      4 |       1
      4 |       2
      4 |       3
      4 |       5

db<>fiddle here


comma delimited column fix At my current place of employment someone made a decision to put IDs into a comma delimited column.  I'm looking to break these out into a proper many to many relationship.  However;  it is not in the cards for the application that references this data to change right now.  So I need to modify a view to still show the comma delimited column.  What is the best way to setup the view so that it displays the comma delimited column?  Is there a way to do it that doesn't involve leaving the original column?

A stripped down version of  the table definitions is as follows.

ID
ProductName
FeatureIDs

ID     ProductName    FeatureIDS
1      Hot dog        1,5,4
2      Hamburger      1,3
3      Fish           2

ID
FeatureName

ID     FeatureName
1      Mayo
2      TarTar Sauce
3      Pickle
4      Relish
5      Onion

Ultimatly I'd like to combine these into a single products table and a many to many relationship

ID
ProductName
ProductType

where product type would be either 'product' or 'feature'

ID
productid
featureid

for the mapping table.

Yes the view that is setup needs to be an updateable view.

The view could do something like this (obviously I've guessed at table/column names):

CREATE VIEW dbo.whatever
AS
  SELECT src.columns /*...*/ , IDList = STUFF((SELECT ',' + CONVERT(VARCHAR(12), p.ID)
    FROM dbo.ProperRelationalTable AS p
    WHERE p.EntityID = src.EntityID
    FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')
  FROM dbo.SourceTable AS src;

However as @Martin pointed out in a comment, this view will break the application if it expects to perform any DML against the view rather than the base tables. You can avert this problem by setting up INSTEAD OF triggers as he explained.

A related post that might be useful in other ways aside from just the presentation aspect:

http://www.mssqltips.com/sqlservertip/2074/make-your-sql-server-database-changes-backward-compatible-when-changing-a-relationship/


MySQL load data infile ERROR 1064 I am using MySQL version 5.5 and I am trying to use load data infile and I am getting errors.  I've looked at the documentation and it looks like I am doing exactly what the docs specify.

Running this command in mysql

LOAD DATA local infile '/home/pi/Downloads/load_data_infile.txt'
INTO TABLE test_tbl FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

Gives the following error.

  ERROR 1148 (42000): The used command is not allowed with this MySQL version

Running the following command

LOAD DATA local-infile '/home/pi/Downloads/load_data_infile.txt'
INTO TABLE test_tbl FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

Gives the following error

  ERROR 1064 (42000): 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 '-infile '/home/pi/Downloads/load_data_infile.txt' INTO TABLE test_tbl FIELDS TER' at line 1

What is wrong with my syntax?
The data in the file is

126,126text,moretext
127,127text,moretext

CLIENT SIDE
Before you run this
LOAD DATA local infile '/home/pi/Downloads/load_data_infile.txt'
INTO TABLE test_tbl FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

You need to start mysql client program with --local-file
mysql --local-file -uroot -p

If you wish to have this configured permanantly, add this to ~/.my.cnf
[mysql]
local-infile

This forces the mysql client program to execute.
SERVER SIDE
The mysqld (server side must be started with local_infile) in its my.cnf or my.ini.
Make sure to add this
[mysqld]
local_infile=1

and run
service mysql restart

If you cannot config it and restart it, login to mysql and run
mysql> SELECT @@global.local_infile;

or
mysql> SHOW GLOABL VARIABLES LIKE 'local_infile';

If you get 0, you cannot execute LOAD DATA LOCAL INFILE no matter what you do to the client side.
SUPPLEMENTAL INFO
Running the following command
LOAD DATA local-infile '/home/pi/Downloads/load_data_infile.txt'
INTO TABLE test_tbl FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

Gives the following error

ERROR 1064 (42000): 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 '-infile '/home/pi/Downloads/load_data_infile.txt' INTO TABLE test_tbl FIELDS TER' at line 1

That's just a straight up syntax error.
Get rid of the dash between local and infile so that it reads
LOAD DATA local infile '/home/pi/Downloads/load_data_infile.txt'
INTO TABLE test_tbl FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';


Unable to restore database I am trying to restore several databases and I have no clue why some of them fail with the following error:

  Msg 3634, Level 16, State 1, Line 1
  The operating system returned the error '1244(The operation being requested was not performed because the user has not been authenticated.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'K:\Logs\MyDatabase.ldf'.

  Msg 3156, Level 16, State 8, Line 1
  File 'MyDatabase_log' cannot be restored to 'K:\Logs\MyDatabase.ldf'. Use WITH MOVE to identify a valid location for the file.

  Msg 3119, Level 16, State 1, Line 1
  Problems were identified while planning for the RESTORE statement. Previous messages provide details.

  Msg 3013, Level 16, State 1, Line 1
  RESTORE DATABASE is terminating abnormally.

This is the SQL statement that I'm using

RESTORE DATABASE [MyDatabase]
FROM DISK = 'K:\Backups\MyDatabase_backup_2014_10_14_055006_4726051.bak'
WITH REPLACE,
MOVE 'MyDatabase_data' TO 'K:\Data\MyDatabase.mdf',
MOVE 'MyDatabase_log' TO 'K:\Logs\MyDatabase.ldf',
STATS = 10

As I said, some of them will work perfectly fine, while others will give me an error similar to the above. Unfortunately I can't find a pattern for the ones that fail / succeed.

If there was some sort of authentication issue, why does my SQL work on some but not on others? What am I missing?

LE: I had to give this user the sysadmin role for a completely unrelated issue. This may have also fixed this. I'll investigate and come back.

As part of a different issue I had to give my login the sysadmin role. I haven't seen the problem I described above since then.

Yes, it may work now, but I am not happy with this solution, partially because I don't quite understand what was going on.

Later edit: As pointed out by Thomas in a comment, here's what's going on:

  If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database (for the FROM DATABASE_SNAPSHOT option, the database always exists).


Set names to attributes when creating JSON with row_to_json Is it possible to rename default f1, f2, f3... names when using row_to_json function for only some columns?

I can do

row_to_json(customers)

returning

{"id_customer":2,"first_name":"bla","last_name":"second_bla"}

But if I want only names without id_customer, I have to use

row_to_json(row(first_name, last_name))

and then I get

{"f1":"bla","f2":"second_bla"}

And I would like to get this result with either default column names or my own. I know I can create my own composite type and use

row_to_json(row(first_name, last_name))::my_custom_type

but isn't it possible to do it right in the query without creating that type?

select
   c.id,
   (select row_to_json(_) from (select c.first_name, c.last_name) as _) as first_last,
   c.age
from
   customers as c

will do what you want without any performance impact (and is not too verbose):

  id  |   first_last                                |   age
------+---------------------------------------------+---------
  1   | {"fisrt_name": "John", "last_name": "Smit"} |   34


Is it possible to get seek based parallel plan for distinct/group by? An example from this question shows that SQL Server will choose a full index scan to solve a query like this:

select distinct [typeName] from [types]

Where [typeName] has a non-clustered, non-unique ascending index on it.  His example has 200M rows but only 76 unique values.  It seems like a seek plan would be a better choice with that density (~76 multiple binary searchs)?

His case could be normalized but the reason for the question is that I really want to solve something like this:

select TransactionId, max(CreatedUtc)
from TxLog
group by TransactionId

There is an index on (TransactionId, MaxCreatedUtc).

Re-writing using a normalized source (dt) does not not change the plan.

select dt.TransactionId, MaxCreatedUtc
 from [Transaction] dt -- distinct transactions
 cross apply
   (
        select Max(CreatedUtc) as MaxCreatedUtc
          from TxLog tl
         where tl.TransactionId = dt.TransactionId
   ) ca

Running just the CA subquery as a Scalar UDF does show a plan of 1 seek.

select max(CreatedUtc) as MaxCreatedUtc
 from Pub.TransactionLog
 where TransactionID = @TxId;

Using that Scalar UDF in the original query seems to work but loses parallelism (known issue with UDFs):

select t.typeName,
       Pub.ufn_TransactionMaxCreatedUtc(t.TransactionId) as MaxCreatedUtc
  from Pub.[Transaction] t

Rewriting using an Inline TVF reverts it back to the scan based plan.

From answer/comment @ypercube:

select TransactionId, MaxCreatedUtc
 from Pub.[Transaction]  t
  cross apply
   (
        select top (1) CreatedUtc as MaxCreatedUtc
        from Pub.TransactionLog l
        where l.TransactionID = t.TransactionId
        order by CreatedUtc desc
   ) ca

Plan looks good.  No parallelism but pointless since so fast.  Will have to try this on a larger problem sometime.  Thanks.

I have exactly the same set up and I've been through the same stages of rewriting the query.

In my case the table names and meaning is a bit different, but overall structure is the same. Your table Transactions corresponds to my table PortalElevators below. It has ~2000 rows. Your table TxLog corresponds to my table PlaybackStats. It has ~150M rows. It has index on (ElevatorID, DataSourceRowID), same as you.

I'll run several variants of the query on the real data and compare execution plans, IO and time stats. I'm using SQL Server 2008 Standard.

GROUP BY with MAX

SELECT [ElevatorID], MAX([DataSourceRowID]) AS LastItemID
FROM [dbo].[PlaybackStats]
GROUP BY [ElevatorID]

Same as for you optimizer scans the index and aggregates results. Slow.

Individual row

Let's see what optimizer would do if I requested MAX just for one row:

SELECT MAX([dbo].[PlaybackStats].[DataSourceRowID]) AS LastItemID
FROM [dbo].[PlaybackStats]
WHERE [dbo].[PlaybackStats].ElevatorID = 1

Optimizer is smart enough to use the index and it does one seek. By the way, we can see that optimizer uses TOP operator, even though the query doesn't have it. This is a telling sign that optimization paths of MAX and TOP have something in common in the engine, but they are different as we'll see below.

CROSS APPLY with MAX

SELECT
    [dbo].[PortalElevators].elevatorsId
    ,LastItemID
FROM
    [dbo].[PortalElevators]
    CROSS APPLY
    (
        SELECT MAX([dbo].[PlaybackStats].[DataSourceRowID]) AS LastItemID
        FROM [dbo].[PlaybackStats]
        WHERE [dbo].[PlaybackStats].ElevatorID = [dbo].[PortalElevators].elevatorsId
    ) AS CA
;

Optimizer still scans the index. It is not smart enough to convert MAX into TOP and scan into seeks here. Slow. I didn't think of this variant originally, my next try was scalar UDF.

Scalar UDF

I saw that plan for getting MAX for individual row had index seek, so I put that simple query in a scalar UDF.

CREATE FUNCTION [dbo].[GetElevatorLastID]
(
    @ParamElevatorID int
)
RETURNS bigint
AS
BEGIN
    DECLARE @Result bigint;
    SELECT @Result = MAX([dbo].[PlaybackStats].[DataSourceRowID])
    FROM [dbo].[PlaybackStats]
    WHERE [dbo].[PlaybackStats].ElevatorID = @ParamElevatorID;
    RETURN @Result;
END

SELECT
    [dbo].[PortalElevators].elevatorsId
    ,[dbo].[GetElevatorLastID]([dbo].[PortalElevators].elevatorsId) AS LastItemID
FROM
    [dbo].[PortalElevators]
;

It does run fast. At least, much faster than Group by. Unfortunately, execution plan doesn't show details of UDF and what is even worse, it doesn't show the real IO stats (it doesn't include IO generated by UDF). You need to run Profiler to see all calls of the function and their stats. This plan shows only 6 reads. The plan for individual row has 4 reads, so real number would be close to: 6 + 2779 * 4 = 6 + 11,116 = 11,122.

CROSS APPLY with TOP

Eventually, I discovered the CROSS APPLY and how it can be applied ;-) in this case.

SELECT
    [dbo].[PortalElevators].elevatorsId
    ,LastItemID
FROM
    [dbo].[PortalElevators]
    CROSS APPLY
    (
        SELECT TOP(1) [dbo].[PlaybackStats].[DataSourceRowID] AS LastItemID
        FROM [dbo].[PlaybackStats]
        WHERE [dbo].[PlaybackStats].ElevatorID = [dbo].[PortalElevators].elevatorsId
        ORDER BY [dbo].[PlaybackStats].[DataSourceRowID] DESC
    ) AS CA
;

Here optimizer is smart enough to do ~2000 seeks. You can see that number of reads is much lower than for group by. Fast.

Interestingly, number of reads here (11,850) is a bit more than reads that I estimated with UDF (11,122). Table IO stats with CROSS APPLY have 11,844 reads and 2,779 scan count of the large table, which gives 11,844 / 2,779 ~= 4.26 reads per index seek. Most likely, seeks for some values use 4 reads and for some 5, with average 4.26. There are 2,779 seeks, but there are values only for 2,130 rows. As I said, it is difficult to get real number of reads with UDF without profiler.

Recursive CTE

As was pointed in comments, Paul White described a Recursive Index Skip Scan method to find distinct values in a large table without performing a full index scan, but doing index seeks recursively. To start recursion we need to find the MIN or MAX value for an anchor and then each step of recursion adds next value one by one. The post explains it in details.

WITH RecursiveCTE
AS
(
    -- Anchor
    SELECT TOP (1) [ElevatorID], [DataSourceRowID]
    FROM [dbo].[PlaybackStats]
    ORDER BY [ElevatorID] DESC, [DataSourceRowID] DESC

    UNION ALL

    -- Recursive
    SELECT R.[ElevatorID], R.[DataSourceRowID]
    FROM
    (
        -- Number the rows
        SELECT
            T.[ElevatorID], T.[DataSourceRowID]
            ,ROW_NUMBER() OVER (ORDER BY T.[ElevatorID] DESC, T.[DataSourceRowID] DESC) AS rn
        FROM
            [dbo].[PlaybackStats] AS T
            INNER JOIN RecursiveCTE AS R ON R.[ElevatorID] > T.[ElevatorID]
    ) AS R
    WHERE
        -- Only the row that sorts lowest
        R.rn = 1
)
SELECT [ElevatorID], [DataSourceRowID]
FROM RecursiveCTE
OPTION (MAXRECURSION 0);

It is pretty fast, though it performs almost twice amount of reads as CROSS APPLY. It does 12,781 reads in Worktable and 8,524 in PlaybackStats. On the other hand, it performs as many seeks as there are distinct values in the large table. CROSS APPLY with TOP performs as many seeks as there are rows in the small table. In my case small table has 2,779 rows, but large table has only 2,130 distinct values.

Summary

                         Logical Reads       Duration
CROSS APPLY with MAX           482,121          6,604
GROUP BY with MAX              482,123          6,581
Scalar UDF                    ~ 11,122            728
Recursive                       21,305             30
CROSS APPLY with TOP            11,850              9 (nine!)

I ran each query three times and picked the best time. There were no physical reads.

Conclusion

In this special case of greatest-n-per-group problem we have:

n=1;
number of groups is much smaller than the number of rows in a table;
there is appropriate index;

Two best methods are:

In case when we have a small table with the list of groups, the best method is CROSS APPLY with TOP.
In case when we have only large table, the best method is Recursive Index Skip Scan.


Table partitioning in SQL 2008 - Why? I understand how it works, but the book I'm studying doesn't tell me why you'd want to partition. Could someone shed some light? I can't understand any concept unless I see how it's useful.

Basically, to split up a huge table into smaller sub tables or partitions

offload data into different files/filegroups (with changes in backup/restore strategy too)
separate "working" from "historic" data (without having 2+ separate tables which complicates queries)
allow compression of older data

We're talking 100s of millions of rows and/or high volumes.

You don't partition for a few million rows.


Attach MDF File To SSMS I am attempting to attach a .mdf file to my SSMS install.  I go through the steps and at the very end I get this message.

What is causing the .mdf file to not attach?

Also, I have verified that the .mdf file is NOT read only.

EDIT
If I run Select @@Version I get the output that I have 2008 which is what my .mdf file requires..

As the error message clearly states, the version of the *.mdf file you are trying to attach is the internal version 661.

  The database 'E:\PRAKASH_PRIVATE\ACADEMIC DOC\PROJECT_DOC\MYPROJECT R3\MYPROJECT\APP_DATA\SHOPING.MDF' cannot be opened because it is version 661.

The SQL Server you are running supports the internal version number 655 and earlier.

  This server support version 655 and earlier. A downgrade is not supported.

To attach the MDF file you would have to find the corresponding SQL Server version number that has the internal database version 661 (the MDF file). This turns out to be SQL Server version 2008 R2.

The SQL Server version you are running is less than SQL Server 2008 R2 (possibly 2008).

Reference: Microsoft SQL Server Internal Database Versions and Compatibility Levels

The table is reproduced here to provide a complete source:

SQL Server Version  | Internal Database Version | Database Compatibility Level
--------------------+---------------------------+------------------------------
SQL Server 2019 CTP | 895 / 904                 | 150
--------------------+---------------------------+------------------------------
SQL Server 2017     | 869                       | 140
--------------------+---------------------------+------------------------------
SQL Server 2016     | 852                       | 130
--------------------+---------------------------+------------------------------
SQL Server 2014     | 782                       | 120
--------------------+---------------------------+------------------------------
SQL Server 2012     | 706                       | 110
--------------------+---------------------------+------------------------------
SQL Server 2012 CTP1|                           |
(a.k.a. 2011 Denali)| 684                       | 110
--------------------+---------------------------+------------------------------
SQL Server 2008 R2  | 660 / 661                 | 100
--------------------+---------------------------+------------------------------
SQL Server 2008     | 655                       | 100
--------------------+---------------------------+------------------------------
SQL Server 2005 SP2+|                           |
VarDecimal enabled  | 612                       | 90
--------------------+---------------------------+------------------------------
SQL Server 2005     | 611                       | 90
--------------------+---------------------------+------------------------------
SQL Server 2000     | 539                       | 80
--------------------+---------------------------+------------------------------
SQL Server 7.0      | 515                       | 70
--------------------+---------------------------+------------------------------
SQL Server 6.5      | 408                       | 65
--------------------+---------------------------+------------------------------
SQL Server 6.0      | ?                         | 60
--------------------+---------------------------+------------------------------

Your assumption that....

  If I run Select @@Version I get the output that I have 2008 which is what my .mdf file requires..

...is wrong. The mdf file contains a database with the version SQL Server 2008 R2 which is not the same as your SQL Server version SQL Server 2008.

To attache the mdf file you would have to install SQL Server 2008 R2.


Multiple databases or tables with prefix? I was creating an Accounting ERP for a multibrach company.Normally i would keep different databases for different branches, Can anyone tell me which is the standard way?
1) create databases respective to branches
2) keep single database and arrange tables with branch prefix

It's not simply a question of plugging values into an equation and, hey presto, you have an answer (as @dbdemon also points out - no standard solution).

You need to put considerable thought into this - it could be a choice which affects you for a considerable time.

A few things you should do before you start coding:

Gather detailled requirements (as kindly suggested by @Peter)
Check out the threads and a few of my answers on multi-tenancy here. Also, as suggested by @Colin'tHart, do a search on this site for other posts related to multi-tenancy.
You could also download a few Open Source apps - use your search engine of choice - duckduckgo got me a few links.
Take a look at databaseanswers and their schemas - something there might be of interest/help.
Do a bit of prototyping - see what "fits" with your company/clients/stakeholders requirements and/or your company's skillset in the coding/database areas.


Can't see connexion to Postgresql using php I have created a php form to connect to a remote postgresql database. It seems that I can log in successfully because my message "connexion succeeded" appears.
But I can't see the corresponding line in the pg_stat_activity view.
Does it mean that the connexion is closed just after completion ? or is there some other reason ?
Thanks

using pg_pconnect creates a permanent connection not like pg_connect I was using.