Hi guys how do you hide duplicate records, how would I do a select statement for that
In (SELECT [AccountNo] FROM [2006_CheckHistory] As Tmp GROUP BY [AccountNo] HAVING Count(*)>1 )
I have about had it with this database I have been asked to make a report out ofwhat do you want to do ?
Lists only unique AccountNo ?
select distinct AccountNo from [2006_CheckHistory]|||i personally don't "hide" duplicate rows, i prevent them from happening|||so there was nothing I could do about the duplicates, they are making the query difficult to create, and this thing is driving me crazy. Some took data and just threw it into a database and called it a day (was a VB programmer)
what do you want to do ?
Lists only unique AccountNo ?
select distinct AccountNo from [2006_CheckHistory]
I'm trying to make the AccountNo unique, you dont need it entered more then once in a table.|||I want to delete them I just want to create a query that shows me all the duplicates and then delete them, leaving only one AccountNo instead of several. Does that make sense??|||okay, you want one row per AccountNo
what about the other columns? which values would you like from those?
here's an example of the problem you could be facing --
AccountNo Name Age City
1234 Fred 23 Chicago
1234 Fred 23 Waukegan
1234 Fred 26 Chicago
1234 Todd 23 Chicago
5678 Mary 16 Milwaukee
5678 Mary 15 Milwaukee
5678 Mary 17 Milwaukeenow how are we supposed to know which row to keep?|||Yeah, the problem that I need to work around is when I have it as a report I cant create a total per AccountNo it just totals it for all the records rather then per account... like if there is an account for number 000111222 then I cant get the total amount for just that record. I need one record to show and its total for that year and this is such a BIG PAIN. I didnt create this database so its a matter of some serious fixing. The problem is gettign this done before the 22nd of January...ACCCKKKK HELP PLEASE!!!!!!!!!!!!!!
Then as soon as I'm done I'm going to do some serious restructuring|||It seems that you are talking about one table, but think about those tables which are related with this one.
Whatever you plan, plan considering all related tables in the database.|||Take your offending table, and run this query against just that table (no, joins, no fancy stuff, just this query alone):SELECT Count(AccountNo), Count(DISTINCT AccountNo)
FROM OffendingTable-PatP|||Oh yeah, while you are at it could you explain what you consider to be a "duplicate record" ? Does that mean that all columns in more than one row are identical? Does it mean that one column (AccontNo) in more than one row are identical? Does it mean something completely different that I haven't thought of yet?
-PatP|||Thank you so much guys,
It means that there is identical [AccountNo] in the table, these are businesses, now when the business changes ownership they indicate that with a letter like A, B..etc, or if theres equipment at that business thats indicated with a letter. I was thinking of making the AccountNo Unique by getting rid of the duplicates and making it unique. And having a seperate table with the A, B and the date they changed hands linked to the AccountNo table, since this is table would reflect changes to the [AccountNo] table. The [AccountNo] table would have the information concering the business, like address, name of business..etc. Does that make sense??|||Only problem is what about the other columns...which values do you want? Does it matter to you? It usually does.
Post the DDL of the table and some sample data, and what you want in the final table...
And what are the report req's?|||Only problem is what about the other columns...which values do you want? Does it matter to you? It usually does.
Post the DDL of the table and some sample data, and what you want in the final table...
And what are the report req's?
OwnerManagerID AccountNo CheckDate CheckNumber CheckAmount OwnerAddress1 NameFor1099 OwnerCity OwnerState OwnerZip
A 00010002 2006-09-20 37065 $621.75 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-03-20 19006 $679.25 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-10-23 38341 $587.13 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-02-16 28548 $723.38 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-06-20 33119 $994.00 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-08-23 35806 $578.75 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-07-24 34551 $488.50 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-05-18 31820 $573.50 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-04-21 30734 $582.88 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-12-20 40892 $614.00 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-01-17 24366 $427.75 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010002 2006-11-17 39614 $730.00 11070 BLAH-DE-BLAH FOL-DER-OL EUREKA KS 90120-1234
A 00010004 2006-06-22 23676 $1,972.79 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-02-09 23644 $1,737.02 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-03-10 23660 $1,632.50 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-05-31 32492 $1,586.44 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-09-12 36608 $1,778.60 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-07-20 34540 $1,715.68 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-04-11 30257 $1,951.40 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-08-07 35355 $1,362.58 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-01-06 25876 $1,834.49
A 00010004 2006-12-06 40426 $1,647.72 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-11-13 39605 $1,795.44 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937
A 00010004 2006-11-06 39164 $1,524.11 123 SESAME STREET #937 LUXE SUITES DRY GULCH TX 90937-0937|||I want in the ownermanager_table Ownermanagerid, Address, city, state zip..ect, AccountNo, fedtaxid.
That should be one table and the other table should be the check amount, checkdate, name of bank..etc|||sorry, that looked like really private information so i obfuscated it|||Thank you appreciate it|||WOOHOO I GOT THE QUERY I NEEDED ITS OVER THIS HORRIBLE NIGHTMARE IS OVER....WOOOHOOOOOOOO!!!!!!!!!!!!!!!!!!!
I'M SO HAPPY I COULD SCREAM...Good grief this person just took live DATA and stuck in a table and just called it a day, no documentation no nothing. The whole thing is structured so badly its a nightmare...EEEEEEEEEEEEEEEEEE!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!
Thank you guys
Showing posts with label group. Show all posts
Showing posts with label group. Show all posts
Monday, March 26, 2012
Sunday, February 19, 2012
dts works but run same dts via sql job failed
hi all,
i posted this somewhere else but i think here is the right group.
i have a dts that shld write to a text file located at a mapped drive. i read somewhere that sql job does not recognise mapped drive, so i use UNC path in form \\128.1.1.1\dtsfile, which dtsfile folder is the shared folder name. i opened the folder permission to everyone.
thats abt the remote server. the sql server i am running is on windows NT, logon using Administrator to local. my sql is SQL 2000. i register the server using sa username. the server agent is start up using System account.
now the problem is when i run the dts interactively/manually, it runs succesfully. but when i run it thru SQL job it says "Access denied". Or full error is like this,
DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 Error Detail Records: Error: 5 (5); Provider Error: 5 (5) Error string: Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
i really hope and appreciate if someone out there can help me out. thanks!
ps: pls let me know if more clues needed from my environment settings.The account that executes the scheduled job does not have the same permissions as you. You need the job to be run with an account with the necessary permissions (same as your perhaps) which also has a non-changing password.|||I had a similar problem which was caused by the account used by the sqlagent service. I changed it to a domain account with the appropriate privs, and the problem was resolved. Whey you exec the DTS job via the designer, it is using your credentials vs. the agent's credentials.|||thank u both for yr reply...
what do u mean by change it to domain account? i tried to start up the sql server agent using THIS ACCOUNT, which taking sql id that i created as sysadm and the name and password is the same username/password i log on to the server. the reason to do this to make the job using the same logon id as i run it thru dts designer. but now i am getting this error:
DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: Error opening datafile: Logon failure: unknown user name or bad password. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 Error Detail Records: Error: 1326 (52E); Provider Error: 1326 (52E) Error string: Error opening datafile: Logon failure: unknown user name or bad password. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
i think my problem now, i am not sure when running the dts thru job, from where the job is taking username/password to connect to the remote server location. appreciate further help...thnks much.|||hi again...
i came across some suggestions on the net like this:
Make sure that the SQL Agent uses the same account as SQL Server service.
how to check the SQL Server service account?
really need yr expertise here..thanks.|||hi again...
i came across some suggestions on the net like this:
Make sure that the SQL Agent uses the same account as SQL Server service.
how to check the SQL Server service account?
really need yr expertise here..thanks.
start -> run -type 'services.msc'
Find the SQL Server and SQL Agent services, right click, properties, and go to the login tab.|||start -> run -type 'services.msc'
Find the SQL Server and SQL Agent services, right click, properties, and go to the login tab.
hi again...
i already changed the sql services to use logon username/password to be the same as sql agent. means both sql agent and sql services are using domain account now, no longer system account. but unfortunately, i am stil getting the same error...
anymore ideas can help me out here? please please...
tqvm.|||hi all again...
maybe i forgot to tell that my remote server i am connecting to is UNIX server (is that made a difference anyway?). well...just to test out, i just gave it a try to point to another remote server running on Windows. i shared the same folder and give sufficient privs while my sql server is stil using same domain account for sql agent as well as MSSQLSERVER services. then i run the job, it run successfully!
1.now i wld like to ask yr expertise here to guide me - how do we share a folder in UNIX to enable another server to connect to it? right now i just give permission read/write/execute to the folder. and it seems giving the above error - logon error/bad username or bad password.
2.how do we give permission to a server running on local? (not on network). the reason i asked is - normally its easy to give permission to some users on same network. is it by IP will work?
hope u all out there can help! thanks...really appreciate yr help.
i posted this somewhere else but i think here is the right group.
i have a dts that shld write to a text file located at a mapped drive. i read somewhere that sql job does not recognise mapped drive, so i use UNC path in form \\128.1.1.1\dtsfile, which dtsfile folder is the shared folder name. i opened the folder permission to everyone.
thats abt the remote server. the sql server i am running is on windows NT, logon using Administrator to local. my sql is SQL 2000. i register the server using sa username. the server agent is start up using System account.
now the problem is when i run the dts interactively/manually, it runs succesfully. but when i run it thru SQL job it says "Access denied". Or full error is like this,
DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 Error Detail Records: Error: 5 (5); Provider Error: 5 (5) Error string: Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
i really hope and appreciate if someone out there can help me out. thanks!
ps: pls let me know if more clues needed from my environment settings.The account that executes the scheduled job does not have the same permissions as you. You need the job to be run with an account with the necessary permissions (same as your perhaps) which also has a non-changing password.|||I had a similar problem which was caused by the account used by the sqlagent service. I changed it to a domain account with the appropriate privs, and the problem was resolved. Whey you exec the DTS job via the designer, it is using your credentials vs. the agent's credentials.|||thank u both for yr reply...
what do u mean by change it to domain account? i tried to start up the sql server agent using THIS ACCOUNT, which taking sql id that i created as sysadm and the name and password is the same username/password i log on to the server. the reason to do this to make the job using the same logon id as i run it thru dts designer. but now i am getting this error:
DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: Error opening datafile: Logon failure: unknown user name or bad password. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 Error Detail Records: Error: 1326 (52E); Provider Error: 1326 (52E) Error string: Error opening datafile: Logon failure: unknown user name or bad password. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
i think my problem now, i am not sure when running the dts thru job, from where the job is taking username/password to connect to the remote server location. appreciate further help...thnks much.|||hi again...
i came across some suggestions on the net like this:
Make sure that the SQL Agent uses the same account as SQL Server service.
how to check the SQL Server service account?
really need yr expertise here..thanks.|||hi again...
i came across some suggestions on the net like this:
Make sure that the SQL Agent uses the same account as SQL Server service.
how to check the SQL Server service account?
really need yr expertise here..thanks.
start -> run -type 'services.msc'
Find the SQL Server and SQL Agent services, right click, properties, and go to the login tab.|||start -> run -type 'services.msc'
Find the SQL Server and SQL Agent services, right click, properties, and go to the login tab.
hi again...
i already changed the sql services to use logon username/password to be the same as sql agent. means both sql agent and sql services are using domain account now, no longer system account. but unfortunately, i am stil getting the same error...
anymore ideas can help me out here? please please...
tqvm.|||hi all again...
maybe i forgot to tell that my remote server i am connecting to is UNIX server (is that made a difference anyway?). well...just to test out, i just gave it a try to point to another remote server running on Windows. i shared the same folder and give sufficient privs while my sql server is stil using same domain account for sql agent as well as MSSQLSERVER services. then i run the job, it run successfully!
1.now i wld like to ask yr expertise here to guide me - how do we share a folder in UNIX to enable another server to connect to it? right now i just give permission read/write/execute to the folder. and it seems giving the above error - logon error/bad username or bad password.
2.how do we give permission to a server running on local? (not on network). the reason i asked is - normally its easy to give permission to some users on same network. is it by IP will work?
hope u all out there can help! thanks...really appreciate yr help.
Friday, February 17, 2012
DTS will not send attachment using sp_send_cdosysmail
I have created a DTS that exports the results of a query to an excel
sheet and the delivers the xls to a group of users.
The DTS will send the the email but not the attachment; however, if I
execute the same statement using QA, the attachment works fine. I have
the DTS authenticating to the location of the file using xp_cmdshell
earlier in the process.
Below is the SQL statement, I am using:
DECLARE @.Body varchar(4000)
DECLARE @.EmailUsers VARCHAR(1024)
DECLARE @.Attmt VARCHAR(1024)
SELECT @.Body = 'Attached is the most recent CustCD customer
listing.'
SELECT @.EmailUsers = COALESCE(@.EmailUsers + ';', '') + UserEmail
FROM EmailUsers
WHERE (CustCD = 1)
SELECT @.Attmt='\\Server1\C$\c3.xls'
EXEC sp_send_cdosysmail 'abc@.abc.com', @.EmailUsers, 'CustCD
XLS',@.Body, @.Attmt
Does anyone have any ideas?
Thanks,
Chris
Assuming that the connection in your Execute SQL Task uses a database in
which the sp_send_cdosysmail is not defined, you might want to try to use a
three-part name for calling the sp_send_cdosysmail e.g. EXEC
master.dbo.sp_send_cdosysmail ...
-- Oskar
"ChrisP" wrote:
> I have created a DTS that exports the results of a query to an excel
> sheet and the delivers the xls to a group of users.
> The DTS will send the the email but not the attachment; however, if I
> execute the same statement using QA, the attachment works fine. I have
> the DTS authenticating to the location of the file using xp_cmdshell
> earlier in the process.
> Below is the SQL statement, I am using:
> DECLARE @.Body varchar(4000)
> DECLARE @.EmailUsers VARCHAR(1024)
> DECLARE @.Attmt VARCHAR(1024)
> SELECT @.Body = 'Attached is the most recent CustCD customer
> listing.'
> SELECT @.EmailUsers = COALESCE(@.EmailUsers + ';', '') + UserEmail
> FROM EmailUsers
> WHERE (CustCD = 1)
> SELECT @.Attmt='\\Server1\C$\c3.xls'
> EXEC sp_send_cdosysmail 'abc@.abc.com', @.EmailUsers, 'CustCD
> XLS',@.Body, @.Attmt
> Does anyone have any ideas?
> Thanks,
> Chris
>
|||If the suggestion below does not help you could try to set on the logging for
the package and check if the UNC path is indeed accessible by executing for
example EXEC master.dbo.xp_cmdshell 'dir \\Server1\C$\'
-- Oskar
"Oskar" wrote:
[vbcol=seagreen]
> Assuming that the connection in your Execute SQL Task uses a database in
> which the sp_send_cdosysmail is not defined, you might want to try to use a
> three-part name for calling the sp_send_cdosysmail e.g. EXEC
> master.dbo.sp_send_cdosysmail ...
> -- Oskar
> "ChrisP" wrote:
sheet and the delivers the xls to a group of users.
The DTS will send the the email but not the attachment; however, if I
execute the same statement using QA, the attachment works fine. I have
the DTS authenticating to the location of the file using xp_cmdshell
earlier in the process.
Below is the SQL statement, I am using:
DECLARE @.Body varchar(4000)
DECLARE @.EmailUsers VARCHAR(1024)
DECLARE @.Attmt VARCHAR(1024)
SELECT @.Body = 'Attached is the most recent CustCD customer
listing.'
SELECT @.EmailUsers = COALESCE(@.EmailUsers + ';', '') + UserEmail
FROM EmailUsers
WHERE (CustCD = 1)
SELECT @.Attmt='\\Server1\C$\c3.xls'
EXEC sp_send_cdosysmail 'abc@.abc.com', @.EmailUsers, 'CustCD
XLS',@.Body, @.Attmt
Does anyone have any ideas?
Thanks,
Chris
Assuming that the connection in your Execute SQL Task uses a database in
which the sp_send_cdosysmail is not defined, you might want to try to use a
three-part name for calling the sp_send_cdosysmail e.g. EXEC
master.dbo.sp_send_cdosysmail ...
-- Oskar
"ChrisP" wrote:
> I have created a DTS that exports the results of a query to an excel
> sheet and the delivers the xls to a group of users.
> The DTS will send the the email but not the attachment; however, if I
> execute the same statement using QA, the attachment works fine. I have
> the DTS authenticating to the location of the file using xp_cmdshell
> earlier in the process.
> Below is the SQL statement, I am using:
> DECLARE @.Body varchar(4000)
> DECLARE @.EmailUsers VARCHAR(1024)
> DECLARE @.Attmt VARCHAR(1024)
> SELECT @.Body = 'Attached is the most recent CustCD customer
> listing.'
> SELECT @.EmailUsers = COALESCE(@.EmailUsers + ';', '') + UserEmail
> FROM EmailUsers
> WHERE (CustCD = 1)
> SELECT @.Attmt='\\Server1\C$\c3.xls'
> EXEC sp_send_cdosysmail 'abc@.abc.com', @.EmailUsers, 'CustCD
> XLS',@.Body, @.Attmt
> Does anyone have any ideas?
> Thanks,
> Chris
>
|||If the suggestion below does not help you could try to set on the logging for
the package and check if the UNC path is indeed accessible by executing for
example EXEC master.dbo.xp_cmdshell 'dir \\Server1\C$\'
-- Oskar
"Oskar" wrote:
[vbcol=seagreen]
> Assuming that the connection in your Execute SQL Task uses a database in
> which the sp_send_cdosysmail is not defined, you might want to try to use a
> three-part name for calling the sp_send_cdosysmail e.g. EXEC
> master.dbo.sp_send_cdosysmail ...
> -- Oskar
> "ChrisP" wrote:
DTS will not send attachment using sp_send_cdosysmail
I have created a DTS that exports the results of a query to an excel
sheet and the delivers the xls to a group of users.
The DTS will send the the email but not the attachment; however, if I
execute the same statement using QA, the attachment works fine. I have
the DTS authenticating to the location of the file using xp_cmdshell
earlier in the process.
Below is the SQL statement, I am using:
DECLARE @.Body varchar(4000)
DECLARE @.EmailUsers VARCHAR(1024)
DECLARE @.Attmt VARCHAR(1024)
SELECT @.Body = 'Attached is the most recent CustCD customer
listing.'
SELECT @.EmailUsers = COALESCE(@.EmailUsers + ';', '') + UserEmail
FROM EmailUsers
WHERE (CustCD = 1)
SELECT @.Attmt='\\Server1\C$\c3.xls'
EXEC sp_send_cdosysmail 'abc@.abc.com', @.EmailUsers, 'CustCD
XLS',@.Body, @.Attmt
Does anyone have any ideas?
Thanks,
ChrisAssuming that the connection in your Execute SQL Task uses a database in
which the sp_send_cdosysmail is not defined, you might want to try to use a
three-part name for calling the sp_send_cdosysmail e.g. EXEC
master.dbo.sp_send_cdosysmail ...
-- Oskar
"ChrisP" wrote:
> I have created a DTS that exports the results of a query to an excel
> sheet and the delivers the xls to a group of users.
> The DTS will send the the email but not the attachment; however, if I
> execute the same statement using QA, the attachment works fine. I have
> the DTS authenticating to the location of the file using xp_cmdshell
> earlier in the process.
> Below is the SQL statement, I am using:
> DECLARE @.Body varchar(4000)
> DECLARE @.EmailUsers VARCHAR(1024)
> DECLARE @.Attmt VARCHAR(1024)
> SELECT @.Body = 'Attached is the most recent CustCD customer
> listing.'
> SELECT @.EmailUsers = COALESCE(@.EmailUsers + ';', '') + UserEmail
> FROM EmailUsers
> WHERE (CustCD = 1)
> SELECT @.Attmt='\\Server1\C$\c3.xls'
> EXEC sp_send_cdosysmail 'abc@.abc.com', @.EmailUsers, 'CustCD
> XLS',@.Body, @.Attmt
> Does anyone have any ideas?
> Thanks,
> Chris
>|||If the suggestion below does not help you could try to set on the logging fo
r
the package and check if the UNC path is indeed accessible by executing for
example EXEC master.dbo.xp_cmdshell 'dir \\Server1\C$'
-- Oskar
"Oskar" wrote:
[vbcol=seagreen]
> Assuming that the connection in your Execute SQL Task uses a database in
> which the sp_send_cdosysmail is not defined, you might want to try to use
a
> three-part name for calling the sp_send_cdosysmail e.g. EXEC
> master.dbo.sp_send_cdosysmail ...
> -- Oskar
> "ChrisP" wrote:
>
sheet and the delivers the xls to a group of users.
The DTS will send the the email but not the attachment; however, if I
execute the same statement using QA, the attachment works fine. I have
the DTS authenticating to the location of the file using xp_cmdshell
earlier in the process.
Below is the SQL statement, I am using:
DECLARE @.Body varchar(4000)
DECLARE @.EmailUsers VARCHAR(1024)
DECLARE @.Attmt VARCHAR(1024)
SELECT @.Body = 'Attached is the most recent CustCD customer
listing.'
SELECT @.EmailUsers = COALESCE(@.EmailUsers + ';', '') + UserEmail
FROM EmailUsers
WHERE (CustCD = 1)
SELECT @.Attmt='\\Server1\C$\c3.xls'
EXEC sp_send_cdosysmail 'abc@.abc.com', @.EmailUsers, 'CustCD
XLS',@.Body, @.Attmt
Does anyone have any ideas?
Thanks,
ChrisAssuming that the connection in your Execute SQL Task uses a database in
which the sp_send_cdosysmail is not defined, you might want to try to use a
three-part name for calling the sp_send_cdosysmail e.g. EXEC
master.dbo.sp_send_cdosysmail ...
-- Oskar
"ChrisP" wrote:
> I have created a DTS that exports the results of a query to an excel
> sheet and the delivers the xls to a group of users.
> The DTS will send the the email but not the attachment; however, if I
> execute the same statement using QA, the attachment works fine. I have
> the DTS authenticating to the location of the file using xp_cmdshell
> earlier in the process.
> Below is the SQL statement, I am using:
> DECLARE @.Body varchar(4000)
> DECLARE @.EmailUsers VARCHAR(1024)
> DECLARE @.Attmt VARCHAR(1024)
> SELECT @.Body = 'Attached is the most recent CustCD customer
> listing.'
> SELECT @.EmailUsers = COALESCE(@.EmailUsers + ';', '') + UserEmail
> FROM EmailUsers
> WHERE (CustCD = 1)
> SELECT @.Attmt='\\Server1\C$\c3.xls'
> EXEC sp_send_cdosysmail 'abc@.abc.com', @.EmailUsers, 'CustCD
> XLS',@.Body, @.Attmt
> Does anyone have any ideas?
> Thanks,
> Chris
>|||If the suggestion below does not help you could try to set on the logging fo
r
the package and check if the UNC path is indeed accessible by executing for
example EXEC master.dbo.xp_cmdshell 'dir \\Server1\C$'
-- Oskar
"Oskar" wrote:
[vbcol=seagreen]
> Assuming that the connection in your Execute SQL Task uses a database in
> which the sp_send_cdosysmail is not defined, you might want to try to use
a
> three-part name for calling the sp_send_cdosysmail e.g. EXEC
> master.dbo.sp_send_cdosysmail ...
> -- Oskar
> "ChrisP" wrote:
>
DTS will not send attachment using sp_send_cdosysmail
I have created a DTS that exports the results of a query to an excel
sheet and the delivers the xls to a group of users.
The DTS will send the the email but not the attachment; however, if I
execute the same statement using QA, the attachment works fine. I have
the DTS authenticating to the location of the file using xp_cmdshell
earlier in the process.
Below is the SQL statement, I am using:
DECLARE @.Body varchar(4000)
DECLARE @.EmailUsers VARCHAR(1024)
DECLARE @.Attmt VARCHAR(1024)
SELECT @.Body = 'Attached is the most recent CustCD customer
listing.'
SELECT @.EmailUsers = COALESCE(@.EmailUsers + ';', '') + UserEmail
FROM EmailUsers
WHERE (CustCD = 1)
SELECT @.Attmt='\\Server1\C$\c3.xls'
EXEC sp_send_cdosysmail 'abc@.abc.com', @.EmailUsers, 'CustCD
XLS',@.Body, @.Attmt
Does anyone have any ideas?
Thanks,
ChrisAssuming that the connection in your Execute SQL Task uses a database in
which the sp_send_cdosysmail is not defined, you might want to try to use a
three-part name for calling the sp_send_cdosysmail e.g. EXEC
master.dbo.sp_send_cdosysmail ...
-- Oskar
"ChrisP" wrote:
> I have created a DTS that exports the results of a query to an excel
> sheet and the delivers the xls to a group of users.
> The DTS will send the the email but not the attachment; however, if I
> execute the same statement using QA, the attachment works fine. I have
> the DTS authenticating to the location of the file using xp_cmdshell
> earlier in the process.
> Below is the SQL statement, I am using:
> DECLARE @.Body varchar(4000)
> DECLARE @.EmailUsers VARCHAR(1024)
> DECLARE @.Attmt VARCHAR(1024)
> SELECT @.Body = 'Attached is the most recent CustCD customer
> listing.'
> SELECT @.EmailUsers = COALESCE(@.EmailUsers + ';', '') + UserEmail
> FROM EmailUsers
> WHERE (CustCD = 1)
> SELECT @.Attmt='\\Server1\C$\c3.xls'
> EXEC sp_send_cdosysmail 'abc@.abc.com', @.EmailUsers, 'CustCD
> XLS',@.Body, @.Attmt
> Does anyone have any ideas?
> Thanks,
> Chris
>|||If the suggestion below does not help you could try to set on the logging for
the package and check if the UNC path is indeed accessible by executing for
example EXEC master.dbo.xp_cmdshell 'dir \\Server1\C$\'
-- Oskar
"Oskar" wrote:
> Assuming that the connection in your Execute SQL Task uses a database in
> which the sp_send_cdosysmail is not defined, you might want to try to use a
> three-part name for calling the sp_send_cdosysmail e.g. EXEC
> master.dbo.sp_send_cdosysmail ...
> -- Oskar
> "ChrisP" wrote:
> > I have created a DTS that exports the results of a query to an excel
> > sheet and the delivers the xls to a group of users.
> > The DTS will send the the email but not the attachment; however, if I
> > execute the same statement using QA, the attachment works fine. I have
> > the DTS authenticating to the location of the file using xp_cmdshell
> > earlier in the process.
> > Below is the SQL statement, I am using:
> > DECLARE @.Body varchar(4000)
> > DECLARE @.EmailUsers VARCHAR(1024)
> > DECLARE @.Attmt VARCHAR(1024)
> > SELECT @.Body = 'Attached is the most recent CustCD customer
> > listing.'
> > SELECT @.EmailUsers = COALESCE(@.EmailUsers + ';', '') + UserEmail
> > FROM EmailUsers
> > WHERE (CustCD = 1)
> > SELECT @.Attmt='\\Server1\C$\c3.xls'
> > EXEC sp_send_cdosysmail 'abc@.abc.com', @.EmailUsers, 'CustCD
> > XLS',@.Body, @.Attmt
> >
> > Does anyone have any ideas?
> > Thanks,
> > Chris
> >
> >
sheet and the delivers the xls to a group of users.
The DTS will send the the email but not the attachment; however, if I
execute the same statement using QA, the attachment works fine. I have
the DTS authenticating to the location of the file using xp_cmdshell
earlier in the process.
Below is the SQL statement, I am using:
DECLARE @.Body varchar(4000)
DECLARE @.EmailUsers VARCHAR(1024)
DECLARE @.Attmt VARCHAR(1024)
SELECT @.Body = 'Attached is the most recent CustCD customer
listing.'
SELECT @.EmailUsers = COALESCE(@.EmailUsers + ';', '') + UserEmail
FROM EmailUsers
WHERE (CustCD = 1)
SELECT @.Attmt='\\Server1\C$\c3.xls'
EXEC sp_send_cdosysmail 'abc@.abc.com', @.EmailUsers, 'CustCD
XLS',@.Body, @.Attmt
Does anyone have any ideas?
Thanks,
ChrisAssuming that the connection in your Execute SQL Task uses a database in
which the sp_send_cdosysmail is not defined, you might want to try to use a
three-part name for calling the sp_send_cdosysmail e.g. EXEC
master.dbo.sp_send_cdosysmail ...
-- Oskar
"ChrisP" wrote:
> I have created a DTS that exports the results of a query to an excel
> sheet and the delivers the xls to a group of users.
> The DTS will send the the email but not the attachment; however, if I
> execute the same statement using QA, the attachment works fine. I have
> the DTS authenticating to the location of the file using xp_cmdshell
> earlier in the process.
> Below is the SQL statement, I am using:
> DECLARE @.Body varchar(4000)
> DECLARE @.EmailUsers VARCHAR(1024)
> DECLARE @.Attmt VARCHAR(1024)
> SELECT @.Body = 'Attached is the most recent CustCD customer
> listing.'
> SELECT @.EmailUsers = COALESCE(@.EmailUsers + ';', '') + UserEmail
> FROM EmailUsers
> WHERE (CustCD = 1)
> SELECT @.Attmt='\\Server1\C$\c3.xls'
> EXEC sp_send_cdosysmail 'abc@.abc.com', @.EmailUsers, 'CustCD
> XLS',@.Body, @.Attmt
> Does anyone have any ideas?
> Thanks,
> Chris
>|||If the suggestion below does not help you could try to set on the logging for
the package and check if the UNC path is indeed accessible by executing for
example EXEC master.dbo.xp_cmdshell 'dir \\Server1\C$\'
-- Oskar
"Oskar" wrote:
> Assuming that the connection in your Execute SQL Task uses a database in
> which the sp_send_cdosysmail is not defined, you might want to try to use a
> three-part name for calling the sp_send_cdosysmail e.g. EXEC
> master.dbo.sp_send_cdosysmail ...
> -- Oskar
> "ChrisP" wrote:
> > I have created a DTS that exports the results of a query to an excel
> > sheet and the delivers the xls to a group of users.
> > The DTS will send the the email but not the attachment; however, if I
> > execute the same statement using QA, the attachment works fine. I have
> > the DTS authenticating to the location of the file using xp_cmdshell
> > earlier in the process.
> > Below is the SQL statement, I am using:
> > DECLARE @.Body varchar(4000)
> > DECLARE @.EmailUsers VARCHAR(1024)
> > DECLARE @.Attmt VARCHAR(1024)
> > SELECT @.Body = 'Attached is the most recent CustCD customer
> > listing.'
> > SELECT @.EmailUsers = COALESCE(@.EmailUsers + ';', '') + UserEmail
> > FROM EmailUsers
> > WHERE (CustCD = 1)
> > SELECT @.Attmt='\\Server1\C$\c3.xls'
> > EXEC sp_send_cdosysmail 'abc@.abc.com', @.EmailUsers, 'CustCD
> > XLS',@.Body, @.Attmt
> >
> > Does anyone have any ideas?
> > Thanks,
> > Chris
> >
> >
Subscribe to:
Posts (Atom)