problem with before insert/update trigger

hey guys!
i'm not able to understand the problem. the situation is like this,
i've a table that has before insert/update trigger which checks for
some data in some tables and if the data is not found it suppose to
throw an error.
to get the data, that is inserted into the table, i'm using this query
select @.ResourceID=ResourceID from inserted
but this returns me nothing and so the rest of the process is failing.
the error threw was this :
Server: Msg 50000, Level 16, State 1, Procedure trigg_insert_XXX, Line
30
and i've no clue about this error. even not able to think any other
solution.
please guys help me out here, i'm tired of this Database thing,
thanks,
Luckylucky wrote:
> hey guys!
> i'm not able to understand the problem. the situation is like this,
> i've a table that has before insert/update trigger which checks for
> some data in some tables and if the data is not found it suppose to
> throw an error.
> to get the data, that is inserted into the table, i'm using this query
> select @.ResourceID=ResourceID from inserted
What happens when multiple rows are inserted? Your trigger isn't
written to properly handle multi-row inserts.
> but this returns me nothing and so the rest of the process is failing.
> the error threw was this :
> Server: Msg 50000, Level 16, State 1, Procedure trigg_insert_XXX, Line
> 30
> and i've no clue about this error. even not able to think any other
> solution.
> please guys help me out here, i'm tired of this Database thing,
> thanks,
> Lucky
>
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||In order for us to 'see' your issue, please provide the table DDL, the
entire Trigger code, and perhaps a few rows of sample data in the form of
INSERT statements.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"lucky" <tushar.n.patel@.gmail.com> wrote in message
news:1164899748.814785.48970@.l39g2000cwd.googlegroups.com...
> hey guys!
> i'm not able to understand the problem. the situation is like this,
> i've a table that has before insert/update trigger which checks for
> some data in some tables and if the data is not found it suppose to
> throw an error.
> to get the data, that is inserted into the table, i'm using this query
> select @.ResourceID=ResourceID from inserted
> but this returns me nothing and so the rest of the process is failing.
> the error threw was this :
> Server: Msg 50000, Level 16, State 1, Procedure trigg_insert_XXX, Line
> 30
> and i've no clue about this error. even not able to think any other
> solution.
> please guys help me out here, i'm tired of this Database thing,
> thanks,
> Lucky
>|||Hi Tracy ,
your guess is correct. i'm only checking for a single ID in the
trigger. i dont know how can i handle the situation where i would have
mulitple rows.
i thought the trigger will be triggered each time row is getting
inserted. i dont know how the trigger behaves in bulk insert/update.
by the way here is the code for the trigger. please give it a look and
advise me how can i modify it to handle the bulk insert/update.
alter TRIGGER trigg_insert_row
ON [Table10]
FOR INSERT, UPDATE
AS
declare @.ResourceID uniqueidentifier
declare @.msg varchar(255)
declare @.flag bit
set @.flag=0
select @.ResourceID=ResourceID from inserted
IF EXISTS(SELECT [ID] FROM [Table1] where [ID]=@.ResourceID)
set @.flag=1
IF EXISTS(SELECT [ID] FROM [Table2] where [ID]=@.ResourceID)
set @.flag=1
IF EXISTS(SELECT [ID] FROM [Table3] where [ID]=@.ResourceID)
set @.flag=1
IF EXISTS(SELECT [ID] FROM [Table4] where [ID]=@.ResourceID)
set @.flag=1
if(@.flag=0)
begin
set @.msg='Resource Not Found -- '--+ cast( @.ResourceID as varchar(255)
)
RAISERROR ( @.msg,16, 1 )
rollback transaction
end
-----
Please let me know if u need some more information on it.
thanks,
Lucky|||lucky wrote:
> Hi Tracy ,
> your guess is correct. i'm only checking for a single ID in the
> trigger. i dont know how can i handle the situation where i would have
> mulitple rows.
> i thought the trigger will be triggered each time row is getting
> inserted. i dont know how the trigger behaves in bulk insert/update.
> by the way here is the code for the trigger. please give it a look and
> advise me how can i modify it to handle the bulk insert/update.
> alter TRIGGER trigg_insert_row
> ON [Table10]
> FOR INSERT, UPDATE
> AS
> declare @.ResourceID uniqueidentifier
> declare @.msg varchar(255)
> declare @.flag bit
> set @.flag=0
> select @.ResourceID=ResourceID from inserted
> IF EXISTS(SELECT [ID] FROM [Table1] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table2] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table3] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table4] where [ID]=@.ResourceID)
> set @.flag=1
> if(@.flag=0)
> begin
> set @.msg='Resource Not Found -- '--+ cast( @.ResourceID as varchar(255)
> )
> RAISERROR ( @.msg,16, 1 )
> rollback transaction
> end
>
> -----
> Please let me know if u need some more information on it.
> thanks,
> Lucky
>
Untested, but I think something like this will work better for you:
IF OBJECT_ID('trigg_insert_row', 'TR') IS NULL
BEGIN
DROP TRIGGER Table10.trigg_insert_row
END
GO
CREATE TRIGGER trigg_insert_row
ON [Table10]
FOR INSERT, UPDATE
AS
DECLARE @.ResourceID UNIQUEIDENTIFIER
DECLARE @.msg VARCHAR(255)
SELECT TOP 1 @.ResourceID = ResourceID
FROM
(
SELECT ResourceID
FROM inserted
LEFT JOIN Table1
ON inserted.ResourceID = Table1.ID
WHERE Table1.ID IS NULL
UNION
SELECT ResourceID
FROM inserted
LEFT JOIN Table2
ON inserted.ResourceID = Table2.ID
WHERE Table2.ID IS NULL
UNION
SELECT ResourceID
FROM inserted
LEFT JOIN Table3
ON inserted.ResourceID = Table3.ID
WHERE Table3.ID IS NULL
UNION
SELECT ResourceID
FROM inserted
LEFT JOIN Table4
ON inserted.ResourceID = Table4.ID
WHERE Table4.ID IS NULL
) AS MissingIDs
IF @.ResourceID IS NULL
BEGIN
SET @.msg = 'Resource Not Found -- ' + CAST(@.ResourceID AS VARCHAR(255))
RAISERROR (@.msg, 16, 1)
ROLLBACK TRANSACTION
END
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi guys,
i was doing some R&D and i found that when i wrote a query :
select * from inserted
into the trigger, the query returned me no rows and that is why the
rest of the code failed. it is very very streng that before insert
trigger is getting triggered but it is not able to find any data in the
INSERTED table.
data i'm inserting into the table using Stored Procedure. i also check
into the procedure that it is getting the correct data and it showed me
that it is getting the correct data but when it is firing the INSERT
STATEMENT, the trigger on the table is not able to find the data.
Man! i'm not able to understand anything about this.
guys, deadline is very near and i need to finish this as soon as
possible. please help me out.
thanks,
Lucky
lucky wrote:
> Hi Tracy ,
> your guess is correct. i'm only checking for a single ID in the
> trigger. i dont know how can i handle the situation where i would have
> mulitple rows.
> i thought the trigger will be triggered each time row is getting
> inserted. i dont know how the trigger behaves in bulk insert/update.
> by the way here is the code for the trigger. please give it a look and
> advise me how can i modify it to handle the bulk insert/update.
> alter TRIGGER trigg_insert_row
> ON [Table10]
> FOR INSERT, UPDATE
> AS
> declare @.ResourceID uniqueidentifier
> declare @.msg varchar(255)
> declare @.flag bit
> set @.flag=0
> select @.ResourceID=ResourceID from inserted
> IF EXISTS(SELECT [ID] FROM [Table1] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table2] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table3] where [ID]=@.ResourceID)
> set @.flag=1
> IF EXISTS(SELECT [ID] FROM [Table4] where [ID]=@.ResourceID)
> set @.flag=1
> if(@.flag=0)
> begin
> set @.msg='Resource Not Found -- '--+ cast( @.ResourceID as varchar(255)
> )
> RAISERROR ( @.msg,16, 1 )
> rollback transaction
> end
>
> -----
> Please let me know if u need some more information on it.
> thanks,
> Lucky|||Hi Tracy,
Thanks for you great help. the solution u provided worked very well.
another problem i posted was not in the Trigger but it was in the SP.
i'm still working on it but the problem of bulk insertion is solve,
thanks to you.
Lucky
Tracy McKibben wrote:
> lucky wrote:
> > Hi Tracy ,
> > your guess is correct. i'm only checking for a single ID in the
> > trigger. i dont know how can i handle the situation where i would have
> > mulitple rows.
> > i thought the trigger will be triggered each time row is getting
> > inserted. i dont know how the trigger behaves in bulk insert/update.
> >
> > by the way here is the code for the trigger. please give it a look and
> > advise me how can i modify it to handle the bulk insert/update.
> >
> > alter TRIGGER trigg_insert_row
> > ON [Table10]
> > FOR INSERT, UPDATE
> > AS
> >
> > declare @.ResourceID uniqueidentifier
> > declare @.msg varchar(255)
> > declare @.flag bit
> > set @.flag=0
> >
> > select @.ResourceID=ResourceID from inserted
> >
> > IF EXISTS(SELECT [ID] FROM [Table1] where [ID]=@.ResourceID)
> > set @.flag=1
> >
> > IF EXISTS(SELECT [ID] FROM [Table2] where [ID]=@.ResourceID)
> > set @.flag=1
> >
> > IF EXISTS(SELECT [ID] FROM [Table3] where [ID]=@.ResourceID)
> > set @.flag=1
> >
> > IF EXISTS(SELECT [ID] FROM [Table4] where [ID]=@.ResourceID)
> > set @.flag=1
> >
> > if(@.flag=0)
> > begin
> > set @.msg='Resource Not Found -- '--+ cast( @.ResourceID as varchar(255)
> > )
> > RAISERROR ( @.msg,16, 1 )
> > rollback transaction
> > end
> >
> >
> > -----
> > Please let me know if u need some more information on it.
> >
> > thanks,
> >
> > Lucky
> >
> Untested, but I think something like this will work better for you:
> IF OBJECT_ID('trigg_insert_row', 'TR') IS NULL
> BEGIN
> DROP TRIGGER Table10.trigg_insert_row
> END
> GO
> CREATE TRIGGER trigg_insert_row
> ON [Table10]
> FOR INSERT, UPDATE
> AS
> DECLARE @.ResourceID UNIQUEIDENTIFIER
> DECLARE @.msg VARCHAR(255)
> SELECT TOP 1 @.ResourceID = ResourceID
> FROM
> (
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table1
> ON inserted.ResourceID = Table1.ID
> WHERE Table1.ID IS NULL
> UNION
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table2
> ON inserted.ResourceID = Table2.ID
> WHERE Table2.ID IS NULL
> UNION
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table3
> ON inserted.ResourceID = Table3.ID
> WHERE Table3.ID IS NULL
> UNION
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table4
> ON inserted.ResourceID = Table4.ID
> WHERE Table4.ID IS NULL
> ) AS MissingIDs
> IF @.ResourceID IS NULL
> BEGIN
> SET @.msg = 'Resource Not Found -- ' + CAST(@.ResourceID AS VARCHAR(255))
> RAISERROR (@.msg, 16, 1)
> ROLLBACK TRANSACTION
> END
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Tracy McKibben wrote:
> Untested, but I think something like this will work better for you:
[snip]
> SELECT TOP 1 @.ResourceID = ResourceID
> FROM
> (
> SELECT ResourceID
> FROM inserted
> LEFT JOIN Table1
> ON inserted.ResourceID = Table1.ID
> WHERE Table1.ID IS NULL
> UNION
[snip]
> ) AS MissingIDs
> IF @.ResourceID IS NULL
> BEGIN
> SET @.msg = 'Resource Not Found -- ' + CAST(@.ResourceID AS VARCHAR(255))
> RAISERROR (@.msg, 16, 1)
> ROLLBACK TRANSACTION
*scratches head* Shouldn't the test be IF @.ResourceID IS NOT NULL?|||Ed Murphy wrote:
> Tracy McKibben wrote:
>> Untested, but I think something like this will work better for you:
> [snip]
>> SELECT TOP 1 @.ResourceID = ResourceID
>> FROM
>> (
>> SELECT ResourceID
>> FROM inserted
>> LEFT JOIN Table1
>> ON inserted.ResourceID = Table1.ID
>> WHERE Table1.ID IS NULL
>> UNION
> [snip]
>> ) AS MissingIDs
>> IF @.ResourceID IS NULL
>> BEGIN
>> SET @.msg = 'Resource Not Found -- ' + CAST(@.ResourceID AS VARCHAR(255))
>> RAISERROR (@.msg, 16, 1)
>> ROLLBACK TRANSACTION
> *scratches head* Shouldn't the test be IF @.ResourceID IS NOT NULL?
Yep, it should be. I did say "untested"... :-)
Tracy McKibben
MCDBA
http://www.realsqlguy.comsql

problem with a stored procedure

Hello guys,
I have again problem with a stored procedure in sql server.
I would like to execute this query
declare @.sSQL varchar(255)
SET @.sSQL =
'SELECT TOP 1 [value], date, name, [times]
FROM (SELECT value, date, times, name
FROM view
WHERE ValueDate =
(SELECT MAX(date)
AS maxdate
FROM view
v
WHERE (date
BETWEEN ''2006-02-01'' AND ''2006-02-28''))) Newtable
WHERE (name = ''TEST'')
GROUP BY [value], date, [times], name
ORDER BY [times] DESC'
EXEC (@.sSQL)
When I execute the query I have no problem but ... when i tried to
stored it I have this several problem I do not know why ... something
like
Line 4: Incorrect syntax near '='
I would like be able to pass variable on it and create function to
store the results in a table.
Please someone could help me on that.
Inaina
Replace EXEC(@.sql) with PRINT @.sql in order to debug and try run it in the
QA.
"ina" <roberta.inalbon@.gmail.com> wrote in message
news:1144763910.125857.198630@.e56g2000cwe.googlegroups.com...
> Hello guys,
> I have again problem with a stored procedure in sql server.
> I would like to execute this query
> declare @.sSQL varchar(255)
>
> SET @.sSQL =
> 'SELECT TOP 1 [value], date, name, [times]
> FROM (SELECT value, date, times, name
> FROM view
> WHERE ValueDate =
> (SELECT MAX(date)
> AS maxdate
> FROM view
> v
> WHERE (date
> BETWEEN ''2006-02-01'' AND ''2006-02-28''))) Newtable
> WHERE (name = ''TEST'')
> GROUP BY [value], date, [times], name
> ORDER BY [times] DESC'
> EXEC (@.sSQL)
> When I execute the query I have no problem but ... when i tried to
> stored it I have this several problem I do not know why ... something
> like
> Line 4: Incorrect syntax near '='
> I would like be able to pass variable on it and create function to
> store the results in a table.
> Please someone could help me on that.
> Ina
>|||> When I execute the query I have no problem but ... when i tried to
> stored it
What does "tried to stored it" mean?

> I would like be able to pass variable on it and create function to
> store the results in a table.
Well, you can't execute dynamic SQL inside a function. If you provide
better specifications on exactly what you are trying to accomplish (instead
of describing how you are already trying to accomplish it), we may be able
to provide better assistance.|||Is it actually spanning multiple lines in your stored procedure? I don't
think this is allowed. Strings have to be contained on a single line.
Either put everything on one line, or concatenate each line to the end of
@.sSQL
Also, 255 is probably not long enough to contain your SQL. Bump this up to
2000 and it should handle most reasonable sql statements.
Lastly, don't ever use dynamic SQL if you have a choice. There are times
when it is needed, but you should simply be able to pass the parameters
inside the stored procedure without dynamic SQL.
Look up "Parameters" in books on line for an explanation of how to use them.
"ina" <roberta.inalbon@.gmail.com> wrote in message
news:1144763910.125857.198630@.e56g2000cwe.googlegroups.com...
> Hello guys,
> I have again problem with a stored procedure in sql server.
> I would like to execute this query
> declare @.sSQL varchar(255)
>
> SET @.sSQL =
> 'SELECT TOP 1 [value], date, name, [times]
> FROM (SELECT value, date, times, name
> FROM view
> WHERE ValueDate =
> (SELECT MAX(date)
> AS maxdate
> FROM view
> v
> WHERE (date
> BETWEEN ''2006-02-01'' AND ''2006-02-28''))) Newtable
> WHERE (name = ''TEST'')
> GROUP BY [value], date, [times], name
> ORDER BY [times] DESC'
> EXEC (@.sSQL)
> When I execute the query I have no problem but ... when i tried to
> stored it I have this several problem I do not know why ... something
> like
> Line 4: Incorrect syntax near '='
> I would like be able to pass variable on it and create function to
> store the results in a table.
> Please someone could help me on that.
> Ina
>|||> Is it actually spanning multiple lines in your stored procedure? I don't
> think this is allowed. Strings have to be contained on a single line.
Strings can span lines. The only (obvious) limitation is the fact that they
need to be enclosed in single quotes.
ML
http://milambda.blogspot.com/|||Thank you for your help and I am sorry ... but I am really newbie and I
am trying to introduce my self in sql server programming.
What I am trying to do it is:
My main query gives to me the max value for a ticket during a period of
time. What I would like to do is the to have the max value for each end
of month for a ticket. each ticket has its own creation date and as
this period I would like to have the max value until today.
For example:
NameTicket |creation_date |month |year |value
----
-
Ticket1 2005-09-15 September 2005 120
ticket 1 2005-09-15 October 2005 125
Ticket 1 2005-09-15 November 2005 152
Ticket 1 2005-09-15 December 2005 152
Sometimes there is no value for one month so it needs to take the max
value for the previous month.
What I would like to do with my query is how to set parameter for sql,
in that what I can set the ticketname and period.
Thank you a lot for your help
Ina|||Thank you for your help and I am sorry ... but I am really newbie and I
am trying to introduce my self in sql server programming.
What I am trying to do it is:
My main query gives to me the max value for a ticket during a period of
time. What I would like to do is the to have the max value for each end
of month for a ticket. each ticket has its own creation date and as
this period I would like to have the max value until today.
For example:
NameTicket |creation_date |month |year |value
----
-
Ticket1 2005-09-15 September 2005 120
ticket 1 2005-09-15 October 2005 125
Ticket 1 2005-09-15 November 2005 152
Ticket 1 2005-09-15 December 2005 152
Sometimes there is no value for one month so it needs to take the max
value for the previous month.
What I would like to do with my query is how to set parameter for sql,
in that what I can set the ticketname and period.
Thank you a lot for your help
Ina|||This sounds better and fairly simple to acomplish. All we need now is DDL an
d
sample data.
This article will help you help us:
http://www.aspfaq.com/etiquette.asp?id=5006
ML
http://milambda.blogspot.com/|||Thank you I will go through that and try to find the solution of my
problem. Thank you :D|||Hello,
I tried to see that but what I need it is to have the max of each month
since the startdate until endate. I already have the max of the period
a set. how to have the list of all period.
Can I use the function month() to accomplish it?
ina

problem with a stored procedure

Hello guys,
I have again problem with a stored procedure in sql server.
I would like to execute this query
declare @.sSQL varchar(255)
SET @.sSQL =
'SELECT TOP 1 [value], date, name, [times]
FROM (SELECT value, date, times, name
FROM view
WHERE ValueDate =
(SELECT MAX(date)
AS maxdate
FROM view
v
WHERE (date
BETWEEN ''2006-02-01'' AND ''2006-02-28''))) Newtable
WHERE (name = ''MIREMONT TEST'')
GROUP BY [value], date, [times], name
ORDER BY [times] DESC'
EXEC (@.sSQL)
When I execute the query I have no problem but ... when i tried to
stored it I have this several problem I do not know why ... something
like
Line 4: Incorrect syntax near '='
I would like be able to pass variable on it and create function to
store the results in a table.
Please someone could help me on that.
InaI think variable @.sSQL je very short:
Try declare @.sSQL varchar(2000)
"ina" wrote:

> Hello guys,
> I have again problem with a stored procedure in sql server.
> I would like to execute this query
> declare @.sSQL varchar(255)
>
> SET @.sSQL =
> 'SELECT TOP 1 [value], date, name, [times]
> FROM (SELECT value, date, times, name
> FROM view
> WHERE ValueDate =
> (SELECT MAX(date)
> AS maxdate
> FROM view
> v
> WHERE (date
> BETWEEN ''2006-02-01'' AND ''2006-02-28''))) Newtable
> WHERE (name = ''MIREMONT TEST'')
> GROUP BY [value], date, [times], name
> ORDER BY [times] DESC'
> EXEC (@.sSQL)
> When I execute the query I have no problem but ... when i tried to
> stored it I have this several problem I do not know why ... something
> like
> Line 4: Incorrect syntax near '='
> I would like be able to pass variable on it and create function to
> store the results in a table.
> Please someone could help me on that.
> Ina
>

problem with "subquery returned more than 1 value"

Hi guys,
I'm getting an error of 'subquery returned more than 1 value' when I try to
run this, which I guess is because the @.remoteID is being set to a SELECT
that returns multiple rows. How do I go about this so that I can accomplish
checking for all existing IDs on the local server (against the remote
server); updating each matching ID; or else inserting a new one?
I think I've got the overall concept right, at least in terms of the
updates/inserts, but need to clarify for SQL Server the exact row to be
involved in any update. Then again, since I'm still trying to learn I could
also be way off...
CollectionID (identity) is the primary key in the table.
#######
ALTER PROCEDURE dbo.usp_CollectionUpdate
AS
BEGIN
DECLARE @.serverUP varchar(100)
SET @.serverUP = ( SELECT SRVNAME
FROM [xxx.xxx.xxx.xxx].master.dbo.sysservers )
BEGIN
-- is the remote server available
IF LEN(@.serverUP) > 0
DECLARE @.getCount int
SET @.getCount = ( SELECT COUNT(CollectionID)
FROM [xxx.xxx.xxx.xxx].Collection1.dbo.tblCollection
WHERE (DATEDIFF(d, LastCollection, GetDate()) = 1) )
BEGIN
-- are there rows to update
IF @.getCount > 0
DECLARE @.remoteID int
SET @.remoteID = ( SELECT CollectionID
FROM [xxx.xxx.xxx.xxx].Collection1.dbo.tblCollection
WHERE (DATEDIFF(d, LastCollection, GetDate()) = 1) )
BEGIN
-- match found so update the id
IF @.remoteID IN ( SELECT CollectionID
FROM dbo.tblCollection )
UPDATE dbo.tblCollection
SET LastCollection = t2.LastCollection, Notes = t2.Notes
FROM [xxx.xxx.xxx.xxx].Collection1.dbo.tblCollection t2
WHERE (dbo.tblCollection.CollectionID = @.remoteID)
-- no match so insert a new one
ELSE
SET IDENTITY_INSERT dbo.tblCollection ON
INSERT INTO dbo.tblCollection (CollectionID, LastCollection, Notes,
SourceID)
SELECT CollectionID, LastCollection, Notes, SourceID
FROM [xxx.xxx.xxx.xxx].Collection1.dbo.tblCollection
END
END
END
END
#######
Any help is appreciated. Thanks.
Message posted via http://www.webservertalk.comIf you get more than one result back from the query you should go for the IN
Clause rather than the EQUAL, but that wont work for you here because you
are going to apply the result set to a variable, so i try to reconstruct
your procodure, without able to test it anyway:
Perhaps you should go with this exmaple just as a suggestion, dont know if
the logical is the right one:
ALTER PROCEDURE dbo.usp_CollectionUpdate
AS
BEGIN
DECLARE @.serverUP varchar(100)
SET @.serverUP = ( SELECT SRVNAME
FROM [xxx.xxx.xxx.xxx].master.dbo.sysservers )
BEGIN
-- is the remote server available
IF LEN(@.serverUP) > 0
DECLARE @.getCount int
SET @.getCount = ( SELECT COUNT(CollectionID)
FROM [xxx.xxx.xxx.xxx].Collection1.dbo.tblCollection
WHERE (DATEDIFF(d, LastCollection, GetDate()) = 1) )
BEGIN
-- are there rows to update
IF @.getCount > 0
DECLARE @.remoteID int
SET @.remoteID = ( SELECT CollectionID
FROM [xxx.xxx.xxx.xxx].Collection1.dbo.tblCollection
WHERE (DATEDIFF(d, LastCollection, GetDate()) = 1) )
BEGIN
UPDATE dbo.tblCollection
SET LastCollection = t2.LastCollection, Notes = t2.Notes
FROM [xxx.xxx.xxx.xxx].Collection1.dbo.tblCollection t2
INNER JOIN
( SELECT CollectionID
FROM [xxx.xxx.xxx.xxx].Collection1.dbo.tblCollection
WHERE (DATEDIFF(d, LastCollection, GetDate()) = 1) )
tSubquery
ON tSubquery.CollectionID = dbo.tblCollection.CollectionID
-- no match so insert a new one
SET IDENTITY_INSERT dbo.tblCollection ON
INSERT INTO dbo.tblCollection (CollectionID, LastCollection, Notes,
SourceID)
SELECT tc1.CollectionID, tc1.LastCollection, tc1.Notes, tc1.SourceID
FROM [xxx.xxx.xxx.xxx].Collection1.dbo.tblCollection tc1
LEFT JOIN dbo.tblCollection tc
ON tc.CollectionID = tc1.CollectionID
Where tc.CollectionID IS NULL
END
END
END
END
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Hi
@.serverUP could also be affected as linked servers get placed in sysservers
too. That table can have 1 or more rows.
If you wanted to keep the code simple, having cursors might be the answer,
look at DECLARE CURSOR in BOL for @.serverUP and @.remoteID.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"The Gekkster via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in
message news:d9a70799ec74474f8a11040b58bece66@.SQ
webservertalk.com...
> Hi guys,
> I'm getting an error of 'subquery returned more than 1 value' when I try
> to
> run this, which I guess is because the @.remoteID is being set to a SELECT
> that returns multiple rows. How do I go about this so that I can
> accomplish
> checking for all existing IDs on the local server (against the remote
> server); updating each matching ID; or else inserting a new one?
> I think I've got the overall concept right, at least in terms of the
> updates/inserts, but need to clarify for SQL Server the exact row to be
> involved in any update. Then again, since I'm still trying to learn I
> could
> also be way off...
> CollectionID (identity) is the primary key in the table.
> #######
> ALTER PROCEDURE dbo.usp_CollectionUpdate
> AS
> BEGIN
> DECLARE @.serverUP varchar(100)
> SET @.serverUP = ( SELECT SRVNAME
> FROM [xxx.xxx.xxx.xxx].master.dbo.sysservers )
> BEGIN
> -- is the remote server available
> IF LEN(@.serverUP) > 0
> DECLARE @.getCount int
> SET @.getCount = ( SELECT COUNT(CollectionID)
> FROM [xxx.xxx.xxx.xxx].Collection1.dbo.tblCollection
> WHERE (DATEDIFF(d, LastCollection, GetDate()) = 1) )
> BEGIN
> -- are there rows to update
> IF @.getCount > 0
> DECLARE @.remoteID int
> SET @.remoteID = ( SELECT CollectionID
> FROM
> [xxx.xxx.xxx.xxx].Collection1.dbo.tblCollection
> WHERE (DATEDIFF(d, LastCollection, GetDate()) =
> 1) )
> BEGIN
> -- match found so update the id
> IF @.remoteID IN ( SELECT CollectionID
> FROM dbo.tblCollection )
> UPDATE dbo.tblCollection
> SET LastCollection = t2.LastCollection, Notes = t2.Notes
> FROM [xxx.xxx.xxx.xxx].Collection1.dbo.tblCollection t2
> WHERE (dbo.tblCollection.CollectionID = @.remoteID)
> -- no match so insert a new one
> ELSE
> SET IDENTITY_INSERT dbo.tblCollection ON
> INSERT INTO dbo.tblCollection (CollectionID, LastCollection, Notes,
> SourceID)
> SELECT CollectionID, LastCollection, Notes, SourceID
> FROM [xxx.xxx.xxx.xxx].Collection1.dbo.tblCollection
> END
> END
> END
> END
> #######
> Any help is appreciated. Thanks.
> --
> Message posted via http://www.webservertalk.com|||From reading your SP, I have several comments
1) your indenting implies that you want the whole set of statements after
the IF @.getCount > 0 to run only if @.GetCount > 0... But the way you wrote i
t
the only statement that will be conditionally executed is the declare ...
Everything aftre that will run whatever @.getCount is. IS that what you want
?
If you want an entire block of multiple statements, to run conditionally -
based on an If statement, then the entire block must be placed in a Begin /
End construction, and must immediately follow the IF.
DECLARE @.remoteID int
SET @.remoteID = ( SELECT CollectionID
FROM [xxx.xxx.xxx.xxx].Collection1.dbo.tblCollection
WHERE (DATEDIFF(d, LastCollection, GetDate()) = 1) )
BEGIN
-- match found so update the id
IF @.remoteID IN ( SELECT CollectionID
FROM dbo.tblCollection )
UPDATE dbo.tblCollection
SET LastCollection = t2.LastCollection, Notes = t2.Notes
FROM [xxx.xxx.xxx.xxx].Collection1.dbo.tblCollection t2
WHERE (dbo.tblCollection.CollectionID = @.remoteID)
-- no match so insert a new one
ELSE
SET IDENTITY_INSERT dbo.tblCollection ON
INSERT INTO dbo.tblCollection
(CollectionID, LastCollection, Notes,SourceID)
SELECT CollectionID, LastCollection, Notes, SourceID
FROM [xxx.xxx.xxx.xxx].Collection1.dbo.tblCollection
END
2. Next, WHy are you calculating and storing the value of @.ServerUP? The
only thing you are using it for is to test whether the SRVName attribute in
theremote table is zerilength ... You can do that without storing teh value.
using One of the following:
If (Select Len(SRVNAME)
FROM [xxx.xxx.xxx.xxx].master.dbo.sysservers ) > 0
-- (If you really want to check the len of the data in an existing row)
or,
If Exists (Select * From [xxx.xxx.xxx.xxx].master.dbo.sysservers )
-- If you really only need to check if there's row there at all....
3. Same issue with detecting if there are any records with
(DateDiff(d, LastCollection, GetDate()) = 1 IF @.getCount > 0 You don't
need to store the value of this in a variable... It looks lije you're just
trying to alternatly run an Insert for rows not already in the destination,m
and an Update fr th ones tha tare... So, Just do that. Run both the Update -
on the rows which are already in Destination, - and the Insert for the rows
that are not, (in that order).
Anyway, assuming that in item 2 you need to check the length.. your stored
Proc could be rewritten as:
-- ****************************************
ALTER PROCEDURE dbo.usp_CollectionUpdate
AS
Set NoCount On
If (Select Len(SvrName)
From [xxx.xxx.xxx.xxx].master.dbo.sysserversIf) > 0
Begin
Update dbo.tblCollection Set
LastCollection = T2.LastCollection,
Notes = T2.Notes
From [xxx.xxx.xxx.xxx].Collection1.dbo.tblCollection T2
Join dbo.tblCollection C
On C.CollectionID = T2.CollectionID
Where DATEDIFF(d, T2.LastCollection, GetDate()) = 1
-- ----
SET IDENTITY_INSERT dbo.tblCollection ON
Insert dbo.tblCollection (CollectionID, LastCollection,
Notes,SourceID)
Select CollectionID, LastCollection, Notes, SourceID
From [xxx.xxx.xxx.xxx].Collection1.dbo.tblCollection T
-- And Don't you need to restrict Insert to rows not already in
there '
Where Not Exists (Select * From dbo.tblCollection
Where CollectionID - T.CollectionID)
SET IDENTITY_INSERT dbo.tblCollection Off -- Got to set it back !!!
End|||Thanks...
This certainly seems much easier; and thanks for pointing out my errors -
this helps me understand the 'why' as I go along. So hopefully I don't make
the same mistakes going forward.
You guys (ALL) are awesome - an invaluable resource for those like me who
are just getting started with SQL Server.
Message posted via http://www.webservertalk.com

Problem whit "." or "," in field MONEY

Hi guys...

UPDATE PRODUCT SET PRICE='1,11' WHERE COD='001'

but, in field "PRICE" = 111,00

I don′t need "." but i need to use ","

UPDATE PRODUCT SET PRICE='1.11' WHERE COD='001'

Works perfectly... but all sql commands in applications use ","

In Server

User′s set LANGUAGE=PORTUGUESES

DEFAULT LANGUAGE = PORTUGUESES

Tks All

If i'm correct then you are using money or decimal for price.

use varchar for price and use CHAR(44) for ',' and format the string.

Problem When using Recordset paging

Hi, guys,

I got a problem when trying to paging the recordset. the problem is
even I set the pagesize but the first page will always show all the
records and the number of records that shown on the second page will
always all records number minus pagesize. for example, if I have total
5 records and pagesize = 2, then first page will show 5 record and
second one will show 3 and the last page will show the last one.

any one have some idea about that condition?

Thanks a lot!Li (zlusc@.hotmail.com) writes:
> I got a problem when trying to paging the recordset. the problem is
> even I set the pagesize but the first page will always show all the
> records and the number of records that shown on the second page will
> always all records number minus pagesize. for example, if I have total
> 5 records and pagesize = 2, then first page will show 5 record and
> second one will show 3 and the last page will show the last one.
> any one have some idea about that condition?

I guess you are using paging in ADO, but there are so many ways to do
paging, that rather I would like to know. Maybe you have some code to
share?

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

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

problem when try to config replication

Hi guys, i m just started to learn replication (and new to sql server as well). and got a problem when trying out instructions from MSDN, when i try to start new publication wizard. the following error message comes up. could anyone tell me what's going on?

===================================

Cannot connect to NATURE.

===================================

SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, 'ComputerFans'. (Replication.Utilities)

Program Location:

in Microsoft.SqlServer.Management.UI.ReplicationSqlConnection.CheckServerAlias(ServerConnection conn)
in Microsoft.SqlServer.Management.UI.ReplicationSqlConnection.ValidateConnection(UIConnectionInfo connInfo, IServerType server)
in Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

is it some thing to do with my installation of sql server? btw, i m using sql server 2005

thanks a lot
In Management Studio, have you registered the server using "." or "local", if yes please specify the actual instance name ComputerFans

HTH
Vishal|||

thanks for ur reply.

it seems like its the problem with DB instance name, coz i tried one of my named instance of db engine, it works....

|||

You are partly correct. The problem is the SQL server name does not equal the Windows server name.Do a ‘select * from sysservers’ against the Master Db.The SVRName should be ServerName\InstanceName.If it is not, you’ll need to do an ‘SP_dropserver” followed by a ‘SP_addserver’ to correct it.Check the BOL for the proper parameters before running the SPs.

Odie.

problem when try to config replication

Hi guys, i m just started to learn replication (and new to sql server as well). and got a problem when trying out instructions from MSDN, when i try to start new publication wizard. the following error message comes up. could anyone tell me what's going on?

===================================

Cannot connect to NATURE.

===================================

SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, 'ComputerFans'. (Replication.Utilities)

Program Location:

in Microsoft.SqlServer.Management.UI.ReplicationSqlConnection.CheckServerAlias(ServerConnection conn)
in Microsoft.SqlServer.Management.UI.ReplicationSqlConnection.ValidateConnection(UIConnectionInfo connInfo, IServerType server)
in Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

is it some thing to do with my installation of sql server? btw, i m using sql server 2005

thanks a lot
In Management Studio, have you registered the server using "." or "local", if yes please specify the actual instance name ComputerFans

HTH
Vishal|||

thanks for ur reply.

it seems like its the problem with DB instance name, coz i tried one of my named instance of db engine, it works....

|||

You are partly correct. The problem is the SQL server name does not equal the Windows server name.Do a ‘select * from sysservers’ against the Master Db.The SVRName should be ServerName\InstanceName.If it is not, you’ll need to do an ‘SP_dropserver” followed by a ‘SP_addserver’ to correct it.Check the BOL for the proper parameters before running the SPs.

Odie.

Problem viewing the history of the job I ran manually

Hey guys. I've a job thta I ran manually and when i tried to look for the history, it doesn't show anything. How would I see why it failed. I also tried to look at the sysjobhistory table and nothing there. The thing is, if the agent runs the job, it shows it there but if I run it manually, it doesn't. I know that's not the case in SQL 2000.

Please advice, how to check that. Thank you.

Tej

As explicitly stated in book online,

"Contains information about the execution of scheduled jobs by SQL Server Agent. This table is stored in the msdb database."

So, all sql job execution is logged into sysjobhistory. How did you invoke the job manually? Do make sure you haven't cleared the history info.

If need to, run sql profiler and see if the job is started and if history is written to sysjobhistory table.|||

Hey oj,

What I meant by manual execution is that I right clicked the job and ran it manually. When I do that and go to the Job activity Monitor, it would go to Step 0, which I think is the preparation phase and before even going to step one it finishes. And it doesn't get logged. I've captured the SP:Completed,SP:Starting,StmtCompleted,SP:StmtStarting,SQL:BatchCompleted,SQL:BatchStarting.

I see a call so sp_help_jobstep sp and then it calls the sp_sqlagent_log_jobhistory to log the failure.

I dont see it actually trying to run the jobstep.It's still failing. I had to restart the agent. When I did that, it started logging stuff but the problem is that, this job calls a SSIS package to be executed. It fails saying 'The package execution failed'. When I run the package with my account, it runs fine. When I right click the job and run the package, it runs as SQL Server service account which is also a local admin on the box. And it fails. What would the next step be in order to figure out where the problem is?

Thank you

Tej

|||

HI

I faced this type of issue with job history view. when we tried to view the job history, it runs some scripts in back ground(we can capture thru profile), there microsoft has defined servername variable as 30, but my server name has more than 30 charecters length. I fixed this issue by reducing the server name to 30 charecters.

my solution may help u if ur server name has more length than 30.

Thanks

kiran

|||Hi Tej,

Sorry for the late reply.

Look like you're having permission/security problem. Please check out the following for some guidance.

Sqlagent security:
http://msdn2.microsoft.com/en-us/library/ms190926.aspx

Creating sqlagent proxies:
http://msdn2.microsoft.com/en-us/library/ms189064.aspx

Problem viewing the history of the job I ran manually

Hey guys. I've a job thta I ran manually and when i tried to look for the history, it doesn't show anything. How would I see why it failed. I also tried to look at the sysjobhistory table and nothing there. The thing is, if the agent runs the job, it shows it there but if I run it manually, it doesn't. I know that's not the case in SQL 2000.

Please advice, how to check that. Thank you.

Tej

As explicitly stated in book online,

"Contains information about the execution of scheduled jobs by SQL Server Agent.

This table is stored in the msdb database."

So, all sql job execution is logged into sysjobhistory. How did you invoke the job manually? Do make sure you haven't cleared the history info.

If need to, run sql profiler and see if the job is started and if history is written to sysjobhistory table.|||

Hey oj,

What I meant by manual execution is that I right clicked the job and ran it manually. When I do that and go to the Job activity Monitor, it would go to Step 0, which I think is the preparation phase and before even going to step one it finishes. And it doesn't get logged. I've captured the SP:Completed,SP:Starting,StmtCompleted,SP:StmtStarting,SQL:BatchCompleted,SQL:BatchStarting.

I see a call so sp_help_jobstep sp and then it calls the sp_sqlagent_log_jobhistory to log the failure.

I dont see it actually trying to run the jobstep.It's still failing. I had to restart the agent. When I did that, it started logging stuff but the problem is that, this job calls a SSIS package to be executed. It fails saying 'The package execution failed'. When I run the package with my account, it runs fine. When I right click the job and run the package, it runs as SQL Server service account which is also a local admin on the box. And it fails. What would the next step be in order to figure out where the problem is?

Thank you

Tej

|||

HI

I faced this type of issue with job history view. when we tried to view the job history, it runs some scripts in back ground(we can capture thru profile), there microsoft has defined servername variable as 30, but my server name has more than 30 charecters length. I fixed this issue by reducing the server name to 30 charecters.

my solution may help u if ur server name has more length than 30.

Thanks

kiran

|||Hi Tej,

Sorry for the late reply.

Look like you're having permission/security problem. Please check out the following for some guidance.

Sqlagent security:
http://msdn2.microsoft.com/en-us/library/ms190926.aspx

Creating sqlagent proxies:
http://msdn2.microsoft.com/en-us/library/ms189064.aspx

Problem viewing the history of the job I ran manually

Hey guys. I've a job thta I ran manually and when i tried to look for the history, it doesn't show anything. How would I see why it failed. I also tried to look at the sysjobhistory table and nothing there. The thing is, if the agent runs the job, it shows it there but if I run it manually, it doesn't. I know that's not the case in SQL 2000.

Please advice, how to check that. Thank you.

Tej

As explicitly stated in book online,

"Contains information about the execution of scheduled jobs by SQL Server Agent.

This table is stored in the msdb database."

So, all sql job execution is logged into sysjobhistory. How did you invoke the job manually? Do make sure you haven't cleared the history info.

If need to, run sql profiler and see if the job is started and if history is written to sysjobhistory table.|||

Hey oj,

What I meant by manual execution is that I right clicked the job and ran it manually. When I do that and go to the Job activity Monitor, it would go to Step 0, which I think is the preparation phase and before even going to step one it finishes. And it doesn't get logged. I've captured the SP:Completed,SP:Starting,StmtCompleted,SP:StmtStarting,SQL:BatchCompleted,SQL:BatchStarting.

I see a call so sp_help_jobstep sp and then it calls the sp_sqlagent_log_jobhistory to log the failure.

I dont see it actually trying to run the jobstep.It's still failing. I had to restart the agent. When I did that, it started logging stuff but the problem is that, this job calls a SSIS package to be executed. It fails saying 'The package execution failed'. When I run the package with my account, it runs fine. When I right click the job and run the package, it runs as SQL Server service account which is also a local admin on the box. And it fails. What would the next step be in order to figure out where the problem is?

Thank you

Tej

|||

HI

I faced this type of issue with job history view. when we tried to view the job history, it runs some scripts in back ground(we can capture thru profile), there microsoft has defined servername variable as 30, but my server name has more than 30 charecters length. I fixed this issue by reducing the server name to 30 charecters.

my solution may help u if ur server name has more length than 30.

Thanks

kiran

|||Hi Tej,

Sorry for the late reply.

Look like you're having permission/security problem. Please check out the following for some guidance.

Sqlagent security:
http://msdn2.microsoft.com/en-us/library/ms190926.aspx

Creating sqlagent proxies:
http://msdn2.microsoft.com/en-us/library/ms189064.aspx

Problem variable initialization

Guys I noticed that the value I set on one of the package variables is not carried over during the execution. I have a package variable of type String which I use to hold the string for a parameter in a stored procedure. My stored procedure has one output parameter which is the id of the record fetched using the string parameter. These are my parameters
1) User::vendorId Int32 Initial value = -1
2) User::vendorName String Initial value = Vendor A

I make the assignment in the Variable pane. Are there any other ways of doing this?

Watching the two variables reveals that only the value for the first variable is carried over during execution. The vendorname is always blank --> {}. I'm using a SQL Task with OLEDBConnection.

What's going on here?

Carried over during execution? What do you mean?

Is this a real problem or an issue with the diagnosis method. When watching variables, you must be on a breakpoint, and you must examine them in the watch window. Looking at the variables pane or looking when not on a breakpoint may not give you the correct value, as only the watch window at a breakpoint is updated during execution. Is a bit fussy, as you can see the value in several other windows, but they just do not get updated.

|||When you create a variable and assign it a default, that value never changes. During execution it can change, but when the package is done all variables revert back to the defaults you entered when you created the package.|||"revert back to the defaults" - think of it this way, when you execute a package the execution host loads it, then executes it and then does not save it. The execution host and the designer are two different things, albeit with some communication for the debug features. This is a change to the DTS behaviour if that is in your background.|||Guys thanks so much for th replies. I'm still in the development phase obviously and I just want the flexibility of setting values to the package variables. For this, I use the Variable pane of Visual Studio where the last column - I think - is for the default value to be used on every run. I tried to use the Script task but I couldnt find my package variables. My vendorId which is initialized to -1 retains its value during the execution; at least that's what the Watch window is showing me. The other variable User::vendorName is always blank even if I set it to a certain string value prior to F5. My control flow doesnt have any event handler. Should you need more clarification guys, I'll do everything to make this clear.
|||Just to clarify, if you change the Value property for the variable in the Variables window, save, then close and reopen the package, does it have the value you set it to? If it doesn't, make sure that you don't have a configuration set that is overwriting the variable. If the value is correct, run the package and check the value in the watch window. If it is not correct there, either an expression or a script is changing the value at runtime.|||Thanks so much. Now I remember I've tinkered about package configuration. It didn't occur to me that explains it. It's nice to see you can toggle it on/off.

I created another configuration but I can't find it in the solution explorer or in the bin table? Is the configuration available only after deployment?
|||An XML configuration will be created at whatever path you specify when you create the configuration. If you open the package configurations dialog, you should see the path to the file.

problem using SQL Server 2005 database from C# application through

Hi all.
I dont know if it is right group but maybe you guys be able to show me
a right direction. And im sorry, if i crossposted this message to
several groups.
Ok ,the problem is following:
We have a large ATL COM dll, which is used as common interface for our
database and it is used by many different applications (like Notes, VB6
apps etc). We wrote it a few years ago using VC6++(if it matters) and
everything worked just fine. This spring we updated our developement
tools to VS2005 and i updated VC6 project to VS2005. After few days of
work i got it compiled. NOW the real problem.
Our new C# applications use same COM dll through interop dll and once
again, everything works just fine EXCEPT, C# applications cannot use
SQLServer 2005 database through interop dll!!! This is the only
combination which does not work (C# -> interop dll -> ATL-COM dll -> SQL
Server 2005).
All other tests passed normally, like:
C# app -> interop dll -> ATL-COM dll -> ms access db.
normal C++ app -> ATL-COM dll -> SQL Server 2005 db.
normal C++ app -> ATL-COM dll -> ms access db.
but this one does NOT!!!!
C# -> interop dll -> ATL-COM dll -> SQL Server 2005
Although database object is correctly created in C# app and even
connection to SQL Server 2005 established successfully, every call to
get data from database failes to exception (seems like the exception
type differs randomly, like ArgumentException etc.). Sometimes DLL COM
returns database error like "specified object does not exist").
Can you guys tell me whats going on!!!
Im running out of ideas cause EXACTLY same dll works with other
databases and other programs that C# can use SAME dll with SQL2005 and
no problems...
thanks
Asko.
Roger Wolter[MSFT] wrote:
> Definitely a long shot but C# strings are always Unicode and your VC6 dll
> might not be expecting unicode.
>
Thank you for your response.
Our ATL DLL is definitely unicode cause we have alot of international
data in db and thus it cannot be the cause to my problem.
Asko.