how to make the SQL Express accessible via local IP?

i am using the SQL Server 2005 Express and I want to access it using the local PC IP instead of PC name.

You should be able to just swap the machine name for the ip address and it will work.sql

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 load data into a table using a synonym?

I'd like to use a data flow task to load data into a table by specifying the synonym name of the destination table, instead of the actual table name.

The OLE DB Destination is forcing me to pick an actual table or view from a drop down list. Any ideas on how to get around this?

Thank you.

This will be fixed in SP1, so that synonyms also appear in the UI in the drop down list. For RTM, the workaround is to use SqlCommand access mode, and then if you specify "select * from synonymTable", that will work.

|||

Using a SELECT statement (which is what Ranjeeta has said) is what you should be doing anyway - don't use the drop-down.

Simon explains why here: http://sqljunkies.com/WebLog/simons/archive/2006/01/20/17865.aspx

-Jamie

|||Jamie,

Is that your recommendation even for OLE DB Destinations? I can

certainly understand why you would recommend this for Sources/Lookups,

but for a destination it seems odd because you lose some options

(fast-load, keep identity, check constraints,etc) when using a SQL

statement.

Also do know or have experienced the "feature" or "bug" that Simon describes as being applicable to the OLE DB Destination?

Thanks,

Larry Pope|||

Larry,

No, I was talking about source adapters only.

-Jamie

How to link new primary key column instead of old primary key column on existing

The earlier developer, developed reports very complex formatting reports, may be he was spend lot of time on formatting. Each report is fetching data from 7 to 8 tables. All are working fine. He did very good job. He didnt used Stored Procedures in it. For Complex reports SP are giving better performance as of my knowledge. Recently I joined in the organization they assigned me reports part.

Now the problem is

Recently the database was changed accordingly their convenience. He was developed reports using old database. In the old database one table they added one new column and assigned primary key, old primary key column as set to non-key column. Now I need to link the new primary column instead of old primary key column. I dont know how to change this one, I tried verify database command. But no luck. I check report export. I am confusing on that. Can you please help me to solve this one?



Thanks in advance

NadaI believe you have been answered on another forum.