rwheeler23
asked on
Calling a SQL function in stored procedure that exists in another SQL instance
I have this cursor inside a stored procedure. Inside this script is a call to a function called GETQUANTITY. This function exists in another database in the same instance. Now they want to move that database to another server. When I run this sp now I get this message even after adding the prefix for the linked server.
"Cannot find either column "MYDATABASE" or the user-defined function or aggregate "MYDATABASE.dbo.GETQUANTIT Y", or the name is ambiguous." The purpose of this routine is to insert a purchase order. No value needs to be returned.
DECLARE po_line_cursor CURSOR
FOR SELECT
ISNULL(POH.VENDORID,'UNKNO WN') AS VENDORID,
ISNULL(POH.PODATE,'01/01/1 900') AS PODATE,
ISNULL(POH.REQDATE,'01/01/ 1900') AS REQDATE,
ISNULL(POH.TERMS,'UNKNOWN' ) AS TERMS,
ISNULL(POH.SHIPVIA,'UNKNOW N') AS SHIPVIA,
ISNULL(POH.ISCLOSED,0) AS ISCLOSED,
case WHEN VEN.VGPCODE IS NULL THEN VEN.VENDERID ELSE LTRIM(RTRIM(VEN.VGPCODE)) END AS VGPCODE,
ISNULL(VEN.NAME,'UNKNOWN') AS VENDNAME,
case PATINDEX('%-%',POH.PONUM) WHEN 0 THEN POH.PONUM ELSE SUBSTRING(POH.PONUM,0,PATI NDEX('%-%' ,POH.PONUM )) end AS PONUMBER,
case when POL.POLID IS NULL THEN 0 ELSE POL.POLID END AS POLID,
ISNULL(POL.ITEMNUMBER,'UNK NOWN') AS ITEMNMBR,
ISNULL(CONVERT(CHAR(100),P OL.DESCRIP TION),'UNK NOWN') AS ITEMDESC,
case when POL.UNITPRICE IS NULL THEN 0 ELSE POL.UNITPRICE END AS UNITPRICE,
ISNULL(POL.JOBNUMBER,'UNKN OWN') AS JOBNUMBER,
case when JOB.CONTRACTCOMPLETE IS NULL THEN 0 ELSE JOB.CONTRACTCOMPLETE END AS CONTRACTCOMPLETE,
case when JOB.CONTRACTNUMBER IS NULL THEN '' ELSE JOB.CONTRACTNUMBER END AS CONTRACTNUMBER,
case when MYLINKEDSERVER.MYDATABASE. dbo.GETQUA NTITY(POL. POQTY) IS NULL THEN 0.00 ELSE MYLINKEDSERVER.MYDATABASE. dbo.GETQUA NTITY(POL. POQTY) END AS QTYORDER,
case when POL.ISCOMPLETE IS NULL THEN 0 ELSE POL.ISCOMPLETE END AS ISCOMPLETE,
case when POL.OTHERCOST1 IS NULL THEN 0 ELSE POL.OTHERCOST1 END AS OTHERCOST1,
case when POL.COSTCENTER1 IS NULL THEN '' ELSE POL.COSTCENTER1 END AS COSTCENTER1,
case when POL.TOOLING1 IS NULL THEN 0 ELSE POL.TOOLING1 END AS TOOLING1,
case when POL.OTHERCOST2 IS NULL THEN 0 ELSE POL.OTHERCOST2 END AS OTHERCOST2,
case when POL.COSTCENTER2 IS NULL THEN '' ELSE POL.COSTCENTER2 END AS COSTCENTER2,
case when POL.TOOLING2 IS NULL THEN 0 ELSE POL.TOOLING2 END AS TOOLING2,
case when POL.OTHERCOST3 IS NULL THEN 0 ELSE POL.OTHERCOST3 END AS OTHERCOST3,
case when POL.COSTCENTER3 IS NULL THEN '' ELSE POL.COSTCENTER3 END AS COSTCENTER3,
case when POL.TOOLING3 IS NULL THEN 0 ELSE POL.TOOLING3 END AS TOOLING3,MET.keyweight,
row_number() over (partition by case PATINDEX('%-%',POH.PONUM) WHEN 0 THEN POH.PONUM ELSE SUBSTRING(POH.PONUM,0,PATI NDEX('%-%' ,POH.PONUM )) end order by case PATINDEX('%-%',POH.PONUM) WHEN 0 THEN POH.PONUM ELSE SUBSTRING(POH.PONUM,0,PATI NDEX('%-%' ,POH.PONUM )) end) as POLINE
from [MYLINKEDSERVER].[MYDATABA SE].[dbo]. [POLINE] POL
INNER JOIN [MYLINKEDSERVER].[MYDATABA SE].[dbo]. [POHEADER] POH ON POL.PONUM=POH.PONUM
INNER JOIN [MYLINKEDSERVER].[MYDATABA SE].[dbo]. [VENMAS] VEN ON POH.VENDORID=VEN.VENDERID
INNER JOIN [MYLINKEDSERVER].[MYDATABA SE].[dbo]. [JOBS] JOB ON POL.JOBNUMBER=JOB.JOBNUMBE R
INNER JOIN [MYLINKEDSERVER].[MYDATABA SE].[dbo]. [METHODIZE R] MET ON POL.ITEMNUMBER = MET.ITEMNUMBER
WHERE CASE PATINDEX('%-%',POH.PONUM) WHEN 0 THEN POH.PONUM ELSE SUBSTRING(POH.PONUM,0,PATI NDEX('%-%' ,POH.PONUM ))END = @I_vPONumber
ORDER BY POH.PONUM,POL.POLID
"Cannot find either column "MYDATABASE" or the user-defined function or aggregate "MYDATABASE.dbo.GETQUANTIT
DECLARE po_line_cursor CURSOR
FOR SELECT
ISNULL(POH.VENDORID,'UNKNO
ISNULL(POH.PODATE,'01/01/1
ISNULL(POH.REQDATE,'01/01/
ISNULL(POH.TERMS,'UNKNOWN'
ISNULL(POH.SHIPVIA,'UNKNOW
ISNULL(POH.ISCLOSED,0) AS ISCLOSED,
case WHEN VEN.VGPCODE IS NULL THEN VEN.VENDERID ELSE LTRIM(RTRIM(VEN.VGPCODE)) END AS VGPCODE,
ISNULL(VEN.NAME,'UNKNOWN')
case PATINDEX('%-%',POH.PONUM) WHEN 0 THEN POH.PONUM ELSE SUBSTRING(POH.PONUM,0,PATI
case when POL.POLID IS NULL THEN 0 ELSE POL.POLID END AS POLID,
ISNULL(POL.ITEMNUMBER,'UNK
ISNULL(CONVERT(CHAR(100),P
case when POL.UNITPRICE IS NULL THEN 0 ELSE POL.UNITPRICE END AS UNITPRICE,
ISNULL(POL.JOBNUMBER,'UNKN
case when JOB.CONTRACTCOMPLETE IS NULL THEN 0 ELSE JOB.CONTRACTCOMPLETE END AS CONTRACTCOMPLETE,
case when JOB.CONTRACTNUMBER IS NULL THEN '' ELSE JOB.CONTRACTNUMBER END AS CONTRACTNUMBER,
case when MYLINKEDSERVER.MYDATABASE.
case when POL.ISCOMPLETE IS NULL THEN 0 ELSE POL.ISCOMPLETE END AS ISCOMPLETE,
case when POL.OTHERCOST1 IS NULL THEN 0 ELSE POL.OTHERCOST1 END AS OTHERCOST1,
case when POL.COSTCENTER1 IS NULL THEN '' ELSE POL.COSTCENTER1 END AS COSTCENTER1,
case when POL.TOOLING1 IS NULL THEN 0 ELSE POL.TOOLING1 END AS TOOLING1,
case when POL.OTHERCOST2 IS NULL THEN 0 ELSE POL.OTHERCOST2 END AS OTHERCOST2,
case when POL.COSTCENTER2 IS NULL THEN '' ELSE POL.COSTCENTER2 END AS COSTCENTER2,
case when POL.TOOLING2 IS NULL THEN 0 ELSE POL.TOOLING2 END AS TOOLING2,
case when POL.OTHERCOST3 IS NULL THEN 0 ELSE POL.OTHERCOST3 END AS OTHERCOST3,
case when POL.COSTCENTER3 IS NULL THEN '' ELSE POL.COSTCENTER3 END AS COSTCENTER3,
case when POL.TOOLING3 IS NULL THEN 0 ELSE POL.TOOLING3 END AS TOOLING3,MET.keyweight,
row_number() over (partition by case PATINDEX('%-%',POH.PONUM) WHEN 0 THEN POH.PONUM ELSE SUBSTRING(POH.PONUM,0,PATI
from [MYLINKEDSERVER].[MYDATABA
INNER JOIN [MYLINKEDSERVER].[MYDATABA
INNER JOIN [MYLINKEDSERVER].[MYDATABA
INNER JOIN [MYLINKEDSERVER].[MYDATABA
INNER JOIN [MYLINKEDSERVER].[MYDATABA
WHERE CASE PATINDEX('%-%',POH.PONUM) WHEN 0 THEN POH.PONUM ELSE SUBSTRING(POH.PONUM,0,PATI
ORDER BY POH.PONUM,POL.POLID
It's not possibly to use functions directly on a linked server.
The (ugly) workarounds:
Use OPENROWSET('use your function') or LinkedServer.DatabaseName. dbo.sp_exe cutesql 'use your funciton'.
Thus:
- Review your function, can it be done in a store procedure instead?
- Why are you using cross-database funcionts? This indicates maybe a necessary coupling, thus moving the database to different instances is not really an option.
- Why not calling the function from the final consumer, if possible?
The (ugly) workarounds:
Use OPENROWSET('use your function') or LinkedServer.DatabaseName.
Thus:
- Review your function, can it be done in a store procedure instead?
- Why are you using cross-database funcionts? This indicates maybe a necessary coupling, thus moving the database to different instances is not really an option.
- Why not calling the function from the final consumer, if possible?
You can create a SQL SERVER JOB (JOB) scheduled for every 5 minutes (or so) on the second SQL Server where your function reside.
The JOB should execute a stored procedure on the second server.
The stored procedure will SELECT changes on the first server.
For changes you can create a table CHANGES on the first server and instead of calling the function on the second server write to CHANGES.
Scheduled procedure will select from CHANGES with LINKED SERVER, insert to where it should be, and deleted processed change from CHANGES.
Ferst SQL Server:
1. Your cursor fetches next row.
2. Write to CHANGES (including DateTime of the change).
Second SQL Server:
1. Every 5 minutes SQL Server Job runs the following (It will not start if you have many changes and it takes more than 5 min to process them.)
2. SELECT next (TOP 1) row from CHANGES (Located on First SQL Server) ordered by DateTime of the change. (Stop if there is no next row.) (You should not use CURSOR FOR UPDATE here - it will lock the CHANGE table. Just use SELECT TOP 1 ... ORDERED BY ChangeDate)
3. Put the data where it should be.
4. Delete (or flag) processed row.
5. Go to 2.
This is a stable architecture. For example, if second server is down, the first server will continue working. When it is back online the rows from CHANGES will be processed automatically without losses.
Sorry for long description, but it is a quite simple to implement.
The JOB should execute a stored procedure on the second server.
The stored procedure will SELECT changes on the first server.
For changes you can create a table CHANGES on the first server and instead of calling the function on the second server write to CHANGES.
Scheduled procedure will select from CHANGES with LINKED SERVER, insert to where it should be, and deleted processed change from CHANGES.
Ferst SQL Server:
1. Your cursor fetches next row.
2. Write to CHANGES (including DateTime of the change).
Second SQL Server:
1. Every 5 minutes SQL Server Job runs the following (It will not start if you have many changes and it takes more than 5 min to process them.)
2. SELECT next (TOP 1) row from CHANGES (Located on First SQL Server) ordered by DateTime of the change. (Stop if there is no next row.) (You should not use CURSOR FOR UPDATE here - it will lock the CHANGE table. Just use SELECT TOP 1 ... ORDERED BY ChangeDate)
3. Put the data where it should be.
4. Delete (or flag) processed row.
5. Go to 2.
This is a stable architecture. For example, if second server is down, the first server will continue working. When it is back online the rows from CHANGES will be processed automatically without losses.
Sorry for long description, but it is a quite simple to implement.
ASKER
Excellent advice everyone. Here is a little background on this project. On the first server sits an ERP solution which has very strict rules, one of which is that once a purchase order is sent to a vendor no further changes are permitted. On the second server is a home grown application that has no rules. I have been fighting with this beast for years. It was born in MS Access and the database was merely imported into SQL. Every PO that was ever entered for the past 20+ years sits in there. When my current stored procedure runs it take about 20 minutes to complete.I have asked to have change indicators added to the two tables involved but they refuse. I like the idea of a CHANGE table(s) as users are always pulling up purchase orders and making changes. I could create my own indicators on my tables on the first server. Back to the design table!
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Create the same function in a local db. Don't try to run functions remotely.