Stored Proc Return File Size
Jan 21, 2004Hi 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
ADVERTISEMENT
Can't Get Stored Proc To Return A Value
Feb 12, 2008Hi,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
Return Value From Stored Proc?
Jun 8, 2005I 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 RelatedPLEASE PLEASE HELP - How Can I Get A Return Value From A SQL Stored Proc Is ASP.NET?
Nov 30, 2006Hi. 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 RelatedStored Proc Returning 0 Size
Mar 3, 2004i have a stored proc
CREATE PROCEDURE pop_notes ( @contnum as bigint,@cusnotes varchar(1000) OUTPUT) as
begin
declare @noteid as int
select @noteid=max(noteid) from cusaddnotes where contractnum=@contnum and rtrim(popup)='yes'
if @noteid > 0
begin
select @cusnotes=(notes + ' [' + convert(varchar(100),dateadded) + ']' ) from cusaddnotes where noteid =@noteid
end
else
begin
set @cusnotes='none'
end
print @cusnotes
end
GO
the srored proc is returning the @cusnotes with a size 0 and its throwing out errors in my asp.net page
'@cusnotes' of type: String, the property Size has an invalid size: 0
i tried to run the stored proc frm the QA
declare @note as varchar(1000)
exec @note=pop_notes 3430,'y'
print @note
it returned
none
0 <-- this seems to be causing the error
what could be the error/reason...
thanks in advance
Get A Return Value From An Insert Without Using A Stored Proc.
Oct 31, 2006hi 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
Return @@rowcount From Stored Proc
Sep 24, 2007HiI'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 RelatedStored Proc How To Return A Single Value
Oct 9, 2007How 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
Stored Proc Won't Return Correct Value.
Oct 18, 2007I 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
Execute Stored Proc And Then Return A Value
Jul 13, 2004ok 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?
Newbie: Cannot Get Return Value From Stored Proc
Feb 20, 2007Good 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
Invalid Return Value From Stored Proc From VC++ 6.0
Oct 27, 2006I 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
Stored Proc && Transaction Log Size Problems!!!!!
Apr 17, 2006For some reason, my stored procedure which is kicked off by VB.NET is growing the log file for the database in question by gigs, very fast. Why is it doing this?
The code:
Sub Main()
objConn.Open()
Dim cmdSql As New SqlClient.SqlCommand("IT_sss_Collector_DotNet", objConn)
cmdSql.CommandType = CommandType.StoredProcedure
cmdSql.CommandTimeout = 1000
Dim reader As SqlDataReader = cmdSql.ExecuteReader()
Dim ppa_rowcount As Integer
ppa_rowcount = 0
If reader.HasRows() Then ' test to see if there is any data in the reader
reader.Read()
ppa_rowcount = Convert.ToInt16(reader("pcount"))
End If
reader.Close()
'Dim ppa_rowcount As Integer = Convert.ToInt16(cmdSql.ExecuteScalar())
MsgBox(ppa_rowcount)
If ppa_rowcount > 0 Then
MsgBox("got here")
RunsssReport()
End If
objConn.Close()
End Sub
The Stored proc:
http:\www.webfound.netstoredproc_growinglogfile.txt
the log is 29 gigs! this is a copy of a production database running on my test server. The production db log is only 15 gigs. How do I manage this and why it is growing at such a crazy porportion just from running this simple stored proc with a cursor???????
Stored Proc Failing To Return Results
Aug 9, 2000I 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?
Return Formatted Date From Stored Proc?
Mar 8, 2004What 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
How Do I Access The Value Of A Stored Proc Return Param In C# Using ExecuteNonQuery?
Jun 8, 2007I'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;}
SQL Server Express Stored Proc Does Not Return Any Data
Jan 28, 2008Hi!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 RelatedLooping A Package Based On A Return Value From A Stored Proc?
Oct 24, 2007Hi,
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
How To Call AS400 Stored Proc And Evaluate The Return Code?
May 30, 2007I 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.
How To Return A Numeric Value Based Upon If A Record Is Returned From My Query/Stored Proc.
Oct 2, 2007
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'
Exec SQL Task: Capture Return Code Of Stored Proc Not Working
May 19, 2006I 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
Save Some Stored Proc In File And Create SP From File
Jul 27, 2006Every day we are restoring prod DB in Development env. I need to save before restore users stored proc,
restore DB and after create SP from file.
Thanks.
DTS File Created After Stored Proc Executed Now What?
Apr 11, 2005I'm trying to fire a DTS package through a stored procedure.
After running my one stored procedure I got my DTS file to create just fine. However after this point I'm stuck...I can't do anything else with this file and I need to update data with it.
How would I use the DTS file to update my data.
Thanks,
RB
Capture Stored Proc Output To File
Jan 8, 2001I am running a stored proc that does many updates. I want to capture the results of the stored proc in a text file. Results returned such as, 18 rows affected (the stuff that shows in results pane of query analyzer). Do I have to run the stored proc in batch mode to do this and capture via the output file in OSQL? I also want to capture error messages in case the stored proc aborts in the middle.
I am running SQL Server 7.0 SP2 on Win2K. I am running the stored proc in a DTS package.
Thanks.
Tim
SQL 2005 Resize Initial Log Size: MODIFY FILE Failed. Specified Size Is Less Than Current Size.
Sep 4, 2007
I am trying to resize a database initial log file from 500M to 2M. I€™m using€?
ALTER DATABASE <DBNAME> MODIFY FILE ( NAME = <DBLOGFILENAME, SIZE = 2 ) "
And I'm getting "MODIFY FILE failed. Specified size is less than current size." I tried going into the database properties and setting the log file to 2M, but it doesn€™t keep the changes.
Any help with this process?
Can You Trace Into A Stored Proc? Also Does RAISERROR Terminate The Stored Proc Execution.
Feb 13, 2008I 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
Import Data From Text File Into A Temp Table In Stored Proc
Oct 1, 2001Hey,
can one of you please show me how to import data from a text file into a temp table in a stored proc.
thanks
Zoey
Integration Services :: Create Flat File From Stored Proc And View Using SSIS
Aug 25, 2015I have a Stored proc which on execution, will generate data in the view .
My requirement is using SSIS, how can I create a Flat file pointing to this view ?
Stored Proc - Calling A Remote Stored Proc
Aug 24, 2006I am having trouble executing a stored procedure on a remote server. On my
local server, I have a linked server setup as follows:
Server1.abcd.myserver.comSQLServer2005,1563
This works fine on my local server:
Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName
This does not work (Attempting to execute a remote stored proc named 'Data_Add':
Exec [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.Data_Add 1,'Hello Moto'
When I attempt to run the above, I get the following error:
Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'.
No entry found with that name. Make sure that the name is entered correctly.
Could anyone shed some light on what I need to do to get this to work?
Thanks - Amos.
Stored Proc Question : Why If Exisits...Drop...Create Proc?
Jun 15, 2006Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
View 3 Replies View RelatedASP Cannot Run Stored Proc Until The Web User Has Run The Proc In Query Analyzer
Feb 23, 2007I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
View 1 Replies View RelatedLog Backup File Size Not Matching Log File Size
Apr 15, 2008I have a log file that is approximately 50 GIG. I backed up just the log and the file size of the .bak is 192 GIG . Why is this? Shouldn't it be closer to the 50 GIG.
Normally I wouldn't let log grow this much. But we are in process of getting new server up and running and don't have backups going yet. They are working on getting that up and running this week.
So I did a log backup to give me back some log space for now but was concerned when I saw the size of the .bak file.
When I view media contents of the backup device it shows one tranaction log back up and size of 192 GIG.
What is up with this. I know in SQL 2000 the log backup files where never this big. they were about the size of the log itself.
Any ideas?
Stacy
Sql 2005 Install Error. MODIFY FILE Failed. Specified Size Is Less Than Current Size.
Jun 15, 2006I installed sql 2005 a while back. Then I recently found out my file system was fat32 (I don't understand why the hardware people did this...) and I had to convert to NTFS. Naturally the sql service no longer worked so I uninstalled inorder to reinstall now I can't reinstall it I keep getting this message
native_error=5039, msg=[Microsoft][SQL Native Client][SQL Server]MODIFY FILE failed. Specified size is less than current size.
I'll try to post the full log in a new post.