Debugger For Stored Procedure In SQL Server 7.0
Sep 2, 2000Hi
Can anyone let me know if there is any Debugger for stored procedure in SQL Server 7.0, if yes
where can i find one
thanks and regards
Arun
ADVERTISEMENT
Transact SQL Debugger Not Stopping In Stored Procedure
Sep 5, 2007I am trying to use the Transact-SQL debugger to step through a stored procedure on a server to determine where it is failing. I right-click the stored procedure I want to debug in the Query Analyzer's Object Browser and choose Debug... In the Debug Procedure dialog I fill in the values for the parameters, choose the Auto roll back checkbox, and press Execute.
The T-SQL Debugger screen comes up with my stored procedure and a message on the bottom of Waiting for user input. But I can input nothing, neither click on the icons on top, set breakpoints, or doing anything else, as the stored procedure runs to the end and gives me the return code.
If I subsequently set some breakpoints in my stored procedure and try the Go button. Once again the stored procedure runs to the end without stopping at any breakpoint.
In both cases, the stored procedure shows the waiting icon as it proceeds to the end, so that I can not click on anything to stop it.
I have been told this is may be a permissions issue between myself as a client on my machine and the stored procedure on the server. If so, does anybody know what I the things that have to be done on the permissions side to be able to debug the stored procedure using the Transact SQL debugger.
ARGH!!! Can't Pass Smalldatetime To Stored Procedure In DEBUGGER!
Feb 6, 2004I know this is probably user error, but here's the scoop:
I have a stored procedure...
CREATE PROCEDURE [dbo].[sp_Build_CurrentPortfolio_By_Date]
@NewDate smalldatetime
and when I try to run it in debug in the SQL Query Analyzer, I sent in the parameter via the debug interface as '2004/02/02', and as GETDATE(), and a number of other formats, but cannot even get into the procedure once debugger starts up!
I get the following error once I hit "execute" after putting in the above data in the parameter text box...
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification
HELP PLEASE!!! (I'm about to punch my new monitor!!!)
T-SQL Debugger Doesn't Allow Stepping Through Stored Procedures No Other Procedure With The Same Name
May 10, 2006T-SQL Debugger Doesn't Allow Stepping Through Stored ProceduresAnd there is no other procedure with the same name owned by dbo or anyother users.There is no error messages also, it just completes procedure andreturns resultserver:Microsoft SQL Server 2000 - 8.00.818 (Intel X86)Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build2195: Service Pack 4) and I don't know how do determine Client'sversion.What should be done (on client site or server site )to fix thisproblem.Thank you
View 12 Replies View RelatedProcedure Debugger..
Mar 9, 1999Hello All
Can anyone tell me besttool for debugging the Stored Procedure user
friendly. We are using Powerbuilder/Sql Server 6.5 as back end. we got
lots of stored procedures and I am having tough time in debugging.
I actually moved from Oracle and don't know much about Sqlserver just started
learning.
If anyone tell me best tool,method for doing then I will be very greatful
Thanks In Advance
Sundar
How To Use A Debugger On Stored Procedures
Jul 11, 2005Please kindly advise in details how to use a debugger on stored procedures.Thanks.
View 4 Replies View RelatedNot Able To 'Step Into' The Procedure Code In SQL Debugger From Client Machine.
Jul 25, 2003Hi,
I am working on SQL-Server 2000 (developer edition) with SP3 on Win 2000 and Clients are Win98/Win XP.
I am not able to 'Step Into' my SQL Procedure code in Debug mode, executed from Query Analyzer from a client machine. As soon as I select the 'Execute' button after inserting all the parameters value, the whole procedure gets executed. But I'm able to do the same (i.e 'step into') from the Server itself.
Do I need any additional configuration settings in the client machine ??
I shall be greatful if anyone can help me to solve the above problem.
Thanx in advance.
Surajit
SQL Server 2014 :: Embed Parameter In Name Of Stored Procedure Called From Within Another Stored Procedure?
Jan 29, 2015I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
[Code] ....
Connect To Oracle Stored Procedure From SQL Server Stored Procedure...and Vice Versa.
Sep 19, 2006I have a requirement to execute an Oracle procedure from within an SQL Server procedure and vice versa.
How do I do that? Articles, code samples, etc???
SQL Server 2012 :: Executing Dynamic Stored Procedure From A Stored Procedure?
Sep 26, 2014I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
Saw Debugger In Action At SQL Server Live
May 17, 2002At Sql Server Live, I saw Sharon Dooley using the debugger in Query Analyzer, but now that I've tried it I can't step an sp, instead, I get....Quote:
SP debugging may not work properly if you log on as 'Local
System account' while SQL Server is configured to run as a
service.
You can open Event Viwer to see details.
Do you wish to continue?
End Quote:
I'm logged in at a W2Kpro box connectig via client tools
to a W2KSVR box via Integrated NT security. SS2K is NOT
installed locally.
SQL Server 2008 :: How To See Table Variable Data In SSMS Debugger
Jul 28, 2015is there a way to see the data of a table variable in the SSMS debugger? For example, if I set a breakpoint in SSMS and look at a populated table variable named @MyTable in the Locals tab at the bottom of the IDE, a value of "(table)" is displayed. There does not appear to be a way to expand or drill into this variable in the debugger to see the data. Do you know if there's a way to do this through the debugger or do you use an alternate approach when using the SSMS debugger?
View 1 Replies View RelatedSQL Server 2012 :: CLR Procedure Takes Ages To Pass TVP To Stored Procedure?
Jan 21, 2014On SQL 2012 (64bit) I have a CLR stored procedure that calls another, T-SQL stored procedure.
The CLR procedure passes a sizeable amount of data via a user defined table type resp.table values parameter. It passes about 12,000 rows with 3 columns each.
For some reason the call of the procedure is verz very slow. I mean just the call, not the procedure.
I changed the procdure to do nothing (return 1 in first line).
So with all parameters set from
command.ExecuteNonQuery()to
create proc usp_Proc1
@myTable myTable read only
begin
return 1
end
it takes 8 seconds.I measured all other steps (creating the data table in CLR, creating the SQL Param, adding it to the command, executing the stored procedure) and all of them work fine and very fast.
When I trace the procedure call in SQL Profiler I get a line like this for each line of the data table (12,000)
SP:StmtCompleted -- Encrypted Text.
As I said, not the procedure or the creation of the data table takes so long, really only the passing of the data table to the procedure.
Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)
Nov 1, 2007Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
Execute Stored Procedure Y Asynchronously From Stored Proc X Using SQL Server 2000
Oct 14, 2007I 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.
View 3 Replies View RelatedCalling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?
Mar 3, 2008Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class
///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance,
Scott Chang
More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
T-SQL (SS2K8) :: One Stored Procedure Return Data (select Statement) Into Another Stored Procedure
Nov 14, 2014I am new to work on Sql server,
I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.
Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.
Grab IDENTITY From Called Stored Procedure For Use In Second Stored Procedure In ASP.NET Page
Dec 28, 2005I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
View 9 Replies View RelatedHelp: Why Excute A Stored Procedure Need To More 30 Seconds, But Direct Excute The Query Of This Procedure In Microsoft SQL Server Management Studio Under 1 Second
May 23, 2007Hello to all,
I have a stored procedure. If i give this command exce ShortestPath 3418, '4125', 5 in a script and excute it. It takes more 30 seconds time to be excuted.
but i excute it with the same parameters direct in Microsoft SQL Server Management Studio , It takes only under 1 second time
I don't know why?
Maybe can somebody help me?
thanks in million
best Regards
Pinsha
My Procedure Codes are here:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[ShortestPath] (@IDMember int, @IDOther varchar(1000),@Level int, @Path varchar(100) = null output )
AS
BEGIN
if ( @Level = 1)
begin
select @Path = convert(varchar(100),IDMember)
from wtcomValidRelationships
where wtcomValidRelationships.[IDMember]= @IDMember
and PATINDEX('%'+@IDOther+'%',(select RelationshipIDs from wtcomValidRelationships where IDMember = @IDMember) ) > 0
end
if (@Level = 2)
begin
select top 1 @Path = convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)
from wtcomValidRelationships as A, wtcomValidRelationships as B
where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0
and PATINDEX('%'+@IDOther+'%',B.RelationshipIDs) > 0
end
if (@Level = 3)
begin
select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)
from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C
where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0
and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and PATINDEX('%'+@IDOther+'%',C.RelationshipIDs) > 0
end
if ( @Level = 4)
begin
select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)
from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C, wtcomValidRelationships as D
where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0
and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and charindex(convert(varchar(100),D.IDMember), C.RelationshipIDs) > 0
and PATINDEX('%'+@IDOther+'%',D.RelationshipIDs) > 0
end
if (@Level = 5)
begin
select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)+'-'+convert(varchar(100),E.IDMember)
from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C, wtcomValidRelationships as D, wtcomValidRelationships as E
where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0
and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and charindex(convert(varchar(100),D.IDMember), C.RelationshipIDs) > 0
and charindex(convert(varchar(100),E.IDMember),D.RelationshipIDs) > 0 and PATINDEX('%'+@IDOther+'%',E.RelationshipIDs) > 0
end
if (@Level = 6)
begin
select top 1 @Path = '' from wtcomValidRelationships
end
END
System Stored Procedure Call From Within My Database Stored Procedure
Mar 28, 2007I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.
How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?
Thanks in advance
Ad Hoc Query Vs Stored Procedure Performance Vs DTS Execution Of Stored Procedure
Jan 23, 2008
Has anyone encountered cases in which a proc executed by DTS has the following behavior:
1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio
2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio
What could explain this?
Obviously,
All three scenarios are executed against the same database and hit the exact same tables and indices.
Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).
The DTS execution effectively never finishes even after many hours (10+)
The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query)
The Update ad-hoc query will finish in 2 minutes
FoxPro Triggers Call FoxPro Stored Proc Calls SQL Server Stored Procedure
Mar 10, 2005I 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:
FOXPRO employee.dbf InsertTrigger:
employee_insert_trigger(VAL(Employee.ep_pk),Employ ee.fname,Employee.lname,Employee.email,Employee.us er_login,Employee.phone)
FOXPRO corresponding Stored Procedure:
FUNCTION EMPLOYEE_INSERT_TRIGGER
PARAMETERS wepk,wefname,welname,weemail,WEUSERID,WEPHONE
nhandle=SQLCONNECT('SS_PDITHP3','userid','password ')
IF nhandle<0
m.errclose=.f.
IF !USED("errorlog")
USE tisdata!errorlog IN SELECT(1)
m.errclose=.t.
ENDIF
SELECT errorlog
INSERT INTO errorlog (date, time, program,source,user) ;
values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nhandle<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))
IF m.errclose
USE IN errorlog
ENDIF
RETURN
ENDIF
nquery="exec ewo_sp_insertNewEmployee @WEPK ="+STR(wepk)+",@WEFNAME ='"+wefname+"',@WELNAME ='"+welname+"',@WEEMAIL ='"+weemail+"',@WEUSERID ='"+weuserid+"',@WEPHONE='"+wephone+"',@RETCODE =0"
nsucc=SQLEXEC(nhandle,nquery)
SQLDISCONNECT(nhandle)
IF nSucc<0
m.errclose=.f.
IF !USED("errorlog")
USE tisdata!errorlog IN SELECT(1)
m.errclose=.t.
ENDIF
SELECT errorlog
INSERT INTO errorlog (date, time, program,source,user) ;
values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nSucc<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))
IF m.errclose
USE IN errorlog
ENDIF
ENDIF
RETURN
SQL SERVER Stored Procedure called from FOXPRO Stored Procedure
CREATE procedure ewo_sp_insertNewEmployee (
@WEPK int,
@WEFNAME char(20),
@WELNAME char(20),
@WEEMAIL char(50),
@WEUSERID char(15),
@WEPHONE char(25),
@RETCODE int OUTPUT
)
AS
insert into WO_EMP (
WE_PK,
WE_FNAME,
WE_LNAME,
WE_EMAIL,
WE_USERID,
WE_PHONE
)
VALUES (
@WEPK,
@WEFNAME,
@WELNAME,
@WEEMAIL,
@WEUSERID,
@WEPHONE
)
IF @@ERROR <> 0
BEGIN
SET @RETCODE=@@ERROR
END
ELSE
BEGIN
-- SUCCESS!!
SET @RETCODE=0
END
return @RETCODE
GO
User 'Unknown User' Could Not Execute Stored Procedure - Debugging Stored Procedure Using Visual Studio .net
Sep 13, 2007Hi all,
I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.
Whenever I tried to right click stored procedure and select step into store procedure> i get following error
"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"
I am not sure what needs to be done on sql server side
We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.
Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?
Please advise..
Thank You
Please Help Me With My SQL Server Stored Procedure
Oct 9, 2007Can someone help me with my SQL stored procedure? I am trying to do a query. The query will return one record. I then want to set a single valuedepending on the record returned from the query. Here is my sql stored proc. And below it is the error message. Please can someone help me?
USE [QMS07]GO/****** Object: StoredProcedure [dbo].[GetQuarterIdBasedOnDescription] ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO
ALTER PROCEDURE [dbo].[GetQuarterIdBasedOnDescription]( @QuarterString nvarchar(10), @TheQuarterId int output)AS
BEGIN SELECT QuarterId from Quarter WHERE Description=@QuarterString @TheQuarterId = QuarterId END
------------------------------------------------------------Msg 102, Level 15, State 1, Procedure GetQuarterIdBasedOnDescription, Line 10Incorrect syntax near ','.
How To Do This In SQL Server Stored Procedure
Jan 20, 2004Here is my problem:
I am designing Support System. I have a stored procedure for storing new Support Ticket. This stored procedure internally gets next ticket number and inserts Support Ticket
CREATE PROCEDURE [sp_SaveSupportTicket]
(
@pid int,
@uidGen int,
@status VarChar (100),
@probDes text,
@probSol text,
@guestName VarChar (100),
@os VarChar (100),
@roomNum VarChar (100)
)
AS
DECLARE @ticNum int
SELECT @ticNum = MAX(ticNum) + 1 FROM sup_TicDetails
INSERT INTO sup_TicDetails ( ticNum, pid, uidGen, status, probDes, probSol, guestName, os, roomNum,dateofsub)
VALUES (@ticNum, @pid, @uidGen, @status, @probDes, @probSol, @guestName, @os, @roomNum, CONVERT(VARCHAR,GETDATE(),101))
GO
Now... before this happens, on my ASP.NET Page I have a label Ticket# . This label displays next ticket number
CREATE PROCEDURE [sp_GetNextTicketNumber] AS
SELECT max (ticNum) + 1
FROM sup_TicDetails
GO
Now.. how can I have only 1 stored Procedure so that I can obtain next ticket number and display it on ASP.NET page and when I hit "Submit Ticket" sp_SaveSupportTicket gets executed ??
I hope I have made my problem clear !! If not let me know.......
Stored Procedure With Ms SQL Server
Jan 28, 2004please can someone provide some useful links where i can get powerful
documentation for using stored procedures with microsoft SQL Server
rgds.
VB.NET SQL Server Stored Procedure
Jul 20, 2005This one's really got me. I have a VB.NET (version 1.1.4322) projectthat provides an easy way to execute stored procedures on a genericlevel. When I run the code on computer A (running SQL Server 2000version 08.00.0194) the code works great. However, computer B(running SQL Server 2000 version 08.00.0534) bombs when I try toexecute the sproc saying 'Could not find stored procedure'spmw_ReadByPage'. My thought process went as follows...1. Does the procedure really exist. Yes it did. (I tried fullyqualifying it too...'dbo.spmw_ReadByPage')2. Do I have permission to execute the procedure with the way I'mlogging into the database. Yes I did.3. Can I execute a different stored procedure in that db with theexact same code. Yes I could.4. Can I run the same procedure simpliefied to just return a value andno parameters. YES I COULD!!5. So it must be an error in the stored procedure. NO, it executeswith the same parameters in Query Analyzer just fine.6. At this point I guess that what I've come to is....that in version08.00.0534 of SQL SERVER 2000, I could NOT execute any storedprocedure in VB.NET if it accepted parameters (Of course, I mean byusing the OleDBCommand object)Is this true? Is it just me? Any help would be greatly appreciated.Here's what my code looks like. (By the way, the Parameters collectionjust has some home-made objects that have the same properties as aOleDBParameter object, so you don't need it to try the example. Anysproc that takes parameters should reproduce the error.)Public Function ExecuteProc(ByVal ExecutionStyle As ExecutionStyle,Optional ByVal sSQL As String = "") As Boolean'Create a command objectDim oCommand As New OleDbCommand'Create a connection to our default database and open itDim oConn As New OleDbConnection(DBConn.DefaultConnectString)oConn.Open()Try'Go ahead and assing our connection to our Command objectoCommand.Connection = oConn'OK. Did they pass us an SQL statement?If sSQL.Trim <> "" And IsNothing(Parameters) ThenTry to use the sql statementoCommand.CommandType = CommandType.TextoCommand.CommandText = sSQLElse'Don't sweat it, we'll do it for yaoCommand.CommandType = CommandType.StoredProcedure'What's the name of the procedure?oCommand.CommandText = ProcedureName'Use the Parameters the user has specified to createthe'command object parametersFor l = 1 To Parameters.CountWith Parameters(l)Dim oParm As New OleDb.OleDbParameter'Create a new parameteroParm = oCommand.CreateParameter()'Set our parm propertiesoParm.ParameterName = .NameoParm.Direction = .DirectionoParm.OleDbType = .TypeoParm.Value = .Value'Add parameter to our commandoCommand.Parameters.Add(oParm)End WithNextEnd If'Execute our command the way we specifiedSelect Case ExecutionStyleCase ExecutionStyle.ExecuteNonQuerymRowsAffected = oCommand.ExecuteNonQueryCase ExecutionStyle.ExecuteResultSet'Throw that guy in a table so that we canDim oAdapter As New OleDbDataAdapter(oCommand)Dim oSet As New DataSet'Use our data adapter to fill our data setoAdapter.Fill(oSet, "ResultSet")'User our new data table to set our propertiesmResultSet = oSet.Tables("ResultSet")mRowsAffected = 0mResultCount = oSet.Tables("ResultSet").Rows.CountCase ExecutionStyle.ExecuteScalar'Execute this guy returning a single value as anobjectmScalarValue = oCommand.ExecuteScalar()If Not IsNothing(mScalarValue) ThenmResultCount = 1End IfEnd Select'Now that we have executed our commands, we need to'populate the value property for our Output and ReturnvaluesFor l = 0 To oCommand.Parameters.Count - 1With oCommand.Parameters(l)If .Direction = ParameterDirection.InputOutput _Or .Direction = ParameterDirection.Output ThenParameters(l).Value = .ValueEnd IfEnd WithNext'CleanupoConn.Close()ExecuteProc = TrueCatch ex As ExceptionmResultDesc = ex.MessagemResultCode = Err.NumberEnd TryEnd Function
View 3 Replies View RelatedIssue With Sql Server To Sybase Linked Server Using Stored Procedure
Sep 27, 2007I have a linked server from Sql Server 2000 to Sybase Adaptive Server 12.5.1.
When i try to call a stored procedure on Sybase from Sqlserver i get the following message:
"could not execute procedure sp_who on remote server 'linked server name'(42000,7212)
command executed from sql server:
exec <linked_server>.<database>..sp_who
i am able to user open openquery for selects and inserts, successfully
Help appreciated
Thanks.
SQL Server 2005 Stored Procedure Is Very Slow Vs. SQL Server 2000
Apr 18, 2008Hi there,
I was wondering if someone can point out the error or the thing I shouldn't be doing in a stored procedure on SQL Server 2005. I want to switch from SQL Server 2000 to SQL Server 2005 which all seems to work just fine, but one stored procedure is causing me headache.
I could pin the problem down to this query:
DECLARE @Package_ID bigint
DECLARE @Email varchar(80)
DECLARE @Customer_ID bigint
DECLARE @Payment_Type tinyint
DECLARE @Payment_Status tinyint
DECLARE @Booking_Type tinyint
SELECT @Package_ID = NULL
SELECT @Email = NULL
SELECT @Customer_ID = NULL
SELECT @Payment_Type = NULL
SELECT @Payment_Status = NULL
SELECT @Booking_Type = NULL
CREATE TABLE #TempTable(
PACKAGE_ID bigint,
PRIMARY KEY (PACKAGE_ID))
INSERT INTO
#TempTable
SELECT
PACKAGE.PACKAGE_ID
FROM
PACKAGE (nolock) LEFT JOIN BOOKING ON PACKAGE.PACKAGE_ID = BOOKING.PACKAGE_ID
LEFT JOIN CUSTOMER (nolock) ON PACKAGE.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID
LEFT JOIN ADDRESS_LINK (nolock) ON ADDRESS_LINK.SOURCE_TYPE = 1 AND ADDRESS_LINK.SOURCE_ID = CUSTOMER.CUSTOMER_ID
LEFT JOIN ADDRESS (nolock) ON ADDRESS_LINK.ADDRESS_ID = ADDRESS.ADDRESS_ID
WHERE
PACKAGE.PACKAGE_ID = ISNULL(@Package_ID,PACKAGE.PACKAGE_ID)
AND PACKAGE.CUSTOMER_ID = ISNULL(@Customer_ID,PACKAGE.CUSTOMER_ID)
AND PACKAGE.PAYMENT_TYPE = ISNULL(@Payment_Type,PACKAGE.PAYMENT_TYPE)
AND PACKAGE.PAYMENT_STATUS = ISNULL(@Payment_Status,PACKAGE.PAYMENT_STATUS)
AND BOOKING.BOOKING_TYPE = ISNULL(@Booking_Type,BOOKING.BOOKING_TYPE)
-- If this line below is included the request will take about 90 seconds whereas it takes 1 second if it is outcommented
--AND ADDRESS.EMAIl LIKE '%' + ISNULL(@Email, ADDRESS.EMAIL) + '%'
GROUP BY
PACKAGE.PACKAGE_ID
DROP TABLE #TempTable
The request is performing quite well on the SQL Server 2000 but on the SQL Server 2005 it takes much longer. I already installed the SP2 x64, I'm running the SQL Server 2005 on a x64 environment.
As I stated in the comment in the query it takes 90 seconds to finish with the line included, but if I exclude the line it takes 1 second.
I think there must be something wrong with the join's or something else which has maybe changed in SQL Server 2005. All the tables joined have a primary key.
Maybe you folks can spot the error / mistake / wrong type of doing things easily.
I would appreciate any help you can offer me to solve this problem.
On the web I saw that there is a Cumulative Update 4 for the SP2 which fixes the following:
942659 (http://support.microsoft.com/kb/942659/)
FIX: The query performance is slower when you run the query in SQL Server 2005 than when you run the query in SQL Server 2000
Anyhow I think the problem is something else, I haven't tried out the cumulative update yet, as I think it is something different, more general why this query takes ages to process.
Thanks again for any help
Best regards,
Pascal
ASP.NET / SQL Server Stored Procedure Question
Aug 6, 2006Hello,
I wrote a stored procedure that inserts data into one table, then inserts the value of the identity column into another table:
SET NOCOUNT ON;
INSERT INTO ContactUs_TBL
(FullName, Email, Phone, Message)
VALUES
(@FullName, @Email, @Phone, @Message)
SELECT @@IDENTITY
INSERT INTO ContactUsQuestions_TBL
(QuestionText, ContactId)
VALUES
(@QuestionText, @@IDENTITY)
In the CodeFile in asp.net (c#.net), I'm not what to set the value property to below. Right now I just hardcoded a 2 to see how it would work. Could anyone help me out and tell me what I should put here? Each of the other statements I used were set to the value of a form control, but since this id isn't a form control, just an identity column, I'm not sure what to do:
comm.Parameters.Add("@ContactId", SqlDbType.Int);
comm.Parameters["@ContactId"].Value = 2;
-- rkeslar
Stored Procedure That Uses File On Server
Oct 13, 2006I have defined an email as a .html file on my server: /Emails/email.htmlThis file defines what the email will look like and in the text I have placed tags that need to be replaced with values.A tag that requires replacement looks like: <#SENDERNAME> or <#RECEIVERNAME>I want to replace these tags with the names of the sender and the receiver respectively.AFTER this is done the email needs to be sent to the receiver's address, let's say: receiver@yes.comI want to create a stored procedure that takes as input the sender and receivername AND receiveraddress.It then uses the file I have defined on my server, replaces the tags and sends the email.How can I do this?!
View 1 Replies View RelatedSQL Server-Stored Procedure Question
Mar 17, 2004I have a procedure I need to gain output from I would guess.I have no clue how to modify my procedure to allow ASP.net(VB.net)to gain these values so I may use them....Please help
I have a user input the Login name and password ..use this procedure to verify and I need the USERNAME,USERPASSWORD,USERClinic,and UserTester info from this procedure.I am told to create an outstatement but bot sure where it goes????
create procedure dbo.Appt_Login_NET
(
@LoginName nvarchar(15),
@Password NvarChar(15)
)
as
select
UserName,
UserPassword,
UserClinic,
UserTester
from
Clinic_users
where
UserName = @LoginName
and
UserPassword = @Password
GO
SQL Server DB And Stored Procedure - 1st Attempt
Mar 24, 2005New project and this one going with stored procedures.
Should have done it on my first project, but learn as you go...
I have the following code to insert info into the database....
SqlCommand myCommand = new SqlCommand("sp_AddOnlineUser", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.Add("@IP_Address", Request.ServerVariables["REMOTE_ADDR"]);
myCommand.Parameters.Add("@Session_ID", Session.SessionID.ToString());
myCommand.Parameters.Add("@user_Agent", Request.ServerVariables["HTTP_USER_AGENT"]);
myCommand.Parameters.Add("@Referer", Request.ServerVariables["HTTP_REFERER"]);
Now, I just want to be sure this stored procedure is correct and if not, suggestions....
create proc dbo.sp_AddOnlineUser
@IP_Address char(4),
@Session_ID nvarchar(100),
@user_Agent nvarchar(255),
@Referer nvarchar(255)
Thanks all,
Zath