Forum Replies Created

Viewing 15 posts - 1,291 through 1,305 (of 7,619 total)

  • Reply To: Invalid length parameter passed to the LEFT or SUBSTRING function.

    Add an asterisk to the end of the string; it won't hurt if it's not needed but is vital if it is needed.

    SELECT

    SUBSTRING(UCL.PROCEDURE_COMMENT,

    CHARINDEX('*', UCL.PROCEDURE_COMMENT) + LEN('*'),

    CHARINDEX('*', UCL.PROCEDURE_COMMENT + '*', --<<--

    CHARINDEX('*',...

    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 9, 2021 at 6:00 pm

    #3928320

  • Reply To: I killed the process. And it is till running.

    Although it seems odd, that's actually quite normal.  The KILLed process must rollback any data it needs to.  Issue a:

    KILL XXX WITH STATUSONLY

    command.  If it reports 0 and 0%, 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".

    September 8, 2021 at 10:57 pm

    #3928036

  • Reply To: get lastname,firstname, middlename as first 3 pipe delimiter last data no need

    To me, 50/50 on using a splitter for just the first 3.  Here's code using CHARINDEX & SUBSTRING:

    DECLARE @string varchar(200)

    SET @string = 'NAZARE|DEV|T||Current~NAZARE|DEV|T||Previous'

    SELECT string, Lastname, Firstname, Middlename
    FROM...

    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 8, 2021 at 3:17 pm

    #3927929

  • Reply To: Inserting records in bulk, but check total quantity of all products first

    Looks like you're trying to do something like below.  Note that if multiple, different ProductIds are INSERTed at the same time, if ANY of them fail, ALL will be rolled...

    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 7, 2021 at 8:36 pm

    #3927628

  • Reply To: How to Copy SPROC and Rename?

    pietlinden wrote:

    This looks like it's supposed to be looking for the @current_proc_name value, not the literal string 'SOURCE', but then WTH do I know?

    AND CHARINDEX('ALTER', @proc_source) <...

    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 7, 2021 at 5:20 pm

    #3927577

  • Reply To: Speeding up Query

    Jeff Moden wrote:

    Ah... be careful.  Changing the clustered index on the quick fly could cost you everywhere else.

    How is that, specifically, when the current clus index is an identity column?  You don't...

    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 7, 2021 at 5:19 pm

    #3927576

  • Reply To: Can anyone recommend training (a book, a tutorial, etc.) for ERwin?

    When I used ERWin (as they spelled it at the time, IIRC) I thought it was excellent tool (once I got used to it).  However, you have to understand data...

    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 7, 2021 at 2:55 pm

    #3927537

  • Reply To: How to calculate the outcome based on multiple records for same Customer

    Sergiy wrote:

    ScottPletcher wrote:

    Actually, Sergiy, for certain calcs -- such as computing interest on a house loan in some places -- it's illegal to use more than 4 digits of accuracy.

    So,...

    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 3, 2021 at 2:15 pm

    #3926674

  • Reply To: CREATE and INSERT permissions to User_X on DB_Z without DROP or DELETE

    For DELETE, a DENY DELETE should work.

    For DROP, if the user created the table, they likely own it, so you'd have to use a DDL trigger for that AFAIK.

    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 2, 2021 at 7:28 pm

    #3926388

  • Reply To: sql server and when adding files and capping space

    Do you have a q?

    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 2, 2021 at 7:25 pm

    #3926387

  • Reply To: How to calculate the outcome based on multiple records for same Customer

    Actually, Sergiy, for certain calcs -- such as computing interest on a house loan in some places -- it's illegal to use more than 4 digits of accuracy.

    So, if 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 2, 2021 at 2:23 am

    #3926144

  • Reply To: Speeding up Query

    Just change the clustered index.  The other method is too much overhead and too much of a pain.  A clus index change is a one-time thing.  All the create-a-temp-table-and-then-join-to-it has...

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

    #3926143

  • Reply To: Speeding up Query

    How is the orders table clustered?  I'm guessing by orderid.

    But, for this query, since jobdatestart is the key restrictor on the number of rows, that would make a better clustering...

    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 1, 2021 at 3:39 pm

    #3925885

  • Reply To: how to get quantity from yesterday 6pm till today 6pm

    Steve Collins wrote:

    The '+/- #n' offset parameter only works (afaik) with the GETDATE() function and not literal values

    I believe the +/- n days works with any datetime value.  I 'm...

    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 1, 2021 at 3:27 pm

    #3925879

  • Reply To: Resource Monitor - Performance Issues

    Do you have the latest CU applied?  That condition is often a bug in SQL code, fixed by maintenance.

    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 1, 2021 at 2:40 pm

    #3925866

Viewing 15 posts - 1,291 through 1,305 (of 7,619 total)