Does sql server have something that is similar to open query but more functional?

Question

Post reply

Does sql server have something that is similar to open query but more functional?

BaldingLoopMan

SSCertifiable

Points: 6378
More actions
April 8, 2010 at 10:54 am

#222935

We support a bunch of different servers w/ seval sql server instances and version. We have a monitoring sql instance that basically keeps stats on all the boxes supported. It does this through open query because it allows u to loop through all the boxes as long as there set up w/ aliases and query all sorts of info on them.
My problem is open query is limited in what you can do. There is a limitation on the length of the sql script as well as u can't declare var's. u basically can only pass in a select. i want the ability to run any chunck of sql on an external box. Does sql server have anything that allows this or am i stuck w/ openquery.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Garadin One Orange Chip Points: 29613 More actions · Answer 1

Have you tried Linked Servers?

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 2

yes. the problem is there are multiple different monitors which are just dynamic sql to check things like drive space, replication latency, data inconsistencies, ddl changes....etc. These sql strings are run accross n number of servers dynamically by looping through a table that says which monitors should run on which box/instances using open query. So if i use link server i would lose the dynamic nature. Actually there are some instanceswere i've had to use link server but i'd like to have all of htese processes using the same thing instead of some using link server and some using open query.

Essentially all i'm looking for is to be able to run a chunk of sql code on a differenct box/instance w/o using open query because open query is limited as far as what can run in the chunk.

Did i explain that well? hope so cause i think i confused myself.

Garadin One Orange Chip Points: 29613 More actions · Answer 3

Do you have a small segment of one of the scripts that you have to run through openquery (Or one of the whole scripts if they're relatively short)?

Something that's generic enough to post here that you don't think would work via linked servers?

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

I would re-write the process using SSIS, looping through a list of servers is easy. You could even likely re-use most of the queries..

CEWII

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

BaldingLoopMan (4/8/2010)
So if i use link server i would lose the dynamic nature. Actually there are some instanceswere i've had to use link server but i'd like to have all of htese processes using the same thing instead of some using link server and some using open query.
Essentially all i'm looking for is to be able to run a chunk of sql code on a differenct box/instance w/o using open query because open query is limited as far as what can run in the chunk.
Did i explain that well? hope so cause i think i confused myself.

I don't see why the use of linked servers would cause any problems so long as the linked servers were named the same as the servers themselves and you use 4 part naming conventions.

--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)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 6

I did something similar using Powershell and SMO - you only need to install Powershell on your CMS server and use that instance to query all of the servers.

Or, you can use Powershell and SQLCMD to execute the scripts on each server.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

eko indriyawan Hall of Fame Points: 3316 More actions · Answer 7

Have you tried to combine Synonyms and views, maybe with these technique make the query more efficient.

Regards,

Eko Indriyawan