Data Loading issue

[aarcba]

Hi,

I am trying to load data from cognos decision stream into sql server and I am getting the error mentioned below:

UDA-SQL-0115 Inappropriate SQL request.

UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]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. (SQLSTATE=42000, SQLERRORCODE=1204)

Bind error on column 'xxx'; value too large (length 70 vs 50)

When I try to check for number of locks I got a message it does not support any locks.
As per error I see Bind error too, can anybody help me understand when we get this bind error.

I am unable to determine if this issue is because of locks or due to improper data? If anybody has any suggestions on how to resolve this issue, please let me know.

Thanks in advance,

 

[RDCushing]

The "Bind error" appears to be related to your attempting to bind a column from the data source that is longer than the destination column in SQL Server. You will likely need to truncate values in the column in the E-T-L process.

Here's Microsoft's answer to your Error 1204:

ACTION:

Either execute the command again when activity on the server is low, or have the system administrator increase the number of locks by executing sp_configure from the master database.

To view the current configuration:

sp_configure locks
GO

This reports the minimum, maximum, current run, and configuration values. To increase the number of locks, run sp_configure again, specifying the number of locks to be configured. For example, to configure 10,000 locks:

sp_configure locks, 10000
GO
RECONFIGURE WITH OVERRIDE
GO

Stop and restart Microsoft® SQL Server™ so the changes can take effect. Locks are allocated at system startup.

If the number of locks cannot be increased at the current time, and the single action requires more locks than the server is currently configured for, you may be able to reduce the number of locks required for the operation. For example, try the following:

For large UPDATE statements, break the updates into smaller units that will affect only a subset of records at a time. For example, you could use the primary key, changing the single UPDATE statement from:

UPDATE employees
SET salary = salary * 1.05
WHERE employee_id BETWEEN 1000 AND 9999
GO
to several UPDATE statements:

UPDATE employees
SET salary = salary * 1.05
WHERE employee_id BETWEEN 1000 AND 4999
GO
UPDATE employees
SET salary = salary * 1.05
WHERE employee_id BETWEEN 5000 AND 9999
GO

For a maintenance type of task or for a global update, consider putting the database into single-user mode (if it is feasible to keep other users out of the database). Single-user mode does not set locks, so you will not run out of locks, and the operation will run somewhat faster (because you save the locking overhead).

For a large bulk copy operation, the entire operation is treated as a single transaction. When you use the batch parameter (-b), the bcp utility will treat the operation in small transactions with the number of rows specified. At the end of each small transaction, the system resources held by that transaction are freed, so fewer locks are needed.

Richard D. Cushing
Sr. Consultant
SYNERGISTIC SOFTWARE SOLUTION, LLC
A Wholly-owned subsidiary of BDO Seidman, LLP