Saturday, March 30, 2013

How to Save a PDF to a SQL Server

http://www.ehow.com/how_7334919_save-pdf-sql-server.html

Wednesday, March 13, 2013

How to select cells/ranges by using Visual Basic procedures in Excel

ActiveSheet.Cells(5, 4).Select  
or 

  
ActiveSheet.Range("D5").Select  

http://support.microsoft.com/kb/291308

Excel VBA ComboBox Populate dynamic value

Private Sub UserForm_Click()
With ComboBox1
For Row = 1 To 10 'Each cell in the range
If ActiveSheet.Cells(2, Row) <> "" Then
.AddItem ActiveSheet.Cells(2, Row)
End If
Next Row
End With


ActiveSheet.Cells(5, 5) = ComboBox1.Value

End Sub

Tuesday, March 5, 2013

Write A Report in Excel using VBA - SQL-server

I wrote first report in Excel-VBA using data from SQL-Server 


Sub runreports()


    

    Dim cnt As ADODB.Connection

    Dim rst As ADODB.Recordset

    Dim stSQL As String

    Dim wbBook As Workbook

    Dim wsSheet As Worksheet

    Dim rnStart As Range

    

     

    Const stADO As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=xxxxx;Data Source=devDB;User ID=myuser;Password=mypass; Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=myPC;Use Encryption for Data=False;Tag with column collation when possible=False"

    

     

    Set wbBook = ActiveWorkbook

    Set wsSheet = wbBook.Worksheets(5)

    

    With wsSheet

        Set rnStart = .Range("A7")

    End With

    'Report Name

    Cells(1, 10).Value = "Interco Report"

    

    stSQL = "SELECT top 100 * FROM tbl_intercoRep"

    'stSQL = "SP_Int_DeleteThis"

'    stSQL = "SP_IntercoReport"

   

     

    Set cnt = New ADODB.Connection

    

    With cnt

        .CursorLocation = adUseClient

        .Open stADO

        .CommandTimeout = 0

        Set rst = .Execute(stSQL)

    End With

   

'Worksheets("sheet5").Activate

Dim ii As Integer

    For ii = 0 To rst.Fields.Count - 1

    Cells(6, ii + 1).Value = rst.Fields(ii).Name

Next ii

 

    With Range("A1:ZZ1").Font

    .Bold = True

    .Size = 15

   

    End With

   

    With Range("A6:ZZ6").Font

    .Bold = True

   

    

    End With

   

     'Here we add the Recordset to the sheet from A1

    rnStart.CopyFromRecordset rst

    

     'Cleaning up.

    rst.Close

    cnt.Close

    Set rst = Nothing

    Set cnt = Nothing

End Sub


Saturday, March 2, 2013

Building a Word Document Using SQL Server Data

In this walkthrough, you will first create a Microsoft® Office Word 2003 document that contains bookmarks as the location for inserting text retrieved from the Microsoft SQL Server Northwind sample database. You will then use ADO.NET to connect to and retrieve the data. You'll insert the data in the Word document at the specified bookmark.

http://msdn.microsoft.com/en-us/library/office/aa192487(v=office.11).aspx

Thursday, February 28, 2013

Display Sql-server Data into Excel Using VBA


-->
Display Sql-server Data into Excel Using VBA , Working code

Private Sub CommandButton1_Click()
Dim adoCN As ADODB.Connection
Dim sConnString As String
Dim results As ADODB.Recordset
Dim sSQL As String
Dim lRow As Long, lCol As Long
sConnString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=devdb;Data Source=sqlserverdv1;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=USER-PC;Use Encryption for Data=False;Tag with column collation when possible=False"
Set adoCN = CreateObject("ADODB.Connection")
adoCN.Open sConnString

    sSQL = "select top 10 * from tempdata"
Set results = adoCN.Execute(sSQL)
   
    
    
    For Each f In results.Fields
      'Debug.Print f.Name & " " & f
      ActiveSheet.Range("A1").CopyFromRecordset results
    Next
adoCN.Close
Set adoCN = Nothing


End Sub


Tuesday, February 26, 2013

Parent Child Recursive Relation SQL-Server 2008 JDE

 

drop table #temp2

select yaan8,a.ABALPH ChildName, yaanpa,b.ABALPH ParentName into #temp2  from proddta.F060116

left outer join PRODDTA.F0101 a on yaan8=a.aban8

left outer join PRODDTA.F0101 b on yaanpa=b.aban8

where  yapast in('1','0','6') order by 2

go

 

WITH Recursive_CTE AS (

SELECT

  child.YAAN8,

  CAST(child.ChildName as varchar(100)) ChildName,

  child.yaanpa yaanpa,

  CAST(NULL as varchar(100)) ParentUnit,

  CAST('>> ' as varchar(100)) LVL,

  CAST(child.YAAN8 as varchar(100)) Hierarchy,

  1 AS RecursionLevel

FROM #temp2 child

WHERE YAAN8 = 123456

 

UNION ALL

 

SELECT

  child.YAAN8,

  CAST(LVL + child.ChildName as varchar(100)) AS ChildName,

  child.yaanpa,

  parent.ChildName ParentUnit,

  CAST('>> ' + LVL as varchar(100)) AS LVL,

  CAST(Hierarchy + ':' + CAST(child.YAAN8 as varchar(100)) as varchar(100)) Hierarchy,

  RecursionLevel + 1 AS RecursionLevel

FROM Recursive_CTE parent

INNER JOIN #temp2 child ON child.yaanpa = parent.YAAN8

)

SELECT * FROM Recursive_CTE ORDER BY Hierarchy

Thursday, February 21, 2013

Writing Word from Database or Excel

 

 

Writing Word from Database

Best way to do this using Access Database, do following:-

 

1.       Create a Table in access database and define all of the related data for mail merge.

2.       Open this table and click on External Data Word Merge.

3.       Word document will open, Select second option –> Create a new document and then link the data to it.

4.       Type your letter and if you want to attach a field you can click at insert merge field and select your field.

5.       After this you can check the preview.

6.       Click at Finish & merge, once you done.

I think you can use excel by attaching it to access database.


Monday, December 31, 2012

SQL-Server Database Users & Roles

How to find SQL-Server Database Users & Roles:-


DECLARE
    @db_name SYSNAME,
    @sql VARCHAR(1000),
    @databaseNM varchar(50)
    set @databaseNM='JDE_CRP'

DECLARE db_cursor CURSOR FOR SELECT Name FROM sys.databases where Name=@databaseNM
OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @db_name

WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET @sql =
        'SELECT
            ''' + @db_name + ''' AS [Database],
            USER_NAME(role_principal_id) AS [Role],
            USER_NAME(member_principal_id) AS [User]
        FROM
            ' + @db_name + '.sys.database_role_members'

    EXEC(@sql)

    FETCH NEXT FROM db_cursor INTO @db_name
END

CLOSE db_cursor
DEALLOCATE db_cursor