Hi,
I had application that access the database 'test'. Now i created a ETL package, in this Souce database is 'test' and destination database is 'mytest'. I have scheduled the job for every hour.
Now, i want to know that whether any locks will create on source database 'test' while ETL package is running.
Thanks
Dinesh
Hi,
I have just upgraded my sql 65 to sql 7. The problem I am running into is running out of locks all the time with 50000 locks. The sql box has 4 pentium pro 200 processors and 1 Gig of ram. Here is the error message:
DESCRIPTION:Error: 1204, Severity: 19, State: 1
SQL Server has run out of LOCKS. Rerun your statement when there are fewer active users, or ask the system administrator to reconfigure SQL Server with more LOCKS.
COMMENT:Fatal Error Occured in Resource
Help!!!
Jim
Hi, i have installed sql 7.0 sp1 on a server that frecuently report the error: 1024
"SQL Server has run out of LOCKS. Rerun your statement when there are fewer active users, or ask the system administrator to reconfigure SQL Server with more LOCKS.."
I reconfiguered the locks twice, but the error persist.
What do you recomend to do to avoid this error??
thanks.
Hi all ,
When i Run the report in reporting services, it locks the tables.
so is there any option to Unlock the tables. I m using just select query to run the report but when i run the report it locks the tables.
I used with(nolock) option in select query but it didnt work...still showing me lock on the tables.
Pls help...its urgent
Thanking You,
Rupali Rane.
Hi,
First of all, my apology. I'm not sure if this is the right SQL server forum. Moreover, this didn't even happen on SQL Server but Sybase 11.9. But I'm thinking that the same situation might happen on SQL Server and I can't find any Sybase forum to run this by some experts.
I got the following error on a dev server:
Error: Number (1204) Severity (17). SQL Server has run out of LOCKS. Re-run your command when there are fewer active users, or contact a user with System Administrator (SA) role to reconfigure SQL Server with more LOCKS
The number of locks was set to 10,000.
I set it to 15,000 and ran the same code: Same error.
I set it to 20,000 and ran the same code: No error.
I set it back to 15,000 and ran the same code: No error.
I set it to 10,000 and ran the same code: No error.
I set it to 5,000 and ran the same code: No error.
It seems strange that it used not to work at 10,000 locks (repeatedlly) but once I set the number of locks to 20,000 once, then it worked with the number of locks as low as 5,000. Does anybody have any idea why this would happen?
Another question is that when the number of locks is set to 10,000, I run sp_lock while the code is running and the number of rows returned was often over 10,000. Still, I didn't get the error. Anybody knows why?
Did anybody ever run into a similar situation?
Appreciate your help.
Hi,
I have installed MS SQL Server 2005 and Service Pack 2 on a new Windows 32-bit environment. I also installed the 32-bit 10g client (the Administrator option). The tnsnames.ora file is configured properly and I can tnsping to the Oracle database without any issues. I have created a package in Visual Studio and it runs successfully when I execute the job manually. I saved the package to the MS SQL Server and when I log into Management Studio to create a job for this package, I receive the following error:
Message
Executed as user: xxxSYSTEM. ...rsion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:42:37 AM Error: 2007-10-10 10:42:37.28 Code: 0xC0202009 Source: x Connection manager "x.x" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation. Provider is unable to function until these components are installed.". End Error Error: 2007-10-10 10:42:37.28 Code: 0xC020801C Source: Data Flow Task OLE DB Source [18] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method ca... The package execution fa... The step failed.
I have created the package and logged into the server with the same ID to set up the job. And I have set the ProtectionLevel property to "Don't Save Sensitive Data" since I know this has been an issue in the past when I tried automating the job. I've four posts regarding this error message on the MSDN forums that aren't helpful at this moment - I'm hoping that someone will have found a solution since those posts. Any suggestions are welcomed.
Thanks for your help!
Sue
Would be interested in any advice or comment on the issue we are experiencing with SQL 2005.
In order to test some DTS package migrations, we simply created a DTS package on SQL 2000 (using Export) to copy all database objects from one database to another.
Then we restored the source database on SQL 2005 server, migrated the DTS package, and tried to execute it.
The package migrated 'fine', ran part way, and then failed. Since then we have been unable to connect to the server (Database Engine, or Integration Services) using Windows Authentication. (Error 18456, Sev 16, State 11).
We can connect to the Database Engine using SQL Authentication (but not to Integration Services which only allows Windows Authentication).
We have been able to replicate this consistently - every annoying time we've tried it!
(Reinstalling SQL Server does 'fix' the issue ... until you try a similar package again).
I've got an INSERT that's selecting data from a linked server and attempting to push 10 million rows into the blank table. More or less, it looks like this:
insert into ReceivingTable (
Field1, Field2, Field3, Field4
, Field5, Field6, Field7, Field8
, Field9, Field10, Field11, Field12
, Field13, Field14, Field15
[code]...
The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions. There are no other active users. I ran it again and monitored the following DMO to watch the growth of locks for that spid:
SELECT request_session_id, COUNT (*) num_locks
-- select *
FROM sys.dm_tran_locks
--where request_session_id = 77
GROUP BY request_session_id
ORDER BY count (*) DESC
The number of locks started small and held for a while around 4-7 locks, but at about 5 minutes in the number of locks held by that spid grew dramatically to more than 8 million before finally erroring again with the same message. Researching, I can't figure out why it's not escalating from row locks to table locks at the appropriate threshold. The threshold in was set to 0 at first (Server Properties > Advanced > Parallelism > Locks). I set it to 5000, and it still didn't seem to work. Rewriting the INSERT to include a WITH (TABLOCK) allows it to finish successfully in testing. My problem is that it's coming out of an ETL with source code that I can't edit. I need to figure out how to force it to escalate to locking the entire table via table or server level settings.
A colleague suggested that installing service packs may take care of it (the client is running SQL Server 2008 R2 (RTM)), but I haven't found anything online to support that theory.
We are migrating our database(s) from ORACLE to SQL. In Oracle we were able to issue a SELECT statement and see all of the locks (Blocking and Non-Blocking) currently in the system. The query also included the Process ID of the process we needed to kill in order to get rid of the lock.
We now need to create the same type of query for Microsoft SQL Server 2012. I have seen postings on different sites saying that this info can be obtained using SP_WHO2 or using the SQL Server Management Studio Activity Monitor's PROCESSES tab, but we are looking for a SELECT statement that will give us similar information.
Hi All,
I am in the process of moving from a 32-bit SQL Server 2005 Enterprise (9.0.3054) to a 64-bit SQL Server 2005 Enterprise (9.0.3054 with 4 CPUs and 8GB of memory on Win 2003 SP2) and the process has been very frustrating to say the least. I am having a problem with packages that I created on my 64-bit SQL Server. I am importing a few tables from the 32-SQL Server into the 64-bit SQL Server using the Task --> Import to create the package.
Sometimes when I am creating a package I get the following error in a message box:
SQL Server Import and Export Wizard
The SSIS Runtime object could not be created. Verify that DTS.dll is available and registered. The wizard cannot continue and it will terminate.
Additional information: Attempted to read or write protected memory. This is often an indication that other memory is corrupt. (System.Windows.Forms)
Other times when I run a package that has run successfully before I get the following error:
Faulting application dtexecui.exe, version 9.0.3042.0, stamp 45cd726d, faulting module unknown, version 0.0.0.0, stamp 00000000, debug? 0, fault address 0x025d23f0.
Other times I get this error message:
.NET Runtime version 2.0.50727.1433 - Fatal Execution Engine Error (79FFEE24) (80131506)
And still other times
The package appears to hang when running. By this I mean that the Package Execution Progress shows progress up to a point then it just stops. (The package takes about 17 seconds to run normally) CPU usage is at 1% and the package cannot be stopped.
I have deleted and re-created the package several times and I have also re-installed the service pack on the SQL Server (9.0.3054) but that did not help.
Does anyone have any other suggestions to try?
Thanks.
Hi there
We have a SSIS run which runs as follows
The master package has a configuration file, specifying the connect strings
The master package passes these connect-strings to the child packages in a variable
Both master package and child packages have connection managers, setup to use localhost. This is done deliberately to be able to test the packages on individual development pc€™s.
We do not want to change anything inside the packages when deploying to test, and from test to production. All differences will be in the config files (which are pretty fixed, they very seldom change). That way we can be sure that we can deploy to production without any changes at all.
The package is run from the file system, through a job-schedule.
We experience the following when running on a not default sql-server instance (called dkms5253uedw)
Case 1:
The master package starts by executing three sql-scripts (drop foreign key€™s, truncate tables, create foreign key€™s). This works fine.
The master package then executes the first child package. We then in the sysdtslog get:
Error - €ścannot connect to database xxx€?
Info - €śpackage is preparing to get connection string from parent €¦€?
The child package then executes OK, does all it€™s work, and finish. Because there has been an error, the master package then stops with an error.
Case 2:
When we run exactly the same, but with the connection strings in the config file pointing to the default instance (dkms5253), the everything works fine.
Case 3:
When we run exactly the same, again against the dkms5253uedw instance, but now with the exact same databases defined in the default instance, it also works perfect.
Case 4:
When we then stop the sql-server on the default instance, the package faults again, this time with
Error - €śtimeout when connect to database xxx€?
Info - €śpackage is preparing to get connection string from parent €¦€?
And the continues as in the first case
From all this we conclude, that the child package tries to connect to the database before it knows the connection string it gets passed in the variable from the master package. It therefore tries to connect to the default instance, and this only works if the default instance is running and has the same databases defined. As far as we can see, the child package does no work against the default instance (no logging etc.).
We have tried delayed validation in the packages and in the connection managers, but with the same results (error).
So we are desperately hoping that someone can help us solve this problem.
Thanx,
/Nils M - Copenhagen
Hi all,
I am creating a dts package to export files from one database to another database.
I tried to search for ways to execute the files and found out that i need to add
reference to Microsoft.Sql.managedDts. However, I cannot find this reference from
my reference. Do i have other alternatives to run this file?
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.
Thanks in advance for your help.
How do I temporarily lock a database (read-only mode) programatically?
For example:
BeginTrans
//LOCK OUT ALL OTHER USERS
//Only the Server has Write access
CommitTrans
THANKS!
Our enterprise consists of a mix of mission-critical transaction-oriented applications. The ERP applications are accessed by 70 branch offices around the country. The network also carries file transfers, e-mail, and Web traffic. Each branch is linked to headquarters via a Frame Relay network with port speeds of 64Kbps.
We are facing problems when ERP database locks up and cause time-outs and frustrating messages such as: "User disconnected - unknown error."
Rachel
Greetings:
I've a stored procedure that grabs from a table all of the records that have a status = 2. Once these records are grabbed the same stored procedure needs to update the status of the records to 3. Something like this:
SELECT
*
FROM
[MyTable]
WHERE
Status = 2
UPDATE
[MyTable]
SET Status = 3
WHERE
Status = 2
What I'm afraid will happen is that between the running of the select statement and the running of the update statement that a record that originally had a status of 1 will be set to 2 - which means the record was never selected by the first half of the stored procedure but had its status updated to 3 in the second half of the stored procedure.
I thought at first wrapping all of this in a transaction would fix the problem but I've done some more reading and that doesn't seem to be the case. I need the Update to run on only those records the Select query grabbed no matter what else happens in the table.
Is there a lock to address this in a way that will allow other records (those not selected) to be updated to a value of 2 without them being updated to 3 when the Update query runs?
The database is configured for single publisher, many subscribers,merge replication. The maintenance plan started to fail a couple ofmonths ago and the database would not get backed up. After clearingall the locks, I am able to backup the database manually. The locksreturn again and I'm not able to backup the database with themaintenance plan. How can I get around the lock issue or solve it sothat I can backup the database again?Thanks,Chris
View 1 Replies View RelatedIn my environment, there is maintenance plan configured on one of the server and while running DBCC checkdb on a database of size around 200GB, log file usage of tempdb is increasing and causing the maintenance job to fail.
What can I do to make the maintenance job run successfully, size of the tempdb database is only 50GB and recovery model is set to simple. It cannot be increased as the mount point on which it is residing is 50GB.
I am trying to run a SSIS package (called "Parent") that calls another package (called "Child"). The packages are stored in SQL Server (MSDB database) and are called from a console app by the code below:
Public Shared Function RunPackage(ByVal server As String, _
ByVal userName As String, _
ByVal password As String, _
ByVal packageName As String, _
ByVal packagePassword As String) As String
Dim pkg As New Package
Dim app As New Application
Dim pkgResults As DTSExecResult
Dim result As String = ""
app.PackagePassword = packagePassword
pkg = app.LoadFromSqlServer(packageName, server, userName, password, Nothing)
pkgResults = pkg.Execute()
result = pkgResults.ToString() & vbNewLine
For Each err As DtsError In pkg.Errors
result += "Task: " & err.Source & vbNewLine
result += ParseErrorMessage(err.Description) & vbNewLine
Next
Return result
End Function
If I just run the Child package with this code, then it works! But is I run the Parent package from the same code, then it fails with this error: "Error 0x80004002 while preparing to load the package. No such interface supported".
The only difference between the 2 packages is that the Parent calls the Child, and the Child calls nothing.
The Connection Manager in Parent used to connect to Child uses SQL Server Authentication and it needs to be that.
My question: From VB.NET, how do I run a SSIS package that calls another package?
Hi,
I am joining a table with a view in my query to get the desired data. But when I run this query it does not produce any result, instead the execution goes on never ending finally locking the database.
Surprisingly if the selected data from this view is put in a temporary table and that table is joined with the table to get the result, it works fine.
Could anybody please help me with this as creating a table every time would be slow procedure. Is there any restrictions related to views which may be I have ignored.
Thanx in advance.
Regards,
Sushma
Hello all,
When I create a new database and replicate to it using BeginMonitoredBackgroundSync :
Code Block
public void BeginMonitoredBackgroundSync(string User)
{
CreateReplicationInstance(User);
repl.BeginSynchronize(
OnSimplifiedSynchronizeComplete,
SqlCeReplication_OnStartTableUpload,
SqlCeReplication_OnStartTableDownload,
SqlCeReplication_OnSynchronization,
repl);
}
private void CreateReplicationInstance(string User)
{
repl = new SqlCeReplication();
string host = repl.HostName;
repl.HostName = User;
string dbFilePath = "";
dbFilePath = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) +
"\" + repl.HostName + ".sdf";
string myConnectionString = string.Format("Data Source = {0};PWD = {1}", dbFilePath, sqlSettings.Items["SqlPassword"]);
repl.InternetUrl = dynamicsReplicationSettings.ReplicationSettingsItems["InternetUrl"];
repl.PublisherSecurityMode = SecurityType.DBAuthentication;
repl.Publisher = dynamicsReplicationSettings.ReplicationSettingsItems["Publisher"];
repl.PublisherDatabase = dynamicsReplicationSettings.ReplicationSettingsItems["PublisherDatabase"];
repl.PublisherLogin = dynamicsReplicationSettings.ReplicationSettingsItems["PublisherLogin"];
repl.PublisherPassword = dynamicsReplicationSettings.ReplicationSettingsItems["PublisherPassword"];
repl.Publication = dynamicsReplicationSettings.ReplicationSettingsItems["Publication"];
repl.Subscriber = "RemoteSubscription" + repl.HostName;
repl.SubscriberConnectionString = myConnectionString;
repl.ConnectionRetryTimeout = 120;
repl.LoginTimeout = 120;
repl.CompressionLevel = 6;
if (File.Exists(dbFilePath))
{
FileInfo info = new FileInfo(dbFilePath);
if (info.Length <= 20480)
{
File.Delete(dbFilePath);
repl.AddSubscription(System.Data.SqlServerCe.AddOption.CreateDatabase);
}
}
else
{
repl.AddSubscription(System.Data.SqlServerCe.AddOption.CreateDatabase);
}
primeConnection();
}
After the replication finishes, I dispose the replication object like so:
Code Block
void OnAsyncSynchronizeComplete(IAsyncResult asyncResult)
{
try
{
repl.EndSynchronize(asyncResult);
if (repl != null)
{
repl.Dispose();
repl = null;
}
if (ReplicationComplete != null) ReplicationComplete(this, true);
}
catch(Exception ex)
{
if (ReplicationComplete != null) ReplicationComplete(ex, false);
}
}
Then later, if I try to update, insert or delete to the database, the application will hang. I can read from it, but I cannot write. If I close the application down and open it back up without replicating, I will not get any lockups. It also will not hang up after any replications prior to the create replication. I think I am doing something wrong in the initial replication that is holding on to some connection to the DB causing it to lock up. Has anyone seen anything like this before?
Hi,
I want to make SQLTransaction as global and use it checking the State.
But then where there are Only Select queries are going to fire, it will open transaction.
So, Does it locks database/tables when Only Select query comes in SQLTransaction.
If you have another successful way of doing this, Please suggest.
Thanking you.
tats
Hey, I've a few jobs which call SSIS packages. If I run the SSIS package, it runs fine but if I try to run the job which calls this package, it fails. Can someone help me troubleshoot this issue? None of my jobs that call an SSIS package work. All of them fail.
Thank you
Tej
Can anyone point me to some sample code ord give me an example. I'm trying to run a DTS package from my asp.net/vb.net application. I don't need to pass it any variable, just need it to run. I can't seem to find anything on the net for this. Thanks in advance, Ryan
View 7 Replies View RelatedHi
I have one DTS package looks for specific file in a directory to process the data into sql. I have schedule this package to run every 5 mins.
I would like to invoke the package when a file arrive at the folder instead up running it every 5 mins.
Thanks
Hi Folks
I am calling SSIS package from my .net code. I want to know whether my package is still running or not. And if it is taking longer than a limit then execution of package should stop. I am executing package in different thread like this
DelegateExecute del = new DelegateExecute(pkg.Execute);
del.BeginInvoke(null, null);
As I explored, I learnt that RunningPackage class could help me in not only determining whether package is running butalso in stopping too. Unfortunately RunningPackage class is saying that there is no package running! Is Runningpackage works only with the packages running from MSDB?
Following is the code I am using.
public string LoadPackage(string path, long ThrashHold)
{
string pkgLocation;
Package pkg;
Application app;
//DTSExecResult pkgResults;
string str;
pkgLocation = path;
app = new Application();
pkg = app.LoadPackage(pkgLocation, null);
//pkgResults = pkg.Execute();
long startDT = DateTime.Now.Ticks/10000;
DelegateExecute del = new DelegateExecute(pkg.Execute);
del.BeginInvoke(null, null);
DealPackage();
while (pkg.ExecutionStatus != DTSExecStatus.Completed)
{
//str = "pkg.ExecutionStatus = " + pkg.ExecutionStatus.ToString();
//str = str + "pkg.StartTime = " + pkg.StartTime.ToString();
//str = str + "pkg.StopTime = " + pkg.StopTime.ToString();
//str = str + "pkg.ExecutionDuration = " + (DateTime.Now.Ticks / 10000 - startDT).ToString();
//Console.WriteLine("********************************");
//Console.WriteLine(str);
//Console.WriteLine("********************************");
if ((DateTime.Now.Ticks / 10000 - startDT) > ThrashHold)
{
Console.WriteLine("********************************");
Console.WriteLine("package should be stopped");
//Console.WriteLine(str);
Console.WriteLine("********************************");
break;
}
}
str = "pkg.ExecutionStatus = " + pkg.ExecutionStatus.ToString();
str= str+"pkg.StartTime = " + pkg.StartTime.ToString();
str= str+"pkg.StopTime = " + pkg.StopTime.ToString();
str = str + "pkg.ExecutionDuration = " + pkg.ExecutionDuration.ToString();
DBOperations dbr=new DBOperations(ServerName,"PEPE_SSIS","sa","sa@123");
dbr.EnterPackageDetail(path,pkg.ExecutionStatus.ToString(),pkg.StartTime.ToString(),pkg.StopTime.ToString(),pkg.ExecutionDuration.ToString());
return str;
}
private void DealPackage()
{
RunningPackages pkgs = app.GetRunningPackages(se);
int pkgsRunning = pkgs.Count;
Console.WriteLine("Packages before stop: " + pkgsRunning);
foreach (RunningPackage p in pkgs)
{
Console.WriteLine("InstanceID: " + p.InstanceID);
Console.WriteLine("PackageDescription: " + p.PackageDescription);
Console.WriteLine("PackageID: " + p.PackageID);
Console.WriteLine("PackageName: " + p.PackageName);
Console.WriteLine("UserName: " + p.UserName);
}
pkgs = app.GetRunningPackages("yourserver");
foreach (RunningPackage package in pkgs)
{
package.Stop();
}
pkgsRunning = app.GetRunningPackages("yourserver").Count;
Console.WriteLine("Packages after stop " + pkgsRunning);
}
one more guidance needed with regard to the Quotation marks
path = @"D:SSISProjectIntegration Services Project1ArchiveTicket.dtsx";
jobCommand = new SqlCommand(@"xp_cmdshell 'dtexec /f "'path '" /Set package.Variables[User::ArchiveFileType].Properties[Value];""Excel""'", cconn);
How Do i set the path's value in c# the quotation marks are wrong again. cos i want to set value of path according to selection in the UI.
Select("deviceUniqueId = '" + deviceUniqueId + "'"); is an e.g of Doing in C# but when i implement it through text it does not recognize
Thanks
Jas
When I ran a package directly on the machine with sql 2005 installed it ran at a fine speed. But when I used Remote Desktop to access the same client and run the same package it was much slower.
1. I think that the package is running in the memorycpu of the local machine. Is this correct?
2. Is there any way for the remote client to run the package in the server's memorycpu?
TIA,
barkingdog
Hi all,
I am trying to run a Excel macro from a DTS package.I've created the macro and Batch file to run a macro, and i am executing a batch file from DTS package.I am able to run a macro when i am executing a DTS package manually.but its not working when i schedule the process. any solutions?
its not the security problem also.I've checked it.
I have a DTS package hat copies several tables to another Reporting database.
When executing it as a apachkage, it works fine. However, when I try to schedule it as a job, it fails. I tried this with another package and it didn't work also. Any ideas?
Thanks,
Steve
Is there a way to run a local package from a stored procedure?
Thanks,
Ken Nicholson
I have a DTS package that is owned by Joe.
A job that is owned by Domain Admin runs Joe's job every night.
Joe has left the company and his account will be deleted.
2 Questions:
1. Will the job still be able to run the DTS package ?
2. If I need to change the owner of Joe's DTS package, how do I do that ?..just a simple 'Save as' ?..and if so, I will not be able to save the DTS package with the same name...and thereby the job will not recognize the new DTS package name...will I have to re-shedule the new DTS package ?
thank you
Hi,
Does anyone know if it is possible to run a DTS Package from MS-Access database (.mdb)? If yes, could you please specify how to do that? Thanks!
Regards,
av