Calling A Function From A Remote (linked Server) Problem
May 21, 2008
Hi guys,
I am trying to call a function from a remote sql server (linked server) using the following syntax:
select [ServerName].[dbName].dbo.CLRHelloWorld('SomeMessage'). I am getting the following error:
The object name '[ServerName].[dbName].dbo.' contains more than the maximum number of prefixes. The maximum is 2.
I need to call this function remotely and using this syntax; I don't want to use OPENQUERY...
Any hints?
Yousef
View 4 Replies
ADVERTISEMENT
May 24, 2004
I have linked Oracle Server as a linked server on my SQL server.
Now i want to execute the stored procedure on oracle server which has one input parameter and one output parameter.
i am using the following sql statements in SQL Server Query analyzer to execute that procedure
declare @sal float
exec [GENEVA].testDB.dbo.EmployeeOutPrm
'1',
@sal output
but getting the following error:
Server: Msg 7212, Level 17, State 1, Line 3
Could not execute procedure 'EmployeeOutPrm' on remote server 'GENEVA'.
[OLE/DB provider returned message: One or more errors occurred during processing of command.]
[OLE/DB provider returned message: Syntax error in {call...} ODBC Escape.]
COULD ANYBODY HELP ME TO RESOLVE THE SAME
many thanks in advance
bhavya
View 7 Replies
View Related
Jan 16, 2004
I am trying to call an RPC from SQL2KSP3 to Sybase ASE 12.5. SQL2K is on an IBM Intel Server running Win2KSP4, ASE is on an IBM RS6000 w/ AIX 5.2.
I have the ASE server set up as a linked server in SQL 2000, and doing queries is working fine, i.e. I can
SELECT * FROM asesrvr.testdb.dbo.tablename
and get back all the data in tablename from the testdb database on the ASE server asesrvr.
However, when I try to run an RPC using the same syntax, I get a generic 7212 error (Could not execute procedure 'procname' on remote server 'asesrvr'. I am calling the rpc with
DECLARE @output char(1)
EXEC asesrvr.testdb.dbo.procname @output output
I'm using Sybase's ASE OLEDB provider; on the Linked Server Properties, I have
General Tab
Product name: asesrvr
Data source: devprod
Provider string: <empty>
Location: greyed out
Catalog: <empty>
Security Tab
One local login, sa, which has impersonate checked as the sa passwords on the two servers are the same (I want to eliminate security as an issue while I try to get this working).
Server Options Tab
Collation Compatible: checked
Data Access: checked
RPC: checked
RPC Out: checked
Use Remote Collation: unchecked
Collation Name: <empty>
Connection Timeout: 0
Query Timeout: 0
SQL2K's @@version is
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Any ideas on how I can troubleshoot this further? It does not appear to be limited to this one procedure (this one only has one output parameter and one line of code, so it's about as simple as it can get for testing purposes). Again, queries work fine, so the linked server itself is connected and working, at least for queries.
Thanks very much for your help,
Vince
View 2 Replies
View Related
Feb 25, 2008
Hello,
How can I call UDFs on linked servers in SQL Server 2005?
More details:
I have some UDFs on Instance "VAXPServerA" in Database "DB1", for example "getSomething(number)".
It might look like this (for example):
----
CREATE FUNCTION [dbo].[getSomething] (
@theNumber int
)
RETURNS int AS
BEGIN
DECLARE @result int
SET @result = theNumber * 5
RETURN @result
END
----
I can call this function inside of the DB by typing:
----
SELECT dbo.getSomething(5)
----
I also can call the function from another DB on instance "VAXPServerA" by typing:
----
SELECT DB1.dbo.getSomething(5)
----
I have a second instance called "VAXPServerB", and "VAXPServerA" is a linked server in "VAXPServerB". I can do selects and stuff, it works fine.
But when I want to call the function...
----
SELECT [VAXPServerA].DB1.dbo.getSomething(5)
----
I obtain the next error:
Msg. 207, Level 16, State 1, Line 1
The column name 'VAXPServerA' is not valid.
Any hint?
Thanks in advance
David
View 1 Replies
View Related
Oct 27, 2007
Hi Sir,
I want to call one linked server stored procedure from another stored procedure.
--BATCH FOR LINKED SERVER SETUP
SET @IBatchNum = 2
EXEC PR_Linked_Server
@VServer = 'Linked_Amisys'
,@VSrvProduct = ''
,@VProvider = 'SQLOLEDB'
,@VDataSrc = 'DTCORPMXDV9'
,@VCatalog = 'pubs'
,@VCurrentUsr = 'CORPajohri'
,@VServerUsr = 'dba'
,@VServerPwd = 'dba'
END TRY
The linked server was not created When I executed the query. However, I was able to create the linked server when I executed this query(batch 2) alone.
Is there any limitation of SP which I am not aware of?
View 1 Replies
View Related
Oct 17, 2006
I was wondering if anyone knows the best way to call a function that is located in database on a different server then the database that is calling the function. Is it possible to do this with out linked servers? I am running SQL Server 2000. Any help in this matter would be greatly appreciated.
View 1 Replies
View Related
May 25, 2006
I have UDF in a database on SQL2000 server. Is it possible to call this UDF from other server (SQL2005)? I did setup a linked server to SQL2000
Call to the following function returns an error:
Msg 207, Level 16, State 1, Line 1
Invalid column name 'srv2000'.
select [srv2000].db_test.dbo.F_TEST()
View 4 Replies
View Related
Mar 23, 2000
Hello all SQL Server 7 gurus,
I need your help!!! I can use SELECT to query a table on the remote server but
I can't use UPDATE,DELETE OR INSERT. Please tell me how to get around this problem.
Thanks in advance.
PS. The remote server is added by using sp_addlinkedserver
View 7 Replies
View Related
Feb 11, 2005
I need to be able to query remote server tables and join them to local tables. Thought that linked server would be best so that I can use local stored procs for parameters if needed. I have no administrative control over remote server. I have been given select permissions only to one db on remote server. Both servers are sql2000, local machine has spk3.
I have established the remote server in network client utility. I can register the remote server in EM, look at tables. I can run select statements against the remote server in QA when I log in and specify that server. I can reference an odbc data connection with web pages and display rowsets.
Next, I created a linked server under security, using sql (since it's an sql 2000 db) with the same name used above in the client utility and the em. I created a local login with the same name and password as the login used successfully above (Paragraph 2). On the security tab, I've used sa as the local login and entered the remote user and password, using the default 'be made without using a security context'. When I try to view the tables (which I can do through registered) or run this through QA:
SELECT * FROM OPENQUERY(CASCE, 'SELECT person_pk, lastname FROM dbName.dbo.tblName (NOLOCK)'),
I get: sql error 17: sql server does not exist or access denied
If I edit security, wipe out the top settings and specify the security context below using the login information, same thing. Tried messing around with different security contexts, no help or ole db error.
Any help? In addition, any tutorials out there a little more user friendly than the bol? I think I want to use the linked server option and stored procs.
View 14 Replies
View Related
Jul 10, 2013
Problem ms sql server 2012 not using remote index on sql server 2000 linked server with odbc
Let say my servers are:
srvdb and remotesrv
when do select on srvdb:
select * from remotesrv.database.dbo.inventory this select not using index on remoteserver
but if this select is on remoteserver like
select * from database.dbo.inventory, this select use index PK_Inventory (clustered)
View 5 Replies
View Related
Oct 25, 2000
Hi Everybody,
Can anyone tell me what is the difference between Remote Server and Linked Servers.
thks in advance,
Srinivasan.
View 1 Replies
View Related
Nov 14, 2007
I have created a linked server (to an AS/400) via MSDASQL. It works fine when I execute queries locally (i.e. from Management Studio running on the SQL 2005 server).
However, when I execute a query from a remote machine I get an error (see below for the exact message).
I know the ODBC connection is between the SQL server and the AS/400, and that the remote client does not have connectivity to the AS/400. But shouldn't the SQL server be able to pass the query through to the linked server even if the query is initiated from a remote machine?
Does anyone have any suggestions on how I can resolve this problem so that queries against the linked server can be executed from remote machines?
Errors:
OLE DB provider "MSDASQL" for linked server "MERCURY" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "MERCURY" returned message "[IBM][iSeries Access ODBC Driver]Key value in connection string too long.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MERCURY".
Environment: SQL 2005, Standard Edition SP2
Thanks in advance for any suggestions.
David Rueter
drueter@assyst.com
View 2 Replies
View Related
Aug 22, 2007
The following T-SQL 2005 snippet executes a remote sproc on an Oracle database.
DECLARE @OutParam VARCHAR(5)
EXEC ('{Call getnextpin(1)}') AT ALTTEST
GetNextPin takes a single input parameter.
I need to modify the above code snippet to return the next PIN number into @OutParam.
Any ideas?
Any help appreciated.
Regards,
Paul.
View 1 Replies
View Related
Oct 7, 2014
Is there any way to find out the password for the remote login of the Linked server
View 1 Replies
View Related
Apr 4, 2008
Can someone please shed some light on what seems to me to be a common requirement.
If I create an alias or linked server to Server1 - say Alias1 - on Server1 and then use that name in a query on Server1, a remote/distributed query is always used (even though we are running on the local server and that overhead is completely unnecessary).
Is SQL Server really not capable of deciding that
select * from Alias1.db1.dbo.table1
and
select * from Server1.db1.dbo.table1
should be optimized and executed exactly the same when Alias1 is Server1, but that it is a distributed query ONLY when Alias1 is really referring to a remote server? I realize that the four part name is not necessary when I am referring to objects on the current server, but I am trying to write code that is server instance independent.
It just seems that if that is not possible, then the only way to create system independent stored procs that can run in dev, staging, and production environments and work with multiple databases on multiple servers is to create all sorts of scripts to regenerate all the procs whenever you move a database between servers?
If SQL Server is even close to the enterprise big iron server that MS now claims it is, it surely needs to support running in dev, staging, and production environments and work with multiple databases on multiple servers?!
I'm really looking for someone to tell me I'm missing something simple, and of course you can do this - but complex workarounds are invited too :-)
This is not something I am investigating as an academic exercise, I am already doing this, but I have to figure out how to do it better because with all these unnecessary distributed queries, performance is horrible.
View 2 Replies
View Related
Mar 8, 2007
I have 2 servers: #1 -- SQL 2005 SP1 publisher ; #2 -- SQL 2005 SP2 subscriber
originally I had #1 as pub and dist but dist but killing my CPU so I was in the process of moving the dist to #2... Got it all configured and when I tried to add #2 as a dist for #1 it fails when I get to the administrative password screen... It give me an error about how it cannot connect with the given password but under that is says linked server failed.
Since it mentioned linked server I tried to connect via the previous linked server to #2 and it failed... I cannot connect to #2 anymore from #1. I can login directly to #2 and I can use osql to connect to #2 but linked server does not work. I tried all drivers and many configurations...
Any ideas??
View 1 Replies
View Related
Jun 18, 2015
I have a database that is off site, it has a database one it that I have a Linked server connection to. We have no other means of connecting to that server database except via that linked server connection. Is there a way I can Copy eplicatemoves etc... that database to an internal server with out doing it table by table. It is apx 80gig db
Copying a backup file is not an option either, and Mailing it on media make it a significant delay.
View 2 Replies
View Related
Jul 20, 2005
I have my own function, which I can use:declare @dt as datetimeselect @dt='20040121 12:22:33'select * from index_gold_iif(@dt)When I try do it from linked serwer:declare @dt as datetimeselect @dt='20040121 12:22:33'select * fromlewiatan.e_.dbo.index_gold_iif(@dt)I have massage:Server: Msg 170, Level 15, State 31, Line 2Line 4: Incorrect syntax near '('.Did server can't see function from linked server? Is it true or I have donesome error (what)?MarekPS. all done as "sa" user, linked server linked with sa account
View 3 Replies
View Related
Jul 22, 2007
i did "Linked server" between To Servers , and it's Working.
---------------------------
For Example :
Server 1 =S1.
Server = S2.
i create table in S1 : name = TblS1
i create same table in S2 : name TblS2
and i create trigger(name tr_cpD) From S1 in TblS1 For send data To TblS2 in S2
/****************** trigger Code ***************
CREATE TRIGGER dbo.tr_cpD
ON dbo.TblS1
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
insert into [S2].[dbname].[dbo].[TblS2] Select ID,Name from insertedEND
**************************************************
result is :
Msg 7399, Level 16, State 1, Procedure tr_cpD, Line 14
The OLE DB provider "SQLNCLI" for linked server "S2" reported an error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Procedure tr_cpD, Line 14
Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked server "S2". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
how i can execute this trigger
View 5 Replies
View Related
Oct 17, 2006
UPDATE CD SET col1=SR.col1,col2=SR.col2,col3=SR.col3,col4=SR.col4,col5=SR.col5,col6=SR.col6,col7=SR.col7,
col8=SR.col8,col9=SR.col9,col10=SR.col10
FROM LNKSQL1.db1.DBO.Table1 CD
join Table2 USRI on USRI.col00 = CD.col00
join table3 SR on USRI.col00 = SR.col00
Here, I'm trying to tun this from an instance and do a remote update. col00 is a primary key and there is a clustered index that exists on this column. When I run this query, it does a 'select * from tabl1' on the remote server and that table has about 60 million rows. I don't understand why it would do a select *... Also, we migrated to SQL 2005 a week or so back but before that everything was running smooth. I dont have the execution plan from before but this statement was fast. Right now, I can't run this statement at all. It takes about 37 secs to do one update. But if I did the update on a local server doing remote joins here, it would work fine. When I tried to show the execution plan, it took about 10 mins to show up an estimated plan and 99% of the time was spent on Remote scan. Please let me know what I can do to improve my situation. Thank you
View 4 Replies
View Related
Aug 14, 2001
In an ASP, I have a dynamically created SQL statement that amounts to "SELECT * FROM Server1.myDB.dbo.myTable WHERE Col1 = 1" (Col1 is the table's primary key). It returns the data immediately when executed.
However, when the same record is updated with "UPDATE Server1.myDB.dbo.myTable SET Comments = 'blah blah blah' WHERE Col1 = 1", the page times out before the query can complete.
I watched the program in Profiler, and I saw on the update that sp_cursorfetch was being executed as an RPC once per each row in the table. In a table of 78000 records, the timeout occurs well before the last record is fetched, and the update bombs.
I can run the same statements in Query Analyzer from a linked server and have the same results. The execution plan shows that a Remote Query is occurring on the select that returns 1 row, and a Remote Scan is taking place on the update scanning 78000 rows (I guess this is where all the sp_cursorfetch calls are happening...?).
How can I prevent the Remote Scan? How can I prevent the execution of the RPC sp_cursorfetch for each row in the remote table?
Thank you!
View 2 Replies
View Related
Jun 7, 2008
i have a function
Create Function ReturnAmountB(@CMID int) Returns Decimal
as
BEGIN
declare @Return decimal
select @Return =sum(PD_PaymentAmount) from Payment_Details where
PD_IsRefund=1 and PD_PaymentType=0 and PD_CMID=@CMID
return @Return
END
when i run this on server(remote) this error shows...
Incorrect syntex near 'Function'
Must declare the variable '@CMID'
A RETURN statement with a return value cannot be used in this context
i am not able to understand why this error shows...
Any solution!!!
spandey
View 6 Replies
View Related
Mar 16, 2007
Could anyone shed some light on the syntax of accessing system function on a linked server?I'm trying to get the recovery models of databases on a linked. However using databasepropertyex locally generates wrong results.e.g. select databasepropertyex(name, 'recovery') RecoveryModel from [server/databasename].master.dbo.SysDatabases I tried select [server/databasename].databasepropertyex(name, 'recovery') RecoveryModel from [server/databasename].master.dbo.SysDatabases which does not work. Thanks.
View 1 Replies
View Related
Nov 23, 2006
I have a Progress DB set up as a linked server.
To get the data through to SQL Server 2005 in a useable format i need to use the progress PRO_ELEMENT function call. How do I delimit this so it gets passed to the progress DB.
I've tried
SELECT
{fn PRO_ELEMENT(fldarr1,1,1)} as fld1
from ls1..pub.tab
This just returns an unknown function message which I believe is on the SQL Server end of the call.
This statement works fine through Business Objects.
Any help greatfully received.
View 2 Replies
View Related
Sep 19, 2014
SELECT MAX(ID)
FROM [LinkedServer].[Database].dbo.[TableName] (NOLOCK)
WHERE <Condition>
The above SQL Script ran successfully up to yesterday. But today its throws the below error message.
Remote table-valued function calls are not allowed.
Now i have modified the SQL script as follows
SELECT MAX(ID)
FROM [LinkedServer].[Database].dbo.[TableName] WITH (NOLOCK)
WHERE <Condition>
I want to know how the 1st SQL script runs successfully up to yesterday.
View 7 Replies
View Related
Feb 13, 2004
Hi, have configured an ODBC linked server for an Adaptive Server Anywhere (ASA6.0) database.
I have to write a function (not a procedure) that receives a number (@Code) and returns 1 if it was found on a table in the linked server, or 0 if not. Looks very simple...
One problem, is that the queries on a linked-server must be made through the OPENQUERY statement, which doesen't support dynamic parameters. I've solved this making the whole query a string, and executing it, something like this:
SET @SQL='SELECT * FROM OPENQUERY(CAT_ASA, ''SELECT code FROM countries WHERE code=' + @Code + ''')'
EXEC sp_executesql @SQL
(CAT_ASA is the linked-server's name)
Then, i would use @@ROWCOUNT to determine if the code exists or not. But before this, a problem appears: sp_executesql is not allowed within a function (only extended procedures are allowed).
Does somebody know how to make what i want?? I prefer to avoid using temporary tables.
Thanks!
View 3 Replies
View Related
Feb 22, 2007
Hello,
I am trying to call a SQL Function in C#, as I want to get the value binded back to the column in a datagrid.
Any answers will be appreciated ...............Urgent.
:)
View 4 Replies
View Related
Feb 26, 2008
I'm calling a function with its full qualifiers.
That means servername.dbname.schemaname.functionname
I'm getting an error like this
Invalid column name 'servername'.
Is it possible to call a function name with its server name?
Thanks
Somu
View 4 Replies
View Related
Aug 30, 2006
Can I call a Stored Procedure in a function?
I created a SP:
CREATE PROCEDURE dbo.test_sp AS
BEGIN
select * from STOCK
END
GO
and I tried to create a function like that:
CREATE FUNCTION dbo.test_fn ()
RETURNS int AS
BEGIN
declare @a int
select @a = sto_id from dbo.test_sp
return @a
END
as seen the function uses the test_sp.but that gives syntax error. How can I do this?
thanks.
View 4 Replies
View Related
Jan 14, 2004
I'm documenting various server configurations such as linked and remote servers. Rather then clicking on each linked and remote server to view their properties I am trying to query sysservers. According to BOL, the column isremote is 1 if server is a remote server, else 0 if server is a linked server. When I query sysservers I see just the opposite. Is BOL wrong or am I missing something? Through Enterprise Manager I see 3 remote servers and 13 linked servers. syssservers shows 3 servers with isremote = 0 and 13 servers with isremote = 1.
Also, I understand remote servers are provided for backward compatibility. If that is the case, why is the default instance listed under remote server and not linked server. Also, why does replication list repl_distribution under remote server and in some cases the subscriber server is also listed under remote servers.
Thanks, Dave
View 2 Replies
View Related
Feb 19, 2003
I need to know if there's any way to call a VB function from within an SQl statement. We have text in Rich Text format in a database and need it converted to regular text before we are able to perform searches on the data. Maybe I can use a stored procedure to accomplish this conversion or to call a function that would do this? Any help would be appreciated.
View 2 Replies
View Related
Mar 10, 2004
Does anyone know if you can call an Access function from DTS?
I'm trying to delete data from an Access database, Compact the database, and load new data. My snag is calling a function in Access to compact the database.
Suggestions?
View 2 Replies
View Related
Jun 23, 2008
Hi
I have created a user function
the only way i can use this function is by specifying dbo. as prefix of the function
example
SELECT column1, dbo.MyFunc(column2)
FROM mytable
what i want is something like this
SELECT column1, MyFunc(column2)
FROM mytable
is this possible?
View 4 Replies
View Related
