Unlike the similar Reese’s dilemmas, these results were far less favorable…

Reese's are yummy

Reese's are yummy

 

As many of these issues begin, a developer put together a procedure.  When he ran it locally, it ran in a matter of seconds.  For some reason, though, he wanted the procedure run from a remote server.  When he attempted to run that same procedure from the remote server, it took a number of minutes.    It was apparent that it wasn’t the result set being returned, as there were OLEDB waits while the query was processing.

 

In looking at the query, it wasn’t anything spectacular.  It dumped some data into a temp table and then joined that table to an existing table and displayed the result.    The problem ended up being the temp tables.  While the query was being executed against the remote server, the temp tables were being created on the server the query was running on.  So, pulling the data into the temp table and joining the two tables all had to occur with data being pulled across the network.

 

The fix was pretty simple – change the procedure to create a table on the remote server and drop it once the procedure was finished.  The procedure ran in the expected time period.

 | Posted by tledwards | Categories: SQLServerPedia, T-SQL | Tagged: , |

You can query everything…

16 September 2009
The possibilities are endless

The possibilities are endless

Well, probably not everything, but there are some times that you might want to execute a query against a server other than the one that you’re currently on. Let’s go further and say that you don’t want to use the servername.dbname.schema.table model.

EXECUTE is used to pass commands to linked servers (http://msdn.microsoft.com/en-us/library/ms188332.aspx ). When executing a query, the syntax is:

EXEC(’some random query’) at servername – note that the query within the parenthesis needs to be quoted.

Now here is the part that I found cool (okay, I said it. It’s geeky and yet still cool). The server that you’re querying against can be passed as a variable. For example, I want to know how many databases are on MyServer.

declare @server varchar(30), @cmd varchar(100)
set @server = ‘MyServer’
set @cmd = ‘EXEC (”SELECT count(name) from sys.databases”) AT ‘+@server+
exec(@cmd)

Because I’m easily amused, finding that out pretty much made my day. In my thirst for knowledge, though, I thought I’d dig deeper. Maybe I have a table (server_inv – id identity, name varchar) that holds all of my servers and I want to figure how how many tables I have on each of them AND I want the server names to be returned as well. I’m never satisfied. So here’s how I’d do that:

SET NOCOUNT ON
declare @next_one int,
@this_one int,
@counter int,
@server varchar(20)

SELECT @counter = 1

SELECT @next_one = MIN(id)
FROM server_inv

SELECT @this_one = id,
@server = name
FROM server_inv
WHERE id = @next_one

WHILE @counter = 1
BEGIN
declare @sqlcmd varchar(1000)
set @sqlcmd = ‘EXEC (”SELECT ””’+@server+””’, count(name) from sys.databases”) AT ‘+@server+
exec(@sqlcmd)

SELECT @next_one = NULL

SELECT @next_one = MIN(id)
FROM server_inv
WHERE id > @this_one

IF ISNULL(@next_one,0) = 0
BEGIN
BREAK
END

SELECT @this_one = id,
@server = name
FROM server_inv
WHERE id = @next_one
END
RETURN

Now, I understand that this is a pretty simplistic example, but hopefully it’s an example of what can be done. I’ll admit that I haven’t fully played with this EXECUTE command and there may be limitations to its functionality.

It’s probably far easier to put together a Central Management Server if you’re on SQL Server 2008, but that’s a post for another day.

 | Posted by tledwards | Categories: SQLServerPedia, T-SQL | Tagged: , , |