CODEWITHSUNDEEP
sqlsql-server-2008-r2
user1466436
user1466436

Reputation: 61

Executing Dynamic SQL statements

when i try to execute follow statement in one SQL Server 2008 R2

declare @tsql varchar(max), @linkedServer varchar(30), @comandoExec varchar(max)


SET @linkedServer = 'Linked_Server'
Set @tsql = 'USE Database_test
select
      left(a.NAME,30),
      left(a.FILENAME,200),
      convert(decimal(12,2),round(a.size/128.000,2)),
      convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)),
      a.groupid
from
      [sys].sysfiles a'

SET @comandoExec =      'EXEC('''+ @tsql +''') AT '+@linkedServer

print @comandoExec
EXEC (@comandoExec)

i got this error message. 

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'SpaceUsed'

Can someone help me in this case?

best regards Edney

Upvotes: 0

Views: 144

Answers (3)

user1466436
user1466436

Reputation: 61

problem is solved. 

declare @tsql varchar(max), @linkedServer varchar(30), @comandoExec varchar(max)


SET @linkedServer = 'Linked_Server'
Set @tsql = 'USE Database_test
select
      left(a.NAME,30),
      left(a.FILENAME,200),
      convert(decimal(12,2),round(a.size/128.000,2)),
      convert(decimal(12,2),round(fileproperty(a.name,''**''**SpaceUsed**''**'')/128.000,2)),
      a.groupid
from
      [sys].sysfiles a'

SET @comandoExec =      'EXEC('''+ @tsql +''') AT '+@linkedServer

print @comandoExec
EXEC (@comandoExec)

Upvotes: 0

user1466436
user1466436

Reputation: 61

i found the problem, this code works fine now. I need to put 2 more '' and the statement, by SpaceUsed

declare @tsql varchar(max), @linkedServer varchar(30), @comandoExec varchar(max)


SET @linkedServer = 'Linked_Server'
Set @tsql = 'USE Database_test
select
      left(a.NAME,30),
      left(a.FILENAME,200),
      convert(decimal(12,2),round(a.size/128.000,2)),
      convert(decimal(12,2),round(fileproperty(a.name,''**''**SpaceUsed**''**'')/128.000,2)),
      a.groupid
from
      [sys].sysfiles a'

SET @comandoExec =      'EXEC('''+ @tsql +''') AT '+@linkedServer

print @comandoExec
EXEC (@comandoExec)

Upvotes: 1

Suman Banerjee
Suman Banerjee

Reputation: 1961

declare @tsql varchar(max), @linkedServer varchar(30), @comandoExec varchar(max)


SET @linkedServer = 'Linked_Server'
Set @tsql = 'USE Database_test
select
      left(a.NAME,30),
      left(a.FILENAME,200),
      convert(decimal(12,2),round(a.size/128.000,2)),
      convert(decimal(12,2),round(fileproperty(a.name,"SpaceUsed")/128.000,2)),
      a.groupid
from
      [sys].sysfiles a'

SET @comandoExec =      'EXEC('''+ @tsql +''') AT '+@linkedServer

print @comandoExec
EXEC (@comandoExec)

Upvotes: 1

Related Questions