Forum Replies Created

Viewing 15 posts - 1,591 through 1,605 (of 2,463 total)

  • RE: Get just the .mdf and .ldf names not the path.

    Excellent, glad that worked for you.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    April 17, 2015 at 12:30 pm

    #1791768

  • RE: selective UNIONing

    With respect to records that are duplicated, let's say you only wanted the one with the most recent

    To get the records with the oldest Export date you could do...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    April 17, 2015 at 12:16 pm

    #1791766

  • RE: selective UNIONing

    I'm assuming you want your query results to include the Export date? and if so which record to you want keep (e.g. the one with the earliest Export date? oldest?)

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    April 17, 2015 at 12:01 pm

    #1791763

  • RE: Get just the .mdf and .ldf names not the path.

    Nope, not that I am aware of. Not without using a sp_cmdshell or other non-tsql scripting tool

    What's wrong with this?

    SELECT

    DbName = db.name,

    FileType = CASE...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    April 17, 2015 at 11:54 am

    #1791761

  • RE: Find/Replace based on a table - strange results

    Katerine459 (4/16/2015)

    Alan.B - thanks for the DDL correction. Sorry about that; I totally forgot to indicate NULL/NOT NULL when setting up the table originally, so SQL Server assumed NULL for...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    April 16, 2015 at 9:16 pm

    #1791545

  • RE: Find/Replace based on a table - strange results

    Okay, I can't come up with a pure set-based way to handle this but I have a solution that I think is pretty slick using a recursive CTE wrapped in...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    April 16, 2015 at 9:03 pm

    #1791544

  • RE: Find/Replace based on a table - strange results

    I really like the way you think. Unfortunately UPDATE does not work the way you are trying to use it.

    Your DDL is bad and some sample data for questions...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    April 16, 2015 at 7:43 pm

    #1791539

  • RE: ROW_NUMBER/RANk RESET WHEN ON/OFF?

    Can you include ddl and sample data for #test

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    April 16, 2015 at 5:35 pm

    #1791524

  • RE: How to create numerous directories on a server using data from sql table

    Jeff Moden (4/16/2015)

    Alan.B (4/16/2015)

    What about a SQL Agent job that kicks off a script? Powershell perhaps?

    If that's not an option, and if Lowell's solution (enable xp_cmdshell then disable when...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    April 16, 2015 at 4:26 pm

    #1791512

  • RE: How to create numerous directories on a server using data from sql table

    What about a SQL Agent job that kicks off a script? Powershell perhaps?

    If that's not an option, and if Lowell's solution (enable xp_cmdshell then disable when you're done) does...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    April 16, 2015 at 4:12 pm

    #1791509

  • RE: Add carriage return to a full address field

    spaghettidba (4/16/2015)

    In windows the line separator is CR+LF. In linux it's LF.

    CR = CHAR(13)

    LF = CHAR(10)

    Got it. Thanks!

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    April 16, 2015 at 3:11 pm

    #1791493

  • RE: Add carriage return to a full address field

    spaghettidba (4/16/2015)

    Concat NULL yelds NULL:

    SELECT

    ISNULL(SAD.HOUSENUMBER + CHAR(13) + CHAR(10),'') +

    ISNULL(SAD.ADDRESS1 + CHAR(13) + CHAR(10),'') +

    ISNULL(SAD.ADDRESS2 +...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    April 16, 2015 at 3:02 pm

    #1791488

  • RE: Unusual Recursive CTE with non-unique codes

    Good job with the DDL but to get a better answer it would be helpful if you could include some sample values so that we can better understand what you...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    April 16, 2015 at 2:50 pm

    #1791486

  • RE: Clustered Index

    What do you mean by "Partition Column"?

    Partition get's used in many contexts: Partition key, Partitioned table... A partition key is generally a non-distinct value/key that is used to group...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    April 16, 2015 at 2:41 pm

    #1791483

  • RE: Can Anyone give me a solution to my subquery in the from clause?

    Looks like you have duplicate column names in both tables but you are not using a table alias

    You query should look something like this:

    SELECT a.col1, a.col2, b.col1

    FROM a

    Join

    (

    <Subquery>

    ) AS...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

    April 16, 2015 at 1:21 pm

    #1791464

Viewing 15 posts - 1,591 through 1,605 (of 2,463 total)