SQL

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

When a client purchases SQL 2019 by Core which version of SQL 2019 should I install?
SQL Standard Edition - Per Core
SQL Standard Edition - Server/CAL

I installed it per core but then when I try to install SSRS and use the product ID it tells me that it is not a valid product ID. Plus when I download off the MS VLSC there appears to be only generic downloads. When I click on Key it says no keys are required for this product.  I have entered the client's license key and authorization number but no where else do I see anything else specific to this client.
0
Success in ‘20 With a Profitable Pricing Strategy
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Hi Experts,

In Access, is there a criteria that filters anything that doesn't start with a number?
0
Hi,
I am following a tutorial on SSAS that was created using SQL Server 2016.
I am trying to find  a 2016 Developer Edition to download but Microsoft's site seems to only have the 2019 version
Any guidance appreciated.
Thanks
0
Hi Experts,

I'd like to know if it's possible to take the value of a combobox, search the value in a table and then return values related to that value?

IE:
1. In frmInspection, we select the value "3035" and then we click on the button "Specification".
2. The same value is applied to the cbo in frmSpecification.
3. A box containing the column C1, C2, C3, C4, C5,C6 is populated, (3082, 3052, 3034, 3035, 3150,3157) (Vlalues from the same row as "3035")

If 3149 was in the combobox, we would have: 3069, 3149, 3114, 3159, 3061, 3031 instead.

Is there  a way to do this?
Equipment.xlsx
Planning.accdb
0
Hi EE,

For the following code

begin tran

create table #UserList (fldUserName nvarchar(max) collate SQL_Latin1_General_CP1_CI_AS)
insert into #UserList (fldUserName) values

DELETE from tblPERMS_Incidents where Username_Incidents collate SQL_Latin1_General_CP1_CI_AS in (select fldUserName from #UserList)

drop table #UserList
rollback tran

Open in new window


Is there a method of rewriting this so I am not limited to deleting 999 rows at one time?

Any assistance is welcome.

Thank you.
0
I'm trying to remove the 1st 3 characters of a text field if the 3rd character is a dash (-) and the 1st 2 characters are numbers in an Access query or VBA. See attached sample result.


Image
0
We have a warehouse that has many locations in the database.  The locations are varchar values as below:
10A1,10A2,10A3,10A4,10A5,10A6,10A7,10A8,10B1,10B2,10B3,10B4,10B5,10B6  ETC.

We want to find the nearest location to say 10A7, we started with the below but it doesn't work:



declare @loc nvarchar(20)
set @loc = '10A7'


SELECT TOP 5 loc  
         FROM   location_mst
              where   loc <= @loc
             and description = 'pa1'
         --ORDER  BY  loc ASC
             union all
            SELECT TOP 5  loc
         FROM   location_mst
           where   loc >= @loc
              and description = 'pa1'
         ORDER  BY loc DESC


Does anyone know of a good way to get the results, we are looking for.  The table houses about 3500 locations all the same naming convention.
0
Does TDE encrypt  just the database or the entire SQL server?     I need to have just one of our databases encrypted (for now)  and need to make sure using TDE only that database is affected.

I currently have one database where I am encrypting a single column but the one in question is a separate database on the same server that also has many other databases.
0
Following on from here I've removed the "\n" from the end of file.path according to the code bellow it has left me with a lot of duplicate  entries

SELECT a.id, a.path
  FROM "file" a
  JOIN (
         select path, COUNT(*) AS path_cnt 
           FROM "file" 
          GROUP BY path
         HAVING COUNT(*) > 1
        ) b ON a.path = b.path

Open in new window


How might I generate a list of id's leave 1 unique id,path in file?

the idea here is I can run two queries from this list

delete FROM movielinkfile where fileId in (<id_list>);  these are  foreign keys 
delete FROM "file" where id in (<id_list>);

Open in new window

0
Hi Experts,

I have a table with data in which I'd like to have a "Clickable" list of Equipements.
When an equipment is selected, it'll populate all the information related to that specific equipment to a form.
IE:
I'd like
E1
E2
E3
...
E16
to be in a selectable rectangle and when an equipment is clicked, it selects the form Inspections and it fills all data related to it.
Is there a way to do it without drop box/list ?
Planning.accdb
0
How to Generate Services Revenue the Easiest Way
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

How can I group the following by month and day. I can make it work by months but this groups month in all the years in one record.

SELECT SWD_SCADA_Daily.[Well Name], Sum(SWD_SCADA_Daily.[Water Injection (bbl/day)]) AS CumInjection, Avg(SWD_SCADA_Daily.[Injection Pressure (Psi)]) AS AverageDischargePressure, Avg(SWD_SCADA_Daily.[WHP (Psi)]) AS AverageWHP, MONTH(SWD_SCADA_Daily.[Time/Date]) INTO SWD_SCADA_MONTHLY
FROM SWD_SCADA_Daily
GROUP BY SWD_SCADA_Daily.[Well Name], MONTH(SWD_SCADA_Daily.[Time/Date]);

Thank you.
0
Can someone tell me which tool is used for diagram. Looks so familiar to me, but can't remember what is it?

l6Ar7.png
0
I am receiving NULL values when calling a function within a view. The name of the function is RC_EXCUSABLE_DELAY_V2. I have attached the SQL for the view and the function. I have also included sample data that should have values for RC_EXCUSABLE_DELAY_V2. Thank you in advance for any help.

View:
select wo_sum2."WO_NUMBER",wo_sum2."WO_TYPE",wo_sum2."RC_NUMBER",wo_sum2."NOTES",wo_sum2."PEL_TAIL_NUMBER",wo_sum2."MANUAL_TAIL_NUMBER",wo_sum2."AMDT_MOD_IN", 

Open in new window

0
How do I implement full join in access. I want to see data from both table joined by two fields.

SELECT SWD_NDIC.*, SWD_SCADA_Daily.*, SWD_NDIC.[Well Name]
FROM SWD_NDIC full JOIN SWD_SCADA_Daily ON (SWD_NDIC.[Well Name] = SWD_SCADA_Daily.[Well Name]) AND (SWD_NDIC.Date = SWD_SCADA_Daily.[Time/Date])
WHERE (((SWD_SCADA_Daily.[Well Name])="Test") AND ((SWD_NDIC.[Well Name])="Test"));
0
Hi

I have a movie database some how I've got some file path with a new line character

Like this
C:\Path\To\MyFile.mp4\n

Open in new window


How do I locate these?

this caused
Msg 402, Level 16, State 1, Line 12
The data types varchar and char are incompatible in the '|' operator.

SELECT * FROM "file" WHERE path like '%' | char(13) | '%' or path like '%' | char(10) | '%';

Open in new window


Once I've found then I'll need to remove the "\n";
But I think there is going to be duplicate
and the file . id is a foreign key


BTW It is a MS SQL database
0
Can someone please help me to understand why I am getting this error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'Lab@xyz.org'.

/trainingSessions/SQLRuns2.asp, line 58


 strSQL3 = "UPDATE _Employees SET Pager = e.LName + ""Lab@xyz.org"" FROM _Employees e INNER JOIN _Department d ON e.reportsto = d.DeptHead WHERE e.isactive = 1 AND e.isScientific= 1 AND e.reportsto = " & Session("strPIID")                                        
 set rsEMP = oConn.Execute(strSQL3)


This is the result in SQL Server Profiler:

UPDATE _Employees SET Pager = e.LName +  "Lab@xyz.org" FROM _Employees e INNER JOIN _Department d ON e.reportsto = d.DeptHead WHERE e.isactive = 1 AND e.isScientific= 1 AND e.reportsto = 7165
0
I'm using the following code and receiving Access error code:

Error

SELECT
CASE 
WHEN [tblConsolidated].[Appeal Category L3] IN
('180 MEDICAL INC',
'180 MEDICAL INC',
'180 Medical Inc.',
'180 Medical, Inc',
'180 MEDICAL, INC.')
THEN '180 MEDICAL INC'
Else [tblConsolidated].[Appeal Category L3];

Open in new window

0
Hi Folks,
I have written a SQL code generator where end users can define their own tables independent of developers. These are financial tables and so the structure can be quite weird and row size can exceed 8k. To tackle this for these tables i am using NVARCHAR(MAX) as data types for the columns and also using  
EXEC sys.sp_tableoption 'dbo.table_names','large value types out of row','ON'

Open in new window

but still i get the following error message.
Cannot create a row of size 9307 which is greater than the allowable maximum row size of 8060.

Open in new window

Can you please help?

I have attached the file of one of the tables

regardsTable.sql
0
I have a SQL Server Reports database (mdf & ldf) that survived the crash of a SQL server which had to be rebuilt.  The tables are valid.  I can do queries on them, such as

select * from catalog

the most important table, as I understand it.

But I cannot use the database as a data source in my newly built SQL Server.  I get an error trying that.

So I built
0
11/26 Forrester Webinar: Savings for Enterprise
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

I'm trying to extract from AX the above data, does anyone know which tables I need to use and how to link. I do not have access to the AX client only the SQL database.

I'm being told to use CUSTTABLE, EMPLTABLE AND DIREPARTY table but I cannot work out how to link these.

Can anyone help?
0
Hey there!

I'm using a DENSE_RANK function in MS SQL to group by a datetime +/- 2hrs and it's working really well but I need to adapt it to Oracle and I'm having some trouble making that happen.

Here is the TSQL:
MyRank = DENSE_RANK() 
OVER (ORDER BY 
MyPID,MyAgency,MyCCD,(DATEADD(hour, (DATEDIFF(hour, '', MyDtTime)/2)*2,'')))

Open in new window


I tried a TSQL to Oracle converter thing but that's not getting it.                    

Any help will be greatly appreciated.

Thanks!
0
Hi Experts,

I want to create a calculated field (or formula) that will show which of the field/s on the table is null for that record.

So for example if FieldA, FieldC and FieldE are null, the value of that calculated field should display "FieldA, FieldC, FieldE".

Need this in SQL 2008 compatible syntax.

Thanks
0
Hi,

I have SQL ssrs standard (which has feature klinkt available) but would need some kpi data on the top of my report.
F.e. number of items in report, number of items with certain value, number of items with other value etc

How can I do this?

J
0
Hi all i am like 99% finshed this pain in the but script but i am now getting a error mysql.connector.errors.ProgrammingError: 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

for the life of me i cant work it out and i tihnk i am going half blind just looking at the code can you please help me out

from requests_html import HTMLSession
import mysql.connector



mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="*****",
  database="flightdata"
)




mycursor = mydb.cursor()

# create an HTML Session object
session = HTMLSession()

# Use the object above to connect to needed webpage
resp = session.get("https://www.adelaideairport.com.au/flight-information/flight-search/?flt_no=&carrier=All&city=&dte=Current&leg=Departures")

# Run JavaScript code on webpage
resp.html.render()
data = []

airline_spans = resp.html.find('.SearchResultFlightListRow')
print (airline_spans)
airline_list = [span.text.split('\n') for span in airline_spans]

for flight in airline_list:
    if len(flight) == 7:
        flightno, From, to, scheduled, estimated, gate, status = flight
        print ("This is a " + estimated)
        if estimated == "":
            print (" currently no dely ")
            print ("This is a " + estimated)
            estimated = 'IDEL'
    #    print (f'Flight no {flightno} from  {From} to {to} is scheduled to depart at {scheduled} from 

Open in new window

0
Hi Experts,

I have a table with 2 columns (Last name) and (First name).

I'd like to extract all the first name that has the same value as another column value.

IE:
The db is:
First name  |  Last name
Olivia                  Jones
Jones                  Mertil
Nicholas             Jay
Avril                    Lavigne
Charles              Brown
Annie                 Charles

The result would be the table

First Name | Last Name
Jones               Mertil
Charles           Brown


Thank you!
0

SQL

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.