Macro to call another macro in another folder after closing all existing file and copy paste values

Hi All,
Below is the macro that will close existing open excel files and then copy paste values


Public Sub Macro1()
' Call another macro
Call test2
 
    Windows("Book1.xlsm").Activate
    Range("B1:B4").Select
    Selection.Copy
    Workbooks.Open Filename:="C:\Documents and Settings\S*****\My Documents\Macro\Book2.xlsx"
    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & lMaxRows + 1).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=True
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    ActiveWorkbook.Save
    ActiveWorkbook.Close
   
End Sub
Public Sub test2()
    Dim path As Variant
    Dim excelfile As Variant
    Dim WkbkName As Object
    path = "C:\Documents and Settings\S*****\My Documents\Macro\"
    excelfile = Dir("*.xlsx")
    Do While excelfile <> ""
           
          Application.ScreenUpdating = False

          For Each WkbkName In Application.Workbooks()
'Do not close the existing workbook and the one with the name "Book1.xlsm" but close every other excel file
              If WkbkName.Name <> ThisWorkbook.Name And WkbkName.Name <> "Book1.xlsm" Then WkbkName.Close
          Next

          ' If everything runs all right, exit the sub.
          Exit Sub
    Loop
End Sub

SQL query to get the previous or last month data only

Hi folks,
Find below the query that fetches the previous month data only



Select * from EmpJD
Where evaluations.quality_date >= DATEADD(MONTH, DATEDIFF(MONTH, 31, getdate()), 0)
AND evaluations.quality_date < DATEADD(MONTH, DATEDIFF(MONTH, 0,  getdate()), 0)

Macro to paste values in next available cell or row

Hi All,
I was given a task which required me to copy, transpose and paste data from one worksheet to another. I completed this task in just 5 minutes with the following macro


Sub Macro1()
'
' Macro1 Macro
'

'
    Range("B1:B4").Select
    Selection.Copy
    Workbooks.Open Filename:="C:\Documents and Settings\ShruSoor\My Documents\Macro\Book2.xlsx"
    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & lMaxRows + 1).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=True
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    ActiveWorkbook.Save
    ActiveWorkbook.Close
   
End Sub

Enjoy!!

Difference between ON and WHERE in sql statements

Hi All,
We generally find the difference between the ON and WHERE statements in sql when JOIN's are used.
WHERE - eliminates rows from the result set
ON - is applied on the join statement only

WHERE is a part of the SELECT query as a whole, ON is a part of each individual join.

ON can only refer to the fields of previously used tables.

When there is no actual match against a record in the left table, LEFT JOIN returns one record from the right table with all fields set to NULLS. WHERE clause then evaluates and filter this.

Refer below links for more clarification

http://stackoverflow.com/questions/2722795/in-sql-mysql-what-is-the-difference-between-on-and-where-in-a-join-statem

http://www.sqlservercentral.com/articles/T-SQL/93039/

Shared Memory Provider Error 233 No Process Is On The Other End Of The Pipe

Hi,
I completed my ETL work on Friday and left my computer locked. When I came on Monday I got the below error message

'Shared Memory Provider Error 233 No Process Is On The Other End Of The Pipe"

I got this message when I was trying to alter the ODBE connection in my SSIS package. I was able to run queries properly in my SQL server. 
After much browsing..I realised that I had to restart my DB..when I disconnected and tried to connect , i got the same error message.


T resolve this issue, one has to open the configuration manager and restart the SQL SERVER instance...bingo..u have got rid of the error

 

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.

Apply colors to the alternate rows in SSRS reports

Hi All,
One of the basic attempt by reporters is is to make their report look good and they would achieve this by adding colors to the rows. It is easy to change the background color of the reports, but if you have to apply colors to the alternate rows in SSRS reports. Then follow the below steps

Choose the row in design mode
Then choose expression under BackgroundColor
Choose Expression and then paste the below

=IIf((RowNumber(Nothing) MOD 2) = 1, "Gainsboro", "Transparent").

The RowNumber will return the count of the row and MOD (modulus) will determine

if the row is even or odd and apply color accordingly

NOTE - This will work only in SSRS 2008 and not in 2005 or 2000

Use more than 1 Order By clause in SQL statement

Hi All,

Do you want the order of your reports in two parameters. Good news, its possible. Use

Order By QDate Asc, Order By DeskName Asc 

to achieve the desired result

Reports not showing the right order of dates or week numbers horizontally

Hi All,
I had this issue from quite a long time. I was trying to publish the data weekwise and strangely the order of weeks would be like Week 2, Week 5 and then Week 3. I medled with lot of SSRS features to correct this...but alas all I had to do was mend the query and add Order By Date/Week Desc/Asc and Viola!...I got my report headings in the order Week 2, Week 3, Week 4 and then Week 5

Excel is pasting the same formula value and not changing with cell value

Hi Folks,
Today I faced a strange issue. When I enter the cell value ABC in A2 and apply the =Proper(A2) I get Abc.
But when I paste the same formula to B3 cell value with formula =Proper(B2) for B2 value DEF I am still getting Abc instead of Def.
The same issue repeated for all formulas.
After trying various options in excel, I was able to find the resolution.
All I had to do was :
Click on Formulas  tab >Calculation Option>And choose Automatic option

Viola!
My formula values were back

Column mappings do not reflect while Configuring SSIS connection manager

Hi Folks,
After I was able to get the sharepoint connection manager and when I try to configure it with the Site URl, I could not get any input parameters.
All I had to do was hit 'REFRESH' tab at the bottom and VIOLA..all column mappings were found

SPCRED not present in SSIS connection manager

Hi Folks,
I am so happy to get this issue resolved.
Got an assignment to import data from the Sharepoint list. 
Browsed and found out that I had to install "SharePointListAdaptersSetup.msi" first.
After the donwload I could not proceed further as SPCRED - connection manager for sharepoint was not present.

All I had to do was close BIDS completely ans start again

Viola..SPCRED was back

Server execute package utility version 10.0.1600.22 for 32-bit

Hi All,
I got the above error message when I tried to execute job which included an SSIS package.

I tried the steps in the below link and it resolved the issue.


http://decipherinfosys.wordpress.com/2008/09/17/scheduling-ssis-packages-with-sql-server-agent/

You may still get error message. But check with the job once. You would have the job completed

If the job has not been completed then check for the sources that you have used for this job.
For example if you have designed a SSIS package where you are extracting data from excel sheet and transferring back to OLEDB then make sure that you have closed the excel files.