I use discountasp.net. I've added some new tables that I'd like to place on my website. My problem is that if I reattach my mdf file to my server, my member roster gets deleted. So my question is how can I update the mdf file without losing member information?
Thanks,
JW
Hi,I made a gridview, and I am trying to make it so when the user deletes a row, values in other tables update. I used the following source code: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" DeleteCommand="DELETE FROM [Transactions] WHERE [TransactionID] = @TransactionID AND UPDATE [items] SET Quantityavailable, numtaken VALUES Quantityavailable + 1, numtaken - 1 WHERE ([Itemid] = @Itemid) " It gives the error that Quantityavailable is not a SET type?Thanks if you can suggest a remedy! Jon
View 2 Replies View RelatedHi,
I'm using ASP with a JScript variant and MSSQL Server 2000. I would like to write a script that basically erases all data except for a few things.
Is there a way to update multiple tables at once without having to write lines and lines of code? I tried UPDATE tbl1,tbl2 SET uid='asc', but to no avail. It gave me a syntax error. My thinking behind it is something like... UPDATE dbo.* SET uid='mferguson' and after that I can delete stuff like DELETE dbo.*... Any ideas?
I know the above is ASP, I've tried this thread in the ASP forum with no avail... they referred me to this forum.
I have created a single Data Flow Task that reads a set of records from a source table and then makes determinations whether to insert, update or delete from a destination table. The data is basically being copied from one database to another with a small amount of data manipulation and lookups. The problem seems to be that when running the task, even a small amount of records read from the source table seem to take a long time for the task to finish. I feed the records into a Script Component (the brains) that sorts the records to three separate outputs. I use OLE DB Commands to perform the DELETE and UPDATE and an OLE DB Destination for INSERT. I thought that by using three separate database connections would help, but it just appears to be locked while trying to perform these commands against the same table.
Is there a way to control or route these three record sets in such a way as to perform them sequentially?
I know it's a bit of a simple question for some of you, but I'm just learning SSIS (but I like it!).
Thanks!
Jeff Tolman
E&M Electric
I have two table both say A and B.If i insert a record in A that record should be inserted in B.If i delete a record in A that record should be deleted from B.Is that possible.If yes please tell me.Thankyou in advance,vishnu
View 3 Replies View RelatedI need to prepare a roster rotating persons within each Department for the available dates
Please refer to the below sample tables and data
CREATE TABLE Roster
(
StartDate Date,
EndDate Date
)
CREATE TABLE Department
[Code] ....
Expected Output
SELECT '01/01/2015' StartDate, '01/05/2015' EndDate, 'A' Department,'P1' Person
UNION
SELECT '01/01/2015' StartDate, '01/05/2015' EndDate, 'B' Department,'P3' Person
UNION
SELECT '01/01/2015' StartDate, '01/05/2015' EndDate, 'C' Department,'P6' Person
[Code] .....
Hi all,
I installed SQL 2005 SP2 + ReportServices Add-in for Sharepoint (WSS 3.0). All it's OK until I try to grant database access in the Sharepoint Central Admin site.
I setup the Reporting Services Integration (Manage integration settings). I use the default SQL instance, I put the USERNAME and the PASSWORD of my ADMIN account in the ENTER CREDENTIALS windows.. When I click the "OK" button, I receive always the error ...
"A new member could not be added to a local group because the member has the wrong account type"
I tried a lot of things... without success.
Is there someone who can help me....
PS: There is no error in the LOG
Thanks
Hi all,
I installed SQL 2005 SP2 + ReportServices Add-in for Sharepoint (WSS 3.0). All it's OK until I try to grant database access in the Sharepoint Central Admin site.
I setup the Reporting Services Integration (Manage integration settings). I use the default SQL instance, I put the USERNAME and the PASSWORD of my ADMIN account in the ENTER CREDENTIALS windows.. When I click the "OK" button, I receive always the error ...
"A new member could not be added to a local group because the member has the wrong account type"
I tried a lot of things... without success.
Is there someone who can help me....
PS: There is no error in the LOG
Thanks
I need to create a member that one of its Attributes (maybe my term is wrong) is a reference to another entity member named group
the code
createRequest.Members.MemberType = MemberType.Leaf;
createRequest.Members.Members = new System.Collections.ObjectModel.Collection<Member> { };
Member aNewMember = new Member();
aNewMember.MemberId = new MemberIdentifier() { Name = uag.groupName, MemberType = MemberType.Leaf };
[code]....
"The attribute data type is not valid".Is it wrong to add the reference as attribute? How can I embed the reference in the new member?
I have a table where I need to delete rows if the member does not exists in the member table. Will the following syntax work ?
Delete T
FROM Event_Temp_Lead_Screen T
left join member M on ( M.MemberID = T.MemberID )
where
T.Gender is NULL
Ok so I have some dynamic sql to delete a file that is created via sql earlier on. It is to provision a copy of a database to an instance on link server. Everything works great and the files used to delete. Now, with no code changes it is throwing a syntax error. I do a print of what the dynamic sql is creating before executing and then I copy / paste what was generated into command prompt and guess what!! The file deletes.
Here is the result on screen:
@DeleteBackupFileStatement: DEL adas16.clients.advance.localwip$AvionteAP_Template_893.bak /Q
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ''.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ''.
Here is the code that creates the statement:
SET @DeleteBackupFileStatement = NULL
BEGIN
SET @DeleteBackupFileStatement = 'DEL ' + LTRIM(RTRIM(@BackupFile)) + ' /Q'
END
PRINT '@DeleteBackupFileStatement: ' + cast(@DeleteBackupFileStatement as varchar(400))
BEGIN
EXEC adasdb.master.sys.Sp_executesql
@DeleteBackupFileStatement
END
END
The value of @BackupFile is simply the path of the file with the file name, everything in the prepared statement with the exception of DEL and the switch at the end.
I have a situation where deleting old records is blocking updating latest records on highly transactional table and getting timeout errors from application.
In details, I have one table called Tran_table1 in OLTP database. This Tran_table1 is highly transactional table, it will receive data for insert/update continuously
While archiving 2 years old records from Tran_table1 into Tran_table1_archive in batches(using DELETE OUTPUT INTO clause), if there is any UPDATEs on Tran_table1,these updates are getting blocked and result is timeout errors in application.
Is there any SQL Server hints to avoid blocking ..
How can you delete a log file.
Say I have backed up a transaction log at some drive say G:.
Now I want to delete this log file and want to define it in a job.
What is the syntax -
delete G:Test_log.bak ????
What is a proper syntax??
Thanks!
can some one please help me to delete a File with extension .mdf, as its continously giving error that file can't be deleted its being used by any other program or user.
Rahul Arora
07 Batch
NCCE Israna,
######################
IMPOSSIBLE = I+M+POSSIBLE
I am trying to remove the transaction log file since I have created another one on a seperate drive by it wont let me do it . I get this error "cant delete the primary data or log file"
Now microsoft site clearly state steps for deleting the transaction log file , by first emptying it and then deleting it BUT IT DOESNT WORK.
I have even tried detaching the database and then removing the transaction log file and then attaching the database again BUT it attaches the transaction file back again GRRRRRR
then on top of this MS SQL studio does not allow to put PRIMARY or anyother value to the Filegroup for tansaction log files so how on earth can a transaction file be "primary" as the error is stating ??????
can anyone tell me how to get rid of a transaction log file that was created with the database ?
Hi All
Was getting this error
Server: Msg 1101, Level 17, State 10, Line 1
Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth.
So went to EM & added another file (Additional file in some other drive ) to the Tempdb , now I want to delete this file ???
What are the steps ... I cant re-start the SQL Server
Please help .
Hi,
I can not delete this file from C/Windows/Temp directory, all other files form that foder I can delete. The error message I get is that something else is using this file. The thing is I cant find what is using it, even though I uninstaled SQL Server 2005 completely. I have tried lots of things, nothing helps. At the end I entered windows in SAFE MODE, went to Computer Management and disabled almost every service I can, that might be using it, and still cant get rid of this SQL.LOG which is 26GB now. The only thing I could do is to compress the file and now it is 6.5GB.
Txs for the help..
Hi ALL,
I need to delete 1st and last lines of text from a text file in an automated process,and save the file with the same name or a different name at the same location. How is that possible?Any help is greatly appreciated.
Thanks!!
Sai.
In SQL 2012 I'm trying to delete a log file (both physically and logically). There are two log files and one of them is unnecessary. When I click 'remove' in SSMS it will delete the log file, but then when I go back under database properties it's still showing up even though the file has been physically removed from the OS. I'm wondering what steps I can take to get rid of the file permanently?
View 12 Replies View RelatedWhen I delete substantial amounts of data using the SQL DELETE command, the database size apparently remains the same (702 mb) and the log file goes from about 17 mb to over 1 gig. I was expecting for the overall size to decrease drastically, but got just the opposite.
Is this typical? Can I do something to slim it down? As I am just trying to decrease the overall size to make it easier to work with when creating my application in VB, I am not worried about restoring the db (I have secure copies).
I have a flat file that look like this
############################################################
# Market Issue Lookup
# Applies to: Muni,Pfd,Govt,Corp
###########################################################
LU_MARKET_ISSUE| |AUSTRALIAN|Corp|
LU_MARKET_ISSUE| |BULLDOG|Corp|
LU_MARKET_ISSUE| |CANADIAN|Corp|
LU_MARKET_ISSUE| |WARRANTS|Muni|
LU_MARKET_ISSUE| |YANKEE|Corp|
############################################################
# Maturity Type Lookup
and i want it to look like this
LU_MARKET_ISSUE| |AUSTRALIAN|Corp|
LU_MARKET_ISSUE| |BULLDOG|Corp|
LU_MARKET_ISSUE| |CANADIAN|Corp|
LU_MARKET_ISSUE| |WARRANTS|Muni|
LU_MARKET_ISSUE| |YANKEE|Corp|
Basically remove any line that start with a "#" or any blank lines..
I am assuming you can do this only using a script component and not directly through ssis..but i am not too familiar with scripting...so some code would be helpful
Thanks for any help in advance.
smathew
I have maually deleted the Database.LDF file by stopping the SQL Server. When i start the SQL Server, merge replication which was configured for the database does not work. How do i fix this problem.
Thanks in advance.
Regards,
Venu.
Hi,
I'm new to SQL Server 2005 (up until now I've been an Oracle DBA exclusively) and have been tasked with backing up all (6) of our SQL Server 2005 databases on a regular basis. To accomplish this, I've created individual SQL Server jobs for each database I wish to backup. Each of these jobs executes a T-SQL script like this:
BACKUP DATABASE [MYDATA] TO DISK = N'\networksharemydata.bak' WITH RETAINDAYS = 4,
NOFORMAT,
NOINIT,
NAME = N'MyData-FullBackup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO
My goal in specifying 'RETAINDAYS = 4' was so that there would always be 4 days worth of "backupsets" (forgive my Oracle-ese) kept in mydata.bak. But when I look at the amount of backupsets kept in mydata.bak I noticed that all of them were being kept and none were being deleted.
I researched this problem a little bit and thought I had discovered a solution by specifying 'INIT' instead of 'NOINIT' and 'NOSKIP' instead of 'SKIP', but this deletes the entire contents of mydata.bak only-if all of the backupsets contained in it are past their expiration date. I was hoping it would just delete the expired backupsets and keep the non-expired ones, but this isn't the case.
Does anyone know the simplest way - with T-SQL commands - to accmplish the task of backing up a database to 1 individual .bak file and also only retain x amount of "backupsets" within it? Any help would be greatly appreciated, and the more detailed the better. Thanks.
- Gary
Hi,
I'm new to SQL Server 2005 (up until now I've been an Oracle DBA exclusively) and have been tasked with backing up all (6) of our SQL Server 2005 databases on a regular basis. To accomplish this, I've created individual SQL Server jobs for each database I wish to backup. Each of these jobs executes a T-SQL script like this:
BACKUP DATABASE [MYDATA] TO DISK = N'\networksharemydata.bak' WITH RETAINDAYS = 4,
NOFORMAT,
NOINIT,
NAME = N'MyData-FullBackup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO
My goal in specifying 'RETAINDAYS = 4' was so that there would always be 4 days worth of "backupsets" (forgive my Oracle-ese) kept in mydata.bak. But when I look at the amount of backupsets kept in mydata.bak I noticed that all of them were being kept and none were being deleted.
I researched this problem a little bit and thought I had discovered a solution by specifying 'INIT' instead of 'NOINIT' and 'NOSKIP' instead of 'SKIP', but this deletes the entire contents of mydata.bak only-if all of the backupsets contained in it are past their expiration date. I was hoping it would just delete the expired backupsets and keep the non-expired ones, but this isn't the case.
Does anyone know the simplest way - with T-SQL commands - to accmplish the task of backing up a database to 1 individual .bak file and also only retain x amount of "backupsets" within it? Any help would be greatly appreciated, and the more detailed the better. Thanks.
- Gary
How can we delete the column from SSIS file using ssis?
View 3 Replies View RelatedHi
i have to delete the master table data without deleting the child table records,is there any solution for this, parent table has relation with the child table.
regards
vinod.t.v
I have the following issue with Maintenance plan backups that work for BAK DIF and TRN to a remote server share.
When I try and remove the old files with a clean up task I get an error and the files don't get deleted.
The version is as follows
Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50
Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on
Windows NT 5.2 (Build 3790: Service Pack 2)
The error result is as follows,
Failed-1073548784) Executing the query "EXECUTE master.dbo.xp_delete_file
0,N'\\EXECUTE master.dbo.xp_delete_file 0,N'\ABCD-A1\BACKUPS\ABCD_BACKUP\ABC_DAILY\ABCD',N'trn',N'2008-01-13T12:52:49'" failed with the following error: "xp_delete_file() returned error 2, 'The
system cannot find the file specified.'". Possible failure reasons: Problems
with the query, "ResultSet" property not set correctly, parameters not set
correctly, or connection not established correctly.
The maintenance plan seems to be adding extra "" though when i enter the
code directly in a query i get same error.
Query:
EXECUTE master.dbo.xp_delete_file 0,N'\ABCD-A1BACKUPSABCD_BACKUPABC_DAILYABCD',N'trn',N'2008-01-13T12:52:49'
Error:
xp_delete_file() returned error 2, 'The system cannot find the file specified.'
The servers belong to the same domain and are using the same Service account which has all the necessary rights to the share and the file directory location. The backups work but i get the error on the cleanup task.
Trying to figure out how to get the Cleanup task to delete old files. The same happens for all file extensions and I have tried other locations with simpler file paths same error.
Regards,
Scott
/****** Object: StoredProcedure [dbo].[dbo.ServiceLog] Script Date: 07/18/2014 14:30:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[ServiceLogPurge]
-- Purge records dbo.ServiceLog older than 3 months:
-- Purge records in small portions to avoid locking production tables
-- for a long time. The process takes longer, but can co-exist with
-- normal usage of the tables.
[Code] ...
*** Getting this error below when executing the code ***
Msg 102, Level 15, State 1, Procedure ServiceLogPurge, Line 45
Incorrect syntax near 'Failed:'.
Hi, I m trying to update a table using an Excel file using the following code:
[sql]
UPDATE cargo
SET cargo.slot = (SELECT two
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:excel est.xls";Extended properties=Excel 5.0')...Sheet1$
WHERE cargo.container = one)
WHERE EXISTS (SELECT two
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:excel est.xls";Extended properties=Excel 5.0')...Sheet1$
WHERE cargo.container = one)
select container,slot from cargo
[sql]
Now, I have two similar databases, and both contain all the fields necessary for this operation and these fields are identical.
However on one table it works perfectly, on the other it gives an error:
MSDTC on server 'SCRBSQLITGTO002' is unavailable.
The server I am running is the only sql 2000 server we have, so it is cannot be in cluster mode.
Can anyone help?
Thanks
Azh
Hi All,I'm not that great with MS-SQL, as I never really have any occasion touse it. However, I need to get this one thing working and don't havea clue where to start.I have a comma-delimited file that's delivered to the server everynight that contains updates to one of the tables. I'm trying tocreate a DTS package that will read in the text file and update all ofthe records contained in it, rather than simply append them to thebottom of the table (resulting in duplicate entries).I know how to schedule it and such, but if anyone can give me sometips on the design of the DTS package, it would be much appreciated.Thanks!- Steve Osit
View 3 Replies View RelatedI have portions of data coming in as text files containing new records and updates of existing records. The solution I've figured out till yet is to import a portion of data into some intermediate table and then run a stored procedure to migrate the data into the real table.
Any ideas how to do this in a more efficient way?
Thanks in advance,
Asnate
Hi
I am new to asp.net world. I have a page with two formviews bound to two sqldatsources. One datasource connects to sql database and other to access. The information from both the databases is quite similar. The default mode for formview1 is edittemplate and for formview2 it is itemtemplate. I want to give the user an option to update formview1 data based on information retrevied in formview2 on a click of a button.
I want to do this in code behind .cs file. I am not sure how can I access the values from formview templates e.g formview1.itemtemplate... etc?
Can anyone please suggest a way to acheive this?
Thanks
I am trying to update a SQL table using an excel file which has 2 columns FMStyle and FMHSNum.
FMStyle is my link to the SQL table.
Here is what I have for code....
--------------------------------------------------
Update DataTEST.dbo.zzxstylr
SET hs_num = (select FMHSNum FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=c: empStyleHSCodesLoad.xls;HDR=YES ', [Sheet1$]))
Where FMStyle = zzxstylr.style
--------------------------------------------------
Everything seems to be ok except for the "Where FMStyle" is giving me a message Invalid Column name on FMStyle. Do I need to qualify FMStyle and if so how.