SQL-DMO Connection String problem - Help

Question

Post reply

SQL-DMO Connection String problem - Help

Steve Moody

SSC Enthusiast

Points: 194
More actions
September 8, 2006 at 10:21 am

#92380

Hi,
The short version of what I am needing is this:
I need a way to code (In c# and preferably in SQL-DMO) a connection to
a named instance
of SQL Server 2000 that is running on a machine with other named
instances. The named instance in particular in this case is G3. There
is another part that may make this more complicated... G3 is running
specifically on port 1032 and only on that (due to UDP 1434
restrictions on our firewalls).
Now for the longer version:
I have this bit of code in a much larger program
SQLServer2Class myServer = new SQLServer2Class();
myServer.LoginSecure = true;
myServer.Connect("(local)\\G3", null, null);
It fails with the following errors:
Unhandled Exception: System.Runtime.InteropServices.COMException
(0x80040000): [Microsoft][ODBC SQL Server Driver][
DBNETLIB]SQL Server does not exist or access denied.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen
(Connect()).
at SQLDMO.SQLServer2Class.Connect(Object ServerName, Object Login,
Object Password)
at BackUp.BackUpControl.backUpRegularDatabases()
at BackUp.DBBackup.Main(String[] args)
I figure that either
"(local)\\G3" is invalid in which case I need to understand what the
correct syntax is for
working with a named instances on the same physical server
or
The problem is because we had to statically assign a specific TCP\IP
port number for the new instance (do to firewall restrictions)
So the bottom line is... How do I write a piece of connection code
(prefer SQL-DMO) to connect up to a named instance on a machine (local
to where this script is running) that is connecting via a specific port
(No UDP 1434)
Any Help will be really really really appreciated 😉
Thanks
-Steve M
September 11, 2006 at 8:00 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Norman Kelm SSCommitted Points: 1922 More actions · Answer 1

Hi Steve,

Well, EM can deal with this and it uses DMO. Strange. Anyway...

I think your code looks good.

Did you try .\\G3 or theActualServerName\\G3?

How about using the Client Network Utility to create an alias G3Alias? You can define the actual port number in the alias.

Are your client tools (SQLDMO.dll) all at SP4 build 2039?

This is something I need to test in my product which is written in C#. I'll check that source tonight and do a second install to see if I have the same issue.

Let me know how it is going.

Norman

get DTS Package Search at http://www.gerasus.com/

Norman Kelm SSCommitted Points: 1922 More actions · Answer 2

Steve,

I am not experiencing any issues with my code, it can connect to (local) and (local)\G3. The only difference between your code and mine is the object declaration.

Yours: SQLServer2Class myServer = new SQLServer2Class();

Mine: SQLServer2 myServer = new SQLServer2Class();

I'm perplexed why you are having this issue. Windows firewall?

Good Luck,

Norman

get DTS Package Search at http://www.gerasus.com/

Steve Moody SSC Enthusiast Points: 194 More actions · Answer 3

I had my technical lead look at the code as well, and when he tried it ...it failed. He then made some changes, tried it again and it succeeded. He then backed all of his changes out, and tried it again and this time it succeeded.

Near as we can tell, it was the specific syntax I was using that was causing the problem. I think In any case the responses were very helpful and have given me a clearer understanding of what I may have been doing wrong.

Thanks one and all for your help

-Steve