Then you have space available to you beyond 8000 characters. Transact-SQL syntax conventions Syntax syntaxsql if the @sqlquery has more than 8000 character, how to overcome it? [Stores2 Sales Value Net inc VAT - Base],[Measures]. If there are insufficient CRs in the text, it will print it out in Some names and products listed are the registered trademarks of their respective owners. Site design / logo 2023 Stack Exchange Inc; user contributions licensed under CC BY-SA. You can create more general purpose, flexible applications by using dynamic SQL because the full text of a SQL statement may be unknown at compilation. Find centralized, trusted content and collaborate around the technologies you use most. Kindly tell me a method to store a large query into a variable and execute it multiple times in a procedure. 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. DECLARE @SQLFull varchar (8000) --create a temporary table to hold the class dates for the register. msdn.microsoft.com/en-us/library/ms176089.aspx, stackoverflow.com/questions/7392161/t-sql-varcharmax-truncated, http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=52274, How Intuit democratizes AI development across teams through reusability. Updated 9-Sep-10 1:54am v2 . This was added in SQL 2008, and with SQL 2005 you will need to split this into DECLARE + SET. Step 1 : Let me create a table to demonstrate the solution. [Stores2 Sales Cost - Base],[Articles]. [Store Transaction Motive].&[U-]}, [Store Transaction Suspended]. How can a LEFT OUTER JOIN return more records than exist in the left table? [COGS] AS [Measures]. The Exec failsto work in caseif theSQL statement is lengthy (it obviously has a limitation of length), Protecting Yourself from SQL Injection in SQL Server - Part 1, Protecting Yourself from SQL Injection in SQL Server - Part 2, Using the CASE expression instead of dynamic SQL in SQL Server, Run a Dynamic Query against SQL Server without Dynamic SQL, Dynamic SQL execution on remote SQL Server using EXEC AT, Creating Dynamic T-SQL to Move a SQL Server Database, Validate the contents of large dynamic SQL strings in SQL Server, Date and Time Conversions Using SQL Server, Format SQL Server Dates with FORMAT Function, How to tell what SQL Server versions you are running, Rolling up multiple rows into a single row and column for SQL Server data, Resolving could not open a connection to SQL Server errors, SQL Server Loop through Table Rows without Cursor, Add and Subtract Dates using DATEADD in SQL Server, Concatenate SQL Server Columns into a String with CONCAT(), SQL Server Database Stuck in Restoring State, Using MERGE in SQL Server to insert, update and delete at the same time, SQL Server Row Count for all Tables in a Database, Ways to compare and find differences for SQL Server tables and data, http://www.mssqltips.com/sqlservertip/1050/simple-way-to-create-tables-in-sql-server-using-excel/. [Stores2 Sales Value Net inc VAT - Base],[Measures]. [' + @Grouping + '] * [Articles].[Season]. This works perfectly fine on the management studio. Connect and share knowledge within a single location that is structured and easy to search. 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 . 4. Don't forget to pre-set them to an empty string. This is the EASIEST way to invoke SQL injection which, if I didn't mention before, can reek havoc on a database. [Stores2 Sales Value Net exc VAT - Base]), [Articles]. output parameters, code reuse, etc.) [All], ' + @ArticleFilter + '), MEMBER [Measures]. si estamos de acuerdo. 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. If you understood my post you know by now that in SQL 2008 or newer is silly to do this. Dynamic SQL is a programming technique that could be used to write SQL queries during runtime. By clicking Post Your Answer, you agree to our terms of service, privacy policy and cookie policy. If your code does need to be dynamic (i.e. Furthermore, they are not inherently subjected to SQL injection, which can reek havoc on a database. Tengo una aplicacion con unas formulas generadas por el usuario. How Intuit democratizes AI development across teams through reusability. [' + @Grouping + ']. 2. using more than 8000 characters in a local variable. To subscribe to this RSS feed, copy and paste this URL into your RSS reader. 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). [' + @Grouping + '].CURRENTMEMBER,[Articles].[Season].CURRENTMEMBER),([Shop]. [Shop by Model].[Brand].&[7FAM].&[Outlet].&[0D2],[Shop]. I just discovered another benefit of using sp_executesql to execute the dynamic SQL. [Shop by Model].[Brand].&[7FAM].&[Retail].&[0BF],[Shop]. rev2023.3.3.43278. *** NOTA *** - Si desea incluir cdigo de SQL Server Management Studio (SSMS) en su publicacin, copie el cdigo de SSMS y pguelo en un editor de texto como NotePad antes de copiar el cdigo a continuacin para eliminar el Formateo SSMS. [Store Transaction Motive].&[U+], [Store Transaction Motive]. Conclusion : In SQL 2008 ntext is still supported, and if you do the varchar(max) thingy there, it will work. Why did Ukraine abstain from the UNHRC vote on China? Connect and share knowledge within a single location that is structured and easy to search. 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. 2020-10-08: not yet calculated: CVE-2020-3536 CISCO: cisco -- video_surveillance_8000_series_ip_cameras There is a fourth DB where all stored procedures are housed, e.g. Could you please give me a sample to create that SP? i.e., it can contain only 8000 characters in the openquery function. Making statements based on opinion; back them up with references or personal experience. the query is something like below, because we have to create one temp table on local server, and structure of temp table is undefinied. [CountryDelivered] AS ([Measures]. Making statements based on opinion; back them up with references or personal experience. Muchas gracias por su ayuda. [Country Group].CURRENTMEMBER,[Articles]. You can parse the data into ten variables of 8000 characters each (8000 x 10 = 80,000) or you can chop the variable into pieces and put it into a table say LongTable (Bigstring Varchar(8000)) insert 10 rows into this and use an Identity value so you can retrieve the data in the same order. EXECUTE (@SQLString) DECLARE @SQLString varchar (10000) How increase Nvarchar size in SQL? What video game is Charlie playing in Poker Face S01E07? Asking for help, clarification, or responding to other answers. [Stores2 Sales Value Net inc VAT - Base],[Measures]. Maximum length is 8000.) up other areas of concern such as. This makes a dynamic SQL more flexible as it is not hardcoded. I am actually trying to build a a string to create a table dynamically that has more than 80 coulmns and this makes the string exceed the 8000 char limit with the varchar data type. 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. document.getElementById( "ak_js_1" ).setAttribute( "value", ( new Date() ).getTime() ); Enter your email address to follow this blog and receive notifications of new posts by email. For fast, accurate and documented assistance in answering your questions, please read this article.Understanding and using APPLY, (I) and (II) Paul WhiteHidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden, NVARCHAR(MAX) supports a huge string 2^31 - 1 bytes(~1+gig nvarchars )however, many applications, specifically SQL Server Management Studio, will only display the first 8000 characters of the string no matter what the value is, so if the data is stored in a varchar(max)/nvarchar(max), it defaults to display only the first 256 characters, but if you change the setting pictured below to a largest value, it still will only display the first 8K chars(this is for performance reasons, so grids don't freeze up). CREATE INDEX part_of_name ON customer (name(10)); If names in the column usually differ in the first 10 characters, lookups performed using this index should not be much slower than using an index created from the entire name column. Login to reply, The "Numbers" or "Tally" Table: What it is and how it replaces a loop, Increase length of NVARCHAR(MAX) more than 8000 Character. I try using replicate and get same problem. But we can use your suggestion if the table stucture before insert data. Linear regulator thermal information missing in datasheet. [Stores2 Sales Cost - Base], MEMBER [Measures]. / elkin / Medellin colombia. Generally the length of a varchar(Max) data type consider it as a 8000 characters and above. Change), You are commenting using your Facebook account. Extending this suggestion - you can also execute a string at the remote end with EXECUTE AT: EXEC('TRUNCATE TABLE mydb.dbo.' Using indicator constraint with two variables, Linear Algebra - Linear transformation question. From that post: This very simple procedure is designed to overcome the limitation in Thanks for the tip. 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? By clicking Accept all cookies, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy. 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. If the length is more than 8000 characters. I'm not getting the results I expected and cant tell what the problem is. stored procedure? SQL injection vulnerability in ChronoScan version 1.5.4.3 and earlier allows an unauthenticated attacker to execute arbitrary SQL commands via the wcr_machineid cookie. EXEC @Result = sp_executesql @Formula Try this. I have not personally used this technique, but you could try LongPrint. To subscribe to this RSS feed, copy and paste this URL into your RSS reader. A successful exploit could allow the attacker to execute arbitrary script code in the context of the affected interface. My problem is my query (it's only one single query) that I want to feed into the @sql variable uses more than 25 table joins, some of them on temporary table variables, incorporates complex operations and it is hence much more than 8000 characters long. [Shop].CURRENTMEMBER.MEMBER_CAPTION), AS Iif([Measures].[Units]<=0,"",[Measures]. Variable-length Unicode character data. [Season], [Articles]. if the @sqlquery has more than 8000 character, how to overcome it? Tag: Executing Dynamic SQL larger than 8000 characters; 4. vegan) just to try it, does this inconvenience the caterers and staff? check out this Transact-SQL tutorial. Find centralized, trusted content and collaborate around the technologies you use most. SQL Server offers a few ways of running a dynamically built SQL statement. [' + @Grouping + ']. but either way you need to specify the extra single quotes in order for the query ou are not passing parameters via sp+executesql, so you'd be good to go, i think. What values are you passing in and what values to you want to see output? SELECT TOP 1 [EmployeeKey],[ParentEmployeeKey],[EmployeeNationalIDAlternateKey],[ParentEmployeeNationalIDAlternateKey], ,[SalesTerritoryKey],[FirstName],[LastName],[MiddleName],[NameStyle],[Title],[HireDate],[BirthDate],[LoginID], ,[EmailAddress],[Phone],[MaritalStatus],[EmergencyContactName],[EmergencyContactPhone],[SalariedFlag], ,[Gender],[PayFrequency],[BaseRate],[VacationHours],[SickLeaveHours],[CurrentFlag],[SalesPersonFlag], ,[DepartmentName],[StartDate],[EndDate],[Status], SET @sql1 = 'Select * INTO #temp1 from OPENQUERY(lmremote, '''+@Query+''')', *******************************************************************. I realized the PRINT statement has a limit of 8,000 characters before it truncates the string. En el Proc B esta este bloque de instrucciones. (LogOut/ Does ZnSO4 + H2 at high pressure reverses to Zn + H2SO4? In the right side panel choose Results To Text option from the Default destination for results drop down list. [Shop Model].&[Retail], [Shop]. To do so, you must create a global temporary table: I have4 textboxfirstname, middlename,lastname and city. Must declare the scalar variable "@Fomula". Could please tell me how to execute these commands in sql server. How to execute SQL Dynamic query over 8000 characters Hi Experts; I have a string that is > 8000 characters (not by choice). Recovering from a blunder I made while emailing a professor, If the length x of your string is below 4000 characters, a string will be transformed into. [CountryRank] AS Rank(iif("' + @Grouping + '"="Lot" or "' + @Grouping + '"="Style",([Shop]. I received an inquiry from one of my blog readers Mr. While the length of the . we are executing the same code shared with you. [Shop by Model].[Brand].&[7FAM].&[Retail].&[0D3],[Shop]. in our case, this sql query is located in the SP which we can't control the the table structure. Que puede ser (a.arpLargo-2*(BS.apzCalibre)-1. you have to use the new sys.sp_sqlexec stored proc that accepts a parameter of type text. No we are not using BEGIN TRANSACTION / COMMIT TRANSACTION. [Shop by Model].[Brand].&[7FAM].&[Retail].&[0KN],[Shop]. Query greater than 8000 length in EXEC () command. Feedback Submit and view feedback for You better use SELECT statement, then copy from select and paste into the new query window. 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 . [All]', set @Stores='[Shop]. It's not the problem. I have a table in ehich column having some dml commands. e.g. e.g. [Shop].members,strtoset("{'+ @Stores +'}")),[Measures]. [Fiscal Hierarchy].[All],[TransactionType]. This is slow and less secure than the other methods described above. So I suggested him to use VARCHAR(MAX). The storage size, in bytes, is two times the number of characters entered + 2 bytes. They work fine for EXEC (string). Answer. 6. xp_readmail for email longer than 8000 characters. Let's say we In oracle, we use a LONG data type that can handle this, but i am not sure if there is any other data type in t-sql that can do this. If the length y is between 4000 and 8000. Change), You are commenting using your Twitter account. Why did Ukraine abstain from the UNHRC vote on China? Fantastic Greg, congratulations. [Brand].&[VANS].&[Outlet].&[0SS]', set @FiscalTime=N'[Time]. Problem. But even if you use VARCHAR(MAX), you should be careful while working on more than 8000 characters. In dynamic Sql, , I reach the varchar limit is 8000 characters. So put all your data in @SQLString variable and execute like below: Thanks for contributing an answer to Stack Overflow! It can't be used to create dynamic procedures (any CREATE PROCEDURE would have a static definition based on the :SETVAR values in effect originally), but it can be used for some very powerful dynamic scripts.These variables can be used anywhere, in strings, as server, table, or database name, or even parts of names.The variable definition is active for the entire script, even across GO. The method you are trying will not work with MsSql currently. MsSql as of 2012 supports Ntext for example that allows you to go beyond 8000 characters in a variable. [Shop by Model].[Brand].&[7FAM].&[Outlet].&[0D1],[Shop]. There shouldn't be a problem executing sql statement larger than 8000 via exec(). [SQM]AS [Measures]. Why don't you try it and tell us. FROM (SELECT Last_Name, First_Name FROM HAMMOND.dbo.PERSON, SELECT Last_Name, First_Name FROM RIDGEMOUNT.dbo.PERSON, SELECT Last_Name, First_Name FROM ROCKVILLE.dbo.PERSON, I need to develop a "generic" statement that works in various databases. SQL Server DBMS. decided it would be faster to write one myself than search the broader , @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!!! Or use SELECT if the string is more than 8000 characters. 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). do you have other solution?. break up the substrings at the carriage returns and the printed [All],' + @ArticleFilter + '), MEMBER [Measures]. Step 5 : since the queries are all identical and merged using UNION therewith removing duplicates leading to a single SELECT. 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. {[Store Transaction Motive]. The way to solve this is to make multiple variables or multiple rows in a table that you can iterate through. 3. writing 1024 characters in a varchar-field with allows 8000 characters doesnt work. Regards! 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. [' + @Grouping + ']),[Measures]. [Fiscal Hierarchy].[All],[TransactionType]. or any other programming language. Ooopps It only inserted 8000 characters even though I passed 10,000. Do new devs get fired if they can't solve a certain bug? version will exactly reflect the string passed. Browse other questions tagged, Where developers & technologists share private knowledge with coworkers, Reach developers & technologists worldwide. I wished to use TEXT data type to store this query, but MSDN shows a warning message that Microsoft is planning to remove Text, NText and Image data types from their next versions. get the query to build correctly. In DBMS_SQL.PARSE you can use VARCHAR2A or VARCHAR2S to process Large SQL. 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. I have my SQL string exeeding more than 4000 characters . [Shop Model] AS Iif("'+ @DetailLevel +'"= "C",[Shop]. Could you please give me a sample for that? code is robust to check for any issues before executing the statement that is [Stores2 History Inventory Physical Quantity],[Articles]. Puede ser un error mio al colocar la instruccion. @Roberto - this isn't exactly true. For example execute following string. [Stores2 Sales Value Net exc VAT - Base]),' + @ArticleFilter + '),BDESC)), MEMBER [Measures]. Becasue I can't give you the my original query. use you original query to create a view on the remote server (of course, if you can do it): SELECT * FROM RemoteReport in your OPENQUERY statement. SET @ParmDefinition = N'@Valor_OUT Numeric(12,2) OUTPUT', La variable @ValorFrm='SET @Valor_OUT=983.14-2(15.5)+1' Es una interpretacion de unas variables convertidas a numero. Try using use nvarchar (max) - Simon Aug 23 '17 at 16:59. [Fiscal Hierarchy].&[2012031]', set @Currency=N'[Reporting Currency]. [' + @Grouping + '].CURRENTMEMBER.MEMBER_CAPTION, FROM (SELECT {[Shop]. It will print the text passed to it in substrings smaller than 8000 characters. I think this is helpful to new people to show there is an easy way to do this without having to build a long query string and then executing the assembled string. [Shop by Model].[Brand].&[7FAM].&[Retail].&[0DH],[Shop]. [Transactiontype].&[D]), MEMBER [Measures]. It is indeed good way to get data, but it has a restriction that we should know the table structure before we insert the data into the table. My query is 8621 chars long I broke the query into twoVARCHAR(8000) variables, one was 7900 and the other was 721. initally u r declared datatype for @city, then why u are using the samething at EXECUTE statement like. To see the dynamic SQL string, you can use 2 possible methods. Let me create a table to demonstrate the solution. Create multiple 8000 char strings, break your string into 8000 char blocks and run "EXEC (@sql1+@sql2+@sql3+.)". Thanks a lot. How do/should administrators estimate the cost of producing an online introductory mathematics class? set @ParmDefinition = N'@StartDate_str DATE, @EndDate_str DATE'; EXEC sp_executesql @SQLString, @ParmDefinition, @StartDate_str = @startdate, @EndDate_str = @enddate; else-- only the start date is sent from engine. Mil Gracias por tu ayuda y abrazos desde medellin, colombia. [Store Transaction Suspended].&[False], IF OBJECT_ID('tempdb.dbo.#MdxResult') IS NOT NULL. - Becker's Law My blog My TechNet articles I know somebody has run into this before. Relation between transaction data and transaction id. if the script generated is longer than 8000, VARCHAR is simply cannot handle it. Let's say we want [Measures].[CountryDelivered],[Measures].[SQM],[Measures]. Max Length of execute immediate Ray White, March 06, 2003 - 5:38 pm UTC . Share this answer Posted 9-Sep-10 1:53am. but my code below doeas not accept the parameter. How would "dark matter", subject only to gravity, behave? [' + @Grouping + '].CURRENTMEMBER.MEMBER_CAPTION. Dan Guzman, Data Platform MVP, http://www.dbdelta.com. For those who hit a 4000 character max, it was probably because you had Unicode so it was implicitly converted to nVarChar(4000). I don't know how, but the Execute statement is now working. Then you could just call the sproc or the view instead of using such a long statement. LAST_NAME, FIRST_NAME, POSTAL_CODE. I can use the following code for tiny little queries: The above method is very useful in order to maintain large amounts of code, especially when we need to make changes once and have them reflected everywhere. How to run a more than 8000 characters SQL statement from a variable? [Shop by Model].[Brand].&[7FAM].&[Outlet].&[0D2],[Shop]. [Shop by Model].[Brand].&[7FAM].&[Retail].&[07U],[Shop]. Abhijit Jana. As a stored procedure, they can take advantage of plan caching, which can result in faster execution times. How would "dark matter", subject only to gravity, behave? The nature of simulating nature: A Q&A with IBM Quantum researcher Dr. Jamie We've added a "Necessary cookies only" option to the cookie consent popup, How to get the current date without the time part. and see a solution for it. The goal is to provide an alternative that will return the same results as your current query. SP_EXECUTESQL can be slow if you assign a slow-running query to it. varchar(max) also should work just fine - could you please try something like the following? Is it possible to create a concave light? "After the incident", I started to be more careful not to trip over things. n can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. iif("' + @Grouping + '"="Lot" or "' + @Grouping + '"="Style", ([Shop]. No: First we can see that the LEN () of our variable is only 8000 - not 8001 - characters long! nvarchar(max), when it is a column, will hold 2GB in each row. How to print more than 8,000 characters at a time to the SSMS Message window, without compromising text formatting? debug a script that generated a very long dynamic SQL section. On 64-bit servers, the size of the string is limited to 2 GB, the maximum size of nvarchar(max). Que cuidados debo de tener en cuenta para que esto funcione correctamente a tan bajo nivel? You must Break those Strings up or SQL Server will Truncate each one BEFORE concatenating. By clicking Post Your Answer, you agree to our terms of service, privacy policy and cookie policy. However, I am usually executing multiple "commands", not 1 single command greater than 8000 chars. :( 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). [Country Group].CURRENTMEMBER, [Articles]. Is there a single-word adjective for "having exceptionally strong moral principles"? [TopSellersUnits])), AS Iif( "'+ @vat +'"= "incVAT",[Measures]. This is regarding the sp_executesql and the sql statement parameter, in processing a dynamic SQL on SQL Server 2000, in my stored procedure. [Stores2 Sales Value Net exc VAT - Base]),[Articles]. In addition to check out this Transact-SQL tutorial. Does ZnSO4 + H2 at high pressure reverses to Zn + H2SO4? [All], ' + @ArticleFilter + '), AS ([Measures]. did you try to just add your INSERT into your dynamic query. This can be done quite simply from the application perspective i want to count the number of records but while executing found some error.Please help, Set @TableName = 'TableName'Declare @Count intDeclare @SqlString Nvarchar(1000), Set @SqlString = 'Select @OutCount = Count(*) From ' [emailprotected] Exec sp_Execute @SqlString, N'@OutCount Int Output', @OutCount = @Count Output. 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. (LogOut/ I have my SQL string exeeding more than 4000 characters. It uses the 'EXECUTE IMMEDIATE' command to create and execute the SQL at run-time. With the Execute Statement you are building the SQL statement on the fly and can pretty much do whatever you need to in order to construct the statement. Es gratis registrarse y presentar tus propuestas laborales. 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.. your code checks for any potential problems before just executing the generated [Shop by Model].[Brand].&[7FAM].&[Retail].&[0DA],[Shop]. Let's say there are three DBs for each of our branch offices, namely HAMMOND, ROCKVILLE, and RIDGEMOUNT. Well I ran to this before (in SQL 2005) and I can tell you that you have two options: 1 - Use the sys.sp_sqlexec stored procedure that can take a param of type text (IMO this is the way to go).