sp_execute does not support dynamic SQL query with more than 4000 characters
I am using sp_execute statement to execute a dynamic SQL query.
But sp_execute does not support dynamic SQL query with more than 4000 characters.
It is working fine with the EXEC statement, though.
Please suggest if you have any solution.
This is my code:
declare @a nvarchar(max), @b nvarchar(max), @c nvarchar(max), @d nvarchar(max)
select @d = 'a'
select @a = 'select top 1 name,''', @b = replicate(@d, 10000),
@c=''' from sysobjects'
SELECT LEN(@a + @b + @c)
DECLARE @s NVARCHAR(max) = @a + @b + @c
EXEC sp_execute @s
EXEC(@s)
sql
sql-server database edited Nov 21 '18 at 5:17
marc_s
581k13011221268
asked Nov 21 '18 at 4:23
Nayan RudaniNayan Rudani
586
add a comment |
I am using sp_execute statement to execute a dynamic SQL query.
But sp_execute does not support dynamic SQL query with more than 4000 characters.
It is working fine with the EXEC statement, though.
Please suggest if you have any solution.
This is my code:
declare @a nvarchar(max), @b nvarchar(max), @c nvarchar(max), @d nvarchar(max)
select @d = 'a'
select @a = 'select top 1 name,''', @b = replicate(@d, 10000),
@c=''' from sysobjects'
SELECT LEN(@a + @b + @c)
DECLARE @s NVARCHAR(max) = @a + @b + @c
EXEC sp_execute @s
EXEC(@s)
sql
sql-server database edited Nov 21 '18 at 5:17
marc_s
581k13011221268
asked Nov 21 '18 at 4:23
Nayan RudaniNayan Rudani
586
1
You need sp_executesql.
– TT.
Nov 21 '18 at 4:30
add a comment |
I am using sp_execute statement to execute a dynamic SQL query.
But sp_execute does not support dynamic SQL query with more than 4000 characters.
It is working fine with the EXEC statement, though.
Please suggest if you have any solution.
This is my code:
declare @a nvarchar(max), @b nvarchar(max), @c nvarchar(max), @d nvarchar(max)
select @d = 'a'
select @a = 'select top 1 name,''', @b = replicate(@d, 10000),
@c=''' from sysobjects'
SELECT LEN(@a + @b + @c)
DECLARE @s NVARCHAR(max) = @a + @b + @c
EXEC sp_execute @s
EXEC(@s)
sql
sql-server database edited Nov 21 '18 at 5:17
marc_s
581k13011221268
asked Nov 21 '18 at 4:23
Nayan RudaniNayan Rudani
586
I am using sp_execute statement to execute a dynamic SQL query.
But sp_execute does not support dynamic SQL query with more than 4000 characters.
It is working fine with the EXEC statement, though.
Please suggest if you have any solution.
This is my code:
declare @a nvarchar(max), @b nvarchar(max), @c nvarchar(max), @d nvarchar(max)
select @d = 'a'
select @a = 'select top 1 name,''', @b = replicate(@d, 10000),
@c=''' from sysobjects'
SELECT LEN(@a + @b + @c)
DECLARE @s NVARCHAR(max) = @a + @b + @c
EXEC sp_execute @s
EXEC(@s)
sql
sql-server database sql
sql-server database edited Nov 21 '18 at 5:17
marc_s
581k13011221268
asked Nov 21 '18 at 4:23
Nayan RudaniNayan Rudani
586
edited Nov 21 '18 at 5:17
marc_s
581k13011221268
asked Nov 21 '18 at 4:23
Nayan RudaniNayan Rudani
586
edited Nov 21 '18 at 5:17
marc_s
581k13011221268
edited Nov 21 '18 at 5:17
marc_s
581k13011221268
edited Nov 21 '18 at 5:17
marc_s
581k13011221268
581k13011221268
asked Nov 21 '18 at 4:23
Nayan RudaniNayan Rudani
586
asked Nov 21 '18 at 4:23
Nayan RudaniNayan Rudani
586
asked Nov 21 '18 at 4:23
Nayan RudaniNayan Rudani
586
586
1
You need sp_executesql.
– TT.
Nov 21 '18 at 4:30
add a comment |
1
You need sp_executesql.
– TT.
Nov 21 '18 at 4:30
1
1
You need sp_executesql.
– TT.
Nov 21 '18 at 4:30
You need sp_executesql.
– TT.
Nov 21 '18 at 4:30
add a comment |
2 Answers
2
active
oldest
votes
You should use sp_executesql. It supports nvarchar(max). You may refer to here
But your issue is not so much with sp_execute but with the string @b. If you do notice, the following query gives len of 8036 and not 10036
SELECT len(@a+@b+@c)
The culprit is @b=replicate('a',10000) the length max at 8000. Replicate returns the same data type of a which is varchar
if you cast or convert() the string a to nvarchar(max), it will gives you 1000 characters
@b = replicate(convert(nvarchar(max), 'a'),10000)
or you may also try this
declare @d nvarchar(max)
select @d = 'a'
select @b = replicate(@d, 10000)
select len(@b)
answered Nov 21 '18 at 4:47
SquirrelSquirrel
11.9k22228
thanks for reply, I have updated my questions , but still it is not working with EXEC sp_execute @s
– Nayan Rudani
Nov 21 '18 at 5:09
Please go through my answer. It stated to usesp_executesqland notsp_execute
– Squirrel
Nov 21 '18 at 6:18
add a comment |
Use sp_executesql instead of sp_execute.
declare @a nvarchar(max), @b nvarchar(max), @c nvarchar(max), @d nvarchar(max)
select @d = 'a'
select @a = 'select top 1 name,''', @b = replicate(@d, 10000),
@c=''' from sysobjects'
SELECT LEN(@a + @b + @c)
DECLARE @s NVARCHAR(max) = @a + @b + @c
EXEC sp_executesql @s
EXEC(@s)
answered Nov 21 '18 at 6:02
JERRYJERRY
875413
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53405226%2fsp-execute-does-not-support-dynamic-sql-query-with-more-than-4000-characters%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You should use sp_executesql. It supports nvarchar(max). You may refer to here
But your issue is not so much with sp_execute but with the string @b. If you do notice, the following query gives len of 8036 and not 10036
SELECT len(@a+@b+@c)
The culprit is @b=replicate('a',10000) the length max at 8000. Replicate returns the same data type of a which is varchar
if you cast or convert() the string a to nvarchar(max), it will gives you 1000 characters
@b = replicate(convert(nvarchar(max), 'a'),10000)
or you may also try this
declare @d nvarchar(max)
select @d = 'a'
select @b = replicate(@d, 10000)
select len(@b)
answered Nov 21 '18 at 4:47
SquirrelSquirrel
11.9k22228
thanks for reply, I have updated my questions , but still it is not working with EXEC sp_execute @s
– Nayan Rudani
Nov 21 '18 at 5:09
Please go through my answer. It stated to usesp_executesqland notsp_execute
– Squirrel
Nov 21 '18 at 6:18
add a comment |
You should use sp_executesql. It supports nvarchar(max). You may refer to here
But your issue is not so much with sp_execute but with the string @b. If you do notice, the following query gives len of 8036 and not 10036
SELECT len(@a+@b+@c)
The culprit is @b=replicate('a',10000) the length max at 8000. Replicate returns the same data type of a which is varchar
if you cast or convert() the string a to nvarchar(max), it will gives you 1000 characters
@b = replicate(convert(nvarchar(max), 'a'),10000)
or you may also try this
declare @d nvarchar(max)
select @d = 'a'
select @b = replicate(@d, 10000)
select len(@b)
answered Nov 21 '18 at 4:47
SquirrelSquirrel
11.9k22228
thanks for reply, I have updated my questions , but still it is not working with EXEC sp_execute @s
– Nayan Rudani
Nov 21 '18 at 5:09
Please go through my answer. It stated to usesp_executesqland notsp_execute
– Squirrel
Nov 21 '18 at 6:18
add a comment |
You should use sp_executesql. It supports nvarchar(max). You may refer to here
But your issue is not so much with sp_execute but with the string @b. If you do notice, the following query gives len of 8036 and not 10036
SELECT len(@a+@b+@c)
The culprit is @b=replicate('a',10000) the length max at 8000. Replicate returns the same data type of a which is varchar
if you cast or convert() the string a to nvarchar(max), it will gives you 1000 characters
@b = replicate(convert(nvarchar(max), 'a'),10000)
or you may also try this
declare @d nvarchar(max)
select @d = 'a'
select @b = replicate(@d, 10000)
select len(@b)
answered Nov 21 '18 at 4:47
SquirrelSquirrel
11.9k22228
You should use sp_executesql. It supports nvarchar(max). You may refer to here
But your issue is not so much with sp_execute but with the string @b. If you do notice, the following query gives len of 8036 and not 10036
SELECT len(@a+@b+@c)
The culprit is @b=replicate('a',10000) the length max at 8000. Replicate returns the same data type of a which is varchar
if you cast or convert() the string a to nvarchar(max), it will gives you 1000 characters
@b = replicate(convert(nvarchar(max), 'a'),10000)
or you may also try this
declare @d nvarchar(max)
select @d = 'a'
select @b = replicate(@d, 10000)
select len(@b)
answered Nov 21 '18 at 4:47
SquirrelSquirrel
11.9k22228
answered Nov 21 '18 at 4:47
SquirrelSquirrel
11.9k22228
answered Nov 21 '18 at 4:47
SquirrelSquirrel
11.9k22228
answered Nov 21 '18 at 4:47
SquirrelSquirrel
11.9k22228
11.9k22228
thanks for reply, I have updated my questions , but still it is not working with EXEC sp_execute @s
– Nayan Rudani
Nov 21 '18 at 5:09
Please go through my answer. It stated to usesp_executesqland notsp_execute
– Squirrel
Nov 21 '18 at 6:18
add a comment |
thanks for reply, I have updated my questions , but still it is not working with EXEC sp_execute @s
– Nayan Rudani
Nov 21 '18 at 5:09
Please go through my answer. It stated to usesp_executesqland notsp_execute
– Squirrel
Nov 21 '18 at 6:18
thanks for reply, I have updated my questions , but still it is not working with EXEC sp_execute @s
– Nayan Rudani
Nov 21 '18 at 5:09
thanks for reply, I have updated my questions , but still it is not working with EXEC sp_execute @s
– Nayan Rudani
Nov 21 '18 at 5:09
Please go through my answer. It stated to use
sp_executesql and not sp_execute– Squirrel
Nov 21 '18 at 6:18
Please go through my answer. It stated to use
sp_executesql and not sp_execute– Squirrel
Nov 21 '18 at 6:18
add a comment |
Use sp_executesql instead of sp_execute.
declare @a nvarchar(max), @b nvarchar(max), @c nvarchar(max), @d nvarchar(max)
select @d = 'a'
select @a = 'select top 1 name,''', @b = replicate(@d, 10000),
@c=''' from sysobjects'
SELECT LEN(@a + @b + @c)
DECLARE @s NVARCHAR(max) = @a + @b + @c
EXEC sp_executesql @s
EXEC(@s)
answered Nov 21 '18 at 6:02
JERRYJERRY
875413
add a comment |
Use sp_executesql instead of sp_execute.
declare @a nvarchar(max), @b nvarchar(max), @c nvarchar(max), @d nvarchar(max)
select @d = 'a'
select @a = 'select top 1 name,''', @b = replicate(@d, 10000),
@c=''' from sysobjects'
SELECT LEN(@a + @b + @c)
DECLARE @s NVARCHAR(max) = @a + @b + @c
EXEC sp_executesql @s
EXEC(@s)
answered Nov 21 '18 at 6:02
JERRYJERRY
875413
add a comment |
Use sp_executesql instead of sp_execute.
declare @a nvarchar(max), @b nvarchar(max), @c nvarchar(max), @d nvarchar(max)
select @d = 'a'
select @a = 'select top 1 name,''', @b = replicate(@d, 10000),
@c=''' from sysobjects'
SELECT LEN(@a + @b + @c)
DECLARE @s NVARCHAR(max) = @a + @b + @c
EXEC sp_executesql @s
EXEC(@s)
answered Nov 21 '18 at 6:02
JERRYJERRY
875413
Use sp_executesql instead of sp_execute.
declare @a nvarchar(max), @b nvarchar(max), @c nvarchar(max), @d nvarchar(max)
select @d = 'a'
select @a = 'select top 1 name,''', @b = replicate(@d, 10000),
@c=''' from sysobjects'
SELECT LEN(@a + @b + @c)
DECLARE @s NVARCHAR(max) = @a + @b + @c
EXEC sp_executesql @s
EXEC(@s)
answered Nov 21 '18 at 6:02
JERRYJERRY
875413
answered Nov 21 '18 at 6:02
JERRYJERRY
875413
answered Nov 21 '18 at 6:02
JERRYJERRY
875413
answered Nov 21 '18 at 6:02
JERRYJERRY
875413
875413
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53405226%2fsp-execute-does-not-support-dynamic-sql-query-with-more-than-4000-characters%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
You need sp_executesql.
– TT.
Nov 21 '18 at 4:30