I have loaded up the newest latests, greatest Visual Studio, SQL reporting etc. I have a need to pull in data from multiple sources. Currently I have DTS packages I have to run to dump information into 1 data source to report on. Has anybody figured out a way to have multipled data sources available when in the query builder?
I am scratching my head on this one.
Hello,
I am currently working with SQL 2005 Reporting Services and MS Visual studio 2005.
In my SQL server I have data about organizations, so that each organization's data is stored in an individual database.
In addition, the number of organizations/databases is variable. I know the number of organizations and the name of each one (that it agrees with the name of its database)
because there is a database (organizationsDB) with a table (organizationsTB) containing the list of the organizations.
So, It would be possible to create a report containing a summary of each organization? .... for example a table in which each row contains data of each organization.
Could you give me any ideas?
Thanks in advance!
Hi,
I have got a quistion in regards to SQL Server Reporting Services 2005. We're currently using the SSRS in one of our applciations with 20+ reports. We need to deplaoy 2 instances of an applciation: Test and Production. These applcaitions are running off two different databases in SQL Server [DBName]_Test and [DBName]_Live. So far all of our reports have been written to use a shared datasource. This worked fine untill we had to deploy a Live instance of the application, where the shared datasource is overwritten with the live one.
Now we have manually created a new shared datasource called [DataSource]_Live. However, whenever we need to redeploy the reports from visual studio, we then have to go though each report and point it to a correct datasource. This is extremly manual process - and takes up a lot of time...
Surely there must be an easier way around this (without duplicating the reporting project in VS.NET / or having to modify the datasource fr each report manually each time)?
Thanks in advance,
Nick Goloborodko
Is it possible to use a ReportViewer control (or something in .net) to change connection strings of a report's datasources?
The connection string comes from a shared data source, so using an expression for the connection string in the actual report is out.
Essentially, I'm looking to tack on Analysis Services cube role(s) to the connection string to change data permissions. If there's another way to do this with a RS report, I'm all ears.
I've got a report model I am trying to move from a development server to a production server. I published it to my development RS, exported the SMDL file, then uploaded it to the production server. At that point, I tried assigning the data source to the model and got the error:
View 12 Replies View RelatedI am new to reporting services, working with RS2000. Roles and security have previously been set up.
Shared Datasources have been created. How is access granted to shared datasources?
Some users have access but we haven't been able to create new users with access to the shared datasources.
Thanks
One being a query which sums up claims for a group of members. Col A beign the memberID and B and C being amounts.
Now I set the output to Isosrted = true and set the output column A sorted property to 1.
The other OLE source is a table. Col A being member and b,c,d.. being name, address...
I set the same properties for column a and I am trying to use a merge join.
The first two sources return 2245 rows and after the merge I end up with 648.
What would cause the merge to strip some records out?
Thanks.
I have several SSIS packages which I have setup using a datasource to connect to the database. I should be able to switch between databases by just changing the server and database on the datasource but for some reason the ssis package keeps on using the old database. I have the connection managers setup to reference the datasource and can see the connection manager pointing to the new database but when I run a task by right clicking on it and choosing execute task it calls the stored procedure in the old database. I know it is doing this because I renamed the stored procedure in the old database and the task errored out saying it could not find the stored procedure. Can someone please tell me what I am doing wrong?
View 4 Replies View RelatedI need to set a Repeater.Datasource to one of three stored procedures depending on what button the user selects. How do I set it up please?<asp:SqlDataSource ID="SqlDataSource5" runat="server" ConnectionString="<%$ ConnectionStrings:ABCConnectionString %>"SelectCommand="???????????????" SelectCommandType="StoredProcedure"><SelectParameters><asp:ControlParameter ControlID="???????????????????" Name="?????????????" PropertyName="Text" Type="string" /></SelectParameters></asp:SqlDataSource>
protected void btnKeyWordSearch_Click(object sender, ImageClickEventArgs e){ mode = 1; StoredProcdureName = "KWSearch"; KWords = tbxKWordText.Text;}protected void btnCompanyNameSearch_Click(object sender, ImageClickEventArgs e){ mode = 1; StoredProcdureName = "NAMESearch"; CoName = tbxCoNameText.Text;}protected void btnBrandNameSearch_Click(object sender, ImageClickEventArgs e){ mode = 1; StoredProcdureName = "BRANDSearch"; Brand = tbxBrandText.Text;}
Does anyone know of a tool or method that would allow me to join MS SQL and Oracle 7.3 tables for a query?
View 2 Replies View RelatedI have discovered some shortcomings in the way inline table valued function parameters are treated in the OLEDB datasource. You can select the user designed function ine the Generic Query Builder and test it with the required parameters. However when you attempt to set up the parameters for the result ing SQL Command Text you get and error message to the effect that the parameters cannot be retrieved from the datasource. Once again this is disappointing because Report Services seems to deal with the parameters perfectly well.
Dick Campbell
Get this when selecting parameter value in Report Manager
Report is working fine in preview
Up to me, it has nothing to do with credentials
I have some analysis server reports.
I have a single value report parameter, the report runs fine with the default value, when I select another value than the default one, I get above message immediately.
I got the error since I rebuild the report with SP2 CTP.
Strange thing is that the other single value parameter of the report works fine (year)
And that I get the error also with other reports, and on the same dimension (Organisation)
And that I do not have the error when I change the parameter to multi-value (but as the report is not written for multiple values...)
MDX queries are as follows:
SELECT NON EMPTY { KPIValue("KPINewBiz"), KPIGoal("KPINewBiz"), KPIStatus("KPINewBiz"), KPITrend("KPINewBiz") } ON COLUMNS,
NONEMPTY
([Reps].[Organisation].[Organisation].ALLMEMBERS
*[Reps].[Rep].[Rep].ALLMEMBERS
*[Date].[Year].[Year].ALLMEMBERS
*[Date].[Month].[Month].ALLMEMBERS
,[Measures].[Gross Sales]
)
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT ( STRTOSET(@ByYear, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@ByOrganisation, CONSTRAINED) ) ON COLUMNS FROM [Sales])) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
WITH MEMBER [Measures].[ParameterCaption] AS '[Reps].[Organisation].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Reps].[Organisation].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Reps].[Organisation].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,
NONEMPTY([Reps].[Organisation].[Organisation].ALLMEMBERS,[Measures].[Gross Sales]) ON ROWS FROM [Sales]
WITH MEMBER [Measures].[ParameterCaption] AS '[Date].[Year].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Date].[Year].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Date].[Year].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , NONEMPTY([Date].[Year].[Year].ALLMEMBERS,[Measures].[Gross Sales]) ON ROWS FROM [Sales]
Discovered something else that is very strange, you change the value of the parameter, you get the error, you hit the back button, and you can run the report with the new parameter
Did one more test, changed the order of the parameters, is now going wrong on the other parameter, Year.
So, it is always on the first parameter.
Correction, I have put a multi-value parameter as first, I still have the problem, it is always on the first single value parameter
I have the same problem.
Up to me, it has nothing to do with credentials
I have some analysis server reports.
I have a single value report parameter, the report runs fine with the default value, when I select another value than the default one, I get above message immediately.
I got the error since I rebuild the report with SP2 CTP.
Strange thing is that the other single value parameter of the report works fine (year)
And that I get the error also with other reports, and on the same dimension (Organisation)
And that I do not have the error when I change the parameter to multi-value (but as the report is not written for multiple values...)
MDX queries are as follows:
SELECT NON EMPTY { KPIValue("KPINewBiz"), KPIGoal("KPINewBiz"), KPIStatus("KPINewBiz"), KPITrend("KPINewBiz") } ON COLUMNS,
NONEMPTY
([Reps].[Organisation].[Organisation].ALLMEMBERS
*[Reps].[Rep].[Rep].ALLMEMBERS
*[Date].[Year].[Year].ALLMEMBERS
*[Date].[Month].[Month].ALLMEMBERS
,[Measures].[Gross Sales]
)
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT ( STRTOSET(@ByYear, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@ByOrganisation, CONSTRAINED) ) ON COLUMNS FROM [Sales])) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
WITH MEMBER [Measures].[ParameterCaption] AS '[Reps].[Organisation].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Reps].[Organisation].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Reps].[Organisation].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,
NONEMPTY([Reps].[Organisation].[Organisation].ALLMEMBERS,[Measures].[Gross Sales]) ON ROWS FROM [Sales]
WITH MEMBER [Measures].[ParameterCaption] AS '[Date].[Year].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Date].[Year].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Date].[Year].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , NONEMPTY([Date].[Year].[Year].ALLMEMBERS,[Measures].[Gross Sales]) ON ROWS FROM [Sales]
Discovered something else that is very strange, you change the value of the parameter, you get the error, you hit the back button, and you can run the report with the new parameter
is there a way to bring in oracle and sql tables into the same report and join them together on common fields? also, if this is possible, i am trying to join them in the native UI and not the generic query designer mode.
i am hoping that through fully qualifying the tables as you bring them in, that this may be possible.
any suggesions would be greatly appreciated...
thanks!
-dk
Hi,
I have to design a tabular report where the data in all the columns of the table comes from the cube, but for one column where the data comes from the ODS table.
Could anyone please let me know if it is possible to combine the results of the query from a cube with query from an ODS table and display together in a single report?
Any thoughts on this would be greatly appreciated
Thanks.
Brand new at this !
I'm running VB 2005 express edition installed from full MS download which included the SQL2005 express server V9.00.1116
Have used the Video tutorial (video 8 & 9) to create a database and have followed instructiions faithfully
Problem.
From the Data / Add New Datasource menu everything proceeds ok - that is a "dataset.xsd" file is created but then I get an error message
" Could not get type information for .........myfilename dataset "
and so the process is not completed properly and a datasource is not created.
I'm unsure what the type information is and have been unsuccesful turning up anything from the help files or forum so hoping someone can maybe tell me whats happening and how to fix this.
Thanks
Hi,I have two SQLDataSources called "LeagueTableHome" an "LeagueTableAway" on my page.
I want to create another SQLDataSource called "LeagueTableTotal" on my page which adds up all the totals from each of the other two sources.
The datasource looks like this:
Team, Pld, W, D, L, F, A, Agg, Pts
my code for LeagueTableHome looks like this:
SELECT HomeTeam, 1 AS Pld, CASE WHEN HomeScore > AwayScore THEN 1 ELSE 0 END AS Won, CASE WHEN HomeScore = AwayScore THEN 1 ELSE 0 END AS Draw, CASE WHEN HomeScore < AwayScore THEN 1 ELSE 0 END AS Lost, HomeScore AS Scored, AwayScore AS Against, HomeScore - AwayScore AS Agg, CASE WHEN HomeScore > AwayScore THEN 3 ELSE 0 END AS Pts FROM tblFixtures WHERE (CompID = 1) AND (HomeScore IS NOT NULL)
I want then to show LeagueTableTotal in a GridView.
Can anybody help?
Hi all. Can you help me? I'm trying to build a custom component that recieves two datasources (like for instance the union all) . I first started by adding a new IDTSInput90 in the ProvideComponentProperties, but when I tried to use the component I got an error that has very helpful :
===================================
The component could not be added to the Data Flow task.
Could not initialize the component. There is a potential problem in the ProvideComponentProperties method. (Microsoft Visual Studio)
===================================
Error at Data Flow Task [Replica Transformation [1289]]: System.Runtime.InteropServices.COMException (0xC0048004): Exception from HRESULT: 0xC0048004
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSOutputCollection90.get_Item(Object Index)
at MyCustomSSISComponent.SampleComponentComponent.ProvideComponentProperties()
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProvideComponentProperties(IDTSManagedComponentWrapper90 wrapper)
===================================
Exception from HRESULT: 0xC0048004 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HandleUserException(Exception e)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProvideComponentProperties(IDTSManagedComponentWrapper90 wrapper)
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ProvideComponentProperties()
at Microsoft.DataTransformationServices.Design.PipelineTaskDesigner.AddNewComponent(String clsid, Boolean throwOnError)
This is my ProvideComponentProperties:
Code Snippet
public override void ProvideComponentProperties()
{
RemoveAllInputsOutputsAndCustomProperties();
ComponentMetaData.UsesDispositions = true;
IDTSInput90 input = ComponentMetaData.InputCollection.New();
input.Name = "Staging Data";
input.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;
IDTSInput90 input2 = ComponentMetaData.InputCollection.New();
input2.Name = "Replica Data";
input2.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;
// Add the output
IDTSOutput90 output = ComponentMetaData.OutputCollection.New();
output.Name = "Replica Output";
output.SynchronousInputID = input.ID;
output.ExclusionGroup = 1;
// Add the error output
AddErrorOutput("StagingErrorOutput", input.ID, output.ExclusionGroup);
// Adds columns
AddXmlColumn();
IDTSOutputColumn90 column0 = ComponentMetaData.OutputCollection[1].OutputColumnCollection.New();
column0.Name = m_SyncStatusColumnName;
column0.SetDataTypeProperties(DataType.DT_STR, 1, 0, 0, 1252);
IDTSOutputColumn90 column1 = ComponentMetaData.OutputCollection[2].OutputColumnCollection.New();
column1.Name = m_AS400ImportedDateColumnName;
column1.SetDataTypeProperties(DataType.DT_DATE, 0, 0, 0, 0);
}
Since I'm new to SSIS I'm following Josh's SSIS Xmlify Data Flow Task sample.
Thanks.
I need to create a report with variable datasources. Any one knows the best way to do that?
When I try creating variables on the select statement of the dataset it errors out.
I I trayng something like this: select * from @server.@database.dbo.errorlog but It does not recognize the variables. Any suggestions??
Hello,
we use SQL Server 2005 Reporting Services with XML Datasources, but we have the following issue :
with a xml like this :
<Root>
<A>a</A>
<B>b</B>
<L>Item 1</L>
<L>Item 2</L>
<C>c</C>
</Root>
If we use the query Root, the resulting dataset is :
A B C
a b c
But with this query : Root/L, the dataset is :
L A B
Item 1 a b
Item 2 a b
The dataset doesn't find the "C" node.
Anyone have an idea ?
PS : I don't control the xml structure, so I can't displace the "C" node.
We are having our SSRS (Sql Server Reporting Services) Servers deployed in Domain1 to connect to the Domain1 data sources. Now, when we configure the SSRS datasources (which is in Domain1) to run under €œDomain2/User€? credentials , it€™s taking 60 minutes to render a report whereas when we configure the SSRS datasources to run under €œDomain1/User€? account it€™s taking only 5 seconds.
I would like to know €œWhy there is a latency difference while configuring the SSRS data sources with different domain credentials (Domain1Domain2)?€?
Any pointers to Support KB or any other related articles would be really great.
Hi everyone, I am using VS2005, just installed SP1, and the help -> about window reflects this. I have also installed the SQL Server Compact Edition SDK, but when i try to add a new data source connection (following the MANY tutorials on this), i do not see an option for SQL Server Compact Edition nor do i see an option for SQL Server Mobile. I am at my wits end and i just got started! Yikes... Any thoughts on how to proceed?
View 17 Replies View RelatedHi everybody,
The names of the tables that sould be transfered from the production system to the DWH are stored in a table in the production system. Yet I haven't found a proper way to dynamicly define these tables as DataSources and DataDestinations within an Integration Services project.
I hope somebody can help me. Thanks.
I can add more datasources and how can i frame my query based on the tables present in the various datasources?
View 5 Replies View Related
Hi All,
I have created fact tables and dimension tables in datawarehouse database, and i created a olap cube from those tables.
I want to run SSIS Package which populates these fact and dimension tables from datasources.
Thanks in advance,
Archana
Any fix for the seemingly random sort order of the variables in the dropdown list when configuring parameters and connection managers in the SSISDB catalog?
I imported all of our connection strings into an environment (about 200 of them). They were inserted in alpha order and the ID values within the internal.environment_variables table shows them in order as well, by ID and by name.
When I run profiler and capture the command that retrieves them and run it in ssms they are in order but in the dropdown they seem random.
There are no values within any of the tables that accounts for the order they are in.
If a package has 5 connections you need to go through the unsorted list 5 times to find them.
Sometimes you get lucky and they are in the first 20 or so.
I know I can write a script, just wondering if there is a fix for the sorting.
hi all,
i unpacked starter site and dw.pup file and i got 34 reports provided by commerce server 2007. i want to edit these reports.
i followed these steps
1)created a "Report server project" in Visual studio 2005
2)In that project in "Shared data sources" folders i added "Startersite_Datawarehouse" of type "Microsoft SQL Server"
3)i added another shared data source name "Startersite_Datawarehouse" of the type "Microsoft SQL services analysis services"
4)later i added one of the reports to "Reports" folder by selecting "Add existing item".
But in design mode of that report when i clicked "Preview" tab i am getting the error
"an error occured during the local report processing. The item /CSDW_olap cant be found"
when i clicked "data" tab i am getting this below error
"Connection cant be made to database"
please help me in resolving this urgent issue....
Thanks in advance,
Archana Devi Papineni
I can use the IN with the WHERE clause as example:SELECT * FROM NORTHWIND WHERE LASTNAME IN ('FULLER','KING')I want to use the IN and LIKE at the same time:SELECT * FROM NORTHWIND WHERE LASTNAME LIKE ('A%','B%')I know this is a simplistic example, but the members for the IN will bemany, 5 to 10.I'm trying to avoid:SELECT * FROM NORTHWIND WHERE LASTNAME LIKE 'A%' OR LASTNAME LIKE 'B%'OR LASTNAME LIKE 'FU%' OR LASTNAME LIKE 'JON%' <...>and so forth.Any Ideas?TIARob
View 3 Replies View RelatedHai All,
Could some one help me how to combine IN & LIKE in the query selection,
i'v try many syntac and i just have no find the way out
this is what I would like to write:
Select DSCRIPTN,ORMSTRID,ACTINDX
from T50001
where ACTINDX = 350
and DSCRIPTN Like in '%'+(select distinct rtrim(ORMSTRID) ORMSTRID from L10001)+'%'
Hi all,
I have a table with multiple rows with the same ID.
a) How do I combine all columns into one row with the same ID?
b) Is this better to do the combine in the store procedure/trigger or a sub that involked by some sort of datarepeater, datagrid controls?
Since I am trying to brush up my sql. I appreciate any examples on top of conceptual solution.
Thanks
Combine 3 Databases, Not tables.
Let me spell this out -- I have 3 databases (they are in isolation when in use, its a field app) that need to be merged into 1 "masterDB" database. I've discovered I can use something like this to get to each DB in a query...
1 USE [database1]2 SELECT table1.Name, table1.Location, table1.Date, table2.Blog3 FROM table2 INNER JOIN4 table1 ON table2.ID = table1.ID5 ORDER BY table1.Date
and then just repeat for database2 and database3. Ok, fine, rah rah. My question is how do I "merge" all of these into 1. No data on each db will be identical, at all, ever so that is not a concern. I just need all the data from db1, 2 and 3 into masterDB.
Ideas? Direction?
Hello, I have these variables on my page:
userid = "10101"
RequestHost = "example.com"
What would be the best way performace wise to first check if the userid 10101 exists in my sql server db. If it does exist I would then need to check if "example.com" exists for the userid in the userdomains table. If these both exist I would then like to query some additional data. I was hoping its possible to combine this into one query somehow. I dont think this is the best solution:
sqlcommand.CommandText = "SELECT UserId From Users Where UserID = '10101'"
Conn.Open()
dr = sqlcommand.ExecuteReader
if dr.hasrows then
sqlcommand2.CommandText = "SELECT UserDomain From UserDomains Where UserID = 'example.com'"
dr2 = sqlcommand2.ExecuteReader
if dr2.hasrows then
sqlcommand3.CommandText = 'Select Additional Data
dr3 = sqlcommand3.ExecuteReader
'read values
conn.close
else
conn.close
'do something
end if
else
conn.close
'do something
end if Thanks Very Much!
I have an stored procedure that returns 3 columns. Month, Date, and Total Number of Calls.
Here is the stored Proc:
SELECT DATEPART(mm, CALLSTARTTIME) , DATEPART(dd, CALLSTARTTIME), COUNT(*)
FROM CALL_LOG_MASTER
WHERE (COMMERCIALS = '1') AND (PINCODE IS NOT NULL)
GROUP BY DATEPART(mm, CALLSTARTTIME), DATEPART(dd, CALLSTARTTIME)
ORDER BY DATEPART(mm, CALLSTARTTIME), DATEPART(dd, CALLSTARTTIME)
It returns a table:
MONTH DATE TOTAL NUMBER OF CALLS======= ===== ===========1 1 10
1 2 15
My question is: is it possible to combine the Month and Date column into one column. e.g.
Date Total Number of Calls==== ==============1/1 101/2 15
Please Help, Thanks in advance :)
I am looking for the correct T-sql statement. I want to put parameters in a SP from a select statement. And make the SP exec for each records that the select statement returns. The following is the SP that I want to pass the parameters.
CREATE PROCEDURE sp_sendSMTPmail (@To varchar(8000),
@Subject varchar(255),
@Body text = null,
@Importance int = 1, -- 0=low, 1=normal, 2=high
@Cc varchar(8000) = null,
@Bcc varchar(8000) = null,
@Attachments varchar(8000) = null, -- delimeter is ;
@HTMLFormat int = 0,
@From varchar(255) = null)
/* Name: sp_sendSMTPmail
Purpose: Send an SMTP mail using CDONTS object.
Requirements: SMTP server (comes with IIS but doesn't require IIS) must be loaded.
Returns: 0 if successful, 1 if any errors
Sample Usage:
sp_sendSMTPmail 'vince.iacoboni@db.com', 'Testing', 'testing sp_sendSMTPmail, please reply if you receive this',
@cc='irmsqlmail@db.com',
@Importance=1,
@Attachments='c:oot.ini;c:autoexec.bat'
History:
02/07/2001 VRI Created.
*/
AS
SET NOCOUNT ON
DECLARE @object int,
@hr int,
@StrEnd int,
@Attachment varchar(255),
@return int,
@Msg varchar(255)
SELECT @From = isnull(@From, @@SERVERNAME)
-- Create the CDONTS NewMail object.
EXEC @hr = sp_OACreate 'CDONTS.NewMail', @object OUT
IF @hr <> 0 GOTO ObjectError
-- Add the optional properties if they are specified
IF @Body IS NOT NULL
BEGIN
EXEC @hr = sp_OASetProperty @object, 'Body', @Body
IF @hr <> 0 GOTO ObjectError
END
IF @Cc IS NOT NULL
BEGIN
EXEC @hr = sp_OASetProperty @object, 'Cc', @Cc
IF @hr <> 0 GOTO ObjectError
END
IF @Bcc IS NOT NULL
BEGIN
EXEC @hr = sp_OASetProperty @object, 'Bcc', @Bcc
IF @hr <> 0 GOTO ObjectError
END
IF @HTMLFormat <> 0
BEGIN
EXEC @hr = sp_OASetProperty @object, 'MailFormat', 0
IF @hr <> 0 GOTO ObjectError
END
-- Loop through the ; delimited files to attach
CREATE TABLE #FileExists (FileExists int, FileIsDir int, ParentDirExists int)
WHILE isnull(len(@Attachments),0) > 0
BEGIN
SELECT @StrEnd = CASE charindex(';', @Attachments)
WHEN 0 THEN len(@Attachments)
ELSE charindex(';', @Attachments) - 1
END
SELECT @Attachment = substring(@Attachments, 1, @StrEnd)
SELECT @Attachments = substring(@Attachments, @StrEnd+2, len(@Attachments))
-- Ensure we can find the file we want to send.
DELETE #FileExists
INSERT #FileExists
EXEC master..xp_fileexist @Attachment
IF NOT EXISTS (SELECT * FROM #FileExists WHERE FileExists = 1)
BEGIN
RAISERROR ('File %s does not exist. Message not sent.', 16, 1, @Attachment)
RETURN 1
END
EXEC @hr = sp_OAMethod @object, 'AttachFile', NULL, @Attachment
IF @hr <> 0 GOTO ObjectError
SELECT @Msg = 'File ' + @Attachment + ' attached.'
PRINT @Msg
END
-- Call the Send method with parms for standard properties
EXEC @hr = sp_OAMethod @object, 'Send', NULL, @From, @To, @Subject, @Importance=@Importance
IF @hr <> 0 GOTO ObjectError
-- Destroy the NewMail object.
EXEC @hr = sp_OADestroy @object
IF @hr <> 0 GOTO ObjectError
PRINT 'Message sent.'
RETURN 0
ObjectError:
BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN 1
END
GO