Hi there, In one of my stored procedure i am creating and populating the global temp table and then using that table in join with other tables to get the results. I am gettin the desired results but query is ending with error. Below is my query and Error i am getting SELECT DISTINCT A.Prefix,A.FirstName,A.MiddleInitial,A.LastName,A.Suffix,A.Email,A.Address,A.City,A.State,A.Zip ,ER.EventDate,AI.* FROM tblAccount A WITH(NOLOCK) JOIN tblEvent ER WITH(NOLOCK) ON (A.AccountID = ER.AccountFK) join ##tblAccountInfo AI ON (cast(AI.AccountID as int) = ER.AccountFK) WHERE ER.EventID = @EventID And this is the error i am getting: Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'WITH'. Msg 105, Level 15, State 1, Line 1 Unclosed quotation mark before the character string '[Code 495]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE, MAXDOP 1)'. Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'WITH'. Msg 105, Level 15, State 1, Line 1 Unclosed quotation mark before the character string '[Code Code 496]) AS _MS_UPDSTATS_TBL OPTION (BYPASS OPTIMIZER_QUEUE, MAXDOP 1)'. And so on for all the columns in Temp table. This query works perfectly fine when i run outside the stored procedure by running each step manually. Please advice. Thanks!!
What happens if you drop the WITH (NOLOCK) hints? Not quite sure here, but the character strings from the error messages do not appear in the query statement that you posted. Do they appear in the full script of the sproc?
Thanks for quick response! 1. I tried to remove NOLOCK hint and that did not help. I am still getting the same error. 2.Yes ,character string are getting used in procedure. I am renaming the columns of ##tblAccountInfo just before the query. For that i am using the dynamic sql. Below is the code. declare @Key int declare @Name varchar(100) declare @SqlRename varchar(8000) declare C_Rename cursor for select FieldCustomFK ,FieldLabel from #tblTempFieldKey open C_Rename Fetch next from C_Rename into @Key,@Name while @@fetch_status = 0 begin Set @SqlRename = 'exec tempdb..sp_rename ''##tblAccountInfo.[' + cast(@Key as varchar(100)) + ']'',''[' If Len(cast(@Name as varchar(100))) > 15 Set @SqlRename = @SqlRename + Substring(cast(@Name as varchar(100)),1,15) + ' ' + cast(@Key as varchar(100)) + ']'',''Column''' else Set @SqlRename = @SqlRename + Substring(cast(@Name as varchar(100)),1,15) + ' ' + cast(@Key as varchar(100)) + ']'',''Column''' --print @SqlRename exec (@SqlRename) Fetch next from C_Rename into @Key,@Name end close C_Rename deallocate C_Rename This is the Message in regard to this script when this part of procedure runs. Caution: Changing any part of an object name could break scripts and stored procedures. The Column was renamed to '[Country Code 708]'. And so on for all the columns. Please advice! Thanks!!
Ah - the joys of dynamic SQL ... I notice this small debug line - --print @SqlRename Use it, and see where the quotes in the statement are not coming out right. **** Do ask yourself why you would ever need to rename a column - reasons against that in this specific example: (1) This is a temp table, so you have control over the column names anyway. (2) This is a global temp table, so you should not change column names. The same table is accessible from any other connection to this server instance, which might run into error as it would expect different column names. (3) You can simply add aliases to your queries against this table, to get different column names on the results.
Thats why i put this print command in there so that i can test the dynamic sql before using it. This is what i get exec tempdb..sp_rename '##tblAccountInfo.[495]','[Country 495]','Column' exec tempdb..sp_rename '##tblAccountInfo.[496]','[Country Code 496]','Column' and so on for all the columns. I dont see any issues with this. Can you please mention if i am missing something. 1. While creating the temp table i had to use the FieldID rather than FieldName as column names because we have fields with same label but different IDs. 2. This query will be running only on request and very rarely. So chances of conflict are very less. 3.Aliases in this case are not feasible as this temp table columns are different each time its running. Thanks for the response. Please advice where i am missing something in the renaming of the columns.
Tried to edit this message, but a new one was created instead. And now I cannot delete this message ...
Your final query statement would be something like this: SELECT [495] AS [Country 495], [496] AS [Country Code 496] FROM ##tblAccountInfo You know how to generate dynamic SQL (you're already calling sp_rename through dynamic SQL) so you have the complete framework at hand to generate a query statement with aliases. *** By the way, the errors are probably popping up due to the square brackets around the column names on the parameters for sp_rename. In plain SQL, you need the square brackets around numeric column names, and around column names with blanks, but not when feeding the name as a parameter. To be honest, this is rather messy in a few respects. Preferably, column names should not consist of only numbers, and should not contain blanks. You're running into this problem because of poor choices earlier in the design of this procedure.
Thanks for all your help. You almost saved my life I know that this is not one of the best designs, but given the situations it was necessity to use that way. Situation was something like : To register for a event user had to fill a form. In that form user can create their own custom fields and uset them. Now there can be custom fields with same label but different ID One user can signup for multiple events and so can create multiple fields. client wanted to get one row per user for specific event where (if any ) custom fields become the column heading and custom fields become the value of those columns. This might give some idea but there are some complexities with in database design too which needed to be addressed. But you really saved my life, i was going crazy about whats wrong with the rename procedure. Thank alot again.
I almost saved your life - so you died? [H] There are proper ways of organizing that sort of data, trust me.
