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

We have numerous instances of MS SQL Standard 2012 in our environment. MS's product lifecycle page shows SQL 2012 Service Pack 4 as supported until July 2022.

I'm seeing different information regarding MS support for Critical and Security WSUS updates. Am i reading it right that if we install SP4 on those we will continue to get WSUS critical and security updates but may have to pay a fee if we call MS as they are in extended support and no longer mainstream?

Do i have that correct?
0
Ensure you’re charging the right price for your IT
Ensure you’re charging the right price for your IT

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!

Issue: When I output a binary file that is being pulled from SQ server, no matter the different variations of the code below, I get this popup error from Microsoft Word.

I click ok, and the complete document DOES come up in Word. I tried with several other files and works every time. I need to get this popup to not come up.  (see attachment)
Issue-10-15-19.docx
0
Experts,
My goal in the Query below is to return a default value when the subquery has no matching rows.
as-is, it works fine if there is a match, but returns nothing if there is no match

Select 
COALESCE(Cart_ID,'MyValue')
from (
  select Cart_ID from C_Cart_D 
  where Comp_code = 'T2'
  and ORD_NUM = 181985
  and INVT_LEV3 = '000554878547'
  )

Open in new window



How can I correct this ?
0
I am trying to extract data from AX to use in another system. I need Invoice cost by line but can't see where to get it.

INVENTITEMPRICE table has the costs in I need but I don't know how to match the dates to get the correct cost. E.g. if a sale is done after 01/01/2018 and before 26/04/2018 then the correct cost is 15.72 (See below)

Screenshot of INVENTITEMPRICE table
All I'm trying to do is get a cost price per invoice line but I'm not familiar with the AX data structure, can anyone help?
0
Hi,
I have a table with a surname column, a sample of which is below.


O REGAN
O SULLIVAN
O MAHONY
O SULLIVAN

I need to update all the names like the values below with an apostrophe (see below)

O’ REGAN
O’ SULLIVAN
O’MAHONY
O’ SULLIVAN


I can find all the relevant values needing updating using
WHERE LTRIM(em_employee_surname) like 'O %'

Open in new window


I need some guidance re the UPDATE statement
Thank you
1
In the following query I have two calculated columns SlotTime and SlotBreak and I wish to subtract them to create SlotWorked but SQL Server claims SlotTime and SlotBreak are invalid column names
select R.site_id,L.Activity,L.PT,L.Rate,'1/' + DateName(month,R.week_commencing) + '/' + Cast(Year(R.week_commencing) as varchar) as FDM,
L.D1AB AS DayAbs,
L.D1F-L.D1S as SlotTime,
Case when L.D1F-L.D1S < 6.02/24 then 0 else 0.5/24 end as SlotBreak,
([SlotTime]-[SlotBreak]) as SlotWorked
from
tblRotas R inner join tblRotaLines L on R.Rota_ID=L.rota_id
0
Hello Experts,
Here is my issue.  I have a MySQL database that a report is being derived from. There is a field in one of the tables that is stored as XML. Previously, an expert gave me some great advice and I was able to create a formula that broke out one of the 3 pieces of data that I need.

StringVar strTest := {_invoice_transaction1.Extra};
ExtractString (strTest,'"CheckDate">',"</v");

While this formula gives me the CheckDate, as I suspected, I could not create a parameter field from the formula.
Unless there is a workaround that I have not yet deduced, my latest attempt is to go to the SQL query and modify the query prior to the data reaching the report.

I know I need to go to: Database:database expert: odbc: add command... and there I can modify the SQL.

So, that leaves me 2 questions: Where and or how can I see the SQL statement output so I know if my modifications are going to have the desired results. If I was using straight SQL I would probably add a line like: ExtractString(_invoice_transaction1.extra, '"Checkdate">',"</") as checkdate     -  to my query which I have copied from Crystal below.

 SELECT _invoice_transaction1.TransactionDate, _customer1.LastName, _customer1.FirstName, _customer1.MiddleName, _insurancecompany1.Name, _invoice1.ID, _invoicedetails1.BillingCode, _user1.Login, _invoice_transaction1.Amount, _invoice_transaction1.Taxes, _invoicedetails1.ReviewCode, _invoice_transactiontype1.ID, _invoice_transaction1.Extra
 FROM   …
0
I am using VBA code to create a query for a report. I have a field that has both numeric and alphanumeric data. I need to search for a range of values on this field. When I populate a query using query design view, it looks like

QryDesign.JPG
The query works as expected.

When I attempt to create the same query with VBA, I get the following error:

SyntaxError.JPG
My 'where' clause end up like   ([ProOrdNumber] = Between "7927"  AND  "7930") and somehow I am missing an operator.

Can anyone help me?
0
hello expert
I want to save a long type value in another table.
But that's a mistake.
how to fix this error.

In the 'RAD_SONUC' table, 'RAPORX' has the value 'LONG' of the column.

I want to insert the values ​​of the 'raporx' column of the rad_sonuc table into the rad_tani table.

code is below

create or replace trigger raporxx
before  update  of RAPORX
   ON RAD_SONUC
FOR EACH ROW
declare
  VAR1 clob;
  VAR2 VARCHAR2(4000);
  var3 LONG;
begin
      var3:=:new.RAPORX;
      insert into rad_tani(istek_no,rowid1,yazi) values(:new.istek_no,:new.ROWID1,to_char(var3)); 

dbms_output.put_line('istek_no= '||:new.istek_no||' giris= '||VAR3);
end;

Open in new window

The error in the code is as follows.
ORA-04093: references to columns of type LONG are not allowed in triggers

Open in new window

how can i fix it.?
thanks
0
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. I have this error when building a CTE that has aggregate function and GROUP BY STATMENT

      
            WITH ProductSales      (      PoductID                  
                                                ,ProductCount
                                                ,TotalSales
                                          )
            AS
      --We Define the CTE query
      (
            SELECT                  ProductID, COUNT(*) ProductCount,SUM(LineTotal) TotalSales
            FROM                  Sales.SalesOrderDetail
            
      )
      ,CTE_ProductSales
            AS
      --Define the Outer query referencing CTE_RatingInfo
      (
            SELECT                  ProductID, COUNT(*) ProductCount, SUM(LineTotal) TotalSales
            FROM                  Sales.SalesOrderDetail
                                    GROUP BY      ProductID
                                    Order by      ProductID
            
      )

            SELECT *      FROM CTE_ProductSales

How can I implement the TOP here to remove the error ?
0
Python 3 Fundamentals
LVL 13
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

I have a staging table that I want to loop through and insert into the production table - the thing is in the stage table there is multiple fields that might have data, and if they have data I want to insert a new row into production with certain fields in the staging table.

For example in stage table - Customer ID, Customer Name, Order Number 1, Order Number 2, Order Number 3, Created Date, Created By

In the Production Table I want to insert into these fields  Customer ID, Customer ID, Customer Name, Order Number, Created Date, Create By

So if Order Number 1 has a value it inserts 1 row into production, if Order Number 2 has a value insert another row and if Order Number 3 has a value insert one more row.

So the end goal is to split the 1 row in staging into possibly 3 rows if all 3 Order Number fields has value.

Is this possible and if so how ?

Thanks
0
In vb6 I use SQL like e.g.  select * from DEBNAW where ucase(DEBNAME1) LIKE '%JAN%'
on stand alone Pc its fast 600 returns and displayed and sorted in 1  second
on network  Vmware ,  same call 3 seconds ,  users complain about this
would this be possible to speed up??[
pc.png
netwerk.png
0
I am creating a Temporary table, How can I define the values in the columns. I want to use the Sum/Count aggregate functions and use GROUP BY STATEMENT ?

CREATE      TABLE      #ProductionInFo      (ProductID, COUNT(*) ProductCount,SUM(LineTotal) TotalSales)
                                          
            
            INSERT INTO            #ProductionInFo
            SELECT                  ProductID, COUNT(*) ProductCount,SUM(LineTotal) TotalSales
            FROM                  Sales.SalesOrderDetail
                                    GROUP BY      ProductID
                                    Order by      ProductID
SELECT *  FROM #ProductionInfo


                  SELECT            ProductID, COUNT(*) ProductCount,SUM(LineTotal) TotalSales
                  FROM            Sales.SalesOrderDetail
                                    GROUP BY      ProductID
                                    Order by      ProductID
0
Experts I'm going resubmit a too question on Change Tracking with fresh data


The table structure I'm working with is as follows:

CREATE TABLE PrestigeCars.dbo.data_source_table (
  PersonID INT NOT NULL
 ,Name VARCHAR(255) NULL
 ,Age INT NULL
 ,PRIMARY KEY CLUSTERED (PersonID)
) ON [PRIMARY]
GO

Open in new window


ALTER TABLE dbo.data_source_table
ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON)
GO

Open in new window



Before doing any modifications to the dataset it looks as follows

org
The actual dataset is as follows:

create table data_source_table
(
    PersonID int NOT NULL,
    Name varchar(255),
    Age int
    PRIMARY KEY (PersonID)
);

INSERT INTO data_source_table
    (PersonID, Name, Age)
VALUES
    (1, 'aaaa', 21),
    (2, 'bbbb', 24),
    (3, 'cccc', 20),
    (4, 'dddd', 26),
    (5, 'eeee', 22);

Open in new window



Now, I have modified the dataset as follows:

mod
From the above you can see I have inserted a row 'InsertedCol', I have updated a row at PersonID 5 'eeee-updatedCol', and I have deleted a row at PersonID 4 'dddd'.

dfa
I have then executed the following code:

 
SELECT
    ChVer = SYS_CHANGE_VERSION,
    ChCrVer = SYS_CHANGE_CREATION_VERSION,
    ChOp = SYS_CHANGE_OPERATION, PA.*
  FROM CHANGETABLE(CHANGES dbo.data_source_table, 29) AS ChTbl
 LEFT JOIN dbo.data_source_table PA ON PA.PersonID = ChTbl.PersonID
And the results are as follows:

Open in new window




You can see everything is working fine, but where it should say 'dddd' in the name column is gives the results (null).

I need 'dddd' where the null is.

Can you help me with that?
0
;WITH cte_source AS (

                        SELECT a.tagObjectId,      a.objectId,      a.tagId, a.tagCategoryScopeId,      a.isLocked,      c.IsExclusive
                        FROM
                              @TagObject a
                              JOIN dbo.vwTagCategoryScope b ON a.tagCategoryScopeId = b.TagCategoryScopeID
                              JOIN dbo.vwTagCategorySetting c ON b.CategoryDictionaryID = c.CategoryDictionaryID AND b.UserCompanyID = c.UserCompanyID
                        WHERE
                              c.IsActive = 1 AND
                              b.UserCompanyID = @UserCompanyID AND
                              b.ScopeDictionaryID = @ScopeDictionaryID
                  )

                  MERGE INTO dbo.tblTagObject t
                  USING cte_source

                  -- so basically here is how it works
                  -- if we match on primary key then no brainer just perform an update
                  -- if we match on CategoryScope, ObjectID, TagID or Category is exclusive then update
                  -- otherwise insert
                  s ON
                        t.IsActive = 1 AND
                        t.IsDelete = 0 AND (
                              s.tagObjectId = t.TagObjectID OR (
                                    s.tagCategoryScopeId = t.TagCategoryScopeID AND s.objectId = t.ObjectID AND (
                                          s.tagId = t.TagID OR s.IsExclusive = 1
                                    )
                              )
                        )

                  WHEN NOT MATCHED BY TARGET THEN

                        INSERT (TagCategoryScopeID, TagID, ObjectID, IsLocked, CreatedBy) VALUES (s.tagCategoryScopeId, s.tagId, s.objectId, s.isLocked, @UserID)

                  WHEN NOT MATCHED BY SOURCE AND t.IsActive = 1 AND t.IsDelete = 0 AND (t.ObjectID = @ObjectID OR EXISTS(SELECT 1 FROM cte_source aa WHERE aa.objectId = t.ObjectID))

                        AND (
                                    EXISTS (
                                          SELECT
                                                1
                                          FROM
                                                dbo.tblTagCategoryScope aa
      …
0
I have an SSIS solution which has grown over the past few months to the stage where I need to break it out into separate solutions.
A bit of background: The project is migrating data from one database to a new system. Data is extracted from the old database into flat text files which I then import to SQL Server using SSIS Workflows. The data is then transformed and output to Excel templates ready for importing to the new system.

All of this works fine. Now we are coming up to the cut off date for data from the old system. This will be the dataset that forms the Live data in the new system. There is a period of one month between the data cut off and the use of the new system, during which time changes will be made on the old system. So, we have a requirement to produce delta output which generates records that have been Inserted, Deleted, or Updated. All of this is fine and the scripts are ready.

However, I've run into serious issues with memory with having both the main data migration and delta workflows in the same project. Therefore, I want to move the Delta workflow to a new solution. The way I've gone about doing this is to set up a new solution in VS 2017 (15.9.16), and adding an existing package to the solution. When I do this, it loses the Connection to the database. So, I've tried creating the connection prior to adding the existing package, yet I get the same result.

I could go through every data flow task and update the connection, but this also requires …
0
Hi EE,

I have two queries I want to pass elements of their results as parameter values in stored procedure example:

Query One:
Capture the Value of the 1st Row , Column Name ProductCode

Query Two:
Capture the Value of the 1st Row Column Name: ChangeNum and Item No.

Exec SPGenerate @ProductCode, @ChangeNum, @ItemNo

How would I go about doing this?

Any assistance would be appreciated.

Thank you.
0
Hello Community,

It is very important to me to get to grips with tracking changes to tables in a SQL database, so I'm using the Change Tracking feature in SQL.

I have enabled change tracking on the database and the table that I want to track. I have also enabled change tracking on columns

When I issue the following command I get the results I expect to see.
res

However, if I wanted to see another column from the results, for example City I get the following error;

Msg 207, Level 16, State 1, Line 7
Invalid column name 'City'.

Completion time: 2019-10-10T21:01:10.0240167+01:00

SELECT
NewTableVersion = CHANGE_TRACKING_CURRENT_VERSION()

SELECT
ChVer = SYS_CHANGE_VERSION,
ChCrVer = SYS_CHANGE_CREATION_VERSION,
ChOp = SYS_CHANGE_OPERATION, AddressID, City
FROM CHANGETABLE(CHANGES Person.Address, 1) AS ChTbl;

Open in new window


I would like to be able view other columns.

Can someone let me know where I'm going wrong?

The table is defined as follows:

CREATE TABLE AdventureWorks2012.Person.Address (
  AddressID INT IDENTITY NOT FOR REPLICATION
 ,AddressLine1 NVARCHAR(60) NOT NULL
 ,AddressLine2 NVARCHAR(60) NULL
 ,City NVARCHAR(30) NOT NULL
 ,StateProvinceID INT NOT NULL
 ,PostalCode NVARCHAR(15) NOT NULL
 ,SpatialLocation GEOGRAPHY NULL
 ,rowguid UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_Address_rowguid DEFAULT (NEWID()) ROWGUIDCOL
 ,ModifiedDate DATETIME NOT NULL CONSTRAINT DF_Address_ModifiedDate DEFAULT (GETDATE())
 

Open in new window

0
I have a stored procedure in one database that writes to another database. Security is assigned using SQL Authentication.
For each user I have issued
GRANT DELETE,UPDATE,INSERT TO JOBCOSTDETAIL TO USERNAME

This stored procedure is called via a  VS C# program. Whenever a user clicks the button to run the stored procedure they get:
The INSERT permission was denied on the object 'JOBCOSTDETAIL' ,database 'DATABASE',schema 'dbo'

I could change my code to run the sp as 'sa' but I should not need to do that. When I check the properties of this user on this database they do have INSERT permission as dbo. What else do I need to set to get an average user the ability to write to this table in this other database.
0
Starting with Angular 5
LVL 13
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

MERGE INTO dbo.tblTagObjectSummary t
USING cte_summary s ON s.ObjectID = t.ObjectID AND s.ScopeDictionaryID = t.ScopeDictionaryID

WHEN NOT MATCHED BY TARGET THEN
      INSERT (ScopeDictionaryID, ObjectID, TagSummary, TagSummaryExtended, CreatedBy) VALUES (s.ScopeDictionaryID, s.ObjectID, s.TagSummary, s.TagSummaryExtended, @UserID)

WHEN NOT MATCHED BY SOURCE AND t.IsActive = 1 AND t.IsDelete = 0 AND EXISTS(SELECT 1 FROM cte_summary aa WHERE aa.objectId = t.ObjectID AND aa.ScopeDictionaryID = t.ScopeDictionaryID) THEN
      UPDATE
      SET
            IsActive = 0,
            ModifiedBy = @UserID,
            ModifiedDate = GETUTCDATE()

WHEN MATCHED AND (ISNULL(t.TagSummary, '-1') <> ISNULL(s.TagSummary, '-1') OR ISNULL(t.TagSummaryExtended, '-1') <> ISNULL(s.TagSummaryExtended, '-1')) THEN

      UPDATE
      SET
            TagSummary = ISNULL(NULLIF(s.TagSummary,''), t.TagSummary),
            TagSummaryExtended = ISNULL(NULLIF(s.TagSummaryExtended,''), t.TagSummaryExtended),
            IsActive = CASE
                              WHEN NULLIF(s.TagSummary,'') IS NOT NULL OR NULLIF(s.TagSummaryExtended,'') IS NOT NULL THEN 1
                              ELSE 0
                          END,
            IsDelete = 0,
            ModifiedBy = @UserID,
            ModifiedDate = GETUTCDATE()

OUTPUT 'tblTagObjectSummary', $Action, NULL, Inserted.TagObjectSummaryID INTO @tblMergeResults;
0
I have a question regarding SQL permissions. I have a C# application that calls stored procedures. This application is wrapped around an ERP solution. This solution grants rights through a group. My stored procedures run fine on all the database that are part of the ERP solution. There is one stored procedure that runs against a database that is outside of the ERP solution. I have given user db_reader, db_writer and db_owner roles on the external database. These users are getting permission denied on this external database. What other role(s) must I give them? Is there something I need to grant on a table level?
0
Need to explore the concept and implementation of foreign keys.

Consider this scenario with appropriate data:
      Create table Grade (letter char(1) primary key); /* A B C D E */
      Create table Student (ID char(8),
      Name varchar(40),
      Grd char(1),
      foreign key(grd) references Grade(letter));

For this problem, will implement the "foreign key" requirement so remove it from the above table definition.

(1) Write the appropriate trigger on table Student that will check the value of Grd against the table Grade. Raise an appropriate error if the value is improper.
(2) Demonstrate that your trigger is working correctly using various test cases.

*** Can anyone guid me to finish this problem? ***
0
Hi guys

I'm currently managing an entire infrastructure team at the moment and we've hired a third party to monitor all of our servers and infrastructure for us in the case of an emergency, so that they can handle it whilst we are all in lala land;). However, one issue I do have is that we do not have something like a 'runbook' which they can refer to. Now, I know that creating a run book can vary from company to company. Our systems consist of an infrastructure with many virtual machines, each of these virtual machines has a variety of applications running on them, some of them have POS systems, databases with SQL, order fullfilment systems etc.

If you were to have a runbook, how would you have yours and what would it consist of?

Any help would be great.
Thanks for looking
Yash
0
When creating a sql db with cli, how do add 5btu?  I have this so far:

az sql db create --name chr1db --server chris11ql --edition Basic --max-size 100MB (5BTU??)

Let me clarify, I am looking for what is the --service-object.  II cannot find the script for all service levels Specifically, I am looking for Basic. I know Standard is S0.
0
Requirement :

In CSV file I want to check three scenarios.

First : Find the Columns which have Header Empty and Data in the Column and rename the Column Header as Temporary Header
Second : Find the Columns which has Header but not the data in the Column (do nothing to these columns)
Third : Find the Columns with No Data at all and delete

Finally Convert CSV  to XLsx format and save the file name with Time stamp  like Filename_YYYY_MM_DD_HHmm (processed time)
Delete.PNG
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.