Delte From 3 Table In One Procedure

Jan 9, 2007

HI

I have 3 table
in table 1 i have a ID i use in table 2 and in table 2 i have a ID i use en table 3

In table one i have a datetime field


What i want is to delete all from this 3 table when the datetime value are more than one hour old.

I can delete old from the table one by:
delete from tableName
where datettimeField < dateadd(hour, -1, getdate())

But how do i delete all the records from table 2 and table 3 off course where the ID in table one are the same as in table 2 and the ID in table 2 are the same as in table3

Hope someone can help

LN







View 6 Replies


ADVERTISEMENT

SQL Backup Table And Delte

Dec 20, 2007

this stored proc (sp) should first backup the table if it doesn't exist and then delete all but the most recent 3

View 9 Replies View Related

How To Delte Rows Using Linked Server

Jun 19, 2007

Hi,
I have two mssql databases. I am able to create a linked server.
I want to delete some of the rows using this linked server.

Here HAFEEZ is one mssql database and DPVSQSL is another.So, i want to delete rows from DPVSQL from HAFEEZ using HAFEEZDPVSQL linkedserver.

DELETE OPENQUERY (HAFEEZTODPVSQL, 'SELECT EVENT_SYS_ID FROM DIS_MEASMT_FTR_ACTUAL WHERE EVENT_SYS_ID = ''evesysid0015''');

when i run the query i got the following error.

"Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "HAFEEZTODPVSQL" supplied inconsistent metadata for a column. The column "EVENT_SYS_ID" (compile-time ordinal 1) of object "SELECT EVENT_SYS_ID FROM DIS_MEASMT_FTR_ACTUAL WHERE EVENT_SYS_ID = 'evesysid0015'" was reported to have a "Incomplete schema-error logic." of 0 at compile time and 0 at run time."

Please note that i am able to query something using this linked server,please see the below query.

SELECT * FROM OPENQUERY (HAFEEZTODPVSQL, 'SELECT EVENT_SYS_ID FROM DIS_MEASMT_FTR_ACTUAL WHERE EVENT_SYS_ID = ''evesysid0015''');

Can anyone please help me regarding this.

Thanks and Regards,
Hafeez.

View 5 Replies View Related

What Is The Difference Between: A Table Create Using Table Variable And Using # Temporary Table In Stored Procedure

Aug 29, 2007

which is more efficient...which takes less memory...how is the memory allocation done for both the types.

View 1 Replies View Related

Stored Procedure To Copy Table 1 To Table 2 Appending The Data To Table 2.

Jan 26, 2006

Just wondering if there is an easy transact statement to copy table 1 to table 2, appending the data in table 2.with SQL2000, thanks.

View 2 Replies View Related

Is There A Way To Find The Last Procedure Execution Time If Procedure Updates The Existing Table And There Is No Flags/triggers?

Aug 21, 2007

View 8 Replies View Related

Power Pivot :: Temp Table Or Table Variable In Query (not Stored Procedure)?

Jul 19, 2012

I don't know if it's a local issue but I can't use temp table or table variable in a PP query (so not in a stored procedure).

Environment: W7 enterprise desktop 32 + Office 2012 32 + PowerPivot 2012 32

Simple example:
    declare @tTable(col1 int)
    insert into @tTable(col1) values (1)
    select * from @tTable

Works perfectly in SQL Server Management Studio and the database connection is OK to as I may generate PP table using complex (or simple) queries without difficulty.

But when trying to get this same result in a PP table I get an error, idem when replacing table variable by a temporary table.

Message: OLE DB or ODBC error. .... The current operation was cancelled because another operation the the transaction failed.

View 11 Replies View Related

T-SQL (SS2K8) :: Procedure That Create Views With Table Name And A Table Field Parameter?

Aug 4, 2015

I would like to create a procedure which create views by taking parameters the table name and a field value (@Dist).

However I still receive the must declare the scalar variable "@Dist" error message although I use .sp_executesql for executing the particularized query.

Below code.

ALTER Procedure [dbo].[sp_ViewCreate]
/* Input Parameters */
@TableName Varchar(20),
@Dist Varchar(20)
AS
Declare @SQLQuery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(2000)

[code]....

View 9 Replies View Related

How Can I Assign Data Returned From A Stored Procedure Into The Return Table Of A Table Valued Function

Apr 18, 2007

Here is the scenario,
I have 2 stored procedures, SP1 and SP2

SP1 has the following code:

declare @tmp as varchar(300)
set @tmp = 'SELECT * FROM
OPENROWSET ( ''SQLOLEDB'', ''SERVER=.;Trusted_Connection=yes'',
''SET FMTONLY OFF EXEC ' + db_name() + '..StoredProcedure'' )'

EXEC (@tmp)

SP2 has the following code:

SELECT *
FROM SP1 (which won't work because SP1 is a stored procedure. A view, a table valued function, or a temporary table must be used for this)

Views - can't use a view because they don't allow dynamic sql and the db_name() in the OPENROWSET function must be used.
Temp Tables - can't use these because it would cause a large hit on system performance due to the frequency SP2 and others like it will be used.
Functions - My last resort is to use a table valued function as shown:

FUNCTION MyFunction
( )
RETURNS @retTable
(
@Field1 int,
@Field2 varchar(50)
)
AS
BEGIN
-- the problem here is that I need to call SP1 and assign it's resulting data into the
-- @retTable variable

-- this statement is incorrect, but it's meaning is my goal
INSERT @retTableSELECT *FROM SP1

RETURN
END

View 2 Replies View Related

Error Inserting Image Into SQL Server2000 Table From Pocket PC Application Only When Using Stored Procedure In Table Adapter Wiz

Apr 24, 2008

My Pocket PC application exports signature as an image. Everything is fine when choose Use SQL statements in TableAdapter Configuration Wizard.


main.ds.MailsSignature.Clear();

main.ds.MailsSignature.AcceptChanges();


string[] signFiles = Directory.GetFiles(Settings.signDirectory);


foreach (string signFile in signFiles)

{


mailsSignatureRow = main.ds.MailsSignature.NewMailsSignatureRow();

mailsSignatureRow.Singnature = GetImageBytes(signFile); //return byte[] array of the image.

main.ds.MailsSignature.Rows.Add(mailsSignatureRow);

}


mailsSignatureTableAdapter.Update(main.ds.MailsSignature);

But now I am getting error "General Network Error. Check your network documentation" after specifying Use existing stored procedure in TableAdpater Configuration Wizard.


ALTER PROCEDURE dbo.Insert_MailSignature( @Singnature image )

AS

SET NOCOUNT OFF;

INSERT INTO MailsSignature (Singnature) VALUES (@Singnature);



SELECT Id, Singnature FROM MailsSignature WHERE (Id = SCOPE_IDENTITY())

For testing I created a desktop application and found that the same Code, same(Use existing stored procedure in TableAdpater Configuration Wizard) and same stored procedure is working fine in inserting image into the table.

Is there any limitation in CF?

Regards,
Professor Corrie.

View 3 Replies View Related

Stored Procedure That Fetch Each Row Of A Table And Update Rows In Another Table

Jan 31, 2006

I am working with the following two tables:

Category(NewID,OldID)
Link(CategoryID,BusinessID)

All fields are of Integer Type.

I need to write a stored procedure in sql 2000 which works as follows:

Select all the NewID and OldID from the Category Table
(SELECT NewID,OldID FROM Category)

Then for each rows fetched from last query, execute a update query in the Link table.

For Example,

Let @NID be the NewID for each rows and @OID be the OldID for each rows.
Then the query for each row should be..

UPDATE Link SET CategoryID=@CID WHERE CategoryID=@OID

Please help me with the code.

Thanks,
anisysnet

View 1 Replies View Related

Update Temp Table With Stored Procedure Joined With Table

Sep 8, 2006

Hello

Is it possible to insert data into a temp table with data returned from a stored procedure joined with data from another table?

insert #MyTempTable

exec [dbo].[MyStoredProcedure] @Par1, @Par2, @Par3

JOIN dbo.OtherTable...

I'm missing something before the JOIN command. The temp table needs to know which fields need be updated.

I just can't figure it out

Many Thanks!

Worf

View 2 Replies View Related

Stored Procedure For Insert Data From One Table To Another Table

Apr 25, 2006

Hi,
     I am having 2 tables. One is main table and another is history table. Whenever I update the main table, I need to insert the all the main table data to History table, before updating the main table.
Overall it is like storing the history of the table updation.
How do i write a stored procedure for this?
Anybody has done this before?
Pls help me.
 

View 1 Replies View Related

Stored Procedure Append All From Table A To Table B

Jan 29, 2013

I've never used a stored procedure before - let alone created one. how to append records from table A to table B.

View 1 Replies View Related

Creating A Stored Procedure That Will Summarize Data In A Table Into A Table Reflecting Period Data Using An Array Type Field

Sep 20, 2007

I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.

Any help would be greatly appreciated.

Current Table

Project | Task | Category | Fiscal Year | Fiscal Month | Total Hours
---------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 2007 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2007 | 02 | 20
Proj 1 | Task 1 | Cat 3 | 2007 | 03 | 35
Proj 1 | Task 1 | Cat 1 | 2008 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2008 | 02 | 40
Proj 1 | Task 1 | Cat 3 | 2008 | 03 | 40

Proposed Table

Project | Task | Category | Fiscal Month 01 | Fiscal Month 02 | Fiscal Month 03 | Fiscal Year
---------------------------------------------------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2007
Proj 1 | Task 1 | Cat 2 | 0 | 20 | 0 | 2007Proj 1 | Task 1 | Cat 3 | 0 | 0 | 35 | 2007
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2008

Proj 1 | Task 1 | Cat 2 | 0 | 40 | 0 | 2008
Proj 1 | Task 1 | Cat 3 | 0 | 0 | 40 | 2008

Thanks,
Mike Misera

View 6 Replies View Related

Procedure Or Query To Make A Comma-separated String From One Table And Update Another Table's Field With This String.

Feb 13, 2006

We have the following two tables :

Link  ( GroupID int , MemberID int )
Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) )

The Link table contains the records showing which Member is in which Group. One particular Member can be in
multiple Groups and also a particular Group may have multiple Members.

The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated
Groups ID, showing in which Groups the particular Member is in).

We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to
fill the GroupID field of the Member table, from the Link Table.

For instance,

Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID,
then update the GroupID field of the corresponding Member in the Member table.

Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.

View 1 Replies View Related

Getting The Table Name From Stored Procedure

Oct 11, 2007

Hi,
I am having a SP which returns two Rowsets:
   create proc GetSalesDetails as
        select CustName, ProductPurchased from Customers where PurchaseDate > '10/10/2006'
        select ProductName from Products where SalesDate > '10/10/2006'
 
Now in my code when I am filling the Dataset using this SP then it is giving the Table Names as "Table" and "Table1". Is there any way to get the actual table names respectively?
 
Cheers,
Soumen

View 3 Replies View Related

Table Name In A Stored Procedure

Mar 9, 2004

How can be used in a stored procedure the name of a table that was passed as a varchar parameter? The table name must be used in the "from" clause of a "select" statement without calling the "EXECUTE" or "sp_executesql". Is it possible?
Marius G.

View 2 Replies View Related

Procedure To Update Table In Best Possible Way.

May 29, 2008

Hi All,

I need a generalised procedure which can update any table with its new version i.e updated data.I want to insert a row if that row is not available in old table.I want to update a row in old table if any of the field in new version of same table for that tuple is changed and it should work for any table with any no. of columns, it should be a generalised one.

For eg: if i have a table "Stud" which includes the records of all students in my college which keeps on changing regularly.I have a new data of students which needs to be updated in table "Stud".If any new student is added then add that enrty, if any of the column detail for any row is changed that update that row in table Stud and if no chnage is there in new and old data then skip that row.

I hope u got my problem statement.It would be great if any one could help me on this.

Thanks.

View 1 Replies View Related

Procedure To Search A Table

Sep 27, 2007

Hi All,

I have the following table:




Code Block
Request
requestid
customername
age
sex
address
status

[status =
0 deleteme
1 addme
2 updatme
3 processed]


I need to write a stored proc that will do the following :

Input : requestid

Logic : check the customers who already have a status of 'deleteme' and have now filed a status of 'addme'. For this, inside the stored proc, i will retreive the customername and address using the requestid and then check in the table for all the customers who have the samename, address and status=0. How can i detect this.

Output : 0 for no entry found and requestid if a request of 'deleteme' for the same person was found.

For eg:



Code Blockrequestid,customername,age,sex,address,status
22352,Jack,23,M,'Texas',1


I need to check if a person with the name of Jack and address Texas is there in the db with the status of 0.

Plz note: My Request table can contain more than 100000 records at a time. Please sugest accordingly

Plz help. Thanks

View 5 Replies View Related

Is Possible To Create A New Permanent Table By A Procedure?

Mar 23, 2007

Is possible to create a new permanent table by a procedure? I mean not a temporary table. If it is how do I do with the name? Because I would like put for each new table created as name as "forum1", "forum2", etc ... and not just numbers as I'll show you in the example. I tried to create the procedure, but I got stuck with permissions e primary key.
create procedure new_forum
 
@user_id int,
@title varchar(50),
@description varchar(200)
 
as
begin
 
declare @totalForuns int
 
select @totalForuns = count(*) from foruns
 
set @totalForuns = @totalForuns + 1
 
Insert into foruns (title,creation,country,[views],[description])
      values (@title,getdate(),'England',0,@description)
 
CREATE TABLE [dbo].[@totalForuns](
      [thread_id] [int] IDENTITY(1,1) NOT NULL,
      [user_id] [int] NOT NULL,
      [last_user_id] [int] NOT NULL,
      [title] [nvarchar](50) NOT NULL,
      [creation] [datetime] NOT NULL,
      [last_answer] [datetime] NOT NULL,
      [answers] [int] NOT NULL CONSTRAINT [DF_threads_answers]  DEFAULT ((0)),
      [vizuais] [int] NOT NULL CONSTRAINT [DF_threads_views]  DEFAULT ((0)),
      [fixed] [bit] NOT NULL CONSTRAINT [DF_threads_fixed]  DEFAULT ((0)),
      [votos] [int] NOT NULL CONSTRAINT [DF_threads_votos]  DEFAULT (''),
 CONSTRAINT [PK_threads] PRIMARY KEY CLUSTERED
(
      [@totalForuns] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
ALTER TABLE [dbo].[@totalForuns]  WITH CHECK ADD  CONSTRAINT [FK_threads_users] FOREIGN KEY([user_id])
REFERENCES [dbo].[users] ([user_id])
GO
ALTER TABLE [dbo].[@totalForuns] CHECK CONSTRAINT [FK_threads_users]
 
end
Go
 
Thank you very much.
 

View 5 Replies View Related

Using Stored Procedure In View As A Table ?!

Aug 28, 2007

Hi guys
I have a stored procedure that a make crosstab table , In this table the main column is "job titles" these jobs  must be ordered  in certain  way , for example "1st managers then engineers  … workers  … " so In the table that   job titles are defined  there is also a column named "Ranking" so the" job titles" could be sorted appropriately by ranking order .
The problem is I cannot have the "Ranking" column with my crosstab table so I need to load it in a view or something like that.
Any Idea?
 

View 8 Replies View Related

How To Call Stored Procedure To Table??

Nov 12, 2007

Hi.....I have problem and I need your helpI stored a procedure in the Projects Folder in my computerand I want to return the procedure result in a column inside tableHow I can do that?????????thank you

View 2 Replies View Related

Loop Through A Table In A Stored Procedure

May 17, 2008

Hi, how do I loop through a table in a store procedure? I need to check the all the record in a table and do some logic and then insert or update another table base on the logic?

View 4 Replies View Related

Stored Procedure Update Table

Jan 20, 2004

Hi,

I've got a stored procedure which should update a table (add new customer record)
When I run it locally everythings fine,

Since uploading it all to the web it no longer seems to add a new record,
I've debugged it and it seems that the output parameters is set to nothing.

I believe it's a permissions issue but the user i'm using has full access to both the table
and permission to execute the stored procedure is there any error handling I can
do to capture the exact error? the code I use to execute the sProc is below

thanks for any help

Dave



Try
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()

' Calculate the New CustNo using Output Param from SPROC
Dim custNo As Integer = CInt(parameterCustNo.Value)

Return custNo.ToString()
Catch
<---This is where it's dropping in can I put any
Error handling in to show me the error?
Return String.Empty
End Try

View 3 Replies View Related

Convert Table In Stored Procedure

Apr 23, 2004

I have a flat table from 1 of the clients with a lot of fields (more then 100) like this

Item F1 F2 F3 .... F(N)
---------------------------------------------
100 X X
101 X

There are more 10000 records , X is the data inside the field.

I need to quickly convert it to this table

Item FieldNumber Value
----------------------------
100 1 X
100 2 X
101 1 X

Any ideas ?

Thanks

Mikhail

View 3 Replies View Related

Need To Create Table With Stored Procedure

Feb 10, 2005

I need to use a stored procedure that will create a table. The table name must be passed to the stored procedure.
This is what I have so far, but it does not allow me to run it.

alter procedure dbo.createNewBUtable
(
@BU as varchar(50)
)
as
set nocount on;
create table @BU
(
BUid varchar(50) primary key,
BUinfo varchar(50)
)

View 4 Replies View Related

Stored Procedure That Uses Input From Another Table

Apr 25, 2006

Hi,    I need to be able to create a Stored Procedure that gets its information based on dates stored in another table.Does anyone have an idea on how I can acheive this??Regards..Peter.

View 2 Replies View Related

Copying Table In Stored Procedure

Feb 16, 2001

How can I create a table identical to another one, in a stored procedure?
I need to copy the indexes and constraints too.
Example: I have a table "employee" and I want another table "employee2"
with the same indexes and primary key and references.

I need to do the work in a stored procedure because there are many, many tables, and this process belong to a convertion program.

I can't script the table because this process must be automatic no manual.

Thank you

View 1 Replies View Related

How To Use A Stored Procedure As A Derived Table

Nov 20, 2001

Hi All,

Can any one give me code sample which uses the recordset returned by the execution of a stored procedure as a derived table.

Thanks,
venkat.

View 1 Replies View Related

Copying Table In Stored Procedure

Feb 16, 2001

How can I create a table identical to another one, in a stored procedure?
I need to copy the indexes and constraints too.
Example: I have a table "employee" and I want another table "employee2"
with the same indexes and primary key and references.

I need to do the work in a stored procedure because there are many, many tables, and this process belong to a convertion program.

Thank you

View 1 Replies View Related

Manipulating Table In Store Procedure

Mar 5, 2007

Hi
I am new to SQL programming. This is what I am trying to do in store procedure.
Alter a table - adding two columns.
Then update those columns
Both the above action in same procedure .
When I execute them - it complains that the columns I am adding in the first part of store procedure , does not exists.
So the SP looks like this
create store procedure <name> as
alter table <name> ADD
column_1 nvarchar (256),
column_2 nvarchar (256);

update table
set
column_1 = <condition>
column_2 = <condition>

The SQL complains -
invalid column name column_1
invalid column name column_2

Can some one help please...

View 3 Replies View Related

Stored Procedure To Copy From A Table

Feb 28, 2006

Hy ,
How is the stored procedure, to copy from a table to others tables? I have a stored procedure which is doing that, but for 1 registration : " insert into....". So i want for each registration in the source table to execute this procedure and put the data in my format table.

View 2 Replies View Related







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