Delete directory

Can any one let me know like how can I remove one directory, which contains
couple of file, through xp_cmdshell extended stored procedure.
Thanks in advance.
xp_cmdshell 'rd C:\Somedirectory /s' ?
Remember that fixed Drive letters are on the SQl Server only.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Joh" <joh@.mailcity.com> schrieb im Newsbeitrag
news:uthGMXKVFHA.2700@.TK2MSFTNGP12.phx.gbl...
> Can any one let me know like how can I remove one directory, which
> contains
> couple of file, through xp_cmdshell extended stored procedure.
> Thanks in advance.
>
|||Thanks Jens Suessmeyer.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OXVSHdKVFHA.3432@.TK2MSFTNGP10.phx.gbl...
> xp_cmdshell 'rd C:\Somedirectory /s' ?
> Remember that fixed Drive letters are on the SQl Server only.
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Joh" <joh@.mailcity.com> schrieb im Newsbeitrag
> news:uthGMXKVFHA.2700@.TK2MSFTNGP12.phx.gbl...
>
|||Can I see all the dos commands like if i write Help in dos command so it
will show all the commands, is there any commands available ?
Thanks
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OXVSHdKVFHA.3432@.TK2MSFTNGP10.phx.gbl...
> xp_cmdshell 'rd C:\Somedirectory /s' ?
> Remember that fixed Drive letters are on the SQl Server only.
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Joh" <joh@.mailcity.com> schrieb im Newsbeitrag
> news:uthGMXKVFHA.2700@.TK2MSFTNGP12.phx.gbl...
>
|||Yes through HELP it's possible.....
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OXVSHdKVFHA.3432@.TK2MSFTNGP10.phx.gbl...
> xp_cmdshell 'rd C:\Somedirectory /s' ?
> Remember that fixed Drive letters are on the SQl Server only.
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Joh" <joh@.mailcity.com> schrieb im Newsbeitrag
> news:uthGMXKVFHA.2700@.TK2MSFTNGP12.phx.gbl...
>

Delete and return data

Is there anyway for a stored procedure to delete a record but also return
the data from the record? My first guess would be to copy all the returned
fields to temp variables but this seems awkward. The call will be in a
transation so if I fail to receive the data the transaction will be rolled
back thus saving the data.
I'm thinking something like this but am looking for a better suggestion
(note very simplified example).
CREATE PROCEDURE [dbo].[procName]
(
@.val1 int,
@.val2 int
)
AS
DECLARE @.@.tV1 int
DECLARE @.@.tV2 int
DECLARE @.@.tV3 int
SELECT @.@.tV1=V1, @.@.tV2=V2, @.@.tV3=V3 FROM tb1 WHERE @.val1=V1 AND @.val2=V2
DELETE tb1 WHERE @.val1=V1 AND @.val2=V2
SELECT @.@.tV1 as 'V1', @.@.tV2 as 'V2', @.@.tV3 as 'V3'
Regards,
JohnWhy not this:
SELECT ... FROM tb1 WHERE @.val1=V1 AND @.val2=V2
DELETE tb1 WHERE @.val1=V1 AND @.val2=V2
"John J. Hughes II" <no@.invalid.com> wrote in message
news:O85n3SrkFHA.3336@.tk2msftngp13.phx.gbl...
> Is there anyway for a stored procedure to delete a record but also return
> the data from the record? My first guess would be to copy all the
> returned fields to temp variables but this seems awkward. The call will
> be in a transation so if I fail to receive the data the transaction will
> be rolled back thus saving the data.
> I'm thinking something like this but am looking for a better suggestion
> (note very simplified example).
> CREATE PROCEDURE [dbo].[procName]
> (
> @.val1 int,
> @.val2 int
> )
> AS
> DECLARE @.@.tV1 int
> DECLARE @.@.tV2 int
> DECLARE @.@.tV3 int
> SELECT @.@.tV1=V1, @.@.tV2=V2, @.@.tV3=V3 FROM tb1 WHERE @.val1=V1 AND @.val2=V2
> DELETE tb1 WHERE @.val1=V1 AND @.val2=V2
> SELECT @.@.tV1 as 'V1', @.@.tV2 as 'V2', @.@.tV3 as 'V3'
> Regards,
> John
>|||You've almost got it right, but use update locks when reading out the row.
I prefer to return data for a single row in output parameters instead of
returning a result set.
"John J. Hughes II" <no@.invalid.com> wrote in message
news:O85n3SrkFHA.3336@.tk2msftngp13.phx.gbl...
> Is there anyway for a stored procedure to delete a record but also return
> the data from the record? My first guess would be to copy all the
returned
> fields to temp variables but this seems awkward. The call will be in a
> transation so if I fail to receive the data the transaction will be rolled
> back thus saving the data.
> I'm thinking something like this but am looking for a better suggestion
> (note very simplified example).
> CREATE PROCEDURE [dbo].[procName]
> (
> @.val1 int,
> @.val2 int
> )
> AS
> DECLARE @.@.tV1 int
> DECLARE @.@.tV2 int
> DECLARE @.@.tV3 int
> SELECT @.@.tV1=V1, @.@.tV2=V2, @.@.tV3=V3 FROM tb1 WHERE @.val1=V1 AND
@.val2=V2
> DELETE tb1 WHERE @.val1=V1 AND @.val2=V2
> SELECT @.@.tV1 as 'V1', @.@.tV2 as 'V2', @.@.tV3 as 'V3'
> Regards,
> John
>|||Your method would only return that one row was deleted and not tell me what
was deleted. In my example I would lose the data for V3.
Regards,
John
"Raymond D'Anjou" <rdanjou@.savantsoftNOSPAM.net> wrote in message
news:OxDxCvrkFHA.2916@.TK2MSFTNGP14.phx.gbl...
> Why not this:
> SELECT ... FROM tb1 WHERE @.val1=V1 AND @.val2=V2
> DELETE tb1 WHERE @.val1=V1 AND @.val2=V2
> "John J. Hughes II" <no@.invalid.com> wrote in message
> news:O85n3SrkFHA.3336@.tk2msftngp13.phx.gbl...
>|||Thanks for the feedback... I can see the advantage of both. I need to do
this with several table, some of which have a lot of columns and was trying
to avoid the maintenance headache but I guess I have.
Regards,
John
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:O$wSSpskFHA.2156@.TK2MSFTNGP14.phx.gbl...
> You've almost got it right, but use update locks when reading out the row.
> I prefer to return data for a single row in output parameters instead of
> returning a result set.
> "John J. Hughes II" <no@.invalid.com> wrote in message
> news:O85n3SrkFHA.3336@.tk2msftngp13.phx.gbl...
> returned
> @.val2=V2
>|||That's why I left the example "open" - SELECT ... FROM tb1
You can replace the ... with all the columns that you need.
SELECT V1, V2, V3 FROM tb1 WHERE @.val1=V1 AND @.val2=V2
"John J. Hughes II" <no@.invalid.com> wrote in message
news:OWX7wXukFHA.3256@.TK2MSFTNGP12.phx.gbl...
> Your method would only return that one row was deleted and not tell me
> what was deleted. In my example I would lose the data for V3.
> Regards,
> John
> "Raymond D'Anjou" <rdanjou@.savantsoftNOSPAM.net> wrote in message
> news:OxDxCvrkFHA.2916@.TK2MSFTNGP14.phx.gbl...
>|||Yes I understood that. The problem is the SQL is only going to return the
last result which is the delete command. I won't know what the query result
was from the select.
Regards,
John
"Raymond D'Anjou" <rdanjou@.savantsoftNOSPAM.net> wrote in message
news:u9t3YcukFHA.320@.TK2MSFTNGP09.phx.gbl...
> That's why I left the example "open" - SELECT ... FROM tb1
> You can replace the ... with all the columns that you need.
> SELECT V1, V2, V3 FROM tb1 WHERE @.val1=V1 AND @.val2=V2
> "John J. Hughes II" <no@.invalid.com> wrote in message
> news:OWX7wXukFHA.3256@.TK2MSFTNGP12.phx.gbl...
>|||I really didn't understand what you meant by this so I re-read the post
carefully and finally figured it out.
I had not seen that this procedure would be called from another procedure.
Brian give you the best solution.
If the delete only affects 1 row, return the values in Output parameters.
If not, I think that you're stuck with using a temp table.
See this site for more information:
http://www.sommarskog.se/share_data.html
"John J. Hughes II" <no@.invalid.com> wrote in message
news:%23hgaXT6kFHA.1968@.TK2MSFTNGP14.phx.gbl...
> Yes I understood that. The problem is the SQL is only going to return the
> last result which is the delete command. I won't know what the query
> result was from the select.
> Regards,
> John
> "Raymond D'Anjou" <rdanjou@.savantsoftNOSPAM.net> wrote in message
> news:u9t3YcukFHA.320@.TK2MSFTNGP09.phx.gbl...
>sql

Delete all records in tblA not matching in tblB

I'm hoping someone can tell me how to construct a stored procedure that
deletes all records in tblA not matching the PK in tblB

This gives me the recordset of all records in tblA with no matching
records in tblB (ID is the PK in tblB)

SELECT a.ID
FROM dbo.tblB b
RIGHT OUTER JOIN dbo.tblA a ON b.ID = a.ID
WHERE
b.ID IS NULL

thanks,
lqDELETE FROM tblA
WHERE NOT EXISTS
(SELECT *
FROM tblB
WHERE tblB.id = tblA.id)

(Untested. Make sure you have a current backup and test it out for
yourself first.)

--
David Portas
SQL Server MVP
--|||Thanks for that. I have never used NOT EXISTS before. lq

Delete a file from the Server

I have a table in my database on SQL Server which holds a file name
that refers to a file that is stored on the server. I would like to
create a trigger to delete this file from the server if the row in the
table is deleted. I have been trying to use this command in a trigger
(<filename> is the name and path of the file):

xp_cmdshell "delete <filename>"

If some one could please help I would appreciate it very much. I
would love a code sample if you have one. Thank you so very much.

From,

RyanOn 28 Jul 2003 11:12:32 -0700 in comp.databases.ms-sqlserver,
rvanarnam@.aol.com (Ryan) wrote:

>I have a table in my database on SQL Server which holds a file name
>that refers to a file that is stored on the server. I would like to
>create a trigger to delete this file from the server if the row in the
>table is deleted. I have been trying to use this command in a trigger
>(<filename> is the name and path of the file):
>xp_cmdshell "delete <filename>"
>If some one could please help I would appreciate it very much. I
>would love a code sample if you have one. Thank you so very much.

(untried)
xp_cmdshell "cmd /c del <filename>"

--
Ride Free (but you still have to pay for the petrol)

(replace sithlord with trevor for email)|||"Ryan" <rvanarnam@.aol.com> wrote in message
news:3d47fdc9.0307281012.7f4575cc@.posting.google.c om...
> I have a table in my database on SQL Server which holds a file name
> that refers to a file that is stored on the server. I would like to
> create a trigger to delete this file from the server if the row in the
> table is deleted. I have been trying to use this command in a trigger
> (<filename> is the name and path of the file):
> xp_cmdshell "delete <filename>"
> If some one could please help I would appreciate it very much. I
> would love a code sample if you have one. Thank you so very much.
> From,
> Ryan

You can use xp_cmdshell in a trigger, but this may not be the best approach,
especially if there are very frequent deletes. Calling xp_cmdshell is a
relatively slow operation, and everything will be blocked while the trigger
runs. Also, if there are any problems executing xp_cmdshell, such as a
timeout on a network drive, or a slow file delete, then there may be a
significant impact on your main table. Finally, if someone deletes 20 rows
in a single delete statement, you will have to iterate through them with a
loop or cursor in your trigger, calling xp_cmdshell for each file name, and
that could really cause performance problems.

A more flexible approach might be to use the trigger to put the file name(s)
into a 'pending deletion' table, then have a SQL Server job which checks the
table every few minutes and deletes the file. Using xp_cmdshell for that
would be fine, because you don't care (as much) if it times out. Or even
have an external script read the table and do the deletion from the
operating system side. By doing it that way, any unexpected behaviour won't
impact your main processing.

Simonsql

Delete

hi
i have a stored procedure for Delete with following code
Even though i delete thru UI the delete procedure gets called but when i
again open my UI i find the record that is being deleted.
could u please eloborate especially what that ROWSTATUS | 0x890
mean and also probable reasons for not deleting
UPDATE C_SetupDet
set RowStatus = RowStatus | 0x890
WHERE GUID = @.GUID
thanks and regards
MadhaviHi Madhu
The question is not very clear. Are you really trying to delete or are you
updating?
The query that you gave here is updating a value
the Hexa decimal value 0x890 is actually 888 or 100010001000
as per the code here you are using the OR operation on the column value.
You might be better aware of the business logic involved here
Please let me know if you have any concerns
thanks and regards
Chandra
"madhavi" wrote:

> hi
> i have a stored procedure for Delete with following code
> Even though i delete thru UI the delete procedure gets called but when i
> again open my UI i find the record that is being deleted.
> could u please eloborate especially what that ROWSTATUS | 0x890
> mean and also probable reasons for not deleting
> UPDATE C_SetupDet
> set RowStatus = RowStatus | 0x890
> WHERE GUID = @.GUID
> thanks and regards
> Madhavi
>
>|||hi
Actually the Record is not deleted but instead a flag variable is set 1
which indicates that that record is deleted and it should not be visible in
the User Interface(UI)
thanks and regards
Madhavi
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:708E0CD0-6FDF-469C-8F1A-E67036B8D083@.microsoft.com...
> Hi Madhu
> The question is not very clear. Are you really trying to delete or are you
> updating?
> The query that you gave here is updating a value
> the Hexa decimal value 0x890 is actually 888 or 100010001000
> as per the code here you are using the OR operation on the column value.
> You might be better aware of the business logic involved here
> Please let me know if you have any concerns
> thanks and regards
> Chandra
>
> "madhavi" wrote:
>
i

Delayed stored procedure

We have a SQL Server 2000 database on my PC running WinXP Pro with a GUI
interface/application provided by a third party. We're recruiters and this i
s
the “Applicant Tracking System”/database we use to track our
candidates/resumes and clients.
This software was installed on my machine by others and I do not have the
technical expertise to diagnose problems when they occur.
There is a button within the application we click to search the (indexed)
resumes based on keywords.
Apparently this button runs a stored command thru SQL Server:
sp_help_fultext_columns 'consultants"
It used to be that when we clicked on the button a window (GUI) would open
instantly with fields where we enter our key words and then execute the
search.
However… for unknown reasons it now takes approximately 40 seconds after w
e
click for the search window to open. Our software provider does not know why
.
They logged on to my machine today and used Enterprise Manager and I think
Query Analyzer to run the query above. It also took 40 seconds to open and
they said this indicated we have a problem with SQL Server... not their
application... and sent us off to find our own solution.
We live in the country and there are no SQL Server experts within miles.
Does this sound like a problem that could be diagnosed/fixed by someone who
accesses my machine remotely?
Or better yet... does anyone have a clue how to fix the problem simply based
on my question above?Jeff,
If somebody can remote desktop in , they should be able to identify
the issue.
I don't want to sound presumptous but I somehow find it hard to believe that
just sp_help_fultext_columns 'consultants' is taking 40 seconds. This system
stored proc only brings back a list of columns that have full text index on
them in a particular table(or table-column),
If you can run trace and make sure that is the only thing running in the
backend when you click that button, that might help you narrow it down a
little more.
To run a trace just click sql profiler from the Sql server program menu and
click File-New Trace & Hit run. go back and click the Button on the
Application. and see the list of sql commands being executed as a result.
HTH,
RA
"Jeff Ingman" wrote:

> We have a SQL Server 2000 database on my PC running WinXP Pro with a GUI
> interface/application provided by a third party. We're recruiters and this
is
> the “Applicant Tracking System”/database we use to track our
> candidates/resumes and clients.
> This software was installed on my machine by others and I do not have the
> technical expertise to diagnose problems when they occur.
> There is a button within the application we click to search the (indexed)
> resumes based on keywords.
> Apparently this button runs a stored command thru SQL Server:
> sp_help_fultext_columns 'consultants"
> It used to be that when we clicked on the button a window (GUI) would open
> instantly with fields where we enter our key words and then execute the
> search.
> However… for unknown reasons it now takes approximately 40 seconds after
we
> click for the search window to open. Our software provider does not know w
hy.
> They logged on to my machine today and used Enterprise Manager and I think
> Query Analyzer to run the query above. It also took 40 seconds to open and
> they said this indicated we have a problem with SQL Server... not their
> application... and sent us off to find our own solution.
> We live in the country and there are no SQL Server experts within miles.
> Does this sound like a problem that could be diagnosed/fixed by someone wh
o
> accesses my machine remotely?
> Or better yet... does anyone have a clue how to fix the problem simply bas
ed
> on my question above?|||Rocky...
Thanks for your comments. But I'm not confident enough in my SQL expertise
to understand/follow thru on your recommendations.
(One small point... I mispelled the query in my original post... it should
be "fulltext" not "fultext").
I think I'll need to find a DBA who can logon to my machine remotely to
diagnose this.
I'd have done this by now except we only access this command a couple times
daily and I haven't wanted to hire an expensive consultant.
Would Microsoft paid incident support be a good route to go?
........................
"Rocky A" wrote:
[vbcol=seagreen]
> Jeff,
> If somebody can remote desktop in , they should be able to identify
> the issue.
> I don't want to sound presumptous but I somehow find it hard to believe th
at
> just sp_help_fultext_columns 'consultants' is taking 40 seconds. This syst
em
> stored proc only brings back a list of columns that have full text index o
n
> them in a particular table(or table-column),
> If you can run trace and make sure that is the only thing running in the
> backend when you click that button, that might help you narrow it down a
> little more.
> To run a trace just click sql profiler from the Sql server program menu an
d
> click File-New Trace & Hit run. go back and click the Button on the
> Application. and see the list of sql commands being executed as a result.
> HTH,
> RA
> "Jeff Ingman" wrote:
>|||Jeff,
I would suggest going through a consultant rather than a MS paid
incident, I am guessing this is not an issue with Sql server as a product, i
t
is more of a day-to-day technical issue, i would go the DBA route. I can
take a look at it if you are interested.
thanks,
RA
"Jeff Ingman" wrote:
[vbcol=seagreen]
> Rocky...
> Thanks for your comments. But I'm not confident enough in my SQL expertise
> to understand/follow thru on your recommendations.
> (One small point... I mispelled the query in my original post... it should
> be "fulltext" not "fultext").
> I think I'll need to find a DBA who can logon to my machine remotely to
> diagnose this.
> I'd have done this by now except we only access this command a couple time
s
> daily and I haven't wanted to hire an expensive consultant.
> Would Microsoft paid incident support be a good route to go?
> ........................
> "Rocky A" wrote:
>|||I would like help Rocky... how do we arrange? You can find my email address
at www.ingmancompany.com.
jeff
"Rocky A" wrote:
[vbcol=seagreen]
> Jeff,
> I would suggest going through a consultant rather than a MS paid
> incident, I am guessing this is not an issue with Sql server as a product,
it
> is more of a day-to-day technical issue, i would go the DBA route. I can
> take a look at it if you are interested.
> thanks,
> RA
> "Jeff Ingman" wrote:
>

Delayed stored procedure

We have a SQL Server 2000 database on my PC running WinXP Pro with a GUI
interface/application provided by a third party. We're recruiters and this is
the â'Applicant Tracking Systemâ'/database we use to track our
candidates/resumes and clients.
This software was installed on my machine by others and I do not have the
technical expertise to diagnose problems when they occur.
There is a button within the application we click to search the (indexed)
resumes based on keywords.
Apparently this button runs a stored command thru SQL Server:
sp_help_fultext_columns 'consultants"
It used to be that when we clicked on the button a window (GUI) would open
instantly with fields where we enter our key words and then execute the
search.
Howeverâ?¦ for unknown reasons it now takes approximately 40 seconds after we
click for the search window to open. Our software provider does not know why.
They logged on to my machine today and used Enterprise Manager and I think
Query Analyzer to run the query above. It also took 40 seconds to open and
they said this indicated we have a problem with SQL Server... not their
application... and sent us off to find our own solution.
We live in the country and there are no SQL Server experts within miles.
Does this sound like a problem that could be diagnosed/fixed by someone who
accesses my machine remotely?
Or better yet... does anyone have a clue how to fix the problem simply based
on my question above?Jeff,
If somebody can remote desktop in , they should be able to identify
the issue.
I don't want to sound presumptous but I somehow find it hard to believe that
just sp_help_fultext_columns 'consultants' is taking 40 seconds. This system
stored proc only brings back a list of columns that have full text index on
them in a particular table(or table-column),
If you can run trace and make sure that is the only thing running in the
backend when you click that button, that might help you narrow it down a
little more.
To run a trace just click sql profiler from the Sql server program menu and
click File-New Trace & Hit run. go back and click the Button on the
Application. and see the list of sql commands being executed as a result.
HTH,
RA
"Jeff Ingman" wrote:
> We have a SQL Server 2000 database on my PC running WinXP Pro with a GUI
> interface/application provided by a third party. We're recruiters and this is
> the â'Applicant Tracking Systemâ'/database we use to track our
> candidates/resumes and clients.
> This software was installed on my machine by others and I do not have the
> technical expertise to diagnose problems when they occur.
> There is a button within the application we click to search the (indexed)
> resumes based on keywords.
> Apparently this button runs a stored command thru SQL Server:
> sp_help_fultext_columns 'consultants"
> It used to be that when we clicked on the button a window (GUI) would open
> instantly with fields where we enter our key words and then execute the
> search.
> Howeverâ?¦ for unknown reasons it now takes approximately 40 seconds after we
> click for the search window to open. Our software provider does not know why.
> They logged on to my machine today and used Enterprise Manager and I think
> Query Analyzer to run the query above. It also took 40 seconds to open and
> they said this indicated we have a problem with SQL Server... not their
> application... and sent us off to find our own solution.
> We live in the country and there are no SQL Server experts within miles.
> Does this sound like a problem that could be diagnosed/fixed by someone who
> accesses my machine remotely?
> Or better yet... does anyone have a clue how to fix the problem simply based
> on my question above?|||Rocky...
Thanks for your comments. But I'm not confident enough in my SQL expertise
to understand/follow thru on your recommendations.
(One small point... I mispelled the query in my original post... it should
be "fulltext" not "fultext").
I think I'll need to find a DBA who can logon to my machine remotely to
diagnose this.
I'd have done this by now except we only access this command a couple times
daily and I haven't wanted to hire an expensive consultant.
Would Microsoft paid incident support be a good route to go?
........................
"Rocky A" wrote:
> Jeff,
> If somebody can remote desktop in , they should be able to identify
> the issue.
> I don't want to sound presumptous but I somehow find it hard to believe that
> just sp_help_fultext_columns 'consultants' is taking 40 seconds. This system
> stored proc only brings back a list of columns that have full text index on
> them in a particular table(or table-column),
> If you can run trace and make sure that is the only thing running in the
> backend when you click that button, that might help you narrow it down a
> little more.
> To run a trace just click sql profiler from the Sql server program menu and
> click File-New Trace & Hit run. go back and click the Button on the
> Application. and see the list of sql commands being executed as a result.
> HTH,
> RA
> "Jeff Ingman" wrote:
> > We have a SQL Server 2000 database on my PC running WinXP Pro with a GUI
> > interface/application provided by a third party. We're recruiters and this is
> > the â'Applicant Tracking Systemâ'/database we use to track our
> > candidates/resumes and clients.
> >
> > This software was installed on my machine by others and I do not have the
> > technical expertise to diagnose problems when they occur.
> >
> > There is a button within the application we click to search the (indexed)
> > resumes based on keywords.
> >
> > Apparently this button runs a stored command thru SQL Server:
> >
> > sp_help_fultext_columns 'consultants"
> >
> > It used to be that when we clicked on the button a window (GUI) would open
> > instantly with fields where we enter our key words and then execute the
> > search.
> >
> > Howeverâ?¦ for unknown reasons it now takes approximately 40 seconds after we
> > click for the search window to open. Our software provider does not know why.
> > They logged on to my machine today and used Enterprise Manager and I think
> > Query Analyzer to run the query above. It also took 40 seconds to open and
> > they said this indicated we have a problem with SQL Server... not their
> > application... and sent us off to find our own solution.
> >
> > We live in the country and there are no SQL Server experts within miles.
> > Does this sound like a problem that could be diagnosed/fixed by someone who
> > accesses my machine remotely?
> >
> > Or better yet... does anyone have a clue how to fix the problem simply based
> > on my question above?|||Jeff,
I would suggest going through a consultant rather than a MS paid
incident, I am guessing this is not an issue with Sql server as a product, it
is more of a day-to-day technical issue, i would go the DBA route. I can
take a look at it if you are interested.
thanks,
RA
"Jeff Ingman" wrote:
> Rocky...
> Thanks for your comments. But I'm not confident enough in my SQL expertise
> to understand/follow thru on your recommendations.
> (One small point... I mispelled the query in my original post... it should
> be "fulltext" not "fultext").
> I think I'll need to find a DBA who can logon to my machine remotely to
> diagnose this.
> I'd have done this by now except we only access this command a couple times
> daily and I haven't wanted to hire an expensive consultant.
> Would Microsoft paid incident support be a good route to go?
> ........................
> "Rocky A" wrote:
> > Jeff,
> > If somebody can remote desktop in , they should be able to identify
> > the issue.
> >
> > I don't want to sound presumptous but I somehow find it hard to believe that
> > just sp_help_fultext_columns 'consultants' is taking 40 seconds. This system
> > stored proc only brings back a list of columns that have full text index on
> > them in a particular table(or table-column),
> >
> > If you can run trace and make sure that is the only thing running in the
> > backend when you click that button, that might help you narrow it down a
> > little more.
> >
> > To run a trace just click sql profiler from the Sql server program menu and
> > click File-New Trace & Hit run. go back and click the Button on the
> > Application. and see the list of sql commands being executed as a result.
> >
> > HTH,
> > RA
> >
> > "Jeff Ingman" wrote:
> >
> > > We have a SQL Server 2000 database on my PC running WinXP Pro with a GUI
> > > interface/application provided by a third party. We're recruiters and this is
> > > the â'Applicant Tracking Systemâ'/database we use to track our
> > > candidates/resumes and clients.
> > >
> > > This software was installed on my machine by others and I do not have the
> > > technical expertise to diagnose problems when they occur.
> > >
> > > There is a button within the application we click to search the (indexed)
> > > resumes based on keywords.
> > >
> > > Apparently this button runs a stored command thru SQL Server:
> > >
> > > sp_help_fultext_columns 'consultants"
> > >
> > > It used to be that when we clicked on the button a window (GUI) would open
> > > instantly with fields where we enter our key words and then execute the
> > > search.
> > >
> > > Howeverâ?¦ for unknown reasons it now takes approximately 40 seconds after we
> > > click for the search window to open. Our software provider does not know why.
> > > They logged on to my machine today and used Enterprise Manager and I think
> > > Query Analyzer to run the query above. It also took 40 seconds to open and
> > > they said this indicated we have a problem with SQL Server... not their
> > > application... and sent us off to find our own solution.
> > >
> > > We live in the country and there are no SQL Server experts within miles.
> > > Does this sound like a problem that could be diagnosed/fixed by someone who
> > > accesses my machine remotely?
> > >
> > > Or better yet... does anyone have a clue how to fix the problem simply based
> > > on my question above?|||I would like help Rocky... how do we arrange? You can find my email address
at www.ingmancompany.com.
jeff
"Rocky A" wrote:
> Jeff,
> I would suggest going through a consultant rather than a MS paid
> incident, I am guessing this is not an issue with Sql server as a product, it
> is more of a day-to-day technical issue, i would go the DBA route. I can
> take a look at it if you are interested.
> thanks,
> RA
> "Jeff Ingman" wrote:
> > Rocky...
> >
> > Thanks for your comments. But I'm not confident enough in my SQL expertise
> > to understand/follow thru on your recommendations.
> >
> > (One small point... I mispelled the query in my original post... it should
> > be "fulltext" not "fultext").
> >
> > I think I'll need to find a DBA who can logon to my machine remotely to
> > diagnose this.
> >
> > I'd have done this by now except we only access this command a couple times
> > daily and I haven't wanted to hire an expensive consultant.
> >
> > Would Microsoft paid incident support be a good route to go?
> > ........................
> > "Rocky A" wrote:
> >
> > > Jeff,
> > > If somebody can remote desktop in , they should be able to identify
> > > the issue.
> > >
> > > I don't want to sound presumptous but I somehow find it hard to believe that
> > > just sp_help_fultext_columns 'consultants' is taking 40 seconds. This system
> > > stored proc only brings back a list of columns that have full text index on
> > > them in a particular table(or table-column),
> > >
> > > If you can run trace and make sure that is the only thing running in the
> > > backend when you click that button, that might help you narrow it down a
> > > little more.
> > >
> > > To run a trace just click sql profiler from the Sql server program menu and
> > > click File-New Trace & Hit run. go back and click the Button on the
> > > Application. and see the list of sql commands being executed as a result.
> > >
> > > HTH,
> > > RA
> > >
> > > "Jeff Ingman" wrote:
> > >
> > > > We have a SQL Server 2000 database on my PC running WinXP Pro with a GUI
> > > > interface/application provided by a third party. We're recruiters and this is
> > > > the â'Applicant Tracking Systemâ'/database we use to track our
> > > > candidates/resumes and clients.
> > > >
> > > > This software was installed on my machine by others and I do not have the
> > > > technical expertise to diagnose problems when they occur.
> > > >
> > > > There is a button within the application we click to search the (indexed)
> > > > resumes based on keywords.
> > > >
> > > > Apparently this button runs a stored command thru SQL Server:
> > > >
> > > > sp_help_fultext_columns 'consultants"
> > > >
> > > > It used to be that when we clicked on the button a window (GUI) would open
> > > > instantly with fields where we enter our key words and then execute the
> > > > search.
> > > >
> > > > Howeverâ?¦ for unknown reasons it now takes approximately 40 seconds after we
> > > > click for the search window to open. Our software provider does not know why.
> > > > They logged on to my machine today and used Enterprise Manager and I think
> > > > Query Analyzer to run the query above. It also took 40 seconds to open and
> > > > they said this indicated we have a problem with SQL Server... not their
> > > > application... and sent us off to find our own solution.
> > > >
> > > > We live in the country and there are no SQL Server experts within miles.
> > > > Does this sound like a problem that could be diagnosed/fixed by someone who
> > > > accesses my machine remotely?
> > > >
> > > > Or better yet... does anyone have a clue how to fix the problem simply based
> > > > on my question above?sql

degraded stored procedure performance

Hi,

I have been experiencing an intermittent problem that I hope someone can help me with.

Twice over the last month or so, we have experienced a problem where the performance of a single stored procedure degrades dramatically very quickly, possibly instantaneously from ~1sec to ~15-25 secs. What's throwing me is there are hundreds of other stored procedures, but none of them has been affected. Is there a possibility that our Maintenance plan which is scheduled to reorganize the data and index pages on Sunday night be the culprit?? Both times the problem reared it's ugly head on Monday that's why I'm wondering if it is causing some problem with the sp's execution plan or something, because the problem goes away if I recompile the stored procedure. If it is the problem, I have a couple more questions, what can I do to help prevent it from occurring in the future, and why does it only affect one sp?? If however you don't think it is the cause any other thoughts would be greatly appreciated.

The stored procedure in question has not been modified for 2 months and generally performs fine.

SQL Server 2000 Enterprise Edition 8.00.679

Thanks in AdvanceWhat you have describe is not that uncommon. This would most likely happen if the size of the underlying table(s) grew or shrank significantly after the sp was compiled or if the data distibution drastically changed after the sp was compiled.

One thing you might want to do is force a recompile of all sp and triggers after the maintinance plan runs check out 'sp_recompile' in BOL.|||to step though all tables you can use:

Microsofts code:
sp_MSforeachtable 'sp_recompile ''?'''

Or your own:
declare @.Tbl sysname
select @.Tbl = min(name) from sysobjects where type = 'U'
while (@.Tbl is not null) begin
exec('sp_recompile ' + @.Tbl)
select @.Tbl = min(name) from sysobjects where type = 'U' and name > @.Tbl
end

Degraded performance after applying SP4 on SQL 2000 -- repost for no response

Hi group,
I am seeing degraded performance after applying SP4 on SQL 2000.
My specific queries (they are in stored proc) access linked server. When
the process is run, I keep seeing blocks on the linked server side among
processes of the same spid spun off from the calling process like this:
A. a process (say spid 58) on initiating server runs, some times shows
blocked by a process (say spid 111) on the linked server, some times not
displaying the block but just continue to run slow;
B. a few processes (spid 111) on the linked server running, often showing
one or two of them blocked by another spid 111.
I have googled and found that there are reports of such slowed down
performance after SP4 application, not necessarily related to the use of
linked server. There is an explanation by Microsoft why processes could
show being blocked by process of the same spid. Problem is that it looks
like no solution to the problem and nor explanation why the switch of locks
is so slow.
From what I read other people report, this blocking among processes of the
same spid repeats itself so often and is so slow that you can easily catch
the display of
such blocks in EM. This is the same case by me: when the process is
running, I
easily see such blocks with refresh of EM.
My process used to run about 30 sec on SP3a, now it runs some 15 min. The
only troubleshoot hint I saw sofar is the correct setup of MS DTC, which I
checked on my systems and is set up properly.
Wish to hear from you.
QuentinThe parallel process blocking occurred long before you applied SP4.
Microsoft added visibility to that blocking in SP4 so DBAs could manage
parallelism better. Reduce the degree of parallelism on your query, or even
server-wide, and you will see the self-blocking go away.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
news:uHMgUq0JGHA.2088@.TK2MSFTNGP11.phx.gbl...
> Hi group,
> I am seeing degraded performance after applying SP4 on SQL 2000.
> My specific queries (they are in stored proc) access linked server. When
> the process is run, I keep seeing blocks on the linked server side among
> processes of the same spid spun off from the calling process like this:
> A. a process (say spid 58) on initiating server runs, some times shows
> blocked by a process (say spid 111) on the linked server, some times not
> displaying the block but just continue to run slow;
> B. a few processes (spid 111) on the linked server running, often showing
> one or two of them blocked by another spid 111.
> I have googled and found that there are reports of such slowed down
> performance after SP4 application, not necessarily related to the use of
> linked server. There is an explanation by Microsoft why processes could
> show being blocked by process of the same spid. Problem is that it looks
> like no solution to the problem and nor explanation why the switch of
> locks is so slow.
> From what I read other people report, this blocking among processes of the
> same spid repeats itself so often and is so slow that you can easily catch
> the display of
> such blocks in EM. This is the same case by me: when the process is
> running, I
> easily see such blocks with refresh of EM.
> My process used to run about 30 sec on SP3a, now it runs some 15 min. The
> only troubleshoot hint I saw sofar is the correct setup of MS DTC, which I
> checked on my systems and is set up properly.
> Wish to hear from you.
> Quentin
>
>|||Geoff, Quentin read this link:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58294&whichpage=1
This SP4 problem is widespread and how can it be justifed, "I/O latch
jargon" or not this is not a feature, but an optimizer bug.
Queries/processes blocking themselves, that ain't parallelism. This
has been ignored by MS for too long.
John Lamarsh
Technical Manager
Full Circle Systems Inc.
Geoff N. Hiten wrote:
> The parallel process blocking occurred long before you applied SP4.
> Microsoft added visibility to that blocking in SP4 so DBAs could manage
> parallelism better. Reduce the degree of parallelism on your query, or even
> server-wide, and you will see the self-blocking go away.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
> news:uHMgUq0JGHA.2088@.TK2MSFTNGP11.phx.gbl...
> > Hi group,
> >
> > I am seeing degraded performance after applying SP4 on SQL 2000.
> >
> > My specific queries (they are in stored proc) access linked server. When
> > the process is run, I keep seeing blocks on the linked server side among
> > processes of the same spid spun off from the calling process like this:
> >
> > A. a process (say spid 58) on initiating server runs, some times shows
> > blocked by a process (say spid 111) on the linked server, some times not
> > displaying the block but just continue to run slow;
> >
> > B. a few processes (spid 111) on the linked server running, often showing
> > one or two of them blocked by another spid 111.
> >
> > I have googled and found that there are reports of such slowed down
> > performance after SP4 application, not necessarily related to the use of
> > linked server. There is an explanation by Microsoft why processes could
> > show being blocked by process of the same spid. Problem is that it looks
> > like no solution to the problem and nor explanation why the switch of
> > locks is so slow.
> > From what I read other people report, this blocking among processes of the
> > same spid repeats itself so often and is so slow that you can easily catch
> > the display of
> > such blocks in EM. This is the same case by me: when the process is
> > running, I
> > easily see such blocks with refresh of EM.
> >
> > My process used to run about 30 sec on SP3a, now it runs some 15 min. The
> > only troubleshoot hint I saw sofar is the correct setup of MS DTC, which I
> > checked on my systems and is set up properly.
> >
> > Wish to hear from you.
> >
> > Quentin
> >
> >
> >|||Thanks for both responses.
As John stated, this SP4 problem appear to be widespread. Other posts I
read through my google search support this. It does not have anything to do
whether the blocking between same spid shows up or not -- I don't care that.
It has to with the slow processing.
Strange enough that though I saw many complaint similar to mine (mostly do
not have linked server in the play), there is no solution reported, and no
heavy complaint that it does not get fixed, until I see the link John
provided. I guess it is a slow down that many can still afford, but my case
and in Jerry's case it was not.
Any additional comments are still welcome since I am still troubleshooting
this...
Quentin
<john@.fullcirclesystems.com> wrote in message
news:1138814051.851469.199190@.g14g2000cwa.googlegroups.com...
> Geoff, Quentin read this link:
> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58294&whichpage=1
> This SP4 problem is widespread and how can it be justifed, "I/O latch
> jargon" or not this is not a feature, but an optimizer bug.
> Queries/processes blocking themselves, that ain't parallelism. This
> has been ignored by MS for too long.
> John Lamarsh
> Technical Manager
> Full Circle Systems Inc.
> Geoff N. Hiten wrote:
>> The parallel process blocking occurred long before you applied SP4.
>> Microsoft added visibility to that blocking in SP4 so DBAs could manage
>> parallelism better. Reduce the degree of parallelism on your query, or
>> even
>> server-wide, and you will see the self-blocking go away.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>>
>> "Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
>> news:uHMgUq0JGHA.2088@.TK2MSFTNGP11.phx.gbl...
>> > Hi group,
>> >
>> > I am seeing degraded performance after applying SP4 on SQL 2000.
>> >
>> > My specific queries (they are in stored proc) access linked server.
>> > When
>> > the process is run, I keep seeing blocks on the linked server side
>> > among
>> > processes of the same spid spun off from the calling process like this:
>> >
>> > A. a process (say spid 58) on initiating server runs, some times shows
>> > blocked by a process (say spid 111) on the linked server, some times
>> > not
>> > displaying the block but just continue to run slow;
>> >
>> > B. a few processes (spid 111) on the linked server running, often
>> > showing
>> > one or two of them blocked by another spid 111.
>> >
>> > I have googled and found that there are reports of such slowed down
>> > performance after SP4 application, not necessarily related to the use
>> > of
>> > linked server. There is an explanation by Microsoft why processes
>> > could
>> > show being blocked by process of the same spid. Problem is that it
>> > looks
>> > like no solution to the problem and nor explanation why the switch of
>> > locks is so slow.
>> > From what I read other people report, this blocking among processes of
>> > the
>> > same spid repeats itself so often and is so slow that you can easily
>> > catch
>> > the display of
>> > such blocks in EM. This is the same case by me: when the process is
>> > running, I
>> > easily see such blocks with refresh of EM.
>> >
>> > My process used to run about 30 sec on SP3a, now it runs some 15 min.
>> > The
>> > only troubleshoot hint I saw sofar is the correct setup of MS DTC,
>> > which I
>> > checked on my systems and is set up properly.
>> >
>> > Wish to hear from you.
>> >
>> > Quentin
>> >
>> >
>> >
>|||It appears to be related to procedure call. When I execute the stored proc
itself, the process runs for 3 min, but when I execute the content of the
stored proc in QA it takes 15 seconds.|||Hello
I had the same problem when executing the store procedure and when
executing just the code of the procedure on QA. Executing the procedure
took more than 1 hour. Executing just the code took 14 seconds!
I opened a case on PSS and the problem was related to "parameter
snifing". There is an excelent post on Ken Herdenson Blog about the
subject. http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx
The solution that PSS gave to my problem was a little bit diferent from
the solution that is on Ken's Blog.
Here is what I did:
I have a strore procedure that have only one parameter as an input
value. This parameter is used all long the code. What PSS instruct me
was to not use the store procedure parameter but a local variable
throught the code.
Example of the old code:
create procedure @.foo datetime
as
select * from bar where foo = @.foo
Example of the new code:
create procedure @.foo_1 datetime
as
declare @.foo datetime
select @.foo=@.foo_1
select * from bar where foo=@.foo
The change was very simple but made a huge impact on performance. After
the change the store procedure took just 10 seconds.
Maybe you should give a try.
Regards
Carlos Selonke
http://carlos.geekbunker.org

Degraded performance after applying SP4 on SQL 2000 -- repost for no response

Hi group,
I am seeing degraded performance after applying SP4 on SQL 2000.
My specific queries (they are in stored proc) access linked server. When
the process is run, I keep seeing blocks on the linked server side among
processes of the same spid spun off from the calling process like this:
A. a process (say spid 58) on initiating server runs, some times shows
blocked by a process (say spid 111) on the linked server, some times not
displaying the block but just continue to run slow;
B. a few processes (spid 111) on the linked server running, often showing
one or two of them blocked by another spid 111.
I have googled and found that there are reports of such slowed down
performance after SP4 application, not necessarily related to the use of
linked server. There is an explanation by Microsoft why processes could
show being blocked by process of the same spid. Problem is that it looks
like no solution to the problem and nor explanation why the switch of locks
is so slow.
From what I read other people report, this blocking among processes of the
same spid repeats itself so often and is so slow that you can easily catch
the display of
such blocks in EM. This is the same case by me: when the process is
running, I
easily see such blocks with refresh of EM.
My process used to run about 30 sec on SP3a, now it runs some 15 min. The
only troubleshoot hint I saw sofar is the correct setup of MS DTC, which I
checked on my systems and is set up properly.
Wish to hear from you.
Quentin
The parallel process blocking occurred long before you applied SP4.
Microsoft added visibility to that blocking in SP4 so DBAs could manage
parallelism better. Reduce the degree of parallelism on your query, or even
server-wide, and you will see the self-blocking go away.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
news:uHMgUq0JGHA.2088@.TK2MSFTNGP11.phx.gbl...
> Hi group,
> I am seeing degraded performance after applying SP4 on SQL 2000.
> My specific queries (they are in stored proc) access linked server. When
> the process is run, I keep seeing blocks on the linked server side among
> processes of the same spid spun off from the calling process like this:
> A. a process (say spid 58) on initiating server runs, some times shows
> blocked by a process (say spid 111) on the linked server, some times not
> displaying the block but just continue to run slow;
> B. a few processes (spid 111) on the linked server running, often showing
> one or two of them blocked by another spid 111.
> I have googled and found that there are reports of such slowed down
> performance after SP4 application, not necessarily related to the use of
> linked server. There is an explanation by Microsoft why processes could
> show being blocked by process of the same spid. Problem is that it looks
> like no solution to the problem and nor explanation why the switch of
> locks is so slow.
> From what I read other people report, this blocking among processes of the
> same spid repeats itself so often and is so slow that you can easily catch
> the display of
> such blocks in EM. This is the same case by me: when the process is
> running, I
> easily see such blocks with refresh of EM.
> My process used to run about 30 sec on SP3a, now it runs some 15 min. The
> only troubleshoot hint I saw sofar is the correct setup of MS DTC, which I
> checked on my systems and is set up properly.
> Wish to hear from you.
> Quentin
>
>
|||Geoff, Quentin read this link:
http://www.sqlteam.com/forums/topic...94&whichpage=1
This SP4 problem is widespread and how can it be justifed, "I/O latch
jargon" or not this is not a feature, but an optimizer bug.
Queries/processes blocking themselves, that ain't parallelism. This
has been ignored by MS for too long.
John Lamarsh
Technical Manager
Full Circle Systems Inc.
Geoff N. Hiten wrote:[vbcol=seagreen]
> The parallel process blocking occurred long before you applied SP4.
> Microsoft added visibility to that blocking in SP4 so DBAs could manage
> parallelism better. Reduce the degree of parallelism on your query, or even
> server-wide, and you will see the self-blocking go away.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
> news:uHMgUq0JGHA.2088@.TK2MSFTNGP11.phx.gbl...
|||Thanks for both responses.
As John stated, this SP4 problem appear to be widespread. Other posts I
read through my google search support this. It does not have anything to do
whether the blocking between same spid shows up or not -- I don't care that.
It has to with the slow processing.
Strange enough that though I saw many complaint similar to mine (mostly do
not have linked server in the play), there is no solution reported, and no
heavy complaint that it does not get fixed, until I see the link John
provided. I guess it is a slow down that many can still afford, but my case
and in Jerry's case it was not.
Any additional comments are still welcome since I am still troubleshooting
this...
Quentin
<john@.fullcirclesystems.com> wrote in message
news:1138814051.851469.199190@.g14g2000cwa.googlegr oups.com...
> Geoff, Quentin read this link:
> http://www.sqlteam.com/forums/topic...94&whichpage=1
> This SP4 problem is widespread and how can it be justifed, "I/O latch
> jargon" or not this is not a feature, but an optimizer bug.
> Queries/processes blocking themselves, that ain't parallelism. This
> has been ignored by MS for too long.
> John Lamarsh
> Technical Manager
> Full Circle Systems Inc.
> Geoff N. Hiten wrote:
>
|||It appears to be related to procedure call. When I execute the stored proc
itself, the process runs for 3 min, but when I execute the content of the
stored proc in QA it takes 15 seconds.
|||Hello
I had the same problem when executing the store procedure and when
executing just the code of the procedure on QA. Executing the procedure
took more than 1 hour. Executing just the code took 14 seconds!
I opened a case on PSS and the problem was related to "parameter
snifing". There is an excelent post on Ken Herdenson Blog about the
subject. http://blogs.msdn.com/khen1234/archi...02/424228.aspx
The solution that PSS gave to my problem was a little bit diferent from
the solution that is on Ken's Blog.
Here is what I did:
I have a strore procedure that have only one parameter as an input
value. This parameter is used all long the code. What PSS instruct me
was to not use the store procedure parameter but a local variable
throught the code.
Example of the old code:
create procedure @.foo datetime
as
select * from bar where foo = @.foo
Example of the new code:
create procedure @.foo_1 datetime
as
declare @.foo datetime
select @.foo=@.foo_1
select * from bar where foo=@.foo
The change was very simple but made a huge impact on performance. After
the change the store procedure took just 10 seconds.
Maybe you should give a try.
Regards
Carlos Selonke
http://carlos.geekbunker.org

Degraded performance after applying SP4 on SQL 2000 -- repost for no response

Hi group,
I am seeing degraded performance after applying SP4 on SQL 2000.
My specific queries (they are in stored proc) access linked server. When
the process is run, I keep seeing blocks on the linked server side among
processes of the same spid spun off from the calling process like this:
A. a process (say spid 58) on initiating server runs, some times shows
blocked by a process (say spid 111) on the linked server, some times not
displaying the block but just continue to run slow;
B. a few processes (spid 111) on the linked server running, often showing
one or two of them blocked by another spid 111.
I have googled and found that there are reports of such slowed down
performance after SP4 application, not necessarily related to the use of
linked server. There is an explanation by Microsoft why processes could
show being blocked by process of the same spid. Problem is that it looks
like no solution to the problem and nor explanation why the switch of locks
is so slow.
From what I read other people report, this blocking among processes of the
same spid repeats itself so often and is so slow that you can easily catch
the display of
such blocks in EM. This is the same case by me: when the process is
running, I
easily see such blocks with refresh of EM.
My process used to run about 30 sec on SP3a, now it runs some 15 min. The
only troubleshoot hint I saw sofar is the correct setup of MS DTC, which I
checked on my systems and is set up properly.
Wish to hear from you.
QuentinThe parallel process blocking occurred long before you applied SP4.
Microsoft added visibility to that blocking in SP4 so DBAs could manage
parallelism better. Reduce the degree of parallelism on your query, or even
server-wide, and you will see the self-blocking go away.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
news:uHMgUq0JGHA.2088@.TK2MSFTNGP11.phx.gbl...
> Hi group,
> I am seeing degraded performance after applying SP4 on SQL 2000.
> My specific queries (they are in stored proc) access linked server. When
> the process is run, I keep seeing blocks on the linked server side among
> processes of the same spid spun off from the calling process like this:
> A. a process (say spid 58) on initiating server runs, some times shows
> blocked by a process (say spid 111) on the linked server, some times not
> displaying the block but just continue to run slow;
> B. a few processes (spid 111) on the linked server running, often showing
> one or two of them blocked by another spid 111.
> I have googled and found that there are reports of such slowed down
> performance after SP4 application, not necessarily related to the use of
> linked server. There is an explanation by Microsoft why processes could
> show being blocked by process of the same spid. Problem is that it looks
> like no solution to the problem and nor explanation why the switch of
> locks is so slow.
> From what I read other people report, this blocking among processes of the
> same spid repeats itself so often and is so slow that you can easily catch
> the display of
> such blocks in EM. This is the same case by me: when the process is
> running, I
> easily see such blocks with refresh of EM.
> My process used to run about 30 sec on SP3a, now it runs some 15 min. The
> only troubleshoot hint I saw sofar is the correct setup of MS DTC, which I
> checked on my systems and is set up properly.
> Wish to hear from you.
> Quentin
>
>|||Geoff, Quentin read this link:
http://www.sqlteam.com/forums/topic...294&whichpage=1
This SP4 problem is widespread and how can it be justifed, "I/O latch
jargon" or not this is not a feature, but an optimizer bug.
Queries/processes blocking themselves, that ain't parallelism. This
has been ignored by MS for too long.
John Lamarsh
Technical Manager
Full Circle Systems Inc.
Geoff N. Hiten wrote:[vbcol=seagreen]
> The parallel process blocking occurred long before you applied SP4.
> Microsoft added visibility to that blocking in SP4 so DBAs could manage
> parallelism better. Reduce the degree of parallelism on your query, or ev
en
> server-wide, and you will see the self-blocking go away.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
> news:uHMgUq0JGHA.2088@.TK2MSFTNGP11.phx.gbl...|||Thanks for both responses.
As John stated, this SP4 problem appear to be widespread. Other posts I
read through my google search support this. It does not have anything to do
whether the blocking between same spid shows up or not -- I don't care that.
It has to with the slow processing.
Strange enough that though I saw many complaint similar to mine (mostly do
not have linked server in the play), there is no solution reported, and no
heavy complaint that it does not get fixed, until I see the link John
provided. I guess it is a slow down that many can still afford, but my case
and in Jerry's case it was not.
Any additional comments are still welcome since I am still troubleshooting
this...
Quentin
<john@.fullcirclesystems.com> wrote in message
news:1138814051.851469.199190@.g14g2000cwa.googlegroups.com...
> Geoff, Quentin read this link:
> http://www.sqlteam.com/forums/topic...294&whichpage=1
> This SP4 problem is widespread and how can it be justifed, "I/O latch
> jargon" or not this is not a feature, but an optimizer bug.
> Queries/processes blocking themselves, that ain't parallelism. This
> has been ignored by MS for too long.
> John Lamarsh
> Technical Manager
> Full Circle Systems Inc.
> Geoff N. Hiten wrote:
>|||It appears to be related to procedure call. When I execute the stored proc
itself, the process runs for 3 min, but when I execute the content of the
stored proc in QA it takes 15 seconds.|||Hello
I had the same problem when executing the store procedure and when
executing just the code of the procedure on QA. Executing the procedure
took more than 1 hour. Executing just the code took 14 seconds!
I opened a case on PSS and the problem was related to "parameter
snifing". There is an excelent post on Ken Herdenson Blog about the
subject. http://blogs.msdn.com/khen1234/arch.../02/424228.aspx
The solution that PSS gave to my problem was a little bit diferent from
the solution that is on Ken's Blog.
Here is what I did:
I have a strore procedure that have only one parameter as an input
value. This parameter is used all long the code. What PSS instruct me
was to not use the store procedure parameter but a local variable
throught the code.
Example of the old code:
create procedure @.foo datetime
as
select * from bar where foo = @.foo
Example of the new code:
create procedure @.foo_1 datetime
as
declare @.foo datetime
select @.foo=@.foo_1
select * from bar where foo=@.foo
The change was very simple but made a huge impact on performance. After
the change the store procedure took just 10 seconds.
Maybe you should give a try.
Regards
Carlos Selonke
http://carlos.geekbunker.orgsql

Defrag data disk

Hi,
The disks on which my databases are stored is heavily
fragmented. This because of wrong database grow settings.
Can I use the Windows system tool: Disk Defragmenter to
defragment my disk or will SQL not like this?
Thanks,
Jeroen.I believe you can, so long as the block size <= 4KB.
You will need to stop sql server if you want the data files defragemented
"Jeroen" <nieuwdamsigt@.hotmail.com> wrote in message
news:006101c3c31f$9e391750$a101280a@.phx.gbl...
> Hi,
> The disks on which my databases are stored is heavily
> fragmented. This because of wrong database grow settings.
> Can I use the Windows system tool: Disk Defragmenter to
> defragment my disk or will SQL not like this?
> Thanks,
> Jeroen.|||"Jeroen" <nieuwdamsigt@.hotmail.com> wrote in message
news:006101c3c31f$9e391750$a101280a@.phx.gbl...
> Hi,
> The disks on which my databases are stored is heavily
> fragmented. This because of wrong database grow settings.
> Can I use the Windows system tool: Disk Defragmenter to
> defragment my disk or will SQL not like this?
>
Also your disks may appear more fragmented than they really are.
If you have a few large files that are in 2 fragments, you disk can report
as being 90% fragmented without this being a big deal.
David

Definition of Object Has Changed Since it was last compiles

All I'm having a weird problem.. I have 2 stored procedures that run 98% of the time without any issue but inconsistenly through the following error.

'The definition of object 'proc name goes here' has changed since it was compiled'

We have adding 'with recompile' to the proc but we still get this error - but not consistently. The stored proc is not changing nor is the table structure of any of the objects that are being used in the sp. Any idea to trace down the why this is happening or what objecte it thinks is changing? Let me know your thoughts.

Ken

We've started experiencing this, except:
- It's occurring 100% of the time on SQL 2K5, for a particular data set, but not for another data set on the same schema.
- It never occurred in SQL 2K.
- It only occurs on SQL 2K5 (w/ DB in 2K compatibility mode)

My first reaction, for our case, is that it's a broken 2K5/2K compatibility issue. We're doing something pretty shady - disabling a trigger on table B from within a trigger firing on table A. IOW:

Trigger A, Table A:
- Disable trigger B on table B
- UPDATE table B
- Re-enable trigger B

So I'm guessing SQL 2K5 is finally calling us out on this. But I'd still prefer a quick fix to rewriting the triggers. Have you had any luck with your issue?

Defining custom Roles with limited access to SQL Objects

Hi!

I'm assisting in the creation of a development enviroment with SQL Server 2005, and I need to assign some custom roles, in particular, a Stored Developer Role should be able to create, modify and execute Stored Procedures but they should not be able to alter tables or views, but should be able to retrieve/insert data from those tables.

I've tried with the default roles in 2005 to no avail.

Is there a relatively easy way to accomplish this with a database alredy populated with objects of both kinds? (SP's and Tables / Views)

Thanks!

You should create your own role and assign the required permissions to it. See CREATE ROLE for information on how to create a role.

Thanks
Laurentiu

define query parameters ?

when I'm defining a dataset based on stored procedure I type in the name of
my stored proc and hit the !
then define query parameters comes up. My stored proc has default values
defined for the parameter. Why not have the define query parameters fill in
those default values? It's really anoying to type in 15 parameters almost
everytime I need to re-run the query.HI,
"letuce dance" <letucedance@.discussions.microsoft.com> schrieb im
Newsbeitrag news:C5B2E705-D5B4-47F7-8595-FDC9266B917F@.microsoft.com...
> when I'm defining a dataset based on stored procedure I type in the name
> of
> my stored proc and hit the !
> then define query parameters comes up. My stored proc has default values
> defined for the parameter. Why not have the define query parameters fill
> in
> those default values? It's really anoying to type in 15 parameters almost
> everytime I need to re-run the query.
you can set the default values in the report itself.
witch to layout, click on the upper left corner (report properties), context
menu "report parameters" and there you can set the default settings
"not-queried" and store all your default-parameter values.
hth, Tony

Deferrend Name Resolution for a field in a Stored Procedure

We are doing an upgrade in about a month and changing the account structure
in one of our tables. I am trying to write a routine that will check to see
if we are using the old format or the new format. So I wrote the following
stored procedure:
CREATE PROCEDURE [dbo].[sp_Account_Info] AS
if exists(select COLUMN_NAME = convert(sysname,name) from syscolumns where
name ='ACTNUMBR_6')
begin
select ACTNUMBR_5,ACTNUMBR_6 from Account_Table
--Do more stuff
end
else
begin
select ACTNUMBR_4 from Account_Table
--Do more stuff
end
In the old format the columns stop at ACTUNUMBR_4, but in the new table
structure(Which has not been implmented yet) we will be adding ACTNUMBR_5
and ACTNUMBR_6. The problem I am having is SQLServer will not let me save
this stored procedure because it says I have an invalid column name. Is
there a way to save the stored procedure even though the new columns do not
yet exist?You could use dynamic SQL (www.sommarskog.se), but perhaps you should consid
er a stable data model
instead, which doesn't require you to add columns over time.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Miles C" <mcousens@.clearwater.ca> wrote in message news:eZmKRAHlGHA.408@.TK2MSFTNGP03.phx.g
bl...
> We are doing an upgrade in about a month and changing the account structur
e in one of our tables.
> I am trying to write a routine that will check to see if we are using the
old format or the new
> format. So I wrote the following stored procedure:
> CREATE PROCEDURE [dbo].[sp_Account_Info] AS
> if exists(select COLUMN_NAME = convert(sysname,name) from syscolumns where
name ='ACTNUMBR_6')
> begin
> select ACTNUMBR_5,ACTNUMBR_6 from Account_Table
> --Do more stuff
> end
> else
> begin
> select ACTNUMBR_4 from Account_Table
> --Do more stuff
> end
> In the old format the columns stop at ACTUNUMBR_4, but in the new table st
ructure(Which has not
> been implmented yet) we will be adding ACTNUMBR_5 and ACTNUMBR_6. The pro
blem I am having is
> SQLServer will not let me save this stored procedure because it says I hav
e an invalid column
> name. Is there a way to save the stored procedure even though the new col
umns do not yet exist?
>

DEFAULT values for Procedure parameters

Hi there,
I was wondering if there's a way to get the default
values for stored procedure parameters.
I've tried both MS SQL 2000 and SQL 2005, but
I cannot figure out a way to do it except for parsing
the source code of the procedure.
Both "sys.parameters" and "syscolumns" have references
to default values, but neither fills them in for TSQL
Stored Procedures.
Anyone has an idea?
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
Hi
I have my doubt that you have a direct way to get it out
USE Northwind
GO
SELECT *
FROM information_schema.routines
WHERE ROUTINE_NAME = 'SPname'
"Martijn Tonies" <m.tonies@.upscene.removethis.com> wrote in message
news:%236ALBXaHGHA.2012@.TK2MSFTNGP14.phx.gbl...
> Hi there,
> I was wondering if there's a way to get the default
> values for stored procedure parameters.
> I've tried both MS SQL 2000 and SQL 2005, but
> I cannot figure out a way to do it except for parsing
> the source code of the procedure.
> Both "sys.parameters" and "syscolumns" have references
> to default values, but neither fills them in for TSQL
> Stored Procedures.
> Anyone has an idea?
> --
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
> Server
> Upscene Productions
> http://www.upscene.com
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
>

DEFAULT values for Procedure parameters

Hi there,
I was wondering if there's a way to get the default
values for stored procedure parameters.
I've tried both MS SQL 2000 and SQL 2005, but
I cannot figure out a way to do it except for parsing
the source code of the procedure.
Both "sys.parameters" and "syscolumns" have references
to default values, but neither fills them in for TSQL
Stored Procedures.
Anyone has an idea?
--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.comHi
I have my doubt that you have a direct way to get it out
USE Northwind
GO
SELECT *
FROM information_schema.routines
WHERE ROUTINE_NAME = 'SPname'
"Martijn Tonies" <m.tonies@.upscene.removethis.com> wrote in message
news:%236ALBXaHGHA.2012@.TK2MSFTNGP14.phx.gbl...
> Hi there,
> I was wondering if there's a way to get the default
> values for stored procedure parameters.
> I've tried both MS SQL 2000 and SQL 2005, but
> I cannot figure out a way to do it except for parsing
> the source code of the procedure.
> Both "sys.parameters" and "syscolumns" have references
> to default values, but neither fills them in for TSQL
> Stored Procedures.
> Anyone has an idea?
> --
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
> Server
> Upscene Productions
> http://www.upscene.com
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
>

DEFAULT values for Procedure parameters

Hi there,
I was wondering if there's a way to get the default
values for stored procedure parameters.
I've tried both MS SQL 2000 and SQL 2005, but
I cannot figure out a way to do it except for parsing
the source code of the procedure.
Both "sys.parameters" and "syscolumns" have references
to default values, but neither fills them in for TSQL
Stored Procedures.
Anyone has an idea?
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.comHi
I have my doubt that you have a direct way to get it out
USE Northwind
GO
SELECT *
FROM information_schema.routines
WHERE ROUTINE_NAME = 'SPname'
"Martijn Tonies" <m.tonies@.upscene.removethis.com> wrote in message
news:%236ALBXaHGHA.2012@.TK2MSFTNGP14.phx.gbl...
> Hi there,
> I was wondering if there's a way to get the default
> values for stored procedure parameters.
> I've tried both MS SQL 2000 and SQL 2005, but
> I cannot figure out a way to do it except for parsing
> the source code of the procedure.
> Both "sys.parameters" and "syscolumns" have references
> to default values, but neither fills them in for TSQL
> Stored Procedures.
> Anyone has an idea?
> --
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
> Server
> Upscene Productions
> http://www.upscene.com
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
>

default value to a stored proc

I'm trying to put char(0) as the default value to a variable in a stored
procedure .But it gives me syntax error .
Is there any way to escape that. I cannot use '' or anything because we
decided to use char(0) whereever we need empty string.
Thanks
create procedure TESTING_001
@.COLUMNNAME varchar(50) = char(0)
as
print @.COLUMNNAME
GOCHAR in a valid T-SQL string function. You cannot use functions as default
values for input parameters to stored procedures/UDFs. One workaround is to
assign the parameter a constant default & change it in the first line of the
stored procedure like:
SET @.p = COALESCE(NULLIF(@.p, ''), CHAR(0)) ;
--
- Anith
( Please reply to newsgroups only )