I am trying to design package which would copy all tables and stored procedures from one server to another. It used to be simple task in SQL 2000, there was wizard. However the new wizard does not work...
I wrote SSIS package, but when I execute I get the following error: "Can not apply value null to property Login..."
Any idea? It seems to me that this new SQl2005 is not that simple to use like 2000. 2000 was a great product. I am considering to rollback to 2000. Very frustrated with the new one...
when i right click and execute the package from the Management Studio, it runs fine, everytime. When i schedule a job to execute it, it fails, everytime.
"The package execution failed. The step failed."
i have made sure that the package and job ran using credentials with more than enough access to everything. Still, with no luck. PLEASE! I am at my wits ends here, and the boss is getting antsey...
I just deployed my first interface and below is the error message
failure". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "TCP Provider: An existing connection was forcibly closed by the remote host. ". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'EUROPExxx123$'.". End Error Error: 2008-05-09 11:01:13.00 Code: 0xC020801C Source: Load New Data Into TransDat... The package execution fa... The step failed.
The interface is just to copy the data from 1 database to another based on a date. I suspect that i could have missed a step during the deployment, can someone tell me if the error is caused me forgetting to configure something during the step up of the job or is it network related
I have a dts package migrated from sql server 2000 to sql server 2005 clustered server using migration wizard without any problem.
I have created a new job on sql server 2005 and one of the steps involves executing the SSIS package. It keeps failing with the error message "package execution failed". I have logged in here as a domain administrator (as also a local administrator).
when I try to run a package as Job I receive the following error
Executed as user: SITAsqlserveragent_svc. The package execution failed. The step failed.
Has someone an idea how I can get more detailed information about WHY the job failed? Tha package was created in BIDS and imported into MSDB. Running the package from the Integration Services context menu works, but starting it as a job from SQL Server Agent fails. Any idea or hint what the reason could be or how I can get more information?
I have a SSIS package which run well when stand-alone. However it was failed when executed from .NET web application. It just simply return Failure. Code snip below:
Dim packagekgResults As DTSExecResult ......... packagekgResults = package.Execute()
I created a package which passes some infornmations( through parameters) to its child package.
I need to do some processing in parent package based on execution status of child package.i.e.
if child fails then some operation and if child succeeds then other operation.
To determine the status of execution of child package I am using two differnt constraint ..one constraint is having value "Success" and other having value "Failure".
My problem is that when child packge is executed successfully the constraint with value = "Success" works properly but when child fails the constraint with value "Failure" does not work.
I've built SSIS package and made a job to execute it automatically but it always returns an error. The job returns OK but when we looked at the Log File viewer, it conatins this error log :
Key not valid for use in specified state
Failed to decrypt protected XML node "DTS Password" with error
What's wrong with the package ? Thanks in advance.
I have a group of reports using a shared datasource. Going to the preview of the report works fine in the report designer, but when I try and view it from a browser (deployed on a website), it gives the error:
"An error has occurred during report processing.
Query execution failed for data set 'DataSet1_ticketInfo'.
Failed to parse SQL.[long sql query here]"
If there's a problem with it, I don't get why it works in preview mode. I'm using SQL server 2005.
I created the an assembly and stored procedure using the following steps:
sp_configure 'clr enabled', 1 GO RECONFIGURE GO
alter database TEST set trustworthy on
CREATE ASSEMBLY ClrWebServices FROM 'D:Dataclr_4.dll' WITH PERMISSION_SET = UNSAFE; GO
CREATE ASSEMBLY [ClrWebServices.XmlSerializers] FROM 'D:Dataclr_4.XmlSerializers.dll' WITH PERMISSION_SET = SAFE; GO
CREATE PROCEDURE InsertLocation(@city nvarchar(200), @state nvarchar(200), @country nvarchar(200)) AS EXTERNAL NAME ClrWebServices.StoredProcedures.clr_4 GO
After this when i am trying to execute the procedure InsertLocation
EXEC InsertLocation 'Sarasota','Florida','USA'
I am getting the following exception..
Msg 6522, Level 16, State 1, Procedure InsertLocation, Line 0 A .NET Framework error occurred during execution of user-defined routine or aggregate "InsertLocation": System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it System.Net.Sockets.SocketException: at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress) at System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP) at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception) System.Net.WebException: at System.Net.HttpWebRequest.GetRequestStream() at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) at ClrWebServices.Test.TerraService.GetPlaceFacts(Place place) at StoredProcedures.GetLocationImage(SqlString city, SqlString state, SqlString country) at StoredProcedures.clr_4(SqlString city, SqlString state, SqlString country)
I searched in the google and found one answer in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=76222&SiteID=1 suggesting use
ALTER ASSEMBLY ClrWebServices WITH PERMISSION_SET=EXTERNAL_ACCESS
when i executed i got the exception
Msg 6213, Level 16, State 1, Line 1 ALTER ASSEMBLY failed because method "add_ConvertLonLatPtToNearestPlaceCompleted" on type "ClrWebServices.Test.TerraService" in external_access assembly "clr_4" has a synchronized attribute. Explicit synchronization is not allowed in external_access assemblies.
I have a job that executes an ssis package. The SSIS package executes successfully in business intelligence Studio however when I make it into a job I get the error message in the topic title. The log file isnt any more helpful which simply says "The package execution failed. The step failed."
I have several reports that are working great on my report server. However, one of my reports "ReportB" is launched as a hyperlink from "ReportA" through the use of a custom function which uses a little javascript to simply open a new browser to the URL of ReportB along with passing a parateter through that URL. I can get it all to work great on my local machine I'm using for development. But when I deploy it to my production server I get the following problems (Please note ReportA works fine, it is ReportB that gives me these problems):
1. When running report in web browser on my local machine - An error has occurred during report processing. Query execution failed for data set 'FDQW'. For more information about this error navigate to the report server on the local machine, or enable remote errors.
2. When running the report in a web browser on the report server itself (also is the SQL server containing the data) - An error has occurred during report processing. Query execution failed for data set 'FDQW'. Must declare the scalar variable "@DocNo".
Like I said above... when I deploy to my local RS on my development machine I get neither of the above problems. I have tried changing SQL roles, RS Execution accounts, database connection credentials, all kinds of permission realated things and can't get it to work.
I am not using a stored procedure as a data source. Here is a copy of my data set query if it helps:
SELECT DocumentHeaders.DocNo, DocumentHeaders.SoldToCompany, DocumentHeaders.SoldToContact, DocumentHeaders.SalesRep, DocumentHeaders.DocName, DocumentHeaders.DocDate, DocumentHeaders.GrandTotal, DocumentItems.LineNumber, DocumentItems.Description, DocumentItems.QtyTotal, DocumentItems.ExtendedPrice FROM DocumentHeaders INNER JOIN DocumentItems ON DocumentHeaders.ID = DocumentItems.DocID WHERE (DocumentHeaders.DocNo = @DocNo) AND (LEN(CONVERT(varchar, DocumentItems.Description)) > 0)
We are using SQL 2005 server for generating reports.When we ran the reports it taking so much time after some time it shows this error:---
ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set ---> System.Data.SqlClient.SqlException: A severe error occurred on the current command. The results, if any, should be discarded.
When i execute a sql statment, i get this error: System.Data.SqlClient.SqlException: Execution of a full-text operation failed. A clause of the query contained only ignored words.
i tryed to execute same statment in sql query analyzer...in analyzer it works, but in .net returns this error. Have anybody any clue how to fix this error? i'm using ms sql database, SqlDataAdapter and datatable.
This is the most frustrating error I've ever encountered.
I can get no more info from sql server besides "server execution failed". It gives this error when starting the snapshot, distribution, and log reader agents.
This server has been working in this configuration for over a year. We made no changes except restarted the server. This is a windows 2003 server with sql server 2000 running all the latest patches and windows updates.
I have various ssis packages which need to schadule through SQL server agent, I manage to schadule those packages but every time SQL server agent execute those packages automaticlly they failed ... I went to package log and it gives error that Step one failed, on the other hand if i execute those packages through "Execute Package Utility", they run without any error ...
I got this Error, Query Execution for Dataset 'Source'.
When i try to run a report that is actually a drillthrough from another report. It runs fine in Report Designer, and when i deploy to my Local server. But when i deploy it to my virtual Report Server, I get this error messege. Why is it doing this? and where can i see errors for this type of stuff, so i can figure this out. This uses the same Datasource as the report linked from it. That report works fine, why wont this one? any ideas?
Hi All, I have a report which has two subreports in a table.First Sub report1 ,then a detail section and sub report 2. Sub report1 runs fine.But sub report2 gives error in the main report"
Query execution failed for data set 'DataSet_azshdsd30'. For both the sub reports parameters passed from the main report are same(2 parameters). When I run sub report2 indivisually it gives right result.But not in the main report. Also when I put Subreport 2 in place of subreport 1 I do get the results in the main report. Any idea,any body?? Thanks.
I am receiving an error on my master package that executes a number of other packages. The individual packages work fine when executed by themselves. However, I am getting the following error when I attempt to execute it from another package:
Error: Failed to acquire connection "conneciton". Connection may not be configured correctly or you may not have the right permissions on this connection.
I have a logon trigger on a SQL Server 2008 R2 Express Advanced production database to prevent remote logons. The trigger works fine. When I need to connect via my local machine, remotely, I connect via a VPN, can connect with SSMS and do whatever I need. However, if I use a linked server on my local machine (still connected via VPN), I receive the logon error
Msg 17892, Level 14, State 1, Line 1 Logon failed for login 'sa' due to trigger execution.
The trigger is below and it logs any failures, except for the linked server.
If I disable the trigger, the linked server connects ok.
CREATE TRIGGER [tr_MasterLogon] ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON AS BEGIN DECLARE @ClientAddress varchar(48) = (SELECT client_net_address
Hello, I've configured SQL Server 2000 for merge replication and am trying to start the snapshot agent for the publication and after about 20 minutes of it trying to start, it gives up with this error.
Server execution failed
There is no more information in the event viewer. I've tried it with a bunch of different subscriptions. SQL Server and SQL Agent are running under a local account which has access to the replcation data directory. SQL Agent is running.
I have already created package which loads a text file to database.I wanted to execute that package based on the availability of the file using Visual Basic,Perl or VBSCRIPT whichever is easier.Please advise.Thanks
I wrote a stored procedure that calls a DTS package that imports a file into a SQL Server table, then executes some commands on that table. How can I tell that the DTS package has completed, so that I can continue with the next command? Here is my code:
-- Run the DTS package EXEC @Result = msdb..sp_start_job @job_name = 'MyJob' WAITFOR DELAY '00:00:02'
-- Call the table that the package created SELECT * FROM <imported_table> WHERE ...
I had to insert a WAITFOR statement to make this work. Any other suggestions as to how to know when to continue?
Is there a method to either: A) Kick of a DTS package directly from a stored procedure without shelling out to the DTSRun facility. B) Kick off a scheduled job containing a DTS task from a stored procedure?
I need help from you. I am working on SSIS packages for ETL purpose. The version of SQL Server i am using is SQL Server 2005.
In Brief , the working of current ETL is as follows.
In ODS database i have 2 tables i.e Table_A & Table_B which gets loaded from another 2 staging tables A & B. And using this 2 tables data will be loaded into a target table i.e Trg_A.
The ETL packages are executed by stored procedures by creating a job within the stored procedure.
The loading of the trg table is little tricky. Before that loading of Table_A is implemented in a single SSIS package. and loading of Table_B is been implemented in another SSIS package.
In the trg table there are two columns which will be getting updated as and when each table is loaded. so for the first time if i run the package which is resposible for loading Table_A, it loads values into Table_A and once done it will updates (col1) in the target table.
Once after the complete of the execution of Package1. Now i will kick off the second ssis package which loads the data into Table_B and updates the trg table's columns (col2).
Now the actual problem what i am facing is:
For loading Table_A and updating the col1 in Trg table i will be receving more than 5 excel file every month on weekly basis. I cannot even gather all the files and run using a For-Loop counter. So presently i am loading data excel file per week .
Similarly loading of table_B.
For a week if i am executing both the packages which loads the Table_A and updates the Trg(col1) and Table_B and updates Trg(col2), then i am getting a Deadlock Error and the entire ETL is getting messed up.
Now my requirement is , Eventhough the 2 packages are run in parallel , there could certain milli seconds time difference while start of the execution in Job Monitor. I need to implement a Queing Mechanism which takes care of running the packages in a sequential manner rather than in parallel. i .e i need to ensure only one SSIS package is running in Job Monitor. Only after successful execution of either one the package, then only the second package should start its execution.
If we can implement such a queing mechanism , then my problem is solvedl.
I need some suggestions on this regard in implementing the Queing mechanism in a programatic approach using SQL Server Job Related MetaData Tables. or else is there in server parameter or initialization parameters which can be set at Database level which suffice my requirement.
Any suggestions would be greatly appreciated. Looking for sincere comments on this regards.
I'm trying to get some proof of concept reports made for a MOSS / SQL Server 2005 (sp2) Integrated solution. I have a simple report that displays everything in the 'incident' uploaded to the MOSS server, and it works fine.
I've made another report that uses a line chart to plot some averages. This report, called 'Average Control Time by Weekday' works 100% ok in VS2005 preview mode and both reports use the same datasource. The report is uploaded and the dataset is all linked up.
When I view the report (by clicking on it's link on my Sharepoint Site), however, I get an error:
An error has occurred during report processing.
Query execution failed for data set 'DataSource1'.
Invalid object name 'incident'.
I've checked the SQL that was run in the SQL Server Profiler to see what transactions were hitting the database. The query that the report makes when in VS2005 Preview mode is precicely the same as what Sharepoint makes when it tries to render the report.
I made another plain report (just a table view) that uses the same query as the 'Average Control' report and tested it on Sharepoint, and it works perfectly. Any ideas?
p.s. I even asked I-God (http://www.titane.ca/concordia/dfar251/igod/main.html) all he said was that I would need a court order to get rid of the recursion in AIML... and then asked for my credit card number.
I'm having a problem sending the query set as an email text attachment. Test transmissions from Database mail working fine.
Send simple messages with the sp_send_dbmail sproc works fine as well.
It is only when I try and send a query result that things blow up. The query itself is working fine also, so I'm now down to think there is some esoteric problem with the sproc itself.
Surface config features have database mail on, and SQL Mail off.
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 476 Query execution failed: Error initializing COM Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded.
Investigating the sproc itself shows...
Line 476 in the sproc is the beginning of a 'trap' --Raise an error it the query execution fails -- This will only be the case when @append_query_error is set to 0 (false) IF( (@RetErrorMsg IS NOT NULL) AND (@exclude_query_output=0) ) BEGIN RAISERROR(14661, -1, -1, @RetErrorMsg) END
RETURN (@rc) this is the last section of code in the sproc
I have a package that has 12 data pump tasks all executing in parallel.
It is transferring raw data from an AS400 DW to a MSSQLSvr Staging area.
Each pump task on completion assigns values to a set of global variables, then having done this passes these as parameters to a sproc which inserts them into a table.
This seems to work for 4 or 5 of the pump tasks but, the rest of the rows in the table are all the same because the remaining pump tasks are all executing before the sprocs.
Is there a way to make sure that the entire set of job steps completes, before starting another job set of steps while still keeping them running in parallel.
I had wondered if there was a way to use the PumpComplete phase of each pump step to fire off the sproc, but can't see how you execute the step.
I want to finsih the execution of a DTS package from an ActiveX task. If a condition is ok, the package would continue as normal. If not, I want to finish the package without any error. Just without executing the next tasks. Do you have any idea?
