Query Syntax

54K

Solutions

20K

Contributors

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 need to give one user access the the jobs in SQL Server Agent.

Does anyone know how? What is the syntax?

Thanks
0
Exploring SharePoint 2016
LVL 13
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

I am not an advanced SQL database admin, so I am looking for a simple and temporary means to monitor if and when data is changed in dbo.MyTable.MyColumn.  The field is Not Null, TinyInt and is either 0 or 1.  

Any suggestions?
0
Whats the best way to remove dupes from a table? I have two fields, OrderNumber and CustomerID. There are many customers with more than one order. I want to remove all but one record for each CustomerID (doesnt matter which OrderNumber I'm left with).
0
Hi, I have few SSIS packages. NOw I want to deploy them. However I can see that our DBA has not
installed any Integration services on the server. But he asked me to just create a Job in SQL Server
agent and call the SSIS Package from the path and schedue it.

can anyone pleae let me know this will work and is this a good strategy ? I thought we need to first
import the SSIS package into the SSIS server and then call this from SQL server agent for the job schedue.

Many Thanks
0
Hi experts,

I need SQL syntax help please.

Here is my business scenario; I need to get a count by distinct employee type of 4 different measures. If the count of employees by each measure does not equal zero/null then show the total count. If the count of employees by each measure equals zero or null, show zero.

I'd like to use PHP to echo the results in a table that would take the following format:

distinct_type | MeasureA | MeasureB | MeasureC | MeasureD
Emp Type X | 1 | 0 | 1 | 1
Emp Type Y | 0 | 1 | 0 | 1
Emp Type Z | 1 | 1 | 0 | 0

Something like...I know I am way off...

(SELECT DISTINCT employee.type AS  'distinct_type' FROM mydatabase ORDER BY employee.type)
UNION ALL
(SELECT employee.type, COUNT(*) AS 'MeasureA' FROM my database WHERE employee.type=distinct_type AND measures.a=1 GROUP BY employee.type ORDER BY employee.type ASC)
UNION ALL
(SELECT employee.type, COUNT(*) AS 'MeasureB' FROM my database WHERE employee.type=distinct_type AND measures.b=1 GROUP BY employee.type ORDER BY employee.type ASC)
UNION ALL
(SELECT employee.type, COUNT(*) AS 'MeasureC' FROM my database WHERE employee.type=distinct_type AND measures.c=1 GROUP BY employee.type ORDER BY employee.type ASC)
UNION ALL
(SELECT employee.type, COUNT(*) AS 'MeasureD' FROM my database WHERE employee.type=distinct_type AND measures.d=1 GROUP BY employee.type ORDER BY employee.type ASC)
0
need to change a value to <blank> if the word is "NO"

I have this field, which has the "YES" or "NO" values.

If the value  =  "Yes" bring back a "Yes"
if the value  = "NO" bring back  nothing  


[PayTermsTiedVolCommi] as 'Payment Terms tied to VC Performance'



Thanks
fordraiders
0
error message on openquery sql  missing right paren ?
OLE DB provider "OraOLEDB.Oracle" for linked server "PROD" returned message "ORA-00907: missing right parenthesis".


I dont see the error ?


SELECT
 PROJECTID
,LINEITEMID
,CUSTOMERPRICE
,PRICE
,SKU
,CSPIDENTIFICATION
,PRODUCTVARICOMMENTS
FROM OPENQUERY (PROD, 'select
 PROJECTID
,LINEITEMID
,CUSTOMERPRICE
,PRICE
,SKU
,CSPIDENTIFICATION
,PRODUCTVARICOMMENTS
FROM XREF.XREF_RESULTS
where lastmodified >= to_date(''''01/01/2019'''',''''MM/DD/YYYY'''')')

Thanks
fordraiders
0
Good evening everyone,

I have a table with several data (> 2000) of this type:
Reference | Date | Product | Quantity
BC0001 | 2019-09-11 | Bread | 7
BC0001 | 2019-09-11 | Caviar | 5
BC0002 | 2019-09-12 | Oil | 2
BC0003 | 2019-09-13 | Bread | 4
BC0003 | 2019-09-13 | Egg | 12

Open in new window


And another table where I have to reinsert this unique data like this:
Reference | Date
BC0001 | 2019-09-11
BC0002 | 2019-09-12
BC0003 | 2019-09-13

Open in new window


I wish I could execute a SQL query to add in my table2 a single line of my table1 as shown in the example above.

Is there a SQL function to simplify my task?
Thank you for your help

Regards,
Jaber
0
hi Experts,

We have a column DOB that was mistake defined as text and we are planning to convert to Date column.
However there are many records with invalid dates in it.
Wondering if there is a function that will correct this when something is obvious, like only missing slashes or removing spaces etc...
See attached for sample data.

Thanks
Untitled.png
0
I have an unbound form frmLogRptCPUSelection that uses a combo box cboComputerDevice to select a device using SQL <SELECT tblComputer.cID, tblComputer.cName
FROM tblComputer ORDER BY tblComputer.cName;>  Once the selection is made I have a "Print" button on the form that has the following code in the Print button Click event.

I also have a report rptEventLogByComputer that is based on a query qryComputerLogCPU.  One problem is that cboComputerDevice is the value of cID not cName.  
I can get the cName with

Dim strID As String
Dim strcName As String

strID = Me.cboComputerDevice
strcName = DLookup("[cName]", "tblComputer", "[cID] = " & strID)

I have tried putting [Forms]![frmLogRptCPUSelection]![cboComputerDevice] as the critera in the query but it does not work.

Now how do I pass strcName to qryComputerLogCPU so that the report will work?
0
Bootstrap 4: Exploring New Features
LVL 13
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

problem getting open query to run

i keep getting an error when trying to run this openquery ?




INSERT INTO
[Metrics].[dbo].[EXTRACT_CUSTOMERPRICE_staging]
           ([PROJECTID]
           ,[LINEITEMID]
           ,[CUSTOMERPRICE]
           ,[WWGSKUPRICE]
           ,[WWGSKU])
SELECT
 [PROJECTID],
[LINEITEMID],
[CUSTOMERPRICE],
[PRICE],
[SKU]
FROM OPENQUERY (XREF_PROD, 'select
 [PROJECTID],
[LINEITEMID],
[CUSTOMERPRICE],
[PRICE],
[SKU]
FROM xref.xref_results')

OLE DB provider "OraOLEDB.Oracle" for linked server "XREF_PROD" returned message "ORA-00936: missing expression".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "select
 [PROJECTID],
[LINEITEMID],
[CUSTOMERPRICE],
[PRICE],
[SKU]
FROM xref.xref_results" for execution against OLE DB provider "OraOLEDB.Oracle" for linked server "XREF_PROD".


Thanks
fordraiders
0
First time for me seeing this one …

After attaching a SQL database in SSMS and linking to an Access Db, all linked tables display #Deleted in every field.

Ideas anyone?
0
I enter the date value in this format.
oracle gives me
 ORA-01843: not a valid month error.
NOTE:(you can better understand the question in the pictures.
Please see the pictures.hatali.png
but
I enter the date value in this format.
Oracle is running.
ikinci_resim.png)
I want to change the date format.
I don't want to use this date (13.jun.2014),
 I want to use this date = (13.06.2014),
how can I do this?

Result of query 'select sysdate from dual'
17/09/2019 17:46:00

Open in new window

0
Problem with my query, when adding a value that has a single quote to a database field.

eg. //-- values
        $value1 = "Peter";
        $value2 = "O'Leary";
        $value3 = "Contact Person";
     //-- query
        $stmt = $mysqli->prepare("INSERT INTO table1 (field1, field2, field3) VALUES (?,?,?)");
        $stmt->bind_param('sss',  $value1, $value2, $value3);
        $stmt->execute();
        $newId = $mysqli->insert_id;
        $stmt->close();

This does not insert anything, and $newId has no value.

So it seems I have a really silly question... I'm sure it's something really obvious, but I'm dead tired and can't figure it out.

I've tried...
$value2 = htmlspecialchars("O'Leary", ENT_QUOTES, 'UTF-8');
$value2 = mysqli_real_escape_string($mysqli, "O'Leary");
0
i have 64 names i need to enter into this table via code if possible.
I have a table with these names in a field caled "employee_names"

Or is there another way to add their names or re-design the table ?

Thanks
fordraiders
EMPLOYEE_SCORE.jpg
0
First, I'm a novice with SSIS, having mostly just maintained existing packages.  I have a project that I upgraded from SQL Server 2008 R2/Visual Studio 2008 to SQL Server 2016 (deployment model) in VS 2017.  All of the packages work running locally on my machine just as they always have, with the exception of one.  

This loops through oledb source servers  using a connection string built by variables received from a query.  Then it starts the control flows, which each has a series of data flow queries.    The weird thing is sometimes it runs fine.  Most of the time I get errors like this:

[NetComply - CollectionMembers [212]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Protocol error in TDS stream".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Communication link failure".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "TCP Provider: An existing connection was forcibly closed by the remote host.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Communication link failure".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  
0
I am trying to filter a result set based on a DATETIME column
select * FROM [ReportServer].[dbo].[ExecutionLogStorage]  where timestart>=2018-12-31 

Open in new window


here is the what is displayed typically for a TimeStart value
TimeStart
2018-08-03 09:08:24.937

Open in new window


however the filter is not correct, all rows in the table are currently returned.
Any guidance appreciated, thanks
0
Hello All

Here is the setup.  We have an Access database with an SQL backend.  The backend is on a server in our building.  The people in the building use the Access database as a front end (it is loaded with a lot of vba code).  Our salespeople, as opposed to our in house people, do not need all the vba and code, all they need is to be able to read the data in the SQL database.  I want to use ASPRunner.net to create an interface for them (because I only know Access and SQL). From what I understand ASPRunner.net can create a web interface to our SQL data.

The question is, can we keep the SQL backend on our server?  Or, in order to allow them to have access over the internet, do we have to move the SQL backend to the cloud.  We would prefer not to do that.

If anyone knows any other way to do it (besides remote desktop because we have many salespeople who could be on at the same time so sharing one or a few computers with remote access would be a nightmare) I would be interested to hear it.

Thank you!
0
SQL 2014  Cluster
Windows 2012 R2 Servers VM
VMware 6.5

I have two Node SQL Cluster Servers  all disks were iSCSI  connected to a NAS device Seagate Black Armor.

The Black Armor died and I lost two Dries.  but all my SQ Drives were on the two drives that are still working.

 But each of the Server could not connect to the drives.    After remove the target node and adding it back I was able to get most of the drives back.

I have three SQL instances running on both Nodes.

I have a new NAS device and setup all the LUNS and Targets now both servers have al the drives again.

I copied the data from the old NAS to the new NAS

In the cluster I was able to get the DTC back online

and one of the three instances are back online.

I had to add all the permissions back to the new drives folders

The other two instances I can get the SQL server service to start but it stops shortly after

I tried to run a SQL repair from the install DVD  that failed  

Feature failure reason:
An error occurred during the setup process of the feature.

Error details:
§ Error installing SQL Server Database Engine Services Instance Features
Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.
Error code: 0x851A001A
Visit …
0
PMI ACP® Project Management
LVL 13
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

I have a python script on python 3.7.2 that I have pieced together. The script will first accept user input for a string and store into a variable and then iterate through each function call.

Depicted here:

while True:
    uin = input("Please enter a uin or 'enter' to quit: ")
    if not uin:
        break
    startTime = datetime.now()
    selectCustomer(uin)
    selectVehicle(uin)
    selectHRO(uin)
    selectHLABOR(uin)
    selectHPARTS(uin)
    selectINV(uin)
    selectSUPPLIER(uin)
    selectLABOR_OP(uin)
    selectSOURCE(uin)
    selectRO(uin)
    selectLABOR(uin)
    selectPARTS(uin)
    selectEHRO(uin)
    selectEHLABOR(uin)
    selectEHPARTS(uin)
    endTime = datetime.now() - startTime
    print('It took', endTime, ' seconds to run...')

Open in new window


Each function (for the most part is exactly identical and is composed of:

  • opening up a SQL connection
  • running a query
  • storing the output in an ordered dictionary
  • creating a JSON file and naming it with the user input (in our case UIN)
  • closing connection and file

Depicted here:

def selectEHPARTS(uin, output_dir=r"C:\Code\TestData"):
    driver = 'DRIVER={SQL Server};'
    host = 'SERVER=SQL2;'
    database = 'DATABASE=ROWSTAGE;'

    connectionstr = driver + host + database
    conn = pyodbc.connect(connectionstr)
    cursor = conn.cursor()

    cursor.execute("SELECT STORE_UIN, RO_NO, PARTNO, PARTDESC, ITEM_NO, 

Open in new window

0
I'm getting the following Event Viewer errors when trying to start the SQL Server (SQLEXPRESS) Service:

SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.


Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

TDSSNIClient initialization failed with error 0x2740, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Only one usage of each socket address (protocol/network address/port) is normally permitted.

TDSSNIClient initialization failed with error 0x2740, status code 0xa. Reason: Unable to initialize the TCP/IP listener. Only one usage of each socket address (protocol/network address/port) is normally permitted.

Server TCP provider failed to listen on [ 'any' <ipv4> 49172]. Tcp port is already in use.

I've tried restarting the Server but get the same result.
0
Experts,

I need to add a decode to a JOIN due to inconsistent values used in a table.
In table CL no value is stored as an asterix
in table PI no value is a proper null

...and DECODE(CL.INVT_LEV4,'*',null,CL.INVT_LEV4) = PI.INVT_LEV4

Open in new window


The decode above looks correct to me, but returns no data
If I use the same in a select the columns the proper (null) result.

Why is the join not working?
0
I'm trying to run a subquery that will only be valid if only 1 row exists in my subquery.

For example:
select o.*
from orders o
where o.a = 'a'
and o.c = 1
and exists (select a.a
from address a
where o.a = a.a
and only 1 row exists)

If I'm supposed to use a "having (a) = 1" I really don't know the syntax for it in a subquery.

Also, I know I could use a proc to easily solve this but unfortunately I don't have access to implement that so I need to do it in a SQL statement. If it's even possible, that is.

Would a case statement work for this?

Please note that I'm working on Oracle 12c if that helps any.

Thanks,
Larry
0
I am trying to write a SQL statement whereby I have a field named FormElementReference, which is the name of the question, and an associated field called value which holds the answer to that question.

I've written a case statement to try and achieve this, and store the separate values in separate fields for export to SSRS.

What I currently get is an row for each answer value.

Screenshot showing SQL with a row for each value.
SELECT DISTINCT
  FormResults.FormResultKey AS [FormResults FormResultKey]
  ,FormResults.FormID
  ,FormResults.FormReference
  ,UserForename + ' ' + UserSurname as [Full Name]
  ,FormResults.FormResultDate
 
 

,CASE WHEN FormResultDetails.FormElementReference = 'Date' then value else null end AS JobDATE
,CASE WHEN FormResultDetails.FormElementReference = 'Time' then value else null end AS TIMEFrom
,CASE WHEN FormResultDetails.FormElementReference = 'TEB' then value else null end AS TIMETo
,CASE WHEN FormResultDetails.FormElementReference = 'TEA' then value else null end AS TotalWorked
,CASE WHEN FormResultDetails.FormElementReference = 'JNO' then value else null end AS JobNumbers
,CASE WHEN FormResultDetails.FormElementReference = 'AA' then value else null end AS JobAddress
,CASE WHEN FormResultDetails.FormElementReference = 'Reason' then value else null end AS JobReason
,CASE WHEN FormResultDetails.FormElementReference = 'Managers' then value else null end AS ManagerAuthorised


FROM
  FormResultAnswers
  LEFT OUTER JOIN Documents
    ON …
0
Hello:

In SQL Reporting Services (SSRS), I have an "If, Then" formula shown in the first of two formulas pasted below.

I have this formula as an "expression" in a cell.  I want to multiply this cell times a value in another cell and have the product (multiplication result) appear in another cell.  I keep getting an error message that does not explain itself.  But, the error is clearly letting me know that I;'m not doing this correctly.

Specifically, I'm trying to tell SSRS to multiply this formula times another field.

This other field contains the second formula pasted below.

What formula do I use to accomplish this multiplication?

Thank you!

John


=If(sum(Fields!TRXQTY.Value)=0,0,sum(Fields!DEBITAMT.Value-Fields!CRDTAMNT.Value))
/ IIF(sum(Fields!TRXQTY.Value)=0,1,sum(Fields!TRXQTY.Value))


=Sum(Fields!TRXQTY.Value*(Fields!DEBITAMT.Value-Fields!CRDTAMNT.Value))
0

Query Syntax

54K

Solutions

20K

Contributors

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.