How to make SMO use TCP instead of NP for server connections?

We have ~30 servers that don't have Named Pipes enabled and I can't stop/start the services until the next Maintenance Window (30 days away). I need to query all the server for users and logins (Security Audit). I want to use SMO since working with its collections will save me a lot of coding. It seems that the default protocol for SMO is Named Pipes so I get connection failure for those servers that don't have NP enabled. Is there any way to have SMO use TCP instead of NP for connecting?

SMO uses the ConnectionManager class to create connections, this class assembles the connection string based on the passed variables. The connection will pick the protocol order the client has specified to connect to the server. If you want to force the connection to use Named Pipes, you might use:

Instead of just the servername use npervername

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||Thanks - I'll give a try, but I need to TCP so I think it would tcpervername.

|||

Yep, sure you are right, you should use (without smileys :-) )

Code Snippet

tcp:Servername

|||

It didn't work. I probably haven't set the variables up correctly. I found the ConnectionContext last night and plugged it in this morning.That works but I'd like to see how your syntax should be put together if you don't mind.

note - the forum software put the Smileys in. I typed a colon as I'm sure you did (this one is intentional)

Try

svrname = Trim(svrname)

svrname = "tcpERVERNAME"

Dim svr As Server = New Server(svrname)

'svr.ConnectionContext.NetworkProtocol = NetworkProtocol.TcpIp

For Each db In svr.Databases

If db.Status = 1 Then

....

...

|||Mea Culpa - It seems there was a typo in the server name. It does work. Thank you for your help.

how to link executionid of sysdtslog90

I have enabled logging using database.

When I try to query the sysdtslog90 table using System::ExecutionInstanceGUID to link executionid - it is not matching.

Any resolution?

Thanks

The variable System::ExecutionInstanceGUID always matches the sysdtslog90.executionid column when I try it.

Are the times matching up? Are you looking in the right table? Try using a Script Task to fire an event with the ExecutionInstanceGUID in the description text, so that it is logged and you can compare the two values in the same row.

|||

It worked when I pass the System::ExecutionInstanceGUID as VARCHAR parameter instead of GUID in SQL Task parameter mapping. And when the stored procedure recieves the same parameter as uniqueidentifier

sorry for my late response.

Thanks

how to link executionid of sysdtslog90

I have enabled logging using database.

When I try to query the sysdtslog90 table using System::ExecutionInstanceGUID to link executionid - it is not matching.

Any resolution?

Thanks

The variable System::ExecutionInstanceGUID always matches the sysdtslog90.executionid column when I try it.

Are the times matching up? Are you looking in the right table? Try using a Script Task to fire an event with the ExecutionInstanceGUID in the description text, so that it is logged and you can compare the two values in the same row.

|||

It worked when I pass the System::ExecutionInstanceGUID as VARCHAR parameter instead of GUID in SQL Task parameter mapping. And when the stored procedure recieves the same parameter as uniqueidentifier

sorry for my late response.

Thanks

|||

I just ran into the same issue. I noticed that all of a sudden the executionid's between sysdtslog90 and the logging table modeled from project real (audit.executionlog) were not matching. I changed the execute sql task that calls the audit.usp_OnBeginPackageEvent stored procedure to use the System::ExecutionInstanceGUID variable as a varchar instead of GUID and now all of sudden they match. Anyone have any idea why this is happening? I know it used to work, and on the service pack sp2.

|||I don't know why it's happening, but I've encountered the same thing a few times.

how to link executionid of sysdtslog90

I have enabled logging using database.

When I try to query the sysdtslog90 table using System::ExecutionInstanceGUID to link executionid - it is not matching.

Any resolution?

Thanks

The variable System::ExecutionInstanceGUID always matches the sysdtslog90.executionid column when I try it.

Are the times matching up? Are you looking in the right table? Try using a Script Task to fire an event with the ExecutionInstanceGUID in the description text, so that it is logged and you can compare the two values in the same row.

|||

It worked when I pass the System::ExecutionInstanceGUID as VARCHAR parameter instead of GUID in SQL Task parameter mapping. And when the stored procedure recieves the same parameter as uniqueidentifier

sorry for my late response.

Thanks

|||

I just ran into the same issue. I noticed that all of a sudden the executionid's between sysdtslog90 and the logging table modeled from project real (audit.executionlog) were not matching. I changed the execute sql task that calls the audit.usp_OnBeginPackageEvent stored procedure to use the System::ExecutionInstanceGUID variable as a varchar instead of GUID and now all of sudden they match. Anyone have any idea why this is happening? I know it used to work, and on the service pack sp2.

|||I don't know why it's happening, but I've encountered the same thing a few times.