mssql 2000 update from select

I am trying to update a table from a select statement, any suggestions?

update (select column1 from table1)
set column2 = 'value'

The data in column1 contains the name of the table I want to update column2 with the 'value'.

I'm am pretty sure the syntax is in correct along with the structure. The error...

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '='.

Quote:

Originally Posted by tdority

I am trying to update a table from a select statement, any suggestions?

update (select column1 from table1)
set column2 = 'value'

The data in column1 contains the name of the table I want to update column2 with the 'value'.

I'm am pretty sure the syntax is in correct along with the structure. The error...

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '='.

nope, the syntax is not correct. in this case you have to build the string for the entire update command..

set @.sqlcmd = 'update ..."

exec (@.sqlcmd)|||Please explain how to build.

MSmerge_genhistory error-515

Can't insert value NULL into column nicknames.

This error generated when I try start sync.

SQL Profiler is not helping w/ locating specific error.

I tried to allow NULLS in nickname column, for this table, but this table gets dropped and recreated each time.

Pls. advise on where to make correction.

thx..bt

Can you tell us what version of sql server you're using, including Service Pack # (if any)?|||

Setup:

Server A: SQL Server 2000, Enterprise Ed, SP4 (setup as subscriber)

Server B: SQL Server 2000, Personal Ed, SP4 (setup as publisher/distributor, using PUSH subscription type service).

Server B is a Tablet PC running Win XP 2005, SP2.

When I force 'start sync' to run for Merge Agent, I get failure in Agent logs, but when I query Server A I see the updated data sets. Why would I get failure "error 515 for insert of NULL to nicknames column in Process table and still pass updates?

|||

Additional information that may or may not be helpful:

My data entries are stored on Server B (no connection to LAN or wireless), when I return the tablet PC to cradle I expect snapshot creation and merge to occur.

Server A-subscription -status shows 'Never Started'.

How can this be? When I query tables on Server A I see the updates have occured in my tables.

Apparently I can force 'start sync' from the Merge Agent, but I can't get the agent to successfully run w/o intervention.

Here's the latest error:

process could not bulk copy into table 'x' - error 20037.

thx. for taking the time to review and provide input..bt

|||

You've described three different problems, but it's unclear what the root problem is.

First, replication does not ship anything out of the box that would allow your tablet pc to automatically sync when first connecting to the cradle. You have to either sync manually, schedule a sync, or write a program to sync on connect.

Regarding the last error, 20037, can you drill down to get the exact error message? I don't know what error 20037 is, but if you cannot apply your snapshot, then you cannot sync properly afterwards. I'm guessing that you were able to apply part of your snapshot successfully, which is why you see some rows in your table.

|||

Error 20037 indicates article already exists in another publication w/ different article resolver. (I think this occurs if merge agent is being re-ran w/ same snapshot files.)

I have not figured out how to delete the snapshot directory files directly after Merge agent runs. Maybe it will not delete until it can run successfully w/o manual push.

If delete MSmerge_contents, MSmerge_tombstone,MSmerge_genhistory tables, then re-run Merge agent, result is:

'can't insert value NULL into column nicknames.table Process.dbo.MSmerge_genhistory, column does not allow nulls.'

Review of same tables, I see MSmerge_genhistory has records, but other two tables have none.

If re-run Merge agent, result is:

error 20037 (see above), but data copies to server tables.

So the cycle continues.

|||

Manually deleting merge metadata tables is an unsupported option. This explains why you're getting the "cannot insert NULL" error message. The only workaround now is to remove replication completely and start over. What can happen when you manually delete rows from metadata tables is that cleanup/setup/etc. will try to reference rows that don't exist, or tables will have orphaned rows.

Once you start over on a clean slate, and you're still getting errors, please post the first error and error message that you get.

|||

I deleted the publication and articles via use of stored procedures in SQL Books Online and verified no rowsets in tables.

I then re-created a new Process publication and a new PUSH subscription, it ran. The snapshot created successfully, but the merge agent gave this error.

cannot insert value NULL into column 'nicknames'.table 'Process.dbo.MSmerge_genhistory'. column does not allow nulls. INSERT fails. error 515.

|||

After the publication deletion process and getting same error 515, I did the following: because you indicated the metatables were trashed after I did some manual deletions.

I have looked at MSmerge_history table on both servers. The number of row sets does not match (don't know if it should though).

Anyway, the two rowsets on subscriber do match 2 of the 11 that exist on publisher server.

MSmerge_contents table does match across both servers-w/ 3 rowsets.

MSmerge_tombstone matches on both servers-w/ no rowsets.

Not sure what else to check or whether I should detach my db and remove SQL Server from both systems and start over.

|||

Why does nickname column from MSmerge_genhistory have to accept non-NULL values?

This is the crux of my problem. I can't change the table design becuase it gets re-created each time merge agent runs.

Does anyone know what stored procedure is used to drop/create this table each time?

|||Are there any other errors that you can see in the Merge history table? Can you do "select * from distribution.dbo.MSmerge_history" and see if there are any other errors returned from that particular sync? This may be just one of several errors that showed up, and unfortunately it's not enough info at the moment to determine where the problem is.|||Have you renamed one of your machines recently, or in the past, while SQL Server was installed?

Msg:15335

Server: Msg 15335, Level 11, State 1, Procedure
sp_rename, Line 342
Error: The @.newname value 'BC_SLATFix' is already in use
as a object name and would cause a duplicate that is not
permitted.
Thanks reply
hi,
While using sp_rename stored procedure make sure the value that you are
passing as a new name of the object, does not exists in the database .
Ex:
sp_rename 'old_name,'new_name' --object with the new_name must be existing
in the database.
Vishal Parkar
vgparkar@.yahoo.co.in
sql

Msg 512 on cursor

Everytime I execute this procedure it gives a msg 512 subquery returns more then one value. Can someone just explain why or what I'm doing wrong? Thanks in advance

Here is my code:

create procedure insert_sku_info
AS
Declare
@.method varchar(40),
@.sku int,
@.location varchar(40)

Declare insert_cur Cursor For
select a.method, s.sku, s.location
from archive_sku a INNER JOIN sku s on a.sku = s.sku
and a.location = s.location

Open insert_cur

Fetch Next from insert_cur
Into @.method, @.sku, @.location

While (@.@.Fetch_Status = 0)
Begin

print @.method + @.sku + @.location

Update sku
set method = @.method
where sku = @.sku
and location = @.location

Fetch Next from insert_cur
Into @.method, @.sku, @.location

End
close insert_cur
deallocate insert_curNever mind I figured it out.

That print statement I had listing all the variables was the problem.

Thanks anyway.

msg 296 - out of range smalldatetime value

Server: Msg 296, Level 16, State 3, Line 4
The conversion of char data type to smalldatetime data type resulted in an
out-of-range smalldatetime value.
I'm doing a simple select, one of the values is the concatention of a CASE
statement and two other fields. I've got no date specification or range of
any sort in my query, except that one of the fields I am concatenating with
is a date. the relevant portion of the code is here;
...
...
END+A.ctDate+CAST(A.ID AS varchar(12))+'.'+'BB' AS oID
This is the only date I reference at all in the select, and if pulled from
the query, it runs fine. What's the right way to get around msg 296 in this
situation?
-- LynnIf you're concatenating to a string, you should cast it explicitly as a
string.
...
END+CONVERT(CHAR(8), A.ctDate, 112)+CASE ...
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:7FB003B4-F5EB-4FC6-B330-8483A80EA586@.microsoft.com...
> Server: Msg 296, Level 16, State 3, Line 4
> The conversion of char data type to smalldatetime data type resulted in an
> out-of-range smalldatetime value.
> I'm doing a simple select, one of the values is the concatention of a CASE
> statement and two other fields. I've got no date specification or range
> of
> any sort in my query, except that one of the fields I am concatenating
> with
> is a date. the relevant portion of the code is here;
> ...
> ...
> END+A.ctDate+CAST(A.ID AS varchar(12))+'.'+'BB' AS oID
> This is the only date I reference at all in the select, and if pulled from
> the query, it runs fine. What's the right way to get around msg 296 in
> this
> situation?
> -- Lynn|||excellent. that works perfectly. thanks very much
-- Lynn
"Aaron Bertrand [SQL Server MVP]" wrote:

> If you're concatenating to a string, you should cast it explicitly as a
> string.
> ....
> END+CONVERT(CHAR(8), A.ctDate, 112)+CASE ...
>
>
> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
> news:7FB003B4-F5EB-4FC6-B330-8483A80EA586@.microsoft.com...
>
>