"Stored Procedure" working every 5 minute, how?

hi
i working on SQL2000, and want to make "Stored Procedure" that's working
every 5 mibute, without making any calling.
how i can make that ?
--
Best Regards
Tark M. Siala
Development Manager
INTERNATIONAL COMPUTER CENTER (ICC.Networking)
Mobile: +218-91-3125900
E-Mail: tarksiala@.icc-libya.com
Messenger: tarksiala@.hotmail.com
Web Page: http://www.icc-libya.com
Blog: http://spaces.msn.com/tarksiala
======================================Hi Tark
You can set up a job to run the stored procedure on a 5-minute schedule.
Look up How To Create a Job.
(I also suggest you don't include quite so many newsgroups in your list.
What does this question have to do with clustering anyway? You included the
msde group also, does that mean you are running on msde? If so, you should
say that.)
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Tark Siala" <tarksiala@.icc-libya.com> wrote in message
news:eH4GDSyXGHA.3936@.TK2MSFTNGP05.phx.gbl...
> hi
> i working on SQL2000, and want to make "Stored Procedure" that's working
> every 5 mibute, without making any calling.
> how i can make that ?
> --
> Best Regards
> Tark M. Siala
> Development Manager
> INTERNATIONAL COMPUTER CENTER (ICC.Networking)
> Mobile: +218-91-3125900
> E-Mail: tarksiala@.icc-libya.com
> Messenger: tarksiala@.hotmail.com
> Web Page: http://www.icc-libya.com
> Blog: http://spaces.msn.com/tarksiala
> ======================================
>
>

"Query Cost (relative to the batch)" in Query Analyzer

Hi,
I'm trying to troubleshoot a slow-running query which is part of a
stored procedure. When I run the query as it is - it takes 53 seconds
to run.
--
Example original query:
SELECT DISTINCT SP.SP_ID
FROM StandardProject SP
INNER JOIN Classes cls ON SP.Cls_ID = cls.Cls_ID
INNER JOIN Groups ON cls.Cls_ID = Groups.Cls_ID
INNER JOIN ClassesGroups ON ClassesGroups.groupid = Groups.groupid
WHERE ClassesGroups.StudentID = 7615
AND ((AccessReport & 2) > 0 OR (AccessReport & 8) > 0)
AND SP.ProjectType = 0
AND Groups.status = 1
AND exists (SELECT SP_ID FROM Schedules WHERE SP_ID = SP.SP_ID)
--
If I removed the most time-consuming inner join tables and set this
part to insert values needed by the rest of the query into a table
variable and ran both queries (INSERT INTO table variable and the
SELECT), the query finishes in 0 seconds.
I've used the "Query Cost (relative to the batch)" in Query Analyzer
counter to analyze the performance of queries in the past (this value
displays when Show Execution Plan is set). In this case I'm
because when I run the original SELECT query and the modified query
(INSERT INTO table variable and then SELECT) - it says the Query Cost
relative to the batch for the first query is 35%, the INSERT = 64%, the
SELECT = 1%.
Why does the first query COST only 35% when it takes the longest to
finish? (53 seconds as opposed to under 1 second)? Does this mean that
the first query will perform better under load?
--
According to SQL Server Performance.com:
"Sometimes, it is valuable to compare two more queries at the same
time, checking to see which one is the better performer. This is
especially true if you have a query that you have rewritten two or more
different ways, and you want to find out which variation of the query
is the most efficient.
While you can analyze each query, one at a time in Query Analyzer, one
trick you should consider trying is to place all of the queries you
want to compare in Query Analyzer, turn on "Show Execution Plan," then
run all the queries at once. When you do this, the "Execution Plan"
window will divide into a separate window for each query ran, allowing
you to compare their execution plans much easier. In addition, in each
execution plan window, you will see a "Query Cost (relative to the
batch)" number. What this number is telling you is how long each query
took as a percentage of the total amount of time it took all of the
queries to run. The query with the lowest number is the query that
performed the quickest, and so on.
As you can imagine, this information can make it much easier to compare
different queries."
--
So what else should I be taking into account? What am I missing here?
Thank you,
Smitha
SQL Server DBAsmithabreddy@.gmail.com,
When comparing two or more queries, it is fair to:
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
before each one is executed. (DO NOT DO THIS ON A PRODUCTION SERVER)
AMB
"smithabreddy@.gmail.com" wrote:

> Hi,
> I'm trying to troubleshoot a slow-running query which is part of a
> stored procedure. When I run the query as it is - it takes 53 seconds
> to run.
> --
> Example original query:
> SELECT DISTINCT SP.SP_ID
> FROM StandardProject SP
> INNER JOIN Classes cls ON SP.Cls_ID = cls.Cls_ID
> INNER JOIN Groups ON cls.Cls_ID = Groups.Cls_ID
> INNER JOIN ClassesGroups ON ClassesGroups.groupid = Groups.groupid
> WHERE ClassesGroups.StudentID = 7615
> AND ((AccessReport & 2) > 0 OR (AccessReport & 8) > 0)
> AND SP.ProjectType = 0
> AND Groups.status = 1
> AND exists (SELECT SP_ID FROM Schedules WHERE SP_ID = SP.SP_ID)
> --
> If I removed the most time-consuming inner join tables and set this
> part to insert values needed by the rest of the query into a table
> variable and ran both queries (INSERT INTO table variable and the
> SELECT), the query finishes in 0 seconds.
> I've used the "Query Cost (relative to the batch)" in Query Analyzer
> counter to analyze the performance of queries in the past (this value
> displays when Show Execution Plan is set). In this case I'm
> because when I run the original SELECT query and the modified query
> (INSERT INTO table variable and then SELECT) - it says the Query Cost
> relative to the batch for the first query is 35%, the INSERT = 64%, the
> SELECT = 1%.
> Why does the first query COST only 35% when it takes the longest to
> finish? (53 seconds as opposed to under 1 second)? Does this mean that
> the first query will perform better under load?
> --
> According to SQL Server Performance.com:
> "Sometimes, it is valuable to compare two more queries at the same
> time, checking to see which one is the better performer. This is
> especially true if you have a query that you have rewritten two or more
> different ways, and you want to find out which variation of the query
> is the most efficient.
> While you can analyze each query, one at a time in Query Analyzer, one
> trick you should consider trying is to place all of the queries you
> want to compare in Query Analyzer, turn on "Show Execution Plan," then
> run all the queries at once. When you do this, the "Execution Plan"
> window will divide into a separate window for each query ran, allowing
> you to compare their execution plans much easier. In addition, in each
> execution plan window, you will see a "Query Cost (relative to the
> batch)" number. What this number is telling you is how long each query
> took as a percentage of the total amount of time it took all of the
> queries to run. The query with the lowest number is the query that
> performed the quickest, and so on.
> As you can imagine, this information can make it much easier to compare
> different queries."
> --
> So what else should I be taking into account? What am I missing here?
> Thank you,
> Smitha
> SQL Server DBA
>|||I performed the compare after running both DBCC FREEPROCCACHE and DBCC
DROPCLEANBUFFERS.|||But did you run this between each query, or before the entire batch?
<smithabreddy@.gmail.com> wrote in message
news:1147268627.698501.322300@.v46g2000cwv.googlegroups.com...
> I performed the compare after running both DBCC FREEPROCCACHE and DBCC
> DROPCLEANBUFFERS.
>|||To capture relative cost information (compare the cost of both
approaches), I ran DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS first
and then ran both batches. This is where I get cost information for
the 53 seconds duration query = 34% and cost for the under 1 second
query = 76%.
I then ran DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS, ran the
original query. Took 1.05 minutes to run the first time and 54 seconds
to run the second time.
Ran DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS again, then ran the
modified query (with INSERT INTO table variable and then SELECT). Took
6 seconds to run the first time and 0 seconds to run the second time.
Basically -
I'd been relying on this measure (relative cost for each query in a
batch) somewhat to determine which query is more efficient. And when
both queries return values in under 1 second - I figured it would be
better to go with the lower cost option. But what happens in a
situation such as this where the lower cost query take SO much longer
to run?
I review the output from SET STATISTICS IO ON, Query execution time and
Execution plan to determine if a query has the most efficient
construct. This relative cost information seems counter-intuitive to
me...what rules of thumb should I use to guage query efficiency?|||This is puzzling, and I don't really know the answer, but off the top of my
head I can think of a possibility. This is more a guess than anything, so
take it as such.
I believe the cost considers IO, Memory, and CPU, among other things. I
don't think run time is necessarily as big an issue as the other resources.
It is possible that the faster query is using much more of one of these
resources, which results in the higher cost calculation, even though it
finishes in less time.
i.e. (these numbers are completely bogus, included only to illustrate the
idea):
Query1 uses a lot of IO (5,000) and CPU (5,000), but virtually no memory
(10), and finishes in 60 seconds.
Query2 uses minimal IO (10) and CPU (10) but a lot of memory (10,000).
Because it is using memory without the more time consuming IO or CPU usage,
everything is much faster.
Now, the example above is highly flawed and over simplified, but hopefully
it illustrates my point anyway.
The faster query may actually use more resources over all, and although it
runs faster in test, it may perform slower under peak usage (depending on
your available resources).
I have seen cases where a query would run in 30 seconds, but use 100 percent
of the CPU and make all other transactions grind to a halt, where a slight
change to the query would make it run in 60 seconds on 5 percent CPU and
everything else continued to run without issue.
I guess the point is that run time by itself is not always the best
indication of efficiency, and there is not a simple answer.
<smithabreddy@.gmail.com> wrote in message
news:1147280303.752842.58360@.v46g2000cwv.googlegroups.com...
> To capture relative cost information (compare the cost of both
> approaches), I ran DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS first
> and then ran both batches. This is where I get cost information for
> the 53 seconds duration query = 34% and cost for the under 1 second
> query = 76%.
> I then ran DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS, ran the
> original query. Took 1.05 minutes to run the first time and 54 seconds
> to run the second time.
> Ran DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS again, then ran the
> modified query (with INSERT INTO table variable and then SELECT). Took
> 6 seconds to run the first time and 0 seconds to run the second time.
> Basically -
> I'd been relying on this measure (relative cost for each query in a
> batch) somewhat to determine which query is more efficient. And when
> both queries return values in under 1 second - I figured it would be
> better to go with the lower cost option. But what happens in a
> situation such as this where the lower cost query take SO much longer
> to run?
> I review the output from SET STATISTICS IO ON, Query execution time and
> Execution plan to determine if a query has the most efficient
> construct. This relative cost information seems counter-intuitive to
> me...what rules of thumb should I use to guage query efficiency?
>|||Makes sense...although I've found quite often that something making
sense to me doesn't amount to a hill of beans when dealing with the
OPTIMIZER... :)
I will check this out. Thanks very much.|||Please let me know what you find. I am curious to see if my theory holds
any water.
<smithabreddy@.gmail.com> wrote in message
news:1147289299.238269.87340@.g10g2000cwb.googlegroups.com...
> Makes sense...although I've found quite often that something making
> sense to me doesn't amount to a hill of beans when dealing with the
> OPTIMIZER... :)
> I will check this out. Thanks very much.
>|||So the optimizer has estimated that the first query will use 34% of the
total elapsed time and the second query the remaining 76%, but in
reality the first query is taking 98% of the time and the second query
2%.
There could be a couple of things going on:
a) the table statistics might be out of date. This could easily explain
the incorrect relative cost, and cause the optimizer to choose a
suboptimal plan. When in doubt run UPDATE STATISTICS on all tables in
the query, preferably WITH FULLSCAN.
b) the data distribution of one the the table columns could be very
a-typical. If the optimizer has no usuable statistics for that column
and the column is used in the predicates, then this could result in a
poor query plan. This could be the case with column AccessReport when
evaluating the expression ((AccessReport & 2) > 0 OR (AccessReport & 8)
> 0). If you know how to read query plans, you might be able to determine if that is
the case. Also, the IO statistics would show a marked difference between the fast a
nd slow query with respect to that table.
c) maybe the original query runs into a bug/flaw in the optimizer. For
example the inappropriate use of parallellism (when in doubt, add OPTION
(MAXDOP 1) to the query).
By the way, you can rewrite
((AccessReport & 2) > 0 OR (AccessReport & 8) > 0)
as
AccessReport & (8+2) > 0
which might run a tiny bit faster.
Also, if the column SP.ProjectType or Groups.status is of data type bit,
then make sure you explicitely convert the literal to a bit. For
example, if Groups.status is a bit, then write
AND Groups.status = CAST(1 as bit)
And finally, if column StandardProject.SP_ID is unique, then you can
eliminate the DISTINCT keyword by replacing the joins that could cause
duplicates by pushing them to the EXISTS clause.
HTH,
Gert-Jan
smithabreddy@.gmail.com wrote:
> To capture relative cost information (compare the cost of both
> approaches), I ran DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS first
> and then ran both batches. This is where I get cost information for
> the 53 seconds duration query = 34% and cost for the under 1 second
> query = 76%.
> I then ran DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS, ran the
> original query. Took 1.05 minutes to run the first time and 54 seconds
> to run the second time.
> Ran DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS again, then ran the
> modified query (with INSERT INTO table variable and then SELECT). Took
> 6 seconds to run the first time and 0 seconds to run the second time.
> Basically -
> I'd been relying on this measure (relative cost for each query in a
> batch) somewhat to determine which query is more efficient. And when
> both queries return values in under 1 second - I figured it would be
> better to go with the lower cost option. But what happens in a
> situation such as this where the lower cost query take SO much longer
> to run?
> I review the output from SET STATISTICS IO ON, Query execution time and
> Execution plan to determine if a query has the most efficient
> construct. This relative cost information seems counter-intuitive to
> me...what rules of thumb should I use to guage query efficiency?|||a) the table statistics might be out of date. This could easily explain
the incorrect relative cost, and cause the optimizer to choose a
suboptimal plan. When in doubt run UPDATE STATISTICS on all tables in
the query, preferably WITH FULLSCAN.
--> No difference after updating stats with full scan for all tables
involved. Issued DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS before
executing the queries.
b) Wouldn't the stats below suggest that the table variable option is
more efficient?
Fast Query: IO Stats for Groups:
Table 'Groups'. Scan count 0, logical reads 111026, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Slow Query: IO Stats for Groups:
Table 'Groups'. Scan count 0, logical reads 27419652, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
When I looked at the costs associated with the various nodes in the
execution plan - the one cost that is consistently higher than the
others for the fast query is: Estimated Operator Cost which then causes
the Estimated Subtree Cost for that node to be higher. According to
SQL Server 2005 BOL:
Estimated Operator Cost = The cost to the query optimizer for executing
this operation. The cost of this operation as a percentage of the total
cost of the query is displayed in parentheses. Because the query engine
selects the most efficient operation to perform the query or execute
the statement, this value should be as low as possible.
Estimated Subtree Cost = The total cost to the query optimizer for
executing this operation and all operations preceding it in the same
subtree.
Does this mean that this is just an estimation or that this is a real
cost which will affect the SP each time it executes?
--
Thanks again.

"Phantom" Stored Procedures?

I have run into a problem where I cannot add a new stored
procedure to the master database because it supposedly
already exists, but I cannot see it in Enterprise
Manager, I cannot see it if I query the sysobjects table
directly for it, and I cannot drop it (I get a message
that nothing by that name exists in the catalog). I'd
like to know what is going on here with this catch-22
thing. It seems to be related to the fact that I moved
the master database over to this server from another one
using BACKUP/RESTORE. The originating server and the
destination server were named differently, and the master
database was moved from the C drive on the original
server to the D drive on the destination server when it
was restored.
I would be most grateful if someone could help me avoid a
rebuild of the master database, or worse, a re-install of
SQL Server altogether to solve this problem.
Mark> I have run into a problem where I cannot add a new stored
> procedure to the master database because it supposedly
> already exists, but I cannot see it in Enterprise
> Manager, I cannot see it if I query the sysobjects table
> directly for it, and I cannot drop it (I get a message
> that nothing by that name exists in the catalog).
Could you tell us the name of the stored procedure you are trying to add,
and why you are trying to add it to the master database?|||1) Are you using the sp_addextendedproc to add it?
2) Have you tried removing it first using sp_dropextendedproc?
3) and you're sure you are adding to master?
"Mark Schmidt" <Me@.spamthis.com> wrote in message
news:006d01c35127$e3ffc370$a101280a@.phx.gbl...
> >--Original Message--
> >> I have run into a problem where I cannot add a new
> stored
> >> procedure to the master database because it supposedly
> >> already exists, but I cannot see it in Enterprise
> >> Manager, I cannot see it if I query the sysobjects
> table
> >> directly for it, and I cannot drop it (I get a message
> >> that nothing by that name exists in the catalog).
> >
> >Could you tell us the name of the stored procedure you
> are trying to add,
> >and why you are trying to add it to the master database?
> >
> >
> >.
> >
> Well, this time it's actually an extended stored
> procedure that a consulting company gave me to effect
> some functionality in one of our applications that uses
> SQL Server. However, I had the same problem upgrading
> another of our servers to SP3a. That time it was system
> stored procedures that couldn't be dropped and re-added
> via the upgrade script because they were already there.
> Both scenarios involve the movement of the master
> database from an older server (which went out the door
> due to its lease expiration) to a newer one. I have
> noticed that seems to be the common denominator.

"Order by" by parameter in stored procedure

I would like to pass the name of a column to a stored procedure, so the
result of the query would be ordered by that column.
I tried this:
CREATE Procedure ProductsByTab
(
@.TabID int,
@.Order nvarchar (50)
)
AS
SELECT
*
FROM
Product
WHERE
TabID = @.TabID
ORDER BY
@.Order
GO
but I get this message:
Error 1008: The SELECT item identified by the ORDER BY number 1 contains a
variable as part of the expression identifying a column position. Variables
are only allowed when ordering by an expression referencing a column name.
Is it possible to do what I want? What am I doing wrong?
Thank you.i have the same thing but i use a different approach, i pass a flag
representing the numeric order of the field
select * from ttt
order by case @.flag when 1 then name when 2 then description else '' end,
case @.flag when 3 then amount else 0 end,name,description,amount
"Carlos Santos" wrote:

> I would like to pass the name of a column to a stored procedure, so the
> result of the query would be ordered by that column.
> I tried this:
> CREATE Procedure ProductsByTab
> (
> @.TabID int,
> @.Order nvarchar (50)
> )
> AS
> SELECT
> *
> FROM
> Product
> WHERE
> TabID = @.TabID
> ORDER BY
> @.Order
> GO
> but I get this message:
> Error 1008: The SELECT item identified by the ORDER BY number 1 contains a
> variable as part of the expression identifying a column position. Variable
s
> are only allowed when ordering by an expression referencing a column name.
> Is it possible to do what I want? What am I doing wrong?
> Thank you.|||How do I use a variable in an ORDER BY clause?
http://www.aspfaq.com/show.asp?id=2501
AMB
"Carlos Santos" wrote:

> I would like to pass the name of a column to a stored procedure, so the
> result of the query would be ordered by that column.
> I tried this:
> CREATE Procedure ProductsByTab
> (
> @.TabID int,
> @.Order nvarchar (50)
> )
> AS
> SELECT
> *
> FROM
> Product
> WHERE
> TabID = @.TabID
> ORDER BY
> @.Order
> GO
> but I get this message:
> Error 1008: The SELECT item identified by the ORDER BY number 1 contains a
> variable as part of the expression identifying a column position. Variable
s
> are only allowed when ordering by an expression referencing a column name.
> Is it possible to do what I want? What am I doing wrong?
> Thank you.|||Thanks! Your replies were absolutely efective.

"On Error Resume Next" in SQL Server

I am executing a stored procedure that uses a cursor to do
a CONTAINS search for each record and to produce a result
set from that for output. The trouble is that if a good
old "ignored words" error occurs for one of the records
the procedure stops running. I need this to carry on
running regardless of this as the only reason an error
would occur would be due to bad user input which does not
bother me and can therefore be "ignored".
I have followed the advice in the KB article at
http://support.microsoft.com/default.aspx?scid=kb;en-
us;246800 for formatting input for CONTAINS searches but
there is always some nasty ASCII/UNICODE character that
slips through.
Is there a method to isolate the full-text search (or any
part of the procedure for that matter) and to guarantee
that my stored procedure will run to the end of the cursor?
Any help would be much appreciated.Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"Andy Wakeling" wrote:

> I am executing a stored procedure that uses a cursor to do
> a CONTAINS search for each record and to produce a result
> set from that for output. The trouble is that if a good
> old "ignored words" error occurs for one of the records
> the procedure stops running. I need this to carry on
> running regardless of this as the only reason an error
> would occur would be due to bad user input which does not
> bother me and can therefore be "ignored".
> I have followed the advice in the KB article at
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;246800 for formatting input for CONTAINS searches but
> there is always some nasty ASCII/UNICODE character that
> slips through.
> Is there a method to isolate the full-text search (or any
> part of the procedure for that matter) and to guarantee
> that my stored procedure will run to the end of the cursor?
> Any help would be much appreciated.
>|||When you are running your query in query analyzer, does it stop running? If
so, then please post the code. If it doesn't, then it is your code that is
causing it to stop. Just have your calling code ignore the errors and
continue on.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Andy Wakeling" <anonymous@.discussions.microsoft.com> wrote in message
news:203501c50ad3$6ee202d0$a601280a@.phx.gbl...
>I am executing a stored procedure that uses a cursor to do
> a CONTAINS search for each record and to produce a result
> set from that for output. The trouble is that if a good
> old "ignored words" error occurs for one of the records
> the procedure stops running. I need this to carry on
> running regardless of this as the only reason an error
> would occur would be due to bad user input which does not
> bother me and can therefore be "ignored".
> I have followed the advice in the KB article at
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;246800 for formatting input for CONTAINS searches but
> there is always some nasty ASCII/UNICODE character that
> slips through.
> Is there a method to isolate the full-text search (or any
> part of the procedure for that matter) and to guarantee
> that my stored procedure will run to the end of the cursor?
> Any help would be much appreciated.|||Louis,
I won't post the actual code as it is a massive SP and
besides, we've tried various tests in QA as well but the
gist is as follows:
DECLARE TestCursor CURSOR FOR /*WHATEVER*/
OPEN TestCursor
WHILE (1 = 1)
BEGIN
FETCH NEXT FROM TestCursor INTO @.SearchText
SET @.SearchText = FormatSearchText(@.SearchText)
/*
This is a UDF with output as per KB article as mentioned.
If anything goes wrong this returns '' as I am not
bothered if it cannot resolve input but note: This can
still output junk that can break the CONTAINS search.
*/
INSERT INTO #TEMPTABLE SELECT /*WHATEVER FROM WHEREVER*/
WHERE CONTAINS(/*SEARCHFIELD*/, @.SearchText)
/*
When run in QA, if this query causes an ignored-word error
the SP stops dead. I need it to carry on to the end of the
cursor.
*/
END, CLOSE, DEALLOCATE etc.
SELECT * FROM #TEMPTABLE /* Output of entire cursor */
That's pretty much what I'm trying to achieve. What do you
reckon?
Cheers
Andy

>--Original Message--
>When you are running your query in query analyzer, does
it stop running? If
>so, then please post the code. If it doesn't, then it is
your code that is
>causing it to stop. Just have your calling code ignore
the errors and
>continue on.
>--
>----
--
>Louis Davidson - drsql@.hotmail.com
>SQL Server MVP
>Compass Technology Management - www.compass.net
>Pro SQL Server 2000 Database Design -
>http://www.apress.com/book/bookDisplay.html?bID=266
>Blog - http://spaces.msn.com/members/drsql/
>Note: Please reply to the newsgroups only unless you are
interested in
>consulting services. All other replies may be ignored :)
>"Andy Wakeling" <anonymous@.discussions.microsoft.com>
wrote in message
>news:203501c50ad3$6ee202d0$a601280a@.phx.gbl...
do
result
not
any
cursor?
>
>.
>|||No idea, as I don't use full text search at all. However, if the
formatSearchText can output stuff to cause it to fail, is this text
something that would obviously make it fail? Such that you could clean it
up in the UDF? Hopefully someone else who has used full text search can see
the problem with it. Maybe posting some of the values that cause it to
fail?
Sorry I am not much help on this subject.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Andy Wakeling" <anonymous@.discussions.microsoft.com> wrote in message
news:21c301c50cff$5894aea0$a401280a@.phx.gbl...
> Louis,
> I won't post the actual code as it is a massive SP and
> besides, we've tried various tests in QA as well but the
> gist is as follows:
> DECLARE TestCursor CURSOR FOR /*WHATEVER*/
> OPEN TestCursor
> WHILE (1 = 1)
> BEGIN
> FETCH NEXT FROM TestCursor INTO @.SearchText
> SET @.SearchText = FormatSearchText(@.SearchText)
> /*
> This is a UDF with output as per KB article as mentioned.
> If anything goes wrong this returns '' as I am not
> bothered if it cannot resolve input but note: This can
> still output junk that can break the CONTAINS search.
> */
> INSERT INTO #TEMPTABLE SELECT /*WHATEVER FROM WHEREVER*/
> WHERE CONTAINS(/*SEARCHFIELD*/, @.SearchText)
> /*
> When run in QA, if this query causes an ignored-word error
> the SP stops dead. I need it to carry on to the end of the
> cursor.
> */
> END, CLOSE, DEALLOCATE etc.
> SELECT * FROM #TEMPTABLE /* Output of entire cursor */
> That's pretty much what I'm trying to achieve. What do you
> reckon?
> Cheers
> Andy
>
> it stop running? If
> your code that is
> the errors and
> --
> interested in
> wrote in message
> do
> result
> not
> any
> cursor?

"Must declare variable" error caused by stored procedure

I'm having the most difficult time trying to generate a report that
first calls a stored procedure and then retrieves the data produced
from it.
I get the error, "An error has occurred during report
processing...query execution failed for data set dsOrgs...Must declare
the variable @.INum" when I try to run the report.
The dataset below (dsOrgs) first calls a stored procedure
(SV_GetSubordinates) that populates a table with hierarchical data.
The second part (the Select statement) then retrieves the data
produced by the stored procedure. I have no problem running this set
of SQL statements in the Data view of the Reporting Services Report
Designer.
EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
SELECT v_Orgs.*
FROM v_Orgs, SVSiblings
WHERE
v_Orgs.INum = @.INum AND
v_Orgs.INum = SVSiblings.INum AND
v_Orgs.OrgNum = SVSiblings.Num
By the way, @.INum is a parameter that will be passed to the report in
a URL string eventually. But for now, I have to use both the Preview
capability of the Report Designer and the Report Manager rendering
engine to test out my report.
I have another dataset that gets the @.OrgNum parameter value from a
selection in a drop down in my report. Here is the query for that
data set...
SELECT
NULL AS OrgNum,
'-- ALL Orgs --' AS [Description]
FROM SVGroupDefs
WHERE
INum = @.INum
UNION
SELECT
OrgNum,
[Description]
FROM SVGroupDefs
WHERE
INum = @.INum
ORDER BY [Description]
As you can see, I'm using @.INum in this dataset first so I can
populate my drop down list. When the user selects an Organization
from the drop down list, the selection returns the value for the
parameter @.OrgNum, which is used in my dsOrgs dataset along with @.INum
to retrieve the hierarchical data for my report.
You may be asking why I need to get hierarchical data when the table
object in the report designer uses a parent-child relationship. The
reason why I'm going through all this pain is because I need to
recursively get all the children from a starting parent level, which
@.OrgNum supplies. SQL Server does not natively support a way to
recursively get all the children in a hierarchy. The only way to do
this is to run through my stored procedure, which recursively calls
itself and then populates a table with the child OrgNum values
(fortunately Yukon has solved this recursive nightmare).
Anyway, how can I generate my report when the error states I must
first declare @.INum?I guess thats because the SPs run independently.
BTW have you considered using cursors in your SP?
>--Original Message--
>I'm having the most difficult time trying to generate a
report that
>first calls a stored procedure and then retrieves the
data produced
>from it.
>I get the error, "An error has occurred during report
>processing...query execution failed for data set
dsOrgs...Must declare
>the variable @.INum" when I try to run the report.
>The dataset below (dsOrgs) first calls a stored procedure
>(SV_GetSubordinates) that populates a table with
hierarchical data.
>The second part (the Select statement) then retrieves the
data
>produced by the stored procedure. I have no problem
running this set
>of SQL statements in the Data view of the Reporting
Services Report
>Designer.
>EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
>SELECT v_Orgs.*
>FROM v_Orgs, SVSiblings
>WHERE
> v_Orgs.INum = @.INum AND
> v_Orgs.INum = SVSiblings.INum AND
> v_Orgs.OrgNum = SVSiblings.Num
>By the way, @.INum is a parameter that will be passed to
the report in
>a URL string eventually. But for now, I have to use both
the Preview
>capability of the Report Designer and the Report Manager
rendering
>engine to test out my report.
>I have another dataset that gets the @.OrgNum parameter
value from a
>selection in a drop down in my report. Here is the query
for that
>data set...
>SELECT
> NULL AS OrgNum,
> '-- ALL Orgs --' AS [Description]
>FROM SVGroupDefs
>WHERE
> INum = @.INum
>UNION
>SELECT
> OrgNum,
> [Description]
>FROM SVGroupDefs
>WHERE
> INum = @.INum
>ORDER BY [Description]
>As you can see, I'm using @.INum in this dataset first so
I can
>populate my drop down list. When the user selects an
Organization
>from the drop down list, the selection returns the value
for the
>parameter @.OrgNum, which is used in my dsOrgs dataset
along with @.INum
>to retrieve the hierarchical data for my report.
>You may be asking why I need to get hierarchical data
when the table
>object in the report designer uses a parent-child
relationship. The
>reason why I'm going through all this pain is because I
need to
>recursively get all the children from a starting parent
level, which
>@.OrgNum supplies. SQL Server does not natively support a
way to
>recursively get all the children in a hierarchy. The
only way to do
>this is to run through my stored procedure, which
recursively calls
>itself and then populates a table with the child OrgNum
values
>(fortunately Yukon has solved this recursive nightmare).
>Anyway, how can I generate my report when the error
states I must
>first declare @.INum?
>.
>|||Ravi, the stored procedure must call itself recursively as it gets the
children for each parent. For example, here's my hierarchy
OrgNum ParentOrgNum
1001 NULL
1002 1001
1003 1002
1004 1002
1005 1001
1006 1005
1007 1006
1008 1006
If I want all the children for OrgNum 1005, the sproc first runs and
gets OrgNum 1006 as a child. Then 1006 becomes the parent and the
sproc calls itself to get all the children for 1006, which are 1007
and 1008. When the sproc tries to get their children, there are no
more, and the sproc terminates. So, I end up with 1006, 1007, and
1008 as children for 1005. During each iteration of the sproc, I
insert the child OrgNum values into a table.
So, with my dsOrgs dataset, I first run the sproc, which does the
stuff above. Then I run a select statement which retrieves all the
children from the table I populated from my sproc. All that works
fine when I run everything in the data area of Reporting Services'
Report Builder.
But that's not the problem. The problem is that I get an error
stating that I have to declare a variable, @.INum. That's what my
first message covers in detail, and the problem to which I'm seeking a
solution.
"Ravi" <ravikantkv@.rediffmail.com> wrote in message news:<549501c49175$e1e4dcd0$a601280a@.phx.gbl>...
> I guess thats because the SPs run independently.
> BTW have you considered using cursors in your SP?
> >--Original Message--
> >I'm having the most difficult time trying to generate a
> report that
> >first calls a stored procedure and then retrieves the
> data produced
> >from it.
> >
> >I get the error, "An error has occurred during report
> >processing...query execution failed for data set
> dsOrgs...Must declare
> >the variable @.INum" when I try to run the report.
> >
> >The dataset below (dsOrgs) first calls a stored procedure
> >(SV_GetSubordinates) that populates a table with
> hierarchical data.
> >The second part (the Select statement) then retrieves the
> data
> >produced by the stored procedure. I have no problem
> running this set
> >of SQL statements in the Data view of the Reporting
> Services Report
> >Designer.
> >
> >EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
> >SELECT v_Orgs.*
> >FROM v_Orgs, SVSiblings
> >WHERE
> > v_Orgs.INum = @.INum AND
> > v_Orgs.INum = SVSiblings.INum AND
> > v_Orgs.OrgNum = SVSiblings.Num
> >
> >By the way, @.INum is a parameter that will be passed to
> the report in
> >a URL string eventually. But for now, I have to use both
> the Preview
> >capability of the Report Designer and the Report Manager
> rendering
> >engine to test out my report.
> >
> >I have another dataset that gets the @.OrgNum parameter
> value from a
> >selection in a drop down in my report. Here is the query
> for that
> >data set...
> >
> >SELECT
> > NULL AS OrgNum,
> > '-- ALL Orgs --' AS [Description]
> >FROM SVGroupDefs
> >WHERE
> > INum = @.INum
> >UNION
> >SELECT
> > OrgNum,
> > [Description]
> >FROM SVGroupDefs
> >WHERE
> > INum = @.INum
> >ORDER BY [Description]
> >
> >As you can see, I'm using @.INum in this dataset first so
> I can
> >populate my drop down list. When the user selects an
> Organization
> >from the drop down list, the selection returns the value
> for the
> >parameter @.OrgNum, which is used in my dsOrgs dataset
> along with @.INum
> >to retrieve the hierarchical data for my report.
> >
> >You may be asking why I need to get hierarchical data
> when the table
> >object in the report designer uses a parent-child
> relationship. The
> >reason why I'm going through all this pain is because I
> need to
> >recursively get all the children from a starting parent
> level, which
> >@.OrgNum supplies. SQL Server does not natively support a
> way to
> >recursively get all the children in a hierarchy. The
> only way to do
> >this is to run through my stored procedure, which
> recursively calls
> >itself and then populates a table with the child OrgNum
> values
> >(fortunately Yukon has solved this recursive nightmare).
> >
> >Anyway, how can I generate my report when the error
> states I must
> >first declare @.INum?
> >.
> >|||Hi Steve,
I am doing something sort of similar... I have created a
stored procedure that takes in a couple of parameters and
passes them to the database and creates a table. I then
have a query that selects the data from the table. I
created two seperate datasets one for the stored
procedure and one for the select statement and it seems
to be working. I'm not sure how you tell it what to
execute first but another report writer here is doing the
same but she had to increase the timeout to give the
stored procedure a chance to finish otherwise it was
throwing errors. Sorry I'm not much help but I'm curious
if you've tried to use the parent group within the group
that is suppose to recursively search in a parent-child
relationship? I have the same exact thing to do that you
are doing and I would love to hear any lessons learned.
Thanks!!
>--Original Message--
>I'm having the most difficult time trying to generate a
report that
>first calls a stored procedure and then retrieves the
data produced
>from it.
>I get the error, "An error has occurred during report
>processing...query execution failed for data set
dsOrgs...Must declare
>the variable @.INum" when I try to run the report.
>The dataset below (dsOrgs) first calls a stored procedure
>(SV_GetSubordinates) that populates a table with
hierarchical data.
>The second part (the Select statement) then retrieves
the data
>produced by the stored procedure. I have no problem
running this set
>of SQL statements in the Data view of the Reporting
Services Report
>Designer.
>EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
>SELECT v_Orgs.*
>FROM v_Orgs, SVSiblings
>WHERE
> v_Orgs.INum = @.INum AND
> v_Orgs.INum = SVSiblings.INum AND
> v_Orgs.OrgNum = SVSiblings.Num
>By the way, @.INum is a parameter that will be passed to
the report in
>a URL string eventually. But for now, I have to use
both the Preview
>capability of the Report Designer and the Report Manager
rendering
>engine to test out my report.
>I have another dataset that gets the @.OrgNum parameter
value from a
>selection in a drop down in my report. Here is the
query for that
>data set...
>SELECT
> NULL AS OrgNum,
> '-- ALL Orgs --' AS [Description]
>FROM SVGroupDefs
>WHERE
> INum = @.INum
>UNION
>SELECT
> OrgNum,
> [Description]
>FROM SVGroupDefs
>WHERE
> INum = @.INum
>ORDER BY [Description]
>As you can see, I'm using @.INum in this dataset first so
I can
>populate my drop down list. When the user selects an
Organization
>from the drop down list, the selection returns the value
for the
>parameter @.OrgNum, which is used in my dsOrgs dataset
along with @.INum
>to retrieve the hierarchical data for my report.
>You may be asking why I need to get hierarchical data
when the table
>object in the report designer uses a parent-child
relationship. The
>reason why I'm going through all this pain is because I
need to
>recursively get all the children from a starting parent
level, which
>@.OrgNum supplies. SQL Server does not natively support
a way to
>recursively get all the children in a hierarchy. The
only way to do
>this is to run through my stored procedure, which
recursively calls
>itself and then populates a table with the child OrgNum
values
>(fortunately Yukon has solved this recursive nightmare).
>Anyway, how can I generate my report when the error
states I must
>first declare @.INum?
>.
>|||Melissa,
Reporting Services does a fine job handling parent-child sets of
data in tables and what not...but that's assuming you have a data set
with all the data you want. What I have is an entire hierarchy, of
which, only one part I might want to retrieve in the data set for the
report (e.g. pulling back a region and their branches vs. the whole
entire organization). So, the whole problem here is about retrieving
the data and not about how Reporting Services will handle it after the
data is retrieved.
With that being said, I've already tried to break out the stored
procedure that gets all the children vs. the SQL query that retrieves
the data produced by the stored procedure into two separate data sets.
But how would Reporting Services know when the stored procedure has
finished in order to run the second query, which retrieves the data?
By arbitrarily setting a timeout? I find that method too unreliable.
I've already broken out my stored procedure and select statement into
two data sets, but that doesn't work.
So, what else have I done? I've tried to do a recursive SQL
function, but to no avail (functions can't recursive do selects of
data), a recursive stored procedure with a varying output parameter
(Reporting Services allows only 1 value per parameter for the current
release), but to no avail, setting the output of my stored procedure
to a temp table (can't seem to get that to work), but to no avail,
using a global temp table within my stored procedure (doesn't work
because the stored procedure recursively calls itself and you can only
declare the global temp table once), but to no avail, and a bunch of
other techniques in order to recursively grab all the children for the
region I select for my data set. The Yukon release of SQL Server will
solve my problem, because I will be able to execute a single
expression and retrieve the recursive data I need in a single
operation...but I need something in the meantime. (By the way, Oracle
already supports recursion with their Connect method).
So, I ended up having to pair the stored procedure with my select in
the same dataset in order to 1) generate a list of child values and 2)
retrieve that list of values AFTER they are generated. The dataset
refreshes no problem, but I get that stupid "need to declare @.INum
first" error, which I can't get rid of...it's so frustrating. I wish
I could speak to one of the Reporting Services developers over the
phone and figure this out.
"Melissa" <anonymous@.discussions.microsoft.com> wrote in message news:<028801c491fd$3c204e00$a401280a@.phx.gbl>...
> Hi Steve,
> I am doing something sort of similar... I have created a
> stored procedure that takes in a couple of parameters and
> passes them to the database and creates a table. I then
> have a query that selects the data from the table. I
> created two seperate datasets one for the stored
> procedure and one for the select statement and it seems
> to be working. I'm not sure how you tell it what to
> execute first but another report writer here is doing the
> same but she had to increase the timeout to give the
> stored procedure a chance to finish otherwise it was
> throwing errors. Sorry I'm not much help but I'm curious
> if you've tried to use the parent group within the group
> that is suppose to recursively search in a parent-child
> relationship? I have the same exact thing to do that you
> are doing and I would love to hear any lessons learned.
> Thanks!!
> >--Original Message--
> >I'm having the most difficult time trying to generate a
> report that
> >first calls a stored procedure and then retrieves the
> data produced
> >from it.
> >
> >I get the error, "An error has occurred during report
> >processing...query execution failed for data set
> dsOrgs...Must declare
> >the variable @.INum" when I try to run the report.
> >
> >The dataset below (dsOrgs) first calls a stored procedure
> >(SV_GetSubordinates) that populates a table with
> hierarchical data.
> >The second part (the Select statement) then retrieves
> the data
> >produced by the stored procedure. I have no problem
> running this set
> >of SQL statements in the Data view of the Reporting
> Services Report
> >Designer.
> >
> >EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
> >SELECT v_Orgs.*
> >FROM v_Orgs, SVSiblings
> >WHERE
> > v_Orgs.INum = @.INum AND
> > v_Orgs.INum = SVSiblings.INum AND
> > v_Orgs.OrgNum = SVSiblings.Num
> >
> >By the way, @.INum is a parameter that will be passed to
> the report in
> >a URL string eventually. But for now, I have to use
> both the Preview
> >capability of the Report Designer and the Report Manager
> rendering
> >engine to test out my report.
> >
> >I have another dataset that gets the @.OrgNum parameter
> value from a
> >selection in a drop down in my report. Here is the
> query for that
> >data set...
> >
> >SELECT
> > NULL AS OrgNum,
> > '-- ALL Orgs --' AS [Description]
> >FROM SVGroupDefs
> >WHERE
> > INum = @.INum
> >UNION
> >SELECT
> > OrgNum,
> > [Description]
> >FROM SVGroupDefs
> >WHERE
> > INum = @.INum
> >ORDER BY [Description]
> >
> >As you can see, I'm using @.INum in this dataset first so
> I can
> >populate my drop down list. When the user selects an
> Organization
> >from the drop down list, the selection returns the value
> for the
> >parameter @.OrgNum, which is used in my dsOrgs dataset
> along with @.INum
> >to retrieve the hierarchical data for my report.
> >
> >You may be asking why I need to get hierarchical data
> when the table
> >object in the report designer uses a parent-child
> relationship. The
> >reason why I'm going through all this pain is because I
> need to
> >recursively get all the children from a starting parent
> level, which
> >@.OrgNum supplies. SQL Server does not natively support
> a way to
> >recursively get all the children in a hierarchy. The
> only way to do
> >this is to run through my stored procedure, which
> recursively calls
> >itself and then populates a table with the child OrgNum
> values
> >(fortunately Yukon has solved this recursive nightmare).
> >
> >Anyway, how can I generate my report when the error
> states I must
> >first declare @.INum?
> >.
> >|||In case anyone cares, I solved this problem myself.
The problem was related to a dataset that first calls a stored
procedure to populate a table with recursive data and then runs a
select statement to retrieve a set of data filtered by the recursive
data. What was happening was that Reporting Services was erroring
because my select was trying to fire before my stored procedure
finished. I ended up gettting a "Must declare variable" error (among
other things).
The trick is to make the select statement "wait" for the stored
procedure. How do you do this, you ask? It's simple.
You have to declare a variable and then set the execution of the
stored procedure to the variable. The select statement that follows
has to wait for the variable above to get populated with a "0", which
means the stored procedure executed properly. Then the select will
fire.
Here's a sample dataset query that uses the variable wait method:
DECLARE @.ResultValue INT
EXEC @.ResultNum = SV_GetSubordinates @.INum,'Goals',@.GoalNum
SELECT *
FROM v_Goals
WHERE
GoalNum IN (
SELECT Num
FROM SVSiblings
WHERE
INum = @.INum AND
Type = 'Goals' AND
UserID = USER_ID())
See the part about "EXEC @.ResultNum = "? That's the key to avoiding
the "Must declare variable" error I encountered and the misfiring of
the select statement. The select part of the query waits for
@.ResultNum to be populated with a "0" before getting run.
steve.pantazis@.salesviz.com (Steve Pantazis) wrote in message news:<45c5a039.0409032037.1e978da4@.posting.google.com>...
> Melissa,
> Reporting Services does a fine job handling parent-child sets of
> data in tables and what not...but that's assuming you have a data set
> with all the data you want. What I have is an entire hierarchy, of
> which, only one part I might want to retrieve in the data set for the
> report (e.g. pulling back a region and their branches vs. the whole
> entire organization). So, the whole problem here is about retrieving
> the data and not about how Reporting Services will handle it after the
> data is retrieved.
> With that being said, I've already tried to break out the stored
> procedure that gets all the children vs. the SQL query that retrieves
> the data produced by the stored procedure into two separate data sets.
> But how would Reporting Services know when the stored procedure has
> finished in order to run the second query, which retrieves the data?
> By arbitrarily setting a timeout? I find that method too unreliable.
> I've already broken out my stored procedure and select statement into
> two data sets, but that doesn't work.
> So, what else have I done? I've tried to do a recursive SQL
> function, but to no avail (functions can't recursive do selects of
> data), a recursive stored procedure with a varying output parameter
> (Reporting Services allows only 1 value per parameter for the current
> release), but to no avail, setting the output of my stored procedure
> to a temp table (can't seem to get that to work), but to no avail,
> using a global temp table within my stored procedure (doesn't work
> because the stored procedure recursively calls itself and you can only
> declare the global temp table once), but to no avail, and a bunch of
> other techniques in order to recursively grab all the children for the
> region I select for my data set. The Yukon release of SQL Server will
> solve my problem, because I will be able to execute a single
> expression and retrieve the recursive data I need in a single
> operation...but I need something in the meantime. (By the way, Oracle
> already supports recursion with their Connect method).
> So, I ended up having to pair the stored procedure with my select in
> the same dataset in order to 1) generate a list of child values and 2)
> retrieve that list of values AFTER they are generated. The dataset
> refreshes no problem, but I get that stupid "need to declare @.INum
> first" error, which I can't get rid of...it's so frustrating. I wish
> I could speak to one of the Reporting Services developers over the
> phone and figure this out.
>
> "Melissa" <anonymous@.discussions.microsoft.com> wrote in message news:<028801c491fd$3c204e00$a401280a@.phx.gbl>...
> > Hi Steve,
> > I am doing something sort of similar... I have created a
> > stored procedure that takes in a couple of parameters and
> > passes them to the database and creates a table. I then
> > have a query that selects the data from the table. I
> > created two seperate datasets one for the stored
> > procedure and one for the select statement and it seems
> > to be working. I'm not sure how you tell it what to
> > execute first but another report writer here is doing the
> > same but she had to increase the timeout to give the
> > stored procedure a chance to finish otherwise it was
> > throwing errors. Sorry I'm not much help but I'm curious
> > if you've tried to use the parent group within the group
> > that is suppose to recursively search in a parent-child
> > relationship? I have the same exact thing to do that you
> > are doing and I would love to hear any lessons learned.
> > Thanks!!
> > >--Original Message--
> > >I'm having the most difficult time trying to generate a
> report that
> > >first calls a stored procedure and then retrieves the
> data produced
> > >from it.
> > >
> > >I get the error, "An error has occurred during report
> > >processing...query execution failed for data set
> dsOrgs...Must declare
> > >the variable @.INum" when I try to run the report.
> > >
> > >The dataset below (dsOrgs) first calls a stored procedure
> > >(SV_GetSubordinates) that populates a table with
> hierarchical data.
> > >The second part (the Select statement) then retrieves
> the data
> > >produced by the stored procedure. I have no problem
> running this set
> > >of SQL statements in the Data view of the Reporting
> Services Report
> > >Designer.
> > >
> > >EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
> > >SELECT v_Orgs.*
> > >FROM v_Orgs, SVSiblings
> > >WHERE
> > > v_Orgs.INum = @.INum AND
> > > v_Orgs.INum = SVSiblings.INum AND
> > > v_Orgs.OrgNum = SVSiblings.Num
> > >
> > >By the way, @.INum is a parameter that will be passed to
> the report in
> > >a URL string eventually. But for now, I have to use
> both the Preview
> > >capability of the Report Designer and the Report Manager
> rendering
> > >engine to test out my report.
> > >
> > >I have another dataset that gets the @.OrgNum parameter
> value from a
> > >selection in a drop down in my report. Here is the
> query for that
> > >data set...
> > >
> > >SELECT
> > > NULL AS OrgNum,
> > > '-- ALL Orgs --' AS [Description]
> > >FROM SVGroupDefs
> > >WHERE
> > > INum = @.INum
> > >UNION
> > >SELECT
> > > OrgNum,
> > > [Description]
> > >FROM SVGroupDefs
> > >WHERE
> > > INum = @.INum
> > >ORDER BY [Description]
> > >
> > >As you can see, I'm using @.INum in this dataset first so
> I can
> > >populate my drop down list. When the user selects an
> Organization
> > >from the drop down list, the selection returns the value
> for the
> > >parameter @.OrgNum, which is used in my dsOrgs dataset
> along with @.INum
> > >to retrieve the hierarchical data for my report.
> > >
> > >You may be asking why I need to get hierarchical data
> when the table
> > >object in the report designer uses a parent-child
> relationship. The
> > >reason why I'm going through all this pain is because I
> need to
> > >recursively get all the children from a starting parent
> level, which
> > >@.OrgNum supplies. SQL Server does not natively support
> a way to
> > >recursively get all the children in a hierarchy. The
> only way to do
> > >this is to run through my stored procedure, which
> recursively calls
> > >itself and then populates a table with the child OrgNum
> values
> > >(fortunately Yukon has solved this recursive nightmare).
> > >
> > >Anyway, how can I generate my report when the error
> states I must
> > >first declare @.INum?
> > >.
> > >

"Like" Stored Procedure Help Needed

Jeff,

> where (((title like '@.SearchTerm%' or @.Searchterm IS NULL) or
> (Document like '@.SearchTerm%' or @.SearchTerm IS NUll))and
You are surrounding the variable name with apostrophes and this is not the
same as concatenating the value of the variable with the wildcard '%'.
'@.SearchTerm%' --> @.SearchTerm + '%'
Example:
declare @.s varchar(25)
set @.s = 'sql server'
select '@.s%', @.s + '%'
go
so your statement should be like:
...
where
(((title like @.SearchTerm + '%' or @.Searchterm IS NULL) or
(Document like @.SearchTerm + '%' or @.SearchTerm IS NUll))and
...
AMB
"Jeff" wrote:

> What is wrong with my Stored Procedure? The select Statement returns corr
ect
> results...why not the Stored Procedure?
>
> select *
> from tbldocument
> where (title like 'ros%') or
> (document like 'asdf%') and
> Submitterid = '1'
>
> Create Procedure PROCEDURE SearchDocument
> (
> @.SearchTerm char (200) = NULL,
> @.SubmitterID int = null
> )
> as
>
> SELECT dbo.tblDocument.DocumentID,
> dbo.tblDocument.Title,
> dbo.tblSubmitter.SubmitterName
> FROM dbo.tblDocument join dbo.tblSubmitter
> on (tbldocument.submitterid = tblsubmitter.submitterid)
> where (((title like '@.SearchTerm%' or @.Searchterm IS NULL) or
> (Document like '@.SearchTerm%' or @.SearchTerm IS NUll))and
> (dbo.tblDocument.SubmitterID = @.submitterID or @.SubmitterID is null)
)
>Me have so much to learn!!
Thanks!!
"Alejandro Mesa" wrote:
> Jeff,
>
> You are surrounding the variable name with apostrophes and this is not the
> same as concatenating the value of the variable with the wildcard '%'.
> '@.SearchTerm%' --> @.SearchTerm + '%'
> Example:
> declare @.s varchar(25)
> set @.s = 'sql server'
> select '@.s%', @.s + '%'
> go
> so your statement should be like:
> ...
> where
> (((title like @.SearchTerm + '%' or @.Searchterm IS NULL) or
> (Document like @.SearchTerm + '%' or @.SearchTerm IS NUll))and
> ...
>
> AMB
> "Jeff" wrote:
>

"Invalid Character value for cast specification" from VC++ when i execute a proc

Hi Everybody,

I have a question regarding the running of a procedure from VC++ in SQL server.

well this is the scenario.

i have a procedure in SQL server and i am running that thru my VC++ code.
:eek:
i have a recordset class and i create an object of it and then do a obj.Open(). the procedure is fetching 24 records correctly in the query analyser and in my while(!(obj.IsEOF())) loop it gets the first record correctly but the moment it encounters obj.MoveNext(); it bombs and gives me the following error..

"Invalid Character value for cast specification ".

i don't know why this is happening

Thanks for you help.Somewhere in your Transact-SQL code you are trying to convert a character value to another datatype. My first guess would be DATETIME, next might be INT or another numeric datatype. This is usually either in a column you are returning or in a WHERE clause, but it can happen anywhere you can refer to a column.

-PatP|||BTW, since this actually appears to be a Transact-SQL problem, I'm moving the thread back to the Microsoft SQL forum.

-PatP

"Internal SQL Server error."

"Server: Msg 8624, Level 16, State 7, Procedure p_get_LocalAuthor2, Line 58
Internal SQL Server error."
...and on that note, I'm going home.
What does SELECT @.@.VERSION yield? What does p_get_LocalAuthor2 look like?
What is on line 58?
"Mike C#" <xyz@.xyz.com> wrote in message
news:uOisHvzEHHA.3608@.TK2MSFTNGP02.phx.gbl...
> "Server: Msg 8624, Level 16, State 7, Procedure p_get_LocalAuthor2, Line
> 58
> Internal SQL Server error."
> ...and on that note, I'm going home.
>
|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e66wS1zEHHA.3784@.TK2MSFTNGP02.phx.gbl...
> What does SELECT @.@.VERSION yield? What does p_get_LocalAuthor2 look like?
> What is on line 58?
Hi Aaron,
Don't sweat it. This is a stored procedure that ran fine about 20 times in
a row on SQL 2000 SP 4. The 21st time seems to have been the charm. I
already checked and there's no KB articles that address this particular
situation (though there are a few that talk about "Internal Server Error"
under different circumstances that don't apply to my situation.)
BTW, Line 58 looks like this: DECLARE @.s VARCHAR(50)
Cool, huh? Thanks.
|||> already checked and there's no KB articles that address this particular
> situation (though there are a few that talk about "Internal Server Error"
> under different circumstances that don't apply to my situation.)
Most "Internal Server Error" issues are internal bugs that are fixed with
SPs or hotfixes. It may very well be a symptom that is fixed by a hotfix
that addressed a different system (not all bugs ever make it to the
knowledge base, so don't consider it to be gospel).
But, if you want to stay at SP4 and ignore the more recent hotfixes, you may
still continue to have this problem. If you can reproduce it on a second
box, especially after applying the latest hotfix, you may want to post a bug
on connect.
A
|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23SYpze8EHHA.4016@.TK2MSFTNGP02.phx.gbl...
> Most "Internal Server Error" issues are internal bugs that are fixed with
> SPs or hotfixes. It may very well be a symptom that is fixed by a hotfix
> that addressed a different system (not all bugs ever make it to the
> knowledge base, so don't consider it to be gospel).
> But, if you want to stay at SP4 and ignore the more recent hotfixes, you
> may still continue to have this problem. If you can reproduce it on a
> second box, especially after applying the latest hotfix, you may want to
> post a bug on connect.
Thanks, I was just venting For now I've rebooted the box and it
suddenly works again. Go figure. I'll probably post a bug if it happens
again.
Thanks again
|||> Thanks, I was just venting
You should consider a blog.
Here, just about everything is presumed to be a request for help.
A

"Internal SQL Server error."

"Server: Msg 8624, Level 16, State 7, Procedure p_get_LocalAuthor2, Line 58
Internal SQL Server error."
...and on that note, I'm going home.What does SELECT @.@.VERSION yield? What does p_get_LocalAuthor2 look like?
What is on line 58?
"Mike C#" <xyz@.xyz.com> wrote in message
news:uOisHvzEHHA.3608@.TK2MSFTNGP02.phx.gbl...
> "Server: Msg 8624, Level 16, State 7, Procedure p_get_LocalAuthor2, Line
> 58
> Internal SQL Server error."
> ...and on that note, I'm going home.
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:e66wS1zEHHA.3784@.TK2MSFTNGP02.phx.gbl...
> What does SELECT @.@.VERSION yield? What does p_get_LocalAuthor2 look like?
> What is on line 58?
Hi Aaron,
Don't sweat it. This is a stored procedure that ran fine about 20 times in
a row on SQL 2000 SP 4. The 21st time seems to have been the charm. I
already checked and there's no KB articles that address this particular
situation (though there are a few that talk about "Internal Server Error"
under different circumstances that don't apply to my situation.)
BTW, Line 58 looks like this: DECLARE @.s VARCHAR(50)
Cool, huh? Thanks.|||> already checked and there's no KB articles that address this particular
> situation (though there are a few that talk about "Internal Server Error"
> under different circumstances that don't apply to my situation.)
Most "Internal Server Error" issues are internal bugs that are fixed with
SPs or hotfixes. It may very well be a symptom that is fixed by a hotfix
that addressed a different system (not all bugs ever make it to the
knowledge base, so don't consider it to be gospel).
But, if you want to stay at SP4 and ignore the more recent hotfixes, you may
still continue to have this problem. If you can reproduce it on a second
box, especially after applying the latest hotfix, you may want to post a bug
on connect.
A|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:%23SYpze8EHHA.4016@.TK2MSFTNGP02.phx.gbl...
> Most "Internal Server Error" issues are internal bugs that are fixed with
> SPs or hotfixes. It may very well be a symptom that is fixed by a hotfix
> that addressed a different system (not all bugs ever make it to the
> knowledge base, so don't consider it to be gospel).
> But, if you want to stay at SP4 and ignore the more recent hotfixes, you
> may still continue to have this problem. If you can reproduce it on a
> second box, especially after applying the latest hotfix, you may want to
> post a bug on connect.
Thanks, I was just venting For now I've rebooted the box and it
suddenly works again. Go figure. I'll probably post a bug if it happens
again.
Thanks again|||> Thanks, I was just venting
You should consider a blog.
Here, just about everything is presumed to be a request for help.
A

"Internal SQL Server error."

"Server: Msg 8624, Level 16, State 7, Procedure p_get_LocalAuthor2, Line 58
Internal SQL Server error."
...and on that note, I'm going home.What does SELECT @.@.VERSION yield? What does p_get_LocalAuthor2 look like?
What is on line 58?
"Mike C#" <xyz@.xyz.com> wrote in message
news:uOisHvzEHHA.3608@.TK2MSFTNGP02.phx.gbl...
> "Server: Msg 8624, Level 16, State 7, Procedure p_get_LocalAuthor2, Line
> 58
> Internal SQL Server error."
> ...and on that note, I'm going home.
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e66wS1zEHHA.3784@.TK2MSFTNGP02.phx.gbl...
> What does SELECT @.@.VERSION yield? What does p_get_LocalAuthor2 look like?
> What is on line 58?
Hi Aaron,
Don't sweat it. This is a stored procedure that ran fine about 20 times in
a row on SQL 2000 SP 4. The 21st time seems to have been the charm. I
already checked and there's no KB articles that address this particular
situation (though there are a few that talk about "Internal Server Error"
under different circumstances that don't apply to my situation.)
BTW, Line 58 looks like this: DECLARE @.s VARCHAR(50)
Cool, huh? Thanks.|||> already checked and there's no KB articles that address this particular
> situation (though there are a few that talk about "Internal Server Error"
> under different circumstances that don't apply to my situation.)
Most "Internal Server Error" issues are internal bugs that are fixed with
SPs or hotfixes. It may very well be a symptom that is fixed by a hotfix
that addressed a different system (not all bugs ever make it to the
knowledge base, so don't consider it to be gospel).
But, if you want to stay at SP4 and ignore the more recent hotfixes, you may
still continue to have this problem. If you can reproduce it on a second
box, especially after applying the latest hotfix, you may want to post a bug
on connect.
A|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23SYpze8EHHA.4016@.TK2MSFTNGP02.phx.gbl...
>> already checked and there's no KB articles that address this particular
>> situation (though there are a few that talk about "Internal Server Error"
>> under different circumstances that don't apply to my situation.)
> Most "Internal Server Error" issues are internal bugs that are fixed with
> SPs or hotfixes. It may very well be a symptom that is fixed by a hotfix
> that addressed a different system (not all bugs ever make it to the
> knowledge base, so don't consider it to be gospel).
> But, if you want to stay at SP4 and ignore the more recent hotfixes, you
> may still continue to have this problem. If you can reproduce it on a
> second box, especially after applying the latest hotfix, you may want to
> post a bug on connect.
Thanks, I was just venting :) For now I've rebooted the box and it
suddenly works again. Go figure. I'll probably post a bug if it happens
again.
Thanks again|||> Thanks, I was just venting :)
You should consider a blog.
Here, just about everything is presumed to be a request for help.
A

"If you want a lot of information, one has to use cursors."

A developer I'm working with just gave me a stored procedure that uses three
cursors, two of which are nested into the first. These cursors retrieve data
from tables one row at a time to build temporary tables, which are used to
build a result set.
I told him not to use cursors because they're incredibly slow compared to
joins, and make the query much harder to read and maintain. He replied,
"If you want a lot of information, one has to use cursors."
I didn't know that. To think, I'd been using just plain old joins to get
data all these years. Guess I've been just doing it wrong.
PaulTell him "Cursors are useful if you don't know SQL"
(Acknowledgments to Nigel Rivett)
David Portas
SQL Server MVP
--|||The correct quote is if one has a lot of information and one chooses to use
cursors, one will see a significant decline in performance.
"PJ6" <nobody@.nowhere.net> wrote in message
news:%23nsiUcWuFHA.664@.tk2msftngp13.phx.gbl...
>A developer I'm working with just gave me a stored procedure that uses
>three cursors, two of which are nested into the first. These cursors
>retrieve data from tables one row at a time to build temporary tables,
>which are used to build a result set.
> I told him not to use cursors because they're incredibly slow compared to
> joins, and make the query much harder to read and maintain. He replied,
> "If you want a lot of information, one has to use cursors."
> I didn't know that. To think, I'd been using just plain old joins to get
> data all these years. Guess I've been just doing it wrong.
> Paul
>|||I didn't see a smiley face and if there's sarcasm there, it's not obvious
enough for me.
If this guy really ticks you off, you're going to have to prove him wrong
but this involves a bit of work for you re-coding the SP and the client
application.
Good luck.
"PJ6" <nobody@.nowhere.net> wrote in message
news:%23nsiUcWuFHA.664@.tk2msftngp13.phx.gbl...
>A developer I'm working with just gave me a stored procedure that uses
>three cursors, two of which are nested into the first. These cursors
>retrieve data from tables one row at a time to build temporary tables,
>which are used to build a result set.
> I told him not to use cursors because they're incredibly slow compared to
> joins, and make the query much harder to read and maintain. He replied,
> "If you want a lot of information, one has to use cursors."
> I didn't know that. To think, I'd been using just plain old joins to get
> data all these years. Guess I've been just doing it wrong.
> Paul
>|||My rule of thumb is that a cursor runs 10 times slower than a query.
The nested cursors are usually a sign that he is mimicking a tape file
system instead of writing SQL.|||> "If you want a lot of information, one has to use cursors."
You could swap out "if you want a lot of information" and replace it with
"if one doesn't understand set theory"...|||Hi
It is a good idea to make your developers test on databases that have a
reasonable amount of realistic data.
John
"PJ6" <nobody@.nowhere.net> wrote in message
news:%23nsiUcWuFHA.664@.tk2msftngp13.phx.gbl...
>A developer I'm working with just gave me a stored procedure that uses
>three cursors, two of which are nested into the first. These cursors
>retrieve data from tables one row at a time to build temporary tables,
>which are used to build a result set.
> I told him not to use cursors because they're incredibly slow compared to
> joins, and make the query much harder to read and maintain. He replied,
> "If you want a lot of information, one has to use cursors."
> I didn't know that. To think, I'd been using just plain old joins to get
> data all these years. Guess I've been just doing it wrong.
> Paul
>|||You must not know how to write a cursor. If it's done right, a cursor will
only take twice as long as a set-based query, but in some cases--usually
those involving a self-join--a cursor will actually perform better.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1126729006.844457.69490@.g14g2000cwa.googlegroups.com...
> My rule of thumb is that a cursor runs 10 times slower than a query.
> The nested cursors are usually a sign that he is mimicking a tape file
> system instead of writing SQL.
>|||He wrote the book on self joins.
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:Or3yv1WuFHA.3932@.TK2MSFTNGP15.phx.gbl...
> You must not know how to write a cursor. If it's done right, a cursor
> will
> only take twice as long as a set-based query, but in some cases--usually
> those involving a self-join--a cursor will actually perform better.
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1126729006.844457.69490@.g14g2000cwa.googlegroups.com...
>|||I just used a cursor a few hours ago:
open dishwasher for all the dirty dishes
while (@.@.fetch_status = 0)
begin
fetch next dish
insert into dishwasher
dish
end
And it worked. Yes, pseudo-code is good enough for most of my kitchen
appliances.
ML

"GO" used in a stored procedure

I have a stored procedure in sql 2000 that requires steps to be fully
completed before moving to the next command in the procedure. I have
tried to place the word "GO" after each statement. When I create the
procedure then take a look at it through em, it only shows the code up
until the word "GO".

Example:
CREATE PROCEDURE mytest
as
create table mytable col1 varchar(5), col2 varchar(10)
GO
insert into mytable (col1, col2) values(abc, def)

All i see is:
CREATE PROCEDURE mytest
as
create table mytable col1 varchar(5), col2 varchar(10)
GO

I need to be 100% certain that the table has already been created
before trying to add records to it.

Is there any command that makes t-sql halt until the previous command
has finished?

Thanks,
DaveGO isn't a TSQL command. It marks the end of a batch in Query Analyzer and
therefore signals the end of a stored procedure definition in that batch, so
it cannot be part of an SP.

--
David Portas
SQL Server MVP
--|||Dave wrote:

> I have a stored procedure in sql 2000 that requires steps to be fully
> completed before moving to the next command in the procedure. I have
> tried to place the word "GO" after each statement. When I create the
> procedure then take a look at it through em, it only shows the code up
> until the word "GO".
> Example:
> CREATE PROCEDURE mytest
> as
> create table mytable col1 varchar(5), col2 varchar(10)
> GO
> insert into mytable (col1, col2) values(abc, def)
> All i see is:
> CREATE PROCEDURE mytest
> as
> create table mytable col1 varchar(5), col2 varchar(10)
> GO
>
> I need to be 100% certain that the table has already been created
> before trying to add records to it.
> Is there any command that makes t-sql halt until the previous command
> has finished?
> Thanks,
> Dave

You can be assured that the table will be created before the insert
statement is executed. Each command will be 100% finished and committed
before moving on to the next statement (unless you using transactions).

Zach|||
David,

Thanks for responding so quickly. I did read in several places that the
GO command does exactly what you have stated.

However, I still need to find a way to make sure that my code is
finished executing before moving to the next step within my stored
procedure.

The example I posted earlier was really dummied down from what I really
need to do. We have some procs here that are over 600 lines of code, and
most of them depend on the previous chunk of code to have completed
before they execute.

Any other ideas would be greatly appreciated.
Thanks,
Dave

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Dave F wrote:

> David,
> Thanks for responding so quickly. I did read in several places that the
> GO command does exactly what you have stated.
> However, I still need to find a way to make sure that my code is
> finished executing before moving to the next step within my stored
> procedure.
> The example I posted earlier was really dummied down from what I really
> need to do. We have some procs here that are over 600 lines of code, and
> most of them depend on the previous chunk of code to have completed
> before they execute.
> Any other ideas would be greatly appreciated.
> Thanks,
> Dave
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Like I said, SQL will ALWAYS complete a command before moving on to the
next one. What makes you think this doesn't already happen?

Zach|||[posted and mailed, please reply in news]

Dave (funkdm1@.yahoo.com) writes:
> Is there any command that makes t-sql halt until the previous command
> has finished?

To echo what Zach said: there is no command that causes T-SQL to continue
with the next command, before the previous has completed. So you
have no reason to worry.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||This is a catch 22 situation.

Do you really need to create a new table each time you execute the stored proc?
If so, use temporary tables.

Otherwise empty your existing table before you insert rows.

"Dynamic" sorting inside a procedure

Hello,
I need to have a stored procedure, which performs sorting. Something like
this:

CREATE PROCEDURE procname
@.sortby varchar(30)
AS
BEGIN
SELECT some, columns
FROM some_table
ORDER BY @.sortby
END

(of course, i know this won't work, but it gives the idea of what i mean)
Is there a possibility to write a procedure which behaves like that? It is
important for me not to have multiple procedures just for different sorting
criteria...

Thanks,
MikeOne method is to build and execute a dynamic SQL statement. For example:

EXEC
(
'SELECT some, columns
FROM some_table
ORDER BY ' + @.sortby
)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Michal Grabowski" <mgrabow1@.elka.pw.edu.pl> wrote in message
news:cf5h33$g1j$1@.julia.coi.pw.edu.pl...
> Hello,
> I need to have a stored procedure, which performs sorting. Something like
> this:
> CREATE PROCEDURE procname
> @.sortby varchar(30)
> AS
> BEGIN
> SELECT some, columns
> FROM some_table
> ORDER BY @.sortby
> END
> (of course, i know this won't work, but it gives the idea of what i mean)
> Is there a possibility to write a procedure which behaves like that? It is
> important for me not to have multiple procedures just for different
sorting
> criteria...
> Thanks,
> Mike|||Michal Grabowski (mgrabow1@.elka.pw.edu.pl) writes:
> CREATE PROCEDURE procname
> @.sortby varchar(30)
> AS
> BEGIN
> SELECT some, columns
> FROM some_table
> ORDER BY @.sortby
> END
> (of course, i know this won't work, but it gives the idea of what i
> mean) Is there a possibility to write a procedure which behaves like
> that? It is important for me not to have multiple procedures just for
> different sorting criteria...

http://www.sommarskog.se/dynamic_sql.html gives some suggestions.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for all tips!

Regards,
Mike

"Drop Table" at Stored Procedure

Hello folks,
i like to change a sql-skript to a procedure. The skript includes 20
statements like
select ... into #temp
and
drop table #temp
It runs as a batch if i use
drop table #temp
go
In order to change it to a procedure i change all "drops" to
drop table #temp;
but i can't create the procedure.
The SQL-Server error message was
'Es gibt bereits ein Objekt #Temp'
(translated 'Object #temp still exists')
This Message was shown five times and point to the Select-Staments (not to
all).
To move the drop - Statement direct before the select does'nt help to solve
this behaivor;
I have no idea to solve this problem.
Thanks in advance for any help.
NielsCREATE PROC mysp
AS
IF OBJECT_ID('TempTable') IS NOT NULL
DROP TABLE TempTable
"nieurig" <nieurig@.discussions.microsoft.com> wrote in message
news:045E2CDA-E8BF-4F55-8668-EC1B47139314@.microsoft.com...
> Hello folks,
> i like to change a sql-skript to a procedure. The skript includes 20
> statements like
> select ... into #temp
> and
> drop table #temp
> It runs as a batch if i use
> drop table #temp
> go
> In order to change it to a procedure i change all "drops" to
> drop table #temp;
> but i can't create the procedure.
> The SQL-Server error message was
> 'Es gibt bereits ein Objekt #Temp'
> (translated 'Object #temp still exists')
> This Message was shown five times and point to the Select-Staments (not to
> all).
> To move the drop - Statement direct before the select does'nt help to
solve
> this behaivor;
> I have no idea to solve this problem.
> Thanks in advance for any help.
> Niels
>|||It's a resolution issue. Consider naming the temporary table differently in
each create. The other option is to use dynamic execution, but such approach
involves many issues of its own.
BG, SQL Server MVP
www.SolidQualityLearning.com
"nieurig" <nieurig@.discussions.microsoft.com> wrote in message
news:045E2CDA-E8BF-4F55-8668-EC1B47139314@.microsoft.com...
> Hello folks,
> i like to change a sql-skript to a procedure. The skript includes 20
> statements like
> select ... into #temp
> and
> drop table #temp
> It runs as a batch if i use
> drop table #temp
> go
> In order to change it to a procedure i change all "drops" to
> drop table #temp;
> but i can't create the procedure.
> The SQL-Server error message was
> 'Es gibt bereits ein Objekt #Temp'
> (translated 'Object #temp still exists')
> This Message was shown five times and point to the Select-Staments (not to
> all).
> To move the drop - Statement direct before the select does'nt help to
> solve
> this behaivor;
> I have no idea to solve this problem.
> Thanks in advance for any help.
> Niels
>|||Thanks to Itzik and Uri !!
I will use the workaround with different names of my temp-table.
Have a nice day.
Niels

"Cursor-Fetch" problem:Oracle2SQL Server Migration

Dear all,

I have a procedure in Oracle that contains the following cursor:

CURSOR SCHED_TRIPS IS
SELECT TRAVELDATE, STOP_NUM, TRIPID, STOP_TYPE, PROMISED_TIME, ETA, PERFORM_TIME, DEPART_TIME, ETD, DRIVERWAIT, PASSENGERWAIT, TRIPTIME, GROUP_ID
FROM Dbo.SCHEDTRIPS_VIEW
WHERE UNQ_ID = SESSION_ID AND TRUNC(TRAVELDATE) = TRUNC(TDATE)
AND DISPOSITION <> 'V';
BEGIN
FOR S IN SCH_TRIPS LOOP
UPDATE dbo.SCHEDULES T
SET T.DIRTYBIT = 1
WHERE T.TRIPID = S.TRIPID AND T.STOP_TYPE = S.STOP_TYPE AND (T.STOP_NUM <> S.STOP_NUM OR T.ETA <> S.ETA);

UPDATE dbo.SCHEDULES T
SET T.STOP_NUM = S.STOP_NUM, T.PROMISED_TIME = S.PROMISED_TIME, T.ETA = S.ETA, T.ETD = S.ETD, T.LAST_CHANGED = SYSDATE
WHERE T.TRIPID = S.TRIPID AND T.STOP_TYPE = S.STOP_TYPE;
END LOOP;
COMMIT ;
END;

My problem is with the line shown in Red. What will be the T-SQL equivalent for this line.

Anxiously waiting for help!Most common loop structure is:

while @.@.fetch_status = 0 begin
...
end

But you'll have to change your UPDATE statement to reference the variables that you're going to be FETCHing the values into, rather than referencing the fields from the cursor. Also, by looking at your JOINs you'll have to implement conditional UPDATE because values from the cursor will correspont to only 1 row at a time, while your current syntax suggests that the cursor now is used as a subquery which will not be possible in SQL. In other words it'll look something like this:declare @.stop_num int, @.tripid int, @.stop_type char(1), @.promised_time datetime, @.eta datetime, @.etd datetime
declare s cursor local for
select STOP_NUM, TRIPID, STOP_TYPE, PROMISED_TIME, ETA, ETD
from Dbo.SCHEDTRIPS_VIEW
where UNQ_ID = SESSION_ID AND convert(char(8), TRAVELDATE, 112) = convert(char(8), TDATE, 112)
open s
fetch next from s into @.stop_num, @.tripid, @.stop_type, @.promised_time, @.etd, @.etd
while @.@.fetch_status = 0 begin
update t
set t.STOP_NUM = @.stop_num,
t.PROMISED_TIME = @.promised_time,
t.ETA = @.eta,
t.ETD = @.etd,
t.LAST_CHANGED = current_timestamp,
t.DIRTYBIT = case when (t.STOP_NUM <> @.stop_num OR t.ETA <> @.eta) then 1 else t.DIRTYBIT end
from dbo.SCHEDULES t
where t.TRIPID = @.tripid AND t.STOP_TYPE = @.stop_type
fetch next from s into @.stop_num, @.tripid, @.stop_type, @.promised_time, @.etd, @.etd
end
deallocate s
close s|||But In this sort of case, native TSQL programmers probably wouldm't use a cursor at all. I would code:

UPDATE T
SET T.DIRTYBIT = 1
FROM dbo.SCHEDULES T,
Dbo.SCHEDTRIPS_VIEW S
WHERE T.TRIPID = S.TRIPID
AND T.STOP_TYPE = S.STOP_TYPE
AND (T.STOP_NUM <> S.STOP_NUM OR T.ETA <> S.ETA)
AND UNQ_ID = SESSION_ID
AND TRUNC(TRAVELDATE) = TRUNC(TDATE)

UPDATE T
SET T.STOP_NUM = S.STOP_NUM,
T.PROMISED_TIME = S.PROMISED_TIME,
T.ETA = S.ETA,
T.ETD = S.ETD,
T.LAST_CHANGED = SYSDATE
FROM dbo.SCHEDULES T,
Dbo.SCHEDTRIPS_VIEW S
WHERE T.TRIPID = S.TRIPID AND
T.STOP_TYPE = S.STOP_TYPE;
AND UNQ_ID = SESSION_ID
AND TRUNC(TRAVELDATE) = TRUNC(TDATE)

Bill|||Thx rdjabarov for going into the intricacies of my proc and giving a detailed reply.
But this was something which I was trying to avoid. Isn't there something similar to Oracle in SQL Server. Else I will have to declare hundreds of vars bcoz this is not the only proc with this style of code.
Moreover, shouldn't Close cursor statement come before deallocation?

Plz do suggest something to overcome my dilemma.

Thx again|||If you want to mimic the PL/SQL cursor style of updates in TSQL, I'm afraid there are no shortcuts.

As you'll be aware, the widespread use of cursors in ORACLE is unavoidable - that's just how you do things like updating one table from another. The particular syntax of the cursor loop in your example is neat PL/SQL shorthand to make cursor loops easier and quicker to code.

There is no equivalent to this shorthand in TSQL. You just have to do it the long way :(

In TSQL (in both MSSQL and Sybase) the use of cursors is widely discouraged, where avoidable. There is a significant overhead in using them that simply isn't there in ORACLE.

I don't know if this might be of some use to you...

http://www.swissql.com/products/oracle-to-sqlserver/index.html

Bill|||Actually the overhead associated with cursors also exists in Horacle. It's just the latter is usually run on monsterous hardware that can handle sloppy coding and poor design. SQL Server is running in prod environment on machines that are several times (sometimes a dozen or more) cheaper, and every intelligent attempt to optimize a process brings a reward in improved performance.|||But In this sort of case, native TSQL programmers probably wouldm't use a cursor at all...TSQL programmers would also rewrite it into 1 update and convert the Horacle style into ANSI ;)

I just tried to retain the structure as it was presented in the post, that also included the use of cursor.|||Thx guys for the tips,

rdjabarov, why "Horacle"?

thompbil, I have already used the link that u kindly pointed out. Didn't find the results satisfactory. Thx all the same. Another thing, besides the marginal loss in performance by using Cursors, what other overheads can I expect? Moreover, what cud be a substitute for cursors, if the overheads are significant?

Accepted that SQL Server is user friendly, but I think it is miles behind in "usefulness" as compared to Oracle. My original post is a case inpoint. Just imagine the lengths that I will have to go to achieve what has been accomplished so simply in Oracle.
Date functions of Oracle is another feather in Oracle's cap if we put these 2 RDBMSs head-to-head.

So, whatsay? (Is it a pandora's box I am opening here or what?)|||Thx guys for the tips,

Accepted that SQL Server is user friendly, but I think it is miles behind in "usefulness" as compared to Oracle. My original post is a case inpoint. Just imagine the lengths that I will have to go to achieve what has been accomplished so simply in Oracle.
Date functions of Oracle is another feather in Oracle's cap if we put these 2 RDBMSs head-to-head.

So, whatsay? (Is it a pandora's box I am opening here or what?)

They are just different. SQL Server does some things better than ORACLE. ORACLE does some things better than SQL Server.
You could say that the "UPDATE...FROM..." construct (as in my original reply) is even neater than the PL/SQL cursor update example you originally cited. I think so...but that's just an opinion.|||Man, just wait till Yukon comes out, - talking about Horacle...|||Yukon! Horacle! Whoa.. What? Who? When?

Duhh...?|||Yukon! Horacle! Whoa.. What? Who? When?

Duhh...?Yukon is the project name for the next version of SQL Server (either 9.0 or SQL 2005, depending on your point of view).

Horacle is an often used rdjabarovism for Oracle.

-PatP