I was going through comments on various posts to see if I have missed to answer any comments. I realized that there are quite a few times I have answered question which discuss about how to call stored procedure or query on linked server or another server. This is very detailed topic, I will keep it very simple.
I am making assumptions that remote server is already set up as linked server with proper permissions in application and network is arranged.
Method 1 : Remote Stored Procedure can be called as four part name:
Syntax:
EXEC [RemoteServer] .DatabaseName.DatabaseOwner.StoredProcedureName
‘Params’
Example:
EXEC [DEVSQL] .AdventureWorks.dbo.uspGetEmployeeManagers ’42′
Method 2 : Use Distributed Query functions (OpenQuery())
Syntax: SELECT *
FROM OPENQUERY([RemoteServer],DatabaseName.DatabaseOwner.StoredProcedureName)
Example: SELECT *
FROM OPENQUERY([DEVSQL],AdventureWorks.dbo.spAdmin_GetAllUsers)
Let me know if you want me write about above two methods in detail.
Reference : Pinal Dave (http://blog.SQLAuthority.com)
I would love to have you cover these in detail. This is very timely for me.
Hi,
Can I execute a remote stored procedure which is sql 2000 from a sql 2005 procedure.
Leo Thomas.
Yes with the help of Linked Server
can i edit view in data base
Yes
ALTER view your_view
.
.
The servers are set up as linked servers, server A is sql 2005 and the remote server B is sql 2000.
The procedure has to be run on B, it gets values from a procedure running on A and returns a result to the proc on A.
I used the four part naming convention.
When i execute the following
EXEC @result = Dino.Prod.[dbo].[Proc_MCC_Payroll] @rows, @rows_retran;
I get an error Server ‘Dino’ is not configured for RPC.
both servers run on windows 2003 server enterprise edition SP2
How do I fix this?
Make sure that Remote Procedure Call is started at the Server
On your Link Servers Properties you have to configure the Server options Tab and Set the RPC = True and RPC OUT = True.
Do we need admin rights for this?
Remote Procedure Call should be enabled
Is there a way to execute the call to the remote stored procedure dynamically where the remote stored procedure accespts 2 input parameter and one output parameter?
Remote Stored procedure Header
Create Procedure usp_BillBroadcast (
@intFileFormat int,
@intFileType int,
@intBroadcast int out)
Since I cannot hardcode the link server name in my code, is there any way that I may be able to call the above stored procedure? Can you please cover it in detail?
Your help is greatly appreciated.
You can use dynamic SQL
Make sure you read this
http://www.sommarskog.se/dynamic_sql.html
About the error “is not configured for RPC”. We have fix it, chaning ( on local server), linked server configuration to remote server setting “outbound RPC” as True.
We use:
EXECUTE (‘ select …etc… ‘ ) at
To Pinal Dave: Tanks, This blog is very interesting. I’m learning a lot.
Sorry, Problems with puntuaction shadown command line on my post. This is complete:
About the error “is not configured for RPC”: We have fix it, chaning ( on local server), linked server configuration to remote server setting “outbound RPC” as True.
We use:
EXECUTE (’ select …etc… ‘ ) at remoteserver
To Pinal Dave: Tanks, This blog is very interesting. I’m learning a lot.
Have you ever come across a situation in SQL 2005 where a query in the form of a stored proc, comes across as a llinked server call and gets hung, often causing it to be a lead block?
I have a few sprocs that are called frequently, and every so often they become hung, blocking other processes requiring the spid to be killed manually…Because we are a 24/7 shop we have gone as far as setting up jobs that look for these spids and KILL them when they meet the criteria that we have identified.
Any ideas what could be causing this? Anyone else experience this in their org? Your thoughts & experiences are very much appreciated!
C~
To follow up on RAM’s question, if you are using 2005, setup a linked server and a synonym. The synonym will allow you to “hard code” the server name and database instance name once. The synonym will act as an alias to the remote database. If you change the synonym to point somewhere else, any code referencing the synonym requires no changes. I only wish 2000 had synonym…
I am thankful for sites like this that really try to help. I have been struggling to call a stored proc from a linked server when the calling server is SQL 2005 and the linked server is SQL 2000. After a lot of reading and experimenting I found the solution, which I have laid out in simple steps below. Good Luck!
– Add a linked server
EXEC sp_addlinkedserver
@server=’BOB’,
@srvproduct=”,
@provider=’SQLOLEDB’,
@datasrc=’SERVER’,
@catalog = ‘Northwind’
– get a list of linked servers
EXEC sp_linkedservers
– add linked server login (this user and password has already been created on linked server)
EXEC sp_addlinkedsrvlogin ‘BOB’, ‘false’, NULL, ‘userlogin’, ‘password’
EXEC sp_serveroption @server=’BOB’, @optname = ‘rpc’, @optvalue=’true’
EXEC sp_serveroption @server=’BOB’, @optname=’rpc out’, @optvalue=’true’
–see all linked servers and status
EXEC sp_helpserver
– perform query against linked server
SELECT * FROM OPENQUERY (BOB, ‘SELECT * FROM customers’)
– execute stored proc on Linked Server
declare @CustomerID char(50)
EXEC BOB.Northwind.dbo.CustOrderHist ‘ALFKI’
– drop linked server logins (must be done prior to droping linked server)
EXEC sp_droplinkedsrvlogin ‘BOB’, NULL
– drop linked server
EXEC sp_dropserver ‘BOB’
I would like to run the following on a server that is linked in.
backup database @sourcedb to disk = @backup_file
Can you help with the syntax. When I specify
[remote server]…backup datebase [remotedb] to disk = [file]
Thanks
EXEC(‘backup database ‘+@sourcedb+’ to disk = ”’+@backup_file+””)
Tony,
You can’t use a linked server to address the database you want to backup.
You can use ” sp_executesql ” to execute an SQL string on the remote server
EXEC .master.dbo.sp_executesql N’BACKUP DATABASE pubs TO DISK = ”c:\pubs.bak”’
Thanks chema! You helped me solve a big problem I was having!
Hi, problem is re Linked Servers and rpc calls.
Using SQL Server 7 (A) rpc to SQL Server 2005 (B).
A is setup with a linked server B and can replicate to B – NO problems. (The replication was setup with sa connection)
I have a linked server setup with a standard user and appropriate access rights. Below 1 query works the other fails. Any ideas? It used to work until I had to recreate everything due to firewall failure.
Query fails
1. select * from SERVERNAME.dbname.dbo.tablename
> Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider ‘SQLOLEDB’ reported an error. The provider did not give any information about the error.
Query Suceeds
2. select * from openquery(SERVERNAME,’select * from tablename’)
I have also created said same linked server on another SQL server 7 (C) db and both queries work NO PROBLEMS. It’s hard to crack the problem. Any ideas?
For extra info. using Patrick example above. All works for OpenQuery but same error when executing
select * from SERVERNAME.dbname.dbo.tablename
Sorry fellas you took too long. ;-)
to solve the problem go the registry
(HKLM\Software\Microsoft\Mssqlserver\providers\SQLOLEDB ) and make sure all
except “AllowInProcess” have a value of 0.
Dave
I have an intresting situation whereby a stored procedure called via a 2005 linked server connection to an SQL 2000 database fails to complete but does not raise an error. The execution of the Stored procedure is aborted and the calling SQL procedure continues from the next statement.
If I run the stored procedure in management studio in completes correctly.
If I run the SQL job that calls the stored procedure it aborts without error as previously mentioned.
Finally this behaviour is unpredictable in that this problem has only started to happen recently. No changes have been made to any of the stored procedures
ewan, sounds like user access rights to me. Is the SQL job ran as “sa” or another user?
Since this thread is the closest I’ve found to my problem I was hoping someone may be able to help me with the folllowing issue:
I have a stored procedure on a SQL 2005 server exec’ing a stored procedure on a SQL 2000 server through a linked server. I receive the following error on this exec:
The attempt by the provider to pass remote stored procedure parameters to remote server ‘TEMP_TOPTAGGING’ failed. Verify that the number of parameters, the order, and the values passed are correct.
This exact same scenario works correctly going from SQL 2005 to SQL 2005…just not from SQL 2005 to 2000.
I have verified that the parameters, orders and values are correct.
Has anyone had a similar issue?
Thanks,
Bill
As soon as I ask the question I figure out what’s causing the problem.
We were using the 2005 varchar(max) variable to a text variable parameter on 2000. Changed it to varchar(8000) and it works correctly.
do we require system admin rigths to create stored procedures in sql 2005.. please answer my question
I am trying to run the following stored procedure using link server. My other non parameter SP works fine with this following ways but the parameter SP gives me the following error:
DECLARE @vTechnicianName Varchar(25)
DECLARE @cmd Varchar(8000)
SET @vTechnicianName = ‘AMY GAMBLE’
SET @CMD = ‘AGY_REC_RPT.AA_Test.dbo.AA_Legal_Agents_Report ‘+””+@vTechnicianName+””
Print @cmd
EXEC @CMD
Error:
AGY_REC_RPT.AA_Test.dbo.AA_Legal_Agents_Report ‘AMY GAMBLE’
OLE DB provider “MSDASQL” for linked server “AGY_REC_RPT” returned message “[Microsoft][SQL Native Client][SQL Server]Could not find stored procedure ‘AA_Test.dbo.AA_Legal_Agents_Report ‘AMY GAMBLE”.”.
Msg 7212, Level 17, State 1, Line 8
Could not execute procedure ‘AA_Legal_Agents_Report ‘AMY GAMBLE” on remote server ‘AGY_REC_RPT’.
Is there a limit to the number of linked servers that can be attached to a single SQL 2005 Server?
Read about Maximum Capacity Specifications in SQL Server help file
Please let me know what i have to do in the case of linked server is not created…I have the 100 server..i want to create the job on 100 server which will call to the 1 server.so it does n’t make sense to create the linked server for all 100 servers..
@Manish,
In SQL Server 2000 and 2005, we have something called Master and Target Server.
Right click on SQL Server Agent Properties -> Make this Server Master or Target.
Make a Master Server and then also define what will be target servers for this master server.
Use of this is, if you have a job on Master server and you want this to run in 100 different target servers, you can, you just need to create this job only on master Server.
I don’t know what type of job you are running what script does it contain, so I can’t really tell you if this has to be a linked server for in order to work.
Please explore more about this Master Server and Target Server, its a very good concept.
After you sucessfully implement this, please share here how did you do this, it will be helpful for all of us.
Regards,
IM.
Hi, I have a Sybase linked server on SQL 2005, I need execute a store procedures, when the sp has no parameters all work fine, but with parameters bring up the next error:
Mens. 7357, Nivel 16, Estado 2, Línea 1
Cannot process the object “exec finmes..sp_testsql ‘a’”. The OLE DB provider “MSDASQL” for linked server “SPC” indicates that either the object has no columns or the current user does not have permissions on that object.
So, this is the query:
SELECT * FROM OPENQUERY(SLS, ‘exec database..sp_testsql ”a”’)
Thanks a lot.
Hello, I have a Sybase linked server toward SQL 2005 and I need execute a stored procedure with parameters. If I try the sp without paramaters all works fine, but when I add the parameters bring me up the next error:
Mens. 7357, Nivel 16, Estado 2, Línea 1
Cannot process the object “exec finmes..sp_testsql ‘a’”. The OLE DB provider “MSDASQL” for linked server “SPC” indicates that either the object has no columns or the current user does not have permissions on that object.
This is my sql, executed in SQL:
SELECT * FROM OPENQUERY(SPC, ‘exec sybasedb..sp_testsql ”a”’)
This is my sp created in Sybase:
create procedure sp_testsql @test char(1)
as
select @test + ‘I AM IN SYBASE’
Thanks a lot for help.
how can i call a stored procedure (which receives parameters and returns a value) from another stored proedure
thanks
Luis Alberto,
I am in a similar situation and I get the same error message when i run a insert query using OPENQUERY.
Were you able to solve your problem. I would really appreciate it if you can post it here.
Thanks
[...] Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements. With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data. Stored Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server. (Read More Here) [...]
Luis,
I’ve a problem with a table which is getting wrong data and i need to create a trigger to track it, but I want to know which stored procedures is sending bad data and I tried with @@PROCID and it returns the trigger ID. How can I get the ID of the stored procedure filling the table?
Thanks
Y
At the end of each procedure, use a variable that gets assigned
set @var=@@PROCID
You can return that variable to know the procid
I created a utility database (UTIL) on the same server as my test database (TEST). The production database (PROD) is on another server.
I had hoped that I could put procedures, functions, etc. on the UTIL server and run them remotely from TEST and PROD.
However, it looks like the procedure looks at its own database and not on the one where the call originated.
Example:
Table1 resides on DEV
Table2 resides on UTIL
Proc1Sp resides on UTIL and has the following command:
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @table_name
I connect to DEV and execute the proc on UTIL as follows:
UTIL.dbo.Proc1Sp Table1 – It cannot find Table1.
UTIL.dbo.Proc1Sp Table2 – I get a resultset.
Is my thinking wrong? Does the procedure only look at the database where it resides? If I have many databases and want to build a collection of utilities, do I have to copy them to all the databases or can I put them all in one place?
Thanks… mpv
Thank you Patrick for your great response on 4th April 2008. I was having terrible trouble trying to get a linked server to work on SQL 2005 from SQL 2000. I was creating it in Enterprise Manager to no avail but when I created it as you said in your steps it works perfectly! Thanks again!
Hello Pinal,
You sight is wonderful. I’ve found so much information here. I have a question for you.
Our company has 13 SQL Server Databases. Currently each database has a duplicate copies of all of our Stored Procedures (SP). I would like to centralize these SP into one database then perhaps add the database name to the arguments to get data from the other database. This central database would not contain the tables just the SP while the other database would contain the data but not the SP.
Example:
CentralDB.SP1(‘TestDB’, …)
{…
select * from Table … table does not exist in this database
}
called from
TestDB.SP1(…) … table exists in this database
Hi Pinal,
Some One told me that “The new table created with the SELECT INTO clause in linked servers does not support 4 part names.
But I am not sure, Do we have this thing documented any where ?
As in BOl they have just mentioned that SELECT INTO is supported in linked servers as well.
Thank You,
Mithalesh
Hiiii
I want to execute one SP available at Remote Server.
when i am executing it as you told:
Execute [192.168.1.82\SQL2005].[PCFileParser].[dbo].[usp_RemoteCalling_Test]
It is giving Error:
Msg 7202, Level 11, State 2, Line 1
Could not find server ’192.168.1.82\SQL2005′ in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
Please solve my problem as soon as possible.
My client don’t want to use linked Server, so can’t use OPENQUERY clause.
Hi Pinal,
I am looking for a T-SQL script that automatically script out all the linked server. is it possible in SQL server. Please guide me.
Regards
Priyaa
Yes Priya, if you go object explorer details and select the Linked Servers that you want to script –> Right Click –> Script to New Query Window and you will have all Linked Servers in a new query window. This applies to SQL 2005 and onwards. for SQL 2000, there is a script that scripts all Linked Servers.
hi how i call Stored Procedure in asp.net
YRAIS,
You may want to have each stored-procedure store a static value or some sort of identifier into one of the fields of the table. That way that field in the table holds the source of the data. When you run a query you see where the data came from.
RJM
Hi,
I Have the same problem reported by Luis Alberto, calling stored procedures with parameters.
Please If you have some notice post it.
Thanks.
Query with parameters
—————————-
SELECT * FROM OPENQUERY([ServerInstance],’exec Test.dbo.USP_Process ”1234567890123456”’)
Dynamically pass parameters
=====================
declare @sql varchar(1000),@param varchar(16)
set @param=’1234567890123456′
set @sql=”” +’exec Test.dbo.USP_Process ‘ + ””” +@param+ ””””
EXEC (‘SELECT * FROM OPENQUERY([ServerInstance],’+@sql+’)')
Also,you can insert the results into a table.
Hope this helps…
Hi Expert
i m learining to sql server2005 initialy . so i have one doute in store procedure how can i use the month and year in store procedure
regrds by
karthik
select month(getdate())
select year(getdate())
Hello all. I am a newbee to Link Servers and this blog is coming pretty handy to get started, so thanks everyone for the info.
Up to the moment we have been using one sql server containing all the DB of our proyects but now we are changing to linked servers. The problem is that we are going to have to change all the Stored Procedures of all our databases, which really is going to be a lot of work, so I am trying to find a way of doing this that doesn´t imply changing by hand every single SP, considering also that the location of a particular database may change from time to time and we´ll have to do the same operation more times.
Any ideas would be nice,
Thanks.
when changing all the stored procedures I meant where before we had DB.Table we´ll obiously we´ll have to change it toDB.Table we´ll have to change it to Server.DB.Table
PayDogg,
If you have your code in T-SQL you should be able to use a search and replace utility.
RJM
hello,
i am trying to call a mysql 5.1.26 procedure from sql server 2000 procedure.
the server is linked properly
when i do a select statement using openquery for just getting the remote data it works.
but i am getting this error..
my remote server name is TOMySql
and the schema belongs to root.
Could not execute procedure ‘myproc’ on remote server ‘TOMySql’.
[OLE/DB provider returned message: [MySQL][ODBC 3.51 Driver][mysqld-5.1.29-rc-community]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘?=call “myschema”.”myproc”;1′ at line 1]
the sql server 2000 procedure is
alter proc mydistproc
–@name varchar(50),
–@address varchar(50)
as
begin
exec TOMySql..myschema.myproc
–EXEC TOMySql.root.myschema.myproc()
–select * from OPENQUERY(TOMySql, ‘call myschema.myproc’)
–exec(OPENQUERY(TOMySql, myschema.myproc))
–insert into OPENQUERY(TOMySql, ‘SELECT name, address FROM myschema.temp’)
–select @name, @address
–ROWLOCK
end
————————————————————————
the my sql procedure is
DELIMITER $$
DROP PROCEDURE IF EXISTS `myproc` $$
CREATE DEFINER=`root`@`%` PROCEDURE `myproc`()
BEGIN
select ‘hi’ as greetings;
END $$
DELIMITER ;
———————————————————————-
Kindly help
i dont know where i am going wrong..
Regards
Nitesh
Hi Nitesh
My name is Ludwig and I’m getting exactly the same error from MS Server 2008 when calling a stored procedure (SP) on MySQL 5.2.34.
I can’t seem to call the SP directly:
EXEC ERA.fswd_eras.dbo.RunMySP;
ERA is the linked server and like you I can also use the Openquery to perform selects, inserts and updates, but openquery does not support calling functions on MySQL.
Have you managed to solve the problem as yet?
Regards
Ludwig
Thanks to “SSC Journeyman”, I’ve got an answer:
EXEC(‘CALL RunMySP’) AT ERA.
And it works!
Hi all,
I created a linked server using below syntax
– Stored Procedure to Create Linked Server
EXEC sp_addlinkedserver
@server = ‘TEST1′,
@srvproduct = ‘SQLServer OLEDB Provider’,
@provider = ‘SQLOLEDB’,
@datasrc = ’192.168.20.45\ls’
It is successful
I am trying to execute stored procedure created in ”192.168.20.45\ls”
EXEC TEST1.IELS_SIT_SUB_7Jan.dbo.Spu_Poc_ShowHearingDetails ‘null’,'null’
but it is throwing below error
Msg 7357, Level 16, State 2, Line 1
Cannot process the object “exec IELS_SIT_SUB_7Jan.dbo.sp_test”. The OLE DB provider “SQLNCLI” for linked server “TEST1″ indicates that either the object has no columns or the current user does not have permissions on that object.
Please some one help me in this.
Thanks in advance
For executing a stored procedure that requires parameters and it’s on a linked server I suggest you try the AT version of exec … For example:
exec (‘exec IELS_SIT_SUB_7Jan.dbo.Spu_Poc_ShowHearingDetails null, null’) AT TEST1
Details in SQL Server BOL. Hope this helps. Regards Nik.
How to pass dynamic parameters to stored procedure
Using a dynamic SQL
Can you give us some sample values?
need help, i am using sql server 2005, i have to schedule a job in which i have to call a stored procedure that is made in Oracle 10g….is this possible? ho0w can we do this?
Can I run a remote stored procedure in SQL job, both database servers are sql server 2005
If the SQL Server service account does not have execute rights on remote stored procedure than create a Credential using the user who has rights, then on that credential create a Proxy and use that proxy in agent job step.
Regards,
Pinal Dave
Hi.
I am passing table value paramter from one server to another server but I am getting the following error.
valued parameters are not allowed in remote calls between servers.
I am using sql server 2008 so do you have any solution for this ??
@Shailesh,
Is it possible to share SQL you are using. It would be helpful to resolve this issue.
Can;t you create Type data type in remote server and use it locally.
~IM.
I hope this thread may still be alive? I am adding to an application that uses SQl2005. I have used Dynamic SQL in a Stored Procedure to retrieve data. Because of the way the application is structured I need to select the result set into a View. I have tried many ways to do this – including OpenRowSet (needs Ad Hoc Queries on (which they are not) – and accessing the Stored Procedure via a Linked Server using OpenQuery – which returns errors
SELECT * FROM OPENQUERY([SERVER-1].Test1DB.dbo.[SP_RFTA])
gives error:
Incorrect syntax near ‘.’.
any thoughts – and thank you in advance,
PJ
Hi pinal,
I am working with sql server 2005. My company db is very huge. so i am planning to divide the db with help of linked server concept. Previous 7 years datas put into separate machine and forcoming datas are put into separate machine.so the we can fetch the data very fast manner. i don’t know how to do.. plz guide me..
Thanks,
Vinoth
I have a weird one…
what if the column is named something like [name.name]?
I get the following error:
Msg 7357, Level 16, State 2, Line 1
Cannot process the object “”MyDB”.”dbo”.”test”". The OLE DB provider “SQLNCLI” for linked server “MylinkServer” indicates that either the object has no columns or the current user does not have permissions on that object.
Table name is TEST
Column name is [Ship.Address1]
I can get around this by creating a view and renaming my columns as Address1, but is there another solution?
try with :-
select ‘['+ column name + ']‘
it should work
how can i call a stored proc?
please send the answer to my Email.
thanks.
EXEC your_procedure
Hi Pinal Dave,
My name is Metin Gümüş and I m from Turkiye. I m really proud of learning you and letting us know about your knowledge.
Thanks Dave.
can anyone help us to execute mysql stored procedure from mssql trigger. or please provide some useful link which describes how to do it.
Regards
V.S.Jain
You need to set up mysql as a linked server from where you can access mysql procedure
Hi,
Please tell me how to access multiple databases in a instance using single linked server.
Thanks in advance
Venki
Hi,
How can I call a remote Oracle 10g procedure from SQL Server 2000?
We tried the following sentences:
exec
[LINKSERVER].Chema.Package.Procedure param1, param2
We received the following error message:
[OLE/DB provider returned message: One or more errors occurred during processing of command.]
Msg 7212, Level 17, State 1, Line 1
Could not execute procedure ‘XXXX’ on remote server ‘LINKSERVER’.
We also tried testing teh LINKSERVER with SELECT * FROM OpenQUERY (LINKSERVER, ‘SELECT * FROM TABLE’) and it worked fine. We know that we cannot use OPENQUERY for execute procedures.
Please, could you help us?
Thanks in advance
Regards,
manager
Hi,
in Oracle 10g one out parameter will be defined as SYS_REFCURSOR. when the parameters are passed to stored procedure, OUT parameter is not taken care.
i am also facing the same problem. as described by you. if you find the answer please post it to site.
Thanks
Regards,
Mohan
how can i call a SP through below way. And where i have to mention the parameters?
SELECT * FROM OPENQUERY
([DEVSQL],AdventureWorks.dbo.spAdmin_GetAllUsers)
SELECT * FROM OPENQUERY
([DEVSQL],AdventureWorks.dbo.spAdmin_GetAllUsers ‘parameter value’)
Hey madhivanan
I got an error in this syntax.
My statement is like this :
([DEVSQL],AdventureWorks.dbo.spAdmin_GetAllUsers ‘parameter value’)
it gives error like : “Incorrect syntax near ‘AdventureWorks’.”
Hi,
I want to create the Linked server for IBM Informix.
Could you provide the steps for the same.
Thanks in advance.
Using method #1, is there a way to keep the ‘remote server’ name variable ? It will really help lot of issues. I am using method #1 to insert record into Oracle from SQL Server using linked server. The Oracle table has a time stamp column and this is the only way I could actually do so. Issue is that Linked Server (remote server) could be variable. Any suggestions ??
Thanks in advance.
Here is the SQL syntax that I am working with:
declare @currentDateToUse datetime ;
set @currentDateToUse = convert ( datetime, getdate());
Exec (‘ insert into SchemaName.TableName( col1, col2 ) values (?,?)’, @var1, @currentDateToUse ) at OracleLinkedServer1
Hello
I want to call linked server stored procedure in trigger.
But it’ll be given me an error.
Error :
Msg 7391, The operation could not be performed because OLE DB provider “SQLNCLI10″ for linked server “ServerName” was unable to begin a distributed transaction.
Thanks in advance
Hi,
I am getting the following error when I am trying to connect to a remote database via stored proc.
Login failed for user(null) Not a trusted SQL Connection.
I am trying to access a remote database.
I did the following settings on the machine:
1)I added the server which I am trying to coonect in the Linkedserver using sp_addlinkedserver
2)I checked the ‘Enforced Distriuted transactions’ in Connection tab is sqlserver properties.
3)I am using sql2000 and installed the service pack 4 on tht machine.
4)I have also set the allow inbound,outbound connections in the Component services in Administrative tools of Control panel.
Do I need to do something more?
I have one stored procedure in one database and its inserting values to three tables simultaneously. Two tables are in the same database and another one in different DB but in the same server.
When i was trying to insert into table of second database it was not inserting and i used OpenDatasource() for inserting that.
But what happens now is; i am missing some data as it takes more time for executing the stored procedure. This procedure have to execute 4 times in every second.
Please suggest me a solution.
Regards,
Shenu Lal
Hi ALL,
Can link server be setup between sql server 2005 and sql server 2008. The data will be fetched from sql server 2008 database. The link server is defined in sql server 2005. I get following error:
OLE DB provider “SQLNCLI” for linked server “xxxx” returned message “Login timeout expired”.
OLE DB provider “SQLNCLI” for linked server “xxxx” returned message “An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.”.
Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53].
Thanks a lot,
Amit
[...] Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server databases using T-SQL Statements. With a linked server, you can create very clean, easy–to-follow SQL statements that allow remote data to be retrieved, joined and combined with local data. Stored Procedures sp_addlinkedserver, sp_addlinkedsrvlogin will be used to add new Linked Server. (Read more here) [...]
I’m a newbie and just starting to use linked servers. I created the linked servers and have no problem calling stored procedures on the linked servers. I need to be able to call the same stored procedure on all the linked servers setup (a little less than 100). I am using a vbscript to execute the stored procedure from one server. I need to be able to call the stored procedure when needed for the needed linked server. My question is…Do I need to create the stored procedure on each individual linked server database in order to call it from a ‘centralized’ program/server? Thanks
Hello, when I try that code, i get an error saying : invalid outside procedure.
Any ideas what I should do?
Thank You So Much, Its really good one …
Question: I have a main database and a sattelite database in a SQL 2008 linked configuration. I’m running the following query in my sattelite database and want to know where would the actual query execute?
select C1,C2,C3, A1,A2,A3, A4 from MainDatabase..dbo.Customers inner join Accounts on Customers.id = Accounts.CustomerId and Customers.lastName=’Smith’
Will data first be copied from the MainDatabase to my local database and then the Join/Search Will occurr? Or the Whole Query will be shipped to the Main Database for execution.
Hi,Pinal,
Using spAddLinkServer i can add remote machine and execute remote sql commands.
I have few questions:
1) when it adds the remote server , all the databases of the remote server are visible.Can’t it be only the required database?
2) In some cases the command allows to add the link server . while exploring the catalog it shows error: login fail for user sa. Error code is:18456
Guys!!
Is there any way to copy data of one or more tables from your local database to a remote database , without using LinkedServer Concept.
Use SSIS
I need to copy data of one or more table from a local SQL database to a hosted server database. Most of the data in the local database is copied to the live one, though optimisations are made to reduce the amount of actual data required to be sent.
What is the best way of sending this data from one database to the other?
Hi,
Pinal
Can i use Transactional statements while using linked server concept?
hi,
got a problem on how to confine server name into a local variable.
Example:
declare @Server varchar(100),
@Database varchar(100)
SET @Server = ‘server’
SET @Database = ‘Database’
Exec [@Server].[@Database].[dbo].[sp_Storeprocedure]
with example above, i’ve got error 7202
any idea how to handle this?
thanx
I have a problem where we are migrating from Sybase to SQL Server. I need to be able to invoke a stored procedure in Sybase from Sql Server. The issue is that these STPs have output paramteters. I have a link set up but can not figure out how I can get to the output parameters. I ended up basically building lot of code in the OPENQUERY but while it seems to run it also does not work
execute (‘use tord_ord
declare @priceed datetime,
@error_code int,
@error_message varchar(255),
@a char(10),
@b varchar(10),
@c varchar(10),
@d varchar(10),
@e int,
@f char(2),
@x varchar(300)
set @priceed = CAST(”2007-05-08 12:35:29.123” AS datetime)
set @a =”1234567890”
set @b = ”1234567890”
set @c = ”1234567890”
set @e = -1 /* should cause error with TN set below”
set @d = ”1234567890”
set @f = ”TN”
set @error_code = 101010
set @error_message = ”startng mmessage”
exec STP_SIP_OA_close @a,@b,@c,@d,@e,@f,@priceed,@error_code,@error_message
set @x = rtrim(cast(@error_code as char(20)))
select @error_message as error_message
select @error_code as error_code
select @a
select @x
select @f
select @a
‘) at WPOALINK
And though no errors were reported I do not think it really invoked the stored procedure the values of error_code and error_message stayed the same. One of the first things the STP does is set them to 0 and an empty string.
hi madhivanan ,is there a way to call remote procedure in hibernate.is it can be done.can you give some suggestions
Hi Pinal,
I want in detail about Linked Servers in sql server 2008. I want to connect Oracle 10g in SQL Server 2008. Pls help me in this issue.
do you guys know how to execute a sotred procedure (in a linked server, Sybase this time) which needs an output parameter with openquery o any other option? I´m trying this:
sp in sybase:
CREATE PROCEDURE dbo.usp_genera_saldo (@anio smallint, @periodo varchar(1), @cuenta int, @cve_con int, @importe_pagar decimal(30,2), @fecha_limpago datetime, @clave_carrera int, @opcion smallint, @salida int output )…….. end
calling it in sql server :
execute (‘Execute usp_genera_saldo (?,?,?,?,?,?,?,?,?)’ ,2012,’0′,171394,4,100,’2012/06/22′,904000,3,) AT SYBASE1
or
SELECT * FROM OPENQUERY(SYBASE1,’{EXECUTE usp_genera_saldo({ 2012, ”0”, 171394, 4,100,”2012/06/22”,904000,3,{? , ReturnVal})’)
or
declare @r int
EXECUTE ( ‘begin usp_genera_saldo (?,?,?,?,?,?,?,?,?); end;’, 2012, ’0′, 171394, 4,100,’2012/06/22′,904000,3, @r output) AT SYBASE1;
nothing works
Can i create Store Procedure or Alter SP in linked Server? Please , can i get an example/
hi; I am trying to execute a local stored procedure on multiple linked servers.
how can i do it?
thanks;
You need to add the server which has that procedure as linked server in other servers and using four part name, you can execute the procedure
Hi all,
I have a stored proc on server A, have linking from server B to server A,
wanted to view server A text using sp_helptext as don’t have access to serverA.
Ho to to do this..
Select * from opequery(serverA, ‘sp_helptext spname’)
not working throwing error…invalid object name #comments( this is an temp table whcih is created in sp_helptext procedure )
Any idea.?
Thanks,
RK