xp_ndo on clustered environment

DenSter

When you add an extended stored procedure, you point it to the dll, which should be stored in the Binn folder on the C drive. In a clustered environment, you don't have access to the C drive from with the dialog in SQL Server, I'm guessing because if the cluster fails, the C drive is no longer the C drive but the C drive of the other cluster node.

For now I put the dll on a shared drive, so we can test, but I want to be able to put the dll on the OS drive. I know I'll have to put a copy of the dll on both cluster nodes, so that's not the issue.

My question is if it is possible to register the dll on both cluster nodes and leave the whole path out of the extended stored procedure. I'd like to hear how others have solved this.

ara3n

Hello Daniel
I would keep the DLL in c drives in both clusters.

You just need to register it twice in both clusters.


Think of them as two separate sql servers.

ara3n

Make sure you switch the server over in order to register the second.

I've done this 3 times now and it works perfectly.

DenSter

The thing is though, that when I click on the "..." button to browse to the dll from where you add the extended stored procedure, it doesn't show me the C drive. Do you just type it in there then (connection problems prevent me from trying at the moment)?

So if I register the dll on both clusters I can just leave out the path and type "xp_ndo.dll" without the path?

ara3n

hmm. to register the dll.
I put the dll in the bin directory.

Then I connect in enterprise manger, I paste this code.

USE master

EXEC sp_addextendedproc xp_ndo_enumusergroups, 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\xp_ndo.dll'

GO

 

GRANT EXECUTE 

ON [xp_ndo_enumusergroups]

TO PUBLIC

GO

 

USE master

EXEC sp_addextendedproc xp_ndo_enumusersids, 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\xp_ndo.dll'

GO

 

GRANT EXECUTE 

ON [xp_ndo_enumusersids]

TO PUBLIC

GO

 

If the there is an error when I execute this, I drop the procedures that exists.

I do this on both clusters.

DenSter

of course... T-SQL in SQL Server.... why didn't I think of that... :oops: I've been adding them directly in the master database for so long it didn't even dawn on me to try it a different way.

Thanks Ahmed

ara3n

You are welcome.

When I'm onsite, I am sometimes the same way, there is too many distraction and things going on.

DenSter

Follow up:
The extended stored procedure itself was present on both clusters as soon as I added it to the first cluster. All you have to do is add the dll to the second cluster.

ara3n

Where you connecting to the virtual server? or to the actual server?

I was connecting to each actual servers.

DenSter

I was connected through RDP to the IP address of the different nodes individually. I'm pretty sure I was on the actual server, since the dll was not in the second node's binn folder.