Expert advice on database administration

Digg This!     StumbleUpon     Del.icio.us

[TABLE]

Question: Is it possible to execute an Oracle stored procedure on SQL Server? If so, how?

Kline: Oracle uses its own dialect of SQL called PL/SQL (procedural language extension to Structured Query Language), while SQL Server uses T-SQL (Transact-SQL). The two have very different rules about variables, parameters, conditional processing, error handling and so forth. So unless the Oracle stored procedure contains only ANSI SQL statements, it won't work on SQL Server. However, Microsoft recently introduced a new tool that aids in the migration process from Oracle to SQL Server. You should investigate the SQL Server Migration Assistant. You should also note that moving from Oracle to SQL Server is not considered a simple point-n-click process. It usually involves a healthy dose of planning, analysis and good ol' fashion elbow grease.

Click for the original question and response.

[TABLE]

Question: I am currently working on a project with Oracle9i Forms at the front end and Microsoft SQL Server at the back end. What steps should I take to connect

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL Server Interoperability Utilize SSAS for data predictions and classification using Excel How to create a SQL Server linked server to DB2 Export SQL Server data to an Excel file using SSIS and Visual Studio Performance tuning for SQL Server 2005 and Exchange running on SBS Custom VB.Net scripting in SQL Server Integration Services Can SQL Server 2000 work on Windows 2003 platform? Query to search text in old DTS packages in SQL Server? Handle slowly changing dimensions with SSIS 2005 wizard Run DTS packages within SQL Server Integration Services SQL Server Integration Services how-to SQL Server Stored Procedures Top 10 SQL Server development tips of 2008 SQL Server trigger vs. stored procedure to receive data notification SQL Server errors, failures and other problems fixed from the trenches SQL Server and data manipulation in T-SQL How to use SQL Server 2008 hierarchyid data type SQL Server stored procedures tutorial: Write, tune and get examples Check SQL Server database and log file size with this stored procedure SQL Server source code analysis and management adds database security Configure SQL Server Service Broker for sending stored procedure data Find size of SQL Server tables and other objects with stored procedure SQL Server Management A first look at Microsoft SQL Server 2008 R2 Maintaining high availability of SQL Server virtual machines Creating fault-tolerant SQL Server installations Using Microsoft Hyper-V for SQL Server consolidation Scaling up vs. scaling out with SQL Server 2008 Migrating to SQL Server 2008 and leveraging new features Testing a SQL Server environment before an upgrade Does upgrading to SQL Server 2008 fit your business? Meeting business needs with SQL Server full-text search Using dynamic management views to improve SQL Server index effectiveness

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary CORBA  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

an Oracle9i Forms application with a SQL Server database?

Kline: I can't think of an easy way off of the top of my head. There's the Oracle Transparent Gateway. But that is certainly not an easy approach. There are also a few tools available using Oracle's Migration Workbench. You may also be able to connect using a JDBC driver. Check out Microsoft's JDBC driver for SQL Server. You can also find a list of JDBC drivers at DevX.com.

Click for the original question and response.

[TABLE]

Question: Can a stored procedure be used to import data from other types of databases and text files to SQL Server?

Kline: Absolutely! I've written many stored procedures to import data from other servers, usually Oracle and Access as well as from text files. When importing data from other servers, you'll have an easier time if you set up the source server as a Linked Server (see SQL Server Books Online for more details).

Once you've set up the Linked Server, you can write SQL statements directly against the Linked Server. For example, you could write the statement INSERT INTO local_table…SELECT col1, col2,… FROM linked_server.db1.dbo.foo.

When importing data from text files, simply use the BULK INSERT statement or, my preference, call the BCP command line utility using XP_CMDSHELL within the stored procedure. It takes a little work to get the exact XP_CMDSHELL string correct. All you have to do is encapsulate the BCP command-line string within a parameter for XP_CMDSHELL extended stored procedure.

Click for the original question and response.

More information from SearchSQLServer.com

Rate this Tip To rate tips, you must be a member of SearchSQLServer.com. Register now to start rating these tips. Log in if you are already a member. DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.