[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

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

Hi Experts ~
I'm looking for guidance on best practices when working with a database server, and a web server.
We have both and all our sites are hosted on the web server.
The work is all done on the d/b server and for many sites, .csv files are created from a SQL query.

My question:
Outside of reducing d/b server traffic - Is there any performance benefit on the site to storing those resulting .csv files on the web server vs. the d/b server?

Thanks
0
The 7 Worst Nightmares of a Sysadmin
LVL 1
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

I'm currently migrating a Microsoft Access client with a Microsoft Access backend to an Azure SQL backend.  I have a fully working version of the client, but that was before we implemented security with Application Roles.  In order to ensure the users are getting an elevated connection I've been slowly rewriting the client to use ADO recordsets with the SQL in the VBA rather than using access queries tied to linked views/tables and pass-through queries.  This has brought up several questions, but let me go ahead and just ask 2 for now and i can put the rest in another thread as they may not be fully related to this question (plus they're the most important).

Simple question first:
1) I am creating ADO recordsets that select views when forms load to tie them to the Form.recordset property.  I am using a client side cursor (Location) with adOpenDynamic (Type) and adLockOptimistic (LockType).  When I do this the forms are no longer editable.  I've confirmed in VBA that the recordset.iseditable property is true for the SQL statement.  In most cases the SQL statement is as simple as "SELECT * FROM <view name>".  The view is editable in SQL Server Management Studio, and the linked view is editable if I'm using an admin account that I don't need to elevate via application role so I know it's not an issue with joins.  Is there some other curor/recordset setting I'm missing to ensure that when I bind an ADO recordset to a form it remains editable?

2)  I also have a more …
0
Hello,

I have created SQL job using below script to execute every 30 minutes interval from 12:00:00 AM and 11:59:59 PM. The job will create new CSV file on shared folder for every 30 minutes. Instead of creating new CSV file (multiple CSV) every time is it possible in SQL to concat (append) new data on existing file like below? If its possible in MS SQL, can you please share the sample script.

Like: data should be appended like below on single file:

Execution DateTime: 12102018 20:00
Count: 10

Execution DateTime: 12102018 20:30
Count: 10

Execution DateTime: 12102018 21:00
Count: 15

Execution DateTime: 12102018 21:30
Count: 10
DECLARE @FileName VARCHAR(500) = 'E:\FileShare\Count_' + FORMAT(getdate(), 'ddMMyyyy_HHmm') + '.csv'
DECLARE @SQLCmd VARCHAR(500) = 'bcp "select count(*) from DBName.dbo.IB where Notes = ''BO'' and CreatedOn between ''2018-11-22 00:00:10.000'' and ''2018-11-22 23:59:10.000''" queryout ' + @FileName + ' -T -c'
EXECUTE master..xp_cmdshell @SQLCmd

Open in new window

0
I have an InfoPath form connected to a SQL database.  I have two fields in the Query window for the user to choose from in retrieving records.  Previously I had three fields, but it was decided that the user did not ever query on the third field.  When I try and query with the new form on just the two fields, I get an error stating that there are no records.  I know that there is a record based on those 2 fields.  When I go back and use an older version of the InfoPath form and enter just the two fields, the record is returned.

What have I not done properly in modifying the form to only have the two fields to query on?

Any and all help would be greatly appreciated.
0
Hello,

My requirement:
1.      I have to fetch number of records created in half an hour interval from 12am to 11:59pm in every 30 minutes.
2.      We have make it dynamic to run every 30 minute.
3.      This job will give a report for everyday and every 30 mints interval in csv format

To achieve above, I have created below SQL but it's giving error. Can you please help me to fix below error.
DECLARE @FileName VARCHAR(500) = 'E:\FileShare\YourData.csv'
DECLARE @SQLCmd VARCHAR(500) = 'bcp "select count(*) from IB where Notes = ''M_BO'' and CreatedOn between ''2018-11-22 00:00:10.000'' and ''2018-11-22 23:59:10.000''" queryout ' + @FileName + ' -T -c'
EXECUTE master..xp_cmdshell @SQLCmd

Open in new window

Error:
NULL
Starting copy...
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid object name 'IB'.
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 11 for SQL Server]Unable to resolve column level collations
NULL
BCP copy out failed
NULL
1
Hi

Through the advice from this plat form I have moved all the ms access tables to Ms Sql server express with some amendments of course on the sub forms and data type which were a problem, I avoided the pass through queries due to some challenges on updating. So I'm using the less DSN File and ODBC 17 which is working fine and the speed is no different with Ms Access Back-end:

Deployment:

(1) I want to deploy this application to a client outside our network, this mean there will be a new server to host the tables, now correct me if I,m wrong here:

(a) I will download an MS Sql Server Express on a nominated computer to act as a server where all other 50 work stations will be linked
(b) On the 50 work station I will install the same ODBC 17 and MS Access runtime 2016 if no full license available
(c)  The ODBC connection string  will now change or I will replace my computer name currently showing to the new computer or server name.  After that before logging out I will compile the application to accde, I know after make an accde it may want to log in, but at this point I expect to fail because of the change to host computer, but when I install it to one of the work station mentioned above I expect it to located the correct server at that point.
(d) Anything else?

MS Access 64 Vs 32

Here I have serious problems most of my clients are now shifting 64 BIT office , now I understand there is a code out there that can allow a 32 BIT written application to run on …
0
In SQL I was hoping someone could explain the difference between % and _.

Thanks
0
Hi,
I have to create the following table in postgreSQL :
create table rbl_kyc_profile (
  id int8 not null,
  userid varchar(50) not null,
  kyctype enum ('PAPER','E_KYC','MINIMAL','SHORTFALL')
)

Open in new window


I got an error : SQL Error [42704]: ERROR: type "enum" does not exist
 
I saw on postgreSQL documentation page : https://www.postgresql.org/docs/9.2/datatype-enum.html
That one first needs to create an enum type in postgreSQL.
This is very different from MYSQL where you can just create a column of enum type by specifying the values...
I have the following questions..

1) Since the kycType in my java classes is an enum. And it has a fixed set of values should i create an ENUM in postgre for it or just create a varchar for it ?
2) Because postgreSQL uses enum types in a different way than MySQL does what impact it has if i create the above kycType as an enum type and then use it in the table ?

Thanks
0
I am developing an employee database. I added all employees to an organisational unit (department etc) - and each organisational unit belongs to another organisational unit, except for the top one which is blank; the CEO. With a self-join, I can then create a hierarchy once all the data has been added.

 
 screengrab2.png

I tried creating a function which would populate a collection from the uppermost organisational unit (the CEO) all the way down and I used recursions to traverse the tree.

Function preorderProcessing(parentID As Integer)

    Dim MyDB            As DAO.Database
    Dim MyRS            As DAO.Recordset
    Dim x               As Integer
    
    Set MyDB = CurrentDb()
    Dim qdf As DAO.QueryDef
    Set colOrgUnits = New Collection
    
    If parentID = 0 Then
        Set qdf = MyDB.QueryDefs("qyrOrgUnits_SearchParentID_Null")
    Else
        Set qdf = MyDB.QueryDefs("qryOrgUnits_SearchParentID")
        qdf.Parameters("parentID") = parentID
    End If
    
    Dim rs As DAO.Recordset
    Set rs = qdf.OpenRecordset
    
    Do While Not rs.EOF
      colOrgUnits.Add (rs.Fields("org_OrganisationUnitID"))
      rs.MoveNext
    Loop
    rs.Close
    
    'debug
    Debug.Print "Now checking units: "
    For x = 1 To colOrgUnits.Count
        Debug.Print "In this stack: " & colOrgUnits.Item(x)
    Next x
    
    Debug.Print "COUNT: " & colOrgUnits.Count
    If colOrgUnits.Count > 0 Then
        For x = 1 To colOrgUnits.Count
            

Open in new window

0
I would like to read JSON URL link from SQL SERVER. I would like to build a stored procedure by passing JSON URL Link and would like to store output into a SQL SERVER table.
0
SolarWinds® Network Configuration Manager (NCM)
SolarWinds® Network Configuration Manager (NCM)

SolarWinds® Network Configuration Manager brings structure and peace of mind to configuration management. Bulk config deployment, automatic backups, change detection, vulnerability assessments, and config change templates reduce the time needed for repetitive tasks.

How can you tell if a SPOC is signed with a certificate?

Working on a dbase and apparently a certificate was used.  Having issues accessing data from the database and believe this is the issue.
0
I want to be able to multiple QTY_OH * UNIT_COST display the column as AMOUNT and group by syscm_auto_key, gla_auto_key and AMOUNT using the following statement below.
Attached is a screenshot of the results.

select st.syscm_auto_key, st.stm_auto_key, st.pn, st.qty_oh, st.unit_cost, st.gla_auto_key, account_number
from stock st
JOIN gl_account  gla
ON st.GLA_AUTO_KEY = gla.GLA_AUTO_KEY
where qty_oh > 0 and unit_cost > 0
group by st.syscm_auto_key, st.stm_auto_key, st.pn, st.qty_oh, st.unit_cost, st.gla_auto_key, account_number
order by syscm_auto_key, gla_auto_key

Open in new window

Capture.JPG
0
I have this SQL Code and converted to the below LINQ to Entities.  I generated the LINQ code but think there must be a better way.  I am trying to implement a Sequence column value.   The table already has an identity column and  a sequence for each item

SELECT MAX(COALESCE(FailureSequence, 0)) + 1 AS NextSequence
FROM FunctionalFailures
WHERE(FunctionalAnalysisId = 2);

Open in new window


and this LINQ

from FunctionalFailures in
(from FunctionalFailures in db.FunctionalFailures
where
  FunctionalFailures.FunctionalAnalysisId == 2
select new {
  Column1 = ((Int32?)FunctionalFailures.FailureSequence ?? (Int32?)0),
  Dummy = "x"
})
group FunctionalFailures by new { FunctionalFailures.Dummy } into g
select new {
  NextSequence = (g.Max(p => p.Column1) + 1)
}

Open in new window

0
Just a question regarding the best direction and tools to use. I have published reports to sharepoint using PowerBi and the Microsoft Gateway and the schedule allows me to refresh the data to the web. All works well.  
However, I have an excel workbook with a pivot table linked to an SQL database. Company management would like to be able to view this spreadsheet remotely. I am looking for direction on what are the steps/ tools required to publish an excel workbook to Office 365/sharepoint and maintain the ability to refresh the data from the onsite SQL server either by request or on a schedule.
I have looked a the PowerBi matrix but it doesn't really give me the same level of flexibility as an excel pivot table.
Thanks in advance.
0
Hi All Experts,

I want an oracle pl/sql scripts/procedure/package anything compatible to oracle 12c, which can help me to send the result of a query over my email.

For example: Select * from employee;

1. If this query returns any rows, the count will be emailed to a@b.com
2. The result of this query will stored in excel and the excel file will mailed to a@b.com
0
Hi,
I have been tasked with a project where we have 4 identically constructed databases with different customer data in each running on premises using MSSQL and Microsoft Dynamics 2016.  I need to export specific customer data from each of the 4 databases and import that data into a MySQL database that is hosted off site with our web site. This data will then be periodically uploaded to the website database either with triggers or at a timed interval.

As I have very little experience with this type of database project, I am seeking information on how this can be done and what basic steps are needed in order for this to work.  Any additional details would be appreciated.
Thank you!
Troy
0
Hi,
I am using the following to populate an SSIS variable
SELECT ? = CAST(MIN_ACTIVE_ROWVERSION() AS BINARY(8))

Open in new window


When I run this in BIDS it returns a non specific error.
When I run the code in SQL 2005 management studio (minus ? =) it returns the value as expected.

Anyone any ideas on why this is not working?

Any help would be appreciated.

Thanks
0
We have 5 machines, each machine inserts a record to a SQL database every second.
At the record generation time, it writes the order number, machineID, a machine status (either U up or D down) and the time period).
Machine data is attached.

Ref:
(note that ordernumber is 7 digits and the next digit is machineID, so first line order is 2633448 and machine is 2)
OrderNumber      Machine ID      Status      TimePeriod
2633448      2      U      2018-12-07 08:08:13.000
2601441      4      U      2018-12-07 08:08:12.000
2637183      3      D      2018-12-07 08:08:08.000
2624657      5      U      2018-12-07 08:08:08.000
2633448      2      U      2018-12-07 08:08:03.000
2601441      4      U      2018-12-07 08:08:02.000
2637183      3      D      2018-12-07 08:07:59.000
2624657      5      U      2018-12-07 08:07:59.000
2633448      2      U      2018-12-07 08:07:54.000
2601441      4      U      2018-12-07 08:07:52.000

What I'm trying to accomplish is a report of time-based data showing when the machine switches status and the total duration of the status.
Something like this:
OrderNumber      Machine ID      Status      Status End      Status Start      Duration
2633448      2      U      2018-12-07 08:08:13.000      2018-12-07 07:57:10.000      0:11:03
2633448      2      D      2018-12-07 07:57:49.000      2018-12-07 07:57:42.000      0:00:07
2633448      2      U      2018-12-07 07:57:41.000      2018-12-07 07:57:11.000      0:00:30

As part of the first step, my thinking is to find the points where the status changed using a lag.
Like this...

Select 
[OrderNumber],
[MachineNumber],
Status,
Lag(Timeperiod, 1) Over (partition by Ordernumber, [Status] order by OrderNumber, TimePEriod) As 

Open in new window

0
Hi

Having worked through the SQL Server as a backend to Ms Access FE I want to make a code that will check the connection strings that is valid from the access FE module, for example if the FIRST string below is wrong then it should check the SECOND string or return connection FAILED result:
(1)      sConnect = "ODBC;DRIVER={sql server};DATABASE=YourDatabaseName;SERVER=YourServerName;Trusted_Connection=Yes;"
(2)      strConnect = "ODBC;Driver={SQL Server};UID="& Me.txtUser & ";PWD=" & Me.txtPwd & ";"
From the above situation how do I use CASE SELECT  , END SELECT  in a VBA code? Do not worry about a full code I have a working code intact, its working fine.
I have put the same working VBA code in a module and is call by a macro called autoexe, now what to achieve with suggested selection is that before deployment to some clients I have to collect some as follows:
(1)      Computer Number ( for security VBA code purpose)
(2)      Computer name ( to used in the connection string above )
The current VBA will continue working silently without the user knowing what is happening, so in short string (1) will have my computer/server details while string (2) while have the new details for the new client computer name (Where the application will be deployed). The reasons for the above suggestion is as follows:
(1)      If I compile the application based on my computer connection it will fail to connect the new client computer because the server name will be different , hence the required  2 string …
0
IT Degree with Certifications Included
IT Degree with Certifications Included

Aspire to become a network administrator, network security analyst, or computer and information systems manager? Make the most of your experience as an IT professional by earning your B.S. in Network Operations and Security.

Need to combine the values of two columns(ro_dept and wo_dept) into one column DEPT_NAME with their corresponding record.

Ex. Instead of:
SM_NUMBER     ORDER_TYPE    WO_DEPT     RO_DEPT
       1000                  WO               SH ELE
       1100                  RO                                    PURCH

Need to see:
SM_NUMBER     ORDER_TYPE    DEPT_NAME
       1000                  WO               SH ELE
       1100                  RO                PURCH
 
Any help will be appreciated.

SELECT
    sm_header.sm_number,
    DECODE(sm_header.woo_auto_key,'',DECODE(sm_header.soh_auto_key,'',DECODE(sm_header.roh_auto_key,'',DECODE(sm_header.whs_from,'',''
,'WHS'),'RO'),'SO'),'WO') order_type,
    DECODE(sm_header.woo_auto_key,'',DECODE(sm_header.soh_auto_key,'',DECODE(sm_header.roh_auto_key,'','',ro_header.ro_number),so_header
.so_number),wo_operation.si_number) order_number,
    sm_status.description status,
    sm_header.entry_date entry_date,
    sm_header.date_created,
    sm_header.ship_date ship_date,
    sm_header.ship_timestamp,
    sm_header.ship_name,
    ship_via_codes.ship_via_code,
    sm_header.tracking_number,
    sm_header.number_of_packages,
    so_header.dpt_auto_key,
    sm_header.total_weight,
    sm_header.ship_date - sm_header.entry_date time_to_ship_days,
    round( (sm_header.ship_timestamp - sm_header.date_created) * 24,2) time_to_ship_hours,
    warehouse_from.description whs_from,
    warehouse_to.description whs_to,
   

Open in new window

0
I trying to set up a job with SQL Server to send an email out whenever the Builder's General Contractor Ins Exp Date is coming up for expiration or expired.  If it is expiring/expired to send an email out to the specific contractor in the email column letting them know of it expiring.  I have set up database mail and tested that it is working.

How do I do this?
Capture.PNG
0
I'm wondering if it's prudent to adjust the Database "Initial Size" value for my environment?
SQL version:  2014
Recovery model:  Full
Database Size:"   158GB

I recent ran a utility which indicated to me that: "89 growths took more than 15 seconds each. Consider setting file autogrowth to a smaller increment."
Autogrowth / Maxsize setting appears to be default of:  10% unlimited, and we DO see unexplained performance issues on this database.
Does it make sense to calculate an anticipated DB size,(for say, 1-3 years down the road?) reset Initial Size to that size, and keep the autogrowth setting as-is?
I've also seen on Microsoft's site that the growth % should be about 1/8" the size of the database...Should I change autogrowth from 10% to around 13GB?

Thoughts?
0
in Oracle sql developer, how do you find the retention period associated with the Recycle Bin of a database?
0
I'm using MS Access 2000

When my customers place an order they specify a manufacturer and a manufacturer part number.
Each line item (manufacturer and Manufacturer Part number therefore has a line item price (quantity * unit price)

I need help writing a query that will only show me the most expensive line item purchase from each customer.

For example, if a customer buys 100 pieces Motorola 2N2222 which cost .10 each the line item cost is $10
if that customer also buys 500 pieces of Texas Instruments SN7400N for .20 each the line item cost is $100
My report should generate a row that looks like:
01234 John Doe Texas Instruments $100
01235 Bill Doe    AMD $50

and so forth

Here is a query that lists all parts purchased.  I need to modify it to only display the largest purchase (one row for each customer)
Can anyone help???

SELECT ManufacturerMaster.ShortName, InvoiceHistory.PartNumber, Max([Price]*[quantity]) AS Amount
FROM InvoiceHistory INNER JOIN ManufacturerMaster ON InvoiceHistory.LineID = ManufacturerMaster.LineID
GROUP BY InvoiceHistory.CustomerNumber, ManufacturerMaster.ShortName, InvoiceHistory.PartNumber
HAVING (((Max([Price]*[quantity]))>0));
0
extra space replacing value when using             text_io am having vdate value replace by space
V_DCN := 	'ZD'||V_MOE_CODE||rpad(nvl(V_DATE,' '),5,' ')||'18'||V_DCN_SERIAL_NO;

Open in new window

i have attch the full code and file
INT.REQ-5605-18340.TXT
sapace12.jpg
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.