Remote Procedure Call
Jul 25, 2002
I have a SQL2000(sp2) database (ServerA) and a SQL7(sp2) database(ServerB)
From the SQL2000 database I want to call a remote stored procedure on ServerB and store the result set in a table on ServerB.
E,g
insert TableA
exec ServerB...sp_GetStuff
If I try this by making serverB a remote server,
I get the following error message
Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user 'sa'.
If I make ServerB a Linked server, I get the error
Server: Msg 8501, Level 16, State 1, Line 1
MSDTC on server '' is unavailable.
Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
Now the MSDTC on ServerB won't start - It returns error 3221229574 to which the solution seems to be to reinstall MSDTC which looks like a very messy job with registry hacks and also the threat of reformatting the hard drive So I don't want to do this if possible
I really don't want a distibuted transaction anyway so I tried to stop the transaction being promoted using
SET REMOTE_PROC_TRANSACTIONS OFF
But this has no effect
Any ideas would be appreciated"
View 1 Replies
ADVERTISEMENT
Sep 15, 1998
What are the steps for setting up a remote stored procedure call?
Thanks in advance,
RM
View 4 Replies
View Related
Nov 27, 2001
Hi,
I was wondering is anyone can help me out on this one,
I want to run a query, but I need to reference a Database that exists on a different server. I am using SQL 6.5
Any suggestions would be welcomed,
Thanks a mill,
Fin
View 1 Replies
View Related
Nov 29, 2005
I want to call a windows based service running with remote objects listner from a C# Stored procedure. Any idea how to do that?
View 2 Replies
View Related
Sep 10, 2007
I'm calling a procedure on a remote Server (local SQL2005, remote SQL2005) and I need the return value.
Local:
declare @value int execute ('exec mbtest1.dbo.psybcis ?', @value OUTPUT) at [REMOTESQLSERVER] select @value
Remote:
create procedure [dbo].[psybcis] (@value int OUTPUT) as begin select @value = '13' end
I do not get a value in the OUTPUT variable - just NULL. Documentation says:
Execute a pass-through command against a linked server
{ EXEC | EXECUTE } ( { @string_variable | [ N ] 'command_string [ ? ] ' } [ + ...n ] [ { , { value | @variable [ OUTPUT ] } } [ ...n ] ] ) [ AS { LOGIN | USER } = ' name ' ] [ AT linked_server_name ] [;]
There is an OUTPUT parameter, but how does it work? Is my syntax wrong?
It works when using following syntax,
declare @value int exec [REMOTESQLSERVER].mbtest1.dbo.psybcis @value OUTPUT select @value
but I need to use this procedure call in a distributed transaction to a Sybase ASE server and this syntax is not allowed for cross-system-calls.
So first I want to get it work from SQL2005 to SQL2005.
View 2 Replies
View Related
May 8, 2015
We have server with Sql2008 R2 RTM 64 bit standard edition.We installed 2012 sp1 64 bit standard edition in win2012sp1 64 bit as side by side setup.After installation, i found the below error when i opened SSCM:
Remote Procedure Call Failed:
I fixed this error by renaming the MSC file.
At event log i am observing continuously below error:
Source:       Application Error   Event ID:     1000
Faulting application name: wmiprvse.exe, version: 6.3.9600.16384, time stamp: 0x5532e9c9
Faulting module name: svrenumapi100.dll, version: 2009.100.1600.1, time stamp: 0x4bb681be
Exception code: 0xc0000005
Fault offset: 0x00000000000514e6
Faulting process id: 0x17e8
Faulting application start time: 0x01d0873d9d37c382
Faulting application path: C:Windowssystem32wbemwmiprvse.exe
Faulting module path: C:Program FilesMicrosoft SQL Server100Sharedsvrenumapi100.dll
Report Id: afe88c1-330-11e4-80c4-008786656bda
Faulting package full name:Â
Faulting package-relative application
is there any other way to get rid of this error without installing a SP1 or later on sql 2008 R2 RTM version.
View 7 Replies
View Related
Jun 20, 2011
I can't access SQL Server 2008 R2 remotely on Windows 2008
1. TCP/IP Enabled for SQL Server Network Configuration Protocols, SQL Native Client 10.0 configuration clients, and SQL Native Client 10.0 configuration clients(32 bit)
2. Firewall disabled to make sure its not interferring with things.
I noticed the SQL Server Agent is Stopped. Not sure if this is the issue. When I try and turn this from disabled to Automatic or Manual, I get this error:
Remote procedure call failed (0x800706be)
It shouldn't be this difficult.
View 23 Replies
View Related
May 22, 2006
I've read the other posts related to this issue, but I'm just REALLY confused as to whats happening in my case. Like everyone else it was working fine in SQL 2000 but now in SQL 2005 there is an issue. I'm calling a stored procedure with parameters defined like this:
@action varchar(10),
@GLTransactionID int = NULL OUTPUT ,
@GLBatchID int = NULL ,
@GLAccountID int = NULL ,
@CurrencyID int = NULL ,
@LocalDebit decimal(28, 13) = NULL ,
@LocalCredit decimal(28, 13) = NULL ,
@BaseDebit decimal(28, 13) = NULL ,
@BaseCredit decimal(28, 13) = NULL ,
@TransID int =NULL,
@Description varchar(255) = NULL
I am calling this proc from VS.NET 2003 using the .Net SqlClient Data Povider (C#). I'm setting the values of the parameters like this:
cm.Parameters.Add("@action", "insert");
cm.Parameters.Add("@GLBatchID", _gLBatchID.DBValue);
cm.Parameters.Add("@GLAccountID", _gLAccountID.DBValue);
cm.Parameters.Add("@CurrencyID", _currencyID.DBValue);
cm.Parameters.Add("@LocalDebit", _localDebit.DBValue);
cm.Parameters.Add("@LocalCredit", _localCredit.DBValue);
cm.Parameters.Add("@BaseDebit", _baseDebit.DBValue);
cm.Parameters.Add("@BaseCredit", _baseCredit.DBValue);
cm.Parameters.Add("@TransID", _transID.DBValue);
cm.Parameters.Add("@Description", _description.DBValue);
When I execute the call to the stored proc I get this:
"The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 8 ("@BaseDebit"): The supplied value is not a valid instance of data type numeric. Check the source data for invalid values. An example of an invalid value is data of numeric type with scale greater than precision."
Using the VS.NET command window I then inspect that parameter to see what the heck is going on and get this:
?cm.Parameters["@BaseDebit"].SqlDbType
Decimal
?cm.Parameters["@BaseDebit"].Precision
0
?cm.Parameters["@BaseDebit"].Scale
22
?cm.Parameters["@BaseDebit"].DbType
Decimal
?cm.Parameters["@BaseDebit"].Value
1000000
[System.Decimal]: 1000000
So I set a decmial parameter to 1,000,000, that parameter in the DB is defined as decimal(28,13) so should fit no problem, but it seems the Sql data provider is confused and thinks 1,000,000 is decimal (0,22)???
View 5 Replies
View Related
Dec 20, 2006
Hello,
I have a bundling package that runs about 20 other packages. It has been working fine for a while but a couple of days ago it fail with the following message,
Error 0x800706BE while loading package file "D:PackagesToradSales.dtsx". The remote procedure call failed.
I´m running the SSIS packages in an 64-bit environment.
Thankful for help with this!
//Patrick
View 3 Replies
View Related
Aug 29, 2007
I'm calling this from another sql server....
I created a linked server... and want to restore database backups on the other box....
The restore script runs fine when ran locally but fails with the message below when calling it remotely
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Server: Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
CREATE PROCEDURE usp_restore_database_backups AS
RESTORE DATABASE BesMgmt
FROM DISK = 'D:MSSQLBACKUPBesMgmtBesMgmt_backup_device.bak '
WITH
--DBO_ONLY,
REPLACE,
--STANDBY = 'D:MSSQLDataBesMgmtundo_BesMgmt.ldf',
MOVE 'BesMgmt_data' TO 'D:MSSQLDataBesMgmt.mdf',
MOVE 'BesMgmt_log' TO 'D:MSSQLDataBesMgmt.ldf'
WAITFOR DELAY '00:00:05'
EXEC sp_dboption 'BesMgmt', 'single user', true
GO
I have set it to read only dbo only .... single user.... still get the same message....
does anyone have any suggestions....
View 6 Replies
View Related
Jun 25, 2007
I know that Unix and Windows don't mix very well in more than one way. However, the reality is that most places use both and it appears that SSIS (out of the box) has just about zero support for heterogeneous environments. Has anyone come up with a way (kludge will be gladly accepted) to execute a unix script on a remote unix host? Is anyone using any third-party product like MKS with SSIS?
On a similar note, can you use the execute process task to execute remote windows commands?
thanks
John
View 1 Replies
View Related
Dec 3, 2007
Hello,
is there any way to execute a SSIS package at the SQL Server programatically from a remote computer? (I use .net 2)
I don't need variables or return values, I only want to start the package. I know that it is easier to do that job with a webservice etc on the SQL Server but I cannot use such.
A method to call a package from a stored procedure would also help me a lot
Kind regards, _Rodney_
View 1 Replies
View Related
Nov 9, 2006
We have schedule process server, calling SSIS package via command line (see below) to physical SSIS server. Get message "insufficient for component" and package call bombs.
Facts:
1. schedule process server has Workstation tools / Clients / Connectivity for SSIS loaded
2. SSIS is 2005, SP1
What are we missing?
c:>dtexec /DTS "File SystemSalesDWgyp_dm_carrier" /SERVER BPATLQDDW /CONFIGFILE "\bpatlqddwd$SSISSalesDWgypdm.dtsconfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V
Error: 2006-11-09 10:43:34.94
Code: 0xC00470FE
Source: DTF - Xfer DW to DM DIM_CARRIER DTS.Pipeline
Description: The product level is insufficient for component "Slowly Changing Dimension" (289).
End Error
Error: 2006-11-09 10:43:34.94
Code: 0xC00470FE
Source: DTF - Xfer DW to DM DIM_CARRIER DTS.Pipeline
Description: The product level is insufficient for component "OLE DB Command" (775).
End Error
Warning: 2006-11-09 10:43:34.94
Code: 0x80019002
Source: gyp_dm_carrier
Description: The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
End Warning
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 10:43:32 AM
Finished: 10:43:34 AM
Elapsed: 2.383 seconds
View 6 Replies
View Related
Oct 5, 2007
Hi,
I have an integration solution that backs into two databases. Database A is custom and Database B is integrated from a COTS product. The manufacturer of B supplies a formal interface as stored procedures (and I intend to honor that contract by limiting my access to B through those stored procs).
Now, I have a search that is going to involve a subselect in the form of the following pseudocode:
select a,b from dbA.user.TableX where exists (select a from dbB.user.TableY)
I am trying to avoid creating 2 temporary cursors in the integration application and doing things in-memory (b/c of performance concerns).
So, my q is:
How can I make this happen within database A?
View 3 Replies
View Related
Apr 30, 2008
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.ostat_CDS_rpt47_2006 Script Date: 4/30/2008 2:25:41 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ostat_CDS_rpt47_2006]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ostat_CDS_rpt47_2006]
GO
/****** Object: Stored Procedure dbo.ostat_CDS_rpt47_2006 Script Date: 23/04/2008 11:26:14 ******/
/****** Object: Stored Procedure dbo.ostat_CDS_rpt47_2006 Script Date: 22/04/2008 14:35:33 ******/
/****** Object: Stored Procedure dbo.ostat_CDS_rpt47_2006 Script Date: 21/04/2008 11:46:08 ******/
/****** Object: Stored Procedure dbo.ostat_CDS_rpt47_2006 Script Date: 16/04/2008 17:18:04 ******/
/****** Object: Stored Procedure dbo.ostat_CDS_rpt47_2006 Script Date: 04/04/2008 12:44:34 ******/
/****** Object: Stored Procedure dbo.ostat_CDS_rpt47_2006 Script Date: 31/03/2008 12:03:31 ******/
/****** Object: Stored Procedure dbo.ostat_CDS_rpt47_2006 Script Date: 31/03/2008 11:11:07 ******/
/****** Object: Stored Procedure dbo.ostat_CDS_rpt47_2006 Script Date: 09/03/2008 14:31:30 ******/
-- ostat_CDS_rpt47_2006 2008,03,17,17
CREATE procedure ostat_CDS_rpt47_2006
@pYear int,
@pMonth int,
@pDay1 int,
@pDay2 int
as
begin
declare @sql varchar(4000)
declare @tb1 varchar(128)
declare @tb2 varchar(128)
set @tb1= 'margin..ncds0421'
set @tb2='Report.dbo.ob_apr08'
set @sql=
'
insert into '+@tb2+'
select yyyy,mm,dd,HH,sitecode,TrunkOut,SwitchCode,prefixCode,0 isCallback,
cast(callcost as decimal (10,5)) cost,count(*) attempt,
sum(case when connectflg=1 then 1 else 0 end) connected,sum(talktime) Talktime
from '+@tb1+'
where yyyy='+ltrim(str(@pYear))+'
and mm='+ltrim(Str(@pMonth)) +'
and dd between '+ltrim(Str(@pDay1))+' and '+ltrim(Str(@pDay2))+'
and talktime >0 and trunkin is null
group by yyyy,mm,dd,HH,sitecode,TrunkOut,SwitchCode,prefixCode,cast(callcost as decimal (10,5))
order by yyyy,mm,dd,HH,sitecode,TrunkOut,SwitchCode,prefixCode,cast(callcost as decimal (10,5))
'
--print (@sql)
exec (@sql)
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
View 2 Replies
View Related
Jul 20, 2005
Hi All,I want to catch the next MSSQL error in my SQL code with following continuecalculationsServer: Msg 17, Level 16, State 1, Line 1SQL Server does not exist or access denied.If REMOTE_SERVER_1 is inaccessible (as in (a) below) the executing of SQLwill not continue with (b) - I need the code in (b) to run despite whetherthe previous exec was successful or not - Any ideas?begin transaction(a) exec REMOTE_SERVER_1...bankinsert '1' , '1' , 1 , 0 , 0(b) print @@errorcommit transactionwhere REMOTE_SERVER_1 is link to server created byEXEC sp_addlinkedserver @server = 'REMOTE_SERVER_1', @srvproduct = '',@provider = 'SQLOLEDB', @datasrc = 'MYCOMP1', @catalog = 'mirror2'EXEC sp_addlinkedsrvlogin @rmtsrvname = 'REMOTE_SERVER_1', .....Exec sp_serveroption 'REMOTE_SERVER_1', 'data access', 'true'Exec sp_serveroption 'REMOTE_SERVER_1', 'rpc', 'true'Exec sp_serveroption 'REMOTE_SERVER_1', 'rpc out', 'true'Exec sp_serveroption 'REMOTE_SERVER_1', 'collation compatible', 'true'Any help will be greatly appreciated
View 1 Replies
View Related
Aug 1, 2007
I have a stored procedure that creates a normalized table from an existing denorm table. So I just need a simple way to call this SP from an aspx page. It would be good though to know how many records were effected, but this is not a requirement.
View 5 Replies
View Related
Aug 29, 2007
I have a stored procedure I created in SQL server 2005. Now I need to call the stored procedure in C#. Can someone help me out here? What is the C# code I need to call this stored procedure? I have never done this before and need some help.
CREATE PROCEDURE [dbo].[MarketCreate]
( @MarketCode nvarchar(20), @MarketName nvarchar(100), @LastUpdateDate nvarchar(2),)
ASINSERT INTO Market( MarketCode MarketName LastUpdateDate)VALUES( @MarketCode @MarketName @LastUpdateDate
)
View 5 Replies
View Related
Apr 26, 2008
dear all
i am using VS 2005 and sqlserver 2005....
i created one procedure [dbo].[Rptdailyactivity_jul](@cond Varchar(8000), @Type varchar(100),@groupname4fsumm varchar(100) ,@Fromdate4fsumm datetime, @todate4fsumm datetime)
i want to call this procedure from VS how???
i tried based on the following code...
usig the following code i can able to manipulate tables...but how i can able to call procedure based on this code??and how i will pass parameters???
************
Dim Conomandb As New System.Data.SqlClient.SqlConnection("Data Source=HJIOertsv;Initial Catalog=tr;Integrated Security=True") If Conomandb.State = 1 Then Conomandb.Close() Conomandb.Open()
Dim Comomandb1 As New System.Data.SqlClient.SqlCommand(strSQL, Conomandb) Dim rsOman1 As System.Data.SqlClient.SqlDataReader = Comomandb1.ExecuteReader()
If rsOman1.Read() Then fghghfghf
End If Comomandb1.Cancel() Comomandb1.Dispose() rsOman1.Close() ''*********************
View 4 Replies
View Related
Sep 22, 2004
I create a store procedure in sql, how do I call it?
thankss
View 2 Replies
View Related
Mar 31, 2006
I have created a stored procedure only with an insert statement in sql server 2005.
How can i call this stored procedure through code in ASP.NET page using vb.
i want to pass one parameter that comes from a text box in asp.net page.
my emailid is: g12garg@yahoo.co.in pls reply.
Thank you
Gaurav
View 2 Replies
View Related
Aug 7, 2001
Does anyone give me syntax for adding a bcp script within a stored procedure..I had done it once 3 yrs back does'nt seem to work now, and I do not know where I am going wrong??
Thanks
View 2 Replies
View Related
Aug 7, 2001
Hi, If I have a dll file and I know the interface of that dll file. How can I use stored procedure to call this dll file? Thank you.
View 1 Replies
View Related
Aug 7, 2001
Hi, If I have a dll file and I know the interface of that dll file. How can I use stored procedure to call this dll file? Thank you.
View 2 Replies
View Related
Nov 9, 2005
Greetings,
Even though this may be not right place with this issue I would like to try!
I facing with the problem “Object Variable or With Block variable not set” while I am trying to execute the stored procedure from Ms. Access form.
I need some help very badly or maybe a good sample of code that works in this issue is very welcome.
Many thanks in Advance
View 1 Replies
View Related
Aug 1, 2006
Hi,
I have two SQL SERVER server1 and server2. A server1's stored procedure should call server2's stored procedure. how can i do this.please explain me in detail.i am new to this concept.
VENKAT
View 4 Replies
View Related
Jul 4, 2006
hi,
i created a stored procedure and below is the code segment of it;
create Procedure test1
@price as varchar(50) output,
@table as varchar(50) = ''
AS
Declare @SQL_INS VarChar(1000)
SELECT @SQL_INS = 'select ['+@price+'] from ['+@table+']'
Exec (@SQL_INS)
Procedure gets 2 parameters, one of them is just INPUT parameter and the other one is both OUTPUT and INPUT. What i wanna do is to get the result set of this procedure to use in my application.
View 5 Replies
View Related
Dec 11, 2007
Hi, i wanna know if we can call a stored procedure from another one. If yes, what's the syntax?
Thanks
View 3 Replies
View Related
Mar 12, 2008
Hi, how to call the shrink_file proc from shrink_file_caller proc as below??
CREATE PROCEDURE [dbo].[Shrink_File]
@DBName VARCHAR(100),
@err INT OUTPUT
AS
...
GO
CREATE PROCEDURE [dbo].[Shrink_File_Caller]
@DBName VARCHAR(100),
@err INT OUTPUT
AS
SET NOCOUNT ON;
DECLARE @nsql NVARCHAR(4000);
SET @nsql = '' + @DBName + '.dbo.Shrink_File ''' + @DBName + ''', @err OUTPUT' ;
EXEC sp_executesql @nsql,N'@err INT OUTPUT', @err = @err OUTPUT;
GO
declare @err int;
EXEC Shrink_File_CALLER 'DATABASE1', @err
select @err as err;
I get the error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'DATABASE1'.
(1 row(s) affected)
View 9 Replies
View Related
Oct 6, 2007
Respected Sir/MAm
i am working on VC++ (visual studio2005 with sql200).i have created one stored procedure to insert data into table.
i want to call this strd procedure in the VC++ main.cpp file....
Could you please help me for the correct syntax code with example.
Thank You.
Upali
View 4 Replies
View Related
Apr 7, 2008
Hi there. My problem is: I have two stored procedures.
1. SELECT A FROM B
2. SELECT C FROM D WHERE A = EXEC First procedure
The meaning: First procedure gets some Id from B table. The second one gets a DataSet by this Id number. The problem is that when I getting an Id from first proc I use SELECT, than in the second one I use EXEC, and in the end, seconf procedure returns two DataSets. The first contains an Id from first procedure, second contains a valid DataSet. Therefore my application falls because it suppose that valid data in first DataSet. Hoow can I call to stored procedure from another stored procedure without creating two DataSets?
P.S. I already tried to use return instead of select in the first procedure. Same result.
Thank you
View 8 Replies
View Related
Nov 30, 2007
Hi I am trying to run a dll or exe from a procedure or trigger in the database.
I have tried EXEC master ..xp_cmdshell '\xx empdivConsoleApplication1.exe' and
use master exec dbo.StoredProcedure1
If I run the exe from explorer all is working, but from a trigger or procedure in the database nothing happens only executed successfully.
I loaded the assembly and procedure into the master database
Code Block
CREATE PROCEDURE [dbo].[ClassLibrary3] AS EXTERNAL NAME
[ClassLibrary3].[ClassLibrary3.Class1].[StoredProcedure1]
My C# code:
Code Block
public partial class StoredProcedures
{ [Microsoft.SqlServer.Server.SqlProcedure]
public static void StoredProcedure1()
{ //------first a select to get some data String prosjekter = "";
String connString = @"Data Source=serverOFFICESERVERS;Initial Catalog=WSS_Content;Integrated Security=SSPI";
SqlConnection conn = new SqlConnection(connString);
SqlCommand sqlcmd = new SqlCommand("Select nvarchar3 prosjekt from dbo.AllUserData where tp_listId = @listid", conn);
sqlcmd.Parameters.AddWithValue("@listid", "xxx"); try { conn.Open(); SqlDataReader r = sqlcmd.ExecuteReader();
while (r.Read())
{ prosjekter += "" + r["prosjekt"] + "" + "";
}
r.Close(); conn.Close();
} catch (Exception)
{ conn.Close(); }
//-----write some changes back to a program pocketForm pf = new pocketForm();
try {
pf.LoadFromFile("C:/temp/div/xxx.xpf");
string xmlstring = pf.FormXML;
int id, item, items; id = xmlstring.IndexOf("xxxxx");
item = xmlstring.IndexOf("", id);
items = xmlstring.IndexOf("", item);
xmlstring = xmlstring.Remove(item, items - item);
xmlstring = xmlstring.Insert(item, prosjekter);
pf.FormXML = xmlstring;
pf.StatusModifiedLocally = true;
WebService.Login("http://xxxx.no", "xx", string.Empty, string.Empty);
WebService.Save(pf);
} catch (Exception)
{
}
What am I missing here ? Please answer this as I have tried everything with no success. Regards Ivar
View 5 Replies
View Related
Jul 10, 2006
Hi,
I would like to trigger a DTS or a stored procedure from asp.net 1.1 BUT
I don't want to wait for it to finish. In fact the DTS/Storeproc calculates values into different tables.
Those values are not needed immediately. The calculation takes between 20 or 30 minutes.
Do you have any idea how to do it ?
Thanks
View 3 Replies
View Related
