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.
I 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?
I 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.
Converting 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
Hello,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;
Hello,I am new to sql*server, but have used Oracle for years. Can anyonerecommend a good online guide for converting all the Oracle SQL I knowto TSQL?tia,Mike
SELECT 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
I 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'
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 .
Hi all, I have a table with the list of tables I need to drop. So basically before droping those tables I need to disable the FK and PK constraints. So I want to spool out the out of this script. SELECT 'ALTER TABLE ' + QUOTENAME( c.TABLE_NAME ) + ' NOCHECK CONSTRAINT ' + QUOTENAME( c.CONSTRAINT_NAME ) AS ALTER_SCRIPT FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS c WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
Is there a way to store the output of this script in a .sql file so that I could execute it. Any thoughts will help! Thank you!
Hi, 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
My client will be receiving a .dbf file which needs to be uploadedinto a sql server database table (as an append) every week. They areNOT computer savvy and I would like to automate this process ratherthan go into enterprise manager and run data transformation.Is there any way to write a batch file or a set of command lines whichwill do this?Thanks.Monica
does Sql server has command equalent to Rollback to in Oracle. That means when you do some update operation and i want to revertback the original record. If so what is that command.
I want to use the OLEDB command to call a oracle function, but i havnt found any materials about how to do that, my oracle function is as below:
CREATE OR REPLACE function GET_ZONEID_FROM_SYFZ(ycz varchar2,xc varchar2,strat_id varchar2) return varchar2 IS zone_id_result varchar2(10) ; begin PKG_DM_DQ.GET_ZONEID_FROM_SYFZ(ycz,xc,strat_id,zone_id_result); return zone_id_result; end;
In OLEDB command transformation component, i fill the sql command with "select GET_ZONEID_FROM_SYFZ(?,?,?) from dual", but i dont have it worked.
The error message is :provider can not derive parameter information and setparameterinfo has not been called.
1. (calls an oracle package) - call lpaarchive.pibrdg.setlastbridgeruntime(sysdate);
2. (selects rows) - select timestamp ,
pitag ,
rtlmp from LPAARCHIVE.I_PIBRDG_BUS5MINRTLMPS
where timestamp <= (sysdate + 0.002777778)
order by TIMESTAMP ASC; I need to execute these 2 statements together. I tried using Execute SQL task to call the package and then an OLEDB source that calls a variable with the select statement. But it does not retrieve any rows. It seems like the result of calling the package is used by the select statement to give the final rows. Could anyone please help me resolve this issue.
How to launch Oracle SQL Developer through "Run" window in windows as we can launch SQL Server Management studio through "Run" command line using command line "sqlwb -S localhost -d MyDB -U sa -P
I 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.
We 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.".
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
i am using visual web developer 2005 and SQL 2005 with VB as the code behindi am using INSERT command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString() test.InsertCommandType = SqlDataSourceCommandType.Text test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@roll,@name, @age, @email) " test.InsertParameters.Add("roll", TextBox1.Text) test.InsertParameters.Add("name", TextBox2.Text) test.InsertParameters.Add("age", TextBox3.Text) test.InsertParameters.Add("email", TextBox4.Text) test.Insert() i am using UPDATE command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString() test.UpdateCommandType = SqlDataSourceCommandType.Text test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll 123 " test.Update()but i have to use the SELECT command like this which is completely different from INSERT and UPDATE commands Dim tblData As New Data.DataTable() Dim conn As New Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True") Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn) Dim da As New Data.SqlClient.SqlDataAdapter(Command) da.Fill(tblData) conn.Close() TextBox4.Text = tblData.Rows(1).Item("name").ToString() TextBox5.Text = tblData.Rows(1).Item("age").ToString() TextBox6.Text = tblData.Rows(1).Item("email").ToString() for INSERT and UPDATE commands defining the command,commandtype and connectionstring is samebut for the SELECT command it is completely different. why ?can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?if its possible how to do ?please help me
i am using a OLE DB Source in my dataflow component and want to select rows from the source based on the Name I enter during execution time. I have created two variables,
enterName - String packageLevel (will store the name I enter)
myVar - String packageLevel. (to store the query)
I am assigning this query to the myVar variable, "Select * from db.Users where (UsrName = " + @[User::enterName] + " )"
Now in the OLE Db source, I have selected as Sql Command from Variable, and I am getting the variable, enterName,. I select that and when I click on OK am getting this error.
Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E0C. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object.".
Can Someone guide me whr am going wrong?
myVar variable, i have set the ExecuteAsExpression Property to true too.
I 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.
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.
we 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 ?
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?
