In Sql server if i have a varchar field of length 12, if i enter 12 chinese characters, it fails. In Oracle there is a setting nls_length_semantics.If you put char in nls_length_semantics , it takes 12 characters irrespective of english(one byte) or chinese(doublr byte).
Is there any similiar setting for Sql server?
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.
Does anyone know where I can get some good examples of how to set up a linked
server to an Oracle data source in SQL 2000? I can't seem to get a link
set up to my Oracle database. In particular I am wondering how the communication works between OLEDB to the Oracle Instance and how to trouble shoot issues (for example, would is the Oracle Listener what OLEDB is talking to?) Thanks!!
I have a requirement to set up an Oracle linked server, Ive never done this before and in fact never touched Oracle so I dont really know what I am doing.
View 4 Replies View RelatedI have Oracle 64-bit drivers installed. I can run SQLPLUS and it works. I can create linked servers to oracle and that works also. I am having this error when I try to add oracle as a publisher. OraOLEDB.Oracle is already set to 'Allow InProces'.
Msg 21626, Level 16, State 1, Procedure sp_MSrepl_testconnection, Line 110
Unable to connect to Oracle database server 'DTMART' using the Oracle OLEDB provider OraOLEDB.Oracle. For addition information, see SQL Server Error 21626 in Troubleshooting Oracle Publishers in SQL Server Books Online.
Msg 21747, Level 16, State 1, Procedure sp_MSrepl_adddistpublisher, Line 463
Cannot establish a connection to the Oracle Publisher 'DTMART'. Verify connection information and ensure that you can connect to the Publisher through a tool like SQL*PLUS.
Thanks..
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?
In my ASP.NET app, I'm executing a stored procedure via a SQLCommand the searches a customer database. I believe the default timeout is 90 seconds. I'm curious of what happens to the SQL Server Stored Procedure after timing out from the ASP.NET application. Does it timeout at the same time or do you have to set up a value in SQL Server?
View 1 Replies View RelatedI have a Sybase Adaptive Server Enterprise server which I need to set up as a linked server in SQL Server 2005. The Sybase server is version 12.5.2, and the Sybase ODBC driver version is 4.20.00.67. I have already installed the Sybase client software on the server.
I also created a SystemDSN on the SQL Server to connect to the Sybase server. I tested the connection and it was able to connect.
I ran the following code to create the linked server:
<code>
EXEC master.dbo.sp_addlinkedserver @server = N'LinkedServerName', @srvproduct=N'Sybase', @provider=N'MSDASQL', @datasrc=N'Sybase System DSN', @provstr=N'"Provider=Sybase.ASEOLEDBProvider;Server Name=servername,5000;Initial Catalog=databasename;User Id=username;Password=password"'
</code>
I then ran sp_tables_ex to make sure I could view the tables in the Sybase database. Here is the error message I get:
<code>
OLE DB provider "MSDASQL" for linked server "LinkedServerName" returned message "[DataDirect][ODBC Sybase Wire Protocol driver]Error parsing connect string at offset 13. ".
Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "LinkedServerName".
</code>
Any ideas what is happening here?
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.
Hi 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
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
Oracle 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
Hi
I have a setup where I need to replicate the database which is actually subscribing from another database. The current setup is all in SQL Server 2000. I need to now setup a Distrbutor on a SQL server 2005 and publish the database using this distributor to another server on SQL server 2000.
Has anybody done this before. If yes what will I need to check. Can you please let me know :-
1) SQL Server 2000 which SP should be installed to support this enviroment.
2) SQL Server 2005 which SP should be installed to support this environment.
3) Any thing that I need to look out for.
Thanks for any inputs on this.
Regards
I am having a nightmare of a time with my Sql Server 2005 Express. My computer crashed on Friday the 13th - of course. I saved all my files onto another drive and wiped out C: I brought everything back in but I started a new file for my Visual Basic projects. After asking questions I found this was a major no-no So I brought my original folder back in. This solved some of my problems I was having with Visual Studio and Visual Web, but I am continuing to have problems with Sql Server. I will explain these two problem they may be related, I don't know. When I run my Visual Website on a server I get this message:
An attempt to attach an auto-named database for file(then my file name)failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
And my second problem is when I attempt to run Sql Server Management Studio Express I get this error message:
There is no editor available for (then my file name) .mdf
Make sure the application for file type(.mdf)is installed.
need 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
Hello, Iam new at this SQL stuff and want to setup an SQL Server at work and need to know what do i need to use this software. I have a EVAL version of 6.5 and was told you need MSACCES or Visual Basic. Is there anything else that i need to use this? Thanks!!
View 14 Replies View RelatedWhat is the best way to set up a sql server db that will be accessed via web(asp/ado) and windows (vb/ado) environemnts. I am concerned with security and authentication.
Thanks!
I am working with 3 sql 7.0 servers (Servers A, B, C) and am trying to create a trace via profiler to run against server B.
Every trace that I create keeps pointing to server A even though I have server B defined in the properties of the trace - (when I edit the trace properties it REALLY does show server B). I have server B defined in the screen where you 'Select the SQL Server to run the trace on'. I even used the wizard to run one of the canned traces and it still points to server A. Every trace I set up runs against server A. Can anyone think of what the heck I'm doin' wrong? Is is a configuration setting somewhere?
Does anyone know the best way to set up NT security. It seems a little confusing as to how to set up NT groups and assign permissions (where do the roles come into it?).
Any help would be much appreciated
thanks
Paul
Is there anyway to set up SQLMail without using Windows Messaging and using a MAPI Client?
Thanks,
Scott Hughes
Hi,
There has been a lot of discussion about moving single databases or devices in same server. Can someone suggest the most simplest method to set up a identical server with all the logins, permissions and password etc?
Building a new server with sp_help_revdatabase and dumps/loads is relatively easy but moving the master is confusing to me. I should run the new server and databases for a little testing period (with diffrent server name, of course) and then go live by dropping the old and by adding the new. I remember someone said that this would be as easy as copying master.dat to new machine (when servers are identical), any experience or other suggestions?
TIA,
Al
:eek:
I am having trouble setting my SQL databases up as analysis services databases in SQL Server '05. Does anyone know how to help me???? I have lots of cubes to build, but I can't get the datasources there.
Thanks much for any advice.
mbunch@bighammer.com
How to change SQL Server setting so that it allows remote control?
View 4 Replies View RelatedHow do I find out what port my server is listening on? I'm trying to connect to my database locally to install Vignette Portal. And no matter what I try, nothing works. I think it is listening on port 1433. But there is no way to be sure. Should something come up if I hit localhost:1433 in my browser? Because, when I do that, nothing comes up.
Please help.
Set up linked server for windows user.please explain step by step
View 9 Replies View RelatedI am installing my application thru Installshield Professional 5. Howto know whether MS SQL Server is installed or not on the machine onwhich i am installing my application. What is the registry key thruwhich we can find this.
View 1 Replies View RelatedHi all,
I am soon to be setting up a new server that is to have SQL Server 2005 on it.
The server has 2 x Quad Core Xeon CPUs, 16Gb of RAM and 2x73Gb HDDs in RAID1 configuration - the whole running under Windows Server 2003 x64. (The data will be on an external RAID disk array, so the 73Gb is just for the O/S and utils.)
I was reading that Windows will, by default, create a page file of (RAM size) + 1Mb, though my old-school education says use a page file of 1.5 times the RAM size. Then again, 16Gb is a whole pile of RAM and do I really want a page file of 24Gb when I only have 73Gb of HD space..? Is it worth partitioning the HDD into two or just leave it whole?
I'm looking for guidance from knowledgeable types (that's you chaps) who could offer advice on how best to implement SQL Server on a system of this spec.
I've also thought about assigning different processor affinitie to Analysis Services and the Database Engine - does anyone have any strong thoughts either way on the benefits of this?
Many thanks,
Robo
Is it secure setting ftp on the server so that i can use this ftp task in ssis.
I want to get some files located on the sever to my machine.
right now it does not work.
Is it safe setting ftp on the server???????
My 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