Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Microsoft SQL Server





Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Share tech news, updates, or what's on your mind.

Sign up to Post

I am trying to print out a date with today's date.  The result i'm looking for is 9/18/2017 23:59:59.  Or it can be 2017-09-20 23:59:59.
SET @sDate = CONVERT(VARCHAR, GETDATE(), 101) + ' 23:59:59.999'

SET @Date = CONVERT(DATETIME, @sDate, 102)
--SET @Date = @sDate

Open in new window

Concerto's Cloud Advisory Services
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Hello expert,

Working on selecting pairs of Payers and Component
and excluding them from lists.

    and Pay.Payer_ID <> 100083  --exclude  DISA
    and Component_ID <> 7  -- exclude Audiogram

But this query excludes all DISA not just where DISA is a payer for the Audiogram.
For instance this excludes DISA where DISA is the Payer for a DOT Physical.
So I tried
   and (Pay.Payer_ID <> 100083 and and Component_ID <> 7 )
hoping this would exclude the combination of DISA and Audiogram
but it has the say effect as the first where statement.

What is the syntax for excluding a record that does not equal a
combination of two fields equal to certain values?


Allen in Dallas
I have 4 tables: Distributors, States, Counties, DistibutorsCountiesLookup

I am associating distributors with counties within a state. A state may be divided between many distributors. A distributor in linked to a state/county via the stateID and CountyID. So the lookup table has DistributorID, StateID, CountyID. When I switch states using a combobox, I want to return the counties in that state that are assigned to the selected distributor or have not yet been assigned. Here are the tables.

StateID (PK)

CountyID (PK)
StateID (FK)

DistributorID (PK)

DistributorID (FK)
StateID (FK)
CountyID (FK)

Thank You,

  I need to grant the 'SELECT' privileges to a user for a View that belongs to another database, but i do not want to grant 'SELECT' to any table of that database to the user, or even any of the tables that belongs to that view.    What happens is that I only want that user to be able to 'SELECT' to that view, for specific fields that belongs to that view.

  For example.., if the views is built using TABLE A  that has 8 fields, but in the view the display is only viewing  3 fields of that table, the user must be able to SELECT  the view for only to see the fields that belong to the view, but can not see the others fields of the table, or any other table of the database.
  Can i do it in MS SQL Server ?

Windows 7 Pro
Office Enterprise 2007
SQL Server 2014
SQL Server 2014 Management Studio

I'm working on an application which requires me to occassionally load a CSV file into SQL Server.  Unfortunately, the number and names of the columns will vary, depending on the CSV file that is uploaded; the code in my application will resolve the column name issues but at the moment, I'm simply trying to read the csv file using the OpenRowset command.

I've tried:
SELECT * FROM OpenRowSet('Microsoft.Ace.OLEDB.16.0',
'Text;Database=C:\SampleCSVFiles\;HDR=Yes','SELECT * FROM TestFile.CSV')

Open in new window

But this returns the error:
OLE DB provider "Microsoft.Ace.OLEDB.16.0" for linked server "(null)" returned message "Syntax error in FROM clause.".
Msg 7321, Level 16, State 2, Line 44
An error occurred while preparing the query "SELECT * FROM GPEX_Statements_7-21-2017-12-20-27-PM.CSV" for execution against OLE DB provider "Microsoft.Ace.OLEDB.16.0" for linked server "(null)". 

Open in new window

I've also tried:
select * FROM openrowset('MSDASQL','Driver={Microsoft Access Text Driver (*.txt, *.csv)}',
'select * from C:\SampleCSVFiles\TestFile.CSV')

Open in new window

But this generates the error:
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Text Driver] Your network access was interrupted. To continue, close the database, and then open it again.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Text Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0xad0 Thread 0xe50 DBC 0x177a4758                                                             Text'.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Text Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0xad0 Thread 0xe50 DBC 0x177a4758                                                             Text'.".
Msg 7303, Level 16, State 1, Line 49
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

Open in new window

I've even tried opening the CSV file in Excel and saving it as an Excel .xlsx file, and then tried reading it using:
'EXCEL 12.0 xml;HDR=YES;DATABASE=C:\SampleCSVFiles\TestFile.xlsx',
'SELECT * FROM [Sheet1$]')

Open in new window

But it generates the error:
Msg 7399, Level 16, State 1, Line 58
The OLE DB provider "MICROSOFT.ACE.OLEDB.16.0" for linked server "(null)" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 58
Cannot get the column information from OLE DB provider "MICROSOFT.ACE.OLEDB.16.0" for linked server "(null)".

Open in new window

which seems to imply that the file is already open.  After I receive this error, if I check task manager, it does not show an instance of Excel running, but when I open Excel and attempt to open the file it indicates that the file is already in use.

Please, someone help me get this syntax right.

I am running sql server 2008 R2. Everything seems to be running just fine.  The server is running on Scale hyperconverged servers.
When I try to run the report from my own desktop using studio management, or even when I RDP into the server, it does not work.
The report is called  "all blocking transactions".
Here is the error message.
sql error
When I log into the server directly via the console, the report works fine.   All other reports work just fine from my own desktop or even via RDP, it's just the one called "all blocking transactions" that does not work.

Any idea's if this is a sql issue or some kind of network problem, but I doubt it's a network issue, as all other reports are running just fine.
I have sql server 2016 express and the report server.  How do I specify sql server authentication in the RSReportServer.config file rather than NTLM?
I have a query, when I try to run it commenting a logic defined below, it runs fine.

But I need to include this logic and run the query, the query is taking hell lot of time. 2 hours for 1 lakh record.


--CASE WHEN (select distinct  1
--from TBL c            
--where EXISTS (SELECT 1 FROM TBL1 leave            
--               WHERE convert(date,c1.DT) = leave.DT
--                 ))

FROM (select *,
case when test= 'A' then DATEADD(hour,8,Date)  
     when test=  'B' then DATEADD(hour,2,Date)  
     when  test= 'C' then DATEADD(hour,-4,Date)
     else Date end as DT

 from STAGING c
 ) c1
I have jobs running in two different servers, using a query or SP I want to know if any of these jobs are running on these 2 servers.

How can I achieve it ?
Hi All,

In a table I have a date column, I want to calculate Month and Quarter start days and End days dynamically whenever I load the table or append new records. I have attached the excel on the same. In the sample I have given a series of sorted date but in my actual, it is not sorted.
The Eight Noble Truths of Backup and Recovery
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

I need to create a report in SharePoint 2013 , on premises , There is a custom SharePoint list which has username, Manager and Status. say it SPLIst
I have created another SharePoint list as external content type using SQL procedure which returns all all active users. In this second list say DBList I have username and their department.
My requirement is , I need to compare the two lists and whoever is not found in SPList, should be marked as "Not Started".

Could you please help me to achieve this?

Thanks in advance.
i have a asp.net mvc application.

I'm using the Employees table of the SQL Server Northwind database

My table looks like this:

Right now my dropdownlist displays  the EmployeeID

My razor code on my view looks like this:

@Html.DropDownListFor(model => model.EmployeeID, "Please Select an Employee: ", new { @class = "employeeclass1", @id = "EmployeeDDL" })

How do i set the default value on page load for my DDL to be 0?
How do I add a "ALL" text to my DDL so if someone selects that then the selected value for that is -2.

So then the only options my DDL would have are:
"Please Select an Employee:"  and the default selected value for this would be 0
"All" and the selected value for this would be -2
The other choices they would have are EmployeeID 1 through 9 which I already have and come from my model.
Is the positioning of the begin try and begin transaction and end try and commit transaction correct? I ask because I sometimes get a message about transaction count being wrong with previous count being 0 and current count being 1.

CREATE PROCEDURE [dbo].[rbs_RevalidateQuote]
      @I_vQuote_Number INT,
      @I_vSequence_Number INT,
      @I_vQuote_Date DATETIME

DECLARE @TranCount int
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int

SET @TranCount = @@TRANCOUNT

IF @TranCount = 0
      BEGIN TRAN UpdateRevalidateNumber
      SAVE TRAN UpdateRevalidateNumber


-- Get the current highest revalidate number

              -- Update quote header

            --      Update quote lines

            -- Update quote kit lines

I have delphi10.
And during insert procedure in MSSQL, sometimes I get exception...

Access violation at address 5D3CD9A2 in module  MSADO15.dll. Read of address 00000000.

Can somebody help...

Thank you
We are having an issue on our SQL server where jobs that send out a lot of emails send some emails but not all of the them.  In going through the logs the emails are sending fine and report 'Mail successfully sent' and then all of a sudden the error comes up with 'The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2017-09-18T12:16:43). Exception Message: Cannot send mails to mail server. (The operation has timed out.).).  It seems to fail at different intervals.

On the failure emails, the last_mod_user in the sysmail_event_log is 'sa' and the account_id is NULL.  However, on the successfully sent emails, the last_mod_user is 'DOMAIN\Administrator' and the account_id is 1.  It almost appears as if the Administrator looses connection and then the mail starts failing thereafter.

The same activity seems to happen regardless of running the proc manually or having the agent job initiate it.

The mail settings are (we use AuthSMTP.com to send):

Server Name: mail.authsmtp.com
Port: 2525
SSL: required
Basic Authentication with our AuthSMTP credentials.

Database Mail Executable Minimum Lifetime (seconds) = 600

Does anyone know why this would occur?  

Thank you.

I have to try and determine if certain conditions are true.
Something like this - if column a is null and column b is null and column c is not null then do an update statement based on a select.

What would be the best way to do this.

Many thanks
Hi EE,

I get the following error see attaching when trying to open the MSDB folder on a SSIS instance.

Tried to fix using the following post but no luck.


Attached is the current  MsDtsSrvr.ini.xml file I am using.

Any assistance is appreciated.

Thank you.
Hi All

My Company is implementing SFB 2015 Ent Edition. We have 2 SQL Servers in cluster. The SQL Servers is easily getting connected through SSMS via cluster. But when I am trying to publish the topology it s giving error. Please find below the error:

****Creating DbSetupInstance for 'Microsoft.Rtc.Common.Data.XdsDatabase'****
Initializing DbSetupBase
Parsing parameters...
Found Parameter: SqlServer Value dccluster.gsi.gov.in.
Found Parameter: SqlFilePath Value C:\Program Files\Common Files\Skype for Business Server 2015\DbSetup.
Found Parameter: DatabaseType Value .
Found Parameter: FeatureName Value CentralMgmtStore.
Found Parameter: DatabaseNames Value System.Collections.Generic.List`1[System.String].
Found Parameter: Publisheracct Value GSI\RTCUniversalServerAdmins.
Found Parameter: Replicatoracct Value GSI\RTCUniversalConfigReplicator.
Found Parameter: Consumeracct Value GSI\RTCUniversalReadOnlyAdmins.
Found Parameter: Role Value master.
Trying to connect to Sql Server dccluster.gsi.gov.in. using windows authentication...
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.InvalidOperationException: Cannot open Service Control Manager on computer 'dccluster.gsi.gov.in'. This operation might require other privileges. ---> System.ComponentModel.Win32Exception: The RPC server is unavailable

Please help to solve the error. All firewall service is disabled in all servers and we are using …
I am try to connect conquestdicomserver with sql Server 2008 R2 manually and also trying to editing "dicom.ini" file.. but this is not working..

Any One can help me..please.... Thanks in Advances

Here is DICOM.INI file code .

i am using window32

Sql Server Name :- GMDahri-PC
databse    Name :- conquest
login         Name :- conquest
password            :- conquest1415
sa                        :- 123

# This file contains configuration information for the DICOM server
# Do not edit unless you know what you are doing

MicroPACS                = sscscp

# Network configuration: server name and TCP/IP port#
MyACRNema                = NICVDPACS
TCPPort                  = 5678

# Host(ignored), name, username and password for ODBC data source
SQLHost                  = localhost
SQLServer                = conquestpacs_s
Username                 = conquest
Password                 = conquest1415
DoubleBackSlashToDB      = 0
UseEscapeStringConstants = 0

# Configure server
ImportExportDragAndDrop  = 1
ZipTime                  = 05:
UIDPrefix                = 1.2.826.0.1.3680043.2.135.736588.40949073
EnableComputedFields     = 1

FileNameSyntax           = 4

# Configuration of compression for incoming images and archival
DroppedFileCompression   = un
IncomingCompression      = un
ArchiveCompression       = as

# For debug information
PACSName                 = NICVDPACS
OperatorConsole          =
NFR key for Veeam Backup for Microsoft Office 365
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Hello everyone!

I am running with TFS 2015.3, and SQL Server 2016 SP1, in separate servers.

Recently, I change our production TFS Database from 32 bit to 64 bit (for updating purposes), and I believe we loose some database configuration and permissions.

This week (after the change), we are having some issues with some Jobs, related to Warehouse and Coverage Analysis:

-Work Item Tracking Warehouse Sync
-Test Management Warehouse Sync
-Team Foundation Server Coverage Analysis

The error that is shown is:

Microsoft.TeamFoundation.Framework.Server.DatabaseOperationTimeoutException: TF246018: The database operation exceeded the timeout limit and has been cancelled. Verify that the parameters of the operation are correct. ---> System.Data.SqlClient.SqlException: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception: The wait operation timed out --- End of inner exception stack trace --- at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)…

I have a requirement daily database refresh at 4.00 am and i schedule this job, now need to download .bak file from ftp site daily basis so that SQL job can restore the .bak file from local drive.

Please help!
As per the limitation of Amazon RDS not being able to do Distributed Queries (i.e. Linked Servers) to OnPremise MSSQL host (as per https://aws.amazon.com/blogs/database/implement-linked-servers-with-amazon-rds-for-microsoft-sql-server/ documentation), I want to know if its possible to setup something of a 'reverse proxy' for allowing a MSSQL RDS instance, connect to said proxy and send SQL calls to an OnPremise SQL host instead?

As per Amazon support -- "It’s an internal IP resolution and routing issue.  When the SQL Server is inside a VPC, the SQL Server isn’t able to use the customer provided DNS entries. This causes DNS lookup failure.  Additionally, the server’s routing tables don’t allow the server to see the customer’s VPC Gateway meaning, there’s no routing path for traffic back to your on-prem servers even if lookup succeeded (or used IP addresses). // If the IP address doesn’t appear in the VPC, we will not route the traffic through the correct network interface, and on-premise database servers would fall into this category." (As per support as well -- "There’s an open enhancement request to fix this but honestly, it’s pretty old and hasn’t gotten much traction for prioritization.")

To get around the limitation of what Amazon has done with the internal IP resolution / routing, primarily with RDS and the TDS Protocol outside of a VPC, I would like to 'trick' the Amazon MSSQL RDS instance into thinking that it is communicating with a Windows EC2 instance running…
Yesterday, I was able to use SSMS to connect to a remote server using SQL Server authentication.  Today, when I try I get an error message about login failed.  I confirmed the username / password have not changed because I was able to RDP to the server directly and use SSMS and connect with same credentials.

Yesterday, I installed a new instance of SQL Server 2012 on my local machine and I feel that is what is causing the issue.  I unistalled everything and reinstalled and it appeared to work until I rebooted my machine.  Is there a service I am missing?
Below is the sample data:
Country;     Value
AAA          10
AAA           12
AAA           14
AAA           35
AAA           72
AAA           827
AAA           992
AAA           1000
AAA           1001
AAA           1002
BBB           12
BBB          15
BBB          26
BBB           27
BBB          30
BBB           33
BBB           50
BBB           400
BBB          800

My Final table should be as below
Country     FirstQ     ThirdQ     InterQ
AAA         XXX        YYY        ZZZ
BBB         XXX        YYY        ZZZ

I got the solution partially from this site  by CYBERKIWI

;with tmp as (
      select c=COUNT(*) over (), rn=ROW_NUMBER() over (order by n), n
      from quartile_test)
,qs as (
            q1=1+(c-1.0)/4, q1a=FLOOR(1+(c-1.0)/4), q1b=CEILING(1+(c-1.0)/4),
            q2=1+(c-1.0)/2, q2a=FLOOR(1+(c-1.0)/2), q2b=CEILING(1+(c-1.0)/2),
            q3=1+(c-1.0)*3/4, q3a=FLOOR(1+(c-1.0)*3/4), q3b=CEILING(1+(c-1.0)*3/4)
      from (select top 1 c from tmp) x)
select (1-q1+q1a)*MIN(n)+(q1-q1a)*MAX(n)
from tmp,qs where rn in (q1a,q1b) group by qs.q1,qs.q1a,qs.q1b
union all
select (1-q2+q2a)*MIN(n)+(q2-q2a)*MAX(n)
from tmp,qs where rn in (q2a,q2b) group by qs.q2,qs.q2a,qs.q2b
union all
select (1-q3+q3a)*MIN(n)+(q3-q3a)*MAX(n)
from tmp,qs where rn in (q3a,q3b) group by qs.q3,qs.q3a,qs.q3b
union all
select n
from tmp,qs where rn=c

Can someone help me modify this as per country?

I am studying and trying to rebuild an example from this page: https://www.toptal.com/sql/interview-questions I Understand the reference between the primary key in table Customers and Id in table Invoices. But I don´t understand what the relation between the primary key Id and the field RefferedBy means? internal_table_reference
I want to know so I can create examples to make sure that I really understand the sql-code in the example from the page.


Microsoft SQL Server





Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.