Hi All,
I have a requirement of calling a Executable from SQL Server.
I know we can use -
EXEC @result = Master..xp_cmdshell @<command_string>
However I want to get the return value (int) from the executable.
xp_cmdshell always returns 0 or 1. How can the executable return code
say 99 be obtained in SQL Server?
Thanks & Regards,
Chandra Mohan
SQL Server 2012 Standard SP 1.
Stored procedure A calls another stored procedure B. Rowcount is set properly in called procedure B, but does not seem to return it to calling procedure A. Otherwise the two stored procedures are working correctly. Here is the relevant code from the calling procedure A:
declare @NumBufferManagerRows int = 0;
exec persist.LoadBufferManager @StartTicks, @EndTicks, @TimeDiff, @NumBufferManagerRows;
print 'BufferManagerRows';
print @NumBufferManagerRows;
Print statement prints @NumBufferManagerRows as 0.
Here is the called stored procedure B:
CREATE PROCEDURE [persist].[LoadBufferManager]
-- Add the parameters for the stored procedure here
@StartTicks bigint,
@EndTicks bigint,
@TimeDiff decimal(9,2),
@NumRows int OUTPUT
[Code] ...
Hi EveryBody, I need to call a Stored Procedure in a UDF.This input of SP would be a string and in turn that output Of SP would also be a string.Also i need to store the output of SP in a Calling UDF.Is it possible if Yes Please tell me How can i make It.Please help.Thanks!!!Dheeraj
View 3 Replies View Relatedhi,
i am new to SQL. i am trying to retrieve a set of data(i.e. set of rows which satisfies my condition given in the select statement). i want to output this recordset to my calling program i.e C#.net. how could i do this. i was adviced to use temp tables. but i am not sure how to do it. can any one say that using the temp table is the correct way. if so can u give me some sample code.
my proble is
Table name: ProductDetails Table
It has following columns
ProductID Name UnitPrice Category Stock
when i give a category value from front end it should bring me the rows which comes under the given category. there will be many rows under this category. how could i send this set of rows back to my calling program.
regards,
Johny
Hi again, and so soon...Having just solved my previous issue, I am now trying to call this stored proc from my page.aspx.vb code. Firstly the stored proc looks like this:----------- ALTER PROCEDURE dbo.CountTEstAS SET NOCOUNT ON DECLARE @sql nvarchar(100); DECLARE @recCount int; DECLARE @parms nvarchar(100); SET @sql = 'SELECT @recCount1 = COUNT(*) FROM Pool 'SET @parms = '@recCount1 int OUTPUT' exec sp_executesql @sql, @parms, @recCount1 = @recCount OUTPUTRETURN @recCount -------------When tested from the stored proc, the result is: @RETURN_VALUE = 4 My code that calls this stored proc is: Dim connect As New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True") connect.Open() Dim cmd As New SqlCommand("CountTEst", connect) cmd.CommandType = CommandType.StoredProcedure Dim MyCount As Int32 MyCount = cmd.ExecuteScalar connect.Close() So I expext MyCount = 4 but this code returns MyCount = 0 With the RETURN statement in the stored proc, and then calling it via MyCount = cmd.ExecuteScalar, I didn't think I need to explicitly define any other parameters. Any help please. thanks,
View 5 Replies View RelatedHi,In SQL Books Online in the section on @@Error it gives the followingexample:-- Execute the INSERT statement.INSERT INTO authors(au_id, au_lname, au_fname, phone, address,city, state, zip, contract) values(@au_id,@au_lname,@au_fname,@phone,@address,@city,@state,@zip,@contract)-- Test the error value.IF @@ERROR <> 0BEGIN-- Return 99 to the calling program to indicate failure.PRINT "An error occurred loading the new author information"RETURN(99)ENDELSEBEGIN-- Return 0 to the calling program to indicate success.PRINT "The new author information has been loaded"RETURN(0)ENDGOHow do I access the value returned by the RETURN statement (i.e. 99 or0) in my asp application that called the stored proc.Sometimes rather than just return an integer signifying success orfailure I've seen examples where the id of the newly added item isreturned on success and perhaps -1 if the operation fails. Theseexamples make use of ouput parameters to achieve this. If theoperation succeeds then then the output parameters value is set to thenew id and this is accessed from the calling application.E.g.IF @@ERROR <> 0BEGIN-- Return -1 to the calling program to indicate failure.PRINT "An error occurred loading the new author information"SELECT @MyOuptputParameter = -1ENDELSEBEGIN-- Return id to the calling program to indicate success.PRINT "The new author information has been loaded"SELECT @MyOuptputParameter = @@IDENTITYENDWhy go to this trouble if you can use the RETURN statement?
View 4 Replies View RelatedWe are using asp's and tables sucessfully but when we click a link to an exe on a page it is slow, to the point of timing out. Very slow. Any help would be appreciated. Email me tperry@kpmg.com
Tim
Using JDBC, is there a way to call a stored procedure with multiplereturn values? Thanks.
View 4 Replies View RelatedI've been trying to create an async transform component and one of the things I'd like to do is add a new output and/or columns to existing outputs for each input column that is selected.
I thought that the SetUsageType method would be a good place to do this as it will allow me to delete output's and columns based on the user's selections but, despite the fact that my code doesn't actually throw or cause errors, when I return from the SetUsageType method I'm informed that error 0xC0047041 (DTS_E_OBJECTNOTINHASHTABLE) has occurred .
I pared the method down to an absolute minimum and found that a call to GetVirtualInput is enough to push it over the edge even if it does nothing! What is wrong here?
Thanks
Charlie
Sample:
public override IDTSInputColumn90 SetUsageType(int inputID, IDTSVirtualInput90 virtualInput,
int lineageID, DTSUsageType usageType)
{
if (usageType == DTSUsageType.UT_READONLY || usageType == DTSUsageType.UT_READWRITE)
{
ComponentMetaData.InputCollection[0].GetVirtualInput();
}
return base.SetUsageType(inputID, virtualInput, lineageID, usageType);;
}
Dear Forum Members.
I am currently using Visual Studio 2005: Business Intelligence Development Studio to create an Integration Services Package.
The package contains several containers, within each container there is a €˜foreach loop container€™ and within the €˜foreach loop container€™ there is a script component, execute component and another script component.
Each of the three components is connected together by single connectors.
My issue arises when I disable a container and then enable the container; the connectors between the components disappear (e.g. are not visible) but do exist as I can not create another connector between the components. When I attempt to create the connector again between the components I receive the error message. This tells me the connectors are present but not visible.
TITLE: Microsoft Visual Studio
------------------------------
Cannot create connector.
Only one workflow can exist between the same two executables.
------------------------------
BUTTONS:
OK
------------------------------
Can someone kindly explain why this is happening and how to resolve this issue?
I can not change the connector types from success to completion or vice versa because the connectors are not visible for me to select them.
Many Thanks
I know that if I set the Package.MaxConcurrentExecutables Property to -1, it will execute the maximum number of concurrent running executables to equal the number of processors plus two.
I have a requirement where I have to run the maximum number of concurrent running executables. However, the number of concurrent executables to run varies each month (it is not fixed).
If I have a loop inside the package that executes each stored procedure (same sproc, different parameter), will SQL Server execute the maximum number of concurrent running executables possible? (It may execute 4 or 8 at a time and that is fine.)
Thanks,
Michael
I need a simple example of calling a dll or exe with params
from a stored procedure.
Thanks
Robert Kapciuk
Is it possibe to call a Stored Proc on a remote server in a DTS package?
Thanks in advance,
John
Is it possble to automatically call an application when another application makes an INSERT from inside sql server? If its possible - how do I do that?
Thx in advance
/MiZza
For some reason, over a server link, the following call to a UDF on a linked server does not work, it seems it is treating the UDF as a table.The same UDF called on the local server works fine.--call/select from functionSELECT MASTERID FROM PAUL.RISK_DB.dbo.FNTRADEFILEPRICE('10/2/2006')error:Server: Msg 170, Level 15, State 31, Line 5Line 5: Incorrect syntax near '('.Thanks.
View 1 Replies View RelatedI 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.
I've created a dll that load a SSIS package and execute it. It works perfectly from outside SQL but when I tried to used it from SQL it always give me the same error: The task has failed to load. The contact information for this task is "".
The assembly has been registered as UNSAFE and the error I received it's like it was not able to load the tasks defined inside the package. I've tried with an empty package and it doesn't generate any error.
I've also see in the logging file that the account used for running the package is different from the windows account I've connected to the SQL Server.
Any idea on how assure that the package is run with the correct user to allow the execution of the package successfully from the SQL?
Hello All,
I wonder how this can be accomplished.
We have created a class lib wrapper that imports the com dll. Upon placing the assembly in sql 2005, both the wrapper Dll and the com dll (probably a converted version) gets placed inside the sql 2005 assembly store. Next a CLR sp references the 2 assemblies above, but within the call to the CLR sp things fail with exception:
System.UriFormatException:
Invalid URI: The URI is empty
The works fine from a windows forms app.
What is the methodology to invoke a COM Dll? How far are we from implementing this in SQL CLR
Thank you,
Lubomir
Hi,
I have got a simple C# assembly which call a ASP.net web service and executes a webmethod, now i want to call this assembly from sql server 2005 within the sql server function. can any body let me know how to do it.
Thanks
Hi this is my first post
I really needs some help
I have been stressing with issue for last few days.
I have a stored Procedure ramApplicationAddTest, I can run it through SQL Query Analyser without any problem.
I can call this using JDBC and it works, here is how I make call I make to SP
var con1 = DBDriver.getConnection ("alias:ProclaimConnect");
var stmt = con1.prepareCall( "{call ramApplicationAddTest(?,?,?,?,?)}");
stmt.setString(1,'BuildApp');
stmt.setString(2,'BASwimPool');
stmt.setString(3,'Test Test....');
stmt.setString(4,'');
stmt.setFloat(5,0);
stmt.execute();
stmt.close();
con1.close();
I also have another SQL 2000 Stored Procedure called
RamApplicationAddTest1, I can run this SP also through SQL Query Analyser without any problem.
But when I try to run this using JDBC it, gives me following Exception.
SQLState[ERR-Unexpected]ErrorCode[0]Message[java.lang.NullPointerException
at com.hs.form.jdbc.JDBCErrorhandler.isMssqlConnectio nErrorInTx(JDBCErrorHanlder.
An exception 'DatabaseQueryException' has occurred at DatabaseQueryClientWinInetImpl.cpp (291).
var con2 = DBDriver.getConnection ("alias:ProclaimConnect");
var stmt = con2.prepareCall( "{call ramApplicationAddTest1(?,?,?)}");
stmt.setString(1,’BuildApp’);
stmt.setString(2,’BASwimPool’);
stmt.setString(3,’Test’);
stmt.execute();
stmt.close();
con2.close();
I hope somebody can give me some hints to fix this.
Amar
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
I have a simple stored procedure:
create procedure sp_testres
@mult1 int,
@mult2 int,
@result int output
as
select @result = (10*@mult1) + @mult2
go
When I call it
declare @result int
exec sp_testres 5, 6, @result output
print @result
(Result is correctly shown as 56).
I then in C# wrote the following:
m_cmd.CommandText = "sp_testres";
SqlParameter param2 = new SqlParameter("@mult2", SqlDbType.Int);
param2.Value = 6;
SqlParameter param1 = new SqlParameter("@mult1", SqlDbType.Int);
param1.Value = 5;
SqlParameter param3 = new SqlParameter("@result", SqlDbType.Int);
param3.Direction = ParameterDirection.Output;
m_cmd.CommandType = CommandType.StoredProcedure;
m_cmd.Parameters.Add(param1);
m_cmd.Parameters.Add(param2);
m_cmd.Parameters.Add(param3);
m_cmd.ExecuteNonQuery();
This works and param3.Value holds the result value.
I also notice that I can supply the parameters in any order, and things work fine.
What I want to know is: can I call the stored procedure with parameters, where I haven't supplied the parameter name, and just rely on the parameter order matching instead?
Hi ,
I have a question about calling functions in SQl Server 2005.
Let's say that I have created as a dbo a function called Calculations.
If I want to call it from T-SQL I will write Select dbo.Calculations (arguments if any) etc.
My question is If I can skip the "dbo" part. Call the function without using the dbo .
Can I do that ?
Should I create the function as supervisor ? Does Sql Server has a property or something which will allow me to call the function without using the "dbo." ?
Thank you
zkar
I need to get the application lists calling our DB. I need both dormant and current. I thought profiler can give only active ones.
View 7 Replies View RelatedHello,
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
Setup: I have a C# 2.0 class library that contains certain business logic that is to be triggered by certain database states. There is a trigger that calls a stored procedure that is working properly (i.e. the stored procedure is being executed). Problem: I have not yet figured out how to call the dll from the stored procedure. Does anybody have any tutorials they could point me to or any advice to point me in the right direction? Thanks in advance for any help.
View 2 Replies View RelatedI am trying to call DB2 stroe procedure from within SQL server 2000 using DTS. I have the IBM odbc driver installed on the server. I have created an ACtiveX script to run in DTS and it fails staing it could not findor load the DB2 store procedure.
Has anyone come across doing this and how they did it?
THanks for the help....
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?
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()
I have an application that manage an sql srever compact edition database it worked OK until I got the following problem:
Only when debuging the application ("Start Debugging" from visual studio). In the first call to an sql server routine I get an exception "Unspecified error [ sqlceqp30.dll ]"
My code:
this.usersTableAdapter.Fill(this.nessDBDataSet.Users);
The line with the exception:
[global:ystem.Diagnostics.DebuggerNonUserCodeAttribute()]
[global:ystem.ComponentModel.DataObjectMethodAttribute(global:ystem.ComponentModel.DataObjectMethodType.Fill, true)]
public virtual int Fill(NessDBDataSet.UsersDataTable dataTable) {
this.Adapter.SelectCommand = this.CommandCollection[0];
if ((this.ClearBeforeFill == true)) {
dataTable.Clear();
}
int returnValue = this.Adapter.Fill(dataTable); //Here I get the exception
return returnValue;
}
The Exception:
Unspecified error [ sqlceqp30.dll ]
Again if I do "Run Without Debugging" Evrything is working.
Can any one help me with this
Thanks
Ofer
Hello
do ne one know how to call a SQL SERVER agent job from a SSIS package?
regards,
Anas
Hi All,
I have written ETL software that runs on SQL Server. We are running it for the first time on a 4cpu (2 x dual core) machine on sql server 2005.
One of the things this software does is perform a 'select * from tablename' to validate that the tables passed to it as parameters exist. This has worked fine on previous releases and on single cpu machines because what the optimiser decides to do is to return just the first page of data and then fetch more. I guess it even works in 2005 standard edition.
However, 2005 enterprise edition allows parallelism. And what the optimiser is deciding to do with such a query is to parallelise it and fetch all rows and then give the result back to the program. So, instead of seeing a fraction of a second to return the first page of data we are seeing up to 90 seconds and the database goes and fetches 15M rows in parallel.
Obviously, what we would like to do is to somehow tell the optimiser that this set of programs should not perform any parallel queries. Or, we would like to turn parallelism off on the specific tables we are dealing with for the period of running these ETL programs....they have no need of parallel processing at the database level for virtually all the calls that are performed.
Would someone please be so kind as to advise us if we can do something like pass a parameter to ODBC to stop parallelism or if we can issue commands against specific tables to stop parallelism for a period and then turn it back on?
Thanks in Advance.
Peter Nolan
www.peternolan.com
Hi All,
You have to forgive me but I am relatively new to this. I created a batch file which would call a SQL Server Agent Job. The said job calls SSIS packages. The last step for the job was to update a table which contains the next run date for the job. The batch file, after calling the job, would then query the table where the next run date is stored and place it in a log file. However, it seems that the log file gets updated first. The user who clicked on my batch file assumes that the job is finished and would proceed to run another program which scans all the records affected by the job. Since the job is not yet finished, the few records that were affected are the ones that are only scanned leaving the other records that are yet to be transferred.
Does calling SQLCMD from a batch file spawn a new thread for the job?
Why does the date get updated even though the job is not finished yet?
I have attached the bat file I created. Below that also is the control flow
@echo off
echo.
echo Executing job PRD_ICBS...
echo.
echo Please wait...
SQLCMD -Q "EXEC msdb.dbo.sp_start_job @job_name='TRANSFER_DATA'"
echo.
echo Creating folder %date:~-4,4%-%date:~-10,2%-%date:~-7,2%...
md %date:~-4,4%-%date:~-10,2%-%date:~-7,2%
cd %date:~-4,4%-%date:~-10,2%-%date:~-7,2%
date/t >> TRANSFER_DATA.txt
echo PRD_Insert_HMTRANSACTIONDATADETAILS_From_ICBS_TD >> PRD_ICBS.txt
echo PRD_Insert_HMTRANSACTIONDATADETAILS_From_ICBS_CASA >> PRD_ICBS.txt
echo PRD_Insert_HMTRANSACTIONDATADETAILS_From_ICBS_LOANS >> PRD_ICBS.txt
SQLCMD -Q "SELECT next_rundate FROM db.NEXT_RUNDATE_PARAM WHERE NAME = 'PRD_Insert_HMTRANSACTIONDATADETAILS_From_ICBS_TD'" -o "PRD_Insert_HMTRANSACTIONDATADETAILS_From_ICBS_TD.txt"
SQLCMD -Q "SELECT next_rundate FROM db.NEXT_RUNDATE_PARAM WHERE NAME = 'PRD_Insert_HMTRANSACTIONDATADETAILS_From_ICBS_CASA'" -o "PRD_Insert_HMTRANSACTIONDATADETAILS_From_ICBS_CASA.txt"
SQLCMD -Q "SELECT next_rundate FROM db.NEXT_RUNDATE_PARAM WHERE NAME = 'PRD_Insert_HMTRANSACTIONDATADETAILS_From_ICBS_LOANS'" -o "PRD_Insert_HMTRANSACTIONDATADETAILS_From_ICBS_LOANS.txt"
----------------------------- ----------------------------------------
| | | |
| Transfer Data | ===> | Update NEXT RUN DATE | |
| | | |
----------------------------- ----------------------------------------
Thanks in advance