February 25, 2013 at 7:09 pm
I am now implementing an update of a large table by looping all the databases in my server instance. Its a large table and I should loop through some 200 databases. So I am done looping the databases and writing the query for updationit. but my dynamic sql will result in a very big query with all UNION ALLS' in between. I need a datatype to hold such a large text value. I tried VARCHAR(MAX) first and changed it to text which is resulting an error saying I cant use text datatype for declaring scalar variables.. Some one please suggest an idea.. For your better understanding i posted a small portion of coding
IF OBJECT_ID(@DBName + N'.dbo.Organization') IS NOT NULL
AND COL_LENGTH(@DBName+'.dbo.Organization','OrganizationID') IS NOT NULL
AND COL_LENGTH(@DBName+'.dbo.Organization','POL_ID') IS NULL
BEGIN
SET @sql = @sql + 'INSERT INTO ' + @dbname + '.dbo.Organization ' +'(Last_Backup, Backupsize) select DateModified, FileSizeinKB from AIM_Master.dbo.PSBackupStatistics'
+ CHAR(13) + CHAR(10) +'INNER JOIN '+@dbname+ '.dbo.Organization ON AIM_Master.dbo.PSBackupStatistics.POLID ='+@dbname + '.dbo.Organization.OrganizationID'
+CHAR(13) + CHAR(10)+ CHAR(13) + CHAR(10)
--Pra:-):-)--------------------------------------------------------------------------------
February 25, 2013 at 7:57 pm
What is the problem of using varchar(max). I think 2GB is well enough for any query.
If you are printing or selecting from the variable, the output result may be truncated in ssms result window.
If that is the case try changing result to file and see whether your result is as expected
February 25, 2013 at 8:32 pm
so what you say is, even if it doesn't show the result in SSMS when i try to exec the local variable it works???? Problem is only with displaying it in the SSMS results window?? Y is it so?? Am i missing any essential thing about SSMS???
--Pra:-):-)--------------------------------------------------------------------------------
February 25, 2013 at 9:29 pm
in ssms - Tools > Options > Query Results
You will find that the charecters that can be diplayed in each type has an upper limit.
But while using the variables or columns are bound only by its max capacity in varcharmax it is 2GB
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply