I have a table I insert a record into to give access to a user. It uses
primary keys so duplicates are not allowed, so trying to add a record
for a user more than once is not allowed.
In my .NET programs, sometimes it's easier to let the user select a
group of people to give access to eben if some of them may already have
it.
Of course this throws an exception an an error message. Now I could
catch and ignore the message in .NET for this operation but then I'm
stuck if something is genuinely wrong.
So is there a way to do this? :
In my stored procedure determine if an error occured because of a
duplicate key and somehow not cause an exception to be returned to
ADO.NET in that case?
I am trying to create a page that adds users to a MS SQL database. In doing so, I have run into a couple errors that I can't seem to get past. I am hoping that I could get some assistance with them.
Error from SQL Debug: --- Server: Msg 295, Level 16, State 3, Procedure AdminAddUser, Line 65 [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting character string to smalldatetime data type. ---
Error from page execution: --- Exception Details: System.Data.OleDb.OleDbException: Error converting data type varchar to numeric.
Source Error:
Line 77: cmd.Parameters.Add( "@zip", OleDbType.VarChar, 100 ).Value = Request.Form("userZip") Line 78: Line 79: cmd.ExecuteNonQuery() ---
Below is what I currently have for my stored procedure and the pertinent code from the page itself.
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
I have the following stored proc that is providing the following error - Server: Msg 156, Level 15, State 1, Line 79[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'ORDER'. The sp works fine if the DateOfBirth elements are removed. I think it is something to do with the way in which the date variable is incorporated into the string that will be executed by the EXEC command but I'm unsure as to what is wrong. CREATE PROCEDURE dbo.GetPersonsByName (@FirstName varchar(50)=NULL, @FamilyName varchar(50)=NULL, @DateOfBirth datetime=NULL) AS EXEC ('SELECT PersonId, Title, FirstName, FamilyName , AltFamilyName, Sex, DateOfBirth, Age, DateOfDeath, CauseOfDeath, Height, Weight, ABO, RhD, Comments, LocalIdNo, NHSNo, CHINo, Hospital, HospitalNo, AltHospital, AltHospitalNo, EthnicGroup, Citizenship, NHSEntitlement, HomePhoneNo, WorkPhoneNo, MobilePhoneNo, CreatedBy, DateCreated, UpdatedBy, DateLastUpdated FROM vw_GetPersons WHERE FirstName LIKE ''%' + @FirstName + '%'' AND FamilyName LIKE ''%' + @FamilyName + '%'' AND DateOfBirth = '+ @DateOfBirth +' ORDER BY FamilyName, FirstName') GO
Can anyone see an error here? I am getting a general exception when call this and it is driving me crazy. What am I missing? The error is.....{"Incorrect syntax near 'UpdateStaffDept'." ... Any help is greatly appreciated! Below is the code calling the proc Thank you! set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[UpdateStaffDept] @Co_Id INT, @CardNo VARCHAR(50), @DeptNo VARCHAR(50)
AS SET NOCOUNT ON
DECLARE @Error INT
SELECT @Error = @@Error IF @Error = 0 BEGIN
BEGIN TRANSACTION UPDATE Staff SET Dept_No = @DeptNo WHERE Co_Id = @Co_Id AND PC_Id IN (SELECT PC_ID FROM PC_Number WHERE PC_Number = @CardNo)
SELECT @Error = @@Error IF @Error <> 0 BEGIN ROLLBACK TRANSACTION RETURN @Error END ELSE COMMIT TRANSACTION
END
Dim seldeptno As String seldeptno = ddlEditPosDept.SelectedValue Dim cmdChnDept As SqlCommand cmdChnDept = New SqlCommand("UpdateStaffDept", conEditPos) cmdChnDept.Parameters.Add(New SqlParameter("@Co_Id", SqlDbType.Int 10)) cmdChnDept.Parameters("@Co_Id").Value = compid cmdChnDept.Parameters.Add(New SqlParameter("@CardNo", SqlDbType.VarChar, 50)) cmdChnDept.Parameters("@CardNo").Value = lblEditPosCardNo.Text cmdChnDept.Parameters.Add(New SqlParameter("@DeptNo", SqlDbType.VarChar, 50)) cmdChnDept.Parameters("@DeptNo").Value = seldeptno cmdChnDept.ExecuteNonQuery()
I come from an Access/VB background where in an error trapping routine or testing for an error your statement "exit sub" ended the process. WHen using a stored procedure and you come across an error what is the best method to exit/end the stored proc?
I have a stored proc that loops through records using a cursor. If during the loop an error occurs i need to trap the error and write it to a log file. Error handling needs to be placed in the update and insert sections and the error that occurs needs to get recorded along current row from the cursor.
Here is the code: declare @tier_id int, @tier_desctext varchar(50), @tier_shortdesc varchar(50), @long_name varchar(50), @short_name varchar(50), @rec_count int
--Cursor for UES data DECLARE cr_ues_tier INSENSITIVE CURSOR FOR SELECT DISTINCT tier_id, tier_desctext, tier_shortdesc FROM "dbsourcedayoldprod".ues.dbo.Tiers
OPEN cr_ues_tier
--Select cursor values into local variables FETCH NEXT FROM cr_ues_tier INTO @tier_id, @tier_desctext, @tier_shortdesc WHILE (@@FETCH_STATUS <> -1) BEGIN
--Set Data Mart variables SELECT @long_name = long_name, @short_name = short_name FROM uesrpt..coverage_level WHERE ues_tier_id = @tier_id
--Set the record counter SET @rec_count = (select ues_tier_id from uesrpt..coverage_level where ues_tier_id = @tier_id)
--Are there exsisting records if so go to the update section IF @rec_count <> 0
--Update Data Mart values if they have changed BEGIN IF @long_name <> @tier_desctext OR (@long_name IS NULL AND @tier_desctext IS NOT NULL) OR (@long_name IS NOT NULL AND @tier_desctext IS NULL)
OR @short_name <> @tier_shortdesc OR (@short_name IS NULL AND @tier_shortdesc IS NOT NULL) OR (@short_name IS NOT NULL AND @tier_shortdesc IS NULL)
UPDATE uesrpt..coverage_level SET long_name = @tier_desctext, short_name = @tier_shortdesc WHERE ues_tier_id = @tier_id END
--Rows don't exsist in the Data Mart - Insert new rows ELSE INSERT INTO uesrpt..coverage_level (ues_tier_id, long_name, short_name) SELECT @tier_id, @tier_desctext, @tier_shortdesc
--Get next row from UES cursor FETCH NEXT FROM cr_ues_tier INTO @tier_id, @tier_desctext, @tier_shortdesc END
This query keeps giving me a Primary Key violation error and I am not sure what it is getting at. maybe you can help?
truncate table tbl_rtv_cosmetic_dif go insert tbl_RTV_COSMETIC_DIF select d.fisc_yr ,d.fisc_pd ,d.fisc_wk ,d.fisc_dy ,substring(a.long_sku,1,4) as DeptNo ,substring(a.long_sku,7,3) as VendorNo ,substring(a.long_sku,10,5) as MarkStyleNo ,a.Loc as LocNo ,convert(int,a.RsnCd) as ReasonCode ,min(a.EAN_Nbr) as UPCEANNo ,coalesce(i.own_rtl,0) as OwnedCur ,sum(convert(int,a.units)) as UPCUnits
from november a inner join generalinfo..tbl_fisc_date d on a.date = d.cal_date
left outer join itemdata..tbl_item_import_history i on substring(a.long_sku,1,4) = i.dept and substring(a.long_sku,7,3) = i.vendor and substring(a.long_sku,10,5) = i.mrk_style and d.fisc_yr = i.amc_yr and d.fisc_pd = i.amc_pd and d.fisc_wk = i.amc_wk
group by fisc_yr,fisc_pd,fisc_wk,fisc_dy ,substring(long_sku,1,4),substring(long_sku,7,3),substring(long_sku,10,5) ,loc,rsncd,own_rtl
Hi all,I have a sproc that uses OpenRowset to an Oracle db. If OpenRowseterrors, it terminates the procedure. I need it to continueprocessing. Is there any workaround for this?ThanksPachydermitis
When I try to deploy this managed stored proc assembly on SQL Server 2005 I get this error: Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x8007000e. You may fix the problem and try again later I looked on the net but found no documentation about it. I know the CLR v2.0 is working as I have some ASP.NET apps running from this server that use it. Any ideas?
i try create this example:http://www.codeproject.com/KB/webforms/ReportViewer.aspxI have Northwind database added in the SQL server management and i select Northwind databse in drop box and I push Execute!ALTER PROCEDURE ShowProductByCategory(@CategoryName nvarchar(15) )ASSELECT Categories.CategoryName, Products.ProductName, Products.UnitPrice, Products.UnitsInStockFROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryIDWHERE CategoryName=@CategoryNameRETURNbut error is:Msg 208, Level 16, State 6, Procedure ShowProductByCategory, Line 11Invalid object name 'ShowProductByCategory'.on web not so clear what is issue pls. help
I have a Stored Proc that is called by a SQL Job in SQL Server 2000. This stored proc deadlocks once every couple of days. I'm looking into using the @@error and try to doing a waitfor .5 sec and try the transaction again. While looking around google I've come across a few articles stating that a deadlock inside a Stored Proc will stop all execution of the stored proc so I will not be able doing any error handling. Is this true? Does anyone have any experience that could help me out?
I know the best solution would be to resolve why I get a deadlock. We are currently looking into that but until we can resolve those issues I would like to get some type of error handling in place if possible.
I feel like I'm missing something obvious here, but I'm stumped...I have a stored procedure with code that looks like:INSERT INTO MyTableA ( ...fields... ) VALUES (...values...)IF (@@ERROR <> 0)BEGINROLLBACK TRANSACTION;RAISERROR('An error occurred in the stored proc.', 16, 1);RETURN(1);END--FORCING AN ERROR AT THE END FOR TESTING PURPOSESRAISERROR('Proc Successful',16,1)On MyTableA, there is a trigger that loops through the inserted data andstops the insert in certain circumstances, returning an error:IF (some criteria)BEGINROLLBACKRAISERROR('An error occurred in the trigger.',16,1)RETURNENDWhen I call the stored procedure from VB (connecting via RDO) witherror-causing data, the trigger successfully stops the insert, and adds thetrigger-error-msg to the errors collection, but it does NOT seem to createan error situation back in the stored procedure. The procedure finishes upwith the "Proc Successful" message, so that when I iterate through theerrors collection back in VB, I have "Proc Successful" followed by "An erroroccurred in the trigger."Is there some way I'm not finding to have the calling procedure recognizethat a raiserror occurred in the trigger and behave appropriately for anerror situation?Jen
Hi,I a stored procedure that inserts a record into a table asbelow.The insert works OK, but if the insert violates a uniqueindewx constraint on one of the columns, the proc terminatesimmediately, and does NOT execute the 'if @@ERROR <> 0'statement.Am I doing something wrong, or do I need to set an attributesomewhere?tia,Billbegin traninsert into Users(UserName, UserPWD, Lname, Fname, UserDesc)values (@userName, @userPWD, @lname, @fname, @userDesc)if @@ERROR <> 0beginrollback transet @returnCode = -2set @errMsg = 'SQL error '+ convert(varchar(6), @@ERROR)+ ' occurred adding user '+ @userNameend
I am trying to add a data driven subscription to my report. When I get to the screen to enter the Delivery Query, I enter the stored procedure (GetStatusReportData) in the query box. When i try to validate, I get a "Query is not valid" error. If I replace the stored procedure name with a generic query (select * from jobs), it works fine.
Is there some sort of syntax I need to use to enter a stored procedure here? I have used "exec GetStatusReportData" and that did not work either.
I am attempting to compile a stored proc that contains SQL statements that access databases across different SQL servers. I am getting the following error:
"Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query."
Does this mean that I need to put the necessary statements (Set ANSI_NULLS ON etc.) in my stored proc or that I need to configure my SQL Server differently somehow? I tried the former without success.
Finally found what is causing my .net c# service application to return with Error 18456 (NT Authority/anonymous logon" severity 14 State 11.
It is connecting to a sql server db in another machine and running a stored procedure in that db. I found out that if I remove that two statements that create temp tables (with select INTO), the stored procedure executes without a problem.
Note that this stored procedure updates the tables does not have problems updated tables that are in the DB. I gets upset when creating temp tables though.
I cannot do away with those temp tables as they are needed in the calculation. How can I fix this problem?
Why would creating temp tables remotely cause this error? What am I missing in my set up. My service application connects with the integrated security = true. My service process installer has the account set to "User". What am I missing.
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
I 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':
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?
Hi 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
I 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.
I have a stored procedure "uspX" that calls another stored procedure "uspY" and I need to retrieve the return value from uspY and use it within uspX. Does anyone know the syntax for this?
I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement
Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc
The SELECT statement in question retrieves a single row from a table containing 10 columns.
Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc?
I know about stored proc return values and about output parameters, but I think I am looking for something different.
I would like to know if the following is possible/permissible:
myCLRstoredproc (or some C# stored proc) { //call some T SQL stored procedure spSQL and get the result set here to work with
INSERT INTO #tmpCLR EXECUTE spSQL }
spSQL (
INSERT INTO #tmpABC EXECUTE spSQL2 )
spSQL2 ( // some other t-sql stored proc )
Can we do that? I know that doing this in SQL server would throw (nested EXECUTE not allowed). I dont want to go re-writing the spSQL in C# again, I just want to get whatever spSQL returns and then work with the result set to do row-level computations, thereby avoiding to use cursors in spSQL.
I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.
Hi Peeps I have a SP that returns xml I have writen another stored proc in which I want to do something like this:Select FieldOne, FieldTwo, ( exec sp_that_returns_xml ( @a, @b) ), FieldThree from TableName But it seems that I cant call the proc from within a select. I have also tried declare @v xml set @v = exec sp_that_returns_xml ( @a, @b) But this again doesn't work I have tried changing the statements syntax i.e. brackets and no brackets etc..., The only way Ive got it to work is to create a temp table, insert the result from the xml proc into it and then set @v as a select from the temp table - Which to be frank is god awful way to do it. Any and all help appreciated. Kal
I didn't want to maintain similar/identical tables in a legacy FoxPro system and another system with SQL Server back end. Both systems are active, but some tables are shared.
Initially I was going to use a Linked Server to the FoxPro to pull the FP data when needed. This works. But, I've come up with what I believe is a better solution. Keep in mind that these tables are largely static - occassional changes, edits.
I will do a 1 time DTS from FP into SQL Server tables.
I then create INSERT and UPDATE triggers within FoxPro.
These triggers fire a stored procedure in FoxPro that establishes a connection to the SQL Server and fire the appropriate stored procedure on SQL Server to CREATE and/or UPDATE the corresponding table there.
In the end - the tables are local to both apps.
If the UPDATES or TRIGGERS fail I write to an error log - and in that rare case - I can manually fix. I could set it up to email me from within FoxPro as well if needed.
Here's the FoxPro and SQL Server code for reference for the Record Insert:
Im trying to perform an update with a stored procedure thats all working but an exception is thrown ....violation of primary key contraint....cannot insert duplicate pri key I understand whats going on but how do you update some ones details if its protected this way. ?