Replace a Cursor

Question

Replace a Cursor

Viewing 6 posts - 16 through 20 (of 20 total)

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

ksrikanth77 SSCrazy Points: 2232 More actions · Answer 1

Thanks Lynn for the code. It works as expected however the length of the @STR Nvarchar(Max) variable is not enough to generate the complete select statement for all the companies.Is there any way to extend the variable size?

Thanks

SR

ksrikanth77 SSCrazy Points: 2232 More actions · Answer 2

ksrikanth77

SSCrazy

Points: 2232

July 3, 2014 at 2:52 pm

#1726967

Sorry the Variable @SQLCmd and not @STR

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

ksrikanth77 (7/3/2014)
Thanks Lynn for the code. It works as expected however the length of the @STR Nvarchar(Max) variable is not enough to generate the complete select statement for all the companies.Is there any way to extend the variable size?
Thanks
SR

Are you saying that because the print statment of the dynamic SQL variable truncated the output? Check the length of the data in the variable. The SQL variable will only print about 8000 bytes or less. Remember that it's an NVARCHAR so it'll probably only print 4,000 characters or less.

--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.
"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)
Intro to Tally Tables and Functions

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 4

Jeff Moden (7/3/2014)
ksrikanth77 (7/3/2014)
Thanks Lynn for the code. It works as expected however the length of the @STR Nvarchar(Max) variable is not enough to generate the complete select statement for all the companies.Is there any way to extend the variable size?
Thanks
SR
Are you saying that because the print statment of the dynamic SQL variable truncated the output? Check the length of the data in the variable. The SQL variable will only print about 8000 bytes or less. Remember that it's an NVARCHAR so it'll probably only print 4,000 characters or less.

Jeff is correct. NVARCHAR(MAX) will hold 2GB of data.

Still waiting for an explanation as to why it was so important to eliminate the cursor from the code.

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 5

If you are running the code in SSMS add the following after the PRINT. You can then click on the results and it will bring up the code in an xml tab but it will be all text.

select cast(@SQLCmd as xml);

Sean Lange SSC Guru Points: 286562 More actions · Answer 6

anthonyk653 (7/16/2014)
Hello,
I used Long Path Tool software that simply worked for me for Long Path files. It helped me alot.
Thank you....

This sure sounds like spam to me. It doesn't seem to have any relevance to the question and this exact answer has appeared more than once.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/