Forum Replies Created

Viewing 15 posts - 2,266 through 2,280 (of 7,619 total)

  • Reply To: Trim trailing characters from a varchar?

    I think this will do it:

    SELECT REPLACE(string, '000', '')

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

    October 2, 2020 at 1:40 am

    #3792407

  • Reply To: convert scalar function to table valued function

    tanehome wrote:

    ScottPletcher wrote:

    This code does include the date swap if they pass in a DateFrom that is greater than the DateTo.

    if i need it to does not include date swap,...

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

    October 1, 2020 at 4:50 pm

    #3792313

  • Reply To: SQL database compression before shrink ?

    Do you use secondary filegroups?  If you do, I'd create a new filegroup and files and do the data compression into that new filegroup.  Then you can shrink the original...

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

    October 1, 2020 at 4:23 pm

    #3792305

  • Reply To: DB Space Script - Arithmetic Overflow

    ...

    sum(CAST(size AS bigint))/128.0 AS File_Size_MB,

    sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS bigint))/128.0 as Space_Used_MB,

    SUM(CAST(size AS bigint))/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS bigint))/128.0 AS Free_Space_MB

    ...

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

    October 1, 2020 at 4:21 pm

    #3792302

  • Reply To: convert scalar function to table valued function

    OOPS, yeah, I forgot about the cross-db reference.  I should not have put SCHEMABINDING in there.

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

    October 1, 2020 at 4:11 pm

    #3792301

  • Reply To: Self referencing formula has me stumped

    Here's how to calc the new selling price for any new net margin.  (I always did love algebra.)

    Edit: I didn't see Des's until after I had posted mine.

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

    October 1, 2020 at 4:07 pm

    #3792300

  • Reply To: trigger on a Large 5 billion row table

    Hmm, that task sounds like it should be a single INSERT statement, which means SQL should resolve GETDATE() only once.  Therefore it seems as if your trigger is using a...

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

    October 1, 2020 at 9:57 am

    #3792181

  • Reply To: convert scalar function to table valued function

    You don't need a calendar table for this.  Also, note that the Friday check below works under any/all DATEFIRST settings.  DATEFIRST is not something you want to change in your...

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

    October 1, 2020 at 9:50 am

    #3792178

  • Reply To: Calculating offset (adding days to a date but keeping same day name of week)

    SELECT DATEADD(DAY, (@DaysDifference + 6) / 7 * 7, @OriginalFirstStartDate)

    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, 2020 at 9:04 pm

    #3791760

  • Reply To: issue with adding another column in a table

    The UPDATE "doesn't work" because since reserve is null, it will stay null, even though you think you "added 1 to it".  NULL + 1 = NULL.

    If you prefer, 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 29, 2020 at 8:58 pm

    #3791759

  • Reply To: Only return first matches per Id and calculate difference of dates in minutes

     

    SELECT
        Id,
        MIN(CASE WHEN StatusName = 'Draft' THEN CreatedOn ELSE NULL END) AS Draft,
        MIN(CASE WHEN StatusName =...

    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, 2020 at 4:12 am

    #3791499

  • Reply To: Error for updating Extended Property

    Would you provide the actual T-SQL statement you were attempting to execute?

    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, 2020 at 3:13 pm

    #3791348

  • Reply To: Error - The conversion of a varchar data type to a datetime data

    The fix is:

    Select STUFF((Select Distinct + ', '+ CONVERT(varchar(20), A1.date, 105)

    ...

    Since A1.date is a datetime, which has a higher precedence than varchar, SQL is attempting to add ',' to the...

    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, 2020 at 7:29 am

    #3791230

  • Reply To: about the option (fast n) and acutal execution plan

    Frankly the percentages from an estimated query plan are often so inaccurate they should just be ignored.

    Look at the actual operations in the query plan, not the percentages.

    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, 2020 at 7:23 am

    #3791229

  • Reply To: SQL Agent error history

    Restore old msdb backup(s) to a different db name.  We typically add '_' and the date of the backup to the name.  Something like this:

    RESTORE DATABASE msdb_20190915 FROM DISK =...

    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 25, 2020 at 3:55 pm

    #3790985

    • Viewing 15 posts - 2,266 through 2,280 (of 7,619 total)