Forum Replies Created

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

  • Reply To: Join together two Dynamic SQL variables

    Jeff Moden wrote:

    Jeffrey Williams wrote:

    You should also not use SELECT to set the variable - use SET instead:

    WHY?  What do you base that recommendation on?

    I remember someone testing and saying that it...

    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 30, 2021 at 2:05 am

    #3934873

  • Reply To: Index design on large and "wide" tables

    About the>= vs =, yes, you're correct, it can be = in this example. I didn't mention it, but often we rerun a few days in the back, so we...

    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 30, 2021 at 2:03 am

    #3934872

  • Reply To: Join together two Dynamic SQL variables

    Just an example of concatenation when using EXEC():

    DECLARE @sql1 varchar(1000)

    DECLARE @sql2 varchar(1000)

    SET @sql1 = 'SELECT GETDATE() AS today'

    SET @sql2 = 'SELECT COUNT(*) AS object_count FROM sys.objects'

    EXEC(@sql1 + ';' + @sql2)

    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 29, 2021 at 7:24 pm

    #3934786

  • Reply To: Join together two Dynamic SQL variables

    Oh, sorry, I didn't look closely enough, I thought it was EXEC() and not EXEC sys.sp_executesql.

    For sp_executesql, you must combine code before running the proc.

    DECLARE @sql NVARCHAR(4000)

    ,@SQL1 NVARCHAR(4000);

    SELECT @sql =...

    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 29, 2021 at 7:22 pm

    #3934785

  • Reply To: Join together two Dynamic SQL variables

    You left off the parens (and something to separate the new SELECT from the old command, either a space or a semi-colon):

    EXEC sp_executesql (@SQL + ';' + @SQL1)

    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 29, 2021 at 7:09 pm

    #3934783

  • Reply To: Index design on large and "wide" tables

    To determine proper clustering (PKs) for the tables, we'd need to know more about how the data is most often processed, by statistics type or entityid or timeperiod range.

    However, as...

    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 29, 2021 at 3:40 pm

    #3934718

  • 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

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