Forum Replies Created

Viewing 15 posts - 4,201 through 4,215 (of 7,619 total)

  • RE: sp_executesql and howto return rowcount

    Your variable in the code is named @Rcount. You need to use that name in the @params clause:

    EXEC sys.sp_executesql @stmt = @SQLstmt

    , @params = N'@Rcount INT OUTPUT'

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    August 18, 2016 at 11:33 am

    #1896228

  • RE: Sql script to check that each patient has both a diastolic and a systolic blood pressure for each given service date

    select MBR_LAST_NAME, MBR_FIRST_NAME, BIRTH_DT, SERVICE_DT,

    SERVICE_TYPE_CD, case when SERVICE_TYPE_CD = 'DP' then DP ELSE SP END AS BLOOD_PRESSURE

    from (

    select MBR_LAST_NAME, MBR_FIRST_NAME, BIRTH_DT, SERVICE_DT,...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    August 18, 2016 at 8:52 am

    #1896175

  • RE: Sql script to check that each patient has both a diastolic and a systolic blood pressure for each given service date

    select MBR_LAST_NAME, MBR_FIRST_NAME, BIRTH_DT, SERVICE_DT,

    MAX(case when SERVICE_TYPE_CD = 'DP' then <value> end) AS DP,

    MAX(case when SERVICE_TYPE_CD = 'SP' then <value> end)...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    August 17, 2016 at 4:16 pm

    #1896060

  • RE: Indexes

    Those are excellent points. Thus, the other rule you should follow when creating indexes is to always explicitly specify CLUSTERED or NONCLUSTERED. Again, clustering is far too critical...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    August 17, 2016 at 2:55 pm

    #1896045

  • RE: Linked server query merging remote data with local - deadlock victim

    To avoid pulling the entire remote table columns over every time the query runs, you need to add an index to the remote table that will identify rows updated or...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    August 17, 2016 at 1:55 pm

    #1896027

  • RE: Indexes

    1) No. The primary key and clustering key are not necessarily directly related. The clustering index is the single most critical factor for performance. And it should...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    August 17, 2016 at 1:49 pm

    #1896025

  • RE: Need query for After Trigger on update,delete

    Ed Wagner (8/17/2016)

    Jeff Moden (8/17/2016)

    ScottPletcher (8/17/2016)

    Again, auditors would not have access to the original table.

    But they have direct access to the audit table? That doesn't make sense to me...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    August 17, 2016 at 12:37 pm

    #1896009

  • RE: Loop through an integer and test the values and update with a char

    SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

    CAST(price AS varchar(20)),

    '0', 'A'),

    '1', 'B'),

    '2', 'C'),

    --...

    ...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    August 17, 2016 at 11:05 am

    #1895971

  • RE: how to copy data from primary table and its dependencies from one id to another and then how could I remove primary table with its dependencies

    You could. Or you could change the FK rules to "CASCADE" rather than "NO ACTION" -- at least temporarily -- then just try updating the id from 1 to...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    August 17, 2016 at 10:54 am

    #1895968

  • RE: what are some common naming conventions for sprocs?

    I prefer to name procedures first by topic area as well. I was merely pointing out that it's more common to use verb_object.

    Personally I despise a prefix that represents...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    August 17, 2016 at 9:52 am

    #1895950

  • RE: Need query for After Trigger on update,delete

    Jeff Moden (8/17/2016)

    ScottPletcher (8/17/2016)

    Jeff Moden (8/16/2016)

    ScottPletcher (8/16/2016)

    Jeff Moden (8/16/2016)

    ScottPletcher (8/16/2016)

    Even so, it's likely that for in-house audits, or less formal audits, the current table could be freely accessed, and then...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    August 17, 2016 at 9:37 am

    #1895947

  • RE: what are some common naming conventions for sprocs?

    Probably most common is verb_object, such as Get_UserName, or some such. I've never seen the "main" table name used as part of the proc name, and it seems like...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    August 17, 2016 at 8:53 am

    #1895937

  • RE: Need query for After Trigger on update,delete

    Jeff Moden (8/16/2016)

    ScottPletcher (8/16/2016)

    Jeff Moden (8/16/2016)

    ScottPletcher (8/16/2016)

    Even so, it's likely that for in-house audits, or less formal audits, the current table could be freely accessed, and then INSERTs might not...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    August 17, 2016 at 8:22 am

    #1895929

  • RE: Need query for After Trigger on update,delete

    Jeff Moden (8/16/2016)

    ScottPletcher (8/16/2016)

    Even so, it's likely that for in-house audits, or less formal audits, the current table could be freely accessed, and then INSERTs might not need logged. ...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    August 16, 2016 at 12:27 pm

    #1895743

  • RE: Update statistics after Index Rebuild ?

    Grant Fritchey (8/16/2016)

    MyDoggieJessie (8/16/2016)

    I've always been a little fuzzy on this one - So, is just executing sp_updatestats a safe bet after indexing? Realizing that rebuilildng indexes will only...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    August 16, 2016 at 11:32 am

    #1895727

Viewing 15 posts - 4,201 through 4,215 (of 7,619 total)