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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-03-02 : 07:43:09
|
| Elvis writes "Hi, I'm running this query from the forum: Reverse Pivot tableDECLARE @sql varchar(1000), @table varchar(100),@columna sysnameset @columna = 'ts'SELECT @sql = '', @table = '#TS3'SELECT @sql = @sql + 'SELECT DIA, ''' + @columna + ''' AS CODIGO, ' + @columna + ' AS VALOR FROM ' + table_name + ' UNION ' FROM information_schema.columns WHERE table_name LIKE '#TS3%' AND column_name <>'DIA'SELECT @sql = Left(@sql,Len(@sql)-5)EXEC (@sql)But it is returning me this error:Server: Msg 536, Level 16, State 3, Line 10Invalid length parameter passed to the substring function.I think, the reason is that I'm using a temporary table, because if I change it to a normal table, the error does'nt ocurr. I don't know how solve this problem. Could you help me with this?I'm using, SQL Server 2000 Service Pack 3, Windows 2000 Service Pack 4" |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-03-02 : 10:16:29
|
| 1. PRINT THE @SQL before you execute it.2. The error refers to a SUBSTRING....but your code doesn't show such a word....is this CODE snippet complete??3. Error may be due to a 0 or negative length? |
 |
|
|
doryllis
Starting Member
2 Posts |
Posted - 2006-03-03 : 11:15:56
|
| When you do the Left(), you are assigning the left a len that is less than 1.Check how long your string is before you do that function. |
|
|
|
doryllis
Starting Member
2 Posts |
Posted - 2006-03-03 : 15:04:00
|
quote:
Originally posted by AskSQLTeamSELECT @sql = Left(@sql,Len(@sql)-5)
That should be IF (Len(@sql) > 5) SELECT @sql = Left(@sql,Len(@sql)-5) |
|
|
|
|
|
|
|
|
Error 536. Invalid length parameter passed to the substring function.