SSIS Web Service Task Input Parameters

There is not a way to pass parameters to input of Web Service tasks.  I heard this problem is fixed with SQL2K5 SP1 and even the online doc says that one can choose either "value" or "variable" when specifying input for web service tasks, but after I installed what-I-think-is SP1, there is still no way to do this.

If one can only specify values (hard-coded) as input to web service tasks, then this would be a very severe limitation.  I hope I'm wrong, so could someone please give a pointer.  Thanks

Kevin Le

View Replies


SSIS Web Service Task - SOAP Header Input


In SSIS web service task - when you specify the Service and Method in the input tab for a WSDL file being used,
it seems to prompt for the parameters to be supplied only in the body of the WSDL file, and not the header.
I need to be able to provide security information (present in the SOAP header of the WSDL) like username, password etc..which is necessary to post any response to the web server, and I cannot see where I can give this in the input tab of the web service task.
Any help highly appreciated.

View Replies View Related

Web Service Task Not Showing Input

For the life of me I can't figure this one out.  I'm trying to call a web service using the SSIS "Web Service Task".  I give the Connection Manager a valid URL, download the WSDL, and go to the input pane of the "Web Service Task" options.  No problems.  I select my web service and pick a web method from the methods drop-down box.  No problems.  But where it should pop up with an input dialog box that allows you set your inputs for the web service call, nothing shows up.  No errors are reported; the input pane just remains completely blank.  I can inspect the WSDL with other tools (like Altova's XMLSpy) and plainly see the inputs it takes.  I haven't seen a similar issue posted on this forum so I don't know if I'm going nuts or my version of SSIS is evil or what.  I applied SQL Server SP1 and SP2 but it didn't change anything.  I then tried to connect to one of Amazon's public web services to see if it was a problem related to our in-house WSDL formatting, and I discovered the same problem.  Take for instance
I create a Web Service Task for this service and when I go to select the "ListLookup" method from the drop-down box on the input page no input parameters appear.  The WSDL clearly defines this method to take a bunch of inputs.  If anybody has any ideas about what's going on here I would greatly appreciate the help.  Thanks.

View Replies View Related

Null Value For Web Service Task Input

I'm attempting to use the Web Service task to call a method provided by a vendor.  The inputs are all simple types so that helps a lot, but for one input it's a string array.  If I enter a single string value for a given id using the array dialog, the method works fine.


However if I try to change the input to use a variable to provide the value it fails.  I'm assuming that you can't create a variable to return a string array.  Please let me know if this is the case or if I'm missing something obvious ;-)


In addition, is there a way to specify a null value for an input?  Currently there doesn't appear to be a way to do this in SP2.  I tried leaving the string empty which didn't work and I tried using a variable with expression "NULL(DT_WSTR, 1252)" but that failed too.

View Replies View Related

Web Service Task - Passing Variable As Input

Microsoft says it is possible but I just do not see how. Here is the
link to the help file where it said that variables could be pass as
input to web methods...I do not see the check box they mention on my

Any help would be greatly appreciated.


View Replies View Related

Execute SQL Task : Input And Output Parameters In Tsql Stataments With ADO.NET Connection Type

Hi Everyone,

I haven't been able to successfully use the ADO.NET connection type to use both input and output parameters in an execute sql task containing just tsql statements (no stored procedure calls). I have successfully used input parameters on their own but when i combine it with output parameters it fails on the simplest of tasks.

I would really find it beneficial if you could use the flexibility of an ADO.NET connection type as the parameter marker and parameter name can be referenced anywhere throughout the sql statement in no particular order. The addition of an output parameter would really make it great!!




View Replies View Related

Dyanamically Passing Input Parameters To Stored Procedure By Using SSIS


I have 2 source tables emp_ass,aprvl_status these tables are not having common column to join. and 1 target table Time_Card, i have a stored procedure with 4 input parameters, emp_ass_id,status_id,start date,end date,i am inserting data into timecard based on emp_ass_id, my week start date is sunday and end date is saterday if emp start date is sunday i am just incremnting the start date by 7 days as end date is saterday and inserting that row, if employe statrt date is other than Sunday. i am just insering start date with to reach end date saterday, this work fine when i give the input parameters, now my reqirement is i need to automate this process as i need to get new emp_ass_id which is not in target table and insert his records based on his start date and end date,
if emp_ass_id is 1001, start date 1/1/2008 and end date is 2/1/2008 then i need to insert

Uniq_Id,      emp_ass_id,               start_date                                     end_date                    status_id



1/1/2008 12:00:00 AM
1/5/2008 12:00:00 AM                  1



1/6/2008 12:00:00 AM
1/12/2008 12:00:00 AM                1



1/13/2008 12:00:00 AM
1/19/2008 12:00:00 AM                1



1/20/2008 12:00:00 AM
1/26/2008 12:00:00 AM                1



1/27/2008 12:00:00 AM
2/2/2008 12:00:00 AM                  1

the stored procedure will insert these records if i give the input parameters, now i need to automate this process by using SSIS. please help me,i need to get emp_ass_id,start_date,end_date dynamically from source table if emp_ass_id is not in target table.

Thanks in advance.

View Replies View Related

Input Parameter In SSIS Execute SQL Task.


I would like to create a SSIS package that is going to be called by store procedures.

What i have done so far.

1) I created a Execute SQL task that come with this statement e.g. Seleect * from tblA where BD >= ? and BD =< ?

2) I save this package as a DTSX file and will called it from a proc.

My intention is to pass 2 values when i call the proc. What should do next? any guided tutorial or steps i would be happy. thanks

View Replies View Related

Dynamically Changing Web Service Task Parameters At Runtime?...

Okay, this one might stump you guys.

View Replies View Related

SSIS Web Service Task

I have been struggling with a problem with the Web Service Task.  I have a package that uses this and has to authenticate to the web service.  This works fine on my development server but if I export the package to file and run from another computer / user, the package fails as it doesn't log in to the web service.  I have found the problem to be the package was set to default of EncryptSensitiveWithUserKey and I understand why this is.  If I change to use EncryptSensitiveWithPassword, it prompts for creds and works fine.  My question is how do I use a package configuration file to use different creds (there is not password option for the http connection) so I can standardize on use of config files for this operation.  Thanks.



View Replies View Related

SSIS - Web Service Task

I just started using the Web Service Task in my package.

I set the HTTP Connection Manager with URL & required credentials and the test connection was successful. And I saved the wsdl locally and selected the wsdl. In the Input I selected the WebService and as soon as I do that I am getting the error:

"Item has been already added.  Key in dictionary: PosDataSet    Key being added: PosDataSet

I am using this web service elsewhere in a console app and it is working fine.

Any idea what could be causing this? Any input will be greatly appreciated

View Replies View Related

Web Service Task IN SSIS

hi friends,
regarding the Web Service Task
i was created a with c#   webservices one.asmx  wich contains the 2 methods
 1) no perameters(just like hello world)
 2) with perameters:
        sum(int a , int b)
         return a+b;
until this step fine
in ssis Web Service Task   i am giving the input permeters to sum method
whenever we select the sum() method bellow box displaying the 2 input values
 Name   type Value
 a          int    ??????????????    (IS IT POSSIBLE TO give our local variables    i was given   gives an error @   [  a  ] )
 b          int   ???????????????   (IS IT POSSIBLE TO give our local variables    i was given   gives an error @    [  b  ] )
 we can store the output into our local variables but is it possible to give the input as our loc variables
thanks to all

View Replies View Related

Passing Parameters To SSIS Execute Process Task

Can anyone help me in for the following.
i want to execute a exe file with two variable parameters
Executable : C: empMyExe.exe 
Parameter1   : User::Category  type is string  (below example A)
Parameter2  :  User::Amount    type is string  (below example 1)
in dos it looks like this
c: empMyExe A 1
This will executes fine.

View Replies View Related

Using Ssis Task To Call A Web Service


I m trying to use the web services task, but when i try to run it i got the following error

[Web Service Task] Error: An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: Could not execute the Web method. The error is: Object reference not set to an instance of an object..    at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebMethodInvokerProxy.InvokeMethod(DTSWebMethodInfo methodInfo, String serviceName, Object connection)    at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTaskUtil.Invoke(DTSWebMethodInfo methodInfo, String serviceName, Object connection, VariableDispenser taskVariableDispenser)    at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThread()".

This is probably because i never worked with this task, even so, does anyone knows what might be wrong?

I got a connection a wsdl, i can configure the service, method and variables(i use fixed values), and i configure the output to a variable with the type object(i have tryed string and int)

I even can download the wsdl file, but the error seems to be in the connection



View Replies View Related

Multi-parameters Issue In Data Flow Task SSIS

Hi all,

I met a problem when trying to pass values to a SQL statement through parameters. It's a data flow task. I used the OLE DB connection. My statement is like the statement below(the real statement is little complex):

Select * from myTable where mydate>? and mydate<?

I used the "set query parameter" dialogbox to bulid two parameters varStartTime and varEndTime, the values for the two parameters were set to "1/1/2005" and "12/30/2006" respectively. But when I click the "Parse query" button, I got errors

Parameter Information cannot be derived from SQL statements. Set parameter information before preparing command.

I have referred to the posts, but the problem still exists. Any help will be highly appreciated. Thanks a lot!

View Replies View Related

Stored Procedures And SSIS Web Service Task

I have created a simple Stored Procedure 'hello_world'. This procedure has one argument and returns a string.

I exposed this Stored Procudure using 'CREATE ENDPOINT' and granted access. All fine. Endpoint was called 'hello_world_endpoint'.

Next step: call the exposed Stored Procedure from the SSIS Web Service Task. At the 'General' tab of the Web Service Task Editor I define the HHTPConnection, configure the WSDL file. This works as well.

Next step is the 'Input' tab. It is weird to see only three lines under the 'Input' on the right (BOL suggests that there are 6 lines). I have 'Service', 'Method' and 'WebMethodDocumentation'. I select the service 'hello_world_endpoint'under 'Service'. Then I select the method (which is the Stored Procedure name). Also okay.. now, only one line left: 'WebMethodDocumentation'. Can't fill in anything here.

Now, if I press 'OK', I the message 'The selected Web method contains unsupported arguments'. What could this be? I even deleted the initial input argument (so the procedure only return a string) and still have the same error. Am I missing the options 'Name', 'Type' and 'Value' that are listed as options in BOL:

Thanks for your time!

Jaap Wagenvoort (The Netherlands)

View Replies View Related

SSIS Calling Web Service Task 401 Unauthorized Error


I'm pretty stuck on a security issue in SSIS. The web service works by itself, but I can't call it from SSIS, it gives me a 401 unauthorized error. The web service also uses impersonation of my domain admin account.

I have tried the following things:
Setting integrated windows authentication in IIS
Setting the NTFS permissions of those web site directories to EVERYONE
Using a credential / proxy in SSIS and running it from SQL Agent
Changing the log on services of MSSQLSERVER, SQLAGENT, and SQL Integration Services to my domain admin account

I can't get anything to work. What is wrong with this thing? Microsoft's security model has gotten completley out of of hand imo

Also in the security event log it shows all authentication as successful.

View Replies View Related

Adding A Web Reference (web Service) To An SSIS Script Task?

Is it possible to do this under SSIS 2005? How? I see I can add a reference to but then what?


The web service was developed in 2005 and I have no problem adding and consuming it from a web page developed using 2005 -


Thanks for any help or information.





View Replies View Related

SSIS SQL 2005 Script Task For Windows Monitoring Service


Does any body knows how to convert this VBScript Code to VBDotNet (SQL SERVER 2005 INTEGERATION). Below Codes Returns a list of all the services installed on a computer, and indicates their current status (typically, running or not running).Its in VBScript Format.

strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\" & strComputer & "

Set colRunningServices = objWMIService.ExecQuery("Select * from Win32_Service")

For Each objService in colRunningServices
    Wscript.Echo objService.DisplayName  & VbTab & objService.State

Deepu M.I


View Replies View Related

SSIS SQL 2005 Script Task For Display Windows Service


The Below code which i have used for getting the status for a windows service .
The Code display the status of Telnet service either Running or Stopped.
I would like to check if its service is stopped or starting i would able to notify.
For example My SSIS Script(SQL SERVER 2005) application is running
Manually i am changing the status of Telnet through (Control pannel / Services) like start and stop.
If the service is stopped i should able to see the status.

I have tried to use timer control to execute the scripts for simultaneosuly...
But timer event is not firing in the SSIS script componet page....

Please help ....


Imports System
Imports System.Management
Imports System.Data
Imports System.Math
Imports System.Web
Imports System.Windows.Forms
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

  Public Sub Main()
        Dts.TaskResult = Dts.Results.Success
        Dim str As String
            Dim searcher As New ManagementObjectSearcher("rootCIMV2", "Select * from Win32_Service where Name='TlntSvr'")

           For Each queryObj As ManagementObject In searcher.Get()
                If queryObj("State").ToString = "Running" Then
                    'str = str & queryObj("DisplayName").ToString & " --- Status " & queryObj("State").ToString & vbCrLf
                    str = "Service is Running"
                    str = "Service is Stopped"
                End If


        Catch err As ManagementException
            MessageBox.Show("An error occurred while querying for WMI data: " & err.Message)
            MessageBox.Show("An error occurred while querying for WMI data: " & err.StackTrace)
       End Try

    End Sub

 End Class

Regards Deepu M.I



View Replies View Related

Problems With Connections And Analysis Service Processing Task In SSIS

Hi everybody,

I'm fairly new to the SSAS/SSIS world (though not new on databases, etc.) and I'm having some problems with the SSIS packages in our Cube environment.

Currently in our SSAS/SSIS project, we have two major connection managers, one to the database we use for loading the Cube, and the other connector for the cube itself. To load the data from the database to the cube, we wrote some SSIS packages and used the Analysis Service Processing tasks to process all the dimensions and measures. This works pretty good, so no problems here.

The real problem starts, when I try to change the connection parameters, e.g. because the server changed, or the database has been renamed.
As soon as the connection managers points to another (existing) cube, regardless if the structure is exactly the same as the one of the old cube, the tasks lose all the assigned objects from their lists. It is really annoying to add all these exactly same objects to the task again. I tried experimenting with the DelayValidation attribute so the Development Studio doesn't destroy my work every time, but when I deploy the package the Cube breaks. Obviously some kind of deeper connection is destroyed when I change the connection string.

Is there a way to prevent the package from breaking/losing objects, without me having to sacrifice 15 minutes every time I change the connection parameters?


View Replies View Related

How To Specify Input Parameters?

I'm very new to SQL Server so please forgive me if this question is ridiculously simple.  I have to upgrade the report engine from one that was used in a legacy VB6 app to a app.  In doing so, I'm looking at assorted reports that came out of the old app.  Here is the SQL code for one of them:

SELECT (LTRIM(STR(From_To,10,0)) + '-' + LTRIM(STR(From_To + 49,10,0))) AS Bonus_Earned, COUNT (Empl_ID) AS Men, round(SUM (SumDollars),2) AS Group_Earn, round((SUM (SumDollars) / COUNT (Empl_ID)),2) AS Calc0 FROM (SELECT CONVERT (int, round (SumDollars / 50, 2)) * 50 AS From_To, SumDollars, Empl_ID FROM (SELECT round(SUM (Actual_Hours_Dollars.Incentivedollars * Contract_History.Bonus_Pct / 100), 2) AS SumDollars, employees.Empl_ID FROM ((Employees INNER JOIN Actual_hours_dollars ON Employees.Empl_ID = Actual_Hours_Dollars.Empl_ID_R) INNER JOIN Contracts ON Actual_Hours_Dollars.Contract_ID = Contracts.Contract_No) INNER JOIN Contract_History ON Contracts.Contract_Idx = Contract_History.Contract_Idx_R where employees.empl_idx = (SELECT max(empl_idx) FROM Employees AS OuterEmployees WHERE OuterEmployees.empl_id = employees.empl_id AND outeremployees.Datex = (SELECT max(datex) FROM Employees AS InnerEmployees WHERE InnerEmployees.empl_id = employees.empl_id AND Inneremployees.disabled = 0 AND Inneremployees.Datex < '~EndDate~')) AND Contracts.Datex = (SELECT max(datex) FROM Contracts AS InnerContracts WHERE InnerContracts.contract_no = Contracts.contract_no AND InnerContracts.disabled = 0 and InnerContracts.deleted = 0 AND InnerContracts.Datex < '~EndDate~') AND Actual_hours_dollars.Datex >= '~StartDate~' AND Actual_Hours_Dollars.Datex < '~EndDate~' AND dateadd(month, Contract_History.Monthx - 1, dateadd(year, Contract_History.Yearx - 1900, '01 Jan 1900')) >= '~StartDate~' AND dateadd(month, Contract_History.Monthx - 1, dateadd(year, Contract_History.Yearx - 1900, '01 Jan 1900')) < '~EndDate~' and Actual_Hours_Dollars.IncentiveHours <> 0 GROUP BY employees.empl_ID) AS InnerRS1 GROUP BY ROUND (SumDollars * 2, -2) /2, SumDollars, Empl_ID) AS InnerRS2 GROUP BY From_To

I'm only including it for completeness.  The key thing I'd like to draw your attention to are two variables that are clearly input parameters:  ~StartDate~ and ~EndDate~.

My question is this: If I want to copy this code into SQL Query Analyzer and run it to see what kind of results I get back, what's the simplest way to define these two input parameters?  I'm hoping you could just show me the syntax to define them above the SELECT statement.

Robert Werner
Vancouver, BC

View Replies View Related

Retrieve Date Using Input Parameters W/o GUI

I want to retrieve data in between two date formats using a query in SQL?
can i do it w/o using GUI tools?
For Exp i have sales data from date 11/11/2000 to 11/2004.
now as a user i want to give Input paramete value ranging between 06/06/2002 and 07/07/2002?
Is there any SQL query which i can use to retrieve the above date values?
Thanx in Advance

View Replies View Related

Parameters Input Field Size



is it possible to change the appearence of input fields for parameters on the report server? My parameter is Multi-value with quite large amount of available values. On report server, user can (without scrolling) see only the first value. Parameter values are quite long, so user has to move alternally with both vertical and horizontal scrollbars to find the right value.




View Replies View Related

Insert Syntax When Passing Input Parameters

I'm trying something like this:

CREATE PROCEDURE Add_Junk @Dist char, @CheckNo int =null OUTPUT AS
Set NoCount On
VALUES (@Dist)
select @CheckNo=@@IDENTITY

If what I pass is "416" I only get the "4" in my database and nothing else.
I don't get an error message.
What is wrong with my syntax?

PS I'm using Microsoft SQL 7.0

View Replies View Related

SqlCommand Return And Output Parameters Not Working, But Input Does?

The foolowing code I cannot seem to get working right. There is an open connection c0 and a SqlCommand k0 persisting in class.The data in r0 is correct and gets the input arguments at r0=k0->ExecuteReader(), but nothing I do seems to get the output values. What am I missing about this?

System::Boolean rs::sp(System::String ^ ssp){

System::String ^ k0s0; bool bOK;

System::Data::SqlClient::SqlParameter ^ parami0;

System::Data::SqlClient::SqlParameter ^ parami1;

System::Data::SqlClient::SqlParameter ^ parami2;

System::Data::SqlClient::SqlParameter ^ paramz0;

System::Data::SqlClient::SqlParameter ^ paramz1;

System::Int32 pz0=0;System::Int32 pz1=0;

k0s = ssp;



paramz0=k0->Parameters->Add("@RETURN_VALUE", System::Data::SqlDbType::Int);




















ndx = -1;


if (ndx == -1){





for (iG1_20=0;iG1_20<r0nf;iG1_20++){





if (psv0[iG1_20]=="int") {pai0[ndx,pai0ndx]=System::Convert::ToInt32(r0->GetValue(iG1_20));pai0ndx++;}

if (psv0[iG1_20]=="float") {pad0[ndx,pad0ndx]=System::Convert::ToDouble(r0->GetValue(iG1_20));pad0ndx++;}



else {



for (iG1_20=0;iG1_20<r0nf;iG1_20++)

{ this->pas0[ndx,iG1_20]=System::Convert::ToString(this->r0->GetValue(iG1_20));

if (psv0[iG1_20]=="int") {pai0[ndx,pai0ndx]=System::Convert::ToInt32(r0->GetValue(iG1_20));pai0ndx++;}

if (psv0[iG1_20]=="float") {pad0[ndx,pad0ndx]=System::Convert::ToDouble(r0->GetValue(iG1_20));pad0ndx++;}











return true;


View Replies View Related

Webservice Task Input

I want to dynamically pass values to the input value of a Web method to return values to a webservice.

View Replies View Related

Stored Procedures Management - Keeping Input Parameters Updated

Hi everyone

I have just starting creating some stored procedures for our system and have a question related to management of these.

When using input parameters using the following syntax:

CREATE PROCEDURE sp_someInputProcedure
@Username as varchar(16)
@Password as varchar(12)
@Name as varchar(50)
@Address as varchar(60)
@Zip as int
@City as varchar(30)

This is all well and good, but what if I make a change in the datamodel - for instance changing a datatype or the length of a varchar - do I need to remember to manually update all stored procedures that uses these columns/variables?

Seems like a bit of a hazzle. Is there an easier way to do this?

Many thanks,

Stian Danielsen

View Replies View Related

1 SP With Dynamic Input Parameters And Multiple Rows As The Source Of The Query

How can I run a single SP by asking multiple sales question eitherby using the logical operator AND for all the questions; or usingthe logical operator OR for all the questions. So it's alwayseither AND or OR but never mixed together.We can use Northwind database for my question, it is very similarto the structure of the problem on the database I am working on.IF(SELECT OBJECT_ID('REPORT')) IS NOT NULLDROP TABLE REPORT_SELECTIONGOCREATE TABLE REPORT_SELECTION(AUTOID INT IDENTITY(1, 1) NOT NULL,REPSELNO INT NOT NULL, -- Idenitifies which report query this-- "sales question" is part ofSupplierID INT NOT NULL, -- from the Suppliers tableProductID INT NOT NULL, -- from the Products table, if you choose--a ProductID, SupplierID is selected also by inheritenceCategoryID INT NOT NULL, -- from the Categories tableSOLDDFROM DATETIME NULL, -- Sold from which dateSOLDTO DATETIME NULL, -- Sold to which dateMINSALES INT NOT NULL, -- The minimum amount of salesMAXSALES INT NOT NULL, -- The maximum amount of salesOPERATOR TINYINT NOT NULL -- 1 is logical operator AND, 2 is OR)GOINSERT INTO REPORT_SELECTIONSELECT 1, 1, 2, 1, '1/1/1996', '1/1/2000', 10, 10000, 1 UNION ALLSELECT 1, -1, -1, 1, '1/1/1996', '1/1/2000', 10, 1000, 1You can ask all kinds of sales questions like:1-I want all employees that sold products from supplierID 1(Exotic Liquids), specifically the ProductID 2 (Chang) from theCategoryID 1 (Beverages) between Jan 1 1996 to Jan 1 2000 and soldbetween $10 and $10000 - AND for my 2nd sales question2-I want all employees that sold CategoryID 1 (beverages) betweenJan 1 1996 to Jan 1 2000 and sold between $10 and $1000I want to get the common result of both questions and find outwhich employee(s) are in this list.Here are some of the points:1-I want my query to return the list of employees fitting theresult of my sales question(s).2-If I ask three questions with the logical operator AND, I wantthe list of employees that are common to all three questions.3-If I ask 2-3-4. questions with the logical operator OR, I wantthe list of employees that are in the list of the 1st "successful"sales question (the first question that returns any employee isgood enough)4-You can ask all kind of sales question you want even if theycontradict each other. The SP should still run and returnnothing if that is the case.5-Let's assume you can have the same product name from the samesupplier but under different categories. So entering a ProductIDshould not automatically enter the CategoryID also; whereasentering the ProductID should automatically enter its SupplierID.6-SOLDFROM, SOLDTO, MINSALES, MAXSALES, OPERATOR are mandatoryfields, you can't leave them NULL7-SupplierID, ProductID and CategoryID are the dynamic inputparameters, there can be 5 different combinations to choose from:a-SupplierID onlyb-SupplierID and a ProductID,c-SupplierID and a CategoryIDd-SupplierID, ProductID and a CategoryIDe-CategoryID onlyf-Any time you choose a ProductID, the SupplierID valuewill be filled automatically based on the ProductID'srelationshipg-Any of the three values here that is not chosen by theuser will take a default value of -1 (meaning return ALLfor this Column, in other words don't filter by this column)The major problem I have is I can't use dynamic SQL for choosingthe three dynamic columns as the 2nd row of records would have adifferent selection of dynamic columns (at least I don't know howif the solution is dynamic SQL). The only solution I can think oflooks pretty bad to me. I would use a cursor, run each row at atime, store a TRUE, FALSE value to stop processing or not andstore the result in another detail table. Then if all ANDquestions have ended with TRUE do a union of all the result andreturn the common list of employees. It sounds pretty awful as anapproach. I am hoping there's a simpler method for achieving this.Does anyone know if any SQL book has a topic on this type ofquery? If so I'll definitely buy the book.I appreciate any help you can provide.Thank you

View Replies View Related

Sniffing StoredProc Input Parameters For General Error Handling


id beg for a hint if our idea of a general dynamic CATCH handler for SPs is possible somehow. We search for a way to dynamically figure out which input parameters where set to which value to be used  in a catch block within a SP, so that in an error case we could buld a logging statement that nicely creates a sql statement that executes the SP in the same way it was called in the error case. Problem is that we currently cant do that dynamically.

What we currently do is that after a SP is finished, a piece of C# code scans the SP and adds a general TRY/CATCH bloack around it. This script scans the currently defined  input parameters of the SP and  generates the logging statement  accordingly. This works fine, but the problem is that if the SP is altered the general TRY/CATCH block has to be rebuildt as well, which could lead to inconstencies if not done carefully all the time. As well, if anyone modifies an input param somewhere in the SP we wouldnt get the original value, so to get it right we would have to scan the code and if a input param gets altered within the SP we would have to save it at the very beginning.

So the nicer solution would be if we could sniff the input param values dynamically on run time somehow, but i havent found a hint to do the trick.....

Any tipps would be appreciated...


View Replies View Related

Copyrights 2005-15, All rights reserved