Sum In Subquery Doesn't Work Well

Jun 8, 2005

This is the autogenerated code from the SelectCommand of my DataAdapter, except the red text. This DataAdapter is used to fill a DataGrid. What I want to do, is to calculate the total memory (4 slots) / PC.This code makes the sum of all memory of all PC's together.I'm not sure if the group by clause is needed here ...Me.OleDbSelectCommand1.CommandText = "SELECT PC.ID, PC.Nummer, PC.Netwerknaam, Case_Type.Type AS Case_Type, Processor_T" & _"ype.Type AS Processor_Type, Processor_Snelheid.Snelheid AS Processor_Snelheid, " & _"(SELECT SUM(Memory) FROM Memory, PC, RAM WHERE RAM.PcID = PC.ID AND RAM.GrootteID = Memory.ID)" & _"AS Memory, OS.Naam AS OS, OS_SP.Nummer AS OS_SP, Gebru" & _"iker.Naam AS Gebruiker_Naam, Status.Status, PC.Tagged FROM (Status RIGHT OUTER J" & _"OIN ((((((((PC LEFT OUTER JOIN (RAM LEFT OUTER JOIN Geheugen ON RAM.GrootteID = " & _"Geheugen.ID) ON PC.ID = RAM.PcID) LEFT OUTER JOIN Case_Type ON PC.Case_TypeID = " & _"Case_Type.ID) LEFT OUTER JOIN OS_SP ON PC.OS_SpID = OS_SP.ID) LEFT OUTER JOIN Ge" & _"bruiker ON PC.GebruikersID = Gebruiker.ID) LEFT OUTER JOIN Processor_Snelheid ON" & _" PC.Processor_SnelheidID = Processor_Snelheid.ID) LEFT OUTER JOIN Processor_Type" & _" ON PC.Processor_TypeID = Processor_Type.ID) LEFT OUTER JOIN OS ON PC.OsID = OS." & _"ID) LEFT OUTER JOIN Switchbox_Details ON PC.ID = Switchbox_Details.PcID) ON Stat" & _"us.ID = PC.StatusID) GROUP BY PC.ID, PC.Nummer, PC.Netwerknaam, Case_Type.Type, " & _"Processor_Type.Type, Processor_Snelheid.Snelheid, OS.Naam, OS_" & _"SP.Nummer, Gebruiker.Naam, Status.Status, PC.Tagged"I would like to know how to calculate the total memory for each separate PC.Hope you can help me.

View 5 Replies


ADVERTISEMENT

Subquery Returned More Than 1 Value But Work FINE (field_xxx=subquery)

Jul 19, 2007

Hi guys,



A have a problem that I need understand.... I have 2 server with the same configuration...



SERVER01:

-----------------------------------------------------

Microsoft SQL Server 2000 - 8.00.2191 (Intel IA-64)

Mar 27 2006 11:51:52

Copyright (c) 1988-2003 Microsoft Corporation

Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)



sp_dboption 'BB_XXXXX'

The following options are set:

-----------------------------------

trunc. log on chkpt.

auto create statistics

auto update statistics

********************************

SERVER02:

-----------------------------------------------------

Microsoft SQL Server 2000 - 8.00.2191 (Intel IA-64)

Mar 27 2006 11:51:52

Copyright (c) 1988-2003 Microsoft Corporation

Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)



sp_dboption 'BB_XXXXX'

The following options are set:

-----------------------------------

trunc. log on chkpt.

auto create statistics

auto update statistics



OK, the problem is that if a run the below query in server01, i get error 512:



Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.



But, if run the same query in the server02, the query work fine -.



I know that I can use IN, EXISTS, TOP, etc ... but I need understand this behavior.



Any idea WHY?



SELECT dbo.opf_saldo_ctb_opc_flx.dt_saldo,

dbo.opf_saldo_ctb_opc_flx.cd_indice_opf,

dbo.opf_saldo_ctb_opc_flx.cd_classificacao,

dbo.opf_movimento_operacao.ds_tipo_transacao ds_tipo_transacao_movimento ,

dbo.opf_header_operacao.ds_tipo_transacao ds_tipo_transacao_header,

'SD' ds_status_operacao,

dbo.opf_header_operacao.ds_tipo_opcao ,

dbo.opf_header_operacao.id_empresa,

dbo.opf_saldo_ctb_opc_flx.ic_empresa_cliente,

0 vl_entrada_compra_ctro ,0 vl_entrada_compra_premio,

0 vl_entrada_venda_ctro , 0 vl_entrada_venda_premio,

0 vl_saida_compra_ctro, 0 vl_saida_compra_premio,

0 vl_saida_venda_ctro, 0 vl_saida_venda_premio,

0 vl_lucro , 0 vl_prejuizo, 0 vl_naoexec_contrato,

0 vl_naoexec_premio,

sum(dbo.opf_saldo_ctb_opc_flx.vl_aprop_ganho) vl_aprop_ganho,

sum(dbo.opf_saldo_ctb_opc_flx.vl_aprop_perda) vl_aprop_perda,

sum(dbo.opf_saldo_ctb_opc_flx.vl_rever_ganho) vl_rever_ganho,

sum(dbo.opf_saldo_ctb_opc_flx.vl_rever_perda) vl_rever_perda,

sum(dbo.opf_saldo_ctb_opc_flx.vl_irrf) vl_irrf

FROM dbo.opf_saldo_ctb_opc_flx,

dbo.opf_header_operacao ,

dbo.opf_movimento_operacao

WHERE dbo.opf_saldo_ctb_opc_flx.dt_saldo = '6-29-2007 0:0:0.000'

and ( dbo.opf_header_operacao.no_contrato = dbo.opf_saldo_ctb_opc_flx.no_contrato )

and ( dbo.opf_header_operacao.no_contrato = dbo.opf_movimento_operacao.no_contrato )

and ( dbo.opf_movimento_operacao.dt_pregao = (select (o.dt_pregao) from dbo.opf_movimento_operacao o

where o.no_contrato = dbo.opf_movimento_operacao.no_contrato and o.dt_pregao <='6-28-2007 0:0:0.000' ) )

and (dbo.opf_saldo_ctb_opc_flx.ic_tipo_saldo = 'S')

group by dbo.opf_saldo_ctb_opc_flx.dt_saldo,

dbo.opf_saldo_ctb_opc_flx.cd_indice_opf,

dbo.opf_saldo_ctb_opc_flx.cd_classificacao,

dbo.opf_movimento_operacao.ds_tipo_transacao,

dbo.opf_header_operacao.ds_tipo_transacao ,

ds_status_operacao,

dbo.opf_header_operacao.ds_tipo_opcao ,

dbo.opf_header_operacao.id_empresa,

dbo.opf_saldo_ctb_opc_flx.ic_empresa_cliente



Thanks

Nilton Pinheiro

View 9 Replies View Related

Subquery Does Not Work

Jan 8, 2007

In the query below the subquery in RED does not work. It displays the following error message:

Server: Msg 512, Level 16, State 1, Line 7
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

If I comment out the subquery, everything works fine.


Code:

Declare @DateFrom Datetime
Declare @DateTo Datetime

Set @DateFrom = '12/1/05'
Set @DateTo = '12/15/06'

SELECT L.LeadId, L.LeadDate, L.BusinessName, U.Firstname + ' ' + U.Lastname AS LeadRep,

(SELECT COUNT(L.LeadId) AS TotalLeads
FROM Leads L
WHERE L.LeadDate BETWEEN @DateFrom AND @DateTo) AS TotalLeads,

(SELECT COUNT(A.DealId) AS TotalDeals
FROM AccountInfo A
LEFT OUTER JOIN MerchantInfo M ON A.DealId = M.DealId
LEFT OUTER JOIN Leads L ON M.LeadInfoId = L.LeadId
WHERE (A.ValidationDate BETWEEN @DateFrom AND @DateTo) AND (A.IsValidated = 1) AND (L.HowSoonId = 10 OR
L.HowSoonId = 12 OR
L.HowSoonId = 14 OR
L.HowSoonId = 16)) AS TotalProcessingDeals,
CASE WHEN A.IsValidated = 1 THEN 'YES' ELSE 'NO' END AS Validated,
AST.Status,

(Select A.ExpectedMonthlySales
From AccountInfo A
Where A.IsValidated = 1)AS ExpectedMonthlySales

FROM Leads L LEFT OUTER JOIN UserMap U ON L.SalesRepId = U.UserId
LEFT OUTER JOIN MerchantInfo M ON L.LeadId = M.LeadInfoId
LEFT OUTER JOIN AccountInfo A ON M.DealId = A.DealId
LEFT OUTER JOIN AccountStatus AST ON A.StatusId = AST.StatusId

WHERE (L.LeadDate BETWEEN @DateFrom AND @DateTo) AND (L.HowSoonId = 10 OR
L.HowSoonId = 12 OR
L.HowSoonId = 14 OR
L.HowSoonId = 16)

ORDER BY L.LeadDate



I cannot figure out this puzzle. Any help would be highly appreciate it.

View 3 Replies View Related

DTS Doesn't Work Through Job

Sep 15, 2005

I'm pretty new to DTS, so forgive me if this is basic. I created a simple DTS package to run a query and export it to a text file. I can execute the package fine from my workstation through EM, but when I try to execute the job to run the package I get this error:
Error = -2147467259 (80004005) Error string: Error opening datafile: Access is denied.

I think that maybe SQL Agent doesn't have the right permissions to write to that network drive. What should the permissions be?

View 3 Replies View Related

IIF Doesn't Work

Nov 10, 2004

This is probably very simple, but I can't get passed this problem.

I have a report in MS Access that uses info generated by a query. One of the text fields in the query contains either the word 'Select' or the name of a course.
The report should display a space if the value is 'Select', or the actual value of the field in any other case. The field can never contain a null value.

I've used:
=IIf([optVoc1]="Select","",[optVoc1])
in the text box on the report, but this only returns #error regardless of the actual content of the field.

What am I doing wrong?

Regards,

BD

View 5 Replies View Related

Sql Job Doesn't Work

Aug 27, 2004

Hi all,

I create and schedule a SQL job to run every minute to update a table base on certain condition but it doesn't work. Job history says successful every time but the table doesn't get updated.

However if I move it to Query Analyzer and run it under dba, it will work. Thinking that it may have to do with the user the job run as, I then change run as user from self to dba. But still SQL job won't update my table.

Anything about user permission or security that I can check? Or it there any other possibility?

TIA

View 1 Replies View Related

Why Doesn't This Work

Apr 26, 2007

When I run the select its fine but I cannot delete..... i have done this many times and it has worked.... I cannot see the error what am i missing

select
eqnow.empnumber,
eqnow_names.empnumber,
eqnow_names.names
--delete
from
eqnow
inner join eqnow_names
on eqnow.empnumber = eqnow_names.empnumber
where
eqnow_names.names is null



i get this error
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'inner'.

View 3 Replies View Related

Doesn't Work

Oct 21, 2007



Msg 15123, Level 16, State 1, Procedure sp_configure, Line 78

The configuration option 'user instances enabled' does not exist, or it may be an advanced option.



Valid configuration options are

View 1 Replies View Related

Help! LIKE Doesn't Work!!!

Apr 19, 2008

Hi to all, I'm building (and learn) an application with VB Express. In "edit dataset with designer" I've build this sql query:

SELECT tbl_soggetto.[ID Soggetto], tbl_soggetto_tipo.Tipo, tbl_soggetto.[Cognome/Denominazione], tbl_soggetto.Nome, tbl_soggetto.Indirizzo, tbl_soggetto.CAP, tbl_soggetto.Città , tbl_soggetto.Provincia, tbl_soggetto.[Telefono 1], tbl_soggetto.[Telefono 2], tbl_soggetto.[Telefono 3], tbl_soggetto.[Telefono 4], tbl_soggetto.[eM@il 1], tbl_soggetto.[eM@il 2], tbl_soggetto.Note
FROM tbl_soggetto INNER JOIN tbl_soggetto_tipo ON tbl_soggetto.[ID Tipo] = tbl_soggetto_tipo.[ID Tipo]
WHERE (tbl_soggetto.[Cognome/Denominazione] LIKE '%' + @Testo + '%')


The LIKE doesn't work!
I call the query with Me.griglia.DataSource = Me.TA_tbl_soggetto_ricerca.Search_Cognome(Me.txt_trova.Text.Trim)

But with LIKE '%ABC%' work!

Me.griglia.DataSource = Me.TA_tbl_soggetto_ricerca.Search_Cognome()

Someone can help me? Thanks...

View 12 Replies View Related

SET Doesn't Work

Dec 11, 2006

When I try to install the problem I get the following error.

The SQL Server service failed to start. For more information, see the SQL Server Books Online topics, "How to: View SQL Server 2005 Setup Log Files" and "Starting SQL Server Manually."

The log tells me nothing useful. I can't start the thing manually because after clicking cancel on the error message, the installer proceeds to roll back the installation.

How do I fix this problem?








View 3 Replies View Related

HELP! Sp_attach_db Doesn&#39;t Work!

Sep 20, 2000

I had a SQL Server falure. I rebiuld Master and tried to attach my database
with sp_attach_db? but get an error

Location: pageref.cpp:3931
Expression: rowLog.RowCount () == 1 || pPage->IsEmpty ()
SPID: 10
Process ID: 119

Connection Broken

View 1 Replies View Related

Attaching Db Doesn&#39;t Work

Mar 1, 2001

I try to copy a DB from one server to another. On the target server an older version of the DB has been deleted and I now try to attach the new version using "sp_attach_db DBname, Filelocation", but I always get an error "Device Activation error. The physical file name 'D:mssql7dataAgency_log.ldf' may be incorrect"
"Database 'Agency' cannot be Created"

To me it seems that the database is looking for the log files (now deleted).
I've tried forcing a new log file I created using the same locations for the mdfs. I've tried using create a new database and replace the mdf file, but nothing works.

View 3 Replies View Related

Trigger - This One Doesn't Work - Why?

Mar 31, 2003

Hi,

I wanted to create a new trigger, but Enterprise Manager tells me about an "Incorrect syntax near @UpdatedByID, line 28". I double-checked everything, but it still does not work :mad: .

Any hints :confused: ?

TIA,

-Gernot


Here is the statement (line 28 is marked with ***):


CREATE TRIGGER TransferToABII ON [dbo].[CALGeneral]
FOR INSERT
AS
BEGIN TRANSACTION
BEGIN
DECLARE @Event varchar(255),
@BBaseUID int,
@StartDate smalldatetime,
@EndDate smalldatetime,
@Details varchar(255),
@AddressID int,
@ProjectID int,
@UpdatedByID int,
@ActID int,
@EventID int

SELECT @Event = Event,
@BBaseUID = BBaseUID,
@StartDate = StartDate,
@EndDate = EndDate,
@Details = Details,
@AddressID = AddressID,
@UpdatedByID = UpdatedBy,
@ProjectID = ProjectID
FROM INSERTED

BEGIN
EXEC BrainBase.dbo.BB_NEW_CREATE_NoteTask_Ret *** (@UpdatedByID,
@AddressID,
@ProjectID,
@BBaseUID,
@StartDate,
GetDate(),
@Event,
NULL,
NULL,
NULL,
NULL,
@Details text,
@ActID = @ActID OUTPUT,
@EventID = @EventID OUTPUT)
END
BEGIN
UPDATE CALGeneral SET ActID = @ActID WHERE ID = INSERTED.ID
END
END

IF @@ERROR <> 0
BEGIN
RAISERROR('Error occured',16,1)
ROLLBACK TRANSACTION
END
COMMIT TRANSACTION

View 4 Replies View Related

Identity Doesn&#39;t Work

Nov 11, 1999

I'm working with mssql 6.5

I have an primary key column with Identity property.
And at the moment server doesn't insert the proper value to this column.

Error message is following

Msg 2601, Level 14, State 3
Attempt to insert duplicate key row in object 'Spot' with unique index 'XPKSpot'
Command has been aborted.

The datatype of this column is int, and number of rows ~17000.
If I execute select @@identity it returns null.

View 4 Replies View Related

WHERE With An Alias Doesn't Work

Aug 2, 2004

I'm combining first name, last name, middle name, and an ID number together into an alias. Then I need to match that alias with a variable passed to the page (its a search results page). The problem is it claims that there is no table with the name of my alias. Anyone know what I'm doing wrong?

A mockup of the SQL looks like this:

SELECT UserID, Last_Name + ', ' + First_Name + ' ' + Middle_Name + '.' AS name
FROM Table
WHERE name LIKE 'variable%'


Everything looks right with the results, if I take out the WHERE clause it has name displayed properly and joined together with the rest of the data in the results properly.

Thanks in advance for any help that can be provided!

View 3 Replies View Related

Linkserver Doesn't Work.

Oct 12, 2007

I have a query that doesn't work when i use 4 name convention instead of a openquery. The msg is below. Anyone know what is going on?
Both queries are the same but one doesn't work.

-- works
SELECT TOP 1 * FROM OPENQUERY(AS400_PROD, 'SELECT * FROM PPTREASUSA.ORDDET')

-- doesnt work
SELECT TOP 1 * FROM AS400_PROD.S1030Y3M.PPTREASUSA.ORDDET



Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: Unspecified error]
[OLE/DB provider returned message: [IBM][iSeries Access ODBC Driver][DB2 UDB]CPF5715 - File ORDDET01 in library QTEMP not found.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBSchemaRowset::GetRowset returned 0x80004005: ].





http://www.sqlserverstudy.com

View 2 Replies View Related

Sp_send_dbmail Doesn't Work

Jan 17, 2008

hi all,
i made a stored procedure that uses the sp_send_dbmail to send mails. SQL server dislays the message "mail queued" but nothing is recieved

here is the code of the stored procedure i made
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Exams',
@recipients = 'me@domain.com',
@Body_format = 'HTML' ,
@subject = 'Room Preparation' ,
@body='hi there';
so can anyone help with this issue
thanks in advance

View 6 Replies View Related

Drill Through Doesn't Work.

Feb 7, 2007

I have a drill through that passes four parameters. Three are passed from the current selections in that reports parameters and the fourth needs to be the customer name they click on in the body of the report so it's passed as Fields!fieldname.Value. When I click on the customer name, the drillthough fires but the report simply doesn't load. If I remove the parameter from the field clicked on and just pass the three parameters, it goes to the drill through correctly and that fourth parameter just sets to the default for that parameter in that report.

I can then simply check that parameter and select the value from the list that is exactly the same as the value I was attempting to pass it in the drill through and report refreshes correctly.

Whatever it is, is something in the manner that the value is passed in the drill through specifically.

Any ideas?

View 1 Replies View Related

Using SP_ATTACH_SINGLE_FILE_DB Doesn't Work.

Mar 15, 2007

Hello,
I've rescued a MDF and LDF files off a client's old server, and I wanted to attach it to our own, but I can't seem to get the command to work, basically I have these two files, which I've dropped on our server:

C:Program FilesMicrosoft SQL ServerMSSQLDataMYCLIENTNAME_Data.MDF
C:Program FilesMicrosoft SQL ServerMSSQLDataMYCLIENTNAME_Data.LDF

So when I do a
SP_ATTACH_SINGLE_FILE_DB 'somedb','C:Program FilesMicrosoft SQL ServerMSSQLDataMYCLIENTNAME_Data.MDF'

It says the LDF path my be incorrect, and that there's two other files that are missing:
MYCLIENTNAME_LOG (no extension)
extra_log (no extension)

I thought the whole point of the command is that you only need a single file?
Its very hard to go back to the client's old server and try to find these two files, and it doesn't really matter if we loose a bit of data, so long as the bulk of it is available.

Update: I think I've found the answer...its not possible to do this, it really needs all the log files.
Any workarounds?

View 1 Replies View Related

Why Doesn't This DecryptByKeyAutoCert Work?

Jun 25, 2007

This works:



-- authenticator

CREATE FUNCTION [dbo].[cc2_Helper]( @SecretData VARBINARY(256), @cId INT )

RETURNS NVARCHAR(50)

WITH EXECUTE AS 'DBO'

AS

BEGIN

RETURN convert( NVARCHAR(50), decryptbykeyautocert( cert_id( 'cert_SecretTable_SecretData_Key' ), null, @SecretData, 1, convert(varbinary, @cId) ) )

END

go

CREATE VIEW [dbo].[cc2View2]

AS

SELECT CardID as CardID, [dbo].[cc2_Helper](CardNumber, CardID) as CardNumber FROM [dbo].[cc2]

go

GRANT SELECT ON [dbo].[cc2View2] TO [user_low_priv]





This doesn't:



-- auth2/view3

CREATE FUNCTION [dbo].[cc2_Helper2]( @SecretData VARBINARY(256), @vBin VARBINARY )

RETURNS NVARCHAR(50)

WITH EXECUTE AS 'DBO'

AS

BEGIN

RETURN convert( NVARCHAR(50), decryptbykeyautocert( cert_id( 'cert_SecretTable_SecretData_Key' ), null, @SecretData, 1, @vBin ) )

END

go

CREATE VIEW [dbo].[cc2View3]

AS

SELECT CardID as CardID, [dbo].[cc2_Helper2](CardNumber, convert(varbinary, CardID)) as CardNumber FROM [dbo].[cc2]

go

GRANT SELECT ON [dbo].[cc2View3] TO [user_low_priv]



WHY? Note that the conversion to VARBINARY was moved from the call to DecryptByKeyAutoCert to the call to cc2_Helper2. That is the only change...



But if I declare @vBin as VARBINARY(256) then it does work! Guess I'm a little confused on declaring vars...anyone can elucidate? Thanks.

View 1 Replies View Related

SQLDS Doesn't Work Well With Uniqueidentifier ?

Dec 25, 2005

In the Data Source Wizard the "insert,delete,update" advanced options are greyed out.

The table's PK is not an identity, but a uniqueidentifier.

View 2 Replies View Related

STUMPED: Why Doesn't This Procedure Work?

Jan 16, 2006

I have been looking at this for over a day now. I cannot see why this procedure does not work, its so simple.
No matter what happens it always returns 0. If it locates a record, it doesnt update it, yet it still returns 0.
It should not be returning 0 if its not updating so I can't figure out why it does.
Why does this always return 0?
[pre]Create Procedure CreateNewCategory @title nvarchar(100), @description nvarchar(1000), @displayOrder intAS DECLARE @Result as int
IF EXISTS(SELECT categoryTitle FROM categories WHERE categoryTitle = @title) BEGIN  SELECT @Result = 1 ENDELSE BEGIN  INSERT INTO categories(categoryTitle, categoryDescription, displayOrder)  VALUES(@title, @description, @displayOrder)   /* If no error was encountered, 0 will be returned. */  SELECT @Result = @@Error ENDGO[/pre]
Thanks!
 

View 2 Replies View Related

LOAD TABLE With UNC Doesn&#39;t Work

Mar 28, 2000

Since I have to go across the network, I'm trying to use the UNC. However, this won't even work when I'm using the UNC to point to the server on which this is run. I'm trying to restore a single table on 6.5. What is the obvious piece that I'm missing?

This works.

LOAD TABLE address
FROM DISK = 'd:MSSQLackupDBBackup.DAT'
WITH source='address'


This doesn't.

LOAD TABLE address
FROM DISK = 'server1d$MSSQLackupDBBackup.DAT'
WITH source='address'

View 2 Replies View Related

Upgrade And Now Mail Doesn`t Work

Sep 4, 1998

Hello, I upgraded to Beta 3 and now mail isn`t working(I hate mail!) I found 2 error messages. One when trying to add an operators email id and the other when I try to "test" mail under server properties.

- Unable to start a mail session on the server with this mail profile.

- no mail profile defined.

The account that I am using is used for all our SQL Servers and was working on this server on 6.5 before the upgrade. I have checked the new SQLServerAgent service and it`s using the correct account.

Any help would be appreciated.

And to all you that are off for a long Labor Day weekend...I`m jealous!

View 1 Replies View Related

Xfragent Doesn&#39;t Work After Upgrade To 7.0

Sep 5, 2000

Hi All,

I just upgraded my servers from 6.5 to 7.0,
however, all the transfer jobs that was using xfragent.exe
does not work anymore. Any idea? Please help.

Thanks,
Richard

View 1 Replies View Related

A Simple Trigger That Doesn't Work

Jan 18, 2006

Hi all, I have a problem with this trigger. It seams to be very simple, but it doesn't work...

I created a trigger on a table and I would want that this one updates a field of a table on a diffrent DB (Intranet). When I test it normally (a real situation), it doesn't work, but when I do an explicit update ("UPDATE AccesCard SET LastMove = getDate();" by example) it works.

If anyone could help me, I would appreciate.

NB: Is there a special way, in a trigger, to update a table when the table to update is on another BD ?

Francois

This is the trigger:
------------------------------------------------------------

ALTER TRIGGER UStatus
ON AccesCard
AFTER UPDATE, INSERT
AS

DECLARE @noPerson int

SET NOCOUNT OFF

IF UPDATE(LastMove)
BEGIN
SELECT @noPerson = Person FROM INSERTED
UPDATE Intranet.dbo._Users SET Intranet.dbo._Users.status = 1 WHERE personNo = @noPerson;
END

SET NOCOUNT ON

View 5 Replies View Related

SQL Server 2000: TCP/IP Doesn't Seem To Work

Nov 24, 2006

I have the following situation: A webserver in a DMZ which connects to a DbServer in a Domain.

but when I try to make a ODBC connection on the webserver I get the well-known "Server does not exist or access denied" error.

I tried using IP address as well as FQDN. I turned off the firewall in the router (I'm able to access shares and what not from the webserver so the firewall is really turned off).

I think however i have narrowed down the problem to the TCP/IP connection of the SQL Server. Normally when you try from a command prompt
"telnet dbserver 1433" you get a connection. But when I try this to the specific DbServer I get an "unable to connect" error. Even when I try it on the dbserver itself (so telnet localhost 1433). This should work always?

In the network configuration of the sql server the Named Piped as well as the TCP/IP protocols are enabled. TCP/IP is offcourse configured on port 1433.

How can I fix this problem?

View 2 Replies View Related

An Ugly Self-join (oh, That Doesn't Work!!)

Feb 17, 2004

Hi again, all...here I am again, trying to work on my CloseIndex thang again...Same subject, different tack...

Basically, I have two tables...for the sake of simplicity, let me define them as:


PortfolioIndex
PortfolioID int
CreateDate smalldatetime
CloseIndex float

PortfolioPerformance
PortfolioID int
CreateDate smalldatetime
PrevDate smalldatetime
DailyPerChg float

UPDATE PortfolioIndex
SET CloseIndex = CASE
WHEN PPI.CloseIndex IS NULL THEN 100.00
ELSE (PPI.CloseIndex + (PPI.CloseIndex * PP.DailyPerChg / 100))
END
FROM PortfolioIndex AS P INNER JOIN PortfolioIndex AS PPI on (P.PortfolioID = PPI.PortfolioID), PortfolioPerformance AS PP
WHERE (P.PortfolioID = PP.PortfolioID) AND
((P.CreateDate = PP.CreateDate) AND
(P.CreateDate = @CreateDate) AND
(PPI.CreateDate = PP.PrevDate))


What I am trying to do is...get the previous day's portfolioIndex row's CloseIndex and create a new one for today's row.

As ugly as it is, it works when I execute it in the SQL Query Analyzer, but when I try to create the stored procedure, the syntax check complains that the PortfolioIndex reference at the UPDATE... part is AMBIGUOUS...yet when I define it in the SP as P.PortfolioIndex, it fails at run time saying there is no object named P.PortfolioIndex (well, of course there isn't!).

How can I make this work (and if possible, make it prettier too! *L* ;) )

View 7 Replies View Related

BCP Utility Doesn't Work With View

Mar 16, 2015

I am using BCP utility to export data from a view to text file. I get the following error

"Copy direction must be either r 'in', 'out' ..." when I execute the following

exec master..xp_cmdshell 'bcp [Salesforce_Prod].[dbo].[Project With Opps] out c: est99.txt -c -t, -T -S'

The same thing work if I replace the view in the statement above with a table name.

View 3 Replies View Related

Why Doesn't SELECT @columns... Work?

Aug 12, 2005

Bob writes "Your FAQ asks an intersting question:

Why doesn't SELECT @columns FROM @tablname work?

No answer is provided however.

I need to write a stored procedure the passes in a fieldname, retrives next key type int data from that field, increments the field, and returns the NextKey.

Would be nice if something like this worked:
DECLARE @iNext INT
SELECT @iNext = SELECT @columnName FROM Next_Keys WHERE ID = 1
SELECT @iNext = @iNext +1
UPDATE Next_Keys Set @columnName = @iNext WHERE ID = 1
RETURN @iNext

SQL Server 2000 - Win Server 2003
Editing SP from VS2005
TSQL newbe writing first sp"

View 1 Replies View Related

Simple Script... Doesn't Work

Apr 20, 2007

***Disclaimer***
This is homework, just need a hint.

I compare this to other scripts and can't find any differences. I'm getting a syntax error:

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '('.

Any hints would be great! Thanks.



USE StateUBookstore

ALTER TABLE dbo.Classes
ADD( InstructorID char (5) NOT NULL,
Credits int NOT NULL);
GO

ALTER TABLE dbo.Students
ADD( PhoneNO char(14) NULL,
Email char (50) NULL),
DROP COLUMN BookStoreEmp;
GO

ALTER TABLE dbo.Enrollment
ADD( Semester int NOT NULL,
EndDate DateTime NOT NULL);

View 13 Replies View Related

Wscript.sleep Doesn't Work In SQL?

Aug 22, 2007

I setup a maintenance plan in SQL and added some vbscript codes. One line is just wscript.sleep 10000. When I tried to run the job, I received error description: variable is undefined: 'wscript'

What's up with SQL?

View 1 Replies View Related

Why This Trigger Doesn't Work In Sql 2005

Mar 28, 2008

hi eveyrone,

I have an after insert trigger that works in sql2000 but not in sql2005. Can you please help me!!

CREATE trigger dbo.trUPS_tbl_I
on dbo.UPS_tbl
After Insert --For insert
as
declare @Tracking varchar(800),
@UPSID varchar(10),
@Cmmd varchar(800)

select @UPSID = cast(inserted.UPSID as varchar)
from inserted

commit

set @Cmmd = 'c:TasksUpdTrackingUpdateTrackingCS ' + @UPSID + ' ' + 'UPS1'
EXEC master.dbo.xp_cmdshell @Cmmd


The UpdateTrackingCS program will call a stored procedure to get the inserted data and update other databases. And the reason to put the commit statement in sql2000 is to have sql commit the transaction, so the store procedure in UpdateTrackingCS can query thtat inserted records.

When I use the same code in SQL2005, no matter what I do the UpdateTrackingCS program cannot query the data by the UPSID. It always says record not found.

I have tried to change commit to begin trans ... commit trans. But nothing works. Please help!

Thanks in advance.

lyw

View 1 Replies View Related







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