A doubt about this: "Could not find stored Procedure"

Hi Everybody!

I have a problem in my SQL Server 2000 SP1 Database Called "Embossamento". When I run the following comand in the Query Analyzer, I have this error message :

command: exec dbcc_all_dbreindex
message: Could not find stored procedure 'dbcc_all_dbreindex'.

Otherwise, in the same server, but in another Database called "Autorizacao" I execute this dbcc_all_dbreindex with no problems.
Does anybody know why this happens?
I will be waiting for some reply, ok?
Thanks,This must be a custom stored procedure that uses the dbcc dbreindex command - check out the database, Autorizacao, and look for your stored procedures under that database.|||Ok! Thanks!
It worked! I just realized that my database did not have the procedure dbcc_all_dbreindex.
Now it's ok...

A difficult Combining Rows problem

Greetings,
I'm working to combine rows based on a time window and I am hoping to
be able to write a stored procedure to do this for me, rather than have
parse through all this data in my program. I'm not very well versed
with T-SQL syntax.. just enough to get by selecting using inner joins,
updating and inserting... thats about it. (Hence why I am here.)
The raw data I have below looks like this:
groupID, StartTime, EndTime, Min, Max, Points
----
1, 2005-10-05 06:00, 2005-10-05 06:14:59, 7, 32, 13
1, 2005-10-05 06:15, 2005-10-05 06:29:59, 5, 29, 6
1, 2005-10-05 06:30, 2005-10-05 06:44:59, 5, 28, 4
1, 2005-10-05 06:45, 2005-10-05 06:59:59, 5, 29, 16
1, 2005-10-05 07:00, 2005-10-05 07:14:59, 5, 23, 13
1, 2005-10-05 07:15, 2005-10-05 07:29:59, 5, 25, 18
1, 2005-10-05 07:30, 2005-10-05 07:44:59, 5, 34, 49
1, 2005-10-05 07:45, 2005-10-05 07:59:59, 5, 31, 49
Pretty straight forward; you can see each entry is a 15 minute time
interval. What I want to be able to do is to use a view or a stored
procedure to view this in one hour chunks, like below:
groupID, StartTime, EndTime, Min, Max, Points
----
1, 2005-10-05 06:00, 2005-10-05 06:59:59, 5, 32, 39
1, 2005-10-05 07:00, 2005-10-05 07:59:59, 5, 34, 129
This involves several things:
- Recognizing that there are variable # of rows (maybe we only have 3
15 minute entries instead of 4)
- Getting a min of those row's min column
- Getting a max of those row's max column
- Getting a total for those row's points column
- Input to any view or whatver would be based on the startTime and
endTime and would always be in whole hours.
I have a feeling that I am going to be doing this all in the C# .NET
end of things, but it's at least worth a shot asking all of you SQL
experts. What I am basically interested in knowing is, do you all
think that this is possible using views or stored procedures or
something else I don't know about. I didn't even know about views
until i started researching how to do this.
Any ideas? Is this possible? Should I just give up and do it on the
C# end of things? Seems to me that it might be possible to do in a
stored procedure, but possible not worth my time. I aprpeciate any
help or suggestions.
JasonTry this:
SELECT groupid,
MIN(DATEADD(HH,DATEDIFF(HH,'20050101',st
arttime),'20050101')),
MIN(DATEADD(HH,DATEDIFF(HH,'20050101',st
arttime),'2005-01-01T00:59:59')),
MIN(min), MAX(max), SUM(points)
FROM tbl
GROUP BY groupid, DATEDIFF(HH,'20050101',starttime) ;
David Portas
SQL Server MVP
--|||Hi
Check out the dateadd/datepart functions in Books Online for rounding times.
Try:
SELECT GROUPID, DATEADD(mi,-DATEPART(mi,Starttime),Starttime) AS StartTime,
DATEADD(ms,-3,DATEADD(hh,1,DATEADD(mi,-DATEPART(mi,Starttime),Starttime)))
AS EndTime,
Min([Min]), Max([Max]), SUM([Points])
FROM Readings
GROUP BY GroupId,
DATEADD(mi,-DATEPART(mi,Starttime),Starttime),
DATEADD(ms,-3,DATEADD(hh,1,DATEADD(mi,-DATEPART(mi,Starttime),Starttime)))
John
"Factor" wrote:

> Greetings,
> I'm working to combine rows based on a time window and I am hoping to
> be able to write a stored procedure to do this for me, rather than have
> parse through all this data in my program. I'm not very well versed
> with T-SQL syntax.. just enough to get by selecting using inner joins,
> updating and inserting... thats about it. (Hence why I am here.)
> The raw data I have below looks like this:
> groupID, StartTime, EndTime, Min, Max, Points
> ----
> 1, 2005-10-05 06:00, 2005-10-05 06:14:59, 7, 32, 13
> 1, 2005-10-05 06:15, 2005-10-05 06:29:59, 5, 29, 6
> 1, 2005-10-05 06:30, 2005-10-05 06:44:59, 5, 28, 4
> 1, 2005-10-05 06:45, 2005-10-05 06:59:59, 5, 29, 16
> 1, 2005-10-05 07:00, 2005-10-05 07:14:59, 5, 23, 13
> 1, 2005-10-05 07:15, 2005-10-05 07:29:59, 5, 25, 18
> 1, 2005-10-05 07:30, 2005-10-05 07:44:59, 5, 34, 49
> 1, 2005-10-05 07:45, 2005-10-05 07:59:59, 5, 31, 49
> Pretty straight forward; you can see each entry is a 15 minute time
> interval. What I want to be able to do is to use a view or a stored
> procedure to view this in one hour chunks, like below:
> groupID, StartTime, EndTime, Min, Max, Points
> ----
> 1, 2005-10-05 06:00, 2005-10-05 06:59:59, 5, 32, 39
> 1, 2005-10-05 07:00, 2005-10-05 07:59:59, 5, 34, 129
> This involves several things:
> - Recognizing that there are variable # of rows (maybe we only have 3
> 15 minute entries instead of 4)
> - Getting a min of those row's min column
> - Getting a max of those row's max column
> - Getting a total for those row's points column
> - Input to any view or whatver would be based on the startTime and
> endTime and would always be in whole hours.
> I have a feeling that I am going to be doing this all in the C# .NET
> end of things, but it's at least worth a shot asking all of you SQL
> experts. What I am basically interested in knowing is, do you all
> think that this is possible using views or stored procedures or
> something else I don't know about. I didn't even know about views
> until i started researching how to do this.
> Any ideas? Is this possible? Should I just give up and do it on the
> C# end of things? Seems to me that it might be possible to do in a
> stored procedure, but possible not worth my time. I aprpeciate any
> help or suggestions.
> Jason
>|||John Bell and David Portas,
I will have to read up on these Dateadd/DatePart parameters an actually
interpret what is going on within these statements, but just from what
you gave me here it looks like this will work out very well, and I
really appreciate the insight. This will allow me to vary that time
window fairly easily I do believe, all on a SQL call (that's much
better than bringing back all the data and parsing through it all it.
Thanks again,
Jason|||John
I have read over those functions and I now understand what they do and
how to use them, but I am still as to why the min / max /
total fields actually work. I assume it has something to do with the
GROUP BY statements, but again, I don't know why.
Assuming black magic happens and thats just how it works, I should just
be able to change those hh,1 to hh,4 and get 4 hour increments instead.
When I do that, the Starttime and Endtime values do return correctly
(although I do get an entry for 8-12, 9-1, 10-2, etc... thats fine) but
the MIN/MAX/SUM stuff is still reflective of the 1 hour timing.. so
that black magic that is limiting the MIN/MAX/SUM to one hour is still
limiting them to one hour even with the altered start and end times.
I'm unsure how to fix or get around this because I don't yet understand
what is limiting that max to an hour in the first place. How does this
work? I've been tripped up GROUP BY things before, it's my kryptonite
for some reason.
Hope that is not too confusing, I'm all jumbled in my head.
I really apprecaite the help with this so far, you've all been
wonderful.
Jason|||John
I have read over those functions and I now understand what they do and
how to use them, but I am still as to why the min / max /
total fields actually work. I assume it has something to do with the
GROUP BY statements, but again, I don't know why.
Assuming black magic happens and thats just how it works, I should just
be able to change those hh,1 to hh,4 and get 4 hour increments instead.
When I do that, the Starttime and Endtime values do return correctly
(although I do get an entry for 8-12, 9-1, 10-2, etc... thats fine) but
the MIN/MAX/SUM stuff is still reflective of the 1 hour timing.. so
that black magic that is limiting the MIN/MAX/SUM to one hour is still
limiting them to one hour even with the altered start and end times.
I'm unsure how to fix or get around this because I don't yet understand
what is limiting that max to an hour in the first place. How does this
work? I've been tripped up GROUP BY things before, it's my kryptonite
for some reason.
Hope that is not too confusing, I'm all jumbled in my head.
I really apprecaite the help with this so far, you've all been
wonderful.
Jason|||On 10 Nov 2005 11:41:54 -0800, Factor wrote:

>John
>I have read over those functions and I now understand what they do and
>how to use them, but I am still as to why the min / max /
>total fields actually work. I assume it has something to do with the
>GROUP BY statements, but again, I don't know why.
Hi Jason,
Correct. The GROUP BY tells SQL Server to combine the data from several
rows into one row. This is normally used to report totals, minimum,
maximum per project, per section, etc. But with the appropriate
expression, it cal also be used to combine rows that fit in the same
"period" into one group.
Though John's and David's versions both work, I suggest you go with
Davids version, as this is more flexible. (And, once you get your head
around it, easier to understand as well).
Basically, John's version works by taking each of the date parts you
want to disregard (milliseconds, seconds, minutes), then subtracting
that amount of time from the Starttime. The end result will of course be
the last full hour equal to or before Starttime.
David's version works the other way around - it calculates the number of
full hours that have elapsed since a chosen anchor date, then adds that
number to the chosen anchor date. The result will be the same as John's
expression.
(Note: David chose to just use the number of hours for the group by, and
add it back to the anchor date in the SELECT clause only)

>Assuming black magic happens and thats just how it works, I should just
>be able to change those hh,1 to hh,4 and get 4 hour increments instead.
No. I'll give you two examples how to modify David's query to report on
4-hour intervals and to report on 1/2-hour intervals.
For 4-hour intervals, again calculate the number of hours since an
anchor date. Divide by 4 and truncate, then multiply by 4 again. Add
this number of hours to the anchor date. There you have the start of the
last 4-hour interval
SELECT groupid,
MIN(DATEADD(hour,
4 * (DATEDIFF(hour, '20050101', Starttime) / 4),
'20050101')),
MIN(DATEADD(hour,
4 * (DATEDIFF(hour, '20050101', Starttime) / 4),
'2005-01-01T03:59:59')),
MIN(min), MAX(max), SUM(points)
FROM tbl
GROUP BY groupid, DATEDIFF(hour, '20050101', Starttime) / 4;
For 1/2-hour intervals, we can't divide the number of hours sice the
anchor date by 0.5, as that won't give us back the precision we already
lost. Instead, we'll have to calculate minutes and divide by 30:
SELECT groupid,
MIN(DATEADD(minute,
30 * (DATEDIFF(minute, '20050101', Starttime) /30),
'20050101')),
MIN(DATEADD(minute,
30 * (DATEDIFF(minute, '20050101', Starttime) /30),
'2005-01-01T00:29:59')),
MIN(min), MAX(max), SUM(points)
FROM tbl
GROUP BY groupid, DATEDIFF(minute, '20050101', Starttime) /30;
In both cases, don't forget to change the shifted anchor value in the
expression for the end point of the interval. Instead of using the same
anchor date, then adding 30 minunte or 4 hours minus one second, the
anchor date is shifted by 30 minutes or 4 hours minus one second.
Now, the above code can still be simplified further. If your table
always has the complete data (as your sample roiws indicate), then you
could change the above queries to:
SELECT groupid,
MIN(StartTime), MAX(EndTime),
MIN(min), MAX(max), SUM(points)
FROM tbl
GROUP BY groupid, DATEDIFF(minute, '20050101', Starttime) /30;
-- or: GROUP BY groupid, DATEDIFF(hour, '20050101', Starttime) / 4;
Note that this might show "holes" in the periods if your real data is
not as complete as the sample you posted indicates. But the advantage is
that you get rid of the "shifted" anchor date for calculating end time.
Final step would be to put it in a stored procedure and use a parameter
for the interval length (in minutes):
SELECT groupid,
MIN(StartTime), MAX(EndTime),
MIN(min), MAX(max), SUM(points)
FROM tbl
GROUP BY groupid, DATEDIFF(minute, '20050101', Starttime) / @.Interval;
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Jason,
What David Provided is an Excellent query .
Let us see if this query can help you.
Select GID , Min(STime) , Max(ETime) ,
Min(Minimum),Max(Maximum),Sum(Points)[co
lor=darkred]
>From yourTableName Group By[/color]
GID,Convert(varchar,STime,112),DatePart(
hh,STime)
Having same name as Functions/ Keyword sound confusing to me so I
changed them.
With Warm Regards
Jatinder Singh|||Hi
This is easier with David's method (see Hugo's reply for an explanation).
Dividing the number of hours by 4 and dropping the remainder will give you 4
hour chunks when they are multiplied back up. You also need to change the en
d
time to give a 4 hour gap.
SELECT groupid,
MIN(DATEADD(HH,
4*(DATEDIFF(HH,'20050101',starttime)/4),'20050101')
) AS Starttime,
MAX(DATEADD(HH,
4*(DATEDIFF(HH,'20050101',starttime)/4),'2005-01-01T03:59:59')
) AS Endtime,
MIN(min) AS [Min],
MAX(max) AS [Max],
SUM(points) AS [Total Points]
FROM Readings
GROUP BY groupid,
4*(DATEDIFF(HH,'20050101',starttime)/4)
John
"Factor" wrote:

> John
> I have read over those functions and I now understand what they do and
> how to use them, but I am still as to why the min / max /
> total fields actually work. I assume it has something to do with the
> GROUP BY statements, but again, I don't know why.
> Assuming black magic happens and thats just how it works, I should just
> be able to change those hh,1 to hh,4 and get 4 hour increments instead.
> When I do that, the Starttime and Endtime values do return correctly
> (although I do get an entry for 8-12, 9-1, 10-2, etc... thats fine) but
> the MIN/MAX/SUM stuff is still reflective of the 1 hour timing.. so
> that black magic that is limiting the MIN/MAX/SUM to one hour is still
> limiting them to one hour even with the altered start and end times.
> I'm unsure how to fix or get around this because I don't yet understand
> what is limiting that max to an hour in the first place. How does this
> work? I've been tripped up GROUP BY things before, it's my kryptonite
> for some reason.
> Hope that is not too confusing, I'm all jumbled in my head.
> I really apprecaite the help with this so far, you've all been
> wonderful.
> Jason
>|||Wondeful! Lots ot take in, I thank everyone for their help. I've made
a lot of progress and I've learned a TON about SQL int he past two
days.
I hope I can help you all in the future with something!
Thanks again,
Jason

A DB block during stored procedure excecution...

Hi all,
I'd like to ask if is there any deference between executing a stored
procedure with 'exec' command in the Query analyzer and executing the code o
f
the stored procedure in Query analyzer too. I mean that I copied the source
of stored procedure in a window of the analyzer, I declare the parameters of
stored procedure as variables and I set the same values into them and I just
run the source...
When I execute the stored proc by "exec sp 'x', 'y', 'z' " where x,y,z are
the parameters sometimes it occures a block... When I run the source
declaring the parameters as variables etc, all are fine and I have never any
block... How is it possible? Is there any idea?
Thanks in advance..Christos,
What do you mean by block?
See if this helps:
[url]http://groups-beta.google.com/group/microsoft.public.sqlserver.server/msg/a5517668
94ed8781? q=%22what+is%22%2B%22parameter+sniffing%
22&hl=en&lr=&ie=UTF-8&rnum=1[/url
]
AMB
"Christos" wrote:

> Hi all,
> I'd like to ask if is there any deference between executing a stored
> procedure with 'exec' command in the Query analyzer and executing the code
of
> the stored procedure in Query analyzer too. I mean that I copied the sourc
e
> of stored procedure in a window of the analyzer, I declare the parameters
of
> stored procedure as variables and I set the same values into them and I ju
st
> run the source...
> When I execute the stored proc by "exec sp 'x', 'y', 'z' " where x,y,z are
> the parameters sometimes it occures a block... When I run the source
> declaring the parameters as variables etc, all are fine and I have never a
ny
> block... How is it possible? Is there any idea?
> Thanks in advance..
>|||I mean that stored procedure never ends, so it is blocking other users from
using the same tables etc (the sp updates data in some tables...)|||Christos,
If the sp never ends could be because another process is blocking the
resources needed by the sp and it is waiting, or the workload is heavy.
You can use EM (Management - Current Activity) or execute sp_who2 from QA to
see the processes and locks. You can also use Profiler to trace locks.
AMB
"Christos" wrote:

> I mean that stored procedure never ends, so it is blocking other users fro
m
> using the same tables etc (the sp updates data in some tables...)|||The strange in this situation is that when I have a block during the sp
execution, if I kill the process of sp and I try again to run it separetely
with the same parameters in the Q Analyzer's environment, the blocking
happens again. If I 'export' the source code in the analyzer's window and ru
n
it again using the parameters as variables it finishes imediatelly without
any problem...sql

A Database Trigger Calling an Oracle Procedure which in turn calls a Java functi

A Database Trigger Calling an Oracle Procedure which in turn calls a Java function
Hi,

I am having an error in A Database Trigger Calling an Oracle Procedure which in turn calls a Java function....any body can help.

JAVA FUNCTION:
import java.sql.*;
import java.io.*;

public class Insert{
public String putsData(String szApplData)throws SQLException{
{
System.out.println("Entering the function inside java");
Connection conn = DriverManager.getConnection("jdbc:default:connection:");

String szQry="INSERT INTO TESTUSER.TRN_APPL_REQUESTS@.MAINLINK(ID_REQUEST, ID_RESULT, ST_REQUEST, DT_INSERT,ID_APPLICATION,ID_FE,FLAG_GET,ID_FUNCTIO
N,NO_SEMCALL,SEQ_REQUEST) VALUES ("+"'"+szApplData.substring(0,1)+"','"+szApplData.substring(1,3)+"','000',SYSDATE,'"+(szApplData.substring(30,46)).trim()+"','"+(szApplData.substring(46,50)).trim()+"',"+"'"+ (szApplData.substring(53,54)).trim() +"'"+",'"+ (Double.valueOf(szApplData.substring(54,57))).doub leValue() +"'"+",'"+ (Double.valueOf(szApplData.substring(57,59))).doub leValue() +"'"+",SEQ_REQUEST.CURRVAL)";
try {
PreparedStatement pstmt = conn.prepareStatement(szQry);
pstmt.executeUpdate();
pstmt.close();
} catch (SQLException e) {System.err.println(e.getMessage());}

return szQry;

}
}
public String overrideInsert(String szApplData) {

String szOverride ="INSERT INTO TRN_OVERRIDE (SEQ_REQUEST, ID_FE,DT_OVERRIDE,USER_OVERRIDE,DESC_REASON,FLAG_A
CCEPTANCE,DT_ACCEPTANCE,SEQ_OVERRIDE ) VALUES ('"+szApplData.substring(4,14)+"','"+ szApplData.substring(63,67)+"',TO_DATE('"+szApplData.substring(67,75)+"','DD/MM/YYYY'),'"+ szApplData.substring(75,125)+"','"+ szApplData.substring(125,225)+"','"+ szApplData.substring(225,226)+"',TO_DATE('"+szApplData.substring(226,234)+"','DD/MM/YYYY'),SEQ_OVERRIDE.NEXTVAL)";

return szOverride ;
}
}

ORACLE PROCDURE WHICH CALLS THE JAVA:
create or replace procedure TESTINSERT1(szApplData in varchar2) as language java
name 'Insert.putsData(String)';

DATABASE TRIGGER:

1 create or replace trigger TRIG_REMOTE
2 AFTER INSERT ON TRN_APPL_REQUESTS
3 FOR EACH ROW
4 declare
5 szApplData varchar2(5000);
6 Result varchar2(5000);
7 my_sqlerrm VARCHAR2(150);
8 PRAGMA AUTONOMOUS_TRANSACTION;
9 Begin
10 dbms_output.put_line('Inside Begin');
11 szApplData = '123456789012345678901234567890abcdefghijklmn10FE1
F111111221234567891234567891234567891234';
12 dbms_output.put_line('After Assigning SZApplData');
13 call TESTINSERT1(szApplData);
14 dbms_output.put_line('After Select Statement :'||result);
15 commit;
16 exception when others then
17 my_sqlerrm := SUBSTR(SQLERRM,1,150);
18 dbms_output.put_line('Oracle Error Message :'||my_sqlerrm);
19* End;

ERROR ENCOUNTERED:
Errors for TRIGGER TRIG_REMOTE:

LINE/COL ERROR
--- --------------------
8/13 PLS-00103: Encountered the symbol "=" when expecting one of the
following:
:= . ( @. % ;
The symbol ":= was inserted before "=" to continue.

10/7 PLS-00103: Encountered the symbol "TESTINSERT1" when expecting
one of the following:
:= . ( @. % ;
The symbol ":=" was substituted for "TESTINSERT1" to continue.Hello,

it must be
szApplData := '123456789012345678901234567890abcdefghijklmn10FE1

in line 11

Hope this helps.

Greetings

Manfred Peter
(Alligator Company)
http://www.alligatorsql.com|||Originally posted by alligatorsql.com
Hello,

it must be
szApplData := '123456789012345678901234567890abcdefghijklmn10FE1

in line 11

Hope this helps.

Greetings

Manfred Peter
(Alligator Company)
http://www.alligatorsql.com

Hello ,

Thanks for the Reply..It was rectified..and Trigger created successfully.
when i try to insert a record for testing purpose..I am getting as below
iN THE DB TRIGGER I have given messages and when executing the procedure it is failing ...need help Pls ...Thanks

SQL> @.insert
Inside Begin
After Assigning SZApplData
Oracle Error Message :ORA-29531: no method putsData in class Insert

1 row created.
:)|||Hello,

could it be, that the problem is the prototype
public String putsData(String szApplData)throws SQLException{

You define a return value, but you use a procedure for the java function, which can not send back variables

create or replace procedure TESTINSERT1(szApplData in varchar2) as language java name 'Insert.putsData(String)';

In my opionion, Oracle is looking for a method

void Insert.putsData(String)

Is that your problem ?

Greetings

Manfred Peter
(Alligator Company)
http://www.alligatorsql.com|||It is working fine now ...I have not changed anything in database Trigger but changed the procedure and java function as below.

Two reasons for not working:
(1)The way we call the java function thro oracle procedure.
(2)In the Java parameter declaration change from vector to String

The Corrected Procedure Calling Java:

OLD:

create or replace procedure TESTINSERT1(szApplData varchar2) as language java
name 'Insert.putsData(String)';

NEW:
create or replace procedure TESTINSERT1(szApplData varchar2) as language java
name 'Insert.putsData(java.lang.String)';


Thanks for the Quick Responses from MANFRED PETER and BRICKLEN thro db forum for looking it in other angle in finding the solution.|||You are welcome

Manfred Peter
(Alligator Company)
http://www.alligatorsql.com

A cursor with the name 'TESTING' does not exist.

Hi,
I have created a simple stored procedure and I am getting some errors in it.
I couldn't figure out why the error is.
Any help would be appreciated.
Here is my sp:
---
CREATE PROCEDURE dbo.test
(
@.ID int,
@.NUMBERS nvarchar(2000)
)
AS
DECLARE @.REF int
EXEC('DECLARE TESTING CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT REF FROM TABLE1 WHERE ID IN (SELECT DISTINCT ID
FROM TABLE2
WHERE (DS_ID IN (8))
AND (TABLE2.NUMBERS IN (SELECT CONVERT(nvarchar(20),VALUE) COLLATE
SQL_Latin1_General_CP1_CI_AS FROM fn_Split('''+@.NUMBERS+''','+''',''' + ')))
AND (REF <= 0) AND (S_ID ='+ @.ID + '))
GROUP BY REF')
OPEN TESTING
FETCH NEXT FROM TESTING
INTO @.REF
WHILE @.@.FETCH_STATUS = 0
BEGIN
--doing something
FETCH NEXT FROM TESTING INTO @.REF
END
CLOSE TESTING
DEALLOCATE TESTING
---
When I try to run this, the errors I get
Server: Msg 16916, Level 16, State 1, Procedure test, Line 21
A cursor with the name 'TESTING' does not exist.
Server: Msg 16916, Level 16, State 1, Procedure test, Line 22
A cursor with the name 'TESTING' does not exist.
Server: Msg 16916, Level 16, State 1, Procedure test, Line 33
A cursor with the name 'TESTING' does not exist.
Server: Msg 16916, Level 16, State 1, Procedure test, Line 34
A cursor with the name 'TESTING' does not exist.
Thanks
KiranKiran,
The rest of the Cursor code need to be within the EXEC statement.
Gopi
"Kiran" <kiran_nospam@.gmail.com> wrote in message
news:O0WJF0eWFHA.2540@.tk2msftngp13.phx.gbl...
> Hi,
> I have created a simple stored procedure and I am getting some errors in
> it.
> I couldn't figure out why the error is.
> Any help would be appreciated.
> Here is my sp:
> ---
> CREATE PROCEDURE dbo.test
> (
> @.ID int,
> @.NUMBERS nvarchar(2000)
> )
> AS
>
> DECLARE @.REF int
>
>
> EXEC('DECLARE TESTING CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
> SELECT REF FROM TABLE1 WHERE ID IN (SELECT DISTINCT ID
> FROM TABLE2
> WHERE (DS_ID IN (8))
> AND (TABLE2.NUMBERS IN (SELECT CONVERT(nvarchar(20),VALUE) COLLATE
> SQL_Latin1_General_CP1_CI_AS FROM fn_Split('''+@.NUMBERS+''','+''',''' +
> ')))
> AND (REF <= 0) AND (S_ID ='+ @.ID + '))
> GROUP BY REF')
> OPEN TESTING
> FETCH NEXT FROM TESTING
> INTO @.REF
>
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> --doing something
> FETCH NEXT FROM TESTING INTO @.REF
> END
> CLOSE TESTING
> DEALLOCATE TESTING
> ---
> When I try to run this, the errors I get
> Server: Msg 16916, Level 16, State 1, Procedure test, Line 21
> A cursor with the name 'TESTING' does not exist.
> Server: Msg 16916, Level 16, State 1, Procedure test, Line 22
> A cursor with the name 'TESTING' does not exist.
> Server: Msg 16916, Level 16, State 1, Procedure test, Line 33
> A cursor with the name 'TESTING' does not exist.
> Server: Msg 16916, Level 16, State 1, Procedure test, Line 34
> A cursor with the name 'TESTING' does not exist.
> Thanks
> Kiran
>|||Don't declare it as a local cursor, that makes it be local to the EXEC. And,
consider if you can do
without the cursor in the first place, tend to be code easier to read and pe
rform better.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kiran" <kiran_nospam@.gmail.com> wrote in message news:O0WJF0eWFHA.2540@.tk2msftngp13.phx.gb
l...
> Hi,
> I have created a simple stored procedure and I am getting some errors in i
t.
> I couldn't figure out why the error is.
> Any help would be appreciated.
> Here is my sp:
> ---
> CREATE PROCEDURE dbo.test
> (
> @.ID int,
> @.NUMBERS nvarchar(2000)
> )
> AS
>
> DECLARE @.REF int
>
>
> EXEC('DECLARE TESTING CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
> SELECT REF FROM TABLE1 WHERE ID IN (SELECT DISTINCT ID
> FROM TABLE2
> WHERE (DS_ID IN (8))
> AND (TABLE2.NUMBERS IN (SELECT CONVERT(nvarchar(20),VALUE) COLLATE
> SQL_Latin1_General_CP1_CI_AS FROM fn_Split('''+@.NUMBERS+''','+''',''' + ')
))
> AND (REF <= 0) AND (S_ID ='+ @.ID + '))
> GROUP BY REF')
> OPEN TESTING
> FETCH NEXT FROM TESTING
> INTO @.REF
>
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> --doing something
> FETCH NEXT FROM TESTING INTO @.REF
> END
> CLOSE TESTING
> DEALLOCATE TESTING
> ---
> When I try to run this, the errors I get
> Server: Msg 16916, Level 16, State 1, Procedure test, Line 21
> A cursor with the name 'TESTING' does not exist.
> Server: Msg 16916, Level 16, State 1, Procedure test, Line 22
> A cursor with the name 'TESTING' does not exist.
> Server: Msg 16916, Level 16, State 1, Procedure test, Line 33
> A cursor with the name 'TESTING' does not exist.
> Server: Msg 16916, Level 16, State 1, Procedure test, Line 34
> A cursor with the name 'TESTING' does not exist.
> Thanks
> Kiran
>|||Thanks a ton Tibor.
Kiran kumar
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ed39iCfWFHA.616@.TK2MSFTNGP12.phx.gbl...
> Don't declare it as a local cursor, that makes it be local to the EXEC.
> And, consider if you can do without the cursor in the first place, tend to
> be code easier to read and perform better.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Kiran" <kiran_nospam@.gmail.com> wrote in message
> news:O0WJF0eWFHA.2540@.tk2msftngp13.phx.gbl...
>

A Clarification required on xp_terminate_process

HI ,

I have a problem where i have to kill a windows process in my procedure. In sql server 2000 i could find a procedure called xp_terminate_process ( which takes process id as parmeter and kills the process) . but i couldnt find any replacement or another new one to do the same in sql server 2005. if any body have any idea or suggestion regarding how to go about this are welcome..

Thanks in advance

Mohan

I just can't imagine task required Windows process killing on database server. Could you please tell us?|||

it will be some thing like ,

i have created a package and scheduled it to run , then when it executes it will create a dtshost.exe process.i ll capturing this process id and store it . and i have some data where i can know if any job exceeds its time of excution (for eg: schduled for 10 minutes but running for 20 minutes). we can do this scheduling by using sqlserver agent. but i ll be using some other mechanism where i can control the concurrency of executions. there i ll have procedure whihch will run periodically and checks that if any job is timedout , then get the process id of that job and kill the process.

This is what my requirement is ...

thanks and Regards

Krishna Kishore

|||Maybe it would be enough just to kill sql server connection made by dtshost.exe ( using simple KILL command ) ?|||

i dont think there will be n number of connections to the n number of servers in my package ,i can not determine which connection i need to terminate. so i think kill is not sifficient. by using xp_terminate_process you can kill the entire process...

but it s not available in the sql server 2005 ? what should i do any alternate for this...

Thanks in advance

krishna Kishore

|||

Hi Krishna,

Have u used xp_terminate_procces in SQL Server 2000?, as i m using it but it doesnt seems to terminate the process, not even showing any error. I m using the following query:

exec master..xp_terminate_process <pid>

When i execute, it just says the command completed successfully....

but the process is no killed :(

Plz reply me, if u have any idea whats wrong with the query or if i m not using it correctly..

Thanks and Regards,

|||

Hi ,

you have not given your requirement clearly? But i XP_terminate_process can kill any kind of process, have you checked whether your are referring to correct pid or not?

if you still feel it's not working use xp_cmdshell to kill the proces by using a command argument

the command is taskkill

taskkill /f /pid 1234 where pid is 1234

Thanks

Krishna

|||

I m using xp_terminate_process with a correct processid, but the command is not terminating the process.(I know that its an undocumented procedure, so its not guaranteed to work). I just wanted to know if u have used xp_terminate_process successfully?

I have sqlserver 2000 with sp4. I m using the following statement.

exec master..xp_terminate_process 3254

(Here 3254 is the process id for cmd.exe, i wish to terminate)

I know about cmdshell and kill/taskkill but i dont want to use cmdshell for some reason.

Thanks for ur reply.

A Clarification required on xp_terminate_process

HI ,

I have a problem where i have to kill a windows process in my procedure. In sql server 2000 i could find a procedure called xp_terminate_process ( which takes process id as parmeter and kills the process) . but i couldnt find any replacement or another new one to do the same in sql server 2005. if any body have any idea or suggestion regarding how to go about this are welcome..

Thanks in advance

Mohan

I just can't imagine task required Windows process killing on database server. Could you please tell us?|||

it will be some thing like ,

i have created a package and scheduled it to run , then when it executes it will create a dtshost.exe process.i ll capturing this process id and store it . and i have some data where i can know if any job exceeds its time of excution (for eg: schduled for 10 minutes but running for 20 minutes). we can do this scheduling by using sqlserver agent. but i ll be using some other mechanism where i can control the concurrency of executions. there i ll have procedure whihch will run periodically and checks that if any job is timedout , then get the process id of that job and kill the process.

This is what my requirement is ...

thanks and Regards

Krishna Kishore

|||Maybe it would be enough just to kill sql server connection made by dtshost.exe ( using simple KILL command ) ?|||

i dont think there will be n number of connections to the n number of servers in my package ,i can not determine which connection i need to terminate. so i think kill is not sifficient. by using xp_terminate_process you can kill the entire process...

but it s not available in the sql server 2005 ? what should i do any alternate for this...

Thanks in advance

krishna Kishore

|||

Hi Krishna,

Have u used xp_terminate_procces in SQL Server 2000?, as i m using it but it doesnt seems to terminate the process, not even showing any error. I m using the following query:

exec master..xp_terminate_process <pid>

When i execute, it just says the command completed successfully....

but the process is no killed :(

Plz reply me, if u have any idea whats wrong with the query or if i m not using it correctly..

Thanks and Regards,

|||

Hi ,

you have not given your requirement clearly? But i XP_terminate_process can kill any kind of process, have you checked whether your are referring to correct pid or not?

if you still feel it's not working use xp_cmdshell to kill the proces by using a command argument

the command is taskkill

taskkill /f /pid 1234 where pid is 1234

Thanks

Krishna

|||

I m using xp_terminate_process with a correct processid, but the command is not terminating the process.(I know that its an undocumented procedure, so its not guaranteed to work). I just wanted to know if u have used xp_terminate_process successfully?

I have sqlserver 2000 with sp4. I m using the following statement.

exec master..xp_terminate_process 3254

(Here 3254 is the process id for cmd.exe, i wish to terminate)

I know about cmdshell and kill/taskkill but i dont want to use cmdshell for some reason.

Thanks for ur reply.

a big question for procedure

Dear all
i had one question would like to ask someone who can
give me a litter help
if my database data like it

traceno otherno username

0913377594 0913377594 roger
0913787170 0913787170 roger
0915534569 0915534569 roger
0925306029 0925306029 roger
0930443931 0930443931 roger
0936565187 0936565187 roger

drop procedure delete_phone_group
create procedure delete_phone_group
@.param1 varchar(30),
@.username varchar(5) with encryption
as
declare @.var varchar(30) , @.varotherno varchar(30)
select @.var = otherno
from phonegroup
where traceno = @.param1
and traceno != otherno
delete from phonegroup where traceno = otherno and traceno = (select otherno
from phonegroup
where traceno = @.param1
and traceno != otherno)
select @.varotherno = otherno
from phonegroup
where traceno = @.var
and traceno != otherno
print @.varotherno
insert into phonegroup(traceno,otherno,username)
values(@.param1,@.varotherno,@.username)
update phonegroup set phonecount = 1 where traceno = @.var and otherno = @.varotherno

when problem is when i edit one record in (phonegroup) this table
the script like it
insert into phonegroup(traceno,otherno,username)
valuse('0915534569' ,0913787170,'roger')
the data will be like this 0913787170 to be delete but when i try edit second data
insert into phonegroup(traceno,otherno,username)
valuse('0915534569' ,0936565187,'roger')

0913377594 0913377594 roger
0915534569 0913787170 roger
0915534569 0915534569 roger
0915534569 0936565187 roger
0925306029 0925306029 roger
0930443931 0930443931 roger
0936565187 0936565187 roger

but i donot why the second data it does not to be delete in my script

Have you done a DELETE statement. All I can see are inserts?|||yes i do i have donesql

70-229 Microsoft example

I am studying for the 70--229 microsoft exam using the MS study guide. An example of a stored procedure used to add customer and check for dupes is below. However although the dupelicatecheck sp is called duplicate contact's can be added. Can anyone give me advise on where I may have gone wrong with this example.

CREATE procedure dbo.addcustomer
@.firstname varchar(30)='Unknown',
@.lastname varchar(30)='Unknown', @.Phone varchar(24)= NULL,
@.Address1 varchar(60)= NULL,
@.address2 varchar(60)='unknown', @.city varchar(15)=Null,
@.state varchar(7)=Null, @.zip varchar(12)=NULL
as
IF (@.Firstname='Unknown') and (@.Lastname='Unknown')
return(1)

Else IF @.phone is null
return(2)
Else IF
@.Address1 is null or @.city is null or
@.state is null or @.zip is null
Return(3)
--begin nesting
declare @.r_code int, @.v_firstname varchar(30),
@.v_lastname varchar(30), @.v_city varchar(15),
@.v_state varchar(7), @.v_phone varchar(24)
execute @.r_code=dbo.checkforduplicatecustomer
@.1_firstname=@.firstname, @.1_lastname=@.lastname,
@.1_city=city, @.1_state=@.state, @.1_phone=@.phone,
@.o_firstname=@.v_firstname output,
@.o_lastname=@.v_lastname output, @.o_city=@.v_city output,
@.o_state=@.v_state output, @.o_phone=@.v_phone output
if @.@.rowcount>0
begin
Print'A duplicate record was found for' + @.v_firstname+' ' +
@.v_lastname
print 'in' +@.v_city + ' '+ @.v_state + ' with a phone number'
print 'of' + @.v_phone +','
return(5)
end
--end nesting
insert [bookshopdb].[dbo].[customers]
(firstname, lastname, phone,
address1, address2, city, state, zip)
values
(@.firstname, @.lastname, @.phone,
@.address1, @.address2, @.city, @.state, @.zip)
Return(select @.@.identity as 'identity')
if @.@.error <>0
return (4)

create procedure dbo.checkforduplicatecustomer
@.1_firstname varchar(30)='Unknown',
@.1_Lastname varchar(30)='Unknown',
@.1_City varchar (15)= Null, @.1_state varchar(7)= null,
@.1_phone varchar(24)= null, @.o_firstname varchar (30) output,
@.o_lastname varchar(30) output, @.o_city varchar (15) output,
@.o_state varchar(7) output, @.o_phone varchar(24) output
as
select @.o_firstname=firstname, @.o_lastname=lastname,
@.o_city=city, @.o_state=state, @.o_phone=phone
from customers
where firstname=@.1_firstname and lastname=@.1_lastname
and city=@.1_city and state=@.1_state and phone=@.1_phone
if @.@.rowcount <> 0
return (5)You assign the output of checkforduplicatecustomer to variable @.r_code, but your main procedure checks that value of if @.@.rowcount to determine whether to generate a duplicate record error. Shouldn't you be checking @.r_code instead?

Also, you would be better off wrting your checkforduplicatecustomer procedure as a user-defined function. They are much easier to use in code, like this:
if dbo.checkforduplicatecustomer(Parameters...) = 1 begin....

And why don't you just check for duplicates in your main code using IF EXISTS? That would be the simplest way, unless you really need to reuse the duplicate checking logic in other procedures as well.

blindman

7/11 Windows update broke my stored procedure

I have a managed code DLL that is used as a CLR assembly in my database. A stored procedure in the database references this CLR assembly.
My CLR assembly has a dependency on Microsoft's System.Management.dll.
In order to get my assembly to work, I added System.Management.dll as an assembly to the database using:

CREATE ASSEMBLY SystemManagement
FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Management.dll'
WITH PERMISSION_SET = UNSAFE
GO

For months this stored procedure has worked fine... up until today.

Today (7/11/07) I noticed a new Microsoft update was available.
I applied the patch to an XP machine that has my database and now my CLR assembly stored procedure no longer works.
I checked a 2003 Server machine that did not have the latest patch and see that the stored procedure is working fine.
I then apply Microsoft update to the 2003 server machine.
Now the stored procedure no longer works on the 2003 machine.

I deleted my stored procedure and CLR assembly and then added them back with no success.
I then deleted and added back the System.Management.dll assembly again and suddenly now my stored procedure works again.

This is BAD. I can't have Windows updates blowing up my app.
What am I doing wrong here?
I must use the System.Management.dll in my CLR stored procedure but I can't have changes to Microsoft's files causing me to be unable to reference them as assemblies.

How can my CLR stored procedure assembly reference a Microsoft assembly such that changes to the Microsoft assembly does not cause version mismatches?

Hmm, that's weird. In what way does you proc no longer work?

The reason I think it is weird is that as the dll in question is loaded from the database, having a new dll should not really matter, unless the patch also changes something else that the dll calls into and therefore fails.

So, once again - in what way does your proc fail?

Niels

|||

Unfortunately I do not remember the exact phraseology of the exception (the problem has been fixed on my development machine) but it did mention that the assembly it was looking for did not match the version found in the GAC. I'm certain it mentioned the GAC.

I will include as much detail as I can below.

I have a web app that makes calls to the stored procedure like this:

Code Snippet

try
{
using (SqlConnection sqlConnection = new SqlConnection(strConnectionString))
{
sqlCommand = new SqlCommand("usp_MyCLRStoredProcedure", sqlConnection);

sqlConnection.Open();

sqlCommand.CommandType = CommandType.StoredProcedure;

sqlParameter = new SqlParameter("@.iRetVal", SqlDbType.Int);
sqlParameter.Direction = ParameterDirection.ReturnValue;
sqlCommand.Parameters.Add(sqlParameter);

sqlParameter = new SqlParameter("@.iVersion", SqlDbType.Int);
sqlParameter.Value = (int)eVersion;
sqlCommand.Parameters.Add(sqlParameter);

// After applying the 7/11 Windows update, execution of
// the next line results in an exception being thrown
sqlCommand.ExecuteNonQuery();

// Code after this point never executes due to exception being thrown...

}
}
catch (Exception e)
{
Console.WriteLine("Error while attempting to access database:\n" + e.ToString());
}

The CLR stored procedure is a SQL Server Project written in Visual Studio and compiled into a DLL. This C# code fetches the CPU ID of the machine, and does some cryptography work. My CLR stored procedure is similar to the following code block (I've tried to omit non-relevant parts):

Code Snippet

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Globalization;
using System.Management;
using System.IO;
using System.Security.Cryptography;

public partial class StoredProcedures
{

// This is the entry point of the stored procedure
[Microsoft.SqlServer.Server.SqlProcedure]
public static int MyStoredProcedure(int iVersion)
{
// ... code ...

// calls are made to Environment.MachineName

// ... more code ...

// get the processor id
ManagementClass managementClass = new ManagementClass( "Win32_Processor" );
ManagementObjectCollection mocInstances = managementClass.GetInstances();

// ... more code ...
// Calls are made to System.Security.Cryptography.HMACSHA1
// Calls are made to System.Text.Encoding
// Calls are made to System.Convert
// ... more code ...

// Call ExecuteNonQuery() on a regular (T-SQL based) stored procedure

// ... more code ...
}
}

To hook the CLR DLL code above with a stored procedure that is accessible by SQL Server, I performed the following from within Microsoft SQL Server Management Studio:

Code Snippet

USE MyDatabase
GO

-- SQL server needs to know if a database is "trustworthy" before it
-- will allow it to load any extra assemblies
ALTER DATABASE MyDatabase SET trustworthy ON
GO

-- To add a reference to a "SQL Server Project" within Visual Studio
-- you must first add the dll you want to reference to the database itself
-- The following code block places "System.Management.dll" and all of
-- the assemblies it depends on in the database.
-- After completing this step, you should be able to use the "Add Reference"
-- feature of Visual Studio to add the "SystemManagement" class as a
-- reference in your SQL Server Project.
CREATE ASSEMBLY SystemManagement
FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Management.dll'
WITH PERMISSION_SET = UNSAFE
GO

-- The "compatibility level" of the Database needs to be set to 90
-- before it has the ability to recognize CLR calls
exec sp_dbcmptlevel 'MyDatabase', 90
GO

-- SQL Server 2005 won't recognize CLR calls unless you manually enable it.
-- To enable the CLR on SQL Server 2005, execute the code block below
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

-- Load a CLR DLL as an assembly onto the database
CREATE ASSEMBLY MyAssembly
FROM 'C:\Devel\MyProject\bin\Debug\MyCLRStoredProcedure.dll'
WITH PERMISSION_SET = UNSAFE
GO

-- The following code block demonstrates how to create and associate
-- a stored procedure with a method in a CLR assembly.
-- Of course, the CLR assembly must already be registered
-- with the database (as shown above) before this is attempted.
-- The "EXTERNAL NAME" clause takes the following parameter
-- [Assembly name (as shown in MSSQL Server Management Studio)].ClassName.MethodName
CREATE PROCEDURE usp_MyCLRStoredProcedure
@.iVersion int
AS EXTERNAL NAME MyAssembly.StoredProcedures.MyStoredProcedure
GO

-- Give execute priveleges to the newly added stored procedure
GRANT EXECUTE ON usp_MyCLRStoredProcedure TO MyWebAppUser
GO

-- To debug the CLR stored procedure, the PDB file
-- must be added to the assembly.
-- 1) The MyCLRStoredProcedure.PDB file must
-- be installed in the database; execute the
-- T-SQL code block below.
-- 2) In the Visual Studio IDE, select
-- Debug -> Attach to Process
-- 3) Select "sqlserver.exe" from the Available Processes
-- list box and click the "Attach" button.
-- 4) Breakpoints should now be functional in
-- the MyCLRStoredProcedure source code.
ALTER ASSEMBLY MyAssembly
ADD FILE FROM 'C:\Devel\MyProject\bin\Debug\MyCLRStoredProcedure.pdb'
GO

At this point, everything is wired up and ready to go.

Prior to the 7/11 Windows update, a user of the web app could initiate a call to "usp_MyCLRStoredProcedure" and everything would work fine.
After applying the 7/11 Windows update, an exception now occurs somewhere during the call to usp_MyCLRStoredProcedure.

After deleting the stored procedure "usp_MyCLRStoredProcedure" and the assemblies "MyAssembly" and "SystemManagement" from the database and then re-running the SQL code block shown above, everything worked normally again.

I have one machine that I haven't fixed yet but it does not have any sort of development environment. I can try to get a development environment set up on the machine or instrument the web app to log the exception text but in the meantime if anyone has any clues or sees something I am doing wrong in the code above, please let me know.

Thank you.

|||it would be definitely easier to help you with the exact error message that is thrown.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

64bit Proc Cache limit?

I understand that there is a 2Gb limit to SQL Server's procedure cache,
although I have found no official MS documentation that says so...
My question is whether the 64bit version suffers the same limit? Any input
and especially documentation would be much appreciated.
There are no such restrictions in 64bit. Virtually all of the available
memory for SQL Server is dynamic.
Andrew J. Kelly SQL MVP
"Don Peterson" <no1@.nunya.com> wrote in message
news:#Z6TidVKEHA.3580@.TK2MSFTNGP10.phx.gbl...
> I understand that there is a 2Gb limit to SQL Server's procedure cache,
> although I have found no official MS documentation that says so...
> My question is whether the 64bit version suffers the same limit? Any
input
> and especially documentation would be much appreciated.
>

64bit Proc Cache limit?

I understand that there is a 2Gb limit to SQL Server's procedure cache,
although I have found no official MS documentation that says so...
My question is whether the 64bit version suffers the same limit? Any input
and especially documentation would be much appreciated.There are no such restrictions in 64bit. Virtually all of the available
memory for SQL Server is dynamic.
--
Andrew J. Kelly SQL MVP
"Don Peterson" <no1@.nunya.com> wrote in message
news:#Z6TidVKEHA.3580@.TK2MSFTNGP10.phx.gbl...
> I understand that there is a 2Gb limit to SQL Server's procedure cache,
> although I have found no official MS documentation that says so...
> My question is whether the 64bit version suffers the same limit? Any
input
> and especially documentation would be much appreciated.
>

64bit Proc Cache limit?

I understand that there is a 2Gb limit to SQL Server's procedure cache,
although I have found no official MS documentation that says so...
My question is whether the 64bit version suffers the same limit? Any input
and especially documentation would be much appreciated.There are no such restrictions in 64bit. Virtually all of the available
memory for SQL Server is dynamic.
Andrew J. Kelly SQL MVP
"Don Peterson" <no1@.nunya.com> wrote in message
news:#Z6TidVKEHA.3580@.TK2MSFTNGP10.phx.gbl...
> I understand that there is a 2Gb limit to SQL Server's procedure cache,
> although I have found no official MS documentation that says so...
> My question is whether the 64bit version suffers the same limit? Any
input
> and especially documentation would be much appreciated.
>

501 when connect to Endpoint

I Created SP and endpoint that exposes SP as web service :

CREATE PROCEDURE TimeServer.ResponseTime2
(
@.TimeType int,
)
AS
IF (@.TimeType = 0 )
SELECT @.Result AS CZAS
ELSE
SELECT @.Result AS CZAS
GO
CREATE ENDPOINT MyWebService
STATE = STARTED
AS HTTP
(
PATH = '/AdventureWorks/MyWebService',
AUTHENTICATION = (INTEGRATED ),
PORTS = ( CLEAR ),
SITE = 'localhost'
)
FOR SOAP
(
WEBMETHOD 'GetTime' (Name = 'AdventureWorks.TimeServer.ResponseTime2', FORMAT = ROWSETS_ONLY),
DATABASE = 'AdventureWorks'
)

GO

When I try to connect to webservice (localhost/AdventureWorks/MyWebServic
I get 501 error - Not implemented or not supported.

Any ideas why ?

I don't know if this answers your question. However, we support HTTP GET requests ONLY for requesting WSDL. In this case if you submitted the following request

http://localhost/AdventureWorks/MyWebService?wsdl

it should return WSDL describing the endpoint.

The rest of the SOAP requests have to be submitted via HTTP POST.

Thanks
Srik

|||

I was also getting this at one point. Try going into IE browser->Tools->Internet Options->Advanced and enable "Use HTTP 1.1 through proxy connections" if this is not set (may be related to proxy settings associated wih IE Browser->Connections->LAN Settings)

501 when connect to Endpoint

I Created SP and endpoint that exposes SP as web service :

CREATE PROCEDURE TimeServer.ResponseTime2
(
@.TimeType int,
)
AS
IF (@.TimeType = 0 )
SELECT @.Result AS CZAS
ELSE
SELECT @.Result AS CZAS
GO
CREATE ENDPOINT MyWebService
STATE = STARTED
AS HTTP
(
PATH = '/AdventureWorks/MyWebService',
AUTHENTICATION = (INTEGRATED ),
PORTS = ( CLEAR ),
SITE = 'localhost'
)
FOR SOAP
(
WEBMETHOD 'GetTime' (Name = 'AdventureWorks.TimeServer.ResponseTime2', FORMAT = ROWSETS_ONLY),
DATABASE = 'AdventureWorks'
)

GO

When I try to connect to webservice (localhost/AdventureWorks/MyWebServic
I get 501 error - Not implemented or not supported.

Any ideas why ?

I don't know if this answers your question. However, we support HTTP GET requests ONLY for requesting WSDL. In this case if you submitted the following request

http://localhost/AdventureWorks/MyWebService?wsdl

it should return WSDL describing the endpoint.

The rest of the SOAP requests have to be submitted via HTTP POST.

Thanks
Srik

|||

I was also getting this at one point. Try going into IE browser->Tools->Internet Options->Advanced and enable "Use HTTP 1.1 through proxy connections" if this is not set (may be related to proxy settings associated wih IE Browser->Connections->LAN Settings)

3rd Party Performance Tool/Software

Is there any software that can analyze stored procedure performance and
recommend solutions?
Thanks,
Dave
How about the database engine tuning advisor, which ships with the product?
You can send it a workload consisting of a single stored procedure call, but
for a better analysis and set of recommended enhancements, you're better off
providing a workload of a typical business day.
Note that this tool will recommend structural changes like adding/removing
indexes, partitioning, etc. It will not look at your SELECT query with 18
unions and suggest a single FROM with a CASE expression. :-)
A
"David" <nospam@.home.com> wrote in message
news:216B98B9-8D9B-4143-A12C-810B24CD2723@.microsoft.com...
> Is there any software that can analyze stored procedure performance and
> recommend solutions?
> Thanks,
> Dave
|||As far as I am aware, there is no tool that would take a stored procedure,
optimize and refactor it, and recommend alternative solutions.
The only 'tool' that can do that is a skilled DBA!!
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"David" <nospam@.home.com> wrote in message
news:216B98B9-8D9B-4143-A12C-810B24CD2723@.microsoft.com...
> Is there any software that can analyze stored procedure performance and
> recommend solutions?
> Thanks,
> Dave
|||InDepth for SQL Server has such a feature which it implements by levaraging
either ITW or DTA depending on SQL version. It uses performance data stored
in its Performance Warehouse to feed the recommendations. It also clearly
identifies the statements and batches that are consuming most resource to
help guide your tuning efforts. To be honest we mostly use it for the latter
as it's extremely light weight and much less resource intensive than a trace
plus we don't want ITW/DTA run against a production database. However by
identifying the main resource consumers these can be tuned and measured in
the QA envionment to produce recommendations for production.
http://www.symantec.com/enterprise/products/overview.jsp?pcid=1021&pvid=317_1
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"David" <nospam@.home.com> wrote in message
news:216B98B9-8D9B-4143-A12C-810B24CD2723@.microsoft.com...
> Is there any software that can analyze stored procedure performance and
> recommend solutions?
> Thanks,
> Dave