Syntax help needed again - SQL Server Forums

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 again

Author

Topic

TJ
Posting Yak Master

201 Posts

Posted - 2002-01-03 : 11:15:50

I hope everyone had a safe and happy holiday!

Things have changed here again. Now I HAVE to do all of the work from sql1 to sql2. I found out why I couldn't get the link to work before: sql2 has another name. Once I changed that I was able to get them to link.

My issue is this:

I'm running the same code that I got help with last week. I've changed the direction of the requests and I'm having trouble. I think it's the way I have it written, but it could be just wrong all together? Right now I'm getting an error on the insert line that says: 'MSDTC on server 'NTSSQL1' is unavailable'. But I truly think it's a syntax error.

Here's the original code (sql2 linked to sql1)
--perform query
declare @pProd varchar (6), @sProd varchar (6), @Addr varchar (35)
declare @txStat varchar (1), @Key varchar(4), @mailbox varchar (5)

set @pProd= ''
set @sProd= ''
set @addr = ''
set @txStat = 'P'
set @Key = '0'

--insert applicable records into IH_Accounts
update ntssql1.efiling.dbo.efile set taxwrkstat = 'X' where taxwrkstat = 'P'
insert into ih_accounts(accountkey,prepprod, prepstate, firmaddress, mailbox)
select @key, prepprod, prepstate, addr, mailbox
FROM ntssql1.efiling.dbo.efile
where taxwrkstat = 'X' 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')
or
(left(prepstate, 3) = 'TAX' or prepstate = '1040SD' or prepstate = '1040PL'
or prepstate = 'ENHEDT' or prepstate = 'PROEDT' or prepstate = 'PROED+' or
prepstate = '1040IN'))

update ef
set ef.taxwrkstat = 'C', ef.serialno = mb.serialno, ef.chargeno = mb.chargeno, ef.acctno = mb.acctno, ef.accesscode1040 = mb.accesscode1040
FROM
ntssql1.efiling.dbo.efile ef
INNER JOIN
(select distinct mailbox, serialno, chargeno, acctno, accesscode1040 from dbo.ih_accounts
where dbo.ih_accounts.mailbox is not null)
as mb ON ef.mailbox = mb.mailbox
where taxwrkstat = 'W' and mb.serialno > '0'

Here's the new code (sql1 linked to sql2)
(declarations are all the same)

update efile2 set taxwrkstat = 'P' where taxwrkstat = 'X'

select distinct @key, prepprod, prepstate, addr, mailbox
FROM dbo.efile2
where taxwrkstat = 'X' 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')
or
(left(prepstate, 3) = 'TAX' or prepstate = '1040SD' or prepstate = '1040PL'or prepstate = 'ENHEDT' or prepstate = 'PROEDT' or prepstate = 'PROED+' or prepstate = '1040IN'))
insert into ntsdev.ih_accounts.dbo.ih_accounts2 (accountkey ,prepprod, prepstate, firmaddress, mailbox)
--set the taxwrkstat to 'W' so the records are not duplicated in the ih_accounts table
update efile2 set taxwrkstat = 'W' where taxwrkstat = 'X'

Thanks in advance!
Teresa

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-01-03 : 11:22:02

Hi

That doesn't sound like a syntax error to me.

Take a look at this, see how it sounds to you
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11818

Damian

TJ
Posting Yak Master

201 Posts

Posted - 2002-01-03 : 11:28:13

quote:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11818

This is what was happening before I was able to get them to connect. It was because I was using the 'alias' name to the server. Once I changed that I connected. Perhaps there's still something that needs changed?? This is an actual server though. No one is using it as a workstation.

Thanks for your input!

ToddV
Posting Yak Master

218 Posts

Posted - 2002-01-03 : 11:51:22

quote:

'MSDTC on server 'NTSSQL1' is unavailable'

Is this running? You can check in the support services folder in EM.

TJ
Posting Yak Master

201 Posts

Posted - 2002-01-03 : 11:59:23

It was the syntax. It's bitten my beehind before, so I knew I'd done something wrong. After hacking at it a bit more, I was able to get it to work with this code:

update efile2 set taxwrkstat = 'X' where taxwrkstat = 'P'
insert into ntsdev.ih_accounts.dbo.ih_accounts2 (accountkey, prepprod, prepstate, firmaddress, mailbox)
select distinct @key, prepprod, prepstate, addr, mailbox
FROM dbo.efile2 ............

Have a great day! Thanks for your responses! Sometimes, I just need someone to hold my hand! :o)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources