The OLE DB provider "SQLNCLI10" for linked server reported an error. The provider ran out of memory.

Hi,
I opened SSRS after a long time and I got this error..hm..got exited to solve this error message.
Found out the root cause of this issue..it was with the linked server group...
I followed few steps, not sure which one actually resolved my issue. You may try any of these...

1. Clear Temp files
2. Delete some unwanted database from your SQL database on which linked servers are created
3. Backup database which are old (optional)
4. Restart all the SQL services in the SQL server configuration manager window

Viola..I was able to add linked servers again


Enjoy!

Error (Data mining): Either the user, ....\Administrator, does not have permission to access the referenced mining model, Adventure Works DW2008R2, or the object does not exist.

Hi All.
I got the above error message while I was trying to run basic MDX query. All I had to do was add axes group to the group i,e on 0 and on 1 as shown below


Select [Measures].[Sales Amount] on 0, [Dim Product].[English Product Name] on 1 from
[Adventure Works DW2008R2]

And viola..ur error is resolved :)

session mining objects (including special data source views used to process data mining) cannot be created on this instance

Hi,
Was trying Data mining on my home PC. Got the below error

session mining objects (including special data source views used to process data mining) cannot be created on this instance

Close excel sheets, Followed steps in  the below link and viola..it got fixed

SSIS LookUP

Hi Folks,
Here is an interesting link that teaches us about LookUP in SSIS

http://www.youtube.com/watch?v=PQ1eM0TUdl0&feature=related

Difference between truncate and Delete statement in SQL

Compared to the DELETE statement, TRUNCATE TABLE has the following advantages:

• Less transaction log space is used.
  
  The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.
• Fewer locks are typically used.
  
  When the DELETE statement is executed using a row lock, each row in the table is locked for deletion. TRUNCATE TABLE always locks the table and page but not each row.
• Without exception, zero pages are left in the table.
  
  After a DELETE statement is executed, the table can still contain empty pages. For example, empty pages in a heap cannot be deallocated without at least an exclusive (LCK_M_X) table lock. If the delete operation does not use a table lock, the table (heap) will contain many empty pages. For indexes, the delete operation can leave empty pages behind, although these pages will be deallocated quickly by a background cleanup process.

TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. To remove the table definition in addition to its data, use the DROP TABLE statement.

If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used. To retain the identity counter, use DELETE instead.

Restrictions

You cannot use TRUNCATE TABLE on tables that:

• Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)
• Participate in an indexed view.
• Are published by using transactional replication or merge replication.

For tables with one or more of these characteristics, use the DELETE statement instead.

TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions. For more information, see CREATE TRIGGER (Transact-SQL).

Delete Excel sheet contents in SSIS

Hi,
I wanted to my excel sheet to have new data ever time I run a package in SSIS. I tried many steps and what worked for me is the steps given in the below link

http://oakdome.com/programming/SSIS_DataTransform.php


Hope this has helped you!

Delete Excel sheet contents in SSIS

Hi,
I wanted to my excel sheet to have new data ever time I run a package in SSIS. I tried many steps and what worked for me is the steps given in the below link

http://oakdome.com/programming/SSIS_DataTransform.php


Hope this has helped you!

Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (5) 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.

Hi,
I had got the above error message suddenly after running a series of ForEach loop in SSIS.
To resolve this issue:

Check if your ForEach loop is referring to the same file location as your Excel Connection Manager is doing
Later simply set 'Delay Validation' = True

Issue is resolved.....!!

What is the use of Stored Procedures

Hi,
 Stored Procedure is used in separating the logic that performs basic data insert from core matching logic provides advantages for auditing purposes and enables the process of creating new batches to be more modular. The other advantage to using stored procedures is higher performance. A stored procedure enables you to place all the T-SQL logic in one place, and it can be optimized by the Query Optimizer. A stored procedure can also be placed under separate execution rights and managed separately, instead of embedding the T-SQL into, and applying the rights to, the package itself.