Boolean value

How can I return boolean value to the client.
This doesn't work:
SELECT column1=case when exists (select ...) then TRUE else FALSE end,...
If I return :
SELECT column1=case when exists (select ...) then 1 else 0 end,...
then client gets the int32 value
regards,S> How can I return boolean value to the client.
You can't. There is no Boolean datatype in TSQL. There is a bit datatype, wh
ich is a integer-class
datatype restricted to the values 0, 1 and NULL. Some libraries (ADO etc) wi
ll interpret 1 as TRUE
and 0 as FALSE. If you want to return but, you have to CAST. for example
SELECT case when exists (select ...) then CAST(1 AS bit) else CAST(0 AS bit)
end AS column1
Or you can do the CAST outside the CASE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"simon" <simon.zupan@.stud-moderna.si> wrote in message news:O3fYef9oFHA.3256@.tk2msftngp13.p
hx.gbl...
> How can I return boolean value to the client.
> This doesn't work:
> SELECT column1=case when exists (select ...) then TRUE else FALSE end,...
> If I return :
> SELECT column1=case when exists (select ...) then 1 else 0 end,...
> then client gets the int32 value
> regards,S
>|||There's no boolean data type in SQL. I'd suggest using the bit data type.
Like this:
ELECT column1=case when exists (select ...) then cast(1 as bit) else cast(0
as bit) end
ML

Boolean Report Parameters

Hi, I have created a report parameter using a Boolean data type.

When I preview my Report I can select either True or False for this parameter. Is there any way of changing the 'True' label to say something else and again the same with the 'False' label?

Click on Report-Parameters

Select your parameter and go down to Available Values

Type in a label and the value

ex.

Open - True

Closed - False

Simone

|||This brings the values up in a list box, I was hoping to keep the selection as tick boxes but amend 'True' to say 'Open' for example.|||

I ran into the same issue today when I tried to change "String" parameter to "Boolean", and hoping to have radiobox instead of listbox dropdown.

No good, it'd be boolean type with dropdown, which is the same as "String" type essentially

It'd be nice to able to change label for "True" and "False"

Booking database - query

Hi,

I need to write a qry for sql 7/ASP to select a 72hr X 5 matrix of 1/2 hr periods - values need to show booked/unbooked for each of 5 cars for all 72 hours. The problem is the database only stores records for the booked periods.

The query (below) works ok for 1 car & one time slot, but calling this 720 times is not good! - Ideally I need some query that can return all the data in one go, and use the recordset to build a table showing availablity.

How do I write this kind of query ?

Thanks
---

dteDateTime="27-Jul-02 15:30"
iCarID=3

SELECT [pk_bookedTimeID] FROM tblBookedTimes INNER JOIN tblBookings ON [tblBookedTimes].[fk_bookingID]=[tblBookings].[pk_bookingID] WHERE [tblBookings].[fk_carID]=" & iCarID & " AND " & "[tblBookedTimes].[bookedTime] = '" & dteDateTime & "'"can you give a brief example of what the result set is supposed to look like with the holes in it?

have a look at Finding all the dates between two dates (http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid474893_tax285649,00.html) which shows how to use an integer table to generate dates

rudy
http://rudy.ca/|||Hi,

The results would be as follows :

car_id | 24/07/02 00:00 | 24/07/02 00:30 | 24/07/02 01:00 ...
1 0 1 1
2 1 1 1
3 1 1 1

Each row would be a car, each column a date/time.
The date/times would cover 72 hrs ( 144 columns)
from any date/time specified.

The problem I have is that I only store the 1's in the db not the 0's!

The article looks like exactly what Im trying to do - Thanks.|||okay, what you want is a cross-tab report, and since you'll have to do that in asp anyway, there's not much point generating the missing values with sql, just do them with asp too...

rudy|||Thanks rudy,

At least I know what its called now! that was half the problem trying to research it.

I have solved this already in ASP, the performance was so bad- thats why I looked into doing it on the db server with SQL.|||the performance was bad in ASP?

then you must have been doing something wrong, like calling the database in a loop :cool:

call the database like this:SELECT bookedTime
FROM tblBookedTimes
INNER JOIN tblBookings
ON tblBookedTimes.fk_bookingID
= tblBookings.pk_bookingID
WHERE tblBookings.fk_carID=" & iCarID & "
ORDER BY bookedTimenotice that you will get back all the bookedTime values for the car, and they will be in bookedTime sequence

now "cross-tab" the bookedTime values using ASP logic, laying them out into an array with zeroes in the "empty" spots...

there's no way that's going to have bad performance

laying out the results into an array should be easy, because from your original question it looks like all your datetime values were previously entered with times on the half hour (which makes sense for a booking table)

so just retrieve the data in one query (you may want to add a WHERE condition for the right date, and maybe get more info on which car, etc.), and ask an ASP programmer for help with the array

i guess i should mention that i don't do ASP :rolleyes:

rudy
http://rudy.ca/

Book recommendation on performance

I have the Transact-SQL Programming book from O'Reilly. It was
published in 1999. It states that "SELECT ... INTO" statements end up
locking the entire database of the target table. Since the tempdb is
also involved (in many cases), this creates major deadlocks for the
entire database and all users. It suggests using the "INSERT ...
SELECT" form instead.

Considering that the book is somehow dated, is this recommendation
still valid, especially on target sizes of up to 5 million records?php newbie (newtophp2000@.yahoo.com) writes:
> I have the Transact-SQL Programming book from O'Reilly. It was
> published in 1999. It states that "SELECT ... INTO" statements end up
> locking the entire database of the target table. Since the tempdb is
> also involved (in many cases), this creates major deadlocks for the
> entire database and all users. It suggests using the "INSERT ...
> SELECT" form instead.
> Considering that the book is somehow dated, is this recommendation
> still valid, especially on target sizes of up to 5 million records?

To a large extent, no. The author seems to have had SQL 6.5 in mind, where
SELECT INTO a temptable, indeed to bring a server to a stand still. The
problem is that SELECT INTO creates the temp table, and then goes on to
fill it with data. Since the query is one transaction, it keeps a lock on
the system tables until the query has completed. This was fatal in SQL 6.5
which only had page locks. In SQL7 and SQL2000 where you have row locks,
the impact on other processes is much smaller.

The advantage of SELECT INTO is that is that is minimally logged, so
SELECT INTO #tmp for five million rows can be faster and take less
toll on the server than CREATE TABLE INSERT INTO.

Nevertheless, there are contexts where SELECT INTO is an inferior choice
over CREATE TABLE not talking about a table variable. Say that you already
have a transaction in progress, and you creating many small tables
repeatedly within this transaction. SELECT INTO takes out more locks on
than CREATE TABLE, so you acquire a whole lot more locks with SELECT
INTO, and this can have an impact on performance. Case in point: I had
a procedure which suddently started to perform much slower than before.
This procedure performs some complex data updating in an iterative
fashion. My profiling pointed to a seeminginly innocent query which
appeared to take longer and longer time as the procedure proceeded.
Eventually I found the answer in a trigger (which was not affected by
this query). I had replaced direct deferences to "inserted" with temp
table created through "SELECT * INTO #inserted FROM inserted".

To summarize: SELECT INTO is fine for single-time queries on large
tables. It is bad to have in triggers in tables which maninly are
updated one row at a time.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

BOL says you should not kill a SELECT statement. Why not?

BOL says this about the KILL command:
Use KILL very carefully, especially when critical processes are
running. You cannot kill your own process. Other processes not to kill
are:
AWAITING COMMAND
CHECKPOINT SLEEP
LAZY WRITER
LOCK MONITOR
SELECT
SIGNAL HANDLER
Those all make sense except for the SELECT but BOL doesn't elaborate on
why you shouldn't kill a SELECT statement. Any ideas why they caution
against this?
ThanksIt must be a mistake. They removed it from 2005 BOL.
<pshroads@.gmail.com> wrote in message
news:1154975355.227889.163170@.m79g2000cwm.googlegroups.com...
> BOL says this about the KILL command:
> Use KILL very carefully, especially when critical processes are
> running. You cannot kill your own process. Other processes not to kill
> are:
> AWAITING COMMAND
> CHECKPOINT SLEEP
> LAZY WRITER
> LOCK MONITOR
> SELECT
> SIGNAL HANDLER
> Those all make sense except for the SELECT but BOL doesn't elaborate on
> why you shouldn't kill a SELECT statement. Any ideas why they caution
> against this?
> Thanks
>

BOL says you should not kill a SELECT statement. Why not?

BOL says this about the KILL command:
Use KILL very carefully, especially when critical processes are
running. You cannot kill your own process. Other processes not to kill
are:
AWAITING COMMAND
CHECKPOINT SLEEP
LAZY WRITER
LOCK MONITOR
SELECT
SIGNAL HANDLER
Those all make sense except for the SELECT but BOL doesn't elaborate on
why you shouldn't kill a SELECT statement. Any ideas why they caution
against this?
ThanksIt must be a mistake. They removed it from 2005 BOL.
<pshroads@.gmail.com> wrote in message
news:1154975355.227889.163170@.m79g2000cwm.googlegroups.com...
> BOL says this about the KILL command:
> Use KILL very carefully, especially when critical processes are
> running. You cannot kill your own process. Other processes not to kill
> are:
> AWAITING COMMAND
> CHECKPOINT SLEEP
> LAZY WRITER
> LOCK MONITOR
> SELECT
> SIGNAL HANDLER
> Those all make sense except for the SELECT but BOL doesn't elaborate on
> why you shouldn't kill a SELECT statement. Any ideas why they caution
> against this?
> Thanks
>

Bocking Issue

I have a spid that has a wait type of networkio, open
trans = 0, command = select, status is runnable, wait time
of around 70000000. application is IIS.
I'm assuming I can just kill this spid beceause it's
a "select" and not part of a transaction. Is there some
way to tell if it's safe to kill this?Did you do a DBCC INPUTBUFFER on that spid? That'll at least tell you what
exactly that spid is selecting, and you could determine, where that
connection is coming from.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"maryann" <anonymous@.discussions.microsoft.com> wrote in message
news:0bcf01c3d53c$b61fb070$a501280a@.phx.gbl...
I have a spid that has a wait type of networkio, open
trans = 0, command = select, status is runnable, wait time
of around 70000000. application is IIS.
I'm assuming I can just kill this spid beceause it's
a "select" and not part of a transaction. Is there some
way to tell if it's safe to kill this?

blog select with comment count

The following sql works great when the field for my main blog message is type nvarchar but doesn't work for text which I need to convert to.

select
a.id, b.textField
count(b.a_id) as myCount
from a left join b on a.id = b.a_id
group by a.id, b.textField

What other methods could i use to get "myCount" within one sql statement?

Thanks in advance, JeffHere is more information:

using sql server 2000 (from asp.net) the following sql works great when the "textForm" field is of type nvarchar but doesn't work for the text type.

I don't believe nvarchar will work for my needs since it is limited to 8000 (4000 due to double storage). I believe I will need to use the text type.

select bm.message_id,
bm.title,
bm.display_date,
bm.message,
count(bc.comment_id) as commentCount
from blog_messages as bm LEFT JOIN blog_comments as bc
on bm.message_id = bc.message_id
where bm.active_flag = 1
group by bm.message_id, bm.title, bm.display_date, bm.message
order by display_date DESC

The purpose of the select statement is to display a list of blog entries that includes the number of comments on each entry. An example of this in cold fusion is found at
http://www.camdenfamily.com/morpheus/blog/

With my limited knowledge of the asp.net repeater control, I'm not sure how to integrate 2 select statements (1st for blog content, 2nd for comment count). It seems the fastest way to get all of the information I want is to use a stored procedure using either a temp table or a cursor to compile all of the data together. However this method may forcee me to select the count from table b for each record of table a which could be time consuming.

Is there any way to achieve this goal with one select using the union statement or another type of join?

Although this might be a better sql question, I am also wondering about alternative asp.net solutions including repeaters with 2 or more select statements or using an array or list.|||How about something like:

select a.id,
b.textField,
myCount = (select count(*) from b where a_id = a.id)
from a

You might need to include a COALESCE function around the correlated subquery to check for NULL and send back a zero.|||it worked! thanks!

select bm.message_id,
bm.title,
bm.display_date,
bm.message,
commentCount = (select count('x')
from blog_comments as bc
where bc.message_id = bm.message_id)
from blog_messages as bm
where bm.active_flag = 1
order by bm.display_date DESC

Blocking When doing bcp

Any ideas?

I execute a sproc from QA.
It interogates a folder:

Select @.Command_String = 'Dir ' + @.FilePath + '\*.txt'
Insert Into XLAT_Folder exec master..xp_cmdshell @.Command_String
[/code[

Which I then Parse and get the details...

Set up a CURSOR (Booo hiss...)

[code]

DECLARE XLAT_Folder CURSOR FOR
SELECT Create_Time
, File_Size
, [File_Name]
FROM XLAT_Folder_Parsed
ORDER BY [File_Name]


WHILE FETCH STATUS = 0
BEGIN
BEGIN TRAN

Then, based on the methodology that the file name must match the table and format file (I check to make sure everything is out there)

I then bcp the data in using my connection pooling id (I'm logged on as sa in qa)

SET @.cmd = 'bcp ' + @.db_name + '..' + SUBSTRING(@.File_Name,1,CHARINDEX('.',@.File_Name)-1) + ' in '
+ @.FilePathAndName
+ ' -f' + 'd:\Data\Tax\' + SUBSTRING(@.File_Name,1,CHARINDEX('.',@.File_Name)-1) + '.fmt'
+ ' -S' + @.@.servername + ' -U -P'

SET @.Command_string = 'EXEC master..xp_cmdshell ''' + @.cmd + '''' + ', NO_OUTPUT'

INSERT INTO #XLAT_BCP_Results(Col1) Exec(@.Command_String)


MOVE DATA FILE TO ARCHIVE
COMMIT TRAN
ANOTHER FETCH

A spid is launched to do the bcp...I have 4 files...on the last load The connection Pooling lauched spid gets blocked by the sa spid that launched the sproc...it doesn't happen all the time, just most of the time...

I've put a trace on in Profiler, but I don't see anything...I've picked the event class of deadlocks...but I never see it...

When I do sp_who, it shows the blocking...

I'm so confused...Can you get the table/index that has the blocking lock on it?|||Well the blocking spid I found in sp_who, is the QA window I lauched the sproc from...the blocked spid is the idependent bcp thread (well not so independent)..

There's no RI between these code tables, and just a trigger...that moves the rows to history table...

I'll check sp_locks...|||Every single lock on the "parent" spid has a status of "grant" and a type of IX...|||I am pretty sure there wasa way of telling a blocking lock from a regular lock. I will have to do a bit of checking, but this job thing has me a bit tied up.|||In the output of sp_lock, look for a status of "WAIT". This is the process that is attempting to get at the locked resource, and of course, must wait for the first process to get done with it. Alternatively, you can check the waitresource column in the sysprocesses table. This will also tell you what the processes that is blocked is waiting on.
This will give you the name of the table/index that you are getting hung up on. After this, I am afraid it is going to be a slog through the code to find the actual stumbling block.
Let us know how it comes out.|||Thanks...

Been there...

It seems that even though I expected lines inside of a sproc to be serial, it looks like the delete doesn't complete before the sproc continues to the nect line of code, which is the xp_cmdshell bcp...

I find this totally unbelievable, but in every instance, the independent xpshell thread (which is executed with a different login) is the one that is being blocked. And it is being blocked by the spid that executed the sproc...

With me so far...

I then placed the COMMIT immediatley after the DELETE, and viola!

No more problems...now tell me...are the lines in a sproc serial?

I always thought they were, and I'll be damned if I believe any differently, but I have NO other explination.

Thanks for everyones help.

WORK AROUND ...HO!

Blocking issue in sql2000

Hi
I have one user who uses SQL EM and run queries and modifies data.
We see that when the main blocking SPID is SQLEM and command is "select"
My question is
1. How can select command from EM be a blocking spid.
2. How can you cause blocling using EM?
Please explain.
Mangesh
Mangesh Deshpande wrote:
> Hi
> I have one user who uses SQL EM and run queries and modifies data.
> We see that when the main blocking SPID is SQLEM and command is
> "select"
> My question is
> 1. How can select command from EM be a blocking spid.
> 2. How can you cause blocling using EM?
> Please explain.
> Mangesh
I can't stress this enough when I say that you should never use SQL EM
for editing data, unless in development or off-hours.
SQL EM uses a server-side, firehose cursor which is good, but does not
fetch all data immediately (which is bad), like Query Analyzer or any
well-written application might. The benefit is that a large table can be
queried and viewed quickly, but most of the rows are sitting on the
server in the result set waiting for the user to scroll though the
results (at which point they are fetched). Until then, shared locks
remain on some pages on the table.
To avoid this, immediately issue a CTRL+END to move the end of the
results. That forces SQL EM to fetch all data, thereby releasing the
locks. If it's a large table, however, this process could be time
consuming.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||A SELECT statement will hold a shared (S) lock on the specific resource
(generally a KEY or RID) and an intent shared (IS) lock on the escalated
resources (usually an IS(PAG) and IS(TAB)). The data browsing window in
SQLEM will hold locks open for big tables while you're scrolling around
through the data (just like MSAccess). If SQLEM happens to have a
shared lock on a page (PAG), which is entirely possibly, and some other
process requests an intent exclusive (IX) lock on that same page (for
example it wants to delete a row on the page, so it will acquire an
X(RID) or X(KEY) and an IX(PAG) and an IX(TAB)) then the IX(PAG) will be
blocked by the S(PAG), because they're incompatible lock types on the
same resource, until the S)PAG) is release.
See BOL for more into on lock compatibility:
(http://msdn.microsoft.com/library/de...on_7a_8um1.asp)
The important thing to remember is that SQLEM is just another client app
running SELECT queries and issuing shared lock requests, sometimes on
pages of data at a time, and often doesn't release those locks (if it's
a bit enough table and you haven't reached the last page of it yet)
until you close the child window displaying that data (that issued the
SELECT statement).
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Mangesh Deshpande wrote:

>Hi
> I have one user who uses SQL EM and run queries and modifies data.
>We see that when the main blocking SPID is SQLEM and command is "select"
>My question is
>1. How can select command from EM be a blocking spid.
>2. How can you cause blocling using EM?
>Please explain.
>Mangesh
>
|||What is a firehose cursor ?
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:ujEppUzZFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Mangesh Deshpande wrote:
> I can't stress this enough when I say that you should never use SQL EM
> for editing data, unless in development or off-hours.
> SQL EM uses a server-side, firehose cursor which is good, but does not
> fetch all data immediately (which is bad), like Query Analyzer or any
> well-written application might. The benefit is that a large table can be
> queried and viewed quickly, but most of the rows are sitting on the
> server in the result set waiting for the user to scroll though the
> results (at which point they are fetched). Until then, shared locks
> remain on some pages on the table.
> To avoid this, immediately issue a CTRL+END to move the end of the
> results. That forces SQL EM to fetch all data, thereby releasing the
> locks. If it's a large table, however, this process could be time
> consuming.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||A firehose cursor is essentially a forward-only, read-only cursor. From
BOL:
Rows are sent to the client in the order they are placed in the
result set, and the application must process the rows in this order.
After executing an SQL statement on a connection, the application
cannot do anything on the connection other than retrieve the rows in
the result set until all the rows have been retrieved. The only
other action that an application can perform before the end of the
result set is to cancel the remainder of the result set. This is the
fastest method to get rows from SQL Server to the client.
See
http://msdn.microsoft.com/library/de...on_07_7d6b.asp
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Hassan wrote:

>What is a firehose cursor ?
>"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>news:ujEppUzZFHA.3132@.TK2MSFTNGP09.phx.gbl...
>
>
>

Blocking issue in sql2000

Hi
I have one user who uses SQL EM and run queries and modifies data.
We see that when the main blocking SPID is SQLEM and command is "select"
My question is
1. How can select command from EM be a blocking spid.
2. How can you cause blocling using EM?
Please explain.
MangeshMangesh Deshpande wrote:
> Hi
> I have one user who uses SQL EM and run queries and modifies data.
> We see that when the main blocking SPID is SQLEM and command is
> "select"
> My question is
> 1. How can select command from EM be a blocking spid.
> 2. How can you cause blocling using EM?
> Please explain.
> Mangesh
I can't stress this enough when I say that you should never use SQL EM
for editing data, unless in development or off-hours.
SQL EM uses a server-side, firehose cursor which is good, but does not
fetch all data immediately (which is bad), like Query Analyzer or any
well-written application might. The benefit is that a large table can be
queried and viewed quickly, but most of the rows are sitting on the
server in the result set waiting for the user to scroll though the
results (at which point they are fetched). Until then, shared locks
remain on some pages on the table.
To avoid this, immediately issue a CTRL+END to move the end of the
results. That forces SQL EM to fetch all data, thereby releasing the
locks. If it's a large table, however, this process could be time
consuming.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||A SELECT statement will hold a shared (S) lock on the specific resource
(generally a KEY or RID) and an intent shared (IS) lock on the escalated
resources (usually an IS(PAG) and IS(TAB)). The data browsing window in
SQLEM will hold locks open for big tables while you're scrolling around
through the data (just like MSAccess). If SQLEM happens to have a
shared lock on a page (PAG), which is entirely possibly, and some other
process requests an intent exclusive (IX) lock on that same page (for
example it wants to delete a row on the page, so it will acquire an
X(RID) or X(KEY) and an IX(PAG) and an IX(TAB)) then the IX(PAG) will be
blocked by the S(PAG), because they're incompatible lock types on the
same resource, until the S)PAG) is release.
See BOL for more into on lock compatibility:
(7a_8um1.asp" target="_blank">http://msdn.microsoft.com/library/d.../>
7a_8um1.asp)
The important thing to remember is that SQLEM is just another client app
running SELECT queries and issuing shared lock requests, sometimes on
pages of data at a time, and often doesn't release those locks (if it's
a bit enough table and you haven't reached the last page of it yet)
until you close the child window displaying that data (that issued the
SELECT statement).
HTH
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Mangesh Deshpande wrote:

>Hi
> I have one user who uses SQL EM and run queries and modifies data.
>We see that when the main blocking SPID is SQLEM and command is "select"
>My question is
>1. How can select command from EM be a blocking spid.
>2. How can you cause blocling using EM?
>Please explain.
>Mangesh
>|||What is a firehose cursor ?
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:ujEppUzZFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Mangesh Deshpande wrote:
> I can't stress this enough when I say that you should never use SQL EM
> for editing data, unless in development or off-hours.
> SQL EM uses a server-side, firehose cursor which is good, but does not
> fetch all data immediately (which is bad), like Query Analyzer or any
> well-written application might. The benefit is that a large table can be
> queried and viewed quickly, but most of the rows are sitting on the
> server in the result set waiting for the user to scroll though the
> results (at which point they are fetched). Until then, shared locks
> remain on some pages on the table.
> To avoid this, immediately issue a CTRL+END to move the end of the
> results. That forces SQL EM to fetch all data, thereby releasing the
> locks. If it's a large table, however, this process could be time
> consuming.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||A firehose cursor is essentially a forward-only, read-only cursor. From
BOL:
Rows are sent to the client in the order they are placed in the
result set, and the application must process the rows in this order.
After executing an SQL statement on a connection, the application
cannot do anything on the connection other than retrieve the rows in
the result set until all the rows have been retrieved. The only
other action that an application can perform before the end of the
result set is to cancel the remainder of the result set. This is the
fastest method to get rows from SQL Server to the client.
See
7_7d6b.asp" target="_blank">http://msdn.microsoft.com/library/d... />
7_7d6b.asp
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Hassan wrote:

>What is a firehose cursor ?
>"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>news:ujEppUzZFHA.3132@.TK2MSFTNGP09.phx.gbl...
>
>
>

Blocking issue in sql2000

Hi
I have one user who uses SQL EM and run queries and modifies data.
We see that when the main blocking SPID is SQLEM and command is "select"
My question is
1. How can select command from EM be a blocking spid.
2. How can you cause blocling using EM?
Please explain.
MangeshMangesh Deshpande wrote:
> Hi
> I have one user who uses SQL EM and run queries and modifies data.
> We see that when the main blocking SPID is SQLEM and command is
> "select"
> My question is
> 1. How can select command from EM be a blocking spid.
> 2. How can you cause blocling using EM?
> Please explain.
> Mangesh
I can't stress this enough when I say that you should never use SQL EM
for editing data, unless in development or off-hours.
SQL EM uses a server-side, firehose cursor which is good, but does not
fetch all data immediately (which is bad), like Query Analyzer or any
well-written application might. The benefit is that a large table can be
queried and viewed quickly, but most of the rows are sitting on the
server in the result set waiting for the user to scroll though the
results (at which point they are fetched). Until then, shared locks
remain on some pages on the table.
To avoid this, immediately issue a CTRL+END to move the end of the
results. That forces SQL EM to fetch all data, thereby releasing the
locks. If it's a large table, however, this process could be time
consuming.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||This is a multi-part message in MIME format.
--070605070403080308080508
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
A SELECT statement will hold a shared (S) lock on the specific resource
(generally a KEY or RID) and an intent shared (IS) lock on the escalated
resources (usually an IS(PAG) and IS(TAB)). The data browsing window in
SQLEM will hold locks open for big tables while you're scrolling around
through the data (just like MSAccess). If SQLEM happens to have a
shared lock on a page (PAG), which is entirely possibly, and some other
process requests an intent exclusive (IX) lock on that same page (for
example it wants to delete a row on the page, so it will acquire an
X(RID) or X(KEY) and an IX(PAG) and an IX(TAB)) then the IX(PAG) will be
blocked by the S(PAG), because they're incompatible lock types on the
same resource, until the S)PAG) is release.
See BOL for more into on lock compatibility:
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_8um1.asp)
The important thing to remember is that SQLEM is just another client app
running SELECT queries and issuing shared lock requests, sometimes on
pages of data at a time, and often doesn't release those locks (if it's
a bit enough table and you haven't reached the last page of it yet)
until you close the child window displaying that data (that issued the
SELECT statement).
HTH
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Mangesh Deshpande wrote:
>Hi
> I have one user who uses SQL EM and run queries and modifies data.
>We see that when the main blocking SPID is SQLEM and command is "select"
>My question is
>1. How can select command from EM be a blocking spid.
>2. How can you cause blocling using EM?
>Please explain.
>Mangesh
>
--070605070403080308080508
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>A SELECT statement will hold a shared (S) lock on the specific
resource (generally a KEY or RID) and an intent shared (IS) lock on the
escalated resources (usually an IS(PAG) and IS(TAB)). The data
browsing window in SQLEM will hold locks open for big tables while
you're scrolling around through the data (just like MSAccess). If
SQLEM happens to have a shared lock on a page (PAG), which is entirely
possibly, and some other process requests an intent exclusive (IX) lock
on that same page (for example it wants to delete a row on the page, so
it will acquire an X(RID) or X(KEY) and an IX(PAG) and an IX(TAB)) then
the IX(PAG) will be blocked by the S(PAG), because they're incompatible
lock types on the same resource, until the S)PAG) is release.<br>
<br>
See BOL for more into on lock compatibility:
(<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_8um1.asp</a>)<br>">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_8um1.asp">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_8um1.asp</a>)<br>
<br>
The important thing to remember is that SQLEM is just another client
app running SELECT queries and issuing shared lock requests, sometimes
on pages of data at a time, and often doesn't release those locks (if
it's a bit enough table and you haven't reached the last page of it
yet) until you close the child window displaying that data (that issued
the SELECT statement).<br>
<br>
HTH<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
Mangesh Deshpande wrote:
<blockquote cite="mid5515F106-3E87-4255-9DD6-7CAA798E2526@.microsoft.com"
type="cite">
<pre wrap="">Hi
I have one user who uses SQL EM and run queries and modifies data.
We see that when the main blocking SPID is SQLEM and command is "select"
My question is
1. How can select command from EM be a blocking spid.
2. How can you cause blocling using EM?
Please explain.
Mangesh
</pre>
</blockquote>
</body>
</html>
--070605070403080308080508--|||What is a firehose cursor ?
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:ujEppUzZFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Mangesh Deshpande wrote:
> > Hi
> >
> > I have one user who uses SQL EM and run queries and modifies data.
> > We see that when the main blocking SPID is SQLEM and command is
> > "select"
> >
> > My question is
> > 1. How can select command from EM be a blocking spid.
> > 2. How can you cause blocling using EM?
> >
> > Please explain.
> >
> > Mangesh
> I can't stress this enough when I say that you should never use SQL EM
> for editing data, unless in development or off-hours.
> SQL EM uses a server-side, firehose cursor which is good, but does not
> fetch all data immediately (which is bad), like Query Analyzer or any
> well-written application might. The benefit is that a large table can be
> queried and viewed quickly, but most of the rows are sitting on the
> server in the result set waiting for the user to scroll though the
> results (at which point they are fetched). Until then, shared locks
> remain on some pages on the table.
> To avoid this, immediately issue a CTRL+END to move the end of the
> results. That forces SQL EM to fetch all data, thereby releasing the
> locks. If it's a large table, however, this process could be time
> consuming.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||This is a multi-part message in MIME format.
--060809080506050602090205
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
A firehose cursor is essentially a forward-only, read-only cursor. From
BOL:
Rows are sent to the client in the order they are placed in the
result set, and the application must process the rows in this order.
After executing an SQL statement on a connection, the application
cannot do anything on the connection other than retrieve the rows in
the result set until all the rows have been retrieved. The only
other action that an application can perform before the end of the
result set is to cancel the remainder of the result set. This is the
fastest method to get rows from SQL Server to the client.
See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_07_7d6b.asp
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Hassan wrote:
>What is a firehose cursor ?
>"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>news:ujEppUzZFHA.3132@.TK2MSFTNGP09.phx.gbl...
>
>>Mangesh Deshpande wrote:
>>
>>Hi
>>I have one user who uses SQL EM and run queries and modifies data.
>>We see that when the main blocking SPID is SQLEM and command is
>>"select"
>>My question is
>>1. How can select command from EM be a blocking spid.
>>2. How can you cause blocling using EM?
>>Please explain.
>>Mangesh
>>
>>I can't stress this enough when I say that you should never use SQL EM
>>for editing data, unless in development or off-hours.
>>SQL EM uses a server-side, firehose cursor which is good, but does not
>>fetch all data immediately (which is bad), like Query Analyzer or any
>>well-written application might. The benefit is that a large table can be
>>queried and viewed quickly, but most of the rows are sitting on the
>>server in the result set waiting for the user to scroll though the
>>results (at which point they are fetched). Until then, shared locks
>>remain on some pages on the table.
>>To avoid this, immediately issue a CTRL+END to move the end of the
>>results. That forces SQL EM to fetch all data, thereby releasing the
>>locks. If it's a large table, however, this process could be time
>>consuming.
>>
>>--
>>David Gugick
>>Quest Software
>>www.imceda.com
>>www.quest.com
>>
>
>
--060809080506050602090205
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>A firehose cursor is essentially a forward-only, read-only cursor.
From BOL:<br>
</tt>
<blockquote><a>Rows are sent to the client in the order they are placed
in the result set, and the application must process the rows in this
order. After executing an SQL statement on a connection, the
application cannot do anything on the connection other than retrieve
the rows in the result set until all the rows have been retrieved. The
only other action that an application can perform before the end of the
result set is to cancel the remainder of the result set. This is the
fastest method to get rows from SQL Server to the client.<br>
</a></blockquote>
<tt>See
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_07_7d6b.asp</a></tt><br>">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_07_7d6b.asp">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_07_7d6b.asp</a></tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
Hassan wrote:
<blockquote cite="midujrJPozZFHA.2420@.TK2MSFTNGP12.phx.gbl" type="cite">
<pre wrap="">What is a firehose cursor ?
"David Gugick" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:david.gugick-nospam@.quest.com"><david.gugick-nospam@.quest.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:ujEppUzZFHA.3132@.TK2MSFTNGP09.phx.gbl">news:ujEppUzZFHA.3132@.TK2MSFTNGP09.phx.gbl</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Mangesh Deshpande wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Hi
I have one user who uses SQL EM and run queries and modifies data.
We see that when the main blocking SPID is SQLEM and command is
"select"
My question is
1. How can select command from EM be a blocking spid.
2. How can you cause blocling using EM?
Please explain.
Mangesh
</pre>
</blockquote>
<pre wrap="">I can't stress this enough when I say that you should never use SQL EM
for editing data, unless in development or off-hours.
SQL EM uses a server-side, firehose cursor which is good, but does not
fetch all data immediately (which is bad), like Query Analyzer or any
well-written application might. The benefit is that a large table can be
queried and viewed quickly, but most of the rows are sitting on the
server in the result set waiting for the user to scroll though the
results (at which point they are fetched). Until then, shared locks
remain on some pages on the table.
To avoid this, immediately issue a CTRL+END to move the end of the
results. That forces SQL EM to fetch all data, thereby releasing the
locks. If it's a large table, however, this process could be time
consuming.
David Gugick
Quest Software
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.imceda.com</a>">http://www.imceda.com">www.imceda.com</a>
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=www.quest.com</a>">http://www.quest.com">www.quest.com</a>
</pre>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--060809080506050602090205--

Blocking issue

Dear Sir/Madam,
I have run the sp_who2, and found that a process is blocking others processes.
But after I checked the blocker process, it is a SELECT query, which suppose
wouldn't lock any pages/tables, am I right? So, what is happening?
Many thanks!
Yours sincerely,
Henry
SELECT query needs to get read locks in SQL Server unless you are in read
uncommitted isolation. Please read the "Understanding Locking in SQL Server"
section in the Books Online.
Wei Xiao
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:3C18EC8B-1573-4BB3-8EB0-EFD74AAEB4D1@.microsoft.com...
> Dear Sir/Madam,
> I have run the sp_who2, and found that a process is blocking others
processes.
> But after I checked the blocker process, it is a SELECT query, which
suppose
> wouldn't lock any pages/tables, am I right? So, what is happening?
> Many thanks!
> Yours sincerely,
> Henry
|||To add more details, using the default settings for SQLServer, Select
statements get a short read lock. This lock lasts a very short time unless
the select is in a trasaction with Holdlock. In that case the read locks (
called shared locks) are held for the duration of the transaction. These
shared locks will prevent anyone from updating the rows.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:3C18EC8B-1573-4BB3-8EB0-EFD74AAEB4D1@.microsoft.com...
> Dear Sir/Madam,
> I have run the sp_who2, and found that a process is blocking others
processes.
> But after I checked the blocker process, it is a SELECT query, which
suppose
> wouldn't lock any pages/tables, am I right? So, what is happening?
> Many thanks!
> Yours sincerely,
> Henry
|||Also, again with the defaults, is to check whether or not you have IMPLICIT
TRANSACTIONs on. If so, you do not need to explicitly specify a BEGIN
TRANSACTION statement to initiate one and one is usually initiated with most
statements, including READ operations.
Sincerely,
Anthony Thomas

"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:3C18EC8B-1573-4BB3-8EB0-EFD74AAEB4D1@.microsoft.com...
Dear Sir/Madam,
I have run the sp_who2, and found that a process is blocking others
processes.
But after I checked the blocker process, it is a SELECT query, which suppose
wouldn't lock any pages/tables, am I right? So, what is happening?
Many thanks!
Yours sincerely,
Henry
|||It depends upon what transaction isolation level you are running under. The
transaction isolation level will determine what types of locks are issued
and how long the locks are held. If you are running under serializable, this
is what I would expect. See the BOL for the transaction isolation level
topics.
If these queries are being issued from an object managed by COM+, you may be
going down into the wormhole where all queries are issued under the
serializable transaction isolation level, regardless of what level you
specify in your query. Microsoft has several KB articles about this. It
depends upon how the object is written, whether transactions are used, ad
nauseum. I don't remember the exact rules off the top of my head, but it
essentially dumbs down everything so that you are assured of not hurting
yourself with concurrency issues.
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:3C18EC8B-1573-4BB3-8EB0-EFD74AAEB4D1@.microsoft.com...
> Dear Sir/Madam,
> I have run the sp_who2, and found that a process is blocking others
> processes.
> But after I checked the blocker process, it is a SELECT query, which
> suppose
> wouldn't lock any pages/tables, am I right? So, what is happening?
> Many thanks!
> Yours sincerely,
> Henry

BLock records using UPDLOCK

Hi all,
here my question :
I have 2 applications.

connection 1.
one does select max(grp) from orv and one does select max(grp) from orh. orh is the historical file from orv. We did this to know which is the greather grp between these 2 files.
After having did this, we add 1 at grp field.
we insert into orv the record max(grp) + 1

connection 2.
an other application could insert at the same time record in this table orv with same parameters.

my problem is the following. I need to block record in orv table either on the select ( connection 1) or Insert ( connection 2) to avoid having select max(grp) + 1 on orv at connection 1 and Insert a record into orv at connection 2.
I believe I need to use HOLDLOCK, UPDLOCK.
but I have not the habitude to use them.

Can I do this ?
connection 1
select max(grp) from orv WITH HOLDLOCK
connection 2
what should I use to avoid lock when I need to insert into orv. ?

thanks for your quick answerCan you explain what you are trying to do from a business or "real world" perspective? It sounds to me like you've dropped into the technology so far that you've lost sight of the original goal, and are making this a lot harder than it needs to be!

-PatP

Blobs in SQL Server

We have a blob in one table that is storing pdf files. I need to
write a select query that will grab that column and write those *.pdf
files out to a location on my hard drive. Does anyone know how that
can be done? All suggestions would be greatly appreciated...Connie (csawyer@.rwbaird.com) writes:

Quote:

Originally Posted by

We have a blob in one table that is storing pdf files. I need to
write a select query that will grab that column and write those *.pdf
files out to a location on my hard drive. Does anyone know how that
can be done? All suggestions would be greatly appreciated...

I have a very quick sketch for this on
http://www.sommarskog.se/blobload.txt.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Feb 15, 4:26 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Connie (csaw...@.rwbaird.com) writes:

Quote:

Originally Posted by

We have a blob in one table that is storing pdf files. I need to
write a select query that will grab that column and write those *.pdf
files out to a location on my hard drive. Does anyone know how that
can be done? All suggestions would be greatly appreciated...

>
I have a very quick sketch for this onhttp://www.sommarskog.se/blobload.txt.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Here is some good information that I found in my search to complete
the above task, I am posting as this may help others working with
blobs:
In this article I want to show, how you can copy a single text or
image value into or out of SQL Server with textcopy.exe utility. You
can find this utility in the directory containing the standard SQL
Server EXE files (C:\Mssql\Binn by default for SQL Server 6.5 and C:
\Mssql7\Binn by default for SQL Server 7.0).

The Textcopy utility is not described in SQL Server Books Online, but
you can get its description by typing textcopy /? from the command
prompt. This is the description:

Copies a single text or image value into or out of SQL Server. The
value
is a specified text or image 'column' of a single row (specified by
the
"where clause") of the specified 'table'.

If the direction is IN (/I) then the data from the specified 'file' is
copied into SQL Server, replacing the existing text or image value. If
the
direction is OUT (/O) then the text or image value is copied from
SQL Server into the specified 'file', replacing any existing file.

TEXTCOPY [/S [sqlserver]] [/U [login]] [/P [password]]
[/D [database]] [/T table] [/C column] [/W"where clause"]
[/F file] [{/I | /O}] [/K chunksize] [/Z] [/?]

/S sqlserver The SQL Server to connect to. If 'sqlserver' is
not
specified, the local SQL Server is used.
/U login The login to connect with. If 'login' is not
specified,
a trusted connection will be used.
/P password The password for 'login'. If 'password' is not
specified, a NULL password will be used.
/D database The database that contains the table with the
text or
image data. If 'database' is not specified, the
default
database of 'login' is used.
/T table The table that contains the text or image value.
/C column The text or image column of 'table'.
/W "where clause" A complete where clause (including the WHERE
keyword)
that specifies a single row of 'table'.
/F file The file name.
/I Copy text or image value into SQL Server from
'file'.
/O Copy text or image value out of SQL Server into
'file'.
/K chunksize Size of the data transfer buffer in bytes.
Minimum
value is 1024 bytes, default value is 4096 bytes.
/Z Display debug information while running.
/? Display this usage information and exit.

You will be prompted for any required options you did not specify.

You can use the following stored procedure to simplify the using of
textcopy utility:

CREATE PROCEDURE sp_textcopy (
@.srvname varchar (30),
@.login varchar (30),
@.password varchar (30),
@.dbname varchar (30),
@.tbname varchar (30),
@.colname varchar (30),
@.filename varchar (30),
@.whereclause varchar (40),
@.direction char(1))
AS
DECLARE @.exec_str varchar (255)
SELECT @.exec_str =
'textcopy /S ' + @.srvname +
' /U ' + @.login +
' /P ' + @.password +
' /D ' + @.dbname +
' /T ' + @.tbname +
' /C ' + @.colname +
' /W "' + @.whereclause +
'" /F ' + @.filename +
' /' + @.direction
EXEC master..xp_cmdshell @.exec_str

This is the example to copy image into SQL Server database pubs, table
pub_info, column name logo from picture.bmp file where pub_id='0736':

sp_textcopy @.srvname = 'ServerName',
@.login = 'Login',
@.password = 'Password',
@.dbname = 'pubs',
@.tbname = 'pub_info',
@.colname = 'logo',
@.filename = 'c:\picture.bmp',
@.whereclause = " WHERE pub_id='0736' ",
@.direction = 'I'

BLOB or PIPING a Select

I posted this a few months ago, but at the time did not find a simple method
..
I would like to have a select statement output into a single result...
IE
select email from a.client
rather then return as:
user1@.user.com
user2@.user.com
user3@.user.com
I would like:
user1@.user.comuser1@.user.comuser1@.user.com
Basically treating the result as a single row rather then one for each...
anythoughts or ideas? Am I on crack?
Jordyhttp://support.microsoft.com/newsgr...n-us&sloc=en-us
AMB
"Jordy Boss" wrote:

> I posted this a few months ago, but at the time did not find a simple meth
od...
> I would like to have a select statement output into a single result...
> IE
> select email from a.client
> rather then return as:
> user1@.user.com
> user2@.user.com
> user3@.user.com
> I would like:
> user1@.user.comuser1@.user.comuser1@.user.com
> Basically treating the result as a single row rather then one for each...
> anythoughts or ideas? Am I on crack?
> Jordy

Blob in Tsql

Hi,
I am writing a tsql in which i am saving contents of one table into another using "insert into.. select".
But it doesnt work for Blobs.. what should i do??
can anyone help me on this.
Thank You.Refer to UPDATETEXT/WRITETEXT/READTEXT under BOL.|||ya.. i have tried with writetext and readtext but am not able to get it right with it.

i have to read a blob from a table and insert it into another table.

the code is as follows:
/**************
DECLARE @.ptrval binary(16)
DECLARE @.destval binary(16)

SELECT @.ptrval = TEXTPTR(icon_file)
FROM subject_master where subject_id=1

READTEXT subject_master.icon_file @.ptrval 1 2000

select @.destval = TEXTPTR(icon_file)
FROM subject_master_ver where subject_id=1

Writetext subject_master_ver.icon_file @.destVal @.ptrval
************/
but it doesnt work...
can u help me out with this...

Thank You.

Originally posted by Satya
Refer to UPDATETEXT/WRITETEXT/READTEXT under BOL.|||Have you tried using DTS in this case, if its a regular process then schedule the job to do so.

HTH
Originally posted by swatisk
ya.. i have tried with writetext and readtext but am not able to get it right with it.

i have to read a blob from a table and insert it into another table.

the code is as follows:
/**************
DECLARE @.ptrval binary(16)
DECLARE @.destval binary(16)

SELECT @.ptrval = TEXTPTR(icon_file)
FROM subject_master where subject_id=1

READTEXT subject_master.icon_file @.ptrval 1 2000

select @.destval = TEXTPTR(icon_file)
FROM subject_master_ver where subject_id=1

Writetext subject_master_ver.icon_file @.destVal @.ptrval
************/
but it doesnt work...
can u help me out with this...

Thank You.|||its not a regular process... it is triggered by the user of the system.
i have not used DTS at all.
by the way what is the problem with my code??
the way i am using writetext,readtext is fine??
i am not able to find any example for writing a blob(image) into a blob(image) of a table... not available in the books online.
pls help.

Thank You.

Originally posted by Satya
Have you tried using DTS in this case, if its a regular process then schedule the job to do so.

HTH|||Try this

http://www.motobit.com/help/ScptUtl/sa307.htm

blank spaces....

select * from Customer where customerId ='123 '
select * from Customer where customerId ='123'
Both the above queries return the same result.
What is the property which ignores the white space character?
SQL 2K.
Thank you,
Larry
Larry Smith wrote:
> select * from Customer where customerId ='123 '
> select * from Customer where customerId ='123'
> Both the above queries return the same result.
> What is the property which ignores the white space character?
> SQL 2K.
> Thank you,
> Larry
Assuming you really have blank spaces in your customerId column, you can
use a LIKE clause to force the search for a trailing blank.
create table #whitespacetest (col1 varchar(10))
insert into #whitespacetest values ('ABC')
insert into #whitespacetest values ('ABC ')
select col1, col1 + '1' from #whitespacetest
select col1 from #whitespacetest where col1 = 'ABC'
select col1 from #whitespacetest where col1 = 'ABC '
select col1 from #whitespacetest where col1 LIKE 'ABC'
select col1 from #whitespacetest where col1 LIKE 'ABC[ ]'
drop table #whitespacetest
David Gugick
Quest Software
www.imceda.com
www.quest.com

blank spaces....

select * from Customer where customerId ='123 '
select * from Customer where customerId ='123'
Both the above queries return the same result.
What is the property which ignores the white space character?
SQL 2K.
Thank you,
LarryLarry Smith wrote:
> select * from Customer where customerId ='123 '
> select * from Customer where customerId ='123'
> Both the above queries return the same result.
> What is the property which ignores the white space character?
> SQL 2K.
> Thank you,
> Larry
Assuming you really have blank spaces in your customerId column, you can
use a LIKE clause to force the search for a trailing blank.
create table #whitespacetest (col1 varchar(10))
insert into #whitespacetest values ('ABC')
insert into #whitespacetest values ('ABC ')
select col1, col1 + '1' from #whitespacetest
select col1 from #whitespacetest where col1 = 'ABC'
select col1 from #whitespacetest where col1 = 'ABC '
select col1 from #whitespacetest where col1 LIKE 'ABC'
select col1 from #whitespacetest where col1 LIKE 'ABC[ ]'
drop table #whitespacetest
David Gugick
Quest Software
www.imceda.com
www.quest.com

blank spaces....

select * from Customer where customerId ='123 '
select * from Customer where customerId ='123'
Both the above queries return the same result.
What is the property which ignores the white space character?
SQL 2K.
Thank you,
LarryLarry Smith wrote:
> select * from Customer where customerId ='123 '
> select * from Customer where customerId ='123'
> Both the above queries return the same result.
> What is the property which ignores the white space character?
> SQL 2K.
> Thank you,
> Larry
Assuming you really have blank spaces in your customerId column, you can
use a LIKE clause to force the search for a trailing blank.
create table #whitespacetest (col1 varchar(10))
insert into #whitespacetest values ('ABC')
insert into #whitespacetest values ('ABC ')
select col1, col1 + '1' from #whitespacetest
select col1 from #whitespacetest where col1 = 'ABC'
select col1 from #whitespacetest where col1 = 'ABC '
select col1 from #whitespacetest where col1 LIKE 'ABC'
select col1 from #whitespacetest where col1 LIKE 'ABC[ ]'
drop table #whitespacetest
David Gugick
Quest Software
www.imceda.com
www.quest.com