Hello
I am trying to call a Web Servie within a SSIS package. I want to pull a row at a time from a SQL Server 2005 table and pass this data values onto the web service.
Any help is highly appreciated!
Kind Regards
Gayatri
I have created a SOAP/HTTP end point which returns .NET DataSet ( ... FORMAT=ROWSETS_ONLY ... ). Then I created a proxy class using Visual Studio 2005 and wrote a cusumer .NET class in which I call that web service's method synchronously. Everything is Ok but when I call asynchronously "TargetInvocationException" is raised. I need urgent solution to this problem.
Thanks !
I'm trying to return Annotations from a BSM scorecard by calling the PMService.asmx web service. I can successfully call operations like "GetScorecards" that require no parameters...but I keep getting the same error when trying to call "GetAllAnnotationsbyScorecardID"
Here is the Query:
<Query>
<SoapAction>http://www.microsoft.com/performancemanagement/scorecards/GetAllAnnotationsByScorecardID</SoapAction>
<Method Namespace="http://www.microsoft.com/performancemanagement/scorecards/" Name="GetAllAnnotationsByScorecardID">
<Parameters>
<Parameter Name="scorecardID">
<DefaultValue>7e3b541b-51db-4ded-b8f3-f7e8f6657c42</DefaultValue>
</Parameter>
</Parameters>
</Method>
</Query>
Here is the XML error returned:
<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlnsoap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<soap:Fault>
<faultcode>soaperver</faultcode>
<faultstring>Server was unable to process request. --> scorecardID</faultstring>
<detail />
</soap:Fault>
</soap:Body>
</soap:Envelope>
Am I structuring the Query the right way? Not sure if I'm a passing the guid correctly...I got the scorecard guid from the result set of calling "GetScorecards"
Is it possible to call a WCF service from a SQL CLR.
We have had success calling standard web services by including a Proxy in the Assembly but I wondered if it's possible to call out to a WCF service as the enpoint configuraion is done with an external XML file and I don't know if this will work in the same way. We may want to alter the wcf configuration depending on where the service is sitting, same box or remote.
Please do not respond with 'why do you want to do this' the reson we want to do this is because we use Service broker to recieve incoming messages and want to trigger a stored procedure to do some work on an external service when it recives some data.
Cheers,
Darren
Hi,
Is it possible to call a Web Service using SSRS and bind the dataset to the the table in the Report? Please share your views.
Thanks and Warm Regards
Saanj
I have a table called employee_punch_record that we use to store employee time clock punches.
The columns are:
employeeid,
punch_timestamp,
punch_type (In / Out),
closed (bit used as status for open or closed pay periods),
ident
Here are some examples of a record:
bkingery62015-10-06 16:59:04.000In0
bkingery72015-10-06 16:59:09.000Out0
bkingery82015-10-06 16:59:13.000In0
bkingery92015-10-06 18:22:44.000Out0
bkingery102015-10-06 18:22:46.000In0
bkingery112015-10-06 18:22:48.000Out0
bkingery122015-10-06 18:22:51.000In0
tfeller52015-10-05 17:00:05.000In0
We are using SQL Server 2008 as our database and use Access as a GUI. I am looking to create a form in Access where employees can access their time card and request changes from management. I want to use the format from the attached screen shot for the form. I pretty much know how to do it all, the only point of complication is trying to figure out the easiest way to get the transaction punch record data on employee_punch_record into a format where I can easily populate the form in the horizontal format you see in the screen shot.
I am not super strong in SQL, but figure I can do it using a formatting table of some sort. quick and easy way to move transaction records into a more horizontally oriented record?
Hi: I a SSIS beginner and am hoping this is an easy one. I am using a Foreach loop container with an in-memory ADO recordset as the enumerator. The recordset has 3 columns and one of them I wish to fixup data in by calling a web service task that returns an integer. I see how to call the web service and return the integer into a variable but am unsure how to approach putting that variable back into the in-memory ADO recordset? Should I even be trying to do it this way?
thanks!
Henry
I used the URL approach to call the reporting service. In the ASP page, I use javascript to open a new window to view the report. But when I close the new window and work on the original window. It seems that the DB connection to the SQL server is not working.
I need to create a stored procedure (SQL 2005). I did it in C# (Visual Studio). The .dll file (inside) uses web services of Project Server 2007 (I just need to log on, create a project and log out). I prepared 2 files (exactly the same code): dll an exe. the .exe works fine. the problem is with .dll file (stored procedure in SQL)...
by the way: I created serialization file with sgen tool.
the problems start when I am trying to register these 2 .dlls in SQL server. I can do that in unsafe mode only. when I am trying to register them in externall access (normal dll) and safe mode (serialization .dll) I am getting errors:
CREATE ASSEMBLY failed because method "add_QueueCheckInProjectCompleted" on type "ConsoleApplication11.ProjectWebSvc.Project" in external_access assembly "ConsoleApplication11" has a synchronized attribute. Explicit synchronization is not allowed in external_access assemblies
thus I did it in unsafe mode (both of them). than I created the procedure.
Now, I tried to call that procedure. I am getting error:
A .NET Framework error occurred during execution of user defined routine or aggregate 'Main':
System.Net.WebException: The request failed with the error message:
--
<html><head><title>Object moved</title></head><body>
<h2>Object moved to <a href="http://my_comp:22278/projectserver/_layouts/1033/error.aspx?ErrorText=Object%20reference%20not%20set%20to%20an%20instance%20of%20an%20object%2E">here</a>.</h2>
</body></html>
--.
System.Net.WebException:
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at ConsoleApplication11.ProjectWebSvc.Project.QueueCreateProject(Guid jobUid, ProjectDataSet dataset, Boolean validateOnly)
at ConsoleApplication11.Program.Main()
That means "Object reference not set to an instance of an object". the problem occurs when calling QueueCreateProject method
I dont understand that error. I wrote the same code for .dll and .exe. the .exe file is working ok (no errors) but .dll (as a stored procedure) shows this error
my code in C#:
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Net;
using PSLibrary = Microsoft.Office.Project.Server.Library;
namespace ConsoleApplication11
{
public class Program
{
public static void Main()
{
const string LOGINWINDOWS = "_vti_bin/PSI/LoginWindows.asmx";
string baseUrl = "http://localhost:22278/projectserver/";
CookieContainer cookies = new CookieContainer();
LoginWindowsWebSvc.LoginWindows loginWindows = new LoginWindowsWebSvc.LoginWindows();
loginWindows.Url = baseUrl + LOGINWINDOWS;
loginWindows.Credentials = CredentialCache.DefaultCredentials;
loginWindows.Login();
ProjectWebSvc.Project project = new ProjectWebSvc.Project();
project.Credentials = loginWindows.Credentials;
project.Url = baseUrl + "_vti_bin/psi/project.asmx";
ProjectWebSvc.ProjectDataSet dsProject = new ProjectWebSvc.ProjectDataSet();
ProjectWebSvc.ProjectDataSet.ProjectRow projectRow = dsProject.Project.NewProjectRow();
Guid projectGuid = Guid.NewGuid();
projectRow.PROJ_UID = projectGuid;
projectRow.PROJ_TYPE = 0;
projectRow.PROJ_NAME = "Jakis";
projectRow.PROJ_SESSION_UID = Guid.NewGuid();
dsProject.Project.AddProjectRow(projectRow);
Guid jobGuid = Guid.NewGuid();
project.QueueCreateProject(jobGuid, dsProject, false);
System.Threading.Thread.Sleep(5000);
loginWindows.Logoff();
project.Credentials = null;
}
}
}
any help will be appreciated. my email is: stro.na@interia.pl
Hi,
I need to call a webservice directly from a tigger or stored procedrue instead of creating a window service to read from a table then call the webservice .Is it possible ? if yes, please i need your support.
Thanks,
how I can load the CSV file data into the sql server table. I know there are ways like bulk insert and other to load the csv file data into the table. But in my case the table doesn't exist and has to be created at the run time. With simple insert in temp table we do like select * into #temp from tablename and that creates the temp table. So. I need something like that which create the temp table and load the data into it. because the CSV file would have different number of columns and names so I can not create the table structure in advance. I have to create the table at run time.Â
View 3 Replies View RelatedHello,
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.
Has anyone encountered this error? I get it when I run the installer (c#). I have seen a few posts on google and msdn, but still can't get past the error. I have tried uninstall/resinstalling the parser, registering from the command line, setting the property of the interop to "donotregister" and others with no luck.
Any ideas?
Thanks,
Lee
Posted the same message to the Service Broker forum. But the board is neither responsive nor active. So I am trying this forum. Hope someone can help.
I would like to call an SSIS package from a Service Broker Queue.
There is one way that I am aware of -
Using xp_cmdshell from within an activation stored procedure and using DTEXEC.
Is there a more elegant way of executing an SSIS package from within SSB?
Also,
I am not interested in writing a .NET external activator to process my
messages in the queue. I would like this operation to be strictly
database oriented. Having said this, I am also trying to avoid triggers
processing the messages in the queue.
Thank you!
I would like to call an SSIS package from a Service Broker Queue.
There is one way that I am aware of -
Using xp_cmdshell from within an activation stored procedure and using DTEXEC.
Is there a more elegant way of executing an SSIS package from within SSB?
Also, I am not interested in writing a .NET external activator to process my messages in the queue. I would like this operation to be strictly database oriented. Having said this, I am also trying to avoid triggers processing the messages in the queue.
Thank you!
The Web Service Task seems to support calling methods using parameters but not (as far as I can see) using the Document/Literal calling convention. Is this correct? Is this likely to change in the future?
Thanks
*** Campbell
I am trying to call a SSIS package from a web service hosted on the same machine as the package file is sitting. All that the package does is a simple Execute SQL task with one datasource connection.
I have set impersonation as true. When I run the package from the web service on the virual port (through Visual studio IDE) it runs fine.
http://localhost:4609/WebServiceRunSSIS/LaunchSSISPackageServiceCS.asmx
In the above case I don't think impersonation means anything coz it runs successfully even when there is no impersonation set!
I then run the same service (asmx page) from the browser after making it a web service on my machine
http://localhost/WebServiceRunSSIS/LaunchSSISPackageServiceCS.asmx
I get an error indicating package failure.
I have enabled logging in SSIS. The package log indicates the following:
Failed to acquire connection "APNetDS". Connection may not be configured correctly or you may not have the right permissions on this connection.
Effectively, my web service impersonated account (which is my admin account) is not being authenticated for any of the db connection (uses windows integrated authentication) and I find that odd. To impersonate, I have set the Impersonation to true in the web.config file. The authentication mode is windows.
Please tell me what bit of security I am missing? In case if it helps, the database connection is to my local database and hence should not deny access to my own account. And yes, I think my impoersonation is working because when I debug, the user.identity shows my user id.
Thank you,
Sumeet
I am trying to call a stored procedure from an asp.net application. If I execute the stored procedure in Query Analyzer it takes 1 second to execute, but when I am trying to call it from c# code it times out.
Ideas?
Thanks in advance.
When running an integration services package from a windows service I get the "Object ... has been disconnected or does not exist at the server." exception after aproximately six minutes of execution.
This is *not* my windows service failing. I can loop indefinately while tracing to a log file within the service and it will run forever. While calling the mypackage.execute(...) method however, after six minutes (give or take) the exception is thrown...
my code looks something like this:
<code>
dim foo as Microsoft.SqlServer.Dts.Runtime.Application
mypackage = foo..LoadPackage(strimportPkgFilename, pkgevents)
results = myPackage.Execute(Nothing, Nothing, pkgevents, Nothing, Nothing)
</code>
<error>
A first chance exception of type 'System.Runtime.Remoting.RemotingException' occurred in mscorlib.dll
Exception in: frmMyForm.DoImports
Message: Object '/b76f98a0_5bd9_49d8_a524_eeb49d55b303/bqbhkjnaofq_ifr_cwz+srid_1.rem' has been disconnected or does not exist at the server.
</error>
oddly, this same code works perfectly if I run it within a windows form application no matter how long it takes.
It also runs fine if the package can complete in under six minutes.
Any suggestions?
Mark
As part of my SSIS package, a list of sites is created that need to be created on a remote machine. let's say 1000 sites. I need to pass this list to a web service so web service sitting on that machine creates these sites for me. MY SSIS package does not run frequently so I can sacrifice time a little bit to get better functionality.
I need to move the sites that are not created (for any reasons) by web service to another table and successfully created sites to another table, so I need to get confirmation for each site from the web service.
Which option is better?
1) Calling web service for every single record (site) and get the confirmation and then based on the confirmation I move the records accordingly. I know this might be very time consuming, but as I said my SSIS package might only run every six months
OR
2) Sending records to a web service in a batch and get the result. I don€™t know how to do this though.
Any help or better solution is appreciated.
I had try calling a function, that call a store procedure, repeatly using a for loop, but I notice it will only get the expected part_id in the first time, and return an empty string sub-sequentially without throwing an exception. So I had try using a sql query instead, but the same thing happen. Below is my function, can you point out to me what's wrong?
My original version that calls a store procedure
Public Shared Function getPartId(ByVal part_supplierserialnumber As String) As String
Dim mySqlCommand As New SqlCommand
Dim mySqlConnection As SqlConnection = New SqlConnection(GetERATSConnectionString())
Dim myPart_id As String
mySqlCommand.CommandType = CommandType.StoredProcedure
mySqlCommand.CommandText = "getPartId"
mySqlCommand.Connection = mySqlConnection
mySqlCommand.Parameters.Add(New SqlParameter("@part_supplierserialnumber", part_supplierserialnumber))
Try
mySqlConnection.Open()
myPart_id = mySqlCommand.ExecuteScalar()
Catch ex As Exception
myPart_id = ""
Finally
mySqlConnection.Close()
mySqlConnection.Dispose()
End Try
Return myPart_id
End Function
My Store procedure
create procedure getPartId@part_supplierserialnumber as nvarchar(50)as
select top 1 part_id from tblPtSingapore where part_supplierserialnumber = @part_supplierserialnumber order by part_datecreated desc
GO
The new version I tried which happen the same thing
Public Shared Function getPartId(ByVal part_supplierserialnumber As String) As String
Dim myPart_id As String
Dim strSql As String = "select top 1 part_id from tblPtSingapore where part_supplierserialnumber = '" & part_supplierserialnumber & "' order by part_datecreated desc"
Dim mySqlConnection As SqlConnection = New SqlConnection(GetERATSConnectionString())
Dim mySqlCommand As New SqlCommand(strSql, mySqlConnection)
Try
mySqlConnection.Open()
myPart_id = mySqlCommand.ExecuteScalar()
Catch ex As Exception
myPart_id = ""
Finally
mySqlConnection.Close()
mySqlConnection.Dispose()
End Try
Return myPart_id
End Function
I have a CLR stored proceudure than runs under 2 or 3 mins when run in management studio. But when the report calls the same SP, it runs in around 30 mins and the CPU usage goes to 90 or 100% and the sqlserver service uses massive amount of memory. Previously the report worked fine, only after made a few data driven subscriptions and redeployed the reports with minor changes that it began to happen.
Please help--Iam stumped.
There are no infinite loops in the report. I checked.
Hi
I´m a SysAdmin and I´m implementing ASSP (anti-spam) to use with an email server.
But the ASSP requires a txt file with all domains hosted in the server that are in a SQL Table of the server.
My questions is: Is there anything to make a real time TXT file with all the domains ?
maybe use trigger I dont know..
Is there a Tool/Utility/StoredProcedure/Query/View that can tell me the last time the data in a particular table was changed?
That change can be the result of either an ADD , INSERT, or DELETE statement.
I am using SQL Server 2005.
I am trying to write a stored procedure that will select information from a SQL table based on a specific time.
For example I have a name field and a time field, I want to return just the names that were created between a specific time frame. ex between 3pm and 4pm.
Any thoughts?
View 1 Replies View Related
Can anyone help me on this...
when i select data from table using select statement it takes huge amount of time....The table contains 7 million entries and when i select by mentioning a criteria it takes around 45 secs..The system has 4GB RAM and Dual Processing CPU. The select statement does not contain any grouping and all..
Will it take this much time to retrieve data.?.
The table does include an indexed field,
So can anyone help me on the different things i can do to make the retrieval faster?
Andy
Hi, all experts here,
Thanks for your kind attention.
I want to use time series algorithm to mine data from my case table and nested table. Case table is Date table, while nested table is the fact table. E.g, I want to predict the monthly sales amount for different region (I have region table related to the fact table), how can I achieve this?
Thanks a lot and I hope it is clear for your help and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
Hi, all experts here,
Thank you very much for your kind attention.
I am confused on key column of case table and key time column of nested table by using Time Series algorithm.
In my case, the case table structure is as below:
Territory key text (the ID is actually dimrisk_key, in this case, I use the name column binding to combine the Territory column of case table Dimrisks),
While the nested table structure is as below:
Cal_month key time (in this case, actually the ID is dimdate_key, again, I used name column bining property to bind the Cal_month to the ID)
So my question is, as the key column of case table has been set to be Territory, as a result, does the model training still cover all the cases (rows) based on the ID of the table?
Also, in the nested table, as the key time column has been set to Cal_month rather than Dimdate_key of the nested table, as a result, would the single series based on the cal_month?
Hope it is clear for your advices and help.
And I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
Hi,
I have a clustered SQL 2000.
for some reason, and I can't find why, the SQL service is very busy, mostly at 100% cpu.
sometimes when I check on the databases, I see that the files ( data, log), have free space, but when I look with Enterprise manager, view -> taskpad, I see "available space" 0Mb.
Please advice...
I'm trying to figure this out
I have a store procedure that return the userId if a user exists in my table, return 0 otherwise
------------------------------------------------------------------------
Create Procedure spUpdatePasswordByUserId
@userName varchar(20),
@password varchar(20)
AS
Begin
Declare @userId int
Select @userId = (Select userId from userInfo Where userName = @userName and password = @password)
if (@userId > 0)
return @userId
else
return 0
------------------------------------------------------------------
I create a function called UpdatePasswordByUserId in my dataset with the above stored procedure that returns a scalar value. When I preview the data from the table adapter in my dataset, it spits out the right value.
But when I call this UpdatepasswordByUserId from an asp.net page, it returns null/blank/0
passport.UserInfoTableAdapters oUserInfo = new UserInfoTableAdapters();
Response.Write("userId: " + oUserInfo.UpdatePasswordByUserId(txtUserName.text, txtPassword.text) );
Do you guys have any idea why?
I need help with the following field:
'Age @ TOS' = DATEDIFF (YY, patientprofile.birthdate, patientvisitprocs.dateofservicefrom),
PatientProfile.Birthdate - 01/01/2001
PatientVisitProcs.dateofservicefrom = 07/01/2001
In my report, I get a '0' as my Age @ TOS. What I would like to have is 7 months vs the '0' it is reporting. I would like to try accomplishing this without doing a function if possible. I know I used YY in my DATEDIFF. I only noticed this today because of a few patients in my DB were under 1 yr at the TOS.
I need to take a temporary table that has various times stored in a text field (4:30 pm, 11:00 am, 5:30 pm, etc.), convert it to miltary time then cast it as an integer with an update statement kind of like:
Update myTable set MovieTime = REPLACE(CONVERT(CHAR(5),GETDATE(),108), ':', '')
how this can be done while my temp table is in session?