SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

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

Sign up to Post

I have an Access database written to keep track of events.  I am trying to combine the following two queries and have the results added together.  Both of these work independently, just trying to find the easiest way to combine their numeric results.  In the second example, the "Spouse" field is blank.  Any help is appreciated.

SELECT Count([Invitee])+Sum(Abs(IsNull([Spouse])=False)) AS RSVPYESCount
FROM Invitations
WHERE (((Invitations.EventID)=[Forms]![Events]![Invitations_Subform]![EventID]) AND ((Invitations.RSVP)="Yes"));

SELECT Count([Invitee]) AS RSVPSpouseOnly
FROM Invitations
WHERE (((Invitations.EventID)=Forms!Events!Invitations_Subform!EventID) And ((Invitations.RSVP)="Spouse Only"));
Free Backup Tool for VMware and Hyper-V
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Hi Experts,

I have this bit of code:
,(Select SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END ) as [Fail] FROM CTE_Compliance as di1 where di1.[Branch Region] = own.[Branch Region] ) as RegionFail
,(Select SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END ) as [Fail] FROM CTE_Compliance as di1 where di1.[Branch Region] = own.[Branch Region] and di1.[Branch Network] = own.[Branch Network] ) as NetworkFail
,(Select SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END ) as [Fail]  FROM CTE_Compliance as di1 where di1.[Branch Region] = own.[Branch Region] and di1.[Branch Network] = own.[Branch Network]  and di1.[Branch] = own.[Branch] ) as BranchFail
,(Select Cast( SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END)  as float) /Count(*) as PercentCompliant  FROM CTE_Compliance as di1 where di1.[Branch Region] = own.[Branch Region] ) as RegionPercent
,(Select Cast( SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END)  as float) /Count(*) as PercentCompliant FROM CTE_Compliance as di1 where di1.[Branch Region] = own.[Branch Region] and di1.[Branch Network] = own.[Branch Network] ) as NetworkPercent
,(Select Cast( SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END)  as float) /Count(*) as PercentCompliant FROM CTE_Compliance as di1 where di1.[Branch Region] = own.[Branch Region] and di1.[Branch Network] = own.[Branch Network]  and di1.[Branch] = own.[Branch] ) as BranchPercent
,(Select count(*) as [All] FROM CTE_Compliance as di1 where di1.[Branch 

Open in new window

I am posting this question as a follow up to the question at this link:


I have a form with two unbound combo boxes, one for the course and one for the preferred session of that course.  Each are located in the header of a continuous form which displays each request submission that has not been assigned to a session.  The user is able to filter the list based upon both the class and the preferred session.  

I need to add the cascading functionality so that if the user filters the list by course only the preferred sessions for the requests for that particular course show up in the preferred sessions filter combo.

Here is the SQL I have for each combo box:


SELECT qryReqPendingAssignment.Course, tblCourses.CourseName FROM tblCourses INNER JOIN qryReqPendingAssignment ON tblCourses.CourseID = qryReqPendingAssignment.Course UNION SELECT Null, '(View All)' FROM qryReqPendingAssignment
ORDER BY tblCourses.CourseName;

Preferred Session

SELECT qryReqPendingAssignment.PreferredSession, tblEvents.EventDesc FROM tblEvents INNER JOIN qryReqPendingAssignment ON tblEvents.EventID = qryReqPendingAssignment.PreferredSession UNION SELECT Null, '(View All)' FROM qryReqPendingAssignment
ORDER BY tblEvents.EventDesc;
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

In my following code, I am getting the error 'Invalid number of arguments':

with Combined as(
      Select staff_name Staff, Status
      from rpt_scheduled_activities
      where trunc(service_date) between '01 aug 2017' and '31 aug 2017'
      and staff_id in (39882, 41116, 45723, 19395, 19365, 63898, 48071,
      55352, 36065, 60240, 62247, 33570, 60693, 61025)
      order by 1)

      "'Kept'" as NumKept,
      "'CBT'" as NumCBT,
      "'CBC'" as NumCBC,
      "'DNS'" as NumDNS,
      cast(round((("'Kept'" / NULLIF( ("'Kept'" + "'CBT'" + "'CBC'" + "'DNS'"))*100),2), 0) as varchar(10)) || ' %' as PercentKept
(Select * from Combined
PIVOT(count(*) for (Status)
      IN ('Kept', 'CBT', 'CBC', 'DNS')

I figure the error is somewhere in the 'cast(round...' statement.
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.
Hi experts, How to set in sql command the table below where I wish to display by group the field name theId. Please see the table below!

Amount       Desc                                      TheId
10                 fair                                          x
12                 meals                                     y
15                 allowance                              y
15                 fuel                                         x
50                 Medicines                              x

I tried this command but it doesn't work;

Set rs = cn.Execute("Select Desc, theId, Amount from PList group by TheId")
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.
Get free NFR key for Veeam Availability Suite 9.5
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

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 am working on a .Net application that is using nHibernate and I am passing through a structured datatype into a stored procedure.

I am using an extended method like this

    public static class StructuredExtensions
        private static readonly Sql2008Structured structured = new Sql2008Structured();

        public static IQuery SetStructured(this IQuery query, string name, DataTable dt)
            return query.SetParameter(name, dt, structured);

Open in new window

The full code of this is in the attached file,

The SQL User Defined Table Type is

	[PRNCPL_ACC_CD] [char](10) NOT NULL,
	[BRANCH_ACC_CD] [char](10) NOT NULL,

Open in new window

hi i have upgrade the liabrary from 6i to 11g the foolowing code work in 6i but when i compile in 11g is giving error check attachment
Hello Experts,

I have a situation as follows,
I have to investigate data mismatch between three different databases

Current process I am following,

-I am taking sample data and comparing against 3 different database, a very manual process
- I am joining across 3 databases and getting records.
-Join is being done based on customerId and date

I need to find out which database has the wrong record..
--TableA has the authoritative data and TableC is the reporting database. mismatch across TableA,B and C has to be found  

Solution I am seeking
- How do I write a better query to find out in which database the non matching or the wrong record exist?..Here I have just given an example for just few records. the database I am working on has several million records..

Table A

date      CustomerId      CustomerName
1/6/2005      1      John Quan
1/6/2005      2      Megan Muir
1/6/2005      3      Pete Sampras
1/6/2005      4      Bill Gambino
1/6/2005      5      Oscar Hernandez

date      CustomerId      CustomerName
1/6/2005      1      John Qan--Name is wrong
1/6/2005      2      Megan Muir
1/6/2005      3      Pete Sampras
1/6/2005      4      Bill Gambino
1/6/2005      5      Oscar Hernandez

date      CustomerId      CustomerName
1/6/2005      1      John Qan--Name is wrong as it is coming to this system from upstream database B, table B
1/6/2005      2      Megan Muir
1/6/2005      3      Pete Sampras
1/6/2005      4      Bill Gambino
1/6/2005      5      Oscar Hernandez
I've tried theses code but these isn't working, please help me T_T

Dim st As String = "Update OrderItems set Item=@item,Price=@price where ID=@id"
        Dim cmd As New SqlCommand(st, con)


            If cmd.Connection.State = ConnectionState.Open Then cmd.Connection.Close()
            con.ConnectionString = connection
            cmd.Parameters.AddWithValue("@item", txtitem.Text.Trim)
            cmd.Parameters.AddWithValue("@price", txtprice.Text.Trim)
            cmd.Parameters.AddWithValue("@id", txtid.Text.Trim)

            If cmd.ExecuteNonQuery > 0 Then
                MsgBox("Updated Successfully", MsgBoxStyle.Information)

            End If

        Catch ex As Exception

        End Try
I am trying to add new link server to oracle db in SSMS. I've installed oracle client 12c 64 bit.
I can connect to the oracle db via SQL developer when I enter the tnsnames directory but not mgt studio, when adding a new linked server. This confirms all service names passwords and firewall rules, content of tnsname.ora are correct. I get "ORA-12154 tns could not resolve the connect identifier specified" immediately so its not finding the tnsnames.ora file.

So I have also checked the following:
I have amended the provider options to allow inprocess.

My tnanames.ora and sqlnet.ora files are in c:\oracle\product\12.2.0\client_1\network\admin\

I have names.directory_path= (Tnsnames) in my sqlnet.ora file.

If I do a tnsping tnsentry it uses the above sqlnet.ora as parameter file bit I get
tns-03505: failed to resolve name error

Ive check the path in environment variables. In regedit, the path of Inprocserver32 of the oracle clsid is c:\oracle\product\12.2.0\client_1\bin\oraOlEDB12.dll

Ive restarted SQL services.

How can I get SQL mgt studio to point.to my tnsnames file.?  Is there anywhere in regedit that tells SQL of the file location
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          =
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)…
Moving data to the cloud? Find out if you’re ready
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

I want to load invoice header and lines information from external system provided csv file to custom table, please help to create sql loader. this is bit urgent.
Below is file structure which contains multiple data. One header invoice can have more than 1 lines. hope below files help you to understand the requirement.

H~TEST_INVOICE~STANDARD~2017-09-10~1019658~47026~655207.76~GBP~~~~Invoice For flower 10 Sep 2017~Prophet~~~~~~~~
D~1~ITEM~145942.80~VAT20~Prophet invoice desc~120~6033099~00000~74710~00000~000~000000~~
D~2~ITEM~155509.70~VAT20~Prophet invoice desc~120~6033099~00000~74710~00000~000~000000~~
D~3~ITEM~164851.65~VAT20~Prophet invoice desc~120~6033099~00000~74710~00000~000~000000~~
D~4~ITEM~40835.47~VAT20~Prophet invoice desc~120~6033099~00000~74710~00000~000~000000~~
D~5~ITEM~43511.65~VAT20~Prophet invoice desc~120~6033099~00000~74710~00000~000~000000~~
D~6~ITEM~44992.09~VAT20~Prophet invoice desc~120~6033099~00000~74710~00000~000~000000~~
D~7~TAX~59564.40~VAT20~Prophet invoice desc~410~6033099~00000~74710~00000~000~000000~~
H~INVOICE_2~STANDARD~2017-09-10~6987356~64953~120.00~GBP~~~~Invoice For flower 11 Sep 2017~Prophet~~~~~~~~
D~1~ITEM~30.70~VAT20~Prophet invoice desc1~120~6033099~00000~74710~00000~000~000000~~
D~2~ITEM~69.30.~VAT20~Prophet invoice desc1~120~6033099~00000~74710~00000~000~000000~~
D~7~TAX~20.00~VAT20~Prophet invoice desc1~410~6033099~00000~74710~00000~000~000000~~

I have created two table table A for header information and table…

Looking for SQL Server Database AdventureWorks based SQL exercises needed for extensive sql queries practices

Anybody know of SQL Server  excercise sanything like this?

How to display the size of tblmountertrace_1 (to) 9 to text box? Please see picture:

database table
Im using this code but I dont know how to query. I put it on the Timer (timSERVERSIZE) to update every 4 Hours a day.

        Private Sub timSERVERSIZE_Tick(sender As Object, e As EventArgs) Handles timSERVERSIZE.Tick
        Dim cmd2 As New MySqlCommand
        Dim myDA2 As New MySqlDataAdapter(cmd2)
        Dim myDT2 As New DataTable
        cmd2.Connection = conn
        cmd2.CommandText = "SELECT * FROM esd_reco ?????????????????????????????????????"
        TextBox1.items.add(myDA2)  <<<<<<<<<<<<??????????????????????????

    End Sub

Open in new window

Thanks for the help
I have a clr assembly installed on SQL Server 2008R2
It has been working flawlessly for 4 months
Not 1 iota of code has been changed
If calls a web service
Not one thing has been changed on the web service

Out of the blue I am getting this message on the invoke

Msg 6522, Level 16, State 2, Line 2
A .NET Framework error occurred during execution of user-defined routine or aggregate "MyGTG": 
System.NullReferenceException: Object reference not set to an instance of an object.
   at HelloEveryware.PassIn.GTG(String user, String token, String PID, String OID, String TSW_ID, String FirstName, String LastName, String IsOwner, String EmailAddress, String AccomName, String AccomAddress, String AccomState, String CheckinDate, String CheckinTime, String CheckoutTime, String AccomNights, String TourDate_Disp, String TourWave, String TourLocation, String ContactUsPhone, String DateAdded, String SendNow, String AccomCreationDate, String AccomPhone, String ActivitySeason, String CampaignName, String CancellationPeriod, String CheckinDate_Disp, String CheckInDayOfWeek, String CheckoutDate, String CheckoutDate_Disp, String ContactUsEmail, String ContactUsSignature, String OpenDate, String OpenExpirationDate, String OpenExpirationDate_Unix, String OpenMonths, String OwnerAgentName, String OwnerLottoPreferenceLevel, String OwnerLottoRequest, String OwnerLottoUse, String OwnerNumber, String OwnershipSeason, String OwnershipUnitSize, String 

Open in new window

Currently I have 3 applications using SQL 2008R2 Express.  I am in the process of purchasing SQL 2016 Standard and downgrading to 2012.  

My question is this:  

 If I use the in-place upgrade path is it possible to leave 2 of the DB's still using Express 2008R2 and upgrading to 2012 only having the Primafact data bases being upgraded to SQL 2012?
Hey guys-
I have a sql server that runs a few apps but is also our main reporting server.  the apps aren't heavy loads on the sql database.

is there a way to tell which reports are causing all the cpu usage?  do I have to find the query behind the report in activity monitor on ssms or is there another way.

basically, whats a good way to figure out what's killing our server here.

Update... this is an edit.  the spike occurs when I go to the SRSS landing page as a user from internet explorer


SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.