One of our Oracle Tables changed and I am wondering if there's any way that I can query all of our Stored Procedures to try and find out if that Oracle Table Name is referenced in any of our SQL Server Stored Procedures OPENQUERY statements?
View 2 RepliesCreate table #tblActvity
(
activityIDvarchar (50),
activityParentIDvarchar(50)
)
Insert into #tblActvity
SElect '1',Null
[Code] ...
--If I pass activityId 3 or 2 or 4 it should return 0 as none of the activity is circular but If I pass 5, 6 or 7 it should return 1 as they have circular reference....
I need a sql qry which will require to find a circular reference in it.....
As in above sample of data ,If I pass activityId 3 or 2 or 4 to qry it should return 0 as none of the activity is circular but If I pass 5, 6 or 7 it should return 1 as they have circular reference....
I need to list out all the procedures which have select queries and does not use NOLOCK hint. Please note that there are more than 2000 sps and some sps have single select some have multiple, some does not have select queries at all. I need to find out only the ones which does not use NOLOCK hint in a select query.
View 8 Replies View RelatedI was trying to figure out what the OPENQUERY Syntax is to Insert into SQL Server Table from Oracle Linked Server.
View 7 Replies View RelatedI want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
View 11 Replies View RelatedHello Everyone,
First problem :
I have an application which uses Power Builder and Oracle and my job is to convert it so that it can run on a SQL server database.
Oracle allows empty strings - '' - to mean NULL. If the application inserts an empty string in a column of a table, Oracle takes it to mean NULL.
How can i have this functionality in SQL Server?
Second Problem:
SQL Server does not allow stored procedures like sp_addlogin and sp_droplogin etc., to be executed from within a transaction. So everytime i need to execute these stored procedures thru my application, i disconnect and set autocommit to true and connect again. after executing these stored procedures, i disconnect again , set autocommit to false and then connect again. is this how it should be done or is there some better way of doing it.
All help will be highly appreciated. I really need help on these problems - fast.
Thanks
I have just successfully published an web app and db to a server
but when the web app tries to use a stored procedure it says that
it cannot find stored procedure.....in my data base......eeeer
anyone had this problem
Can someone please tell me what oracle software needs to be installed on a sql server machine that will link to an Oracle database on an NT4 machine.
Also can you also confirm if you have successfully executed an oracle stored procedure from sql server using the open query function. If yes, How? because I have read about people having prolems executing stored procedures with arguments using the OPENQUERY Function.
Thanks
How can I find calls which do not exist in stored procedures and functions?We have many stored procedures, sometimes a stored procedure or function which is called does not exist. Is there a query/script or something that I can identify which stored procedures do not 'work' and which procedure/ function they are calling?I am searching for stored procedures and functions which are still called, but do not exist in the current database.
View 7 Replies View RelatedReplication-Replication Distribution Subsystem: agent PRODDATA6 failed. Could not find stored procedure 'sp_MSdel_dboActiveTask_msrepl_ccs'.
Replication-Replication Transaction-Log Reader Subsystem: agent PRODDATA6. The process could not execute 'sp_replcmds' on 'PRODDATA6'.
Replication-Replication Distribution Subsystem: agent PRODDATA6. Could not find stored procedure 'sp_MSupd_dboActiveTask'.
Looks like these sp's got deleted?
I thought this may be very helpful for those of you using SQL7 linked server to Oracle 7.3.4 Database. After much research and no answers when opening a case with Microsoft I finally figured out how to delete using Openquery. There are a few missing pieces of information that would of been a great help in BOL. The first is, the Microsoft OLE DB for Oracle is not the correct choice for the data provier as one might think. The correct choice is the Oracle Provider for OLE DB. The next important thing is that the Oracle table you are querying MUST have a unique index on at least one column not necessarily the column in your WHERE clause. Thirdly, you get much better performance and use of indexes if you put the WHERE clause inside the OPENQUERY statement. Here is the syntax that I found to work in my application:
DECLARE @ins_id varchar(7)
DECLARE @sqlstring nvarchar(2000)
Select @ins_id = '123456'
Select @ins_id = convert(int, @ins_id)
select @ins_id
select @sqlstring = 'DELETE FROM OPENQUERY(LinkedServerName,"SELECT * FROM OracleTableName WHERE I_ID = '
+ '' + @ins_id + '' + '")'
select @sqlstring
EXEC sp_executesql @sqlstring
*Note...I had an additional data conversion from varchar to integer in my statement.
I hope this information helps any others who are having trouble performing this type of task.
I surfed a lot of the internet piecing this together. I have a database that gets copied every night from a live database so users can run queries against it, so I needed to be able to recreate a stored procedure from within ASP.NET. Also, I needed to take information I was importing from a .CSV file and compare it using the stored procedure to find specific information and import it into another database. I was able to do this using the SQLDMO reference object.
Here are some clipits of what you need.
Imports SQLDMOPrivate myServer As New SQLServer
Dim serverName As String = System.Configuration.ConfigurationManager.AppSettings("Server").ToString()Dim userName As String = System.Configuration.ConfigurationManager.AppSettings("UserName").ToString()
Dim password As String = System.Configuration.ConfigurationManager.AppSettings("Password").ToString()
Dim storedProcedure As New StoredProcedure
Dim qresults As SQLDMO.QueryResults
Example Stored Procedure
Try
myServer.Connect(serverName, userName, password)
storedProcedure.Text = "IF EXISTS (SELECT * FROM sysobjects WHERE name='memb_proc' AND user_name(uid)='dbo') DROP PROCEDURE dbo.memb_proc"myServer.Databases.Item("<databasename>", "dbo").StoredProcedures.Add(storedProcedure)
storedProcedure.Text = "CREATE PROCEDURE dbo.memb_proc @lastname varchar(50) = '', @firstname varchar(50) = '', @zip varchar(5) = '', @membrowno varchar(50) OUTPUT AS Select @membrowno = rowno from memb where lastname LIKE @lastname and name LIKE @firstname and zip1 LIKE @zip "myServer.Databases.Item("<databasename>", "dbo").StoredProcedures.Add(storedProcedure)
Label1.Text = "Creation of stored procedure successful"Catch ex As Exception
Label1.Text = "Creation of stored procedure failed"
Finally
myServer.DisConnect()End Try
Run Stored Procedure and Obtain Results
myServer.Connect(serverName, userName, password)
qresults = myServer.ExecuteWithResults("USE [databasename] DECLARE @return_value int,@membrowno varchar(50) EXEC @return_value = [dbo].[memb_proc] @lastname = N'" & Last & "', @firstname = N'" & First & "',@zip = N'" & ZIP & "', @membrowno = @membrowno OUTPUT SELECT @membrowno as N'@membrowno'")For num = 1 To qresults.Rows
MemberRowNo = qresults.GetColumnString(num, 1)
Next
myServer.DisConnect()
How can I list the stored procedures and user-defined functions that reference a given column? I could search ROUTINE_DEFINITION in INFORMATION_SCHEMA.ROUTINES for '%MyColumnName%' but MyColumnName is not always unique.
Thanks.
Consider a situation where a stored procedure taking a varbinary(max) (BLOB) input parameter then calls a nested stored procedure and passes along that varbinary(max) as an input parameter to the nested stored procedure.
Is a copy of the BLOB provided to the nested stored procedure (passed by value) OR is the BLOB passed by reference.
My interest is in understanding the potential memory hit when handling large BLOBs in this environment.
For example, if the BLOB is 200MB, will SQL server need to allocate memory for a new copy each time it's passed to another stored procedure?
Looks like table type parameters are passed by reference, but I haven't been able to find any info on BLOBS in this context.
The script below can be used to determine the reference levels of all tables in a database in order to be able to create a script to load tables in the correct order to prevent Foreign Key violations.
This script returns 3 result sets. The first shows the tables in order by level and table name. The second shows tables and tables that reference it in order by table and referencing table. The third shows tables and tables it references in order by table and referenced table.
Tables at level 0 have no related tables, except self-references. Tables at level 1 reference no other table, but are referenced by other tables. Tables at levels 2 and above are tables which reference lower level tables and may be referenced by higher levels. Tables with a level of NULL may indicate a circular reference (example: TableA references TableB and TableB references TableA).
Tables at levels 0 and 1 can be loaded first without FK violations, and then the tables at higher levels can be loaded in order by level from lower to higher to prevent FK violations. All tables at the same level can be loaded at the same time without FK violations.
Tested on SQL 2000 only. Please post any errors found.
Edit 2006/10/10:
Fixed bug with tables that have multiple references, and moved tables that have only self-references to level 1 from level 0.
-- Start of Script - Find_Table_Reference_Levels.sql
/*
Find Table Reference Levels
This script finds table references and ranks them by level in order
to be able to load tables with FK references in the correct order.
Tables can then be loaded one level at a time from lower to higher.
This script also shows all the relationships for each table
by tables it references and by tables that reference it.
Level 0 is tables which have no FK relationships.
Level 1 is tables which reference no other tables, except
themselves, and are only referenced by higher level tables
or themselves.
Levels 2 and above are tables which reference lower levels
and may be referenced by higher levels or themselves.
*/
declare @r table (
PK_TABLE nvarchar(200),
FK_TABLE nvarchar(200),
primary key clustered (PK_TABLE,FK_TABLE))
declare @rs table (
PK_TABLE nvarchar(200),
FK_TABLE nvarchar(200),
primary key clustered (PK_TABLE,FK_TABLE))
declare @t table (
REF_LEVEL int,
TABLE_NAME nvarchar(200) not null primary key clustered )
declare @table table (
TABLE_NAME nvarchar(200) not null primary key clustered )
set nocount off
print 'Load tables for database '+db_name()
insert into @table
select
TABLE_NAME = a.TABLE_SCHEMA+'.'+a.TABLE_NAME
from
INFORMATION_SCHEMA.TABLES a
where
a.TABLE_TYPE = 'BASE TABLE'and
a.TABLE_SCHEMA+'.'+a.TABLE_NAME <> 'dbo.dtproperties'
order by
1
print 'Load PK/FK references'
insert into @r
selectdistinct
PK_TABLE =
b.TABLE_SCHEMA+'.'+b.TABLE_NAME,
FK_TABLE =
c.TABLE_SCHEMA+'.'+c.TABLE_NAME
from
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS a
join
INFORMATION_SCHEMA.TABLE_CONSTRAINTS b
on
a.CONSTRAINT_SCHEMA = b.CONSTRAINT_SCHEMA and
a.UNIQUE_CONSTRAINT_NAME = b.CONSTRAINT_NAME
join
INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
on
a.CONSTRAINT_SCHEMA = c.CONSTRAINT_SCHEMA and
a.CONSTRAINT_NAME = c.CONSTRAINT_NAME
order by
1,2
print 'Make copy of PK/FK references'
insert into @rs
select
*
from
@r
order by
1,2
print 'Load un-referenced tables as level 0'
insert into @t
select
REF_LEVEL = 0,
a.TABLE_NAME
from
@table a
where
a.TABLE_NAME not in
(
select PK_TABLE from @r union all
select FK_TABLE from @r
)
order by
1
-- select * from @r
print 'Remove self references'
delete from @r
where
PK_TABLE = FK_TABLE
declare @level int
set @level = 0
while @level < 100
begin
set @level = @level + 1
print 'Delete lower level references'
delete from @r
where
PK_TABLE in
( select TABLE_NAME from @t )
or
FK_TABLE in
( select TABLE_NAME from @t )
print 'Load level '+convert(varchar(20),@level)+' tables'
insert into @t
select
REF_LEVEL =@level,
a.TABLE_NAME
from
@table a
where
a.TABLE_NAME not in
( select FK_TABLE from @r )
and
a.TABLE_NAME not in
( select TABLE_NAME from @t )
order by
1
if not exists (select * from @r )
begin
print 'Done loading table levels'
print ''
break
end
end
print 'Count of Tables by level'
print ''
select
REF_LEVEL,
TABLE_COUNT = count(*)
from
@t
group by
REF_LEVEL
order by
REF_LEVEL
print 'Tables in order by level and table name'
print 'Note: Null REF_LEVEL nay indicate possible circular reference'
print ''
select
b.REF_LEVEL,
TABLE_NAME = convert(varchar(40),a.TABLE_NAME)
from
@table a
left join
@t b
on a.TABLE_NAME = b.TABLE_NAME
order by
b.REF_LEVEL,
a.TABLE_NAME
print 'Tables and Referencing Tables'
print ''
select
b.REF_LEVEL,
TABLE_NAME = convert(varchar(40),a.TABLE_NAME),
REFERENCING_TABLE =convert(varchar(40),c.FK_TABLE)
from
@table a
left join
@t b
on a.TABLE_NAME = b.TABLE_NAME
left join
@rs c
on a.TABLE_NAME = c.PK_TABLE
order by
a.TABLE_NAME,
c.FK_TABLE
print 'Tables and Tables Referenced'
print ''
select
b.REF_LEVEL,
TABLE_NAME = convert(varchar(40),a.TABLE_NAME),
TABLE_REFERENCED =convert(varchar(40),c.PK_TABLE)
from
@table a
left join
@t b
on a.TABLE_NAME = b.TABLE_NAME
left join
@rs c
on a.TABLE_NAME = c.FK_TABLE
order by
a.TABLE_NAME,
c.PK_TABLE
-- End of Script
Results from Northwind database:
Load tables for database Northwind
(13 row(s) affected)
Load PK/FK references
(13 row(s) affected)
Make copy of PK/FK references
(13 row(s) affected)
Load un-referenced tables as level 0
(0 row(s) affected)
Remove self references
(1 row(s) affected)
Delete lower level references
(0 row(s) affected)
Load level 1 tables
(7 row(s) affected)
Delete lower level references
(9 row(s) affected)
Load level 2 tables
(4 row(s) affected)
Delete lower level references
(3 row(s) affected)
Load level 3 tables
(2 row(s) affected)
Done loading table levels
Count of Tables by level
REF_LEVEL TABLE_COUNT
----------- -----------
1 7
2 4
3 2
(3 row(s) affected)
Tables in order by level and table name
Note: Null REF_LEVEL nay indicate possible circular reference
REF_LEVEL TABLE_NAME
----------- ----------------------------------------
1 dbo.Categories
1 dbo.CustomerDemographics
1 dbo.Customers
1 dbo.Employees
1 dbo.Region
1 dbo.Shippers
1 dbo.Suppliers
2 dbo.CustomerCustomerDemo
2 dbo.Orders
2 dbo.Products
2 dbo.Territories
3 dbo.EmployeeTerritories
3 dbo.Order Details
(13 row(s) affected)
Tables and Referencing Tables
REF_LEVEL TABLE_NAME REFERENCING_TABLE
----------- ---------------------------------------- ----------------------------------------
1 dbo.Categories dbo.Products
2 dbo.CustomerCustomerDemo NULL
1 dbo.CustomerDemographics dbo.CustomerCustomerDemo
1 dbo.Customers dbo.CustomerCustomerDemo
1 dbo.Customers dbo.Orders
1 dbo.Employees dbo.Employees
1 dbo.Employees dbo.EmployeeTerritories
1 dbo.Employees dbo.Orders
3 dbo.EmployeeTerritories NULL
3 dbo.Order Details NULL
2 dbo.Orders dbo.Order Details
2 dbo.Products dbo.Order Details
1 dbo.Region dbo.Territories
1 dbo.Shippers dbo.Orders
1 dbo.Suppliers dbo.Products
2 dbo.Territories dbo.EmployeeTerritories
(16 row(s) affected)
Tables and Tables Referenced
REF_LEVEL TABLE_NAME TABLE_REFERENCED
----------- ---------------------------------------- ----------------------------------------
1 dbo.Categories NULL
2 dbo.CustomerCustomerDemo dbo.CustomerDemographics
2 dbo.CustomerCustomerDemo dbo.Customers
1 dbo.CustomerDemographics NULL
1 dbo.Customers NULL
1 dbo.Employees dbo.Employees
3 dbo.EmployeeTerritories dbo.Employees
3 dbo.EmployeeTerritories dbo.Territories
3 dbo.Order Details dbo.Orders
3 dbo.Order Details dbo.Products
2 dbo.Orders dbo.Customers
2 dbo.Orders dbo.Employees
2 dbo.Orders dbo.Shippers
2 dbo.Products dbo.Categories
2 dbo.Products dbo.Suppliers
1 dbo.Region NULL
1 dbo.Shippers NULL
1 dbo.Suppliers NULL
2 dbo.Territories dbo.Region
(19 row(s) affected)
CODO ERGO SUM
Is there a way to call an Oracle stored procedure through a MS SQL stored procedure (via linked server)? If so, can output parameters be used?
View 1 Replies View Related
Hi all,
I wrote following SP in Oracle but getting Red color with Into symbol at Procedure Name in List of Procs.
create or replace procedure EXAMPLE(:year in char, TYPE in char) AS
BEGIN
IF :year IS NULL THEN
select s.survey_year,st.survey_type_name,count(s.survey_id) as count from tqdb_survey s,TQDB_SURVEYS_TYPE st where s.survey_type_id=st.SURVEY_TYPE_ID
and st.survey_type_name=TYPE and s.is_active='N' and s.survey_year>0 group by s.SURVEY_YEAR,st.survey_type_name;
ELSE IF TYPE IS NULL THEN
select s.survey_year,st.survey_type_name,count(s.survey_id) as count from tqdb_survey s,TQDB_SURVEYS_TYPE st where s.survey_type_id=st.SURVEY_TYPE_ID
and s.survey_year=:year and s.is_active='N' and s.survey_year>0 group by s.SURVEY_YEAR,
st.survey_type_name;
END IF;
END EXAMPLE
Any Body Help me.
Hi
Is it possible to to create a 'Like' statement that refernces all the data in a column from another table?
Thanks
I would like to create a function that will replace a string using a reference table
I have a table : reference
ID String ReplaceWith
1 ≈ &
2 < <
3 > >
If I pass a string into a function, function needs to replace a string with replace with string column in reference table
For example, if I pass a string : car $ap; fjld
The function should return car & fjld
How can i create a function like this so that i can call it in Stored procedure....
How do i migrate Stored Procedures from SQL Server (7.0) to ORACLE (8.0)??
Thanks
Anand
Hello,
Does SQL server 2005 provide capability to run Oracle stored procedures. I already have a linked server established for Oracle.
I have several oracle stored procedures that :
a) Accept multiple input parameters and return multiple out parameters.
b) Accept multiple input parameters and return a REF CURSOR as out parameter.
If you have any sample code, can you please post it here along with any suggestions. I researched, but there seems to be no solution, especially for REF CURSOR. Much appreciate it.
Thnx
Sam
CREATE TABLE PRODUCT_SALES
(
Salesmanid BIGINT,
Productid BIGINT
)
INSERT INTO PRODUCT_SALES (Salesmanid,Productid) VALUES (1,1)
INSERT INTO PRODUCT_SALES (Salesmanid,Productid) VALUES (1,2)
INSERT INTO PRODUCT_SALES (Salesmanid,Productid) VALUES (1,3)
SELECT * FROM PRODUCT_SALES
/* SalesmanID is reference key from Sales Master and ProductID is reference key from Product Master. How should i restrict user through DB with any constraint check, if user tries to enter
INSERT INTO PRODUCT_SALES (Salesmanid,Productid) VALUES (1,2),
It should throw error , if possible user defined message would be more useful.
Oracle Server Quick Reference Guidehttp://quickdba.blogspot.com/2006/0...ence-guide.htmlVisit daily to get more updated articles
View 1 Replies View RelatedHi Guys,I want to find any specific text in all SQL server stored procedure.How can I do this. Please let me knowThanks in Advance
View 1 Replies View RelatedHi
im using sqlserver studio managment
i goto a specific database and goto stored procedures and add stored procedures from context menu and created successfully .
i try to find it between procedures but not found i try to create it again gives me error that already found so.
where i can get my created stored procedures from sqlserver studio managment?
thanks in advance.
i am inserting something into the temp table even without creating it before. But this does not give any compilation error. Only when I want to execute the stored procedure I get the error message that there is an invalid temp table. Should this not result in a compilation error rather during the execution time.?
--create the procedure and insert into the temp table without creating it.
--no compilation error.
CREATE PROC testTemp
AS
BEGIN
INSERT INTO #tmp(dt)
SELECT GETDATE()
END
only on calling the proc does this give an execution error
I am required to find or check that a specific word exists in string or not.
Suppose I have to find the word 'st' than I need the result true if and only if the following occurrences are there.
1. 'St is valid;' -> true
2. 'DOB is valid;ST is invalid;' -> true
3. 'DOB is valid; ST is invalid;' -> true
4. 'DOB is valid;invalid ST;' -> true
5. 'DOB is valid; invalid ST;' -> true
6. 'DOB is valid; invalid STate;' -> false
Means the exact ST should be search. It is not free text search.
T-SQL is needed to be used in select statement with case using PATINDEX, RegEx or any suitable t-sql command.
Hi,
This may be a really silly question, but I am starting to experiment with stored procedures for the first time and I am not really sure about how they work. I am working in SQL Server Management Studio Express...
When putting a SELECT query statement into a stored procedure and executing it, how or where can you get or find the actual record set supposedly retrieved?
Thanks for any help..
--------------------------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [petrander].[DynamicQuery] @taxparent int = 1ASBEGIN SET NOCOUNT ON; SELECT ID, TaxonID, Taxon FROM QueryView WHERE ParentID1 = COALESCE(@taxparent, ParentID1) AND ParentID2 = COALESCE(@taxparent, ParentID2) AND ParentID3 = COALESCE(@taxparent, ParentID3) AND ParentID4 = COALESCE(@taxparent, ParentID4) AND ParentID5 = COALESCE(@taxparent, ParentID5) AND ParentID6 = COALESCE(@taxparent, ParentID6) AND ParentID7 = COALESCE(@taxparent, ParentID7) AND ParentID8 = COALESCE(@taxparent, ParentID8)END
Hi,
I am using the below SQL query for Oracle
SELECT FP_MONTH
FROM CDWD..CDW.ACCOUNT_SALES
WHERE LAST_MODIFIED_DT > '2002-12-01 00:00:00.000'
OR SOURCE_CONTROL_DT > '2002-12-01 00:00:00.000'
My questions are
1. If I am using this date fileter it is taking long time than without filter
Why ?.
2. How I can write Open query for the above query?.
I am expecting valuable advice.
Thanks,
Ravi
In SQL Server 2000 I have set up an Oracle linked server. When I runthe following query it runs fine:-SELECT*FROMOPENQUERY(LINKEDSERVERNAME,'SELECT * FROM ORACLETABLENAME')However the following query does not work:-SELECT*FROMOPENQUERY(LINKEDSERVERNAME,'SELECT FIELDNAME FROM ORACLETABLENAME')This error is returned:-Server: Msg 7321, Level 16, State 2, Line 1An error occurred while preparing a query for execution against OLE DBprovider 'MSDAORA'.[OLE/DB provider returned message: ORA-00904: "FIELDNAME": invalididentifier]OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Preparereturned 0x80040e14].Basically select * works ok, but if I specify the field(s) I need thenit errors. I have tried entering the field names in upper and lowercase but it makes no difference.My real problem is that some dates in the Oracle database are pre 1753which SQL server does not recognise so I need to convert (decode) them.Any help would be appreciated.ThanksChris
View 2 Replies View RelatedHow to find out last created stored procedures in sql.
View 5 Replies View RelatedHi
Does anyone the syntax or command to View Stored Procedures with Transact SQL statement. I have tried using the syscomments but there is some information missing.
I would appreciate any reply !
Thanks you for your time.
I found lots of methods for individually scripting out stored procedures, but I'd like to know if there's a way to do many (like 100 or so) at once. Ideally, I'd like to execute something that would save all the procedures that start with 'dev_' to the file system somewhere, then delete the dev_ SP's from the database and leave the other SPs that actually get used. Is that possible in one step?
View 10 Replies View Related