Syntax Help Needed- Problem is solved! Thanks!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Syntax Help Needed- Problem is solved! Thanks!

Author

Topic

TJ
Posting Yak Master

201 Posts

Posted - 2001-12-20 : 15:23:35

I have two servers that I've been able to connect. I'm trying to write a statement that gets applicable data from one server/table and insert it into the other server/table.

If you have any ideas what needs changed in this code, please let me know!

Thanks in advance,
Teresa

--perform query
declare @mbox varchar(5), @pProd varchar (6), @sProd varchar (6), @Addr varchar (35)
declare @stat varchar (1), @txStat varchar (1), @Key varchar(4), @chkTrue varchar (1)
declare @mailbox varchar (5)

set @mbox = ""
set @pProd= ""
set @sProd= ""
set @addr = ""
set @stat = ""
set @txStat = 'P'
set @Key = '0000'
set @chkTrue = 'F'

declare mBox_cursor cursor for
select distinct mailbox
from ntssql1.efiling.dbo.efile
where mailbox is not null
open mBox_cursor
fetch next from mBox_cursor
into @mBox
while @@fetch_status = 0
begin
if (select (mailbox) FROM ntssql1.efiling.dbo.efile
where mailbox = @mbox and (left(prepprod, 3) = 'TAX' or prepprod = '1040SD' or prepprod = '1040PL'
or prepprod = 'ENHEDT'or prepprod = 'PROEDT' or prepprod = 'PROED+' or
prepprod = 'TV990E' or prepprod = '1040IN'and taxwrkstat = 'P'))> 0
begin
*/
set @pProd= prepprod
set @sProd= prepstate
set @addr = addr
set @txStat = "W"
insert into IH_ACCOUNTS (ACCOUNTKEY,PREPPROD, PREPSTATE, FIRMADDRESS, MAILBOX)VALUES(@key, @pProd, @sProd, @ADDR, @mbox)
update efile set taxwrkstat = 'W' where mailbox = @mbox
end
else
begin
print @mbox + "Does not match the criteria!"
end
fetch next from mbox_cursor
into @mbox
end
close mbox_cursor
deallocate mbox_cursor

Edited by - tj on 12/21/2001 12:14:31

chadmat
The Chadinator

1974 Posts

Posted - 2001-12-20 : 15:59:06

What happens when you run this?

-Chad

PiecesOfEight
Posting Yak Master

200 Posts

Posted - 2001-12-20 : 16:04:16

I'm nnot sure if there's anything else, but you have an unmatched comment end tag in the middle of the code: */

nr
SQLTeam MVY

12543 Posts

Posted - 2001-12-20 : 16:07:10

if (select (mailbox) FROM ntssql1.efiling.dbo.efile ... ) = 0
maybe should be
if exists (select * FROM ntssql1.efiling.dbo.efile

update efile set taxwrkstat = 'W' where mailbox = @mbox
should probably be updating on the remote server.

Is it possible that an entry can be put in ntssql1.efiling.dbo.efile while it is processing the mailbox? If so I guess you could miss it.
If not it woulkd probably be easier to get all the entries from the remote server into a temp table and do the processing all in one go rather than one by one - then update in a single statement at the end.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

TJ
Posting Yak Master

201 Posts

Posted - 2001-12-20 : 16:34:08

I have it working, but not updating the additional fields that I had set statements for.

Here is new code. If anyone knows of a better way to do this, please say so. I am new to SQL and am hacking my way through it. I know it's not the best way, but I have a deadline to meet... it was last night. :o)

Thanks again!
Teresa

declare @mbox varchar(5), @pProd varchar (6), @sProd varchar (6), @Addr varchar (35)
declare @stat varchar (1), @txStat varchar (1), @Key varchar(4), @chkTrue varchar (1)
declare @mailbox varchar (5)

set @mbox = ""
set @pProd= ""
set @sProd= ""
set @addr = ""
set @stat = ""
set @txStat = 'P'
set @Key = '0000'
set @chkTrue = 'F'

declare mBox_cursor cursor for
select distinct mailbox
from ntssql1.efiling.dbo.efile
where mailbox is not null
open mBox_cursor
fetch next from mBox_cursor
into @mBox
while @@fetch_status = 0
begin
if (select(mailbox) FROM ntssql1.efiling.dbo.efile
where mailbox = @mbox and (left(prepprod, 3) = 'TAX' or prepprod = '1040SD' or prepprod = '1040PL'
or prepprod = 'ENHEDT'or prepprod = 'PROEDT' or prepprod = 'PROED+' or
prepprod = 'TV990E' or prepprod = '1040IN'and taxwrkstat = 'P'))>0
begin

set @pProd= (select prepprod from efile)
set @sProd= (select prepstate from efile)
set @addr = (select addr from efile )
set @txStat = "W"

insert into IH_ACCOUNTS (ACCOUNTKEY,PREPPROD, PREPSTATE, FIRMADDRESS, MAILBOX)VALUES(@key, @pProd, @sProd, @ADDR, @mbox)
update efile set taxwrkstat = 'W' where mailbox = @mbox
end
else
begin
print @mbox + "Does not match the criteria!"
end
fetch next from mbox_cursor
into @mbox
end
close mbox_cursor
deallocate mbox_cursor

nr
SQLTeam MVY

12543 Posts

Posted - 2001-12-20 : 17:23:49

set @pProd= (select prepprod from efile)
set @sProd= (select prepstate from efile)
set @addr = (select addr from efile )
set @txStat = "W"

I guess you have a local efile too.
Should these statements include 'where mailbox = @mbox'

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

TJ
Posting Yak Master

201 Posts

Posted - 2001-12-20 : 17:40:00

I don't have a local efile. That was a mistake that I realized after running it! Sorry about that!
However, this causes an error that says something about a subselect.. it takes 20minutes to run... I've lost that error... I'm lost... :o)

It should read:

set @pProd= (select prepprod from ntssql1.efiling.dbo.efile)
set @sProd= (select prepstate from ntssql1.efiling.dbo.efile)
set @addr = (select addr from ntssql1.efiling.dbo.efile )
set @txStat = "W"

TJ
Posting Yak Master

201 Posts

Posted - 2001-12-20 : 19:37:13

This is the code that works the best! :)

It's amazing how quickly it runs without a cursor!!!

Thanks for your support and ideas!
Teresa

insert into ih_accounts(accountkey,prepprod, prepstate, firmaddress, mailbox)
select @key, prepprod, prepstate, addr, mailbox
FROM ntssql1.efiling.dbo.efile
where (left(prepprod, 3) = 'TAX' or prepprod = '1040SD' or prepprod = '1040PL'
or prepprod = 'ENHEDT'or prepprod = 'PROEDT' or prepprod = 'PROED+' or
prepprod = 'TV990E' or prepprod = '1040IN'and taxwrkstat = 'P')
or
(left(prepstate, 3) = 'TAX' or prepstate = '1040SD' or prepstate = '1040PL'
or prepstate = 'ENHEDT' or prepstate = 'PROEDT' or prepstate = 'PROED+' or
prepstate = '1040IN'and taxwrkstat = 'P')

--set the taxwrkstat to 'W' so the records are not duplicated in the ih_accounts table
update ntssql1.efiling.dbo.efile set taxwrkstat = 'W' where taxwrkstat = 'P'

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-20 : 19:53:25

quote:
It's amazing how quickly it runs without a cursor!!!

I think we may have another convert here!

Teresa, your series of or prepprod = ... or preprod = ... or... could be turned into something like preprod IN ('1040SD', '1040PL', ...). I believe the performance would be the same. You can do the same thing for prepstate in the second part. This might make it a little easier to read. NOTE: Your comparison of left(prepprod, 3) won't fit the pattern so it would be separate.

-------------------
It's a SQL thing...

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-20 : 20:03:05

Also, be aware that if any new records in ntssql1.efiling.dbo.efile can be marked with taxwrkstat = 'P' while this process is running, that you could miss some rows because the update statement fires after the INSERT statement. Two ways to work around this are:

A) Set your ntssql1.efiling.dbo.efile records to a temporary taxwrkstat code at the beginning. And then update them at the end to the final code. It would look like:

update ntssql1.efiling.dbo.efile set taxwrkstat = 'X' where taxwrkstat = 'P'
INSERT .....
update ntssql1.efiling.dbo.efile set taxwrkstat = 'W' where taxwrkstat = 'X'

B) Make your UPDATE statement join back to ih_accounts on the key fields.

-------------------
It's a SQL thing...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources