July 16, 2008 at 9:56 am
I want to combine a select statement and a stored procedure output.
What I'm looking to do is combine 'sp_validatelogins' and 'select @@servername'.
So, the results would be similar...
Server........SID...........NT Login
Server1.......0.x00........user343
Server1.......0x01.........user64
Server2.......0x0...........user33
server2........0x33.........user4
July 16, 2008 at 10:19 am
I don't see how you're going to get multiple different @@servernames (as in the example), but this gets the resultset:
declare @OrphanNTLogins table
(
SID varbinary(85) null,
NTLogin sysname null
)
insert @OrphanNTLogins exec master.dbo.sp_validatelogins
select @@servername as Server,
SID,
NTLogin
from @OrphanNTLogins
order by 2
If you need this to be a query (vs a batch), then you could hide the proc call inside OpenQuery():
exec sp_addlinkedserver 'MyServer', 'SQL Server'
exec sp_serveroption 'MyServer', 'data access', true
go
select * from OpenQuery(MyServer, '
declare @OrphanNTLogins table --holds results from sp_validatelogins
(
SID varbinary(85) null,
NTLogin sysname null
)
--this proc finds Windows logins that no longer exist outside of SQL
insert @OrphanNTLogins exec master.dbo.sp_validatelogins
select @@servername as Server,
SID,
NTLogin
from @OrphanNTLogins
order by 2
')
July 16, 2008 at 10:23 am
Just a quick datapull. Simply going to use SQLCMD
:Connect Server1
:r c:\script.sql
:Cnnect Server2
:r c:\script.sql
July 16, 2008 at 10:33 am
its failing with below error::
Msg 17, Level 16, State 1, Line 1
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
July 16, 2008 at 10:36 am
Simple example of what I'm doing....
:connect [YourServer]
:r c:\script.sql
:connect [YourServer2]
:r c:\script.sql
Script w/in c:\script.sql
Begin
create table ##Temptable1544
(
SID varbinary(85) null,
NTLogin sysname null
)
insert ##Temptable1 exec master.dbo.sp_validatelogins
select @@servername as Server,
SID,
NTLogin
from ##Temptable1544
order by 2
drop table ##Temptable1544
July 16, 2008 at 10:45 am
I'm not too familar w/ sp_addlinked server.
In regards, i would need to pull this data from 100+ servers.
When create/running this proc, is this only for the session or is that established going forward as being linked servers..?
July 16, 2008 at 11:11 am
Only mentioned OpenQuery because wasn't sure what you were trying to do. Now that you've clarified, I don't think it's the right approach.
But the basic approach ought to work.
July 16, 2008 at 11:14 am
Msg 17, Level 16, State 1, Line 1
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
I think your approach should work. The above error does not indicate a problem with the query. You're missing the final "end", but maybe that was webpage cut & paste error.
Can you succeed with a simpler sql script, e.g. "select @@servername"
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply