I am trying to pass a parameter into the DtsCommand object, but always get the error "a requested parameter does not exist in the package". I have a variable defined at the top scope named EmailPromotion so I tried the following
View 9 Replies
Hi,
Ive been taking a look at how to consume events from a package when executing programatically.
Ive got some code (copied below) that creates a package programatically, adds a sequence container then within that adds a script task , then executes it using the overloaded method of Package.Execute() that takes an IDtsEvents argument.
My class that implements IDtsEvents simply output a message to the console for each event type.
Weird thing is, when I execute, this is the only output I get:
Starting...
OnPreValidate: Microsoft.SqlServer.Dts.Runtime.Package
OnPreValidate: Microsoft.SqlServer.Dts.Runtime.Sequence
OnPreValidate: Microsoft.SqlServer.Dts.Runtime.TaskHost
OnPostValidate:Microsoft.SqlServer.Dts.Runtime.TaskHost
OnQueryCancel
Package ran successfully
What I find weird is that I dont get information for loads of other event types. I would at least have expected to see some OnPostExecute events.
Anyone know why i dont see all of the events?
Thanks
Jamie
Heres the code:
Code Snippet
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Tasks.ScriptTask;
namespace Package_API
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("Starting...");
Package p = new Package();
p.InteractiveMode = true;
p.OfflineMode = true;
// Add a Script Task to the package.
Sequence s = (Sequence)p.Executables.Add("STOCK:Sequence");
TaskHost taskH = (TaskHost)s.Executables.Add("STOCK:ScriptTask");
// Run the package.
DtsEvents events = new DtsEvents();
p.Execute(null,null,events,null,null);
//p.Execute();
if (p.ExecutionResult == DTSExecResult.Failure || p.ExecutionStatus == DTSExecStatus.Abend)
Console.WriteLine("Package failed or abended");
else
Console.WriteLine("Package ran successfully");
Console.ReadLine();
}
}
}
// Class that implements the IDTSEvents interface:
public sealed class DtsEvents : IDTSEvents
{
void IDTSEvents.OnPreExecute(Executable exec, ref bool fireAgain)
{
Console.WriteLine("OnPreExecute: " + exec.ToString());
}
void IDTSEvents.OnBreakpointHit(IDTSBreakpointSite breakpointSite, BreakpointTarget breakpointTarget)
{
Console.WriteLine("OnBreakpointHit");
}
void IDTSEvents.OnCustomEvent(TaskHost taskHost,string eventName,string eventText,ref Object[] arguments,string subComponent,ref bool fireAgain)
{
Console.WriteLine("CustomEvent");
}
void IDTSEvents.OnPreValidate(Executable exec, ref bool fireAgain)
{
Console.WriteLine("OnPreValidate: " + exec.ToString());
}
void IDTSEvents.OnPostValidate(Executable exec, ref bool fireAgain)
{
Console.WriteLine("OnPostValidate:" + exec.ToString());
}
void IDTSEvents.OnWarning(DtsObject source,int warningCode,string subComponent,string description,string helpFile,int helpContext,string idofInterfaceWithError)
{
Console.WriteLine("OnWarning");
}
void IDTSEvents.OnInformation(DtsObject source,int informationCode,string subComponent,string description,string helpFile,int helpContext,string idofInterfaceWithError,ref bool fireAgain)
{
Console.WriteLine("OnInformation");
}
void IDTSEvents.OnPostExecute(Executable exec, ref bool fireAgain)
{
Console.WriteLine("OnPostExecute");
}
bool IDTSEvents.OnError(DtsObject source,int errorCode,string subComponent,string description,string helpFile,int helpContext,string idofInterfaceWithError)
{
Console.WriteLine("OnError");
return true;
}
void IDTSEvents.OnTaskFailed(TaskHost taskHost)
{
Console.WriteLine("OnTaskFailed");
}
void IDTSEvents.OnProgress(TaskHost taskHost,string progressDescription,int percentComplete,int progressCountLow,int progressCountHigh,string subComponent,ref bool fireAgain)
{
Console.WriteLine("OnProgress");
}
bool IDTSEvents.OnQueryCancel()
{
Console.WriteLine("OnQueryCancel");
return true;
}
void IDTSEvents.OnExecutionStatusChanged(Executable exec,DTSExecStatus newStatus,ref bool fireAgain)
{
Console.WriteLine("OnExecutionStatusChanged");
}
void IDTSEvents.OnVariableValueChanged(DtsContainer DtsContainer,Variable variable,ref bool fireAgain)
{
Console.WriteLine("OnVariableValueChanged");
}
}
Hello,
I have a DataReader souce configured to an ADO.NET connection manager that uses a .NET ODBC Data Provider. The DSN configured in the connection manager uses the IBM DB2 ODBC Driver.
I have a package variable, the value of which I want to use in the WHERE clause of the query contained in the Data Reader source. The query is as follows:
SELECT
T1."IRK_ACCT_CD",
T1."IRK_COMPANY_NBR",
T1."IRK_ACCT_NAME",
T1."IRK_ADDRESS_1",
T1."IRK_ADDRESS_2",
T1."IRK_ADDRESS_3",
T1."IRK_STATE_CD",
T1."IRK_POSTAL_CD",
T1."IRK_AR_TYPE",
T2."RK502_ITEM_RE_NO",
T2."RK502_TRANS_CD",
T2."RK502_TRANS_TYPE",
T2."RK502_ITEM_AMT",
T2."RK502_ITEM_DT",
T2."RK502_PURCH_ORD_NO",
T2."RK502_GL_EFF"
FROM "CBDBOW"."IRK_RECORD" T1, "CBDBOW"."RK502_OPEN_ITEMS" T2
WHERE
T1."IRK_COMPANY_NBR" = T2."RK502_COMP_NO"
AND
T1."IRK_ACCT_NUMBER" = T2."RK502_ACCT_NO"
AND
T2."RK502_ITEM_DT" < ?
I'm not very sure as to how to map the package variable this way. I think for ODBC parameters, I need to represent the parameter with a question mark.
What else do I need to do to set this up?
Thank you for your help!
cdun2
Hello,
View 9 Replies View Relateddoes using sql parameters speed asp.net code up?
View 3 Replies View RelatedHello Everyone,
I would like to create a report parameter that will allow the user to select a week from a list of all weeks so far this year.
For example:
First Parameter is Week: (Week 1, Week 2, ... , Week 7)
Second Parameter is Year: 2008
How can I accomplish this? Can I create a function and use it as a parameter query?
Thank You in advanced
Hi All, Maybe because it's Friday afternoon and I can't think clearly anymore... A really (I guess) simple problem: DataView with SqlDataSource <asp:DataList ID="DataList1" runat="server" DataSourceID="SqlDataSource1"> <ItemTemplate> <asp:Label ID="id" runat="Server" Text='<%# Eval("ID")%>' /> </ItemTemplate> </asp:DataList> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyConnectionString %>" SelectCommand="GetItem" SelectCommandType="StoredProcedure"> </asp:SqlDataSource> Now, what I have to do is to pass two parameters to the stored procedure: 1) ProviderUserKey2) Int ValueBut the question becomes "how"?Even if I define: <SelectParameters> <asp:Parameter Name="I_GUID" /> <asp:Parameter Name="I_TYPE" Type="Int32" DefaultValue="1" /> </SelectParameters>I need to set the parameters from code behind.... Thanks for any suggestions Adam
View 8 Replies View Relatedhow do i pass the paramenters and storedprocedure to dataview from code-behind?
the below code have sqldatasource control but i want to pass through code-behind everything...
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1"
EmptyDataText="There are no data records to display." DataKeyNames="NewsId">
<Columns>
<asp:BoundField DataField="NewsId" HeaderText="NewsId" InsertVisible="False" ReadOnly="True"
SortExpression="NewsId" />
<asp:BoundField DataField="PostDate" HeaderText="PostDate" SortExpression="PostDate" />
<asp:BoundField DataField="PostedBy" HeaderText="PostedBy" SortExpression="PostedBy" />
<asp:BoundField DataField="PostedByName" HeaderText="PostedByName" SortExpression="PostedByName" />
<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
<asp:BoundField DataField="Body" HeaderText="Body" SortExpression="Body" />
<asp:BoundField DataField="LastUpdated" HeaderText="LastUpdated" SortExpression="LastUpdated" />
<asp:CheckBoxField DataField="IsVisible" HeaderText="IsVisible" SortExpression="IsVisible" />
</Columns>
</asp:GridView> i have storedprocdure which accepts 4 parameters 3 paramenters which user will supply and 1 parameter will be supplied from code-behindwhat i mean by that is:the 4 parameters storedprocedure accepts is:empid, start_date, end_date (user will supply those 3 parameters)internal_id - which internally pass along with other 3 parmeterssomething like this:internal_id, empid, start_date, end_dateany thoughts?thanks.
Hi,
I am trying to retrieve and manipulate the value of the stored procedure parameters in the following custom code:
Code Snippet
Public Function ShowLoading(ByVal intOption as integer, byVal BasicLoading as string, byVal BasicHealthExtra as string, byVal RiderLoading as string, byVal RiderHealthExtra as string, byVal TotalAmount as string) As string
dim strRider as String
dim strCompanyName as string
strCompanyName = Fields!CompanyName.Value
select case strCompanyName
case "and Plus Rider ":
strRider = "Plus Rider"
case "and Assist Rider ":
strRider = "Assist Rider"
end select
return strRider
End Function
However, i got this error when I try to preview the report: An error occurred during local report processing, The definition of the report :
'/reportC' is invalid. There is an error on line 5 of custom code: [BC30469] Reference to a non-shared member requires an object reference.
Any help is greatly appreciated. Thanks in advance.
Is there an easy way to generate the parameters code for calling stored procedures?
Is there an easy way to generate stored procedure code without having to manually type it in? I've seen 3rd party tools, but I was wondering if there's anything within Sql Server to do it.
I'm using c#, visual studio 2005, sql server 2005.
Thanks,
John
oConn = New SqlClient.SqlConnection
oConn.ConnectionString = "user id=MyUserID;data source=MyDataSource;persist security info=False;initial catalog=DBname;password=password;"
oCmd = New SqlClient.SqlCommand
oCmd.Connection = oConn
oCmd.CommandType = CommandType.StoredProcedure
oCmd.CommandText = "TestStdInfo"
'parameters block
oParam1 = New SqlClient.SqlParameter("@intSchoolID", Me.ddlSchl.SelectedItem.ToString())
oParam1.Direction = ParameterDirection.Input
oParam1.SqlDbType = SqlDbType.Int
oCmd.Parameters.Add(oParam1)
oParam2 = New SqlClient.SqlParameter("@dob", Convert.ToDateTime(Me.txbBirth.Text))
oParam2.Direction = ParameterDirection.Input
oParam2.SqlDbType = SqlDbType.DateTime
oCmd.Parameters.Add(oParam2)
oParam3 = New SqlClient.SqlParameter("@id", Me.txbID.Text)
oParam3.Direction = ParameterDirection.Input
oParam3.SqlDbType = SqlDbType.VarChar
oCmd.Parameters.Add(oParam3)
oConn.Open()
daStudent = New SqlClient.SqlDataAdapter("TestStdInfo", oConn)
dsStudent = New DataSet
daStudent.Fill(dsStudent) 'This line is highlighted when error happens
oConn.Close()The error I am getting :Exception Details: System.Data.SqlClient.SqlException: Procedure 'TestStdInfo' expects parameter '@intSchoolID', which was not supplied.I am able to see the value during debugging in the autos or command window. Where does it dissapear when it comes to Fill?Could anybody help me with this, if possible fix my code or show the clean code where the procedure called with multiple parameters and dataset filled.Thank you so much for your help.
Hi
This is the code which I have written in code window.
Public Shared Function CalcLocalFactor(ByVal CalcLifeCode As Integer, ByVal CalcFiscalAge As Integer, ByVal CalcLifeYearsUsed As Double, ByVal CalcLocConvention As String, ByVal CalcSRate As Integer) As Double
Dim locCalcFiscalAge As Integer = 0
Dim locFactor As Double= 1.0
Dim locFactor1 As Double = 1.0
Dim REM1 As Integer = 1
Dim DEP As Double = 0
Dim YR As Integer
Dim HALF_YEAR As Double
Dim LINEAR As Double
Dim MACR As Double
If CalcFiscalAge > CalcLifeCode + 1 Then
locCalcFiscalAge = 0
locFactor = 1.0
End If
If (CalcLocConvention <> "HALF-YEAR" And CalcLifeYearsUsed < CalcLifeCode) Then
locFactor = Math.Round((CalcLifeYearsUsed / CalcLifeCode), 4)
End If
If (CalcLocConvention = "HALF-YEAR") Then
for YR = 1 to CalcFiscalAge step 1
If YR = CalcLifeCode + 1 Then
locFactor1 = 1
Exit For
End If
If (YR = 1 Or YR = CalcFiscalAge) Then
HALF_YEAR = 2
Else
HALF_YEAR = 1
End If
LINEAR = Math.Round(REM1 / (CalcLifeCode - YR + 1.5) / HALF_YEAR, 4)
MACR = Math.Round(REM1 / CalcLifeCode * CalcSRate / HALF_YEAR, 4)
If MACR >= LINEAR Then
DEP = MACR
Else
DEP = LINEAR
End If
locFactor1 = locFactor1 + DEP
REM1 = 1 - locFactor1
locFactor = locFactor1
Next
End If
Return locFactor
End Function
I'm calling this code in a Report Parameter like below:
=Code.CalcLocalFactor(Parameters!CalcLifeCode.Value,Parameters!CalcFiscalAge.Value,Parameters!CalcLifeYearsUsed.Value,Parameters!CalcLifeYearsUsed.Value, Parameters!CalcSRate.Value )
It is working fine for the first record where as for other records, the value is not getting changed. i.e. the first records value is coming repeatedly for all other records also.
How can I dynamically change the parameter values of the function?
Parameter is not accepting directly the field names, hence I used other parameter to initialize the field and used that parameter for this.
Ex. Parameter Name ; FieldPurchDate (internal) FieldName : PURCHDATE
Other parameter: FieldInDate (internal) FieldName : InDate
While initializing the new parameter CalcPurchDate,, I used an expression for this: Parameters!CalcFiscalAge.Value
=iif(Parameters!FieldPurchDate.Value is nothing, Parameters!FieldInDate.Value,Parameters!FieldPurchDate.Value)
and using this CalcPurchDate for processing of the parameter:
These are some of the things , I am doing ....
Please let me know how to fix this issue...
Thanks in advance
Regards,
Radhika
I've just faced quite interesting case.
I have two SQL Servers 7.0 running on two W2K(sp1) Server boxes (Compaq ProLiant 1600 and HP NetServer LC3) both with RAID5. One of the task these servers peform is logging MS Proxy.
For more convenient way I created the procedure that show me proxy users, their IP address and URL head where they hit for the last one minute and half. This one as follow:
-----
REATE proc usp_showusers as
select clientip, clientusername, substring(stuff(uri, 1, 7, ''), 1, patindex('%/%', stuff(uri, 1, 7, ''))) short_uri into #whoweb from msplog where logtime > getdate()-0.001
select clientip, clientusername, short_uri from #whoweb
group by clientip, clientusername, short_uri
-----
So, when I run this proc on HP box it executes well and SQL keeps on consuming the same amount of memory (approximately 60MB)
When I run on Compaq, the proc executes well as well, but promptly gets memory consume up to 150MB (before execution it did ~50(60)MB). In half an hour it pulls down 90MB.
In addition to SQL Server the boxes run Lotus Domino R5 and Active Directory.
The installed software on them is absolutely identical.
Any idea? I'd be really appreciated.
Thanks
Hi there,
I've been following Vineets and David's procedures to consume web
services using SQL CLR to the t. I created my web service in C#.NET
2005, and generated my proxy using this command:
wsdl /par:oldwsdlconfig.xml /o:ExactMobileService.cs /n:Project360.SmsService http://www.exactmobile.co.za/interactive/interactivewebservice.asmx
I added both files to the project, set Generate Serialization Assembly to on and compiled it.I then generated a strong name key for the assembly and signed my assembly with that key.Inside my post-build event I added the following script:
"E:DevelopmentMicrosoft
Visual Studio 8SDKv2.0Binsgen.exe" /force
/compiler:/keyfile:SmsServiceKey.snk /t:StoredProcedures
$(TargetDir)$(TargetName).dll
This compiled into my assembly, the XmlSerializer assembly and then added strong name key to both.
In SQL Server 2005, I enabled CLR, made my DB trustworthy, created my
first assembly with permissions EXTERNAL ACCESS and then the
XmlSerializer assembly with permissions SAFE. I created my stored
procedure and ran it. When I did I got this error which I assumed the
XmlSerializer was supposed to solve for me:
System.InvalidOperationException:
Cannot load dynamically generated serialization assembly. In some
hosting environments assembly load functionality is restricted,
consider using pre-generated serializer. Please see inner exception for
more information. ---> System.IO.FileLoadException: LoadFrom(),
LoadFile(), Load(byte[]) and LoadModule() have been disabled by the
host.
I have seen alot of posts about this error, but none of them has been able to solve my problem.
Please can you help me?
O'Connor
Hi,
We've been using Sql Server 2005 for some time now and we've found it to be stable and pretty much bug free, well done! (as opposed to Oracle 10g, which is bug-ridden) I'm not sure if this is the right forum to post this in, but it felt like the best place to start. Please move my post if it is not correct.
In the last few weeks we've had a very serious problem; whenever we try to execute our upgrade scripts the CPU usage spikes to 100%. And then every insert takes > 0.5 seconds. Needless to say, but I'll do it anyway, this is a huge problem. Some of our scripts have > 100 000 inserts, so the upgrade process takes "forever".
At this point it might be useful to talk just briefly about how we use our Sql Server; We have an application that is configured via the database (we support Sql Server and Oracle) and so we use the database for a lot more than just a "dumb" datastore. We do massive amounts of insert/update for every version of our application and we add new tables quite often. We have a build system, using TFSBuild, where we do nightly builds. That build will drop the current database (example of name: MONDAY, TUESDAY etc.) and restore it again using an image. Then we upgrade the new database to the most current version of our scripts. On our Sql Server 2005 server we have 4 such databases so the process is repeated 4 times each night. And since we build our database using a baseline image and scripts (both stored in source control) we do not need backups of the databases, so no backup plan is running. I'm only mentioning that because we've had a theory that this might be the cause of our problems...
So, does anybody have an idea how we might go about solving this?
What are the types of queries that are most time consuming...which may easily lead to expired time out etc...thanks
Regards
Alu
I have a sotred procedure using a cursor which sort data and create subsets based on same oid and same decision_date columns, for each subset I am trying to order them and affect values 1, 2,... for each record in a different subset.
The stored procedure seems to work very well and fast against a small tables (during tests). When used against a table with 200,000 records it takes more than 24 hours...
I am looking for a help to make it work faster, thanks guys.
here is the stored procedure:
CREATE PROCEDURE ORDERING_TEST_PROCEDURE AS
DROP TABLE REVIEWS_TEST_TABLE
SELECT OID,DECISION_DATE,DECISION_ID,VOTES_REQUIRED, ORDERING INTO REVIEWS_TEST_TABLE FROM DECISION_FLAGS
WHERE VOTES_REQUIRED IN ('1','2','3') and FINAL_DECISION_CODE NOT IN ('0800','0810','0840') and DECISION_TYPE_CODE < '0100'
ORDER BY OID, DECISION_DATE,VOTES_REQUIRED DESC
CREATE INDEX OID_DECISIONID_DATE_INDEX ON CRIMS.dbo.REVIEWS_TEST_TABLE (DECISION_ID, DECISION_DATE, OID)
CREATE INDEX VOTESREQUIRED_INDEX ON CRIMS.dbo.REVIEWS_TEST_TABLE (VOTES_REQUIRED)
CREATE INDEX DECISIONID_INDEX ON CRIMS.dbo.REVIEWS_TEST_TABLE (DECISION_ID)
declare @oldoid varchar(12)
declare @olddecision_date varchar(75)
declare @oid varchar(12)
declare @decision_date varchar(75)
declare @Decision_id varchar(12)
declare @ordering varchar(1)
declare @ordering_count int
declare @votes_required varchar(12)
set @oldoid = 'space'
set @olddecision_date = 'space'
set @oid = 'space'
set @decision_date = 'space'
set @votes_required='space'
set @Decision_id = 'space'
set @ordering = '0'
set @ordering_count = 0
declare review_test_cursor cursor for
select oid,decision_date,votes_required,ordering,decision _id from CRIMS.dbo.reviews_test_table
order by oid,decision_date,votes_required asc
open review_test_cursor
fetch review_test_cursor into @oid,@decision_date,@votes_required, @ordering,@Decision_id
while (@@fetch_status = 0 )
begin
if @oldoid <> @oid or @olddecision_date <> @decision_date
begin
set @oldoid = @oid
set @olddecision_date = @decision_date
set @ordering_count=0
end
update reviews_test_table
set ordering = CAST ((@ordering_count + 1) as VARCHAR)
where decision_id = @Decision_id
set @ordering_count = @ordering_count + 1
fetch review_test_cursor into @oid,@decision_date,@votes_required, @ordering,@Decision_id
end
close review_test_cursor
deallocate review_test_cursor
/*********************************/
UPDATE DECISION_FLAGS
SET ORDERING = '0'
UPDATE DECISION_FLAGS
SET DECISION_FLAGS.ORDERING = TEM.ORDERING
FROM DECISION_FLAGS DEC, REVIEWS_TEST_TABLE TEM
Hi,
The sqlsrv.exe process has been consuming a high number of cpu cycles and time and seems not be decreasing at all. Can somebody point me in the right direction as to how to identify the bottleneck and resolve the same at the earliest
Thanks in advance
Hi:
Can someone help me with a SSIS package that would consume from a Web Service (in fact two of them) and then load the data into SQL Server. I currently have Web Service task which connects to ForEachLoop task, and inside the loop task, I have a DFT. I am thinking, I would need to call the webservice utilizing the Web Services Task, and then store the output in a Full ResultSet variable. In my loop, I would like to loop thru the resultset, and store the data into SQL server. Inside the DFT, how would I construct this mechanism? Also, is this a good way to consume from a Web Service and then populate SQL Server? Are there any alternate ideas on this? Any documentation on this yet? Thanks.
I have a situation where I have created a recordset in a previous data flow task where the output was a recordset.
Now I want to write that recordset to a flat file.
There is no direct recordset input data flow source. So I thought I would use a source script transformation to read the recordset. Then I searched on this and found no information on how I might do this.
Anyone have any ideas on how to do this in a script transformation?
There are reasons that I would like to do it this way, but if I have to choose another way, well that's life.
Hi All,
We have a webservice, which we are able to consume in the SSRS report.
Due to some requirement, we need to conver this webservice in to the secured webservice using the SSL https extension.
We did this and just changed the connection string from http to https.
But when we try to run the report, we get the following error message. Copied from BIDS.
TITLE: Microsoft Report Designer
------------------------------
An error occurred while executing the query.
Failed to prepare web request for the specified URL.
------------------------------
ADDITIONAL INFORMATION:
Failed to prepare web request for the specified URL. (Microsoft.ReportingServices.DataExtensions)
------------------------------
The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel. (System)
------------------------------
The remote certificate is invalid according to the validation procedure. (System)
------------------------------
BUTTONS:
OK
------------------------------
Can anybody help in this.
Thanks in advance.
Virendra
Is it possible to consume the sql 2005 web service from 1.1? When I try and do so I receive the following error:Type 'http://schemas.microsoft.com/sqlserver/2004/sqltypes:varchar' is not declared or not a simple type. An error occurred at , (1, 2452).
Thanks,
Olja
I try to consume a SSL Endpoint that is locate in localhost, but i get the follow error msg:
The underlying connection was closed: An unexpected error occurred on a receive.
m_safeCertContext is an invalid handle.
I do not know what gone wrong with my VS2005, i tried in IE with https://localhost/test?wsdl, and it was okay and display the wsdl document successfully. Beside i also tried with CLEAR port, everything is ok and i managed to consume the web service too.
any suggestion would be appreciated. Thank q
Hi,
I believe this question has been covered quite a bit, but none the less I wanted to ask it as I still have no resolution at the moment. I am consuming a table from Oracle which has a data type of NUMBER. When I use a .Net Provider to consume the data, the SCALE defaults to 0 for these select columns and thus we lose any decimal point information for these columns. I have tried using both the .Net Provider and the .Net for OLEDB providers for ORACLE. Both seem to suffer from this. The SSIS server itself is a Win 03' server on x64 hardware architecture. Also, when trying to use the Oracle Provider for OLEDB it throws a design-time error stating "Error in Initializing Provider".
Thanks!
Derek
This is my SProc:
CREATE PROCEDURE dbo.ap_Select_ModelRequests_RequestDateTime
/* Input or Output Parameters *//* Note that if you declare a parameter for OUTPUT, it can still be used to accept values. *//* as is this procedure will very well expect a value for @numberRows */@selectDate datetime ,@selectCountry int ,@numberRows int OUTPUT
AS
SELECT DISTINCT configname FROM ModelRequests JOIN CC_host.dbo.usr_smc As t2 ON t2.user_id = ModelRequests.username JOIN Countries ON Countries.Country_Short = t2.country WHERE RequestDateTime >= @selectDate and RequestDateTime < dateadd(dd,1, @selectDate) AND configname <> '' AND interfacename LIKE '%DOWNLOAD%' AND result = 0 AND Country_ID = @selectCountry ORDER BY configname
/* @@ROWCOUNT returns the number of rows that are affected by the last statement. *//* Return a scalar value of the number of rows using an output parameter. */SELECT @numberRows = @@RowCount
GO
And This is my code. I know there will be 100's of records that are selected in the SProc, but when trying to use the Output Parameter on my label it still says -1Protected Sub BtnGetModels_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim dateEntered As String = TxtDate.TextDim selectCountry As String = CountryList.SelectedValue
Dim con As New SqlClient.SqlConnection
con.ConnectionString = "Data Source=10.10;Initial Catalog=xx;Persist Security Info=True;User ID=xx;Password=xx"Dim myCommand As New SqlClient.SqlCommand
myCommand.CommandText = "ap_Select_ModelRequests_RequestDateTime"
myCommand.CommandType = CommandType.StoredProceduremyCommand.Parameters.AddWithValue("@selectDate", dateEntered)
myCommand.Parameters.AddWithValue("@selectCountry", CInt(selectCountry))Dim myParam As New SqlParameter("@numberRows", SqlDbType.Int)
myParam.Direction = ParameterDirection.Output
myCommand.Parameters.Add(myParam)
myCommand.Connection = con
con.Open()Dim reader As SqlDataReader = myCommand.ExecuteReader()Dim rowCount As Integer = reader.RecordsAffected
numberParts.Text = rowCount.ToString
con.Close()
End Sub
What should I fix?
Dear All,This is a query surrounding a problem I encounteredyesterday.In SQL Server, it is possible to write a procedure thathas one or more select statements in it.The results from these select statements will all beindividually returned to SQL Query Analyser where theycan be viewed in "grid" views. Also, these individualresults sets can be consumed by eg ADO.NET by steppingthrough each results set in turn and processing therespective results.My question is, can you do the same in a SQL Serverprocedure? ie:Create Procedure Proc1ASbeginselect Col1, COl2from Table1select Col1, Col2, Col3from Table2endCreate Procedure Proc2ASbeginexec Proc1endCan both/either of the results sets from Proc1 beconsumed by the calling procedure Proc2?I can see that you could design the procedures up-front to do almost anything without consuming theresult sets in this way, but if the proceduresreturning the results sets are already built andin use in other places (for instance in client code),can they be re-used on server-side SQL procedures?Thanks in anticipation!Paul.
View 5 Replies View RelatedAssume I've created a web service named 'SaveActivityData' for some SQL 2005 database on some remote server. Also assume that the stored proc takes two parameters; the first is an integer value and the second an XML value.
1) Is it possible for a stored procedure on a completely different SQL 2005 server to consume this web service?
2) If the above is possible, could someone show me an example of the T-SQL syntax required to consume this web service, passing the two required parameters? In your example, you can make up whatever URL you like for the remote web service location.
Thanks very much - Amos.
Hi,
I have a problem while rendering a report which returns around 5000 rows. At first the server is busy to process the request, when the server is done the rendering client side takes 100% of CPU and never displays the result ("IE is not responding"). It seems that the ReportViewer has trouble to handle the server response.
Do you have any idea why ?
Sébastien.
I am very new to Reporting Services. What I would like to know is if there is anyway for me to consume the raw data on a specific report from within excel. That is, can I call a report from reporting services directly from excel.
Maybe someone can outline the supported interaction between excel and reporting services for me. I have been having lots of trouble locating information about the interaction between these two.
--David
Hi,
I have created a program that imports a csv into the sql server. but during that import I need to track all the errors that occured for some malformed rows. I think I need to use the error output collection of the dataflow components to track the errors. I figured out that every dataflow component has a error output collection along with the data output collection. I want to write those error outputs into a separete database. So, I have created a SQL server data destination component and created a path between derived columns error output and it input collection. But it is not working as expected. can any body help on this?
or can anyone give me any example how to use/handle error output collection in SSIS?
I will appreciate all kind of suggestions.
thanks
Hi,
Need a guidance on consuming the WCF service with complex type in the SSIS package.
I have a WCF service with complex type inside the complex type (Nested complex type) in the web method as an argument. When I try to use this WCF service in the SSIS web service task, I get an error "The web method has unsupported arguments".
I am able to consume the WCF service with the web method having Complex type and simple/prmitive type in side that as argument.
For example:
The web method in WCF service which accepts the argument as comlex type say "Employee" whose structure is:
Employee
{
FName String;
LName String;
Age int
}
It is possible to consume this WCF service and pass the arguments.
But when the Employee complex type is changed to have one more complex type in side it it give the above mentioned error. The Employee type is modified as:
Employee
{
FName String;
LName String;
Age String
Type EmployeeType;
}
EmployeeType
{
type string;
}
Now I get the error Web Method has unsupported arguments.
Can this be done in SSIS?
Virendra
HI!
as far as I know from docs and forum datareader is for .NET data in memory. So if a use a complex dataflow to build up some data and want to use this in other dataflow componens - could i use data datareader source in the fist dataflow and then use a datareader souce in the second dataflow do read the inmemoty data from fist transform to do fursther cals ?
how to pass in memory data from one dataflow to the next one (i do not want to rebuild the logic in each dataflow to build up data data ?
Is there a way to do this ? and is the datareader the proper component ? (because its the one and only inmemory i guess, utherwise i need to write to temp table and read from temp table in next step) (I have only found examples fro .NET VB or C# programms to read a datareader, but how to do this in SSIS directly in the next dataflow?
THANKS, HANNES
Hello All,
I’m looking for a solution to timeouts that occur when I’m executing a stored procedure from my web application. Most of the SPs will run from 3 to 15 minutes, and, unfortunately, modifying/optimizing them isn’t an option at the moment. I tried setting the CommandTimeout to 0 with no luck. Unless, I didn’t use it properly. Here’s my code: 1
2 try
3 {
4 string dbConn = ConfigurationManager.ConnectionStrings["ConStringNTMTLDEV"].ToString();
5 OleDbConnection connection = new OleDbConnection(dbConn);
6
7 lbl_SearchResult.Text = dbConn;
8
9 //OleDbDataAdapter adapter = new OleDbDataAdapter();
10 OleDbCommand cmd = new OleDbCommand("SP_CallHistoryLookUp", connection);
11 cmd.CommandType = CommandType.StoredProcedure;
12
13 cmd.Parameters.Add(new OleDbParameter("@phoneNumber", "1234567890"));
14 cmd.Parameters.Add(new OleDbParameter("@email", "123@123.com"));
15 cmd.Parameters.Add(new OleDbParameter("@WebUser", "123"));
16 connection.Open();
17 cmd.CommandTimeout = 0;
18 cmd.ExecuteNonQuery();
19 cmd.Dispose();
20 connection.Close();
21 }catch(OleDbException ex)
22 {
23 lbl_SearchResult.Text += "<br/> Something went wrong </br>";
24 lbl_SearchResult.Text += ex.Message.ToString();
25 }
26
27
28
Is it possible to launch a stored procedure and close the connection without waiting for a result?Would the stored procedure still run on the SQL server? I’m using MSSQL 7. Would you have any examples that would solve this problem? Thank you for your help.
R.