I want to convert oracle "SELECT LEVEL R FROM CONNECT BY LEVEL <=10" my bottom requirement is get 1 to 10 as a dynamic table inside a query.
Ex: Select id, name, R from names N, (1,2,3,4,5,6,7,8,9,10) R WHERE id < 1000
If any one know something regarding this please reply me.
Thx,
Buddhika
Hi,I'm attempting to connect to an Oracle database using SQL Server 2005Express (OS is Windows XP Professional) and having absolutely no luck.Here is the information SQL Plusgives me about the database when I log in -Connected to:Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.6.0 - ProductionAs you can see I have no problem connecting with the Oracle specifictools (SQL PLUS and PL/SQL Developer).I've tried creating a linked server with both the Microsoft providedOLE DB provider and the Oracle provider. With the Microsoft Provider Iget the following error when attempting a query in the query editwindow.OLE DB provider "MSDAORA" for linked server "ORACLE" returned message"Oracle client and networking components were not found. Thesecomponents are supplied by Oracle Corporation and are part of theOracle Version 7.3.3 or later client software installation.Provider is unable to function until these components are installed.".Msg 7303, Level 16, State 1, Line 2Cannot initialize the data source object of OLE DB provider "MSDAORA"for linked server "ORACLE".When I try connecting withe Oracle provider I get the following -OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLETEST2"returned message "ORA-01017: invalid username/password; logon denied".Msg 7303, Level 16, State 1, Line 2Cannot initialize the data source object of OLE DB provider"OraOLEDB.Oracle" for linked server "ORACLETEST2".I've set the password up as a local to remote login mapping using thesecurity page of the Linked Server property dialog. The username /password are definitely correct -- I used them to log in using SQL PLUSand PL/SQL Developer. I'm using an Oracle 9 client.PLEASE HELP !!!!!Thanks,Signed Frustrated
View 8 Replies View Related
created an Oracle datasource (tried both ole db & oracle client) in Visual Studio 2005 report project - works fine connecting and preview of report within ide , deployed to Reporting Services web server, received the following error when attempting to run the report on web server:
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'oracle'. (rsErrorOpeningConnection)
System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.
web server has oracle client 9i client installed and connecting to db ok through sql plus (this server also is loaded with VS2005 and the SqlServer2005 db)
any thoughts or direction greatly appreciated
We have recently migrated from SQL 2000 (32bit) to SQL 2005 on Win2003 (both are 64bit). And we have Legacy DTS packages on SQL 2005, some packages are pulling records from Oracle 10g (32bit server). When I execute the DTS package I am getting following error.
Error = -2147467259 (80004005) Error string: Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation. Provider is unable to function until these components are installed. Error source: Microsoft OLE DB Provider for Oracle
Did I need to install/update any MS OLE DB driver for Orace.
Some blogs & forums suggesting to install Oracle 10g Client. If so Which version of Oracle client(32bit/64bit) I have to install?.
Thanks.
Dhans
Hi, Is there a tool that anyone here is aware of which converts SQL, stored procedures and such stuff from Oracle compatibility to SQL Server compatibility? I have some Oracle based SQL that I wud like to convert into T-sql instead of re writing the whole T-SQL..
Thanks,
I am getteing
need help
Query analyzer error Unable to connect server local Msg17, level 16,state 1
ODBC SQL server driver [DBNETLIB]SQL server does not exist
I'm trying to convert a simple SQL Statement over to Oracle...
Select top 25 * from Customers
Is there a way to just select the top 25 in Oracle??
Thanks
I have this update working in an Oracle database and I need to make it run on SQL Server.
UPDATE table3 C
SET C.column1 = 'A'
WHERE (C.column2, C.column3) IN (SELECT B.column2, B.column3 FROM table1 A, table2 B WHERE A.column2 = B.column2 AND A.column3 = B.column3 AND B.column4 = 1)
Help please.
Thanx!
Hi,
Can any one change this oracle proc. to SQL Server procedure.
Any help will be appreciated.
PROCEDURE CALC_PERC (DB_ID IN NUMBER, LAT_TYPE IN CHAR) IS
Tot_work_all number(12,2);
Bid_tot number(12,2);
Ewo number(12,2);
Overruns number(12,2);
Underruns number(12,2);
Contr_tot_all number(12,2);
sContractType ae_contract.contr_type%type;
BEGIN
select sum(nvl(tamt_ret_item,0) + nvl(tamt_paid_item,0))
into Tot_work_all
from valid_item
Where db_contract = db_id;
Select sum(Contq * Contr_Price) into Bid_tot
From Valid_item
Where nvl(New_Item,'N') <> 'Y'
and db_contract = db_id;
Select sum(Qtd * Contr_price) into Ewo
From Valid_item
Where nvl(New_item,'N') = 'Y'
and db_contract = db_id;
Select Sum((Qtd-Nvl(Projq,0))*Contr_Price) into Overruns
From Valid_item
Where Qtd > Nvl(Projq,0)
and db_contract = db_id
and nvl(New_Item,'N') = 'N';
IF LAT_type <> 'R' THEN
Select Sum((Nvl(Projq,0)-Contq) * Contr_Price) into Underruns
From Valid_item
Where Nvl(Projq,0) < Contq
and db_contract = db_id
and nvl(New_Item,'N') = 'N';
ELSE
Select Sum((Nvl(Qtd,0)-Contq) * Contr_Price) into Underruns
From Valid_item
Where Nvl(Qtd,0) < Contq
and db_contract = db_id
and nvl(New_Item,0) = 'N';
end if;
Contr_tot_all:= NVL(Bid_tot,0) +NVL(ewo,0) +NVL(overruns,0)
+NVL(underruns,0);
IF Contr_tot_all = 0 THEN
Select Contr_type into sContractType from ae_contract where db_contract = db_id;
IF sContractType = 'A' OR sContractType = 'T' THEN
--If the divisor is zero here, it's not an error.
update ae_contract set perc_compu = 0 where db_contract = db_id;
ELSE
--If the divisor is zero here, it would be an error
update ae_contract set perc_compu = 100 * tot_work_all/contr_tot_all where db_contract = db_id;
END IF;
Else
--Here we have a real number to calculate, so go ahead and do your stuff!
update ae_contract set perc_compu = 100 * tot_work_all/contr_tot_all where db_contract = db_id;
END IF;
END;
Hi 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
i had worked on oracle 8i and i am planning to work on sql server 2000,i am requested by a company to help in converting there pl/sql code of oracle 8.0 to something equivalent which works on sql server 7.0 as they want to have similar code on both..i had not worked on sql server 7.0 ,but as pl/sql code works only on oracle stuff..so could kindly anyone guide me in this as to whether there is any product which coverts pl/code (the existing pl/code runs into thousands of line) automatically..i will be very grateful if anyone can enlighten me with such a product(software) or script.. along with its information and site address..any resources and any guidance as to how to go about about this conversion will be very invaluable..hope to hear soon from you guys...early response....will be appreciated..
with regards,
vijay.
sql server 7.0 on winnt
pl/sql code on oracle 8.0
Hello,
Does anyone know how to connect Oracle 10g DB to Microsoft SQL Server? If so, any tips or help would be greatly appreciated!
Thanks!
Hello ,
I'm facing a complicated problem and I don't think that the solution will be an easy one.
I have an SQL statement in Oracle which I need to translate it in Sql Server 2005.
select lpad(' ',5*(orderid)) || to_char(descr) as menui
from <table _name>
where MENU_ITEM not in ('test1','test2','test3') and item_parent not in ('test4,'test5,'test6')
start with <item_parent='item_parent' >
connect by prior <menu_item = item_parent and menu_name='ADR_m_adr_frame'; > ?(condition)
Somewhere I have read that SQL server does not support Hierarchical Sql statements. Is this true ? How am I going to do that ?
Any help will be appreciated.
Thank you
In SQL Server I've created a linked server to an Oracle database. I am trying to insert (within the context of an sql server table trigger) an SQL Server datetime to an Oracle column with similar precision. Oracle timestamps are not compatible with sql server datetimes and I don't know how to convert the data (or if I should use a different type of column to store the data in Oracle). I have full control over the structure of the Oracle table so I can use a different type if timestamp is not best, but I need the destination column to have at least the same precision as the sql server datetime value. What is the easiest way to do this?
View 22 Replies View RelatedHi Everyone,
Just like the title says:
Can you connect to an Oracle DB using MS Sql Server Management Studio Express?
If so, can anyone please give me an example?
Thanks heaps!
How to connect to SQL server database from Oracle 9ias Application Server (9.0.3) & Test & Verify the Connection. Is it posible.
View 1 Replies View RelatedGreetings,
I want to connet to Oracle databse from SQL server 2000 since I am more comfortable with SQL 2000 client package. The oracle server is 8i or above.
I am running a SQL Server 2000 on a PC with Windows XP OS.
Please help what I need to do and configuration information.
Thank you all in advance.
BPG
I'm having a problem connecting with a Java application but I CAN connect using my .Net application - the user name and password are the same for both (using the same database on SQL Server Express 2005).
The error I get is: "com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database "CORNERS" requested by the login. The login failed." An interesing note - I get the same message if the database is not running.
SQL Server Express 2005 is installed in mixed mode.
Here is my connection string in the .Net appplication: <add key="connectString" value="Server=(local);UID=sa;PWD=myPasswd;Database=CORNERS" />.
These are my values in my Java app web.xml -
<init-param>
<param-name>DBDriver</param-name>
<param-value>com.microsoft.sqlserver.jdbc.SQLServerDriver</param-value>
</init-param>
<init-param>
<param-name>DBURL</param-name> <param-value>jdbc:sqlserver://localhostsqlexpress:1055;databaseName=CORNERS</param-value>
</init-param>
<init-param>
<param-name>DBUser</param-name>
<param-value>sa</param-value>
</init-param>
<init-param>
<param-name>DBPwd</param-name>
<param-value>myPasswd</param-value>
</init-param>.
And yes, the port is 1055 - I checked to find it.
I am using Microsoft SQL Server 2005 JDBC Driver 1.0 (sqljdbc_1.0.809.102).
Does anyone have any idea what is wrong so that the login fails in the Java application but works in the .Net application?
hi
i want to know if row level trigger works with sql 2005.
because in 2000.. only statement level trigger was used.
Thks in advance.
hi
actually i have a temporay table wich has four columns
say col_1,col_2,col_3,col_4.
so firstly i wud bulk insert from a text file wich mite contain thousands of rows.
then from this temp table col_1 and col_2 shd go to master_a.wich has an identiy column as primary key.
say id,col_1, col_2.
i will have another table detail_b where in there is a foreign key to the table master_a for id.
so it will have f_id, col_3,col_4.
so i am riting a trigger on master_a. so whenver row is inserted in master_a. coresponding id and col 3 col4 shd be inserted into detail_b.
For this in oracle we have row level trigger. where in for each insertion in master a .. correspoding trigger will fire but in sql 2000..
but i wud like to implemtn row level trigger..
can u hlep me out..
So I have to build dynamic T-SQL because of a date parameter that will be provided. The Date Parameter will be provided in SSRS in normal MM/DD/CCYY format. So how do I then convert that date to my Oracle format
NUMERIC(8,0) CCYYMMDD?
I tried this...
SET@SQLQuery=@SQLQuery+'ANDMEMBER_SPAN.YMDEFF<='''''+CAST(@AsOfDateASVARCHAR)+''''''+@NewLineChar;
SET@SQLQuery=@SQLQuery+'ANDMEMBER_SPAN.YMDEFF>='''''+CAST(@AsOfDateASVARCHAR)+''''''+@NewLineChar;
but that put it in the format of...
AND
MEMBER_SPAN.YMDEFF<=''2015-04-01''
AND
MEMBER_SPAN.YMDEFF>=''2015-04-01''
Which is close...I think I just need to lose the "-"
Hello all,
I will try to supply all of the information available to me to help generate some answers to the question I have. I am doing some benchmark testing of an application of how it deals with various database queries, for example oracle 10g, DB2 and MSSQL 2005.
I have a very simple database called "student" that has various tables, with various columns, all as mentioned very simplistic. Each table has a few hundred rows of data, the most being one table with 2000 rows. The same database (structure and dataset), is replicated across all 3 DB's.
In order to do the testing, I have been recording the same queries (select * from table_a, select * from table_b etc etc) for each database at an interface which monitors traffic between the client and db server, the problem starts here; The size of the tcpdumps are vastly greater for MSSQL vs oracle or db2. Some examples:
100 query or transaction dump:
mssql=3.7mb
oracl10g=133kb
50,000 query or transaction dump:
mssql=1.8gb
oracl10g=0.6gb
db2=0.6gb
as mentioned each database's tables and data are identical to my knowledge and all queries are the same. The bloated dumps are making mssql performance numbers look bad.
So my question is: What could be the reason for MSSQLs client/server queries to contain so much information, has anyone else come across this? Is there any setting or something I could try to minimize it?
Thank you for your time.
I've got a table adapter that connects using an oracle data connector. In the adapter, I'm using native oracle SQL such as:
select TO_DATE(SUBSTR(TO_CHAR(weird_oracle_field),0,12),'YYYYMMDDHH24MI') as dt_added from oracle_data_table
There's also a CASE statement in there with some other data transformations.
Anyway, I want to take the results of that Oracle query and put the dataset into a SQL Server Compact Edition database - within an application that I'm creating in Visual Studio 2005.
For whatever reason, I can't seem to do anything like that in 'bulk' and there aren't any data migration tools that work with anything other than "full" SQL Server versions. My client doesn't support SQL Server, but I can deploy my app with SQL CE. I need a 'local' copy of the database (for several reasons) and just can't seem to figure out how to make this work.
I'm really going nuts. I feel like I'm soooo close when I see the data I want in the table adapter - but I can't seem to actually *move* the data over!!
Can anyone help?
thanks,
Jon
hi,
How to get 2 level tree node in the sql server 2005. We have table with data.On load we populate the tree in Asp.net.
Regards,
Vinayak Panchal
We've devoted a resource to this today, but I have to believe it's something easy that we're overlooking. The scneario is that we have a production Web application that until last weekend had a SQL 2000 back end. This weekend we installed a new instance of SQL 2005 and everything works (we tested in a sandbox environment, but someone must not have load tested enough) and never saw these exceptions. So, after the upgrade we now receive 100's of thexe SQL excptions per day:A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)Does anyone know what we've overlooked that's causing this issue?Thanks for any help!
View 2 Replies View RelatedHi,
I create a dll assembly with the strong name in VB.Net environment.
Created assembly is registered also.
SQL Server 2005 configuraton level is set at "80"
I want to call that assembly from stored procedure with the database config level at "80"
But when i execute the stored proecure i get the following error
Error Source: "ODSOLE Extended Procedure"
Description: "Invalid Class String"
My Code in VB.Net is given below:
Imports System
Imports System.Reflection
Imports System.Globalization
Imports System.IO
Imports System.data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports Microsoft.VisualBasic
Imports System.Diagnostics
Imports GreatDataAccess
Namespace Test
Public Class clsTest
Public Shared Sub GenTest()
''''............ some code is here
End Sub
End Class
End Namespace
Stored Procedure
CREATE PROCEDURE [dbo].[PPGenerateFile]
AS
BEGIN
Declare @retVal INT
Declare @comHandler INT
declare @errorSource nvarchar(500)
declare @errorDescription nvarchar(500)
declare @retString nvarchar(100)
-- Intialize the COM component
EXEC @retVal = sp_OACreate Test.clsTest, @comHandler OUTPUT
IF(@retVal <> 0)
BEGIN
--Trap errors if any
EXEC sp_OAGetErrorInfo @comHandler,@errorSource OUTPUT, @errorDescription OUTPUT
SELECT [error source] = @errorsource, [Description] = @errordescription
Return
END
-- Call a method into the component
EXEC @retVal = sp_OAMethod @comHandler,'GenTest()',@retString
IF (@retVal <>0 )
BEGIN
EXEC sp_OAGetErrorInfo @comHandler,@errorSource OUTPUT, @errorDescription OUTPUT
SELECT [error source] = @errorsource, [Description] = @errordescription
Return
END
select @retString
END
Please Help me to solve this problem.
Hello All,
I am in the process of upgrading my current database in SQL Server 2000 to SQL Server 2005. I have finished my analysis using the upgrade advisor and have found a lot of SPs having upgrade related issues. From what I estimate I think it will take around 3-4 weeks to resolve all of those issues. I wanted a quicker way to upgrade my database since we have very little time for the final release. The main reason for upgrade to 2005 was use of new features like Database Mirroring and/or peer to peer replication alongwith the Partitioning features and the new T-SQL enhancements.
The question I wanted to ask was does the compatibility level 80 of SQL Server 2005 supports these new features.
From what I have read on the KB article at http://support.microsoft.com/kb/822400 is that mirroring is supported in any compatibility level, but there is no specific mention on the peer to peer thing.
Also when I personally tried using the New Partitioning Features of 2005 on the database having compatibility 80, it worked fine. Also other features like SQLCLR and TRY...CATCH works fine.
One thing I observed was that new T-SQL statements like PIVOT/UNPIVOT is not supported
It will be nice if someone can give out the entire list of features that will be supported in the compatibility Level 80 and also the list of the ones that are not supported.
Thanks in Advance,
Mitesh
We want to use sql 2005 failover clustering feature, so that upgrade sql 2000 is necessary. But some of the stored procedures built in 2000 are not working directly in 2005, set the compatibility level to 80 could make them work. In this case, can somebody here tell me if down grade the database level will affect failover clustering running properly? e.g. will data still be synchronized properly? Thanks in advance.
View 3 Replies View RelatedHo do I convert Oracle 8 database into SQL server 7.0 database ? Is it possible ? Please let me know at skbhaduri@rediffmail.com
View 1 Replies View RelatedHi,
I have database with unlimited growth in Data with 2MB growth and Log File with 1MB growth. The continuous data is inserted with min 20KB per minute. We struct with following error and Log file size is too large (85GB) than Data file(20GB). Now server is not allowing to take backup as diskspace is not available.
Msg 9002, Level 17, State 2, Procedure DumpData, Line 10
The transaction log for database '%DB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
status at SYS.DATABASES:
log_reuse_wait log_reuse_wait_desc
-------------- ------------------------------------------------------------
2 LOG_BACKUP
Please help me to resolved the issue and let me know hints to shrink LOG file. Data inserted are using ADO applications that uses some insert/select stored procedures that does not have begin transaction-Commit Transaction block as appliction issues the Commit over connection object.
Regards,
Pavan
I installed SQL Server 2005 Developer Edition. When i create a new database (using the "New Database" dialog) i cannot set the new database's compatibility level to "SQL Server 2005(90)" because this option is not in the "dropdown list". the only items shown are: "SQL Server 7.0(70)" and "SQL Server 2000(80)". I set the owner to "sa". How do i get "SQL Server 2005(90)" in my "compatibility level" drop down list? Is this an installation option that i missed? Thanks in advance for any assistance!
-chris
Hi,
I am using MS Access 2003 SP2 to maintain some data tables. I use SSIS to transfer them to SQL Server 2005, Enterprise Edition.
When I run the SSIS package from within Visual Studio 2005, the package runs without error.
When I try to run the same SSIS package by double-clicking on it in my File System (which invokes the Execute Package Utility, Version: 1.0) none of the tables get copied. Instead all I receive is a message for each table,
Error: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "Data Conversion 1" (49).
The only data conversion I perform is double-byte characters to single-byte characters.
Bob Bojanic, MSFT, made a few suggestions about this in another thread -- but I have created this new thread to help focus on this specific issue. In particular, he asked if we have installed the complete SSIS support for SQL Server 2005, Enterprise Edition, and my network support and database support staff assure me that such complete SSIS support was installed.
Are others having this problem?
Dan
(I just took a look at some of the transformations in the Data Conversion task, and many of them are using an Output Alias identical to the Input Column name. Might that be causing the problem? I will try changing the Output Alias for some tables and see if they then transfer correctly. The "identical name" Output Alias values were created by the Migration Wizard for a DTS 2000 package.)
hi,
I have some SQL CE database .sdf file on my handheld, and I was trying to connect to that file via SQL Server 2005 Management Studio, and it does not work. I am wondering if there is any good tool that I can use on desktop to connect to sqlce .sdf database file on my handheld?
Thanks.