please help

I have SQL 2000. I have one table tmpJcrewUpdates and another table
Jcrewupdates.
I have a task that reads a text file and imports all of it into
tmpjcrewupdates. The floowing script I am try to get it to either update
exsistign information in Jcrewupdates if the REF# is already in teh
jcrewupdates table. If not, I want the entire data for that ref# to be
sent to the jcrewupdates table.
For some reason, it isn't updating exsisting records, it just adds new ones
if the ref# isn't already there.
update [JCrewUpdates]
set [do rec'd] = [tmpJCrewUpdates].[do rec'd],[bill of ladin
g #] =
[tmpJCrewUpdates].[Bill of lading #],[container] =
[tmpJCrewUpdates].[container],Vessel=[tmpJCrewUpdates].Vessel,CF
LNY=[tmpJCrewUpdates].CFLNY,ETA=[tmpJCrewUpdates].ETA,LFD=[tmpJC
rewUpdates].LFD,terminal=[tmpJCrewUpdates].terminal,cfs=[tmpJCrewUpd
ates].cfs,Ctns=[tmpJCrewUpdates].Ctns,status=[tmpJCrewUpdat
es].status,[p/u
date]=[tmpJCrewUpdates].[p/u date],[del. date]=[tmpJCrewUpda
tes].[del. date]
From [JCrewUpdates]
Join [tmpJCrewUpdates]
on [tmpJCrewUpdates].[ref#]=[JCrewUpdates].[ref#]
Insert into [JCrewUpdates]([do rec'd],ref#,vessel,[BILL OF LADIN
G
#],cflny,eta,LFD,container,terminal,cfs,
ctns,status,[p/u date],[del.
date])
Select [do rec'd],[ref#],vessel,[bill of lading
#],cflny,eta,lfd,container,terminal,cfs,
ctns,status,[p/u date],[del.
date]
From [tmpJCrewUpdates]
Where NOT Exists (Select * from [JCrewUpdates] Where
[JCrewUpdates].[ref#]=[tmpJCrewUpdates].[ref#])Hi John
These are your queries re-written slighly,
UPDATE j
SET [do rec'd] = t.[do rec'd],
[bill of lading #] = t.[Bill of lading #],
[container] = t.[container],
Vessel=t.Vessel,
CFLNY=t.CFLNY,
ETA=t.ETA,
LFD=t.LFD,
terminal=t.terminal,
cfs=t.cfs,
Ctns=t.Ctns,
status=t.status,
[p/u date]=t.[p/u date],
[del. date]=t.[del. date]
FROM [JCrewUpdates] J
JOIN [tmpJCrewUpdates] t ON t.[ref#]=j.[ref#]
INSERT INTO [JCrewUpdates]([do rec'd],[ref#],vessel,
[bill of lading #],cflny,eta,LFD,container,terminal,
cfs,ctns,status,[p/u date],[del. date])
SELECT t.[do rec'd],t.[ref#],t.vessel,
t.[bill of lading #],t.cflny,t.eta,t.lfd,t.container,t.terminal,
t.cfs,t.ctns,t.status,t.[p/u date],t.[del. date]
FROM [tmpJCrewUpdates] t
LEFT JOIN [tmpJCrewUpdates] t ON t.[ref#]=j.[ref#]
WHERE t.[ref#] IS NULL
I could not see anything that would cause the updates not to happen, if the
following returns any rows then you should get updates;
SELECT t.[do rec'd],t.[ref#],t.vessel,
t.[bill of lading #],t.cflny,t.eta,t.lfd,t.container,t.terminal,
t.cfs,t.ctns,t.status,t.[p/u date],t.[del. date]
FROM [tmpJCrewUpdates] t
JOIN [tmpJCrewUpdates] t ON t.[ref#]=j.[ref#]
If not, you may have some problems with the values in the [ref#] columns
John
"Johnfli" wrote:

> I have SQL 2000. I have one table tmpJcrewUpdates and another table
> Jcrewupdates.
> I have a task that reads a text file and imports all of it into
> tmpjcrewupdates. The floowing script I am try to get it to either update
> exsistign information in Jcrewupdates if the REF# is already in teh
> jcrewupdates table. If not, I want the entire data for that ref# to be
> sent to the jcrewupdates table.
> For some reason, it isn't updating exsisting records, it just adds new one
s
> if the ref# isn't already there.
>
>
> update [JCrewUpdates]
> set [do rec'd] = [tmpJCrewUpdates].[do rec'd],[bill of lad
ing #] =
> [tmpJCrewUpdates].[Bill of lading #],[container] =
> [tmpJCrewUpdates].[container],Vessel=[tmpJCrewUpdates].Vessel,CFLNY=&#
91;tmpJCrewUpdates].CFLNY,ETA=[tmpJCrewUpdates].ETA,LFD=[tmpJCrewUpdates].LF
D,terminal=[tmpJCrewUpdates].terminal,cfs=[tmpJCrewUpdates].cfs,Ctns=[tm
pJCrewUpdates].Ctns,status=[tmpJCrewUpd
ates].status,[p/u
> date]=[tmpJCrewUpdates].[p/u date],[del. date]=[tmpJCrewUp
dates].[del. date]
> From [JCrewUpdates]
> Join [tmpJCrewUpdates]
> on [tmpJCrewUpdates].[ref#]=[JCrewUpdates].[ref#]
> Insert into [JCrewUpdates]([do rec'd],ref#,vessel,[BILL OF LAD
ING
> #],cflny,eta,LFD,container,terminal,cfs,
ctns,status,[p/u date],[de
l. date])
> Select [do rec'd],[ref#],vessel,[bill of lading
> #],cflny,eta,lfd,container,terminal,cfs,
ctns,status,[p/u date],[de
l. date]
> From [tmpJCrewUpdates]
> Where NOT Exists (Select * from [JCrewUpdates] Where
> [JCrewUpdates].[ref#]=[tmpJCrewUpdates].[ref#])
>
>

please help

I have SQL 2000. I have one table tmpJcrewUpdates and another table
Jcrewupdates.
I have a task that reads a text file and imports all of it into
tmpjcrewupdates. The floowing script I am try to get it to either update
exsistign information in Jcrewupdates if the REF# is already in teh
jcrewupdates table. If not, I want the entire data for that ref# to be
sent to the jcrewupdates table.
For some reason, it isn't updating exsisting records, it just adds new ones
if the ref# isn't already there.
update [JCrewUpdates]
set [do rec'd] = [tmpJCrewUpdates].[do rec'd],[bill of lading #] = [tmpJCrewUpdates].[Bill of lading #],[container] = [tmpJCrewUpdates].[container],Vessel=[tmpJCrewUpdates].Vessel,CFLNY=[tmpJCrewUpdates].CFLNY,ETA=[tmpJCrewUpdates].ETA,LFD=[tmpJCrewUpdates].LFD,terminal=[tmpJCrewUpdates].terminal,cfs=[tmpJCrewUpdates].cfs,Ctns=[tmpJCrewUpdates].Ctns,status=[tmpJCrewUpdates].status,[p/u
date]=[tmpJCrewUpdates].[p/u date],[del. date]=[tmpJCrewUpdates].[del. date]
From [JCrewUpdates]
Join [tmpJCrewUpdates]
on [tmpJCrewUpdates].[ref#]=[JCrewUpdates].[ref#]
Insert into [JCrewUpdates]([do rec'd],ref#,vessel,[BILL OF LADING
#],cflny,eta,LFD,container,terminal,cfs,ctns,status,[p/u date],[del. date])
Select [do rec'd],[ref#],vessel,[bill of lading
#],cflny,eta,lfd,container,terminal,cfs,ctns,status,[p/u date],[del. date]
From [tmpJCrewUpdates]
Where NOT Exists (Select * from [JCrewUpdates] Where
[JCrewUpdates].[ref#]=[tmpJCrewUpdates].[ref#])Hi John
These are your queries re-written slighly,
UPDATE j
SET [do rec'd] = t.[do rec'd],
[bill of lading #] = t.[Bill of lading #],
[container] = t.[container],
Vessel=t.Vessel,
CFLNY=t.CFLNY,
ETA=t.ETA,
LFD=t.LFD,
terminal=t.terminal,
cfs=t.cfs,
Ctns=t.Ctns,
status=t.status,
[p/u date]=t.[p/u date],
[del. date]=t.[del. date]
FROM [JCrewUpdates] J
JOIN [tmpJCrewUpdates] t ON t.[ref#]=j.[ref#]
INSERT INTO [JCrewUpdates]([do rec'd],[ref#],vessel,
[bill of lading #],cflny,eta,LFD,container,terminal,
cfs,ctns,status,[p/u date],[del. date])
SELECT t.[do rec'd],t.[ref#],t.vessel,
t.[bill of lading #],t.cflny,t.eta,t.lfd,t.container,t.terminal,
t.cfs,t.ctns,t.status,t.[p/u date],t.[del. date]
FROM [tmpJCrewUpdates] t
LEFT JOIN [tmpJCrewUpdates] t ON t.[ref#]=j.[ref#]
WHERE t.[ref#] IS NULL
I could not see anything that would cause the updates not to happen, if the
following returns any rows then you should get updates;
SELECT t.[do rec'd],t.[ref#],t.vessel,
t.[bill of lading #],t.cflny,t.eta,t.lfd,t.container,t.terminal,
t.cfs,t.ctns,t.status,t.[p/u date],t.[del. date]
FROM [tmpJCrewUpdates] t
JOIN [tmpJCrewUpdates] t ON t.[ref#]=j.[ref#]
If not, you may have some problems with the values in the [ref#] columns
John
"Johnfli" wrote:
> I have SQL 2000. I have one table tmpJcrewUpdates and another table
> Jcrewupdates.
> I have a task that reads a text file and imports all of it into
> tmpjcrewupdates. The floowing script I am try to get it to either update
> exsistign information in Jcrewupdates if the REF# is already in teh
> jcrewupdates table. If not, I want the entire data for that ref# to be
> sent to the jcrewupdates table.
> For some reason, it isn't updating exsisting records, it just adds new ones
> if the ref# isn't already there.
>
>
> update [JCrewUpdates]
> set [do rec'd] = [tmpJCrewUpdates].[do rec'd],[bill of lading #] => [tmpJCrewUpdates].[Bill of lading #],[container] => [tmpJCrewUpdates].[container],Vessel=[tmpJCrewUpdates].Vessel,CFLNY=[tmpJCrewUpdates].CFLNY,ETA=[tmpJCrewUpdates].ETA,LFD=[tmpJCrewUpdates].LFD,terminal=[tmpJCrewUpdates].terminal,cfs=[tmpJCrewUpdates].cfs,Ctns=[tmpJCrewUpdates].Ctns,status=[tmpJCrewUpdates].status,[p/u
> date]=[tmpJCrewUpdates].[p/u date],[del. date]=[tmpJCrewUpdates].[del. date]
> From [JCrewUpdates]
> Join [tmpJCrewUpdates]
> on [tmpJCrewUpdates].[ref#]=[JCrewUpdates].[ref#]
> Insert into [JCrewUpdates]([do rec'd],ref#,vessel,[BILL OF LADING
> #],cflny,eta,LFD,container,terminal,cfs,ctns,status,[p/u date],[del. date])
> Select [do rec'd],[ref#],vessel,[bill of lading
> #],cflny,eta,lfd,container,terminal,cfs,ctns,status,[p/u date],[del. date]
> From [tmpJCrewUpdates]
> Where NOT Exists (Select * from [JCrewUpdates] Where
> [JCrewUpdates].[ref#]=[tmpJCrewUpdates].[ref#])
>
>

please check this trigger

Aperently I could not insert a text field to another table from INSERTED in a trigger.
It seems th follwoing is working, do you see any problem joining INSERTED to the mySrcTable
which is the table that has this trigger

INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
SELECT i.myTrID, i.myFirstName, p.myBigText
FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
WHERE (i.myTrType = 'In')

Thanks,

You can set the compatibility level to 80 or higher Or you can choose varchar(max) which has limitation of 8000 characters.

For details seeCREATE TRIGGER.

Hope it helps.

sql

plain text mails

How can I set the default body_format in database mail. I would like to send a warning with plain text format (from the alerts), that i'll get on my mobile phone, but the message's format is html... always...and I don't get the sms-s.Hmm...I believe the default format is text. However, whenever you call sp_send_dbmail, you can specify the @.body_format param to be 'TEXT' so that you know it will be sent as text. To test, send a couple of test emails to an email account. If they are text, then you know you are on the right track. You could even put some html tags inside of the text body to ensure that it isn't formatted as html.|||i know this, but i would like to send an email from the ssms's GUI environment (alerts).
i get an error message...i can send email in plain text format only...but i can't modify the body_format value (in gui environment)...this is a smtp server problem ?...i don't know
) ... sorry my English is not too good...)
...just a comment, when i'm trying to send a mail from the database mail test ...it's success...but from the alert...unsuccess...|||One option is to run Profiler as you are trying to send an email from the GUI. That way, you can find out exactly what is being sent to the database. That may help.
Tim|||I traced "Send Test-Email" process.

I got this:

declare @.mailid int

EXECUTE [msdb].[dbo].[sp_send_dbmail]
@.profile_name = 'DBA'
,@.recipients = 'csaba.molnar_sms@.domainname.com'
,@.body = 'This is a test e-mail sent from Database Mail on FAR03.'
,@.subject = 'Database Mail Test'
,@.mailitem_id = @.mailid OUTPUT

select @.mailid

and I got a sms on my mobile...in unreadable format :
"VGhpcyBpcyBhIHRIc3QgZS1tYWIsIHNIbnQ..."blablabla

I think, th body_format value is wrong. Where can I set this value for the instance?

plain text mails

How can I set the default body_format in database mail. I would like to send a warning with plain text format (from the alerts), that i'll get on my mobile phone, but the message's format is html... always...and I don't get the sms-s.Hmm...I believe the default format is text. However, whenever you call sp_send_dbmail, you can specify the @.body_format param to be 'TEXT' so that you know it will be sent as text. To test, send a couple of test emails to an email account. If they are text, then you know you are on the right track. You could even put some html tags inside of the text body to ensure that it isn't formatted as html.|||i know this, but i would like to send an email from the ssms's GUI environment (alerts).
i get an error message...i can send email in plain text format only...but i can't modify the body_format value (in gui environment)...this is a smtp server problem ?...i don't know
) ... sorry my English is not too good...)
...just a comment, when i'm trying to send a mail from the database mail test ...it's success...but from the alert...unsuccess...|||One option is to run Profiler as you are trying to send an email from the GUI. That way, you can find out exactly what is being sent to the database. That may help.
Tim|||I traced "Send Test-Email" process.

I got this:

declare @.mailid int

EXECUTE [msdb].[dbo].[sp_send_dbmail]
@.profile_name = 'DBA'
,@.recipients = 'csaba.molnar_sms@.domainname.com'
,@.body = 'This is a test e-mail sent from Database Mail on FAR03.'
,@.subject = 'Database Mail Test'
,@.mailitem_id = @.mailid OUTPUT

select @.mailid

and I got a sms on my mobile...in unreadable format :
"VGhpcyBpcyBhIHRIc3QgZS1tYWIsIHNIbnQ..."blablabla

I think, th body_format value is wrong. Where can I set this value for the instance?

Plain text (fixed length field) report output?

I am desperate to get output from Reporting Services into a text
file. Any plugins, converters or technique that anybody knows of?On Aug 2, 12:20 pm, datam...@.gmail.com wrote:
> I am desperate to get output from Reporting Services into a text
> file. Any plugins, converters or technique that anybody knows of?
Outside of the export option of Reporting Services and the Report Mgr,
you would most likely need to design a custom asp.net application that
could output the results of an ADO.NET dataset. An SSRS report could
be executed programmatically (this link might help:
http://msdn2.microsoft.com/en-us/library/aa964126.aspx#sqldocum_topic7
). If exporting to PDF programmatically is an option, I would suggest
using the open source library iTextSharp (http://sourceforge.net/
projects/itextsharp/ ). Crystal Reports could be setup via a report
viewer control and cr.exe to programmatically export a report to text
via an application. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

PKZip issue

I have command line PKZip (pkzipc.exe) installed on my SQL Server 2000.
I want to use it to zip up some text files that I receive and process
on a daily basis to save space. I want to schedule it to run as a job.
The problem is that when I run the job as a standard user, it just does
not work. No error messages or anything. If I make the job owned by
SA, it is fine.

My only thought is that when I am signed in as a SA, my account on the
box is in the Admin group. When I sign in as a standard user, I am not
sure what account it uses to hit the file system. I would assume it
would use the xp_cmdshelluser account I have set up but I am not sure.

Has anyone seen this before? It has me stumped.

Any ideas??

Thanks!

Jim Youmans
St Louis, MissouriIn SQL 2000, the SQL Agent Proxy account is used as the OS security context
for non-sysadmin users. This is configurable from Enterprise Manager under
SQL Agent-->Properties-->Job System. When the 'Only users with Sysadmin
privileges..' is unchecked, you can specify the Windows account to be used
as the security context for non-sysadmin users. It's best to specify a
minimally privileged account.

I see you are located in St. Louis, You might consider joining our local
SQL Server User Group (http://www.stlssug.org/index.html) if you haven't
already done so. We also have a BI special interest group.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Jim" <jdyoumans@.gmail.comwrote in message
news:1158185320.259108.169510@.p79g2000cwp.googlegr oups.com...

Quote:

Originally Posted by

>I have command line PKZip (pkzipc.exe) installed on my SQL Server 2000.
I want to use it to zip up some text files that I receive and process
on a daily basis to save space. I want to schedule it to run as a job.
The problem is that when I run the job as a standard user, it just does
not work. No error messages or anything. If I make the job owned by
SA, it is fine.
>
My only thought is that when I am signed in as a SA, my account on the
box is in the Admin group. When I sign in as a standard user, I am not
sure what account it uses to hit the file system. I would assume it
would use the xp_cmdshelluser account I have set up but I am not sure.
>
Has anyone seen this before? It has me stumped.
>
Any ideas??
>
Thanks!
>
Jim Youmans
St Louis, Missouri
>

|||Thank you for your suggestions. I am a member of the user group and my
coworker who solved this was at the meeting on Wed.

Here is how we ended up solving the issue. Hope it helps someone else.

"I was finally able to get PKZIPC to work as a non-SQL administrator on
FENSQLMLMD04, but it's a bit of a hack. Thanks for all your
suggestions and help. Kudos to Jerry for the final idea...

Tim, since you gave terminal services rights to US\US_SQLCmdshell,
pkzipc worked whenever I remoted in to the server as US_SQLCmdshell.
But as soon as I logged off, it stopped working. Same problem between
pkzipc version 8 and version 6 (except that 6 hangs), same problem on
the other sql server.

I think the problem is that pkzipc is looking in the HKCU registry key,
just to see if it exists. If it does, pkzipc reads the user's
environment information from another registry key,
HKLM...\ProfileList\... and then looks like it sets up the command
prompt environment variables. But if HKCU doesn't exist, it drops out
or hangs.

Maybe that's a standard Win32API thing whenever a command shell program
is launched? I don't think so, because I can run other commands like
"xcopy" fine. Anyway, when I'm not logged in as US_SQLCmdshell, the
HKCU key doesn't exist so pkzipc drops out at that point before it ever
loads the user's environment information.

So Jerry suggested that we use a "dummy" or "benign" Windows service
that is running as US\US_SQLCmdshell in order to keep it logged in.
Brilliant -- now it works! "

Jim Youmans
St Louis Missouri

Dan Guzman wrote:

Quote:

Originally Posted by

In SQL 2000, the SQL Agent Proxy account is used as the OS security context
for non-sysadmin users. This is configurable from Enterprise Manager under
SQL Agent-->Properties-->Job System. When the 'Only users with Sysadmin
privileges..' is unchecked, you can specify the Windows account to be used
as the security context for non-sysadmin users. It's best to specify a
minimally privileged account.
>
I see you are located in St. Louis, You might consider joining our local
SQL Server User Group (http://www.stlssug.org/index.html) if you haven't
already done so. We also have a BI special interest group.
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"Jim" <jdyoumans@.gmail.comwrote in message
news:1158185320.259108.169510@.p79g2000cwp.googlegr oups.com...

Quote:

Originally Posted by

I have command line PKZip (pkzipc.exe) installed on my SQL Server 2000.
I want to use it to zip up some text files that I receive and process
on a daily basis to save space. I want to schedule it to run as a job.
The problem is that when I run the job as a standard user, it just does
not work. No error messages or anything. If I make the job owned by
SA, it is fine.

My only thought is that when I am signed in as a SA, my account on the
box is in the Admin group. When I sign in as a standard user, I am not
sure what account it uses to hit the file system. I would assume it
would use the xp_cmdshelluser account I have set up but I am not sure.

Has anyone seen this before? It has me stumped.

Any ideas??

Thanks!

Jim Youmans
St Louis, Missouri

Pivoting currency text file

Hi!

I have a currency exchange rate flat file with this format:

date;USD;EUR;SEK;

01-01-2004;8.232;8.00;1.43;

02-01-2004;8.232;8.00;1.43;

..and so on.

I need to pivot this to:

01-01-2004;USD;8.232;

01-01-2004;EUR;8.00;

.. and so on..

Anyone got any tips on how to achieve this?

Try the UNPIVOT transformation.

It does exactly what you need to do. There is a useful walkthrough demo in BOL that explains what the UNPIVOT transformation does.

-Jamie