1

Topic: Nvarchar (max)

Hello!
There is an operating SQL query. It is necessary to make dynamic to change the periods of MDX-inquiry in a SQL query.
[spoiler a SQL query]

USE [SM]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo]. [StandardMatrixNoveltiesONESFO3] (@DT date) AS
DECLARE @MDXQUERY nvarchar (4000);
>>> DECLARE @SQLQUERY nvarchar (max); <<<
SET @MDXQUERY =
N ' "SELECT
{[Measures]. [Shipments ]} ON 0;
... FROM PROFIT) ' ";
SET @SQLQUERY = N '...
FROM OPENROWSET (
"MSOLAP";
"Provider=MSOLAP.3;...";
' + @MDXQUERY +'
...'
EXEC (@SQLQUERY);

[/spoiler] about 4000 characters are all the same cut off. How it is possible to transfer in EXEC a variable, containing text in length over 4000 characters, and not to use such variant EXEC (@SQLQUERY1 + @SQLQUERY2 + @SQLQUERY3) , which c the partitioned texts of request?

2

Re: Nvarchar (max)

ferzmikk, if so?

SET @SQLQUERY = N '...
FROM OPENROWSET (
"MSOLAP";
"Provider=MSOLAP.3;...";
' + cast (@MDXQUERY as Varchar (MAX)) +'
...'

3

Re: Nvarchar (max)

I write so

SET @SQLQUERY = CAST (N "AS VARCHAR (MAX)) + N '...'

Does not help

4

Re: Nvarchar (max)

Kopelly wrote:

ferzmikk, if so?

SET @SQLQUERY = N '...
FROM OPENROWSET (
"MSOLAP";
"Provider=MSOLAP.3;...";
' + cast (@MDXQUERY as Varchar (MAX)) +'
...'

does not help. Here not MDX-inquiry long, and SQL.

5

Re: Nvarchar (max)

ferzmikk;
Run error what?

6

Re: Nvarchar (max)

TaPaK wrote:

a run error what?

Cuts off. In the code the end such commands

...
PRINT @MDXQUERY
PRINT @SQLQUERY
PRINT @DT
EXEC (@SQLQUERY);
PRINT ' It is completed'

7

Re: Nvarchar (max)

ferzmikk;
To begin with

PRINT CAST (@SQLQUERY as TEXT)

8

Re: Nvarchar (max)

TaPaK wrote:

ferzmikk;
To begin with

PRINT CAST (@SQLQUERY as TEXT)

cut off Less

9

Re: Nvarchar (max)

ferzmikk;
Well and look that at you in 42 line. Stares in print not mandatory

10

Re: Nvarchar (max)

So it can print cuts off, look through select.

11

Re: Nvarchar (max)

boltnik wrote:

So it can print cuts off, look through select.

jokes for 300?

12

Re: Nvarchar (max)

ferzmikk;
So gives entirely if very much it would be desirable to look all, well or output parameters change in adjustment ssms

SELECT CAST (' <root> <! [CDATA [' + @SQLQUERY + ']]> </root> ' AS XML)

13

Re: Nvarchar (max)

TaPaK wrote:

jokes for 300?

By no means.
https://docs.microsoft.com/ru-ru/sql/t- … erver-2017

wrote:

A message string can be up to 8,000 characters long if it is a non-Unicode string, and 4,000 characters long if it is a Unicode string. Longer strings are truncated. The varchar (max) and nvarchar (max) data types are truncated to data types that are no larger than varchar (8000) and nvarchar (4000).

14

Re: Nvarchar (max)

boltnik;
Overestimated... A joke for 150 "look through select."

15

Re: Nvarchar (max)

TaPaK wrote:

well or output parameters change in adjustment ssms

In which branch? [quote =]

SELECT CAST (' <root> <! [CDATA [' + @SQLQUERY + ']]> </root> ' AS XML)

Also cuts off as Text