Does This Call For A Cursor? How?

Dec 1, 2006

Hi, sorry I am kind of stuck how to do this correctly.
The following is my problem statement:

There are three tables: TAB1 TAB2 TAB3

There is a 1 to many relationship between TAB2 and TAB1.

For every row in TAB2 that has a match in TAB1, they are related via
TAB2.col3 = TAB1.col2

I want to delete all those rows in both tables that match, but I have to
delete those rows in TAB1 first because of dependency constraints.
The primary key for TAB2.col1

Therefore I need to have a select block first and store it somewhere(?)

select
TAB2.col1
from
TAB2
where
TAB2.col3 IN
(select
TAB1.col2
from
TAB1
where
TAB1.col1 IN
(select
TAB3.col1
from
TAB3))



Then I delete all those rows in TAB1 that has a match in TAB3

delete from TAB1
where
TAB1.col1 IN
(select
TAB3.col1
from
TAB3))

now i delete those rows in TAB2 from the result set of the first sql block.

thanks for any help.

View 6 Replies


ADVERTISEMENT

How To Call A Procedure While Declaring A Cursor

Apr 30, 2004

HI,
WHILE DECLARING A CURSOR TO SELECT RECORDS FROM A TABLE WE NORMALLY WRITE :-

DECLARE CUR_NAME CURSOR
FOR SELECT * FROM CLEANCUSTOMER

BUT SAY, IF I HAVE WRITTEN A SIMPLE PROCEDURE CALLED AS MY_PROC :-

CREATE PROCEDURE MY_PROC
AS
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CLEANCUSTOMER A
INNER JOIN TRCUSTOMERPREFERENCE03JULY B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
ORDER BY B.INTPREFERENCEID

WHICH IS RUNNING FINE AND GIVING ME THE REQUIRED DATA WHILE EXECUTING THE PROCEDURE :-

EXEC MY_PROC

BUT IF I WANT TO CALL THIS PROCEDURE MY_PROC WHILE DECLARING A CURSOR :-

I AM USING :-

DECLARE CHK_CUR CURSOR
FOR SELECT * FROM MY_PROC

WHICH IS GIVING AN ERROR "Invalid object name 'MY_PROC'."


AND IF I USE :-

DECLARE CHK_CUR CURSOR
FOR EXEC MY_PROC

WHICH IS GIVING AN ERROR "Incorrect syntax near the keyword 'EXEC'".


AND IF I USE :-

DECLARE CHK_CUR CURSOR
FOR CALL MY_PROC

WHICH IS GIVING AN ERROR "Incorrect syntax near 'CALL'. "

IS THERE ANY WAY BY WHICH I CAN FETCH RECORDS FROM THE STORED PROCEDURE?
HOW DO I DECLARE THE PROCEDURE WHILE WRITING THE CURSOR
PLS HELP.

I NEED THIS URGENTLY, I HAVE TO USE THE CURSOR TO FETCH THE RECORDS FROM THE SP,THAT'S HOW THEY WANT IT.I CAN'T HELP IT AND I DON'T KNOW HOW

THANKS

View 14 Replies View Related

How To Call A Stored Procedure In T-SQL And Pass It To A Cursor

Dec 2, 2007

Hi,

I have a kind of problem. In SQL Server I have a stored procedure ressembling this:




Code Block
ALTER PROCEDURE procedure1
(

@param int
)

SELECT * FROM table WHERE param = @param




Now I want to call this procedure and pass it to a cursor. We all know you can do this:



Code Block

DELCARE cursor1 CURSOR for
SELECT * FROM table WHERE param = @param



.. , but I want something like this:




Code Block
DECLARE cursor1 CURSOR for
EXEC procedure1 @param




Is it possible? I could solve it in another, but then I have to connect 2x to the database, which is less performant.

I have also tried something like this:




Code Block
ALTER PROCEDURE procedure1
(

@param int
)
SELECT @test = id FROM table WHERE param = @param
RETURN @test

ALTER PROCEDURE procedure2
(

@param int
)
DECLARE @var varchar(100)
EXEC @var = procedure1 @param




But then it returns always 0.

So is there a way to pass a procedure's select to a cursor?

Thanks in advance

Stevevil0

View 1 Replies View Related

SQL Server 2012 :: Call Stored Proc Once Per Each Row Of A Table Without Using CURSOR

Jul 10, 2014

I have a situation where I need to call a stored procedure once per each row of table (with some of the columns of each row was its parameters). I was wondering how I can do this without having to use cursors.

Here are my simulated procs...

Main Stored Procedure: This will be called once per each row of some table.

-- All this proc does is, prints out the list of parameters that are passed to it.

CREATE PROCEDURE dbo.MyMainStoredProc (
@IDINT,
@NameVARCHAR (200),
@SessionIDINT
)
AS
BEGIN

[Code] ....

Here is a sample call to the out proc...

EXEC dbo.MyOuterStoredProc @SessionID = 123

In my code above for "MyOuterStoredProc", I managed to avoid using cursors and was able to frame a string that contains myltiple EXEC statements. At the end of the proc, I am using sp_executesql to run this string (of multipl sp calls). However, it has a limitation in terms of string length for NVARCHAR. Besides, I am not very sure if this is an efficient way...just managed to hack something to make it work.

View 9 Replies View Related

I Just Want One Entry For Each Call, With SLA Status 'Breach' If Any Of The Stages For The Call Were Out Of SLA.

Mar 19, 2008

Hi,

I am producing a php report using SQL queries to show the SLA status of our calls. Each call has response, fix & completion targets. If any of these targets are breached, the whole SLA status is set as 'Breach'.

The results table should look like the one below:





CallRef.

Description

Severity



ProblemRef

Logged
Date

Call
Status

SLA Status



C0001

Approval for PO€™s not received

2



DGE0014

05-01-06 14:48

Resolved

Breach


C0002

PO€™s not published

2



DGE0014

06-01-06 10:21

Resolved

OK


C0003

Approval for PO€™s not received from Siebel.

2



n/a

05-01-06 14:48

Investigating

OK



















Whereas I can pick the results for the first 6 columns from my Select query, the 'SLA Status' column requires the following calculation:

if (due_date < completed_date)
{ sla_status = 'OK';
}
else sla_status = 'Breach';

The Select statement in my query is looking like this...

Select Distinct CallRef, Description, Severity, ProblemRef, Logdate, Status, Due_date, Completed_date;

The problem is that my query is returning multiple entries for each stage of the call (see below), whereas I just want one entry for each call, with SLA status 'Breach' if any of the stages for the call were out of SLA.






CallRef.

Description

Severity



ProblemRef

Logged
Date

Call
Status

SLA Status



C0001

Approval for PO€™s not received

2



DGE0014

05-01-06 14:48

Resolved

Breach


C0001

Approval for PO€™s not received

2



DGE0014

05-01-06 14:48

Resolved

OK


C0001

Approval for PO€™s not received

2



DGE0014

05-01-06 14:48

Resolved

Breach



















Any help will be much much appreciated, this issue has been bothering me for some time now!!!




View 7 Replies View Related

Transact SQL :: STATIC Defines A Cursor That Makes Temporary Copy Of Data To Be Used By Cursor

Aug 12, 2015

In MSDN file I read about static cursor

STATIC
Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in
tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications

It say's that modifications is not allowed in the static cursor. I have a  questions regarding that

Static Cursor
declare ll cursor global static
            for select  name, salary from ag
  open ll
             fetch from ll
 
              while @@FETCH_STATUS=0
               fetch from ll
                update ag set salary=200 where 1=1
 
   close ll
deallocate ll

In "AG" table, "SALARY" was 100 for all the entries. When I run the Cursor, it showed the salary value as "100" correctly.After the cursor was closed, I run the query select * from AG.But the result had updated to salary 200 as given in the cursor. file says  modifications is not allowed in the static cursor.But I am able to update the data using static cursor.

View 3 Replies View Related

Dynamic Cursor Versus Forward Only Cursor Gives Poor Performance

Jul 20, 2005

Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin

View 1 Replies View Related

Could Not Complete Cursor Operation Because The Set Options Have Changed Since The Cursor Was Declared.

Sep 20, 2007

I'm trying to implement a sp_MSforeachsp howvever when I call sp_MSforeach_worker
I get the following error can you please explain this problem to me so I can over come the issue.


Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31

Could not complete cursor operation because the set options have changed since the cursor was declared.

Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 32

Could not complete cursor operation because the set options have changed since the cursor was declared.

Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153

Cursor is not open.

here is the stored procedure:


Alter PROCEDURE [dbo].[sp_MSforeachsp]

@command1 nvarchar(2000)

, @replacechar nchar(1) = N'?'

, @command2 nvarchar(2000) = null

, @command3 nvarchar(2000) = null

, @whereand nvarchar(2000) = null

, @precommand nvarchar(2000) = null

, @postcommand nvarchar(2000) = null

AS

/* This procedure belongs in the "master" database so it is acessible to all databases */

/* This proc returns one or more rows for each stored procedure */

/* @precommand and @postcommand may be used to force a single result set via a temp table. */

declare @retval int

if (@precommand is not null) EXECUTE(@precommand)

/* Create the select */

EXECUTE(N'declare hCForEachTable cursor global for

SELECT QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_TYPE = ''PROCEDURE''

AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)), ''IsMSShipped'') = 0 '

+ @whereand)

select @retval = @@error

if (@retval = 0)

EXECUTE @retval = [dbo].sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0

if (@retval = 0 and @postcommand is not null)

EXECUTE(@postcommand)

RETURN @retval



GO


example useage:


EXEC sp_MSforeachsp @command1="PRINT '?' GRANT EXECUTE ON ? TO [superuser]"

GO

View 7 Replies View Related

Join Cursor With Table Outside Of Cursor

Sep 25, 2007

part 1

Declare @SQLCMD varchar(5000)
DECLARE @DBNAME VARCHAR (5000)

DECLARE DBCur CURSOR FOR
SELECT U_OB_DB FROM [@OB_TB04_COMPDATA]

OPEN DBCur
FETCH NEXT FROM DBCur INTO @DBNAME


WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @SQLCMD = 'SELECT T0.CARDCODE, T0.U_OB_TID AS TRANSID, T0.DOCNUM AS INV_NO, ' +
+ 'T0.DOCDATE AS INV_DATE, T0.DOCTOTAL AS INV_AMT, T0.U_OB_DONO AS DONO ' +
+ 'FROM ' + @DBNAME + '.dbo.OINV T0 WHERE T0.U_OB_TID IS NOT NULL'
EXEC(@SQLCMD)
PRINT @SQLCMD
FETCH NEXT FROM DBCur INTO @DBNAME

END

CLOSE DBCur
DEALLOCATE DBCur


Part 2

SELECT
T4.U_OB_PCOMP AS PARENTCOMP, T0.CARDCODE, T0.CARDNAME, ISNULL(T0.U_OB_TID,'') AS TRANSID, T0.DOCNUM AS SONO, T0.DOCDATE AS SODATE,
SUM(T1.QUANTITY) AS SOQTY, T0.DOCTOTAL - T0.TOTALEXPNS AS SO_AMT, T3.DOCNUM AS DONO, T3.DOCDATE AS DO_DATE,
SUM(T2.QUANTITY) AS DOQTY, T3.DOCTOTAL - T3.TOTALEXPNS AS DO_AMT
INTO #MAIN
FROM
ORDR T0
JOIN RDR1 T1 ON T0.DOCENTRY = T1.DOCENTRY
LEFT JOIN DLN1 T2 ON T1.DOCENTRY = T2.BASEENTRY AND T1.LINENUM = T2.BASELINE AND T2.BASETYPE = T0.OBJTYPE
LEFT JOIN ODLN T3 ON T2.DOCENTRY = T3.DOCENTRY
LEFT JOIN OCRD T4 ON T0.CARDCODE = T4.CARDCODE
WHERE ISNULL(T0.U_OB_TID,0) <> 0
GROUP BY T4.U_OB_PCOMP, T0.CARDCODE,T0.CARDNAME, T0.U_OB_TID, T0.DOCNUM, T0.DOCDATE, T3.DOCNUM, T3.DOCDATE, T0.DOCTOTAL, T3.DOCTOTAL, T3.TOTALEXPNS, T0.TOTALEXPNS


my question is,
how to join the part 1 n part 2?
is there posibility?

View 1 Replies View Related

Cursor Inside A Cursor

Oct 5, 2004

I'm new to cursors, and I'm not sure what's wrong with this code, it run for ever and when I stop it I get cursor open errors




declare Q cursor for
select systudentid from satrans


declare @id int

open Q
fetch next from Q into @id
while @@fetch_status = 0
begin

declare c cursor for

Select
b.ssn,
SaTrans.SyStudentID,
satrans.date,
satrans.type,
SaTrans.SyCampusID,
Amount = Case SaTrans.Type
When 'P' Then SaTrans.Amount * -1
When 'C' Then SaTrans.Amount * -1
Else SaTrans.Amount END

From SaTrans , systudent b where satrans.systudentid = b.systudentid

and satrans.systudentid = @id




declare @arbalance money, @type varchar, @ssn varchar, @amount money, @systudentid int, @transdate datetime, @sycampusid int, @before money

set @arbalance = 0
open c
fetch next from c into @ssn, @systudentid, @transdate, @type, @sycampusid, @amount

while @@fetch_status = 0
begin

set @arbalance = @arbalance + @amount
set @before = @arbalance -@amount

insert c2000_utility1..tempbalhistory1
select @systudentid systudentid, @sycampusid sycampusid, @transdate transdate, @amount amount, @type type, @arbalance Arbalance, @before BeforeBalance
where( convert (int,@amount) <= -50
or @amount * -1 > @before * .02)
and @type = 'P'




fetch next from c into @ssn, @systudentid, @transdate, @type, @sycampusid, @amount
end
close c
deallocate c
fetch next from Q into @id

end
close Q
deallocate Q


select * from c2000_utility1..tempbalhistory1
truncate table c2000_utility1..tempbalhistory1

View 1 Replies View Related

Client Side Cursor Vs Sever Side Cursor?

Jul 20, 2005

I having a difficult time here trying to figure out what to do here.I need a way to scroll through a recordset and display the resultswith both forward and backward movement on a web page(PHP usingADO/COM)..I know that if I use a client side cursor all the records get shovedto the client everytime that stored procedure is executed..if thisdatabase grows big wont that be an issue?..I know that I can set up a server side cursor that will only send therecord I need to the front end but..Ive been reading around and a lot of people have been saying never touse a server side cursor because of peformance issues.So i guess im weighing network performance needs with the client sidecursor vs server performance with the server side cursor..I am reallyconfused..which one should I use?-Jim

View 1 Replies View Related

Getting One SP To Call Other SPs

Jan 8, 2008

I've written a T-SQL stored procedure that I want to call other stored procedures.  The opening part of it looks like this:set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[spArchive]   @dtArchiveBefore DateTimeASEXEC spArchiveAHD @dtArchiveBeforeGOEXEC spArchiveContract_History @dtArchiveBeforeGOEXEC spArchiveContracts @dtArchiveBeforeGO  But I'm getting this error repeatedly: Msg 137, Level 15, State 2, Line 2  Must declare the scalar variable "@dtArchiveBefore".I've searched for this term but can't figure out what's wrong with my code.Any ideas?Robert W. 

View 4 Replies View Related

Call Dll

Aug 27, 2001

Hi, There,

Do you know when should I use extend sp to call a dll file, and when should I use sp_OACreate to call dll. It seems only dll created by C++ can be called from extend sp, how about VB created dll?

Thank you very much

Tony

View 1 Replies View Related

Call A SP From Another SP

Jun 23, 2008

MS SQL 2005
I developed several SP that update tables
If I execute them one by one in the SQL Sever Management Studio, they work ok

Now,
I want to execute them all inside another SP
I wrote it (attached code)
Execute it and give me a message that runs successfully but that is not true
May anyone of You tell me the right syntax to do it?

[Code]
USE REPORTES
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATEPROCEDURE dbo.Astral_sp_Acumula
AS
--
EXECUTE [REPORTES].[dbo].[Astral_sp_AcCpasArtC]
GO
EXECUTE [REPORTES].[dbo].[Astral_sp_AcDevArtC]
GO
EXECUTE [REPORTES].[dbo].[Astral_sp_AcVtasArtV]
GO
-- More sp executions
[Code]

Thanks

JG

View 2 Replies View Related

Sql Call

Apr 3, 2006

i have made a .sql file in which i want to call another .sql file
plz tell me the syntax for it

View 5 Replies View Related

Want To Call SP From Another Sp

Sep 21, 2006

Hi all,
I have 2 tables like that
CREATE TABLE tab1 (
id int IDENTITY (1, 1) NOT NULL ,
master varchar (50) NULL
) ON PRIMARY
GO

CREATE TABLE tab2 (
id int IDENTITY (1, 1) NOT NULL ,
student_name varchar (50) NULL ,
master_id int NULL
)

Now i want to create a Sp
which is

create proc inner_sp(@s1 varchar(50))
as
insert into tab1(master) values (@s1)


and i want to call it from another sp

create proc outer_sp2(@s2 varchar(50),@s1 varchar(50))
as
declare @r int
exec inner_sp @s1
set @r=Scope_Identity()
insert into tab2(student_name,master_id) values(@s2,@r)

exec outer_sp2 'Vivek1','Test'


But in table tab2 master_id column contain NULL value....
Please fix it...
Ujjwal

View 5 Replies View Related

Call SQL Job In ASP.NET Application

Jun 27, 2006

is it possible to call a SQL job from an ASP.NET application?  if so, how? I'm using VS.net2003 and am programming in C#I've been searching for a while, but haven't found anything.  It doesn't help that one of my keywords when searching is "job(s)."

View 1 Replies View Related

Can I Call A Web Service From SQL??

Sep 14, 2006

I have two databases with two identical tables in seperate physical locations. I want database B tables to be updated automatically when database A tables change. Is there a way to call a web service from SQL to make this happen? Or is there a better way to do this? I would really like it to get the rows that were modified and then copy only those rows to the other database tables. If anyone knows if this can be done please let me know. Thank you.   

View 4 Replies View Related

XML Import Do Not Call

Dec 18, 2003

Well I seem to be a gomer when trying to understand XML. Could some one please help me? I am trying to write a script or DTS package that will read 4 XML files (Do not call list) compare each phone number to the numbers I have in my DB and if there is a match put the contactID in to another table. Or at the very least get the XML phone numbers into the database. This is what the XML doc looks like

- <list type="full" level="state" val="AL">
- <ac val="205">
<ph val="2025896" />
<ph val="2061994" />
<ph val="2061995" />
<ph val="2062028" />

View 3 Replies View Related

How Do I Call A Udf For Asp.net/ Vb.net Code?

Apr 16, 2005

can someone point me in the right direction?  I know how to call a stored procedure, but I can't seem to find code examples on how to call user defined functions.

View 2 Replies View Related

Can I Call A DTS Package From An SP??

Jan 4, 2000

Hi SQL Colleagues:

Is it possible for me to call a DTS package from within a stored procedure? If it is not possible to do so directly, would I at least be able to call the package through a job?

Thanks!!!
Don

View 1 Replies View Related

Problem When I Call A C++ DLL

Aug 23, 2000

Hello,

Somebody in my society has realised a function logwrite in a DLL logfil32.dll
With Sybase we can use it without problem.

It write in a file for example 'c:logmetrixloglogmetrix.txt' the text 'test logwrite'

I add logwrite in the extended stored procedures of mzster

and i tried to do :

execute master..logwrite 'c:logmetrixloglogmetrix.txt','test logwrite'

In the file logmetrix.txt 'test logwrite' wasn't be added. WHY??

Thanks to tour answers

Axel

View 2 Replies View Related

How To Call A Sp From A SQL Statement??

Oct 2, 2006

Hi,
I need to call a stored procdure from a SQL statement that I am running from query analyzer.

My SQL statement will select from a master table of bills. I have a stored procedure that calculates the amount due on each bill. The sp does not use the master table in the calculation.

How do I form the SQL statement to call the sp, which needs one of the master table columns as a parm?
i.e.
SELECT *, EXEC sp_abc columns name AS whatever
FROM tblMaster WHERE......
TIA!
Dave

View 3 Replies View Related

Call 2 Columns

Feb 6, 2007

Hello,

How do you call 2 columns from a table? mine wont work?

Quote: select message, id_by, id_from, name from message, members where message.id_by = message.id_by

output is ( I can't get the names );
Quote: message / by / to

“hello” / mark / jen
“hi” / shane / bill

message table; Quote:
message | by | to

“hello” | 1 | 3
“hi” | 2 | 4

members table; Quote:
id | name

1 | mark
2 | shane
3 | jen
4 | bill

View 1 Replies View Related

Is This Call Inner Join As Well ?

Jul 18, 2006

hi, if i have query like following

select * from o_customer,o_address
where
o_customer.name = o_address.custname

is it same as

select * from o_customer
inner join o_address on o_customer.name = o_address.custname


if there are same, which one is prefer in term of performance ..

thank you for guidance

View 2 Replies View Related

How To Call Below Procedure

Apr 30, 2008

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.ostat_CDS_rpt47_2006 Script Date: 4/30/2008 2:25:41 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ostat_CDS_rpt47_2006]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ostat_CDS_rpt47_2006]
GO


/****** Object: Stored Procedure dbo.ostat_CDS_rpt47_2006 Script Date: 23/04/2008 11:26:14 ******/

/****** Object: Stored Procedure dbo.ostat_CDS_rpt47_2006 Script Date: 22/04/2008 14:35:33 ******/

/****** Object: Stored Procedure dbo.ostat_CDS_rpt47_2006 Script Date: 21/04/2008 11:46:08 ******/

/****** Object: Stored Procedure dbo.ostat_CDS_rpt47_2006 Script Date: 16/04/2008 17:18:04 ******/

/****** Object: Stored Procedure dbo.ostat_CDS_rpt47_2006 Script Date: 04/04/2008 12:44:34 ******/

/****** Object: Stored Procedure dbo.ostat_CDS_rpt47_2006 Script Date: 31/03/2008 12:03:31 ******/

/****** Object: Stored Procedure dbo.ostat_CDS_rpt47_2006 Script Date: 31/03/2008 11:11:07 ******/


/****** Object: Stored Procedure dbo.ostat_CDS_rpt47_2006 Script Date: 09/03/2008 14:31:30 ******/
-- ostat_CDS_rpt47_2006 2008,03,17,17

CREATE procedure ostat_CDS_rpt47_2006
@pYear int,
@pMonth int,
@pDay1 int,
@pDay2 int

as
begin

declare @sql varchar(4000)
declare @tb1 varchar(128)
declare @tb2 varchar(128)

set @tb1= 'margin..ncds0421'
set @tb2='Report.dbo.ob_apr08'

set @sql=
'
insert into '+@tb2+'
select yyyy,mm,dd,HH,sitecode,TrunkOut,SwitchCode,prefixCode,0 isCallback,
cast(callcost as decimal (10,5)) cost,count(*) attempt,
sum(case when connectflg=1 then 1 else 0 end) connected,sum(talktime) Talktime
from '+@tb1+'
where yyyy='+ltrim(str(@pYear))+'
and mm='+ltrim(Str(@pMonth)) +'
and dd between '+ltrim(Str(@pDay1))+' and '+ltrim(Str(@pDay2))+'
and talktime >0 and trunkin is null
group by yyyy,mm,dd,HH,sitecode,TrunkOut,SwitchCode,prefixCode,cast(callcost as decimal (10,5))
order by yyyy,mm,dd,HH,sitecode,TrunkOut,SwitchCode,prefixCode,cast(callcost as decimal (10,5))
'
--print (@sql)
exec (@sql)

end









GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

View 2 Replies View Related

Function Call

Mar 28, 2006

can i make a function call from stored procedure

View 3 Replies View Related

What Do We Call The Table That Does The N To N ...

Dec 13, 2007

Question:
what do we call the table that does the n to n Relation

Hi,
When we have Table1 and Table2, then we link both tables using a third table Table3 that relates n records in Table1 to n records in table2, how do we call Table3? There is a name in dataBase modeling for that, right?

View 8 Replies View Related

Why Cant I Call A Sql Web Service From A Url?

Feb 16, 2008

With regular .net web services in vb.net I can expose the service and call it like this:


http://server/EnrollmentLookup/EnrollmentService.asmx/GetS3MonitorLogs?StartDate=02/02/08&EndDate=02/05/08

But when I create a SQL XML Webservice via an endpoint all I can find on the net is how to show the wsdl.

http://localhost/Contacts?wsdl

Ive created a method in the "Contacts" webservice called GetContacts. Why cant I construct a similar url to execute that method without having to write a vb.net wrapper for it?

View 6 Replies View Related

Call C++ ATL COM From C# SSIS

Aug 16, 2006

Hi All,

Is it possible to call methods of C++ COM ATL component from C# SSIS component?

I did try to call methods of C++ COM ATL component and C# library inside my SSIS but without success :-(

Regards,

Svilen Varbanov

View 3 Replies View Related

The Task FTP Call Cannot Run ...

May 9, 2006



Hi

I have created a SSIS package with an FTP call. I have it set to EncryptSensitiveWithPassword.

I can manually run the package from my IDE. Once I build it, create a job, it fails.

If you try to run the package directly from the Stored Packages in Integrations Services it fails with the following error:

Error: The task "FTP Call" cannot run on this edition of Integration Services. It requires a higher level edition.

This is running on our production with a full version.

Does anyone have any suggestions?ideas?

Any incite would be greatly appreciated.

PR

View 4 Replies View Related

How To Call An Object

Feb 27, 2008



Hello,

Does anybody know if it's possible to call an object (specifically, a textbox) from the expression of another object? I would imagine it would something like 'objectname.Value' or 'objectname.ToString', but I can't get it to recognise my object names.

I have two textboxes - Last Event and Number of Weeks Since Last Event. I have an expression to calculate the date of the last event (which is rather lengthy), and currently I get the number of weeks by doing a DateDiff between Today and another copy of the lengthy LastEvent code. This works, but it would be much simpler and more efficient if I could just say:

DateDiff("W", LastEvent.ToString, Today)


Anybody have any ideas?


Cheers,
Peter

View 3 Replies View Related

Call A Function

Feb 26, 2008

Does anybody knows how to call a function from one VB source file to another VB source file??

I have create a MDI parent form, now i want to call the function of the child form from the parent form. Does anyone know this??

View 1 Replies View Related







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