------------------------
I want to fire a trigger which calls a stored proc, passing the name of the table the trigger is defined on to the proc without having to hardcode.
Can it be done ?
trigger x on table y
for update
as
declare @.table_name
select @.table_name = get underlying table_name 'y' from somewhere
exec stored proc (@.table_name)
where do I find the name of the table the current trigger is defined on ?
thanksI want to make a birthday cake out of nitroglycerin.
Sometimes the question is not "can it be done", but "should it be done".
Don't put this kind of crap in a trigger.|||Why don't you tell us what your trying to acccomplish instead of trying to force a technical solution that doesn't seem to be a "best practice" kind of thing
Blind dude, did they base rat on you?|||Huh? Rat? Base? Huh?
Showing posts with label proc. Show all posts
Showing posts with label proc. Show all posts
Saturday, February 25, 2012
Passing table variable as input parameter to stored proc
I understand you cannot pass a table variable as input parameter to a stored
proc.
But what is the alternative in my case?
Current DB standards limit access of linked servers to the execution of
stored procs and functions, no cross server joins.
But I need to get data from the remote server that matches keys on imy local
server. The remote table is too large to bring over to the local server in
its entirety. In order to limit my result set from the remote server, I must
pass it the
keys of the records I want.
My issue is how to pass the keys.
For example, say I have customers and orders databases are on different
servers. From orders I want to get customer information for 200 customers.
Somehow I need to pass 200 custids to customers, execute the query there and
return 200 sets of customer data.
How do I call a proc on orders and pass 200 custids as a parameter?You can always query on a table of some other database on some other
server by specifying server name, db name, owner name and table name.
Something like this:
SELECT * FROM <Server Name>.<Database Name>.<Owner>.<Table>
If you have any other concern which is not covered here, please write.
I will surely try to help you.
Thanks|||Dave
Table-Valued UDFs --
USE Northwind
GO
IF object_id('dbo.get_cust_orders') IS NOT NULL
DROP FUNCTION dbo.cust_orders
GO
CREATE FUNCTION dbo.get_cust_orders
(
@.custid char(5)
)
RETURNS TABLE
AS
RETURN SELECT
*
FROM
Orders
WHERE
CustomerID = @.custid
GO
SELECT OrderID, OrderDate
FROM get_cust_orders('VINET') AS C
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:BFDF4510-EDA1-4098-BCCA-E2C80EAFFA73@.microsoft.com...
>I understand you cannot pass a table variable as input parameter to a
>stored
> proc.
> But what is the alternative in my case?
> Current DB standards limit access of linked servers to the execution of
> stored procs and functions, no cross server joins.
> But I need to get data from the remote server that matches keys on imy
> local
> server. The remote table is too large to bring over to the local server
> in
> its entirety. In order to limit my result set from the remote server, I
> must
> pass it the
> keys of the records I want.
> My issue is how to pass the keys.
> For example, say I have customers and orders databases are on different
> servers. From orders I want to get customer information for 200
> customers.
> Somehow I need to pass 200 custids to customers, execute the query there
> and
> return 200 sets of customer data.
> How do I call a proc on orders and pass 200 custids as a parameter?
>
proc.
But what is the alternative in my case?
Current DB standards limit access of linked servers to the execution of
stored procs and functions, no cross server joins.
But I need to get data from the remote server that matches keys on imy local
server. The remote table is too large to bring over to the local server in
its entirety. In order to limit my result set from the remote server, I must
pass it the
keys of the records I want.
My issue is how to pass the keys.
For example, say I have customers and orders databases are on different
servers. From orders I want to get customer information for 200 customers.
Somehow I need to pass 200 custids to customers, execute the query there and
return 200 sets of customer data.
How do I call a proc on orders and pass 200 custids as a parameter?You can always query on a table of some other database on some other
server by specifying server name, db name, owner name and table name.
Something like this:
SELECT * FROM <Server Name>.<Database Name>.<Owner>.<Table>
If you have any other concern which is not covered here, please write.
I will surely try to help you.
Thanks|||Dave
Table-Valued UDFs --
USE Northwind
GO
IF object_id('dbo.get_cust_orders') IS NOT NULL
DROP FUNCTION dbo.cust_orders
GO
CREATE FUNCTION dbo.get_cust_orders
(
@.custid char(5)
)
RETURNS TABLE
AS
RETURN SELECT
*
FROM
Orders
WHERE
CustomerID = @.custid
GO
SELECT OrderID, OrderDate
FROM get_cust_orders('VINET') AS C
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:BFDF4510-EDA1-4098-BCCA-E2C80EAFFA73@.microsoft.com...
>I understand you cannot pass a table variable as input parameter to a
>stored
> proc.
> But what is the alternative in my case?
> Current DB standards limit access of linked servers to the execution of
> stored procs and functions, no cross server joins.
> But I need to get data from the remote server that matches keys on imy
> local
> server. The remote table is too large to bring over to the local server
> in
> its entirety. In order to limit my result set from the remote server, I
> must
> pass it the
> keys of the records I want.
> My issue is how to pass the keys.
> For example, say I have customers and orders databases are on different
> servers. From orders I want to get customer information for 200
> customers.
> Somehow I need to pass 200 custids to customers, execute the query there
> and
> return 200 sets of customer data.
> How do I call a proc on orders and pass 200 custids as a parameter?
>
Passing table variable as input parameter to stored proc
I understand you cannot pass a table variable as input parameter to a stored
proc.
But what is the alternative in my case?
Current DB standards limit access of linked servers to the execution of
stored procs and functions, no cross server joins.
But I need to get data from the remote server that matches keys on imy local
server. The remote table is too large to bring over to the local server in
its entirety. In order to limit my result set from the remote server, I must
pass it the
keys of the records I want.
My issue is how to pass the keys.
For example, say I have customers and orders databases are on different
servers. From orders I want to get customer information for 200 customers.
Somehow I need to pass 200 custids to customers, execute the query there and
return 200 sets of customer data.
How do I call a proc on orders and pass 200 custids as a parameter?
You can always query on a table of some other database on some other
server by specifying server name, db name, owner name and table name.
Something like this:
SELECT * FROM <Server Name>.<Database Name>.<Owner>.<Table>
If you have any other concern which is not covered here, please write.
I will surely try to help you.
Thanks
|||Dave
Table-Valued UDFs --
USE Northwind
GO
IF object_id('dbo.get_cust_orders') IS NOT NULL
DROP FUNCTION dbo.cust_orders
GO
CREATE FUNCTION dbo.get_cust_orders
(
@.custid char(5)
)
RETURNS TABLE
AS
RETURN SELECT
*
FROM
Orders
WHERE
CustomerID = @.custid
GO
SELECT OrderID, OrderDate
FROM get_cust_orders('VINET') AS C
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:BFDF4510-EDA1-4098-BCCA-E2C80EAFFA73@.microsoft.com...
>I understand you cannot pass a table variable as input parameter to a
>stored
> proc.
> But what is the alternative in my case?
> Current DB standards limit access of linked servers to the execution of
> stored procs and functions, no cross server joins.
> But I need to get data from the remote server that matches keys on imy
> local
> server. The remote table is too large to bring over to the local server
> in
> its entirety. In order to limit my result set from the remote server, I
> must
> pass it the
> keys of the records I want.
> My issue is how to pass the keys.
> For example, say I have customers and orders databases are on different
> servers. From orders I want to get customer information for 200
> customers.
> Somehow I need to pass 200 custids to customers, execute the query there
> and
> return 200 sets of customer data.
> How do I call a proc on orders and pass 200 custids as a parameter?
>
proc.
But what is the alternative in my case?
Current DB standards limit access of linked servers to the execution of
stored procs and functions, no cross server joins.
But I need to get data from the remote server that matches keys on imy local
server. The remote table is too large to bring over to the local server in
its entirety. In order to limit my result set from the remote server, I must
pass it the
keys of the records I want.
My issue is how to pass the keys.
For example, say I have customers and orders databases are on different
servers. From orders I want to get customer information for 200 customers.
Somehow I need to pass 200 custids to customers, execute the query there and
return 200 sets of customer data.
How do I call a proc on orders and pass 200 custids as a parameter?
You can always query on a table of some other database on some other
server by specifying server name, db name, owner name and table name.
Something like this:
SELECT * FROM <Server Name>.<Database Name>.<Owner>.<Table>
If you have any other concern which is not covered here, please write.
I will surely try to help you.
Thanks
|||Dave
Table-Valued UDFs --
USE Northwind
GO
IF object_id('dbo.get_cust_orders') IS NOT NULL
DROP FUNCTION dbo.cust_orders
GO
CREATE FUNCTION dbo.get_cust_orders
(
@.custid char(5)
)
RETURNS TABLE
AS
RETURN SELECT
*
FROM
Orders
WHERE
CustomerID = @.custid
GO
SELECT OrderID, OrderDate
FROM get_cust_orders('VINET') AS C
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:BFDF4510-EDA1-4098-BCCA-E2C80EAFFA73@.microsoft.com...
>I understand you cannot pass a table variable as input parameter to a
>stored
> proc.
> But what is the alternative in my case?
> Current DB standards limit access of linked servers to the execution of
> stored procs and functions, no cross server joins.
> But I need to get data from the remote server that matches keys on imy
> local
> server. The remote table is too large to bring over to the local server
> in
> its entirety. In order to limit my result set from the remote server, I
> must
> pass it the
> keys of the records I want.
> My issue is how to pass the keys.
> For example, say I have customers and orders databases are on different
> servers. From orders I want to get customer information for 200
> customers.
> Somehow I need to pass 200 custids to customers, execute the query there
> and
> return 200 sets of customer data.
> How do I call a proc on orders and pass 200 custids as a parameter?
>
Passing table variable as input parameter to stored proc
I understand you cannot pass a table variable as input parameter to a stored
proc.
But what is the alternative in my case?
Current DB standards limit access of linked servers to the execution of
stored procs and functions, no cross server joins.
But I need to get data from the remote server that matches keys on imy local
server. The remote table is too large to bring over to the local server in
its entirety. In order to limit my result set from the remote server, I must
pass it the
keys of the records I want.
My issue is how to pass the keys.
For example, say I have customers and orders databases are on different
servers. From orders I want to get customer information for 200 customers.
Somehow I need to pass 200 custids to customers, execute the query there and
return 200 sets of customer data.
How do I call a proc on orders and pass 200 custids as a parameter?You can always query on a table of some other database on some other
server by specifying server name, db name, owner name and table name.
Something like this:
SELECT * FROM <Server Name>.<Database Name>.<Owner>.<Table>
If you have any other concern which is not covered here, please write.
I will surely try to help you.
Thanks|||Dave
Table-Valued UDFs --
USE Northwind
GO
IF object_id('dbo.get_cust_orders') IS NOT NULL
DROP FUNCTION dbo.cust_orders
GO
CREATE FUNCTION dbo.get_cust_orders
(
@.custid char(5)
)
RETURNS TABLE
AS
RETURN SELECT
*
FROM
Orders
WHERE
CustomerID = @.custid
GO
SELECT OrderID, OrderDate
FROM get_cust_orders('VINET') AS C
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:BFDF4510-EDA1-4098-BCCA-E2C80EAFFA73@.microsoft.com...
>I understand you cannot pass a table variable as input parameter to a
>stored
> proc.
> But what is the alternative in my case?
> Current DB standards limit access of linked servers to the execution of
> stored procs and functions, no cross server joins.
> But I need to get data from the remote server that matches keys on imy
> local
> server. The remote table is too large to bring over to the local server
> in
> its entirety. In order to limit my result set from the remote server, I
> must
> pass it the
> keys of the records I want.
> My issue is how to pass the keys.
> For example, say I have customers and orders databases are on different
> servers. From orders I want to get customer information for 200
> customers.
> Somehow I need to pass 200 custids to customers, execute the query there
> and
> return 200 sets of customer data.
> How do I call a proc on orders and pass 200 custids as a parameter?
>
proc.
But what is the alternative in my case?
Current DB standards limit access of linked servers to the execution of
stored procs and functions, no cross server joins.
But I need to get data from the remote server that matches keys on imy local
server. The remote table is too large to bring over to the local server in
its entirety. In order to limit my result set from the remote server, I must
pass it the
keys of the records I want.
My issue is how to pass the keys.
For example, say I have customers and orders databases are on different
servers. From orders I want to get customer information for 200 customers.
Somehow I need to pass 200 custids to customers, execute the query there and
return 200 sets of customer data.
How do I call a proc on orders and pass 200 custids as a parameter?You can always query on a table of some other database on some other
server by specifying server name, db name, owner name and table name.
Something like this:
SELECT * FROM <Server Name>.<Database Name>.<Owner>.<Table>
If you have any other concern which is not covered here, please write.
I will surely try to help you.
Thanks|||Dave
Table-Valued UDFs --
USE Northwind
GO
IF object_id('dbo.get_cust_orders') IS NOT NULL
DROP FUNCTION dbo.cust_orders
GO
CREATE FUNCTION dbo.get_cust_orders
(
@.custid char(5)
)
RETURNS TABLE
AS
RETURN SELECT
*
FROM
Orders
WHERE
CustomerID = @.custid
GO
SELECT OrderID, OrderDate
FROM get_cust_orders('VINET') AS C
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:BFDF4510-EDA1-4098-BCCA-E2C80EAFFA73@.microsoft.com...
>I understand you cannot pass a table variable as input parameter to a
>stored
> proc.
> But what is the alternative in my case?
> Current DB standards limit access of linked servers to the execution of
> stored procs and functions, no cross server joins.
> But I need to get data from the remote server that matches keys on imy
> local
> server. The remote table is too large to bring over to the local server
> in
> its entirety. In order to limit my result set from the remote server, I
> must
> pass it the
> keys of the records I want.
> My issue is how to pass the keys.
> For example, say I have customers and orders databases are on different
> servers. From orders I want to get customer information for 200
> customers.
> Somehow I need to pass 200 custids to customers, execute the query there
> and
> return 200 sets of customer data.
> How do I call a proc on orders and pass 200 custids as a parameter?
>
Monday, February 20, 2012
passing table name as parameter for SP - asap
Hi all,
i have SP where i need to pass the tablename and the date as the
parameter
i started like this
CREATE PROC SP_DEL_STG_TAB
@.TABLE_NAME varchar(50) ,
@.PRESENT_REC_LD_DT DATETIME
AS
EXEC ( 'DELETE FROM '+ @.TABLE_NAME +' WHERE LOAD_DT =' +
@.PRESENT_REC_LD_DT)
GO
when i complied i did not find error but during the execution by passing
the parameters using
EXEC SP_DEL_STG_TAB 'STG_UNSUCCESS_LOGINS', '7/3/2004'
it gives an wiered error
Line 1: Incorrect syntax near '3'.
please help me on this as early as possible i am stuck at this point.
thanks in advance
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Hi
Use YYYYMMDD format for dates.
"sushma B V" <bv.sushma@.iflexsolutions.com> wrote in message
news:%23msW6xBFFHA.624@.TK2MSFTNGP15.phx.gbl...
>
> Hi all,
> i have SP where i need to pass the tablename and the date as the
> parameter
> i started like this
> CREATE PROC SP_DEL_STG_TAB
> @.TABLE_NAME varchar(50) ,
> @.PRESENT_REC_LD_DT DATETIME
> AS
> EXEC ( 'DELETE FROM '+ @.TABLE_NAME +' WHERE LOAD_DT =' +
> @.PRESENT_REC_LD_DT)
> GO
> when i complied i did not find error but during the execution by passing
> the parameters using
> EXEC SP_DEL_STG_TAB 'STG_UNSUCCESS_LOGINS', '7/3/2004'
> it gives an wiered error
> Line 1: Incorrect syntax near '3'.
> please help me on this as early as possible i am stuck at this point.
> thanks in advance
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||Try to use a string variable and build the query, then execute it using exec
command
Babu M K
"sushma B V" <bv.sushma@.iflexsolutions.com> wrote in message
news:%23msW6xBFFHA.624@.TK2MSFTNGP15.phx.gbl...
>
> Hi all,
> i have SP where i need to pass the tablename and the date as the
> parameter
> i started like this
> CREATE PROC SP_DEL_STG_TAB
> @.TABLE_NAME varchar(50) ,
> @.PRESENT_REC_LD_DT DATETIME
> AS
> EXEC ( 'DELETE FROM '+ @.TABLE_NAME +' WHERE LOAD_DT =' +
> @.PRESENT_REC_LD_DT)
> GO
> when i complied i did not find error but during the execution by passing
> the parameters using
> EXEC SP_DEL_STG_TAB 'STG_UNSUCCESS_LOGINS', '7/3/2004'
> it gives an wiered error
> Line 1: Incorrect syntax near '3'.
> please help me on this as early as possible i am stuck at this point.
> thanks in advance
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
Passing SMALLDATETIME into a Stored Proc
Hello people,
I am haivng an issue passing a smalldatetime into a stored proc.
The issue is on MSSQL to be valid, you have to enter it as
'2003-10-03 00:00:00'
This makes it into a stored proc with no issues,
but to be valid
I need something like this
select * from FIWORKING where DEALDATE > '@.WINDOW'
I can't seem to get the ' to stick in the stored proc,
is there a way to protect it ??
ChrisGot it to work by passing the datetime into the stored proc using the
below syntax
{ d '2003-31-11' }
Anyone know of another way to pass datetime into a Stored Proc ?
I am haivng an issue passing a smalldatetime into a stored proc.
The issue is on MSSQL to be valid, you have to enter it as
'2003-10-03 00:00:00'
This makes it into a stored proc with no issues,
but to be valid
I need something like this
select * from FIWORKING where DEALDATE > '@.WINDOW'
I can't seem to get the ' to stick in the stored proc,
is there a way to protect it ??
ChrisGot it to work by passing the datetime into the stored proc using the
below syntax
{ d '2003-31-11' }
Anyone know of another way to pass datetime into a Stored Proc ?
Passing results from select into exec
Hi
I'm trying to call a stored proc from within another of my stored
procs, passing the results of a select statement as the parameters -
but it's not working. Here's what I'm trying to do:
CREATE PROCEDURE [MoveRecordsToArchive]
AS
EXEC [ARCHIVEInsert]
(
SELECT
*
FROM
[CURRENT_DATA]
)
where [ARCHIVEInsert] is a stored proc which takes parameters which are
the same as the columns in [CURRENT_DATA].
But I'm getting the following error when trying to run
MoveRecordsToArchive:
"Procedure 'MoveRecordsToArchive' expects parameter '@.ID', which was
not supplied.", where @.ID is the first parameter to [ARCHIVEInsert] and
[ID] is the first column in [CURRENT_DATA].
Am I trying to do something impossible again? If so, how else could I
go about this?You will have to do it like this
declare @.ID' int @.name varchar(50)
select @.id =id,@.name =name
from [CURRENT_DATA] where id = Somevalue
exec ARCHIVEInsert @.id ,@.name
keep in mind you can only use 1 row at a time
You can do this if you need to move more rows
INSERT INTO DATA_ARCHIVE
SELECT * FROM [CURRENT_DATA]
----
--
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/|||Unfortunately, there may well be many rows to move, and I can't just
use your second method (i.e. a straight INSERT) since I need to insert
if the row doesn't exist in the archive table, and update if it does,
which is the process that the ARCHIVEInsert sp follows.
Is there then an easy way to iterate through all the rows in the table
so that I can use your first method?|||The insert
INSERT INTO DATA_ARCHIVE
SELECT * FROM [CURRENT_DATA] c
left join DATA_ARCHIVE d on c.id =d.id
where d.id is null
The update
update d set d.field1 = c.field1,d.field2 = c.field2,etc,etc,etc
FROM [CURRENT_DATA] c
join DATA_ARCHIVE d on c.id =d.id
----
--
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/|||Yeah, that's not quite what I need. As I say, I have the
insert-or-update functionality working, in my ARCHIVEInsert storedproc.
I guess I could duplicate in the MoveRowsToArchive proc, but it seems
a bit silly when I have a perfectly good stored proc to call that
already does what I need.
Thanks anyway.
SQL wrote:
> The insert
> INSERT INTO DATA_ARCHIVE
> SELECT * FROM [CURRENT_DATA] c
> left join DATA_ARCHIVE d on c.id =d.id
> where d.id is null
> The update
> update d set d.field1 = c.field1,d.field2 = c.field2,etc,etc,etc
> FROM [CURRENT_DATA] c
> join DATA_ARCHIVE d on c.id =d.id
> ----
--
> "I sense many useless updates in you... Useless updates lead to
> fragmentation... Fragmentation leads to downtime...Downtime leads to
> suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
> and DBCC DBREINDEX are the force...May the force be with you" --
> http://sqlservercode.blogspot.com/|||"Cathryn Johns" <cjohns@.gmail.com> wrote in message
news:1130510060.880509.220720@.f14g2000cwb.googlegroups.com...
> Yeah, that's not quite what I need. As I say, I have the
> insert-or-update functionality working, in my ARCHIVEInsert storedproc.
> I guess I could duplicate in the MoveRowsToArchive proc, but it seems
> a bit silly when I have a perfectly good stored proc to call that
> already does what I need.
> Thanks anyway.
> SQL wrote:
>
Well, if you want to do it the hard way..
Create a CURSOR in the first stored procedure and pull one row at a time and
call your insert/update spoc in a loop.
Something like:
declare @.ID' int @.name varchar(50)
DECLARE Cur CURSOR FOR
select id, name
from [CURRENT_DATA] where id = Somevalue
OPEN Cur
FETCH NEXT FROM Cur
INTO @.ID, @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
exec ARCHIVEInsert @.id ,@.name
FETCH NEXT FROM Cur
INTO @.ID, @.name
END
CLOSE Cur
DEALLOCATE Cur|||Cathryn Johns (cjohns@.gmail.com) writes:
> Yeah, that's not quite what I need. As I say, I have the
> insert-or-update functionality working, in my ARCHIVEInsert storedproc.
> I guess I could duplicate in the MoveRowsToArchive proc, but it seems
> a bit silly when I have a perfectly good stored proc to call that
> already does what I need.
That's not really right. You have a stored procedure which has the
logic to do this for one single row. Now you need something that performs
the same thing for multiple rows.
You can of coruse iterate over the source table, and move one row at a
time. But, frankly, for simple logic like this, that would be about
criminal. OK, that choice of words may stun you, but if I tell you
that to move 10000 rows, it would take 50 seconds with calling the
stored procedure for each row, and five seconds with the solution
that Denis posted, you may agree. Of course, I just made those numbers
up, but the difference is really that drastic - or even worse, if
proper indexing is not in place.
RDBMS are designed to work with sets of data, and you should try to
this as much as possible.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> Cathryn Johns (cjohns@.gmail.com) writes:
> That's not really right. You have a stored procedure which has the
> logic to do this for one single row. Now you need something that performs
> the same thing for multiple rows.
> You can of coruse iterate over the source table, and move one row at a
> time. But, frankly, for simple logic like this, that would be about
> criminal. OK, that choice of words may stun you, but if I tell you
> that to move 10000 rows, it would take 50 seconds with calling the
> stored procedure for each row, and five seconds with the solution
> that Denis posted, you may agree. Of course, I just made those numbers
> up, but the difference is really that drastic - or even worse, if
> proper indexing is not in place.
> RDBMS are designed to work with sets of data, and you should try to
> this as much as possible.
Okay, I see what you're saying, but please bear with me here because I
know I don't have the right sql mindset :-) - I'm more used to regular
functional programming.
I understand the update & insert as posted by SQL, but what I don't get
is how to determine which rows need to be inserted and which need to be
updated *without* going through each row, one by one. Currently my
table has a uniqueness constraint consisting of several columns, and
what my stored proc does is try to insert (since this will happen
successfully 99% of the time, I try the insert first), then checks
@.@.error and if the code indicates a uniqueness constraint violation, it
updates instead. Maybe this isn't the best way to implement what I'm
trying to accomplish, but either way I still don't see how to do this
using a set-based approach. The cursor approach makes more sense to
me, but I can see that it could be really slow.|||Cathryn,
"I understand the update & insert as posted by SQL, but what I don't
get
is how to determine which rows need to be inserted and which need to be
updated *without* going through each row, one by one"
when you do a join only rows that exist in both tables are returned so
you can do an update
when you do a left join with where d.id is null only the rows that
don't exist in the other table are returned so you can do an update
When you work with SQL you have to think in terms of sets, basically
you have to unlearn what you were taught for VB, Java C# etc etc
A very good book on T-SQL is
The Guru's Guide to Transact-SQL by Ken Henderson
http://www.amazon.com/exec/obidos/t...=glance&s=books
And of course Books On Line
----
--
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/|||Cathryn Johns (cjohns@.gmail.com) writes:
> Okay, I see what you're saying, but please bear with me here because I
> know I don't have the right sql mindset :-) - I'm more used to regular
> functional programming.
Then you have a bit to unlearn. :-)
> I understand the update & insert as posted by SQL, but what I don't get
> is how to determine which rows need to be inserted and which need to be
> updated *without* going through each row, one by one.
UPDATE target
SET col = s.col
FROM target t
JOIN sources s ON t.keycol = s.keycol
INSERT target (...)
SELECT ...
FROM source s
WHERE NOT EXISTS (SELECT *
FROM target t
WHERE t.keycol = s.keycol)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
I'm trying to call a stored proc from within another of my stored
procs, passing the results of a select statement as the parameters -
but it's not working. Here's what I'm trying to do:
CREATE PROCEDURE [MoveRecordsToArchive]
AS
EXEC [ARCHIVEInsert]
(
SELECT
*
FROM
[CURRENT_DATA]
)
where [ARCHIVEInsert] is a stored proc which takes parameters which are
the same as the columns in [CURRENT_DATA].
But I'm getting the following error when trying to run
MoveRecordsToArchive:
"Procedure 'MoveRecordsToArchive' expects parameter '@.ID', which was
not supplied.", where @.ID is the first parameter to [ARCHIVEInsert] and
[ID] is the first column in [CURRENT_DATA].
Am I trying to do something impossible again? If so, how else could I
go about this?You will have to do it like this
declare @.ID' int @.name varchar(50)
select @.id =id,@.name =name
from [CURRENT_DATA] where id = Somevalue
exec ARCHIVEInsert @.id ,@.name
keep in mind you can only use 1 row at a time
You can do this if you need to move more rows
INSERT INTO DATA_ARCHIVE
SELECT * FROM [CURRENT_DATA]
----
--
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/|||Unfortunately, there may well be many rows to move, and I can't just
use your second method (i.e. a straight INSERT) since I need to insert
if the row doesn't exist in the archive table, and update if it does,
which is the process that the ARCHIVEInsert sp follows.
Is there then an easy way to iterate through all the rows in the table
so that I can use your first method?|||The insert
INSERT INTO DATA_ARCHIVE
SELECT * FROM [CURRENT_DATA] c
left join DATA_ARCHIVE d on c.id =d.id
where d.id is null
The update
update d set d.field1 = c.field1,d.field2 = c.field2,etc,etc,etc
FROM [CURRENT_DATA] c
join DATA_ARCHIVE d on c.id =d.id
----
--
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/|||Yeah, that's not quite what I need. As I say, I have the
insert-or-update functionality working, in my ARCHIVEInsert storedproc.
I guess I could duplicate in the MoveRowsToArchive proc, but it seems
a bit silly when I have a perfectly good stored proc to call that
already does what I need.
Thanks anyway.
SQL wrote:
> The insert
> INSERT INTO DATA_ARCHIVE
> SELECT * FROM [CURRENT_DATA] c
> left join DATA_ARCHIVE d on c.id =d.id
> where d.id is null
> The update
> update d set d.field1 = c.field1,d.field2 = c.field2,etc,etc,etc
> FROM [CURRENT_DATA] c
> join DATA_ARCHIVE d on c.id =d.id
> ----
--
> "I sense many useless updates in you... Useless updates lead to
> fragmentation... Fragmentation leads to downtime...Downtime leads to
> suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
> and DBCC DBREINDEX are the force...May the force be with you" --
> http://sqlservercode.blogspot.com/|||"Cathryn Johns" <cjohns@.gmail.com> wrote in message
news:1130510060.880509.220720@.f14g2000cwb.googlegroups.com...
> Yeah, that's not quite what I need. As I say, I have the
> insert-or-update functionality working, in my ARCHIVEInsert storedproc.
> I guess I could duplicate in the MoveRowsToArchive proc, but it seems
> a bit silly when I have a perfectly good stored proc to call that
> already does what I need.
> Thanks anyway.
> SQL wrote:
>
Well, if you want to do it the hard way..
Create a CURSOR in the first stored procedure and pull one row at a time and
call your insert/update spoc in a loop.
Something like:
declare @.ID' int @.name varchar(50)
DECLARE Cur CURSOR FOR
select id, name
from [CURRENT_DATA] where id = Somevalue
OPEN Cur
FETCH NEXT FROM Cur
INTO @.ID, @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
exec ARCHIVEInsert @.id ,@.name
FETCH NEXT FROM Cur
INTO @.ID, @.name
END
CLOSE Cur
DEALLOCATE Cur|||Cathryn Johns (cjohns@.gmail.com) writes:
> Yeah, that's not quite what I need. As I say, I have the
> insert-or-update functionality working, in my ARCHIVEInsert storedproc.
> I guess I could duplicate in the MoveRowsToArchive proc, but it seems
> a bit silly when I have a perfectly good stored proc to call that
> already does what I need.
That's not really right. You have a stored procedure which has the
logic to do this for one single row. Now you need something that performs
the same thing for multiple rows.
You can of coruse iterate over the source table, and move one row at a
time. But, frankly, for simple logic like this, that would be about
criminal. OK, that choice of words may stun you, but if I tell you
that to move 10000 rows, it would take 50 seconds with calling the
stored procedure for each row, and five seconds with the solution
that Denis posted, you may agree. Of course, I just made those numbers
up, but the difference is really that drastic - or even worse, if
proper indexing is not in place.
RDBMS are designed to work with sets of data, and you should try to
this as much as possible.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> Cathryn Johns (cjohns@.gmail.com) writes:
> That's not really right. You have a stored procedure which has the
> logic to do this for one single row. Now you need something that performs
> the same thing for multiple rows.
> You can of coruse iterate over the source table, and move one row at a
> time. But, frankly, for simple logic like this, that would be about
> criminal. OK, that choice of words may stun you, but if I tell you
> that to move 10000 rows, it would take 50 seconds with calling the
> stored procedure for each row, and five seconds with the solution
> that Denis posted, you may agree. Of course, I just made those numbers
> up, but the difference is really that drastic - or even worse, if
> proper indexing is not in place.
> RDBMS are designed to work with sets of data, and you should try to
> this as much as possible.
Okay, I see what you're saying, but please bear with me here because I
know I don't have the right sql mindset :-) - I'm more used to regular
functional programming.
I understand the update & insert as posted by SQL, but what I don't get
is how to determine which rows need to be inserted and which need to be
updated *without* going through each row, one by one. Currently my
table has a uniqueness constraint consisting of several columns, and
what my stored proc does is try to insert (since this will happen
successfully 99% of the time, I try the insert first), then checks
@.@.error and if the code indicates a uniqueness constraint violation, it
updates instead. Maybe this isn't the best way to implement what I'm
trying to accomplish, but either way I still don't see how to do this
using a set-based approach. The cursor approach makes more sense to
me, but I can see that it could be really slow.|||Cathryn,
"I understand the update & insert as posted by SQL, but what I don't
get
is how to determine which rows need to be inserted and which need to be
updated *without* going through each row, one by one"
when you do a join only rows that exist in both tables are returned so
you can do an update
when you do a left join with where d.id is null only the rows that
don't exist in the other table are returned so you can do an update
When you work with SQL you have to think in terms of sets, basically
you have to unlearn what you were taught for VB, Java C# etc etc
A very good book on T-SQL is
The Guru's Guide to Transact-SQL by Ken Henderson
http://www.amazon.com/exec/obidos/t...=glance&s=books
And of course Books On Line
----
--
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/|||Cathryn Johns (cjohns@.gmail.com) writes:
> Okay, I see what you're saying, but please bear with me here because I
> know I don't have the right sql mindset :-) - I'm more used to regular
> functional programming.
Then you have a bit to unlearn. :-)
> I understand the update & insert as posted by SQL, but what I don't get
> is how to determine which rows need to be inserted and which need to be
> updated *without* going through each row, one by one.
UPDATE target
SET col = s.col
FROM target t
JOIN sources s ON t.keycol = s.keycol
INSERT target (...)
SELECT ...
FROM source s
WHERE NOT EXISTS (SELECT *
FROM target t
WHERE t.keycol = s.keycol)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
passing report parameters to an Oracle proc
Hi,
I received an error message after running a report by pushing the Preview
button:
ORA-00972:identifier is too long
ORA-06512:at "SYS.DBMS_UTILITY", line 114
ORA-06512: at line 1
Here is the Oracle proc:
CREATE OR REPLACE PACKAGE BODY MPC IS
PROCEDURE Report_Performance_Main_5 (ip_response_interval IN VARCHAR2,
ip_launch_list IN VARCHAR2,
results_cur
OUT T_RESULT_CURSOR)
Here is the Query string:
"MPC"."Report_Performance_Main_5 :ip_response_interval, :ip_launch_list"
At the same time, in the Parameters tab of the Dataset screen, I also create
two parameters:
Name= :ip_response_interval
Value= =Parameters!ip_response_interval.Value
Name= :ip_launch_list
Vaue= =Parameters!ip_launch_list.Value
However, if I click the generic query design button and just type
"MPC.Report_Performance_Main_5" and click the Run button, it pops out the
"Define Query Parameters" screen. After typing the necessary data and hit the
"OK" button, I am able to see the query result in the buttom pane.
I am confused what's wrong when I hit the Preview button.
Any clues will be very appreciated!
JamesNot sure but I think in the generic query window you can do this:
MPC.Report_Performance_Main_5 :ip_response_interval, :ip_launch_list
It should create the report parameters for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"James" <James@.discussions.microsoft.com> wrote in message
news:88CACBA4-4C5A-47EF-8314-C7AC9860DEC0@.microsoft.com...
> Hi,
> I received an error message after running a report by pushing the Preview
> button:
> ORA-00972:identifier is too long
> ORA-06512:at "SYS.DBMS_UTILITY", line 114
> ORA-06512: at line 1
> Here is the Oracle proc:
> CREATE OR REPLACE PACKAGE BODY MPC IS
> PROCEDURE Report_Performance_Main_5 (ip_response_interval IN VARCHAR2,
> ip_launch_list IN VARCHAR2,
> results_cur
> OUT T_RESULT_CURSOR)
> Here is the Query string:
> "MPC"."Report_Performance_Main_5 :ip_response_interval, :ip_launch_list"
> At the same time, in the Parameters tab of the Dataset screen, I also
> create
> two parameters:
> Name= :ip_response_interval
> Value= =Parameters!ip_response_interval.Value
> Name= :ip_launch_list
> Vaue= =Parameters!ip_launch_list.Value
> However, if I click the generic query design button and just type
> "MPC.Report_Performance_Main_5" and click the Run button, it pops out the
> "Define Query Parameters" screen. After typing the necessary data and hit
> the
> "OK" button, I am able to see the query result in the buttom pane.
> I am confused what's wrong when I hit the Preview button.
> Any clues will be very appreciated!
> James
>|||I did this in the generic query and got the following error:
An error occurred while retrieving the parameters in the query.
ORA-00911: invalid character
ORA-06512: at "SYS.DBMS_UTILITY", line 114
ORA-06512: at line 1
Is there any document about call a Oracle proc?
Thanks,
James
"Bruce L-C [MVP]" wrote:
> Not sure but I think in the generic query window you can do this:
> MPC.Report_Performance_Main_5 :ip_response_interval, :ip_launch_list
> It should create the report parameters for you.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "James" <James@.discussions.microsoft.com> wrote in message
> news:88CACBA4-4C5A-47EF-8314-C7AC9860DEC0@.microsoft.com...
> > Hi,
> >
> > I received an error message after running a report by pushing the Preview
> > button:
> >
> > ORA-00972:identifier is too long
> > ORA-06512:at "SYS.DBMS_UTILITY", line 114
> > ORA-06512: at line 1
> >
> > Here is the Oracle proc:
> >
> > CREATE OR REPLACE PACKAGE BODY MPC IS
> > PROCEDURE Report_Performance_Main_5 (ip_response_interval IN VARCHAR2,
> >
> > ip_launch_list IN VARCHAR2,
> > results_cur
> > OUT T_RESULT_CURSOR)
> >
> > Here is the Query string:
> > "MPC"."Report_Performance_Main_5 :ip_response_interval, :ip_launch_list"
> >
> > At the same time, in the Parameters tab of the Dataset screen, I also
> > create
> > two parameters:
> > Name= :ip_response_interval
> > Value= =Parameters!ip_response_interval.Value
> >
> > Name= :ip_launch_list
> > Vaue= =Parameters!ip_launch_list.Value
> >
> > However, if I click the generic query design button and just type
> > "MPC.Report_Performance_Main_5" and click the Run button, it pops out the
> > "Define Query Parameters" screen. After typing the necessary data and hit
> > the
> > "OK" button, I am able to see the query result in the buttom pane.
> >
> > I am confused what's wrong when I hit the Preview button.
> >
> > Any clues will be very appreciated!
> >
> > James
> >
> >
>
>|||Here is some misc things I've kept around. I haven't used Oracle with RS
(although I did use 8.1.7 extensively in the past).
Oracle has a few unique things going on. First, my recommendation is to use
the generic data designer (2 panes). The button to switch to this is to the
right of the ...
Second, because the development environment was not designed for managed
providers they got tricky with what is used under the covers (hence my
recommendation to use the generic designer). Here is a description from
Robert Bruckner [MSFT].
/Snip
Note: the behavior of PREVIEW in Report Designer is identical to the
ReportServer behavior! However the DATA view in Report Designer is
different for the visual designer: * the visual query designer with 4 panes
will internally always use OleDB providers for verifying and executing
queries directly in "Data" view. (Main reason: the visual query designer
does not work with managed providers). Example: if you choose "Oracle" in
the data source dialog, the Data view has to use the OleDB provider for
Oracle behind the scenes, but Preview and Server will use the managed Oracle
provider. The generic text-based query designer (2 panes) will _always_ use
the data provider you specified.
/End Snip
Just a little background for you. OK, now, from the generic query designer.
He then had this to say about stored procedures:
/Snip
In addition, how do you return the data from your stored procedure? Note:
only an out ref cursor is supported. Please follow the guidelines in the
following article on MSDN (scroll down to the section where it talks about
"Oracle REF CURSORs") on how to design the Oracle stored procedure:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp
To use a stored procedure with regular out parameters, you should either
remove the parameter (if it is possible) or write a little wrapper around
the original stored procedure which checks the result of the out parameter
and just returns the out ref cursor but no out parameter. Finally, in the
generic query designer, just specify the name of the stored procedure
without arguments and the parameters should get detected automatically.
/End Snip
And more from Robert:
/Snip
Managed Oracle provider (named parameters):
select * from table where ename = :parameter
OleDB for Oracle (unnamed parameters):
select * from table where ename = ?
The managed Oracle data provider uses a ':' to mark named parameters
(instead of '@.'); the OleDB provider for Oracle only allows unnamed
parameters (using '?'). The following KB article explains more details:
http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
/End Snip
Hope that helps. Definitely not intuitive but it works.
One last thing. The MS managed provider for Oracle need 8.1.7 or higher (8i)
client installed for it to work.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"James" <James@.discussions.microsoft.com> wrote in message
news:14B75C3F-7959-47B9-A563-EF5436510BFC@.microsoft.com...
>I did this in the generic query and got the following error:
> An error occurred while retrieving the parameters in the query.
> ORA-00911: invalid character
> ORA-06512: at "SYS.DBMS_UTILITY", line 114
> ORA-06512: at line 1
> Is there any document about call a Oracle proc?
> Thanks,
> James
> "Bruce L-C [MVP]" wrote:
>> Not sure but I think in the generic query window you can do this:
>> MPC.Report_Performance_Main_5 :ip_response_interval, :ip_launch_list
>> It should create the report parameters for you.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "James" <James@.discussions.microsoft.com> wrote in message
>> news:88CACBA4-4C5A-47EF-8314-C7AC9860DEC0@.microsoft.com...
>> > Hi,
>> >
>> > I received an error message after running a report by pushing the
>> > Preview
>> > button:
>> >
>> > ORA-00972:identifier is too long
>> > ORA-06512:at "SYS.DBMS_UTILITY", line 114
>> > ORA-06512: at line 1
>> >
>> > Here is the Oracle proc:
>> >
>> > CREATE OR REPLACE PACKAGE BODY MPC IS
>> > PROCEDURE Report_Performance_Main_5 (ip_response_interval IN VARCHAR2,
>> >
>> > ip_launch_list IN VARCHAR2,
>> >
>> > results_cur
>> > OUT T_RESULT_CURSOR)
>> >
>> > Here is the Query string:
>> > "MPC"."Report_Performance_Main_5 :ip_response_interval,
>> > :ip_launch_list"
>> >
>> > At the same time, in the Parameters tab of the Dataset screen, I also
>> > create
>> > two parameters:
>> > Name= :ip_response_interval
>> > Value= =Parameters!ip_response_interval.Value
>> >
>> > Name= :ip_launch_list
>> > Vaue= =Parameters!ip_launch_list.Value
>> >
>> > However, if I click the generic query design button and just type
>> > "MPC.Report_Performance_Main_5" and click the Run button, it pops out
>> > the
>> > "Define Query Parameters" screen. After typing the necessary data and
>> > hit
>> > the
>> > "OK" button, I am able to see the query result in the buttom pane.
>> >
>> > I am confused what's wrong when I hit the Preview button.
>> >
>> > Any clues will be very appreciated!
>> >
>> > James
>> >
>> >
>>|||James:
Any luck making this successful..I am strugling with this issue as well.
Please reply if you have this working,
"James" wrote:
> I did this in the generic query and got the following error:
> An error occurred while retrieving the parameters in the query.
> ORA-00911: invalid character
> ORA-06512: at "SYS.DBMS_UTILITY", line 114
> ORA-06512: at line 1
> Is there any document about call a Oracle proc?
> Thanks,
> James
> "Bruce L-C [MVP]" wrote:
> > Not sure but I think in the generic query window you can do this:
> >
> > MPC.Report_Performance_Main_5 :ip_response_interval, :ip_launch_list
> >
> > It should create the report parameters for you.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "James" <James@.discussions.microsoft.com> wrote in message
> > news:88CACBA4-4C5A-47EF-8314-C7AC9860DEC0@.microsoft.com...
> > > Hi,
> > >
> > > I received an error message after running a report by pushing the Preview
> > > button:
> > >
> > > ORA-00972:identifier is too long
> > > ORA-06512:at "SYS.DBMS_UTILITY", line 114
> > > ORA-06512: at line 1
> > >
> > > Here is the Oracle proc:
> > >
> > > CREATE OR REPLACE PACKAGE BODY MPC IS
> > > PROCEDURE Report_Performance_Main_5 (ip_response_interval IN VARCHAR2,
> > >
> > > ip_launch_list IN VARCHAR2,
> > > results_cur
> > > OUT T_RESULT_CURSOR)
> > >
> > > Here is the Query string:
> > > "MPC"."Report_Performance_Main_5 :ip_response_interval, :ip_launch_list"
> > >
> > > At the same time, in the Parameters tab of the Dataset screen, I also
> > > create
> > > two parameters:
> > > Name= :ip_response_interval
> > > Value= =Parameters!ip_response_interval.Value
> > >
> > > Name= :ip_launch_list
> > > Vaue= =Parameters!ip_launch_list.Value
> > >
> > > However, if I click the generic query design button and just type
> > > "MPC.Report_Performance_Main_5" and click the Run button, it pops out the
> > > "Define Query Parameters" screen. After typing the necessary data and hit
> > > the
> > > "OK" button, I am able to see the query result in the buttom pane.
> > >
> > > I am confused what's wrong when I hit the Preview button.
> > >
> > > Any clues will be very appreciated!
> > >
> > > James
> > >
> > >
> >
> >
> >|||anyone lucky enough to make oracle refcursor working under RS? kindly share
with us, thanks...
"MSOracle" wrote:
> James:
> Any luck making this successful..I am strugling with this issue as well.
> Please reply if you have this working,
> "James" wrote:
> > I did this in the generic query and got the following error:
> > An error occurred while retrieving the parameters in the query.
> > ORA-00911: invalid character
> > ORA-06512: at "SYS.DBMS_UTILITY", line 114
> > ORA-06512: at line 1
> >
> > Is there any document about call a Oracle proc?
> >
> > Thanks,
> >
> > James
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Not sure but I think in the generic query window you can do this:
> > >
> > > MPC.Report_Performance_Main_5 :ip_response_interval, :ip_launch_list
> > >
> > > It should create the report parameters for you.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "James" <James@.discussions.microsoft.com> wrote in message
> > > news:88CACBA4-4C5A-47EF-8314-C7AC9860DEC0@.microsoft.com...
> > > > Hi,
> > > >
> > > > I received an error message after running a report by pushing the Preview
> > > > button:
> > > >
> > > > ORA-00972:identifier is too long
> > > > ORA-06512:at "SYS.DBMS_UTILITY", line 114
> > > > ORA-06512: at line 1
> > > >
> > > > Here is the Oracle proc:
> > > >
> > > > CREATE OR REPLACE PACKAGE BODY MPC IS
> > > > PROCEDURE Report_Performance_Main_5 (ip_response_interval IN VARCHAR2,
> > > >
> > > > ip_launch_list IN VARCHAR2,
> > > > results_cur
> > > > OUT T_RESULT_CURSOR)
> > > >
> > > > Here is the Query string:
> > > > "MPC"."Report_Performance_Main_5 :ip_response_interval, :ip_launch_list"
> > > >
> > > > At the same time, in the Parameters tab of the Dataset screen, I also
> > > > create
> > > > two parameters:
> > > > Name= :ip_response_interval
> > > > Value= =Parameters!ip_response_interval.Value
> > > >
> > > > Name= :ip_launch_list
> > > > Vaue= =Parameters!ip_launch_list.Value
> > > >
> > > > However, if I click the generic query design button and just type
> > > > "MPC.Report_Performance_Main_5" and click the Run button, it pops out the
> > > > "Define Query Parameters" screen. After typing the necessary data and hit
> > > > the
> > > > "OK" button, I am able to see the query result in the buttom pane.
> > > >
> > > > I am confused what's wrong when I hit the Preview button.
> > > >
> > > > Any clues will be very appreciated!
> > > >
> > > > James
> > > >
> > > >
> > >
> > >
> > >|||I have been able to do i just recenly but Only after installing the Oracle
10g client. & using the Orcale provider.
"J" wrote:
> anyone lucky enough to make oracle refcursor working under RS? kindly share
> with us, thanks...
> "MSOracle" wrote:
> > James:
> >
> > Any luck making this successful..I am strugling with this issue as well.
> > Please reply if you have this working,
> >
> > "James" wrote:
> >
> > > I did this in the generic query and got the following error:
> > > An error occurred while retrieving the parameters in the query.
> > > ORA-00911: invalid character
> > > ORA-06512: at "SYS.DBMS_UTILITY", line 114
> > > ORA-06512: at line 1
> > >
> > > Is there any document about call a Oracle proc?
> > >
> > > Thanks,
> > >
> > > James
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > Not sure but I think in the generic query window you can do this:
> > > >
> > > > MPC.Report_Performance_Main_5 :ip_response_interval, :ip_launch_list
> > > >
> > > > It should create the report parameters for you.
> > > >
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "James" <James@.discussions.microsoft.com> wrote in message
> > > > news:88CACBA4-4C5A-47EF-8314-C7AC9860DEC0@.microsoft.com...
> > > > > Hi,
> > > > >
> > > > > I received an error message after running a report by pushing the Preview
> > > > > button:
> > > > >
> > > > > ORA-00972:identifier is too long
> > > > > ORA-06512:at "SYS.DBMS_UTILITY", line 114
> > > > > ORA-06512: at line 1
> > > > >
> > > > > Here is the Oracle proc:
> > > > >
> > > > > CREATE OR REPLACE PACKAGE BODY MPC IS
> > > > > PROCEDURE Report_Performance_Main_5 (ip_response_interval IN VARCHAR2,
> > > > >
> > > > > ip_launch_list IN VARCHAR2,
> > > > > results_cur
> > > > > OUT T_RESULT_CURSOR)
> > > > >
> > > > > Here is the Query string:
> > > > > "MPC"."Report_Performance_Main_5 :ip_response_interval, :ip_launch_list"
> > > > >
> > > > > At the same time, in the Parameters tab of the Dataset screen, I also
> > > > > create
> > > > > two parameters:
> > > > > Name= :ip_response_interval
> > > > > Value= =Parameters!ip_response_interval.Value
> > > > >
> > > > > Name= :ip_launch_list
> > > > > Vaue= =Parameters!ip_launch_list.Value
> > > > >
> > > > > However, if I click the generic query design button and just type
> > > > > "MPC.Report_Performance_Main_5" and click the Run button, it pops out the
> > > > > "Define Query Parameters" screen. After typing the necessary data and hit
> > > > > the
> > > > > "OK" button, I am able to see the query result in the buttom pane.
> > > > >
> > > > > I am confused what's wrong when I hit the Preview button.
> > > > >
> > > > > Any clues will be very appreciated!
> > > > >
> > > > > James
> > > > >
> > > > >
> > > >
> > > >
> > > >
I received an error message after running a report by pushing the Preview
button:
ORA-00972:identifier is too long
ORA-06512:at "SYS.DBMS_UTILITY", line 114
ORA-06512: at line 1
Here is the Oracle proc:
CREATE OR REPLACE PACKAGE BODY MPC IS
PROCEDURE Report_Performance_Main_5 (ip_response_interval IN VARCHAR2,
ip_launch_list IN VARCHAR2,
results_cur
OUT T_RESULT_CURSOR)
Here is the Query string:
"MPC"."Report_Performance_Main_5 :ip_response_interval, :ip_launch_list"
At the same time, in the Parameters tab of the Dataset screen, I also create
two parameters:
Name= :ip_response_interval
Value= =Parameters!ip_response_interval.Value
Name= :ip_launch_list
Vaue= =Parameters!ip_launch_list.Value
However, if I click the generic query design button and just type
"MPC.Report_Performance_Main_5" and click the Run button, it pops out the
"Define Query Parameters" screen. After typing the necessary data and hit the
"OK" button, I am able to see the query result in the buttom pane.
I am confused what's wrong when I hit the Preview button.
Any clues will be very appreciated!
JamesNot sure but I think in the generic query window you can do this:
MPC.Report_Performance_Main_5 :ip_response_interval, :ip_launch_list
It should create the report parameters for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"James" <James@.discussions.microsoft.com> wrote in message
news:88CACBA4-4C5A-47EF-8314-C7AC9860DEC0@.microsoft.com...
> Hi,
> I received an error message after running a report by pushing the Preview
> button:
> ORA-00972:identifier is too long
> ORA-06512:at "SYS.DBMS_UTILITY", line 114
> ORA-06512: at line 1
> Here is the Oracle proc:
> CREATE OR REPLACE PACKAGE BODY MPC IS
> PROCEDURE Report_Performance_Main_5 (ip_response_interval IN VARCHAR2,
> ip_launch_list IN VARCHAR2,
> results_cur
> OUT T_RESULT_CURSOR)
> Here is the Query string:
> "MPC"."Report_Performance_Main_5 :ip_response_interval, :ip_launch_list"
> At the same time, in the Parameters tab of the Dataset screen, I also
> create
> two parameters:
> Name= :ip_response_interval
> Value= =Parameters!ip_response_interval.Value
> Name= :ip_launch_list
> Vaue= =Parameters!ip_launch_list.Value
> However, if I click the generic query design button and just type
> "MPC.Report_Performance_Main_5" and click the Run button, it pops out the
> "Define Query Parameters" screen. After typing the necessary data and hit
> the
> "OK" button, I am able to see the query result in the buttom pane.
> I am confused what's wrong when I hit the Preview button.
> Any clues will be very appreciated!
> James
>|||I did this in the generic query and got the following error:
An error occurred while retrieving the parameters in the query.
ORA-00911: invalid character
ORA-06512: at "SYS.DBMS_UTILITY", line 114
ORA-06512: at line 1
Is there any document about call a Oracle proc?
Thanks,
James
"Bruce L-C [MVP]" wrote:
> Not sure but I think in the generic query window you can do this:
> MPC.Report_Performance_Main_5 :ip_response_interval, :ip_launch_list
> It should create the report parameters for you.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "James" <James@.discussions.microsoft.com> wrote in message
> news:88CACBA4-4C5A-47EF-8314-C7AC9860DEC0@.microsoft.com...
> > Hi,
> >
> > I received an error message after running a report by pushing the Preview
> > button:
> >
> > ORA-00972:identifier is too long
> > ORA-06512:at "SYS.DBMS_UTILITY", line 114
> > ORA-06512: at line 1
> >
> > Here is the Oracle proc:
> >
> > CREATE OR REPLACE PACKAGE BODY MPC IS
> > PROCEDURE Report_Performance_Main_5 (ip_response_interval IN VARCHAR2,
> >
> > ip_launch_list IN VARCHAR2,
> > results_cur
> > OUT T_RESULT_CURSOR)
> >
> > Here is the Query string:
> > "MPC"."Report_Performance_Main_5 :ip_response_interval, :ip_launch_list"
> >
> > At the same time, in the Parameters tab of the Dataset screen, I also
> > create
> > two parameters:
> > Name= :ip_response_interval
> > Value= =Parameters!ip_response_interval.Value
> >
> > Name= :ip_launch_list
> > Vaue= =Parameters!ip_launch_list.Value
> >
> > However, if I click the generic query design button and just type
> > "MPC.Report_Performance_Main_5" and click the Run button, it pops out the
> > "Define Query Parameters" screen. After typing the necessary data and hit
> > the
> > "OK" button, I am able to see the query result in the buttom pane.
> >
> > I am confused what's wrong when I hit the Preview button.
> >
> > Any clues will be very appreciated!
> >
> > James
> >
> >
>
>|||Here is some misc things I've kept around. I haven't used Oracle with RS
(although I did use 8.1.7 extensively in the past).
Oracle has a few unique things going on. First, my recommendation is to use
the generic data designer (2 panes). The button to switch to this is to the
right of the ...
Second, because the development environment was not designed for managed
providers they got tricky with what is used under the covers (hence my
recommendation to use the generic designer). Here is a description from
Robert Bruckner [MSFT].
/Snip
Note: the behavior of PREVIEW in Report Designer is identical to the
ReportServer behavior! However the DATA view in Report Designer is
different for the visual designer: * the visual query designer with 4 panes
will internally always use OleDB providers for verifying and executing
queries directly in "Data" view. (Main reason: the visual query designer
does not work with managed providers). Example: if you choose "Oracle" in
the data source dialog, the Data view has to use the OleDB provider for
Oracle behind the scenes, but Preview and Server will use the managed Oracle
provider. The generic text-based query designer (2 panes) will _always_ use
the data provider you specified.
/End Snip
Just a little background for you. OK, now, from the generic query designer.
He then had this to say about stored procedures:
/Snip
In addition, how do you return the data from your stored procedure? Note:
only an out ref cursor is supported. Please follow the guidelines in the
following article on MSDN (scroll down to the section where it talks about
"Oracle REF CURSORs") on how to design the Oracle stored procedure:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp
To use a stored procedure with regular out parameters, you should either
remove the parameter (if it is possible) or write a little wrapper around
the original stored procedure which checks the result of the out parameter
and just returns the out ref cursor but no out parameter. Finally, in the
generic query designer, just specify the name of the stored procedure
without arguments and the parameters should get detected automatically.
/End Snip
And more from Robert:
/Snip
Managed Oracle provider (named parameters):
select * from table where ename = :parameter
OleDB for Oracle (unnamed parameters):
select * from table where ename = ?
The managed Oracle data provider uses a ':' to mark named parameters
(instead of '@.'); the OleDB provider for Oracle only allows unnamed
parameters (using '?'). The following KB article explains more details:
http://support.microsoft.com/default.aspx?scid=kb;en-us;834305
/End Snip
Hope that helps. Definitely not intuitive but it works.
One last thing. The MS managed provider for Oracle need 8.1.7 or higher (8i)
client installed for it to work.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"James" <James@.discussions.microsoft.com> wrote in message
news:14B75C3F-7959-47B9-A563-EF5436510BFC@.microsoft.com...
>I did this in the generic query and got the following error:
> An error occurred while retrieving the parameters in the query.
> ORA-00911: invalid character
> ORA-06512: at "SYS.DBMS_UTILITY", line 114
> ORA-06512: at line 1
> Is there any document about call a Oracle proc?
> Thanks,
> James
> "Bruce L-C [MVP]" wrote:
>> Not sure but I think in the generic query window you can do this:
>> MPC.Report_Performance_Main_5 :ip_response_interval, :ip_launch_list
>> It should create the report parameters for you.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "James" <James@.discussions.microsoft.com> wrote in message
>> news:88CACBA4-4C5A-47EF-8314-C7AC9860DEC0@.microsoft.com...
>> > Hi,
>> >
>> > I received an error message after running a report by pushing the
>> > Preview
>> > button:
>> >
>> > ORA-00972:identifier is too long
>> > ORA-06512:at "SYS.DBMS_UTILITY", line 114
>> > ORA-06512: at line 1
>> >
>> > Here is the Oracle proc:
>> >
>> > CREATE OR REPLACE PACKAGE BODY MPC IS
>> > PROCEDURE Report_Performance_Main_5 (ip_response_interval IN VARCHAR2,
>> >
>> > ip_launch_list IN VARCHAR2,
>> >
>> > results_cur
>> > OUT T_RESULT_CURSOR)
>> >
>> > Here is the Query string:
>> > "MPC"."Report_Performance_Main_5 :ip_response_interval,
>> > :ip_launch_list"
>> >
>> > At the same time, in the Parameters tab of the Dataset screen, I also
>> > create
>> > two parameters:
>> > Name= :ip_response_interval
>> > Value= =Parameters!ip_response_interval.Value
>> >
>> > Name= :ip_launch_list
>> > Vaue= =Parameters!ip_launch_list.Value
>> >
>> > However, if I click the generic query design button and just type
>> > "MPC.Report_Performance_Main_5" and click the Run button, it pops out
>> > the
>> > "Define Query Parameters" screen. After typing the necessary data and
>> > hit
>> > the
>> > "OK" button, I am able to see the query result in the buttom pane.
>> >
>> > I am confused what's wrong when I hit the Preview button.
>> >
>> > Any clues will be very appreciated!
>> >
>> > James
>> >
>> >
>>|||James:
Any luck making this successful..I am strugling with this issue as well.
Please reply if you have this working,
"James" wrote:
> I did this in the generic query and got the following error:
> An error occurred while retrieving the parameters in the query.
> ORA-00911: invalid character
> ORA-06512: at "SYS.DBMS_UTILITY", line 114
> ORA-06512: at line 1
> Is there any document about call a Oracle proc?
> Thanks,
> James
> "Bruce L-C [MVP]" wrote:
> > Not sure but I think in the generic query window you can do this:
> >
> > MPC.Report_Performance_Main_5 :ip_response_interval, :ip_launch_list
> >
> > It should create the report parameters for you.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "James" <James@.discussions.microsoft.com> wrote in message
> > news:88CACBA4-4C5A-47EF-8314-C7AC9860DEC0@.microsoft.com...
> > > Hi,
> > >
> > > I received an error message after running a report by pushing the Preview
> > > button:
> > >
> > > ORA-00972:identifier is too long
> > > ORA-06512:at "SYS.DBMS_UTILITY", line 114
> > > ORA-06512: at line 1
> > >
> > > Here is the Oracle proc:
> > >
> > > CREATE OR REPLACE PACKAGE BODY MPC IS
> > > PROCEDURE Report_Performance_Main_5 (ip_response_interval IN VARCHAR2,
> > >
> > > ip_launch_list IN VARCHAR2,
> > > results_cur
> > > OUT T_RESULT_CURSOR)
> > >
> > > Here is the Query string:
> > > "MPC"."Report_Performance_Main_5 :ip_response_interval, :ip_launch_list"
> > >
> > > At the same time, in the Parameters tab of the Dataset screen, I also
> > > create
> > > two parameters:
> > > Name= :ip_response_interval
> > > Value= =Parameters!ip_response_interval.Value
> > >
> > > Name= :ip_launch_list
> > > Vaue= =Parameters!ip_launch_list.Value
> > >
> > > However, if I click the generic query design button and just type
> > > "MPC.Report_Performance_Main_5" and click the Run button, it pops out the
> > > "Define Query Parameters" screen. After typing the necessary data and hit
> > > the
> > > "OK" button, I am able to see the query result in the buttom pane.
> > >
> > > I am confused what's wrong when I hit the Preview button.
> > >
> > > Any clues will be very appreciated!
> > >
> > > James
> > >
> > >
> >
> >
> >|||anyone lucky enough to make oracle refcursor working under RS? kindly share
with us, thanks...
"MSOracle" wrote:
> James:
> Any luck making this successful..I am strugling with this issue as well.
> Please reply if you have this working,
> "James" wrote:
> > I did this in the generic query and got the following error:
> > An error occurred while retrieving the parameters in the query.
> > ORA-00911: invalid character
> > ORA-06512: at "SYS.DBMS_UTILITY", line 114
> > ORA-06512: at line 1
> >
> > Is there any document about call a Oracle proc?
> >
> > Thanks,
> >
> > James
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Not sure but I think in the generic query window you can do this:
> > >
> > > MPC.Report_Performance_Main_5 :ip_response_interval, :ip_launch_list
> > >
> > > It should create the report parameters for you.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "James" <James@.discussions.microsoft.com> wrote in message
> > > news:88CACBA4-4C5A-47EF-8314-C7AC9860DEC0@.microsoft.com...
> > > > Hi,
> > > >
> > > > I received an error message after running a report by pushing the Preview
> > > > button:
> > > >
> > > > ORA-00972:identifier is too long
> > > > ORA-06512:at "SYS.DBMS_UTILITY", line 114
> > > > ORA-06512: at line 1
> > > >
> > > > Here is the Oracle proc:
> > > >
> > > > CREATE OR REPLACE PACKAGE BODY MPC IS
> > > > PROCEDURE Report_Performance_Main_5 (ip_response_interval IN VARCHAR2,
> > > >
> > > > ip_launch_list IN VARCHAR2,
> > > > results_cur
> > > > OUT T_RESULT_CURSOR)
> > > >
> > > > Here is the Query string:
> > > > "MPC"."Report_Performance_Main_5 :ip_response_interval, :ip_launch_list"
> > > >
> > > > At the same time, in the Parameters tab of the Dataset screen, I also
> > > > create
> > > > two parameters:
> > > > Name= :ip_response_interval
> > > > Value= =Parameters!ip_response_interval.Value
> > > >
> > > > Name= :ip_launch_list
> > > > Vaue= =Parameters!ip_launch_list.Value
> > > >
> > > > However, if I click the generic query design button and just type
> > > > "MPC.Report_Performance_Main_5" and click the Run button, it pops out the
> > > > "Define Query Parameters" screen. After typing the necessary data and hit
> > > > the
> > > > "OK" button, I am able to see the query result in the buttom pane.
> > > >
> > > > I am confused what's wrong when I hit the Preview button.
> > > >
> > > > Any clues will be very appreciated!
> > > >
> > > > James
> > > >
> > > >
> > >
> > >
> > >|||I have been able to do i just recenly but Only after installing the Oracle
10g client. & using the Orcale provider.
"J" wrote:
> anyone lucky enough to make oracle refcursor working under RS? kindly share
> with us, thanks...
> "MSOracle" wrote:
> > James:
> >
> > Any luck making this successful..I am strugling with this issue as well.
> > Please reply if you have this working,
> >
> > "James" wrote:
> >
> > > I did this in the generic query and got the following error:
> > > An error occurred while retrieving the parameters in the query.
> > > ORA-00911: invalid character
> > > ORA-06512: at "SYS.DBMS_UTILITY", line 114
> > > ORA-06512: at line 1
> > >
> > > Is there any document about call a Oracle proc?
> > >
> > > Thanks,
> > >
> > > James
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > Not sure but I think in the generic query window you can do this:
> > > >
> > > > MPC.Report_Performance_Main_5 :ip_response_interval, :ip_launch_list
> > > >
> > > > It should create the report parameters for you.
> > > >
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "James" <James@.discussions.microsoft.com> wrote in message
> > > > news:88CACBA4-4C5A-47EF-8314-C7AC9860DEC0@.microsoft.com...
> > > > > Hi,
> > > > >
> > > > > I received an error message after running a report by pushing the Preview
> > > > > button:
> > > > >
> > > > > ORA-00972:identifier is too long
> > > > > ORA-06512:at "SYS.DBMS_UTILITY", line 114
> > > > > ORA-06512: at line 1
> > > > >
> > > > > Here is the Oracle proc:
> > > > >
> > > > > CREATE OR REPLACE PACKAGE BODY MPC IS
> > > > > PROCEDURE Report_Performance_Main_5 (ip_response_interval IN VARCHAR2,
> > > > >
> > > > > ip_launch_list IN VARCHAR2,
> > > > > results_cur
> > > > > OUT T_RESULT_CURSOR)
> > > > >
> > > > > Here is the Query string:
> > > > > "MPC"."Report_Performance_Main_5 :ip_response_interval, :ip_launch_list"
> > > > >
> > > > > At the same time, in the Parameters tab of the Dataset screen, I also
> > > > > create
> > > > > two parameters:
> > > > > Name= :ip_response_interval
> > > > > Value= =Parameters!ip_response_interval.Value
> > > > >
> > > > > Name= :ip_launch_list
> > > > > Vaue= =Parameters!ip_launch_list.Value
> > > > >
> > > > > However, if I click the generic query design button and just type
> > > > > "MPC.Report_Performance_Main_5" and click the Run button, it pops out the
> > > > > "Define Query Parameters" screen. After typing the necessary data and hit
> > > > > the
> > > > > "OK" button, I am able to see the query result in the buttom pane.
> > > > >
> > > > > I am confused what's wrong when I hit the Preview button.
> > > > >
> > > > > Any clues will be very appreciated!
> > > > >
> > > > > James
> > > > >
> > > > >
> > > >
> > > >
> > > >
Subscribe to:
Posts (Atom)