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

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

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.

Rajeev Kumar

 http://www.nanthalat.com/jk/sdd4yzc7lqsw7ww9u8z0kjuwx9/

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

JD Edwards EnterpriseOne 1099

http://docs.oracle.com/cd/E20246_01/jded/acrobat/e1_1ayp_1110.pdf

Remote Desktop iPhone iPad windows

http://foxsoft.us/ipad/remote_desktop/help/GuideLines%20to%20Remote%20Desktop.pdf


Sent from my iPhone

Create HTTP GET and POST Request with C#

http://codesamplez.com/programming/http-request-c-sharp


Sent from my iPad

c# - How to receive post data(XML) in .NET

http://stackoverflow.com/questions/986664/how-to-receive-post-dataxml-in-net-like-java-servlet-dopost


Sent from my iPad

This data dictionary defines fields used in P6.

http://www.integrationfaces.com/calling-oracle-primavera-p6-eppm-r8-2-web-services-from-oracle-bpel/

Setting Up Benefits Administration JD Edwards E1

Setting Up Benefits Administration

This chapter contains the following topics:

• Section 2.1, "Understanding Benefits Administration Setup"
• Section 2.2, "Understanding UDCs for Benefits"
• Section 2.3, "Setting Up Common Settings for Benefits Enrollment"
• Section 2.4, "Setting Up Benefit Group Information"
• Section 2.5, "Setting Up Benefit Plans"
• Section 2.6, "Setting Up Plan Options"
• Section 2.7, "Recalculating Plan Option Costs"
• Section 2.8, "Linking Plans to Benefit Categories"
• Section 2.9, "Setting Up Date Codes"
• Section 2.10, "Setting Up Pay Type Tables"
• Section 2.11, "Setting Up Eligibility Tables"
• Section 2.12, "Setting Up Enrollment Parameters"
• Section 2.13, "Linking Allocation Funds to Plans"
• Section 2.14, "Reviewing the Benefit Structure"
  
  
  
  
  
  
  http://docs.oracle.com/cd/E16582_01/doc.91/e15091/stup_beft_adm.htm#EOACC00049
  
  
  
  
  
  

Converting Payroll History JD Edwards E1

Converting Payroll History

When you implement the JD Edwards EnterpriseOne Payroll system in the middle of a calendar year, you typically need to enter the payroll history records from the legacy payroll system into the JD Edwards EnterpriseOne Payroll system. The system uses these payroll history records to calculate the information that appears on employees year-end forms.


The system provides a conversion process that you can use to import payroll history records from a legacy system and convert them into the format that is used by the JD Edwards EnterpriseOne Payroll system.



Each time that you process a payroll cycle, the system creates historical records of employees earnings, deductions, and taxes. You use these historical records to print historical and governmental reports, answer employees questions, and process year-end forms for employees. In some cases, you might need to import payroll history records from another payroll system and convert them to the format that is used by the JD Edwards EnterpriseOne Payroll system. These situations are examples of when you might need to convert payroll history:

    http://docs.oracle.com/cd/E16582_01/doc.91/e15133/cvrt_payroll_histry.htm  

Building and Using WebServices with JDeveloper

Web Services provide client neutral access to data and other services. JDeveloper allows you to create different types of Web Services quickly and easily....

In this tutorial, you will create 4 different Web Services: a POJO Annotation-Driven service, a Declaratively-Driven POJO service, a service for existing WSDL, and an EJB service. The focus of these scenarios is to demonstrate and test Java EE 5 web services. In particular this means JAX-WS (Java API for XML Web Services) and annotation handling. JAX-WS enables you to enter annotations directly into the Java source without the need for a separate XML deployment descriptor 

At the end of the tutorial you create an ADF Client application that consumes the web service you created. 

http://docs.oracle.com/cd/E18941_01/tutorials/jdtut_11r2_52/jdtut_11r2_52_1.html

Jdeveloper To connect to Microsoft SQL Server

To connect to Microsoft SQL Server:

1. From www.microsoft.com, download and install the appropriate Microsoft SQL Server driver:

   • For Microsoft SQL Server 2005, choose Microsoft SQL Server 2005 Driver.

   • For Microsoft SQL Server 2008, choose Microsoft SQL Server 2008 Driver.

2. Set up the user library to contain install-directory\sqljdbc.jar.

3. Create a database connection to Microsoft SQL Server. Use the following values:

   • Connection Type: SQLServer

   • Username and Password: enter the appropriate values for the connection.

   • Driver Class: com.microsoft.sqlserver.jdbc.SQLServerDriver

   • Library: the library you created for the driver.

   • JDBC URLs: jdbc:sqlserver://machine-name:port;DatabaseName=database-name, where the section DatabaseName=database-name is optional

What you May Need to Know

If you are using Windows Authentication credentials to connect to Microsoft SQL Server, you need to add do the following:

• Add the connection property integratedSecurity=TRUE and the username and password values to the JDBC URL, for example

  jdbc:sqlserver://machine-name:port;DatabaseName=database-name;username=USERNAME;password=PASSWORD;integratedSecurity=TRUE  

• Add the location of sqljdbc_auth.dll to your PATH variable:

  • For 32bit JVM, this is installation-directory\sqljdbc_version\language\auth\x86

  • For 64bit JVM, this is installation-directory\sqljdbc_version\language\auth\x64

For more information, see Building the Connection URL, which is available as part of Connecting to SQL Server with the JDBC Driver at the Microsoft MSDN website.

http://docs.oracle.com/cd/E16162_01/user.1112/e17455/connect_work_databases.htm

Using BPEL to Build Composite Services and Business Processes

BPEL is a rich XML based language for describing the assembly of a set of existing 

web services into either a composite service or a business process. Once deployed, 

a BPEL process itself is actually invoked as a web service.

Thus anything that can call a web service, can also call a BPEL process, including 

of course other BPEL processes. This allows you to take a nested approach to 

writing BPEL processes, giving you a lot of flexibility.

In this chapter we first introduce the basic structure of a BPEL process, its key 

constructs, and the difference between a synchronous and asynchronous service. 

We then demonstrate through the building and refinement of two example BPEL 

processes, one synchronous the other asynchronous, how to use BPEL to invoke 

external web services (including other BPEL processes) to build composite services. 

During this procedure we also take the opportunity to introduce the reader to many 

of the key BPEL activities in more detail.

http://media.techtarget.com/searchSOA/downloads/OracleSOADevelopersGuide_05_Final.pdf

--