"Update" in Trigger not working

Hi,

I am trying to concatenate the columns (PrevEmp01, PrevEmp02, PrevEmp03, PrevEmp04, PrevEmp05) into column (ft) using trigger:

CREATE TRIGGER [tg_prevemp_ft_update] ON [tStaffDir_PrevEmp]
FOR INSERT, UPDATE
AS
UPDATE tStaffDir_PrevEmp SET ft = PrevEmp01 + ' ' + PrevEmp02 + ' ' + PrevEmp03 + ' ' + PrevEmp04 + ' ' + PrevEmp05

I would expect the (ft) column will be populated accordingly regardless if any of the columns are (Null).
But the Trigger will only work when all the 5 columns are populated. If one of the column is (Null), the (ft) column will be (Null) too.

Please advise. Many Thanks.

Try this:

CREATE TRIGGER [tg_prevemp_ft_update]ON [tStaffDir_PrevEmp]FOR INSERT,UPDATEASUPDATE tStaffDir_PrevEmpSET ft =ISNULL(PrevEmp01,'') +' ' +ISNULL(PrevEmp02,'') +' ' +ISNULL(PrevEmp03,'') +' ' +ISNULL(PrevEmp04,'') +' ' +ISNULL(PrevEmp05,'')

Good luck.

|||

Thanks! It worked.

"string or binary data would be truncated" error message

I am getting the message ("string or binary data would be truncated") when trying to update a field in a table in my database, but am unclear as to why. I understand that the error usually occurs when the length of a string exceeds the limit allowed for a field in the database, but in this situation, that constraint should not apply. The length of the field for which I am trying to make the update is a varchar field of length 100. The value for which I am attempting to update is an e-mail address of length 42 of the format xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx@.yahoo.com. The initial insertion of the value (via my website) succeeded in inserting the first 40 characters of the address, but cut off the "om" at the end. It doesn't seem to matter how large I make the field in the database as it always gives me this error when I try to make the update. I have tried to change the value in both Query Analyzer and Enterprise Manager, but I invariably receive the same error.

Any ideas?I just posted my best guesses of causes for this here:

http://forums.asp.net/thread/1366165.aspx

Hope it helps,

Joe|||Thanks, your tips actually did help. There was a trigger on the table I was trying to insert into and it was attempting to insert into a table where the email field only allowed for addresses as large as 40 characters.

"Simple" query help

Hello!
I would like some help to figure out how to write a specific update query.
What I'm trying to do:
I have one table with these columns:
no (primary key or clustered?)
object (primary key or clustered?)
cid
usergroup1
usergroup2
Lets say the table I would like to upgrade is called obj1 and the table I'm
getting the value to set from is called obj2.
This is in real the same table and it's called objectx.
I would like to set cid for the records with no=7 in obj1 to the value of
the records in obj2 with:
obj2.no=1,
obj2.usergroup2=obj1.object
the value should be set to obj2.usergroup1
The obj2.usergroup2 values could be found several times.
Any idea how to write this query?
Regards MagnusOn Fri, 9 Dec 2005 12:16:41 +0100, Magnus Blomberg wrote:

>Hello!
>I would like some help to figure out how to write a specific update query.
>What I'm trying to do:
>I have one table with these columns:
>no (primary key or clustered?)
>object (primary key or clustered?)
>cid
>usergroup1
>usergroup2
>Lets say the table I would like to upgrade is called obj1 and the table I'm
>getting the value to set from is called obj2.
>This is in real the same table and it's called objectx.
>I would like to set cid for the records with no=7 in obj1 to the value of
>the records in obj2 with:
>obj2.no=1,
>obj2.usergroup2=obj1.object
>the value should be set to obj2.usergroup1
>The obj2.usergroup2 values could be found several times.
>Any idea how to write this query?
>Regards Magnus
>
Hi Magnus,
Before writing the query, the specifications should be clear. You say
that obj2.usergroup2 can be found several times. That means that there
might be more than one obj2.usergroup1. Which one of these should be
used to set obj1.cid'
If you need the lowest value, try if this works:
UPDATE objectx
SET cid = (SELECT MIN(obj2.usergroup1)
FROM objectx AS obj2
WHERE obj2.no = 1
AND obj2.usergroup2 = objectx.object)
WHERE no = 7
(untested - if you prefer a tested reply or if this doesn;t work, then
please check www.aspfaq.com/5006 to find out how to provide clear specs
and test data).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

"Read Only" problem from Access 2003 to SQL Server

I am unable to add/update/delete data in the SQL Server database from Access
2003. I can connect just fine using either an ODBC link or via an ADO
connection in VBA. However, any time I try to change any SQL Server data (or
run a stored procedure) it either "times out" or gives me a message to the
effect that "...another user is attempting to change the data at the same
time...". Since I am the only one on the network, I know this is not the
real problem. I have SQL Profiler running in order to see what may be
happening when I try to run a stored procedure. The SQL Server doesn't get
the request at all. Access seems to be blocking any calls to stored
procedures and blocking any attempt to change data.
I can change data with no problem from within Enterprise Manager. The SQL
Server User (in the ADO connection) has db owner rights and rights to all
objects being accessed.
Here is the configuration: I am running Win 2K Pro with SQL Server 2000 on
PC-1 and Win XP Pro and Access 2003 on PC-2. I have an Access 2003 database
application on PC-2 that needs to modify the SQL data on PC-1. I have tried
Windows authentication and SQL Server Authentication (modifying the SQL
Server settings and ADO connection string settings as needed). Both ways of
connecting give the same result - read only.
When I installed Access 2003 I saw a message relating to security and Access
not allowing potentially destructive code to run. I took no action on the
message although I did remember seeing something about a necessary update to
2003 for the issue (I figured I'd revisit it later). This issue may be
related to the problem, but I don't know. There are no outstanding updates
available for Access 2003 from MS. Now I can't figure out what it was
talking about. I may uninstall the software and reinstall to see if I can
catch it again.
I have tried everything I can think of, including soliciting help from
peers, searching the knowledge databases and reading many posts. No luck.
It appears as though no one has had this particular issue come up. I tried
to ask Microsoft (using my one free question) but my (valid) Product ID was
rejected by their system.
Anyone out there have any experience with this one? Three days stuck on one
issue is getting old.
Thanks and regards,
--
Ken Wells
President
Smart Computing Solutions, LLCAll it took was a post. I have, for all intents and purposes, solved this
problem myself.
I broke down and rewrote the code (inherited from another developer), basing
it on code that I had done in the past. I can now execute a stored procedure
and update or add records in the SQL database from Access 2003 using an ADO
connection and SQL Server Authentication.
I still cannot directly modify ODBC-linked SQL table data from the database
window, but that is a minor consideration at this point.
"SmartComputing" wrote:
> I am unable to add/update/delete data in the SQL Server database from Access
> 2003. I can connect just fine using either an ODBC link or via an ADO
> connection in VBA. However, any time I try to change any SQL Server data (or
> run a stored procedure) it either "times out" or gives me a message to the
> effect that "...another user is attempting to change the data at the same
> time...". Since I am the only one on the network, I know this is not the
> real problem. I have SQL Profiler running in order to see what may be
> happening when I try to run a stored procedure. The SQL Server doesn't get
> the request at all. Access seems to be blocking any calls to stored
> procedures and blocking any attempt to change data.
> I can change data with no problem from within Enterprise Manager. The SQL
> Server User (in the ADO connection) has db owner rights and rights to all
> objects being accessed.
> Here is the configuration: I am running Win 2K Pro with SQL Server 2000 on
> PC-1 and Win XP Pro and Access 2003 on PC-2. I have an Access 2003 database
> application on PC-2 that needs to modify the SQL data on PC-1. I have tried
> Windows authentication and SQL Server Authentication (modifying the SQL
> Server settings and ADO connection string settings as needed). Both ways of
> connecting give the same result - read only.
> When I installed Access 2003 I saw a message relating to security and Access
> not allowing potentially destructive code to run. I took no action on the
> message although I did remember seeing something about a necessary update to
> 2003 for the issue (I figured I'd revisit it later). This issue may be
> related to the problem, but I don't know. There are no outstanding updates
> available for Access 2003 from MS. Now I can't figure out what it was
> talking about. I may uninstall the software and reinstall to see if I can
> catch it again.
> I have tried everything I can think of, including soliciting help from
> peers, searching the knowledge databases and reading many posts. No luck.
> It appears as though no one has had this particular issue come up. I tried
> to ask Microsoft (using my one free question) but my (valid) Product ID was
> rejected by their system.
> Anyone out there have any experience with this one? Three days stuck on one
> issue is getting old.
> Thanks and regards,
> --
> Ken Wells
> President
> Smart Computing Solutions, LLC|||"SmartComputing" <kennethwells@.NO_SPAM_yahoo.com> wrote in message
news:9F4BA364-B69B-4026-AD67-CF024DE7FEDD@.microsoft.com...
> All it took was a post. I have, for all intents and purposes, solved this
> problem myself.
> I broke down and rewrote the code (inherited from another developer),
> basing
> it on code that I had done in the past. I can now execute a stored
> procedure
> and update or add records in the SQL database from Access 2003 using an
> ADO
> connection and SQL Server Authentication.
> I still cannot directly modify ODBC-linked SQL table data from the
> database
> window, but that is a minor consideration at this point.
I reckon it's the connection parameters are most likely the problem.
Go into the linked table manager.
If you hover over the connected tables you should see a connection string in
there.
Whilst you're there, refresh the connections and check that doesn't help.
Check out your DSN or connection strings.
Oh...
Access wants unique keys to allow updates.
Looking at the table definition in access, you see a primary key on the
tables?
There's also joins can make a query not updateable but if nothing works this
seems unlikely.
--
Regards,
Andy O'Neill

"Read Only" problem from Access 2003 to SQL Server

I am unable to add/update/delete data in the SQL Server database from Access
2003. I can connect just fine using either an ODBC link or via an ADO
connection in VBA. However, any time I try to change any SQL Server data (or
run a stored procedure) it either "times out" or gives me a message to the
effect that "...another user is attempting to change the data at the same
time...". Since I am the only one on the network, I know this is not the
real problem. I have SQL Profiler running in order to see what may be
happening when I try to run a stored procedure. The SQL Server doesn't get
the request at all. Access seems to be blocking any calls to stored
procedures and blocking any attempt to change data.
I can change data with no problem from within Enterprise Manager. The SQL
Server User (in the ADO connection) has db owner rights and rights to all
objects being accessed.
Here is the configuration: I am running Win 2K Pro with SQL Server 2000 on
PC-1 and Win XP Pro and Access 2003 on PC-2. I have an Access 2003 database
application on PC-2 that needs to modify the SQL data on PC-1. I have tried
Windows authentication and SQL Server Authentication (modifying the SQL
Server settings and ADO connection string settings as needed). Both ways of
connecting give the same result - read only.
When I installed Access 2003 I saw a message relating to security and Access
not allowing potentially destructive code to run. I took no action on the
message although I did remember seeing something about a necessary update to
2003 for the issue (I figured I'd revisit it later). This issue may be
related to the problem, but I don't know. There are no outstanding updates
available for Access 2003 from MS. Now I can't figure out what it was
talking about. I may uninstall the software and reinstall to see if I can
catch it again.
I have tried everything I can think of, including soliciting help from
peers, searching the knowledge databases and reading many posts. No luck.
It appears as though no one has had this particular issue come up. I tried
to ask Microsoft (using my one free question) but my (valid) Product ID was
rejected by their system.
Anyone out there have any experience with this one? Three days stuck on one
issue is getting old.
Thanks and regards,
Ken Wells
President
Smart Computing Solutions, LLC
All it took was a post. I have, for all intents and purposes, solved this
problem myself.
I broke down and rewrote the code (inherited from another developer), basing
it on code that I had done in the past. I can now execute a stored procedure
and update or add records in the SQL database from Access 2003 using an ADO
connection and SQL Server Authentication.
I still cannot directly modify ODBC-linked SQL table data from the database
window, but that is a minor consideration at this point.
"SmartComputing" wrote:

> I am unable to add/update/delete data in the SQL Server database from Access
> 2003. I can connect just fine using either an ODBC link or via an ADO
> connection in VBA. However, any time I try to change any SQL Server data (or
> run a stored procedure) it either "times out" or gives me a message to the
> effect that "...another user is attempting to change the data at the same
> time...". Since I am the only one on the network, I know this is not the
> real problem. I have SQL Profiler running in order to see what may be
> happening when I try to run a stored procedure. The SQL Server doesn't get
> the request at all. Access seems to be blocking any calls to stored
> procedures and blocking any attempt to change data.
> I can change data with no problem from within Enterprise Manager. The SQL
> Server User (in the ADO connection) has db owner rights and rights to all
> objects being accessed.
> Here is the configuration: I am running Win 2K Pro with SQL Server 2000 on
> PC-1 and Win XP Pro and Access 2003 on PC-2. I have an Access 2003 database
> application on PC-2 that needs to modify the SQL data on PC-1. I have tried
> Windows authentication and SQL Server Authentication (modifying the SQL
> Server settings and ADO connection string settings as needed). Both ways of
> connecting give the same result - read only.
> When I installed Access 2003 I saw a message relating to security and Access
> not allowing potentially destructive code to run. I took no action on the
> message although I did remember seeing something about a necessary update to
> 2003 for the issue (I figured I'd revisit it later). This issue may be
> related to the problem, but I don't know. There are no outstanding updates
> available for Access 2003 from MS. Now I can't figure out what it was
> talking about. I may uninstall the software and reinstall to see if I can
> catch it again.
> I have tried everything I can think of, including soliciting help from
> peers, searching the knowledge databases and reading many posts. No luck.
> It appears as though no one has had this particular issue come up. I tried
> to ask Microsoft (using my one free question) but my (valid) Product ID was
> rejected by their system.
> Anyone out there have any experience with this one? Three days stuck on one
> issue is getting old.
> Thanks and regards,
> --
> Ken Wells
> President
> Smart Computing Solutions, LLC
|||"SmartComputing" <kennethwells@.NO_SPAM_yahoo.com> wrote in message
news:9F4BA364-B69B-4026-AD67-CF024DE7FEDD@.microsoft.com...
> All it took was a post. I have, for all intents and purposes, solved this
> problem myself.
> I broke down and rewrote the code (inherited from another developer),
> basing
> it on code that I had done in the past. I can now execute a stored
> procedure
> and update or add records in the SQL database from Access 2003 using an
> ADO
> connection and SQL Server Authentication.
> I still cannot directly modify ODBC-linked SQL table data from the
> database
> window, but that is a minor consideration at this point.
I reckon it's the connection parameters are most likely the problem.
Go into the linked table manager.
If you hover over the connected tables you should see a connection string in
there.
Whilst you're there, refresh the connections and check that doesn't help.
Check out your DSN or connection strings.
Oh...
Access wants unique keys to allow updates.
Looking at the table definition in access, you see a primary key on the
tables?
There's also joins can make a query not updateable but if nothing works this
seems unlikely.
Regards,
Andy O'Neill

"Read Only" problem from Access 2003 to SQL Server

I am unable to add/update/delete data in the SQL Server database from Access
2003. I can connect just fine using either an ODBC link or via an ADO
connection in VBA. However, any time I try to change any SQL Server data (o
r
run a stored procedure) it either "times out" or gives me a message to the
effect that "...another user is attempting to change the data at the same
time...". Since I am the only one on the network, I know this is not the
real problem. I have SQL Profiler running in order to see what may be
happening when I try to run a stored procedure. The SQL Server doesn't get
the request at all. Access seems to be blocking any calls to stored
procedures and blocking any attempt to change data.
I can change data with no problem from within Enterprise Manager. The SQL
Server User (in the ADO connection) has db owner rights and rights to all
objects being accessed.
Here is the configuration: I am running Win 2K Pro with SQL Server 2000 on
PC-1 and Win XP Pro and Access 2003 on PC-2. I have an Access 2003 database
application on PC-2 that needs to modify the SQL data on PC-1. I have tried
Windows authentication and SQL Server Authentication (modifying the SQL
Server settings and ADO connection string settings as needed). Both ways of
connecting give the same result - read only.
When I installed Access 2003 I saw a message relating to security and Access
not allowing potentially destructive code to run. I took no action on the
message although I did remember seeing something about a necessary update to
2003 for the issue (I figured I'd revisit it later). This issue may be
related to the problem, but I don't know. There are no outstanding updates
available for Access 2003 from MS. Now I can't figure out what it was
talking about. I may uninstall the software and reinstall to see if I can
catch it again.
I have tried everything I can think of, including soliciting help from
peers, searching the knowledge databases and reading many posts. No luck.
It appears as though no one has had this particular issue come up. I tried
to ask Microsoft (using my one free question) but my (valid) Product ID was
rejected by their system.
Anyone out there have any experience with this one? Three days stuck on one
issue is getting old.
Thanks and regards,
--
Ken Wells
President
Smart Computing Solutions, LLCAll it took was a post. I have, for all intents and purposes, solved this
problem myself.
I broke down and rewrote the code (inherited from another developer), basing
it on code that I had done in the past. I can now execute a stored procedur
e
and update or add records in the SQL database from Access 2003 using an ADO
connection and SQL Server Authentication.
I still cannot directly modify ODBC-linked SQL table data from the database
window, but that is a minor consideration at this point.
"SmartComputing" wrote:

> I am unable to add/update/delete data in the SQL Server database from Acce
ss
> 2003. I can connect just fine using either an ODBC link or via an ADO
> connection in VBA. However, any time I try to change any SQL Server data
(or
> run a stored procedure) it either "times out" or gives me a message to the
> effect that "...another user is attempting to change the data at the same
> time...". Since I am the only one on the network, I know this is not the
> real problem. I have SQL Profiler running in order to see what may be
> happening when I try to run a stored procedure. The SQL Server doesn't ge
t
> the request at all. Access seems to be blocking any calls to stored
> procedures and blocking any attempt to change data.
> I can change data with no problem from within Enterprise Manager. The SQL
> Server User (in the ADO connection) has db owner rights and rights to all
> objects being accessed.
> Here is the configuration: I am running Win 2K Pro with SQL Server 2000 o
n
> PC-1 and Win XP Pro and Access 2003 on PC-2. I have an Access 2003 databa
se
> application on PC-2 that needs to modify the SQL data on PC-1. I have tri
ed
> Windows authentication and SQL Server Authentication (modifying the SQL
> Server settings and ADO connection string settings as needed). Both ways
of
> connecting give the same result - read only.
> When I installed Access 2003 I saw a message relating to security and Acce
ss
> not allowing potentially destructive code to run. I took no action on the
> message although I did remember seeing something about a necessary update
to
> 2003 for the issue (I figured I'd revisit it later). This issue may be
> related to the problem, but I don't know. There are no outstanding update
s
> available for Access 2003 from MS. Now I can't figure out what it was
> talking about. I may uninstall the software and reinstall to see if I can
> catch it again.
> I have tried everything I can think of, including soliciting help from
> peers, searching the knowledge databases and reading many posts. No luck.
> It appears as though no one has had this particular issue come up. I trie
d
> to ask Microsoft (using my one free question) but my (valid) Product ID wa
s
> rejected by their system.
> Anyone out there have any experience with this one? Three days stuck on o
ne
> issue is getting old.
> Thanks and regards,
> --
> Ken Wells
> President
> Smart Computing Solutions, LLC|||"SmartComputing" <kennethwells@.NO_SPAM_yahoo.com> wrote in message
news:9F4BA364-B69B-4026-AD67-CF024DE7FEDD@.microsoft.com...
> All it took was a post. I have, for all intents and purposes, solved this
> problem myself.
> I broke down and rewrote the code (inherited from another developer),
> basing
> it on code that I had done in the past. I can now execute a stored
> procedure
> and update or add records in the SQL database from Access 2003 using an
> ADO
> connection and SQL Server Authentication.
> I still cannot directly modify ODBC-linked SQL table data from the
> database
> window, but that is a minor consideration at this point.
I reckon it's the connection parameters are most likely the problem.
Go into the linked table manager.
If you hover over the connected tables you should see a connection string in
there.
Whilst you're there, refresh the connections and check that doesn't help.
Check out your DSN or connection strings.
Oh...
Access wants unique keys to allow updates.
Looking at the table definition in access, you see a primary key on the
tables?
There's also joins can make a query not updateable but if nothing works this
seems unlikely.
Regards,
Andy O'Neill

"Multiple-Step operation cannot be generated Check each status value" Error

Hi All,

I have a field 'Rowguid' of type uniqueidentifier in a table. This field is the last field in the table. In this case if I update a record through the application I don't get any error. Suppose if there are additional fields after the field Rowguid I get the error "Multiple-Step operation cannot be generated Check each status value"

For your reference I have used the following statement to add the RowGuid field

Alter table <tablename>
Add RowGuid uniqueidentifier ROWGUIDCOL NOT NULL Default (newid())

Can anyone please help me.

Thanks

SatheshPost your update statement, and any trigger code on this table.|||Where's Brett when we need him?

Logically, columns don't have any order within a table. It so happens that the current implementation of Microsoft SQL Server has a physical order, but that's implementation dependant. Relying on a column order (logical or physical) is a bug waiting to happen!

The problem is that you are not specifying a column list for your INSERT statement, you are relying on an implied order. Once you add columns so that the rowid is no longer the last column, your assumed order bytes you. If you specify the columns via a column list, this won't be a problem.DROP TABLE BadExample
GO
CREATE TABLE BadExample (
comment NVARCHAR(25)
)
GO

INSERT INTO BadExample
SELECT 'Simple case'

SELECT * FROM BadExample
GO

ALTER TABLE BadExample
ADD RowGuid uniqueidentifier ROWGUIDCOL NOT NULL
DEFAULT (newid())

INSERT INTO BadExample (comment)
SELECT 'Not as simple example'

SELECT * FROM BadExample
GO

ALTER TABLE BadExample
ADD myDate DATETIME NOT NULL
DEFAULT GetDate()
GO

INSERT INTO BadExample (comment, myDate)
SELECT 'This works!', '1950-01-02'

SELECT * FROM BadExample
GO

INSERT INTO BadExample
SELECT 'This fails', '1960-03-04'

SELECT * FROM BadExample-PatP|||The problem is that you are not specifying a column list for your INSERT statement, you are relying on an implied order.How did you come up with that from his post!?

"Key Column Information...."

Hi everybody!
I have the following problem: I have many tables in my database and for
some columns it happens that when I try to update them, I get the error
message, that the update doesn't work properly, because of "Key column
information insufficient or incorrect". This happens only for a few
columns in the table.
Example:
Table "tabProductColorPackage"
ID (INT, AutoSeed, Pkey)
Number (DECIMAL(18,2))
Unit (DECIMAL(18,2))
My VB-Code:
Recordset.Open "SELECT Unit FROM tabProductColorPackage WHERE ID=2"
Recordset.Fields("Unit") = 1
Recordset.Update
--> Works fine!
Recordset.Open "SELECT Number FROM tabProductColorPackage WHERE ID=2"
Recordset.Fields("Unit") = 1
Recordset.Update
--> Produces the error given in my description.
What the hell is going wrong here?
Thanks in advance,
BerndHi!
Sorry, the VB-Code that produces the error is as follows:
> Recordset.Open "SELECT Number FROM tabProductColorPackage WHERE ID=2"
> Recordset.Fields("Number") = 1
> Recordset.Update
Thanks,
Bernd

"Key Column Information...."

Hi everybody!
I have the following problem: I have many tables in my database and for
some columns it happens that when I try to update them, I get the error
message, that the update doesn't work properly, because of "Key column
information insufficient or incorrect". This happens only for a few
columns in the table.
Example:
Table "tabProductColorPackage"
ID (INT, AutoSeed, Pkey)
Number (DECIMAL(18,2))
Unit (DECIMAL(18,2))
My VB-Code:
Recordset.Open "SELECT Unit FROM tabProductColorPackage WHERE ID=2"
Recordset.Fields("Unit") = 1
Recordset.Update
--> Works fine!
Recordset.Open "SELECT Number FROM tabProductColorPackage WHERE ID=2"
Recordset.Fields("Unit") = 1
Recordset.Update
--> Produces the error given in my description.
What the hell is going wrong here?
Thanks in advance,
BerndHi!
Sorry, the VB-Code that produces the error is as follows:

> Recordset.Open "SELECT Number FROM tabProductColorPackage WHERE ID=2"
> Recordset.Fields("Number") = 1
> Recordset.Update
Thanks,
Bernd

"insert on existing update" in MS SQL Server?

Hello

I used to work in a Sybase database environment. When I had to insert/
update records in the database, I always used "insert on existing
update", in this way, you didn't have to check whether a record
already existed (avoid errors) and you were always sure that after
running the scripts, the last version was in the database.

Now I'm looking for the same functionality in MS SQL Server, asked a
few people, but nobody knows about such an option.
Does anybody here knows the SQL Server counterpart of "insert on
existing skip/update"? If this doesn't exist, this is a minus for
MS ;).

Greetz,

BartBart op de grote markt wrote:

Quote:

Originally Posted by

Hello
>
I used to work in a Sybase database environment. When I had to insert/
update records in the database, I always used "insert on existing
update", in this way, you didn't have to check whether a record
already existed (avoid errors) and you were always sure that after
running the scripts, the last version was in the database.
>
Now I'm looking for the same functionality in MS SQL Server, asked a
few people, but nobody knows about such an option.
Does anybody here knows the SQL Server counterpart of "insert on
existing skip/update"? If this doesn't exist, this is a minus for
MS ;).

In ANSI/SQL it's called a MERGE statement.
In SQL Server I'd do an UPDATE FROM, if no row found follow up with an
INSERT.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab|||Bart op de grote markt (bartwarnez@.freegates.be) writes:

Quote:

Originally Posted by

I used to work in a Sybase database environment. When I had to insert/
update records in the database, I always used "insert on existing
update", in this way, you didn't have to check whether a record
already existed (avoid errors) and you were always sure that after
running the scripts, the last version was in the database.
>
Now I'm looking for the same functionality in MS SQL Server, asked a
few people, but nobody knows about such an option.
Does anybody here knows the SQL Server counterpart of "insert on
existing skip/update"? If this doesn't exist, this is a minus for
MS ;).

I'm afraid that you will have to chalk up one minus for MS SQL Server.

You will have to do:

UPDATE ...

INSERT ...
SELECT ...
WHERE NOT EXISTS (...)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Ok, thank you all for the replies! It's a pity but well :-). If MS
reads this post: hello MS, you know what to do next.

Grtz,

Bart|||On Feb 6, 7:28 am, "Bart op de grote markt" <bartwar...@.freegates.be>
wrote:

Quote:

Originally Posted by

Hello
>
I used to work in a Sybase database environment. When I had to insert/
update records in the database, I always used "insert on existing
update", in this way, you didn't have to check whether a record
already existed (avoid errors) and you were always sure that after
running the scripts, the last version was in the database.
>
Now I'm looking for the same functionality in MS SQL Server, asked a
few people, but nobody knows about such an option.
Does anybody here knows the SQL Server counterpart of "insert on
existing skip/update"? If this doesn't exist, this is a minus for
MS ;).
>
Greetz,
>
Bart

Also look up "Mimicking MERGE Statement in SQL Server 2005 ":

http://sqlserver-tips.blogspot.com/...ent-in-sql.html

"GO" statement in SQK2K

Hi,
I have been using a lot of "GO" statement in my SQL
script (say a file called abc.sql) with SQL7.0
For Example :
--
Select * from A
GO
Update A Set COL1 = Null
GO
Since I upgraded to SQL2K, I have go a SYNTAX
error.
Help Needed
Thanks
EJChewFrom where do you execute the SQL code? QA? Check configuration if someone changed the batch
separator. Can you post the exact errormessage?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"EJChew" <ejchew@.cyberoffice.com.sg> wrote in message news:3f94db83@.news.starhub.net.sg...
> Hi,
> I have been using a lot of "GO" statement in my SQL
> script (say a file called abc.sql) with SQL7.0
> For Example :
> --
> Select * from A
> GO
> Update A Set COL1 = Null
> GO
> Since I upgraded to SQL2K, I have go a SYNTAX
> error.
> Help Needed
> Thanks
> EJChew
>
>|||ejchew:
just a stab in the dark, but do you have the GO statments located on the
same line as another sql statement? this will result in an error
for example:
USE master
GO
SELECT name FROM dbo.sysobjects;
GO
SELECT id FROM dbo.sysobjects; GO
now try the following queries:
USE master
GO
SELECT name FROM dbo.sysobjects;
GO
SELECT id FROM dbo.sysobjects;
GO
hth
jeff clausius
sourcegear corporation
"EJChew" <ejchew@.cyberoffice.com.sg> wrote in news:3f94db83
@.news.starhub.net.sg:
> Hi,
> I have been using a lot of "GO" statement in my SQL
> script (say a file called abc.sql) with SQL7.0
> For Example :
> --
> Select * from A
> GO
> Update A Set COL1 = Null
> GO
> Since I upgraded to SQL2K, I have go a SYNTAX
> error.
> Help Needed
> Thanks
> EJChew
>
>

"DBCC DBReindex" VS "Update Statistics"

Hi all,
Is it neccessay to run the "Update Statistics" after run "DBCC DBReindex"? Does "Update Statistics" automatically update the stats?
Thanks in advance
Kim,As you can see DBCC DBREINDEX updates statistics by itself - it is no need to update statistics after DBCC DBREINDEX.

drop table test
create table test(id int identity,code varchar(10))
create index test_i on test(id)
go
insert test(code) values('a')
insert test(code) values('b')
insert test(code) values('c')
insert test(code) values('d')
insert test(code) values('e')
go
UPDATE STATISTICS test
go
SELECT STATS_DATE(i.id, i.indid)
FROM sysobjects o, sysindexes i
WHERE o.name = 'test' AND o.id = i.id and i.name='test_i'
go
DBCC DBREINDEX ('test')
SELECT STATS_DATE(i.id, i.indid)
FROM sysobjects o, sysindexes i
WHERE o.name = 'test' AND o.id = i.id and i.name='test_i'
go

"Change Tracking" with "Update Index in Background" FREEZE ENTERPRISE

Hi,
When trying to set up "Change Tracking" with "Update Index in Background"
the Enterprise Manager console freezes.
Do I need to stop some Windows services to do that ?
Configuration :
SQL Server 2000 + SP3
Windows 2000 + SP3
Any help will be appreciated.
Thanks.
This should not happen at all. What happens if you configure change tracking
with update index in background using TSQL.
Here is an example of how you would do this for the authors table:
exec sp_fulltext_table 'authors', 'start_change_tracking'
GO
exec sp_fulltext_table 'authors', 'start_background_updateindex'
GO
It is possible that you are experiencing some locking during the intial
population if you have a high number of inserts/updates/and deletes running.
Also are you running profiler? Profiler will degrade performance even while
you have paused the trace.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Jean-Marc PUGIN" <jmpugin@.mobileworkers.com> wrote in message
news:euMN3dD3EHA.2804@.TK2MSFTNGP15.phx.gbl...
> Hi,
> When trying to set up "Change Tracking" with "Update Index in Background"
> the Enterprise Manager console freezes.
> Do I need to stop some Windows services to do that ?
> Configuration :
> SQL Server 2000 + SP3
> Windows 2000 + SP3
> Any help will be appreciated.
> Thanks.
>
|||Jean-Marc,
Most likely setting "Change Tracking" with "Update Index in Background" has
launched either an Incremental (if the table has a timestamp column) or a
Full Population in order to sync-up the MSSearch controlled FT Catalog with
the SQL Server 2000 table. Depending upon your server's resources this may
cause very high CPU-usage as well as with high memory and disk I/O to occur
during certain phases of the Incremental or Full Population and this will
have the affect of temporarily freezing the Enterprise Manager. Note, you
can use SQL Profiler or Perfmon or Task Mgr. to monitor the system usage.
Regards,
John
"Jean-Marc PUGIN" <jmpugin@.mobileworkers.com> wrote in message
news:euMN3dD3EHA.2804@.TK2MSFTNGP15.phx.gbl...
> Hi,
> When trying to set up "Change Tracking" with "Update Index in Background"
> the Enterprise Manager console freezes.
> Do I need to stop some Windows services to do that ?
> Configuration :
> SQL Server 2000 + SP3
> Windows 2000 + SP3
> Any help will be appreciated.
> Thanks.
>
|||John-
I have seen your name on quite a few posts and I believe you are helping
one of my colleagues on a FTS problem.
I thought I saw somewhere that you might have a list of Microsoft Full
Text Search replacements?
I am having a heck of a time finding something to replace the MS FTS in
SQL Server 2000. So far all I can find are web crawlers.
Could you give me some suggestions?
Thanks,
Dave
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||I'm sorry - hit submit by mistake.
I was actually going to offer some assistance on this question.
I did a small FTS build the other day, and I had the same problem. I
was using Query Analyzer, and it took 10 minutes for the second command
to complete execution (see previous posting).
If you do this in EM, expect the same behavior. It will appear to
freeze up for quite a while until it processes the commands.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

"case when" vs multiple update

Which one is faster and cost less for tables with a lot of rows:
1. update atable
set acol = case when cond1 then 1 when cond2 then 2 ..... end
2. update atable set acol = 1 where cond1
update atable set acol = 2 where cond2
update atable set acol = 3 where cond3
....>> Which one is faster and cost less for tables with a lot of rows:
Test it. One UPDATE statement with a CASE tends to be faster than multiple
ones.
Anith|||1 is probably cheaper. But it depends on whether you have a WHERE clause and
the selectivity, how
efficient indexes you have to drive that where clause and also how many rows
you modify.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"nick" <nick@.discussions.microsoft.com> wrote in message
news:A755000B-0491-4ECA-8BF3-0F6FE1A2B456@.microsoft.com...
> Which one is faster and cost less for tables with a lot of rows:
> 1. update atable
> set acol = case when cond1 then 1 when cond2 then 2 ..... end
> 2. update atable set acol = 1 where cond1
> update atable set acol = 2 where cond2
> update atable set acol = 3 where cond3
> .....
>|||it depends, as usual.
look at the execution plans. If at least one of those updates scans the
whole tables, combine them into one update.
On the other hand, if every simple update accesses the table via a
highly selective index and touches just a handful of rows, it could be
better leave them as is, because the combined statement might be
executed as an expensive table scan|||The table may have up to 10 million rows. And the conditions may be only up
to 30s. So it sounds a low selective.
"Tibor Karaszi" wrote:

> 1 is probably cheaper. But it depends on whether you have a WHERE clause a
nd the selectivity, how
> efficient indexes you have to drive that where clause and also how many ro
ws you modify.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "nick" <nick@.discussions.microsoft.com> wrote in message
> news:A755000B-0491-4ECA-8BF3-0F6FE1A2B456@.microsoft.com...
>|||I'm not sure what you mean by "And the conditions may be only up to 30s". Ar
e you saying that the
SELECT statement will typically return about 30 rows from a table with 10,00
0,000 rows? If so, the
query has very high selectivity (it returns relatively few rows). In that ca
se, I'd go with my
original recommendation. If you cannot create that index as a clustered inde
x, a non-clustered index
should be efficient as well (thanks to the high selectivity).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"nick" <nick@.discussions.microsoft.com> wrote in message
news:5D4A2719-76F6-46BD-9249-94CFA8BE8656@.microsoft.com...
> The table may have up to 10 million rows. And the conditions may be only u
p
> to 30s. So it sounds a low selective.
> "Tibor Karaszi" wrote:
>

error creating maintenance plan

Hello,
I'm creating a maintenance plan for a SQL SERVER DB in 64 bits. The plan
includes rebuild indexes, check integrity, update statistics, etc.
I'm using the wizard to create to plan, but at the when I press the finish
button at the end of the wizard there is an error message and I'm unable to
create the plan.
The error message is include at the end of this email.
Does anybody know what could be causing the error?
In advance I appreciate your help and comments.
Edmundo J. Davila
ERROR
TITLE: Maintenance Plan Wizard Progress
Create maintenance plan failed.
ADDITIONAL INFORMATION:
Create failed for JobStep 'Subplan'.
(Microsoft.SqlServer.MaintenancePlanTasks)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Create+JobStep&LinkId=20476[/url]
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
The specified '@.subsystem' is invalid (valid values are returned by
sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476[ /url]
BUTTONS:
OK
This question has already been answered.
Ekrem nsoy
"Edmundo J. Davila" <edavila@.monisa.com> wrote in message
news:4F578C0F-8F8A-4371-88B1-C6FDA8C0D4AD@.microsoft.com...
> Hello,
> I'm creating a maintenance plan for a SQL SERVER DB in 64 bits. The plan
> includes rebuild indexes, check integrity, update statistics, etc.
> I'm using the wizard to create to plan, but at the when I press the finish
> button at the end of the wizard there is an error message and I'm unable
> to create the plan.
> The error message is include at the end of this email.
> Does anybody know what could be causing the error?
> In advance I appreciate your help and comments.
> Edmundo J. Davila
> ERROR
>
> TITLE: Maintenance Plan Wizard Progress
> --
> Create maintenance plan failed.
> --
> ADDITIONAL INFORMATION:
> Create failed for JobStep 'Subplan'.
> (Microsoft.SqlServer.MaintenancePlanTasks)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Create+JobStep&LinkId=20476[/url]
> --
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> The specified '@.subsystem' is invalid (valid values are returned by
> sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476[ /url]
> --
> BUTTONS:
> OK
> --
>

error creating maintenance plan

Hello,
I'm creating a maintenance plan for a SQL SERVER DB in 64 bits. The plan
includes rebuild indexes, check integrity, update statistics, etc.
I'm using the wizard to create to plan, but at the when I press the finish
button at the end of the wizard there is an error message and I'm unable to
create the plan.
The error message is include at the end of this email.
Does anybody know what could be causing the error?
In advance I appreciate your help and comments.
Edmundo J. Davila
ERROR
---
TITLE: Maintenance Plan Wizard Progress
--
Create maintenance plan failed.
--
ADDITIONAL INFORMATION:
Create failed for JobStep 'Subplan'.
(Microsoft.SqlServer.MaintenancePlanTasks)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476
--
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
--
The specified '@.subsystem' is invalid (valid values are returned by
sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476
--
BUTTONS:
OK
--This question has already been answered.
--
Ekrem Önsoy
"Edmundo J. Davila" <edavila@.monisa.com> wrote in message
news:4F578C0F-8F8A-4371-88B1-C6FDA8C0D4AD@.microsoft.com...
> Hello,
> I'm creating a maintenance plan for a SQL SERVER DB in 64 bits. The plan
> includes rebuild indexes, check integrity, update statistics, etc.
> I'm using the wizard to create to plan, but at the when I press the finish
> button at the end of the wizard there is an error message and I'm unable
> to create the plan.
> The error message is include at the end of this email.
> Does anybody know what could be causing the error?
> In advance I appreciate your help and comments.
> Edmundo J. Davila
> ERROR
> ---
>
> TITLE: Maintenance Plan Wizard Progress
> --
> Create maintenance plan failed.
> --
> ADDITIONAL INFORMATION:
> Create failed for JobStep 'Subplan'.
> (Microsoft.SqlServer.MaintenancePlanTasks)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476
> --
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> The specified '@.subsystem' is invalid (valid values are returned by
> sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476
> --
> BUTTONS:
> OK
> --
>