User profile for gokhanvarol

I have version 5.3.8.2, below line fails to format, please advise. SELECT try_cast('1' AS int) AS SitusCbsaCd

2 followers 2 comments 0 votes

sql prompt fails to format forceseek index hint (sql 2012)

SQL prompt fails to format below index hint, MSDN documentation is below. This is a major problem now, I cannot use sqlprompt period for formatting since most of my scripts uses the forceseek hint....

1 follower 1 comment 0 votes

Automatically commit changes in Source Control

Is there anyway to automatically commit changes in a scheduled or from background without user intervention? Thank you

0 followers 0 comments 0 votes

I know how to make it work by removing the bracket, but in any case sql prompt should not crash and take ssms down with it. This piece of code was part of a longer (over 4500 lines) stored procedure which kept crashing on format, eventually I spent the time and find out what's crashing sqlprompt and posted here. / comments

I know how to make it work by removing the bracket, but in any case sql prompt should not crash and take ssms down with it. This piece of code was part of a longer (over 4500 lines) stored procedur...

0 votes

SQLPrompt format crashes crashes ssms

Formatting the snipplet with sqlprompt crashes ssms. Please advise CREATE TABLE #ChangedKeysChildren ( CntyCd CHAR(5) NULL , PclId VARCHAR(45) NULL , PclSeqNbr TINYINT NULL ,NameSeq tinyint NULL ,B...

3 followers 4 comments 0 votes

With very minor changes (see below, removing object_id function , putting subquery down) this query returned in 1:44 and returned rows as below. I can still provide the trace data I am collecting, but if you could put the changes into sql compare and give us a prerelease version or so I'll really appreciated. We are not able to use the tool currently since this query is hanging. Thank you --(3900848 row(s) affected) SELECT CONVERT(bit, CASE i.type WHEN 1 THEN 1 WHEN 5 THEN 1 ELSE 0 END) AS [Clustered], -- 5 is reserved as 'clustered columnstore' which doesn't exist yet CONVERT(bit, CASE i.type WHEN 3 THEN 1 ELSE 0 END) AS Xml, CONVERT(bit, CASE i.type WHEN 5 THEN 1 WHld EN 6 THEN 1 ELSE 0 END) AS Columnstore, i.is_unique AS [Unique], ic.is_included_column AS [Included], i.is_unique_constraint AS UniqueConstraint, i.is_primary_key AS [Primary], i.ignore_dup_key AS IgnoreDupKey, i.is_padded AS IsPadIndex, CONVERT(bit, CASE WHEN o.type='U' THEN 1 ELSE 0 END) AS IsTable, CONVERT(bit, CASE WHEN o.type='V' THEN 1 ELSE 0 END) AS IsView, CONVERT(bit, CASE WHEN fi.object_id IS NOT NULL THEN 1 ELSE 0 END) AS FullTextKey, fg.name AS FileGroup, o.name AS ParentName, os.name AS SchemaName, i.name AS IndexName, i.fill_factor AS [FillFactor], c.name AS ColumnName, ic.is_descending_key AS Descending, CONVERT (bit, 0) AS [Statistics], CASE WHEN i.type IN (3, 4) THEN i.no_recompute/*(SELECT nrp.no_recompute from sys.objects nro JOIN sys.stats nrp ON nro.object_id = nrp.object_id WHERE nro.parent_object_id = i.object_id AND nrp.name = i.name)*/ ELSE s.no_recompute END AS NoRecompute, i.data_space_id, fg.type AS dataspacetype, i.index_id AS indexid, x.using_xml_index_id AS [UsingIndex], xi.name AS [UsingIndexName], x.secondary_type AS [SecondaryXmlType], ic.key_ordinal, ic.partition_ordinal, i.allow_row_locks, i.allow_page_locks, i.is_disabled, i.filter_definition, si.spatial_index_type, si.tessellation_scheme, sit.bounding_box_xmin, sit.bounding_box_ymin, sit.bounding_box_xmax, sit.bounding_box_ymax, sit.level_1_grid, sit.level_2_grid, sit.level_3_grid, sit.level_4_grid, sit.cells_per_object, ps.data_compression, ps.partition_number AS data_compression_partition_number, CONVERT(bit, CASE kc.is_system_named WHEN 1 THEN 1 ELSE 0 END) As is_system_named, kc.[object_id] AS o2, -- hack! Can't see where else to get this value though -- added -- removed OBJECT_ID('.') AS ObjectID -- hack! Can't see where else to get this value though fi.property_list_id INTO #temp FROM (SELECT * ,(SELECT nrp.no_recompute from sys.objects nro JOIN sys.stats nrp ON nro.object_id = nrp.object_id WHERE nro.parent_object_id = i.object_id AND nrp.name = i.NAME AND i.type IN (3, 4)) AS no_recompute FROM sys.indexes i WITH (NOLOCK))i LEFT JOIN sys.key_constraints kc WITH (NOLOCK) ON kc.parent_object_id = i.object_id AND kc.unique_index_id = i.index_id LEFT JOIN sys.data_spaces fg WITH (NOLOCK) ON fg.data_space_id=i.data_space_id LEFT JOIN sys.objects o WITH (NOLOCK) ON o.object_id=i.object_id LEFT JOIN sys.schemas os WITH (NOLOCK) ON os.schema_id=o.schema_id LEFT JOIN sys.index_columns ic WITH (NOLOCK) ON ic.object_id=i.object_id AND ic.index_id=i.index_id LEFT JOIN sys.columns c WITH (NOLOCK) on c.object_id=ic.object_id AND c.column_id=ic.column_id LEFT JOIN sys.stats s WITH (NOLOCK) on s.object_id=i.object_id AND s.name=i.name LEFT JOIN sys.xml_indexes x WITH (NOLOCK) on i.object_id=x.object_id AND i.index_id=x.index_id LEFT JOIN sys.xml_indexes xi WITH (NOLOCK) on i.object_id=xi.object_id AND x.using_xml_index_id = xi.index_id LEFT JOIN sys.spatial_indexes AS si WITH (NOLOCK) ON i.object_id=si.object_id AND i.index_id=si.index_id LEFT JOIN sys.spatial_index_tessellations AS sit WITH (NOLOCK) ON i.object_id = sit.object_id AND i.index_id = sit.index_id LEFT JOIN sys.partitions ps WITH (NOLOCK) ON ps.object_id=o.object_id AND ps.index_id = i.index_id LEFT JOIN sys.fulltext_indexes fi WITH (NOLOCK) on fi.object_id = i.object_id AND fi.unique_index_id = i.index_id WHERE o.type IN ('U', 'V', 'TF', 'TT') AND i.is_hypothetical = 0 ORDER BY i.object_id, x.secondary_type, i.index_id, ic.key_ordinal, ps.partition_number / comments

With very minor changes (see below, removing object_id function , putting subquery down) this query returned in 1:44 and returned rows as below. I can still provide the trace data I am collecting, ...

0 votes

SQLCompare query taking over 4 hours....

This query is used by sql compare. It's taking over 4 hours in one of our system which I am collecting a trace for it and I would like to send the trace and the database snapshot sqlcompare creates...

3 followers 3 comments 0 votes

How can we help you today?

gokhanvarol

Activity overview