What Am I Missing Here? Using A Stored Proc Output To Build Table Rows
Nov 27, 2007
Hi all,
I have access to a stored procedure that was written previously for a process that uses the output from the stored procedure to provide input to a BCP operation in a bat file that builds a flat text file for use in a different system.
To continue with the set up, here is the stored procedure in question:
CREATE PROCEDURE [dbo].[HE_GetStks] AS
select top 15
Rating,
rank,
coname,
PriceClose,
pricechg,
DailyVol,
symbol
from
(selectf.rating,
f.rank,
s.coname,
cast ( f.priceclose as decimal(10,2)) as PriceClose,
cast ( f.pricechg as decimal(10,2)) as pricechg,
f.DailyVol,
f.symbol
from dailydata f, snames s
where f.tendcash = 0
and f.status = 1
and f.typ = 1
and f.osid = s.osid) tt
order by rating desc, rank desc
GO
The code in the calling bat file is:
REM *************************
REM BCP .WRK FILE
REM *************************
bcp "exec dailydb.[dbo].[HE_GetStks]" queryout "d:TABLESINPUTHE_GetStks.WRK" -S(local) -c -U<uname> -P<upass>
This works just peachy in the process for which it was designed, but I need to use the same stored procedure to grab the same data in order to store it in a historical table in the database. I know I could duplicate the code in a separate stored procedure that does the inserting into my database table, but I would like to avoid that and use this stored procedure in case the select statement is changed at some point in the future.
Am I missing something obvious in how to utilize this stored procedure from inside an insert statement in order to use the data it outputs? I know I cannot use an EXECUTE HE_GetStks as a subquery in my insert statement, but that is, in essence, what I am trying to accomplish.
I just wanted to bounce the issue of y'all before I go to The Boss and ask him to change the procedure to SET the data into a database table directly (change the select in the proc to an INSERT to a local table) then have the external BAT file use a GET procedure that just does the select from the local table. This is the method most of our similar jobs use when faced with this type of "intercept" task.
Any thoughts?
View 6 Replies
ADVERTISEMENT
Feb 5, 2008
I have a stored procedure that returns a resultset AND an output parameter, pseudocode:myspGetPoll@pollID int,@totalvoters int outputselect questionID,question from [myPoll] where pollID=@pollID @totalvoters=(select count(usercode) from [myPoll] where pollID=@pollID)1. In my code behind I'd like to read both the rows (questionID and question) as well as total results (totalvoters) How could I do so?2. what would be the signature of my function so that I can retreive BOTH a resultset AND a single value?e.g.: private function getPollResults(byval pollID as integer, byref totalvoters as integer) as datasetwhile reader.read dataset.addrow <read from result>end whiletotalvoters=<read from result>end functionThanks!
View 2 Replies
View Related
Sep 2, 2004
Want to obtain the outpur of a xp_cmdshell (or any other procedure call) command to a table. Is it possible ?
View 6 Replies
View Related
Feb 18, 2008
I writing a unit test which has one stored proc calling data from another stored proc. Each time I run dbo.ut_wbTestxxxxReturns_EntityTest I get a severe uncatchable error...most common cause is a trigger error. I have checked and rechecked the columns in both of the temp tables created. Any ideas as to why the error is occurring?
--Table being called.
ALTER PROCEDURE dbo.wbGetxxxxxUserReturns
@nxxxxtyId smallint,
@sxxxxxxxxUser varchar(32),
@sxxxxName varchar(32)
AS
SET NOCOUNT ON
CREATE TABLE #Scorecard_Returns
(
NAME_COL varchar(64),
ACCT_ID int,
ACCT_NUMBER varchar(10),
ENTITY_ID smallint,
NAME varchar(100),
ID int,
NUM_ACCOUNT int,
A_OFFICER varchar(30),
I_OFFICER varchar(30),
B_CODE varchar(30),
I_OBJ varchar(03),
LAST_MONTH real,
LAST_3MONTHS real,
IS int
)
ALTER PROCEDURE dbo.ut_wbTestxxxxReturns_EntityTest
AS
SET NOCOUNT ON
CREATE TABLE #Scorecard_Returns
(
NAME_COL varchar(64),
ACCT_ID int,
ACCT_NUMBER varchar(10),
ENTITY_ID smallint,
NAME varchar(100),
ID int,
NUM_ACCOUNT int,
A_OFFICER varchar(30),
I_OFFICER varchar(30),
B_CODE varchar(30),
I_OBJ varchar(03),
LAST_MONTH real,
LAST_3MONTHS real,
IS int
)
INSERT #Scorecard_Returns(
NAME_COL ,
ACCT_ID
ACCT_NUMBER ,
ENTITY_ID,
NAME,
ID,
NUM_ACCOUNT,
A_OFFICER,
I_OFFICER,
B_CODE,
I_OBJ ,
LAST_MONTH
LAST_3MONTHS,
IS
)
EXEC ISI_WEB_DATA.dbo.wbGetxxxxxcardUserReturns
@nId = 1,
@sSUser = 'SELECTED USER',
@sUName = 'VALID USER'
View 4 Replies
View Related
Jul 20, 2005
HiI'm trying to make this to work and need helpHere my SP and I'm building sql with output param.Alter PROCEDURE lpsadmin_getSBWReorderDollars(@out decimal(10,2) output,@sType varchar(20),@dSearchDateFrom datetime,@dSearchDateTo datetime,@sOrderType char(1))ASDECLARE @sql as nvarchar(4000)SELECT @sql = 'SELECT @out = SUM(Price*Quantity)FROM PortraitReOrderOrder jcpreINNER JOIN Orders jcporON OrderID = OrderIDWHERE jcpor.Archive = 0AND jcpre.CreatedDate between ''' + CONVERT(varchar(10),@dSearchDateFrom, 101) + ''' AND ''' + CONVERT(varchar(10),@dSearchDateTo, 101) + ''''IF @sOrderType <> 0SELECT @sql = @sql + ' AND LEFT(OrderType,3) = (SELECT OrderTypeNameFROM OrderTypes WHERE OrderTypeID = ' + @sOrderType + ')'IF @sType = 'Active'SELECT @sql = @sql + ' AND PATINDEX(''%SHR%'', AccessCode) = 0 'IF @sType = 'Shared'SELECT @sql = @sql + ' AND PATINDEX(''%SHR%'', AccessCode) <> 0 'Print @sqlEXECUTE sp_executesql @sqlIt gives me an error messageMust declare the variable '@out'.Please help
View 2 Replies
View Related
Jun 17, 2015
Is it possible to force a build to fail when a stored procedure in a project calls another stored procedure with one or more required parameters missing. E.g.:
CREATE PROCEDURE [App].[ServiceUser_Save]
@userID int-- Param #0
,@serviceuserID int-- Param #1
,@version int-- Param #2 etc...
And then in a separate stored procedure we have the following
CREATE PROCEDURE [Admin].[CreateMiscellaneousData]
@customerIDint,
@serviceIDint,
@fullURL nvarchar(255),
@apiUserPwd nvarchar(255)
AS
BEGIN
...
EXEC@return_value = [App].[ServiceUser_Save]
@userID = 1,
@serviceuserID = 0, etc...
Note there is no value passed for the @Version parameter.
What I want is the build to fail because of the missing parameter on the call to ServiceUser_Save in the Create_MiscellaneousData stored procedure.
How can I achieve this?
View 4 Replies
View Related
Jun 16, 2004
How can I view the output of a stored procedure that is returning a OUTPUT variable? I've written a stored proc that uses OUTPUT but when I run it, all I see is "The command(s) completed successfully." I'm at a loss on how to debug/verify/view the output value.
The same thing happens using this example from MS.
CREATE PROCEDURE titles_sum @TITLE varchar(40) = '%', @SUM money OUTPUT
AS
SELECT 'Title Name' = title
FROM titles
WHERE title LIKE @TITLE
SELECT @SUM = SUM(price)
FROM titles
WHERE title LIKE @TITLE
GO
TIA - KB
View 5 Replies
View Related
Feb 19, 2005
Has anyone ever tried to use a cursor as an output variable to a stored proc ?
I have the following stored proc - CREATE PROCEDURE dbo.myStoredProc
@parentId integer,
@outputCursor CURSOR VARYING OUTPUT
AS
BEGIN TRAN T1
DECLARE parent_cursor CURSOR STATIC
FOR
SELECT parentTable.childId, parentTable. parentValue
FROM parentTable
WHERE parentTable.parentId = @parentId
OPEN parent_cursor
SET @outputCursor = parent_cursor
DECLARE @childId int
DECLARE @parentValue varchar(50)
FETCH NEXT FROM parent_cursor INTO @childId, @parentValue
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT childTable.childValue
FROM childTable
WHERE childTable.childId = @childId
FETCH NEXT FROM parent_cursor INTO @childId, @parentValue
END
CLOSE parent_cursor
DEALLOCATE parent_cursor
COMMIT TRAN T1
GOAnd, I found that I had to use a cursor as an output variable because, although the stored proc returns a separate result set for each returned row in the first SQL statement, it did not return the result set for the first SQL statement itself.
My real problem at the moment though is that I can't figure a way to get at this output variable with VB.NET.Dim da as New SqlDataAdapter()
da.SelectCommand = New SqlCommand("myStoredProc", conn)
da.SelectCommand.CommandType = CommandType.StoredProcedure
Dim paramParentId as SqlParameter = da.SelectCommand.Parameters.Add("@parentId", SqlDbType.Int)
paramParentId.Value = 1
Dim paramCursor as SqlParameter = daThread.SelectCommand.Parameters.Add("@outputCursor")
paramCursor.Direction = ParameterDirection.OutputThere is no SqlDataType for cursor. I tried without specifying a data type but it didn't work. Any ideas?
Thanks
Martin
View 6 Replies
View Related
Oct 2, 2006
I have difficulty reading back the value of an output parameter that I use in a stored procedure. I searched through other posts and found that this is quite a common problem but couldn't find an answer to it. Maybe now there is a knowledgeable person who could help out many people with a good answer.The problem is that cmd.Parameters["@UserExists"].Value evaluates to null. If I call the stored procedure externally from the Server Management Studio Express everything works fine.Here is my code:using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("mys_ExistsPersonWithUserName", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@UserName", SqlDbType.VarChar).Value = userName;
cmd.Parameters.Add("@UserExists", SqlDbType.Int);
cmd.Parameters["@UserExists"].Direction = ParameterDirection.Output;
cn.Open();
int x = (int)cmd.Parameters["@UserExists"].Value;
cn.Close();
return (x>1);
} And the corresponding stored procedure: ALTER PROCEDURE dbo.mys_Spieler_ExistsPersonWithUserName
(
@UserName varchar(16),
@UserExists int OUTPUT
)
AS
SET NOCOUNT ON
SELECT @UserExists = count(*)
FROM mys_Profiles
WHERE UserName = @UserName
RETURN
View 1 Replies
View Related
Nov 30, 2004
Hi Guys
I am wondering if you could spare some time and help me out with this puzzle.
I am new to this stuff so please take it easy on me.
I’m trying to create procedure which will take 2 input parameters and give me 1 back.
Originally there will be more outputs but for this training exercise 1 should do.
There are 2 tables as per diagram below and what I’m trying to do is
Verify username & password and pull out user group_name.
|---------------| |-----------------------|
| TBL_USERS | |TBL_USER_GROUPS|
|---------------| |-----------------------|
| USERNAME | /|GROUP_ID |
| PASSWORD | / |GROUP_NAME |
| GROUP_ID |< | |
|---------------| |-----------------------|
For my proc. I am using some ideas from this and some other sites, but obviously i've done something wrong.
'====================================================
ALTER PROCEDURE dbo.try01
(
@UserName varchar(50),
@Password varchar(50),
@Group varchar Output
)
AS
SET NOCOUNT ON;
SELECT TBL_USERS.USERNAME, TBL_USERS.PASSWORD,@Group = TBL_USER_GROUPS.GROUP_NAME,
TBL_USERS.USER_ID, TBL_USER_GROUPS.GROUP_ID
FROM TBL_USERS INNER JOIN TBL_USER_GROUPS
ON TBL_USERS.GROUP_ID = TBL_USER_GROUPS.GROUP_ID
WHERE (TBL_USERS.USERNAME = @UserName)
AND (TBL_USERS.PASSWORD = @Password)
'====================================================
and this is what i'm getting in VS.Net while trying to save.
'====================================================
ADO error: A select statement that assigns a value to variable must
not be combined with data-retrieval operation.
'====================================================
I did not see any samples on the net using ‘varchar’ as OUTPUT usually they where all ‘int’s. Could that be the problem?
Please help.
CC
View 1 Replies
View Related
Jan 8, 2001
I 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
View 3 Replies
View Related
Mar 16, 2006
I'm trying to call a stored procedure in an Execute SQL task which has several parameters. Four of the parameters are input from package variables. A fifth parameter is an output parameter and its result needs to be saved to a package variable.
Here is the entirety of the SQL in the SQLStatement property:
EXEC log_ItemAdd @Destination = 'isMedicalClaim', @ImportJobId = ?, @Started = NULL, @Status = 1, @FileType = ?, @FileName = ?, @FilePath = ?, @Description = NULL, @ItemId = ? OUTPUT;
I have also tried it like this:
EXEC log_ItemAdd 'isMedicalClaim', ?, NULL, 1, ?, ?, ?, NULL, ? OUTPUT;
Here are my Parameter Mappings:
Variable Name Direction Data Type Parameter Name
User::ImportJobId Input LONG 0
User::FileType Input LONG 1
User::FileName Input LONG 2
User::FilePath Input LONG 3
User::ImportId Output LONG 4
When this task is run, I get the following error:
0xC002F210 at [Task Name], Execute SQL Task: Executing the query "EXEC log_ItemAdd @Destination = 'isMedicalClaim', @ImportJobId = ?, @Started = NULL, @Status = 1, @FileType = ?, @FileName = ?, @FilePath = ?, @Description = NULL, @ItemId = ? OUTPUT" failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_I4)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
The User::ImportId package variable is scoped to the package and I've given it data types from Byte through Int64. It always fails with the same error. I've also tried adjusting the Data Type on the Parameter Mapping, but nothing seems to work.
Any thoughts on what I might be doing wrong?
Thanks.
View 4 Replies
View Related
Oct 2, 2007
How do I specify a parameter as an output parameter --> OUTPUT paramI am referring to how to do this on line 10 below
1 int GetTheReturnValue=0;2//Code not shown//
9 mySqlCommand.Parameters.Add("@returnParameter", SqlDbType.Int, 10).Value = 0; // How to specify output param?10 GetTheReturnValue=mySqlCommand.ExecuteNonQuery();
View 7 Replies
View Related
Sep 26, 2000
I have a problem in calling a procedure.
It has two input parameters and seven(7) output parms.
When I run it this way:
exec usp_List_Relationship_Users @relationship_id = 1851 ,
@status = 'Channel Member',
@cust_idOUTPUT,
@user_login_id OUTPUT,
@username OUTPUT,
@password OUTPUT,
@status_id OUTPUT,
@sdesc OUTPUT,
@administrator OUTPUT
I get this error:
Server: Msg 119, Level 15, State 1, Line 10
Must pass parameter number 3 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.
But when I run it this way :
exec usp_List_Relationship_Users 1851 ,
'Channel Member',
@cust_idOUTPUT,
@user_login_id OUTPUT,
@username OUTPUT,
@password OUTPUT,
@status_id OUTPUT,
@sdesc OUTPUT,
@administrator OUTPUT
I get the expected results. Why should I omit the name of the name of the input parameters? I don't know why I am getting this error if don't run by ommitting the name of the input params.
Any help regarding this matter is greatlt appreciated.
You can assume that all the variables are declared prior to executing the stored procedure.
Regards
Sushruth Nanduri.
View 3 Replies
View Related
Jun 5, 2007
Hello
This is my first post so please be gentle!!
I have a stored proc that returns a value:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
-- =============================================
ALTER PROCEDURE [dbo].[spPK]
-- Add the parameters for the stored procedure here
@varNSC varchar(4),
@varNC varchar(2),
@varIIN varchar(7),
@varIMCDMC varchar(8),
@varOut as int output
AS
Declare
@varPK int
set @varPK = 0
BEGIN
--This checks Method 1
--NSC = @varNSC
--NC = @varNC
--IIN = @varIIN
begin
if exists
(select Item_id
From Item
Where NSC = @varNSC
and NC = @varNC
and IIN = @varIIN)
set @varPK =
(select Item_id
From Item
Where NSC = @varNSC
and NC = @varNC
and IIN = @varIIN)
set @varOut = @varPK
if @varPK <> 0 Return
end
[There are some more methods here]
Return
END
How do I get at the output value?
I have tried using derived column and ole db command but can't seem to grasp how to pass the value to the derived column. I can get oledb command to run using 'exec dbo.spPK ?, ?, ?, ?, ? output' but don't know what to do from here.
View 10 Replies
View Related
Apr 26, 2004
Anyone can help with this question: thanks
in a asp .net application, I call a stored procedure which have a output parameter.
the output parameter works find in sql session, but not in the asp .net application.
if I put select msg_out = "error message" in position A(see below for stored proc), it works fine
if I put them inside the if statement, the output parameter wont work in asp .net application, but fine in SQL session
The stored proc was created like this:
Create procedure XXXXXXX
(@msg_out varchar(80) OUTPUT
)
as
begin
while exists (*******)
begin
//position A
if certain condition
begin
select msg_out = "error message"
return 1
end
end
end
end
It seems to me that anything inside if - the second begin...end - it wont get executed.
Anyone has got a clue
Any help much appreciated!
View 5 Replies
View Related
Jun 1, 2013
how can i define table type argument as OUTPUT in PROC and don't READONLY? if it's not possible, is there another way to do same this?
View 1 Replies
View Related
Apr 28, 2004
Please any help would be much appreciated !!
I have developed many stored procedures which run off tables in my SQL database. The stored procedures take two criteria and return summary data for use in Access db reports. [I take the data via ADO to a mdb].
I have just written a large sp which takes the criteria amends data in tables and then should produce results using a select statement at the end. The correct data has moved around, but no rows are returned.
The really odd thing is that rows are returned to grid when run from analyzer, which is where I was developing it. Is this a problem with Access/ADO or something because it is going to drive me crazy.... :confused:
View 6 Replies
View Related
Jul 23, 2005
I'm using a stored proceedure which should update a number of rows in atable depending on a key value supplied (in this case 'JobID'). Butwhat's happening is when I call the proc from within the program, onlyone row gets updated.SoWhen I call the proc from Query Analyser, all rows get updated.When I call the proc from within the program, only one row gets updatedAny ideas as to why this is happening??JobID Description Price Status----------------------------------------------73412 Documents:Item 3 .00 073412 Documents:Item 5 .00 073412 Documents:Item 2 .00 073412 Documents:Item 4 .00 073412 Documents:Item 1 .00 0^^^^Only one record gets updated, so the table ends up being...JobID Description Price Status----------------------------------------------73412 Documents:Item 3 .00 473412 Documents:Item 5 .00 073412 Documents:Item 2 .00 073412 Documents:Item 4 .00 073412 Documents:Item 1 .00 0Public Sub UpdateAllItems() As BooleanDim objCnn As ADODB.ConnectionDim objCmd As ADODB.CommandSet objCnn = New ADODB.ConnectionWith objCnn.ConnectionString = cnConn.CursorLocation = adUseClient.OpenEnd WithSet objCmd = New ADODB.CommandSet objCmd.ActiveConnection = objCnnWith objCmd.CommandText = "sp_UpdateJobItem".CommandType = adCmdStoredProc.Parameters.Append .CreateParameter("@Status", adInteger,adParamInput, 4, Me.Status).Parameters.Append .CreateParameter("@JobID", adInteger,adParamInput, 4, Me.iJobID).ExecuteEnd WithSet objCnn = NothingSet objCmd = NothingEnd Sub-----------------------------------------------------------------SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOALTER PROCEDURE dbo.sp_UpdateJobItem@JobID As int, @Status As intAS--================================================== ===========================================SET XACT_ABORT OFF -- Allow procedure to continue aftererrorDECLARE @error integer -- Local variable to capture theerror OnHoldAction.--================================================== ===========================================BEGIN TRANSACTIONUPDATE tbl_JobItemsSET Status = @statusWHERE JobID = @JobID--================================================== ===========================================-- Check for errors--================================================== ===========================================SELECT @error = @ERRORIf @error > 0BEGINROLLBACK TRANSACTIONENDElseBEGINCOMMIT TRANSACTIONENDGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO
View 13 Replies
View Related
Feb 8, 2007
SQL 2k, DDL below.I have a simple table with the following data:fldYear fldCode1 fldCode22000 ABC1 ABC122000 ABC1 ABC132001 ABC1 ABC122002 ABC1 ABC122002 ABC1 ABC13I need to know, for every distinct combination of fldCode1 andfldCode2, if there are any years missing.For example,SELECT DISTINCT fldCode1, fldCode2 FROM MyTablereturnsABC1 ABC12ABC1 ABC13I need to know that in 2001 there was no entry for ABC1/ABC13Thanks!Edwardif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MyTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[MyTable]GOCREATE TABLE [dbo].[MyTable] ([fldYear] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[fldCode1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[fldCode2] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GO
View 2 Replies
View Related
Oct 20, 2015
I have a table of languages, identified by a lang_id column as follows:
LANG_IDLANG_NAME
deDeutsche
enEnglish
plPolski
trTurkish
czCzech
I also have a RESOURCES table of phrases (for translation purposes) similar to this:
res_id res_lang res_phrase
AccessDenied en Access Denied
For some rows in the resources table I do not have all language codes present so am missing some translations for a given res_id.My question is what query can I use to determine the RESOURCE.RES_IDs for which I do not have a translation for.
For example I might have a de, en, cz translation for a phrase but not a pl phrase and I need to identofy those rows in order that I can obtain translations for the missing RESOURCE rows.
View 6 Replies
View Related
Jul 29, 2015
In a t-sql 2012 sql update script listed below, it only works for a few records since the value of TST.dbo.LockCombination.seq only contains the value of 1 in most cases. Basically for every join listed below, there should be 5 records where each record has a distinct seq value of 1, 2, 3, 4, and 5. Thus my goal is to determine how to add the missing rows to the TST.dbo.LockCombination where there are no rows for seq values of between 2 to 5. I would like to know how to insert the missing rows and then do the following update statement. Thus can you show me the sql on how to add the rows for at least one of the missing sequence numbers?
UPDATE LKC
SET LKC.combo = lockCombo2
FROM [LockerPopulation] A
JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type
JOIN TST.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber = LKR.number
[Code] ....
View 10 Replies
View Related
Dec 20, 2007
I need to transform Foxpro table to SQL Server table with merging all rows into one where all column values are the same except one . For this the only column with the different values , I want them also to be merged as coma or space delimited string. The question whether SSIS is a good candidate for this kind of data munging and also would be interested to know knowing as many as possible ways of doing that. Surely I may produce Foxpro script in 5 minutes which wil do that and be a pre-processor action before SSIS starts.
View 3 Replies
View Related
Feb 13, 2008
I 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
View 3 Replies
View Related
Jul 17, 2005
Need some suggestions on what to check or look for.My stored proc is not finding the table in the sql db. This is the error...Invalid object name 'tblServRec'I use the same function to pass in an arraylist and the sp name for another sp and that one works ok.The sp is the same as another one except for the table name and that one works ok.The sp works fine on my local machine. It finds the table ok. But when I try it on the server, it doesn't work.I have checked permissions, and they are the same for all tables, even the one that the other sp works ok on the server.Here is the sp if that will help.....CREATE PROCEDURE dbo.deleteServRec
@fldNum INT ASBEGIN DECLARE @errCode INT
DELETE FROM tblServRec WHERE fldNum = @fldNum SET @errCode = 0 RETURN @errCode
HANDLE_APPERR:
SET @errCode = 1 RETURN @errCodeENDGOThanks all,Zath
View 4 Replies
View Related
Mar 29, 2006
I take the information below in query analyzer and everything runs fine-returns 48 rows. I try to run it as a stored proc and I get no records. If I put a simple select statement into the stored proc, it runs, so it's not permissions. Can anyone help me with why this won't execute as a stored procedure? Articles seem to indicate you can do this with temp tables in a stored procedure. Thanks
declare
@style as int,
@disc as int,
@key as varChar(500),
@sdate as varChar(15),
@edate as varChar(15),
@ld as varChar(15)
set @style=0
set @disc=0
set @sdate='3/1/2006'
set @ld='2'
create table #ListAll (wid int, parentID int, myFlag int)
insert into #ListAll
SELECT top 100 percent wid, parentID, 0 as myFlag FROM myTable WHERE (@style=0 or styleID=@style)
and (@edate is null or start_date < @edate)
and ((start_date is null) or (datediff(day,start_date,@sdate) <1))
and (@ld='9' or charIndex(convert(varchar(1),datepart(dw,start_dat e)),@ld)>0)
and wid in (select wid from myTable2 where (@disc=0 or discID=@disc))
and wid in (select wid from myTable where @key is null or ([title] like '%' + @key + '%' or [keywords] like '%' + @key + '%'))
update #ListAll set myFlag=1 where parentID<>0
insert into #ListAll
select w.wid, w.parentID, 0 as myFlag from myTable w right join #ListAll on #ListAll.parentID=w.wid where #ListAll.parentID<>0
delete #ListAll where myFlag=1
SELECT top 100 percent srt, w.WID, w.parentID, w.[title], w.start_date, w.end_date, w.cancelled, w.url, styleID, w.[keywords], w.onlineID, w.httplocation, datepart(dw,w.start_date) as lddate
FROM myTable w
right join #ListAll on #ListAll.wid=w.wid
ORDER BY srt, start_date, [title]
drop table #ListAll
GO
View 4 Replies
View Related
Sep 29, 2004
Recently someone told me that I could use a Parameter in a Stored Proc as a text placeholder in the SQL Statement. I needed to update a table by looping thru a set of source tables. I thought NOW IS MY TIME to try using a parameter as a table name. Check the following Stored Proc
CREATE PROCEDURE [dbo].[sp_Update]
@DistributorID int,
@TableName varchar(50)
AS
UPDATE C
SET C.UnitCost = T.[Price]
FROM (tbl_Catalog C INNER JOIN @TableName T ON C.Code = T.Code)
GO
NEEDLESS TO SAY this didn't work. In reviewing my references this seems to be a no no.
Is it possible to use a parameter as a table name? OR is there another way to do this?
Thanks in advance for your help!
View 3 Replies
View Related
May 22, 2008
Hi All,
We have modified a column name for a table. Now we need to find out all the database objects (stored procedures, functions, views) which access this table so that we can modify them. This is a very big database and its not easy to open the code for each object and check if its access that table.
To add to the complexity, its not just one table and column but a number of tables have been modified.
Any pointers is highly appreciated
Thanks,
Krishna.
View 3 Replies
View Related
Apr 24, 2007
I am looking to populate a Schedule table with information from twoother tables. I am able to populate it row by row, but I have createdtables that should provide all necessary information for me to beableto automatically populate a "generic" schedule for a few weeks ormoreat a time.The schedule table contains:(pk) schedule_id, start_datetime, end_datetime, shift_employee,shift_positionA DaysOff table contains:(pk) emp_id, dayoff_1, dayoff_2 <-- the days off are entered in dayofweek (1-7) formA CalendarDays table contains:(pk) date, calendar_dow <-- dow contains the day of week number (asabove) for each day until 2010.My main question is how to put all of this information together andhave SQL populate the rows with data based on days off, for a fewweeks in advance. Anysuggestions?
View 4 Replies
View Related
Apr 25, 2007
I am looking to populate a Schedule table with information from twoother tables. I am able to populate it row by row, but I have createdtables that should provide all necessary information for me to beableto automatically populate a "generic" schedule for a few weeks ormoreat a time.The schedule table contains:(pk) schedule_id, start_datetime, end_datetime, shift_employee,shift_positionA DaysOff table contains:(pk) emp_id, dayoff_1, dayoff_2 <-- the days off are entered in dayofweek (1-7) formA CalendarDays table contains:(pk) date, calendar_dow <-- dow contains the day of week number (asabove) for each day until 2010.My main question is how to put all of this information together andhave SQL populate the rows with data based on days off. Anysuggestions?
View 2 Replies
View Related
Jul 20, 2005
Hello,Does anyone know of a way to loop thru a SQL table using code in a storedprocedure?I need to go thru each record in a small table and build a string usingvalues from the fields associated with a part number, and I can't find anyway to process each record individually. The string needs to be initializedwith the data associated with the 1st record's part number, and I need tobuild the string until a new part number is incurred. Once a new part numberis found in the table, the string is written to a different table and resetfor this next part number in the table. Need to repeat until all records inthe table have been processed.I use ADO in access 2000 to work thru local recordsets, I just can't findanyway to do this in a stored SQL procedure.Thanks for any suggestions, Eric.
View 1 Replies
View Related
Dec 25, 2006
While using Aggregate Transformation to group one column,the rows of output sometimes larger than the rows returned by a T-SQL statement via SSMS.
For example,the output of the Aggregate Transformation may be 960216 ,but the
'Select Count(Orderid) From ... Group By ***' T-SQL Statement returns 96018*.
I'm sure the Group By of the Aggregate Transformation is right!
But ,when I set the "keyscale" property of the transformation,the results match!
In my opinion,the "keyscale" property will jsut affects the performance of the transformaiton,but not the result of the transformation.
Thanks for your advice.
View 2 Replies
View Related
Nov 8, 2004
i'm trying to create a stored procedure that takes 2 input parameters (taken from a querystring): a table name, and a number that needs to be checked whether it exists within that table. yes, i have different tables, one for each forum, so they will have the same structures -- i'm figuring this approach will result in faster page-load times if the tables have less in them. if everyone here tells me that having one table for all the forums will work fine, i'll just go that route, but if not, here's my procedure:
Create Procedure VerifySubjectNum
(@forum_ID VARCHAR(10), @subject_num INT)
As
If Exists
(SELECT subject_num FROM @forum_ID WHERE subject_num = @subject_num)
Return 1
Else
Return 0
when i try to create this, i get an error saying @forum_ID must be declared... why doesn't it work within the select for the EXISTS?
View 6 Replies
View Related
