how to dump or append data to a text file

i am doing some insert or update on my db, and i want to dump the errors, if any, to a text file.

If the text file deosnt exist, then i want to create it. Then append data to it, in smthg like:

insert into mytable values (f1,f2,f3)

IF @.@.ERROR <> 0

begin

-- check if text file exist

-- if no create it and write 'an error occured etc...'

-- else append to it another line 'an error occured etc...

end

thanks for helping!!

Hi Terry,

If you're using 2000, then there really is no way to achieve what you are asking except to shell the command out via xp_cmdshell - which will do everything you asked with a little "creativity".

If using 2005, then your best bet would be to wrap the required functionality in a CLR stored proc. Again, BOL has some great info on this.

Cheers,
Rob

how to drop erroneous user from db in 2005

Hi All
i have a row in sysusers table with name 'analyst'. The same login exist in
master..syslogins table but sid's are different. It likely happened when
somebody loaded db but I really don't know.
In 2000 I would remove this row from sysusers table. In 2005 I can't do
that. Which essentially leaves me with option not to use 'analyst' login at
all becase it works in some db's and not others.
What should I do in this situation? How can I remove this erroneous row
which does not have matching sid in master..syslogins table?
Hi
Have you checked that the user is not orphaned with EXEC
sp_change_users_login 'report' ?
In SQL 2005 it is possible to CREATE USER <user> WITHOUT LOGIN and you can
use ALTER USER <user> WITH LOGIN = 'loginname' to remap the sids.
John
John
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:F32E5B19-0559-4953-9069-BA209533E549@.microsoft.com...
> Hi All
> i have a row in sysusers table with name 'analyst'. The same login exist
> in
> master..syslogins table but sid's are different. It likely happened when
> somebody loaded db but I really don't know.
> In 2000 I would remove this row from sysusers table. In 2005 I can't do
> that. Which essentially leaves me with option not to use 'analyst' login
> at
> all becase it works in some db's and not others.
> What should I do in this situation? How can I remove this erroneous row
> which does not have matching sid in master..syslogins table?
|||Hi John
It is orphaned user. I just did not know how to handle it other than remove
it in '2000' fashion. I will try your way tomorrow.
Thank you, Gene.
"John Bell" wrote:

> Hi
> Have you checked that the user is not orphaned with EXEC
> sp_change_users_login 'report' ?
> In SQL 2005 it is possible to CREATE USER <user> WITHOUT LOGIN and you can
> use ALTER USER <user> WITH LOGIN = 'loginname' to remap the sids.
> John
> John
>
> "Gene." <Gene@.discussions.microsoft.com> wrote in message
> news:F32E5B19-0559-4953-9069-BA209533E549@.microsoft.com...
>
>
|||Hi Gene
You should be able use the same methods in 2005 as you used in SQL 2000 see
http://support.microsoft.com/kb/274188/
John
"Gene." wrote:
[vbcol=seagreen]
> Hi John
> It is orphaned user. I just did not know how to handle it other than remove
> it in '2000' fashion. I will try your way tomorrow.
> Thank you, Gene.
> "John Bell" wrote:

how to drop erroneous user from db in 2005

Hi All
i have a row in sysusers table with name 'analyst'. The same login exist in
master..syslogins table but sid's are different. It likely happened when
somebody loaded db but I really don't know.
In 2000 I would remove this row from sysusers table. In 2005 I can't do
that. Which essentially leaves me with option not to use 'analyst' login at
all becase it works in some db's and not others.
What should I do in this situation? How can I remove this erroneous row
which does not have matching sid in master..syslogins table?Hi
Have you checked that the user is not orphaned with EXEC
sp_change_users_login 'report' ?
In SQL 2005 it is possible to CREATE USER <user> WITHOUT LOGIN and you can
use ALTER USER <user> WITH LOGIN = 'loginname' to remap the sids.
John
John
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:F32E5B19-0559-4953-9069-BA209533E549@.microsoft.com...
> Hi All
> i have a row in sysusers table with name 'analyst'. The same login exist
> in
> master..syslogins table but sid's are different. It likely happened when
> somebody loaded db but I really don't know.
> In 2000 I would remove this row from sysusers table. In 2005 I can't do
> that. Which essentially leaves me with option not to use 'analyst' login
> at
> all becase it works in some db's and not others.
> What should I do in this situation? How can I remove this erroneous row
> which does not have matching sid in master..syslogins table?|||Hi John
It is orphaned user. I just did not know how to handle it other than remove
it in '2000' fashion. I will try your way tomorrow.
Thank you, Gene.
"John Bell" wrote:
> Hi
> Have you checked that the user is not orphaned with EXEC
> sp_change_users_login 'report' ?
> In SQL 2005 it is possible to CREATE USER <user> WITHOUT LOGIN and you can
> use ALTER USER <user> WITH LOGIN = 'loginname' to remap the sids.
> John
> John
>
> "Gene." <Gene@.discussions.microsoft.com> wrote in message
> news:F32E5B19-0559-4953-9069-BA209533E549@.microsoft.com...
> > Hi All
> > i have a row in sysusers table with name 'analyst'. The same login exist
> > in
> > master..syslogins table but sid's are different. It likely happened when
> > somebody loaded db but I really don't know.
> > In 2000 I would remove this row from sysusers table. In 2005 I can't do
> > that. Which essentially leaves me with option not to use 'analyst' login
> > at
> > all becase it works in some db's and not others.
> > What should I do in this situation? How can I remove this erroneous row
> > which does not have matching sid in master..syslogins table?
>
>|||Hi Gene
You should be able use the same methods in 2005 as you used in SQL 2000 see
http://support.microsoft.com/kb/274188/
John
"Gene." wrote:
> Hi John
> It is orphaned user. I just did not know how to handle it other than remove
> it in '2000' fashion. I will try your way tomorrow.
> Thank you, Gene.
> "John Bell" wrote:
> > Hi
> >
> > Have you checked that the user is not orphaned with EXEC
> > sp_change_users_login 'report' ?
> >
> > In SQL 2005 it is possible to CREATE USER <user> WITHOUT LOGIN and you can
> > use ALTER USER <user> WITH LOGIN = 'loginname' to remap the sids.
> >
> > John
> >
> > John
> >
> >
> > "Gene." <Gene@.discussions.microsoft.com> wrote in message
> > news:F32E5B19-0559-4953-9069-BA209533E549@.microsoft.com...
> > > Hi All
> > > i have a row in sysusers table with name 'analyst'. The same login exist
> > > in
> > > master..syslogins table but sid's are different. It likely happened when
> > > somebody loaded db but I really don't know.
> > > In 2000 I would remove this row from sysusers table. In 2005 I can't do
> > > that. Which essentially leaves me with option not to use 'analyst' login
> > > at
> > > all becase it works in some db's and not others.
> > > What should I do in this situation? How can I remove this erroneous row
> > > which does not have matching sid in master..syslogins table?
> >
> >
> >

How to do an update on existing records?

I have one table of new records (tableA) that may already exist in
tableB. I want to insert these records into tableB with insert if they
don't already exist, or update any existing ones with new data if they
do already exist. A column (Action) in tableA already tells me whether
this is an INSERT, UPDATE, or DELETE. I'm able to derive that I can do
an insert with

select * into tableB from tableA where Action = 'INSERT'

...and I think I can handle the delete.

But I'm stuck on the update. How do I do the update? An ordinary
UPDATE statement just won't do unless I use a cursor to cycle through
the recordset. I want to avoid a cursor."Google Mike" <googlemike@.hotpop.com> wrote in message
news:25d8d6a8.0402231212.16ab7593@.posting.google.c om...
> I have one table of new records (tableA) that may already exist in
> tableB. I want to insert these records into tableB with insert if they
> don't already exist, or update any existing ones with new data if they
> do already exist. A column (Action) in tableA already tells me whether
> this is an INSERT, UPDATE, or DELETE. I'm able to derive that I can do
> an insert with
> select * into tableB from tableA where Action = 'INSERT'
> ...and I think I can handle the delete.
> But I'm stuck on the update. How do I do the update? An ordinary
> UPDATE statement just won't do unless I use a cursor to cycle through
> the recordset. I want to avoid a cursor.

I don't completely understand your description, and it would be useful to
see the structure of your tables (ie CREATE TABLE statements), as well as
some sample data. However, here is a fairly generic solution - if it doesn't
work as you expect, then please consider posting the additional information.

/* INSERT new records */

insert into dbo.tableB (col1, col2, ...)
select col1, col2, ...
from dbo.tableA a
where not exists
(select * from dbo.tableB b
where a.PrimaryKeyCol = b.PrimaryKeyCol)
and a.[Action] = 'INSERT'

/* UPDATE existing records */

update dbo.tableB
set col1 = a.col1, col2 = a.col2, ...
from dbo.tableB b
join dbo.tableA a
on a.PrimaryKeyCol = b.PrimaryKeyCol
where a.[Action] = 'UPDATE'

/* DELETE existing records */

delete from dbo.tableB
where exists
(select * from dbo.tableA a
where a.PrimaryKeyCol = dbo.tableB.PrimaryKeyCol
and a.[Action] = 'DELETE')

Note that 'Action' is listed in "Reserved Keywords" as a word to avoid using
in code (at least in SQL 2000 Books Online - you didn't mention which
version of MSSQL you're using).

Simon