Call Function From Separate Database

[vbc22]

Hi there,

I currently have TSQL code that queries from 2 separate databases residing on same the MS SQL Server instance.

My question is: What's the syntax to call a function that's on a separate database?

Current Code

		-- Data Source #1
		-- -- -- -- -- -- 
SELECT	ds1.fname,
		ds1.lname,
		ds1.email, 

		-- Data Source #2
		-- -- -- -- -- -- 
		ds2.maritalStatus,
		ds2.address,
		
		-- **************************************
		-- call function from 2nd database source
		-- SYNTAX???
		dbo.functNumChildren(ds2.employeeID)
		-- **************************************

FROM	ds1 INNER JOIN

			-- make reference to separate database 
			OPENDATASOURCE( 'SQLOLEDB',
							'Data Source=Server;
							 User ID=UID;
							 Password=PWD').DATASOURCE2DB.dbo.TBLNAME AS ds2 

			-- the field that connects the two database sources
				ON ds1.employeeID = ds2.employeeID

I am aware that ds2 is currently referring to a table on the separate database.

How would I refer to a function that's on that same database?

Any advice or suggestion would be very welcome. Thanks!

Regards.

 

[kxramse]

Unless I'm missing something, you just do this...

SELECT ds1.fname,
ds1.lname,
ds1.email,

-- Data Source #2
-- -- -- -- -- --
ds2.maritalStatus,
ds2.address,

-- **************************************
-- call function from 2nd database source
-- SYNTAX???
[DATASOURCE2DB].dbo.functNumChildren(ds2.employeeID)
-- **************************************
FROM DATASOURCE1DB.dbo.ds1 INNER JOIN
DATASOURCE2DB.dbo.TBLNAME AS ds2
-- the field that connects the two database sources
ON ds1.employeeID = ds2.employeeID

 

[vbc22]

Hi,

I do not believe that will work.

The Select statement is grabbing fields from 2 separate databases, hence the OPENDATASOURCE function that includes the server name, login, password for accessing those fields.

 

[kxramse]

If you're grabbing data from the same instance I can't see why it wouldn't work. I have tried similar code here to test and I've never used that complex connect string. Did you try what I suggested?

 

[vbc22]

Hi kxramse,

Your suggestion works. I tested a query based on multiple databases housed under a single server and the method you illustrated works.

Unfortunately, it does not work when I query against multiple databases residing different servers. Each server will have its own separate login and password.

The above example I showed got me what I wanted, but I want to also access a function on that 2nd database residing on a different server. The OPENDATASOURCE references a table so it doesn't allow for a function call.

My fault for not explaining clearly---I apologize. I should have used the term "server" instead of database.

After doing some more research, I came across two Stored Procedures. I have also re-written the sample code to demonstrate.

EXEC	sp_addlinkedserver	@server= 'srv2'
			
EXEC	sp_addlinkedsrvlogin	@rmtsrvname='srv2',
								@useself = 'False', 
								@locallogin = NULL, 
								@rmtuser ='sa',
								@rmtpassword = 'pwd'

SELECT	d1.fld1, 
		d2.fld2,
		-- call function residing on 2nd server
		d2.functCall(d1.paramFld) AS fld3

FROM	db1.dbo.name AS d1 
			-- unsure about syntax here...
			LEFT JOIN srv2.db1.dbo.tbl AS d2
				ON d1.fld = d2.fld

This code will not work. I'm still trying to find a way to do this. If anyone knows how or can direct me to good source material, I would really appreciate.

Regards.

 

[vbc22]

Alright...I don't know why it would work, but it did.

Instead of including all that sp_login code, I simply used the four-part naming scheme to reference the other server and it worked. This is what you had suggested kxramse, so I thank you and apologize for not going through with your suggestion in the first place.

The other server has a separate login and password but is it because it was already a registered Database Engine that I didn't have to provide the login info?

Anyways, once again thank you.

Regards.