Passing Error Messages From Stored Procedure To Osql Command
Oct 7, 2004
I have a process that is running on the os. This process is picking up FTP files every 5 min, it renames them so not to confuse them with the new files, copies all renamed files into one file to be used by bulk insert, runs the bulk insert to populate a table, and then runs the stored procedure that scrubbing the data and insert it into another table. For every transaction that I do in my stored procedure, I do the error checking as follows:
IF @@error <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN
If my stored procedure encounters an error, return statement will stop it from running. If this happens, I need to stop the process that is running on the os as well.
Questions:
How can that be accomplished?
How to restart the stored procedure ones the error has been corrected?
I keep getting this error on one of my pages when trying to do an update to the table via a stored procedure: *System.Data.SqlClient.SqlException: Procedure or function 'sp_u_contractor_references' expects parameter '@p_contractorrfid', which was not supplied I have verified that I am adding this parameter and that the spelling is correct. I have also made sure that the value is not NULL. I'm confused as to what else the problem could be. Any ideas?
I have a procedure that will save to table in sql server 200 via stored procedure. When I hit the save button it alwasy give me an error saying "Procedure 'sp_AddBoard' expects parameter '@dtmWarrantyStart', which was not supplied" even though I supplied it in the code
which is
Dim ParamdtmWarrantyStart As SqlParameter = New SqlParameter("@dtmWarrantyStart", SqlDbType.datetime, 8) ParamdtmWarrantyStart.Value = dtmWarrantyStart myCommand.Parameters.Add(ParamdtmWarrantyStart)
below is the stored procedure.
create Proc sp_AddBoard(@BrandID int, @strPcName varchar(50), @bitAccounted bit, @dtmAccounted datetime, @dtmWarrantyStart datetime, @dtmWarrantyEnd datetime, -- @strDescription varchar(500), @intStatus int, @ModelNo varchar(50), @intMemorySlots int, @intMemSlotTaken int, @intAgpSlots int, @intPCI int, @bitWSound bit, @bitWLan bit, @bitWVideo bit, @dtmAcquired datetime, @stat bit output, @intFSB int) as if not exists(select strPcName from tblBoards where strPcName=@strPcName) begin insert into tblBoards (BrandID, strPcName, bitAccounted, dtmAccounted, dtmWarrantyStart, dtmWarrantyEnd, --strDescription, intStatus, ModelNo, intMemorySlots, intMemSlotTaken, intAgpSlots, intPCI, bitWLan, bitWVideo, dtmAcquired,intFSB,bitWSound)
values
(@BrandID,@strPcName,@bitAccounted, @dtmAccounted,@dtmWarrantyStart, @dtmWarrantyEnd,--@strDescription, @intStatus, @ModelNo,@intMemorySlots,@intMemSlotTaken, @intAgpSlots,@intPCI,@bitWLan, @bitWVideo,@dtmAcquired,@intFSB,@bitWSound) end else begin set @stat=1 end
The table is also designed to accept nulls on that field but still same error occured.
ALTER PROCEDURE [dbo].[sp_STATEWLEVEL_DAILY] @STATE varchar(50),@TBLNAME varchar(50)
AS BEGIN TRANSACTION -- Start the transaction TRUNCATE TABLE @TBLNAME; SELECT t1.Date_Taken as 'DATE', t1.Time as 'TIME', t1.Main_ID as 'MAIN_ID', t1.WATER_ULEVEL as 'WATER_ULEVEL' FROM dbo.SEL t1 INNER JOIN dbo.station_info t2 ON t1.Main_ID=t2.Main_ID WHERE t2.STATE=@STATE AND t1.Date_Taken=CONVERT(VARCHAR(10), GETDATE(), 101) ORDER BY t1.Date_Taken, t1.Time
-- See if there is an error IF @@ERROR <> 0 -- There's an error b/c @ERROR is not 0, rollback ROLLBACK ELSE COMMIT -- Success! Commit the transaction
could someone please tell me : am I supposed to use the OLE DB Command in a dataflow to call a stored procedure to return a value? Or is it just supposed to be used to call a straightforward insert statement only?
What I am hoping to do:
I have a table with a few columns and one identity column. In a dataflow I would like to effect an insert of a record to this table and retrieve the identity value of the inserted record... and I'd like to store the returned identity in a user variable.
If I AM supposed to be able to do this... then how on earth do I do it?
I have spent hours fooling around with the OLE DB command trying to call a stored proc and get a return value.
In the Advanced Editor any time I try to add an output column (by clicking on Add Column) I just get an error dialog that says "the component does not allow adding columns to this input or output)
So, am getting pretty concussed .. banging my head of the wall like this...
So put me out of my misery someone please.... is the OLE DB Command intended for this or not?
I am trying to set up a command line so a user can use osql to run a stored procedure on SQL Server 2000. An example of what I am using looks like this: osql -E -Q "EXEC Penguin.Foot.dbo.sp_who". When I run this I get an error that the login failed for the user. However, this user can run the stored procedure from Query analyzer. I've not found a lot of info on osql and any help would be greatly appreciated.
Some T-SQL commands can fail with multiple errors. For example, if you try to disable a primary key constraint using ALTER TABLE t1 NOCHECK CONSTRAINT PK, you will get messages like:
Msg 11415, Level 16, State 1, Line 341 Object 'PK' cannot be disabled or enabled. This action applies only to foreign key and check constraints. Msg 4916, Level 16, State 0, Line 341 Could not enable or disable the constraint. See previous errors.
However, in the code below, only the last message is printed. How can I print all the error messages?
USE tempdb; CREATE TABLE #t1(c1 INT CONSTRAINT PK PRIMARY KEY); BEGIN TRY ALTER TABLE #t1 NOCHECK CONSTRAINT PK; PRINT 'Primary key disabled' END TRY BEGIN CATCH PRINT ERROR_MESSAGE(); END CATCH
Hello, I am getting many of these messages in my server's event log (approximately 13 every 5 seconds or so). I have tried clearing the queue with "END CONVERSATION @ConvHandle WITH CLEANUP;" but the event log keeps getting messages. I have attached an example below.
Type: Information Event Source: MSSQLSERVER Event Category: (2) Event ID: 9724 Date: 7/10/2007 Time: 3:52:37 PM Description: The activated proc [dbo].[SqlQueryNotificationStoredProcedure-32e779eb-edcb-44d1-ba30-93f46ef9d9f8] running on queue HoudiniPlatform.dbo.SqlQueryNotificationService-32e779eb-edcb-44d1-ba30-93f46ef9d9f8 output the following: 'Could not find stored procedure 'dbo.SqlQueryNotificationStoredProcedure-32e779eb-edcb-44d1-ba30-93f46ef9d9f8'.'
How can I get the messages sent by the server while I'm executing a stored procedure via the JDBC driver?
I need to get my own debug messages (done through the print() function) and also standard server messages (such as "x row(s) affected" or results from SET STATISTICS TIME ON). Is this possible?
I got the following error messages: Msg 111, Level 15, State 1, Procedure sp_getRecords, Line 8 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch. Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 10 Incorrect syntax near the keyword 'PROC'. Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 11 Incorrect syntax near the keyword 'PROC'. Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 14 Incorrect syntax near 'EXE'. Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 20 Incorrect syntax near 'updateRecord'. ///////////////////////////////////////////////////////////////////////////////////////////////////// Please help, tell me what is wrong in my execution of the code and advise me how I can correct this problem. Thanks, Scott Chang
I got the following T-SQL error messages: Msg 111, Level 15, State 1, Procedure sp_getRecords, Line 8 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch. Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 10 Incorrect syntax near the keyword 'PROC'. Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 11 Incorrect syntax near the keyword 'PROC'. Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 14 Incorrect syntax near 'EXE'. Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 20 Incorrect syntax near 'updateRecord'. ///////////////////////////////////////////////////////////////////////////////////////////////////// Please help, tell me what is wrong in my execution of the code and advise me how I can correct this problem. Thanks, Scott Chang
I got the following error messages: Msg 111, Level 15, State 1, Procedure sp_getRecords, Line 8 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch. Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 10 Incorrect syntax near the keyword 'PROC'. Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 11 Incorrect syntax near the keyword 'PROC'. Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 14 Incorrect syntax near 'EXE'. Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 20 Incorrect syntax near 'updateRecord'. ///////////////////////////////////////////////////////////////////////////////////////////////////// Please help, tell me what is wrong in my execution of the code and advise me how I can correct this problem. Thanks, Scott Chang
I got the following T-SQL error messages: Msg 111, Level 15, State 1, Procedure sp_getRecords, Line 8 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch. Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 10 Incorrect syntax near the keyword 'PROC'. Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 11 Incorrect syntax near the keyword 'PROC'. Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 14 Incorrect syntax near 'EXE'. Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 20 Incorrect syntax near 'updateRecord'. ///////////////////////////////////////////////////////////////////////////////////////////////////// Please help, tell me what is wrong in my execution of the code and advise me how I can correct this problem. Thanks, Scott Chang
I got the following T-SQL error messages: Msg 111, Level 15, State 1, Procedure sp_getRecords, Line 8 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch. Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 10 Incorrect syntax near the keyword 'PROC'. Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 11 Incorrect syntax near the keyword 'PROC'. Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 14 Incorrect syntax near 'EXE'. Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 20 Incorrect syntax near 'updateRecord'. ///////////////////////////////////////////////////////////////////////////////////////////////////// Please help, tell me what is wrong in my execution of the code and advise me how I can correct this problem. Thanks, Scott Chang
I got the following T-SQL error messages: Msg 111, Level 15, State 1, Procedure sp_getRecords, Line 8 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch. Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 10 Incorrect syntax near the keyword 'PROC'. Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 11 Incorrect syntax near the keyword 'PROC'. Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 14 Incorrect syntax near 'EXE'. Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 20 Incorrect syntax near 'updateRecord'. ///////////////////////////////////////////////////////////////////////////////////////////////////// Please help, tell me what is wrong in my execution of the code and advise me how I can correct this problem. Thanks, Scott Chang
I want to pass a command line values into an osql run stored procedure. The commandline values should be are the values to put into the insert stored procedure that writes them to a table.
I have successfully Create a Site, Inserting Updating and Deleting information in my DB all with Stored Procedures, But I need the ability to pass their username into my Stored Procedures. How and where do I code this in my ASPX file?
My SP would be something like this
Create procedure test
@UserName varchar(50)
As
Select *
From table
Where username = @username
All of the data is tied to the user in one way or another but I do not know what code to put in my page?
I have two variables in my code behind .cs, string Zip;int MenuCode; I am trying to pass them to the stored procedure below and am getting compile errors. How do I properly construct the parameter object? error: Cannot create an object of type 'System.TypeCode' from its string representation '"String";' for the 'Type' property. (points to Parameter Name ="Zip" Type="string" <asp:SqlDataSource ID="LocalPremiumLinks" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>" SelectCommand="CPLink" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:Parameter Name="Zip" Type="string" /> <asp:Parameter Name="MenuCode" Type="double" /> </SelectParameters>
Hi i have been working on these stored procedures for what seems like ages now and i just cant understand why they dont work. I have followed many tutorials and think i have the correct synat but i jus keep getting errors. Basically, i have SPOne and SPTwo - SPOne is writing to a table called Publication that has PublicationID as its PK (auto generated). SPtwo needs to get this PublicationID from SPOne and use it to insert rows into a second table PublicationAuthors - the PublicationID is hence a FK in the table PublicationAuthors. The error i get is: Procedure or function 'SPOne' expects parameter '@publicationID', which was not supplied. Cannot insert the value NULL into column 'publicationID', table .dbo.PublicationAuthors'; column does not allow nulls. INSERT fails. SPOne is as follows: ALTER PROCEDURE dbo.StoredProcedureOne @typeID smallint=null, @title nvarchar(MAX)=null, @publicationID smallint OUTPUTASBEGIN SET NOCOUNT ON INSERT INTO Publication (typeID, title) VALUES (@typeID, @title) SELECT @publicationID = scope_identity() END and SPtwo is as follows: ASDECLARE @NewpublicationID IntEXEC StoredProcedureOne @NewpublicationID = OUTPUTSET @publicationID = @NewpublicationIDINSERT INTO PublicationAuthors (publicationID, authorID) VALUES (@publicationID, @authorID) SELECT @NewpublicationID Thanks Gemma
HII m trying to use the following Store Procedure to search Suppliers... user provides a Column Name (Search By) and an Expression... but i m not getting it work properly... plz review it and tell me wots wrong with this stored procedure is... CREATE PROCEDURE dbo.SearchSupplier ( @Column nvarchar(50), @Expression nvarchar(50) ) AS SELECT SupplierID,Name,Address,Country,City,Phone,Fax FROM Supplier WHERE @Column LIKE '%'+@Expression +'%' OR @Column = @Expression RETURN Thanks
Hi i have some radio buttons, what i want to achieve is that if one of the radio buttons is clicked then the value is sent to the stored procedure, as as now the radio buttonlist is like this; <asp:RadioButtonList ID="SortButtons" runat="Server" RepeatDirection="Horizontal" RepeatLayout="Flow" Width="69px" OnSelectedIndexChanged="SortButtons_SelectedIndexChanged"><asp:ListItem Text="Price" Value="productPrice"></asp:ListItem><asp:ListItem Text="Quantity " Value="Qty"></asp:ListItem></asp:RadioButtonList> My question is how will my stored procedure take in the radio button that has been clicked and order it by that value, e.g if price is clicked how will the stored procedure take taht in, this is how my normal unordered stored procedure currently looks; CREATE PROCEDURE stream_CategoryResults@subCategoryID INT ASSELECT DISTINCT Companies.companyName, Companies.companyLogo,SubCategories.subCategoryName, Products.productPrice, Products.productInfoURL FROM Companies INNER JOIN Products ON Companies.companyID = Products.companyID INNER JOIN SubCategories ON Products.subcategoryID = SubCategories.subCategoryID WHERE SubCategories.subCategoryID=@subCategoryID
Hey huys, I got this stored procedure. First of all: could this work?
--start :) CREATE PROCEDURE USP_MactchUser
@domainUserID NVARCHAR(50) ,
@EmployeeID NVARCHAR(50) ,
@loginType bit = '0' AS
INSERT INTO T_Login
(employeeID, loginType, domainUserID)
Values (@EmployeeID, @loginType, @domainUserID) GO --end :)
then I got this VB.Net code in my ASP.net page....
---begin :) Private Sub matchUser() Dim insertMatchedUser As SqlClient.SqlCommand Dim daMatchedUser As SqlClient.SqlDataAdapter SqlConnection1.Open() 'conn openned daMatchedUser = New SqlClient.SqlDataAdapter("USP_MatchUser", SqlConnection1) daMatchedUser.SelectCommand.CommandType = CommandType.StoredProcedure daMatchedUser.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@EmployeeID", SqlDbType.NVarChar, 50)) daMatchedUser.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@domainUserID", SqlDbType.NVarChar, 50)) daMatchedUser.SelectCommand.Parameters("@EmployeeID").Value = Trim(lblEmployeeID.Text) daMatchedUser.SelectCommand.Parameters("@domainUserID").Value = Trim(lblDomainuserID.Text) daMatchedUser.SelectCommand.Parameters("@EmployeeID").Direction = ParameterDirection.Output daMatchedUser.SelectCommand.Parameters("@domainUserID").Direction = ParameterDirection.Output SqlConnection1.Close() 'conn closed
End Sub ---
If I try this it doesn't work (maybe that's normal :) ) Am I doing it wrong. The thing is, in both label.text properties a values is stored that i want to as a parameter to my stored procedure. What am I doing wrong?
hi,i have a stored procedure that is used to insert the employee data into a EMPLOYEE table.now i am passing the employee data from sqlCommand.i have the XML string like this'<Employee><Name>Gopal</Name><ID>10157839</ID><sal>12000</sal><Address>Khammam</Address></Employee>' when i pass this string as sql parameter it is giving an error. System.Data.SqlClient.SqlException: XML parsing error: A semi colon character was expectedbut when i execute the stored procedure in query analyzer by passing the same parameter. it is working.please reply me on gk_mpl@yahoo.co.in
Hi, that's the problem: I have a GridView, bound to a SQLDataSource, with an stored procedure as a Select query. The Select Parameters are bound to controls in the web form, acting like some filter fields. When I submit the page, everythings works fine, except when I try to set some value in the DateTime fields. .Net is enclosing the date with extra single quotes, as I could see in the Profiler: exec sel_despesa_procura @codigo=NULL,@fornecedor=NULL,@descricao=NULL,@vencto_ini=''2005-10-10 00:00:00:000'',@vencto_fim=''2005-10-20 00:00:00:000'',@pagto_ini=NULL,@pagto_fim=NULL,@valor=NULL,@valor_pago=NULL,@centro_custo=NULL,@pago=N'0,1' The fields are defined as follows: <SelectParameters>...<asp:ControlParameter ControlID="txtFiltroVencIni" Name="vencto_ini" PropertyName="Text" Type="DateTime" /><asp:ControlParameter ControlID="txtFiltroVencFim" Name="vencto_fim" PropertyName="Text" Type="DateTime" />...</SelectParameters> The stored procedure doesn't even execute, due to the bad formatted arguments. It returns the error: Msg 102, Level 15, State 1, Line 1Incorrect syntax near '2005'. I'm going to change the parameter type to varchar, as a workaround, but I'd like to solve this problem.
I have the following stored procedure ALTER PROCEDURE dbo.TextEntered @searchString varchar(30) /* ( @parameter1 datatype = default value, @parameter2 datatype OUTPUT ) */ AS Select GAVPrimaryKey, NAME from towns where NAME like @searchString order by NAME
RETURN doesn't work!!!! I would like to produce the following where s is @searchString: Select GAVPrimaryKey, NAME from towns where NAME like 's%' order by NAME Any ideas how I might acomplish this I have tried almost everything !
Hi All,I have sometimes used the following sort of query to pull data from one table to another:INSERT INTO Table1 SELECT fname, lname FROM Table2Now, let's suppose that I had created a stored procedure to do the insert (and any other logic i was concerned about) and I did something like this:EXECUTE Table1 _Insert SELECT fname, lname FROM Table2It won't work, giving an error that looks something like this:Server: Msg 201, Level 16, State 3, Procedure Table1_Insert, Line 0Procedure 'Table1_Insert' expects parameter '@fname', which was not supplied.I assume I'm not doing things right... how would I pass a result set to a stored procedure, with each row corresponding to an input parameter of the stored procedure?
make it so that I compare that parameter, that is a char(3), to something like "XXX" and then choose the correct SELECT statement to use depending on that parameter.... much like an if else.
Hello, I'd greatly appreciated if someone can help me out on this problem: I'd like to pass a string (which is a multiple value in this case - "Smith", "Lee", "Jones", "Hanson") as an input parameter to a stored procedure. I'd like to use this string as part of the select statement:
exec sp_GetLN "smith, lee, jones, hanson"
In the stored procedure: @strLN varchar <- "smith, lee, jones, hanson"
/*What do I need to do here to SET/REPLACE the original string so that the syntax can be accepted by the select statement??*/
SELECT * from tblCustomers WHERE LASTNAME IN (@strLN)
It looks simple but I've been trying to get the syntax to work. What can I do to change the quote from ' to "?? Can I use char(34) like VB?? Your help is greatly appreciated~
Is it possible to have a stored procedure that passes recordsets which are created and passed from different tables and joins the information in those recordsets to form one group of information?
