Possibly Incorrect Query Result

Jul 13, 2006

/* Test table */
create table test (c1 char(1), c2 varchar(1));
insert into test values ('','');

/* Query */
select
c1,
len(c1) len_c1,
c2,
len(c2) len_c2
from test

The result of the len(c1) expression is 0. I would expect the correct result to be 1, since "c1" is a fixed-length character string type and the values are right-padded with spaces to fit the defined length, in this case 1.

I'm using SQL Server 2005.

Regards,
Ole Willy Tuv

View 1 Replies

SUM And JOIN And Possibly GROUP BY - Incorrect Value Returned By SUM

Mar 23, 2006

It's me again :)

So; if you read my earlier thread here (http://www.dbforums.com/showthread.php?t=1214353), you'll know that I'm trying to build stored procedures to deal with ticketing queries, and that it's all getting a bit complicated. I have, however, made a bit of progress and now have the following working:

CREATE PROCEDURE [dbo].[getAvailableTickets]
@eventId INT,
@standId INT,
@admissionDateId INT,
@concessionId INT,
@userId INT

AS

DECLARE @startyear DATETIME
DECLARE @endyear DATETIME
SELECT @startyear=CONVERT(datetime, '2006/01/01')
SELECT @endyear=CONVERT(datetime, '2006/12/31')

SELECT
[tblTickets].[id] AS ticketId,
[tblEvents].[id] AS eventId,
[tblStands].[id] AS standId,
[tblAdmissionDates].[id] AS admitDateId,
[tblEvents].[event_name],
[tblStands].[stand_name],
[tblTicketConcessions].[concession_name],
[tblMemberships].[membership_name],
[tblAdmissionDates].[admission_start_date],
[tblAdmissionDates].[admission_end_date],
[tblBookingMinQuantities]. AS minBookingQuantity,
[tblBookingMaxQuantities].[booking_quantity] AS maxBookingQuantity,
MIN([tblQuotas].[quota]) AS Quota,
[B]SUM([tblBasket].[ticket_quantity]) AS History,
[tblTickets].[price],
[tblTickets].[availability]

FROM [tblTickets]
LEFT JOIN [tblEvents]ON [tblEvents].[id] = [tblTickets].[event_id]
LEFT JOIN [tblStands]ON [tblStands].[id] = [tblTickets].[stand_id]
LEFT JOIN [tblBookingDates]ON [tblBookingDates].[id] = [tblTickets].
LEFT JOIN [tblTicketConcessions]ON [tblTicketConcessions].[id] = [tblTickets].[ticket_concession_id]
LEFT JOIN [tblBookingQuantities] AS tblBookingMinQuantities ON [tblBookingMinQuantities].[id] = [tblTickets].[booking_min_quantity_id]
LEFT JOIN [tblBookingQuantities] AS tblBookingMaxQuantitiesON [tblBookingMaxQuantities].[id] = [tblTickets].[booking_max_quantity_id]
LEFT JOIN [tblAdmissionDates]ON [tblAdmissionDates].[id] = [tblTickets].[admission_date_id]
LEFT JOIN [tblMemberships]ON [tblMemberships].[id] = [tblTickets].[membership_id]
LEFT JOIN [tblQuotas]ON
([tblQuotas].[event_id] = [tblTickets].[event_id] OR [tblQuotas].[event_id] IS NULL) AND
([tblQuotas].[stand_id] = [tblTickets].[stand_id] OR [tblQuotas].[stand_id] IS NULL) AND
([tblQuotas].[admission_date_id] = [tblTickets].[admission_date_id] OR [tblQuotas].[admission_date_id] IS NULL) AND
([tblQuotas].[concession_id] = [tblTickets].[ticket_concession_id] OR [tblQuotas].[concession_id] IS NULL) AND
([tblQuotas].[membership_id] = [tblTickets].[membership_id] OR [tblQuotas].[membership_id] IS NULL) AND
([tblQuotas].[ticket_id] = [tblTickets].[id] OR [tblQuotas].[ticket_id] IS NULL)
[B]LEFT JOIN [tblBasket] ON [tblBasket].[ticket_id] = [tblTickets].[id]
LEFT JOIN [tblOrders] ON [tblOrders].[id] = [tblBasket].[order_id]

WHERE 1=1
AND ([tblTickets].[ticket_open] = 1)
AND (([tblEvents].[id] = @eventId OR @eventId = 0)AND ([tblEvents].[event_open] = 1))
AND (([tblStands].[id] = @standId OR @standId = 0)AND ([tblStands].[stand_open] = 1))
AND (([tblAdmissionDates].[id] = @admissionDateId OR @admissionDateId = 0)AND ([tblAdmissionDates].[date_open] = 1))
AND ([tblTicketConcessions].[id] = @concessionId OR @concessionId = 0)
AND ((getdate() BETWEEN [tblBookingDates]. AND [tblBookingDates].[booking_end_date]) OR ([tblBookingDates].[booking_start_date] IS NULL AND [tblBookingDates].[booking_end_date] IS NULL))
AND (([tblMemberships].[id] IN (SELECT [membership_id] FROM [tblUsers_Memberships] WHERE [user_id]=@userId)) OR [tblMemberships].[id] IS NULL)
[B]AND ([tblOrders].[user_id] = @userId OR @userId=0)

GROUP BY
[tblTickets].[id],
[tblEvents].[id],
[tblStands].[id],
[tblAdmissionDates].[id],
[tblEvents].[event_name],
[tblStands].[stand_name],
[tblTicketConcessions].[concession_name],
[tblMemberships].[membership_name],
[tblAdmissionDates].[admission_start_date],
[tblAdmissionDates].[admission_end_date],
[tblBookingMinQuantities].[booking_quantity],
[tblBookingMaxQuantities].[booking_quantity],
[tblTickets].[price],
[tblTickets].[availability]
GO

Except... there's two problems with it. One is that it only returns tickets that you've already bought, the other is that it doens't work out correctly how many of those tickets you've bought in past orders.

This is what's in tblBasket:

idticket_idquantityorder_iddate
152321/03/2006
262321/03/2006
3144421/03/2006
4154421/03/2006
551421/03/2006

Both the orders in there are for the same user id: "1". All the tickets in there are tied to event "2".

When I run Exec dbo.getAvailableTickets 2,0,0,0,1, it tells me it's found 6 of ticket 5, 4 of ticket 6 and 12 of ticket 14. And I can't for the life of me figure out how it's calculating it. Any ideas?

And how do i get it to return all tickets regardless of whether you've bought them previously or not?

View 4 Replies View Related

Possibly Extremely Simple SQL Query

Jan 27, 2004

Right, I'm no SQL programmer. As I type this, I have roughly the third the hair I had at 5 o'clock last night. I even lost sleep over it.

I'm trying to return a list of records from a database holding organisation names. As I've built a table to hold record versions, the key fields (with sample data) from a View I created to display this is as follows:

record_id-----org_id-----live-----version
====== ===== === =====
1-------------1----------0----------1
2-------------2----------0----------1
3-------------1----------1----------2
4-------------2----------0----------2

as you can see the record id will always be unique. record 3 is a newer version of record 1, and 4 of 2. the issue is thus: i only want to return unique organisations. if a version of the organisation record is live on the system (in this case record id 3), i want to return the live version with its unique record id. i'm assuming for this i can perform a simple "SELECT WHERE live = 1" query.

however, some organisations will have no live versions (see org with id 2). i still wish to return a record for this organisation, but in this case the most recent version ie version 2 (and again - its unique record id)

in actual fact, it seems so much clearer when laid out like this. however, i feel it's not going to happen this end, and so any help would be greatly appreciated.

many thanks in advance,

phil

View 2 Replies View Related

Query Help - Select Distinct Possibly?

Oct 26, 2006

I need to return the current case cost for every UPC in my table. In my current query I return case costs that have an effective date of today or earlier. The problem is that in my results, one UPC may have two or more case costs that were are effective <= GETDATE(). I can sort it by effective date (DESC) so I know the first of every UPC in my results will be the current effective case cost, but how do I modify my query so that in my result set I only get the first of every UPC?

Here is my query:

SELECT factCaseCosts.nUpcKey, factCaseCosts.dCaseCost, factCaseCosts.dtEffectiveDate
FROM factCaseCosts
WHERE (factCaseCosts.dtEffectiveDate <= GETDATE())
ORDER BY dtEffectiveDate DESC

Here is my current result set:
52023.762006-08-01 00:00:00
52023.762006-02-18 00:00:00
52123.762006-08-01 00:00:00
52123.762006-02-18 00:00:00
52230.362006-08-01 00:00:00
52230.362006-02-18 00:00:00
52323.762006-08-01 00:00:00
52323.762006-02-18 00:00:00

I only want the first 520 returned, the first 521 returned, the first 522 returned, and the first 523 returned.
How can I do this?
Thanks!

View 3 Replies View Related

Possibly Simple Query But I'm Not Good Enough To Fathom It!

Mar 8, 2006

Hi AllI know that I should supply the DDL for the tables I'm going to talk about,but I'm not 100% on how to generate them just yet. Hopefully my question ismore a query methodology question than how the tables are constructed.My first attempt at the query is as follows:SELECT st.STOCKID, sd.FULLDESCRIPTION, sc.NAME, Sum(sq.QUANTITYINSTOCK) AS'Qty In Stock Total', Sum(st.QUANTITY) AS 'Qty Sold'FROM STOCK s, STOCKCATEGORIES sc, STOCKDESCRIPTIONS sd, STOCKQUANTITIES sq,STOCKTRANSACTIONS stWHERE sc.STOCKCATEGORYID = s.STOCKCATEGORYID AND st.STOCKID = s.STOCKID ANDsd.STOCKID = s.STOCKIDAND sq.STOCKID = s.STOCKID AND (sd.LANGUAGEID='UK') AND(st.TRANSACTIONTYPE=8) AND(sq.QUANTITYINSTOCK > 0)GROUP BY st.STOCKID, sd.FULLDESCRIPTION, sc.NAMEThis works in a fashion, but I need to sort of query the stocktransactionstable again to get the sum of the st.QUANTITY table for st.TRANSACTIONTYPE=1so that I can have an extra select field of Sum(st.QUANTITY) AS 'QtyOrdered', ie transactions with transactiontype of 1 are sales orders andtype 8 are invoices.I basically need to get a report result of:PRODUCT, DESCRIPTION, CATEGORY, CURRENT_STOCK_QTY, SALES_IN_PERIOD,ORDERS_IN_PERIODIs there any pointers whatsoever you can give me to try and get thisdouble-double query to work?Many thanks.Rgds Laphan

View 3 Replies View Related

Select Distinct - Incorrect Result

Jul 20, 2005

I have a function that is designed to return a variable that containsconcatenated values from a partinular field in the returned rows:DECLARE @output varchar(8000)SELECT@output =CASEWHEN @output IS NULL THEN CAST(TSD.ScheduledTime ASvarchar(4))ELSE @output+ ', '+ ISNULL(CAST(TSD.ScheduledTime ASvarchar(4)),'')ENDFROM TSDWHERE ClientGUID = 2000001447020001 ANDParentGUID = 6000006684068001Select @outputThe variable returned with this code contains:"1200, 1400, 1200, 1400"I want to only get the unique values so that the variable returns "1200,1400". Seems simple enough just to add DISTINCT to the SELECT statement.However, what is returned is simply "1400".I cannot figure out why that is the case. Is there any explanation to thisresult?Side note: I can work around this by using a cursor but I would like toknow why DISTINCT does not work.Many thanks in advance for any help that can be provided!Pat

View 5 Replies View Related

Incorrect Order Result Set When Join Table

Mar 24, 2002

Hi all,
I faced a problem, I have two tables - part and partmaster
part : part_no, part_qty (no key)
partmaster : part_no, part_description (primary key : part_no )

I want to select table part.* and partmaster.part_description.

(run on mssql 2k)
select a.*, b.part_description
from part a, partmaster b where a.part_no *= b.part_no

I want to and expect to have the result order like table "part". However, after the join, the order is different. I try to run it on mssql 7.0, the order is ok.

Then I modify and run the statement select a.* from part a, partmaster b where a.part_no *= b.part_no on 2k again. The result order is ok.

can anyone tell me the reason?

Now I try to fix this problem is adding a sequence field "part_seq" into table "part" and run the statement by adding a order by part_seq.
It does work!

Regards,
Simon

View 1 Replies View Related

Saving Query Result To A File , When View Result Got TLV Error

Feb 13, 2001

HI,
I ran a select * from customers where state ='va', this is the result...

(29 row(s) affected)
The following file has been saved successfully:
C:outputcustomers.rpt 10826 bytes

I choose Query select to a file
then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend
invalid TLV record

Thanks for your help

Ali

View 1 Replies View Related

End Result Is Main Query Results Ordered By Nested Result

May 1, 2008

As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC.
Tables I have are structured as follows:

SHOWS
showID
showTitle

SHOWACCESS
showID
remoteID

VIDEOS
videoDate
showID

SQL is as follows:

SELECT shows.showID AS showID, shows.showTitle AS showTitle,
(SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID)
FROM shows, showAccess
WHERE shows.showID = showAccess.showID
AND showAccess.remoteID=21
ORDER BY vidDate DESC;

I had it ordering by showTitle and it worked fine, but I need it to order by vidDate.
Can anyone shed some light on where I am going wrong?

thanks

View 3 Replies View Related

Possibly A BUG In 6.5

Dec 8, 1999

I have a situation where I may have found a bug in SQL 6.5. I have a process that creates a temp table, then calls a stored procedure that uses that temp table, and the temp table has an identity column on it. The second procedure declares a cursor using an ORDER BY clause on the identity column and spins through the records, updating each row. The below sample is a test case that I think proves the bug. If you remove the ORDER BY clause from the cursor declaration, it works fine. Because of the nature of our data, we have to have the ORDER BY clause.

If you put the following code in a window, run it, you'll see. Then remove the ORDER BY and rerun and you'll get good results. Has anyone seen this or know of a work around? The only solution I have for a workaround is to select the data from the temp table into another temp table and declare the cursor on the second temp table. That seems to work fine. Any suggestions will be appreciated.

SQL SERVER 6.5 - SP5a; NT 4.0 SP5

Thanks,
Mike Nolen
Dealer Solutions Sr. DBA
Houston, TX

-----------SNIP AND RUN------------
-- just needed for the below proc to compile...
create table #mn_temp (
col1 int identity NOT NULL,
col2 char(1)NOT NULL
)
go

drop proc mn2
go
create proc mn2
as
begin
declare @iint, @cchar(1)

-- insert a few records
insert into #mn_temp values ('A')
insert into #mn_temp values ('B')
insert into #mn_temp values ('C')
insert into #mn_temp values ('D')
insert into #mn_temp values ('E')

-- cursor should spin through 5 records
declare aaa cursor for
select col1, col2 from #mn_temp
order by col1

open aaa

fetch next from aaa into @i, @c
select @@fetch_status "fs", @i "ident", @c "char"

while (@@fetch_status <> -1)
begin
print 'In while loop'

fetch next from aaa into @i, @c

-- should print 1A, 2B, 3C, 4D, 5E...but it doesn't!!!
select @@fetch_status "fs", @i "ident", @c "char"
end
close aaa
deallocate aaa
end
go

-- temp table no longer needed...drop it before compiling mn0...
drop table #mn_temp
go

drop proc mn0
go
create proc mn0
as
begin
create table #mn_temp (
col1 int identity NOT NULL,
col2 char(1) NOT NULL )

exec mn2
end
go

View 4 Replies View Related

SQL Query Incorrect Syntax Help

Mar 4, 2007

I am getting a SQLExcepetion error near , in this query string...so obvicously my query string is wrong...
could someone help me get this query string right please...

Thanks
"Select OrgID, OrgName From aspnet_OrgNames Where UserID = @UserID, OrgID = @OrgID"

View 1 Replies View Related

Incorrect Syntax Near '4' - But No '4' In Query

Oct 13, 2005

I'm using the following vbScript and T-SQL and receiving a seemingly strange error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '4'.

vb Code:

Original
- vb Code

intUserID = 124

Set quizCmd = Server.CreateObject("ADODB.Command")
Set SQLConn = Server.CreateObject("ADODB.Connection")
SQLConn.Open Application("DBCONNECTION")

quizCmd.ActiveConnection = SQLConn
quizCmd.CommandText = "checkComplete"
quizCmd.CommandType = adCmdStoredProc
quizCmd.Parameters.Append quizCmd.CreateParameter("@userID", adInteger, adParamInput, 4, intUserID)
quizCmd.Parameters.Append quizCmd.CreateParameter("@status", adVarChar, adParamOutput, 6, 0)
quizCmd.Execute

intUserID = 124 Set quizCmd = Server.CreateObject("ADODB.Command")Set SQLConn = Server.CreateObject("ADODB.Connection")SQLConn.Open Application("DBCONNECTION") quizCmd.ActiveConnection = SQLConnquizCmd.CommandText = "checkComplete"quizCmd.CommandType = adCmdStoredProcquizCmd.Parameters.Append quizCmd.CreateParameter("@userID", adInteger, adParamInput, 4, intUserID)quizCmd.Parameters.Append quizCmd.CreateParameter("@status", adVarChar, adParamOutput, 6, 0)quizCmd.Execute

sql Code:

Original
- sql Code

CREATE PROCEDURE [dbo].[checkComplete]

@userID int,
@status varchar (6) output

AS

declare @complete int

Set @complete = (SELECT Count(pass) as num FROM tblAttempts WHERE userID = @userID AND pass = 1)

If (@complete = 24)
Begin
Set @status = 'OK'
End
Else
Begin
Set @status = 'NOT OK'
End
GO

CREATE PROCEDURE [dbo].[checkComplete] @userID int,@status varchar (6) output AS DECLARE @complete int SET @complete = (SELECT COUNT(pass) AS num FROM tblAttempts WHERE userID = @userID AND pass = 1) IF (@complete = 24) BEGIN SET @status = 'OK' ENDELSE BEGIN SET @status = 'NOT OK' ENDGO

For reference, I have tried changing intUserID to be a different value (e.g. 13) to ensure it is not that '4' in question - likewise for @complete = 24 (e.g. 23).

Any ideas where the 4 is coming from and what is wrong here?

View 1 Replies View Related

Incorrect Query Text

Nov 13, 2007

I have this as my query text

SELECT MachNumber, ShiftNumber, HeatNumber, PartNumber,

RevNumber, CureTime, Bump1, Bump1Interval, Bump2,
Bump2Interval, DwellTime, LateToDrop, EarlyToDrop,
OverTime, CycleTime, ChangeTime, Date
FROM HeatInfo
WHERE (ShiftNumber = @ShiftNumber)

AND (Date >= @StartDate) AND (Date <= @EndDate)

This correctly returns 4 rows, which is great except that I would like to have these records Sorted by the Date. So I added the OrderBy command to the bottom of the query, like this

SELECT MachNumber, ShiftNumber, HeatNumber, PartNumber,

RevNumber, CureTime, Bump1, Bump1Interval, Bump2,
Bump2Interval, DwellTime, LateToDrop, EarlyToDrop,
OverTime, CycleTime, ChangeTime, Date
FROM HeatInfo
WHERE (ShiftNumber = @ShiftNumber)

AND (Date >= @StartDate) AND (Date <= @EndDate)
ORDER BY Date

This query returns 181 rows! whis is not great. What am I doing wrong here, I suppose that it is something extreamly obvious or I would probably have figured it out.

View 6 Replies View Related

Incorrect Query Plan

Nov 16, 2007

I was doing a demo last night, something that I've done hundreds of times already. Last night was the first time that it has failed to work. I was trying to show what the sys.dm_db_missing_index_* DMVs can provide.

AdventureWorks database

I'm running the following query:

select city from person.address where city like 'A%'

This is supposed to produce a table scan which in turn will obviously cause SQL Server to detect that an index could be beneficial. However, it does a clustered index scan (yes, I know, basically the same thing) instead and I see absolutely nothing appear in the DMVs. I pulled the data out into a dummy table that did not have a primary key either using the following:
select * into person.tmpaddress from person.address

I then execute the same query and get a table scan which is expected:

select city from person.address where city like 'A%'

However, it does not matter how much I execute that query or any other permutation of explicit query, absolutely nothing at all gets logged into the sys.dm_db_missing_index_* DMVs. I have also tried this same type of thing with several other tables in the AW database and can not find a single query which will cause anything to be logged to these DMVs. It seems that something is broken, but for the life of me, I can't figure out what is wrong. No weird settings, I'm running as sa, etc.

I can run queries like this in other databases and stuff gets immediately logged to the DMVs as expected. Any ideas?

View 5 Replies View Related

Possibly Merge Join Bug?

Mar 27, 2006

i'm merge joining 2 data sources, one is oracle and the other is excel...the problem is in the oracle source, it's a sql statement like:

select hdr.div_ord_no, hdr.mtr_no, hdr.prod_cd
from qctrl_div_ord_header hdr,
(select max(sub.eff_dt_from) min_eff_dt_from, div_ord_no
from qctrl_div_ord_header sub
group by div_ord_no
) tmp
where hdr.eff_dt_from = tmp.min_eff_dt_from
and hdr.div_ord_no = tmp.div_ord_no

having that sql statement, merging will come out with 0 rows

however, having a simple query like:

select hdr.div_ord_no, hdr.mtr_no, hdr.prod_cd
from qctrl_div_ord_header hdr

merging will come out with 2 rows

you may think that the data in the first sql statement is not there for the merge, which causing the 0 rows, however, the data is there, i'm only joining by one column and definitely the data is there, the merge result should be 2 rows for both query statements

i believe this is a problem with SSIS, anyway around this?

View 2 Replies View Related

XML Query Error Incorrect Syntax Near '&&<'

Feb 6, 2007

Hi all,

I use

="<Query><XmlData>" & Parameters!XMLData.Value & "</XmlData><ElementPath>Product {@}</ElementPath></Query>"

and I enter XMLData with value "<Products><Product>Chair</Product><Product>Table</Product></Products>"

for the query expression of the dataset, and I always got error:

Query failed for dataset "Dataset1"

Incorrect syntax near '<'

What's wrong with it?

Thanks,

Jone

View 1 Replies View Related

Autogrow Of Log File Possibly Cause Timeout?

Jan 30, 2007

i had a problem with a server instance where an app processing a file and putting the data into the database using a stored procedure was timing out. the app is a service and in the case of errors, will try to process the file until it has been succesfully added data to the database. in the event viewer i keep seeing this message during this timeout period:

"Autogrow of file 'mydatabase_log' in database 'mydatabase' was cancelled by user or timed out after 15687 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size."

is there a possibility that this process was causing my SP to timeout? are there any other log or debug files that SQL Express creates that might be helpful in discovering what caused the timeout? currently the log is at... ouch 10gigs restricted... maybe trying to autogrow from this while restricted is hogging resources?

View 9 Replies View Related

Question About Database Mirroring And Possibly NLB

Sep 12, 2006

OK since my company really would not prefer to spend the money for Windows 2003 Enterprise and MS SQL 2005 Enterprise I was thinking about this idea...

Server1 - W2K3 Std - SQL2005 Std - Primary
Server2 - W2K3 Std - SQL2005 Std - Mirror
Server3 - W2K3 Std - SQL2005 Std - Witness
Server4 - W2K3 Std - SQL2005 Std - Processing

OK So we have Server1 & Server2 doing the Database Mirroring. Then I have Server3 as the Witness to say who is the Primary and so forth. Now Server4 I thinking to us to create jobs that will run on a Schedule to pull data from our Ticketing system and import it into Server1 & Server2 (Yes this is a Datawarehouse).. So what I am wondering... We really wish to not have to change our connections to the DB with the Failover connection string and just use a common name. Since we are not usng Enterprise I can not do the Failover Clustering... So what I was wondering is if we could use the MS NLB. So for example with Server1 being primary and Server2 being Mirror... Server1 dies or goes down for maintance or whatever... Then Server3 will notice that and say OK Server2 you are now primary but also have Server3 go into the MS NLB and say Server3 Online and take all connections. Then when Server1 comes back on-line it is set as the Mirror system and is Offline in the NLB... Is this possible? If yes can someone either A... Give me directions on how to do that... or B shot me some URL's where I can read up on this stuff?

Thanks for all your help anyone is able to provide. It is greatly appricated.

Billy S.

View 14 Replies View Related

SP To Perform Query Based On Multiple Rows From Another Query's Result Set

Nov 7, 2007

I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w. one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included)         INSERT INTO @users            SELECT u.userName,u.userID                FROM tableU u               LEFT JOIN tableW w ON w.workgroupID = u.workgroupID               WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is:             SELECT u.userName,u.userID
               FROM tableU u
               LEFT JOIN tableW w ON w.workgroupID = u.workgroupID
               WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!

View 1 Replies View Related

Ms Query Sql Error 'Incorrect Column Expression'

Jul 22, 2005

I am getting an error from the case part of the select statement below which reads 'Incorrect Column Expression' then it quotes the case statement. All I am trying to do is convert and return the weight value to kilos if it was entered in pounds.

SELECT Salesinv.Unique, Salesinv.SalesNo, Salesinv.PurchNo, Salesinv.SalesInvNo, Salesinv.InvValue,

(case when Salesinv.WUnits = 'Llb' then round(Salesinv.NettWeight/2.2046,0) else Salesinv.NettWeight end)

FROM Salesinv Salesinv
WHERE (Salesinv.Unique>=38397.3092 And Salesinv.Unique<=38537.39885)

Any help would be greatly appreciated, hopefully thanks in advance.

View 12 Replies View Related

Incorrect Column Expression With CHAR In MS Query

Jan 16, 2014

I am working with Excel, then within Excel I am using MS Query to query a database. I am trying to use the CAST function on a field with numbers (1,2 or 3 digits) so I can convert it to a text value with three digits, i.e. 1 would read 001, 12 would read 012, etc.

I am not using CAST in the design grid. Is this even possible?

I am modifying the underlying SQL code. Here is the line that is giving me trouble:

CAST(GL02GLF.GLF_SEQ_NUM as CHAR(3)) as “Sequence”

View 1 Replies View Related

Incorrect Query Results, Could Use Fresh Eyes

Jun 4, 2007

I have three tables:

Category: category id, category name, more…
Topic: topic id, topic name, category id, more…
Post: post id, post text, topic id, more…

I need help with a query to display the following:

Category name, # of topics, # of posts

Example:
Category.........................Topics.....Posts
SQL Stored Procedures.........12........562

It’s coming along but there are some problems, ASP.NET actually has 2 posts not 1. And the java totals are correct but it should be Java, 3, 10 (all in one line)
Category.....Topics...Posts
ASP.NET.........2........1
C#................1........1
Java..............1........1
Java..............1........2
Java..............1........7

Overview: use category id to get count of topics then use the topic id to get the count of posts.

SELECT C.CategoryName, T.ThreadCount AS Threads, T.PostCount AS Posts
FROM Category AS C LEFT OUTER JOIN
(SELECT tt.CategoryID, PostID.PostCount, COUNT(tt.ThreadName) AS ThreadCount
FROM Thread AS tt LEFT OUTER JOIN
(SELECT ThreadID, COUNT(PostID) AS PostCount
FROM Post AS P
GROUP BY ThreadID) AS PostID
ON tt.ThreadID = PostID.ThreadID
GROUP BY tt.CategoryID, PostID.PostCount) AS T
ON C.CategoryID = T.CategoryID
WHERE (C.CategoryID = T.CategoryID)
GROUP BY C.CategoryName, T.ThreadCount, T.PostCount
ORDER BY C.CategoryName

Thanks in advance

View 6 Replies View Related

Incorrect Results With T-sql Query In SQL Server 2005

Apr 18, 2006

I'm seeing some change in behavior for a query in SQL Server 2005 (compared to behavior in SQL Server 2000). The query is as follows:
------------
create table #projects (projectid int) insert into #projects select projectid from tblprojects where istemplate = 0 and projecttemplateid = 365

Select distinct tblProjects.ProjectID
from tblProjects WITH (NOLOCK)
    inner join #projects on #projects.projectid = tblprojects.projectid
     Inner join tblMilestones WITH (NOLOCK) ON tblProjects.ProjectID = tblMilestones.ProjectID
          and tblProjects.projectID in (
               select projectid
               from tblMilestones
               where (parent = 683691 AND PrimaryDate between '4/15/2006' and '4/22/2006' )
                    and enabled = 1 )
------------
This is dynamic SQL generated by the application when a user requests a report with variable parameters. It works fine in SQL Server 2000. It outputs 47 records which is correct.

In SQL Server 2005, for some reason, the DISTINCT keyword is behaving as a TOP operator and outputs just 1 record. (Results of Showplan Text at the end of this post).

If I modify the query even the slightest bit by:
1) Changing "where (parent = 683691 AND PrimaryDate between '4/15/2006' and '4/22/2006' )
    and enabled = 1 )"
To " where (parent = 683691 AND PrimaryDate between '4/15/2006' and '4/22/2006' ) )
    and enabled = 1 "

2) Changing " Select distinct tblProjects.ProjectID"
To   " Select distinct tblProjects.ProjectID+''"

3) Removing the Distinct keyword, storing into a Temp table, then performing a distinct on the temp table

4) Adding: OPTION (FORCE ORDER)

5) OR completely fixing the query (remove redundant loops, etc)

...it works fine (outputs 47 records). It also works if I created new tables (eg. tMilestones instead of tblMilestones) and inserted about 10 records into each and ran the query referencing these new tables.

I reindexed the tables, updated stats, updated usage, ran DBCC FREEPROCCACHE, changed MaxDOP settings...nothing makes the query behave the way it does in SQL Server 2000 without modifying the query/adding the query hint.

Have you come across this? Any ideas on what might be causing the "TOP" operation. (Somewhat resembles the bug mentioned in this article: http://www.kbalertz.com/Feedback_910392.aspx - but this was apparently fixed POST-SQL Server 2000 SP4 - so has it not made it into SQL Server 2005 yet?).

I will appreciate any new insights you might have on this issue.
Thanks much,
Smitha

P.S. Results of Showplan Text:

StmtText
------------------------------
SET STATISTICS PROFILE ON

(1 row(s) affected)

StmtText
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Select distinct tblProjects.ProjectID from tblProjects WITH (NOLOCK)
inner join #projects on #projects.projectid = tblprojects.projectid
Inner join tblMilestones WITH (NOLOCK) ON tblProjects.ProjectID = tblMilestones.ProjectID
and tblProjects.projectID in (
select tblMilestones.projectid from tblMilestones
where (parent = 683691 AND tblMilestones.PrimaryDate between '4/15/2006' and '4/22/2006' )
and tblMilestones.enabled = 1 )

(1 row(s) affected)

StmtText
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Stream Aggregate(DEFINE:([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectID]=ANY([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectID])))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]))
            |--Nested Loops(Inner Join, OUTER REFERENCES:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]))
            |    |--Filter(WHERE:(CONVERT_IMPLICIT(tinyint,[ExpesiteProductionCopy].[dbo].[tblMilestones].[Enabled],0)=(1)))
            |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1014], [ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]) OPTIMIZED)
            |    |         |--Merge Join(Inner Join, MERGE:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID], [Uniq1014])=([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID], [Uniq1014]), RESIDUAL:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] = [ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] AND [Uniq1014] = [Uniq1014]))
            |    |         |    |--Sort(ORDER BY:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] ASC, [Uniq1014] ASC))
            |    |         |    |    |--Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[byPrimaryDate]), SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[PrimaryDate] >= '2006-04-15 00:00:00.000' AND [ExpesiteProductionCopy].[dbo].[tblMilestones].[PrimaryDate] <= '2006-04-22 00:00:00.000') ORDERED FORWARD)
            |    |         |    |--Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[byParentID]), SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[Parent]=(683691)) ORDERED FORWARD)
            |    |         |--Clustered Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[projectid]), SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] AND [Uniq1014]=[Uniq1014]) LOOKUP ORDERED FORWARD)
            |    |--Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblProjects].[PK_tblProjects_1]), SEEK:([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectID]=[ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]) ORDERED FORWARD)
            |--Top(TOP EXPRESSION:((1)))
                 |--Nested Loops(Inner Join)
                      |--Table Scan(OBJECT:([tempdb].[dbo].[#projects]), WHERE:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[tempdb].[dbo].[#projects].[projectid]))
                      |--Clustered Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[projectid]), SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]) ORDERED FORWARD)

(15 row(s) affected)

StmtText
-----------------------------
SET STATISTICS PROFILE OFF

(1 row(s) affected)

View 6 Replies View Related

SUM Returns Incorrect Value In SQL Server Mobile Query ... Bug??

Feb 6, 2007

Hi all,

I am a newcomer to Microsoft Database Technology and have appeared to come across an issue with the SUM function in SQL Server Mobile Edition.

I am running Visual Studio 2005 and have created 2 tables:

Orders and OrderLines which are set up in master detail fashion.

The SQL Statement I create in the Query Builder is as follows:

SELECT     Orders.OrderNo, Orders.OrderDate, Orders.Priority, Orders.Address, SUM(OrderLines.Quantity * OrderLines.QualityReference) AS Total
FROM         Orders, OrderLines
WHERE     Orders.OrderNo = OrderLines.OrderNo
GROUP BY Orders.OrderNo, Orders.OrderDate, Orders.Priority, Orders.Address

Now, the SUM returns a total for all records in the OrderLines table, and not just the records whose OrderNo is the same as Orders.OrderNo

Can someone out there please clarify whether its an issue with my code or a bug with SQL Server Mobile???

Here are a couple of screenshots which will demonstrate what I mean.

Here is the contents of the Orders and OrderLines tables. Each order has only 1 line item in it.

http://public.fotki.com/AussieNoobie/sqlerrors/vs2005sqlceordersdata.html

When performing the summation over the OrderLines table, it produces the SUM of the all records in OrderLines and not according to the GROUP BY clause. See the following screen shot.

http://public.fotki.com/AussieNoobie/sqlerrors/vs2005sqlceerror1.html

I hope this explains it better.

Anyone have any ideas???

Thanks in advance

View 13 Replies View Related

Need To Go Down Path To Find If Everything Is Settled (recursive Possibly)

May 1, 2007

I have the following table.GO/****** Object: Table [dbo].[itTransactionProcess] Script Date:05/01/2007 10:42:31 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[itTransactionProcess]([TransactionID] [int] IDENTITY(1,1) NOT NULL,[LotNumber] [int] NOT NULL,[CurrentProcessStepID] [int] NOT NULL,[NextProcessStepID] [int] NULL,[CategoryID] [int] NULL,[ProductID] [int] NULL,[ProductVariantID] [int] NULL,[ParentTransactionID] [int] NULL,[TransactionDateEntered] [datetime] NULL,[TransactionDateExit] [datetime] NULL,[Settlement] [money] NULL,[Completed] [int] NULL,CONSTRAINT [PK_itTransactionProcess] PRIMARY KEY CLUSTERED([TransactionID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]Sample data is as followsBasically what I need to do is return the lotid where all path have asettlement date.this is my current procedure/****** Object: StoredProcedure [dbo].[getPendingSettlementDetails] Script Date: 05/01/2007 10:47:47******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[getPendingSettlementDetails]ASdeclare @LotNumbersTable table(LotNumber int)insert into @LotNumbersTable EXEC GetPendingSettlementsLotNumbersDeclare @ResultsTable table(LotNumber int, Company varchar(150),Contact varchar(150), DateReceived datetime, DateComplete datetime,SettlementLength int)Declare @LotNumber intDeclare @DateRecieved datetime, @DateComplete datetimeDeclare @NumberOfDaysForSettlement intDeclare @Company varchar(150)Declare @Contact varchar(150)select @LotNumber = min(LotNumber) from @LotNumbersTablewhile @LotNumber is not null beginSelect @DateRecieved = min(TransactionDateEntered) fromitTransactionProcess where LotNumber = @LotNumberSelect @DateComplete = max(TransactionDateExit) fromitTransactionProcess where LotNumber = @LotNumber and Settlement isnot nullSET @NumberOfDaysForSettlement = DATEDIFF(DAY, @DateRecieved,@DateComplete)Select @Company = Company from SP_Active_Lot_Deliveries where LotID =@LotNumberSelect @Contact = ContactName from SP_Active_Lot_Deliveries whereLotID = @LotNumberINSERT INTO @ResultsTable (LotNumber, DateReceived, DateComplete,SettlementLength, Company, Contact) Values(@LotNumber, @DateRecieved, @DateComplete,@NumberOfDaysForSettlement, @company, @contact)select @LotNumber = min(LotNumber) from @LotNumbersTable whereLotNumber @LotNumberendSelect * From @ResultsTable where SettlementLength is not nullhere is sample data"TransactionID","LotNumber","CurrentProcessStepID","NextProcessStepID","CategoryID","ProductID","ProductVariantID","ParentTransactionID","TransactionDateEntered","TransactionDateExit","Settlement","Completed""628","47","1","2","5","","","","","2007-05-0110:23:15.747000000","","""629","47","1","3","17","","","","","2007-05-0110:23:15.747000000","0.25","""630","47","1","4","34","","","","","2007-05-0110:23:15.747000000","-0.15","""631","47","1","3","38","","","","","2007-05-0110:23:15.747000000","-0.15","""632","47","1","4","33","","","","","2007-05-0110:23:15.747000000","-0.35","""633","47","1","3","15","","","","","2007-05-0110:23:15.747000000","10","""634","47","2","3","86","","","628","2007-05-0110:23:15.747000000","2007-05-01 10:32:41.320000000","-0.35","""635","47","3","","17","","","629","2007-05-0110:23:15.747000000","","","""636","47","4","","34","","","630","2007-05-0110:23:15.747000000","","","""637","47","3","","38","","","631","2007-05-0110:23:15.747000000","","","""638","47","4","","33","","","632","2007-05-0110:23:15.747000000","","","""639","47","3","","15","","","633","2007-05-0110:23:15.747000000","","","""640","47","2","3","85","","","628","2007-05-0110:24:47.983000000","2007-05-01 10:32:41.320000000","0.05","""641","47","2","4","88","","","628","2007-05-0110:24:56.343000000","2007-05-01 10:32:41.333000000","0.8","""642","47","2","4","9","","","628","2007-05-0110:25:07.517000000","2007-05-01 10:32:41.333000000","-0.15","""643","47","2","4","100","","","628","2007-05-0110:25:22.470000000","2007-05-01 10:32:41.333000000","-0.35","""644","47","2","4","90","","","628","2007-05-0110:25:44.297000000","2007-05-01 10:32:41.333000000","-0.35","""645","47","2","4","12","","","628","2007-05-0110:25:59.347000000","2007-05-01 10:32:41.333000000","-0.15","""646","47","2","4","26","","","628","2007-05-0110:26:12.610000000","2007-05-01 10:32:41.333000000","-0.35","""647","47","2","3","94","","","628","2007-05-0110:26:29.523000000","2007-05-01 10:32:41.333000000","-3","""648","47","2","3","95","","","628","2007-05-0110:26:47.323000000","2007-05-01 10:32:41.333000000","-0.35","""649","47","2","3","38","","","628","2007-05-0110:27:01.450000000","2007-05-01 10:32:41.333000000","-0.15","""650","47","2","4","33","","","628","2007-05-0110:27:15.533000000","2007-05-01 10:32:41.333000000","-0.35","""651","47","2","4","34","","","628","2007-05-0110:27:33.767000000","2007-05-01 10:32:41.333000000","-0.15","""652","47","2","3","96","","","628","2007-05-0110:27:46.850000000","2007-05-01 10:32:41.350000000","-0.35","""653","47","2","3","97","","","628","2007-05-0110:28:00.917000000","2007-05-01 10:32:41.350000000","0.05","""654","47","2","4","36","","","628","2007-05-0110:28:10.813000000","2007-05-01 10:32:41.350000000","-15","""655","47","2","4","37","","","628","2007-05-0110:28:25.347000000","2007-05-01 10:32:41.350000000","0.35","""656","47","2","3","98","","","628","2007-05-0110:28:36.917000000","2007-05-01 10:32:41.350000000","-0.35","""694","47","2","10","26","","","628","2007-05-0110:32:17.170000000","2007-05-01 10:32:41.350000000","","""695","47","2","10","35","","","628","2007-05-0110:32:27.883000000","2007-05-01 10:32:41.350000000","45","""696","47","3","","86","","","634","2007-05-0110:32:41.320000000","","","""697","47","3","","85","","","640","2007-05-0110:32:41.333000000","","","""698","47","4","","88","","","641","2007-05-0110:32:41.333000000","","","""699","47","4","","9","","","642","2007-05-0110:32:41.333000000","","","""700","47","4","","100","","","643","2007-05-0110:32:41.333000000","","","""701","47","4","","90","","","644","2007-05-0110:32:41.333000000","","","""702","47","4","","12","","","645","2007-05-0110:32:41.333000000","","","""703","47","4","","26","","","646","2007-05-0110:32:41.333000000","","","""704","47","3","","94","","","647","2007-05-0110:32:41.333000000","","","""705","47","3","","95","","","648","2007-05-0110:32:41.333000000","","","""706","47","3","","38","","","649","2007-05-0110:32:41.333000000","","","""707","47","4","","33","","","650","2007-05-0110:32:41.333000000","","","""708","47","4","","34","","","651","2007-05-0110:32:41.333000000","","","""709","47","3","","96","","","652","2007-05-0110:32:41.350000000","","","""710","47","3","","97","","","653","2007-05-0110:32:41.350000000","","","""711","47","4","","36","","","654","2007-05-0110:32:41.350000000","","","""712","47","4","","37","","","655","2007-05-0110:32:41.350000000","","","""713","47","3","","98","","","656","2007-05-0110:32:41.350000000","","","""714","47","10","","26","","","694","2007-05-0110:32:41.350000000","","","""715","47","10","","35","","","695","2007-05-0110:32:41.350000000","","",""If you follow transaction id 714 up through the parent transaction idsit doesn't not have a settlement cost yet lot 47 shows up as settled.Thanks for you help.

View 6 Replies View Related

Unknown Backup Running (possibly Unauthorized)

May 1, 2008

Hello everyone.

First off, I know little to nothing about SQL. I run the IIS web server and the programmers run the SQL server. Recently the programmers found a backup running everynight at midnight and its messing up the Differential backups. By that I mean, since the full backup runs, the differential isnt backing anything up since it is set to only backup from the last full backup.

The backup seems to be backing up to a "Virtual Device" and I have no idea what that is. I am unable to see any user associated with the backup and I'm at a total loss as to where the data is going. Can someone please help me resolve this issue. I searched but was unable to find anything as I have no idea what to search for to find where this backup is coming from.

Here is a log from said backup...

Database backed up. Database: dbname, creation date(time): 2008/03/10(03:40:58), pages dumped: 1, first LSN: 288:523:56, last LSN: 288:547:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{B9A0EFAB-8894-4BBC-BC77-82DA1B58E2D4}28'}). This is an informational message only. No user action is required.

Lastly... The programmers do have backups running.. full once a week and differentials inbetween. the problem is, those logs look like this...

Log was backed up. Database: dbname, creation date(time): 2008/04/09(03:32:53), first LSN: 135:305:1, last LSN: 135:305:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'D:ackup imeanddate.BAK'}). This is an informational message only. No user action is required.

Thank you in advance!

View 10 Replies View Related

Is It Possibly To Rollback A Whole Package If One Dataflow Fails?

Apr 22, 2006

I have 4 data flows in a for loop, is it possibly to roll the whole for loop back if any of the transactions fail. Also is it possibly to roll back a dataflow if any of the components inside the dataflow fail?

Cheers

View 1 Replies View Related

Problem With Backups Possibly Caused By Microsoft SQL

Feb 1, 2008

Hello

Am having a problem which started after I installed a databse with sqlexpress. I get this error when backing up "MSDEWriter" has reported an error 0x800423f4

I am running Windows 2003 SBS SP2.

I've checked to make sure that all of my databases are using simple in recovery mode.

Please help

Thanks

View 1 Replies View Related

Same Query Gives Result With Different Column Sequence When Used In Query Analyzer

Feb 25, 2012

When I run query in excel it gives result with different column sequence. The same query gives result with different column sequence when used in query analyzer or VBA Macro. E.g., Select * from ABC.

result in Excel 2003 SQL OLE DB query

col-A col-B col-C
values...

Result with Query Analyzer and VBA Macro

col-c col-B col-A
values...

View 3 Replies View Related

Re-display Result Set Without Re-running Query In Query Analyzer?

Apr 9, 2006

I hope I am not asking about something that has been done before, but Ihave searched and cannot find an answer. What I am trying to do is torun a query, and then perform some logic on the rowcount and thenpossibly display the result of the query. I know it can be done withADO, but I need to do it in Query Analyzer. The query looks like this:select Varfrom DBwhere SomeCriteriaif @@Rowcount = 0select 'n/a'else if @@Rowcount = 1select -- this is the part where I need to redisplay the resultfrom the above queryelse if @@Rowcount > 1-- do something elseThe reason that I want to do it without re-running the query is that Iwant to minimize impact on the DB, and the reason that I can't useanother program is that I do not have a develpment environment where Ineed to run the queries. I would select the data into a temp table, butagain, I am concerned about impacting the DB. Any suggestions would begreatly appreciated. I am really hoping there is something as simple as@@resultset, or something to that effect.

View 6 Replies View Related

Transact SQL :: SELECT On Column Name From Query Result Set In Same Query?

May 9, 2015

I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).

To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:

COLUMN_NAME Value
----------- -----
colB 123
colA XYZ

I've tried dynamic SQL to no success, probably not executing the concept correctly...

Below is what I have:

CREATE TABLE myTable (colA VARCHAR(3), colB VARCHAR(3), colC VARCHAR(3))
INSERT INTO myTable (colA, colB, colC) VALUES ('ABC', '123', '0X')
INSERT INTO myTable (colA, colB, colC) VALUES ('XYZ', '789', 'X0')
;WITH cte AS
(
SELECT CAST(PATINDEX('%[^0]%', colC) AS SMALLINT) pos, STUFF(colC, 1, PATINDEX('%[^0]%', colC), '') colC

[Code] ....

View 4 Replies View Related

Incorrect Query Plan With Partitioned View On SQL 2000

Jun 19, 2001

I have a partitioned view containing 4 tables (example follows at end)

The query plan generated on a select correctly accesses just one of the tables

The query plan generated on an update always accesses all four of the tables. I thought that it should only access the partition required to satisfy the update. Can anyone please advise whether:
a) Is this is expected behaviour?
b) Is the partitioned view incorrectly configured in some way?
c) Is there is a known bug in this area

Note that the behaviour is the same with SP1 on SQL2000

I would be very grateful for any advice

Thanks

Stefan Bennett

Example follows

--Create the tables and insert the values
CREATE TABLE Sales_West (
Ordernum INT,
total money,
region char(5) check (region = 'West'),
primary key (Ordernum, region)
)
CREATE TABLE Sales_North (
Ordernum INT,
total money,
region char(5) check (region = 'North'),
primary key (Ordernum,region)
)
CREATE TABLE Sales_East (
Ordernum INT,
total money,
region char(5) check (region = 'East'),
primary key (Ordernum,region)
)
CREATE TABLE Sales_South (
Ordernum INT,
total money,
region char(5) check (region = 'South'),
primary key (Ordernum,region)
)
GO

INSERT Sales_West VALUES (16544, 2465, 'West')
INSERT Sales_West VALUES (32123, 4309, 'West')
INSERT Sales_North VALUES (16544, 3229, 'North')
INSERT Sales_North VALUES (26544, 4000, 'North')
INSERT Sales_East VALUES ( 22222, 43332, 'East')
INSERT Sales_East VALUES ( 77777, 10301, 'East')
INSERT Sales_South VALUES (23456, 4320, 'South')
INSERT Sales_South VALUES (16544, 9999, 'South')
GO

--create the view that combines all sales tables
CREATE VIEW Sales_National
AS
SELECT *
FROM Sales_West
UNION ALL
SELECT *
FROM Sales_North
UNION ALL
SELECT *
FROM Sales_East
UNION ALL
SELECT *
FROM Sales_South
GO

--Look at execution plan for this query
-- This correctly only accesses the South partition
SELECT *
FROM sales_national
WHERE region = 'south'

-- Look at execution plan for update
-- This accesses all partitions - Why?
update sales_national
set total = 100
where ordernum = 23456;

View 1 Replies View Related

MS HotFixes/patches Possibly Stunting DB Activity For Certain Amounts Of Data?

Jun 14, 2004

I’m wondering if anyone can shed light on a problem I’ve noticed that's really made for a major thorn in my side. I recently had a Microsoft patch installed on my server, and now for some reason, trying to run INSERT or UPDATE queries against the SQL 2000 database are severely limited. I constantly get the error:

“Error: A severe error occurred on the current command. The results, if any, should be discarded.�?

My Event Logs also return the following:

"Invalid buffer received from client."

I think I’ve isolated the problem to be that I can’t add new or modify existing records that try using a field which is of type TEXT, but now can’t be longer than 4,000 characters, else the error fires. This is really weird, as I’ve used the same ASP.NET script to call a stored procedure to INSERT/UPDATE records thousands of times before with 100% success.

I have a feeling this might have something to do with the patch, but has anyone come across this problem specifically, or know for sure which patch(es) cause it? Why all of a sudden would a TEXT field be so limited in capacity?

View 2 Replies View Related