SSMS Script view as ALTER
-
Hey guys
Just installed SSMS 2012 and am noticing a very strange issue. As a note, doing this works perfectly fine in SSMS 2008.
I have a view on a SQL 2008 server that has 500 columns in it, and when using SSMS 2012 to script this view as a CREATE statement, everything works perfectly fine.
If I attempt to script the view as an ALTER statement, SSMS will hang for a minute or two, and sometimes even crash.
I ran SQL Profiler on the target server and then used SSMS to script view as ALTER and there were hundreds of these queries coming in... I imagine there is 1 per fieldname in the view:
exec sp_executesql N'SELECT
p.name AS [Name]
FROM
sys.all_views AS v
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=v.object_id
INNER JOIN sys.extended_properties AS p ON p.major_id=v.object_id AND p.minor_id=clmns.column_id AND p.class=1
WHERE
(clmns.name=@_msparam_0)and((v.type = @_msparam_1)and(v.name=@_msparam_2 and SCHEMA_NAME(v.schema_id)=@_msparam_3))
ORDER BY
[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000)',@_msparam_0=N'FIELD_NAME',@_msparam_1=N'V',@_msparam_2=N'VIEW_NAME',@_msparam_3=N'dbo'
Is this a well known issue with SSMS 2012? Is this maybe not an SSMS issue but an SMO issue?
Thanks for any help!
-
I would venture a guess that a difference in SMO for 2012 is the source of the trouble. Have you changed any of the standard scripting options in SSMS 2012?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
opc.three (10/1/2012)
I would venture a guess that a difference in SMO for 2012 is the source of the trouble. Have you changed any of the standard scripting options in SSMS 2012?I have made changes to the standard options in an attempt to fix the issue... nothing has helped so far.
Thanks for the response!
-
If you have the time and inclination to provide proof-of-concept code that could be used on any instance to reproduce the issue it would be helpful to the community if you posted a bug on the Connect site:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
opc.three (10/1/2012)
If you have the time and inclination to provide proof-of-concept code that could be used on any instance to reproduce the issue it would be helpful to the community if you posted a bug on the Connect site:Here is a quick and dirty example... Create table. Create view. "Script View As" -> "ALTER to" -> "New query editor window"
It looks to me like SSMS/SMO is testing each column in the view to see if it has extended properties associated with it... row by row. I have disabled "Script extended properties".
As another note... if SSMS 2012 is running on the same server as the SQL Server, performance is acceptable.
Thanks for your help so far!
--
CREATE TABLE TEST_TABLE
(
[001] int, [101] int, [201] int, [301] int, [401] int
, [002] int, [102] int, [202] int, [302] int, [402] int
, [003] int, [103] int, [203] int, [303] int, [403] int
, [004] int, [104] int, [204] int, [304] int, [404] int
, [005] int, [105] int, [205] int, [305] int, [405] int
, [006] int, [106] int, [206] int, [306] int, [406] int
, [007] int, [107] int, [207] int, [307] int, [407] int
, [008] int, [108] int, [208] int, [308] int, [408] int
, [009] int, [109] int, [209] int, [309] int, [409] int
, [010] int, [110] int, [210] int, [310] int, [410] int
, [011] int, [111] int, [211] int, [311] int, [411] int
, [012] int, [112] int, [212] int, [312] int, [412] int
, [013] int, [113] int, [213] int, [313] int, [413] int
, [014] int, [114] int, [214] int, [314] int, [414] int
, [015] int, [115] int, [215] int, [315] int, [415] int
, [016] int, [116] int, [216] int, [316] int, [416] int
, [017] int, [117] int, [217] int, [317] int, [417] int
, [018] int, [118] int, [218] int, [318] int, [418] int
, [019] int, [119] int, [219] int, [319] int, [419] int
, [020] int, [120] int, [220] int, [320] int, [420] int
, [021] int, [121] int, [221] int, [321] int, [421] int
, [022] int, [122] int, [222] int, [322] int, [422] int
, [023] int, [123] int, [223] int, [323] int, [423] int
, [024] int, [124] int, [224] int, [324] int, [424] int
, [025] int, [125] int, [225] int, [325] int, [425] int
, [026] int, [126] int, [226] int, [326] int, [426] int
, [027] int, [127] int, [227] int, [327] int, [427] int
, [028] int, [128] int, [228] int, [328] int, [428] int
, [029] int, [129] int, [229] int, [329] int, [429] int
, [030] int, [130] int, [230] int, [330] int, [430] int
, [031] int, [131] int, [231] int, [331] int, [431] int
, [032] int, [132] int, [232] int, [332] int, [432] int
, [033] int, [133] int, [233] int, [333] int, [433] int
, [034] int, [134] int, [234] int, [334] int, [434] int
, [035] int, [135] int, [235] int, [335] int, [435] int
, [036] int, [136] int, [236] int, [336] int, [436] int
, [037] int, [137] int, [237] int, [337] int, [437] int
, [038] int, [138] int, [238] int, [338] int, [438] int
, [039] int, [139] int, [239] int, [339] int, [439] int
, [040] int, [140] int, [240] int, [340] int, [440] int
, [041] int, [141] int, [241] int, [341] int, [441] int
, [042] int, [142] int, [242] int, [342] int, [442] int
, [043] int, [143] int, [243] int, [343] int, [443] int
, [044] int, [144] int, [244] int, [344] int, [444] int
, [045] int, [145] int, [245] int, [345] int, [445] int
, [046] int, [146] int, [246] int, [346] int, [446] int
, [047] int, [147] int, [247] int, [347] int, [447] int
, [048] int, [148] int, [248] int, [348] int, [448] int
, [049] int, [149] int, [249] int, [349] int, [449] int
, [050] int, [150] int, [250] int, [350] int, [450] int
, [051] int, [151] int, [251] int, [351] int, [451] int
, [052] int, [152] int, [252] int, [352] int, [452] int
, [053] int, [153] int, [253] int, [353] int, [453] int
, [054] int, [154] int, [254] int, [354] int, [454] int
, [055] int, [155] int, [255] int, [355] int, [455] int
, [056] int, [156] int, [256] int, [356] int, [456] int
, [057] int, [157] int, [257] int, [357] int, [457] int
, [058] int, [158] int, [258] int, [358] int, [458] int
, [059] int, [159] int, [259] int, [359] int, [459] int
, [060] int, [160] int, [260] int, [360] int, [460] int
, [061] int, [161] int, [261] int, [361] int, [461] int
, [062] int, [162] int, [262] int, [362] int, [462] int
, [063] int, [163] int, [263] int, [363] int, [463] int
, [064] int, [164] int, [264] int, [364] int, [464] int
, [065] int, [165] int, [265] int, [365] int, [465] int
, [066] int, [166] int, [266] int, [366] int, [466] int
, [067] int, [167] int, [267] int, [367] int, [467] int
, [068] int, [168] int, [268] int, [368] int, [468] int
, [069] int, [169] int, [269] int, [369] int, [469] int
, [070] int, [170] int, [270] int, [370] int, [470] int
, [071] int, [171] int, [271] int, [371] int, [471] int
, [072] int, [172] int, [272] int, [372] int, [472] int
, [073] int, [173] int, [273] int, [373] int, [473] int
, [074] int, [174] int, [274] int, [374] int, [474] int
, [075] int, [175] int, [275] int, [375] int, [475] int
, [076] int, [176] int, [276] int, [376] int, [476] int
, [077] int, [177] int, [277] int, [377] int, [477] int
, [078] int, [178] int, [278] int, [378] int, [478] int
, [079] int, [179] int, [279] int, [379] int, [479] int
, [080] int, [180] int, [280] int, [380] int, [480] int
, [081] int, [181] int, [281] int, [381] int, [481] int
, [082] int, [182] int, [282] int, [382] int, [482] int
, [083] int, [183] int, [283] int, [383] int, [483] int
, [084] int, [184] int, [284] int, [384] int, [484] int
, [085] int, [185] int, [285] int, [385] int, [485] int
, [086] int, [186] int, [286] int, [386] int, [486] int
, [087] int, [187] int, [287] int, [387] int, [487] int
, [088] int, [188] int, [288] int, [388] int, [488] int
, [089] int, [189] int, [289] int, [389] int, [489] int
, [090] int, [190] int, [290] int, [390] int, [490] int
, [091] int, [191] int, [291] int, [391] int, [491] int
, [092] int, [192] int, [292] int, [392] int, [492] int
, [093] int, [193] int, [293] int, [393] int, [493] int
, [094] int, [194] int, [294] int, [394] int, [494] int
, [095] int, [195] int, [295] int, [395] int, [495] int
, [096] int, [196] int, [296] int, [396] int, [496] int
, [097] int, [197] int, [297] int, [397] int, [497] int
, [098] int, [198] int, [298] int, [398] int, [498] int
, [099] int, [199] int, [299] int, [399] int, [499] int
, [100] int, [200] int, [300] int, [400] int, [500] int
)
GO
CREATE VIEW TEST_VIEW
AS
SELECT
[001], [101], [201], [301], [401]
,[002], [102], [202], [302], [402]
,[003], [103], [203], [303], [403]
,[004], [104], [204], [304], [404]
,[005], [105], [205], [305], [405]
,[006], [106], [206], [306], [406]
,[007], [107], [207], [307], [407]
,[008], [108], [208], [308], [408]
,[009], [109], [209], [309], [409]
,[010], [110], [210], [310], [410]
,[011], [111], [211], [311], [411]
,[012], [112], [212], [312], [412]
,[013], [113], [213], [313], [413]
,[014], [114], [214], [314], [414]
,[015], [115], [215], [315], [415]
,[016], [116], [216], [316], [416]
,[017], [117], [217], [317], [417]
,[018], [118], [218], [318], [418]
,[019], [119], [219], [319], [419]
,[020], [120], [220], [320], [420]
,[021], [121], [221], [321], [421]
,[022], [122], [222], [322], [422]
,[023], [123], [223], [323], [423]
,[024], [124], [224], [324], [424]
,[025], [125], [225], [325], [425]
,[026], [126], [226], [326], [426]
,[027], [127], [227], [327], [427]
,[028], [128], [228], [328], [428]
,[029], [129], [229], [329], [429]
,[030], [130], [230], [330], [430]
,[031], [131], [231], [331], [431]
,[032], [132], [232], [332], [432]
,[033], [133], [233], [333], [433]
,[034], [134], [234], [334], [434]
,[035], [135], [235], [335], [435]
,[036], [136], [236], [336], [436]
,[037], [137], [237], [337], [437]
,[038], [138], [238], [338], [438]
,[039], [139], [239], [339], [439]
,[040], [140], [240], [340], [440]
,[041], [141], [241], [341], [441]
,[042], [142], [242], [342], [442]
,[043], [143], [243], [343], [443]
,[044], [144], [244], [344], [444]
,[045], [145], [245], [345], [445]
,[046], [146], [246], [346], [446]
,[047], [147], [247], [347], [447]
,[048], [148], [248], [348], [448]
,[049], [149], [249], [349], [449]
,[050], [150], [250], [350], [450]
,[051], [151], [251], [351], [451]
,[052], [152], [252], [352], [452]
,[053], [153], [253], [353], [453]
,[054], [154], [254], [354], [454]
,[055], [155], [255], [355], [455]
,[056], [156], [256], [356], [456]
,[057], [157], [257], [357], [457]
,[058], [158], [258], [358], [458]
,[059], [159], [259], [359], [459]
,[060], [160], [260], [360], [460]
,[061], [161], [261], [361], [461]
,[062], [162], [262], [362], [462]
,[063], [163], [263], [363], [463]
,[064], [164], [264], [364], [464]
,[065], [165], [265], [365], [465]
,[066], [166], [266], [366], [466]
,[067], [167], [267], [367], [467]
,[068], [168], [268], [368], [468]
,[069], [169], [269], [369], [469]
,[070], [170], [270], [370], [470]
,[071], [171], [271], [371], [471]
,[072], [172], [272], [372], [472]
,[073], [173], [273], [373], [473]
,[074], [174], [274], [374], [474]
,[075], [175], [275], [375], [475]
,[076], [176], [276], [376], [476]
,[077], [177], [277], [377], [477]
,[078], [178], [278], [378], [478]
,[079], [179], [279], [379], [479]
,[080], [180], [280], [380], [480]
,[081], [181], [281], [381], [481]
,[082], [182], [282], [382], [482]
,[083], [183], [283], [383], [483]
,[084], [184], [284], [384], [484]
,[085], [185], [285], [385], [485]
,[086], [186], [286], [386], [486]
,[087], [187], [287], [387], [487]
,[088], [188], [288], [388], [488]
,[089], [189], [289], [389], [489]
,[090], [190], [290], [390], [490]
,[091], [191], [291], [391], [491]
,[092], [192], [292], [392], [492]
,[093], [193], [293], [393], [493]
,[094], [194], [294], [394], [494]
,[095], [195], [295], [395], [495]
,[096], [196], [296], [396], [496]
,[097], [197], [297], [397], [497]
,[098], [198], [298], [398], [498]
,[099], [199], [299], [399], [499]
,[100], [200], [300], [400], [500]
FROM
[dbo].[TEST_TABLE]
-
I cannot recreate the issue. Maybe it has been fixed. Here is the SQL I see when I script the 500-column VIEW as an ALTER:
exec sp_executesql N'SELECTp.name AS [Name]
FROM
sys.all_views AS v
INNER JOIN sys.extended_properties AS p ON p.major_id=v.object_id AND p.minor_id=0 AND p.class=1
WHERE
(v.type = @_msparam_0)and(v.name=@_msparam_1 and SCHEMA_NAME(v.schema_id)=@_msparam_2)
ORDER BY
[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000)',@_msparam_0=N'V',@_msparam_1=N'TEST_VIEW',@_msparam_2=N'dbo'
It looks like it is grabbing all extended properties at once, which is a good thing.
My DB engine Build # 11.0.2325.0
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
opc.three (10/2/2012)
I cannot recreate the issue. Maybe it has been fixed. Here is the SQL I see when I script the 500-column VIEW as an ALTER:It looks like it is grabbing all extended properties at once, which is a good thing.
My DB engine Build # 11.0.2325.0
Thank you so much for your help. I upgraded both the client machine that is running SSMS, and the the SQL 2012 server to CU3 and everything is still doing the 100s of queries. As as FYI, (I think I forgot to mention) the same issue exists when doing the same thing from SSMS 2012 to a SQL 2008 server (but not when ussing SSMS 2008).
As I think this is a client issue, and because Im kind of grasping at straws here, do you mind pasting your "About Microsoft SQL Server Management Studio" details here? Maybe something got upgraded on your computer that wasnt upgraded on mine.
Here is what mine shows:
Microsoft SQL Server Management Studio 11.0.2100.60
Microsoft Analysis Services Client Tools 11.0.2332.0
Microsoft Data Access Components (MDAC) 6.1.7601.17514
Microsoft MSXML 3.0 4.0 6.0
Microsoft Internet Explorer 9.0.8112.16421
Microsoft .NET Framework 4.0.30319.269
Operating System 6.1.7601
We have this issue on at least 3 different computers here (every computer that has SSMS 2012 on it), including an Amazon EC2 virtual server that we JUST built with nothing on it or configured except for SQL 2012 Developer Edition and now CU3.
Thanks again!
-
What does this say on the instance causing the issue:
SELECT SERVERPROPERTY('Edition') AS Edition,SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Here is a post that kind of confirms it as being an SMO issue:
http://sqlblogcasts.com/blogs/jonsayce/archive/2008/02/09/how-to-fix-slow-smo-performance.aspx
As for how to make your SSMS act properly, no clue. For the record I am running SSMS 11.0.2100.60 as well and here are my scripting options:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply