Problem with SQL Server

A Stored Procedure has been running fine returning result in 2 secs but
surddenly started taking 2 mins to run. When I run the SQL codes in query
analyser it runs fine. Any idea what the problem may be?
Thanks
Egbon.Have a look at
INF: Troubleshooting Application Performance with SQL Server
http://support.microsoft.com/default.aspx?scid=kb;EN-US;224587
HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
http://support.microsoft.com/default.aspx?scid=kb;EN-US;243589
INF: Understanding and Resolving SQL Server 7.0
or 2000 Blocking Problems
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q224453
As well as these articles themselves, they contain links in them to lots
of other performace troubleshooting type articles. Lots of good stuff !
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Egbon" <Vnjowusi@.gosps.com> wrote in message
news:uTma$F6WDHA.3248@.tk2msftngp13.phx.gbl...
A Stored Procedure has been running fine returning result in 2 secs but
surddenly started taking 2 mins to run. When I run the SQL codes in query
analyser it runs fine. Any idea what the problem may be?
Thanks
Egbon.|||Thanks for the links Jasper. My question was why will it run fine in Query
Analyzer and run slow in Stored Procedure. I can't understand.
Egbon.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OameON6WDHA.2256@.TK2MSFTNGP10.phx.gbl...
> Have a look at
> INF: Troubleshooting Application Performance with SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;224587
> HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;243589
> INF: Understanding and Resolving SQL Server 7.0
> or 2000 Blocking Problems
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q224453
> As well as these articles themselves, they contain links in them to lots
> of other performace troubleshooting type articles. Lots of good stuff !
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Egbon" <Vnjowusi@.gosps.com> wrote in message
> news:uTma$F6WDHA.3248@.tk2msftngp13.phx.gbl...
> A Stored Procedure has been running fine returning result in 2 secs but
> surddenly started taking 2 mins to run. When I run the SQL codes in query
> analyser it runs fine. Any idea what the problem may be?
> Thanks
> Egbon.
>
>|||Do you mean it runs slow as a stored procedure in Query Analyzer ? i.e. if
you run the contents of the procedure in Query Analyzer does it run quicker
than the exec procedurename ? Sorry if I misunderstood you question, I
thought you meant that in general use by your application the performace of
this procedure was slow which may be cause by blocking etc so profiler would
help to show up the problem. You can also capture the execution plan and
look for differences between the slow and fast executions.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Egbon" <Vnjowusi@.gosps.com> wrote in message
news:eAVQIV6WDHA.2424@.TK2MSFTNGP12.phx.gbl...
Thanks for the links Jasper. My question was why will it run fine in Query
Analyzer and run slow in Stored Procedure. I can't understand.
Egbon.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OameON6WDHA.2256@.TK2MSFTNGP10.phx.gbl...
> Have a look at
> INF: Troubleshooting Application Performance with SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;224587
> HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;243589
> INF: Understanding and Resolving SQL Server 7.0
> or 2000 Blocking Problems
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q224453
> As well as these articles themselves, they contain links in them to lots
> of other performace troubleshooting type articles. Lots of good stuff !
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Egbon" <Vnjowusi@.gosps.com> wrote in message
> news:uTma$F6WDHA.3248@.tk2msftngp13.phx.gbl...
> A Stored Procedure has been running fine returning result in 2 secs but
> surddenly started taking 2 mins to run. When I run the SQL codes in query
> analyser it runs fine. Any idea what the problem may be?
> Thanks
> Egbon.
>
>

Problem with Returning a recordset from a stored procedure in ADO/VBScript

I'm having trouble getting a recordset out of stored procedure in ADO. The SP executes without errors, but the recordset object I return into is always closed.

Here is my code:
<%
.....
Set cmm = Server.CreateObject("ADODB.Command")
Set cmm.ActiveConnection = Connect
cmm.CommandType = adCmdStoredProc
cmm.CommandText = "dbo.client_updates_proc"
cmm.Parameters.Refresh
cmm.Parameters(1) = client_id
Set logRS = cmm.Execute()

if not logRS.EOF then
.....
%>

My SP has one parameter, which I set above, and it ends with a select statement. When I run the SP in Query Analyzer, it outputs the table of results as is should, but I always get an error on 'if logRS.EOF then', saying that the object is closed.A good place to start looking is the ADO Connection Error collection. Check to see if Connect.Errors.Count > 0. If so, you will probably find your problem there.

Also, you can try adding SET NOCOUNT ON at the beginning of your SP, and SET NOCOUNT OFF at the end, before you return your recordset. Sometimes the command object stops asking for data when it gets the "X records affected" messages.

Finally, if that doesn't work, try being more explicit with your parameter naming. A good (and more readable) approach would be to use the CreateParameter function.

CreateParameter([Name As String], [Type As DataTypeEnum = adEmpty], [Direction As ParameterDirectionEnum = adParamInput], [Size As ADO_LONGPTR], [Value]) As Parameter

Assume your parameter is an INT named @.my_param

cmm.Parameters.Append cmm.CreateParameter("@.my_param",3,1, 4,client_id)

[Note: the values of DataTypeEnum and ParameterDriectionEnum can be found at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdaenumnz_2.asp ]

Hope this helps...|||Ahh. Thank you so much. It was the NOCOUNT property.

problem with recursive cte query

I am running into 2 problems with this cte query. First off, it's not returning the results in the right order. What I mean is that, as you can see from the graph, the first row (tabid=4) should be followed by rows 3,4 and 5 but it's throwing in a row between. No matter how I try and order the results, it's not working as it should. The tabindex field is a user defined field as to what order the tabs should show up for the user so I have to order by that field at some point.

ParentTabId TabId Title Link TabIndex14Personal Info/Employee/employeeAdmin.aspx115Employment Info/Employee/positionInfo.aspx248Dependents/Employee/dependents.aspx34169Emergency Contacts/Employee/Contacts.aspx24170Demographics/Employee/EmployeeAdmin.aspx11172Employment Files/Employee/empFiles.aspx3172210New Hire Paperwork/Employee/empFiles.aspx1172211Form I-9/Employee/I9.aspx2172212General/Employee/employment.aspx3172213Notes/Employee/notes.aspx4172214Pre-Employment/Employee/preemployment.aspx5172217Protected Health Information/Employee/protectedhealth.aspx61220View All/Employee/viewAll.aspx4

Problem 2 is that I need to format all of this as heirarchal xml but when I output it as xml in the query itself, every element is coming through as MainTabs (the cte name) instead of nesting rows 3,4 & 5 under tab id 4. So, how do I (if it's even possible) fix these two things? If I can't do this in a query, how would you suggest getting an xml formatted result set from a table structure like this? Structure meaning parenttabid = tabid of another row ordered by the tabid and then tabindex? Thanks.

with MainTabs(ParentTabId, TabId, Title, Link, tabindex) as
(
select ParentTabId, TabId, Title, Link, tabindex from Tabs where parenttabid = 1

UNION ALL

select t.ParentTabId, t.TabId, t.Title, t.Link, t.tabindex from Tabs t inner join
MainTabs on MainTabs.TabId = t.ParentTabId
)
select ParentTabId, TabId, Title, Link, tabindex from MainTabs order by tabid, tabindex

You can generate hierarchical XML using this

CREATE FUNCTION dbo.SubTree(@.TabId int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN RETURN
(SELECT ParentTabId as "@.ParentTabId",
TabId as "@.TabId",
Title as "@.Title",
Link as "@.Link",
tabindex as "@.tabindex",
dbo.SubTree(TabId)
FROM Tabs
WHERE ParentTabId=@.TabId
ORDER BY TabId
FOR XML PATH('Tabs'),TYPE)
END

GO

SELECT ParentTabId as "@.ParentTabId",
TabId as "@.TabId",
Title as "@.Title",
Link as "@.Link",
tabindex as "@.tabindex",
dbo.SubTree(TabId)
FROM Tabs
WHERE parenttabid = 1
ORDER BY TabId
FOR XML PATH('Tabs') , ROOT('MyRoot'), TYPE

|||Wonderful, thank you so much!!! Now, if I can just figure out what it's all doing:)|||

SQL Server 2005 has a maximum limit of 32 recursively nested function invocations. If your parts hierarchy exceeds the limit, you will need to use the old approach of getting the XML in flat form and applying an XSLT style sheet to create the hierarchy.

Its mentioned here : http://msdn2.microsoft.com/en-us/library/ms345137.aspx

Can you suggest any alternate option?

problem with recursive cte query

I am running into 2 problems with this cte query. First off, it's not returning the results in the right order. What I mean is that, as you can see from the graph, the first row (tabid=4) should be followed by rows 3,4 and 5 but it's throwing in a row between. No matter how I try and order the results, it's not working as it should. The tabindex field is a user defined field as to what order the tabs should show up for the user so I have to order by that field at some point.

ParentTabId TabId Title Link TabIndex14Personal Info/Employee/employeeAdmin.aspx115Employment Info/Employee/positionInfo.aspx248Dependents/Employee/dependents.aspx34169Emergency Contacts/Employee/Contacts.aspx24170Demographics/Employee/EmployeeAdmin.aspx11172Employment Files/Employee/empFiles.aspx3172210New Hire Paperwork/Employee/empFiles.aspx1172211Form I-9/Employee/I9.aspx2172212General/Employee/employment.aspx3172213Notes/Employee/notes.aspx4172214Pre-Employment/Employee/preemployment.aspx5172217Protected Health Information/Employee/protectedhealth.aspx61220View All/Employee/viewAll.aspx4

Problem 2 is that I need to format all of this as heirarchal xml but when I output it as xml in the query itself, every element is coming through as MainTabs (the cte name) instead of nesting rows 3,4 & 5 under tab id 4. So, how do I (if it's even possible) fix these two things? If I can't do this in a query, how would you suggest getting an xml formatted result set from a table structure like this? Structure meaning parenttabid = tabid of another row ordered by the tabid and then tabindex? Thanks.

with MainTabs(ParentTabId, TabId, Title, Link, tabindex) as
(
select ParentTabId, TabId, Title, Link, tabindex from Tabs where parenttabid = 1

UNION ALL

select t.ParentTabId, t.TabId, t.Title, t.Link, t.tabindex from Tabs t inner join
MainTabs on MainTabs.TabId = t.ParentTabId
)
select ParentTabId, TabId, Title, Link, tabindex from MainTabs order by tabid, tabindex

You can generate hierarchical XML using this

CREATE FUNCTION dbo.SubTree(@.TabId int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT
BEGIN RETURN
(SELECT ParentTabId as "@.ParentTabId",
TabId as "@.TabId",
Title as "@.Title",
Link as "@.Link",
tabindex as "@.tabindex",
dbo.SubTree(TabId)
FROM Tabs
WHERE ParentTabId=@.TabId
ORDER BY TabId
FOR XML PATH('Tabs'),TYPE)
END

GO

SELECT ParentTabId as "@.ParentTabId",
TabId as "@.TabId",
Title as "@.Title",
Link as "@.Link",
tabindex as "@.tabindex",
dbo.SubTree(TabId)
FROM Tabs
WHERE parenttabid = 1
ORDER BY TabId
FOR XML PATH('Tabs') , ROOT('MyRoot'), TYPE

|||Wonderful, thank you so much!!! Now, if I can just figure out what it's all doing:)
|||

SQL Server 2005 has a maximum limit of 32 recursively nested function invocations. If your parts hierarchy exceeds the limit, you will need to use the old approach of getting the XML in flat form and applying an XSLT style sheet to create the hierarchy.

Its mentioned here : http://msdn2.microsoft.com/en-us/library/ms345137.aspx

Can you suggest any alternate option?

Problem with output parameter in SP

I am having problems returning the value of a parameter I have set in my stored procedure. Basically this is an authentication to check username and password for my login page. However, I am receiving the error:

Procedure 'DBAuthenticate' expects parameter '@.@.ID', which was not supplied.

This stored procedure is supposed to return a -1 if the username is not found, -2 if the password does not match, or the @.ID parameter, which is the user ID, if it is successful. How do i go about fixing this SP so that I am returning this output for @.ID?

CREATE PROCEDURE DBAuthenticate

(

@.UserName nVarChar (20),
@.Password nVarChar (20),
@.@.ID varchar(4) OUTPUT

)

AS
Declare @.ActualPassword nVarchar (20)

Select

@.@.ID = RegionID,

@.ActualPassword =regpassword

From dbo.Regions

Where Region = @.Username

If @.@.ID is not null
Begin
if @.Password =@.actualpassword

Select @.@.ID
Else

Select -2
End
Else

Select -1
GOMake sure that you specify OUTPUT in your EXECUTE call. If either the caller or the called routine fail to specify OUTPUT, the value isn't returned.

-PatP|||A couple of questions/things:

1. Why do you want to return something that your code already knows about? Return 1 instead.
2. Naming your parameter with @.@.xxx would result in server knowing it as @.xxx, not xxx as expected. And it doesn't make your parameter a "global" variable either.
3. Based on your logic @.ID variable will ALWAYS have whatever value was retrieved from Region table based on @.UserName or NULL, regardless of whether authentication was successful or not. You probably need to change the path of your authentication algorythm. How about setting it to NULL even if it exists but the password is wrong?