SQL and Access [Pop Up for Profile Issue]
-
It's a bad mix for me. I'm in development and support and have started using an online call ticket application and made a DTS job in SQL 2000 that will get emails from a public folder and imported them into the applications SQL tables via thru access. I need help. If I execute the DTS package every time it gets to the access connection a pop up appears on the screen asking for a profile; there's only one to choose from and I even select the advance to set it as default; then run the step continues successfully inserting the data from the access table into the SQL table. I save the package and then on next run it again hits the step and the pop up again appears and same issue "Choose Profile: MS Exchange Settings." Can anyone tell me how to keep it from popping up like that because if I schedule the pkg the job hangs because it's waiting on an answer for the profile behind the scenes?
Thank you,
Barry
Mixing SQL w/ Access is like mixing the most expensive wine there is with the cheapest beer possible; why would you want to ruin a good beer like that
-
You're trying to send mail from within the package? Or from Access?
-
It sounds like you are using Access to link to Exchange, then pulling the data into SQL Server through Access. There are other ways to get Exchange data, like using CDO. You might look into writing a script that pulls the data from Exchange and populates the SQL Server tables.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Yes. That's it. I notice you're Co-author and I have multiple SQL 2005 servers and can use the SISS but don't really now much about exchange/email and how to make the connection to the emails in the public folder; The simple task I want is to get the data from the inbox to a SQL 2000 database; here are the STEPS I have in a 2000 DTS pkg and would be thankful for some sample/direction to bypass the access piece:
1st Step: Truncate TABLE [CallTracker].[dbo].[EmailSupport]
--above is in a SQL 2000 db
2nd Step: select `Importance`,`Message Class`,`Priority`,`Subject`,`From`,`Message To Me`,`Message CC to Me`,`Sender Name`,`CC`,`To`,`Received`,`Message Size`,`Creation Time`,`Last Modification Time`,`Subject Prefix`,`Has Attachments`,`Normalized Subject`,`Object Type`,`Content Unread`,`Body` from `Application Support Query`
--above Data transfer from the access data base to SQL, Another team member created the access db usinning a link table or something and I'm not very interested in access but he couldn't figure out how to get the emails another way and I don't so it endend up beining this way and the pop-up is killing the deal.
--The next steps just stages the email data used into the SQL database:
insert into EmailSupportExport ( Importance, [Message Class], Priority, [Subject], [From], [Message To Me], [Message CC to Me], [Sender Name], CC, [To], Received, [Message Size], Body, [Creation Time],
[Last Modification Time], [Subject Prefix], [Has Attachments], [Object Type], [Content Unread],GroupName )
SELECT asit.Importance, asit.[Message Class], asit.Priority, asit.[Subject], asit.[From], asit.[Message To Me], asit.[Message CC to Me], asit.[Sender Name], asit.CC, asit.[To], asit.Received, asit.[Message Size], asit.Body, asit.[Creation Time],
asit.[Last Modification Time], asit.[Subject Prefix], asit.[Has Attachments], asit.[Object Type], asit.[Content Unread], 'App Support'
FROM EmailSupport asit
left outer join EmailSupportExport asi on
asi.subject = asit.subject and asi.received = asit.received
where asi.subject is null and asi.received is null
INSERT INTO bugs
(bg_short_desc,
bg_reported_user,
bg_reported_date,
bg_status,
bg_priority,
bg_category,
bg_project,
bg_assigned_to_user,
bg_last_updated_user,
bg_last_updated_date,
bg_user_defined_attribute)
SELECT Subject,
'4',
Received,
'1',
CASE Priority WHEN '1' THEN '2' ELSE '4' END,
'13',
'19',
'0',
'1',
Received,
'0'
FROM EmailSupportExport
WHERE Subject NOT LIKE 'RE:%' AND Imported = '0'
INSERT INTO bug_posts
(bp_bug,
bp_type,
bp_user,
bp_date,
bp_comment,
bp_email_from,
bp_email_to)
SELECT bg_id,
'comment',
'4',
Received,
Body,
[From],
[To]
FROM bugs INNER join EmailSupportExport e on bugs.bg_short_desc = e.Subject and bugs.bg_reported_date = e.Received
WHERE bugs.bg_reported_date IS NOT NULL AND e.Imported = '0'
UPDATE EmailSupportExport
SET Imported = '1'
WHERE Imported = '0'
So what you said is what I need where I can skip the access connection, but how?
-
You will need to write a script using CDO or LDAP to get the Exchange data. It is not necessarily easy to learn. You can search the web for sample scripts or get a book to learn.
I wonder if there is an easier way. Is the Access db on the same server as SQL? If not, I would try moving it there and modifying your package to point to the new location to see if that helps. When the package runs as a job, it is probably running as the account that starts up SQL Server Agent. Another thing you might try is to log on to the server with that account and make sure that the Outlook profile is set up for that account.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Sure this will be amusing, but what is "CDO?" I did try logging in as the SQL agent and creating the package/job and it's profile is the same before submitting this becasue I thought that may be it too and got the same problem but I haven't tried the move the Access db to the same server; maybe that'll help. Do you know of a "CDO" or LDAP script off hand the works with SQL to drag the public email data in that you might want to share
-
LDAP can be used to pull information from Active Directory and Exchange. CDO can be used to pull information from Exchange. Basically, you would have to write a script to pull the messages from Exchange and then insert the information into SQL Server. I'm not saying this is simple, but it would eliminate the Access bridge. I'll have to check around and see if I can find a script to get you started.
In the meantime, see if moving the Access file to the server helps. I think the goal here would be to get SQL Server to realize it can use its own Outlook profile.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
I saw several older (DAO) references via Google about tweaking the connection properties to set a default profile - might be worth digging into that angle.
-
At the step 2 is where I get the pop up asking for profile. And there's an advanced button that has a check box to set for default profile; I've checked save the package re-run and the pop up ask again and the box is unchecked when I click on advance. I'm about googled out on this issue and may end up having to ask a programmer in the compnay to write or show me hoe to write the LDAP? CDO? or whatever the script is that can just pull from the public email folder; I was really hopeful that there was a way to just set the profile pop up to a quite setting so I could schedule this job and be done.
-
Try slipstick.com for Exchange/Outlook stuff, you might also look for an Access site.
-
I played around with linking to Exchange through Access and noticed that there was a checkbox at that point that allowed you to save the default profile. I assume that you left that checked when you linked the table. If you can get this working, it would be so much easier than writing a script to pull the data from Exchange. I have a feeling the only way to get this working is to have Access installed on the server, log on to the server as the service account and link the to the table there. I think maybe it is getting confused because the original profile is on a different machine.
It has been years since I did any of this kind of programming so I don't have any sample scripts lying around, but as Andy mentioned, there are many resources on the web to get you started.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Wonderful; I'm hopeful that's it; I didn't make the linked connection or the access db for this project and will go back thru it myself and if I were a betting man I'd say that that box was skipped over at that point. I'll post back if it works out and either way http://sqlservercentral.com is awesome and I look forward each day I go to work to see what articles are in the news letter.
Thank you all,
Barry
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply