Converting Oracle SQL To TSQL
Jul 20, 2005Hello,
I am new to sql*server, but have used Oracle for years. Can anyone
recommend a good online guide for converting all the Oracle SQL I know
to TSQL?
tia,
Mike
ADVERTISEMENT
Converting Access DB Code To Tsql--Help!!!!!
May 3, 2001We are migrating an access97 database to sql server7.0. there are queries in access which need to be made into tsql queries.And what are the steps to convert access97 to sql7.0 and how do i migrate memo fields to sql7. Is there some method to convert or tool that does that...any help is welcome and thanks in advance.
View 1 Replies View RelatedHelp Required Converting From MS Access SQL To TSQL
May 20, 2008Hello, I am fairly new to SQL Server so I apologise if this is the wrong forum. I have a Sales analysis table in a SQL Server 2000 database. The table is populated from various sources in our ERP system. via a DTS package For our French branch sales unit of measure is eachs (EA) for actuals, but the primary UOM and our forecast data is normally in cartons. I have a product master table which defines primary unit of measure, and a unit of measure conversion table. So if I wanted to convert the data all to the primary measure I would write the below in Access:
UPDATE (tblSalesReport INNER JOIN tblItemMasterERP ON tblSalesReport.fldProductNo = tblItemMasterERP.fldProductNo) INNER JOIN tblUOMConvertERP ON (tblItemMasterERP.fldShortItemNo = tblUOMConvertERP.fldItemNo) AND (tblItemMasterERP.fldPrimaryUOM = tblUOMConvertERP.fldUOM1) SET tblSalesReport.fldUOM = [tblItemMasterERP]![fldPrimaryUOM], tblSalesReport.fldQuantity = [tblSalesReport]![fldQuantity]/[tblUOMConvertERP]![fldConvFactor]
WHERE (((tblSalesReport.fldCompany)="00007") AND ((tblUOMConvertERP.fldUOM2)=[tblSalesReport]![fldUOM]) AND (([tblSalesReport]![fldUOM])<>[tblItemMasterERP]![fldPrimaryUOM]));
I have found that in the DTS I can add an SQL task, but it seems to only allow UPDATE if there are no joined tables. I found the same thing in Stored Procedures, the SQL designer would only allow me to use one table. I guess I am looking in the wrong places. Can anyone point me in the right direction to incorporate the above sql (or equivolent) into our DTS package. Unfortunately the company decided to dispense with the services of the person who designed the package.
Converting From Oracle.
Oct 13, 2005I recently got a new job at a new company. My previous experience
had all been with Oracle DB's but the new place uses Sql Server
2k. I have a few general questions about how to do stuff in
SqlServer...
1) how do I create a sequence and a trigger?
2) I know SqlServer probably doesnt have anything like Packages, so are
procedures local to schemas? and if so, are schema's nestable in any
way?
3) Can anyone reccomend a good book on T-SQL?
Converting From Oracle
Jan 23, 2008I am porting some application from AIX/C/Oracle to Windows/C#2005/MS SQL 2000. I have one query which has been giving me problems in translation from Oracle to MS SQL. This query had some decode statements which I changed to CASE statements. It also has Oracle (+) join operator, from what I have found this translates into an *= or =* depending which side the (+) is on.
Here is the query before replacing (+)
SELECT CSUM_TERR_CODE,
INVM_COST_FLAG,
SUM((CASE CITM_PROPRD WHEN '200708' THEN 1 ELSE 0 END) * (CITM_QTY * CITM_PRICE)),
SUM(CITM_QTY * CITM_PRICE)
FROM ACC_CUST_SUM, ACC_CUST_ITEMS, ACC_INV_MSTR
WHERE CSUM_PROPRD BETWEEN '200701' AND '200708' AND
CSUM_COMP_CODE = 'A' AND
CSUM_EMPL_CODE = '85' AND
CSUM_PROPRD = CITM_PROPRD AND
CSUM_INV_SEQ = CITM_INV_SEQ AND
CITM_DEPT_CODE NOT IN ('TR', 'DTR') AND
CITM_COMP_CODE = INVM_COMP_CODE(+) AND
CITM_ITEM_CODE = INVM_ITEM_CODE(+) AND
INVM_COST_FLAG = 'C'
GROUP BY CSUM_TERR_CODE, INVM_COST_FLAG
UNION
SELECT CSUM_TERR_CODE,
(CASE INVM_COST_FLAG WHEN NULL THEN 'A'ELSE INVM_COST_FLAG END),
SUM((CASE CITM_PROPRD WHEN '200708' THEN 1 ELSE 0 END) * (CITM_QTY * CITM_PRICE)),
SUM(CITM_QTY * CITM_PRICE)
FROM ACC_CUST_SUM, ACC_CUST_ITEMS, ACC_INV_MSTR
WHERE CSUM_PROPRD BETWEEN '200701' AND '200708' AND
CSUM_COMP_CODE = 'A' AND
CSUM_EMPL_CODE = '85' AND
CSUM_PROPRD = CITM_PROPRD AND
CSUM_INV_SEQ = CITM_INV_SEQ AND
CITM_DEPT_CODE NOT IN ('TR', 'DTR') AND
CITM_COMP_CODE = INVM_COMP_CODE(+) AND
CITM_ITEM_CODE = INVM_ITEM_CODE(+) AND
(INVM_COST_FLAG = 'A' OR INVM_COST_FLAG IS NULL)
GROUP BY CSUM_TERR_CODE, INVM_COST_FLAG
and after replacing with *, I think I have them in the right place
SELECT CSUM_TERR_CODE,
INVM_COST_FLAG,
SUM((CASE CITM_PROPRD WHEN '200708' THEN 1 ELSE 0 END) * (CITM_QTY * CITM_PRICE)),
SUM(CITM_QTY * CITM_PRICE)
FROM ACC_CUST_SUM, ACC_CUST_ITEMS, ACC_INV_MSTR
WHERE CSUM_PROPRD BETWEEN '200701' AND '200708' AND
CSUM_COMP_CODE = 'A' AND
CSUM_EMPL_CODE = '85' AND
CSUM_PROPRD = CITM_PROPRD AND
CSUM_INV_SEQ = CITM_INV_SEQ AND
CITM_DEPT_CODE NOT IN ('TR', 'DTR') AND
CITM_COMP_CODE =* INVM_COMP_CODE AND
CITM_ITEM_CODE =* INVM_ITEM_CODE AND
INVM_COST_FLAG = 'C'
GROUP BY CSUM_TERR_CODE, INVM_COST_FLAG
UNION
SELECT CSUM_TERR_CODE,
(CASE INVM_COST_FLAG WHEN NULL THEN 'A'ELSE INVM_COST_FLAG END),
SUM((CASE CITM_PROPRD WHEN '200708' THEN 1 ELSE 0 END) * (CITM_QTY * CITM_PRICE)),
SUM(CITM_QTY * CITM_PRICE)
FROM ACC_CUST_SUM, ACC_CUST_ITEMS, ACC_INV_MSTR
WHERE CSUM_PROPRD BETWEEN '200701' AND '200708' AND
CSUM_COMP_CODE = 'A' AND
CSUM_EMPL_CODE = '85' AND
CSUM_PROPRD = CITM_PROPRD AND
CSUM_INV_SEQ = CITM_INV_SEQ AND
CITM_DEPT_CODE NOT IN ('TR', 'DTR') AND
CITM_COMP_CODE =* INVM_COMP_CODE AND
CITM_ITEM_CODE =* INVM_ITEM_CODE AND
(INVM_COST_FLAG = 'A' OR INVM_COST_FLAG IS NULL)
GROUP BY CSUM_TERR_CODE, INVM_COST_FLAG
The Oracle run fine, but when run on MS SQL I get the following error.
The table 'ACC_CUST_ITEMS' is an inner member of an outer-join clause. This is not allowed if the table also participates in a regular join clause.
Any ideas on how I can get this one working?
Thanks
Converting From Oracle
Mar 6, 2008Converting Oracle Queries to run on MS SQL 2000. This is query with a sub-query. If I run each query separatly I get results and if I use NOT IN sub-query I get no results. On oracle I get the correct result, but when moved to MS SQL I get nothing. Here is the original Oracle query and my converted query. Am I missing something?
Oracle
SELECT
INVM_COMP_CODE,
INVM_DEPT_CODE,
INVM_ITEM_CODE,
INVM_DESC,
INVM_UNIT_TYPE,
INVM_LOC,
nvl(INVM_REORDER_POINT,0),
nvl(INVM_REORDER_QTY,0),
nvl(INVM_COST,0),
nvl(INVM_SUG_PRICE,0),
INVM_DEV_CLS
FROM ACC_INV_MSTR
WHERE INVM_COMP_CODE||INVM_ITEM_CODE NOT IN
(SELECT CITM_COMP_CODE||CITM_ITEM_CODE
FROM ACC_CUST_ITEMS
WHERE CITM_PROPRD BETWEEN '200706' AND '200710') AND
(INVM_DEPT_CODE IN (SELECT DEPT_CODE FROM ACCSR03_DEPT) OR
:ws_dept_flag = 1)
ORDER BY INVM_COMP_CODE, INVM_DEPT_CODE, INVM_ITEM_CODE
MS SQL
SELECT INVM_COMP_CODE,
INVM_DEPT_CODE,
INVM_ITEM_CODE,
INVM_DESC,
INVM_UNIT_TYPE,
INVM_LOC,
ISNULL(INVM_REORDER_POINT,0),
ISNULL(INVM_REORDER_QTY,0),
ISNULL(INVM_COST,0),
ISNULL(INVM_SUG_PRICE,0),
INVM_DEV_CLS
FROM ACC_INV_MSTR
WHERE INVM_COMP_CODE + INVM_ITEM_CODE NOT IN
(SELECT CITM_COMP_CODE + CITM_ITEM_CODE
FROM ACC_CUST_ITEMS
WHERE CITM_PROPRD BETWEEN '200706' AND '200710') AND
(INVM_DEPT_CODE IN (SELECT DEPT_CODE FROM ACCSR03_DEPT) OR @ws_dept_flag = 1)
ORDER BY INVM_COMP_CODE, INVM_DEPT_CODE, INVM_ITEM_CODE
Converting A Oracle PL/SQL Command Into MS SQL
Jan 31, 2007hi
I was wondering if anyone would be able to advise on converting oracle PL/SQL features into MSSQL.
For example i have the following sequence + trigger below but cant find any information on creating a sequence in MSSQL, is it possible?
create sequence a_SEQ
start with 001
increment by 1
create or replace trigger a_TG
before insert a
for each row
begin
select (concat('D',(cast(a_SEQ.nextval as varchar(4))))) into :new.a_id from dual;
end;
any links or tutorials would be great, i've got a couple of MSSQL 2005 books which do explain triggers but examples are really needed to understand the full functionality.
cheers
Converting Oracle SQL To MS-SQLServer SQL
Jul 20, 2005Hello,Has anyone a small tool or somekind of document which could help meto convert Oracle SQL scripts to SQL Server?Scripts are not very Oracle specified.Thanks,Below is a Script that I would e.g convert to MS SQLServer:SET SCAN ONPROMPT Enter the password and TNS name.PROMPT Enter the oracle SID for TNS name if you are running a local database.CONNECT system/&systempassword@&&tnsname-- Drop the user and all other related objects.DROP USER webstore CASCADE;-- Creating the schemaCREATE USER webstore IDENTIFIED BY welcome;-- Grant the permissions to the user.GRANT RESOURCE, CONNECT TO webstore;ALTER USER webstore DEFAULT TABLESPACE usersQUOTA UNLIMITED ON users;ALTER USER webstore TEMPORARY TABLESPACE temp;CONNECT webstore/welcome@&&tnsnamePROMPT Creating Tables-- Create the category table which contains the data for the categories.CREATE TABLE category (id NUMBER(10) CONSTRAINT pk_vsm_country PRIMARY KEY,name VARCHAR2(20) NOT NULL);-- Create category attributes table which has attributes for the categories in-- the category table.CREATE TABLE category_attributes (category_id NUMBER(10) NOT NULL,label VARCHAR2(20) NOT NULL,CONSTRAINT pk_vsm_cat_attr PRIMARY KEY(category_id,label),CONSTRAINT rk_vsm_catattr_cat FOREIGN KEY(category_id) REFERENCEScategory(id) ON DELETE CASCADE);-- Create country table to hold the country names.CREATE TABLE country (id NUMBER(4) CONSTRAINT pk_country PRIMARY KEY,country_name VARCHAR2(50) CONSTRAINT uniq_country UNIQUE);-- Create users table to hold user details.CREATE TABLE users (user_name VARCHAR2(20) CONSTRAINT pk_users PRIMARY KEY,first_name VARCHAR2(20) NOT NULL,last_name VARCHAR2(20),e_mail VARCHAR2(50) NOT NULL,address VARCHAR2(200) NOT NULL,city VARCHAR2(20) NOT NULL,state VARCHAR2(20) NOT NULL,country NUMBER(4) NOT NULL ,zip NUMBER(8) NOT NULL,phone VARCHAR2(20) NOT NULL,role VARCHAR2(10) NOT NULL,password VARCHAR2(200) NOT NULL,card_provider VARCHAR2(30),card_number VARCHAR2(200),card_expiry_date DATE ,CONSTRAINT rk_usr_cntry FOREIGN KEY(country) REFERENCEScountry(id) ON DELETE CASCADE);-- Create shops master table.CREATE TABLE shops (id NUMBER(10) CONSTRAINT pk_shops PRIMARY KEY,shop_name VARCHAR2(50) NOT NULL,user_name VARCHAR2(20) NOT NULL,description VARCHAR2(4000),category_id NUMBER(10) NOT NULL,reg_date DATE NOT NULL,status VARCHAR2(20) NOT NULL,CONSTRAINT chk_shops CHECK( status IN ('Approved', 'ApprovalPending','Rejected','Discontinued') ),CONSTRAINT rk_shop_cat FOREIGN KEY (category_id) REFERENCES category(id)ON DELETE CASCADE,CONSTRAINT rk_shop_user FOREIGN KEY(user_name) REFERENCES users(user_name)ON DELETE CASCADE);-- Create sub_category table which has the sub categories listed for each shop.CREATE TABLE sub_category (id NUMBER(10) CONSTRAINT pk_subcat PRIMARY KEY,category_id NUMBER(10) NOT NULL,shop_id NUMBER(10) NOT NULL,name VARCHAR2(20) NOT NULL,CONSTRAINT rk_subcat_cat FOREIGN KEY(category_id) REFERENCES category(id)ON DELETE CASCADE,CONSTRAINT rk_subcat_shop FOREIGN KEY(shop_id) REFERENCES shops(id)ON DELETE CASCADE);-- Create item master table.CREATE TABLE item (id NUMBER(10) CONSTRAINT PK_VSM_ITEM PRIMARY KEY,name VARCHAR2(50) NOT NULL,category_id NUMBER(10) NOT NULL,shop_id NUMBER(10) NOT NULL,description VARCHAR2(4000),unit_price NUMBER(15,2) NOT NULL,image VARCHAR2(50),CONSTRAINT rk_item_subcat FOREIGN KEY (category_id ) REFERENCESsub_category(ID) ON DELETE CASCADE ,CONSTRAINT rk_item_shop FOREIGN KEY(shop_id) REFERENCES shops(id)ON DELETE CASCADE);-- Create item_attributes table which has the item attributes for the itemsCREATE TABLE item_attributes (item_id NUMBER(10) NOT NULL,label VARCHAR2(20) NOT NULL,description VARCHAR2(4000),CONSTRAINT pk_item_attr PRIMARY KEY(item_id , label),CONSTRAINT rk_itemattr_item FOREIGN KEY(item_id) REFERENCES item(id)ON DELETE CASCADE);-- Creat inventory table which maps the quantitly against the item.CREATE TABLE inventory (item_id NUMBER(10) CONSTRAINT pk_inventory PRIMARY KEY,quantity NUMBER(4) NOT NULL,CONSTRAINT RK_INVNTRY_ITEM FOREIGN KEY(item_id) REFERENCES item(id)ON DELETE CASCADE);-- Create orders master table which stores all the completed orders.CREATE TABLE orders (id NUMBER(10) CONSTRAINT pk_orders PRIMARY KEY,order_date DATE NOT NULL,user_name VARCHAR2(20) NOT NULL,shop_id NUMBER(10) NOT NULL,ship_to_address VARCHAR2(100),city VARCHAR2(20),state VARCHAR2(20),country NUMBER(4),zip NUMBER(8),phone VARCHAR2(20),CONSTRAINT rk_ordr_usr FOREIGN KEY(user_name) REFERENCES users (user_name)ON DELETE CASCADE,CONSTRAINT rk_ordr_shop FOREIGN KEY(shop_id) REFERENCES shops(id)ON DELETE CASCADE);-- Create order_items table to store the detailed ordersCREATE TABLE order_items (order_id NUMBER(10) NOT NULL,item_id NUMBER(10) NOT NULL,quantity NUMBER(4) NOT NULL,unit_price NUMBER(15,2) NOT NULL,status VARCHAR2(20) NOT NULL,CONSTRAINT chk_oi check ( Status in ('Pending','Shipped')),CONSTRAINT rk_oi_ordr FOREIGN KEY(order_id) REFERENCES orders(id)ON DELETE CASCADE ,CONSTRAINT rk_oi_item FOREIGN KEY(item_id) REFERENCES item(id)ON DELETE CASCADE);-- Create guest_book table to store all the guest_book entriesCREATE TABLE guest_book (id NUMBER(10) CONSTRAINT pk_guest_book PRIMARY KEY,user_name VARCHAR2(50) NOT NULL,email_id VARCHAR2(50),rating NUMBER(1),comment_date DATE NOT NULL,comments VARCHAR2(4000));-- create sequences.CREATE SEQUENCE category_seq MAXVALUE 9999999999;CREATE SEQUENCE shops_seq MAXVALUE 9999999999;CREATE SEQUENCE sub_category_seq MAXVALUE 9999999999;CREATE SEQUENCE item_seq MAXVALUE 9999999999;CREATE SEQUENCE inventory_seq MAXVALUE 9999999999;CREATE SEQUENCE orders_seq MAXVALUE 9999999999;CREATE SEQUENCE guest_book_seq MAXVALUE 9999999999;
View 1 Replies View RelatedHelp Converting An Oracle Query To MS SQL
Oct 15, 2007SELECT distinct whse_zone_id, bay_id
From prod_geneology WHERE whse_zone_id in ('SH','CU','SG')
START WITH unit_id = 'BL7B230811'
CONNECT BY PRIOR parent_id = unit_id
And in this case, 'BL7B230811' could be any 10 character string
Any help would be grateful.
Thanks
Converting From Oracle - Another Problem Query
Jan 28, 2008I am getting a slightly different result set after converting my Oracle query to MS SQL. I am only getting those with invm_cost_code = 'A' and not those that are null as I do when I run the query on Oracle. Note, if I change to left outer join, still get wrogn values. But, if I change to full outer join I get those = 'A' and those that are either null or 'C' as nulls (the 'c' come through as null. Any ideas?
Oracle Query
SELECT CITM_PROPRD, CITM_INV_SEQ, CITM_ITEM_SEQ, INVM_COST_FLAG, (CITM_QTY * CITM_PRICE)
FROM ACC_CUST_SUM, ACC_CUST_ITEMS, ACC_INV_MSTR
WHERE CSUM_COMP_CODE = 'A' AND
CSUM_EMPL_CODE = SUBSTR('85',1,2) AND
TRIM(CSUM_TERR_CODE) = TRIM('MB2') AND
CSUM_PROPRD BETWEEN '200610' AND '200611' AND
CSUM_PROPRD = CITM_PROPRD AND
CSUM_INV_SEQ = CITM_INV_SEQ AND
CITM_COMP_CODE = INVM_COMP_CODE(+) AND
CITM_ITEM_CODE = INVM_ITEM_CODE(+) AND
(INVM_COST_FLAG = 'A' OR INVM_COST_FLAG IS NULL)
Converted MS SQL Query
SELECT CITM.CITM_PROPRD, CITM.CITM_INV_SEQ, CITM.CITM_ITEM_SEQ ,INVM.INVM_COST_FLAG,(CITM.CITM_QTY * CITM.CITM_PRICE)
FROM ACC_CUST_SUM as CSUM
INNER JOIN ACC_CUST_ITEMS as CITM
ON
CSUM.CSUM_PROPRD = CITM.CITM_PROPRD AND
CSUM.CSUM_INV_SEQ = CITM.CITM_INV_SEQ
RIGHT OUTER JOIN ACC_INV_MSTR as INVM
ON
CITM.CITM_COMP_CODE = INVM.INVM_COMP_CODE AND
CITM.CITM_ITEM_CODE = INVM.INVM_ITEM_CODE AND
(INVM.INVM_COST_FLAG = 'A' OR INVM.INVM_COST_FLAG IS NULL)
WHERE CSUM.CSUM_COMP_CODE = 'A' AND
CSUM.CSUM_EMPL_CODE = SUBSTRING('85',1,2) AND
LTRIM(RTRIM(CSUM.CSUM_TERR_CODE)) = LTRIM(RTRIM('MB2')) AND
CSUM.CSUM_PROPRD BETWEEN '200610' AND '200611'
Help Me In Converting Oracle Procedure To SQL Server
Sep 14, 2006Hi All
Can any one help me in converting this from WHICH IN ORACLE to MS SQL SERVER
sqlplus -s $UserId/$PassWord@$DataBase <<EOSQL> $LogFile (This is the Connection String)
set serveroutput on
Declare
tempCnt Number:=0;
totDelCnt Number:=0;
Begin
Loop
$DelStmt
tempCnt := tempCnt+ SQL%ROWCOUNT ;
totDelcnt := totDelCnt+ SQL%ROWCOUNT ;
If SQL%NOTFOUND Then
Exit;
End if;
If tempCnt >= 50000 Then
Commit ;
tempCnt:=0 ;
End if ;
End Loop;
Commit ;
dbms_output.put_line('No of Recs Deleted From $TableName: '|| totDelcnt);
End;
/
exit
EOSQL
Converting Oracle Slq Quer To Sqlserver Query
Jan 14, 2001Hi ,
I have a question for all , please help me out.
the question is : i have a query in Oracle like this
select count(*), to_char(ISL_FIRST_VISIT, 'Day, mm/dd/yyyy') from ISL_USERS
group by to_char(ISL_FIRST_VISIT, 'Day, mm/dd/yyyy')
order by to_char(ISL_FIRST_VISIT, 'Day, mm/dd/yyyy');
i want to convert this into SQLserver but in SQL server equlent function for TO_CHAR is Datename ..when i have given this i am getting error . please give me the tip on this issue .
thanks
shekhar
--------------------------------------------------------------------------------
|
QUESTION: Converting Oracle Shell Scripting From AIX To Windows
Jul 23, 2005Hi, all...I have a sizeable database running under Oracle 9.2.0.4 under AIX5.2.0.I am faced with an impending move to a Windows environment, runningunder SQL 2000.Currently, we are heavy users of shell scripts, for exports, backups,etc.I am looking for viable scripting options under W2K/W2K3. Maybe sh/kshemulators, VB, Perl...?Scheduling of script operations is also required.Anyone been through this kind of move, who could offer some wisdom?Thanks!DW
View 19 Replies View RelatedError While Converting Oracle Timestamp To Sql Server Timestamp (datetime) - Invalid Date Format
Jun 19, 2007I am populating oracle source in Sql Server Destination. after few rows it fails it displays this error:
[OLE DB Destination [16]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description:
"Invalid date format".
I used this script component using the following code in between the adapters, However after 9,500 rows it failed again giving the same above error:
To convert Oracle timestamp to Sql Server timestamp
If Row.CALCULATEDETADATECUST_IsNull = False Then
If IsDate(DateSerial(Row.CALCULATEDETADATECUST.Year, Row.CALCULATEDETADATECUST.Month, Row.CALCULATEDETADATECUST.Day)) Then
dt = Row.CALCULATEDETADATECUST
Row.CALCULATEDETADATECUSTD = dt
End If
End If
I don't know if my code is right . Please inform, how i can achieve this.
Converting Oracle Calculation To Sql Server 2005 Calculation
Jul 19, 2007Hi I am having to convert some oracle reports to Reporting Services. Where I am having difficulty is with the
calculations.
Oracle
TO_DATE(TO_CHAR(Visit Date+Visit Time/24/60/60,'DD-Mon-YYYY HH24:MISS'),'DD-Mon-YYYY HH24:MISS')
this is a sfar as I have got with the sql version
SQLSERVER2005
= DateAdd("s",Fields!VISIT_DATE.Value,Fields!VISIT_TIME.Value246060 )
visit_date is date datatype visit_time is number datatype. have removed : from MI(here)SS as was showing as smiley.
using:
VS 2005 BI Tools
SQLServer 2005
COnverting Numeric Data Type (Oracle) To Date Data Type Using SSIS
Mar 7, 2007We have some columns in a table where the date is stored as 19980101 (YYYYMMDD). The data type for this column is NUMBER(8) in Oracle.
I need to copy rows from Oracle to SQL Server using SSIS. I used the Data Conversion transformation editor to change it to DT_DATE, but the rows are not being inserted to the destination.
On Error, If I fail the component, then the error is :
There was an error with input column "ORDER_DATE_CONV" (1191) on input "OLE DB Destination Input" (29). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
Regards
RH
Equivalent Tsql For Sql Server 2000 Is Needed [from Sql Server 2005 Only Tsql]
Nov 19, 2007Can anyone please give me the equivalent tsql for sql server 2000 for the following two queries which works fine in sql server 2005
1
-- Full Table Structure
select t.object_id, t.name as 'tablename', c.name as 'columnname', y.name as 'typename', case y.namewhen 'varchar' then convert(varchar, c.max_length)when 'decimal' then convert(varchar, c.precision) + ', ' + convert(varchar, c.scale)else ''end attrib,y.*from sys.tables t, sys.columns c, sys.types ywhere t.object_id = c.object_idand t.name not in ('sysdiagrams')and c.system_type_id = y.system_type_idand c.system_type_id = y.user_type_idorder by t.name, c.column_id
2
-- PK and Index
select t.name as 'tablename', i.name as 'indexname', c.name as 'columnname' , i.is_unique, i.is_primary_key, ic.is_descending_keyfrom sys.indexes i, sys.tables t, sys.index_columns ic, sys.columns cwhere t.object_id = i.object_idand t.object_id = ic.object_idand t.object_id = c.object_idand i.index_id = ic.index_idand c.column_id = ic.column_idand t.name not in ('sysdiagrams')order by t.name, i.index_id, ic.index_column_id
This sql is extracting some sort of the information about the structure of the sql server database[2005]
I need a sql whihc will return the same result for sql server 2000
Converting SQL Date Format To Oracle Date Format
May 28, 2008Hi,
I have a column date in my database which I should send it to Oracle database. The Date format in Oracle is number. I don’t know how should I convert the date to that format?
Example :
SQL FormatOracle Format
02/16/05 105046
Thanks.
Issues Using Parameterised Reports Connecting To Oracle Using ODBC And Microsoft OLE DB Provider For Oracle
Sep 12, 2007I have an issue using parameterised reports connecting to Oracle using "ODBC" and "Microsoft OLE DB Provider for Oracle" using parameteried reports. The following error is generated "ORA-01008 not all variables bound (Microsoft OLE DB Provider for Oracle)" and a similiar one for ODBC. It works fine for simple reports. Do these 2 drivers have issues passing parameters for a remote Oracle query?
Thanks.
Output Column Has A Precision That Is Not Valid (loading From Oracle Using OraOLEDB.Oracle.1)
Apr 2, 2007Hi!
I'm loading from Oracle using the OraOLEDB.Oracle.1 provider since I need unicode support and I get the following error:
TITLE: Microsoft Visual Studio
------------------------------
Error at myTask [DTS.Pipeline]: The "output column "myColumn" (9134)" has a precision that is not valid. The precision must be between 1 and 38.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0204018 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
For most of my queries to Oracle I can cast the columns to get rid of the error (CAST x AS DECIMAL(10) etc), but this does not work for:
1) Union
I have a select like "SELECT NVL(myColumn, 0) .... FROM myTable UNION SELECT 0 AS myColumn, .... FROM DUAL"
Even if I cast the columns in both selects (SELECT CAST(NVL(myColumn, 0) AS DECIMAL(10, 0) .... UNION SELECT CAST(0 AS DECIMAL(10, 0)) AS myColumn, .... FROM DUAL) I still get the error above.
2) SQL command from variable
The select basically looks like this:
"SELECT Column1, Column2, ... FROM myTable WHERE Updated BETWEEN User::LastLoad AND User::CurrentLoad"
Again, even if I cast all columns (like in the union), I still get the same error.
Any help would be greatly appreciated. Thanks!
Setting Up Oracle Linked Server : Need Help : Sql2005 Running On XP Linking In Oracle 10.2
Oct 26, 2006Is there any step by step help sites for setting up SQL 2005 linked (oracle 10) server?
I find MSDN articles but they referance winNT and 2000, I'm not getting very far and I'm not a DBA but need to get this working asap.
Data Access :: Accessing Oracle Tables From Server Via Oracle DBLINK?
May 8, 2015we recently got a scenario that we need to get the data from oracle tables which is installed on third party servers. we have sqlserver installed on ourservers. so they have created a DBLINK in oracle server to our sqlserver and published the DBLINK name.
what are the next steps that i need to follow on my sqlserver in order to access the oracle tables ?
Oracle Connection Fail With Microsoft OLEDB Provider For Oracle MSDAORA.1
Feb 22, 2006Hello,
On my dev server I have working ssis packages that use connections Microsoft OLEDB provider for Oracle MSDAORA.1 and Oracle provider for oledb and OracleClient data provider.
I use one or the other according to my needs.
In anticipation and to prepare for the build of a new production server, I have build a test server from scratch and deployed to it the entire dev.
Almost everything works except Microsoft OLEDB provider for Oracle.
ssis packages on the test machine will return an error
Error at Pull Calendar from One [OLE DB Source [1]]: The AcquireConnection method call to the connection manager "one.oledb" failed with error code 0xC0202009.
Error at Pull Calendar from One [DTS.Pipeline]: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.
[Connection manager "one.oledb"]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle error occurred, but error message could not be retrieved from Oracle.".
I have used the same installers for OS, SQL and Oracle SQL*Net on both dev and test machines. The install and then the restore/deployment on Test went fine.
Does anyone could point me to the right direction to solve this issue?
Thanks,
Philippe
Oracle Publication Error:The Permissions Associated With The Administrator Login For Oracle Publisher 'test1' Are Not Sufficient
Jan 12, 2006Hi,
I am trying to make an oracle publiching from sql server 2005 enterprise final release, i installed the oracle client 10.2 (10g) on the same server where sql server already installed, i made different connection to oracle database instance and it was ok.
from sql server : right click on publication -New oracle publication-Next-Add Oracle Publisher-Add button-Add Oracle Publisher-i entered server insttance test1 and their users and passwords--connect --->
the oracle publisher is displayed in the list of publisher but when press ok i got the following error :
TITLE: Distributor Properties
------------------------------
An error occurred applying the changes to the Distributor.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=Microsoft.SqlServer.Management.UI.DistributorPropertiesErrorSR&EvtID=ErrorApplyingDistributor&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
SQL Server could not enable 'test1' as a Publisher. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The permissions associated with the administrator login for Oracle publisher 'test1' are not sufficient.
Changed database context to 'master'. (Microsoft SQL Server, Error: 21684)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=21684&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Any idea about this error ?
Thanks
Tarek Ghazali
SQL Server MVP.
Inserting Into Oracle Table That Has DATE_HIGH As A Partition And Need Oracle Sequence Used
May 11, 2007Hi Everyone,
I've been searching for a solution for this for a week-ish, so I thought I would post my quesiton directly. Here is my scenario..
Source: MS SQL Server
Destination: Oracle 10g
The destination table has a partition set on a column called "DATE_HIGH". How do I populate this date high column in my package? Currently I just have a source object, and a destination object, but I'm unclear how to populate this field in the destination. I've read one blog that states "use OLE DB Command" - but that isn't enough information for me to implement - Can someone be more specific in these steps? Here is an example of what my newb-ness needs to understand
OLE DB Source (Select * from Table) ---> OLE DB Command (What query goes here?) --> OLE DB Destination.
Second part of my question: There is a second column called "ROW_NUM" and there is an Oracle Sequence provided to me... What objects do I need (Source, Destination, OLE DB Command etc...) and how do I call this sequence to populate on the fly as I'm loading data from my source?
If these are simple questions - my appologies, I am new to the product.
Best Regards,
Steve Collins
Unable To Connect To Oracle Using Microsoft OLEDB Provider For Oracle
Aug 23, 2007Hi everybody,
I have designed a DTS package which will migrate a view from Sqlserver 2000 to Oracle.My package is using Microsoft OLEDB provider for Oracle driver for connecting to oracle.Im able to execute this package on the my system ie on the system where sqlclient is installed(Oracle client is also installed on my machine) .But when im doing it on the server im not able to do it.The Connection to Oracle Fails.
I wanted to know in order to connect to Oracle from the server,is it necessary that Oracle has to be installed on the server?.If yes, is it enough if i install oracle client on the server or Oracle Server version has to be installed on Server.
Please suggest me wht should i do know?
Thanks in advance
Regards
Arvind L
Trouble With: Linked Server To Oracle Using OraOLEDB.ORacle Provider
Jan 11, 2007Hi--
I am running SQL Server 2005 on Win2k3:
Microsoft SQL Server Management Studio 9.00.2047.00
Microsoft Analysis Services Client Tools 2005.090.2047.00
Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447)
Microsoft MSXML 2.6 3.0 4.0 6.0
Microsoft Internet Explorer 6.0.3790.1830
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790
I have the OraOLEDB.Oracle provider installed to the (C:oraclexe) directory.
I am having problems querying from linked oracle server. When i setup oracle as a linked server and purposely enter an incorrect password the query i run tells me i have an incorrect password. So it at least knows that. when i set the correct password and run a query I get this error:
(i replaced the real server name with "someServer".)
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer".
This is how I set up my Linked server:
Provider: "Oracle Provider for OLE DB"
Product Name: SomeServer
Data Source: SomeServer
Provider String: "Provider=OraOLEDB.Oracle;Data Source=SomeServer;User Id=MyLogin;Password=MyPassword"
The query I run is:
Select * from [Someserver].[schema or database]..[tbl_name]
Any help??? What am i missing?
Oracle Parameterized Queries To Update Oracle Table Do Not Work
Apr 23, 2007Oracle and MS drivers do not support parameterized queries, so update table set column=? where primarykey=? does not work for Oracle.
Anyone knows how to update an Oracle table through SSIS?
Thanks!
Wenbiao
How To Migrate Oracle Applications 11.03/Oracle 8.05 To Navision 4.0/ms Sqlserver
Oct 29, 2005need a clue about how to migrate the data from an Oracle applications 11.03 and underlying Oracle 8.05 database to navision 4.0 running sql server 2000
tia
Oracle Error ORA-12154 From An Application Which Never Uses Oracle
Mar 14, 2008My application which ran perfectly well yesterday suddenly stopped working in this morning with following error message. I didn't change anything, at least I believe. Interesting thing is that I don't use any Oracle connection but connects to MS SQL Server 2005 Express version locally. Error messages are different according to the connection string though I don't think it makes difference:
ConnectionString = <add key="Databasehandtool1" value="Data Source=.SQLEXPRESS;Database=handtool;SSPI=true" />ErrorMessage from VS2008 ="Unable to get records. Object reference not set to an instance of an object."
ConnectionString = <add key="Databasehandtool1" value="Data Source=.SQLEXPRESS;Database=handtool;User Id=handtool_DB;Password=mypassword" />ErrorMessage from VS2008 = "Unable to get records. Unable to connect to database. ORA-12154: TNS:could not resolve the connect identifier specified." By the way, the application was generated from IronSpeed 5.1 Enterprise. My development environment is : VS2008, SQL2005 EXPRESS, Windows XP SP2, ASP.NET 2.0
We have actually an Oracle server in the network though. My gut feeling is that something might have changed in my network server during the night but how come it can possibly affect my application running on my local PC. I checked the connection of local SQL Express Server from SQL Server Management Studio and it connected well with UserID=handtoo_DB and showed all tables and stored procedures required for the application. So there is no problem in SQL Server database side.
I would welcome and appreciate any input.
fudata
Using SSIS For ETL From Oracle 10g ODS Into Oracle DW And From There Into SSAS Cubes
May 2, 2007Hi,
This might seems a little 'out there', but has anyone tried doing ETL from an Oracle 10g ODS into an Oracle 10g DW, and from there into SSAS2005 cubes?
Any caveats houghtscomments on doing this?
Thanks,
JGP
TSQL Or SQL CLR?
Aug 9, 2006Hello Friends, I am not sure if this is the right place to post this question but if not please suggest me when it can be posted.
I have been thinking of writing Stored procs in SQL CLR/ changing all of my Stored Procs to SQL CLR.
Is there any thing that I need to keep in mind about the size of the sotred proc (like calculation intensive) before I do that? I mean can even change a TSQL stored proc which is relatively small Stored Proc, that simply said Select * from Customers to SQL CLR? or the SQL CLR does only useful/makes difference with calculation intensive stored procs and big stored procs?
When I talked to our Architects they said every small sized stored proc can be written using SQL CLR and more over forget about the classic TSQL stored procs and get used to write SQL CLR when ever writing any database related stuff.
And also there are so many articles that discussed about the advantages of SQL CLR over the TSQL but I would appreciate if some one could put few bulletted points why do you think SQL CLR is more powerful.
Please advise.Thanks in advance,-L
TSQL Please Help
Jan 8, 2006Hi
I am creating some dynamic sql by passing variouse parametrs to my Stored Procedure. One of my Parameters is string has lots of values seperated by a comma to help build an 'IN' statement.
SET @SQL = 'SELECT * FROM areas'SET @p1 = '10,20'If @p1 IS NOT NULLBEGINSET @sSQL = @sSQL + ' WHERE (Areas IN (''' + Replace(@p1,',',''',''') + '''))'END
The above query runs perfecly well in Query Analyser, however when I put it into my ASP.NET application I get an error of "Error converting data type varchar to numeric."
So I think I need to do some sort of casting or Converting but im not sure how to do it. Or do I need to use a INSTRING?
I did manage to work out a method by using the follwoing
SELECT * FROM Areas WHERE PATINDEX('%,' + CAST(ArType AS VARCHAR) + ',%',',' + @p1 + ',') > 0
But I cant seem to convert the above line into coherent dynamic statement. My feeble attempt is below but I keep getting errors
SET @sql = @sql + ' WHERE PATINDEX(''%,'' + CAST(ArType AS VARCHAR) + '',%'','','' + @p1 + '','') > 0'
IM strugging to understand all the '''. My TSQL is pretty basic, any help would be much appreciated
Many thanks in advance