Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Mortal Wombat
Starting Member
10 Posts |
 Posted - 2010-11-01 : 09:59:19
|
| I'm still fairly new to the powers of SQL and am trying to solve the following problem and wondered if anyone had some bright ideas to help. What I am trying to achieve is to simply print out some SQL that has been dynamically created just before it is executed for debugging purposes.The dynamic SQL is being stored in an NVARCHAR(MAX) variable before being executed. However, the variable contains more than 8000 characters (including white space) and is therefore being truncated in my PRINT command. My idea was to count the number of characters in the Dynamic SQL using DATALENGTH() and simply divide this among a number of VARCHAR(8000) variables before printing them sequentially. I was going to achieve this using LEFT(@foo, 8000) but this command only returns characters and not bytes which I would require when using DATALENGTH() to encapsulate the white spaces used also. Is anyone aware of a way to cleanly subdivide my NVARCHAR(MAX) variable into a number of smaller VARCHAR(8000) variables so I can cleanly print out the SQL being executed?Thanks |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-01 : 10:07:12
|
| What is limiting you to 8000 characters? SSMS perhaps? (I thought it was 8,000 bytes, i.e. 4,000 characters for Nvarchar)Do you have Chinese characters (i.e. UNICODE 2-byte characters) in your data? If not use VARCHAR and you can see 8,000 characters instead of 4,000. If the statement you are debugging is more than 8,000 characters it might be better off being changed anyway (bit of a sweeping statement as I don't know your system - but if you've got a massive IN (1,2,3...) statement in there that may be slowing it down and may be going to be a problem in its own right) |
 |
|
|
Mortal Wombat
Starting Member
10 Posts |
Posted - 2010-11-01 : 10:40:46
|
| Thanks for the response Kristen,Checking msdn help for PRINT command "A message string can be up to 8,000 characters long if it is a non-Unicode string".I can confirm that I'm not using any Chinese characters but will need to a bit more reading on UNICODE/non-UNICODE to try and better understand this.I'm afraid I don't really have the option of changing the dynamic SQL and just have to deal with the fact it has a DATALENGTH() of 12510. |
|
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2010-11-01 : 11:32:39
|
As a nvarchar with a data length of 12510 is only 6255 characters, why not try:PRINT CAST(@YourString AS varchar(8000)); |
|
|
|
Mortal Wombat
Starting Member
10 Posts |
Posted - 2010-11-01 : 11:47:03
|
quote:
Originally posted by Ifor
As a nvarchar with a data length of 12510 is only 6255 characters, why not try:PRINT CAST(@YourString AS varchar(8000));
Thanks Ifor, that's a great suggestion and has done exactly what I wanted, it's nice and neat too, problem solved ! |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-11-01 : 11:48:29
|
Where are you trying to print? In SSMS?Have a look into Tools -> Options -> Query Results -> Results to Text -> Maximum number of characters displayed in each column:
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-01 : 13:19:10
|
| Ah, I see, well you learn something every day! I had assumed that PRINT would not complain unless the actual value was more than 8,000 characters - seems it complains if the underlying datatype is capable of more than 8,000 characters :(So CASTing it to only 8,000 characters will be fine - provided that your actual dynamic SQL is less than 8,000 characters.Ignore UNICODE - things that Execute dynamic SQL expect to be given it as Nvarchar, so leave that as it is. |
|
|
|
|
|
|
|
|
Extracting varchar from dynamic SQL