hai,
i want to create a table in a existing database in sql and i have to move data to the table and after my process i have to delete it .This is should be done thro codings.please help me.
Thanks in advance
Viswa
I want to know about the how to connect with asp.net 2005 and sqlexpress through codings using parameters
import System.Data.SqlClient;
related update delete insert codings ....
How to know about the make the new connection with different databases sqlserver, oracle mysql....
Can anybody explain me how to connect sqlserver 2K thro Internet using the IP addess. I want the code for vb/net.
kansjee
which one is smarter, where there is no indexing on the table which is really simple table delete everything or recreate table. I got an argument with one of my coworker. He says it doesnt matter i say do delete. Any opinions.
View 7 Replies View RelatedHi All,
I am trying to pass a text file from desktop machine to PDA thro TCP-IP protocols. It follows Server (on desktop machine) and Client (on pda) architecture. File is converted to bytes and will be pushed to network stream. The client on PDA will receive the bytes and store it on queue and writes the file on PDA. The operation is accomplished through threads on both Server and client. Applications are developed on MS Visual Studio 2005 (C#).
For large files like 1024 KB or having lines about 15000, it takes around 15 minutes. Can anyone give a better idea to transmit files from desktop machine to PDA using TCP-IP protocols.
Thanks in advance
Boss
Hi seniors
there are two tables involve in replication let say table1 and replicated table is also rep.table1.
we are not deleting records physically in table1 so only a bit in table1 has true when u want to delete a record but the strange thing is that replication agaent report that this is hard delete operation on table1 so download and report hard delete operation and delete the record in replicated table which is very crucial.
plz let me know where am i wrong and how i put it into right way.
there is no triggers on published tables and noother trigger is created on published table.
regards
Ahmad Drshen
I writte a stored procedure for username , password ....
How can i call that stored procedure to verify the username & password
then if both match in the database redirected to next page..? in asp with c#.net back end programming
Hi when I am performing a merge replication between my PDA (SQL CE) and SQL I get the above message. There is no information about it anywhere.
Any ideas?
Thanks in advance.
Hello:
Need some serious help with this one...
Background:
Am working on completing an ORM that can not only handles CRUD actions -- but that can also updates the structure of a table transparently when the class defs change. Reason for this is that I can't get the SQL scripts that would work for updating a software on SqlServer to be portable to other DBMS systems. Doing it by code, rather than SQL batch has a chance of making cross-platform, updateable, software...
Anyway, because it needs to be cross-DBMS capable, the constraints are that the system used must work for the lowest common denominator....ie, a 'recipe' of steps that will work on all DBMS's.
The Problem:
There might be simpler ways to do this with SqlServer (all ears :-) - just in case I can't make it cross platform right now) but, with simplistic DBMS's (SqlLite, etc) there is no way to ALTER table once formed: one has to COPY the Table to a new TMP name, adding a Column in the process, then delete the original, then rename the TMP to the original name.
This appears possible in SqlServer too --...as long as there are no CASCADE operations.
Truncate table doesn't seem to be the solution, nor drop, as they all seem to trigger a Cascade delete in the Foreign Table.
So -- please correct me if I am wrong here -- it appears that the operations would be
along the lines of:
a) Remove the Foreign Key references
b) Copy the table structure, and make a new temp table, adding the column
c) Copy the data over
d) Add the FK relations, that used to be in the first table, to the new table
e) Delete the original
f) Done?
The questions are:
a) How does one alter a table to REMOVE the Foreign Key References part, if it has no 'name'.
b) Anyone know of a good clean way to get, and save these constraints to reapply them to the new table. Hopefully with some cross platform ADO.NET solution? GetSchema etc appears to me to be very dbms dependant?
c) ANY and all tips on things I might run into later that I have not mentioned, are also greatly appreciated.
Thanks!
Sky
I'm trying to clean up a database design and I'm in a situation to where two tables need a FK but since it didn't exist before there are orphaned records.
Tables are:
Brokers and it's PK is BID
The 2nd table is Broker_Rates which also has a BID table.
I'm trying to figure out a t-sql statement that will parse through all the recrods in the Broker_Rates table and delete the record if there isn't a match for the BID record in the brokers table.
I know this isn't correct syntax but should hopefully clear up what I'm asking
DELETE FROM Broker_Rates
WHERE (Broker_Rates.BID <> Broker.BID)
Thanks
The requirement is: I should allow single row delete from a table but not bulk delete. An audit table should get updated if there is any single delete or single update. So I wrote the triggers as follows: for single and bulk delete
ALTER TRIGGER [dbo].[TRG_Delete_Bulk_tbl_attendance]
ON [dbo].[tbl_attendance]
AFTER DELETE
AS
[code]...
When I try to run the website, the database error I am getting is:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
i am inserting something into the temp table even without creating it before. But this does not give any compilation error. Only when I want to execute the stored procedure I get the error message that there is an invalid temp table. Should this not result in a compilation error rather during the execution time.?
--create the procedure and insert into the temp table without creating it.
--no compilation error.
CREATE PROC testTemp
AS
BEGIN
INSERT INTO #tmp(dt)
SELECT GETDATE()
END
only on calling the proc does this give an execution error
I would like to create a table valued function that fetch through the table below using a cursor and return the records that are unique
EmpidChDateSiteuseridinitsal finsalNote
-------------------------------------------- ----------
236102015-4-21 22:02:10.8072570 0.696176161 change inisal value
236112015-4-21 22:02:11.0502570 0.696176161change inisal value
236122015-4-21 22:02:11.1202570 0.696176161 change inisal value
236132015-4-21 22:02:11.2452570 0.696176161change inisal value
I need to create a table from these 2 tables and want all the data. Table 1 has 15000 records and table has around 1000 records.
I have 2 tables.
SELECT [UniqueID]
,[Company]
,[CustID]
,[CustomerName]
,[FiscYr]
[Code] ....
I am using SQL SERVER 2012..I have the following table:
tbtab1
| A | B | C |
| 1 | Pluto | NULL |
| 2 | Pippo | NULL |
| 3 | Rossi | NULL |
I want to creare a new empy table with the columns name contained into the column B of the first table; the following should my results:
tbtab2
| Pluto | Pippo | Rossi |
I am using a SQL Server Agent jobs that run each morning to update the records in a table to match what they should be for that day. I built them and tested it using a test table called "testtable1". It worked fine. But when I switched over to our production table, it fails saying the table has to be decaled. What would be the difference. The production table has a "@" in front of the name, is that causing issues?
USE [Live_build]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
BEGIN
DELETE
FROM @ZIPLIST
INSERT INTO @ZIPLIST
SELECT * FROM tblZip3DSWed;
END
I have a table with about 466 Million rows. In this table there is a int column called WeeksToRetain as well as a EventDate column containing the date the row was inserted. I am trying to delete all the rows that that should be deleted according to the WeeksToRetain. For example, if the EventDate is 5/07/15 with a 1 in the WeeksToRetain column the row should be removed by 5/14/15. I am not sure what days SQL considers the beginning and end of the week. However the core issue I am having is the sheer mass of deletions I must do and log growth.
So I am trying to do the delete in batches. More specifically I want to load a temporary table with a million rows, then use the temporary table to load a sub temporary table with 100,000 rows and join this temporary table to the table I want to delete from looping through 10 times to get 1 million. The Logging.EvenLog table which is the table I'm trying to purge has a clustered index on EventDate (ASC). I would like to run this in a schedule job with enough time between executions for log backups to run.
DECLARE @i int
DECLARE @RowCount int
DECLARE @NextBatchDate datetime
CREATE TABLE #BatchProcess
(
EventDate datetime,
ApplicationID int,
[Code] .....
Is there is 'fairly' simple query to delete duplicate rows from ANY table ?
A script which removes duplicates from a table, when a table_name is supplied.
Hi all,
i m using VS 2005 and I have to display records with feature of INSERT / DELETE ITEMS But when i connect to Sql Server Database and select * from columns but here when clicking the "Advance" button , i do not get "Advance Sql generation Option " highlighted. Instead , it is turned off. i.e
The Following options are not highlighting
------ Generate Insert, Update, Delete statements
------ use optimistic concurrency
Plz guide me anyone..... is anything wrong with our VS 2005 software installed?
Bilal
I am experimenting with using CDC to track user changes in our application database. So far I've done the following:
-- ENABLE CDC ON DV_WRP_TEST
USE dv_wrp_test
GO
EXEC sys.sp_cdc_enable_db
GO
-- ENABLE CDC TRACKING ON THE AVA TABLE IN DV_WRP_TEST
USE dv_wrp_test
[Code] ....
The results shown above are what I expect to see. My problem occurs when I use our application to update the same column in the same table. The vb.net application passes a Table Valued Parameter to a stored procedure which updates the table. Below is the creation script for the stored proc:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
if exists (select * from sysobjects where id = object_id('dbo.spdv_AVAUpdate') and sysstat & 0xf = 4)
drop procedure dbo.spdv_AVAUpdate
[Code] ....
When I look at the results of CDC, instead of operations 3 and 4, I see 1 (DELETE) and 2 (INSERT) for the change that was initiated from the stored procedure:
-- GET CDC RESULTS FOR CHANGES TO AVA TABLE
USE dv_wrp_test
GO
SELECT *
FROM cdc.dbo_AVA_CT
GO
--RESULTS SHOW OPERATION 1 (DELETE) AND 2 (INSERT) INSTEAD OF 3 AND 4
--__$start_lsn__$end_lsn__$seqval__$operation__$update_maskAvaKeyAvaDescAvaArrKeyAvaSAPAppellationID
--0x0031E84F000000740008NULL0x0031E84F00000074000230x02119Test26NULL
--0x0031E84F000000740008NULL0x0031E84F00000074000240x02119Test36NULL
--0x0031E84F00000098000ANULL0x0031E84F00000098000310x0F119Test36NULL
--0x0031E84F00000098000ANULL0x0031E84F00000098000420x0F119Test46NULL
Why this might be happening, and if so, what can be done to correct it? Also, is there any way to get the user id associated with the CDC?
I've 2 tables ResumeSkill (Child table) and Skill (Parent table), There are duplicates in the parent table and after removing the foreign key constraint in child table deleted all duplicate values from Parent table. But those deleted duplicate values has references in child table which need to be deleted now.
ResumeSkill Skill
Id SkillId
SkillId Name
I want to delete all the records from ResumeSkill that dont have matching skillId in Skill table.
Hi allIam using an SSIS package to import existing data from Oracle tables to SQl 2005 tables. I need to remove the identity from the sql table, import the data, then set the identity again to the sql table. Im doing that to maintain same ids as where in the Oracle tables. Is there a way that i can do that in an sql command.Any ideas?
View 2 Replies View RelatedI have a master table and i need to import the rows into the parent and child table.
Master table name is Flatfile_Inventory
Parent Table name is INVENTORY
Child Tables name are INVENTORY_AMOUNT,INVENTORY_DETAILS,INVENTORY_VEHICLE,
Error details will be goes to LOG_INVENTORY_ERROR
I have 4 duplicate rows in the Flatfile_Inventory which i have already inserted in the Parent and child table.
Again when i run the query using stored procedure,its tells that all the 4 rows are duplicate and will move to the Log_Inventory_Error.
I need is if i have the duplicate rows in the flatfile_Inventory when i start inserting into the parent and child table the already inserted row have the unique ID i must identify it and delete that row in the both parent and chlid table.And latest row must get inserted into the Parent and child table from Flatfile_Inventory.
-- ===============================================================================================
-- STORED PROCEDURE FOR FLATFILE_INVENTORY
-- ===============================================================================================
USE [IconicMarketing]
---=======================================SALES_CURSUR===========================================
--USE IconicMarketing
--GO
DECLARE
@FileType varchar(50) ,
@ACDealerID varchar(50) ,
[code].....
I have these two tables Log and CategoryLog, I need to archive records older than 13 months in these two tables to two separate tables and then delete the archived records from Log and CategoryLog tables. The problem is that only 'Log' table has a date column, the other table CategoryLog does not have any date column. But the two tables are connected by a column(LogID). How to archive the data and then delete the archive data from both tables.
View 9 Replies View Relatedhi,CAn i have one trigger for both Update and DeleteDelete Trigger---------------------create Trigger [tr_delete_user_log]on [dbo].[user_log] for deleteasbegininsert into z_user_log select * from deletedendTrigger Update---------------------CREATE Trigger [tr_update_user_log]on [dbo].[user_log] for updateasbegininsert into z_user_log select * from deletedendCan i have one trigger instead of these Triggers ..
View 3 Replies View Related'****************************************************************************
Cmd.CommandText = "Drop Table Raj"
Cmd.ExecuteNonQuery()
Cmd.CommandText = "Select * Into Raj From XXX"
Cmd.ExecuteNonQuery()
'**************************************************************************
This generates error that Table already exist.
If Wait 1 sec then execute statement then it works fine.
Thanks in Advance
Piyush Verma
The database has approx. 2500 temporary tables. The temp tables match a pattern such as APTMP... I tried deleting the tables in SSMS with the statement, Delete from Information_Schema.tables where substring(table_name,1,5) = 'APTMP' This returns the error message"Ad hoc updates to system catalogs are not allowed".
What is the correct way to delete a group of tables whose name match a pattern from within SSMS?
Dear friends,
I am thinkking there has to be away to do this in a SQL
statement.
can't you do some kind of...
Create Procedure sp_update_users
@User_Id Int,
@Title_Id Int
AS
UPDATE USERS
DELETE WHERE @User_ID = User_Id and @Title_Id = @Title_Id
ELSE
INSERT INTO USERS VALUES (@User_ID,@Title_Id;)
Hi There, I have created a trigger which supposingly will do event before delete the record from its own table.unfortunately when i try delete the record to test it whether it will do the event (inserting some records to another table), i found that it was not doing the event like i wanted to be. :(the trigger is as below :=======================CREATE TRIGGER TG_D_AGENT ON dbo.AgentFOR DELETEASbegindeclare @vAgentID as numeric,@vAgency as varchar(50),@vUnit as varchar(50),@vAgentCode as varchar(50),@vName as varchar(50),@vIC as varchar(14),@vAddress as varchar(100),@vContactNumber as varchar(50),@vDownlink as varchar(50),@vGSM as varchar(10),@vAM as varchar(10),@vDeleted_date as datetime set @vDeleted_date = convert(datetime, convert(varchar(10) , getdate(),103),103)declare cur_policy_rec CURSOR forselect AgentID,Agency,Unit,AgentCode,[Name],IC,Address,ContactNumber,Downlink,GSM,AM from insertedopen cur_policy_recfetch from cur_policy_rec into @vAgentID,@vAgency,@vUnit,@vAgentCode,@vName,@vIC, @vAddress,@vContactNumber,@vDownlink,@vGSM,@vAM WHILE @@FETCH_STATUS=0BEGIN INSERT INTO [Agent_history] (AgentID,Agency,Unit,AgentCode,Name,IC,Address,Con tactNumber,Downlink,GSM,AM,Deleted_date) VALUES(@vAgentID,@vAgency,@vUnit,@vAgentCode,@vNam e,@vIC,@vAddress,@vContactNumber,@vDownlink,@vGSM, @vAM,@vDeleted_date)fetch from cur_policy_rec into @vAgentID,@vAgency,@vUnit,@vAgentCode,@vName,@vIC, @vAddress,@vContactNumber,@vDownlink,@vGSM,@vAM enddeallocate cur_policy_recend===============================in oracle , i normallly can do something like this...====================================CREATE TRIGGER TG_D_AGENT ON dbo.AgentBEFORE DELETE ON dbo.Agent FOR EACH ROWbeginIs that such thing function like 'BEFORE' in MS SQL SERVER 2000, coz in sql server im not sure they do have or not. Plz someone help me on this...realy appreciated if can!
View 1 Replies View Relatedhelp on CREATE stored procedure delete and after insert where not exist
in one stored procedure
in table_B
Code Snippet
CREATE PROCEDURE [dbo].[delete_from_table_B]
@empID varchar(500)
as
DELETE FROM table_B
WHERE charindex(','+CONVERT(varchar,[empID])+',',','+@empID+',') > 0
---HELP from this ponit how to insert ? after where not exist
IF @@ROWCOUNT > 0
BEGIN
insert into
table_B
set (empID,ShiftDate,shiftType)
where not exist
select
empID,ShiftDate,shiftType
from
table_A
table_A
empID fname ShiftDate shiftType
----------------------------------------------------
111 aaaa 15/03/2008 1
111 aaaa 16/03/2008 2
111 aaaa 18/03/2008 3
111 aaaa 19/03/2008 4
111 aaaa 20/03/2008 5
111 aaaa 21/03/2008 6
999 qqq 21/03/2008 9
222 bbb 02/05/2008 7
222 bbb 03/05/2008 8
222 bbb 04/05/2008 9
222 bbb 05/05/2008 7
222 bbb 06/05/2008 9
222 bbb 07/05/2008 3
222 bbb 08/05/2008 4
222 bbb 09/05/2008 5
333 ccc 03/04/2008 9
333 ccc 04/04/2008 2
TABLE B
empID fname ShiftDate shiftType
----------------------------------------------------
111 aaaa 15/03/2008 1
111 aaaa 16/03/2008 2
111 aaaa 18/03/2008 3
111 aaaa 19/03/2008 4
111 aaaa 20/03/2008 5
111 aaaa 21/03/2008 6
TNX for the help
Hi all,
I ran the following CTE sql code:
Use ChemDatabase
GO
WITH PivotedTestResults AS
(
SELECT TR.AnalyteName, TR.Unit,
Prim = MIN(CASE S.SampleType WHEN 'Primary' THEN TR.Result END),
Dupl = MIN(CASE S.SampleType WHEN 'Duplicate' THEN TR.Result END),
QA = MIN(CASE S.SampleType WHEN 'QA' THEN TR.Result END)
FROM TestResults TR
JOIN Samples S ON TR.SampleID = S.SampleID
GROUP BY TR.AnalyteName, TR.Unit
)
SELECT AnalyteName, UnitForConc,
avg1 = abs(Prim + Dupl) / 2,
avg2 = abs(Prim + QA) / 2,
avg3 = abs(Dupl + QA) / 2,
RPD1 = abs(Prim - Dupl) / abs(Prim + Dupl) * 2,
RPD2 = abs(Prim - QA) / abs(Prim + QA) * 2,
RPD2 = abs(Dupl - QA) / abs(Dupl + QA) * 2
FROM PivotedTestResults
GO
//////////////////////////////////////////////////////////////////////////////////////
I got the following errors:
Msg 207, Level 16, State 1, Line 9
Invalid column name 'Unit'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'Unit'.
////////////////////////////////////////////////////////////////////////////////////////////////////////////////
I guess that I had "Unit" (instead of "UnitForConc"), when I executed the sql code last time!!!???
How can I delete the old, wrong CTE that is already in the ChemDatabase of my SSMSE?
Please help and advise.
Thanks in advance,
Scott Chang
I have some simple files but they are failing because the delete history task is failing as it is looking for files in a non existent directory.
It is looking for files in C:Program FilesMicrosoft SQL ServerMSSQL10_50.INSTANCEMSSQLLog whereas it should be looking in C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLLog
how I can get this corrected so I can get the Maintenance Plans to run correctly.
I have tried deleting and recreating the Plan but to no avail
From ASP.NET, how to make temporary table in SQL server from select statement which build in a button?
For example, if the user clicks a button a table will create based on statement below:
“Select * from Order where [order by] = ‘Mike Lee’�