Oracle StoredProc/Job Running At SQL Server?
Feb 5, 2003Hi,
Is it possible Oracle Stored Proc or Jobs able to schedule in sql job?.
Thanks,
Ravi
ADVERTISEMENT
Setting Up Oracle Linked Server : Need Help : Sql2005 Running On XP Linking In Oracle 10.2
Oct 26, 2006Is 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.
Running SQL And Oracle On The Same Server
May 10, 2006I know that this is primarily a SQL server group, but I am hopingsomeone has experience in running Oracle alongside SQL servers. Wecurrently only have SQL server installations in our organization.However one of our department is considering buying a third-partyapplication that runs on Oracle. We are interested to find out anyproblems that people might have experienced in such an environment. Iam doing some research on this subject to find out what I can, but Ithought I would look to the experts on this.Any input would be greatly appreciated.ThanksKarthika
View 2 Replies View RelatedHave You Ever Installed MSSQL On A Server With Oracle Already Running ?
Apr 6, 2007am i going to cause problems if i install MSSQL2005 on a server with Oracle already installed and running ?
thank you
How To Set User Who Can't Modify Any StoredProc And Data In Tables But Can Read Data In Tables And Any StoredProc ?
Jul 13, 2007Please give me advise ครับ
Using StoredProc With Multiple Result(or Datatable) In Server Reports
Sep 17, 2007Hi guys,
I've been doing some LOCAL reports on my current application until recently there's has been a case that I really need to do SERVER reports.
Usually when I design my local reports, I create a XSD file, so I usually have one dataset with multiple tables in it. I just pass the dataset to report with a single procedure call that returns multiple result sets or data table.
From what I understood server reports are binded to database objects only, like stored procedures. Now I used the same stored procedure that I used in my local report to my server report. But the thing is only the first result set in the stored procedure is recognized. Are there anyway that I can bind the server report to a single stored procedure that return multiple result sets?
Thanks.
Oracle Error When Running Niku Clarity With MS JDBC
Jul 19, 2006Hi All
I get a very strange error when running the JDBC in WAS6.0
This is running the Clarity 7.5.3 Application
Failed to verify database: Io exception:
java.sql.SQLException: Io exception: The Network Adapter could not establish the connection
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
Any ideas
Running A SSIS Package As A Job Error - With A Connection To An Oracle Database
Oct 10, 2007Hi,
I have installed MS SQL Server 2005 and Service Pack 2 on a new Windows 32-bit environment. I also installed the 32-bit 10g client (the Administrator option). The tnsnames.ora file is configured properly and I can tnsping to the Oracle database without any issues. I have created a package in Visual Studio and it runs successfully when I execute the job manually. I saved the package to the MS SQL Server and when I log into Management Studio to create a job for this package, I receive the following error:
Message
Executed as user: xxxSYSTEM. ...rsion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:42:37 AM Error: 2007-10-10 10:42:37.28 Code: 0xC0202009 Source: x Connection manager "x.x" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "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.". End Error Error: 2007-10-10 10:42:37.28 Code: 0xC020801C Source: Data Flow Task OLE DB Source [18] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method ca... The package execution fa... The step failed.
I have created the package and logged into the server with the same ID to set up the job. And I have set the ProtectionLevel property to "Don't Save Sensitive Data" since I know this has been an issue in the past when I tried automating the job. I've four posts regarding this error message on the MSDN forums that aren't helpful at this moment - I'm hoping that someone will have found a solution since those posts. Any suggestions are welcomed.
Thanks for your help!
Sue
Transact SQL :: Running Oracle Developer Application Using Command Line String
Jun 11, 2011How to launch Oracle SQL Developer through "Run"Â window in windows as we can launch SQL Server Management studio through "Run" command line using command line "sqlwb -S localhost -d MyDB -U sa -P
View 4 Replies View RelatedData Access :: Accessing Oracle Tables From Server Via Oracle DBLINK?
May 8, 2015we 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 ?
Trouble With: Linked Server To Oracle Using OraOLEDB.ORacle Provider
Jan 11, 2007Hi--
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?
Storedproc (again Sorry)
Feb 4, 2007I get a execption when i run my code i dont know how to debug sql statements so ya could any one give me adive heres the code public static int CreateMember(string username, string aspApplicationName)
{
int returnvalue = 0;
DateTime dateCreated = DateTime.Now;
// All users are added to users role upon registration.
Roles.AddUserToRole(username, "Users");
String connectionString = ConfigurationManager.ConnectionStrings["SqlConn"].ConnectionString;
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand command = null;
try
{
conn.Open();
command = new SqlCommand("InsertMember", conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@AspNetUsername", username));
command.Parameters.Add(new SqlParameter("@AspNetApplicationName", aspApplicationName));
command.Parameters.Add(new SqlParameter("@DateCreated", dateCreated));
SqlParameter sqlParam = command.Parameters.Add("@Id", SqlDbType.Int);
sqlParam.Direction = ParameterDirection.ReturnValue;
command.ExecuteNonQuery();
returnvalue = (int)command.Parameters["@id"].Value;
conn.Close();
}
catch (Exception ex)
{
}
finally
{
if (command != null)
command.Dispose();
if (conn != null)
conn.Dispose();
}
return returnvalue;
} i get a exception at command.ExecuteNonQuery(); and if i dont do int returnvalue = 0; it says i cant use it cause it hasnt be initialized or something like that ALTER PROCEDURE [dbo].[InsertMember] @AspNetUsername nvarchar(256), @AspNetApplicationName nvarchar(256), @DateCreated smalldatetime = getdateASDECLARE @Id int;SET NOCOUNT ON;INSERTINTO [Members] ([AspNetUsername], [AspNetApplicationName],[DateCreated]) VALUES (@AspNetUsername, @AspNetApplicationName,@DateCreated);SET @Id = @@IDENTITYSELECT @Id AS [Id] theres my stored proc any ideas?
.net StoredProc
Nov 28, 2005Hi,
View 7 Replies View RelatedReturn XML From SQL StoredProc
Apr 21, 2005Hallo
I have a normal "Select * from Table" SP that have OUTPUT parameters as well.
Is it possible to obtain the result into a XML format and ALSO obtain the OUTPUT parameters in .Net1.1
Thank you
Tool For Generating StoredProc
Jul 9, 2001Hi,
I'm using SQL Server 2000 as our back end. I'm finding it bit difficult to write StoredProcs manually to be called from my front end. Is there any good Stored Proc generator tool available?
Thanks,
Harish
Execute Xp_cmdshell And Other SA Storedproc
Mar 8, 2004Hi all,
I have to execute stored procedures containing
xp_cmdshell and certain system storedprocedures in msdb and master
with a user who is not SA.
(i.e iam able to execute stored procedures when i log as sa,
but any other user cannot run them)
Pls tell how to do this, it is quite urgent.
Storedproc For Wildcard Search
Mar 24, 2008Hi someone please help me.
i have a serach page which have 4 textboxes.
passing this textboxes as parameters to storedproc iam searching the value.
filling atleast one textbox should fetch the value.
i have stored proc for searching it using normal column values but i want it do using wildcard search also.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[search1]
(@val1 varchar(225),
@val2 varchar(50),
@val3 varchar(50),
@val4 varchar(50))
AS
BEGIN
DECLARE @MyTable table (CNo varchar(255))
INSERT @MyTable
Select CNo From customer where
((@val1 IS NULL) or (CNo = @val1)) AND
((@val2 IS NULL) or(LastName = @val2)) AND
((@val3 IS NULL) or(FirstName = @val3)) AND
((@val4 IS NULL) or(PhoneNumber = @val4))
--Now do your two selects
SELECT c.*
FROM customer c
INNER JOIN @MyTable T ON c.CNo = T.CNo
Select r.*
From refunds r
INNER JOIN @MyTable t ON r.CNo = t.CNo
END
I WANT THE SEARCH TO BE DONE FOR WILD CARD CHARACTERS ALSO.
if the user enters lastname s*
using same storedproc can i insert wildcard search.
how can i do that please some one help me.
thanks
renu
Problem With Sql2005 Query/storedproc
Oct 2, 2006I am working on the login portion of my app and am using my own setup for the moment so that I can learn more about how things work. I have 1 user setup in the db and am using a stored procedure to do the checking for me, here is the stored procedure code:ALTER PROCEDURE dbo.MemberLogin(@MemberName nchar(20),@MemberPassword nchar(15),@BoolLogin bit OUTPUT)ASselect MemberPassword from members where membername = @MemberName and memberpassword = @MemberPassword if @@Rowcount = 0beginselect BoolLogin = 0returnendselect BoolLogin=1/* SET NOCOUNT ON */ RETURNWhen I run my app, I continue to get login failed but no error messages. Can anybody help? Here is my vb code:Dim MemberName As StringDim MemberPassword As StringDim BoolLogin As BooleanDim DBConnection As New Data.SqlClient.SqlConnection(MyCONNECTIONSTRING)Dim SelectMembers As New Data.SqlClient.SqlCommand("MemberLogin", DBConnection)SelectMembers.CommandType = Data.CommandType.StoredProcedureMemberName = txtLogin.TextMemberPassword = txtPassword.TextDim SelectMembersParameter As Data.SqlClient.SqlParameter = SelectMembers.CreateParameter'NameSelectMembersParameter.ParameterName = "@MemberName"SelectMembersParameter.Value = MemberNameSelectMembers.Parameters.Add(SelectMembersParameter)'PasswordDim SelectPasswordParameter As Data.SqlClient.SqlParameter = SelectMembers.CreateParameterSelectPasswordParameter.ParameterName = "@MemberPassword"SelectPasswordParameter.Value = MemberPasswordSelectMembers.Parameters.Add(SelectPasswordParameter)Dim SelectReturnParameter As Data.SqlClient.SqlParameter = SelectMembers.CreateParameterSelectReturnParameter.ParameterName = "@BoolLogin"SelectReturnParameter.Value = BoolLoginSelectReturnParameter.Direction = Data.ParameterDirection.OutputSelectMembers.Parameters.Add(SelectReturnParameter)If BoolLogin = False ThenMsgBox("Login Failed")ElseIf BoolLogin = True ThenMsgBox("Login Successful")End IfEnd SubThank you!!!
View 3 Replies View RelatedStoredProc Insert Into Composite Key Table
Dec 23, 2005I have three tables that are important here, a 'Plant' table a 'Spindle' table and a 'PlantSpindle' table. The 'PlantSpindle' is comprised of a PlantID and a SpindleID acting as the Primary Key for the table with no other fields.
I have an aspx page that captures the appropriate data to create an entry in the Spindle table. Depending on the user, I will know which plantID they are associated with via a querystring. In my storedproc I insert the data from the webform into the Spindle table but get stuck when I try to also insert the record into the PlantSpindle table with the PlantID I have retrieved via the querystring and the SpindleID of the spindle record the user just created. Basically, I am having trouble retrieving that SpindleID.
Here is what I have in my storedProc (truncated for brevity).
CREATE PROCEDURE [dbo].[InsertSpindle]
@plantID int,
@spindleID int,
@plantHWG varchar(50),
@spindleNumber varchar(50),
@spindleDateInstalled varchar(50),
@spindleDateRemoved varchar(50),
@spindleDurationMonths float(8),
@spindleBearingDesignNumber int,
@spindleArbor varchar(50),
@spindleFrontSealDesign varchar(50),
@spindleFrontBearing varchar(50),
@spindleRearBearing varchar(50),
@spindleRearSealDesign varchar(50),
@spindleNotes varchar(160)
AS
SET NOCOUNT ON
INSERT INTO Spindle
(plantHWG, spindleNumber, spindleDateInstalled, spindleDateRemoved, spindleDurationMonths,
spindleBearingDesignNumber, spindleArbor, spindleFrontSealDesign, spindleFrontBearing,
spindleRearBearing, spindleRearSealDesign, spindleNotes)
VALUES
(@plantHWG, @spindleNumber, @spindleDateInstalled, @spindleDateRemoved, @spindleDurationMonths,
@spindleBearingDesignNumber, @spindleArbor, @spindleFrontSealDesign, @spindleFrontBearing,
@spindleRearBearing, @spindleRearSealDesign, @spindleNotes)
SET @spindleID = (SELECT @@Identity
FROM Spindle)
INSERT INTO PlantSpindle
(plantID, SpindleID)
VALUES
(@plantID, @SpindleID)
I have guessed at a few different solutions but still come up with Procedure 'InsertSpindle' expects parameter '@spindleID', which was not supplied when I execute the procedure.
Any help would be appreciated! thanks!
Aaargh! Storedproc Vs SQL In Gridview Update
Jan 26, 2006When I attempt to update using a stored procedure I get the error 'Incorrect syntax near sp_upd_Track_1'. The stored procedure looks like the following when modified in SQLServer:
ALTER PROCEDURE [dbo].[sp_upd_CDTrack_1]
(@CDTrackName nvarchar(50),
@CDArtistKey smallint,
@CDTitleKey smallint,
@CDTrackKey smallint)
AS
BEGIN
SET NOCOUNT ON;
UPDATE [Demo1].[dbo].[CDTrack]
SET [CDTrack].[CDTrackName] = @CDTrackName
WHERE [CDTrack].[CDArtistKey] = @CDArtistKey
AND [CDTrack].[CDTitleKey] = @CDTitleKey
AND [CDTrack].[CDTrackKey] = @CDTrackKey
END
But when I use the following SQL coded in the gridview updatecommand it works:
"UPDATE [Demo1].[dbo].[CDTrack]
SET [CDTrack].[CDTrackName] = @CDTrackName
WHERE [CDTrack].[CDArtistKey] = @CDArtistKey
AND [CDTrack].[CDTitleKey] = @CDTitleKey
AND [CDTrack].[CDTrackKey] = @CDTrackKey"
Whats the difference? The storedproc executes ok in sql server and I guess that as the SQL version works all of my databinds are correct. Any ideas, thanks, James.
How To Get A Recordset In ASP From StoredProc Using Temp Tables ?
Mar 2, 2000Hi!
The problem that I'm dealing with is that I can't get recordset from SP, where I first create a temporary table, then fill this table and return recordset from this temporary table. My StoredProcedure looks like:
CREATE PROCEDURE MySP
AS
CREATE TABLE #TABLE_TEMP ([BLA] [char] (50) NOT NULL)
INSERT INTO #TABLE_TEMP SELECT bla FROM ……
SELECT * FROM #TABLE_TEMP
When I call this SP from my ASP page, the recordset is CLOSED (!!!!) after I open it using the below statements:
Set Conn = Server.CreateObject("ADODB.Connection")
Baza.CursorLocation = 3
Baza.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=IGOR;Data Source=POP"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "MySP", Conn, , ,adCmdStoredProc
if rs.State = adStateClosed then
response.Write "RecordSet is closed !!!! " ‘I ALLWAY GET THIS !!!!
else
if not(rs.EOF) then
rs.MoveFirst
while not(rs.EOF)
Response.Write rs ("BLA") & " 1 <br>"
rs.MoveNext
wend
end if
end if
Conn.Close
Do you have any idea how to keep this recordset from closing?
Thanks Igor
Testing StoredProc With GUID Parameter
Apr 11, 2002I am working revising a number of stored procs on a system which has suffered some schema changes.
Sometimes I can test my SP code passing in a guid without a problem. Example below:
Test Command:
EXEC usp_Unit_INSERT
'{74A1BABA-0B76-4436-B6AA-01716B686044}', --unitguid
'36', --91', --UnitNumber (varchar)
10, -- xxHospitalNumber
'testUnknown' --UnitName
Above works fine.
I am testing another similar stored proc and am getting this error:
Server: Msg 8152, Level 16, State 9, Procedure usp_Patient_Info_INSERT, Line 24
String or binary data would be truncated.
The statement has been terminated.
(Line 24 performs an insert to a GUID)
Pertient code portions below.
Can anybody shed any light. I am essentially doing nearly identical things to another Stored Proc which works just fine.
Code below fails with above error, but is virtually identical in how it treats all GUID fields to another which does work fine.
-------------------------------------------------
CREATE PROCEDURE [usp_Patient_Info_INSERT]
@PatientGUID varchar(40),--uniqueidentifier,
@PersonGUIDvarchar(40),--uniqueidentifier ,
@CaseNumberdecimal(10,0),
<< and so forth >>
AS
IF @PatientGUID Is Null
SET @PatientGUID =cast( (newid()) as varchar(40))
INSERT INTO [Patient_Info] (
PatientGUID,
PersonGUID,
CaseNumber,
<< and so forth >>
Values (
cast( @PatientGUID as uniqueidentifier),
cast( @PersonGUID as uniqueidentifier),
@CaseNumber,
<< and so forth >>
E-Mail User When StoredProc Fails
Jun 8, 2004Hi,
I want to e-mail a user when a Stored Proc fails, what is the best way to do this? I was going to create a DTS package or is this too complicated?
Also, the Stored Proc inserts data from one table to another, I would like to use Transactions so that if this fails it rolls back to where it was, I'm not sure of the best way to go about this. Could anyone possibly point me in the right direction? Here's a copy of some of the stored procedure to give an idea of what I am doing:
-- insert data into proper tables with extract date added
INSERT INTO tbl_Surgery
SELECT
SurgeryKey,
GETDATE(),
ClinicianCode,
StartTime,
SessionGroup,
[Description],
SurgeryName,
Deleted,
PremisesKey,
@practiceCode--SUBSTRING(SurgeryKey,PATINDEX('%.%',SurgeryKey)+1, 5)
FROM tbl_SurgeryIn
INSERT INTO tbl_SurgerySlot
SELECT
SurgerySlotKey,
GETDATE(),
SurgeryKey,
Length,
Deleted,
StartTime,
RestrictionDays,
Label,
IsRestricted,
@practiceCode
FROM tbl_SurgerySlotIn
INSERT INTO tbl_Appointment
SELECT
AppointmentKey,
GETDATE(),
SurgerySlotKey,
PatientKey,
Cancelled,
Continuation,
Deleted,
Reason,
DateMade
FROM tbl_AppointmentIn
-- empty input tables
DELETE FROM tbl_SurgeryIn
DELETE FROM tbl_SurgerySlotIn
DELETE FROM tbl_AppointmentIn
Any help would me very much appreciated,
Thanks
Call A Storedproc In Select From Block
Mar 31, 2006Hi everyone,
I have a storedproc. This proc send back a value. how can i call this storedproc in select from block. Or what is your advise for other ways....
Select *
, (Exec MyStoredProc MyParam) as Field1
From Table1
Storedproc For Parent Child Gridview
Mar 19, 2008Hi
I have a search page which contains 4 fields.Giving input to anyone of the field should display the result in
Parent Gridview.Parent Gridview has button in it .when i click on the button child Gridview should display related
refund details of customer in parent Gridview.
let us think i have two tables like Customer and refunddetails.
Parent Gridview should display Customer details,Child should display corresponding customers refund details.
I need two storedprocs for binding to both Gridviews.
i have first stored proc for Gridview1
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[MyProc]
(@val1 varchar(255),
@val2 varchar(50),
@val3 varchar(50),
@val4 varchar(50))
--@out smallint OUTPUT
AS
select * from customer where
((@val1 IS NULL) or (name = @val1)) AND
((@val2 IS NULL) or(ssn = @val2)) AND
((@val3 IS NULL) or(accountnumber = @val3)) AND
((@val4 IS NULL) or(phonenumber = @val4))
now i need to capture the @val1 from storedproc1 and using that value retrieve the remaining values in refund table.
name is common in both the tables.
i need this because user can search the value using ssn or accountnumber or phonenumber or name.it is not required that user serches using name.Name textbox can be null.
so please someone help me.
Bind Message Of Storedproc To Lable Control
Mar 23, 2008Hi can anyone tell meHow to bind messages in storedproc to lable control in the front end.I have a stored proc which updates the data table.in certain condition update should not take place and a message should be generated that update did not take place.Can anyone tell me how that message can be shown in front endmy taught was to bind it using lable control. But how the messages can come from storedproc to front endcan we do it using dataset binding.Is there any other way please lemme know immediately .Thankyousiri
View 4 Replies View RelatedStoredProc For Checking For Duplicates And Then Updating The Data.
Mar 22, 2008Hi
I have a question i hope someone here will solve my problem.
I need a storedproc for checking the duplicates before updating the data.
here i need to pass four parameters to storedprocedure which must be updated with existing row.
but before updating the values. Storedproc should check for 2 colum values in all rows.
if same combination of colum values is present in any other row then the present rows which we tring to update should not be
updated. Can anyone help me with this.
Thankyou verymuch.
How To Conditionally Or Programmatically Run A Stored Procedure With Command Type Set To Storedproc?
May 21, 2008I'm using SQL RS 2005 and have a report where we want the report to run a different stored procedure depending on if a condition is true. I've set my 'command type' to stored proc and can type in the name of a stored procedure. If I type in just one stored procedure's name, it runs fine. But if I try to use a =IIF(check condition, if true run stored proc 1, if false run storedproc 2) then the exclamation (run) button is greyed out. Does anyone know how I can do this? Thanks.
View 3 Replies View RelatedSniffing StoredProc Input Parameters For General Error Handling
Dec 10, 2007Hi,
id beg for a hint if our idea of a general dynamic CATCH handler for SPs is possible somehow. We search for a way to dynamically figure out which input parameters where set to which value to be used in a catch block within a SP, so that in an error case we could buld a logging statement that nicely creates a sql statement that executes the SP in the same way it was called in the error case. Problem is that we currently cant do that dynamically.
What we currently do is that after a SP is finished, a piece of C# code scans the SP and adds a general TRY/CATCH bloack around it. This script scans the currently defined input parameters of the SP and generates the logging statement accordingly. This works fine, but the problem is that if the SP is altered the general TRY/CATCH block has to be rebuildt as well, which could lead to inconstencies if not done carefully all the time. As well, if anyone modifies an input param somewhere in the SP we wouldnt get the original value, so to get it right we would have to scan the code and if a input param gets altered within the SP we would have to save it at the very beginning.
So the nicer solution would be if we could sniff the input param values dynamically on run time somehow, but i havent found a hint to do the trick.....
Any tipps would be appreciated...
cheers,
Stefan
Issues Using Parameterised Reports Connecting To Oracle Using ODBC And Microsoft OLE DB Provider For Oracle
Sep 12, 2007I 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.
Output Column Has A Precision That Is Not Valid (loading From Oracle Using OraOLEDB.Oracle.1)
Apr 2, 2007Hi!
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!
Oracle Connection Fail With Microsoft OLEDB Provider For Oracle MSDAORA.1
Feb 22, 2006Hello,
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
Oracle Publication Error:The Permissions Associated With The Administrator Login For Oracle Publisher 'test1' Are Not Sufficient
Jan 12, 2006Hi,
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.