OPENROWSET and get COUNT(*)

[JayKusch]

So how do I go about using an OPENROWSET command to set a variable based on a remote COUNT(*) of a table.

I know it does not look like this ... but here is what I am after:

declare @P  Int,
        @Pub VarChar(20),
        @Pub_DB VarChar(20),
        @Art VarChar(20)

SELECT @P = COUNT(*)
FROM OPENROWSET('SQLOLEDB', 'Server=MySrvr;Trusted_Connection=yes;',
'SELECT COUNT(*) FROM ' + @Pub + '.' + @Pub_DB + '.' + @Art + '' )

What I am doing is loading @Pub, @PubDB and @Art with values that will give me a 3 part name (that being Server.DB.Table).

Ultimately trying to get the record count of the table I have strung together and then populate my variable @P with the count.

Thanks ALL!


Thanks

J. Kusch

 

[SQLSister]

Well right now you haven't populated @pub, @pub_db and @art

"NOTHING is more important in a database than integrity." ESquared

 

[JayKusch]

That is mentioned in the text of the thread:

"What I am doing is loading @Pub, @PubDB and @Art with values that will give me a 3 part name (that being Server.DB.Table)."

I did not add that part of the code in order not to cloud up the main point of getting the OPENROWSET porton of the command to work.

I have code prior to the OPENROWSET that loaded the values of @Pub, @PubDB and @Art via a SELECT statement.

Thanks SS




Thanks

J. Kusch

 

[JayKusch]

Still looking for a work around.

Thanks ALL!

Thanks

J. Kusch

 

[PWise]

for a starter
try something like this


Declare @p varchar(500)
Select @p='SELECT ' + '* FROM [bills]'

exec(' Declare @c int
SELECT @c =count(*) FROM OPENROWSET(
''SQLOLEDB'',
''Server=Server;Trusted_Connection=yes;'',
'''+ @p + ''') Select @c
')

note