July 19, 2011 at 8:47 am
I have some dynamic code that is using OPENQUERY (tried OPENDATASOURCE as well, same error) to get data from some linked servers in a stored procedure. When I run the proc and print out the final statement and run that statement it runs fine but when in the proc using EXEC() to get the data inserted into a temp table it gives these errors:
OLE DB provider "SQLNCLI10" for linked server "Server1" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "Server1" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 65535, Level 16, State 1, Line 0
SQL Server Network Interfaces: Error getting enabled protocols list from registry [xFFFFFFFF].
This is the block of code for reference:
*** Build the command ******
SET @SQLString4 = 'Select DISTINCT [SQL Instance], [DB Name], [DB Owner], [compatibility_level],[Create_Date]
FROM OPENQUERY(['+ @Instance + '], ''
SELECT
''''' + @Instance + ''''' AS [SQL Instance],
name AS [DB Name],
suser_sname(owner_sid) AS ''''DB Owner'''',
compatibility_level,
create_date
from sys.databases
where name like ''''%'+ @DBString + '%''''
'')'
***** Execute *******
INSERT INTO DBNames([SQL Instance], [DB Name], [DB Owner], [compatibility_level], [create_date]) EXEC (@SQLString4)
****** Print out of @String4
Select DISTINCT [SQL Instance], [DB Name], [DB Owner], [compatibility_level],[Create_Date]
FROM OPENQUERY([Server1], '
SELECT
''Server1'' AS [SQL Instance],
name AS [DB Name],
suser_sname(owner_sid) AS ''DB Owner'',
compatibility_level,
create_date
from sys.databases
where name like ''%catalog%''
')
July 19, 2011 at 9:57 am
Have you tried moving the insert statement into the command string?
*** Build the command ******
SET @SQLString4 = 'INSERT INTO DBNames([SQL Instance], [DB Name], [DB Owner], [compatibility_level], [create_date])
Select DISTINCT [SQL Instance], [DB Name], [DB Owner], [compatibility_level],[Create_Date]
FROM OPENQUERY(['+ @Instance + '], ''
SELECT
''''' + @Instance + ''''' AS [SQL Instance],
name AS [DB Name],
suser_sname(owner_sid) AS ''''DB Owner'''',
compatibility_level,
create_date
from sys.databases
where name like ''''%'+ @DBString + '%''''
'')'
***** Execute *******
EXEC (@SQLString4)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 19, 2011 at 10:08 am
Thanks, I have tried that with the same results. I have also tried chaning the security in my linked server to use a remote server login mapping. I have not tried using a SQL account because this query is going to run against 50+ servers and I'm not willing to create that many logins :w00t:
July 19, 2011 at 4:57 pm
Hi
my guess would be that the problem isn't in your code.
That looks like it's just unable to access the server.
Ensure you can telnet to the server and that you can login.
You have the output debug code so test the servername and login for access.
cheers
jannie
July 21, 2011 at 6:53 am
It's not giving you a failed login error, but a login timeout error. That usually means the service isn't available for the connection type you're using.
Are you connecting via Named Pipes, TCP/IP, or something else?
Is the server configured to accept that type of connection?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 21, 2011 at 7:06 am
Thanks for the replies. I found it was an EXECUTE AS 'domain\user' statement at the top of the Stored procedure that was causing the issue.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply