I have mssql2000 running on a Windows 2003 server and now have a requirement to run an Oracle 10g database as well. Is it possible to run both mssql2000 and oracle 10g on the same server without running into any conflicts or will the two programs cause errors with each other?
Anyone have any experience with this? Oracle says it's technically possible but the tech had never seen it done.
Scott
Hello,
Does anybody know, how can I execute stored procedures that are on MSSQL Server 2000 from Oracle9i SP ? Can you show me an examle ?
Thanks for any help.
Okay heres the problem I'm facing. I have GoDaddy as my hosting company. They only support MSSQL2000 but I just recently bought MSSQL2005 Developer Edition. And I was wondering what is the main difference between them two. I didn't find anything on Google. And the only thing is I can't find a better host. Cause I have 50GB of space and 500GB of bandwidth. And I don't want to lose that for just MSSQL2005 support cause my host doesn't/ Have that. So im stuck like chuck. Is there really any difference?
View 1 Replies View RelatedI have a problem in connecting to my hosting database using .net file.
I am able connect to database with my ASP page.
Any sugession is greately appretiated!
Here is my code:
".net" page:
<%@ page language='vb' debug='true' %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<script runat="server">
Sub Page_Load(Src As Object, E As EventArgs)
Dim MyConnection As SqlConnection
Dim da AS sqlDataAdaptor
Dim ds As DataSet
MyConnection = New SqlConnection("DSN=***;UID=***;PWD=***")
da = new sqlDataAdaptor("select * from city", MyConnection)
Try
MyConnection.open()
Response.write "Connection Success " & MyConnection.Database & "<br>"
catch sx AS sqlException
Response.write "Connection successful: <br>"
End Try
ds=new Dataset()
da.Fill(ds, "City")
With DataGrid1
.DataSource = ds.Tables("city").DefaultView
.DataBind()
End With
MyConnection.Close()
End Sub
</script>
<body>
<p>
<ASP:dataGrid id='DataGrid1' runat='server'/>
<p>
</body>
</html>
---------------------------------------------
"ASP" page:
Set DB = Server.CreateObject("ADODB.Connection")
DB.Open ConnectionString
SET RSCheckeMail = DB.Execute ("select * from city")
While not RSCheckeMail.eof
Response.write "City: " & RSCheckeMail("Name") & "<br>"
RSCheckeMail.Movenext
Wend
Thanks for your help in advance.
I am in a situation where I need to get a copy of test database that ison production server running MSSQL 2000 Standard to my local machinerunning MSSQL 2000 personel. I tried to use the copy wizard where itappears I get connected to the source server OK but when I try toindicate the destination server which is my local machine I get errorspoping up about cannot connect to (local) etc.I am NOT a DBA just a programmer trying to get a local test environmentup to be more productive.Lsumnler
View 2 Replies View RelatedI am having MSSQLServer2000 on Win2k and Win2k3 server.
i have to connect these servers from a application using JDBC driver and AD Authentication.
I have programmed all the things needed for AD authentication in my application(changed url , using integratedSecurity=true), and it is working fine when my application connects to MSSQLServer2k5 on WinXP(which is added to same domain of my application).
But when i try to connect MSSQL2000 server on Win2000 server it failes and gives error as follows.
Apr 18, 2008 12:39:51 PM com.microsoft.sqlserver.jdbc.AuthenticationJNI SNISecGenClientContextINFO: Failed to get the SPNcom.microsoft.sqlserver.jdbc.SQLServerException: Connection reset at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(Unknown Source) at com.microsoft.sqlserver.jdbc.TDSChannel.read(Unknown Source) at com.microsoft.sqlserver.jdbc.TDSReader.readPacket(Unknown Source) at com.microsoft.sqlserver.jdbc.TDSReader.readPacket(Unknown Source) at com.microsoft.sqlserver.jdbc.TDSReader.readResponse(Unknown Source) at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(Unknown Source) at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$000(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(Unknown Source) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.loginWithoutFailover(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source) at ChangePassword.process(ChangePassword.java:33) at ChangePassword.main(ChangePassword.java:14)
I am using following code for connection --
String url = "jdbcqlserver://172.16.1.243\dbaudit;portNumber=1034;integratedSecurity=true";
Class dbDriverClass = Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Driver driver = (Driver) dbDriverClass.newInstance();
Properties props = new Properties();
conn=driver.connect(url, props);
Is 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.
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 ?
Hi--
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?
Hi
May I ask how to export MSSQL2000 DB to *.sql with all existing data?
As I built the DB on local machine, the remote machine only provide SQL Server Web Admin which has Query Analyzer only. I can export a *.sql file, but there are no data.
Thanks for help
Hi!
I am a newbie using MSQL so please patience. I compact my database with
DATABASE SHRINK(DB,10)
BACKUP LOG data WITH TRUNCATE
DATABASE SHRINK(DB,10)
When I see the properties of my DB I see something strange
1- My log always has 1 mb of space
2- Database properties show me this information:
Size: 21639.23MB
Space Available: 782.23
Backup
Last database backup: 9/28/2007 1:44am
Last transaction log backup 6/9/2007 12:47 pm
I am so afraid because this says that I just have 782.23MB but my disk has 30GB yet.
I need to start to worry me ?
What can I do to solve this issue with my db?
TIA
:(
Hey Guys and Gals,
here's an interesting one for you.
Ok, so this 3rd party application we have exhibits some interesting behavior; when you amend any table through it's interface it drops and re-adds the object; meaning all permissions to the object are also removed. (Just take my word for it when I say that the table changes have to be done through this app).
Now, I've built a little application that hooks into this using an SQL Server authenticated user account with SELECT only permission on particular tables. Currently the app uses sprocs to access data. However, when changes were made to the schema last week; the application, obviously, received permission errors and ground to a nice halt.
I know that in 2005 we have the lovely EXECUTE AS statement; but I'm running a 65compaability database here and don't have that functionality.
Any ideas on how I can sort this mess out?
Hope I explained this well enough, let me know if you need any more info
Cheers,
George
Hi,
I have a bunch of data from tables on MS SQL 2000 and i want to transfer this data to my new database running on MS SQL 2005. How do i perform bcp on this? thanks :D
Hi Group,I developed a intranet site using MSSQL7/win2000 some time ago.The target environment used MSSQL2000/8.We were (almost painlessly) able to import the db-scheme and data from 7to 8. (Bravo MSSQL)Now I need to do some upgrading on the application and I would like tohave a copy of the database from MSSQL2000/8 to MSSQL7.Is that also possible?Or should I download Microsoft SQL Server Express and use that insteadof my MSSQL7? Is it better?I hope I can get the relationsheet too (that one with Foreign Keysmapped in a nice graphical way).Any advise highly appreciated.I am good with Postgresql, but my MSSQL skills leave a lot to bedesired. :-/For an outsider like me the many versions and OS's are quite confusing.Do I need special commands on MSSQL2000/8 to create a MSSQL7 compatibleexport?Thanks in advance!Regards,Erwin Moller
View 3 Replies View RelatedHi, i am having problem to install sql2000 server, the steps i did are:1. Local machine -> next2. Create a new instance of SQL server -> next3. type name and company -> next4. accept -> next5. Server and client tools -> next6. Default -> next7. Typical -> next8. use the same account ... and Use the Local system account -> next9. Mixed mode , passward -> nextwhatever options i choose, the installation program of sql will exitafter step9. just don't know what the problem is. please help thanks:)
View 2 Replies View RelatedHi.. Never used MSSQL 2000 db. I have this website that uses mssql2000. I want to export it and convert db in mysql. Please help me understand how i can do that. I have all login info..
View 1 Replies View RelatedHi,
I'm executing a stored procedure in my local LAN which executes another one in a loop and I update a Table. The number of loops is about 6300.
This operation takes about 25 seconds in my local LAN.
Then I try to execute though in a VPN which has an upload speed of 256 kbps. I open query analyser connect to the remote server which is must faster than mine and I just write exec mystoredprocname in order to execute the procedure. The performance is very very slow.
In 7 minutes 180 loops are completed out or 6300.
I really cannot understand this. What is the reason of such slow perfomance?? My ADSL model displays no activity when the procedure is executed. I just use the PRINT method in MSSQL in order to display the progress of the operation. I tried to comment it out but with no difference.
I also use SET NOCOUNT ON in order not to display the update results.
Can someone explain me the cause for this? Are there some tricks in order to improve the performance when a slow connection is used like a ADSL with a static IP? It seems that something wrong is happening here.
Best Regards,
Manolis Perrakis
Hello!Does anybody know whether mssql2000 and emc mirrorvew _certified_ forjoint work?(Mirrorview is a fc-based remote mirroring solution)I mean is it supported from the MS point of view to put mssqldatafiles on emc mirrorview volumes?For example Oracle corp. has "Oracle Compatible Remote MirroringTechnologies" certification.But what about MS?
View 1 Replies View Related(MSSQL2000) I have read the transaction/locking sections in theMS-help, online and several books. What I want to understand is thetransaction behavior in single statements [not a BEGIN TRANSACTIONStatement1, Statement2... COMMIT].If I have a Table: "Letters" with 1 column "L" and the table presentlyhas rows{A,B,C,D}Case 1 (Insert):First start transaction T1 "SELECT * FROM Letters"Next start transaction T2 [separate connection] "INSERT INTO LettersVALUES( 'Z' )"Is it possible that T2 ends before T1 and the select returns{A,B,C,D,Z}Is it possible that T1 ends before T2 and the select returns{A,B,C,D} [No 'Z']Is this a race condition and I need to use a TABLOCK or TABLOCKX;and are TABLOCK/TABLOCKX only hints? I mean does the use of TABLOCKguarantee a lock on the table? Do I need to use 'SET TRANSACTIONISOLATION LEVEL SERIALIZABLE' and if I use 'TRANSACTION ISOLATIONLEVEL' is there a means of telling the system which tables I willtouch so that I can avoid a deadlock [upfront tell the system whattables I need to lock so there is not a race later]?Case 2 (Delete basically the same):First start transaction T1 "SELECT * FROM Letters"Next start transaction T2 "DELETE FROM Letters L = 'D'"Is it possible that T2 ends before T1 and the select returns {A,B,C}[No 'D']Is it possible that T1 ends before T2 and the select returns{A,B,C,D}Case 3 (Update basically the same):First start transaction T1 "SELECT * FROM Letters"Next start transaction T2 "UPDATE Letters SET L = 'Z'"Is it possible that T2 ends before T1 and the select returns{A,B,Z,Z} [Some letters were seen to become 'Z']Is it possible that T1 ends before T2 and the select returns{A,B,C,D}
View 3 Replies View RelatedHi experts,
Here I got some problems with my application. (ASP & English Version SQL Server 2000)
As we are using English MSSQL Server 2000, we got some new functions and we have to facilitate support of Chinese characters in the DB. I have set the collation for those Chinese fields already and those queries or Stored Procs for Chinese are working fine, ONLY if I execute them in Enterprise Manager. Chinese characters can be displayed in the relevant tables.
However here comes the big problem and I got really frustrated. As we will provide user interface in ASP pages, we 'll let users to insert the information which will be sent to the DB. If there's Chinese characters in the query string, the Chinese characters added in the DB would be garbled.
e.g. EXECUTE proc_TestChinese 'XYZ', 'test123' (assume XYZ be those Chinese words)
I am wondering if there's any way I can solve this problem. Should I add special handling for these Chinese words? I have set the ASP pages in UTF-8 or Big5 encoding but it doesn't help. Hope you experts can show me the way out of the mess. Thanks in advance!
Manfred
Hi,
I have a datadabase with 1 datafile from 60Gb. Is it a good thing(preformance) to split up this datafile in smaller datafiles from 6Gb each?
I don't have separete diskslices so a can't spread my datafiles on my disks but i only need to know if a datafile from 60Gb sin't too big for MSSQL2000.
Thanks,
Ewoud,
I was using MSSQL7 for a long period.
I upsized to MSSQL7 from Access some years ago. Without any particular reason when writing code in stored procedures, when I wanred to select some records having a bit column to true I used the syntax bitcolumn=-1 (and not bitcolumn=1). This behavior was used in Access. Everything worked fine. Then I moved to MSSQL2000 and by restoring the MSSQL7 database I had no problem.
However, in order to use some features of MSSQL2000 I had to run the sp
sp_dbcmptlevel <database>, 80
After that the condition bitcolumn=-1 didn't work.
Can anyone verify this behavior, since I have to make dozens of changes in my stored procedures and triggers???
Regards,
Manolis Perrakis
I can not get a multiple row resultset to display or even get sent to my client application running on coldfusion.
What is the problem with the code?
How do i display and return a resultset to my coldfusion client application?
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE dbo.nTransaction
(@pAccountNo varchar(30), @N int, @ntransCursor CURSOR VARYING OUTPUT, @nValue varchar(4000) OUTPUT)
AS
set @N = 0
SET ROWCOUNT @N
SET @ntransCursor = CURSOR FOR
-- FORWARD_ONLY STATIC
SELECT CONVERT(varchar,Eh.EntryID), Eh.EntryReference,E.AccountNo, E.Narrative, E.Amount, Eh.EntryDate
FROM entryheaders as Eh cross join entrys as E
WHERE Eh.EntrySerial = E.EntrySerial and AccountNo = @pAccountNo
ORDER BY Eh.EntryID DESC
SET ROWCOUNT 0
OPEN @ntransCursor
WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM @ntransCursor into @nValue
END
CLOSE @ntransCursor
DEALLOCATE @ntransCursor;
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Pls i need urgent help!!!
SELECT * FROM TA a WHERE a.rx=264886 and
AN= (select max(AN) FROM TA where rx=a.rx)
I have a table TA with 8+ million rows and there is clustered PK on (rx, AN) columns. The count on rx=264886 is 6000+ rows. This query takes about 1 to 2 minutes to fetch data. Can anyone suggest how to improve performance and fetch data faster?
Thanks, Vinnie
Hi AllI need to aggregate a query to produce the following:Workplace AvgM100 4.7M120 3.45Which would be a normal aggregate:SELECT Workplace, Avg(VALUE)FROM PRODGROUP BY WorkplaceHowever I need the average to only be based on the most recent 20results from each of the Workplace groups.I've never had to do something like this before so can't think of anyway to only take off the most recent 20 for each group (ordered byDate). It doesn't really matter if there were 25 spread across 2 daysI would just cut the list at 20 VALUEs as there is no time componentinvloved.Is there any way to do a sub-query that uses select top 20 ... foreach group that could then be aggregated?I would prefer to do it through a select statement rather than havingto use a stored procedure using and variables, etc which I can do. Thetable is not huge but is growing rapidly so I'm concerned thatanything using dyamic SQL or similar would be become painfully as thenumber of groups grows to 5,000 or more.If anyone has any ideas they would be greatly appreciated.Thanks in advance,Bevan
View 2 Replies View RelatedHELP!
I'm new to SQL Server and I'm sure this is a simple problem.
But I can't seem to solve it.
Here's the problem:
My website & MS SQL 2000 Server are being hosted by Networksolutions.
Thru the Client Enterprise Manager I am Unable to create a New Table.
This is the error message I get:
" [MS Design Tools]-ODBC error: [Microsoft] [ODBC SQL SERVER DRIVER] [SQL SERVER] SELECT permission denied on object 'sysobjects', database 'namedb', owner'db' " :confused:
Is there a way to transfer ntext data from one table to another?I tried thisUPDATE [projects]SET [description] = (SELECT [description_ntext] FROM [table] WHERE[id]=1)WHERE [id_project] = 1;and thisDECLARE @DESCRIPTION ntextSET @DESCRIPTION = (SELECT [bids].[bid_conditions] FROM [bids],[projects] WHERE [bid_accepted_id] = [bids].[id_bid] AND [id_project] =@ID_PROJECT);UPDATE [projects]SET [description] = @DESCRIPTIONWHERE [id_project] = 1;none of those work in MSSQL2K,error reported is "The text, ntext, and image data types are invalidfor local variables."
View 2 Replies View RelatedI cound't install the service pack msde2ksp3en to my MSSQL2000 Personel edition. An error box has been occured while i install service pack ( "the instance name specified is invalid" ).
How can I solve this problem and install the service pack. I attempted to install service pack 4 but I had taken that error page again. Help me please. Thanx...
Hi All,
As mentione I am trying to replicate(transactional) a table from MSSQL 2000 Enterprise Evaluation to Sybase 15 Developer Edition. I downloaded an ODBC Data (a third party -OpenLink Lite )
The connectivity to heterogenous subscriber is successfully established. Linked Server too is established.
However the distributor agent fails on setting up the replication with the following error:
<< The column descr in table MSrepl7 does not allow null values.(233) >>
The target Sybase table has all the nullable coulmns ie I can insert a complete NULL record.
Any help in this regrd would be appreicated.
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.
Hi!
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!
Hello,
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
Hi,
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.