Forum Replies Created

Viewing 15 posts - 301 through 315 (of 7,626 total)

  • Reply To: scalar function "select" permission issue when Grant has been applied "execute"

    There has to be a DENY somewhere to generate that message.  DENY would override normal SELECT permissions.

    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 10, 2024 at 2:42 pm

    #4359741

  • Reply To: How I can check index size after compression?

    After you've row compressed the data, you can get a pretty idea for page compression using SQL's estimate:

    EXEC sys.sp_estimate_data_compression_savings 'dbo', 'PlayersSets', NULL, NULL, 'PAGE'

     

    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 10, 2024 at 2:37 pm

    #4359740

  • Reply To: Parse out filed as date time ???

    My original code gives a result for the new data, since I purposely wrote the original code generically:

    ~__2019121322545101GMTOFFSET=-18000&DPT=205~__2019121408520502GMTOFFSET=-18000~__2019121409443301GMTOFFSET=-18000&DPT=242~__2019121416570802GMTOFFSET=-18000~

    2019-12-14 08:52:00.000

    2019-12-14 09:44:00.000

    If the first two values are not the correct ones, how...

    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 5, 2024 at 7:11 pm

    #4357395

  • Reply To: Parse out filed as date time ???

    The code will just get the first two numbers, which might cause an "error" of bad data if the first two numbers don't have the datetimes you need.  Otherwise, I...

    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 4, 2024 at 8:53 pm

    #4356888

  • Reply To: Parse out filed as date time ??? 

    DROP TABLE IF EXISTS #data;
    CREATE TABLE #data ( data varchar(4000) NULL );
    INSERT INTO #data VALUES
        ('~__2019121407025301GMTOFFSET=-18000~__2019121415164202GMTOFFSET=-18000~')

    SELECT data, 
        DATEADD(MINUTE, CAST(SUBSTRING(data, date1_start...

    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 4, 2024 at 4:05 pm

    #4356772

  • Reply To: Basic query performance

    Use EXISTS() instead of a JOIN (although SQL likely already is doing the equivalent of that in the plan, it doesn't hurt to be sure).

    The only other thing 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".

    January 3, 2024 at 9:24 pm

    #4356336

  • Reply To: Query Tuning

    If you typically/most often query the table by Final_date, you need to cluster the table first on Final_date.  If  you have a column that would naturally make the index unique,...

    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 2, 2024 at 3:09 pm

    #4355657

  • Reply To: Dyanamic SQL to call sproc with input params and an output param

    I think you would want something more like this:

    DECLARE @DestinationTableName SYSNAME = 'dbo.tableA'
    ,@id NVARCHAR(30)
    ,@ProcessName VARCHAR(100) = 'ETL_InitialLoad'
    ,@SQL NVARCHAR(MAX)

    SELECT @SQL = 'IF NOT EXISTS (SELECT * FROM dbo.ETL_lOG...

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

    December 31, 2023 at 6:31 am

    #4354723

  • Reply To: Temp Tables Used within Procedure giving error in one 2019 sql server instance

    >> will process and return a table with Say DynamicMaster with column Distance added along with respective Data <<

    How, specifically, does it return a table?  Does it create a temp...

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

    December 27, 2023 at 7:23 pm

    #4353204

  • Reply To: string_agg issue

    Great.  Glad it helped, and that the abbreviated way I posted it made sense.

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

    December 20, 2023 at 3:42 pm

    #4349688

  • Reply To: string_agg issue 

    select SAR.ENC_IDN                         ...

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

    December 19, 2023 at 10:35 pm

    #4349179

  • Reply To: Different execution plan on literals/constant VS variables

    (1) Make sure any relevant statistics on the table are up to date.

    (2) Easiest might be to add:

    OPTION(RECOMPILE)

    to the end of the SQL and see if it helps; iirc, 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".

    December 19, 2023 at 5:27 pm

    #4349046

  • Reply To: string_agg issue

    Maybe like this?:

    select 
           s.MBR_NAME                ...

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

    December 19, 2023 at 3:49 pm

    #4348998

  • Reply To: Retrieving First Word, First + Second Word, First + Second + Third Word, First

    Or maybe just this:

    SELECT CompanyName, 
        LEFT(CompanyName, EndOfWord1) AS [First Word], 
        LEFT(CompanyName, EndOfWord2) AS [First  + Second Word],
     ...

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

    December 12, 2023 at 10:09 pm

    #4344779

  • Reply To: Selecting from a view, base table has a DENY on a column

    You need to look at the "Estimated" plan for the one that doesn't work, just to be sure.  See if anywhere SQL is referencing or "output"ing the restricted 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".

    December 11, 2023 at 9:08 pm

    #4344103

Viewing 15 posts - 301 through 315 (of 7,626 total)