Detecting Of Record Does Not Work In If Query

Nov 23, 2007

Edit: Newer mind. I tested this query more after writing this, and now it seems to work!
I hope it continues to work. 
In following query, else is never executed.


CREATE PROCEDURE Put_into_basket
(    @Product_code varchar(20))
AS
BEGIN
    SET NOCOUNT ON;
IF NOT EXISTS(SELECT * FROM dbo.t_shopping_basket WHERE Product_code=@Product_code)
    BEGIN
        INSERT dbo.t_shopping_basket (Product_code, Name,Price)
        SELECT Product_code, Name,Price
        FROM dbo.t_product
        WHERE Product_code= @Product_code
    END
ELSE    --this part is never executed
    BEGIN   
        UPDATE dbo.t_shopping_basket
        SET Quantity=Quantity+1
        WHERE Product_code=@Product_code
    END
END
GO

 The query should test if there is a record or row with Product_code=@Product_code.

If there is not, that is the first part, one such row is inserted. Quantity has a default value of 1. Insertion works, one row is inserted. At least sort of.

If there is already record, That's later part, Quantity is increased by 1. That too works, if ran separately.
But when I test query, it never runs the quantity+1 part. 

View 3 Replies


ADVERTISEMENT

Ways To Make This Work: Several Selectable Related Record For One Main Record.

Apr 6, 2007

Hey all!



Sorry for the less then descriptive post title but I didn't find a better way to describe it. I'm developing an app in the express editions of VB and SQLserver. The application is a task/resource scheduler. The main form will have a datepicker or weekly overview and show all tasks scheduled per day. The problem is, I've got one or more people assigned to tasks and I wonder what's the best way to design this. Personally, I'd go for one Task table, a People table and a table that provides a link between them (several record per task, one for each person assigned linking TaskID and PplID). However, I don't see a nice way of showing this data to the end user, allowing him to edit/add etc on ONE screen.

To fix that the only way I see is just add columns to the Task table for every person with select boxes. This way everything can be done on one simple screen. This obviously does present some future issues.

On top of this, which people are available on a day varies and there should be an option to allow a user to set who is available on a specific day. Which would lead me to my first idea and add another table that would provide this. but then I'm having design issues again for the form.



I'm kinda stuck atm, can anyone shed some light on this. I'm sure there is an elegant way of doing this but I'm failing at finding it.



Thanks in advance,

Johan

View 5 Replies View Related

Query Timeouts When Updating A Record Retrieved Through A Websphere JDBC Datasource - Possible Record Locking Problem

Apr 7, 2008

Hi,

We're running a Sage CRM install with a SQL Server 2000 database at the back end. We're using the Sage web services API for updating data and a JDBC connection to retrieve data as it's so much quicker.

If I retrieve a record using the JDBC connection and then try and update the same record through the web services, the query times out as if the record is locked for updates. Has anyone experienced anything similar or know what I'm doing wrong? If I just use DriverManager.getConnection() to establish the connection instead of the datasource, and then continue with the same code I don't get these record locking problems. Please find more details below.

Thanks,
Sarah

The JDBC provider for the datasource is a WebSphere embedded ConnectJDBC for SQL Server DataSource, using an implementation type of 'connection pool datasource'. We are using a container managed J2C authentication alias for logging on.

This is running on a Websphere Application Server v6.1.

Code snippet - getting the record thru JDBC:


DataSource wsDataSource = serviceLocator.getDataSource("jdbc/dsSQLServer");
Connection wsCon = wsDataSource.getConnection();


// wsCon.setAutoCommit(false); //have tried with and without this flag - same results

Statements stmt = wsCon.createStatement();


String sql = "SELECT * FROM Person where personID = 12345";
ResultSet rs = stmt.executeQuery(sql);


if(rs.next()){
System.out.println(rs.getString("lastName"));
}

if (rs != null){
rs.close();
}
if (stmt != null) {

stmt.close();
}
if (wsCon != null) {

wsCon.close();
}

View 1 Replies View Related

Why The Query Works In Query Analyser And Doesn't Work In Reporting Service ?

Apr 30, 2007



Hello everybody,



I'm developing a report using the following structure :



declare @sql as nvarchar(4000)

declare @where as nvarchar(2000)



set @sql = 'select ....'



If <conditional1>

begin

set @where = 'some where'

end



If <conditional2>

begin

set @where = 'some where'

end



set @sql = @sql + @where



exec(@sql)



I run it in query analyser and works fine, but when I try to run in Reporting Services, Visual studio stops responding and the cpu reaches 100 %.



I realize that when I cut off the if clauses, then it works at Reporting services.

Does anybody know what is happening?

Why the query works in query analyser and doesn't work in Reporting Service ?



Thanks,



Maurício

View 2 Replies View Related

Detecting IIS

Nov 17, 2005

Good Morning,

View 3 Replies View Related

Detecting Commits

Aug 10, 1998

Is there a TSQL function or value that is assigned to some sort of internal variable when part of a distributed transcation commits? If so, how do I access it?
Marc

View 1 Replies View Related

Detecting Database Changes

Jan 10, 2005

Is there a way in detecting changes in the database?

thanks..

View 14 Replies View Related

Detecting LOB Datatypes.

Oct 15, 2007



Does anyone have a good way to detect LOB datatypes for a table or index? I am doing index rebuilds with the ONLINE option and need to work around indexes with LOB datatypes in them...


Any suggestion?

View 3 Replies View Related

Detecting Service Pack 2

Sep 27, 2007

Hello all,

Is there a preferred method for detecting SQL Server 2005 SP2? I do installation/deployment and during my install script I would prefer to skip the SP2 install if the instance is already upgraded.

I had tried to check HKLMSoftwareMicrosoftMicrosoft SQL ServerInstance NamesSQL to first find out what MSSQL.[n] our instance took.

After that I was attempting to check HKLMSoftwareMicrosoftMicrosoft SQL ServerMSSQL.[n]SetupSP but there have been cases where the key SP did not exist.

I decided I could just install it under the condition that no key existed at all, assuming since the 'SP' key did not exist, the instance was not upgraded. In tests however, after a fresh install of SP2, the key was not there.

I guess the question is further defined as: does installing SP2 place a flag that it exists anywhere else? Is it supposed to write to HKLMSoftwareMicrosoftMicrosoft SQL ServerMSSQL.[n]SetupSP?

Thanks,

-Mike

View 4 Replies View Related

Detecting (local) Sql-server

Jul 23, 2005

Can someone please point me to some code (preferably C#, but C++ or C or VBwill work) that will detect if the (local) instance of SQL Server isrunning on a machine or not? Many thanks!-- Rob

View 2 Replies View Related

DDL Triggers And Detecting Sp_rename

Jan 2, 2008

I am trying to use DDL triggers to detect all changes to a table.

Sometimes a user directly or (more likely) through the SQL Server Management Studio GUI renames a column via sp_rename.

I can't seem to figure out how to detect such renames using a DDL trigger. Is there any way or is this a limitation of the DDL trigger system?

View 9 Replies View Related

Detecting A Change In A Particular Field In A Database

Nov 16, 2005

Is it possible, using VB code, to retain a user on a particular page
until the status of a certain field in a database is changed?
The scenario I am working on is that a person makes a reservation and
has to wait until that reservation has been accepted or rejected by the
administration.

Dim dsResv As DataSet
        dsResv = objResv.DALgetResvStatus(resvId)

        While (dsResv.Tables(0).Rows(0).Item("resvStatus") = "pending")

            dsResv = objResv.DALgetResvStatus(resvId)

            If
dsResv.Tables(0).Rows(0).Item("resvStatus") = "denied" Then
                failure.Visible = True
                LinkButton1.Visible = True
           
ElseIf dsResv.Tables(0).Rows(0).Item("resvStatus") = "reserved" Then
                success.Visible = True
                LinkButton1.Visible = True
            End If

        End While

This is how I tried to do it, but it doesnt seem to work.
Any suggestions?

View 2 Replies View Related

Detecting Carriage Returns In A Column

Jun 12, 2006

Can anyone provide me with some SQL that will identify rows from a table where a varchar column named "Notes" contain Carriage Returns?

I know that with report writer SQR I can translate CR's to white space but I do not know of any Sybase function that will allow me to do the same, any ideas on this well would be appreciated.

View 3 Replies View Related

Detecting Joined Records That Number Only 1

Oct 1, 2013

I have 2 tables:

Table 1 Customers with column CustomerId
Table 2 Purchases with columns PurchaseId and CustomerId

I want to select all customers who have made just 1 purchase, what would be the sql for that? I know I can join the tables to get the customers with purchases, but I don't know how to limit it to those with just 1 purchase (and exclude all the other customers).

View 9 Replies View Related

Ssis - Detecting Hung Packages

Apr 17, 2008

Folks,
I have a SSIS package that works fine 90 % of the time. Every once in a while it gets hung and when I notice that the package has been running for several hours I check the job activity monitor and notice that the package is in executing state for several hours..my question is ...Is there a way I can check for this incidence through a c# app ? Does the package name show up in the process task if so I can poll to see if the process corresponding to the package has been executing for > 30 mins then I can kill it.


Any suggestions would be great.

Thanks

View 6 Replies View Related

Lookup Not Detecting New Column On Table

Aug 13, 2007

I have added a new column to a table, but it is not showing up in the column mapping tab of the lookup editor.

It does show up in the preview.

Without deleting and recreating the component (with all the ensuing broken metadata fixes 'downstream' that this always entails), how can I get it to recognise the change?

In future I will do all LUs as a sql statement so at least I can control columns. This again defeats the purpose of the drag and drop environment..... If it were a harry potter character, it would be a dementor

View 5 Replies View Related

Checksum Transformation Is Not Detecting My Change

Jul 20, 2007

I am using the Konesans Checksum transformation ( http://www.sqlis.com/21.aspx ) to detect changes in my big (many columns, type 2 SCD) dimensional table.

But I am running into collossions

The checksum transformation, sometimes misses a small change in the record, for instance when a certain flag is set or unset. Is there a more robust checksum generator? Of any other suggestions on to solve this?

thx

View 12 Replies View Related

Is There A Way To Get This Query To Work In SQL

Aug 3, 2004

What I want to do is update 1 table based on data in another. This query works great in Access XP, but I cannot make it work in SQL 2000 sp3. Is there a different way to populate table A with direct data from table B in an update?

Any help is appreciated

UPDATE [Clients - Complete], AssociateDirectory
SET [Clients - Complete].Phone = AssociateDirectory.WorkPhoneNumber
WHERE [Clients - Complete].Name=AssociateDirectory.LastName And [Clients - Complete].FNAME=AssociateDirectory.FirstName;

View 2 Replies View Related

Why Don't This Little Query Work For Me

Jan 28, 2008

hello everybody!
I'm using ssce.
I'm trying to add the followed query in my table adapter but in the query builder (VS), when I choose execute query, I get a couple of textboxes but whatever I type in, I get an error message "The parameter is wrong"

here is the query:
SELECT EntryID, UserInfo, Kind, ActionDate, Span, SalaryAtAction
FROM Logbook
WHERE
((CASE
WHEN @Enter = 0 AND @Exit = 0 THEN 0
WHEN @Enter = 1 AND @Exit = 0 AND kind = '?????' THEN 1
WHEN @Enter = 0 AND @Exit = 1 AND kind = '?????' THEN 1
WHEN @Enter = 1 AND @exit = 1 AND kind IN ('?????', '?????') THEN 1 ELSE 0 END) = 1)

what I try to implement is, a table that one of it's columns is bit datatype. the end-user can filter out the table by these parameters whether to show only the true rows or only the false rows or both.

would you suggest to use a different various of the query?

(a help button is provided on the error message, it refers to ]ms-help://MS.VSCC.v90/MS.MSDNQTR.v90.en/dv_vdt03/html/f0bf6b59-c245-4340-a869-5d7278fe0ae5.htm in case it helps anybody).

Shimi

View 1 Replies View Related

Request For Info - Detecting DB Changes And Batching For E-mail

Feb 7, 2006

I have a .NET app recently ported from 1.1 to 2.0 with a pending feature request.  I'd first like to state that I'm not asking anyone for the programmatic answer, unless of course, you'd like to provide it.  I'm simply asking the best way to accomplish this task with the tools I have available to me (VS 2005, SQL 2000 and 2005).  I do not consider myself a professional .NET or T-SQL programmer but I do have a good understanding of the technologies so that I can find what I need to know once I know I'm going about something the right way.
The application I wrote, from scratch, manages News, Featured Connections, and FAQs for a web site.  It was designed in a multi-layer approach where there are:
items  -> assigned to categories -> assigned to users
So if user Fred is granted rights to category A, when he authenticates to the app he can Add/Change/Delete any item in category A.  Simple enough.  But also what Fred can do is see all the other items other users have created and *schedule* any relevant item related to his web page to appear within his own items even tho he has no rights to change that item in the admin interface.
The feature request is a notification service, so that Fred gets an e-mail when someone has added a new item or edited an existing one.  I don't want Fred to get an e-mail *every* time an item is created or edited, that would be big bother and would generate lots of e-mails.  What  I'd like to do is send Fred an e-mail sometime in the middle of the night that informs him of the items added or changed since his last login, so that at his choosing he can go into the admin interface and schedule one or more of those items for his page.
Should I just write a new console app that does this, compile it and schedule the .EXE to run at midnight? Or is there a better way to leverage the new features in SQL 2005 (extended strored procs, triggers) and .NET to do this a better way?  Currently, the DB is in SQL 2000 but I have no problems moving it to SQL 2005 if that helps me in some way. Thanks in advance for your comments.
...Bill
 

View 1 Replies View Related

Detecting SQL Server Service Pack Version?

Aug 5, 1999

How do you find out which service pack is installed on SQL Server?
We are installing sp5a but we get an error saying 'cfgchar.exe could not execute' and at the end it says ' can not install successfully'.
How can I understand If I really installed sp ?

View 2 Replies View Related

Detecting Or Monitoring For Long-running Queries.

Jul 20, 2005

I can't seem to find a step-by-step guide to how to detect orcontinuously monitor for long-running queries that is suitablefor a comparative SQL Server novice. I know that it is possibleto monitor for such with other database products - can anyoneenlighten me as to how this is done with SQL Server? Ideally,I'd like to snapshot running queries (preferably with querytext) at a particular instant via a script, although any helpto show what queries are running at an instant will be muchappreciated. Any ideas?__________________________________________________ ___________Are you Catholic ?http://www.CatholicEmail.com100s of FREE email addresses --->http://www.UltimateEmail.comSend an Online Greeting Card http://www.UltimateEcards.com

View 1 Replies View Related

Detecting Installed IIS - Installing SQL 2005 Standard

Dec 5, 2005

I've seen lots of posts but few if any solutions to this apparently common problem.

View 4 Replies View Related

Installing SQL Server 2005, Detecting Installed IIS

Jun 24, 2006

When trying to install SQL 2005, the installation halts at "Detecting Installed IIS". From a previous forum discussion I tried disabling Norton Anti-Virus and Firewall, but the install still does not get past this point.

Please help.

View 9 Replies View Related

Detecting &&amp; Deleting Duplicates In Batch Vs Proc

Aug 22, 2006

I know how to detect & delete dups/or >dups in test with a select clause, this works fine in a small table, but if the table has a million rows say, it sounds like a proc would be faster:  my question is:  How do I display those rows in a proc for detecting what the problem is.  The print stmt. doesn't seem to work and I wondered if I had to go through the process of building an output stream.  The proc creates okay but I'm stuck after that part.

thx

Kat  -- very rough code below

 

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc dupcount
@count int
as
set nocount on
select categoryID,
 CategoryName,
 Count(*) As Dups
 from Categories
group by Categoryid, CategoryName
having count(*) >1

set @count = @@Rowcount
print convert(varchar(30),CategoryID) + ' ' + convert(varchar(30),@count) + CategoryName

/*set @count = @@RowCount
IF @count > 1
print convert CategoryID, CategoryName, convert(varchar(30),@count)*/

 

View 18 Replies View Related

Can't Seem To Make This Query Work

Jul 24, 2006

Hi,
I have a query thatI need to make into one query for the sake of an application reading only one cursor.
 
Here's the syntax:
select (select distinct(x.amount) from escrow k inner join e120 x on k.escrow = x.escrowinner join a10 g on x.escrow = g.escrow  where k.ftype = 'S' group by x.amount, g.officer) As New,a.officer as Officer, count(distinct(j.fstatus))as Escrow_Type, count(distinct(j.amount))as Amount, count(distinct(d.open_date))as [Open], count(distinct(d.close_date)) as Closed, count(distinct(can_date))as Cancelled
from a10 a inner join escrow d on a.escrow = d.escrowinner join e120 j on j.escrow = d.escrow where j.id_scr = 'e21' and j.fstatus = 'PAID' group by a.officer
 
The error message i'm recieving is the following:
 
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
 
Any ideas? any suggestions would be great.
 
Thanks

View 3 Replies View Related

SQL Query Analyzer Does Not Work

Apr 18, 2004

My SQL Query Analyzer just runs the clock but when the clock stops it fails to open the tool. Both my SQL Server 2000 Developer and Enterprise Edition do this. Can anyone suggest how I can get this working? It worked fine in SQL 7.

View 1 Replies View Related

Why Doesnt My Query Work

Dec 6, 2007

select distinct
a.Patients,
b.Patients,
a.pct,
b.pct

from
(
select count(*) as Patients, [pct of res] as pct
from testing
where [18 week wait] <= 18
group by [pct of res]
) as a right outer join
(select distinct[pct of res] from testing) as c on a.pct=c.[pct of res]and a.pct <> 'null' --is not null

(select count(*) as Patients, [pct of res] as pct
from testing
where [18 week wait] >18
group by [pct of res]
) as a left outer join as b on c.[pct of res]=b.pct

View 6 Replies View Related

Noob: Why Does This Query Work?

Jul 23, 2005

I'm wondering how/why this query works. Trying to get my head wrappedaround SQL. Basically the Query deletes from the Import table allrecords that are already in FooStrings so that when I do an insert fromthe FooStringsImport table into the FooStrings table, then I won't getprimary key violations.DELETE FROM FooStringsImportWHERE EXISTS(SELECT * FROM FooStringsWHERE FooStringsImport.FooKey = FooStrings.FooKey)It seems to work fine, but I'm wondering about how the EXISTS keywordworks.(SELECT * FROM FooStringsWHERE FooStringsImport.FooKey = FooStrings.FooKey)This part is going to return only records from FooStrings correct? Ordoes it do a cartesian product since I've specified more than one tablein the WHERE statement?I wonder if it only returns records in FooStrings, then I don't see howa record from FooStringsImport would "EXISTS" in the records returnedfrom FooStrings.The reason I wondered about the cartesian product is because, if onlyFooStrings is specified in the FROM part of the SELECT statement, thenI was thinking it is only going to return FooString records. Theserecords would then be returned by the select statement to the WHEREEXISTS, which would look for FooStringImport records, but would findnone because the select statement only returned FooString records.I'm guessing maybe because it has to do a cartesian product to evaluatethe WHERE Pkey's equal, then the "SELECT *" just goes ahead and getsALL the fields, and not just those in FooStrings.FooStrings and FooStringsImport are identically structured tables,where the FooKey is set as the primary key in each table:CREATE TABLE [dbo].[FooStrings] ([FooKey] [bigint] NOT NULL ,[Name] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[StartDate] [datetime] NULL ,[EndDate] [datetime] NULL ,[Code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOThanks in advance. I'm so appreciative of the help I've gotten here,as I've been able to write several very useful queries on my own nowafter everyones help and plus lots of reading on my own.

View 4 Replies View Related

UNION Query Won't Work

Jul 20, 2005

I am having alot of trouble with a union query Im trying to create.The bizarre thing is I initially created this query and managed tosave it as a viewSELECT ID, DepartureDate, City, Country, Region,fkDepartureAirport, CONVERT(MONEY, Price) AS Price, '1' AS TypeFROM dbo.vHolidayUNION ALLSELECT ID, ValidTo, DestCity, Country, Region, fkDepartureAirport,Price, '2' AS TypeFROM dbo.vFlightHowever when I tried to update the view to this:SELECT ID, DepartureDate, fkCity, City, fkCountry, Country,Region, fkDepartureAirport, CONVERT(MONEY, Price) AS Price, '1' ASTypeFROM dbo.vHolidayUNION ALLSELECT ID, ValidTo, fkCity, DestCity, fkCountry, Country, Region,fkDepartureAirport, Price, '2'FROM dbo.vFlightit comes up with the error: view definition includes no output columnsor includes no items in the from clause.Any ideas??????All suggestions appreciatedAlly

View 1 Replies View Related

Cannot Work Out A Simple T-sql Query

Apr 2, 2008

Hi all,
I have a table with 2 columns (simplified for this question):
Date and Action

Date is normal datetime, Action is varchar and can be either Sent or Received. The data can look like this:

1. '2008-04-02 15:09:09.847', Sent
2. '2008-04-02 15:09:10.125', Sent
3. '2008-04-02 15:09:11.125', Received
4. '2008-04-02 15:09:12.459', Received
5. '2008-04-02 15:09:15.459', Received
6. '2008-04-02 15:09:24.121', Sent

7. '2008-04-02 15:09:28.127', Received

I want to find a pair of rows Sent-Received with the Max Date difference, where Received follows immediately after Sent.


It means in our example, valid are only lines
2. and 3. or
6. and 7.

In this example, rows 6. and 7. have bigger difference of Dates, so the result of the query should be

6. '2008-04-02 15:09:24.121', Sent, 7. '2008-04-02 15:09:28.127', Received


It is probably easy, I just cannot work it out. Suprisingly finding MIN was quite easy. Thanx for any tips!
Regards,
alvar

View 6 Replies View Related

Transact SQL :: Tables And Database Not Detecting Auto While Coding

Aug 27, 2015

When I type Select statemet like below, Tables names are not populate auto. I need to type full lenght instead of selection in list.

Ex: I have few tables and database in my schema and need to select while coding.

select * from (Table Name) When i type first char in the table space it will not populate list of tables in my sql studio. 

View 12 Replies View Related

Transact SQL :: Detecting Modified Rows Before Writing To A Table?

Apr 17, 2015

I've a table with more columns and 1 identifier. I need to write this table when a modified row is detecting respect to the columns not to the identifier.

So I've created a temporary table to put the potential rows to write on the real table, but I want to detect the modified rows. I've thought to use the checksum function, but I don't know how to use it and if it could be useful in this scenario.

Moreover, in the temporary table I've collected daily the rows to write: the first day a row could have a value respect to his columns, the next day a different value and the next one the same value respect to the first day.

View 26 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved