Books online upgrade

Hi,
I recently upgraded the Books online for SQL Server2000
personal edition. Since the when ever I try to open Books
on line I get the below error message.
Cannot open file c:\program files\microsoft SQL Server\80
\tools\books\sql80.col
Can any one please help me?
Thanks
Hi,
Have a look into the below link and proceed :-
http://webservertalk.com/t844230.html
Thanks
Hari
MCDBA
"Siva" <admin@.emoneylinks.com> wrote in message
news:1533a01c4464d$cb368330$a301280a@.phx.gbl...
> Hi,
> I recently upgraded the Books online for SQL Server2000
> personal edition. Since the when ever I try to open Books
> on line I get the below error message.
> Cannot open file c:\program files\microsoft SQL Server\80
> \tools\books\sql80.col
> Can any one please help me?
> Thanks
|||Hi Hari,
I searched my entire hard drive for SQL80.col. But could not find it. would you please help me how to down load it.
Thanks for your help in the long week end.
|||HI,
I Suggest you to install the latest books online. books online can be
downloaded from below link.
http://www.microsoft.com/downloads/d...cb1-a420-445f-
8a4b-bd77a7da194b&DisplayLang=en
Thanks
Hari
MCDBA
"Siva" <anonymous@.discussions.microsoft.com> wrote in message
news:97E9690C-1A33-47F4-8BCF-A6EF6908357F@.microsoft.com...
> Hi Hari,
> I searched my entire hard drive for SQL80.col. But could not find it.
would you please help me how to down load it.
> Thanks for your help in the long week end.
|||Hi Hari,
I down loaded this couple of times. Still I have the same problem. After downloading and Installing I searched my entire drive for SQL80.col but couldnot find it. Any other suggestions please.
Thanks
Siva
sql

Bookmark lookup (Why)

I can't figure out why i have a bookmark lookup cost on ValidVendorPackages
on the below proc:
Select PriceViewHotelPrice.price as Totalprice, PriceViewHotelPrice.Docid
from price_view PriceViewHotelPrice Inner Join
(
select C1.Priceid as Pid,C1.VendorPackageId from
(
select distinct(HA.PriceId), HA.VendorPackageId from Criteria HA Inner Join
(
select VendorPackageId from ValidVendorPackages
where (Vendor = @.Vendor or @.Vendor = '')
and (Sitecode = @.Sitecode or @.Sitecode = '')
and (PackageType = @.PackageType or @.PackageType = '')
and (RequiredItems = @.RequiredItems or @.RequiredItems = -1)
)HB on HA.VendorPackageId = HB.VendorPackageId
and
(
CriteriaId in
(
select CriteriaID from ValidItemCriteria
where Destination = @.Destination
and LengthOfStay = @.LengthOfStay
and Ages = @.Ages
and ComponentType = 'H'
and (ValidItemType = @.HotelValidItemType_1 or @.HotelValidItemType_1 = '' )
and (ItemValue = @.HotelItemValue_1 or @.HotelItemValue_1 = '' )
)
)
) C1
) HotelCriteriaPriceId on PriceViewHotelPrice.priceid =
HotelCriteriaPriceId.Pid
and PriceViewHotelPrice.keydate between @.KeyDateMin and @.KeyDateMax
and ((PriceViewHotelPrice.price) between @.PriceLow and @.PriceHigh or
@.PriceLow = -1)
and (PriceViewHotelPrice.Volatility between @.VolatilityMin and
@.VolatilityMax or @.VolatilityMin = -1)
and (PriceViewHotelPrice.AvgMinsUpdateInterval between
@.AvgMinsUpdateIntervalMin and @.AvgMinsUpdateIntervalMax or
@.AvgMinsUpdateIntervalMin = -1)
Order by TotalPrice
can anyone see why?
will
On Wed, 15 Dec 2004 11:55:04 -0800, we7313 wrote:

>I can't figure out why i have a bookmark lookup cost on ValidVendorPackages
>on the below proc:
Hi we7313,
Without knowing your table structure, indexes, etc, I can't do anything
but guess. Peruse at your own risk :-)
Looking at this part of the query:
>select distinct(HA.PriceId), HA.VendorPackageId from Criteria HA Inner Join
>(
>select VendorPackageId from ValidVendorPackages
>where (Vendor = @.Vendor or @.Vendor = '')
>and (Sitecode = @.Sitecode or @.Sitecode = '')
>and (PackageType = @.PackageType or @.PackageType = '')
>and (RequiredItems = @.RequiredItems or @.RequiredItems = -1)
>)HB on HA.VendorPackageId = HB.VendorPackageId
>and
>(
>CriteriaId in
>(
>select CriteriaID from ValidItemCriteria
>where Destination = @.Destination
>and LengthOfStay = @.LengthOfStay
>and Ages = @.Ages
>and ComponentType = 'H'
>and (ValidItemType = @.HotelValidItemType_1 or @.HotelValidItemType_1 = '' )
>and (ItemValue = @.HotelItemValue_1 or @.HotelItemValue_1 = '' )
>)
>)
I think that the optimizer has decided to start with reducing the number
of matching rows in Criteria by checking the CriteriaId in (...)
condition. Criteria rows that match are then used in the join against
ValidVendorPackages (note: the derived table is easily transformed in a
simple join; the optimizer will probably execute it as a simple join).
Obviously, you have a nonclustered index on the column VendorPackageId in
ValidVendorPackages. In order to check the other conditions (on Vendor,
Sitecode, PackageType and RequiredItems) as well, SQL Server has to fetch
the complete row. The nonclustered index used to find the VendorPackageId
will also contain the values for the clustered index on this table: they
serve as a pointer to the complete row. SQL Server will now take these
values and use them to navigate the clustered index down to the row that
corresponds to the index entry. This action (using the clustered index
values stored in the nonclustered index to find the row) is called a
bookmark lookup.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Bold Axis

I have a line chart with ammounts both above and below 0, and I would
like to make the grid line for 0 be bold, but I can't figure out how to
make this happen. I know in the format box for the line thickness I
need something like this:
=Iif('?=0, 2pt, 3pt)
I just can't think of what I'm comparing with. There has to be
something I'm missing.Did you get the answer if so please let me know
i tried with iff(me.value=0, 1pt,2pt)
but no result
If u got answer please provide me the answer
"MegaGreg" wrote:
> I have a line chart with ammounts both above and below 0, and I would
> like to make the grid line for 0 be bold, but I can't figure out how to
> make this happen. I know in the format box for the line thickness I
> need something like this:
> =Iif('?=0, 2pt, 3pt)
> I just can't think of what I'm comparing with. There has to be
> something I'm missing.
>

BobMgr::GetBuf: Bob write not complete after 60 seconds.

What could cause the error listed below. Is there away to resolve this error
?
Thank You,
Error
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.http://support.microsoft.com/defaul...kb;en-us;309392
"Joe K." wrote:

> What could cause the error listed below. Is there away to resolve this err
or?
> Thank You,
> Error
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.|||Have a look here:
http://support.microsoft.com/defaul...b;en-us;Q309392
Andrew J. Kelly SQL MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:52D9083B-BD91-4CBA-883F-CA818754A38E@.microsoft.com...
> What could cause the error listed below. Is there away to resolve this
> error?
> Thank You,
> Error
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.

BobMgr::GetBuf: Bob write not complete after 60 seconds.

What could cause the error listed below. Is there away to resolve this error?
Thank You,
Error
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
http://support.microsoft.com/default...b;en-us;309392
"Joe K." wrote:

> What could cause the error listed below. Is there away to resolve this error?
> Thank You,
> Error
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
|||Have a look here:
http://support.microsoft.com/default...;en-us;Q309392
Andrew J. Kelly SQL MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:52D9083B-BD91-4CBA-883F-CA818754A38E@.microsoft.com...
> What could cause the error listed below. Is there away to resolve this
> error?
> Thank You,
> Error
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.

BobMgr::GetBuf: Bob write not complete after 60 seconds.

What could cause the error listed below. Is there away to resolve this error?
Thank You,
Error
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.
2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
after 60 seconds.http://support.microsoft.com/default.aspx?scid=kb;en-us;309392
"Joe K." wrote:
> What could cause the error listed below. Is there away to resolve this error?
> Thank You,
> Error
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.|||Have a look here:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q309392
--
Andrew J. Kelly SQL MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:52D9083B-BD91-4CBA-883F-CA818754A38E@.microsoft.com...
> What could cause the error listed below. Is there away to resolve this
> error?
> Thank You,
> Error
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.
> 2005-01-20 06:26:01.65 spid50 BobMgr::GetBuf: Bob write not complete
> after 60 seconds.

Blocking with RECEIVE and SqlDataReader for real-time updates

I have this scenario which is working fine, but would like to know if others have tried it or can recommend a better approach. Below is a brief description, but code should fully explain:

A process updates a SQL Server table via a stored proc, which in turn writes information to a service broker target queue. In my client/server architecture, I would like clients to see this new information as soon as it gets written to the target queue. To do this, I have a WCF service that calls a stored procedure; the stored proc contains an infinite loop which has a RECEIVE statement with an infinite timeout. Once RECEIVE reads data from the SB queue, data is retrieved via a SqlDataReader and results are sent to clients via a pub/sub. I then wait for more rows until RECEIVE unblocks and so on.

Stroed Proc

Code Snippet

-- ...
WHILE 1 = 1
BEGIN
-- Param declarations

...

BEGIN TRANSACTION;
WAITFOR
(
-- Blocks while TargetQueue is empty
RECEIVE
TOP(1)
@.conversation_handle = conversation_handle,
@.message_type_name = message_type_name,
@.conversation_group_id = conversation_group_id,
@.message_body =
CASE
WHEN validation = 'X' THEN CAST(message_body AS XML)
ELSE CAST(N'' AS XML)
END
FROM [dbo].[TargetQueue] -- No time out!
)

-- Handle errors
...

-- Return received information. After this statement is executed,
-- reader.NextResult unblocks and reader.Read() can read these
-- new values
SELECT 'Conversation Group Id' = @.conversation_group_id,
'Conversation Handle' = @.conversation_handle,
'Message Type Name' = @.message_type_name,
'Message Body' = @.message_body ;

COMMIT TRANSACTION

END -- WHILE

C# Code

Code Snippet

// Create a SqlCommand, and initialize to execute above stored proc
// (set command time to zero!)
...

// ExecuteReader blocks until RECEIVE reads data from the target queue
using (SqlDataReader reader = cmd.ExecuteReader())
{
// Begin an infinite loop
while (true)
{
// Process data retrieved by RECEIVE
while (reader.Read())
{
Trace.WriteLine("Conversation Group Id :" + reader.GetGuid(0) +
"Conversation Handle: " + reader.GetGuid(1) +
"Message Type Name : " + reader.GetString(2) +
"Message Body : " + reader.GetString(3));
// Send data via pub/sub
...
}

// Blocks until stored procedure returns another select statement
// i.e., blobks until RECEIVE unblocks and retrieves more data from queue
reader.NextResult();
} // while
}

There isn't anything wrong with your approach from what you show, but I do have a few questions:

Who ends the dialogs and how? The RECEIVE loop has to be prepared to process the with EndDialog and Error messages, otherwise you will leak conversations and your database will grow indefinetly. Also the stored procedure you mention, how does it find the dialog to send on and how does it end it?

Are the notifications important? Can you afford to loose one? You will lose notifications because WCF is cannot guarantee reliable delivery.

What scale are you talking about? One single RECEIVE loop with TOP(1) will have problems if you expect thousands of notifications per second.

|||

Remus,

Thanks for your thoughtful questions. My comments are below:

>What scale are you talking about? One single RECEIVE loop with TOP(1) will have problems if you expect thousands of notifications per second

Currently there will be less than 100 (One hundred) notifications per day. It is likely however, that these notifications will come in bursts as the process that ultimately populates the target queue (via uspSendMessage below) kicks in multiple times during the day. I can remove TOP(1) if necessary.

>Are the notifications important? Can you afford to loose one? You will lose notifications because WCF is cannot guarantee reliable delivery

Yes notifications to clients are very important as they will be used for trade position management. With WCF I am planning on using MSMQ to gurarantee delivery to clients. Is this what you meant?

> Who ends the dialogs and how? how does the stored proc find the dialog to send on and how does it end it?

I have two stored proc. uspSendMessage which updates a database table with new information, also does BEGIN DIALOG, then SEND ON CONVERSATION, then END CONVERSATION and COMMIT TRAN. uspReceiveMessage on the other hand does a RECEIVE (does not specify a conversion group id order to retreive all data), SELECT to select and send data to the SqlDataReader, END CONVERSATION, and then COMMIT TRAN.

Both stored proc from my test harness are shown below:

Code Snippet

CREATE PROCEDURE uspSendMessage

-- Add the parameters for the stored procedure here

@.CompanyName nvarchar(40),

@.PhoneName nvarchar(24)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON

begin try

-- Begin a transaction.

BEGIN TRANSACTION;

-- Insert data

insert into Shippers (CompanyName, Phone) values(@.CompanyName, @.PhoneName)

-- Create the message.

DECLARE @.message XML

SET @.message = N'<Shipper><CompanyName>' + @.CompanyName + '</CompanyName><Phone>' + @.PhoneName + '</Phone></Shipper>'

-- Declare a variable to hold the conversation handle, and then start the conversation

DECLARE @.conversationHandle UNIQUEIDENTIFIER

BEGIN DIALOG CONVERSATION @.conversationHandle

FROM SERVICE InitiatorService

TO SERVICE 'TargetService'

ON CONTRACT NewShipperContract

WITH ENCRYPTION = OFF;

-- Send the message on the dialog.

SEND ON CONVERSATION @.conversationHandle MESSAGE TYPE NewShipperMessage (@.message);

-- End the conversation.

END CONVERSATION @.conversationHandle

COMMIT TRANSACTION

end try

begin catch

select error_number() as ErrorNum, error_message() as ErrorMSg

-- Test XACT_STATE

IF (XACT_STATE()) = -1 -- Uncmmitable transaction state

BEGIN

ROLLBACK TRANSACTION;

END;

IF (XACT_STATE()) = 1 -- Tranaction active

BEGIN

COMMIT TRANSACTION;

END;

end catch

END

Code Snippet

CREATE PROCEDURE uspReceiveMessage

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

WHILE 1 = 1

BEGIN

DECLARE @.conversation_handle UNIQUEIDENTIFIER,

@.conversation_group_id UNIQUEIDENTIFIER,

@.message_body XML,

@.message_type_name NVARCHAR(128);

BEGIN TRANSACTION;

WAITFOR

(

RECEIVE TOP(1)

@.conversation_handle = conversation_handle,

@.message_type_name = message_type_name,

@.conversation_group_id = conversation_group_id,

@.message_body =

CASE

WHEN validation = 'X' THEN CAST(message_body AS XML)

ELSE CAST(N'<none/>' AS XML)

END

FROM [dbo].[TargetQueue]

)

-- Handle error

IF @.@.ERROR <> 0

BEGIN

COMMIT TRANSACTION

BREAK

END

-- Show received information

SELECT 'Conversation Group Id' = @.conversation_group_id,

'Conversation Handle' = @.conversation_handle,

'Message Type Name' = @.message_type_name,

'Message Body' = @.message_body ;

-- If the message_type_name indicates that the message is an error

-- or an end dialog message, end the conversation.

IF @.message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'

OR @.message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error'

BEGIN

END CONVERSATION @.conversation_handle ;

END ;

COMMIT TRANSACTION

END -- WHILE

END

|||

Yazan Diranieh wrote:

>What scale are you talking about? One single RECEIVE loop with TOP(1) will have problems if you expect thousands of notifications per second

Currently there will be less than 100 (One hundred) notifications per day. It is likely however, that these notifications will come in bursts as the process that ultimately populates the target queue (via uspSendMessage below) kicks in multiple times during the day. I can remove TOP(1) if necessary.

If you will hit scalabilty problems removing TOP(1) will not help, as you are sending only one message per dialog anyway, so RECEIVE can only return one message at a time. See http://blogs.msdn.com/remusrusanu/archive/2007/04/24/reusing-conversations.aspx and http://blogs.msdn.com/remusrusanu/archive/2007/05/02/recycling-conversations.aspx on how to send more than one message per dialog. I wouldn't focus on this at the moment though.

Yazan Diranieh wrote:

>Are the notifications important? Can you afford to loose one? You will lose notifications because WCF is cannot guarantee reliable delivery

Yes notifications to clients are very important as they will be used for trade position management. With WCF I am planning on using MSMQ to gurarantee delivery to clients. Is this what you meant?

As it is now, if the process crashes after the RECEIVE was commited but before the WCF send occured (or reached the outgoing MSMQ queue), your notification is lost. You must use a distributed transaction and enroll both the SqlCommand.ExecuteReader and the WCF send in the same transaction context. This also means you cannot use one infintely executing batch, but instead loop in CLR code and call one RECEIVE at a time.

Yazan Diranieh wrote:

> Who ends the dialogs and how? how does the stored proc find the dialog to send on and how does it end it?

I have two stored proc. uspSendMessage which updates a database table with new information, also does BEGIN DIALOG, then SEND ON CONVERSATION, then END CONVERSATION and COMMIT TRAN. uspReceiveMessage on the other hand does a RECEIVE (does not specify a conversion group id order to retreive all data), SELECT to select and send data to the SqlDataReader, END CONVERSATION, and then COMMIT TRAN.

You are doing Fire-and-Forget, this will get you into trouble, see http://blogs.msdn.com/remusrusanu/archive/2006/04/06/570578.aspx. Try to end the conversation from the target side first and have the sender end its side as a response to the target's EndDialog message.

|||Very informative. Thanks very much.

Blob Data

Can someone please show me an example on how to read & write blob data to a Database? For example if I have the query below (Northwind), how do I actually place the blob item in a picture box on a windows form?
SELECT Picture
FROM Categories
WHERE CategoryID = 5

Try the link below for two sample code to upload image in SQL Server. Hope this helps.
http://www.developerfusion.co.uk/show/3905/

http://www.dotnetbips.com/displayarticle.aspx?id=60

|||

All those tutorials simply store the file into a byte array and than write it to the databse.

I was always asking myself the following:

How big can a byte array become? Is the array's index an integer?

What do I have to do if I want to store a file which has more bytes than an integer?

Blank vs. Space

Hello, it seems to me that T-SQL is having a hard time
distinguishing a blank from one or more spaces. When I run
the statements below, the SELECTs always give 1
as a result. This seems completely wrong to me.. or am I
missing something?
CREATE TABLE TempTable (Value VARCHAR(10))
INSERT INTO TempTable (Value) VALUES ('')
SELECT COUNT(*) FROM TempTable WHERE Value = '' --blank
SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --one space
SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --two spaces
using Query Analyzer on SQL Server 2000 sp3a
Thanks,
SteveSQL Server does not take in mind spaces in the right side when using operato
r
= to do the comparison.
Example:
select 1 where space(0) = space(10)
go
try:
SELECT COUNT(*) FROM TempTable WHERE datalenght(Value) = 0
SELECT COUNT(*) FROM TempTable WHERE Value like ' '
SELECT COUNT(*) FROM TempTable WHERE Value like ' '
go
AMB
"Steve Deering" wrote:

> Hello, it seems to me that T-SQL is having a hard time
> distinguishing a blank from one or more spaces. When I run
> the statements below, the SELECTs always give 1
> as a result. This seems completely wrong to me.. or am I
> missing something?
>
> CREATE TABLE TempTable (Value VARCHAR(10))
> INSERT INTO TempTable (Value) VALUES ('')
> SELECT COUNT(*) FROM TempTable WHERE Value = '' --blank
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --one space
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --two spaces
> using Query Analyzer on SQL Server 2000 sp3a
> Thanks,
> Steve
>|||This is, I believe, according to the ANSI SQL definition. You can use LIKE i
nstead, as LIKE is
sensitive to trailing spaces:
SELECT COUNT(*) FROM TempTable WHERE Value LIKE '' --blank
SELECT COUNT(*) FROM TempTable WHERE Value LIKE ' ' --one space
SELECT COUNT(*) FROM TempTable WHERE Value LIKE ' ' --two spaces
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve Deering" <SteveREMOVE@.vin.com> wrote in message
news:134e01c5405b$8696ab70$a501280a@.phx.gbl...
> Hello, it seems to me that T-SQL is having a hard time
> distinguishing a blank from one or more spaces. When I run
> the statements below, the SELECTs always give 1
> as a result. This seems completely wrong to me.. or am I
> missing something?
>
> CREATE TABLE TempTable (Value VARCHAR(10))
> INSERT INTO TempTable (Value) VALUES ('')
> SELECT COUNT(*) FROM TempTable WHERE Value = '' --blank
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --one space
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --two spaces
> using Query Analyzer on SQL Server 2000 sp3a
> Thanks,
> Steve
>|||Trailing spaces are dropped so this is working as advertised.
Try this and you'll see what I mean.
declare @.value varchar(10)
set @.value = ' '
SELECT len(@.value)
...returns 0
This:
set @.value = ' b '
SELECT len(@.value)
...returns 2, the leading space and 'b'
"Steve Deering" <SteveREMOVE@.vin.com> wrote in message
news:134e01c5405b$8696ab70$a501280a@.phx.gbl...
> Hello, it seems to me that T-SQL is having a hard time
> distinguishing a blank from one or more spaces. When I run
> the statements below, the SELECTs always give 1
> as a result. This seems completely wrong to me.. or am I
> missing something?
>
> CREATE TABLE TempTable (Value VARCHAR(10))
> INSERT INTO TempTable (Value) VALUES ('')
> SELECT COUNT(*) FROM TempTable WHERE Value = '' --blank
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --one space
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --two spaces
> using Query Analyzer on SQL Server 2000 sp3a
> Thanks,
> Steve
>|||Tibor,
I think it is right except for the first one.
Example:
select 1
from
(
select space(0) union all select space(1) union all select space(2)
) as t(colA)
where colA like space(0);
AMB
"Tibor Karaszi" wrote:

> This is, I believe, according to the ANSI SQL definition. You can use LIKE
instead, as LIKE is
> sensitive to trailing spaces:
>
> SELECT COUNT(*) FROM TempTable WHERE Value LIKE '' --blank
> SELECT COUNT(*) FROM TempTable WHERE Value LIKE ' ' --one space
> SELECT COUNT(*) FROM TempTable WHERE Value LIKE ' ' --two spaces
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve Deering" <SteveREMOVE@.vin.com> wrote in message
> news:134e01c5405b$8696ab70$a501280a@.phx.gbl...
>
>|||Indeed. Thanks Alejandro... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:CCBDF7B5-B5B2-459D-9DA8-339EFC53AA59@.microsoft.com...
> Tibor,
> I think it is right except for the first one.
> Example:
> select 1
> from
> (
> select space(0) union all select space(1) union all select space(2)
> ) as t(colA)
> where colA like space(0);
>
> AMB
> "Tibor Karaszi" wrote:
>|||Huh. Apparently, it's similar for JOINs, as if the values get RTRIM'd:
USE Northwind
GO
CREATE TABLE TempTable (Value VARCHAR(10))
GO
INSERT INTO TempTable (Value) VALUES ('abc') -- Insert a ZLS
INSERT INTO TempTable (Value) VALUES ('abc ')
INSERT INTO TempTable (Value) VALUES ('abc ')
GO
SELECT COUNT(*) FROM TempTable WHERE Value = 'abc' -- ZLS
SELECT COUNT(*) FROM TempTable WHERE Value = 'abc ' -- 1 space
SELECT COUNT(*) FROM TempTable WHERE Value = 'abc ' -- 2 spaces
SELECT COUNT(*) FROM TempTable WHERE Value = 'abc' + Space(5) -- 5 spaces
GO
SELECT * FROM TempTable t inner join
(select value from TempTable where Value='abc ') t1 on t.value = t1.value
WHERE t.Value = 'abc '
DROP TABLE TempTable
Peace & happy computing,
Mike Labosh, MCSD
"Escriba coda ergo sum." -- vbSensei|||> SELECT COUNT(*) FROM TempTable WHERE Value = '' --blank
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --one space
> SELECT COUNT(*) FROM TempTable WHERE Value = ' ' --two spaces
One of the other code monkeys here posed an interesting question, though we
can't devise an example: What if in some implementation, the trailing
spaces have significant meaning, so that "bob" should not match "bob "?
Peace & happy computing,
Mike Labosh, MCSD
"Escriba coda ergo sum." -- vbSensei|||My name isn't "bob", it's "bob ".
Do you think that you can legally change your name to add a few trailing
spaces. :-)
When is a door not a door?
When it's a "door ".
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:euQJpVGQFHA.2252@.TK2MSFTNGP15.phx.gbl...
> One of the other code monkeys here posed an interesting question, though
> we
> can't devise an example: What if in some implementation, the trailing
> spaces have significant meaning, so that "bob" should not match "bob
> "?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Escriba coda ergo sum." -- vbSensei
>|||You can use LIKE operator in a join condition.
Example:
select
*
from
(
select 'Microsoft'
) as t1(colA)
inner join
(
select 'Microsoft' + space(1)
) as t2(colA)
on t1.colA like t2.colA
go
AMB
"Mike Labosh" wrote:

> One of the other code monkeys here posed an interesting question, though w
e
> can't devise an example: What if in some implementation, the trailing
> spaces have significant meaning, so that "bob" should not match "bob "
?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Escriba coda ergo sum." -- vbSensei
>
>

Blank page when exported to PDF

Hi all,

I have this report which has one table and 2 matixes one below the other. The 2 matrixes are very big matrixes (i mean they are wide enough to spill over to the next page). I have "PageBreakatStart" true for the 2 matixes.

Here is my problem. when i export this report to PDF, i get a blank page after the table. If i get rid of "PageBreakatStart", there is no blank page but matrix1 start right after the table which i don't want. can somebody tell me how to eliminate that blank page? Hope my explanation makes sense.

Hey rsri... what did you do to get around this problem?|||I am still looking for the answer. I did not get around this problem yet.

Biztalk Promoted Fields in SQL Schema

Now I promoted some fields in Biztalk which adds "comments" in an
<annotation> tag as shown below.
<xs:element name="Hipaa30TradingTestInclude">
<xs:annotation>
<xs:appinfo>
<b:properties>
<b:property name="ns0:__Name"
xpath="/*[local-name()='Hipaa30TradingTestInclude' and
namespace-uri()='http://TestSchemas.Hipaa30TradingTestInclude']/*[local-name
()='TradingPartnerCommon'
and
namespace-uri()='http://TestSchemas.TradingParterCommon']/*[local-name()='Na
me' and namespace-uri()='']" />
<b:property name="ns0:City"
xpath="/*[local-name()='Hipaa30TradingTestInclude' and
namespace-uri()='http://TestSchemas.Hipaa30TradingTestInclude']/*[local-name
()='TradingPartnerCommon'
and
namespace-uri()='http://TestSchemas.TradingParterCommon']/*[local-name()='Ci
ty' and namespace-uri()='']" />
<b:property name="ns0:State"
xpath="/*[local-name()='Hipaa30TradingTestInclude' and
namespace-uri()='http://TestSchemas.Hipaa30TradingTestInclude']/*[local-name
()='TradingPartnerCommon'
and
namespace-uri()='http://TestSchemas.TradingParterCommon']/*[local-name()='St
ate' and namespace-uri()='']" />
<b:property name="ns0:Zip"
xpath="/*[local-name()='Hipaa30TradingTestInclude' and
namespace-uri()='http://TestSchemas.Hipaa30TradingTestInclude']/*[local-name
()='TradingPartnerCommon'
and
namespace-uri()='http://TestSchemas.TradingParterCommon']/*[local-name()='Zi
p' and namespace-uri()='']" />
<b:property name="ns0:PrimaryContact"
xpath="/*[local-name()='Hipaa30TradingTestInclude' and
namespace-uri()='http://TestSchemas.Hipaa30TradingTestInclude']/*[local-name
()='TradingPartnerCommon'
and
namespace-uri()='http://TestSchemas.TradingParterCommon']/*[local-name()='Pr
imaryContact' and namespace-uri()='']" />
</b:properties>
</xs:appinfo>
</xs:annotation>
I have five "promoted fields" and i got the following error five times.
Msg 102, Level 15, State 1, Line 49
Incorrect syntax near 'Hipaa30TradingTestInclude'.
Msg 132, Level 15, State 1, Line 49
The label 'http' has already been declared. Label names must be unique
within a query batch or stored procedure.
Msg 132, Level 15, State 1, Line 50
The label 'http' has already been declared. Label names must be unique
within a query batch or stored procedure.
Msg 132, Level 15, State 1, Line 50
Seems like the parser should ignore anything in the <annotation> tag?
Of course, I can manually remove it, but I'm trying to plan ahead for how to
maintain this.
Thanks again,
Neal Walters
http://Biztalk-Training.comIf you're using a SQL string literal to insert your schemas, you need to
escape the single quotes anywhere they occur in the string by using double
quotes.
Cheers,
Bob
"Neal Walters" <NealWalters@.discussions.microsoft.com> wrote in message
news:ECD78F81-E1B2-42F3-8D3E-9D2987FB5031@.microsoft.com...
> Now I promoted some fields in Biztalk which adds "comments" in an
> <annotation> tag as shown below.
> <xs:element name="Hipaa30TradingTestInclude">
> <xs:annotation>
> <xs:appinfo>
> <b:properties>
> <b:property name="ns0:__Name"
> xpath="/*[local-name()='Hipaa30TradingTestInclude' and
> namespace-uri()='http://TestSchemas.Hipaa30TradingTestInclude']/*[local-na
me()='TradingPartnerCommon'
> and
> namespace-uri()='http://TestSchemas.TradingParterCommon']/*[local-name()='
Name'
> and namespace-uri()='']" />
> <b:property name="ns0:City"
> xpath="/*[local-name()='Hipaa30TradingTestInclude' and
> namespace-uri()='http://TestSchemas.Hipaa30TradingTestInclude']/*[local-na
me()='TradingPartnerCommon'
> and
> namespace-uri()='http://TestSchemas.TradingParterCommon']/*[local-name()='
City'
> and namespace-uri()='']" />
> <b:property name="ns0:State"
> xpath="/*[local-name()='Hipaa30TradingTestInclude' and
> namespace-uri()='http://TestSchemas.Hipaa30TradingTestInclude']/*[local-na
me()='TradingPartnerCommon'
> and
> namespace-uri()='http://TestSchemas.TradingParterCommon']/*[local-name()='
State'
> and namespace-uri()='']" />
> <b:property name="ns0:Zip"
> xpath="/*[local-name()='Hipaa30TradingTestInclude' and
> namespace-uri()='http://TestSchemas.Hipaa30TradingTestInclude']/*[local-na
me()='TradingPartnerCommon'
> and
> namespace-uri()='http://TestSchemas.TradingParterCommon']/*[local-name()='
Zip'
> and namespace-uri()='']" />
> <b:property name="ns0:PrimaryContact"
> xpath="/*[local-name()='Hipaa30TradingTestInclude' and
> namespace-uri()='http://TestSchemas.Hipaa30TradingTestInclude']/*[local-na
me()='TradingPartnerCommon'
> and
> namespace-uri()='http://TestSchemas.TradingParterCommon']/*[local-name()='
PrimaryContact'
> and namespace-uri()='']" />
> </b:properties>
> </xs:appinfo>
> </xs:annotation>
>
> I have five "promoted fields" and i got the following error five times.
> Msg 102, Level 15, State 1, Line 49
> Incorrect syntax near 'Hipaa30TradingTestInclude'.
> Msg 132, Level 15, State 1, Line 49
> The label 'http' has already been declared. Label names must be unique
> within a query batch or stored procedure.
> Msg 132, Level 15, State 1, Line 50
> The label 'http' has already been declared. Label names must be unique
> within a query batch or stored procedure.
> Msg 132, Level 15, State 1, Line 50
> Seems like the parser should ignore anything in the <annotation> tag?
> Of course, I can manually remove it, but I'm trying to plan ahead for how
> to
> maintain this.
>
> Thanks again,
> Neal Walters
> http://Biztalk-Training.com
>|||Also note that XML Schema collections will not preserve your annotations.
Put the schema also in your own table using the XML datatype if that is
important information.
Best regards
Michael
"Bob Beauchemin" <bobb_no_spam@.SQLskills.com> wrote in message
news:ewAdNfuMHHA.5064@.TK2MSFTNGP04.phx.gbl...
> If you're using a SQL string literal to insert your schemas, you need to
> escape the single quotes anywhere they occur in the string by using double
> quotes.
> Cheers,
> Bob
> "Neal Walters" <NealWalters@.discussions.microsoft.com> wrote in message
> news:ECD78F81-E1B2-42F3-8D3E-9D2987FB5031@.microsoft.com...
>

Biztalk Promoted Fields in SQL Schema

Now I promoted some fields in Biztalk which adds "comments" in an
<annotation> tag as shown below.
<xs:element name="Hipaa30TradingTestInclude">
<xs:annotation>
<xs:appinfo>
<b:properties>
<b:property name="ns0:__Name"
xpath="/*[local-name()='Hipaa30TradingTestInclude' and
namespace-uri()='http://TestSchemas.Hipaa30TradingTestInclude']/*[local-name()='TradingPartnerCommon'
and
namespace-uri()='http://TestSchemas.TradingParterCommon']/*[local-name()='Name' and namespace-uri()='']" />
<b:property name="ns0:City"
xpath="/*[local-name()='Hipaa30TradingTestInclude' and
namespace-uri()='http://TestSchemas.Hipaa30TradingTestInclude']/*[local-name()='TradingPartnerCommon'
and
namespace-uri()='http://TestSchemas.TradingParterCommon']/*[local-name()='City' and namespace-uri()='']" />
<b:property name="ns0:State"
xpath="/*[local-name()='Hipaa30TradingTestInclude' and
namespace-uri()='http://TestSchemas.Hipaa30TradingTestInclude']/*[local-name()='TradingPartnerCommon'
and
namespace-uri()='http://TestSchemas.TradingParterCommon']/*[local-name()='State' and namespace-uri()='']" />
<b:property name="ns0:Zip"
xpath="/*[local-name()='Hipaa30TradingTestInclude' and
namespace-uri()='http://TestSchemas.Hipaa30TradingTestInclude']/*[local-name()='TradingPartnerCommon'
and
namespace-uri()='http://TestSchemas.TradingParterCommon']/*[local-name()='Zip' and namespace-uri()='']" />
<b:property name="ns0:PrimaryContact"
xpath="/*[local-name()='Hipaa30TradingTestInclude' and
namespace-uri()='http://TestSchemas.Hipaa30TradingTestInclude']/*[local-name()='TradingPartnerCommon'
and
namespace-uri()='http://TestSchemas.TradingParterCommon']/*[local-name()='PrimaryContact' and namespace-uri()='']" />
</b:properties>
</xs:appinfo>
</xs:annotation>
I have five "promoted fields" and i got the following error five times.
Msg 102, Level 15, State 1, Line 49
Incorrect syntax near 'Hipaa30TradingTestInclude'.
Msg 132, Level 15, State 1, Line 49
The label 'http' has already been declared. Label names must be unique
within a query batch or stored procedure.
Msg 132, Level 15, State 1, Line 50
The label 'http' has already been declared. Label names must be unique
within a query batch or stored procedure.
Msg 132, Level 15, State 1, Line 50
Seems like the parser should ignore anything in the <annotation> tag?
Of course, I can manually remove it, but I'm trying to plan ahead for how to
maintain this.
Thanks again,
Neal Walters
http://Biztalk-Training.com
If you're using a SQL string literal to insert your schemas, you need to
escape the single quotes anywhere they occur in the string by using double
quotes.
Cheers,
Bob
"Neal Walters" <NealWalters@.discussions.microsoft.com> wrote in message
news:ECD78F81-E1B2-42F3-8D3E-9D2987FB5031@.microsoft.com...
> Now I promoted some fields in Biztalk which adds "comments" in an
> <annotation> tag as shown below.
> <xs:element name="Hipaa30TradingTestInclude">
> <xs:annotation>
> <xs:appinfo>
> <b:properties>
> <b:property name="ns0:__Name"
> xpath="/*[local-name()='Hipaa30TradingTestInclude' and
> namespace-uri()='http://TestSchemas.Hipaa30TradingTestInclude']/*[local-name()='TradingPartnerCommon'
> and
> namespace-uri()='http://TestSchemas.TradingParterCommon']/*[local-name()='Name'
> and namespace-uri()='']" />
> <b:property name="ns0:City"
> xpath="/*[local-name()='Hipaa30TradingTestInclude' and
> namespace-uri()='http://TestSchemas.Hipaa30TradingTestInclude']/*[local-name()='TradingPartnerCommon'
> and
> namespace-uri()='http://TestSchemas.TradingParterCommon']/*[local-name()='City'
> and namespace-uri()='']" />
> <b:property name="ns0:State"
> xpath="/*[local-name()='Hipaa30TradingTestInclude' and
> namespace-uri()='http://TestSchemas.Hipaa30TradingTestInclude']/*[local-name()='TradingPartnerCommon'
> and
> namespace-uri()='http://TestSchemas.TradingParterCommon']/*[local-name()='State'
> and namespace-uri()='']" />
> <b:property name="ns0:Zip"
> xpath="/*[local-name()='Hipaa30TradingTestInclude' and
> namespace-uri()='http://TestSchemas.Hipaa30TradingTestInclude']/*[local-name()='TradingPartnerCommon'
> and
> namespace-uri()='http://TestSchemas.TradingParterCommon']/*[local-name()='Zip'
> and namespace-uri()='']" />
> <b:property name="ns0:PrimaryContact"
> xpath="/*[local-name()='Hipaa30TradingTestInclude' and
> namespace-uri()='http://TestSchemas.Hipaa30TradingTestInclude']/*[local-name()='TradingPartnerCommon'
> and
> namespace-uri()='http://TestSchemas.TradingParterCommon']/*[local-name()='PrimaryContact'
> and namespace-uri()='']" />
> </b:properties>
> </xs:appinfo>
> </xs:annotation>
>
> I have five "promoted fields" and i got the following error five times.
> Msg 102, Level 15, State 1, Line 49
> Incorrect syntax near 'Hipaa30TradingTestInclude'.
> Msg 132, Level 15, State 1, Line 49
> The label 'http' has already been declared. Label names must be unique
> within a query batch or stored procedure.
> Msg 132, Level 15, State 1, Line 50
> The label 'http' has already been declared. Label names must be unique
> within a query batch or stored procedure.
> Msg 132, Level 15, State 1, Line 50
> Seems like the parser should ignore anything in the <annotation> tag?
> Of course, I can manually remove it, but I'm trying to plan ahead for how
> to
> maintain this.
>
> Thanks again,
> Neal Walters
> http://Biztalk-Training.com
>
|||Also note that XML Schema collections will not preserve your annotations.
Put the schema also in your own table using the XML datatype if that is
important information.
Best regards
Michael
"Bob Beauchemin" <bobb_no_spam@.SQLskills.com> wrote in message
news:ewAdNfuMHHA.5064@.TK2MSFTNGP04.phx.gbl...
> If you're using a SQL string literal to insert your schemas, you need to
> escape the single quotes anywhere they occur in the string by using double
> quotes.
> Cheers,
> Bob
> "Neal Walters" <NealWalters@.discussions.microsoft.com> wrote in message
> news:ECD78F81-E1B2-42F3-8D3E-9D2987FB5031@.microsoft.com...
>

bizarre query problem

Below is part of a stored proc.
This code returns an error ONLY if Field.OrderId is included in the query.
It runs ok if the OrderID is not included.
The error recieved is:
Server: Msg 512, Level 16, State 1, Procedure Jobs_TrackInserts, Line 18
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Running the code without field.OrderID returns..
(0 row(s) affected)
(1 row(s) affected) << ALSO: What does this represent ?
(297 row(s) affected)
CREATE TABLE [OrderRequest_Equipment] (
[OR_EQ_id] [int] IDENTITY (1, 1) NOT NULL ,
[OrderID] [int] NULL ,
[Class] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Preference] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Attachment] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UNIT] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MoveEarliest] [datetime] NULL ,
[MoveLatest] [datetime] NULL ,
[UnLoadEarliest] [datetime] NULL ,
[UnLoadLatest] [datetime] NULL ,
[EditedBy] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MoveType] [int] NULL ,
[OffRentID] [int] NULL ,
[Action] [bit] NOT NULL CONSTRAINT [DF_OrderRequest_Equipment_Action]
DEFAULT (0),
[FromSiteID] [int] NULL CONSTRAINT [DF_OrderRequest_Equipment_FromSiteID]
DEFAULT (0),
[RD] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RD] DEFAULT
(0),
[RW] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RW] DEFAULT
(0),
[RM] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RM] DEFAULT
(0),
[Dur] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[trucking] [smallmoney] NULL ,
[fuel] [smallmoney] NULL ,
[JobNumber] [int] NULL ,
[UnitNotes] [nchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FinishedAsOf] [smalldatetime] NULL ,
CONSTRAINT [PK_OrderRequest_ClassesNeeded] PRIMARY KEY NONCLUSTERED
(
[OR_EQ_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
----
--
CREATE TABLE [DCCPendingMoves] (
[jobNumber] [int] NOT NULL
) ON [PRIMARY]
GO
THIS IS THE QUERY CAUSING THE PROBLEM...
delete from bobmlt.dcc.dbo.OrderRequest_Equipment --Purge Jobs
OrderRequest_Equipment Table
set IDENTITY_INSERT OrderRequest_Equipment on
insert into OrderRequest_Equipment
(OrderID, OR_EQ_id, Class, Preference, Attachment, UNIT, MoveEarliest,
MoveLatest, UnLoadEarliest, UnLoadLatest, EditedBy, MoveType, OffRentID,
Action, FromSiteID, RD, RW, RM, Dur, trucking, fuel, JobNumber, UnitNotes,
FinishedAsOf)
select
OrderID, OR_EQ_id, Class, Preference, Attachment, UNIT, MoveEarliest,
MoveLatest, UnLoadEarliest, UnLoadLatest, EditedBy, MoveType, OffRentID,
Action, FromSiteID, RD, RW, RM, Dur, trucking, fuel, JobNumber, UnitNotes,
FinishedAsOf
from bobmlt.dcc.dbo.DCCPendingMoves_ore
set IDENTITY_INSERT OrderRequest_Equipment off
Thanks in advance for any help on this...
Bob McClellan.> (1 row(s) affected) << ALSO: What does this represent ?
This is a clue. You probably have an INSERT trigger on the table that is
not written correctly to handle multi-row inserts.
Hope this helps.
Dan Guzman
SQL Server MVP
"John 3:16" <bobmcc@.tricoequipment.com> wrote in message
news:emOh9lxBGHA.740@.TK2MSFTNGP12.phx.gbl...
> Below is part of a stored proc.
> This code returns an error ONLY if Field.OrderId is included in the query.
> It runs ok if the OrderID is not included.
> The error recieved is:
> Server: Msg 512, Level 16, State 1, Procedure Jobs_TrackInserts, Line 18
> Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
> expression.
> The statement has been terminated.
> Running the code without field.OrderID returns..
> (0 row(s) affected)
>
> (1 row(s) affected) << ALSO: What does this represent ?
>
> (297 row(s) affected)
>
> CREATE TABLE [OrderRequest_Equipment] (
> [OR_EQ_id] [int] IDENTITY (1, 1) NOT NULL ,
> [OrderID] [int] NULL ,
> [Class] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Preference] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Attachment] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [UNIT] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MoveEarliest] [datetime] NULL ,
> [MoveLatest] [datetime] NULL ,
> [UnLoadEarliest] [datetime] NULL ,
> [UnLoadLatest] [datetime] NULL ,
> [EditedBy] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MoveType] [int] NULL ,
> [OffRentID] [int] NULL ,
> [Action] [bit] NOT NULL CONSTRAINT [DF_OrderRequest_Equipment_Action]
> DEFAULT (0),
> [FromSiteID] [int] NULL CONSTRAINT [DF_OrderRequest_Equipment_FromSiteID]
> DEFAULT (0),
> [RD] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RD] DEFAULT
> (0),
> [RW] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RW] DEFAULT
> (0),
> [RM] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RM] DEFAULT
> (0),
> [Dur] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [trucking] [smallmoney] NULL ,
> [fuel] [smallmoney] NULL ,
> [JobNumber] [int] NULL ,
> [UnitNotes] [nchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [FinishedAsOf] [smalldatetime] NULL ,
> CONSTRAINT [PK_OrderRequest_ClassesNeeded] PRIMARY KEY NONCLUSTERED
> (
> [OR_EQ_id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> ----
--
> CREATE TABLE [DCCPendingMoves] (
> [jobNumber] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
>
> THIS IS THE QUERY CAUSING THE PROBLEM...
> delete from bobmlt.dcc.dbo.OrderRequest_Equipment --Purge Jobs
> OrderRequest_Equipment Table
> set IDENTITY_INSERT OrderRequest_Equipment on
> insert into OrderRequest_Equipment
> (OrderID, OR_EQ_id, Class, Preference, Attachment, UNIT, MoveEarliest,
> MoveLatest, UnLoadEarliest, UnLoadLatest, EditedBy, MoveType, OffRentID,
> Action, FromSiteID, RD, RW, RM, Dur, trucking, fuel, JobNumber, UnitNotes,
> FinishedAsOf)
> select
> OrderID, OR_EQ_id, Class, Preference, Attachment, UNIT, MoveEarliest,
> MoveLatest, UnLoadEarliest, UnLoadLatest, EditedBy, MoveType, OffRentID,
> Action, FromSiteID, RD, RW, RM, Dur, trucking, fuel, JobNumber, UnitNotes,
> FinishedAsOf
> from bobmlt.dcc.dbo.DCCPendingMoves_ore
> set IDENTITY_INSERT OrderRequest_Equipment off
>
> Thanks in advance for any help on this...
> Bob McClellan.
>|||Seems that you have a trigger defined on the table whch isn=B4t able to
process multiple rows affected.
Triggers are fired on a statement basis NOT on a row basis...
HTH, jens Suessmeyer.|||Awesome...
Thanks guys...
"John 3:16" <bobmcc@.tricoequipment.com> wrote in message
news:emOh9lxBGHA.740@.TK2MSFTNGP12.phx.gbl...
> Below is part of a stored proc.
> This code returns an error ONLY if Field.OrderId is included in the query.
> It runs ok if the OrderID is not included.
> The error recieved is:
> Server: Msg 512, Level 16, State 1, Procedure Jobs_TrackInserts, Line 18
> Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
> expression.
> The statement has been terminated.
> Running the code without field.OrderID returns..
> (0 row(s) affected)
>
> (1 row(s) affected) << ALSO: What does this represent ?
>
> (297 row(s) affected)
>
> CREATE TABLE [OrderRequest_Equipment] (
> [OR_EQ_id] [int] IDENTITY (1, 1) NOT NULL ,
> [OrderID] [int] NULL ,
> [Class] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Preference] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Attachment] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [UNIT] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MoveEarliest] [datetime] NULL ,
> [MoveLatest] [datetime] NULL ,
> [UnLoadEarliest] [datetime] NULL ,
> [UnLoadLatest] [datetime] NULL ,
> [EditedBy] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MoveType] [int] NULL ,
> [OffRentID] [int] NULL ,
> [Action] [bit] NOT NULL CONSTRAINT [DF_OrderRequest_Equipment_Action]
> DEFAULT (0),
> [FromSiteID] [int] NULL CONSTRAINT [DF_OrderRequest_Equipment_FromSiteID]
> DEFAULT (0),
> [RD] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RD] DEFAULT
> (0),
> [RW] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RW] DEFAULT
> (0),
> [RM] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RM] DEFAULT
> (0),
> [Dur] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [trucking] [smallmoney] NULL ,
> [fuel] [smallmoney] NULL ,
> [JobNumber] [int] NULL ,
> [UnitNotes] [nchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [FinishedAsOf] [smalldatetime] NULL ,
> CONSTRAINT [PK_OrderRequest_ClassesNeeded] PRIMARY KEY NONCLUSTERED
> (
> [OR_EQ_id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> ----
--
> CREATE TABLE [DCCPendingMoves] (
> [jobNumber] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
>
> THIS IS THE QUERY CAUSING THE PROBLEM...
> delete from bobmlt.dcc.dbo.OrderRequest_Equipment --Purge Jobs
> OrderRequest_Equipment Table
> set IDENTITY_INSERT OrderRequest_Equipment on
> insert into OrderRequest_Equipment
> (OrderID, OR_EQ_id, Class, Preference, Attachment, UNIT, MoveEarliest,
> MoveLatest, UnLoadEarliest, UnLoadLatest, EditedBy, MoveType, OffRentID,
> Action, FromSiteID, RD, RW, RM, Dur, trucking, fuel, JobNumber, UnitNotes,
> FinishedAsOf)
> select
> OrderID, OR_EQ_id, Class, Preference, Attachment, UNIT, MoveEarliest,
> MoveLatest, UnLoadEarliest, UnLoadLatest, EditedBy, MoveType, OffRentID,
> Action, FromSiteID, RD, RW, RM, Dur, trucking, fuel, JobNumber, UnitNotes,
> FinishedAsOf
> from bobmlt.dcc.dbo.DCCPendingMoves_ore
> set IDENTITY_INSERT OrderRequest_Equipment off
>
> Thanks in advance for any help on this...
> Bob McClellan.
>