Database

Re: Error in Restoring SQL Server Backup of v 2012 to v 2008

1-Jun-18 11:14

Hi,

I am getting following error message when trying to restore a Backup of Sql Server 2012 Database on a Sql Server 2008 Server, the error message says following:

This error arises if you attempt to restore a database from a backup created on a later version of SQL Server to an earlier version of SQL Server. You can only restore to the same or a later version of SQL Server.

Any help or suggestion would be greatly helpful, thanks in advance

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

Victor Nijegorodov1-Jun-18 20:46

Victor Nijegorodov

1-Jun-18 20:46

Just install the SQL Server 2008 (Express edition = it is free!) and you'll be able to restore this DB.

PS; check whether it was SQL Server 2008 or SQL Server 2008 R2.

Re: Error in Restoring SQL Server Backup of v 2012 to v 2008

Eddy Vluggen2-Jun-18 0:21

Re: Error in Restoring SQL Server Backup of v 2012 to v 2008

2-Jun-18 0:21

Read the message again; it is very clear on what is wrong and how to solve it.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

indian1434-Jun-18 6:10

Re: Error in Restoring SQL Server Backup of v 2012 to v 2008

4-Jun-18 6:10

It says but little confused about how is it going to resolve by just installing lower version - lol, thanks for helping for both of you.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

Eddy Vluggen4-Jun-18 7:08

A Control flow Task is appearing in the SSIS Packages xml even after disabling and the deleting it.

4-Jun-18 7:08

You can't restore a backup from a higher version on a lower version.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

indian14331-May-18 13:34

The following Query is taking too long to execute

31-May-18 13:34

Hi,

I have a Contolflow Task in my SSIS Package, which sends Emails, and because we don't need it and msdb.dbo.sp_send_dbmail is disabled on our SQL Server, I have first disabled the Controlflow task that sends Email using msdb.dbo.sp_send_dbmail (basically Execute SQL Task).
It was working fine on my local machine, when I deployed the Package on to a Server as SQL Agent Job, Job didn't execute threw error. Then I disabled the Email task on Package deployed it again, but surprisingly, Job is giving the same error as " msdb.dbo.sp_send_dbmail is disabled"

Then I completely deleted the Task and deployed again, still it is giving me the same error, to check when I opened the Package in notepad the task still showing.

Is there any way we can completely get rid of the task from SSIS Package, any help would be very helpful - thanks in advance.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

indian14330-May-18 13:33

Re: The following Query is taking too long to execute

30-May-18 13:33

Hi,

I am executing the following query, its taking too long to execute, can anybody give me any advice what can I do to improve its performance, because its taking hours of time to execute. Thanks in advance.

SELECT
    vw_OwnerXML5010.Owner,
    vw_OwnerXML5010.Billing_EIN AS TP_EIN,
    vw_OwnerXML5010.TP_Name AS TP_NAME,
    vw_OwnerXML5010.County_Code AS County_Code,
    (
   SELECT
   (
    SELECT
        vw_ServiceFacilityXML5010.Service_Facility_NPI AS "Service_Facility_NPI",
        vw_ServiceFacilityXML5010.County_Owned_Indicator AS "County_Owned_Indicator",
    (
    Select 
        vw_ServicesXML5010.Procedure_Code AS "Procedure_Code",
        vw_ServicesXML5010.Modifier1 As "Modifier1",
        vw_ServicesXML5010.Modifier2 AS "Modifier2",
        vw_ServicesXML5010.Modifier3 AS "Modifier3",
        vw_ServicesXML5010.Modifier4 AS "Modifier4",
        vw_ServicesXML5010.From_Date AS "From_Date",
        vw_ServicesXML5010.To_Date AS "To_Date",
        vw_ServicesXML5010.Revenue_Code AS "Revenue_Code",
        vw_ServicesXML5010.Place_Of_Service AS "Place_Of_Service",
        vw_ServicesXML5010.Taxonomy_Code AS "Taxonomy_Code"
    FROM 
    vw_ServicesXML5010 
    WHERE
        vw_ServiceFacilityXML5010.Service_Facility_NPI = vw_ServicesXML5010.Service_Facility_NPI and 
        vw_ServiceFacilityXML5010.County_Code = vw_ServicesXML5010.County_Code
        and vw_ServiceFacilityXML5010.Billing_EIN = vw_ServicesXML5010.Billing_EIN  
    FOR XML PATH('Service'), TYPE , ELEMENTS XSINIL
    ) 
    FROM 
    vw_ServiceFacilityXML5010
    WHERE
        vw_ServiceFacilityXML5010.County_Code = vw_OwnerXML5010.County_Code
        and vw_ServiceFacilityXML5010.Billing_EIN = vw_OwnerXML5010.Billing_EIN
    FOR XML PATH ('TB_SERVICE_FACILITY'), TYPE
) FOR XML PATH ('TB_BILLING_PROVIDER_FILE'), TYPE   
)
FROM 
    dbo.vw_SDMCPhaseTwoParentLevelXML vw_OwnerXML5010
ORDER BY vw_OwnerXML5010.County_Code
FOR XML PATH ('TB_TRADING_PARTNER'), root('TB_BILLING_PROVIDER_FILES')

Here are the following Views:

CREATE VIEW [dbo].[vw_SDMCPhaseTwoParentLevelXML]
AS
SELECT
        DISTINCT 'DMH' AS [Owner]
        ,'' AS [Billing_NPI]
        ,SUBSTRING(RTRIM(LE.TaxId), 1, 9) AS [Billing_EIN]
        ,'' AS DirectIndicator
        ,CO.PK_Geographic_Location_Code AS [County_Code]
        ,CO.County_Name AS [TP_Name]
    FROM
        dbo.LegalEntity LE<br />
        INNER JOIN dbo.county CO
            ON LE.FKCountyLKPId = CO.PKCountyId
    Where LEFT(LegalEntityNbr,3) NOT IN ('HFP', '00F')
    AND LEFT(LegalEntityNbr,3) IN ('000')
    AND CO.PK_Geographic_Location_Code NOT IN ('99', '00', '65', '66')
GO

CREATE VIEW [dbo].[vw_ServiceFacilityXML5010]
AS
SELECT
    distinct VW.Billing_EIN AS [Billing_EIN]
    ,CO.PK_Geographic_Location_Code AS [County_Code]<br />
    ,US.NPINumber AS [Service_Facility_NPI]
    , 'true' AS [County_Owned_Indicator]
    FROM
        dbo.Provider C
        INNER JOIN dbo.ProviderDate CD
            ON CD.FKProviderId = C.PKProviderId
        INNER JOIN dbo.ProviderService CS
            ON CS.FKProviderDateId = CD.PKProviderDateId and cs.FKProviderId=cd.FKProviderId
        INNER JOIN dbo.ProviderService CSD
            ON CSD.FKProviderId = CS.FKProviderId
        LEFT OUTER JOIN dbo.ProviderService MCS
            ON MCS.FKProviderId = CS.FKProviderId
        LEFT OUTER JOIN dbo.ProviderService MCSD
            ON MCSD.FKProviderId = MCS.FKProviderId
        INNER JOIN dbo.ServiceFunctionCategoryLKP SFC
            ON SFC.PKServiceFunctionCategoryLKPId = CS.FKServFuncCatMCModeOfServiceId
        INNER JOIN dbo.ModeOfServiceLKP MS
            ON MS.Code = SFC.ModeOfServiceCode
        LEFT OUTER JOIN dbo.ServFuncCatMCModeOfService SFCMMS
            ON MS.Code= SFCMMS.MCModeOfServiceCode
        LEFT OUTER JOIN dbo.MCModeOfServiceLKP MMS
            ON MMS.PKMCModeOfServiceLKPId = SFCMMS.MCModeOfServiceCode
        INNER JOIN dbo.NPIAssociation USP
            ON USP.FKParentId = C.PKProviderId AND USP.FKParentTypeLKPId=(SELECT Top 1 PKParentTypeLKPId from ParentTypeLKP WHERE ParentCode='PRV')
        INNER JOIN  dbo.NPI US
            ON US.PKNPIId = USP.FKNPIId
        INNER JOIN dbo.LegalEntity LE
            ON LE.PKLegalEntityId = C.FKLegalEntityId
        --INNER JOIN dbo.usrTaxid TX
        --  ON LE.FKTaxId = TX.PKTaxId
        INNER JOIN dbo.County CO
            ON C.FKCountyLKPId = CO.PKCountyId
        INNER JOIN VW_SDMCPhaseTwoParentLevelXML VW
            ON CO.PK_Geographic_Location_Code = VW.county_code
        INNER JOIN dbo.ProviderType FT
            ON C.ProviderTypeId = FT.ProviderTypeId
        INNER JOIN dbo.MCMSSFCrosswalkLKP SP
            ON MMS.Code = SP.MCModeOfServiceCode
    Where LEFT(LegalEntityNbr,3) IN ('000', 'AFC')
    AND LEFT(LegalEntityNbr,3) NOT IN ('HFP', '00F')<br />
    AND MMS.Code IS NOT NULL
    AND CO.PK_Geographic_Location_Code NOT IN ('99', '00', '65', '66')
    AND SP.ServiceFunctionCategoryCode = SFC.ServiceFunctionCategoryCode
    UNION ALL<br />
SELECT
    distinct    VW.Billing_EIN AS [Billing_EIN]
    ,CO.PK_Geographic_Location_Code AS [County_Code]<br />
    ,US.NPINumber AS [Service_Facility_NPI]
    FROM
        dbo.Provider C
        INNER JOIN dbo.ProviderDate CD
            ON CD.FKProviderId = C.PKProviderId
        INNER JOIN dbo.ProviderService CS
            ON CS.FKProviderDateId = CD.PKProviderDateId and cs.FKProviderId=cd.FKProviderId
        INNER JOIN dbo.ProviderService CSD
            ON CSD.FKProviderId = CS.FKProviderId
        LEFT OUTER JOIN dbo.ProviderService MCS
            ON MCS.FKProviderId = CS.FKProviderId
        LEFT OUTER JOIN dbo.ProviderService MCSD
            ON MCSD.FKProviderId = MCS.FKProviderId
        INNER JOIN dbo.ServiceFunctionCategoryLKP SFC
            ON SFC.PKServiceFunctionCategoryLKPId = CS.FKServFuncCatMCModeOfServiceId
        INNER JOIN dbo.ModeOfServiceLKP MS
            ON MS.Code = SFC.ModeOfServiceCode
        LEFT OUTER JOIN dbo.ServFuncCatMCModeOfService SFCMMS
            ON MS.Code= SFCMMS.MCModeOfServiceCode
        LEFT OUTER JOIN dbo.MCModeOfServiceLKP MMS
            ON MMS.PKMCModeOfServiceLKPId = SFCMMS.MCModeOfServiceCode
        INNER JOIN dbo.NPIAssociation USP
            ON USP.FKParentId = C.PKProviderId AND USP.FKParentTypeLKPId=(SELECT Top 1 PKParentTypeLKPId from ParentTypeLKP WHERE ParentCode='PRV')
        INNER JOIN  dbo.NPI US
            ON US.PKNPIId = USP.FKNPIId
        INNER JOIN dbo.LegalEntity LE
            ON LE.PKLegalEntityId = C.FKLegalEntityId
        --INNER JOIN dbo.usrTaxid TX
        --  ON LE.FKTaxId = TX.PKTaxId
        INNER JOIN dbo.County CO
            ON C.FKCountyLKPId = CO.PKCountyId
        INNER JOIN VW_SDMCPhaseTwoParentLevelXML VW
            ON CO.PK_Geographic_Location_Code = VW.county_code
        INNER JOIN dbo.ProviderType FT
            ON C.ProviderTypeId = FT.ProviderTypeId
        INNER JOIN dbo.MCMSSFCrosswalkLKP SP
            ON MMS.Code = SP.MCModeOfServiceCode
    Where LEFT(LegalEntityNbr,3) NOT IN ('000', 'AFC','HFP', '00F')<br />
    AND MMS.Code IS NOT NULL
    AND CO.PK_Geographic_Location_Code NOT IN ('99', '00', '65', '66')
    AND SP.ServiceFunctionCategoryCode = SFC.ServiceFunctionCategoryCode

GO

CREATE VIEW [dbo].[vw_ServicesXML5010]
AS
SELECT
    VW.Billing_EIN AS [Billing_EIN]
    ,CO.PK_Geographic_Location_Code AS [County_Code]    
    ,US.NPINumber AS [Service_Facility_NPI]
    ,ISNULL(SP.RevenueCode, '') AS [Revenue_Code]
    ,SP.ProcedureCode AS [Procedure_Code]
    ,SP.Modifier1 as Modifier1
    ,ISNULL(SP.Modifier2, '') AS [Modifier2]    
    ,ISNULL(SP.Modifier3, '') AS [Modifier3]
    ,ISNULL(SP.Modifier4, '') AS [Modifier4]
    ,CASE 
    WHEN SP.MCModeOfServiceCode = '12' AND SP.ServiceFunctionCategoryCode = '20' THEN '23'
    WHEN SP.MCModeOfServiceCode = '12' AND SP.ServiceFunctionCategoryCode = '25' THEN '20'
    WHEN SP.MCModeOfServiceCode = '18' AND SP.ServiceFunctionCategoryCode = '20' THEN '23'
    WHEN SP.MCModeOfServiceCode = '18' AND SP.ServiceFunctionCategoryCode = '25' THEN '20'  
    ELSE ''
    END Place_Of_Service
    , '' AS Taxonomy_Code   
    ,ISNULL(CONVERT(VARCHAR(8), CS.MCBeginDate, 112), '') AS [From_Date]
    ,CONVERT(VARCHAR(8), CS.MCEndDate, 112) AS [To_Date] 
    FROM
        dbo.Provider C
        INNER JOIN dbo.ProviderDate CD
            ON CD.FKProviderId = C.PKProviderId
        INNER JOIN dbo.ProviderService CS
            ON CS.FKProviderDateId = CD.PKProviderDateId
        INNER JOIN dbo.ServFuncCatMCModeOfService SFC
            ON SFC.PKServFuncCatMCModeOfServiceId = CS.FKServFuncCatMCModeOfServiceId
        INNER JOIN dbo.ModeOfServiceLKP MS
            ON MS.Code = SFC.ModeOfServiceCode
        LEFT OUTER JOIN dbo.MCModeOfServiceLKP MMS
            ON MMS.Code = SFC.MCModeOfServiceCode
        INNER JOIN dbo.NPIAssociation USP
            ON USP.FKParentId = C.PKProviderId AND USP.FKParentTypeLKPId IN
            (
            SELECT PKParentTypeLKPId FROM dbo.ParentTypeLKP a WHERE ParentCode IN ('PRV') --, 'NPI'
            )
        INNER JOIN  dbo.NPI US
            ON US.PKNPIId = USP.FKNPIId
        INNER JOIN dbo.LegalEntity LE
            ON LE.PKLegalEntityId = C.FKLegalEntityId
        --INNER JOIN dbo.usrTaxid TX
        --  ON LE.FKTaxId = TX.PKTaxId
        INNER JOIN dbo.County CO
            ON C.FKCountyLKPId = CO.PKCountyId
        INNER JOIN VW_SDMCPhaseTwoParentLevelXML VW
            ON CO.PK_Geographic_Location_Code = VW.county_code
        INNER JOIN dbo.FacilityDesignationLKP FT
            ON US.FKFacilityDesignationLKPId = FT.PKFacilityDesignationLKPId
        INNER JOIN dbo.MCMSSFCrosswalkLKP SP
            ON MMS.Code = SP.MCModeOfServiceCode
    Where LEFT(LegalEntityNbr,3) NOT IN ('HFP', '00F')
    AND MMS.Code IS NOT NULL
    AND CO.PK_Geographic_Location_Code NOT IN ('99', '00', '65', '66')
    AND SP.FKServFuncCatMCModeOfServiceId = SFC.PKServFuncCatMCModeOfServiceId
    AND SP.Modifier1 <> 'HK'    
    UNION ALL   
    SELECT
    VW.Billing_EIN AS [Billing_EIN]
    ,CO.PK_Geographic_Location_Code AS [County_Code]    
    ,US.NPINumber AS [Service_Facility_NPI]
    ,ISNULL(SP.RevenueCode, '') AS [Revenue_Code]
    ,SP.ProcedureCode AS [Procedure_Code]
    ,SP.Modifier1 as Modifier1
    ,ISNULL(SP.Modifier2, '') AS [Modifier2]    
    ,ISNULL(SP.Modifier3, '') AS [Modifier3]
    ,ISNULL(SP.Modifier4, '') AS [Modifier4]
    ,CASE 
    WHEN SP.MCModeOfServiceCode = '12' AND SP.ServiceFunctionCategoryCode = '20' THEN '23'
    WHEN SP.MCModeOfServiceCode = '12' AND SP.ServiceFunctionCategoryCode = '25' THEN '20'
    WHEN SP.MCModeOfServiceCode = '18' AND SP.ServiceFunctionCategoryCode = '20' THEN '23'
    WHEN SP.MCModeOfServiceCode = '18' AND SP.ServiceFunctionCategoryCode = '25' THEN '20'  
    ELSE ''
    END Place_Of_Service
    , '' AS Taxonomy_Code
    ,CASE
        WHEN (SP.ProcedureCode = 'H2015' or SP.ProcedureCode = 'T1017')AND SP.Modifier1 = 'HK' --all Katie A. Services start on 20130101
        THEN '20130101' 
        ELSE
            ISNULL(CONVERT(VARCHAR(8), CS.MCBeginDate, 112), '')
        END [From_Date]
    ,CONVERT(VARCHAR(8), CS.MCEndDate, 112) AS [To_Date] -- end date is not changing for Katie A. services. Just eliminate the rows that have an end date
    FROM
        dbo.Provider C
        INNER JOIN dbo.ProviderDate CD
            ON CD.FKProviderId = C.PKProviderId
        INNER JOIN dbo.ProviderService CS
            ON CS.FKProviderDateId = CD.PKProviderDateId
        INNER JOIN dbo.ServFuncCatMCModeOfService SFC
            ON SFC.PKServFuncCatMCModeOfServiceId = CS.FKServFuncCatMCModeOfServiceId
        INNER JOIN dbo.ModeOfServiceLKP MS
            ON MS.Code = SFC.ModeOfServiceCode
        LEFT OUTER JOIN dbo.MCModeOfServiceLKP MMS
            ON MMS.Code = SFC.MCModeOfServiceCode
        INNER JOIN dbo.NPIAssociation USP
            ON USP.FKParentId = C.PKProviderId AND USP.FKParentTypeLKPId IN
            (
            SELECT PKParentTypeLKPId FROM dbo.ParentTypeLKP a WHERE ParentCode IN ('PRV') --, 'NPI'
            )
        INNER JOIN  dbo.NPI US
            ON US.PKNPIId = USP.FKNPIId
        INNER JOIN dbo.LegalEntity LE
            ON LE.PKLegalEntityId = C.FKLegalEntityId
        --INNER JOIN dbo.usrTaxid TX
        --  ON LE.FKTaxId = TX.PKTaxId
        INNER JOIN dbo.County CO
            ON C.FKCountyLKPId = CO.PKCountyId
        INNER JOIN VW_SDMCPhaseTwoParentLevelXML VW
            ON CO.PK_Geographic_Location_Code = VW.county_code
        INNER JOIN dbo.FacilityDesignationLKP FT
            ON US.FKFacilityDesignationLKPId = FT.PKFacilityDesignationLKPId
        INNER JOIN dbo.MCMSSFCrosswalkLKP SP
            ON MMS.Code = SP.MCModeOfServiceCode
    Where LEFT(LegalEntityNbr,3) NOT IN ('HFP', '00F')
    AND MMS.Code IS NOT NULL
    AND CO.PK_Geographic_Location_Code NOT IN ('99', '00', '65', '66')
    AND SP.ServiceFunctionCategoryCode = SFC.ServiceFunctionCategoryCode    
    AND (SP.Modifier1 = 'HK' and (CS.MCEndDate is null or  (CS.MCBeginDate >= '20130101' or  CS.MCEndDate > '20130101')))
GO

Any help would be greatly helpful, thanks in advance.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

CHill6031-May-18 2:53

Re: The following Query is taking too long to execute

31-May-18 2:53

CHill60 wrote:
You might get a better response with some sample data and expected results

This is getting old now. When posting SQL queries and asking "what is wrong" or "how does this not work" or ... well, anything, you should include some sample data for all of the tables used in your query.
Nobody wants to use up their spare time (which is how this forum works) traipsing through 200+ lines of code in the off-chance they might spot something that could be improved.

Help us to help you.

indian14331-May-18 13:26

Re: The following Query is taking too long to execute

31-May-18 13:26

Some of the tables in that Query have 15 Columns too, I don't know how can I put all that Data here on the forum itself, it will be more huge message than currently, I am assuming this current message itself is too big.

What I am asking is, are there any places that I can be suspicious of taking more time than normal. Any help would be very very helpful buddy.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

Eddy Vluggen1-Jun-18 0:10

Graph Database vs Dimensional

1-Jun-18 0:10

indian143 wrote:
What I am asking is, are there any places that I can be suspicious of taking more time than normal.

The usual things.

Converting varchars to dates is a big timewaster, having magical numbers shows a design-flaw, and every function that is called on each value will slow the thing down fast.

8 Ways to Fine-tune your SQL Queries (for production databases) | Sisense[^]

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

DADom24-May-18 21:32

DADom

24-May-18 21:32

Hi all. I was in a meeting recently where the chair was extolling the virtues of Graph databases. So much so that he asserted, if they’d been invented, Ralph Kimball would never have become involved with dimensional modelling and would have used graph databases instead. I have been involved with dimensional modelling (and cubes) for many years, but can’t claim to have built a Graph database. However, I understand their strength is in identifying relationships. If you’re working with data that is hierarchical in nature and, the relationships are evident, then Graph is not appropriate. Would be interested in you views. Cheers

Re: Graph Database vs Dimensional

Eddy Vluggen26-May-18 2:45

Re: Graph Database vs Dimensional

26-May-18 2:45

DADom wrote:
So much so that he asserted, if they’d been invented, Ralph Kimball would never have become involved with dimensional modelling and would have used graph databases instead.

Keep any evangelists away from these discussions. You don't need a salesman on your team.

DADom wrote:
Would be interested in you views.

My strength is identifying relationships too, so I prefer to store relational data in a RDBMS in a structure that is at least in BCNF. It is a proven way which guarantees consistency and correctness.

According to the wiki, a graph db excells where data is "difficult to model" (and I agree with the comment "according to whom"). So, to evaluate it, you come looking for anecdotes.

I suggest you make a list (and check it twice) with advantages and disadvantages of both, and decide which are more important to you. THAT is the correct way out of these discussions - you will need to justify your choice, which is not done by pointing to an anecdote on codeproject, but by showing which of the two choices is the better fit in your specific case.

Personally, I'd say that the choice is obvious; Most RDBMSs are stable and well-optimized for data storage and retrieval, and there are a lot of resources on SQL92 (talk about backward compatibility eh?) Graph-databases would require something else than SQL probably, adding to the costs (and riscs).

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

John 1356415428-Jun-18 9:07

John 13564154

28-Jun-18 9:07

Eddy Vluggen wrote:
According to the wiki,

Where is the wiki? Regards.

Re: Graph Database vs Dimensional

Eddy Vluggen28-Jun-18 9:19

How to create a insert trigger on just one table?

28-Jun-18 9:19

John 13564154 wrote:

Where is the wiki? Regards.

We don't have one; I was referring to this[^] wikipedia article.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

Member23621-May-18 23:02

Member236

21-May-18 23:02

I have a table product, columns:id,date,product,productname

I want to create a insert trigger, when I insert id,date,product, the productname will auto update in to the table(the product=productname... I don't know how to make the equation, please help me.
sorry for my terrible English.

modified 22-May-18 5:39am.

Re: How to create a insert trigger on just one table?

CHill6022-May-18 1:34

Re: How to create a insert trigger on just one table?

22-May-18 1:34

Why have two columns with the same data?
Have you tried anything at all yet?

Richard Deeming22-May-18 2:30

Richard Deeming

22-May-18 2:30

CREATE TRIGGER (Transact-SQL) | Microsoft Docs[^]

SQL

CREATE TRIGGER YourTriggerName
ON YourTableName
AFTER insert, update
As
BEGIN
    SET NOCOUNT ON;
    
    If UPDATE(FK)
    BEGIN
        UPDATE
            T
        SET
            RedundantDuplicateColumn = T2.Name
        FROM
            YourTable As T
            INNER JOIN inserted As I
            ON I.PK = T.PK
            LEFT JOIN YourOtherTable As T2
            ON T2.PK = T.FK
        ;
    END;
END

Replace the dummy table and column names with the real table and column names.

Remember that triggers will fire once per batch, not once per row. There could be multiple rows in the inserted table.

But as CHill60 said, duplicating the product name in both tables seems like a bad design. If you have the ID of the product, you can simply join to the product table to get the name on demand.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: How to create a insert trigger on just one table?

Mycroft Holmes22-May-18 14:09

Mycroft Holmes

22-May-18 14:09

This is a REALLY BAD design, instead of having a separate table for productname create a view

SQL

create view vwProductName
as
select distinct product as productname from product

Never underestimate the power of human stupidity
RAH

Re: How to create a insert trigger on just one table?

CHill6022-May-18 22:56

Re: How to create a insert trigger on just one table?

22-May-18 22:56

Am I missing something ... where's the table for the ProductName mentioned?

Mycroft Holmes23-May-18 13:20

Mycroft Holmes

23-May-18 13:20

OP wrote:

the productname will auto update in to the table(the product=productname.

So yes I think you missed this.

Never underestimate the power of human stupidity
RAH

Re: How to create a insert trigger on just one table?

Member23622-May-18 23:04

Member236

22-May-18 23:04

It's an ERP bill, customer want to input the batch number, but they don't want to use any rules, just want to fetch the bill number into that field.

Re: How to create a insert trigger on just one table?

Mycroft Holmes23-May-18 13:23

Mycroft Holmes

23-May-18 13:23

The comments still stand - there should be no reason to store the productname in a different table especially as it is user input and free form.

Never underestimate the power of human stupidity
RAH

FOR XML PATH is not creating Child elements if they are coming with null values

indian14316-May-18 12:39

Re: FOR XML PATH is not creating Child elements if they are coming with null values

16-May-18 12:39

Hi,

I have a query that is using FOR XML PATH, when the child elements has values then its giving the Child elements, but when there are no values coming out of query for the Child elements, its finishing the X-path by putting and end tag (\), but I want the all the elements to be created if there are values are not, it doesn't have to repeat the elements as if there are values but at least one occurrence has to be there even if there are values or don't have values.
Below are the example for it.
Case 1 is the file that it is creating with all the values and elements, there are elements that are repeated I am not putting all that xml here to reduce the size, but I want to create all the elements of the xml even if there are values or no values at least one occurrence.

<TB_BILLING_PROVIDER_FILES>
    <TB_TRADING_PARTNER>
        <Owner>DMH</Owner>
        <TP_EIN>9xxxxxxx1</TP_EIN>
        <TP_NAME>Alameda</TP_NAME>
        <County_Code>01</County_Code>
        <TB_BILLING_PROVIDER_FILE>
            <TB_SERVICE_FACILITY>
                <Service_Facility_NPI>xxxxxxxx7</Service_Facility_NPI>
                <County_Owned_Indicator>true</County_Owned_Indicator>
                <Service xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                    <Procedure_Code>Hxxxxx</Procedure_Code>
                    <Modifier1>HE</Modifier1>
                    <Modifier2>TG</Modifier2>
                    <Modifier3/>
                    <Modifier4/>
                    <From_Date>19811201</From_Date>
                    <To_Date>20030623</To_Date>
                    <Revenue_Code/>
                    <Place_Of_Service/>
                    <Taxonomy_Code/>
                </Service>
            </TB_SERVICE_FACILITY>
        </TB_BILLING_PROVIDER_FILE>
    </TB_TRADING_PARTNER>
</TB_BILLING_PROVIDER_FILES>

The Case 2 xml is as below:

<TB_BILLING_PROVIDER_FILES>
  <TB_TRADING_PARTNER>
    <Owner>DMH</Owner>
    <TP_EIN>xxxxxxxxx</TP_EIN>
    <TP_NAME>Alameda</TP_NAME>
    <County_Code>01</County_Code>
    <TB_BILLING_PROVIDER_FILE/>
  </TB_TRADING_PARTNER>
</TB_BILLING_PROVIDER_FILES>

The sql script for the case 1 is:

SELECT
    vw_OwnerXML5010.Owner,
    vw_OwnerXML5010.Billing_EIN AS TP_EIN,
    vw_OwnerXML5010.TP_Name AS TP_NAME,
    vw_OwnerXML5010.County_Code AS County_Code,
    (
   SELECT
   (
    SELECT
        vw_ServiceFacilityXML5010.Service_Facility_NPI AS "Service_Facility_NPI",
        vw_ServiceFacilityXML5010.County_Owned_Indicator AS "County_Owned_Indicator",
    (
    Select 
        vw_ServicesXML5010.Procedure_Code AS "Procedure_Code",
        vw_ServicesXML5010.Modifier1 As "Modifier1",
        vw_ServicesXML5010.Modifier2 AS "Modifier2",
        vw_ServicesXML5010.Modifier3 AS "Modifier3",
        vw_ServicesXML5010.Modifier4 AS "Modifier4",
        vw_ServicesXML5010.From_Date AS "From_Date",
        vw_ServicesXML5010.To_Date AS "To_Date",
        vw_ServicesXML5010.Revenue_Code AS "Revenue_Code",
        vw_ServicesXML5010.Place_Of_Service AS "Place_Of_Service",
        vw_ServicesXML5010.Taxonomy_Code AS "Taxonomy_Code"
    FROM 
    vw_ServicesXML5010 
    WHERE
        vw_ServiceFacilityXML5010.Service_Facility_NPI = vw_ServicesXML5010.Service_Facility_NPI
        and vw_ServiceFacilityXML5010.County_Code = vw_ServicesXML5010.County_Code
        and vw_ServiceFacilityXML5010.Billing_EIN = vw_ServicesXML5010.Billing_EIN  
    --  and vw_ServiceFacilityXML5010.County_Code in ('01', '02')
    --  and vw_ServiceFacilityXML5010.Service_Facility_NPI = '1932328580'
    FOR XML PATH('Service'), TYPE , ELEMENTS XSINIL
    ) 
    FROM 
    vw_ServiceFacilityXML5010
    WHERE
        vw_ServiceFacilityXML5010.County_Code = vw_OwnerXML5010.County_Code
        and vw_ServiceFacilityXML5010.Billing_EIN = vw_OwnerXML5010.Billing_EIN
    --  and vw_ServiceFacilityXML5010.County_Code in ( '01', '02')
    --  and vw_ServicesXML5010.Service_Facility_NPI = '1932328580'
    FOR XML PATH ('TB_SERVICE_FACILITY'), TYPE
--  ) AS "BILLING_PROVIDERS/TB_BILLING_PROVIDER_FILE/TB_SERVICE_FACILITY" 
--) AS "TB_BILLING_PROVIDER_FILE/TB_SERVICE_FACILITY"   
--) AS "TB_BILLING_PROVIDER_FILE"   
) FOR XML PATH ('TB_BILLING_PROVIDER_FILE'), TYPE   
)
FROM 
    vw_OwnerXML5010
--  where vw_OwnerXML5010.County_Code in ('01', '02')
ORDER BY vw_OwnerXML5010.County_Code
FOR XML PATH ('TB_TRADING_PARTNER'), root('TB_BILLING_PROVIDER_FILES')

The sql script for the case 2 is:

SELECT
    vw_OwnerXML5010.Owner,
    vw_OwnerXML5010.Billing_EIN AS TP_EIN,
    vw_OwnerXML5010.TP_Name AS TP_NAME,
    vw_OwnerXML5010.County_Code AS County_Code,
    (
   SELECT
   (
    SELECT
        vw_ServiceFacilityXML5010.Service_Facility_NPI AS "Service_Facility_NPI",
        vw_ServiceFacilityXML5010.County_Owned_Indicator AS "County_Owned_Indicator",
    (
    Select 
        vw_ServicesXML5010.Procedure_Code AS "Procedure_Code",
        vw_ServicesXML5010.Modifier1 As "Modifier1",
        vw_ServicesXML5010.Modifier2 AS "Modifier2",
        vw_ServicesXML5010.Modifier3 AS "Modifier3",
        vw_ServicesXML5010.Modifier4 AS "Modifier4",
        vw_ServicesXML5010.From_Date AS "From_Date",
        vw_ServicesXML5010.To_Date AS "To_Date",
        vw_ServicesXML5010.Revenue_Code AS "Revenue_Code",
        vw_ServicesXML5010.Place_Of_Service AS "Place_Of_Service",
        vw_ServicesXML5010.Taxonomy_Code AS "Taxonomy_Code"
    FROM 
    vw_ServicesXML5010 
    WHERE
        vw_ServiceFacilityXML5010.Service_Facility_NPI = vw_ServicesXML5010.Service_Facility_NPI
        and vw_ServiceFacilityXML5010.County_Code = vw_ServicesXML5010.County_Code
        and vw_ServiceFacilityXML5010.Billing_EIN = vw_ServicesXML5010.Billing_EIN  
    --  and vw_ServiceFacilityXML5010.County_Code in ('01', '02')
    --  and vw_ServiceFacilityXML5010.Service_Facility_NPI = '1932328580'
    FOR XML PATH('Service'), TYPE , ELEMENTS XSINIL
    ) 
    FROM 
    vw_ServiceFacilityXML5010
    WHERE
        vw_ServiceFacilityXML5010.County_Code = vw_OwnerXML5010.County_Code
        and vw_ServiceFacilityXML5010.Billing_EIN = vw_OwnerXML5010.Billing_EIN
    --  and vw_ServiceFacilityXML5010.County_Code in ( '01', '02')
    --  and vw_ServicesXML5010.Service_Facility_NPI = '1932328580'
    FOR XML PATH ('TB_SERVICE_FACILITY'), TYPE
--  ) AS "BILLING_PROVIDERS/TB_BILLING_PROVIDER_FILE/TB_SERVICE_FACILITY" 
--) AS "TB_BILLING_PROVIDER_FILE/TB_SERVICE_FACILITY"   
--) AS "TB_BILLING_PROVIDER_FILE"   
) FOR XML PATH ('TB_BILLING_PROVIDER_FILE'), TYPE   
)
FROM 
    dbo.vw_SDMCPhaseTwoParentLevelXML vw_OwnerXML5010
--  where vw_OwnerXML5010.County_Code in ('01', '02')
ORDER BY vw_OwnerXML5010.County_Code
FOR XML PATH ('TB_TRADING_PARTNER'), root('TB_BILLING_PROVIDER_FILES')

I am not understanding how are these two different, I just want to create the complete xml at least with one occurrence of all the elements, can anybody please help me, any help a suggestion a link or code snippet, anything helps. Thanks in advance buddies.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

CHill6022-May-18 1:32