3G ram for SQL server: figure

Showing posts with label figure. Show all posts

Tuesday, March 27, 2012

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...
>

Sunday, March 25, 2012

A couple of questions

Well, I had a questions, but I figured I'd include all of the things I've been trying to figure out.

I remember reading in books online about transformnig the data returned by a SQL query. For example, the database has a boolean 1 or 0, but I want to return a Yes / No or True / False. You could even doa number mapping for example, 1 becomes "one", 2 becomes "two" etc. Can anyone point me in the right direction?

Is there a way to check a data field for a specific format? SELECT IDNumber from table WHERE IDNumber is not 6 numberic digits. (IDNumber is nvarchar with a length of 50 and stores IDNUmbers that also contain letters and special characters)

Query returns 123456789, 12345678, 2-0152, A5487, but not 123456 or 101254, etc.

How do I insert several lines if I have the data as text? I can insert multiple lines from another table, but how do I insert multiple lines if I want to type in the data?

How do I insert the same line into several SQL database on different computers using query analyzer? I basically have to enter a line for each user into the user table. I can do it with osql, but I don't know haw to change the server in query analyzer. With query analyzer I can create a script and just substitute the variables defined at the beginning.

Thanks,

Cory

1. To 'transform' data, use the CASE structure.

Code Snippet

SELECT Gender = CASE Sex

WHEN 1 THEN 'M'

WHEN 0 THEN 'F'

END

FROM MyTable.

2. To check for specific format, i.e., 6 character number, something like this 'should' work for you (it's not perfect, but I think it may give you what you desire):

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table
( IDNumber varchar(50) )

INSERT INTO @.MyTable VALUES ( '123456789' )
INSERT INTO @.MyTable VALUES ( '12345678' )
INSERT INTO @.MyTable VALUES ( '2-0152' )
INSERT INTO @.MyTable VALUES ( 'A5487' )
INSERT INTO @.MyTable VALUES ( 'A54871' )
INSERT INTO @.MyTable VALUES ( '123456' )
INSERT INTO @.MyTable VALUES ( '101254' )

SELECT IDNumber
FROM @.MyTable
WHERE ( len( IDNumber ) <> 6
OR isnumeric( IDNumber ) = 0
)

IDNumber
--
123456789
12345678
2-0152
A5487
A54871

How do I insert several lines if I have the data as text?

I don't understand what you want to do here, please give more information.

4. Click on [File], [Connect], to connect to a different server. Open your script file, or cut/paste from the previous connection window.

(OR)

Set the additional Servers up as 'linked servers'. Refer to Books Online, Topic: 'Linked Server', sp_addlinkedserver.

Then you can use four object part naming to designate the server.

|||

Hi Arnie,

May be we can use following statement for point number 2.

select

from

@.MyTable

where

IDNumber notlike'[0-9][0-9][0-9][0-9][0-9][0-9]'

AMB

|||

Thank you for your quick reply.

For #3. In your example you used 7 insert statements to insert the rows into the table. Is there a way to insert them using one statement?

For #4. Is there a way to do it without adding linked servers? In osql, I type

osql -S 192.168.1.123 -E -d databasename -Q "INSERT INTO USERS(username, password) VALUES ("name", "pw")

osql -S 192.168.1.124 -E -d databasename -Q "INSERT INTO USERS(username, password) VALUES ("name", "pw")

osql -S 192.168.1.125 -E -d databasename -Q "INSERT INTO USERS(username, password) VALUES ("name", "pw")

osql -S 192.168.1.126 -E -d databasename -Q "INSERT INTO USERS(username, password) VALUES ("name", "pw")

Is there a way to do it in query analyzer without linked servers.

Thanks,

Cory

Thursday, March 22, 2012

A Challenging Question

How does one figure out, I mean compare the results after some changes
have been done to the SQL Server, I mean the method in which the
comparision is done, lets say I have a query I ran it it gave me the
results in 40 seconds , I ran it again this time it took 30 seconds,
then I again ran it it took 35 seconds, I then created an index , this
time the query ran in 30 seconds ... how does one compare such things ,
I mean i need to give stats as too what kind of performace has takern
place ... please helpo , I need to knw as a DBa how would you convince
your maanger that becasue of some changes the performance has improved,
cause when you ask the users they say its ok , we dont see the
differnece & stuff , please help.This is a multi-part message in MIME format.
--060509040907050200030402
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
If you don't see any difference in response times then the change has
probably not improved the performance at all.
If I was trying to ascertain whether a new index made a difference or
not I would look at the query execution plan before & after the index
was created to see if the new index is now getting used or not. I would
also run the query with "statistics io" and "statistics time" on before
& after the index was created to see what real difference it makes to IO
and elapsed time.
--
*mike hodgson*
http://sqlnerd.blogspot.com
Double_B wrote:
>How does one figure out, I mean compare the results after some changes
>have been done to the SQL Server, I mean the method in which the
>comparision is done, lets say I have a query I ran it it gave me the
>results in 40 seconds , I ran it again this time it took 30 seconds,
>then I again ran it it took 35 seconds, I then created an index , this
>time the query ran in 30 seconds ... how does one compare such things ,
>I mean i need to give stats as too what kind of performace has takern
>place ... please helpo , I need to knw as a DBa how would you convince
>your maanger that becasue of some changes the performance has improved,
>cause when you ask the users they say its ok , we dont see the
>differnece & stuff , please help.
>
>
--060509040907050200030402
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>If you don't see any difference in response times then the change
has probably not improved the performance at all. 
 
If I was trying to ascertain whether a new index made a difference or
not I would look at the query execution plan before & after the
index was created to see if the new index is now getting used or not.
I would also run the query with "statistics io" and "statistics time"
on before & after the index was created to see what real difference
it makes to IO and elapsed time. 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
<a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Double_B wrote:
<blockquote
cite="mid1140406834.987103.198470@.o13g2000cwo.googlegroups.com"
type="cite">
<pre wrap="">How does one figure out, I mean compare the results after some changes
have been done to the SQL Server, I mean the method in which the
comparision is done, lets say I have a query I ran it it gave me the
results in 40 seconds , I ran it again this time it took 30 seconds,
then I again ran it it took 35 seconds, I then created an index , this
time the query ran in 30 seconds ... how does one compare such things ,
I mean i need to give stats as too what kind of performace has takern
place ... please helpo , I need to knw as a DBa how would you convince
your maanger that becasue of some changes the performance has improved,
cause when you ask the users they say its ok , we dont see the
differnece & stuff , please help.
</pre>
</blockquote>
</body>
</html>
--060509040907050200030402--|||But then at times seeing the time the query gets executed also keeps
differing, sometimes a query takes 10 sec , the same may take 12 or 15
secs the next times its executed, ...het does anyone have a document
that really explain you the execution plan & how to read the estimated
time the plan takes & figure out whats the issue & how to tackle it
Thanks,|||This is a multi-part message in MIME format.
--070908070206060606050806
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
If you post the execution plan (& the schema for the tables involved) we
may be able to help you. Response times tend to vary a little due to
unrelated (or at least not directly related) things such as query plan
compilation/recompilation, I/O waits (busy disks), lock & latch waits
(waiting for another connection to release the locks on the indexes you
want to access), other apps hogging CPU cycles, etc.
As for documentation on execution plans, Kalen Delaney wrote an
excellent book about 5 years ago called Inside SQL Server 2000
<http://www.amazon.com/gp/product/0735609985/sr=8-1/qid=1140760885/ref=pd_bbs_1/104-5347882-5925515?%5Fencoding=UTF8>.
It has a couple good chapters in it on the query processor (Ch15) and
query tuning (Ch16) that may clarify things for you. If not then it's
still a very worthwhile volume to have on your professional bookshelf if
you're serious about SQL Server.
--
*mike hodgson*
http://sqlnerd.blogspot.com
Double_B wrote:
>But then at times seeing the time the query gets executed also keeps
>differing, sometimes a query takes 10 sec , the same may take 12 or 15
>secs the next times its executed, ...het does anyone have a document
>that really explain you the execution plan & how to read the estimated
>time the plan takes & figure out whats the issue & how to tackle it
>Thanks,
>
>
--070908070206060606050806
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>If you post the execution plan (& the schema for the tables
involved) we may be able to help you. Response times tend to vary a
little due to unrelated (or at least not directly related) things such
as query plan compilation/recompilation, I/O waits (busy disks), lock
& latch waits (waiting for another connection to release the locks
on the indexes you want to access), other apps hogging CPU cycles, etc. 
 
As for documentation on execution plans, Kalen Delaney wrote an
excellent book about 5 years ago called <a
href="http://links.10026.com/?link=Inside">http://www.amazon.com/gp/product/0735609985/sr=8-1/qid=1140760885/ref=pd_bbs_1/104-5347882-5925515?%5Fencoding=UTF8">Inside
SQL Server 2000</a>. It has a couple good chapters in it on the query
processor (Ch15) and query tuning (Ch16) that may clarify things for
you. If not then it's still a very worthwhile volume to have on your
professional bookshelf if you're serious about SQL Server.</tt> 
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
<a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Double_B wrote:
<blockquote
cite="mid1140752342.020403.82750@.p10g2000cwp.googlegroups.com"
type="cite">
<pre wrap="">But then at times seeing the time the query gets executed also keeps
differing, sometimes a query takes 10 sec , the same may take 12 or 15
secs the next times its executed, ...het does anyone have a document
that really explain you the execution plan & how to read the estimated
time the plan takes & figure out whats the issue & how to tackle it
Thanks,
</pre>
</blockquote>
</body>
</html>
--070908070206060606050806--|||Thanks a lot

Tuesday, March 27, 2012

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

Sunday, March 25, 2012

A couple of questions

Thursday, March 22, 2012

A Challenging Question

A Challenging Question

A Challenging Question

3G ram for SQL server

Blog Archive

About Me