SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Auto-complet anomalies at INSERT for table variables

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
Auto-complet anomalies at INSERT for table variables
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Auto-complet anomalies at INSERT for table variables Reply with quote
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 }*/,
   /*{ [*] }*/
)

Fri Aug 03, 2012 8:44 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
SQL Assistant doesn't currently support linked servers and OPENQUERY for multiple reasons.
Fri Aug 03, 2012 1:49 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
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).
Fri Aug 03, 2012 5:29 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
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.
Fri Aug 03, 2012 6:11 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.