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.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 nvarchar(max) truncates data......

Author  Topic 

ibin
Starting Member

26 Posts
Posted - 2009-10-14 : 05:35:48
is there any datatype which has more storage than nvachar(max). I am using dynamic sql and it gets truncated.

sanoj_av
Posting Yak Master

118 Posts
Posted - 2009-10-14 : 05:38:42
The capacity of nvarchar(max) is 2 GB. what you are going to store in it over and above this? Can you post the entire query?
Go to Top of Page

ibin
Starting Member

26 Posts
Posted - 2009-10-14 : 05:54:13
something similar to this.. there are many columns also in each select statement


select @strsql='if Exists(select * from information_schema.columns where table_Name = N'''+@tableName+''' and column_name = N''testcol1'')
begin
insert into #temp exec sp_executesql N''Select col1 as IDLinkItem, testcol1 as IDLinkItemParent from '+@tableName+' ,tblCheck cld where cld.Check like ''''comp''''''
end
if Exists(select * from information_schema.columns where table_Name = N'''+@tableName+''' and column_name = N''testcol1'')
begin
insert into #temp exec sp_executesql N''Select col1 as IDLinkItem, testcol1 as IDLinkItemParent from '+@tableName+' ,tblCheck cld where cld.Check like ''''comp''''''
end
if Exists(select * from information_schema.columns where table_Name = N'''+@tableName+''' and column_name = N''testcol1'')
begin
insert into #temp exec sp_executesql N''Select col1 as IDLinkItem, testcol1 as IDLinkItemParent from '+@tableName+' ,tblCheck cld where cld.Check like ''''comp''''''
end
if Exists(select * from information_schema.columns where table_Name = N'''+@tableName+''' and column_name = N''testcol1'')
begin
insert into #temp exec sp_executesql N''Select col1 as IDLinkItem, testcol1 as IDLinkItemParent from '+@tableName+' ,tblCheck cld where cld.Check like ''''comp''''''
end
if Exists(select * from information_schema.columns where table_Name = N'''+@tableName+''' and column_name = N''testcol1'')
begin
insert into #temp exec sp_executesql N''Select col1 as IDLinkItem, testcol1 as IDLinkItemParent from '+@tableName+' ,tblCheck cld where cld.Check like ''''comp''''''
end
if Exists(select * from information_schema.columns where table_Name = N'''+@tableName+''' and column_name = N''testcol1'')
begin
insert into #temp exec sp_executesql N''Select col1 as IDLinkItem, testcol1 as IDLinkItemParent from '+@tableName+' ,tblCheck cld where cld.Check like ''''comp''''''
end
if Exists(select * from information_schema.columns where table_Name = N'''+@tableName+''' and column_name = N''testcol1'')
begin
insert into #temp exec sp_executesql N''Select col1 as IDLinkItem, testcol1 as IDLinkItemParent from '+@tableName+' ,tblCheck cld where cld.Check like ''''comp''''''
end
if Exists(select * from information_schema.columns where table_Name = N'''+@tableName+''' and column_name = N''testcol1'')
begin
insert into #temp exec sp_executesql N''Select col1 as IDLinkItem, testcol1 as IDLinkItemParent from '+@tableName+' ,tblCheck cld where cld.Check like ''''comp''''''
end
if Exists(select * from information_schema.columns where table_Name = N'''+@tableName+''' and column_name = N''testcol1'')
begin
insert into #temp exec sp_executesql N''Select col1 as IDLinkItem, testcol1 as IDLinkItemParent from '+@tableName+' ,tblCheck cld where cld.Check like ''''comp''''''
end
if Exists(select * from information_schema.columns where table_Name = N'''+@tableName+''' and column_name = N''testcol1'')
begin
insert into #temp exec sp_executesql N''Select col1 as IDLinkItem, testcol1 as IDLinkItemParent from '+@tableName+' ,tblCheck cld where cld.Check like ''''comp''''''
end
if Exists(select * from information_schema.columns where table_Name = N'''+@tableName+''' and column_name = N''testcol1'')
begin
insert into #temp exec sp_executesql N''Select col1 as IDLinkItem, testcol1 as IDLinkItemParent from '+@tableName+' ,tblCheck cld where cld.Check like ''''comp''''''
end
if Exists(select * from information_schema.columns where table_Name = N'''+@tableName+''' and column_name = N''testcol1'')
begin
insert into #temp exec sp_executesql N''Select col1 as IDLinkItem, testcol1 as IDLinkItemParent from '+@tableName+' ,tblCheck cld where cld.Check like ''''comp''''''
end
if Exists(select * from information_schema.columns where table_Name = N'''+@tableName+''' and column_name = N''testcol1'')
begin
insert into #temp exec sp_executesql N''Select col1 as IDLinkItem, testcol1 as IDLinkItemParent from '+@tableName+' ,tblCheck cld where cld.Check like ''''comp''''''
end
if Exists(select * from information_schema.columns where table_Name = N'''+@tableName+''' and column_name = N''testcol1'')
begin
insert into #temp exec sp_executesql N''Select col1 as IDLinkItem, testcol1 as IDLinkItemParent from '+@tableName+' ,tblCheck cld where cld.Check like ''''comp''''''
end
if Exists(select * from information_schema.columns where table_Name = N'''+@tableName+''' and column_name = N''testcol1'')
begin
insert into #temp exec sp_executesql N''Select col1 as IDLinkItem, testcol1 as IDLinkItemParent from '+@tableName+' ,tblCheck cld where cld.Check like ''''comp''''''
end
if Exists(select * from information_schema.columns where table_Name = N'''+@tableName+''' and column_name = N''testcol1'')
begin
insert into #temp exec sp_executesql N''Select col1 as IDLinkItem, testcol1 as IDLinkItemParent from '+@tableName+' ,tblCheck cld where cld.Check like ''''comp''''''
end
if Exists(select * from information_schema.columns where table_Name = N'''+@tableName+''' and column_name = N''testcol1'')
begin
insert into #temp exec sp_executesql N''Select col1 as IDLinkItem, testcol1 as IDLinkItemParent from '+@tableName+' ,tblCheck cld where cld.Check like ''''comp''''''
end
if Exists(select * from information_schema.columns where table_Name = N'''+@tableName+''' and column_name = N''testcol1'')
begin
insert into #temp exec sp_executesql N''Select col1 as IDLinkItem, testcol1 as IDLinkItemParent from '+@tableName+' ,tblCheck cld where cld.Check like ''''comp''''''
end
if Exists(select * from information_schema.columns where table_Name = N'''+@tableName+''' and column_name = N''testcol1'')
begin
insert into #temp exec sp_executesql N''Select col1 as IDLinkItem, testcol1 as IDLinkItemParent from '+@tableName+' ,tblCheck cld where cld.Check like ''''comp''''''
end'
exec(@strsql)
Go to Top of Page

sanoj_av
Posting Yak Master

118 Posts
Posted - 2009-10-14 : 06:07:49
ok. What is the error you get when executing it?. why did you feel it is string truncation error?. If you try to print the @Strsql in the query windows,It may not display the entire string depeniding on the query result maximum number of characters settings in SQL Server client.

Try to print the last x characters in @strSql, if you really doubt about this. some thing like
Select Right(@strsql,100)
Go to Top of Page

ibin
Starting Member

26 Posts
Posted - 2009-10-14 : 07:19:25
thanks for ur reply... since teh if statements are independent i am executing it sequentially n it works...but now one more problem

select @strsql='if Exists(select * from information_schema.columns where table_Name = N'''+@tableName+''' and column_name = N''testcol1'')
begin
exec sp_executesql N''Select col1 as IDLinkItem, testcol1 as IDLinkItemParent into #temp from '+@tableName+' ,tblCheck cld where cld.Check like ''''comp''''''
end'
exec(@strsql)
select @strsql='if Exists(select * from information_schema.columns where table_Name = N'''+@tableName+''' and column_name = N''testcol1'')
begin
exec sp_executesql N''Select col1 as IDLinkItem, testcol1 as IDLinkItemParent into #temp from '+@tableName+' ,tblCheck cld where cld.Check like ''''comp''''''
end'
exec(@strsql)


i am trying to put it into #temp table. it says its executed. but when i do a select * from #temp it says "Msg 208, Level 16, State 0, Line 1
Invalid object name '#temp'."

does select into temp does not work inside exec sp_executesql??
Go to Top of Page

Kabila
Starting Member

33 Posts
Posted - 2009-10-14 : 08:00:48
Use Global Temp table(##Temp)
Go to Top of Page

sanoj_av
Posting Yak Master

118 Posts
Posted - 2009-10-15 : 06:43:05
Or include the select statement in the same dynamci sql statement.

select @strsql='if Exists(select * from information_schema.columns where table_Name = N'''+@tableName+''' and column_name = N''testcol1'')
begin
exec sp_executesql N''Select col1 as IDLinkItem, testcol1 as IDLinkItemParent into #temp from '+@tableName+' ,tblCheck cld where cld.Check like ''''comp''''''
end'
exec(@strsql)
select @strsql='if Exists(select * from information_schema.columns where table_Name = N'''+@tableName+''' and column_name = N''testcol1'')
begin
exec sp_executesql N''Select col1 as IDLinkItem, testcol1 as IDLinkItemParent into #temp from '+@tableName+' ,tblCheck cld where cld.Check like ''''comp''''''
end; select * from #temp'
exec(@strsql)
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site