May 11, 2011 at 7:29 am
IIRC this is why xml was invented...
You've said on another thread that you can't use nvarchar(max). Why?
Assuming this is true then you are basically screwed at building the code from the app side and using execute on that side.
May 11, 2011 at 7:45 am
Ninja's_RGR'us (5/11/2011)
IIRC this is why xml was invented...
Yes, normally what we supposed to do is, get the data, match it with column names to xml tags and send as a message. But I'm trying to develop a generic application which doesn't include any business logic. It will be triggered from an outer application with table name and ID passed as a parameter, generic application will just run a script (blindly) to gather the whole data and send back.
Therefore it's illegal to write a code like:
if(tableName="deal"){
select * from deal;
}else if(tableName="dealPending"){
select * from dealpending;
}
Ninja's_RGR'us (5/11/2011)
You've said on another thread that you can't use nvarchar(max). Why?
I can but it returns null. I'll send table creation script shortly so that you can test the same on your side.
Ninja's_RGR'us (5/11/2011)
Assuming this is true then you are basically screwed at building the code from the app side and using execute on that side.
I'm simply trying to run this script from sql server management studio. I even haven't tried to run it from c++ code.
May 11, 2011 at 7:50 am
WHERE object_id = OBJECT_ID('boss..dealpending','U')
if boss is another database the query will return nothing, hence null.
I'm testing some code in case the double .. is a typo.
May 11, 2011 at 7:52 am
In SQL2000 you could get over any limit for the size of the variable containing your SQL by declaring and executing the SQL from multiple concatenated variables. These weren't realised anywhere in memory, and therefore had no practical limit. I assume this is still valid for SQL2008 e.g.
DECLARE @sql1 nvarchar(4000)
DECLARE @sql2 nvarchar(4000)
DECLARE @sql3 nvarchar(4000)
SET @sql1 = 'a load of very long sql '
SET @sql2 = 'even more very long sql '
SET @sql1 = 'yet more very long sql '
EXEC(@sql1 + @sql2 + @sql3)
May 11, 2011 at 7:53 am
I tested this on my machine and it works (but I do get syntax error in the next query so I'd focus there if I were you) :
DECLARE @sql NVARCHAR(MAX);
SET @sql = 'SELECT ''' + STUFF((
SELECT '+'':' + name + ';''+CONVERT(VARCHAR(100), ' + name + ')'
FROM sys.columns
WHERE object_id = OBJECT_ID('Groupe Whatever Inc_$Sales Header','U')
ORDER BY column_id
FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,3,'') +
' FROM dealpending where dealid = 1483436';
SELECT LEN(@sql), @sql
--EXECUTE sp_executesql @sql;
(Aucun nom de colonne)(Aucun nom de colonne)
10466SELECT 'timestamp;'+CONVERT(VARCHAR(100), timestamp)+':Document Type;'+CONVERT(VARCHAR(100), Document Type(VARCHAR(100),
...
cut
...
Date next action)+':Transport Payment method;'+CONVERT(VARCHAR(100), Transport Payment method) FROM dealpending where dealid = 1483436
May 11, 2011 at 8:02 am
Ian Scarlett (5/11/2011)
In SQL2000 you could get over any limit for the size of the variable containing your SQL by declaring and executing the SQL from multiple concatenated variables. These weren't realised anywhere in memory, and therefore had no practical limit. I assume this is still valid for SQL2008 e.g.
DECLARE @sql1 nvarchar(4000)
DECLARE @sql2 nvarchar(4000)
DECLARE @sql3 nvarchar(4000)
SET @sql1 = 'a load of very long sql '
SET @sql2 = 'even more very long sql '
SET @sql1 = 'yet more very long sql '
EXEC(@sql1 + @sql2 + @sql3)
But query is created dynamically.
May 11, 2011 at 8:05 am
yep!
it worked
EXEC() has not got any limits.
try to have all the where condition in EXEC() then you will not have any prob with size.
thanks a lot!
May 11, 2011 at 8:08 am
If it works with exec it will work with execute_sql.
You still have mystery to solve here!
May 11, 2011 at 8:12 am
Ninja's_RGR'us (5/11/2011)
I tested this on my machine and it works (but I do get syntax error in the next query so I'd focus there if I were you) :
Did you mean that you got syntax error on this line?
EXECUTE sp_executesql @sql;
It's probably because you don't have dealpending table and dealid column. I'm not getting any syntax error.
And yes,
SELECT LEN(@sql), @sql
prints the correct query but when I run that query (either with EXECUTE sp_executesql @sql or copying it to a script editor and press F5), it returns null.
But I'm sure there is a record with dealid=1483436
when I clean
'timestamp;'+CONVERT(VARCHAR(100), timestamp)+':Document Type;'+CONVERT(VARCHAR(100),
and put *, it returns the correct record :S
SELECT * FROM dealpending where dealid = 1483436;
May 11, 2011 at 8:13 am
But query is created dynamically.
Is the dynamic part so long that it wouldn't fit in 4000 characters?
Couldn't you put the first part in one variable, the dynamic part in the second variable and the trailing bit in a third variable?
May 11, 2011 at 8:15 am
ilker.cikrikcili (5/11/2011)
Ninja's_RGR'us (5/11/2011)
I tested this on my machine and it works (but I do get syntax error in the next query so I'd focus there if I were you) :Did you mean that you got syntax error on this line?
EXECUTE sp_executesql @sql;It's probably because you don't have dealpending table and dealid column. I'm not getting any syntax error.
And yes,
SELECT LEN(@sql), @sqlprints the correct query but when I run that query (either with
EXECUTE sp_executesql @sqlor copying it to a script editor and press F5), it returns null.But I'm sure there is a record with dealid=1483436
when I clean
'timestamp;'+CONVERT(VARCHAR(100), timestamp)+':Document Type;'+CONVERT(VARCHAR(100),and put *, it returns the correct record :S
SELECT * FROM dealpending where dealid = 1483436;
I have a case sensitive server too. So that usually throw more errors than I'd want it to.
Keeo in mind that the select and print statement won't go over 8096?? characters. So if the query is longer you can't copy / paste it. But you should be able to execute it using the variable.
May 11, 2011 at 8:27 am
Ninja's_RGR'us (5/11/2011)
Keeo in mind that the select and print statement won't go over 8096?? characters. So if the query is longer you can't copy / paste it. But you should be able to execute it using the variable.
it's 8109 chars long but I could copy the whole script and run. no result 🙁
May 11, 2011 at 8:34 am
Ian Scarlett (5/11/2011)
But query is created dynamically.
Is the dynamic part so long that it wouldn't fit in 4000 characters?
Couldn't you put the first part in one variable, the dynamic part in the second variable and the trailing bit in a third variable?
first part:
'SELECT '''
second part: dynamic and longer than 4000
third part: ' FROM dealpending where dealid = 1483436'
So, concetanating doesnt help.
May 11, 2011 at 8:35 am
Then it means the query is wrong. Wrong table, wrong schema, wrong PK id.
I'd focus there because you have no other problem at this point.
May 11, 2011 at 9:00 am
Ninja's_RGR'us (5/11/2011)
Then it means the query is wrong. Wrong table, wrong schema, wrong PK id.I'd focus there because you have no other problem at this point.
ohhh, you are right. I found the reason!
SELECT 'dealid:'+CONVERT(VARCHAR(100), dealid) + ';additionalchargenumber:'+CONVERT(VARCHAR(100), additionalchargenumber) FROM dealpending where dealid = 1483436;
additionalchargenumber is NULL for dealid = 1483436
I was expecting:
dealid:1483436;additionalchargenumber:NULL
but I got
NULL
I think CONVERT function gives error and the whole output becomes null.
It will be out of this topic but how can I achieve dealid:1483436;additionalchargenumber:NULL result?
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply