I now support a SQL Server "master" wrapper package that runs several other packages that simply never ends. When I run it in Visual Studio all the tasks complete, but the status stays running. I'm am trying to get this package to run via TWS/OPC and it doesn't end there either.
Does anyone know what the problem is? Has anyone heard of this?
Thanks,
Jim
Hi
I've created a simple package that contains only one task that is an execute sql task. When I run only this single task from Business Intelligence development studio it runs successfully. But when I run the whole package (also from Business intlligence studio), the package fails.
The data source I access is ODBC. I'm sure the real reason for the error is the bad ODBC driver of the data source but this can't be changed. So I need to know what is different from running only a task in a package to running the whole package. If I knew that I might be able to adjust some setting and make it work.
Any help welcome.
I have a delete query on a linked server that never ends. I can do a select where ID = x and it returns lickity split but when I do a delete where id = x it never comes back? Any Clues???
View 1 Replies View RelatedHi,
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.
-Prashant
Hi there,
Anyone know how I can replace (or download) the sqlexpr32.exe bootstrap package with the advanced services SP2 edition of SQL 2005 express ?
Pieter
Why does this query never ends??
Seems that parameter @Sucursales is always the same number. When the query loops in the while statement does it set @Sucursales to the result in the select statement (in red)? Or continue looping from the while statement?
DECLARE @Sucursales INT
SET @Sucursales = (SELECT COUNT(*) FROM CatSucursales)
BEGIN
WHILE @Sucursales > 0
INSERT INTO Compras(Fecha,NumOC,CveProveedor,Importe,CveDepartamento)
SELECT
Fecha,
NumOC,
CveProveedor,
CONVERT(money,SUM(Cantidad * Costo)) AS Importe,
CD.CveDepartamento
FROM
ComprasDetalle CD
INNER JOIN
( SELECT
CveProveedor,
Nombre,
CveDepartamento
FROM
CatProveedores CP
INNER JOIN CatDepartamentos CD ON
CP.CveSucursal = CD.CveSucursal
WHERE
CP.CveSucursal = @Sucursales
)VirCP ON
CD.Proveedor = VirCP.Nombre
AND CD.CveDepartamento = VirCP.CveDepartamento
WHERE
Fecha = DATEADD(dd, DATEDIFF(dd, 0, GETDATE())+0, -1)
GROUP BY Fecha, NumOC, CveProveedor, Nombre, CD.CveDepartamento
SET @Sucursales = @Sucursales - 1
END
Recently I have upgraded my system to SQL Server 2005 SP2 version 9.00.3054; I have developed my packages on SQL Server 2005 SP1 version 9.00.2047. I am unable to open all these packages on SQL Serever SP2 version 9.00.3054 it is giving me error.
Error: Error loading Package.dtsx cannot create an instance of the XML Document Object Model. MSXML may not be registered.
Can any body help me in this?
Thanks in advance
Hello
I have a .Net application that calls an stored procedure. When it does, the execution goes and never ends (I have to kill the windows process). When I call the sp from within the Management Studio, it also never ends executing and I have to cancel the query. But, when I call it immediately after, it takes 45 seconds to complete.
Now, the sp has several parts and I have made that it prints a message at the end of each part so that I can read where it stops. Strange enough, it completes all parts except the last one, which has the form INSERT INTO myLocalTable SELECT * FROM MyRemoteTable. But if I execute the Select independetly, I discover that it brings no rows! Now, many of the @@rowcount printed after the execution of the other parts shows zero rows involved or just a few. I am not using cursors, each part is an UPDATE statement or an INSERT.
TestMachine1 runs SQL2005 SP2 and has as linked server myRemoteServer (SQL2000) server. The stored procedure in TestMachine1 inserts rows to a table in myRemoteServer and brings back some rows.
What could be wrong?
Hi,
we've got this problem with some particular jobs: they look as they
ran correctly, but actually they didn't made it all through their
duties.
The problem is that this job is calling a sequence of DTS, where there
is a DTS with an ActiveX control which modifies another DTS before
launching and some other tasks: the error happens there, .
Launched from the DTS we get the error, from the job no...any idea how
we could get the correct job information?
Thank you
Daniele
Hi,
I have strange situation with one stored procedure on MS SQL Server 2000, sp3:
- It's some old procedure that worked well for several years
- It still works at other locations (even on MS SQL Server 7)
- Now sometimes this procedure just stops working over ODBC and OLE-DB connection. From Query Analyzer it works always ok! Over ODBC looks like client doesn't receive information, that procedure has completed.
I put some insert at the end of the procedure (for debbuging): procedure executes insert (it's the last statement in procedure), I can select inserted values from table, but client just doesn't get answer from server. I looked all processes at SQL Server and none of them is blocked, there are no locks that prevents procedure execution,...
I found out that recreating procedure makes it working again (for all clients). Well, at least for few days or even weeks.
Any ideas?
Thx!
Borut
Hi all,
I have back up job that runs to back up Cube daily but lately it is failing and gives error bellow:
The operation has been cancelled. at Microsoft.AnalysisServices.Xmla.XmlaClient.CheckForSoapFault(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError) at Microsoft.AnalysisServices.Xmla.XmlaClient.CheckForError(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError) at Microsoft.AnalysisServices.Xmla.XmlaClient.SendMessage(Boolean endReceivalIfException, Boolean readSession, Boolean readNamespaceCompatibility) at Microsoft.AnalysisServices.Xmla.XmlaClient.SendMessageAndReturnResult(String& result, Boolean skipResult) at Microsoft.AnalysisServices.Xmla.XmlaClient.Execute(String command, String properties, String& result, Boolean skipResult, Boolean propertiesXmlIsComplete) at Microsoft.SqlServer.Management.Smo.Olap.SoapClient.ExecuteStatement(String stmt, StatementType stmtType, Boolean withResults, String properties, String parameters, Boolean restrictionListElement, String discoverType, String catalog) at Mi. The step failed.
But if i run that job manually it runs fine(successfully)
What could be the problem!!
thanks
I have a question about how Query Notification works and the objects and data created when a new subscription is created.
Here is what we are doing:
- When the application starts, we call the SqlDependency start method:
SqlDependency.Start( <connection string> )
- For each query we want to monitor, we run the following:
SqlConnection oConnection = new SqlConnection( <connection string> );
oConnection.Open();
SqlCommand oCommand = new SqlCommand( <sql statement> , oConnection);
SqlDependency oDependency = new SqlDependency(oCommand);
oDependency.OnChange += new OnChangeEventHandler( <handler delegate> );
SqlDataReader objReader = oCommand.ExecuteReader();
objReader.Close();
- When the application shuts down, we call the SqlDependency stop method:
SqlDependency.Stop( <connection string> )
We have observed that when we create a new subscription, the following things happen:
1. A row is added to sys.DM_QN_Subscriptions.
2. query_notification_% internal table is created.
3. SQLQueryNotificationStoredProcedure% stored procedure is created.
4. SQLQueryNotificationService% Service Queue is created.
5. queue_messages% internal table is created.
When the application ends, the stored procedure, service queue and queue messages internal table are dropped. The row in sys.DM_QN_Subscriptions and the query_notification_% persist.
When the application is restarted, a new row is added to sys.DM_QN_Subscriptions, but the existing query_notification_% internal table is used.
Is this normal behavior? Or, is there something we should be doing to clean up?
What I am wondering is if there is a threshold where these persistent items become a problem with locking, performance, other?
Hi gang,
We have a database that's using merge replication between two servers, and we need to insert a lot (about 1GB) of data into it.
The servers, however, are separated by a 192k WAN connection, so it's impractical to rely on the merge replication to send the data across to the subscriber.
Is there a way to insert the data at both ends? I can get the data out there on a DVD or a laptop easily enough. Can I load the data into both copies of the database and tell the merge agent that it's not to be replicated?
Thanks,
Matt
Hello everybody,
I have a problem concerning the execution status of a data driven subscription. The problem is that
I�m creating a datamart and rendering several thousands of pdf based reports after that. I�ve found out how
to start the subscription using stored procedures with DTS.
Now I want to take further actions after the report building is done. But how do I find that out?
tfr
LG
I am implementing full text serach option in my project.
Its working fine for Begins with ("Text*")
Contains("Text")
But I am not able get the results for ends with ("*Text")
SELECT * FROM CATALOGUE_INDEX WHERE CONTAINS(SHORT_DESCRIPTION,'*OCK')
Now I am expecting the rows which are having "Stock" as value in short_desciption column.
But I am not getting the result.
Please advise me on how to do the same
Regards
Muralimohan
We are about to migrate a 2000 server to a different server with 2005. One of the dts packages has been giving us problems converting it, so we want to leave it as a dts for now so we can look at it later but still upgrade now. Since I'm moving the database to a new server I need to know how to set up a job to run the dts, as well as how to ensure the dts is moved to the new server as well.
my initial thought is just to export the dts then import it to the new server. After that set up a new job on the new server to run the dts. I cant figure out how to set up the job though. I guess I need to write a dtsrun command, but I've always done that by right clicking on the dts and having the wizard create the command for me. And it seems with 2005 i can't do that even with the backward compantability tools installed. Anyone can help?
Hi,
I am using SQL Express 2005 server and sql compact edition for my PDA. For synchronization, which SQL Server 2005 Compact Edition Server Tools installation package do I use?
Is it Sqlce30setupen.msi or sql2kensp4.msi or sql2kensp3a.msi? I know for sql server 2005 its Sqlce30setupen.msi. Is it the same for the SQL Express 2005?
Thanks
I am new to SQL Server and need to understand how to run DTS packages (SSIS) in SQL 2005 from a remote server? In other words run them from another server from where the database is installed.
I am looking for any links on the subject, guidance on how to set this up and how to use it.
Regards,
Lee
I've build the deployment manifest of my ssis package, and installed it on the sql server machine, now, how do i run this packege on sql server 2005?
View 1 Replies View RelatedSo in a script task for one of my packages I have a connection manager to an dtsConfig OLE DB.
This is the code
Dim ConnectionString As String = Dts.Connections("db_stage").ConnectionString
Dim sqlConnection As SqlConnection = New SqlConnection(ConnectionString )
I get a login failed for
user...But if I hardcode the connectionString, including the password this works.
1) Why is it that the ConnectionString from the connection manager omits this password?
2) Since this is an OLE DB, is there anyway to set the Data Source Designer to omit the "Provider=ABCDED.1" section?
Thanks!
Tony
can any one tell me the sql server 2005job schedules,ssis packages
View 1 Replies View RelatedHi,
We are migrating our production environment from 32-bit SQL Server 2005, Windows 2003 server to 64-bit SQL Server 2005, Windows 2003 server environment with 4GB of RAM. We have recompiled the SSIS packages to run in 64-bit mode and stored them in database. But one of the package fails with initial information as
Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 34720 buffers were considered and 34720 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.
And the subsequent error messages received as
Error Message 1
A buffer could not be locked. The system is out of memory or the buffer manager has reached its quota.
Error Message 2
An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.".
Please guide us in troubleshooting this issue.
Thanks & Regards
Amit
I am not sure where to ask this question. So I figured here may be ok
I have several DTS 2000 packages and I have SQL 2005 installed on my local machine. However the rest of the company is still using SQL 2000 (for now). So I do not want to convert them to ssis yet. One of the things we just did to the packages is make all flat files use \localhost and any servers point to (local). Now in ssis this works fine but using the DTS 2000 package designer it does not like either of these at all. The reason we wanted to do this is so when we switch out servers there is no changes to our DTS packages.
Most of my packages that I've created in BIDS will NOT run in SQL Server 2005. The simplest one that I have fails during a script task that calls external managed code. I've done all the steps outlined in "Referencing Other Assemblies...", but I'm still getting "Object reference not set to an instance of an object." Here's a sample of a script that's having a problem. The line in green is the one that seems to be cause of the error. This is extremely frustrating. This code will even run from a command line console without error. Why is it so difficult to deploy one of these projects with managed code?
Code Snippet
Public Sub Main()
Dim variable1 As String = DirectCast(Dts.Variables("packagevariable1").Value, String)
Dim variable2 As String = DirectCast(Dts.Variables("packagevariable2").Value, String)
Dim variable3 As Integer = DirectCast(Dts.Variables("packagevariable3").Value, Integer)
Dim variable4 As String = DirectCast(Dts.Variables("packagevariable4").Value, String)
Dim filePath As String = DirectCast(Dts.Variables("filePath").Value, String)
Dim variable5 As String = DirectCast(Dts.Variables("packagevariable5").Value, String)
Dim results As Boolean
Dim fileGenerator As IProviderInterface
Dim intFactory As integrationServiceFactory = New ProviderIntegrationServiceFactory()
fileGenerator = intFactory.GetProviderEnrollmentGenerator(variable2, variable5)
results = fileGenerator.GenerateFile(variable3, variable1, filePath, variable2)
If results Then
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If
End Sub
Hi, here is my problem :
I want to run ssis package in sql server 2005 agent.
i've made a new JOB with 3 steps ( 3 ssis package)
But when i run the job, i see only 2 "actions step" in the windows :
Start job .....
Execute job ....
I would like to see at least the execution status of every ssis package and if it's possible,
the execution of every task ( to see quickly if there's some error and what's wrong)
What's the best solution to doing that
Thank for helping
BG.
Hi,
View 8 Replies View RelatedSetting up a test AlwaysOn Availability Group for one database.
However, whenever I restore the database to the replica server and join it, it ends up with my user account as the owner of the database.
Obviously I do not want a user account as the database owner, but since it is read-only I cannot modify it directly. If I were able to fail the AG over to the replica, I could change the owner then, but I cannot due to business requirements. this AG is to essentially serve as a replacement to log shipping.
I tried doing the backups and restores using EXECUTE AS login = 'sa', and yet it still shows up as my user account.
I have to send updated Employee list from employee master table to a particular email ID on every last date of Month and when a new employee is added / deleted / edited. Also need to send this as an Excel file
I tried the following but "Invalid Object name dbo.tbl_EmployeeMaster" error coming while inserting a new employee.
USE [eXact]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trg_Email]
[Code] ....
I am new to SQL server 2005 and have a config question:
I am controlling database connection info using XML indirect config - no problems there.
Essentially I am going to have a number of packages that need to use a common file path, that might change from one server to the next, e.g. Server 1: C:sourceFiles versus Server 2: D:sourceFiles. Within this directory the filenames will remain static. So in the flat file connection manager I'd like to use a variable to reflect the folder - but I don't want to have to create this for each package.
So, I thought I would create a system environment variable and create expressions for the connection managers - something like %SOURCE_DIR% + "file.csv" - but this does not evaluate correctly.
So then i though I could use the SQL server configurations table with a configurationFilter SOURCE_DIR and appropriate configuration value - but then how do I access this in the flat file connection manager to create a dynamic file name?
So essentially I want a variable/property available globally to all my packages and potential flat file connection managers that help me to centrally control file path locations.
Any help would be most appreciated.
Does any kind person have a simple example Package for undertaking 'Text Extraction' on one or two columns of text data in a SQL Server table (the data is not in Unicode)?
HI,
I have problems with executing packages in the SQL Server 2005 and before I go into the details I would like to check, if my workflow is correct.
Can someone explain me the best way to move a package from BIDS to a SQL Server? I would like to store the packages in the SQL Server.
In addition if I login to Integration Services with SQL Server Management Studio I can see in the folder "stored packages" my package but I cannot view the folder "MSDB". Ususally I receive an error message from the named pipe provider, that I could not connect to the server (I am using Windows Authentication and my login is an administrator on the SLQ Server box)
Thanks in advvance
Nobs
Hi,
My name is Vinh, I am a new bee in SQL Server 2005. I am using template script (see below) from SQL Server to create account but when I am right click in database mail for testing email and I got the message, could not connect to mail server.
Below I am trying to use smtp to connect but I know in my company we are using Exchange Mail Server. will that make a lot different?
Please help me,
Thank you very much,
sp_configure 'database mail xps', 1
GO
reconfigure
GO
-------------------------------------------------------------
-- Database Mail Simple Configuration Template.
--
-- This template creates a Database Mail profile, an SMTP account and
-- associates the account to the profile.
-- The template does not grant access to the new profile for
-- any database principals. Use msdb.dbo.sysmail_add_principalprofile
-- to grant access to the new profile for users who are not
-- members of sysadmin.
-------------------------------------------------------------
DECLARE @profile_name sysname,
@account_name sysname,
@SMTP_servername sysname,
@email_address NVARCHAR(128),
@display_name NVARCHAR(128);
-- Profile name. Replace with the name for your profile
SET @profile_name = 'TestProfile';
-- Account information. Replace with the information for your account.
SET @account_name = 'vdang';
SET @SMTP_servername = 'smtp.cgdnow.com';
SET @email_address = 'vdang@cdgnow.com';
SET @display_name = 'Vinh, Dang';
-- Verify the specified account and profile do not already exist.
IF EXISTS (SELECT * FROM msdb.dbo.sysmail_profile WHERE name = @profile_name)
BEGIN
RAISERROR('The specified Database Mail profile (<profile_name,sysname,SampleProfile>) already exists.', 16, 1);
GOTO done;
END;
IF EXISTS (SELECT * FROM msdb.dbo.sysmail_account WHERE name = @account_name )
BEGIN
RAISERROR('The specified Database Mail account (<account_name,sysname,SampleAccount>) already exists.', 16, 1) ;
GOTO done;
END;
-- Start a transaction before adding the account and the profile
BEGIN TRANSACTION ;
DECLARE @rv INT;
-- Add the account
EXECUTE @rv=msdb.dbo.sysmail_add_account_sp
@account_name = @account_name,
@email_address = @email_address,
@display_name = @display_name,
@mailserver_name = @SMTP_servername;
IF @rv<>0
BEGIN
RAISERROR('Failed to create the specified Database Mail account (<account_name,sysname,SampleAccount>).', 16, 1) ;
GOTO done;
END
-- Add the profile
EXECUTE @rv=msdb.dbo.sysmail_add_profile_sp
@profile_name = @profile_name ;
IF @rv<>0
BEGIN
RAISERROR('Failed to create the specified Database Mail profile (<profile_name,sysname,SampleProfile>).', 16, 1);
ROLLBACK TRANSACTION;
GOTO done;
END;
-- Associate the account with the profile.
EXECUTE @rv=msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = @profile_name,
@account_name = @account_name,
@sequence_number = 1 ;
IF @rv<>0
BEGIN
RAISERROR('Failed to associate the speficied profile with the specified account (<account_name,sysname,SampleAccount>).', 16, 1) ;
ROLLBACK TRANSACTION;
GOTO done;
END;
COMMIT TRANSACTION;
done:
GO
Is there any built-in way of kicking off a job on SQL Server 2005 Agent whenever a package/job completes in Oracle?
Are there any (Triggers? Msft queue? Event Notification?) mechanisms to automate running a job on the SQL side?
Any article or knowledge articles would be appreciated also.
If not are there any built-in stardardized polling techniques? Or are there any timers in SSIS?
That way I can delay executing a child package until a certain record has been inserted into a control table in Oracle.
I don't want to write an inefficient for loop that blocks all other processing on the server and iterates once every second.
Thanks