No Output Variable In Remote Procedure Call?

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


ADVERTISEMENT

Transact SQL :: Generic Store Procedure Call Without Output Parameters But Catching Output

Sep 21, 2015

Inside some TSQL programmable object (a SP/or a query in Management Studio)I have a parameter containing the name of a StoreProcedure+The required Argument for these SP. (for example it's between the brackets [])

EX1 : @SPToCall : [sp_ChooseTypeOfResult 'Water type']
EX2 : @SPToCall : [sp_ChooseTypeOfXMLResult 'TABLE type', 'NODE XML']
EX3 : @SPToCall : [sp_GetSomeResult]

I can't change thoses SP, (and i don't have a nice output param to cach, as i would need to change the SP Definition)All these SP 'return' a 'select' of 1 record the same datatype ie: NVARCHAR. Unfortunately there is no output param (it would have been so easy otherwise. So I am working on something like this but I 'can't find anything working

DECLARE @myFinalVarFilledWithCachedOutput 
NVARCHAR(MAX);
DECLARE @SPToCall NVARCHAR(MAX) = N'sp_ChooseTypeOfXMLResult
''TABLE type'', ''NODE XML'';'
DECLARE @paramsDefintion = N'@CatchedOutput NVARCHAR(MAX) OUTPUT'

[code]...

View 3 Replies View Related

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 View Related

What Must Be Done To Setup A Remote Stored Procedure Call?

Sep 15, 1998

What are the steps for setting up a remote stored procedure call?

Thanks in advance,

RM

View 4 Replies View Related

Call Remote Server In A Stored Procedure(6.5)

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

How To Call Remote Object Using C# Stored Procedure

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

Using A Variable To Call A Stored Procedure Name

Jan 31, 2006

Hello;

I am using an Access 2003 front-end, and an SQL backend to run my application. I have a pretty good handle on using stored procedures and assigning variables for criteria within the SPROC.

I am however having a problem when I try to use a variable in place of a named procedure. For example I have a function that runs 2 procedures, therefore I "Call" the function with the code that runs my procedure, and simply change the name of the SPROC with each call. My problem is that I cannot figure out the syntax to use a variable for the named procedure. My code always errors on the line "objConn.MySProc MyCalendar, objRs" because MySproc is of course not a named procedure.

So how do I refer to a procedures name using a variable?

Here's my code;


Function LieuBen()

MyCalendar = CurrTSCalendar

Call PopulateTmpFile("sp_DelTmpProctimesheetCalc")
Call PopulateTmpFile("sp_PopTmpCalcLieuBen")

End Function

Function PopulateTmpFile(MySProc As Variant)
Dim sp_PopulateTempOTTable As String

Const DS = "SOS-1"
Const db = "TIMS"
Const DP = "SQLOLEDB"

Dim objConn As New ADODB.Connection
Dim objRs As New ADODB.Recordset
Dim objComm As New ADODB.Command

ConnectionString = "Provider=" & DP & _
";Data Source=" & DS & _
";Initial Catalog=" & db & _
";Integrated Security=SSPI;"

' Connect to the data source.
objConn.Open ConnectionString

' Set a stored procedure
objComm.CommandText = MySProc
objComm.CommandType = adCmdStoredProc
Set objComm.ActiveConnection = objConn

objConn.MySProc MyCalendar, objRs

objConn.Close
Set objRs = Nothing
Set objConn = Nothing
Set objComm = Nothing


End Function

View 1 Replies View Related

How To Call A Stored Procedure On MS Sqlserver From Web Forms And Get Back Output

Apr 7, 2006

Hi,
How to call a stored procedure on MS Sqlserver from web forms and get back output
Thanx,
Nishu

View 1 Replies View Related

How To Call Oracle Stored Procedure Which Has An Output Parameter From SSIS?

Jun 15, 2007

I will really appreciate if someone can post step by step process to call an Oracle Stored Proc from SSIS. Here is the Stored Proc Spec:


PROCEDURE Interface_Begin
(p_from_dttm OUT varchar2,
p_error_code OUT number,
p_error_text OUT varchar2,
p_proc_name OUT varchar2);

View 10 Replies View Related

Call Oracle Stored Procedure With Output Refcursor From Ssis

Nov 6, 2007



I would like to call a oracle stored procedure which looks like this

Create procedure in oracle sql plus


CREATE OR REPLACE PACKAGE GroupsPackage
AS
TYPE CURSOR_TYPE IS REF CURSOR;
PROCEDURE usp_SelectGroups (results_cursor OUT CURSOR_TYPE);
END;
/
CREATE OR REPLACE PACKAGE BODY GroupsPackage
AS
PROCEDURE usp_SelectGroups (results_cursor OUT CURSOR_TYPE)
AS
BEGIN
OPEN results_cursor FOR
SELECT GroupID, GroupName, GroupDescription, LastUpdateDate
FROM Groups
ORDER BY GroupName;
END;
END;
/


Execute procedure in oracle sql plus

set autoprint on;
var x refcursor;
groupspackage.usp_selectgroups(:x);
end;
/

This works in sql plus. But when I try to run the same (or tried everything) in the execute statement in ssis using execute sql task, I am not able to make it work. I am getting an error message. Please advice, what am I doing wrong. Or is this possible or is there any other way to get this working.

View 3 Replies View Related

DB Engine :: Remote Procedure Call Failed After 2012 Installation

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

SQL Tools :: Server Agent - Remote Procedure Call Failed (0x800706be)

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

Stored Procedure With Output Variable

Jul 20, 2005

Can someone post a working stored procedure with output variable thatworks on northwind db or explain to me what I am missing. I've triedever tying but it always returns @outvariable parameter not supplied.I've read everything but must be missing something. Here is an sampleSP that I can't get to work._____________________________________CREATE PROCEDURE SampleProcedure@out smallint OUTPUTASSelect @out = count(*) from titlesGO_____________________________________This errors and returns-Server: Msg 201, Level 16, State 4, Procedure SampleProcedure, Line 0Procedure 'SampleProcedure' expects parameter '@out', which was notsupplied.What am I missing with this?Frustrated,Jeff

View 2 Replies View Related

How Can I Put The Output Of A Stored Procedure Into A Variable

Nov 8, 2007



hi can anyone please help me
i have a stored procedure ,can i put the value of this into a variable......????
I have one more query ---how can i pass the multiple values returned by this stored procedure into a single variable ...
example if my stored procedure is

[dbo].[GetPortfolioName](@NT_Account varchar(100) )
and ans of this are
NL
UK
IN
GN
JK
then how can i put this into a variable like

Declare @PortfolioName as varchar(250)
set @PortfolioName =(exec ].[GetPortfolioName](@NT_Account) )
.......
and my ans is
@PortfolioName = 'NL','UK','IN','GN','JK'

View 22 Replies View Related

Reading An OUTPUT Parameter From A Stored Procedure Into A Variable

Oct 2, 2007

Hello,
I am struggling with this, having read many msdn articles and posts I am non the wiser. I have a sproc with an output parameter @regemail. I can call the sproc OK from my code as below, via a tableadapter. And the sproc works as desired from management studio express. I want to get the result returned in the OUTPUT parameter (NOT the Return Value) and place it in a variable. Can anyone advise how I can do this? J.
THE CODE I USE TO CALL THE SPROC
Dim tableAdapter As New DataSet1TableAdapters.RegistrationTableAdaptertableAdapter.SPVerifyUser(strRegGuid, String.Empty)
 THE STORED PROCEDURECREATE Proc [prs_VerifyUser
 @regid uniqueidentifier,@regemail nvarchar(250) OUTPUT
ASBEGIN
IF EXISTS(SELECT RegEmail from dbo.Registration WHERE RegID = @regid)
BEGIN
SELECT @regemail = RegEmail FROM Registration WHERE RegID = @regid
Return 1
END
Return 2
END

View 4 Replies View Related

T-SQL (SS2K8) :: Get Output Of Procedure And Assign It To A Variable Used In WHERE Clause?

Mar 25, 2014

Get output of SQL Procedure and assign it to a variable used in WHERE Clause

Later I want to use this variable in my code in the WHERE Clause

Declare @ProjectNo nvarchar(10)

--Now I want to assign it to output of a storedprocedure which returns only 1 value and use it in the below SELECT query.

SELECT ID from TABLEA where Project in (@ProjectNo)

How to do it. How to assign @ProjectNo to output of storedProcedure called 'GetProjNumber'

View 1 Replies View Related

The Incoming Tabular Data Stream (TDS) Remote Procedure Call (RPC) Protocol Stream Is Incorrect

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

Error Message: Error 0x800706BE While Loading Package File D:PackagesToradSales.dtsx. The Remote Procedure Call Failed.

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

Remote Stored Proc Call

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

Is There A Way To Call Remote Unix Scripts?

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

.net: Call SSIS-Package From Remote Computer

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

Using Output From A Stored Procedure As An Output Column In The OLE DB Command Transformation

Dec 8, 2006

I am working on an OLAP modeled database.

I have a Lookup Transformation that matches the natural key of a dimension member and returns the dimension key for that member (surrogate key pipeline stuff).

I am using an OLE DB Command as the Error flow of the Lookup Transformation to insert an "Inferred Member" (new row) into a dimension table if the Lookup fails.

The OLE DB Command calls a stored procedure (dbo.InsertNewDimensionMember) that inserts the new member and returns the key of the new member (using scope_identity) as an output.

What is the syntax in the SQL Command line of the OLE DB Command Transformation to set the output of the stored procedure as an Output Column?

I know that I can 1) add a second Lookup with "Enable memory restriction" on (no caching) in the Success data flow after the OLE DB Command, 2) find the newly inserted member, and 3) Union both Lookup results together, but this is a large dimension table (several million rows) and searching for the newly inserted dimension member seems excessive, especially since I have the ID I want returned as output from the stored procedure that inserted it.

Thanks in advance for any assistance you can provide.

View 9 Replies View Related

Remote Command Call Of DTEXEC Gives Insufficient For Component

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

How To Call A Variable In ADO.NET Data Reader

May 2, 2008


Hello everybody.

I recently started a short topic about staging an incremental load, and the answer was to use a variable. I've been fiddling around with it have run into a question about calling the variables.

I've gotten my "Execute SQL Task" to generate a single row result set and store it as a global / package Variable. Now I'm not sure how to call / use this variable when I'm loading data from an ADO.NET Data Reader Source.

Do I need to map the variable to a parameter first? Can I call the variable in the SELECT statement like this?



Code Snippet
SELECT *
FROM source
WHERE date > @variable




I was reading an MSDN page on Using Variables in Packages where they recommended the following syntax:



Code SnippetSELECT * from Production.Product WHERE ProductID = ?




But I'm not sure how to get the variable or parameter mapped to my Data Reader Source.


Any help would be greatly appreciated.
Thanks in advance,

- Trevor

View 6 Replies View Related

How To Call A Variable Declared In Parent Package

Nov 16, 2007



I have declared a variable XYZ in Parent package
Similarly I have declared ABC in Child package and have done the configuration.
I have assigned some value to XYZ
How to get the value in Child Package.

View 6 Replies View Related

Can You Call A Stored Proc That Returns A Table Variable Using ADO?

Jan 8, 2004

I have a stored proc that inserts into a table variable (@ReturnTable) and then ends with "select * from @ReturnTable."

It executes as expected in Query Analyzer but when I call it from an ADO connection the recordset returned is closed. All the documentation that I have found suggests that table variables can be used this way. Am I doing somthing wrong?

View 1 Replies View Related

XML Output Serialized For Remote Query

Jul 23, 2005

Hi,Im running into a strange problem here!I have created a linked server on one of my SQL Servers (local server).Lets call the linked server as LS1.I have an sproc which resides on the remote server(LS1) and uses a FORXML query.On running this sproc from my local server using linked server LS1, Iget a strange serialized data as output instead of the usual XMLoutput.I run the proc asexec [LS1].[DBName].[UserName].[SprocName] '12/1/2004','12/3/2004'the proc accepts 2 parameters as dates.The output generated is smth like:0x44046E0061006D0065004404440061.......(long string of hex codes)Looks like the ouput is getting serialized. How can I avoidserialization OR How to de-serialize this data?Please HelpThanks,Geo

View 5 Replies View Related

SSIS: Problem Mapping Global Variables To Stored Procedure. Can't Pass One Variable To Sp And Return Another Variable From Sp.

Feb 27, 2008

I'm new to SSIS, but have been programming in SQL and ASP.Net for several years. In Visual Studio 2005 Team Edition I've created an SSIS that imports data from a flat file into the database. The original process worked, but did not check the creation date of the import file. I've been asked to add logic that will check that date and verify that it's more recent than a value stored in the database before the import process executes.

Here are the task steps.


[Execute SQL Task] - Run a stored procedure that checks to see if the import is running. If so, stop execution. Otherwise, proceed to the next step.

[Execute SQL Task] - Log an entry to a table indicating that the import has started.

[Script Task] - Get the create date for the current flat file via the reference provided in the file connection manager. Assign that date to a global value (FileCreateDate) and pass it to the next step. This works.

[Execute SQL Task] - Compare this file date with the last file create date in the database. This is where the process breaks. This step depends on 2 variables defined at a global level. The first is FileCreateDate, which gets set in step 3. The second is a global variable named IsNewFile. That variable needs to be set in this step based on what the stored procedure this step calls finds out on the database. Precedence constraints direct behavior to the next proper node according to the TRUE/FALSE setting of IsNewFile.


If IsNewFile is FALSE, direct the process to a step that enters a log entry to a table and conclude execution of the SSIS.

If IsNewFile is TRUE, proceed with the import. There are 5 other subsequent steps that follow this decision, but since those work they are not relevant to this post.
Here is the stored procedure that Step 4 is calling. You can see that I experimented with using and not using the OUTPUT option. I really don't care if it returns the value as an OUTPUT or as a field in a recordset. All I care about is getting that value back from the stored procedure so this node in the decision tree can point the flow in the correct direction.


CREATE PROCEDURE [dbo].[p_CheckImportFileCreateDate]

/*

The SSIS package passes the FileCreateDate parameter to this procedure, which then compares that parameter with the date saved in tbl_ImportFileCreateDate.

If the date is newer (or if there is no date), it updates the field in that table and returns a TRUE IsNewFile bit value in a recordset.

Otherwise it returns a FALSE value in the IsNewFile column.

Example:

exec p_CheckImportFileCreateDate 'GL Account Import', '2/27/2008 9:24 AM', 0

*/

@ProcessName varchar(50)

, @FileCreateDate datetime

, @IsNewFile bit OUTPUT

AS

SET NOCOUNT ON

--DECLARE @IsNewFile bit

DECLARE @CreateDateInTable datetime

SELECT @CreateDateInTable = FileCreateDate FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName

IF EXISTS (SELECT ProcessName FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName)

BEGIN

-- The process exists in tbl_ImportFileCreateDate. Compare the create dates.

IF (@FileCreateDate > @CreateDateInTable)

BEGIN

-- This is a newer file date. Update the table and set @IsNewFile to TRUE.

UPDATE tbl_ImportFileCreateDate

SET FileCreateDate = @FileCreateDate

WHERE ProcessName = @ProcessName

SET @IsNewFile = 1

END

ELSE

BEGIN

-- The file date is the same or older.

SET @IsNewFile = 0

END

END

ELSE

BEGIN

-- This is a new process for tbl_ImportFileCreateDate. Add a record to that table and set @IsNewFile to TRUE.

INSERT INTO tbl_ImportFileCreateDate (ProcessName, FileCreateDate)

VALUES (@ProcessName, @FileCreateDate)

SET @IsNewFile = 1

END

SELECT @IsNewFile

The relevant Global Variables in the package are defined as follows:
Name : Scope : Date Type : Value
FileCreateDate : (Package Name) : DateType : 1/1/2000
IsNewFile : (Package Name) : Boolean : False

Setting the properties in the "Execute SQL Task Editor" has been the difficult part of this. Here are the settings.

General
Name = Compare Last File Create Date
Description = Compares the create date of the current file with a value in tbl_ImportFileCreateDate.
TimeOut = 0
CodePage = 1252
ResultSet = None
ConnectionType = OLE DB
Connection = MyServerDataBase
SQLSourceType = Direct input
IsQueryStoredProcedure = False
BypassPrepare = True

I tried several SQL statements, suspecting it's a syntax issue. All of these failed, but with different error messages. These are the 2 most recent attempts based on posts I was able to locate.
SQLStatement = exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
SQLStatement = exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output

Parameter Mapping
Variable Name = User::FileCreateDate, Direction = Input, DataType = DATE, Parameter Name = 0, Parameter Size = -1
Variable Name = User::IsNewFile, Direction = Output, DataType = BYTE, Parameter Name = 1, Parameter Size = -1

Result Set is empty.
Expressions is empty.

When I run this in debug mode with this SQL statement ...
exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the following error message appears.

SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.

Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Compare Last File Create Date

Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) 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.

SSIS package "MyPackage.dtsx" finished: Failure.

When the above is run tbl_ImportFileCreateDate does not get updated, so it's failing at some point when calling the procedure.

When I run this in debug mode with this SQL statement ...
exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the tbl_ImportFileCreateDate table gets updated. So I know that data piece is working, but then it fails with the following message.

SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.

Error: 0xC001F009 at GLImport: The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date

Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) 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.

SSIS package "MyPackage.dtsx" finished: Failure.

The IsNewFile global variable is scoped at the package level and has a Boolean data type, and the Output parameter in the stored procedure is defined as a Bit. So what gives?

The "Possible Failure Reasons" message is so generic that it's been useless to me. And I've been unable to find any examples online that explain how to do what I'm attempting. This would seem to be a very common task. My suspicion is that one or more of the settings in that Execute SQL Task node is bad. Or that there is some cryptic, undocumented reason that this is failing.

Thanks for your help.

View 5 Replies View Related

Sending Isql Output To Remote Share

Sep 15, 1998

I have a query I`d like to run using the isql command utility. The query works correctly and the output can be sent to a local file with no problem. When I try to designate a network location as an output file (e.g. abcdefgfile.txt), though, I get an error message "No such file or directory."

Is it possible to send SQL output to a remote location?

TIA!

View 1 Replies View Related

Output Parameter Vs (variable) = ?

Jan 18, 2006

Is there any benefit to using Output parameters:eg.[query set-up and execution goes here]

View 2 Replies View Related

Output Variable To Crystal From Sp

May 27, 1999

hey folks...
i am new to store procedures, crystal and sql server... ..one heck of a combination..yikes....neways.... writing a stored procedure in 6.5 to run a crystal report 7.0... want to create an output variable in the procedure that the report will see as a field... can i do this...and if so how....
any and all help muchos gracious.... the higher ups just don't get why i can't do all in 10 days of learning sp's on my own.... egads... management
:-)

take care.....kim

View 2 Replies View Related

Redirect The Output To A Variable

Mar 26, 2008

This is my stored procedure .It is working fine .I want to capture the output in a print statement .Can anyone help me please .

alter proc r (@id INT)

as

BEGIN

DECLARE @input VARCHAR(800)

DECLARE @c_input INT

DECLARE @i_Input INT

DECLARE @input_left VARCHAR(800)

DECLARE @delimiter CHAR(1)

select @delimiter = ','

DECLARE @in VARCHAR(800)

DECLARE @list VARCHAR(800)

declare @list2 VARCHAR(800)



SET @input = 'db2,oracle,sybase'

select @c_input = (select dbo.Fx_CharCount(@delimiter,@input))

set @c_input = @c_input + 1

while @c_input > 0

BEGIN

select @i_input = charindex(@Delimiter,@input)

if @i_input != 0

BEGIN

select @input_left = left(@input, @i_input - 1)

END

else

select @input_left = @input

select @in = '''' + @input_left + ''''

select @list = ISNULL(@list + ',', '') + @in

select @input = right(@input ,(len(@input) - @i_input))

SET @c_input = @c_input -1

if @c_input = 0 or @input = @input_left

break

end

Print @list

EXECUTE ('SELECT Label FROM systemtype WHERE Label Not IN (' + @list + ')')

END




my actual task is like this
My input is a list of values seperated by commas
now my output should be list of values not in the table joined by comma

eg : if my table consists of list of all databases like

db2
oracle,
sybase,
mssql,
mysql,



myinput would be like this db2,sybase,oracle
my output should be mssql,mysql
how to get that
?



Any suggestions

View 2 Replies View Related

Output To Be Redirected To A Variable

Mar 26, 2008

here's my code i want my output to be stored in a variable or out statement .Any suggestions on the below procedure

alter proc r (@id INT)
as
BEGIN

DECLARE @input VARCHAR(800)
DECLARE @c_input INT
DECLARE @i_Input INT
DECLARE @input_left VARCHAR(800)
DECLARE @delimiter CHAR(1)
select @delimiter = ','
DECLARE @inVARCHAR(800)
DECLARE @list VARCHAR(800)
declare @list2 VARCHAR(800)


SET @input = 'AWCA,GCS,IHP,Aetna'
select @c_input = (select dbo.Fx_CharCount(@delimiter,@input))
set @c_input = @c_input + 1
while @c_input > 0
BEGIN
select @i_input = charindex(@Delimiter,@input)
if @i_input != 0
BEGIN
select @input_left = left(@input, @i_input - 1)
END
else
select @input_left = @input

select @in = '''' + @input_left + ''''
select @list = ISNULL(@list + ',', '') + @in

select @input = right(@input ,(len(@input) - @i_input))
SET @c_input = @c_input -1
if @c_input = 0 or @input = @input_left
break
end

PRINT @list

END

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved