execute dynamic sql more than 8000 characters

SQL Injection Attacks where malicious code is inserted into the command that is By: Greg Robidoux | Updated: 2021-07-06 | Comments (63) | Related: 1 | 2 | 3 | 4 | More > Dynamic SQL. HQIntegration. SQL Server offers a few ways of running a dynamically built SQL statement. When using sp_exectesql, this could be a little more secure since you are passing in parameter values instead of stringing the entire dynamic SQL statement together. :) Make all '@scriptN' nvarchar(max) and concatenate them in on '@SQLStrin'g and try to execute this like shown below. Learn more about Stack Overflow the company, and our products. [All]', set @Stores='[Shop]. But to use this way, the datatype and number of variable that to be used at a run time need to be known before. SET @Amount = 1000 Dynamic SQL is a feature that helps minimize hard-coded SQL. It lets you build the general-purpose query on the fly using variables, based on the requirements of the application. Thanks a lot. Data Model and a Brief Introduction PRINT is limited to 8000 characters, the actual variable may contain more characters. did you try to just add your INSERT into your dynamic query. Step 1 : Let me create a table to demonstrate the solution. When it is a variable, it is only 8000 characters; for executing a query that is longer than 4000 ANSI characters is therefore impossible to do from a variable, such as EXEC (@SQL). Executing Dynamic SQL larger than 8000 characters. Transact-SQL syntax conventions Syntax syntaxsql If there are insufficient CRs in the text, it will print it out in Given below is the script. set @ParmDefinition = N'@ccId int, @StartDate_str DATE'; EXEC sp_executesql @SQLString, @ParmDefinition, @ccId = @clientId, @StartDate_str = @startdate; else-- filter the query search by only client company identifier. Also, one of the main benefits to using sp_executesql over EXEC is that sql injection will be blocked for the parameters. When it is a variable, it is only 8000 characters; for executing a query that is longer than 4000 ANSI characters is therefore impossible to do from a variable, such as EXEC(@SQL). How can I enter values to varchar(max) columns, dynamic sql passing parameter of length > 8000, Pad a string with leading zeros so it's 3 characters long in SQL Server 2008, Handling more than 8000 chars in stored proc parameter, why varchar(max) is not storing data more than 8000 charaters, SQL Server is not printing more than 8000 length of data. Here are a few options: We will use the check out this Transact-SQL tutorial. Try editing your original question and add details. Could have turn into days if I havent found your Blog, What would be difference between the 2 query, declare @script nvarchar(1000), @companyid int, @area tinyintselect comapnyid = 1 , @area = 1, select @script = 'select contactname , address, etc'+ + 'from tbljcontactstable' + convert(varchar(4) , @companyid) + 'WHERE contact_area = ' +convert(varchar(4) , @area), declare @script nvarchar(1000), @companyid int, @area tinyint, SELECT @script = ''SELECT @script = @script + 'select contactname , address, etc'select @script = @script + 'from tbljcontactstable' select @script = @script + 'WHERE contact_area = 'SELECT @script = REPLACE(@script, '' , @companyid)SELECT @script = REPLACE(@script, '', @area)exec(@script). false, totally 110% false. However, I am usually executing multiple "commands", not 1 single command greater than 8000 chars. In today's article, we'll show how to create and execute dynamic SQL statements. You can reverse engineer the stored procedure generated by sp_CRUDGen to get some dynamic SQL best practices. How to execute a long dynamic query (greater than 4000) characters - again. How do/should administrators estimate the cost of producing an online introductory mathematics class? Before you go down this route, I Is it possible to rotate a window 90 degrees if it has the same length and width? Click to share on Twitter (Opens in new window), Click to share on Facebook (Opens in new window), Click to share on LinkedIn (Opens in new window), Click to share on Tumblr (Opens in new window), Click to share on Reddit (Opens in new window), SQL SERVER Fix Error :4127 At least one of the arguments to COALESCE must be a typedNULL, SQL SERVER - How to store more than 8000 characters in a column, SQL SERVER How to identify delayed durabilty is disabled using Policy BasedManagement, SQL Server 2022 Improved backup metadata last_valid_restore_time, SQL Server 2022 TSQL QAT_DEFLATE Default Database Backup CompressionAlgorithm, SQL Server 2022 How to Install Intel Quick AssistTechnology, SQL Server 2022 TSQL MS_XPRESS Default Database Backup CompressionAlgorithm, Data Definition Language (DDL) Statements. but my code below doeas not accept the parameter. Asking for help, clarification, or responding to other answers. Thanks for the tip. Site design / logo 2023 Stack Exchange Inc; user contributions licensed under CC BY-SA. And this will really exceed 8000 characters? In DBMS_SQL.PARSE you can use VARCHAR2A or VARCHAR2S to process Large SQL. En el Proc B esta este bloque de instrucciones. [Stores2 Sales Value Net inc VAT - Base],[Measures]. There shouldn't be a problem executing sql statement larger than 8000 via exec(). [All], ' + @ArticleFilter + '), AS ([Measures]. Everywhere it tell me to store the result into a temp table and then query the temp table to store the value into a variable. Ooopps It only inserted 8000 characters even though I passed 10,000. Display More Than 8000 Characters (SQL Spackle) Jeff Moden, 2013-06-28 (first published: 2011-01-27) SQL Spackle" is a collection of short articles written based on multiple requests for similar . solution simple and efficient You did not mention using :SETVAR in scripts running in SQLCMD mode. SQL Server Agent; Management Studio; Backup; Restore; Availability Groups; Webinars; All Categories; T-SQL. I have a Dynamic select, I want to choose dynamically the columns of table 2 who have names as a month but I dont want to use the complete name when I call them with SSRS, my question ishow to save the results of this Dynamic Select in Table 2?I can not do it can someone help me. By clicking Post Your Answer, you agree to our terms of service, privacy policy and cookie policy. How would "dark matter", subject only to gravity, behave? [' + @Grouping + '] * [Articles].[Season]. I have one procedure that accepts one parameter 'BP_Code' (Customer Code) &generates an output (statement) as a text file for that 'BP_Code'. The way to solve this is to make multiple variables or multiple rows in a table that you can iterate through. stored procedure? I haven't seen that error before. n can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. varchar(max) also should work just fine - could you please try something like the following? Site design / logo 2023 Stack Exchange Inc; user contributions licensed under CC BY-SA. code at runtime. I actually wrote a function to go through a string column list like your example, and apply quotes [] to the names to block sql injection. Not sure why it is not working for me if it works for you what is the data type fo the variables that you are using? I have a stored procedure using dynamic SQL to execute some commands at runtime, and use INSERT INTO statement to temporarily keep the output of parameterized executesql in a temporary table. Answer. I thought of storing this query in a separate file, but as it uses joins on table variables and other procedure-specific parameters, I doubt if this is possible. is there anyway to put the procedure in a loop ? Could you please give me a sample to create that SP? 8000 characters. [Shop by Model].[Brand].&[7FAM].&[Retail].&[0DC],[Shop]. How can I get column names from a table in SQL Server? Look into using dynamic SQL in your stored procedures by employing one of [Stores2 Sales Quantity], [Articles]. Abhijit Jana. Why is there a voltage on my HDMI and coaxial cables? [Stores2 Sales Value Net exc VAT - Base]), AS Sum(TopSellers, [Measures]. Esto puede ser a+2(b)+c. Could please tell me how to execute these commands in sql server. What is the purpose of non-series Shimano components? {[Store Transaction Motive]. [Store Transaction Motive].&[U+]. [Store Transaction Motive].&[U-]}, [Store Transaction Suspended]. Does ZnSO4 + H2 at high pressure reverses to Zn + H2SO4? If you create the Temp Table first and then select data into it using EXEC you can then use SELECT to read the data. We can turn the above SQL query into a stored procedure with the following An attacker could exploit this vulnerability by inserting malicious data into a specific data field in an affected interface. [Stores2 Sales Value Net inc VAT - Base],[Measures]. To learn more about SQL Server stored proc development (parameter values, output parameters, code reuse, etc.) [Country Group].CURRENTMEMBER,[Articles]. Is it suspicious or odd to stand by the gate of a GA airport watching the planes? [Shop by Model].[Brand].&[7FAM].&[Outlet].&[0D4],[Shop]. Why do we calculate the second half of frequencies in DFT? Managing SQL Server string with more than 8000 characters First of all, this error appears if you tried to declare an argument of type TEXT in a stored procedure as follows: CREATE PROCEDURE MY_PROCEDURE @Variable_Text TEXT AS BEGIN DECLARE @VARIABLE_TEXT TEXT -- The problem is in this line the function in this case lacks a simple length check and as a result an attacker who is able to send more than 184 characters can easily overflow the values stored on the . [Stores2 Sales Value Net inc VAT - Base],[Measures]. [Shop by Model].[Brand].&[7FAM].&[Retail].&[0D3],[Shop]. Do new devs get fired if they can't solve a certain bug? Always remember that anything called by EXEC statement is executed in a separated session. [' + @Grouping + ']. You can further optimize the performance of your recommendation system by fine-tuning its parameters, or by switching to more dynamic algorithms. Step 1 In SQL Server Management Studio, under the Tools menu, click Options as shown in the image below: Step 2 In the Options dialog box, expand Query Results, expand SQL Server and then select General as shown in the image below. Not the answer you're looking for? Dynamic SQL is the SQL statement that is constructed and executed at runtime based on input parameters passed. I can't believe this is sooo hard to figure out. Try using use nvarchar (max) - Simon Aug 23 '17 at 16:59. [Transactiontype].&[D]), MEMBER [Measures]. How Intuit democratizes AI development across teams through reusability. In dynamic Sql, , I reach the varchar limit is 8000 characters. By clicking Accept all cookies, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy. That's an average of at most 200 characters per line - but remember, spaces still count! [Stores2 Sales Quantity],[Time]. [' + @Grouping + ']),[Measures]. [Stores2 Sales Cost - Base],[Articles]. Dynamic SQL is the SQL statement that is constructed and executed at runtime based on input parameters passed. [Stores2 Sales Value Net exc VAT - Base]), MEMBER [Measures]. 1 2 3 4 5 6 Poorly Performing Dynamic SQL Used in SP_EXECUTESQL. [' + @Grouping + '].CURRENTMEMBER, [Articles]. [Stores2 Sales Quantity],[Articles]. [Shop by Model].[Brand].&[7FAM].&[Retail].&[0BF],[Shop]. si estamos de acuerdo. One issue is the potential for Parameterized queries (especially if they've been made into stored procedures) are the safest and best way to go. I needed to modify some contents of the temporary table and limit the content at some point. If you are on SQL Server 2008 or newer you can use VARCHAR(MAX), Problem is because your string has limit 8000 symbols by default. Dynamic SQL. When concatenating long strings (or strings that you feel could be long) always pre-concatenate your string building with CAST('' as nVarChar(MAX)) like so: What a pain and scary to think this is just how SQL Server works. ", set @Stores='[Shop]. The goal is to provide an alternative that will return the same results as your current query. [CountryStocks]} ON COLUMNS, FROM(SELECT {strtoset("{' + @Stores + '}")}ON COLUMNS FROM VFE), WHERE(' + @Currency + ',' + @ArticleFilter + ',' + @FiscalTime + ',[TransactionStatus].[Transactionstatus].&[0],[TransactionType]. [Shop by Model].[Brand].&[7FAM].&[Outlet].&[0ZW],[Shop]. [Stores2 Sales Value Net inc VAT - Base],[Measures]. - the incident has nothing to do with me; can I use this this way? Find centralized, trusted content and collaborate around the technologies you use most. This could potentially open [CountryDelivered] AS ([Measures]. Since my block of code was well over the 4k/Max limit, I break it out into little chunks like this: So each set @Statement can have the varchar(max) as long as each chunk itself is within the size limit (i cut out the actual code in my example, for space saving reasons). and see a solution for it. SELECT {[Measures].[GroupingParam],[Measures].[Season],[Measures].[Value],[Measures].[COGS],[Measures].[Units],[Measures].[Delivered],[Measures].[CountryRank],[Measures].[CountryValue],[Measures].[CountryCOGS],[Measures].[CountryUnits]. dbo.PERSON and same field names, e.g. Find centralized, trusted content and collaborate around the technologies you use most. Did you try? Can anybody please help me if there is any easier way to directly put the result into a variable, just like how mysql lets you with keyword into @variable in its dynamic query. Does ZnSO4 + H2 at high pressure reverses to Zn + H2SO4? Example: . And when execute it using: EXEC (@script1 + @script2 + @script3 + .) I received an inquiry from one of my blog readers Mr. So I suggested him to use VARCHAR(MAX). [SplitDelimiterString] (@StringWithDelimiter VARCHAR (max), @Delimiter VARCHAR (max)) RETURNS @ItemTable TABLE (Item VARCHAR (max)) AS BEGIN DECLARE @StartingPosition INT; DECLARE @ItemInString . nvarchar(max), when it is a column, will hold 2GB in each row. Because It's not the problem. [Stores2 History Inventory Physical Quantity], [Articles]. Do new devs get fired if they can't solve a certain bug? [Shop Model].&[Outlet]} ON COLUMNS, FROM (SELECT {strtoset("{' + @Stores + '}")} ON COLUMNS. [Fiscal Hierarchy].[All],[TransactionType]. [Shop by Model]. Given below is the script. @changeType varchar(50), @clientId_fromApp int, @startdate_fromApp date, @enddate_fromApp date, @requster varchar(50), @authoriser varchar(50), @startHolding numeric(18, 0), @endHolding numeric(18, 0), Create table #finalrecord ( holder_id int, [Account Number] int, [Shareholder Name] varchar(500), , [Previous Mandate] varchar(500), [New Mandate] varchar(500), , [Current Holdings] numeric(18, 0), [Affected Register] varchar(200), , [Requester] varchar(200), [Authoriser] varchar(200), , [Change Type] varchar(50), [Change Date] date), Declare @cols varchar(1000) = N'hc.holder_id, hc.h_comp_acct_id as [Account Number], , h.last_name + '' '' + h.first_name + '' '' + h.middle_name as [Shareholder''s Name], , isnull(hc.initial_form, ''N/A'') as [Previous Mandate], , isnull(hc.current_form, ''N/A'') as [New Mandate], , hca.total_share_units as [Current Holdings], , isnull(account_affected, '''') as [Affected Register], , ISNULL(change_initiator, ''N/A'') as [Requester], ISNULL(change_authoriser, ''N/A'') as [Authoriser]. And when you try to get the data from OLAP database using Linked server and OPENQUERY function the query in the nvarchar(max) variable is reduced to nvarchar(8000). If you know the shape of the resultset you can use INSERT INTOEXEC()AT. [TopSellersUnits])), AS Iif( "'+ @vat +'"= "incVAT",[Measures]. You don't really know how a user may use the code and therefore Does ZnSO4 + H2 at high pressure reverses to Zn + H2SO4? C++. How to DROP multiple columns with a single ALTER TABLE statement in SQL Server? This is slow and less secure than the other methods described above. Connect and share knowledge within a single location that is structured and easy to search. post the output of print cast((@script1 + @script2 + @script3) as ntext) in your question. decided it would be faster to write one myself than search the broader [Store Transaction Motive].&[U-]},[Store Transaction Suspended]. But perhaps I'm misremembering, and the formatting is preserved once you copy the text from the grid (or run it in text mode). Should you identify any content that is harmful, malicious, sensitive or unnecessary, please contact me via email (imran@raresql.com) so I may rectify the problem. Thanks a lot:) Thanks Lindsay DECLARE @sql1. Try this. [Store Transaction Motive].&[U+], [Store Transaction Motive]. I realized the PRINT statement has a limit of 8,000 characters before it truncates the string. e.g. With that, we have reached the end of this article. While the length of the . The best answers are voted up and rise to the top, Not the answer you're looking for? Has anyone found a better way to preserve formatting while printing a string more than 8,000 characters?perhaps through a custom function or procedure? In 2012 though, only the varchar(max) will work, therefore you'll have to change it before upgrading. Maximum length is 8000.) #1631102. I agree this is not the best method for writing codeand should only be used as a last resort and SQL injection should always be a concern regardless of what methods are used. Did you try to change sp_execute with sp_executesql? No we are not using BEGIN TRANSACTION / COMMIT TRANSACTION. nvarchar(max) holds one or two gb. [Shop by Model].[Brand].&[7FAM].&[Retail].&[0BJ],[Shop]. This makes a dynamic SQL more flexible as it is not hardcoded. Is there a single-word adjective for "having exceptionally strong moral principles"? To learn more, see our tips on writing great answers. For every expert, there is an equal and opposite expert. SP_EXECUTESQL can be slow if you assign a slow-running query to it. Furthermore, they are not inherently subjected to SQL injection, which can reek havoc on a database. Whenever I write dynamic SQL, I typically include a PRINT @DynamicSQL statement in a comment right above the EXEC sp_ExecuteSQL @DynamicSQL statement so that the dynamic SQL can be easily read and debugged when needed. From that post: This very simple procedure is designed to overcome the limitation in sql-server dynamic sql-server-2008-r2 exec. [Stores2 Sales Value Net inc VAT - Base],[Measures]. You had an extra ) in the code. That could easily be missed. It's kooky, it's not popular and Adobe has never figured out to market it. Read the complete thread in MSDN forum ! And when execute it using: I try using replicate and get same problem. Pero este me funciona en el SSMS y no funciona en el procedimiento interno que es llamado por otro procedimiento el cual devuelve dicho total. [TopSellersUnits]AS Sum(TopSellers,[Measures]. How does SSMS connect to a server's database without the instance name? You better use SELECT statement, then copy from select and paste into the new query window. I expect the real query looks quite different By "fake sample" I referred to obfuscated table, column, and parameter naemes but to keep the original structure of the query. PHP, Java I am trying to pass a string like 2151 characters in length, to the EXECUTE IMMEDIATE command. CREATE TABLE #temp (Pivot smalldatetime) --insert the class dates into the temp table. forward, because you also need to define the extra quotes in order to pass a character [Stores2 Sales Value Net exc VAT - Base]), [Articles]. Does MSSQL Server need more space than the size of the data itself for importing? Maximum values allowed for various components of dedicated SQL pool in Azure Synapse Analytics. Viewed 2k times 1 I have a SQL script with more than 8000 characters and I stored it in some VARCHAR (MAX). Let's say we want To see the dynamic SQL string, you can use 2 possible methods. By clicking Post Your Answer, you agree to our terms of service, privacy policy and cookie policy. [' + @Grouping + ']. In the right side panel choose Results To Text option from the Default destination for results drop down list. Even the while loop condition on shop parameter does not help us because we have to get Top N value for all the shops and in case of while loop it gives the Top N value of each shop. [All], ' + @ArticleFilter + '), AS ([Measures]. 6. xp_readmail for email longer than 8000 characters. Site design / logo 2023 Stack Exchange Inc; user contributions licensed under CC BY-SA. How do I UPDATE from a SELECT in SQL Server? You can't create a NVARCHAR (8000). Just different ways of executing a dynamic statement. There shouldn't be a problem executing sql statement larger than 8000 via exec (). debug a script that generated a very long dynamic SQL section. I mean to say, the query which you given for 8000+ width gives error on Both version of 2005/2008. take a look at this tip about how to create tables to see if this helps: http://www.mssqltips.com/sqlservertip/1050/simple-way-to-create-tables-in-sql-server-using-excel/, how to write a sql statement and i do not know to make table plz give me detail regarding this sql statement. Can some one help me on the same. 5. [Country Group].CURRENTMEMBER,[Articles]. INSERT INTO #temp SELECT DISTINCT CONVERT (smalldatetime, AttendanceDate, 103) AS Pivot FROM dbo.vw_ARS_StudentClassAttendance WHERE RegisterID = @RegisterID . Do you have a chance to either create a view or a sproc at the db referenced in OPENQUERY that would hold the content of @sqlquery? How would such a parameter string look like? iif("' + @Grouping + '"="Lot" or "' + @Grouping + '"="Style", ([Shop]. Not sure if this is exactly what you need to do or not. Ithink that Dynamic SQL is the solution, but we consider this one not enough "elegant" (and the Sql injection issue too), Hi Manish, How do I get your sql command as a output to the other stored procedure. For example execute following string. To run a dynamic SQL statement, run the stored procedure sp_executesql as shown below : Use prefix N with the sp_executesql to use dynamic SQL as a Unicode string. [Shop by Model].[Brand].&[VANS].&[Outlet].&[0SG],[Shop]. I know I can loop over my @DynamicSQL variable the number of times 8,000 divides into it's length and print each 8,000 chunk per iteration, but then you lose the formatting where a statement in @DynamicSQL is across two chunks, which kind of defeats my purpose. 2- (This is what I did at first) Check THIS post: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=52274 and do what user "Kristen" says. A priori I don't know what kind of comparission will be submited (for example, amount = 1000 in a execution and amount > 250 in another). rev2023.3.3.43278. Stored Procedure Tutorial; SQL Server Join Example; CROSS APPLY + OUTER APPLY; Cursor in SQL Server; Rolling up multiple rows; Execute Dynamic SQL; Date and Time Conversions; Format SQL Server Dates; Calendar Table; Add and Subtract Dates . Extending this suggestion - you can also execute a string at the remote end with EXECUTE AT: EXEC('TRUNCATE TABLE mydb.dbo.' the three techniques above instead having the code generated from your front-end application. Also, I agree the first example isn't truly dynamic SQL, but it shows how to create a query that can be changed using parameters versus hardcoding items. Step 4 : Thanks for contributing an answer to Stack Overflow! -Jamie Tag: Executing Dynamic SQL larger than 8000 characters; 5 I have been having the same problem, with the strings being truncated. Tengo una aplicacion con unas formulas generadas por el usuario. There are a number of possible issues here, the most likely is that you are using other variables in the construction of the string, and they are not all nvarchar(max). Kindly tell me a method to store a large query into a variable and execute it multiple times in a procedure. How can we prove that the supernatural or paranormal doesn't exist? I try using replicate and get same problem. [Shop by Model].[Brand].&[7FAM].&[Outlet].&[0D6],[Shop]. Un ejemplo de la formula es : a.arpAncho-(2*L.apzCalibre)-1, donde cadacampo , Ancho y Calibre son Medidas de una Pieza de madera rectangular, es una medida que se encuentra en una tabla. Thanks for contributing an answer to Database Administrators Stack Exchange! This can be done quite simply from the application perspective Making statements based on opinion; back them up with references or personal experience. (GO required before a second :CONNECT). Set @test2 = @MonthSelect @test2 = (Case @test2When 1 then 'December'When 2 then 'January'When 3 then 'February'When 4 then 'March'When 5 then 'April'When 6 then 'May'When 7 then 'June'When 8 then 'July'When 9 then 'August'When 10 then 'September'When 11 then 'October'When 12 then 'November'elseNULL end )Declare @test1 Nvarchar(255) = @Test2+'_AvgNetP'Declare @test3 Nvarchar(255) = @Test2+'_AvgROS'Declare @Select nvarchar(1000), Set @Select = 'Select Hdl_Nr,' [emailprotected]+','[emailprotected]+' from [Table1] as TUpdate Table2set Table2.ROS_S = (Select @test1 from @Select)where Table2.Hdl_Nr = T.Hdl_Nr) '. Given below is the script. Not the answer you're looking for? Variable-length Unicode character data. into your WHERE clause of your SQL statement in Microsoft SQL Server. En el SSMS funciona. ALTER FUNCTION [dbo]. if the @sqlquery has more than 8000 character, how to overcome it? Maybe your script does not affect any rows. Thanks for all the help. @Francisco - try something like this. Quiero obtener el total de esa operacion mediante elprocedimientosp_executesql. [Value] AS Iif("'+ @vat +'"= "incVAT",[Measures]. It uses the 'EXECUTE IMMEDIATE' command to create and execute the SQL at run-time. In SQL 2008 ntext is still supported, and if you do the varchar(max) thingy there, it will work. Good question/answer about nvarchat/varchar, To explicitly say to system that this is nvarchar put N before single quoted expression. Why don't you create a Stored Procedure for that query? Styling contours by colour and by line thickness in QGIS. , @ccId = @clientId, @StartDate_str = @startdate, @EndDate_str = @enddate; Print 'THE START DATE ENTERED BY THE USER WHILE SEARCHING WITH DATE RANGE, IS EITHER NULL OR EMPTY , PLEASE CONTACT SYSTEM ADMINISTRATOR!!! Tag: Executing Dynamic SQL larger than 8000 characters; 4. [Country Group].CURRENTMEMBER.MEMBER_CAPTION,[Shop]. Fantastic Greg, congratulations. I only presented the INSERT INTOEXEC() AT technique because you seemed open to parking a VIEW on the remote instanceimplying you would always know the table structure , Viewing 15 posts - 1 through 15 (of 15 total), You must be logged in to reply to this topic. @StackNewUser: that will not help, since, @StackNewUser: Thanks you. The problem is, the same procedure is returning no data when it's called from a Java application. Let me explain the solution step by step. [' + @Grouping + ']. [Country Group].CURRENTMEMBER*iif("' + @Grouping + '"="Lot" or "' + @Grouping + '"="Style",[Articles]. Each DB has the same set of table names, e.g. SQL Server Usage. (LogOut/ Dynamic SQL is a programming technique where you build SQL query as a string and execute it dynamically at runtime. To represent a dynamic SQL statement, a character string must contain the text of a valid DML or DDL SQL statement, but not contain the EXEC SQL clause, host-language delimiter or statement terminator..