How to do an OPENROWSET query within a cursor using variables

Question

Post reply

How to do an OPENROWSET query within a cursor using variables

Blue Rabbit

SSC Enthusiast

Points: 190
More actions
October 22, 2015 at 9:28 am

#312440

Hi, I am writing a custom query to determine if a legacy table exists or not. From My CMS Server I already have all the instances I have to query and I store the name of the instance in the @Instance variable. I cannot get those stubborn ticks to work right in my query. Below I am using the IF EXISTS statement to search the metadata for the legacy table. Any ideas or solutions to this problem? Thanks for any help provided.
DECLARE @Found tinyint
DECLARE @Instance varchar(100)
set @Instance = 'The Instance'
IF (EXISTS (SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=' + @Instance + ';UID=DBAReader;PWD=DBAReader;','SELECT * FROM [DBA].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''TheTable''') AS a))
SET @Found = 1
ELSE
SET @Found = 0
PRINT @Found

Viewing 3 posts - 1 through 2 (of 2 total)

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

x SSC-Insane Points: 23660 More actions · Answer 1

I don't think you're going to be able to run it this way.

https://msdn.microsoft.com/en-us/library/ms190312.aspx

This page says that the argument that you're trying to specify the instance in must be a "string constant", in other words, I don't think you are going to be able to build it using a variable specifying the instance name.

Try playing with some dynamic sql!

DECLARE @C INT

DECLARE @cmd NVARCHAR(200)

DECLARE @instance NVARCHAR(100)

DECLARE @params nvarchar(500)

SET @instance = 'MYSERVER\MYINSTANCE'

SET @params = N'@c_out int OUTPUT'

SET @cmd = N'select @c_out = count(*) from OPENROWSET(''SQLOLEDB'',''Server=' + @instance + ';TRUSTED_CONNECTION=YES;Initial Catalog=mydb'',''select * from mydb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''''test'''''')'

select @cmd

exec sp_executesql @cmd, @params, @c_out = @C OUTPUT;

select @C

edit: forgot a stray 'N' for unicode!

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Blue Rabbit (10/22/2015)
Hi, I am writing a custom query to determine if a legacy table exists or not. From My CMS Server I already have all the instances I have to query and I store the name of the instance in the @Instance variable. I cannot get those stubborn ticks to work right in my query. Below I am using the IF EXISTS statement to search the metadata for the legacy table. Any ideas or solutions to this problem? Thanks for any help provided.
DECLARE @Found tinyint
DECLARE @Instance varchar(100)
set @Instance = 'The Instance'
IF (EXISTS (SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=' + @Instance + ';UID=DBAReader;PWD=DBAReader;','SELECT * FROM [DBA].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''TheTable''') AS a))
SET @Found = 1
ELSE
SET @Found = 0
PRINT @Found

You'll need to do it all as precaclulated dynamic SQL to form the complete nested SELECT that uses OPENROWSET.

You also need to get out of the habit of using login names and passwords in your code. It's a major security violation. You need to setup an Active Directory user that can access the various instances and run the code as that user using trusted connections.

I've not played with CMS myself but I would imagine that's (should be) already been done through CMS and you shouldn't need for explicit logins of any type.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)