Forum Replies Created

Viewing 15 posts - 931 through 945 (of 7,613 total)

  • Reply To: Find string and stop there

    I'd urge you not to write a query based only on current data, but to make it more flexible:

    --
    insert into #tbl_backup_path values ('\\x.x.x.x\Database_Backup_Daily\ServerName$SQLEXPRESS\DB_5\DIFF\ServerName$SQLEXPRESS_DB_4_FULL_20220327_091554.diff')


    SELECT backup_path, LEFT(backup_path, LEN(backup_path) -...

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

    April 4, 2022 at 2:31 pm

    #4010545

  • Reply To: Update a sql_variant column

    mattias.palm1 wrote:

    Where does the data type int come from?

    //Naper72

    It comes from the "ELSE 0", as ratbak implied.  The "0" by itself is a numeric.  Use "'0'" instead.

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

    April 4, 2022 at 2:25 pm

    #4010544

  • Reply To: Converting varchar to Int for date difference

    Btw, I think it would be better if you used a separate column for the string message to keep the diff column as only numeric, but that's just IMO.

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

    April 4, 2022 at 2:22 pm

    #4010543

  • Reply To: SQL query is taking 9 hours to complete

    (1) You need to verify that the 3 values are unique on at least one table; otherwise you will get the equivalent of a CROSS JOIN for all non-unique groups. ...

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

    April 4, 2022 at 2:03 pm

    #4010537

  • Reply To: Best Method for

    If you do not need full-text indexing nor filestream data on the reporting db, you could consider using a db snapshot as "DB2".  The initial create of the snapshot is...

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

    March 31, 2022 at 2:07 pm

    #4008860

  • Reply To: Stored Procedure Performance

    cmartel 20772 wrote:

    In an earlier post, Scott suggested to add BEGIN TRANSACTIONS and COMMIT TRANSACTIONS. As expected this only makes the procedure run much slower.

    I suggested to add them to BLOCKS...

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

    March 31, 2022 at 1:45 pm

    #4008853

  • Reply To: Stored Procedure Performance

    I agree that you'd most likely be better off using a single UPDATE for specific conditions whenever possible.

    However, if that's not feasible at this time, other possibilites are:

    (1) Add BEGIN...

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

    March 29, 2022 at 6:23 pm

    #4007799

  • Reply To: Remove some text

    Not sure what you mean.  It did remove 'rtf' from the string.  What more, specifically, do you want it to do?

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

    March 28, 2022 at 8:14 pm

    #4007279

  • Reply To: Trigger: Need help to save activity log on delete

    Michael L John wrote:

    ScottPletcher wrote:

    As to putting FKs on an audit table... don't EVER do that.  There's not reason to because the rows have already survived the INSERTs and UPDATEs for FKs...

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

    March 28, 2022 at 8:07 pm

    #4007277

  • Reply To: Remove some text

    REPLACE(Note, 'rtf', '')

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

    March 28, 2022 at 8:04 pm

    #4007274

  • Reply To: SQL Select only business hours data

    Jeff Moden wrote:

    ScottPletcher wrote:

    If they're still too incompetent to do it, hopefully they (1) realize that and get help (2) you realize that and get rid of them if they just...

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

    March 28, 2022 at 6:38 pm

    #4007247

  • Reply To: Refactoring an Entity Attribute Value schema

    That is a classic supertype/subtyte setup.  (Similar to sys.objects being a supertype in SQL Server to subtypes of table, procedure, etc.)

    But you're missing the "supertype" table.   That is, you 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".

    March 28, 2022 at 6:32 pm

    #4007241

  • Reply To: Trigger: Need help to save activity log on delete

    As to putting FKs on an audit table... don't EVER do that.  There's not reason to because the rows have already survived the INSERTs and UPDATEs for FKs and FKs...

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

    March 28, 2022 at 5:44 pm

    #4007218

  • Reply To: SQL Server Jobs - Retry Attempts

    Rather than just going back to the first step, I would go to a later, isolated step in the job, and ultimately that step would go back to the first...

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

    March 28, 2022 at 5:33 pm

    #4007213

  • Reply To: SQL Select only business hours data

    Jeff Moden wrote:

    ScottPletcher wrote:

    Since you're only checking for an hour range, you can use BETWEEN.  It's just as accurate and easier to code and read.

    I agree that it's real tempting 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".

    March 25, 2022 at 8:27 pm

    #4006086

    • Viewing 15 posts - 931 through 945 (of 7,613 total)