block comment??

Is there a block comment character that I can use in my SQL script files to
comment large sections of SQL code? Something like /* ... */.
Thanks.
-b"Barry" <Barry@.discussions.microsoft.com> wrote in message
news:452FF1A6-BE3E-4292-9A04-D8AF07908794@.microsoft.com...
> Is there a block comment character that I can use in my SQL script files
> to
> comment large sections of SQL code? Something like /* ... */.
> Thanks.
> -b
This isn't a joke, is it?
From Books OnLine:
/*...*/ (Comment)
Indicates user-provided text. The text between the /* and */ commenting
characters is not evaluated by the server.|||You have is the right commenting characters /*...*/ there for multi-line
block commenting.
( Also in QA, you can select a large chunk of code & press Ctrl+Shft+C to
comment it & Ctrl+Shift+R to remove it. )
Anith|||That's what I thought it meant too, but when I created a SQL script using
this to comment out a section of code that is not yet fully debugged and the
n
ran the script file, I got several errors from code inside the block comment
.
The specific commands were dealing with foreign key constraints, but they
were within the block comment.
Is that any problem with nesting line comments ( '--') inside a block
comment?
-b
"Raymond D'Anjou" wrote:

> "Barry" <Barry@.discussions.microsoft.com> wrote in message
> news:452FF1A6-BE3E-4292-9A04-D8AF07908794@.microsoft.com...
> This isn't a joke, is it?
> From Books OnLine:
> /*...*/ (Comment)
> Indicates user-provided text. The text between the /* and */ commenting
> characters is not evaluated by the server.
>
>|||I can't see what would cause the problems.
Did you highlight some code before running the script?
Mixing block and line comments does not generate an error (that I know of).
Go alone on a line throws an error:
/*comments
GO
comments*/
Trying to nest block comments doesn't work either:
/*comment
/*comment2*/
comment*/
"Barry" <Barry@.discussions.microsoft.com> wrote in message
news:045BF8F8-3EDE-44C9-8F69-AEBAB5FB6936@.microsoft.com...
> That's what I thought it meant too, but when I created a SQL script using
> this to comment out a section of code that is not yet fully debugged and
> then
> ran the script file, I got several errors from code inside the block
> comment.
> The specific commands were dealing with foreign key constraints, but they
> were within the block comment.
> Is that any problem with nesting line comments ( '--') inside a block
> comment?
> -b
> "Raymond D'Anjou" wrote:
>|||Barry
If you just commented out a whole block of code, you maybe have a GO
somewhere in there. This can cause problems.
The following is from Inside SQL Server 2000:
---
GO isn't an SQL command or keyword. It's the end-of-batch signal understood
only by certain client tools. The client interprets it to mean that
everything since the last GO should be sent to the server for execution. SQL
Server never sees the GO command and has no idea what it means. With a
custom application, a batch is executed with a single SQLExecute from ODBC
(or dbsqlexec from DB-Library).
If you include the GO command in the query window, SQL Query Analyzer breaks
up your statement into the indicated batches behind the scenes. Each batch
(as marked by the GO command) is sent individually to SQL Server.
A collection of batches that are frequently executed together is sometimes
called a script. Most of the client tools provide a mechanism for loading a
script that you've saved to a text file and for executing it. In SQL Query
Analyzer, you can use the File/Open command to load a script. From the
command-line OSQL or ISQL programs, we can specify the /i flag followed by a
filename to indicate that the SQL Server batches to execute should come from
the specified file. Alternatively, if we're using OSQL or ISQL
interactively, we can read in a file containing one or more SQL statement by
typing :r followed by the filename. (See SQL Server Books Online for details
about using OSQL and ISQL.)
The fact that the client tool and not SQL Server processes GO can lead to
some unexpected behavior. Suppose you have a script containing several
batches. During testing, you want to comment out a couple of the batches to
ignore them for the time being. Your commented script might look something
like this:
SELECT * FROM authors
/*
GO
SELECT * FROM sales
GO
SELECT * FROM publishers
GO
*/
SELECT * FROM titles
GO
The intention here was to comment out the SELECT from the sales and
publishers tables and to run the SELECT from authors and titles as a single
batch. However, if you run this script from SQL Query Analyzer, you'll get
exactly the opposite behavior! That is, you'll see the data from the sales
and publishers tables but not from authors or titles. If you look at this
script from the perspective of the client tool, the behavior makes sense.
The tool doesn't try to interpret any of your SQL statements; it just breaks
the statements into batches to be sent to SQL Server. A batch is marked by a
GO command at the beginning of a line.
So the above script contains four batches. The first batch (everything
before the first GO) is:
SELECT * FROM authors
/*
SQL Server generates an error message because there's an open comment with
no corresponding close comment.
The second and third batches are:
SELECT * FROM sales
and
SELECT * FROM publishers
Both of these batches are perfectly legal, and SQL Server can process them
and return results.
The fourth batch is:
*/
SELECT * FROM titles
SQL Server also generates an error for this last one because it has a close
comment without an open comment marker, and no data is returned.
If you want to comment out statements within a script that can contain the
end-of-batch GO command, you should use the alternative comment marker-the
double dash-in front of every GO. Alternatively, you can just use the double
dash in front of every line you want to comment out. Your script would then
look like this:
SELECT * FROM authors
-- GO
-- SELECT * FROM sales
-- GO
-- SELECT * FROM publishers
-- GO
SELECT * FROM titles
GO
SQL Query Analyzer makes it easy to comment out a group of lines as in the
code above. You can highlight all the lines to be commented out, and from
the Edit menu, choose Advanced/Comment Out. The keystroke combination to
perform the same action is Ctrl-Shift-C. With this revised script, the
client tool won't recognize the GO as the end-of-batch marker because it's
not the first thing on a line. The client will consider this script to be
one single batch and send it to SQL Server as such.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Barry" <Barry@.discussions.microsoft.com> wrote in message
news:045BF8F8-3EDE-44C9-8F69-AEBAB5FB6936@.microsoft.com...
> That's what I thought it meant too, but when I created a SQL script using
> this to comment out a section of code that is not yet fully debugged and
> then
> ran the script file, I got several errors from code inside the block
> comment.
> The specific commands were dealing with foreign key constraints, but they
> were within the block comment.
> Is that any problem with nesting line comments ( '--') inside a block
> comment?
> -b
> "Raymond D'Anjou" wrote:
>
>|||I've verified that there are no improperly commented GO statements. When I
rerun the script the error comes after several other similar calls from
inside the comment block (1034 lines into the block of 1137 lines). Is it
possible that these comment blocks are too long?
"Kalen Delaney" wrote:

> Barry
> If you just commented out a whole block of code, you maybe have a GO
> somewhere in there. This can cause problems.
> The following is from Inside SQL Server 2000:
> ---
> GO isn't an SQL command or keyword. It's the end-of-batch signal understoo
d
> only by certain client tools. The client interprets it to mean that
> everything since the last GO should be sent to the server for execution. S
QL
> Server never sees the GO command and has no idea what it means. With a
> custom application, a batch is executed with a single SQLExecute from ODBC
> (or dbsqlexec from DB-Library).
> If you include the GO command in the query window, SQL Query Analyzer brea
ks
> up your statement into the indicated batches behind the scenes. Each batch
> (as marked by the GO command) is sent individually to SQL Server.
> A collection of batches that are frequently executed together is sometimes
> called a script. Most of the client tools provide a mechanism for loading
a
> script that you've saved to a text file and for executing it. In SQL Query
> Analyzer, you can use the File/Open command to load a script. From the
> command-line OSQL or ISQL programs, we can specify the /i flag followed by
a
> filename to indicate that the SQL Server batches to execute should come fr
om
> the specified file. Alternatively, if we're using OSQL or ISQL
> interactively, we can read in a file containing one or more SQL statement
by
> typing :r followed by the filename. (See SQL Server Books Online for detai
ls
> about using OSQL and ISQL.)
> The fact that the client tool and not SQL Server processes GO can lead to
> some unexpected behavior. Suppose you have a script containing several
> batches. During testing, you want to comment out a couple of the batches t
o
> ignore them for the time being. Your commented script might look something
> like this:
> SELECT * FROM authors
> /*
> GO
> SELECT * FROM sales
> GO
> SELECT * FROM publishers
> GO
> */
> SELECT * FROM titles
> GO
> The intention here was to comment out the SELECT from the sales and
> publishers tables and to run the SELECT from authors and titles as a singl
e
> batch. However, if you run this script from SQL Query Analyzer, you'll get
> exactly the opposite behavior! That is, you'll see the data from the sales
> and publishers tables but not from authors or titles. If you look at this
> script from the perspective of the client tool, the behavior makes sense.
> The tool doesn't try to interpret any of your SQL statements; it just brea
ks
> the statements into batches to be sent to SQL Server. A batch is marked by
a
> GO command at the beginning of a line.
> So the above script contains four batches. The first batch (everything
> before the first GO) is:
> SELECT * FROM authors
> /*
> SQL Server generates an error message because there's an open comment with
> no corresponding close comment.
> The second and third batches are:
> SELECT * FROM sales
> and
> SELECT * FROM publishers
> Both of these batches are perfectly legal, and SQL Server can process them
> and return results.
> The fourth batch is:
> */
> SELECT * FROM titles
> SQL Server also generates an error for this last one because it has a clos
e
> comment without an open comment marker, and no data is returned.
> If you want to comment out statements within a script that can contain the
> end-of-batch GO command, you should use the alternative comment marker-the
> double dash-in front of every GO. Alternatively, you can just use the doub
le
> dash in front of every line you want to comment out. Your script would the
n
> look like this:
> SELECT * FROM authors
> -- GO
> -- SELECT * FROM sales
> -- GO
> -- SELECT * FROM publishers
> -- GO
> SELECT * FROM titles
> GO
> SQL Query Analyzer makes it easy to comment out a group of lines as in the
> code above. You can highlight all the lines to be commented out, and from
> the Edit menu, choose Advanced/Comment Out. The keystroke combination to
> perform the same action is Ctrl-Shift-C. With this revised script, the
> client tool won't recognize the GO as the end-of-batch marker because it's
> not the first thing on a line. The client will consider this script to be
> one single batch and send it to SQL Server as such.
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Barry" <Barry@.discussions.microsoft.com> wrote in message
> news:045BF8F8-3EDE-44C9-8F69-AEBAB5FB6936@.microsoft.com...
>
>|||OK - Here is what I've narrowed it down to.
If I comment out (using block comments) anything in the script except
another '*' character, it seems to work. However when the commented section
includes a '*' it causes the code to uncomment.
For example the following generated an error
/*
-- if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_PolicySectionMap_policyFileID]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
*/
but changing the '*' to 'id' did not.
I decided to use QA to comment the block with line comments instead.
-b
"Barry" wrote:
> I've verified that there are no improperly commented GO statements. When
I
> rerun the script the error comes after several other similar calls from
> inside the comment block (1034 lines into the block of 1137 lines). Is it
> possible that these comment blocks are too long?
> "Kalen Delaney" wrote:
>|||Barry
This seems very strange.
This batch works for me:
USE pubs
select * from authors
/*
select * from titles
*/
select * from publishers
What exact version are you using? Are you getting the error using QA?
Thanks
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Barry" <Barry@.discussions.microsoft.com> wrote in message
news:846D3E3A-34D8-47EF-A22D-CAC114D7CCB8@.microsoft.com...
> OK - Here is what I've narrowed it down to.
> If I comment out (using block comments) anything in the script except
> another '*' character, it seems to work. However when the commented
> section
> includes a '*' it causes the code to uncomment.
> For example the following generated an error
> /*
> -- if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_PolicySectionMap_policyFileID]') and
> OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> */
> but changing the '*' to 'id' did not.
> I decided to use QA to comment the block with line comments instead.
> -b
> "Barry" wrote:
>
>|||Did you copy this SQL into notepad (or another 3rd party editor) and then
paste back into Enterprise Manager or Query Analyzer? There is a very rare
(but insideous) bug at least in SQL Server 2000 where a line ending in a
CRTL byte but no LF byte (or perhaps vice versa) will look normal when
viewed in QA or EM, but when executed, the SQL interpreter will think there
is no new line.
For example:
Here is what it looks like when viewed in Enterprise Manager:
-- Here is a comment
print 'Hello World'
Here is what gets executed:
-- Here is a comment print 'Hello World'
"Barry" <Barry@.discussions.microsoft.com> wrote in message
news:145BDC63-89E3-4979-9377-29199B3A10EB@.microsoft.com...
> I've verified that there are no improperly commented GO statements. When
> I
> rerun the script the error comes after several other similar calls from
> inside the comment block (1034 lines into the block of 1137 lines). Is it
> possible that these comment blocks are too long?
> "Kalen Delaney" wrote:
>

Blank character in SQL table

Hello,
I have a field in database table called Person_name(varchar). The
Problem is that when the user enters something like this: Mr. Sam,
where there is a blank space after the dot, it displays only Mr. It
ignores any letters after white space. How can I change this, when
using update statement.
Regards
Divadivaof_7@.yahoo.com wrote:
> Hello,
> I have a field in database table called Person_name(varchar). The
> Problem is that when the user enters something like this: Mr. Sam,
> where there is a blank space after the dot, it displays only Mr. It
> ignores any letters after white space. How can I change this, when
> using update statement.
> Regards
> Diva
>
I'm not sure this is a SQL problem. You should check the client
application to see if it's doing some sort of parsing that is dropping
anything after the first space. Use SQL Profiler to capture the actual
INSERT statement coming from the app, I'll bet it's inserting "Mr."
instead of "Mr. Sam".
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks
Can you briefly explain the idea of using SQL profiler, since I've
never dealt with the same
Tracy McKibben wrote:
> divaof_7@.yahoo.com wrote:
> I'm not sure this is a SQL problem. You should check the client
> application to see if it's doing some sort of parsing that is dropping
> anything after the first space. Use SQL Profiler to capture the actual
> INSERT statement coming from the app, I'll bet it's inserting "Mr."
> instead of "Mr. Sam".
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||divaof_7@.yahoo.com wrote:
> Thanks
> Can you briefly explain the idea of using SQL profiler, since I've
> never dealt with the same
Profiler is used to trace the activity that is taking place in your
databases, I would HIGHLY recommend familiarizing yourself with it if
you're going to be working with SQL Server.
Essentially what you want to do is capture every INSERT statement that
is being issued against your table that is having problems. You can
either capture everything and sort through it manually, looking for
INSERT statements to that table, or you can use the filtering
capabilities of Profiler to capture only those statements.
Consult Books Online for further details.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Bit or character data type?

SQL2K
SP4
Im creating a DB that has several Bit coulmns. The data is coming from a
mainframe. When one stored proc is run, the result set needs to have a "y",
an "n", or a " '' " (space) as thats the way current systems work. Another
proc needs to return "Eligible" or "Not eligible". I had originally thought
of using a Bit fields, but now I'm wondering why. It seems to me that if I
use Bit, I then need to convert it to display one of the things just listed.
Whereas if I use character coulmns and add check constraints to the to make
sure only certain values can be added, that conversion doesnt need to occur
when the data is being retrieved from the DB. Id have to imagine Im not the
first one to think of this as no app will probably ever return a 1 or a 0, s
o
Im wondering what others thoughts on the matter are?A couple of things to consider, a bit column by itself takes up one byte.
If eight of them are together in a table you can save some space. To put
"Not eligible" takes 12 or 14 bytes depending on char or varchar. Next bit
has a special behavior that says any integer other than 0 is 1. In the end
I like to format results in the reporting tool so I would generally choose a
tinyint with a check constraint. But in the case of 'y', 'n', or space
char(1) would be pretty effective.
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:57F596FC-365B-4EF5-907E-041498A0D11B@.microsoft.com...
> SQL2K
> SP4
> Im creating a DB that has several Bit coulmns. The data is coming from a
> mainframe. When one stored proc is run, the result set needs to have a
> "y",
> an "n", or a " '' " (space) as thats the way current systems work. Another
> proc needs to return "Eligible" or "Not eligible". I had originally
> thought
> of using a Bit fields, but now I'm wondering why. It seems to me that if I
> use Bit, I then need to convert it to display one of the things just
> listed.
> Whereas if I use character coulmns and add check constraints to the to
> make
> sure only certain values can be added, that conversion doesnt need to
> occur
> when the data is being retrieved from the DB. Id have to imagine Im not
> the
> first one to think of this as no app will probably ever return a 1 or a 0,
> so
> Im wondering what others thoughts on the matter are?|||Chris,
Eight or less bit columns willl occupy only one byte, therefore the bit type
should be the prefered choice if you have multiple such columns in the same
table. From SQL Server books on-line:
"Microsoft SQL ServerT optimizes the storage used for bit columns. If there
are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If
there are from 9 through 16 bit columns, they are stored as 2 bytes, and so
on."
http://msdn.microsoft.com/library/d...br />
2it0.asp
Leo
"Danny" <nomailbox@.nowhere.com> wrote in message
news:Uiqfg.13100$lN5.1551@.trnddc04...
>A couple of things to consider, a bit column by itself takes up one byte.
>If eight of them are together in a table you can save some space. To put
>"Not eligible" takes 12 or 14 bytes depending on char or varchar. Next bit
>has a special behavior that says any integer other than 0 is 1. In the end
>I like to format results in the reporting tool so I would generally choose
>a tinyint with a check constraint. But in the case of 'y', 'n', or space
>char(1) would be pretty effective.
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:57F596FC-365B-4EF5-907E-041498A0D11B@.microsoft.com...
>

Bit or character data type?

SQL2K
SP4
Im creating a DB that has several Bit coulmns. The data is coming from a
mainframe. When one stored proc is run, the result set needs to have a "y",
an "n", or a " '' " (space) as thats the way current systems work. Another
proc needs to return "Eligible" or "Not eligible". I had originally thought
of using a Bit fields, but now I'm wondering why. It seems to me that if I
use Bit, I then need to convert it to display one of the things just listed.
Whereas if I use character coulmns and add check constraints to the to make
sure only certain values can be added, that conversion doesnt need to occur
when the data is being retrieved from the DB. Id have to imagine Im not the
first one to think of this as no app will probably ever return a 1 or a 0, so
Im wondering what others thoughts on the matter are?A couple of things to consider, a bit column by itself takes up one byte.
If eight of them are together in a table you can save some space. To put
"Not eligible" takes 12 or 14 bytes depending on char or varchar. Next bit
has a special behavior that says any integer other than 0 is 1. In the end
I like to format results in the reporting tool so I would generally choose a
tinyint with a check constraint. But in the case of 'y', 'n', or space
char(1) would be pretty effective.
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:57F596FC-365B-4EF5-907E-041498A0D11B@.microsoft.com...
> SQL2K
> SP4
> Im creating a DB that has several Bit coulmns. The data is coming from a
> mainframe. When one stored proc is run, the result set needs to have a
> "y",
> an "n", or a " '' " (space) as thats the way current systems work. Another
> proc needs to return "Eligible" or "Not eligible". I had originally
> thought
> of using a Bit fields, but now I'm wondering why. It seems to me that if I
> use Bit, I then need to convert it to display one of the things just
> listed.
> Whereas if I use character coulmns and add check constraints to the to
> make
> sure only certain values can be added, that conversion doesnt need to
> occur
> when the data is being retrieved from the DB. Id have to imagine Im not
> the
> first one to think of this as no app will probably ever return a 1 or a 0,
> so
> Im wondering what others thoughts on the matter are?|||Chris,
Eight or less bit columns willl occupy only one byte, therefore the bit type
should be the prefered choice if you have multiple such columns in the same
table. From SQL Server books on-line:
"Microsoft® SQL ServerT optimizes the storage used for bit columns. If there
are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If
there are from 9 through 16 bit columns, they are stored as 2 bytes, and so
on."
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_2it0.asp
Leo
"Danny" <nomailbox@.nowhere.com> wrote in message
news:Uiqfg.13100$lN5.1551@.trnddc04...
>A couple of things to consider, a bit column by itself takes up one byte.
>If eight of them are together in a table you can save some space. To put
>"Not eligible" takes 12 or 14 bytes depending on char or varchar. Next bit
>has a special behavior that says any integer other than 0 is 1. In the end
>I like to format results in the reporting tool so I would generally choose
>a tinyint with a check constraint. But in the case of 'y', 'n', or space
>char(1) would be pretty effective.
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:57F596FC-365B-4EF5-907E-041498A0D11B@.microsoft.com...
>> SQL2K
>> SP4
>> Im creating a DB that has several Bit coulmns. The data is coming from a
>> mainframe. When one stored proc is run, the result set needs to have a
>> "y",
>> an "n", or a " '' " (space) as thats the way current systems work.
>> Another
>> proc needs to return "Eligible" or "Not eligible". I had originally
>> thought
>> of using a Bit fields, but now I'm wondering why. It seems to me that if
>> I
>> use Bit, I then need to convert it to display one of the things just
>> listed.
>> Whereas if I use character coulmns and add check constraints to the to
>> make
>> sure only certain values can be added, that conversion doesnt need to
>> occur
>> when the data is being retrieved from the DB. Id have to imagine Im not
>> the
>> first one to think of this as no app will probably ever return a 1 or a
>> 0, so
>> Im wondering what others thoughts on the matter are?
>