May 11, 2011 at 6:22 am
is there any size limitation for dynamic sql query?
May 11, 2011 at 6:24 am
Assuming the query is smaller than the total ram + disk it should run... there was a 256 table limit in sql 2000, I don't know if it's still there in 2008.
May 11, 2011 at 6:33 am
not in terms of tables, i need max size of dynamic query i can write.
like varchar(8000 ) can't i write more then this size?
May 11, 2011 at 6:37 am
How do you execute it?
EXEC() and sp_executesql have no limits.
May 11, 2011 at 6:50 am
sql server forces me to use nvarchar of which max size is 4000:
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
I have the same problem, I need a 8000 long data type.
May 11, 2011 at 6:54 am
ilker.cikrikcili (5/11/2011)
sql server forces me to use nvarchar of which max size is 4000:Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
I have the same problem, I need a 8000 long data type.
sp_executesql accepts nvarchar(max).
Are you on sql 2000-?
May 11, 2011 at 6:55 am
NVARCHAR(MAX)
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMay 11, 2011 at 7:01 am
Ninja's_RGR'us (5/11/2011)
ilker.cikrikcili (5/11/2011)
sql server forces me to use nvarchar of which max size is 4000:Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
I have the same problem, I need a 8000 long data type.
sp_executesql accepts nvarchar(max).
Are you on sql 2000-?
SQL server 2005
May 11, 2011 at 7:04 am
Then use nvarchar(max). That works.
May 11, 2011 at 7:09 am
Jason Selburg (5/11/2011)
NVARCHAR(MAX)
It's interesting, when I tried it, I didn't get any errors but also nothing returned from the query.
When I run the same query with NVARCHAR(MAX) for a smaller table, it worked ok, returned the record.
Basicly, I have 2 tables: deal and dealpending. dealpending is much bigger.
When I run the query with DECLARE @sql nvarchar(4000); for deal => works.
When I run the query with DECLARE @sql nvarchar(max); for deal => works.
When I run the query with DECLARE @sql nvarchar(4000); for dealpending => query doesnt fit to @sql variable.
When I run the query with DECLARE @sql nvarchar(max); for dealpending => returns null.
May 11, 2011 at 7:13 am
More specifically,
DECLARE @sql NVARCHAR(MAX);
SET @sql = 'SELECT ''' + STUFF((
SELECT '+'':' + name + ';''+CONVERT(VARCHAR(100), ' + name + ')'
FROM sys.columns
WHERE object_id = OBJECT_ID('boss..dealpending','U')
ORDER BY column_id
FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,3,'') +
' FROM dealpending where dealid = 1483436';
PRINT @sql;
EXECUTE sp_executesql @sql;
returns nothing even though
select * from dealpending where dealid = 1483436;
returns the record I want.
May 11, 2011 at 7:13 am
Please post the whole code.
May 11, 2011 at 7:20 am
Ninja's_RGR'us (5/11/2011)
Please post the whole code.
I did that 10 seconds before your post 🙂
May 11, 2011 at 7:21 am
Any reason why you're not using select *?
P.S. Yes I know this is not best practice.
May 11, 2011 at 7:26 am
Ninja's_RGR'us (5/11/2011)
Any reason why you're not using select *?P.S. Yes I know this is not best practice.
Because I'm writing an non-intelligent process. I need the data attached with it's column names and on run time I won't know the table name, I will pass the table name as a parameter and I'm aiming to get this output:
column1:value1,column2:value2,column3:value3.....
I will send this information to another system as a string message, this information will be handled there.
for more information, please see:
http://www.sqlservercentral.com/Forums/Topic1095074-391-1.aspx
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply