Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium



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 a simple SPROC in my SQL Developer 14 DB that selects records to return to the calling program.

USE [JTSConversion]
/****** Object:  StoredProcedure [dbo].[aConvertspRead_bView_buildProd_TaxAuthorityRecs_02]    Script Date: 1/20/2018 12:26:00 PM ******/
-- =============================================
-- =============================================
Create PROCEDURE [dbo].[aConvertspbuildProd_TaxAuthorityRecs_02]
      -- Add the parameters for the stored procedure here

      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.

    -- Insert statements for procedure here
      SELECT *
      From bView_buildProd_TaxAuthorityRecs_02
      Order By MuniCode, ControlNum, TaxType

I would like to return the number of records in the result set back to the calling program so I can show a processing status bar as the records are processed in the calling program.

How can I do that?
Has Powershell sent you back into the Stone Age?
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

Hi Experts,
I'm updating a field in my table with a dropdown, which is populated from a different table:
This is the dropdown:

<select name="DestQueueId">
Dim rsName
set rsName=Server.CreateObject("ADODB.recordset")
sql="SELECT distinct(Name), QueueEnvironmentId FROM QueueEnvironment order by Name"
Set rsName = AdoCon.Execute(sql)
<option value="">Change Environment:</option>
Do While Not rsName.EOF
Response.Write "<option value='" & rsName("QueueEnvironmentId") & "'>"& rsName("Name") &"</option>"
Response.Write "</select>"
<input type="Submit" name="Submit" value="Submit" />

Open in new window

After I submit, the DestQueueId column is updated in my table with the value of QueueEnvironmentId.
QueueEnvironmentId is an integer.
Name is a string.
When I view  <%=rs("DestQueueId")%> on my updated page, the integer is displayed.
How could I have the Name string displayed instead?

I would appreciate your help.
I am creating a table and I don't want duplicates in the id_user field.  I am using below SQL statements but I can still add duplicate id_users.

CREATE TABLE SYS_PromisedDateNotif (
	id_user	INT NOT NULL,
	CONSTRAINT FK_id_user FOREIGN KEY (id_user) REFERENCES BASE_User(id_user),

Open in new window

How Do I find the string has an apostrophe, single quote in the string.
I have a function
 Public Function ReplaceSingleQuote(ByRef val As String)
        '//***This function replaces the single quote to double quote***\\
        If IsNothing(val) = False OrElse val <> String.Empty Then
            val = val.Replace("'", "''")
            val = val.TrimStart
        End If
        Return val
    End Function

Open in new window

But, the problem here is that if use for multiple times for the same string it inserts too many single quotes.
Is there any way it just detects an single apostrophe and then replaces it with '' for sql compatibility.
In ms sql

delete table1 from table2 where table1.field=table2.field

Works ok

How can I do the same for 2 tables in mysql

Any ideas
Hi Guys

I'm using the CASE function in the query below.

  from_unixtime(b.`date_create`) AS 'Booking  Date'
  ,from_unixtime(b.`timeslot`)   AS 'Allocated Date & Time Slot'
  ,b.`minutes`                   AS 'Minutes'
  ,b.`voucher_title`             AS 'Product Name'
  ,b.`customer_name`             AS 'Customer Name'
  ,from_unixtime(c.`birthdate`)  AS 'Customer DOB'
  ,c.address                               AS 'Address'
  ,c.city                                   AS 'City'
  ,c.postal_code                         AS 'Post Code'
  ,c.email                                 AS 'Customer Email'
  ,b.`customer_num`              AS 'Guest Number'
  ,b.`invoice_id`                AS 'Invoice Number'
  ,i.total_ht                                AS 'Gross'
  ,i.total_tva                             AS 'Tax'
  ,i.paid_ttc                               AS 'Total Received'
  ,i.origin                                     AS 'Bookig Method'
  ,(CASE WHEN b.checkin <- 1 THEN "Un-Finalized"
         WHEN b.checkin > 1  THEN "Finalized"
    END)                         AS Finalized
  FROM `booking` b
  JOIN customers c ON c.customer_num = b.customer_num
  JOIN invoices  i ON i.id                   = b.invoice_id

The table "checkin" has either a 0 or a 10 digits, such as 1515663909. What I'm trying to achieve is below

IF b.checkin < 0 THEN Un-Finalized
IF b.checkin > 0 THEN Finalized

I'm able to run the query above but I don't feel the CASE function works, any ideas?

Thanks All!
Hi experts,
How can I  differentiate oracle error between connection error and other errers in vba.
I tried the code bellow. But not only when internet cable is disconnected but also when sql syntax error happened , the code show ORACLE CONNECTING ERROR.

Public Function TEST() As Boolean
    Dim strSql As String
    Dim oraDS As OraDynaset
    On Error GoTo ERR
    strSql = ""
    strSql = " select COLUMN from TALE "
    Set OraRec = New ADODB.Recordset
    OraRec.Open strSql, OraCon, adOpenKeyset, adLockReadOnly
    Exit Function

    If OraCon.State <> adStateOpen Then
    End If
End Function
hello all, what is best way to write this code in php when i try and use the tool in sql workbench it does not work for this query in converting it to php

SET @frf = (SELECT driverId FROM drivers WHERE 1 ORDER BY RAND() LIMIT 1);
select  h.*, g.*,  a.*,b.*, c.*, e.* , f.* from
(SELECT  COUNT(*) firstplaceFinishes FROM results r JOIN drivers d WHERE d.driverId=@frf AND r.driverId=d.driverId AND r.position=1 )as a,
(SELECT COUNT(*) secondPlaceFinishes FROM results r JOIN drivers d WHERE d.driverId=@frf AND r.driverId=d.driverId AND r.position=2) as b,
(SELECT COUNT(*) thridPlaceFinishes FROM results r JOIN drivers d WHERE d.driverId=@frf AND r.driverId=d.driverId AND r.position=3) as c,
(SELECT COUNT(*) Poles FROM results r JOIN drivers d WHERE d.driverId=@frf AND r.driverId=d.driverId AND r.grid=1) as e,
(select count(r.statusId) Failures FROM results r JOIN drivers d WHERE d.driverId=@frf AND r.driverId=d.driverId and r.statusId > 1) as f,
(select count(r.statusId) Finished FROM results r JOIN drivers d WHERE d.driverId=@frf AND r.driverId=d.driverId and r.statusId = 1) as g,
(select d.surname,count(*) Races FROM results r JOIN drivers d WHERE d.driverId=@frf AND r.driverId=d.driverId ) as h;

Open in new window

I have been trying to figure out how to bind sql data to a radCombo box that is nested within a radGrid's automatic Edit Form.
Oddly enough, I was able to figure out how to "cascade" the radCombo boxes Department & ActivityNum as shown in the screenshot.  What I have not figured out is how to initially bind ActivityNum onDataBound.  The exact problem I am having right now is that the following line results in cboActivityID=Nothing resulting in the errors in the screenshots below...

Dim cboActivityID As RadComboBox = e.Item.FindControl("cboActivityNum")

<telerik:RadGrid ID="grdDetail" runat="server" CellSpacing="0" DataSourceID="sqldsDetail" DataKeyNames="TID" GridLines="None" Skin="Telerik" Height="350px" AllowSorting="True" AllowAutomaticUpdates="True" >
            <GroupingSettings CollapseAllTooltip="Collapse all groups"></GroupingSettings>
            <Selecting AllowRowSelect="True" />
            <Scrolling UseStaticHeaders="True" AllowScroll="True"/>
            <Resizing AllowColumnResize="True" />
        <MasterTableView AutoGenerateColumns="False" DataSourceID="sqldsDetail" DataKeyNames="TID" EditMode="EditForms" AllowAutomaticUpdates="True">
            <RowIndicatorColumn Visible="true">
            <ExpandCollapseColumn Created="True">

Open in new window

I am trying to write a simple query to show me my "groups" of PC's I have in an Access database. I have a table called PC_EQUIP with 4 fields that I need: type, model, warranty and serial. This query works fine:


This shows me the information I mostly need since we order PC's in groups so the type, model and warranty date of each group will be the same and this query shows me the different groups that I want to see.

I really want to add two more pieces of information. Even though I am using DISTINCT, I would like to see one of the serial numbers for each DISTINCT group, dont care which. Obviously, if I add serial to the query above, I get a list of all records and the "DISTINCT" is useless. I would also like to add a COUNT to the query so it will show me how many rows are rolled up in that DISTINCT clause effectively showing me how many PC's I have on hand for that group,

The serial number part is optional but I cant seem to figure out how to make it return the count for each grouping.

The Lifecycle Approach to Managing Security Policy
The Lifecycle Approach to Managing Security Policy

Managing application connectivity and security policies can be achieved more effectively when following a framework that automates repeatable processes and ensures that the right activities are performed in the right order.

Hello-   I am using a IN SELECT statement  to add a date to my query in MSACCESS,  In (select AD from Admit_Date). Where AD is a field from the local table Admit_Date.

I want to call a date range from two local tables  the two statements I have is  In (select AD from Admit_Date)   and

I tried something like these, but it does not  seem to work:

Between (In (select AD from Admit_Date) and (In (DC from Discharge_Date))
Between In (select AD from Admit_Date) and In (DC from Discharge_Date)

Is there a better way?
Hi all,
is there a documented process to do replication with log shipping where the only thing available to the destination database is the log files?  If not I will have to setup an SSIS package to do restores of the log files to the destination database, which seems problematic?

It is for a entry level server. Windows Server 2016 Standard. 26 users (don't want Essentials LOL!). They don't use SQL or any other data intensive apps. Need maybe 1TB usable storage but let's be safe and say two. So what would you recommend as far a fault tolerance and type of drives in this day and age?
I'm trying to monitor counters on my SQL server(2008) from my Windows 10 laptop.
Here's what I did:
  1. I set up a User Defined data collector, added counters(via my laptop)
  2. Created a database on my laptop via SSMS
  3. Created an ODBC entry to my new database(System DSN)
  4. Went to the collector and changed the Log Format to "SQL"
  5. hit the dropdown for "Data Source Name", expecting my new dsn to appear...
Nothing appears in the dropdown.
I tried recreating the Data Collector, and also a User Defined DSN

I'm using the code below and it's in a never ending loop. Thoughts

Public Function Import_System_Access_Reports()

Dim strFolder As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strFile As String
Dim strTable As String
Dim strExtension As String
Dim lngFileType As Long
Dim strSQL As String
Dim strFullFileName As String
Dim varPieces As Variant

 With Application.FileDialog(4) ' msoFileDialogFolderPicker
     If .Show Then
         strFolder = .SelectedItems(1)
         MsgBox "No folder specified!", vbCritical
         Exit Function
     End If
 End With
 If Right(strFolder, 1) <> "\" Then
     strFolder = strFolder & "\"
 End If
 strFile = Dir(strFolder & "*.xls*")
 Do While strFile <> ""

     lngPos = InStrRev(strFile, ".")
    strTable = "RawData" '<- this could be a constant instead of a variable
Set db = CurrentDb()
' make the UPDATE a parameter query ...
strSQL = "UPDATE [" & strTable & "] SET FileName=[pFileName]" & vbCrLf & _
    "WHERE FileName Is Null OR FileName='';"
Set qdf = db.CreateQueryDef(vbNullString, strSQL)

strFile = Dir(strFolder & "*.xls*")
Do While Len(strFile) > 0
    varPieces = Split(strFile, ".")
    strExtension = varPieces(UBound(varPieces))
    Select Case strExtension
    Case "xls"
        lngFileType = acSpreadsheetTypeExcel9
    Case "xlsx", "xlsm"
        lngFileType = acSpreadsheetTypeExcel12Xml
    Case "xlsb"
        lngFileType = acSpreadsheetTypeExcel12
    End Select
    strFullFileName = strFolder & 

Open in new window

I've got two related Oracle tables: (1) WORKORDER and (2) ASSIGNMENT. The WORKORDER table has two attributes: (1) WONUM and (2) PARENTWONUM. If the PARENTWONUM has a value in it, that means that the record is a child of another WORKORDER. So, in that case, WONUM is the child and PARENTWONUM references the parent WORKORDER. One WORKORDER can have many children. The attached word document does a better job of illustrating this.

The ASSIGNMENT table has two attributes: (1) WONUM and (2) ASSIGNEE. The relationship between WORKORDER and ASSIGNEE is one to many (there can be 0, 1, or many ASSIGNMENT records for a single WORKORDER). Again the attached document does a better job of illustrating this.

I need to construct a query that returns who is assigned to which workorder. The attached document shows a sample result set.

The "rub" here is that this has to be done via sub selects instead of joins. I'm able to do the SQL for Work Orders and their children but adding in the ASSIGNMENT table is a bit to complex for me. For example:

select wonum, parent from workorder
      wonum = '45296' or wonum in (select wonum from workorder wo2 where (wo2.wonum = '45296' or wo2.parent = '45296'))

That would get me work order 45296 and each of its children but I'm not sure how to get a subselect in there for the ASSIGNMENT table.

Let me know if you can help me.

Thanks in advance
I have written the below code, which works fine until it has to pass the sql code to the query definition (CurrentDb.QueryDefs(qry2).sql = sql), as then I get a "System Resource Exceeded" error.   In the example I am testing, the number of OR clauses in qString will be 131.  Any ideas?

Thank you.


Public Function ImportServicePOs()
On Error GoTo ImportServicePOs_Err

'This function imports the spreadsheet with te POs
Dim tbl As String, fd As FileDialog, fn As String, qry As String, tbl1 As String, tbl2 As String, db As DAO.Database, rst As DAO.Recordset, qString As String, qdExtData As QueryDef, qry2 As String, pos As String, sql As String
tbl = "tbl_TempPos"
tbl2 = "tbl_PoNumbers"
qry = "qry_ServicePosWithVendorDetails"
qry2 = "qry_ServicePosWithVendorDetailsPQ"
tbl1 = "tbl_ServicePosWithVendorDetails"
Set fd = Application.FileDialog(msoFileDialogFilePicker)

fd.AllowMultiSelect = False

MsgBox "Browse for the PO file to import...", vbOKOnly, "Attention!"

If fd.Show = True Then
    If fd.SelectedItems(1) <> vbNullString Then
        fn = fd.SelectedItems(1)
    End If
    'Exit code if no file is selected
End If

DoCmd.SetWarnings False
DoCmd.DeleteObject acTable, tbl
DoCmd.TransferSpreadsheet acImport, , tbl, fn, -1

Set rst = …
I have 2 columns in SQL table.

Combine or concatenate these 2 columns and the output should be in single column but in row order. See attached.

Please help.

Thank you for your help.
Hi ,
  I have an Sql Server database with table Documents
  There are many duplicates rows with same Document_No  . But I need to get only one row from each document no

From the following sample data , I am looking for a query

   Doc1 Proj1 Path1 ......
   Doc1 Proj1 Path2
   Doc2 Proj1 Path2
   Doc3 Proj1 Path3

Query Should return rows with  Doc2 and Doc3 and one of the Doc1 row (it does not matter which one)
Sign your company up to try the MB 660 headset now
Sign your company up to try the MB 660 headset now

Take control and stay focused in noisy open office environments with the MB 660. By reducing background noise, you can revitalize your office and improve concentration.

I have a table called myUsers with 1000 UserID's.  This table is the result of a query.  

I have another table called EmailSent that records every email sent to a UserID and which EmailTemplate was used.

How do I get the subset of those 1000 myUsers that have not yet received  EmailTemplate = "Template1.cfm"

This is so I can send the remainder of the UserIDs, the email.

I could do this with spaghetti code but prefer to do it within a CFquery tag.
Hi experts,

I'm using sql server 2008.

I'm using the Employees table from the Northwind sample database.

Query 1A

Filter by EmployeeID

I have a query that works fine, it looks like this:

SET @EmployeeID = -3
SELECT [EmployeeID],[LastName],[FirstName] FROM [Northwind].[dbo].[Employees]
WHERE ( (@EmployeeID = -3) OR ([EmployeeID] = @EmployeeID) )

Open in new window

I'm using -3 to display all records.
When I set the @EmployeeID parameter to -3 the query returns all records like this:

When I set the @EmployeeID parameter to 3 the query returns all records for EmployeeID of 3 like this:

Query 1B

Filter by EmployeeID

I'm practicing writing dynamic sql queries.
So then I wrote this query. It works just fine. It gives the same results as Query 1A.

SET @EmployeeID = -3

DECLARE @mSQL as varchar(5000)

---------------- Select ----------------

Select @mSQL = 'SELECT [EmployeeID],[LastName],[FirstName] FROM [Northwind].[dbo].[Employees]' 

Select @mSQL = @mSQL + ' Where ( ([EmployeeID] = ' + RTrim(@EmployeeID) + ') Or (' + RTrim(@EmployeeID) + ' =  -3) )' 

---------------- Select ----------------


Open in new window

Query 2A

Filter By LastName

So now I want to do the same query as Query 1A but filter by LastName.
I wrote this query and it works fine.

SET @LastName = 'ALL'
SELECT [EmployeeID],[LastName],[FirstName] FROM [Northwind].[dbo].[Employees]
WHERE ( (@LastName = 'ALL') OR ([LastName] = @LastName) )

Open in new window

I'm using the string 'ALL'  to display all records.
When I set the @LastName parameter to 'ALL' the query returns all records.
When I set the @LastName parameter to 'Leverling' the query returns all records with LastName 'Leverling.

Query 2B

Filter By LastName

So now I have the query that looks like this.
The results of this query should be the same as the results of Query 2A. But I'm getting an error.  
In my where clause where i set ALL i'm not sure of the syntax to use for a string.

Open in new window

We are using MS SQL 2012R2.  One of our tables has a field which is varchar(MAX), we are changing it to nvarchar(MAX) in order to allow non-latin characters to be entered.

We already tested and worked fine. My question is:  For all the entries we already have in that varchar field, will they be affected in ANY way if we change the field to nvarchar?

I am getting error on the set statement - what is the proper syntax to include the ampersand in my text?
I have a vb.net App attached to a SQL MDF database.  Inno Script supports .net 45  The database is version 852.(2016+) MsSQLLocalDB will only run version 782database.   since inno script doesn't check for or install dotnet46, or the local db version, how do either:
ADD the new files to connect to this database or
Is there an existing script for the new version of dotnet.

OR should i consider a different installer.  This is not an area of my expertise and this will be installed on many different machines as a local APP.   I could use some advise.  Thanks

What is the most performant way to select the min and max value

here is my query

select (min odometer) and (max odometer) 
From MyTable
Where gpsDateTime between '2018-01-01' and '2018-01-17' and eventID = 1

Open in new window

i basically need  to return 2 item, the max odometer and min odometer reading based on the datetime
I'm looking for the most efficient SQL that can achieve this

SQL server 2014


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.