Removing Old Bak

Jan 15, 2008

Started with maintenance cleanup task set up bak and directory did not delete the files.

Then found this http://blog.solidsoft.com/blogs/richards_infrastructure_blog/archive/2007/07/12/369.aspx

Copy script

use master
declare @dt datetime
select @dt=getdate()-2
EXECUTE master.dbo.xp_delete_file 0,N'D:BackupAcctDB',N'BAK',@dt

Get message

Msg 22049, Level 16, State 1, Line 0
xp_delete_file() returned error 2, 'The system cannot find the file specified.'

Anyone else get problem with removing BAK files

View 4 Replies

Removing *

Aug 18, 2005

Hi! I am trying to remove an "*" I used to transfer a file from Excel to SQL. I put the "*" in the NULL values so that I would not get an error. Now that I have the file in SQL, I need to remove the :*" from some columns in my table. How can I select all of those in a column and delete them without deleting each individual one. Any ideas?

Thanks for your help.

Scott

View 6 Replies View Related

Removing Padding From Var

Feb 7, 2008

I have a search box to look up using a number as an identifier. Currently my query is where x = @enteredNumber, but some place where a user might get the number it is padded with zeros. We can have them just see the number and retype it.
EG: 000000123 would be entered as 123.
But, I would like to let them copy and paste, but then strip the zeros (left padding only).
EG:
@num = stripPadding(@enteredNumber)
IE:
@num = stripPadding(000000123)
@num = 123

View 2 Replies View Related

Removing A Default...

Sep 4, 2002

Hi,

I'm using SQL Server 7.0 SP3. I added a column to a table like so:

ALTER TABLE T704_RELATIONSHIP_INDEX_CLIENT_REPORT_STORE
ADD Universe_Member_Set_cd INT NOT NULL DEFAULT 1

What is the syntax for dropping the 'DEFAULT' from this column once it has been created? I've looked in BOL but can't seem to get the syntax correct.

Thanks in advance,
Darrin

View 1 Replies View Related

Removing The Duplicates

Aug 2, 2000

I need to remove the duplicates from a table. If a record exits 4 times in a
table I need to delete 3 records and retain only one occurance of that.

I need a query to do this.

Can anybody help.

-Rajesh

View 3 Replies View Related

Removing BuiltinAdministrators

Mar 20, 2002

Hi all,

I am using SQL 2k SP2 on Win 2K Advance server Cluster. My problem is how to remove builtinAdministrators login. Earlier when I tried it was doing failovers from one node to another and not stopping. I was not able to do anything. Any help is appriciated

thanks,
Minesh.

View 3 Replies View Related

Removing DB Suspect

Aug 31, 2000

I wonder if one can give a hint on remove a DB suspect,
Mind you I have used SP_dbremove still did not remove the DB
This is on SQL 7.0 box

Any idea welcomed

Saad

View 2 Replies View Related

Removing A Filegroup

Apr 25, 2000

Hello,

A few days ago one our database's default filegroup filled up. To solve the problem one of my colleagues created a new filegroup. This didn't solve the problem as no objects were placed on this filegroup and the initial one is still full. The idea was to expand the original filegroup over a new physical disk, not to create a new filegroup on that disk. Unfortunately, this is what happened.
To solve this I want to delete the new filegroup, but when I issue the command 'alter database PvgOmcsOds remove filegroup ternary' I get the message 'Server: Msg 5042, Level 16, State 7, Line 1 The filegroup 'ternary' cannot be removed because it is not empty.' Yet there're no objects placed on this filegroup.
How can I get rid of this filegroup?

Stef

View 2 Replies View Related

Removing Transaction Log From SQL 7 DB

Apr 7, 1999

I am trying to remove a transaction log that has grown too large for its databse and server. I have truncated the log and have run dbcc shrinkfile with the EmptyFile Option, but when I try to use alter database remove file and am I told that the object is not empty and cannot be removed. The server is in dbo use only.

What am I missing?

View 1 Replies View Related

Removing Filegroup

Mar 8, 2001

Hi all,

I am trying to remove one of the filegroups in the development database to shrink its size down. I started with emptying and removing its files and ran:
dbcc shrinkfile
(pubs_data_1,emptyfile)
go

and it ran fine, with 'DBCC execution completed' message

but when I tried :

alter database pubs
remove file pubs_data_1
go

I got 'pubs_data_1 cannot be removed because it is not empty'.
I even tried to go to each of the tables and delete from them first which didn't work, then drop all tables in that filegroup (I knew which tables are located on that filegroup), that didn't work either.

Any ideas? Is there a way to check what else sits on that filegroup?

Thanks

View 2 Replies View Related

Removing Replication

May 8, 2003

Hi,

We had transactional replication set up by a consultant with remote distributer, push subscription to the publisher (main production database). However, the subscriber machine had fatal crash and is unrecoverable. Now, replication jobs have been failing on distributor and we immediately need to disable and remove replication as it has started causing problems with transaction log backups on production database.

How can I completely remove this whole replication from publisher and distributor?

Please help.

Thanks,
Shaili

View 5 Replies View Related

Removing SQL 6.5 Software

May 28, 1999

Dear Concerned DBAs,

I am wanting to pick someones brain and find out if there is anything I can do to successfully remove and reinstall SQL Server Version 6.5? I have already removed the software from the SQL Setup icon in ProgramsMSSQL Server 6.5 and went and removed the setup.exe and mssql files associated with the software from the server. Now when I attempt to reinstall the software back onto the server after a hard reboot, I do not get the option once the SQL Server Version 6.5 disk is placed into the CD-Rom to install SQL Server. The only options I get are upgrade, server configurations, etc.

Can anyone or everyone give me a hand as to where to take this dilemma other than rebuilding the server from the ground up?

Anticipatorily Yours,
Daimon Russell

View 3 Replies View Related

Removing Certain Characters

Aug 18, 2006

Hello all,

I have a table named users. It consists of user names, user ids, etc... The problem is that whoever designed the ASP code before me allowed people to enter info in any format they want. This poses a problem because now the name can have 1, 2, or sometimes 3 spaces in between the last and first names. And sometimes the middle initial is used with a period following it. This creates problems when I am trying to execute a Select statement since it won't match if there are an unknown number of spaces in the string and throws an error when a period is used.

Is there a SQL query I can execute to change all the user_names into a format such as the following:
LastName, FirstName MiddleInitial

Like I said, it is already almost the same, just has too many spaces and some have periods after the middle initial

View 5 Replies View Related

Removing Duplicates

Jan 27, 2005

I have a members table and have added an extra few thoushand members to it. Now I need to remove the duplicates.

It doesnt matter which duplicate i remove as long as there are unique email addresses.

so here is the format of the table:

id
email
firstname
lastname
datebirth

if i do a:

SELECT COUNT(DISTINCT Email) AS Expr1
FROM Customer

it returns 21345

and

SELECT Count(Email)
FROM Customer

returns 28987

I can get the unique email addresses into another table by going:

SELECT DISTINCT emailaddress INTO DistinctCustomer
FROM Customer

but this will only return unique email addresses. How do i select distinct email address and all other fields into a new table? or just remove duplicates where email address appears more then once?

Thanks in advance

Tom the SQL beginner

View 5 Replies View Related

Removing A Paratiotion

Jun 24, 2008

i want to build a paratiotin which the key of the paratition will be a datre in this structure :
20080101235959
20080102235959
20080103235959
20080104235959
20080105235959
and so on.....
and i want that when there is a paration that it's "date" is more then 30 days to delete it (and that its data will be deleted too)
how do i do that?
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

View 5 Replies View Related

Removing Primary Key

Nov 27, 2005

Hi How do i remove the primary key for this table

create table table2
(col1 int,
col2 int,
primary key(col2))

Thanks
Vic

View 2 Replies View Related

Removing Last Two Char

Apr 25, 2007

Afternoon all

What is the quickest way to remove the last two char from a colunm and leaving the rest of the colunm intact.

cheers

View 4 Replies View Related

Removing Data

Jul 23, 2005

I have a table that I need to delete some data from and put the deleteddata into a different table.How do I script the following.If Field1 in Table1 is null, remove that row from Table1 and put it ina new table called Table2Regards,Ciar�n

View 5 Replies View Related

Removing Duplicates

Jul 20, 2005

HiI have inherited a web app with the following table structure, and need toproduce a table without any duplicates. Email seems like the best uniqueidentifier - so only one of each e-mail address should be in the table.Following http://www.sqlteam.com/item.asp?ItemID=3331 I have been able toget a duplicate count working:select Email, count(*) as UserCountfrom dbo.Membersgroup by Emailhaving count(*) > 1order by UserCount descBut the methods for create a new table without duplicates fail. My code forthe 2nd method is:sp_rename 'Members', 'temp_Members'select distinct *into Membersfrom temp_MembersTable....CREATE TABLE [dbo].[Members] ([MemberID] [int] IDENTITY (1, 1) NOT NULL ,[Username] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,[Password] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,[Email] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,[Title] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,[FirstName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,[Surname] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,[Address1] [varchar] (35) COLLATE Latin1_General_CI_AS NOT NULL ,[Address2] [varchar] (35) COLLATE Latin1_General_CI_AS NOT NULL ,[City] [varchar] (25) COLLATE Latin1_General_CI_AS NOT NULL ,[Country] [varchar] (25) COLLATE Latin1_General_CI_AS NOT NULL ,[Profession] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,[Publication] [varchar] (40) COLLATE Latin1_General_CI_AS NOT NULL ,[DateAdded] [smalldatetime] NOT NULL ,[SendMail] [smallint] NOT NULL) ON [PRIMARY]GOThanks B.

View 2 Replies View Related

Removing Zeros

Sep 24, 2007

Hi,
I have a column cost with sample record as '00003433' . how can i remove all zeros in left side alone?.

Out put : 3433.

Thanks
Raj

View 4 Replies View Related

Removing A Server Name

Mar 31, 2007

SQL
Upgrading to VS05 Pro SP1/SqlExpress (from VS03 Pro/SQL MSDE)

Question 1) �� is there a way to tell which version of the SQL Express was installed? I think it is ��SQL Server 2005 Express Edition With Advance Services�?, but don��t know how to confirm that.

Question 2) - SQL Server Management Studio Express, Registered Servers, it shows :
Database Engine
sqlexpress (note the lower case)
If I right click and choose ��Previously Registered Servers�?, it shows a message that I will be displaying SQL Server 2000 (i.e. MSDE) server names, and it shows
SQL Server Group
SQLEXPRESS (note the upper case)
CSOLUTION

I wish to completely remove CSOLUTION (in MSDE you would uninstall it from the Control Panel), but right clicking and choosing the delete option does nothing (it does not delete it). Deleting SQL Server Group also has no affect either. How do I remove CSOLUTION ?

Question 3) - In the VS05 program, the SQL connection string includes ��Data Source=.SQLEXPRESS��.�? (NOTE UPPER CASE) Why do I have a lower case and an upper case listed above? Starting/stopping one sqlexpress/ SQLEXPRESS does the same to the other. Don��t I wan��t to complete delete anything under SQL Server Group since it represents SQL Server 2000 (i.e. MSDE) server names? SQL Server 2000 and CSOLUTION have been uninstalled from this machine.

Thanks!
bob

View 1 Replies View Related

Removing BUILTINAdministrators

May 1, 2007

We need to remove the BUILTINAdministrators from reporting services. I.T. personnel do not want to be able to see restricted reports. We have two department users that have been added as Content Managers and we are running reporting services under a created domain user account that we created and not a system service. I have removed BUILTINAdministrators from the Home folder and when I check, it comes right back. Do I need to deny access to BUILTINAdministrator on SQL Server 2005 itself? I would apprecate any suggestions.

View 1 Replies View Related

Removing A Certificate

Apr 28, 2008

Hi i'm having issues removing this certificate

select * from sys.certificates

I see

cert_xp_cmdshell_enabler 257 1 NA NO_PRIVATE_KEY 1 Enable xp_cmdshell c5 30 7d 58 ba 8e 96 a6 48 48 df ca 87 bc 91 44 0x010600000000000901000000BEC4CB23E7994CFF1E71403F906BB1EFFDDEAF9A S-1-9-1-600556734-4283210215-1061187870-4021382032-2595217149 Enable xp_cmdshell 2009-04-28 10:31:31.000 2007-04-28 10:31:31.000 0xBEC4CB23E7994CFF1E71403F906BB1EFFDDEAF9A NULL

DROP CERTIFICATE cert_xp_cmdshell_enabler

he certificate cannot be dropped because one or more entities are either signed or encrypted using it.

Is there any way I can drop this as I would like to recreated it and add a whole host of sps that I need to use the xp_cmdshell.

Thanks in advance.

View 4 Replies View Related

Removing Databases

Jun 5, 2006

Using SQL Server Management Studio, I see 4 entries, master, model, msdb and temdb, under Databases -> System Databases. Which of these can I safely remove, if any?

Thank you

View 4 Replies View Related

Removing Row In Excel

Feb 25, 2008

Hi gurus,

I got a pre-formated excel sheet as destination. The header is formatted with color.

The requirement is to populate the excel sheet with different data type (name(string), no (int), sal(int), age(int)) from second row onwards. The source data is a SQL table and I am using SSIS to export data to the above excel destination.

Problem:
Scenario 1: Using SSIS data flow, populate the excel sheet. In this case, the format of the first row is used by all the new inserted records. So, I am not able to format as per the data type requirement.

Scenario 2: To populate data with required data types, I need to format the second row with the specified data types and then export data into the excel in the SSIS data flow. In this case, there is a blank row in the second row position as shown in the picture:
(see the picture on url)
http://picasaweb.google.co.uk/h.zulfi/SsisExcel/photo#5171040908106882370

To meet the requirement, I need to remove the blank row in the excel destination. It should look like below:
http://picasaweb.google.co.uk/h.zulfi/SsisExcel/photo#5171040912401849682

Is there any other method to achieve the export of data meeting the specified requirement?

If no, then is there any method to remove the blank row from excel apart from using com objects to remove the blank row and shift cells up right.

Any thought and help in this regard would be much appreciated.

Cheers,
Zulfi

View 4 Replies View Related

Removing Duplicates

May 19, 2008

Using a sort transform to remove duplicates takes a very long time to execute.Is there a better way to remove duplicate information.

I have alot of duplicates within the data.

I would like to know a better way than using sort transform

Please let me know

View 2 Replies View Related

How To Removing Document Map

Jan 30, 2007

I add document map to my report but I forgot

how can I remove it

thanks!

View 4 Replies View Related

Removing Weekends??

Oct 24, 2006

Hi, i'm new to the SQL game and have been given the task of removing all the weekends in a report so as it only shows the weeks as mon-fri.

I've checked out a few pieces of code but can't seem to get it to work. Anyone able to help?

View 6 Replies View Related

Removing Foreign Keys

Feb 1, 2004

Hi everyone,

I've been using VS.NET to design and implement my SQL databases. This has been simple and effective.

I've deployed the database onto the production database server and it's been performing without a hitch for some time now.

My problem is that I now need to remove a foreign key relationship on the production database as I have had a relationship between two tables but now not wanting the relationship since the data that had the relationship is now not wanting to be compulsory. I've had a look in the system.foreignkey (I think) table but I can't make head or tail of it.

Long story short, how can I find and remove the relationship from Query Analyser / Enterprise Manager ? I know how to do this in VS.NET diagram (just delete the relationship) but when you open the production database in VS.NET the diagram is no longer there.

Any help will be appreciated!!

Thanks!
Andrew

View 1 Replies View Related

Removing Characters From Column

Apr 19, 2001

I have a table called exchange and field called address. The rows(1400+) in the field look like:
MS:VA/Celcmv/VHACLEADAM%SMTP:Doe.Jane@med.va.gov%X200:c=US;a= ;p=av;o=Celcmv;s=Doe;g=Jane;

How do I remove everything to the left of doe.jane@med.va.gov and everything to the right of doe.jane@med.va.gov using query analyzer? Thank you in advance...

View 3 Replies View Related

Removing Identity Column

Jun 26, 2001

How to remove identity property from a column throught SQL statement?

View 4 Replies View Related

Suspect Db - Removing Replication

Jun 28, 2001

Hi all,

I have a db (with replication enabled) which was marked as suspect. As per the logs it is the Hardware crash that caused this db to become suspect.
Tried with sp_resetstatus to recover the db. But it is still in suspect mode. Changed the db into emercengy mode to recover the data thru' bcp which was succesful. Since there is no other way to recover the db, now I want to drop the db and create / restore the db from backup.
I am unable to drop the db becasue it is having a publication. I am also unable to drop publication becasue now the db is in emergency mode.
Kindly help and inform how I can drop the db and create it again from the backup without reinstalling SQL Server again.

TIA.

Ravi

View 1 Replies View Related

Removing Characters From A Column

Sep 19, 2001

Can someone please suggest a function to remove the last 3 characters from a column? I was thinking of the LEN function, but I am unsure of the syntax.

Thanks!
Lisa

View 5 Replies View Related