I have a microsoft report that uses a dll to calculate a value. If I hard code the value, my report shows it. My method in the dll is connecting to a database in order to calculate a result. However, in order to connect to the database, I am using Microsoft.Practices.EnterpriseLibrary.
The error in my report is:
Could not load file or assembly 'Microsoft.Practices.EnterpriseLibrary.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.
I have added a reference in the report to the EnterpriseLibrary but this has not made a difference.
To overcome the inability to manage the sheet tab names when exporting from Reporting Services to Excel, is there anyway to include a short macro with the exported Excel workbook to Auto_Run macro when the Excel file is opened to rename the tabs?
Hello! I have the following problem. I developed CLR Stored Procedure "StartNotification" and deploy it on db. This sp calls external web service. Furthermore, this sp is called according with SQL Server Agent Job's schedule. On my PC SQL Server works under Local System account and this web service is called correctly (Executed as user: NT AUTHORITYSYSTEM). But on ther other server the following exception is raised during job running: Date 17.04.2007 16:42:10 Log Job History (FailureNotificationJob)
Step ID 1 Server MSK-CDBPO-01 Job Name FailureNotificationJob Step Name MainStep Duration 00:00:00 Sql Severity 16 Sql Message ID 6522 Operator Emailed Operator Net sent Operator Paged Retries Attempted 0
Message Executed as user: CORPmssqlserver. A .NET Framework error occurred during execution of user defined routine or aggregate 'StartNotification': System.Security.SecurityException: Request for the permission of type 'System.Net.WebPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed. System.Security.SecurityException: at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet) at System.Security.CodeAccessPermission.Demand() at System.Net. The step failed.
What is the reason of this behaviour? Unfortunately I do not have direct access to this server. I have the following guesses: 1) CORPmssqlserver may have not enough permissions to call web service 2) Something wrong with SQL Server account's permissions 2) Something wrong with SQL Server Agent account's permissions I will take the will for the deed. Thanks.
With the new improvments to the web service task, we can now use variables as arguments in web service calls. I am trying to setup a call to the amazon web service ECS. I am trying to do a simple sellerlookup. I have played with the settings and gotten nowhere. I get one of two error when I try to execute. I can always use a scripting task or write my own task, but I would like to use the built in task if it is possible. Has anyone used AWS with SSIS?
[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()".
or
[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: Method 'ProxyNamespace.AWSECommerceService.SellerLookup' not found.. 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()".
i have a list report some values of this are blank or null i want to avoid that spacing if it is null like name:jjj lastname kkk firstname address uuuu
i want it should be name jjj lastname kkk address uuuu
can someone point me in the right direction? I know how to call a stored procedure, but I can't seem to find code examples on how to call user defined functions.
I have some problem to need you all help. My problem is i have a Excel Worksheet with VBA coding, in VBA code it have modules,function and sub procedures. after that, i change the VBA coding in add-in file and save as .xla to make the user friendly.
But now, i need to use DTS to automatic to execute the excel file that contain VBA code every week. This is because the VBA in excel cannot automation excute by every week. So, i need use DTS to make it automation. The problem is i don't know how to use the DTS function to call the addin excel to execute? Which the connection and task compenent should i use? Or how do i paste the VBA code in DTS?
DTS is very new for me, are any help can provide me the solution? thanks
I have two databases with two identical tables in seperate physical locations. I want database B tables to be updated automatically when database A tables change. Is there a way to call a web service from SQL to make this happen? Or is there a better way to do this? I would really like it to get the rows that were modified and then copy only those rows to the other database tables. If anyone knows if this can be done please let me know. Thank you.
But when I create a SQL XML Webservice via an endpoint all I can find on the net is how to show the wsdl.
http://localhost/Contacts?wsdl
Ive created a method in the "Contacts" webservice called GetContacts. Why cant I construct a similar url to execute that method without having to write a vb.net wrapper for it?
Hi, I have a situation where I need to call a web service from the database (by an insert trigger on a table). Please let me know how I could do this. Thanks in advance Raj
Hi, I have a situation where I need to call a web service from the database (by an insert trigger on a table). Please let me know how I could do this. Thanks in advance Raj
I have been reading up on everything I can find on this subject and I am not clear if this is allowed within the SQL Server 2005 CLR. My function calls work within a Windows form project, but don't run when invoked from a SQL function. I don't get any errors or warnings when creating the assembly and functions within SQL Server, but when running via a select statement, the spid just hangs and I have to stop & restart the service to kill the process. I have been investigating the security settings for this assembly, but I think I have that covered via the RunTime Security Policy settings in the .NET Framework 2.0 Configuration tool.
Any insights, knowledge, or thoughts would be greatly appreciated.
Hi! Have a problem to execute xp_cmdshell with a proxy user in MSSQL2005 (9.00.2047.00). The error is:
Msg 15121, Level 16, State 10, Procedure xp_cmdshell, Line 1 An error occurred during the execution of xp_cmdshell. A call to 'CreateProcessAsUser' failed with error code: '1314'.
The proxy user is a local administrator on the sql server machine. The proxy user have €śReplace a process level token€? rights thru the group SQLServer2005MSSQLUSer (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=627221&SiteId=1).
I have even tried to use a domain administrator as proxy user, but the same error occur.
What is wrong? I have search the internet a lot and found that many people have this problem without any answer.
I would like to call a web service from a sql server 2000 s.proc/sql job, but if there is a better way please let me know. I'm using Captaris Workflow to create and manipulate workflow processes. I have a sql job that will create some pdf's. I want to call the web service that creates a new process in a step from that sql job. I have read about doing this with extended s.procs, but I'm trying to find any possible solutions. Essentially, the web method I want to call will insert several records into sql so I know I can at least do that. I would still like to know if I can make a call to the web service from the sql job. Thanks for any help.
I'm hoping someone has tried something similar before. I am trying to run an integration from a remote computer using a web service. I have set up the web service and the bog standard launch package routine works fine. What i'd like to be able to do is to launch a package and also specify any parameters associated with it. I have the following code which is identcal to the main launch sub with the exception of an extra argument (jagged array of varible name and value variablePair[4][2]). It also processes the variable array and sets them on the DTSPackage object.
[code] [WebMethod] public int LaunchSSISPackageWithVariables(string sourceType, string sourceLocation, string packageName, string[][] variableArray) { string packagePath; Package myPackage; Application integrationServices = new Application();
// Combine path and file name. packagePath = Path.Combine(sourceLocation, packageName);
switch (sourceType) { case "file": // Package is stored as a file. // Add extension if not present. if (String.IsNullOrEmpty(Path.GetExtension(packagePath))) { packagePath = String.Concat(packagePath, ".dtsx"); } if (File.Exists(packagePath)) { myPackage = integrationServices.LoadPackage(packagePath, null); } else { throw new ApplicationException("Invalid file location: " + packagePath); } break; case "sql": // Package is stored in MSDB. // Combine logical path and package name. //if (integrationServices.ExistsOnSqlServer(packagePath, ".", String.Empty, String.Empty)) if (integrationServices.ExistsOnSqlServer(packagePath, ".", "executeSSIS", "p4ssw0rd")) { //myPackage = integrationServices.LoadFromSqlServer(packageName, "(local)", String.Empty, String.Empty, null); myPackage = integrationServices.LoadFromSqlServer(packageName, "(local)", "executeSSIS", "p4ssw0rd", null); } else { throw new ApplicationException("Invalid package name or location: " + packagePath); } break; case "dts": // Package is managed by SSIS Package Store. // Default logical paths are File System and MSDB. if (integrationServices.ExistsOnDtsServer(packagePath, ".")) { myPackage = integrationServices.LoadFromDtsServer(packagePath, "localhost", null); } else { throw new ApplicationException("Invalid package name or location: " + packagePath); } break; default: throw new ApplicationException("Invalid sourceType argument: valid values are 'file', 'sql', and 'dts'."); }
//Variables var = myPackage.Variables; //foreach (string key in variablePairs.Keys) //{ // var[key].Value = variablePairs[key].ToString(); //}
Variables var = myPackage.Variables;
for (int i = 0; i < variableArray.Length; i++) { var[variableArray[0]].Value = variableArray[1].ToString(); }
return (Int32)myPackage.Execute(); } [/code]
For some reason the above code runs and passes back the value that it succeeded. Yet the package doesn't do what it should. Does anyone have any ideas as to why this might be the case or even a better way to pass the variables.
I currently have the fllowing Stored Procedure. When I pass the the Url of the web service in the parameters, I'm having a sp_OAMethor read response failed error.
I don't know how to pass the parameter as well as the name of the function in the Web Service I'm calling. Maybe I'm all wrong here with this code too?
Thanks for any help.
ALTER PROCEDURE [dbo].[pTAPServiceWeb]
@sUrl varchar(200),
@response varchar(8000) out
AS
DECLARE @obj int
DECLARE @hr int
DECLARE @status int
DECLARE @msg varchar(255)
EXEC @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
I am trying to call a web service using the web service task and passing it variables, but even when I set variables to be Strings/any other data type, they always get passed out as objects. The call is then rejected based on the fact that there is no web service that accepts those parameter types. If there is a workaround to this problem, that would be great, otherwise I think I'm going to have to call the web services in a script task. Would someone please give me an example of the steps necessary to call a web service from the script task?
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
I am trying to use SSIS to update an AS400 DB2 database by calling a stored procedure on the AS400 using an OLE DB command object. I have a select statement running against the SQL Server 2005 that brings back 20 values, all of which are character strings, and the output of this select is piped into the OLE DB command object. The call from SSIS works just fine to pass parameters into the AS400 as long as the stored procedure being called does not have an output parameter defined in its signature. There is no way that I can find to tell the OLE DB command object that one of the parameters is an output (or even an input / output) parameter. As soon as one of the parameters is changed to an output type, I get an error like this:
Code Snippet
Error: 0xC0202009 at SendDataToAs400 1, OLE DB Command [2362]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF.
Error: 0xC0047022 at SendDataToAs400 1, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Command" (2362) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
Error: 0xC0047021 at SendDataToAs400 1, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0202009. There may be error messages posted before this with more information on why the thread has exited.
Information: 0x40043008 at SendDataToAs400 1, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x40043009 at SendDataToAs400 1, DTS.Pipeline: Cleanup phase is beginning.
Task failed: SendDataToAs400 1
Warning: 0x80019002 at RetrieveDataForSchoolInitiatedLoans: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Warning: 0x80019002 at Load_ELEP: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Load_ELEP.dtsx" finished: Failure.
I really need to know if the call to the AS400 stored procedure succeeded or not, so I need a way to obtain and evaluate the output parameter. Is there a better way to accomplish what I am trying to do? Any help is appreciated.
I have a SQL Server Agent job with a step to execute an SSIS package. The package has as one of it's connection managers the Connection Manager For Excel Files. When I run the job it fails every time. I have implemented logging and in the OnError event it states "The AcquireConnection method call to the connection manager "LOBMappingExcel" failed with error code 0xC0202009".
I get this error when trying to run xp_cmdshell. I have turned on xp_cmdshell via sp_configure and verified it is on (set to 1) by running sp_configure again. The login trying to run xp_cmdshell has sysadmin privileges. If anyone has a solution please let me know.
I've seen the previous threads on this (although maybe not all of them). However, i don't think I'm getting the error for the same reason. The full error I'm getting is:
We have a requirement to call a Web Service method from SSIS where the method accepts a single parameter that is derived from a database recordset and can exceed 4k characters. From the little we know about the Web Service task it is limited to only accepting direct input and variables. Does anyone have any suggestions to overcoming this issue? Any detailed information in possibly calling the Web Service from script or using an object variable would be extremely appreciated.
I just started looking into SB about a week ago, so my question is likely to be pretty lame. However, that's not going to stop me from asking it :-)
We're trying to do something similar to what you're describing here. How do you "reset" a dialog timer? Why would you need to deal with resetting timers (or with timers at all for that matter) in the event of a success? Couldn't you rewrite the logic to be as follows?
begin transaction receive message if message is web request save state of request (http address, caller etc) else if message is retry timer message load state of request endif commit do the web request (no transaction open) if success begin transaction send back response end conversation commit else set a retry timer on the dialog (say 1 minute) using BEGIN DIALOG TIMER endif
Also, when you set the retry timer you have to associate it with the saved request state, right? Otherwise, how will the service know which request to load on receipt of the timer message?
We are using the whole BI-package from Microsoft - from SQL, DTS-package,Raporting Service and Analyze Service.
It should be very helpful to be able to create a metadata databases where you could find all releations between different objects (tables,views,reports,cubes,DTS-package,Databases.
Just to get answer for: 'where is view xxx used', 'what are Report xxx depending upon'.
While everything exists in different SQL databases it should possible to do.
I get this error (connection closed) in web applications, whenever SQL Server database Service is restarted. When I run the application again, the error disappears. Granted that there are lots of users and Database gets restarted every night is not helping.
Is there any setting in the JDBC driver properties, where I can tell it to open a closed connection (like a retry count), rather than fail the first time and work the second time.
We use 1. SQL SERVER 2005 SP2, 2. Websphere Protal Server 5.1 3. SQL SERVER 2005 JDBC driver 1.1(com.microsoft.sqlserver.jdbc.SQLServerConnectionPoolDataSource.)
I am wondering if connection pool driver is causing this problem. I do not care about connection pooling, but Websphere doen not seem to like any other JDBC Driver. I don't mind switching to another driver as the last option.
Error is : [11/27/07 17:01:59:331 MST] 7202440d WSRdbManagedC W DSRA0080E: An exception was received by the Data Store Adapter. See original exception message: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.getCatalog(Unknown Source) at com.ibm.ws.rsadapter.spi.WSRdbManagedConnectionImpl.getCatalog(WSRdbManagedConnectionImpl.java:676) at com.ibm.ws.rsadapter.spi.WSRdbManagedConnectionImpl.introspectSelf(WSRdbManagedConnectionImpl.java:808) at com.ibm.ws.rsadapter.FFDCLogger.introspect(FFDCLogger.java:169) at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.introspectSelf(WSJdbcConnection.java:1253) at com.ibm.ws.ffdc.IntrospectionLevelMember.getNextMembers(IntrospectionLevelMember.java(Compiled Code)) at com.ibm.ws.ffdc.IntrospectionLevel.getNextLevel(IntrospectionLevel.java:181) at com.ibm.ws.ffdc.ObjectIntrospectorImpl.dumpContents(ObjectIntrospectorImpl.java:67) at com.ibm.ws.ffdc.ObjectIntrospectorImpl.dumpContents(ObjectIntrospectorImpl.java:51) at com.ibm.ws.ffdc.IncidentStreamImpl.introspectAndWrite(IncidentStreamImpl.java:396) at com.ibm.ws.ffdc.IncidentStreamImpl.introspectAndWriteLine(IncidentStreamImpl.java:632) at com.ibm.ws.ffdc.DiagnosticEngine.dumpObjectAndStack(DiagnosticEngine.java:301) at com.ibm.ws.ffdc.DiagnosticEngine.processIncident(DiagnosticEngine.java:147) at com.ibm.ws.ffdc.FFDCFilter.filterEngine(FFDCFilter.java:428) .......lots of lines
I have successfully developed a Transact-SQL stored procedure which calls a CLR function, which calls a web service. I am using a Visual Studio 2005 SQL Server Project to perform the necessary magic.
Is there a neat way to deploy this stuff to a production server?
It is bad enough deploying the sp and function, but the web server proxy also needs to be changed to refer to the production web service.
Is there a way to modify the RSReportServer.config file with a Web Service call. We want to install an app and have it modify the Renders. We want to remove the Tiff export for example. We also want to add simple headers to Excel. Right now we have to open the file and then make our edits.
The package runs fine if I run it by itself. Unfortunately we want to call it from another package and when we do so it fails everytime with the following error; "AcquireConnection method call to the connection manager "BPCSF" failed with error code 0xC0202009." This package is calling an AS400 and pulling data from it into a staging environment. The task that is failing is 4th task on the list and is the 3rd task to call the AS400 environment. If I need to provide more information I would be happy to do so. Any help would be greatly appreciated. Thanks!
In our project, we would like to use the same data source for our analysis service database cubes and for our reporting service reports.
I created the analysis service project first, deployed successfully. When trying to setting up the data source in the report model project, I selected the "create a data source based on another object", and then selected the "create a data source based on an analysis service project". However, there is no analysis service project to select, and no browse button to see where the reporting service is looking for analysis service project either.
I have tried creating a new analysis service project with data source views, cubes, dimensions and all the stuff, but still cannot see the analysis service project in the drop down box to be selected for my reporting model project data source.
As I am fairly new to the reporting service, I'm sure I'm missing something, but couldn't find much information in the help or on the web. Any suggestion would be much appreciated.
