Error while trying to create a csv file

  • Shweta Jain-392928

    Old Hand

    Points: 336

    I have created a stored procudure SP2 which creates the csv file. This store procedure is called within another stored procedure SP1 but when i run stored procedure SP1 which contains execute statement to SP2 from .net application, My table gets locked  and i get a deadlock. the csv file is created but is locked and and it doesn't write anything to it and sits there. Everytime this happens, we have to restart the server. i get the following error when i run the query to see what has happened:

    BlockingSessionID  VictimSessionID BlockingQuery VictimQuery

    82                               96                            xp_cmdshell    SELECT           Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15,Col16,Col17,Col18,Col19,Col20,Col21,Col22 FROM ##csvtemp

    WaitDurationSecond      WaitType              BlockingQueryCompletePercent

    1154                               LCK_M_SCH_S       0

    Don't know how to resolve this issue.

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21492

    you don't have to restart the server,  you can just run "kill 1154" (in this example)

    we would need to see both proc calls, but I suspect that since you are using global temp tables then this is the issue. can you switch to using a TVP?

    it looks like proc 1 is holding a lock and proc 2 is fighting it.

    MVDBA

  • Sue_H

    SSC Guru

    Points: 90543

    It looks like your xp_cmdshell is hanging - and you can't kill that as it spawns a windows process external to SQL Server. You would need to restart the server (as you did) or restart the SQL Server service or kill the PID in Windows - cmd.exe from xp_cmdshell and often any of the processes that xp_cmdshell invoked.

    Without seeing the code it's hard to say what the issues may be but I've seen quite a few posts with the same issue . Try splitting out the xp_cmdshell is about all I can suggest without seeing the code.

    Sue

  • Shweta Jain-392928

    Old Hand

    Points: 336

    ##csvtemp only contains  6 lines.. so it is not big...

     

    set @sql = 'bcp "SELECT Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15,Col16,Col17,Col18,Col19,Col20,Col21,Col22 FROM ##csvtemp" queryout '+@processedFolderLocation+'/"'+@csvFileName+'" -c -d ' + DB_NAME() + ' -t, -S '+ @@servername+ ' -U ******  -P ******** -T'

    exec master..xp_cmdshell @sql

     

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21492

    Shweta Jain-392928 wrote:

    ##csvtemp only contains  6 lines.. so it is not big...

    set @sql = 'bcp "SELECT Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15,Col16,Col17,Col18,Col19,Col20,Col21,Col22 FROM ##csvtemp" queryout '+@processedFolderLocation+'/"'+@csvFileName+'" -c -d ' + DB_NAME() + ' -t, -S '+ @@servername+ ' -U ******  -P ******** -T'

    exec master..xp_cmdshell @sql

    deadlocks are not about the size of the table, it is about 2 competing queries for one resource - in this instance a ## table - your proc2 is competing with proc1 in the same transaction for access to the ## table.

     

    MVDBA

  • Sue_H

    SSC Guru

    Points: 90543

    Shweta Jain-392928 wrote:

    ##csvtemp only contains  6 lines.. so it is not big...

    set @sql = 'bcp "SELECT Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15,Col16,Col17,Col18,Col19,Col20,Col21,Col22 FROM ##csvtemp" queryout '+@processedFolderLocation+'/"'+@csvFileName+'" -c -d ' + DB_NAME() + ' -t, -S '+ @@servername+ ' -U ******  -P ******** -T'

    exec master..xp_cmdshell @sql

    If you can run the bcp command alone without issues then you may want to look at the security context in place when it's run in the stored procedure. It's weird that it creates the file and can't write to it though - maybe make sure the file doesn't already exist.

    Sue

  • Jeff Moden

    SSC Guru

    Points: 995650

    Shweta Jain-392928 wrote:

    ##csvtemp only contains  6 lines.. so it is not big...

    set @sql = 'bcp "SELECT Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15,Col16,Col17,Col18,Col19,Col20,Col21,Col22 FROM ##csvtemp" queryout '+@processedFolderLocation+'/"'+@csvFileName+'" -c -d ' + DB_NAME() + ' -t, -S '+ @@servername+ ' -U ******  -P ******** -T'

    exec master..xp_cmdshell @sql

    Good lord!  Be careful here!  This is a classic reason why people think that xp_CmdShell is a security violation and, in the case of how you've written the code with a hard-coded user name and password, they are incredibly correct (in this case).  You absolutely need to learn to do this with a "Trusted Connection" and without any individual user having the privs to execute xp_CmdShell directly.  They should only have the privs to execute a stored procedure that does such a thing and not even be able to see the content of the stored procedure.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply