Forum Replies Created

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

  • 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

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

    What is the specific error message (and error number and status) you get? (Using dummy column name(s) as needed, of course).

    What is the query plan?  Does it give any help...

    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 6:02 pm

    #4344025

  • Reply To: Compare all tables row counts for 2 server instances?

    You don't need to export to Excel to do the comparison, you can do it directly in SQL Server.

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

    #4342356

  • Reply To: Not enough disk space error

    Look at the files on the drive and see if there is a large, obsolete file(s) (perhaps old backups?).

    Depending on the version of SQL Server, you might also be able...

    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 7, 2023 at 10:38 pm

    #4341815

  • Reply To: STUFF Command Not Working

    Try this.  It may not work because of the GROUPing.  You might have to do the GROUPing after concatenating the subquery results.

    SELECT
    DISTINCT 
    G.[Period], 
    G.[Sector], 
    G.RID, 
    STUFF
    ((
    SELECT 
    DISTINCT
    ...

    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 7, 2023 at 8:52 pm

    #4341776

  • Reply To: Not enough disk space error

    Is your tempdb database on the C: drive?  If so, that could theoretically cause the problem.

    By the way, 9GB is a very tiny amount of free space on a drive. ...

    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 7, 2023 at 3:28 pm

    #4341613

  • Reply To: Small Transactional Table with Frequent Updates

    I don't think you can do much for that table itself.

    You need to look at the code that is UPDATEing that table and make sure that code is efficient.  Also,...

    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 4, 2023 at 6:48 pm

    #4339668

  • Reply To: Commit transaction inside loops

    Would you be able to use TRUNCATE TABLE instead to remove all the rows?  That will be vastly faster.

    If the table has an identity column, and you want to maintain...

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

    November 27, 2023 at 8:04 pm

    #4335440

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