Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

What does it mean for these MS SQL Statements?

Posted on 2005-05-10
4
Medium Priority
?
446 Views
Last Modified: 2008-03-03
Hi,

I got the following SQL statements from 2 differnt examples:

//--------------
USE pubs
DROP TABLE #coffeetabletitles
GO
SET NOCOUNT ON   <----------------
SELECT * INTO #coffeetabletitles
FROM titles
WHERE price < $20
SET NOCOUNT OFF  <-----------------
SELECT name
FROM tempdb..sysobjects
WHERE name LIKE '#c%'
//-----------------

From the above example, I don't know what does it mean with (1) SET NOCOUNT ON and (2) SET NOCOUNT OFF
And these statements are there for what?


// Another example
//-----------------
USE pubs
IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES
      WHERE table_name = 'newtitles')
      DROP TABLE newtitles
GO
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'
USE pubs
SELECT * INTO newtitles
FROM titles
WHERE price > $25 OR price < $20
SELECT name FROM sysobjects WHERE name LIKE 'new%'
USE master                                                                       <----------------------------
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'
//-----------------

Why we have to use the statement: USE master before th statment of "EXEC ... , 'false'\
Why we don't have it before th first EXEC statement

Thanks a lot for any help,

Rfr1tz







0
Comment
Question by:rfr1tz
4 Comments
 
LVL 97

Assisted Solution

by:Lee W, MVP
Lee W, MVP earned 300 total points
ID: 13975008
NO COUNT:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_3ed0.asp


For the latter question, you're switching databases and using a different stored procedure.
0
 
LVL 9

Assisted Solution

by:cyberdevil67
cyberdevil67 earned 160 total points
ID: 13975020
Hi rfr1tz,

 Set NoCount On / Off sets Sql to return rows affected

Cheers!
0
 
LVL 11

Accepted Solution

by:
andrewbleakley earned 400 total points
ID: 13975079
From the SQL Server Books online:
----------------------------------------
When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) is not returned. When SET NOCOUNT is OFF, the count is returned.
-----------------------------------------

Basically it stops you having to see messages like (26554 row(s) affected) in the messages window of Query Analyser

The second statement USE Master changes the database you are using from pubs (the first line of this procedure) to master.
It is useful if you have a query that references multiple databases and you need to move between them in the execution of your script. You can achieve the same effect by using the database.owner.object sysntax when refering to objects
0
 
LVL 8

Assisted Solution

by:Julianva
Julianva earned 140 total points
ID: 13975433
Stops the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results.

When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) is not returned. When SET NOCOUNT is OFF, the count is returned.

Its indicating that you are using another database - which has the data you looking for



0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question