January 20, 2012 at 7:05 am
Hi,
Iam facing an issue with dynamic column list building.
It is working fine in my DEV server but not my PROD server.
Actual task is : My proc builds a dynamic comma seperated column list of a table and insert into another table having same columns. Its working fine in DEV server. But giving issue with PROD server.
Mycode looks like :
SELECT @ColumnList = @ColumnList + CAST(COLUMN_NAME AS VARCHAR(128)) + ', '
FROMINFORMATION_SCHEMA.COLUMNS
WHERETABLE_NAME = @LdgTableName
SET @ColumnList = LEFT(@ColumnList, LEN(@ColumnList) - 1)
The comma as the seperator giving issue.
Any suggestions..?
January 20, 2012 at 7:44 am
DECLARE @ColumnListNVARCHAR(MAX)
SET @ColumnList =''
SELECT@ColumnList= @ColumnList + CAST(COLUMN_NAME AS VARCHAR(128)) + ', '
FROMINFORMATION_SCHEMA.COLUMNS
WHERETABLE_NAME = 'MY_TABLE'
ORDER BYORDINAL_POSITION
IF LEN(@ColumnList) > 0
SET @ColumnList = LEFT(@ColumnList, LEN(@ColumnList) - 1)
PRINT @ColumnList
The above piece of code works fine in PROD server but not in DEV server.
I am getting a comma at the last in the PROD but not in DEV.
January 20, 2012 at 7:53 am
DECLARE @ColumnList NVARCHAR(MAX)
SELECT @ColumnList = COALESCE(@ColumnList, '') + ',' + CHAR(13) + CHAR(10) + CAST(COLUMN_NAME AS VARCHAR(128))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MY_TABLE'
ORDER BY ORDINAL_POSITION
IF LEN(@ColumnList) > 0
BEGIN
SELECT @ColumnList = STUFF(@ColumnList, 1, 3, '')
END
PRINT @ColumnList
January 20, 2012 at 8:10 am
Instead of trim the last character, you can trim the first, using STUFF function:
DECLARE @ColumnListNVARCHAR(MAX)
SET @ColumnList =''
SELECT@ColumnList = @ColumnList + ', ' + CAST(COLUMN_NAME AS VARCHAR(128))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TExtras'
ORDER BY ORDINAL_POSITION
IF LEN(@ColumnList) > 0
SET @ColumnList = STUFF(@ColumnList,1,1,'')
SELECT @ColumnList
I hope this could be useful for you. Best regards!
January 20, 2012 at 8:29 am
I think the actual problem is the PRINT command. PRINT will only show a certain number of characters (I forget the number) and it sounds like you may have reached that limit. For checking dynamic SQL, I always return an XML fragment (usually using FOR XML PATH('')), because the limits for XML is about 2GB, which is much more than I've ever needed for dynamic SQL.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 20, 2012 at 8:34 am
I don’t find any issue in your script. It works fine on my system.
declare @columnlist nvarchar(max)
set @columnlist =''
select @columnlist = @columnlist + cast(column_name as varchar(128)) + ', '
from information_schema.columns
where table_name = 'employees'
order by ordinal_position
print 'original columnlist: ' + @columnlist
if len(@columnlist) > 0
set @columnlist = left(@columnlist, len(@columnlist) - 1)
print 'trimmed columnlist: ' + @columnlist
Original ColumnList: EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, PostalCode, Country, HomePhone, Extension, Photo, Notes, ReportsTo, PhotoPath,
Trimmed ColumnList: EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, PostalCode, Country, HomePhone, Extension, Photo, Notes, ReportsTo, PhotoPath
January 20, 2012 at 11:18 am
thanks for quick replies.
Able to resolve the issue. Applied patch SQLServer2005 SP4 on my RTM. Now its working fine.
Thanks you all
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply