Dynamic SQL variable questions

Question

Post reply

Dynamic SQL variable questions

Staggerlee

SSCertifiable

Points: 5272
More actions
November 16, 2012 at 6:33 am

#265980

Hi all,
trying to run a simple OPENROWSET command to get info on a remote server and put it into a table.
I have the command working but wanted to take it to the next level and use a variable for the Server to get the info from. From what i can understand this has to be done by dynamic sql as you cant use parameters in OPENROWSET.
I think I have it almost there now, I just need to know how to use a variable and not have it encased in ''
my query is:
DECLARE @server VARCHAR(50)= 'VM-TestServer'
DECLARE @Query NVARCHAR (MAX)
SET @Query = N'
INSERT INTO dev.dbo.ServerDetails ([MachineName] , [ServerName], [Instance], [Edition], [ProductLevel], [ProductVersion],[COLLATION], [IsClustered], [IsFullTextInstalled] , [IsIntegratedSecurityOnly])
SELECT * FROM OPENROWSET(''SQLNCLI'', ''Server=''' + @server + ''';Trusted_Connection=yes; '',''
SELECT SERVERPROPERTY(''''MachineName''''),
SERVERPROPERTY(''''ServerName''''),
SERVERPROPERTY(''''InstanceName''''),
SERVERPROPERTY(''''Edition'''') ,
SERVERPROPERTY(''''ProductLevel'''') ,
SERVERPROPERTY(''''ProductVersion'''') ,
SERVERPROPERTY(''''Collation'''') ,
SERVERPROPERTY(''''IsClustered'''') ,
SERVERPROPERTY(''''IsFullTextInstalled'''') ,
SERVERPROPERTY(''''IsIntegratedSecurityOnly'''');
)''
'
PRINT @Query
Look at the PRINT i get:
INSERT INTO dev.dbo.ServerDetails ([MachineName] , [ServerName], [Instance], [Edition], [ProductLevel], [ProductVersion],[COLLATION], [IsClustered], [IsFullTextInstalled] , [IsIntegratedSecurityOnly])
SELECT * FROM OPENROWSET('SQLNCLI', 'Server='VM-TestServer';Trusted_Connection=yes; ','
SELECT SERVERPROPERTY(''MachineName''),
SERVERPROPERTY(''ServerName''),
SERVERPROPERTY(''InstanceName''),
SERVERPROPERTY(''Edition'') ,
SERVERPROPERTY(''ProductLevel'') ,
SERVERPROPERTY(''ProductVersion'') ,
SERVERPROPERTY(''Collation'') ,
SERVERPROPERTY(''IsClustered'') ,
SERVERPROPERTY(''IsFullTextInstalled'') ,
SERVERPROPERTY(''IsIntegratedSecurityOnly'');
)'
Which would work (unless ive missed something else very possible!) except for the line
SELECT * FROM OPENROWSET('SQLNCLI', 'Server='VM-TestServer';Trusted_Connection=yes; ','
should be
SELECT * FROM OPENROWSET('SQLNCLI', 'Server=VM-TestServer;Trusted_Connection=yes; ','
Any suggestions!? :unsure:

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Roland Alexander STL SSCrazy Points: 2915 More actions · Answer 1

Take away the extra apostrophes before and after the variable:

SET @Query = N'

INSERT INTO dev.dbo.ServerDetails ([MachineName] , [ServerName], [Instance], [Edition], [ProductLevel], [ProductVersion],[COLLATION], [IsClustered], [IsFullTextInstalled] , [IsIntegratedSecurityOnly])

SELECT * FROM OPENROWSET(''SQLNCLI'', ''Server=' + @server + ';Trusted_Connection=yes; '',''

SELECT SERVERPROPERTY(''''MachineName''''),

SERVERPROPERTY(''''ServerName''''),

SERVERPROPERTY(''''InstanceName''''),

SERVERPROPERTY(''''Edition'''') ,

SERVERPROPERTY(''''ProductLevel'''') ,

SERVERPROPERTY(''''ProductVersion'''') ,

SERVERPROPERTY(''''Collation'''') ,

SERVERPROPERTY(''''IsClustered'''') ,

SERVERPROPERTY(''''IsFullTextInstalled'''') ,

SERVERPROPERTY(''''IsIntegratedSecurityOnly'''');

)''

'

Roland Alexander
The Monday Morning DBA
There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer

Staggerlee SSCertifiable Points: 5272 More actions · Answer 2

Thanks Roland,

I found it around the same time to 🙂

Now i have the error

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near '

SELECT SERVERPROPERTY('MachineName'),

SERVERPROPERTY('ServerName'),

SERVERPROPERTY('InstanceName'),

SERVERPROPERTY('Edit'.

which im bit lost on... :/

thank you for the help!

Staggerlee SSCertifiable Points: 5272 More actions · Answer 3

finally!

sorted it... just been staring it this silly thing for hours now.. clearly lost perspective..

Code that works! thanks again Roland for the help!

DECLARE @server VARCHAR(50)= 'VM-TestServer'

DECLARE @Query NVARCHAR (MAX)

SET @Query = N'

INSERT INTO dev.dbo.ServerDetails ([MachineName] , [ServerName], [Instance], [Edition], [ProductLevel], [ProductVersion],[COLLATION], [IsClustered], [IsFullTextInstalled] , [IsIntegratedSecurityOnly])

SELECT * FROM

OPENROWSET(''SQLNCLI'', ''Server=' + @server +';Trusted_Connection=yes;'',

''

SELECT

SERVERPROPERTY(''''MachineName''''),