Forum Replies Created

Viewing 15 posts - 1,006 through 1,020 (of 7,619 total)

  • Reply To: Bulk Insert Stored Procedure

    SQL Server does not all "FROM @FILENAME", only "FROM 'data_file_name'".  Google the syntax of the command.

    You'll need to use dynamic SQL if you want to feed in a FROM file...

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

    February 2, 2022 at 11:29 pm

    #3980270

  • Reply To: Update table based on Rownumber

    I'm guessing you don't already have a row_num.

    ;WITH cte_add_row_num AS (
        SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DATE DESC) AS row_num
     ...

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

    February 1, 2022 at 5:39 pm

    #3979677

  • Reply To: Where condition for performance

    Would need to see the tables' DDL and the actual query plan.  Can't troubleshoot air.

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

    February 1, 2022 at 1:02 am

    #3979406

  • Reply To: left join causing results to be multiplied

    Yeah, really should have mentioned that Pervasive thing to begin with :-).  That means there are multiple rows being returned for each lookup.  Which one you get will be random,...

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

    January 31, 2022 at 9:06 pm

    #3979339

  • Reply To: Question about malformed ZIP codes

    Jeffrey Williams wrote:

    Jeff Moden wrote:

    Another possibility is a bit shorter, uses only LIKE instead of LIKE and the LEN() function, and does the same thing is...

    IIF(D.Zip LIKE '[0-9][0-9][0-9][0-9][0-9]', d.Zip,...

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

    January 31, 2022 at 6:37 pm

    #3979302

  • Reply To: Restore autmomation

    Jeff Moden wrote:

    --...
          ALTER DATABASE [DBNameToRestoreHere] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
              ALTER DATABASE [DBNameToRestoreHere]...

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

    January 31, 2022 at 4:41 pm

    #3979251

  • Reply To: Question about malformed ZIP codes

    I say don't use IIF.  It's a completely foreign coding approach to other SQL.  And there's no genuine need for it, since CASE can do the same thing, and IIF...

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

    January 31, 2022 at 4:15 pm

    #3979238

  • Reply To: Question about malformed ZIP codes

    If you want to rely on the existing zip always being fully padded on the left with zeros, you can do this:

    CASE WHEN LEN(D.Zip) = 5 AND...

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

    January 28, 2022 at 10:04 pm

    #3978506

  • Reply To: Recursive Triggers

    Ken at work wrote:

    Ed B wrote:

    ScottPletcher wrote:

    You could probably use TRIGGER_NESTLEVEL to do that.

    I tend to use session context values instead, set via sys.sp_set_session_context and SESSION_CONTEXT(N'<key_name>').  If you want more info on this...

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

    January 28, 2022 at 8:21 pm

    #3978480

  • Reply To: Data not returned in Date Range WHERE

    Jeff Moden wrote:

    ScottPletcher wrote:

    Don't use functions in a WHERE clause, that's a very bad habit.  Yes, SQL Server now generally covers for you with dates/datetimes, but not always, there are some...

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

    January 27, 2022 at 9:00 pm

    #3978057

  • Reply To: Data not returned in Date Range WHERE

    Eddie Wuerch wrote:

    '19 Nov 2022' is converts to the datetime value of '19 Nov 2022 00:00:00.000', which is earlier (less than) '19 Nov 2022 15:19:12.000'.

    Try:

    WHERE [...]
    AND CONVERT(date, TransferredDate)...

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

    January 27, 2022 at 7:13 pm

    #3978033

  • Reply To: Create View in stored procedure

    And the view did not exist before the proc was called and still did not exist after it was called?

    If so, put the SQL in a variable and print 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".

    January 27, 2022 at 6:44 pm

    #3978026

  • Reply To: Data not returned in Date Range WHERE

    The best way is to use > the next day rather than <= the end day.  Like this:

    DECLARE @StartDate datetime
    DECLARE @EndDate datetime

    SET @StartDate = '18 Nov 2021'
    SET...

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

    January 27, 2022 at 6:40 pm

    #3978025

  • Reply To: Alter table add identity column with (online=on)

    I would think it would be.  Why not try it with a small table and see?

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

    January 27, 2022 at 6:38 pm

    #3978024

  • Reply To: Slow Query

    Jonathan AC Roberts wrote:

    ScottPletcher wrote:

    SELECT DISTINCT GRPID, PLNCD, XPLNE, EXTNO, EffDate, TermDate FROM History WHERE MBRNO IN (SELECT MBRNO FROM dbo.Member_MRN_lkupAllMBRNOs(@strMBRNO)) ORDER BY EffDate ASC

    How large (MB/GB) is the "History" table?

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

    January 27, 2022 at 6:37 pm

    #3978023

Viewing 15 posts - 1,006 through 1,020 (of 7,619 total)