Auto-complet anomalies at INSERT for table variables

Let's have a table variable declared as below:

	Code:
	DECLARE @linked_servers TABLE ( id_linked_server BIGINT NOT NULL IDENTITY(1 ,1) ,server_name NVARCHAR(255) NOT NULL ,server_ip NVARCHAR(20) NOT NULL ,open_query AS 'arbitrarily created computed column ' + server_ip --,PRIMARY KEY (id_linked_server) ,UNIQUE (server_name, server_ip) )

Going for INSERT INTO, auto-complete does not recognize open_query as a computed column and creates the following code:

	Code:
	INSERT INTO @linked_servers ( -- id_linked_server -- this column value is auto-generated server_name, server_ip, open_query ) VALUES ( /{ server_name }/, /{ server_ip }/, /{ open_query }/ )

Having a considerably longer computed column, let's say:

Code:

DECLARE
@linked_servers TABLE (
   id_linked_server BIGINT NOT NULL IDENTITY(1 ,1)
   ,server_name NVARCHAR(255) NOT NULL
   ,server_ip NVARCHAR(20) NOT NULL
   ,open_query AS
   'SELECT
                  ' + QUOTENAME(' server_name ' ,'''') + ' AS [server name]
                  ,*
               FROM
                  OPENQUERY(
                     ' + QUOTENAME(server_ip) +
   '
                     ,
                     ''
               SELECT
                  cl.ID
                  ,cl.DatabaseName
                  ,cl.Command
                  ,cl.CommandType
                  ,cl.StartTime
                  ,cl.JobTime
                  ,cl.EndTime
                  ,cl.ErrorNumber
                  ,cl.ErrorMessage
                  ,cl.SchemaName
                  ,cl.ObjectName
                  ,cl.ObjectType
                  ,cl.IndexName
                  ,cl.IndexType
                  ,cl.StatisticsName
                  ,cl.PartitionNumber
                  --,cl.ExtendedInfo
                  ,cl.ExtendedInfo.value(''''/ExtendedInfo[1]/PageCount[1]'''' ,''''INT'''') AS [PageCount]
                  ,cl.ExtendedInfo.value(''''/ExtendedInfo[1]/Fragmentation[1]'''' ,''''DECIMAL(6,3)'''') AS [Fragmentation]
               FROM
                  gemdba.dbo.CommandLog AS cl
               WHERE 1 = 1
                  AND cl.ErrorNumber != 0
               ''
                  ) AS oq
               '
   ,PRIMARY KEY(id_linked_server)
   ,UNIQUE(server_name ,server_ip)
)

the result contains and extra quoted asterisk as well:

	Code:
	INSERT INTO @linked_servers ( -- id_linked_server -- this column value is auto-generated server_name, server_ip, open_query, [] ) VALUES ( /{ server_name }/, /{ server_ip }/, /{ open_query }/, /{ [] }/ )

SQL Assistant doesn't currently support linked servers and OPENQUERY for multiple reasons.

Don't be misled by variable names or string contents. The variable is an ordinary table variable with an ordinary computed field that consists of generated sql code integrating two of the table variable columns. But these have nothing to do with OPENQUERY or linked servers. Though the computed field will eventually be the source of a dynamic SQL that is run by SA that part works flawlessly, just as expected. You can replace 'linked_servers' and 'OPENQUERY' strings with whatever garbled text you like, the result will be the same.

It's the source code analysis of the table variable declaration that fails here. The same analysis for temporary tables fails as well, but works properly for normal (non-temporary) tables. Well, it's probably not the same algorithm but has more or less the same purpose (that is, to create the INSERT INTO code for the table).

I see. Sorry for the initial confusion. You are correct, it doesn't recognize computed columns in table variables, in table variables it expects simple column and constraint declarations only. I'm sure that is why the generated INSERT statement is invalid. Any complex expression in the declaration would produce the same results. This is something we can improve in the future.

Thank you for your feedback. Always appreciated.