Oracle Procs To SQL SPs

Jul 2, 2007

Hi all,

I have to write SPs in SQL 2005 based on procs in Oracle DB.How do I go abt this? Any help will be welcome.

Thank you

Necessity is the mother of all inventions!

View 19 Replies


ADVERTISEMENT

Stored Procs: Is There A Way To Divide Up (or Namespace) Groups Of Stored Procs

Jan 15, 2008

Is there a way to namespace groups of stored procs to reduce confusion when using them?

For C# you can have ProjectName.ProjectSection.Classname when naming a class. I am just wondering if there is a way to do the same with SQL stored procs. I know Oracle has packages and the name of the package provides a namespace for all of the stored procs inside it.

View 1 Replies View Related

Problem With Using Stored Procs As I/p To Another Stored Procs

May 7, 2004

HI,

CREATE PROCEDURE PROC1
AS
BEGIN
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CUSTOMER A
INNER JOIN CUSTOMERPREFERENCE B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
WHERE B.INTPREFERENCEID IN (6,7,2,3,12,10)
ORDER BY B.INTCUSTOMERID

END

IF I AM USING THIS PROC AS I/P TO ANOTHER PROC THEN IT GIVES NO PROBLEM AS I CAN USE ?

CREATE PROCEDURE PROC2
AS
BEGIN

CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))

INSERT INTO #SAATHI
EXEC PROC1


ST......1,
ST......2,


END.

BUT IF , I USE ANOTHER PROC SIMILAR TO THE FIRST ONE WHICH GIVES SLIGHTLY DIFFERENT RESULTS AND GIVES TWO SETS OF RESULTS,THEN WE HAVE A PROBLEM,HO TO SOLVE THIS :-


CREATE PROCEDURE MY_PROC
AS
BEGIN
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CUSTOMER A
INNER JOIN CUSTOMERPREFERENCE B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
WHERE B.INTPREFERENCEID IN (23,12,10)
ORDER BY B.INTCUSTOMERID

END

SELECT A.INTCUSTOMERID,MAX(case when A.intpreferenceid = 23 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 12 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 10 then '1'
else '0' end) AS PREFER
FROM CUSTOMER
GROUP BY A.INTCUSTOMERID
ORDER BY A.INTCUSTOMERID
END

WHICH NOW GIVES ME TWO SETS OF DATA , BOTH REQUIRED THEN HOW TO USE ONE SET OF RESULTS AS I/P TO ANOTHER PROC AND OTHER SET OF RESULTS AS I/P TO YET ANOTHER PROC .



CREATE PROCEDURE PROC2
AS
BEGIN

CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))

INSERT INTO #SAATHI
EXEC MY_PROC


ST......1,
ST......2,

END.

BUT, HERE I WANT TO USE FIRST DATASET ONLY , HOW TO USE IT ?

THANKS.

View 4 Replies View Related

Issues Using Parameterised Reports Connecting To Oracle Using ODBC And Microsoft OLE DB Provider For Oracle

Sep 12, 2007

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.

View 4 Replies View Related

Output Column Has A Precision That Is Not Valid (loading From Oracle Using OraOLEDB.Oracle.1)

Apr 2, 2007

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!

View 10 Replies View Related

Setting Up Oracle Linked Server : Need Help : Sql2005 Running On XP Linking In Oracle 10.2

Oct 26, 2006

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.

View 1 Replies View Related

Data Access :: Accessing Oracle Tables From Server Via Oracle DBLINK?

May 8, 2015

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 ?

View 2 Replies View Related

Oracle Connection Fail With Microsoft OLEDB Provider For Oracle MSDAORA.1

Feb 22, 2006

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

View 17 Replies View Related

Oracle Publication Error:The Permissions Associated With The Administrator Login For Oracle Publisher 'test1' Are Not Sufficient

Jan 12, 2006

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.


 

View 2 Replies View Related

Inserting Into Oracle Table That Has DATE_HIGH As A Partition And Need Oracle Sequence Used

May 11, 2007

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

View 1 Replies View Related

Unable To Connect To Oracle Using Microsoft OLEDB Provider For Oracle

Aug 23, 2007

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

View 3 Replies View Related

Trouble With: Linked Server To Oracle Using OraOLEDB.ORacle Provider

Jan 11, 2007

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?

View 3 Replies View Related

Oracle Parameterized Queries To Update Oracle Table Do Not Work

Apr 23, 2007

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

View 5 Replies View Related

How To Migrate Oracle Applications 11.03/Oracle 8.05 To Navision 4.0/ms Sqlserver

Oct 29, 2005

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

View 1 Replies View Related

Oracle Error ORA-12154 From An Application Which Never Uses Oracle

Mar 14, 2008

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

View 1 Replies View Related

Using SSIS For ETL From Oracle 10g ODS Into Oracle DW And From There Into SSAS Cubes

May 2, 2007

Hi,





This might seems a little 'out there', but has anyone tried doing ETL from an Oracle 10g ODS into an Oracle 10g DW, and from there into SSAS2005 cubes?



Any caveats houghtscomments on doing this?



Thanks,

JGP

View 1 Replies View Related

Stored Procs Are BAD!! BAD, I Tell You!!!

Feb 5, 2005

almost choked when i read the following recent post on The Daily WTF (http://thedailywtf.com/) the other day --

Logical Tiers? That Makes No Sense! (http://thedailywtf.com/ShowPost.aspx?PostID=28959)

i don't think i can do justice to how utterly stupid that stored procedure is

read the comments and have a laugh

one of the points made was that stored procedures are bad

this twigged something in my memory, so i dug around in my bookmarks, and sure enough, here's another decent discussion about stored procs --

Stored procedures are bad, m'kay? (http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx)

enjoy!

View 6 Replies View Related

.exe From Stored Procs

Jun 28, 2007

Hey guys.

I am wondering if it is possible to execute an .exe from a stored proc. I have an application that calls a stored proc and I am wanting for that stored proc to call up an app to show certain results. I have no way of doing this on the application itself as I dont have the source code. So, since I do have access to the SQL I am wondering if it could be done there.

Thanks
tibor

View 14 Replies View Related

Startup Procs

Jan 13, 2004

Does anyone know how to tell which procedures are called when SQL Server 2000 starts up if scan for startup procs is turned on?

View 11 Replies View Related

Formatted Procs

Mar 3, 2004

Can somebody provide me an argument on how nicely formatted and indented procs perform better. I am currently formatting a 1000 line proc just to understand what it does ... Maybe your argument will help convince my development team to write neat procs :)

View 8 Replies View Related

I Need Help With Stored Procs And UDF

Apr 8, 2004

First off, this is a cross post which is also located here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=34073

Now, with the following stored procs and UDF, the Result is NULL and I cannot figure out why.

What I am doing is calling a stored procedure pass in an OUTPUT variable of type float. This stored proceudre then calls a different stored procedure passing the same OUTPUT variable of type float. The second stored procedure then calls a UDF passing in two variables to be multiplied ans this should set the OUTPUT variable to the result.

The UDF does return the correct result to the interior stored procedure, but the interior stored procedure does not pass the value back to the original stored procudure resultint in a Result value = NULL.

The code is below, just copy / past, execute and you will see exactly what I mean.

Any thoughts?


USE NORTHWIND
GO

CREATE FUNCTION RECTANGULAR_XSECTION
(@fWidth float, @fHeight float)

RETURNS float

AS
BEGIN
RETURN (@fWidth * @fHeight)
END
GO


CREATE PROCEDURE usp_shapes_GetRectangularXSection

@fResult float OUTPUT

AS

declare @fWidth float, @fHeight float
SELECT @fWidth = 108, @fHeight = 10

SELECT @fResult = [dbo].[RECTANGULAR_XSECTION](@fWidth, @fHeight)
SELECT @fResult AS CalledProcedureOkHere

GO

CREATE PROCEDURE usp_shapes_GetXSection

@fResult float OUTPUT

AS

EXECUTE usp_shapes_GetRectangularXSection @fResult
SELECT @fResult AS CallingProcedure_NULL?
GO

declare @fResult float
EXECUTE usp_shapes_GetXSection @fResult
SELECT @fResult as OutsideCallingProcedure_NULL?
GO

DROP FUNCTION RECTANGULAR_XSECTION
GO
DROP PROCEDURE usp_shapes_GetRectangularXSection
GO
DROP PROCEDURE usp_shapes_GetXSection
GO


Mike B

View 3 Replies View Related

New To Stored Procs

Nov 13, 2007

I am trying to improve my SQL and reduce the number of connections my website is making to the server.

Currently I have a stored procedure that gets all the games (and their details) for a single user which works fine which uses a WHERE userid = @userid which is an Int value.

I now need to create a new procedure which brings back the to all their "friends" (again by their userid). How is it best to do this? I originally tried to do a WHERE userID IN (@userid) but was unable to work out what variable type to use for @userid.

Is it best to do a single query in this way or is there a way to use the existing SP, loop through the collection of userids and join the result together into a single set to return? If an IN is the best route, what is the correct datatype for the variable?

View 5 Replies View Related

Extended Procs Using C#

Oct 13, 2006

What is the procedure to create a extended stored procedure using C#.

Actually we have created a dll file using c# and it was added to the bin folder of mssql server.
Also dll file is added to the sql server using "xp_extendedproc"

But while executing the extended proc we got this error
"ODBC: Msg 0, Level 16, State 1
Cannot load the DLL xp_TestSql.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.)"

View 8 Replies View Related

SQL Stored Procs

Aug 8, 2006

can anyone help me out.... i need to compare a date that was retrieved from the database and the system date then it must be coded in the stored procs of the database.. help!!!!

View 10 Replies View Related

How To Use SQL NOCOUNT In Store Procs???

Feb 19, 2008

I donot know how to use SQL NOCOUNT . I have written store procs for select and insert , Below are the store procs ,Can anyone tell me how to use SQL NOCOUNT in this StoreProcs???
Select:
if exists (select * from dbo.sysobjects where id = object_id('[dbo].[GetAllLinks]') )drop Procedure [dbo].[GetAllLinks]
GO
CREATE Procedure [dbo].[GetAllLinks]
AS
 Select * from UsefulLink
Go
 
Insert Storeproc:
if exists (select * from dbo.sysobjects where id = object_id('[dbo].[InsertLink]') )drop Procedure [dbo].[InsertLink]
GOCREATE Procedure [dbo].[InsertLink]
@Title varchar(100),
@Location Varchar(100),@ID int OUTPUT
AS
Set @ID = -1Begin Transaction
Insert Into UsefulLink(Title, Location) Values
(@Title,@Location)if @@error <> 0
RollBack Transaction
Else
BEGIN
Commit Transaction
Select @ID = @@identity
 
END
Go

View 3 Replies View Related

Transactions And Stored Procs

Mar 8, 2004

All,

I'm relatively new to stored procs (not to SQL or SQL Server) and I am trying to get transactions to work within a stored proc. Here is the code:


(
--define the parameters that are needed
@userID char(32),
@userName varchar(50),
@status char(10),
@type char(10),
@password varchar(50),
@firstName varchar(100),
@lastName varchar(100),
@email varchar(200),
@domain varchar(50),
@pwdExpiry int,
@badLogin int,
@lastLogin datetime,
@full varchar(8000),
@read varchar(8000),
@noaccess varchar(8000)
)
AS
BEGIN TRAN tmp1
--First, insert the user into the system
INSERT INTO ptsUsers(UserID, UserName, Status, Type, Password, FirstName, LastName, Email, DomainName, PasswordExpiry, BadLoginAttempts, LastLoginDate)
values(@userID, @userName, @status, @type, @password, @firstName, @lastName, @email, @domain, @pwdExpiry, @badLogin, @lastLogin);

--Now, we need to add the function access edges for the user
declare @arrayValue char(32)
declare @rightID char(32)
declare @sepPos int
while patindex('%,%',@full)<>0
BEGIN
select @sepPos = patindex('%,%' , @full)
select @arrayValue = left(@full, @sepPos - 1)
-- replace the value with an empty string
select @full = stuff(@full, 1, @sepPos, '')

--create and parse the new id
declare @strID char(32),@tmpStr varchar(40)
set @tmpStr = newid();
set @strID = REPLACE(@tmpStr,'-','')

--get the access right id for full control
select @rightID = (Select AccessRightID from ptsAccessRights where Name = 'Write')
if(@rightID IS NOT NULL)
--insert the records that are full access
INSERT INTO ptsFunctionAccessEdges(FunctionAccessEdgeID, FunctionID, AccessRightID, UserID)
VALUES(@strID, @arrayValue, @rightID, @userID)
else
RAISERROR(50100,15,1)
END
COMMIT TRAN tmp1
IF @@TRANCOUNT > 0
RAISERROR(50101,15,1)
ROLLBACK TRAN tmp1


The transaction does not rollback when any errors occur at all. Any advice/help?

View 3 Replies View Related

IF Statement In Stored Procs... Help!

Apr 12, 2004

Hi all!

I am not an expert in Stored Procs. I would like to build one for a product list that would return a default value without using output parameters, if possible. Ultimately, I wouldn't be opposed to it.

It currently looks like this:


CREATE PROCEDURE ProductsByVendorNo
(
@VendorNum nvarchar(24)
)
AS

SELECT
P.ProductID AS ProductID,
P.ProductShortName AS ProductName,
P.ProductDesc AS ProductDesc,
U.UnitDesc AS Unit,
P.VendorProductNumber AS VendorNumber,
V.VendorName AS VendorName,
P.Price AS Price,
P.ImageThumb AS ImageThumb

FROM
tblProducts AS P
INNER JOIN tblUnitCodes AS U ON P.UnitCode=U.UnitCode
INNER JOIN tblVendors AS V ON P.VendorID=V.VendorID

WHERE
V.VendorsVendorNo = @VendorNum
AND P.Inactive = 0

ORDER BY
ProductName,
VendorNumber



I would like for it to return a default, constant value for the URL in the ImageThumb field, if this one is empty. I could not find good documentation of how to use IF statements for this case, i.e. to alter the return of just one field.

Suggestions?

Any input is highly appreciated.

Thanks in advance,

Mili Skikic

View 8 Replies View Related

Security On Stored Procs On Dev Db

Mar 25, 2002

I want to "deny" create, update,and delete access on the dbo stored procs that are in the database, but do not want take away dbo owner access. is this possible?

can i create a role and deny access on a particular table in msdb? or a system table in the user table. Thus preventing the developers on the box access to update any of the dbo owned sp's and have them create their own user-owned stored procs?

this is sql7, sp3, development box.

thanks,

View 1 Replies View Related

Using Stored Procs In A DTS Package

Apr 27, 2001

I am trying to set up a DTS package that selects data from one table on server A into another table on server B. I want to do a select statement that will do the following:

select store_name
from store (server A)
where date_created >= (select max(date_created) from store (server B)

I use EM 7.0 to manage all of my extracts, however, the data is moving from a Syabase (adaptive Server)onto another Syabase(adaptive server) machine.
Unfortunately, there is no functionality for a linked server connection.

I tried the following, (which doesn't error out), but is not displaying the source columns in the destination tab of the DTS package when setting up the transformations.

declare @maxdate datetime
exec serverB.dbo.sp_max_date_from_store @maxdate
select store_name
from store --(server A)
where date_created >= @store

Any help or suggestions would be greatly appreciated!
trevorb

View 1 Replies View Related

COM Automation Procs Failure

Oct 24, 2000

I have been trying to execute the following code:

DECLARE @object int
DECLARE @hr int
DECLARE @property varchar(255)
DECLARE @return varchar(255)
DECLARE @output varchar(255)
DECLARE @source varchar(255)
DECLARE @description varchar(255)
--Instantiate a Network Object
EXEC @hr = sp_OACreate 'WScript.Network', @object OUT
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
-- Get a property using an output parameter.
--EXEC @hr = sp_OAMethod @object, 'userDomain' --,@property OUT
EXEC @hr = sp_OAGetProperty @object, 'userDomain'--, @property OUT
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
--PRINT @property

-- Destroy the object.
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END

I get the same behavior stated in microsoft's support site related to BUG
55840 for Sql Server 7.0.
I get an error message when trying to retrieve with @property OUT parameter.
And I get an Empty record set when NO Output param is supplied.

http://support.microsoft.com/support/kb/articles/Q236/4/40.ASP

I am running Sql Server 7.0 SP2 on Windows 2000 AS.
SP2 has this BUG listed as being Fixed. Am I missing something?

Thanks in Advance for your Help,
Chris

View 3 Replies View Related

Not Normal Will Stored Procs Help

Jun 19, 2000

I will be taking over a database that has almost no pk's or relations(this is not my choice, but a vendors)
Management is looking at stored procs to improve performance, but I am wondering
if the db is in this state will there really be a gain. I am pushing for normalization
first, but if anybody has any ideas or opinions I would appreciate

View 2 Replies View Related

Stored Procs And Parameters

Sep 10, 2001

I have a stored proc that is run periodically which I execute with a DTS package. The problem is that I have to update a field for each record selected by this stored proc with a specific date. I need to prompt the operator for this date so I can use this value.

View 6 Replies View Related

Errors And Stored Procs

Jul 28, 2004

Ok, I've read somewhere(which I'm looking for again : ) that said that there are errors like DeadLock that kills the execution of a stored proc and there are other errors that do not necessarily kill the rest of the execution of the stored proc. Is that true? If so does anyone have any links I can read. What I'm seeing is a bad id in the foreign key and I think what is happening is that there was a unique constraint error on the first insert but the stored proc continued executing and used the bad id later on in the stored proc.

I do know I can use the @@error and will start using it but I need more proof to agree or not agree with my theory.

Thanks ahead of time for any information you can give me either way.

DMW

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved