I am unable to acces Oracle from legacy DTS designer, here is what I have done,
1. I have kept my old DTS packages in SQL2K5 legacy DTS container
2. Now I am unable to access oracle; it gives provider error
3. I am able to connect oracle from SQLPLUS
How can I troubleshoot this problem?
------------------------
I think, therefore I am - Rene Descartes
Hi All,
One of my user was able create DTS package using DTS Wizard, working from his workstation and saved this DTS in Legacy(in Data Transaformation services) on different SQL 2005 EE SP 2(9.0.3042) production server..
At same time he has no access to msdb on this SQL 2005 server(he also not sysadmin for this server).. How this could happen..??
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've just downloaded SQL Everywhere CTP, installed the mobile framework 2.0, and attempted to build a the sample on Steve Lasker's blog http://blogs.msdn.com/stevelasker/archive/2006/06/12/SqlEvCTP.aspx.
However, when I attempt to create a new connection to the Northwind.sdf file in VS 2005 designer, I don't get SQL everywhere (or mobile) in the list of available datasources.
If I choose "other" and ">NET Framework data provider for OLE DB" as the Data provider, I can then choose "microsoft SQL server 2005 Everywhere Edition OLE DB provider for Windows", but when I browse to the database file and click ok, I get "Multiple-Step OLE DB operation generated errors...No work was done". Clicking the "Test connection" button reports success.
I am a beginner with Report Designer (and with Reporting Services). Following the tutorials 'Adding parameters to filter Reports ...' I tried using the @symbol to add a parameter to my query: where code = @code_value...I get the error: ORA -000936 missing expression (msorlib) Is there another syntax that I should use to define a parameter/variable in query for my oracle dataset?
View 4 Replies View RelatedI'm using SQL accounting software now and i have a problem with my designer report. When i using designer report to design my customer statement of account, after i save the new design, i haven't rename it for the new statement report so the name there empty and i exit the designer report. So when i re-open the designer report, suddenly pop out "field value required". What should i do...? How can i re-open the designer report again?
View 1 Replies View Relatedwe 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 ?
Access Connection
create a new Connection Manager by right-clicking in the Connection Managers section of the design area of the screen. Select New OLE DB Connection to bring up the Configure OLE DB Connection Manager dialog box. Click New to open the Connection Manager. In the Provider drop-down list, choose the Microsoft Jet 4.0 OLE DB Provider and click OK.
Browse to the Access database file and connection set up---all good!!!
Dataflow task
Add an OLE DB Source component
Double-click the icon to open the OLE DB Source Editor. Set the OLE DB Connection Manager property to the Connection Manager that I created . Select Table from the Data Access Mode drop-down list.
I cannot see the tables set up as set up as pass-through table types to a Oracle 9i db
Any ideas please help
thanks in advance
Dave
Hello!I have an Oracle linked server connected through MSDAORA. Linked serverqueries work perfectly - the "openquery" ones as well as the4-part-named ones.The problem I have is with embedding the queries within SQL Servertriggers.Trigger:CREATE TRIGGER tgTest ON [dbo].[test]FOR INSERT, UPDATE, DELETEASselect * from openquery(LS, 'select * from ORACLE_TEST')executing "delete from test" in SQL Query Analyzer raises this error:Server: Msg 7391, Level 16, State 1, Procedure tgTest, Line 5The operation could not be performed because the OLE DB provider'MSDAORA' was unable to begin a distributed transaction.OLE DB error trace [OLE/DB Provider 'MSDAORA'ITransactionJoin::JoinTransaction returned 0x8004d01b].I've tried almost every solution I found online, but nothing helped:(This looked promissing: http://tinyurl.com/nk2wd , but it didn't get meany futher.Maybe someone can get me through the troubleshoot mentioned in thatlink:- check if DTC running properlyHow do I check that? If I open the "Support services" in EnterpriseManager and right-click the "Distributed Transaction Coordinatior" Ican stop the service, what indicates the service is running, but isthere anything else I should check? I have 0 items in the right windowpane of the DTC item, is it OK?- registry setting as discussed earlierThe following Registry Keys should be entered:[HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSDTCMTxOCI ]"OracleXaLib"="oraclient8.dll""OracleSqlLib"="orasql8.dll""OracleOciLib"="oci.dll"My entries are:"OracleOciLib"="ociw32.dll""OracleSqlLib"="SQLLib18.dll""OracleXaLib"="xa73.dll"Are they OK?- check if Mtxoci.dll is loadedThere is a Mtxoci.dll in my system32 dir, but how do I tell if it'sloaded? Should I regsvr32 it?- SET XACT_ABORT ON should be use in your SQL statement, for example:SET XACT_ABORT ONBEGIN DISTRIBUTED TRANSELECT statementCOMMIT TRANI've tried that, both in trigger but also surrounding the query thatfires the trigger.Am getting deseperate - please help. Will send candies!TIA
View 1 Replies View RelatedHello,
I was hoping someone had this problem and has a solution... :)
Some background info:
SQL 2005 Enterprise x64
Windows 2003 R2 x64 Enterprise (clustered)
Oracle client 10.2.0.3 (latest as of 2/2/07)
The issue I'm having is I have a link server setup within SQL 2005 connecting to a Oracle 8 database server. The Oracle client I'm using is 10.2.0.3 (latest version from Oracle). From the SQL 2005 server I'm able to query the remote oracle server through the link server I had setup if I query the table directly. If I perform a query to a view on the Oracle server I'm able to see the colum names, but not the data. This only happens when I'm using the Oracle client, if I use the MS client for Oracle it works fine OR if I use SQL Plus.
Now, you're probally asking, why not just use MS's driver... Well, I would If I could, but Microsoft hasn't provided a 64 bit driver for Oracle which I need to create the link server in SQL.
Any help would be appreciated.
Does anyone know if I can have a MS Access project with Oracle backend ?
I know it's a breeze with MS SQL backend !
Thanks in advance !
Ivan
Hi all,
SQL Server 2005 SP2
Windows XP Pro SP2
Oracle 10g Client 10.2
Oracle Data Access Components 10.2 VB6
Oracle Rdb 7.1 (not Oracle RDBMS).
I trying to access a remote Oracle Rdb database via a linked server in SQL Server 2005 management studio but having no luck. The remote database is accessed via an Oracle alias in TNSNAMES.ORA called my_oledb (see below) and I know it works ok becuase I'm able to access the remote database via VB6 (ADODB) using a very simple app whose connection string looks like this...
con.ConnectionString = "Provider=OraOLEDB.Oracle.1;User ID=someusername;
Password=topsecret;Data Source=my_oledb"
I got this example VB appication off the net but have to admit I'm not sure of the difference between
OraOLEDB.Oracle.1 & OraOLEDB.Oracle.
The alias in TNSNAMES looks like this...
my_oledb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (Host = spike) (Port = 1527))
)
(CONNECT_DATA = (SERVICE_NAME = oci_oledb)
)
)
I can also TNSPING this alias without any probems.
My SQL server linked server is setup as follows.
exec sp_addlinkedserver
@server = 'MFP2',
@srvproduct='Oracle',
@provider = 'OraOLEDB.Oracle.1',
@datasrc = 'my_oledb'
exec sp_addlinkedsrvlogin
@rmtsrvname='MFP2',
@useself='false',
@locallogin='sa',
@rmtuser='******',
@rmtpassword='******'
Using both openquery and four part naming (?) I get the following errors
select job_title from mfp2...jobs
OLE DB provider "OraOLEDB.Oracle.1" for linked server "mfp2" returned message "ORA-01017: invalid username/password; logon denied".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle.1" for linked server "mfp2".
There are no schemas in the Oracle database so I've just used three dots between the linked server and the table name.
Again I know this syntax works becuase I have used it sucessfully with ODBC drivers.
select * from openquery(mfp2,'select * from jobs')
OLE DB provider "OraOLEDB.Oracle.1" for linked server "mfp2" returned message "ORA-01017: invalid username/password; logon denied".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle.1" for linked server "mfp2".
The passwrods are correct on the Oracle database as these are the ones used in the VB6 application.
Trying to expand the catalogue also causes management studio to hang.
Anyone spot the obvious mistake?
I did read on the net a while ago that registry entries might be rquired on the PC/server, is this still necessary?
Thanks in advance
Dave
Maybe because I have worked mainly with VB as a front end to SQL Server so I am biased, but I now need definite reasons (I am on a committee for potential future directions) for using VB as opposed to Access for front ending SQL/Oracle, etc. I would also like to use ADO as oppose to DAO. Right now we are using Access with DAO.
Any ideas greatly appreciated.
Is there a way to access a particular row of a column in sql
lets say we have a table
A | B| C|
---------
1 | 2 | 3
3 | 5 | 6
And we want to access the 5 in column B. Is there a way to do that?
Hi all,
I encounter a problem of unable to access Remote Server after i placed my web application into the web server.
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.)
So I googled and tried lot of different ways of connecting.By piped name, IP, trying instance which that server don't even have one :X.I also tried typing in cmd > telnet localhost 1433It appeared that the telnet fail, i tried it on other machine, it works. So is it cause by denied of access through that port?I checked at the sql client network, it show 1433, i check the server too, it show 1433.I also checked if there's any firewall, it appear there is no firewall at all.The sql server i trying to connect is SQL 2000, it run by Windows Server 2003
Any advise or more information you needed to help me solve this problem?Thanks
Hi,
I have a SQL Server 2005 Express database dbase.mdf which I cannot access. It works fine in VWD and the development server using the following connection string: String connectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\dbase.mdf;Integrated Security=True;User Instance=True"But when the site is not on the development server but on IIS, I get a SqlException saying "User does not have permission to perform this action." when I execute the connection.Open() command. I find it extremely frustrating that something which works in the development server fails on the real server. Can anyone help me with this? Do I have to change my connection string, or perhaps set some permissions...I have already given the IUSR_MACHINENAME account and the ASPNET account full access to dbase.mdf. Surely this must be an issue that lots of people deal with... How do other people manage to log to access databases? Any help would be much appreciated. Thanks,P1000
Hi All,
Which some 1 can help me this.
I've developed an application and everything work fine after deploy. But after restarted my PC, i'm unable to login to my application which tie to the xxx.mdf DB and prompt Database error with no description. Until when the i click second time, only i'm able to acess into it. What happened actually?
I'm using sql express as embedded db on my local pc. This is without any remoting access.
I'm attempting to move data from an Oracle table to an Access table using an Oracle stored procedure in DTS. The problem is that you can't pass parameters to an Oracle stored proc when its called in a data pump task. Is there a way to pass global variables into an Oracle stored procedure which retrieves data and moves it to an Access database? Maybe in an Active X task? We are required to use an Oracle stored procedure by our DBA's or else I'd just pass the variables into a SQL string and use it in the data pump task.
View 1 Replies View Relatedhello
we suppose to link the form in MS Access with Oracle
can anyone tell me how to write the code for these buttons
i tried to write as I learned in VB.NET,
But it seems doesn't work :(
http://www3.speedyshare.com/data/191404757/10803567/78224332/db2.jpg
How can I access Oracle stored procedure from MS SQL Server?
View 1 Replies View RelatedWhen I try to create a model in Report Builder based on a connection to Oracle 10g database I get the following error.
ORA-02179: valid options: ISOLATION LEVEL { SERIALIZABLE | READ COMMITTED }
Is this a bug in the CTP version or am I missing something?
I can't run my web application on remote pc which is connected in flan with my pc.It works on my local pc but it is not working on remote pc. i m geeting an error like as blowAn error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) i have used sql server 2000 for my database but i my getting error for sql server 2005 which i have never used in my web application.Here is the Exception detailsException Details: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)i have used coonection string as followsData Source=neptune;Initial Catalog=azim;User ID=saso can anyone give me soluion of this problem
View 1 Replies View RelatedI logged on to my SQL Server Express this morning and received the following error:
Cannot connect to MICHELLEWSQLEXPRESS.
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)
What is interesting is that its on a local computer. I followed one recommendation to enable the pipes and tcp but that didn't work either. How do I change the default settings to allow remote connections (on a local computer) when I can't log into the server?
Hi,i'm at wit's end here. My problem:I'm trying to do an insert into a table with ExecuteNonQuery and keep getting the error:"input string is not in correct format"I am trying to insert a string into sql server 2000 type varchar(length=50) and it won't work so instead I just decided to get a count on the number of records existing and I got the same error (see above) when I tried this. The code for this last part:<CODE>
Dim MyCmd As New System.Data.SqlClient.SqlCommand.' other code here.......Dim TempSQL As String = "SELECT COUNT(*) FROM tblUsers"
MyCmd.CommandType = CommandType.Text
MyCmd.CommandText = TempSQLDim a As Object = MyCmd.ExecuteScalar()</CODE>I am really stuck on this and cant find the answer anywhere online.Cheers,Joe
Hi,
I'm unsure what happened, but I'm using SQL Server 05 Developer Edition. I've been working with a local database where I tried to add a clustered index on a huge table (about 50M records). Well, I had to stop the query since it was maxing out my work computer. Since then, I've been unable to open the database or run queries against it.
When I open SQL Server Management Studio the database says In Recovery. Once it's done, when I try to expand tables, I eventually get this error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=1222&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
If I run a Select * from INFORMATION_SCHEMA.TABLES query, it will go on for a while
If I try to access the properties of the Database (right clicking), I get this error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Cannot show requested dialog.
------------------------------
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=1222&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
I'm unsure where to go from this point forward.
I (actually my DBA did it) have created an SSIS package in MS SQL Server 2005. It can be found under Integration Services under:
Stored Packages
MSDB
VisBridges
ExportTime
Using the Execute Package Utility, it runs perfectly.
I used the article at http://msdn2.microsoft.com/en-us/library/ms403355.aspx to build an application to run it.
My code includes the following (written in VB.Net):
a Web Service Function called LaunchPackage:
Public Function LaunchPackage(ByVal sourceType As String, _
ByVal sourceLocation As String, _
ByVal packageName As String) As Integer ' DTSExecResult
Dim packagePath As String
Dim myPackage As New Package()
Dim integrationServices As New Application
' combine path and file name
packagePath = Path.Combine(sourceLocation, packageName)
Select Case sourceType
Case "file"
' package is stored as a file.
' add extension if not present.
If String.IsNullOrEmpty(Path.GetExtension(packageName)) Then
packagePath = String.Concat(packagePath, ".dtsx")
End If
If File.Exists(packagePath) Then
myPackage = integrationServices.LoadPackage(packagePath, Nothing)
Else
Throw New ApplicationException("Invalid file location: " & packagePath)
End If
Case "sql"
' Package stored in MSDB.
' Combine logical path and package name.
If integrationServices.ExistsOnSqlServer(packagePath, <servername>, <UserID>, <password>) Then
myPackage = integrationServices.LoadFromSqlServer(packageName, <servername>, <UserID>, <password>, Nothing)
Else
Throw New ApplicationException("Invalid package name or location: " & packagePath)
End If
Case "dts"
' package is managed by SSIS Package Store.
' Default logical paths are File System and MSDB.
If integrationServices.FolderExistsOnDtsServer(packagePath, <servername>) Then
myPackage = integrationServices.LoadFromDtsServer(packagePath, <servername>, Nothing)
Else
Throw New ApplicationException("Invalid package name or location: " & packagePath)
End If
Case Else
Throw New ApplicationException("Invalid sourceType argument: valid values are 'file', 'sql', and 'dts'.")
End Select
Return myPackage.Execute()
End Function
My TestPage includes the following:
Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
Dim launchPackageService As New LaunchSSISPackageService
Dim packageResult As Integer
Dim sourceType As String = "dts"
'Dim sourceLocation As String = "M:mssqlssiconfig" ' for "file"
'Dim sourceLocation As String = "MSDBVisBridges" ' for "sql"
Dim sourceLocation As String = "VisBridges" ' for "sql & dts"
'Dim sourceLocation As String = "MSDB"
If rdoCreateTimeTrans.Checked Then
Try
packageResult = launchPackageService.LaunchPackage(sourceType, sourceLocation, "ExportTime")
Catch ex As Exception
Response.Write("The following exception occurred: " & ex.Message)
End Try
End If
End Sub
I've tried both "sql" and "dts" with all the various permutations that are listed (and commented out) above. I either get an error that says:
The top level folder "VisBridges" is not found. ("dts" "VisBridges") -- or --
Invalid package name or location: MSDBVisBridgesExportTime ("dts" "MSDBVisBridges") -- or --
Cannot find folder "MSDBVisBridgesExportTime" ("sql" "MSDBVisBridges") -- or --
The specified package could not be loaded from the SQL Server database ("sql" "VisBridges")
Any assistance would be greatly appreciated.
Many thanks,
Michell
I'm unable to do a RDA.Pull() when I have the sqlcesa30.dll running under HTTPS. What are the requirements to allow me to pull with the dll under HTTPS?
what are the impacts running under https:?
Hi Guys,
After installing SQL Server 2005 Reporting Services, I got the following errors:
1. When I tried to open http://localhost/reports/ , I got the following error message
The report server is not responding. Verify that the report server is running and can be accessed from this computer.
2. When I tried to open http://localhost/reportserver/ , I got the following error message
The report server has encountered a configuration error. See the report server log files for more information. (rsServerConfigurationError)
Access to the path 'C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesReportServerRSReportServer.config' is denied.
Is there any clue for this technical problem?
Immediate response is appreciated.
--Amde
Hi
I'm able to get into Report Server but when I try to access the Report Manager with the same logon I get the following error:
"An error occurred while attempting to get the ReportServer Url."
Can anyone shine any light on this please?
Thanks
I have two drives in SQL Server Box. C and D drives. I am using below script to move mdf and log files from C: drive to D: Drive. However, I keep getting error : Directory lookup for the file "D:PackagesTestVGR.mdf" failed with the operating system error 5(Access is denied.).
USE MASTER;
GO
-- Take database in single user mode -- if you are facing errors
-- This may terminate your active transactions for database
ALTER DATABASE TestVGR
[code]...
Hi,
I have SQL Server 2005 installed and functioning (it does everything I've asked it to do locally). I also have the Express and Standard Editions installed on a computer running WinXPPro SP2 with all the WinUpdates applied. I also have VS2005 installed on this computer. I've done some development work on this computer, using VS2005 to create a SQLDB, connect to it, add data, etc. This is all okay.
The problem is I cannot connect to the SQL Server from the client computer. I installed the client tools on this XP Pro box along with all the WinUpdates. Here's what I can do...
I can ping the SQL Server by name (the name gets resolved).
I can copy files between the two computers using Explorer.
I enabled "Allow remote connections to this server" in the Server Properties / Connections page.
I did not change the default port of 1433.
The TCP/IP protocal is enabled in the Configuration Manager.
The Surface Area has been configured to allow local and remote connections using TCP/IP and named pipes.
SQLBrowser surface is running as is the MSSQLSERVER service.
So what's going on? I am new to SQL Server to it may be something very simple / obvious.
Thanks for your help,
Scott
I am transferring data from oracle and getting below error message.
I using 4 data flow tasks with in a single control flow and all the 4 tasks quueries same table but populates data in to different sql tables based on the where contidion
[OLE DB Source 1 [853]] Error: 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: "ORA-01652: unable to extend temp segment by 64 in tablespace TEMP ".
Hi All
My manager told me to link SQL Server database tables to access so that he can access the tables in MSAccess to do his SQL queries.
I am thinking of linking server but I am not sure about that.
Is someone can tell me what to do and explain me how.
Thanks in advance
Sincerely.
David