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

Hello,

I would like to display columns from a table for only the ones selected in my business logic, how do I modify my code to achieve this? and also how do I search the table by entering data in a textbox?

1, Home Controller

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using AOP6TEST.Models;
using static DataLibrary.BusinessLogic.AOP6Processor;

namespace AOP6TEST.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            return View();
        }

        public ActionResult ViewAOP6()
        {
            ViewBag.Message = "AOP-6 Data";

            var data = LoadAOP6();
            List<AOP6Model> AOP6 = new List<AOP6Model>();

            foreach (var row in data)
            {
                AOP6.Add(new AOP6Model
                {
                    SN = row.SN,
                    AGD1 = row.AGD1,
                    COUNTRY = row.COUNTRY,
                });
            }

            return View(AOP6);
        }
    }
}

2, Business Logic
using DataLibrary.DataAccess;
using DataLibrary.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DataLibrary.BusinessLogic
{
    public static class AOP6Processor
    {
        public static List<AOP6Model> LoadAOP6()
        {
            string sql = …
0
Fundamentals of JavaScript
LVL 13
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

I have a BLOB field in SQL Server that I need to be converted to a usable format. I have so far gotten nothing but unreadable characters. It is supposed to be a series of geographic points.

Open in new window

Query Results
Below is an example of one of the values in the BLOB field


Open in new window

0
Is there any way to increase the number of viewed columns from 655 to 900 (e.g.) ?
0
Some time ago we migrated some databases from SQL Server 2012 running on Windows Server 2008 R2 (Databases are in an Availability Group) to SQL Server 2016 running on Windows Server 2016.

The new server is running on All-Flash storage.

There are a pair of web servers running a web application, these servers are Windows Server 2016 and it is a PHP web application running in IIS.

Since migrating there has been a noticable reduction in page load times, particularly when saving data.

Typically it was < 5 seconds, but now has increased to >10 seconds.

We use ExtraHop to monitor our wire data and can see that round trip times to the DB servers is <100ms, however the application executes a significant amount of queries per save operation, so the comulative effect could be a part to play in the performance issues.

We have disabled Anti-Virus, but this had no effect.

What would be the suggested places to look to try and identify where the bottleneck is?
0
Hi experts,

since the sccm server was turned off unexpectedly due a failure on the cluster, the sccm console cannot connect to the site. The console is running on the sccm server.

sccm-failure.png
What i've already tested:
SQL
-Restart SQL Instance
-ODBC connection to the sccm database -> successfully
Check permissions
- DCOM Permissions for Configuration Manager Console Connections
- Verify WMI Permissions
- Verfify "System" Group in ACL on "C:\Program Files\Microsoft Configuration Manager\Logs"

Restart SCCM Server


Log in Eventlog:
Logname: Application
Source: Critical
ID: 1

Transport error; failed to connect, message: 'The SMS Provider reported an error.'\r\nMicrosoft.ConfigurationManagement.ManagementProvider.WqlQueryEngine.WqlQueryException\r\nThe SMS Provider reported an error.\r\n   at Microsoft.ConfigurationManagement.ManagementProvider.WqlQueryEngine.WqlQueryResultsObject.<GetEnumerator>d__74.MoveNext()
   at Microsoft.ConfigurationManagement.ManagementProvider.WqlQueryEngine.WqlConnectionManager.Connect(String configMgrServerPath)
   at Microsoft.ConfigurationManagement.AdminConsole.SmsSiteConnectionNode.GetConnectionManagerInstance(String connectionManagerInstance)\r\nConfigMgr Error Object:
instance of __ExtendedStatus
{
      Operation = "ExecQuery";
      ParameterInfo = "SELECT * FROM SMS_Site WHERE SiteCode = '<**SITENAME***>'";
      ProviderName = "WinMgmt";
};

Error Code:
ProviderLoadFailure
0
Hi,
I need to know this date format is yyyy-mm-dd?
DECLARE @DueDate nvarchar(30)

SELECT RIGHT(DueDate,4)+'/'+SUBSTRING(DueDate,4,2)+'/'+LEFT(DueDate,2)
0
What is the # of bytes for largest floating point number?
0
I am trying to get data from the database.  I have the following tables:  PO, POC, Inventory.  

The fields in each table are:

PO.id_purchaseOrder

POC.id_purchaseOrder, POC.id, POC.id_componentLibrary, POC.id_batch

Inventory.id, Inventory.id_purchaseOrder, Inventory.id_componentLibrary, Inventory.id_batch

The sample code below is letting me look at the data in the records.

SELECT * FROM PO WHERE id_purchaseOrder = 4893
SELECT * FROM POC WHERE id_purchaseOrder = 4893

SELECT Count(*) FROM Inventory
WHERE id_purchaseOrder = 4893 
AND id_batch = 139 
AND id_componentLibrary = 1728

SELECT Count(*) FROM Inventory
WHERE id_purchaseOrder = 4893 
AND id_batch = 139 
AND id_componentLibrary = 1255

SELECT Count(*) FROM Inventory
WHERE id_purchaseOrder = 4893
--AND id_componentLibrary NOT IN (1255, 1728)
AND id_batch NOT IN (139)

Open in new window

The below code is just a sample and gets me the record counts in inventory.
SELECT
	PO.id_purchaseOrder
	,PO.status
	,PO.purchaseNumber
	,PO.dateCreation

	,(SELECT COUNT(*) 
		FROM Inventory C2 
		WHERE C2.id_componentLibrary = POC.id_componentLibrary
		AND C2.id_purchaseOrder = POC.id_purchaseOrder
		AND C2.id_batch = POC.batchID
	 ) AS CQty

Open in new window

What I need to find is the quantity in the Inventory table that is related to the PO table but may not match the id_componentLibrary and id_batch fields.

How do I add a query to find the inventory for the PO that does not match the id_componentLibrary and/or id_batch
0
sql server 2008

I need a random sample from this query..of 300 records.
The below query cte is working ok, but taking 3:00+ to return.

any suggestions to pick a random sample faster for this query ?

WITH CTE AS(
SELECT TOP 100
cd.[PROJECTID]
,cd.[CUSTOMER_MFG_VENDOR_NAME] AS MFG_VENDOR_NAME
,cd.[customer_mfg_vendor_partnumber] AS MFG_VENDOR_PART_NUMBER
,cd.[CUSTOMER_ITEM_DESCRIPTION] AS DETAILED_ITEM_DESCRIPTION
,cd.[customer_wwg_sku] as SKU_PROVIDED
,cd.[CUSTOMERPARTNUMBER] AS CUSTOMER_PART_NUMBER
,CD.[CUSTOMERPRICE] AS TARGET_PRICE
,CD.[UOMQTY] AS UOM_PKG_QTY
,CD.[CUSTOMER_ADDITIONAL_INFO1] AS ADDL_INFO1
,CD.[CUSTOMER_ADDITIONAL_INFO2] AS ADDL_INFO2
,CD.[CUSTOMER_ADDITIONAL_INFO3] AS ADDL_INFO3
,hd.SKU as WWG_ITEM_NUMBER
,HD.SEARCHSTATUS AS CROSS_REFERENCE_CODE
,cp.wwgskuprice as CUSTOMER_PRICE
,CP.CSPIDENTIFICATION AS CSP_IDENTIFICATION
,CP.PRODUCTVARICOMMENTS AS PRODUCT_VARIANCE_AND_COMMENTS
,HD.MATCHTYPE AS MATCH_TYPE
,CD.[LASTMODIFIED]
,CD.[LINEITEMID]
,hd.UPDTTYPE as ASSOC_RACFID
FROM [CRS].[dbo].[ORACLE_CUST_2019] cd

  left join [CRS].[dbo].[HEADER_DETAIL_ORACLE_EXTRACT_2019] hd
  on
  cd.PROJECTID = hd.PROJECTID and cd.[LINEITEMID] = hd.[LINEITEMID]
  left join [CRS].dboHEADER_DETAIL_ORACLE_EXTRACT_CUSTOMERPRICE cp
  on
  cd.PROJECTID = cp.PROJECTID and cd.[LINEITEMID] = cp.[LINEITEMID]

where 
HD.UPDTTYPE = 'DODWS'
and hd.MATCHTYPE is null
ORDER BY NEWID()


)
,
CTE2 AS (
SELECT 
p.material_no
,p.short_description as PRODUCT_DESCRIPTION

Open in new window

0
I would like to speed this query up if at all possible.  Not sure if that can be done with execute rather than  open.  Big meet Saturday...just trying to optimize execution.  Thanks!

    i = 0
    ReDim PartsToSwitch(0)
    Set rs = New ADODB.Recordset
    sql = "SELECT r.RosterSrvrID, ir.ElpsdTime FROM Roster r INNER JOIN IndRslts ir ON r.RosterSrvrID = ir.RosterSrvrID "
    sql = sql & " WHERE ir.RaceSrvrID = " & lRaceFrom & " AND r.Gender = '" & sGender & "' ORDER BY ir.ElpsdTime"
    rs.Open sql, conn, 1, 2
    Do While Not rs.EOF
        dblElpsdTime = ConvertToSeconds(rs(1).Value)
        
        iMyGrade = 0
        If iGradeFrom > 0 Then iMyGrade = GetMyGrade(rs(0).Value)   'get my grade only if using grade as a filter
        
        If iMyGrade >= iGradeFrom And iMyGrade <= iGradeTo Then 'make sure the grade is in the correct window
            If dblElpsdTime >= dblTimeFrom Then
                If dblElpsdTime <= dblTimeTo Then
                    PartsToSwitch(i) = rs(0).Value
                    i = i + 1
                    ReDim Preserve PartsToSwitch(i)
                End If
            End If
        End If
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing

Open in new window

0
Should you be charging more for IT Services?
Should you be charging more for IT Services?

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 ,

Could please some help to write Recursive Query in Hive (or in Pyspark) .



CREATE   TABLE VSOLD_TESTCALC  AS (
     WITH
        RECURSIVE TEST
        (
          PURCHASENUMBER
          , EffDt
          , NetSOLD
          , NetPurchase --Nate
          , PostSeqNum
          , TransCode
          , Amt
          , TransDescription
          , rnum
          , t_flg
          , SpecPeriodFlg
          , MaxSOLDLimit
          )

         AS
        ( SELECT
           PURCHASENUMBER
         , StartDt                                 AS EffDt
         , CAST(SOLD - sell AS DECIMAL(18,4)) AS NetSOLD
         , CAST(Purchase - sell AS DECIMAL(18,4)) AS NetPurchase
         , renewal_seq       AS PostSeqNum
         , CAST(0 AS                INTEGER)       AS TransCode
         , CAST(0.0 AS              DECIMAL(18,2)) AS Amt
         , CAST('INIT_POSTITION' AS VARCHAR(400))  AS TransDescription
         , rnum
         , t_flg
         , SpecPeriodFlg
         , MaxSOLDLimit
        FROM
           TESTDB.SOLD_CurveInitPoint

        UNION ALL

        SELECT
           b.PURCHASENUMBER
         , b.EffDt
         ,NetSOLD 
         , NetPurchase 
         , b.PostSeqNum
         , b.TransCode
         , b.Amt
         , b.TransDescription END
         , b.rnum
         , b.t_flg
         , Flg
         , b.MaxSOLDLimit
        FROM
           TEST a
         , TESTDB.SOLD_Transactions_TESTCALC b
        WHERE
           b.rnum        = a.rnum+1
      

Open in new window

0
Hi ,
 
      Looking to convert below SQL (INSERT and UPDATE ) statement into Hive Query Language .

INSERT INTO HL_TEST.T_Bck
    SELECT a.PURCHASE_NUMBER
         , a.POST_DATE
         , a.EFF_DATE
         , a.SEQ
         , a.TR_CODE
         , a.TR_SIGN
         , a.IND
         , a.TRANSACTION_AMOUNT
         , a.isValidTransaction
         , CASE WHEN la.FINAL_DRAWDOWN_IND = 'Y' THEN 'Y' ELSE 'N' end AS isBDated 
      FROM backD_trans a   
      JOIN HL_TEST.LOANA la
        ON la.LoanNumber = a.PURCHASE_NUMBER
       AND a.EFF_DATE BETWEEN la.src_strt_dt AND la.src_end_dt
	 GROUP 
		BY a.PURCHASE_NUMBER
         , a.POST_DATE
         , a.EFF_DATE
         , a.SEQ
         , a.TR_CODE
         , a.TR_SIGN
		 , a.IND
         , a.TRANSACTION_AMOUNT
         , a.isValidTransaction
         , CASE WHEN la.FINAL_DRAWDOWN_IND = 'Y' THEN 'Y' ELSE 'N' end;

    UPDATE tgt
      FROM HL_TEST.Transactions tgt, HL_TEST.T_Bck t2
       SET EFF_DATE = t2.POST_DATE
     WHERE tgt.PURCHASE_NUMBER = t2.PURCHASE_NUMBER 
      AND tgt.POST_DATE = t2.POST_DATE
      AND tgt.EFF_DATE = t2.EFF_DATE
      AND tgt.TR_CODE = t2.TR_CODE
      AND tgt.SEQ = t2.SEQ
      AND tgt.TR_SIGN = t2.TR_SIGN 
      AND tgt.TRANSACTION_AMOUNT = t2.TRANSACTION_AMOUNT 
      AND tgt.IND = t2.IND
      AND t2.isBDated = 'Y';

Open in new window

Would appreciate if some one can please help me to run above statement in Hive .

Thanks
0
I’m loading multiple .txt files in Ssis using for each loop, data flow tasks.
First I load the data from a flat file to a staging table, then from staging to target table.
However, I’d like to get the exact number of rows loaded to destination table.I mean, I want the target table count.
Can that be accomplished by using rowcount transformation between source and destination or do I need to get the count after
The destination table is loaded using A SQL statement.Does rowcount give us the rows that were loaded from source to destination
, which can be considered the destination table rowcount?

Could someone help me ASAP.

Thank a million in advance
0
I have a new project , where I have to upgrade an asp.net application. currently, application saves XML file in column with varchar(max). the application reads the XML file from the database, saves it to the session and data is read from the XML and displayed to the user. any changes from the user to the data is saved to the XML and eventually saved back to the database.

this workflow of saving the file to the session is not secure and also not efficient. I am planning to create tables/columns for all of the data points in the XML.


with this approach, I can just read the data points needed for a web page  being displayed without
having to load the full XML file in the session.

any thoughts/comments/suggestions for this approach or is there a better way to do this?
0
Create new table in MS Sql Server 2013 using VBA in MS Access

MS Office 2013

I'm starting a project to migrate all of our data tables from backend ms access files into sql server.

I have the server databases created and I have full privileges in them.  Via ODBC connections, I can link tables in my MS Access front end, execute insert record sql.

However, every example I tried to write VBA script in MS Access to create a table does not raise an error, but the table does not appear to be created.

here is the code for my last try

"Microsoft ActiveX Data Object 6.1 Library"

Dim cmd As New ADODB.Command

cmd.ActiveConnection = "Driver={ODBC Driver 13 for SQL Server};Server=…..; Database=…..;Uid=…..; Pwd=…….."

cmd.ActiveConnection.CursorLocation = adUseClient
 cmd.CommandType = adCmdText

 cmd.CommandText = "CREATE TABLE newtable (id int NULL)  ON [PRIMARY]"

 cmd.Execute

 cmd.ActiveConnection.Close
0
In one of our SQL cluster (both nodes vSphere VMs), after a reboot, the quorum disk is no longer seen in Parent Windows Cluster and SQL cluster fails. The quorum disk is online but it does have an error:

Event ID: 1795            Source: Microsoft-Windows-FailoverClustering
Cluster physical disk resource terminate encountered an error.
Physical Disk resource name: SAN2-Quorum
Device Number: 6
Device Guid: {85bf95e3-f896-c760-154b-cbaecdd743fd}
Error Code: 2
Additional reason: OpenPartitionFailure

Have run "Configure Cluster Quorum Settings" wizard many times but it make no difference. Added a fresh disk to act as Quorum but that is also behaving exactly like the old Quorum disk. SQL server is running fine. But cluster is in Failed state and services will not start when failover occurs.

Showing 'absent' Quorum Disk in bad cluster as well as a good cluster (See enclosed screen-shot which also shows how it is in the healthy SQL cluster.) Both Window OS and SQL are 2016. How may I make Quorum Disk healthy? Thanks.
0
I have a query that uses a case statement in the select, and I'm getting this error message at the very end of the from statement.  I'm aware that I'm missing some aspect in the joins, probably a missing 'on' somewhere, but I just can't seem to figure it out.  Here's the query:

SELECT        c.CUSTOMER_ID, c.NAME, c.CONTACT_FIRST_NAME, c.CONTACT_LAST_NAME, i.CREATE_DATE, i.PART_ID, i.QTY, i.UNIT_PRICE, 
						CASE WHEN rel_type = 'estimate' THEN i.id ELSE c.customer_id END AS match,
                         QUOTE_LINE.DRAWING_REV_NO, V_TASK.CATEGORY_ID, V_TASK.CREATE_DATE AS task_create, V_TASK.DUE_DATE, V_TASK.OWNER, V_TASK.REL_ID, V_TASK.REL_TYPE, V_TASK.STATUS, V_TASK.SUBJECT, 
                         V_TASK.ID AS act_id, V_TASK.LAST_MODIFIED, V_TASK.LAST_USER, V_TASK.USER_NOTE, V_TASK.REL_NO, V_TASK.ACTIVITY_TYPE_ID, V_TASK.COMMENTS, RECEIVABLE_LINE.AMOUNT / (RECEIVABLE_LINE.QTY + .001) 
                         AS last_sell, RECEIVABLE.INVOICE_DATE, V_TASK.CREATOR, USER_DEF_FIELDS.STRING_VAL AS est_category, QUOTE.PRINTED_DATE
FROM    V_TASK LEFT OUTER JOIN
                         V_TASK_BINARY ON V_TASK.ID = V_TASK_BINARY.TASK_ID LEFT OUTER JOIN
                         QUOTE as c ON V_TASK.REL_ID = c.CUSTOMER_ID LEFT OUTER JOIN
                         QUOTE as i ON V_TASK.REL_ID = i.ID RIGHT OUTER JOIN
                         USER_DEF_FIELDS RIGHT OUTER JOIN
                         QUOTE ON USER_DEF_FIELDS.DOCUMENT_ID = QUOTE.ID left outer join 
         

Open in new window

0
sql error
We have migrated from sql 2012 to a 2017 version. When trying to change or add a subscriptions we get the above error.
we can't add or change anything. All other functions are okay. We have checked all the issue raised on the error.
0
Hi,

I'm using SSIS 2017, SQL SERVER 2016, Azure cloud:

My ssis package gets data from a table and exports it to an existing Excel file successfully.

Problem I'm having with is that in PROD, I can't have the existing Excel file out there, so the SSIS package fails when CONNECTION MANAGER Excel tries to find the destination Excel but can't find it.

Options?

I'm thinking:
1) Create an Excel template in another folder and copy it over to PROD every time the ssis package runs.   But in SSIS TOOLBOX, I don't see any objects for copying a file.
2) Create an Excel template in another folder and copy it over to PROD every time the ssis package runs.   I could create a Powershell vbscript (that copies the Excel template to the PROD folder)  but I don't know if Azure can run the Powershell.
3) any other ideas?  Pleaes provide example code.
4) Can ssis create a new Excel file?
0
CompTIA Security+
LVL 13
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Hi, I did encounter a miscellaneous error in SQL 2012 STD edition. I am trying to increase the initial size of the log file for couple databases on a SQL server  and I did it using the script and Management studio GUI. Both completes executing successful but the log size still remains the same old size and it's not increased to the new configured value. Please help.
0
What will happen in the following situation, I can't force this nor test this.

In a process a recordset is selected with SQL with a selection e.g. WHERE CODE = 1
(The result can contain a few hundred records.)
each record will be processed (within 15-30 seconds) and flagged as "CODE = 2"

Now a second job (same program) is started.
This program will also select a recordset with SQL with a selection e.g. WHERE CODE = 1.

What will happen when:
Job 1 select a records with code=1
(e.g result 250 records)

Job 2 select a records with code=1
(e.g result 240 records, 10 already processd by job 1)

Job 1 change value of a record to CODE = 2.
Is this record still in the recordset from Job 2?
0
I am creating a clustered index strategy for my reporting tables.

Reports  (report_id)

ReportProducts
    Pk   reportProduct_id
    Fk  report_id

ReportProductOptions
   Pk  reportProductOption_id
   Fk  reportProduct_id
   fk  report_id
         optionCode
         optionName
         lastSold_Date



I am going to create clustered index so that the report_id is always first  in the cluster.   I hope this will prevent the constant deadlocks I am getting.

My question is... if I am going to access my ReportProductOptions table directly to the primary key, do I need to use the report_id in the where clause?

update reportProductOptions
    set optionName = 'Hello'
where report_id = 123
and     reportProductOption_id = 34567


...or....

update reportProductOptions
    set optionName = 'Hello'
where reportProductOption_id = 34567



I suspect my clustered index would NOT hold the table's primary key

   reportProductOptions Cluster Index:    report_id, product_id, optionCode, optionName

Regular (non-cluster) Index on the primary key:  reportProductOptions  reportProductOption_id


Advise please?   Is this the right approach?
0
Hi, I'm using Visual Studio 2017 to create an SSIS package.  Data isha from SQL Server 2016.

Connection Manager has an object EXCEL.

My package has 2 objects: 1) A dataflow control to get data from SQL Server.   2) Excel Data Flow that points to the Connection Manager object Excel.

I can export data from a SQL SERVER to Excel just fine.

QUESTION: How do I reorder the fields that are exported to Excel?
0
Hello Experts
I have database of sql server 2014  in production environment.  On one server  called Production  that used by X application and another database of another sql server 2014 in development environment  called  Development.

The x application has now higher release. So before deployment I need to have the production database in Development environment for testing, as i would like to have all production database with all user logins and data configuration in Development environment to test new release of x application before deploy it in Production.

So I need to do that in organized way so that I can have both databases before change and after change in case something or errors occurs .

What are scripts and steps to follow in details please to update the Development environment.
2
I have multiple FileStream tables on my SQL 2016 server.  I only want the users or groups that I give permission to the tables to see them and be able to use them.  Is there a way to block access to the tables by default unless you have permission to it?
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.