Can you force OPENDATASOURCE to user TCP/IP netork protocol?

[WyldeCarde]

Hi all, hopefully a quick and easier answer to this one:

I am trying to use OPENDATASOURCE to copy data from one SQL server to another. (I'm aware SSIS is better for this but the scripts are easier for my colleagues to understand and update)

I'm falling over at the first hurdle

I've written the following:

SELECT *
FROM OPENDATASOURCE('SQLOLEDB','DRIVER={SQL Server};Data Source=LiveBR;User ID=UN;Password=Pass').v4BusinessRules.DBO.FileFormats

Where LiveBR is a local system DSN that tests fine from the ODBC wizard.

I get this error:

Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53].

Which makes perfect sense, since the server only accepts TCP/IP connections, I can't figure out how you force either the DSN or the query to use TCP/IP.

I would very much appreciate any assistance

**********************************************************
If I had my own icecream van the chimes would play "Don't fear the reaper"
**********************************************************

 

[Qik3Coder]

Can you ping LiveBR? or is livebr.somedomain.com
You could also try putting the IP in lieu of the machine name. (must also be ping-able).

Lod

You've got questions and source code. We want both!

 

[gmmastros]

Try the IP Address as Lod suggests. You may also want to add the network library setting to it, too.

Network Library=DBMSSOCN;

Ex:

SELECT *
FROM OPENDATASOURCE('SQLOLEDB','DRIVER={SQL Server};Data Source=LiveBR;User ID=UN;Password=Pass[!];Network Library=DBMSSOCN;[/!]').v4BusinessRules.DBO.FileFormats

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[WyldeCarde]

Hi

Thanks for the responses

LiveBR is a local ODBC System DSN, which does in fact use the IP address. It resolves fine, and I can query data over it using say... Access, the only issue it that it is using named pipes, whereas the server is configured to only accept TCP/IP

George, I tried your suggestion, which makes it no longer resolve at all, which might mean its the right setting but the wrong option for that setting. I will experiment with that

**********************************************************
If I had my own icecream van the chimes would play "Don't fear the reaper"
**********************************************************

 

[mrdenny]

Setup an alias on the server in cliconfg.exe which uses TCP/IP.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)
MCM (SQL 2008)
MVP

My Site

 

[WyldeCarde]

Thanks once again for the suggestion, I set up the alias, checked that I could connect to the alias name via SSMS and then I ran:

SELECT *
FROM OPENDATASOURCE('SQLNCLI','DRIVER={SQL Server};Data Source=Live24x7;User ID=UN;Password=PW;Network Library=DBMSSOCN').v4BusinessRules.DBO.FileFormats

And now i get:

Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'Live24x7'.

Which is... odd? Data source should be the DB, what on earth is going on here?

**********************************************************
If I had my own icecream van the chimes would play "Don't fear the reaper"
**********************************************************

 

[Qik3Coder]

Ganked from connectionstrings.com (Your best friend)


Standard Security
Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

You've got questions and source code. We want both!