Checking For @@ERROR After A SELECT Inside A Transaction?
Is it normal practice to check for @@ERROR after a SELECT statement that retrieves data from a table OR we should only check for @@ERROR after a DELETE/INSERT/UPDATE type of statement? The SQL statement is inside a transaction.
View Complete Forum Thread with Replies
Related Forum Messages:
EXEC Inside CASE Inside SELECT
I'm trying to execute a stored procedure within the case clause of select statement. The stored procedure returns a table, and is pretty big and complex, and I don't particularly want to copy the whole thing over to work here. I'm looking for something more elegant. @val1 and @val2 are passed in CREATE TABLE #TEMP( tempid INT IDENTITY (1,1) NOT NULL, myint INT NOT NULL, mybool BIT NOT NULL ) INSERT INTO #TEMP (myint, mybool) SELECT my_int_from_tbl, CASE WHEN @val1 IN (SELECT val1 FROM (EXEC dbo.my_stored_procedure my_int_from_tbl, my_param)) THEN 1 ELSE 0 FROM dbo.tbl WHERE tbl.val2 = @val2 SELECT COUNT(*) FROM #TEMP WHERE mybool = 1 If I have to, I can do a while loop and populate another temp table for every "my_int_from_tbl," but I don't really know the syntax for that. Any suggestions?
View Replies !
Transaction Inside Sp_executesql
Hi to all,Probably I'm just doing something stupid, but I would like you to tellme that (if it is so), and point the solution.There ist the thing:I' having a sp, where I call other sp inside.The only problem is, the name of this inside sp is builded variously,and executed over sp_executesql:create pprocedure major_sp@prm_outer_1 varchar(1),@prm_outer_2 varchar(2)assome codingset @nvar_stmtStr = N'exec @int_exRetCode = test_sp_' + @prm_outer_1 +@prm_outer_2set @nvar_stmtStr = @nvar_stmtStr + ' @prm_1, @prm_2, @prm_3, @prm_4output'set @nvar_prmStr = N'@prm_1nvarchar(128), ' +N'@prm_2nvarchar(128), ' +N'@prm_3nvarchar(4000), ' +N'@int_exRetCodeint output, ' +N'@prm_4varchar(64) output'exec sp_executesql @nvar_stmtStr,@nvar_prmStr,@prm_1,@prm_2,@prm_3,@int_exRetCode = @int_exRetCode output,@prm_4 = @prm_4 outputNow the issue is, I've transactions inside test_sp_11 lets say wherethe 11 is @prm_outer_1 + @prm_outer_2.These procedures are existing inside database, but are called dynamiclydepending of the parameters.The problem is, when I call the specified sp directly, the rollbacktransaction is working without any problem.Inside this procedures test_sp_xx, is a call of another sp (lets sayinside_sp).There is a transaction included.When it is called over major_sp, then the rollback is not performedbecause of error:Server: Msg 6401, Level 16, State 1, Procedure inside_sp, Line 54Cannot roll back transactio_bubu. No transaction or savepoint of thatname was found.The funniest way is, if there is no error inside, the commit is workingwithout any problem!The question is majory (because I'm almost sure, that this is anissue): is it possible, to have atransaction inside dynamicly called sp over sp_executesql?If ok to do that?Thank's in advanceMatik
View Replies !
Using Sp_droprolemember Inside A Transaction
I have a trigger which requires dropping a member from a role and includes user transactions. However you cannot call sp_droprolemember from within a user transaction. Looking at the code for the procedure gives me a line quote: exec %%Owner(Name = @membername).SetRoleMember(RoleID = @roluid, IsMember = 0) I can find no documentation on SetRoleMember or %%owner and attempts to run this line as an exec statement fails with "syntax error near '%'" Nor can I find any way of dropping a member from a role using T-SQL or DDL constructs. How do I drop a member from a role using T-SQL code and avoiding sp_droprolemember? And where do I find information about the %%Owner construct?
View Replies !
Problem With Transaction Inside Loop
Hi, When i execute the following set of statements only 8 is getting inserted into table instead 6 and 8. Create Table BPTest(id int) Declare @Id Int Set @Id = 0 While (@Id < 10) Begin begin tran Insert into BPTest values (@id) if(@Id > 5) begin if(@Id % 2 = 0) begin print 'true' print @Id commit tran end else begin print 'false' print @Id rollback tran end end Set @Id = @Id + 1 End Select * from BPTest drop table BPTest Please let me know the reason for this. Thanks in advance Regards, K. Manivannan
View Replies !
Problem Retrieving @@Identity When Inside A Transaction.
I'm using transactions with my SqlConnection ( sqlConnection.BeginTransaction() ). I do an Insert into my User table, and then subsequently use the generated identity ( via @@identity ) to make an insert into another table. The foreign key relationship is dependant on the generated identity. For some reason with transactions, I can't retrieve the insert identity (it always returns as 1). However, I need the inserts to be under a transaction so that one cannot succeed while the other fails. Does anyone know a way around this problem? So heres a simplefied explanation of what I'm trying to do: Begin Transaction Insert into User Table Retrieve Inserted Identity (userID) via @@Identity Insert into UserContact table, using userID as the foreign key. Commit Transaction. Because of the transaction, userID is 1, therefore an insert cannot be made into the UserContact table because of the foreign key constraint. I need this to be a transaction in case one or the other fails. Any ideas??
View Replies !
Transaction Inside A Data Flow Task
We are designing an ETL solution for a BI project using SSIS. We need to load a dimension table from a source DB into the DW; inside the DW there are two tables for each source dimension table: a current dimension table and a storical dimension table. The source table includes technical columns that indicate if each record was processed correctly by ETL procedures. Each row in the source table can be a new record, or an exisisting one. If it's a new record, a corresponding new record should be inserted into the current dimension table of the DW; if it's an exisisting one, a new record should be inserted in the storical dimension table and the existing record in the current dimension table should be updated. Furthermore for each record we need to update the source table with an error code. If the record was processed with succes the code is zero, otherwise it contains an error code. I try to use a single data flow task, using the 'ole db command trasformation' task for managing update and 'ole db destination' task for managing insert. The problem is that some insert and update should be executed inside a single transaction, for each record; for example if the source contains a new record I should insert the record in the current dimension table and update the source record with error code zero if every think goes fine. There is some way to group task in the data flow pane in a single transaction ? There is a better way to do that ? Cosimo
View Replies !
Slow Execution Of Queries Inside Transaction
I have some VB.NET code that starts a transaction and after that executes one by one a lot of queries. Somehow, when I take out the transaction part, my queries are getting executed in around 10 min. With the transaction in place it takes me more than 30 min on one query and then I get timeout. I have checked sp_lock myprocessid and I've noticed there are a lot of exclusive locks on different objects. Using sp_who I could not see any deadlocks. I even tried to set the isolation level to Read UNCOMMITED and still have the same problem. As I said, once I execute my queries without being in a transaction everything works great. Can you help me to find out the problem? Thanks, Laura
View Replies !
Transaction Inside A Transaction
Hi, I'm doing some unit testing using a .NET interface. how I'm doing this is to create a transaction whenever I begin a test, and to roll it back whenever I finish with it. That way i can do whatever I want to the database, and it will get reverted. The only problem I have is... some of my methods already implement transactions because they may need to call multiple queries or stored procedures. Im unsure of how transactions work, but is it possible to make a transaction inside a transaction? So the test itself would be transaction, (which gets rolled back after the test finishes) and then there will also be one or more transaction inside the test itself? Thanks
View Replies !
How To Read Data From Remote Server Inside A Transaction?
Hello, everyone: I have a local transaction, BEGIN TRAN INSERT Z_Test SELECT STATE_CODE FROM View_STATE_CODE COMMIT View_STATE_CODE points to remote SQL server named PROD. There is error when I run this query: Server: Msg 8501, Level 16, State 1, Line 12 MSDTC on server 'PROD' is unavailable. Server: Msg 7391, Level 16, State 1, Line 12 The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d01c]. It looks like remote server is not available inside the local transaction. How to handle that? Thanks ZYT
View Replies !
CREATE FULLTEXT CATALOG Inside A User Transaction.
hi: I try to create full text for new created tables. Since all new created tables will have same columns with different table name. After I run the stored procedure to create table, after i got the new table name, I would like to create full-text on that table in the DDL triger. But I got error like this: CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction. Any one has idea how to deal with it? Thanks
View Replies !
Error 8525: Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.
Hi All I'm getting this when executing the code below. Going from W2K/SQL2k SP4 to XP/SQL2k SP4 over a dial-up link. If I take away the begin tran and commit it works, but of course, if one statement fails I want a rollback. I'm executing this from a Delphi app, but I get the same from Qry Analyser. I've tried both with and without the Set XACT . . ., and also tried with Set Implicit_Transactions off. set XACT_ABORT ON Begin distributed Tran update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TRANSACTIONMAIN set REPFLAG = 0 where REPFLAG = 1 update TSADMIN.TRANSACTIONMAIN set REPFLAG = 0 where REPFLAG = 1 and DONE = 1 update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.WBENTRY set REPFLAG = 0 where REPFLAG = 1 update TSADMIN.WBENTRY set REPFLAG = 0 where REPFLAG = 1 update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.FIXED set REPFLAG = 0 where REPFLAG = 1 update TSADMIN.FIXED set REPFLAG = 0 where REPFLAG = 1 update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.ALTCHARGE set REPFLAG = 0 where REPFLAG = 1 update TSADMIN.ALTCHARGE set REPFLAG = 0 where REPFLAG = 1 update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TSAUDIT set REPFLAG = 0 where REPFLAG = 1 update TSADMIN.TSAUDIT set REPFLAG = 0 where REPFLAG = 1 COMMIT TRAN It's got me stumped, so any ideas gratefully received.Thx
View Replies !
How Do I Imbed A Select Inside A Select
I need a select that gets a value and than appends another value if the criteria is met otherwise nothing is appended. The statement has a select with an imbedded select and when I execute it I get the error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. Thia is a crude sample of the statement SELECT ID + ( select * from tableB where TableB = 0 ) as result1 FROM TableB Why am I getting this error and how do I fix the statement? thanks
View Replies !
Checking The SELECT Statement For An SqlDataAdapter
I'm trying to pass a querystring to an SqlDataAdapter object. To check if the query is a valid SELECT statement, I simply use a try-catch. But dispite the try-catch it still accepts valid INSERT statements. However, in the parameterlist of the SqlDataAdapter the required parameter is a Transact SQL SELECT statement or a stored procedure... Am I doing something wrong? Here is my code:try { my_conn = conn_open(); da = new SqlDataAdapter(query, my_conn); da.Fill(result.resultDataset); my_conn.Dispose(); } catch (Exception e) { result.errMsg = "Database Error: " + e.Message; result.success = false; } Kehil
View Replies !
Problem Returning A Timestamp Column Inside An TSQL Transaction
I cannot manage to fetch the new timestamp value inside a TSQL Transaction. I have tried to Select "@LastChanged" before committing the transaction and after committing the transaction. A TimestampCheck variable is used to get the timestamp value of the Custom Business Object. It is checked against the row updating to see if they match. If they do, the Update begins as a Transaction. I send @LastChanged (timestamp) and an InputOutput param, But I also have the same problem sending in a dedicated timestamp param ("@NewLastChanged"): 1 select @TimestampCheck = LastChanged from ADD_Address where AddressId=@AddressId 2 3 if @TimestampCheck is null 4 begin 5 RAISERROR ('AddressId does not exist in ADD_Address: E002', 16, 1) -- AddressId does not exist. 6 return -1 7 end 8 else if @TimestampCheck <> @LastChanged 9 begin 10 RAISERROR ('Timestamps do not match up, the record has been changed: E003', 16, 1) 11 return -1 12 end 13 14 15 Begin Tran Address 16 17 Update ADD_Address 18 set StreetNumber= @StreetNumber, AddressLine1=@AddressLine1, StreetTypeId=@StreetTypeId, AddressLine2=@AddressLine2, AddressLine3=@AddressLine3, CityId=@CityId, StateProvidenceId=@StateProvidenceId, ZipCode=@ZipCode, CreateId=@CreateId, CreateDate=@CreateDate 19 where AddressId= @AddressId 20 21 select @error_code = @@ERROR, @AddressId= scope_identity() 22 23 if @error_code = 0 24 begin 25 commit tran Address 26 27 select @LastChanged = LastChanged 28 from ADD_Address 29 where AddressId = @AddressId 30 31 if @LastChanged is null 32 begin 33 RAISERROR ('LastChanged has returned null in ADD_Address: E004', 16, 1) 34 return -1 35 end 36 if @LastChanged = @TimestampCheck 37 begin 38 RAISERROR ('LastChanged original value has not changed in ADD_Address: E005', 16, 1) 39 return -1 40 end 41 return 0I do not have this problem if I do not use a TSQL Transaction. Is there a way to capture the new timestamp inside a Transaction, or have I missed something?Thank you,jspurlin
View Replies !
Setting The Transaction Inside A Script Task In A Child Package
I have what I would consider an odd situation that I really need to be able to solve and can't find any information. I have a parent package that is executing a child package, now in that child package I have a script task that is updating some records among other things. I need to know how to tie the updates that are happening in that script task to the transaction in the parent package so that if anything goes wrong anywhere in the package those updates are also rolled back. Can anyone help? Thanks, Jason
View Replies !
Using IF Inside SELECT ?
Is there possibility to use IF conditions inside SELECT statements?For example, can i write something like this:CREATE PROCEDURE [search](@OPTION int,@KEYWORD nvarchar(40))ASBEGINSELECT id FROM projects WHERE title LIKE @KEYWORD IF (@OPTION = 1)THEN (OR description LIKE @KEYWORD)ENDor am i limited to this:....BEGINIF @OPTION = 1SELECT id FROM projects WHERE title LIKE @KEYWORD OR description LIKE@KEYWORDELSESELECT id FROM projects WHERE title LIKE @KEYWORDEND
View Replies !
Using A While Inside A Select Statement
Hi All, Can we use the while loop inside a select statement? Meaning, something like this: Code Block SELECT DATE, WHILE (SELECT TOP 1 DATEPART(HH,DATE) FROM SC_DATEDIMENSION_TABLE) <= 23 (SELECT DATEADD(HH,6,SC_DATEDIMENSION_TABLE.DATE) ) FROM SC_DATEDIMENSION_TABLE What I want to do here is I have a table which has all the dates but with time only representing 00 hrs. I want to display this column and along side, I want to have another column, which displays the date split at 6 hours. So, one left column, there will 4 columns on the right. Hope the question is clear. Thanks a lot. Mannu.
View Replies !
Select Statement Inside UDf
iam trying to rerieve a certain value from one table and i want to use that vaue inside a UDF iam usinf a table valued function as i have to retireve no of values Can i do something like this to retrieve the value SET @Value=Select Value from Table WHERE xyz='some no.' as this value is being calculated by some other fucntion and now this funcation has to use this at runtime.
View Replies !
Pre-Execute AcquireConnection Method Call Fails Inside Sequence Container (transaction Required)
I've created an SSIS package that contains a Sequence Container with TransactionOption = Required. Within the container, there are a number of Execute Package Task components running in a serial fashion which are responsible for performing "Upserts" to dimension and fact tables on our production server. The destination db configuration is loaded into each of these packages using an XML configuration file. The structure of these "Upsert" packages are nearly identical, while some execute correctly and others fail. Those that fail all provide the same error messages. These messages appear during Pre-Execute [Insert new dimension record [1627]] Error: The AcquireConnection method call to the connection manager "DW" failed with error code 0xC0202009. [DTS.Pipeline] Error: component "Insert new dimension record" (1627) failed the pre-execute phase and returned error code 0xC020801C. ... which are followed by [Connection manager "DW"] Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00A "Unable to enlist in the transaction.". [Connection manager "DW"] Error: An OLE DB error has occurred. Error code: 0x8004D00A. While still in debug mode, I can check the properties of the "DW" connection and successfully test the connection within the packages that fail. The same packages run successfully when tested outside the container (i.e. no transaction) or when the configuration file is modified to point the "DW" connection to a development version of the db which is running on the same server as the source database. I have successfully used DTCtester to verify that transactions from source to destination server are working correctly. Also tried setting DelayValidation = True with no change. I have opened a case with Microsoft and am awaiting a reply so I thought I'd throw a post out here to see if anyone else has encountered this and might have a resolution. Here's some more on the environment: Source Server: Windows Server 2003 Enterprise Edition SP1 SQL Server 2005 Enterprise Edition SP0 Destination Server: Windows Server 2003 Enterprise Edition SP1 SQL Server 2000 Enterprise Edition SP3 (clustered) Thank you in advance for any feedback you might be able to provide. KS
View Replies !
Counter Inside Select Statement?
Hi, can you add a counter inside a select statement to get a unique id line of the rows? In my forum i have a page that displays a users past posts in the forum, these are first sorted according to their topicID and then they are sorted after creation date. What i want is in the select statement is to create a counter to get a new numeric order. This is the normal way: SELECT id, post, comment, created FROM forum_posts WHERE (topicID = @topicID) ... some more where/order by statements This is what i want: DECLARE @tempCounter bigintSET @tempCounter = 0SELECT @tempCounter, id, post, comment, created FROM forum_posts WHERE (topicID = @topicID)... some more where/order by statements and at the end.. (SELECT @tempCounter = @tempCounter + 1) Anyone know if this can be done?
View Replies !
Declare Inside Select Statement?
I have a need to execute a cursor inside a select statment, but I'm having problems figuring this out. The reason this need to be inside a select statement is that I am inserting the cursor logic into a query expression in PeopleSoft Query. So! Here's the statement that works: ====================== DECLARE @fixeddate datetime DECLARE @CVG_ELECT char(1) DECLARE @Effdt datetime DECLARE EFFDTS CURSOR FOR SELECT Z.EFFDT, COVERAGE_ELECT FROM PS_LIFE_ADD_BEN Z WHERE Z.EMPLID = '1000' AND Z.EFFDT <= GETDATE() AND Z.PLAN_TYPE = '20' ORDER BY Z.EFFDT DESC OPEN EFFDTS FETCH NEXT FROM EFFDTS INTO @Effdt, @CVG_ELECT WHILE @@FETCH_STATUS = 0 BEGIN if @CVG_ELECT <> 'E' break ELSE SET @fixeddate = @Effdt FETCH NEXT FROM EFFDTS INTO @Effdt, @CVG_ELECT END CLOSE EFFDTS DEALLOCATE EFFDTS PRINT @fixeddate ====================== If I execute this in SQL Query Analyzer it gives me the data I am looking for. However, if I try to paste this into a select statement, it goes boom (actually, it says "Incorrect syntax near the keyword 'DECLARE'.", but you get the idea). Is it possible to encapsulate this inside a select statement?
View Replies !
Select Case Inside Sql Statement ?
Code: function findingcinemaid(nameofthecinema) findcinemaid = "select cinemasid from cinemas" &_ " where brand = 'tgv' and cinemaplace2 like '"&nameofthecinema&"'" set cinemaidfound = objconndb.execute (findcinemaid) end function select case foreachcinema case 0 cinemaname = "ONE UTAMA" findingcinemaid(cinemaname) case 1 cinemaname = "MINES" findingcinemaid(cinemaname) case 2 cinemaname = "SEREMBAN 2" findingcinemaid(cinemaname) case 3 cinemaname = "KINTA CITY" findingcinemaid(cinemaname) case 4 cinemaname = "BUKIT RAJA" findingcinemaid(cinemaname) case 5 cinemaname = "TEBRAU CITY" findingcinemaid(cinemaname) case 6 cinemaname = "SUNWAY PYRAMID" findingcinemaid(cinemaname) case 7 cinemaname = "SURIA KLCC" findingcinemaid(cinemaname) end select any possible way I can merge this select case statement with the sql statement ? I try if else but too many code , defeating the original purpose of simplfying it
View Replies !
Select The CheckBox Inside The DataGridView
Hi, Can anybody help me fetch "True" & "False" value from a CheckBox inside a DataGridView row. I tried the following code:- But the cell.Selected doesnot take the correct value. objGrid = (System.Windows.Forms.DataGridView)controlObj[0]; foreach (DataGridViewRow row in objGrid.Rows) { DataGridViewCheckBoxCell cell = row.Cells[0] as DataGridViewCheckBoxCell; if (cell.Value != cell.FalseValue) { if (cell.Selected == true) { ///Some Code; } } } Kindly Help ASAP Thanks
View Replies !
Select Inside Update Query?
Is it possible for me to do something like update table1 SET var1=something,var2=something2 from table1 (SELECT * from table2) as newtable where newtable.field1=acondition
View Replies !
Subquery With Multiple Rows Inside SELECT
Hi there, I need to select rows from a table, but include the top 3 rows of another linked table as a single field in the results. Here is my basic structure: Table: Profiles Fields: Id, ProfileName Table: Groups Fields: Id, GroupName, ProfileId I then need to return something like this: ProfileName,Groups "Joe Soap","Group1, Group2, Group3" Does anyone know how this can be done? Thanks!
View Replies !
Newbe Question: Calling Function Inside Select
Hi!I have a scalar function that returns integer:xview (int)Now, I'm trying to build a procedure that has the following selectinside:select atr1, xview(atr2)from tablenameBut, I get the 'Invalid name' error when I try to execute thatprocedure.If I got it right, I must use user.fn_name() syntax, but I cannot usedbo.xview() inside my procedure since it means xview will always beexecuted as dbo, which is unaccaptable.I'm a bit confused, so any hint is very welcomed.Thanks!Mario.
View Replies !
SELECT Query Stmt Inside Stored Procedure
Friends, What are the possible usuages of a SELECT query stmt inside a stored procedure ?? How can we process the results of the SELECT query other than for documentation/Reporting purposes(Correct me if i'm wrong in this) ?? can any one throw some lite on this .. Thanks, SqlPgmr
View Replies !
Stored Procedure Output Parameter Inside Select...
Does anyone know how can I (or can I) use a stored procedure output parameter(s) inside Select statement. For example Select abc, cde, 'xyz' = Case When 'aaa' then {output parameter of my stored procedure with 'aaa' as input parameter} When 'bbb' then {output parameter of my stored procedure with 'bbb' as input parameter} end from MyTable Thanks Arcady
View Replies !
Problems Executing A SELECT Inside A TRAN Against Other Computer
Hi I have a problem executing a SELECT inside a TRAN against other computer For example: IN THE SQL Query Analizer of the COMPUTER2 1) this runs OK BEGIN TRAN SELECT * FROM COMPUTER2.DATABASE.DBO.TABLE COMMIT TRAN 2) this runs OK SELECT * FROM COMPUTER2.DATABASE.DBO.TABLE 3) this runs OK SELECT * FROM COMPUTER1.DATABASE.DBO.TABLE 4) this runs bad BEGIN TRAN SELECT * FROM COMPUTER1.DATABASE.DBO.TABLE COMMIT TRAN The problem is that TABLE locks and it does not finish. I've been looking for similar ERRORS in Microsoft Support but I found nothing I've uninstall and install de SQL server 2000 SP4 and the problems continues the same Please, someone could help me, thanks
View Replies !
How To Use A Stored Procedure Inside Select Query (sql Server Version 8)
Hi, Please help me in this problem... i am new to sql server.. i am using sql server version 8...(doesnot support function with retun values..) so i have created a procedure... -----------procedure------------------(to find next monday after 6 months)------------------- [code] create proc next_Monday ( @myDate DATETIME ) as BEGIN set @myDate = dateadd(mm, 6, @myDate) while datepart(dw,@myDate) <> 2 begin set @myDate = dateadd(dd, 1, @myDate) end select @myDate end go [/code] -------------------------------------------------------- i can able to execute this procedure separately.... working well... but don't know how to call it inside another query.... the following throws error.... select smaster.sname, smaster.Datex, 'xxx'=(execute next_monday smaster.Datex) from smaster please help me... how to fix this problem...
View Replies !
How Can I Fill Data In Textboxes From Sql Databases But Two Different Tables When I Select A Name That Is Inside A Dropdownlist
HI I need help how can i fill data in textboxes from sql databases but two different tables when i select a name that is inside a dropdownlist my controls are as follows <asp:DropDownList ID="ddl" runat="server" DataSourceID="SqlDataSource13" DataTextField="fullname" DataValueField="fullname"> </asp:DropDownList> <asp:SqlDataSource ID="SqlDataSource13" runat="server" ConnectionString="<%$ ConnectionStrings:NPI Employee MasterConnectionString2 %>" SelectCommand="SELECT [FirstName]+' '+ [Surname] as fullname FROM [Employee] where CurrentEmployee_YN=1 order by FirstName "></asp:SqlDataSource><table bordercolor="#111111" cellpadding="0" cellspacing="0" style="width: 100%; border-collapse: collapse; height: 32px; visibility: hidden;" id="table0"> <tr> <td style="width: 159px; visibility: hidden;"> </td> <td style="width: 170px"> </td> <td bgcolor="#eeeddb" style="width: 20%; height: 25px"> <strong> Order No：</strong></td> <td bgcolor="#eeeddb" style="width: 26%; height: 25px"> <asp:Label ID="OrderNo" runat="server" Width="104px"></asp:Label></td> </tr> <tr> <td bgcolor="#eeeddb" style="width: 159px; height: 25px"> <strong> Account No：</strong></td> <td bgcolor="#eeeddb" style="width: 170px"> <asp:TextBox ID="AccountNo" runat="Server" MaxLength="10" Width="130px"></asp:TextBox> <asp:RequiredFieldValidator ID="RequiredFieldValidator6" runat="server" ControlToValidate="AccountNo" Display="Static" ErrorMessage="Enter Acc No." Text="*"></asp:RequiredFieldValidator></td> <td bgcolor="#eeeddb" style="width: 20%; height: 25px"> <strong> Today's Date:</strong></td> <td> <asp:Label ID="Label1" runat="server" Text="Label" Width="200px"></asp:Label></td> </tr> <tr> <td bgcolor="#eeeddb" style="width: 159px; height: 25px"> <strong> Travel Consultant：</strong></td> <td bgcolor="#eeeddb" style="width: 170px"> <asp:TextBox ID="Consultant" runat="Server" MaxLength="30" Width="128px"></asp:TextBox> <asp:RequiredFieldValidator ID="RequiredFieldValidator7" runat="server" ControlToValidate="Consultant" Display="Static" ErrorMessage="Enter Travel Consultant." Text="*"></asp:RequiredFieldValidator></td> </tr> </table> <center> </center> <center> </center><table bordercolor="#111111" cellpadding="0" cellspacing="0" style="width: 80%; border-collapse: collapse; height: 32px; display: block; visibility: hidden;" id="table2"> <tr> <td align="center" bgcolor="#ffcc33" colspan="3" style="width: 90%; height: 29px"> <font color="#000000" size="5">Enter Passenger(s) Details</font></td> </tr> <tr> <td bgcolor="#eeeddb" style="width: 31%; height: 25px"> <strong> Surname：</strong></td> <td bgcolor="#eeeddb" style="width: 57%; height: 25px"> <asp:TextBox ID="Surname" runat="Server" MaxLength="30" Width="148px"></asp:TextBox> <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="Surname" Display="Static" ErrorMessage="Enter Surname." Text="*"></asp:RequiredFieldValidator></td> </tr> <tr> <td bgcolor="#eeeddb" style="width: 31%; height: 20px"> <strong> Name：</strong></td> <td bgcolor="#eeeddb" style="width: 57%; height: 20px"> <asp:TextBox ID="Name" runat="Server" MaxLength="30" Width="148px"></asp:TextBox> <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="Name" Display="Static" ErrorMessage="Enter Name." Text="*"></asp:RequiredFieldValidator></td> </tr> <tr> <td bgcolor="#eeeddb" style="width: 31%; height: 25px"> <strong> Initials：</strong></td> <td bgcolor="#eeeddb" style="width: 57%; height: 25px"> <asp:TextBox ID="Initials" runat="Server" MaxLength="5" Width="148px"></asp:TextBox> </td> </tr> <tr> <td bgcolor="#eeeddb" style="width: 31%; height: 25px"> <strong> Title：</strong></td> <td bgcolor="#eeeddb" style="width: 57%; height: 25px"> <asp:DropDownList ID="DropDownList1" runat="server" Width="156px"> <asp:ListItem></asp:ListItem> <asp:ListItem Value="Mr"></asp:ListItem> <asp:ListItem Value="Mrs"></asp:ListItem> <asp:ListItem Value="Ms"></asp:ListItem> <asp:ListItem Value="Dr"></asp:ListItem> <asp:ListItem Value="Prof"></asp:ListItem> <asp:ListItem Value="Min"></asp:ListItem> <asp:ListItem Value="Other"></asp:ListItem> </asp:DropDownList> <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="Dropdownlist1" Display="Static" ErrorMessage="Select Title." Text="*" Width="20px"></asp:RequiredFieldValidator> </td> </tr> <tr><td bgcolor="#eeeddb"> <strong> Department</strong> </td> <td bgcolor="#eeeddb" style="width: 57%; height: 25px"> <asp:TextBox ID="Department" runat="server"></asp:TextBox></td> </tr> <tr><td bgcolor="#eeeddb"> <strong> Cost Centre</strong> </td> <td bgcolor="#eeeddb" style="width: 57%; height: 25px"> <asp:TextBox ID="CostCentre" runat="server"></asp:TextBox></td> </tr> <tr> <td bgcolor="#eeeddb" style="width: 31%; height: 25px"> <strong> Tel：</strong></td> <td bgcolor="#eeeddb" style="width: 57%; height: 25px"> <input id="Tel" runat="SERVER" maxlength="15" name="Tel" onkeypress="if((event.keyCode<48)|| (event.keyCode>57))event.returnValue=false" style="width: 143px" type="text" /> </td> </tr> <tr> <td bgcolor="#eeeddb" style="width: 31%; height: 25px"> <strong> Fax：</strong></td> <td bgcolor="#eeeddb" style="width: 57%; height: 25px"> <input id="Fax" runat="SERVER" maxlength="15" name="Fax" onkeypress="if((event.keyCode<48)|| (event.keyCode>57))event.returnValue=false" style="width: 143px" type="text" /> </td> </tr> </table> cost centre and department are from cost table and the rest are from employee table
View Replies !
Checking @@Error
Hi, Just a brief question. I have a script which does a number of insert statements. What I would like to do is determine if the insert statements were all successful. Aside from checking @@ERROR after every insert, is there a way to check if all the insert statements completed successfully? Thanks, Jim
View Replies !
Help With Error Checking
I have built a procedure to send mail using OLE Automation and want to be able to trap error information when it doesn't work. So in an attempt to do that I have the following stored procedure that will return informaiton if the return value is <> 0. Here is how it is being used: IF @return <> 0 BEGIN EXECUTE sp_displayoaerrorinfo @handle, @return END ELSE PRINT 'Success' This works fine, but I would like to write the error message to a table and so I thought I could just alter to to be: IF @return <> 0 BEGIN EXECUTE sp_displayoaerrorinfo @handle, @return = @failure END ELSE PRINT 'Success' Where @failure is a variable I declared earlier. Then I could insert the value of this variable along with some other infomration into a table that would track the errors. However, when I do this I receive the following: Error: Procedure or Function 'sp_DisplayOAErrorInfo' expects parameter '@HResult', which was not supplied. Number:201 Severity:16 State:4 So it isn't seeing that I am passing two variables into the stored procedure. I know I must be missing something simple but I've tried a bunch of different itterations and can't seem to get it right. Any help would be great. Thanks.
View Replies !
Error Checking Issue
Hi All, I have a stored procedure to which I am adding an error checking. Here is my stored procedure. CREATE PROCEDURE usp_DBGrowth AS DECLARE @dbsize DEC(15,2) DECLARE @logsize DEC(15,2) DECLARE @dbname SYSNAME DECLARE @dbsizestr NVARCHAR(500) DECLARE @logsizestr NVARCHAR(500) DECLARE @totaldbsize DEC(15,2) DECLARE @dbid SMALLINT DECLARE dbnames_cursor CURSOR FOR SELECT name, dbid FROM dbo.sysdatabases OPEN dbnames_cursor FETCH NEXT FROM dbnames_cursor INTO @dbname, @dbid WHILE @@FETCH_STATUS = 0 BEGIN SET @dbsizestr = 'SELECT @dbsize = sum(convert(dec(15,2),size)) FROM ' + @dbname + '.dbo.sysfiles WHERE fileid = 1' EXECUTE sp_executesql @dbsizestr, N'@dbsize decimal(15,2) output', @dbsize output PRINT @dbsize SET @logsizestr = 'SELECT @logsize = sum(convert(dec(15,2),size)) FROM ' + @dbname + '.dbo.sysfiles WHERE fileid = 2' EXECUTE sp_executesql @logsizestr, N'@logsize decimal(15,2) output', @logsize output PRINT @logsize SET @totaldbsize = LTRIM(STR((@dbsize + @logsize)*8/1024,15,2)) PRINT @totaldbsize BEGIN TRANSACTION IF @dbid IN (SELECT dbid FROM dbo.sysdatabases) AND @dbid NOT IN (SELECT dbid FROM dbo.databaseoriginalsize) INSERT INTO databaseoriginalsize (dbid, dbname, dbsize, updatedate) VALUES (@dbid, @dbname, @totaldbsize, getdate()) IF @@ERROR <> 0 ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION BEGIN TRANSACTION INSERT INTO databasesize (dbid, updatedate, dbsize) VALUES (5, getdate(), 25) IF @@ERROR <> 0 ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION FETCH NEXT FROM dbnames_cursor INTO @dbname, @dbid END CLOSE dbnames_cursor DEALLOCATE dbnames_cursor dbid column in the databasesize table is a primary key, so I when I try to insert records with the same dbid, I can an error message that duplicate row can't be inserted. That is fine, however, when I query databasesize table, there are 4 records with the dbid 5. With the error checking I have I thought I shouldn't get any records in the table, since there is an error transaction should be rolled back. Two questions: Can you tell me what I am doing wrong? Also, I need to add error checking after execute sp_executesql statement. Any suggestions?
View Replies !
Error Checking Question
Hi all, I have a stored procedure that inserts records into the database. It has error checking that checks for transaction failure, and if it happens it rollback the transaction. IF @@error <> 0 BEGIN ROLLBACK TRANSACTION RETURN END My question is how can I test if the error checking works?
View Replies !
Error Checking Issue
Hi All, I have a stored procedure to which I am adding an error checking. Here is my stored procedure. CREATE PROCEDURE usp_DBGrowth AS DECLARE @dbsize DEC(15,2) DECLARE @logsize DEC(15,2) DECLARE @dbname SYSNAME DECLARE @dbsizestr NVARCHAR(500) DECLARE @logsizestr NVARCHAR(500) DECLARE @totaldbsize DEC(15,2) DECLARE @dbid SMALLINT DECLARE dbnames_cursor CURSOR FOR SELECT name, dbid FROM dbo.sysdatabases OPEN dbnames_cursor FETCH NEXT FROM dbnames_cursor INTO @dbname, @dbid WHILE @@FETCH_STATUS = 0 BEGIN SET @dbsizestr = 'SELECT @dbsize = sum(convert(dec(15,2),size)) FROM ' + @dbname + '.dbo.sysfiles WHERE fileid = 1' EXECUTE sp_executesql @dbsizestr, N'@dbsize decimal(15,2) output', @dbsize output PRINT @dbsize SET @logsizestr = 'SELECT @logsize = sum(convert(dec(15,2),size)) FROM ' + @dbname + '.dbo.sysfiles WHERE fileid = 2' EXECUTE sp_executesql @logsizestr, N'@logsize decimal(15,2) output', @logsize output PRINT @logsize SET @totaldbsize = LTRIM(STR((@dbsize + @logsize)*8/1024,15,2)) PRINT @totaldbsize BEGIN TRANSACTION IF @dbid IN (SELECT dbid FROM dbo.sysdatabases) AND @dbid NOT IN (SELECT dbid FROM dbo.databaseoriginalsize) INSERT INTO databaseoriginalsize (dbid, dbname, dbsize, updatedate) VALUES (@dbid, @dbname, @totaldbsize, getdate()) IF @@ERROR <> 0 ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION BEGIN TRANSACTION INSERT INTO databasesize (dbid, updatedate, dbsize) VALUES (5, getdate(), 25) IF @@ERROR <> 0 ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION FETCH NEXT FROM dbnames_cursor INTO @dbname, @dbid END CLOSE dbnames_cursor DEALLOCATE dbnames_cursor dbid column in the databasesize table is a primary key, so I when I try to insert records with the same dbid, I can an error message that duplicate row can't be inserted. That is fine, however, when I query databasesize table, there are 4 records with the dbid 5. With the error checking I have I thought I shouldn't get any records in the table, since there is an error transaction should be rolled back. Two questions: Can you tell me what I am doing wrong? Also, I need to add error checking after execute sp_executesql statement. Any suggestions?
View Replies !
Error Checking On A Trigger
What is a good method for error checking on a trigger? For instance I have a trigger whose main statement is "EXEC 'a sql statement'" and it is failing on this statement before it gets to the error checking statement "If @@error<>0 Raiserror('my message')". It seems to abort on the EXEC statement and issue its own error statement in this case 207. Is it just that sometimes it makes it to your error statement with an error(the less critical ones) and sometimes it aborts on its own if the error is critical? Leon CREATE TRIGGER [tr_AddrXtra_Nulls] ON dbo.ADDRXTRA FOR INSERT, UPDATE AS Set NoCount On Declare @SQL varChar(5000) Set @SQL = '' create the statement EXEC(@SQL) if @@error <>0 begin raiserror('checkthisout',16,1) end
View Replies !
SQL Error, Checking For A Date
Hello, i have a very simple table called room.... Room_ID Date 1 20/11/2007 00:00:00 and i have a very simple SQL SELECT statement... SELECT * FROM room WHERE date = '20/11/2007 00:00:00' i have tried several variations of the query but i either get an error or a null dataset. can anyone explain where im going wrong ?? i have read about casting but still i cannot return a dataset where i have a date in the statement. Any help is much appreiciated !! Truegilly
View Replies !
DBCC ERROR ON CHECKING THE DATABASE
Hi all, We have been checking the database by dbcc checkdb,through a automated task on our production server (nightly task run everday)d we always getting the message given below. Msg 625, Level 20, state 1 Could not retrieve row from logical page 957298 via RID because the entry in the offset table (=0) for that RID (=17) is less than or equal to 0. If anyone knows about this error message, can you kindly post the message. Thanks a lot. Jay.
View Replies !
TRANSACTIONS In SSIS (error: The ROLLBACK TRANSACTION Request Has No Corresponding BEGIN TRANSACTION.&&"
I'm receiving the below error when trying to implement Execute SQL Task. "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION." This error also happens on COMMIT as well and there is a preceding Execute SQL Task with BEGIN TRANSACTION tranname WITH MARK 'tran' I know I can change the transaction option property from "supported" to "required" however I want to mark the transaction. I was copying the way Import/Export Wizard does it however I'm unable to figure out why it works and why mine doesn't work. Anyone know of the reason?
View Replies !
Front-end Input Error Checking Or Backend...?
This maybe belongs in the Data-Access Forum, but I'm not sure. Is it generally a better idea to enforce things like unique constraints in the Database or the Webform? Say for example I want to make sure no duplicate Social Security Numbers are entered. Is it better to have an "If Exists" clause in my query, with a function to deal with it in the application or is it better to just fire the data to SQL Server and let the unique constraint on the dbase column deal with it? I then still have to have some code in my application to deal with the potential exisatance of that number, so is it a case of tomatoe, tomahtoe? If I understand things correctly, SQL server will return an error code if the piece of data does exist, and I will be able to parse the error code and display a message to the user. Are there performance/coding issues involved? Best practices?
View Replies !
Error With Subtotal And Grand Totals With Iff Checking.
I have a table with amount columns and I want the amount column to either insert the value from the database or a zero based on a condition. For the table rows I use the following to find the amount: =iif( Fields!TYPE.value="Material" or Fields!TYPE.value="Other", FIELDS.Amount.Value,0) which works fine. However, when I try to Sum in the group foot I get #Error when I use =sum(iif( Fields!TYPE.value="Material" or Fields!TYPE.value="Other", FIELDS.Amount.Value,0) ) for the groupings that have a type other than Material and Other. For some reason, it doesn't total the amounts of Material and Other with the Zeroes that were placed in the table rows based on the Condition. For example, the subtotal errors out when trying to total Material with Labor but if it was just Material and Other, it works. Example of what the Columns are: job, year, month, type, amount s57, 2007, 2, labor, 0 s57, 2007, 2, material, 500 month total errors out year total errors out job total errors out Any help would be appreciated.
View Replies !
|
TRACKER
