Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite
1

I have two databases. They are on different servers. One is named REPORTDB and the other is named TRANSDB. TRANSDB is referenced as a linked server on REPORTDB named TRANS.

I execute a query like this from REPORTDB using Four Part Naming and Distributed Transactions:

SET @val = ''
SET @DBName = 'TRANSDB'
SELECT @val = @val + 'CREATE SYNONYM ' + [name] + ' for ' + @DBName + '.dbo.' 
    + [name] + CHAR(10) + CHAR(13) 
FROM TRANS.db.sys.tables;

I expect the result of this query to generate a CREATE SYNONYM statement for each table in TRANSDB inside my REPORTDB (Table1, Table2, Table3). However, it only creates one statement and that is the last table that is returned in the result set from TRANSDB (Table3).

This correctly returns three CREATE SYNONYM statements for Table1, Table2, and Table3 using OPENQUERY:.

SET @val = ''
SET @DBName = 'TRANSDB'
SELECT @val = @val + 'CREATE SYNONYM + [name] + ' for ' + @DBName + '.dbo.' 
    + [name] + CHAR(10) + CHAR(13) 
FROM OPENQUERY( TRANS, ' select [name] FROM  db.sys.tables')

Since I do not wish to use openquery, how can I get the DISTRIBUTED TRANSACTION using four part naming to return results correctly?

share|improve this question
Quick comment: You don't need to use OPENQUERY syntax at all if you have linked servers. In fact you should disable ad hoc for security reasons. 4 part: [Instance].[DB].[Container].[Object] – Eric Higgins Oct 22 '12 at 17:39
I was unable to reproduce this issue. How are you verifying that the command only includes the last table, via PRINT? Are you sure the ` @val + ` is in there? What version of SQL Server are you using on both servers? – Aaron Bertrand Oct 22 '12 at 17:44
@AaronBertrand Both servers are 2008 R2. I did use PRINT to debug the string's that are getting generated and val + '' is indeed there. Using the samples above the first query returns 1 string and the bottom returns 3 if I do print val. Also, if I put the two databases on the same server and run sample 1 then three strings are returned. – decompiled Oct 22 '12 at 17:51
@EricHiggins without the distributed transaction service configured the query would not run. Perhaps a configuration issue? – decompiled Oct 22 '12 at 17:54
1  
I cross posted this on StackOverflow. Here's what worked for me. stackoverflow.com/questions/13019624/… – decompiled Oct 23 '12 at 13:32
show 1 more comment

2 Answers

up vote 0 down vote

There is a problem with your linked server.

FROM OPENQUERY( TRANS, ' select [name] FROM  db.sys.tables')

is failing and returning the wrong sys.tables OR the TRANS linked server is setup wrong.

share|improve this answer
The results are returning as expected in the OPENQUERY. Not as expected in Four Part name. Setup is using SQLOLEDB. In general queries are working as expected, but not when assigning to a parameter. – decompiled Oct 22 '12 at 20:02
up vote 0 down vote

Variables don't work like that in SQL Server. You'll need to dump the results to a temp table and then loop through it to generate the single large string. Why it works with OPENQUERY, I've got no idea.

share|improve this answer
The first query works when the databases are on the same server. It does not work when they are linked. Query 2 works when they are linked. I'm curious as to why, what is the difference other than what server the query is run on. – decompiled Oct 22 '12 at 20:51
It has to do with the way that SQL receives the data from the remote server. – mrdenny♦ Oct 22 '12 at 21:00
I don't think this is true unless there are very specific linked server settings that are causing an issue. I have no problems assembling concatenated variables across linked servers using an identical query as the OP, and have never had to use a #temp table or a loop to do so. – Aaron Bertrand Oct 22 '12 at 21:19
Strange. Maybe it's just that I've always assumed that it wouldn't work, so I've always just used a loop to handle stuff like this. – mrdenny♦ Oct 22 '12 at 23:04
@AaronBertrand what provider are you using on your linked server? Also, what settings do you have set on the Provider? I'm using SQLOLEDB with nothing checked on the provider properties screen. Thanks! – decompiled Oct 23 '12 at 13:02
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.