Forum Replies Created

Viewing 15 posts - 1,246 through 1,260 (of 7,613 total)

  • Reply To: The metadata could not be determined because statement

    Create the temp table before calling the proc.  Have a flag that is passed in to "tell" the proc to load its results into the temp table, or you could...

    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".

    September 28, 2021 at 3:34 am

    #3933759

  • Reply To: Using (or not) query HINTS to speed up slow statement

    frederico_fonseca wrote:

    sql playing dumb on this case

    if your software can be set to do other types of joins/cross applies or even to, as you said, generate the isnull (which works...

    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".

    September 27, 2021 at 3:20 pm

    #3933555

  • Reply To: Conversion failed when converting date and/or time from character string

    Not one of these are valid date/datetime values:

    'Sep 01', '2001-Q3', '2001-Q2', 'Sep 01', '2001-Q3', '2001-Q2'

    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".

    September 26, 2021 at 10:22 pm

    #3933274

  • Reply To: Need an urgent help with creating a relational database!!

    Whoever's teaching your class is not particularly expert at db design.  Storing NNN-NNN-NNN is just plain wrong; it's a waste of space to store fixed dashes in a column.

    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".

    September 26, 2021 at 10:17 pm

    #3933272

  • Reply To: How show only first friday of every month

    Gotta say this, since it's a pet peeve of mine = Using comments in place of accurate names.

    Viz, why?:

    FROM Tally t --Month count sequence starting at 0

    rather than:

    FROM Tally month_count...

    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".

    September 24, 2021 at 7:43 pm

    #3932878

  • Reply To: Using (or not) query HINTS to speed up slow statement

    cmartel 20772 wrote:

    Hi Scott,

    like I mentioned earlier, the WHERE clause is added afterwards in my application (so the user can choose to see archived corporations only, active corporations only or both)....

    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".

    September 24, 2021 at 7:39 pm

    #3932877

  • Reply To: Using (or not) query HINTS to speed up slow statement

    cmartel 20772 wrote:

    Hi Scott, this is oversimplified but it reproduces the problem. The question becomes, what is missing so we do not have to wrap EA.IsArchived in a ISNULL?

    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".

    September 24, 2021 at 6:32 pm

    #3932851

  • Reply To: Have a total by Entity instead of Line total

    Yeah, you can test for NULLs in columns and/or GROUPING_ID to determine: which rows are total rows and which rows are detail rows; to exclude total rows you don't want;...

    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".

    September 24, 2021 at 6:09 pm

    #3932846

  • Reply To: Using (or not) query HINTS to speed up slow statement

    Actually, if you want to comprehensively review the indexes on those table, run this script twice (once for 'Corporation%' tables and once for 'Entity%' tables) and post both result sets...

    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".

    September 24, 2021 at 6:03 pm

    #3932842

  • Reply To: Using (or not) query HINTS to speed up slow statement

    You should be able to script this out.  Make sure the "Script indexes" option is on.  Then Script the table; that should give you DDL for the table and all...

    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".

    September 24, 2021 at 5:57 pm

    #3932839

  • Reply To: Have a total by Entity instead of Line total

    OK.  You said "instead of" so I figured you didn't want the other details in the new query.

    Maybe try:

    GROUP BY entity,groupname,description,effdate WITH ROLLUP

    You can get rid of total lines you...

    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".

    September 24, 2021 at 5:55 pm

    #3932838

  • Reply To: Have a total by Entity instead of Line total

    Not 100% sure what you need, but probably:

    SELECT  entity
            ,description
            ,[Total] =...

    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".

    September 24, 2021 at 5:32 pm

    #3932825

  • Reply To: Using (or not) query HINTS to speed up slow statement

    Do you have an index on:

    CorporationHistory ( CorporationID, EffectiveDate )?

    If not, you need one.

    I specialize in tuning SQL Server.  It's easy to overlook indexes if you don't have a lot...

    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".

    September 24, 2021 at 5:23 pm

    #3932819

  • Reply To: How show only first friday of every month

    Jonathan AC Roberts wrote:

    Jeff Moden wrote:

    I'd be real careful with that.  It uses the WEEKDAY date part, which is subject to change and most recommend not using it for such calculations.  I...

    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".

    September 24, 2021 at 4:04 pm

    #3932797

  • Reply To: Wait type LATCH_EX [NESTING_TRANSACTION_FULL] - and blocking.

    Create all temp tables separately from loading them.  Bonus: you could then also properly cluster the temp tables before loading them.

    The direct load of table with SELECT ... INTO #temp...

    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".

    September 24, 2021 at 3:49 pm

    #3932779

    • Viewing 15 posts - 1,246 through 1,260 (of 7,613 total)