Hi,
I have a package that I need to loop (possibly multiple times) based on a return value from a stored procedure.
I know that in DTS you could use the return value of "execution status" to do this.
Is there a way in SSIS to loop based on a return value?
Thanks much
I need to call the stored procedure below. Basically what I need to know is if the query returns a record?
Note I would be happy if I could just return the number Zero if no records are returned. Can someone please help me out here?Here is my query so far in SQL Server. I just don't know how to return a value based upon the result of the records returned from the query.
GOCREATE PROCEDURE [dbo].[GetNameStatus]( @CountryId decimal, @NameId decimal, @DescriptionId decimal)AS SELECT Name.Active FROM Name INNER JOIN NameDescription ON Name.NameId = NameDescription.NameId WHERE Name.CountryId=@CountryId AND Name.NameId=@NameId AND NameDescription.DescriptionId=@DescriptionId AND Name.Active='Y'
Hello,I have a stored procedure that processes an individual file from adirectory and archives it in a subdirectory.Now, the problem is, when iexecute it , it will only process one file. What i want to do is to checkto see if there are any files in the folder, and if there are , processthem all, and once done, go to the next part in a DTS package, if there areno files, simply go to the next part in the DTS package. I tried an activexscript that would get the filecount in the folder, and if there were morethan 0 files in the folder, then DTS-sUCCESS and on "success" workflow , itwould run the stored procedure, and thus it woould process one file, then"on completion" the workflow connected it back to the activeX script(thuslooping), which would count the files again. Now if there were 0 files, itwould report DTS_FAILIURE, and I had it set up ,"on failiure" to go to thenext step in the package, but it wouldn't run.Someone mind showing me a ray of light?
View 1 Replies View RelatedHi,I'm having trouble getting a stored procedure to return a single
integer value. Here's a short
version:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~set ANSI_NULLS ONset
QUOTED_IDENTIFIER ONGOALTER PROCEDURE
[dbo].[Perm_Import_CJ]AS/* bunch of stuff removed */DECLARE
@NoCategory intSELECT @NoCategory = COUNT(*) FROM table WHERE CategoryID IS
NULL/* print @NoCategory */RETURN
@NoCategory~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~If I uncomment "print
@NoCategory" it prints exactly the number it's supposed to, so there is no
problem with any of the queries in the stored procedure. Then, in the code,
this is what I'm doing:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Dim dbConn
As New
SqlConnection(WebConfigurationManager.ConnectionStrings("ConnectionName").ConnectionString)Dim
cmd As New SqlCommand("StoredProc", dbConn)cmd.CommandType =
CommandType.StoredProceduredbConn.Open()Dim intNoCategory As Integer =
CInt(cmd.ExecuteScalar())dbConn.Close()~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~But,
and here's the problem ---> Even though @NoCateogry prints as the correct number, by the time it gets to intNoCategory in the code, it is ALWAYS zero.I have no idea what I am doing wrong.
Thanks in advance for any help!Casey
I need a stored procedure to return a value to my .NET app (ascx). The value will tell the app if the stored procedure returned any values or not. For example, if the Select SQL statement in the stored procedure returns no rows, the stored procedure could return a zero to the .NET app, otherwise it could return the number of rows or just a one to the .NET app.Anyone know how to do this?Thanks!
View 1 Replies View RelatedHi. I'm sorry to bother all of you, but I have spent two days lookingat code samples all over the internet, and I can not get a single oneof them to work for me. I am simply trying to get a value returned tothe ASP from a stored procedure. The error I am getting is: Item cannot be found in the collection corresponding to the requested name orordinal.Here is my Stored Procedure code.set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGoALTER PROCEDURE [dbo].[sprocRetUPC]@sUPC varchar(50),@sRetUPC varchar(50) OUTPUTASBEGINSET NOCOUNT ON;SET @sRetUPC = (SELECT bcdDVD_Title FROM tblBarcodes WHERE bcdUPC =@sUPC)RETURN @sRetUPCENDHere is my ASP.NET code.Protected Sub Page_Load(ByVal sender As Object, ByVal e AsSystem.EventArgs) Handles Me.LoadDim oConnSQL As ADODB.ConnectionoConnSQL = New ADODB.ConnectionoConnSQL.ConnectionString = "DSN=BarcodeSQL"oConnSQL.Open()Dim oSproc As ADODB.CommandoSproc = New ADODB.CommandoSproc.ActiveConnection = oConnSQLoSproc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProcoSproc.CommandText = "sprocRetUPC"Dim oParam1Dim oParam2oParam1 = oSproc.CreateParameter("sRetUPC",ADODB.DataTypeEnum.adVarChar,ADODB.ParameterDirectionEnum.adParamOutput, 50)oParam2 = oSproc.CreateParameter("sUPC", ADODB.DataTypeEnum.adVarChar,ADODB.ParameterDirectionEnum.adParamInput, 50, "043396005396")Dim resres = oSproc("sRetUPC")Response.Write(res.ToString())End SubIf I put the line -oSproc.Execute()above the "Dim res" line, I end up with the following error:Procedure or function 'sprocRetUPC' expects parameter '@sUPC', whichwas not supplied. I thought that oParam2 was the parameter. I was alsounder the assumption that the return parameter has to be declaredfirst. What am I doing wrong here?
View 8 Replies View Relatedhi all, lets say i have this insert command being executed from C# to a SQL Db. //store transaction log
SqlCommand cmdStoreTrans = new SqlCommand("INSERT into Transactions(ImportID,ProfileID,RowID) values (@ImportID,@ProfileID,@RowID);",conn);
cmdStoreTrans.Parameters.Add("@ImportID",importId);
cmdStoreTrans.Parameters.Add("@ProfileID",profileId);
cmdStoreTrans.Parameters.Add("@RowID",i);
try
{
conn.Open();
cmdStoreTrans.ExecuteNonQuery();
conn.Close();
}
catch(SqlException ex)
{
throw(ex);
}I need the new Identity number of that record added. how can i get that within THIS Sqlcommand. Currently im closing the connection, creating a new command with 'SELECT MAX(id) from transactions" and getting the value that way, im sure there is a easier way keeping it all within one command object? someone mentioned using something liek @@Identity any help appreciatedTIA, mcm
HiI'm using an sqldatasource control in my aspx page, and then executing it from my code behind page (SqlDataSource1.Insert()), how do i retrieve the number of rows (@@rowcount) which have been inserted into the database and display it in my aspx page. I am using a stored procedure. thanks
View 1 Replies View RelatedHow do I return a value in a stored procedure? I want to return a value for TheQuarterId below but under all test conditions am only getting back a negative one. Please help! create PROCEDURE [dbo].[GetQuarterIdBasedOnDescription]
(
@QuarterString nvarchar(10),
@TheQuarterId int output
)
AS
BEGIN
SELECT @TheQuarterId = QuarterId from Quarter WHERE Description=@QuarterString
END
I am using VS 2006, asp.net and C# to call a stored procedure. I want to return a value from the stored procedure into a variable in my C# code. Currently this is not working for me, and I can not figure out whatthe problem is? Can someone please help me out?
I really don't think the problem is in my stored procedure. I can right click on the stored proc and run it withsuccess. If I trace into the C# code though only a negative one (-1) is returned.
On line 5 I have tried the alternate lines of code but this has not worked for me.
mySqlCommand.Parameters["@TotalRecords"].Direction = ParameterDirection.Output; mySqlCommand.Parameters["@TotalRecords"].Direction = ParameterDirection.ReturnValue;
Can someone please help me out. I have spent to much time trying to figure this one out.
// C# code to call stored proc.1 try2 {3 SqlCommand mySqlCommand = new SqlCommand("[GetRecordsAssociatedWithRealtor]", mySqlConnection);4 mySqlCommand.Parameters.Add("@RealtorId", SqlDbType.Decimal, 10).Value = RealtorId;5 mySqlCommand.Parameters["@TotalRecords"].Direction = ParameterDirection.InputOutput;6 mySqlCommand.CommandType = CommandType.StoredProcedure;7 RecordsAssociatedWithRealtor = mySqlCommand.ExecuteNonQuery();8 }
// Stored procedure below.USE [REALTOR]GO/****** Object: StoredProcedure [dbo].[GetRecordAssociatedWithRealtor] Script Date: 10/18/2007 13:15:18 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[GetRecordAssociatedWithRealtor]( @RealtorId int, @TotalRecords int output)AS BEGIN DECLARE @HouseDetailRecords int DECLARE @RealtorRecords int SELECT @HouseDetailRecords= RealtorId from Realtor where RealtorId=@RealtorId SELECT @RealtorRecords = RealtorId from ConstructionDetail where RealtorId=@RealtorId SET @TotalRecords=SUM(@HouseDetailRecords+@RealtorRecords) RETURN @TotalRecordsEND
ok I have a stored procedure in my MS-SQL Server database.
It looks something like this.....
CREATE PROCEDURE updatePCPartsList
(
@Descriptionvarchar(255),
@ManCodevarchar(255),
@ProdCodevarchar(255),
@Pricedecimal(6,2),
@Commentsvarchar(255)
)
AS
declare @IDFound bigint
declare @LastChangedDate datetime
select @LastChangedDate = GetDate()
select @IDFound = PK_ID from PCPartsList where ProdCode = @ProdCode
if @IDFound > 0
begin
update PCPartsList set Description = @Description, ManCode = @ManCode, ProdCode = @ProdCode, Price = @Price, Comments = @Comments, LastChanged = @LastChangedDate where PK_ID = @IDFound
end
else
insert into PCPartsList (Description, ManCode, ProdCode, Price, Comments, LastChanged) values(@Description, @ManCode, @ProdCode, @Price, @Comments, @LastChangedDate)
GO
It executes fine so I know i've done that much right....
But what i'd like to know is how I can then return a value - specifically @LastDateChanged variable
I think this is a case of i've done the hard part but i'm stuck on the simple part - but i'm very slowly dragging my way through learning SQL.
Someone help?
Good morning, all,
OK, I have read a ton of posting on this issue, but either they don't give enough information or they are for packages which use the Execute SQL command, whereas I am using the OLE DB Command Data Flow Transformation.
I have an Excel spreadsheet that we are receiving from agencies with rows of client data which I have to load into an application that is ready to go live. I also have a stored procedure spClientsInsertRcd, which was written for the application. In the normal flow of the application, the stored procedure is called from a Coldfusion page, which does some processing prior to calling it. So, I have written a 'wrapper' stored procedure, spImportAgencyData, which does the processing and then calls the spClientInsertRcd.
My dataflow has the following components:
An Excel Source, containing my test data, consisting of just one row of data,
which points to a
Derived Column Transformation, which reformats the SSN and adds a user variable, named returnValue with an Expression value of @[User::returnvariable] set to a four-byte signed integer, which (i think) I need to get the value out of the stored procedure.
which points to a
Data Conversion Transformation, which takes care of all the datatype conversions
which points to a
OLE DB Command, which contains the following as the SQL Command:
exec ?= spImportAgencyData ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?
In the OLE DB Command, I have mapped returnValue, my user variable to @RETURN_VALUE.
Right now, I am in initial testing. The dataflow shows that it is succeeding, but my one data record for testing is not getting inserted. I need to get the value of returnValue to figure out what is happening.
How do I get the value of the returnValue? I have tried putting a recordset destination after the OLE DB command, but that just gives me the data that went into the OLE DB Command.
Thanks,
Kathryn
I have a stored procedure that takes a computer name (nvarchar) and either updates a time stamp in a matching row or adds a new row when no match is found based on the computer name (replicates a set of rows in another table as well in the case of not found).
When the row is unmatched, an output param (int) is set to 1 indicating it is new. When found, a zero is placed into the output parameter.
This stored procedure worked fine until we recently upgraded to SQL Server Express (2005).
C++ code:
_bstr_t conn_str = CONN_STR;
conn_str += (LPCTSTR)_server_name;
try
{
_ConnectionPtr epi_conn;
_CommandPtr cmd("ADODB.Command");
_ParameterPtr param;
long addfg;
_variant_t var_addfg;
//
// connect to database
//
epi_conn.CreateInstance(__uuidof(Connection));
epi_conn->Open(conn_str,"","",adConnectUnspecified);
cmd->ActiveConnection = epi_conn;
cmd->CommandText = "qry_DBUpdWorkstation";
cmd->CommandType = adCmdStoredProc;
//
// setup stored procedure call
//
param = cmd->CreateParameter (_bstr_t("@s_computer_name"), adVarChar, adParamInput, 31);
cmd->Parameters->Append(param);
param->Value = client_name;
param = cmd->CreateParameter (_bstr_t("@l_addfg"), adInteger, adParamOutput, 4);
cmd->Parameters->Append(param);
//
// debug output
//
if (trace_fp)
{
COleDateTime cur_dt = COleDateTime::GetCurrentTime();
fwprintf(trace_fp, L"%s - clsSrvrCtrlr::UpdWorkstation 1 client=%s",
cur_dt.Format(), client_name);
fflush(trace_fp);
}
cmd->Execute(NULL, NULL, adCmdStoredProc);
var_addfg = cmd->Parameters->Item[_variant_t("@l_addfg")]->Value;
addfg = var_addfg.iVal;
//
// done with stored proc
//
epi_conn->Close();
if (trace_fp)
{
COleDateTime cur_dt = COleDateTime::GetCurrentTime();
fwprintf(trace_fp, L"%s - clsSrvrCtrlr::UpdWorkstation 2 var_addfg.vt=%d, var_addfg.iVal=%d",
cur_dt.Format(), var_addfg.vt, var_addfg.iVal);
fflush(trace_fp);
}
and the stored proc:
set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF
GO
-- Database Version 5.5.0
ALTER PROCEDURE [dbo].[qry_DBUpdWorkstation]
@s_computer_name nvarchar(31),
@l_addfg int OUTPUT
AS
SET NOCOUNT ON
DECLARE @d_update_dt datetime
DECLARE @l_msg_cd int
DECLARE @b_view_local bit
DECLARE @b_view_global bit
DECLARE @l_alert_type smallint
DECLARE @s_sound_file nvarchar(255)
DECLARE @l_wscd int
SELECT @d_update_dt=UpdateDT
FROM dbo.tblWorkstations
WHERE ComputerNameTxt=@s_computer_name
SET @l_addfg = 0
IF @d_update_dt IS NULL
BEGIN
INSERT tblWorkstations (ComputerNameTxt, UpdateDT, OnlineFg)
VALUES (@s_computer_name, GETDATE(), 0)
SET @l_wscd = SCOPE_IDENTITY()
DECLARE msg_cursor SCROLL CURSOR FOR
SELECT SysMsgCd, ViewLocalFg, ViewGlobalFg, AlertTypeVal, SoundFileTxt
FROM tblMsgAlerts
WHERE WorkstationCd = -1
OPEN msg_cursor
FETCH FIRST FROM msg_cursor INTO
@l_msg_cd, @b_view_local, @b_view_global, @l_alert_type, @s_sound_file
WHILE @@fetch_status = 0
BEGIN
INSERT tblMsgAlerts (WorkstationCd, SysMsgCd, ViewLocalFg, ViewGlobalFg, AlertTypeVal, SoundFileTxt)
VALUES (@l_wscd, @l_msg_cd, @b_view_local, @b_view_global, @l_alert_type, @s_sound_file)
FETCH NEXT FROM msg_cursor INTO
@l_msg_cd, @b_view_local, @b_view_global, @l_alert_type, @s_sound_file
END
CLOSE msg_cursor
DEALLOCATE msg_cursor
SET @l_addfg = 1
END
ELSE
BEGIN
UPDATE tblWorkstations
SET UpdateDT = GETDATE()
WHERE ComputerNameTxt=@s_computer_name
END
The stored proc ALWAYS returns 0 but will execute the code to insert the new row when not found and replicate the rows in the second table. Any ideas, suggestions?
Thanks
I have a search stored proc which fails to return results when called by more than one user.
I have put selects in the various SPs to trace results and if I call from 3 query windows (executnig each as quickly as I can work the mouse) I get the following:
1st query returns the trace values (including correct count of temp table recs) but no result set
2nd query erturns nothing just "The command(s) completed successfully."
3rd query returns full results.
This seems to be consistent.
We are running SQL Server 7 SP1.
Just upgrading to SP2 to see if that helps.
The main SP calls other SPs to build result sets.
These use quite a few temp tables passed between SPs, parse CSV lists, join different other tables, create a SQL string to exec to do the search and get record IDs to return (no cursors).
The result set is built by a called SP using the temp table with IDs to return.
Anyone know of any problems or can suggest anything to try?
Hi everyone,
Does anyone know of a SQL stored proc that when given a operating system filename (i.e. a text file), returns the size of the file in bytes.
Thanks in advance,
Jim
What is the recommended method of returning a formatted date from a stored procedure?
The date is held in a date time field. I wish to return the date formatted as:
dd/mm/yyyy hh:mm
for display in a bound text box on a win form. JUst selecting the date and binding it to the text box shows:
dd/mm/yyyy hh:mm:ss
I do not want the :ss to show. A textbox does not have a format property (that I can see). I suppose I could create my own textbox inheriting from the standard and apply a display format property. I thought it may be easier to select as required in an sp. The textbox is read only on the form.
I was looking at:
select jobHeaders.DateTimeJobTaken AS [Job Taken],
CAST(datepart(dd,jobHeaders.DateTimeJobTaken) as char(2)) + '/' +
CAST(datepart(mm,jobHeaders.DateTimeJobTaken) as char(2)) + '/' +
CAST(datepart(yyyy,jobHeaders.DateTimeJobTaken) as char(4))
from jobHeaders
but this gives :
8 /3 /2004 with spaces.
Before looking further I thought one of you guys may have the answer.
Thanks in advance
I've got a stored proc to insert a record and return the id of the record inserted in an output param.How do I access this value in my code after the proc is executed?
param = comm.CreateParameter();param.ParameterName = "@MemberID";param.Direction = ParameterDirection.Output;param.DbType = DbType.Int32;comm.Parameters.Add(param);
try{ rowsAffected = GenericDataAccess.ExecuteNonQuery(comm);}catch { rowsAffected = -1;}
Hi!I have this table:Units -id uniqueidentified (PK), -groupName NVARCHAR(50) NOT NULL, -name NVARCHAR(50) NOT NULL, -ratio float NULL and the stored proc that simply returns all rows:ALTER PROCEDURE dbo.ilgSP_GetUnitsAS SELECT [id], [groupName], [name], [ratio] FROM [Units] ORDER BY [groupName], [name]If I select 'Show Table Data' in Visual Studio 2005 I see all rows from the table. If I 'Execute' my stored from VS 2005 I get this:Running [dbo].[ilgSP_GetUnits].id groupName name ratio -------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------------------- No rows affected.(1 row(s) returned)@RETURN_VALUE = 0Finished running [dbo].[ilgSP_GetUnits].And I don't get any data in my ASP.NET application. WHY?Thanks!
View 1 Replies View RelatedI am trying to use SSIS to update an AS400 DB2 database by calling a stored procedure on the AS400 using an OLE DB command object. I have a select statement running against the SQL Server 2005 that brings back 20 values, all of which are character strings, and the output of this select is piped into the OLE DB command object. The call from SSIS works just fine to pass parameters into the AS400 as long as the stored procedure being called does not have an output parameter defined in its signature. There is no way that I can find to tell the OLE DB command object that one of the parameters is an output (or even an input / output) parameter. As soon as one of the parameters is changed to an output type, I get an error like this:
Code Snippet
Error: 0xC0202009 at SendDataToAs400 1, OLE DB Command [2362]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF.
Error: 0xC0047022 at SendDataToAs400 1, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Command" (2362) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
Error: 0xC0047021 at SendDataToAs400 1, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0202009. There may be error messages posted before this with more information on why the thread has exited.
Information: 0x40043008 at SendDataToAs400 1, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x40043009 at SendDataToAs400 1, DTS.Pipeline: Cleanup phase is beginning.
Task failed: SendDataToAs400 1
Warning: 0x80019002 at RetrieveDataForSchoolInitiatedLoans: 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.
Warning: 0x80019002 at Load_ELEP: 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 "Load_ELEP.dtsx" finished: Failure.
I really need to know if the call to the AS400 stored procedure succeeded or not, so I need a way to obtain and evaluate the output parameter. Is there a better way to accomplish what I am trying to do? Any help is appreciated.
I am just trying to capture the return code from a stored proc as follows and if I get a 1 I want the SQL Task to follow a failure(red) constrainst workflow and send a SMTP mail task warning the customer. How do I achieve the Exec SQL Task portion of this, i get a strange error message [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".
Using OLEDB connection, I utilize SQL: EXEC ? = dbo.CheckCatLog
EXEC SQL Task Editer settings:
RESULTSET: Single Row
PARAMETER MAPPING: User::giBatchID
DIRECTION: OUTPUT
DATATYPE: LONG
PARAMETER NAME: 0
PS-Not sure if I need my variable giBatchID which is an INT32 but I thought it is a good idea to feed the output into here just in case there is no way that the EXEC SQL TASK can chose the failure constrainst workflow if I get a 1 returned or success constraint workflow if I get a 0 returned from stored proceedure
CREATE PROCEDURE CheckCatLog
@OutSuccess INT
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
DECLARE @RowCountCAT INT
DECLARE @RowCountLOG INT
---these totals should match
SELECT @RowCountCAT = (SELECT Count(*) FROM mydb_Staging.dbo.S_CAT)
SELECT @RowCountLOG = (SELECT Count(*) FROM mydb_Staging.dbo.S_LOG)
--PRINT @RowCountCAT
--PRINT @RowCountLOG
BEGIN
IF @RowCountCAT <> @RowCountLOG
--PRINT 'Volume of jobs from the CAT file does not match volume of jobs from the LOG file'
--RETURN 1
SET @OutSuccess = 1
END
GO
Thanks in advance
Dave
Hi,
I'm having problems with a stored procedure, that i'm hoping someone can help me with.
I have a table with 2 columns - Username (varchar), LastAllocation (datetime)
The Username column will always have values, LastAllocation may have NULL values. Example
Username | LastAllocation
------------------------
Greg | 02 October 2005 15:30
John | 02 October 2005 18:00
Mike | <NULL>
My stored procedure needs to pull back a user name with the following criteria:
If any <NULL> dates send username of first person where date is null, sorted alphabetically, otherwise send username of person with earliest date from LastAllocation
Then update the LastAllocation column with GETDate() for that username.
This SP will be called repeatedly, so all users will eventually have a date, then will be cycled through from earliest date. I wrote an SP to do this, but it seems to be killing my server - the sp works, but I then can't view the values in the table in Enterprise Manager. SP is below - can anyone see what could be causing the problem, or have a better soln?
Thanks
Greg
------------------------------------------------------------------------------
------------------------------------------------------------------------------
CREATE PROCEDURE STP_GetNextSalesPerson AS
DECLARE @NextSalesPerson varchar(100)
BEGIN TRAN
IF (SELECT COUNT(*) FROM REF_SalesTeam WHERE LeadLastAllocated IS NULL) > 0
BEGIN
SELECT TOP 1 @NextSalesPerson = eUserName FROM REF_SalesTeam WHERE LeadLastAllocated IS NULL ORDER BY eUserName ASC
END
ELSE
BEGIN
SELECT TOP 1 @NextSalesPerson = eUserName FROM REF_SalesTeam ORDER BY LeadLastAllocated ASC
END
SELECT @NextSalesPerson
UPDATE REF_SalesTeam SET LeadLastAllocated = GETDATE() WHERE eUserName = @NextSalesPerson
COMMIT TRAN
GO
I'm having some difficulty getting the appropriate results for my scenerio. I have two different datasets that I'm using. One is consisting of two joined tables and the other consisting of one sp. The sp's parameters rely on two things- one is the companyNum (inputed when the user runs the report) and two is the ContactNumType. The ContactTypeNum comes from the dataset of tables. I need to have a table consisting of this format:
ContactNumType1 (From the Tables)
File_Name1 (From the sp)
File_Name4 (From the sp)
File_Name3 (From the sp)
ContactNumType2 (From the Tables)
File_Name2 (From the sp)
File_Name7(From the sp)
ContactNumType3 (From the Tables)
File_Name5 (From the sp)
ContactNumType4 (From the Tables)
File_Name6 (From the sp)
File_Name10 (From the sp)
File_Name8(From the sp)
File_Name9 (From the sp)
So essentially what is going on is that every returned File_Name is grouped based upon the type of ContactNumType. My table returns the appropriate ContactNumTypes and the appropriate number of File_Names but returns only the first File_Name for each row. The File_Names should only grouped by the ContactTypeNums and each be unique. Is there any way to do that?
-------------------------------------------------------------------------------------------
Edited: I still am trying to work this out. I've tried a few run-arounds but none have worked. Adding custom code apparently is too risky at this point because of the security precautions that I've been instructed to take. Any help would be greatly appreciated as this project has been going on for days now....
Good day all! :cool:
I have a DTS package that consists of 2 tasks, one empties a table(1) to receive data from a transformation task(2)...
I found 'code' for a stored procedure to run a DTS package.. problem is that it is only running the first task... I need it to run the whole she-bang!
Anyone know how to do this?
Here is the stored proc:
/************************************************** **************************************/
/* Filename: spExecutePKG.sql*/
/* Description:Execute DTS Packages via sp_OAxxxx*/
/* Created:Darren Green 200006013*/
/************************************************** **************************************/
if exists (select * from sysobjects where id = object_id(N'[dbo].[spExecutePKG]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spExecutePKG]
GO
CREATE PROC spExecutePKG
@Server varchar(255),
@PkgName varchar(255), -- Package Name (Defaults to most recent version)
@ServerPWD varchar(255) = Null,-- Server Password if using SQL Security to load Package (UID is SUSER_NAME())
@IntSecurity bit = 0,-- 0 = SQL Server Security, 1 = Integrated Security
@PkgPWD varchar(255) = ''-- Package Password
AS
SET NOCOUNT ON
/*
Return Values
- 0 Successfull execution of Package
- 1 OLE Error
- 9 Failure of Package
*/
DECLARE @hr int, @ret int, @oPKG int, @Cmd varchar(1000)
-- Create a Pkg Object
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUTPUT
IF @hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN 1
END
-- Evaluate Security and Build LoadFromSQLServer Statement
IF @IntSecurity = 0
SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "' + SUSER_SNAME() + '", "' + @ServerPWD + '", 0, "' + @PkgPWD + '", , , "' + @PkgName + '")'
ELSE
SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "", "", 256, "' + @PkgPWD + '", , , "' + @PkgName + '")'
EXEC @hr = sp_OAMethod @oPKG, @Cmd, NULL
IF @hr <> 0
BEGIN
PRINT '*** LoadFromSQLServer failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END
-- Execute Pkg
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr <> 0
BEGIN
PRINT '*** Execute failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END
-- Check Pkg Errors
EXEC @ret=spDisplayPkgErrors @oPKG
-- Unitialize the Pkg
EXEC @hr = sp_OAMethod @oPKG, 'UnInitialize'
IF @hr <> 0
BEGIN
PRINT '*** UnInitialize failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END
-- Clean Up
EXEC @hr = sp_OADestroy @oPKG
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END
RETURN @ret
GO
how can a go about running a stored proc when my dts package is finished running?
View 2 Replies View RelatedDo you know, if there are any ways to start DTS package from the stored procedure?
Thank you,
Anastasia
My current task in my company is...
I got a Spreadsheet from which i have to get the data into a "Staging Table" and from there the data has to go into 10 different Tables in my database..
I had created a DTS package using ActiveX script and written a Stored Proc to get the records from Staging table to Database tables...every thing is working fine... but...
they asked me to send "TapeType' and "DealName" into two fields and "current datetime" into one more field of the stagingtable fields which are not present inthe Spreadsheet....
so in the Activex script i hard coded... destination"TapeType"= "BidTape" and destination"DealName"= "ABCD" and its working...
and for currentdate i had defined a DTS Task and in that i had written a SQL stmts..
update table....
set currentdate = getdate()
where TapeType = 'BidTape' ..... which is also working....but the problem is
if i got one more spread sheet with same "TapeType" i.e. BidTape but with different "DealName" i.e. "XYZ".... every thing is working out but....the Currentdate field is getting updated every where with as one date..???
can u suggest me a better solution here.....
and after this Iam planning to create one more Task to Execute the Proc..... but I have to pass 2parameters i.e. "Dealname" and "TapeType"... they want every thing to be get done with single mouse click...
can u suggest me here how to pass these 2parameters in the DTS package,..
I will be waiting for any one your valuable suggestions....
I'm a rookie with MSSQL. I need to run a DTS package to export a result setto an MX Excel spread sheet. I need to call the DTS from a stored procedureand pass it three values, depending on the input parameters to the storedproc.DTS package is no problem. Pretty easy with the DTS wizard. My problem isthat I can't figure out how to instansiate the DTS package object from astored proc and pass the three values as parameters to the DTS package sothey can populate the parameters I created in it.I found an article related to it, but I'm too much of a rookie to grasp it.It showed how to do this from a stored procedure:EXEC @hr = sp_OASetProperty @oPKG, 'GlobalVariables("MyGVName").Value,'MyGVValue'IF @hr <> 0BEGINPRINT '*** GlobalVariable Assignment Failed'EXEC sp_displayoaerrorinfo @oPKG, @hrENDI have three values and tree global variables to populate. Do I need to dothe above 3 times?How do I instantiate the package object? I've read up some on sp_OACreate,but I don't get it, yet.How do I initiate the variable @oPKG?It contains the name of the sp_OACreate string, right? How do I address aDTS package in the sp_OACreate string?I would really appreciate just writing out the sp_OACreate string and how Ipass values for three existing global variables to a DTS package named"DTS_1".I'm under some real pressure to get this done.Thanks for any help I can get.Gunny
View 1 Replies View Relatedis there a way to execute SSIS Package through stored proceedure.
Or any other method of executing the SSIS Package command line in stored proceedure
Thanks,
jas
Hello-I'm fairly new to writing SQL statements and would greatly appreciatesome help on this one.I'm working on a project for a non-profit that I volunteer for. Partof the database tracks membership using tables like this:PersonInfo-------------------PersonID (primary key)FirstNameLastNameetc..PeopleMemberships-------------------PPLMembershipIP (primary key)PersonIDMembershipTypeIDFeePaidMembershipTypes--------------------MembershipTypeID (primary key)MembershipYearStandardFeeMembershipDescription (varchar)Just because a person is in PersonInfo, doesn't mean they have anythingin PeopleMemberships (they can be in the databse for other reasons andnot have or have ever had a membership).Membership fees vary by year and type of membership and they want toretain a history of a person's memberships.What I'm looking to do here is write a query (a view in SQL Server)that will return the following InfoPersonID, MostRecentMembershipYear, FeePaidForThatMembership,DescriptionOfThatMembershipI'm thinking that I'd use max(MembershipYear), but that requires groupby for the other columns, so I'm getting all of the people'smemberships returned.I'm pretty sure this can be best done with a subquery, but I'm not surehow.Can someone please point me in the right direction or provide a samplethat I can learn from?Kindly,Ken
View 4 Replies View RelatedHi all,
I have created an SSIS package with protection level - EncryptSensitiveWithUserKey. It is running alright from Visual Studio Environment but i need to call the package from an application, so i created a stored proc to call it, but getting the following error while running the stored proc:
Error: 2007-04-16 17:55:05.78
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTSassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that
the correct key is available.
End Error
any solution?
My Package Exist in
C:WROXWROXinexcel_database package exist
OR sql server agent ->job-> excel_database package exist
now i want to excute the from master database how please send the
stored procedure code
Regards
koti
I know this question has been asked. And the usual answer is don't usecursors or any other looping method. Instead, try to find a solutionthat uses set-based queries.But this brings up several questions / senarios:* I created several stored procedures that take parameters and insertsthe data into the appropriate tables. This was done for easy access/usefrom client side apps (i.e. web-based).Proper development tactics says to try and do "code reuse". So, if Ialready have stored procs that do my logic, should I be writing asecond way of handling the data? If I ever need to change the way thedata is handled, I now have to make the same change in two (or more)places.* Different data from the same row needs to be inserted into multipletables. "Common sense" (maybe "gut instinct" is better) says to handleeach row as a "unit". Seems weird to process the entire set for onetable, then to process the entire set AGAIN for another table, and thenYET AGAIN for a third table, and so on.* Exception handling. Set based processing means that if one row failsthe entire set fails. Looping through allows you to fail a row butallow everything else to be processed properly. It also allows you togather statistics. (How many failed, how many worked, how many wereskipped, etc.)?? Good idea ?? The alternative is to create a temporary table (sandboxor workspace type thing), copy the data to there along with "status" or"valdation" columns, run through the set many times over looking forany rows that may fail, marking them as such, and then at the end onlydealing with those rows which "passed" the testing. Of course, in orderfor this to work you must know (and duplicate) all constraints so youknow what to look for in your testing.
View 13 Replies View RelatedI am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code.
So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message:
Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages.
(1 row(s) affected)
(1 row(s) affected)
I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution?
Also, Is there a way to trace into a stored procedure through Query Analyzer?
-------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised:
SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId
IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END