Removing Existing Connecitons In A DTS
Feb 11, 2004is it possible to remove connections created in a DTS?
View 2 Replies
ADVERTISEMENT
Removing Special Characters From Existing Data
Jun 1, 2006I have a table with several columns of information that I wish to set up some form of schedule to go through this data and remove any special characters that may interfere with other code processes.
Mainly the coma's and the apostrophes. It really messes with my asp pages and scripts when retrieving this information and trying to do other things with it, so I need to figure out how to remove these from the tables so it does not cause these issues.
Knowing this, I cannot figure out how to keep the data in the row/column and just extract the special characters from that data. The other problem is, everything I try requires me to insert either a coma or apostrophe as part of the code string which in lies my issue.
How can I parse through my data, leave the data as-is, but just get rid of coma's, apostrophes, and double quotes?
Does anyone have a basic example that I can use to expand on?
Dropping Or Removing The Identity Property From An Existing Column
Mar 7, 2008How do i drop/remove the identity property for an existing column in all Tables where the Identity column is a primary key.
The Script below was used to find all the tables that have an Identity Column as a primary key in a database. Now i want to remove the identity property from the Identity Columns that have a primary key in the database.
select pk.table_name, c.column_name,
from information_schema.table_constraints pk
INNER JOIN information_schema.key_column_usage c ON c.TABLE_NAME = pk.TABLE_NAME
and c.constraint_name = pk.constraint_name
where constraint_type = 'PRIMARY KEY'
and COLUMNPROPERTY(object_id(pk.table_name), column_name, 'IsIdentity') = 1
ORDER BY pk.table_name, c.column_name
How Can You View The Connection String Of An Existing Datasource? How Do You Delete Existing Dataset?
Nov 14, 2007I went to look at the connection string previously entered for a dataset created in a new report, and am not seeing anything intuitive for bringing up the associated datasource dialog box that was used to enter name, type and connection string. I'm also noticing nothing intuitive for deleting an existing dataset. How do you do these two very simple things in an existing project? I dont see the dataset in solution explorer, I see it only in the text box on the data tab and in a limited kind of way on the dataset view where the columns show and maint is allowed mostly on the columns only. I tried hilighting the dataset here and hitting the delete key to no avail.
View 1 Replies View RelatedForce To Close Existing Connections When Restoring Existing Database
Aug 13, 2007Hi All,
I would like to restore database using RESTORE DATABASE ... REPLACE command.
If database exists already and has any open connections this command will fail.
I would like to close all existing connections to specific database before running RESTORE DATABASE ... REPLACE command.
I can do closing from Management Studio using checkbox "Close Existing Connection" when deleting database. Actually I need to do the same but from script.
Please advice me how to do it.
Thanks in advance,
Roman
Removing *
Aug 18, 2005Hi! 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
Removing Old Bak
Jan 15, 2008Started 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
Removing Padding From Var
Feb 7, 2008I 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
Removing A Default...
Sep 4, 2002Hi,
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
Removing The Duplicates
Aug 2, 2000I 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
Removing BuiltinAdministrators
Mar 20, 2002Hi 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.
Removing DB Suspect
Aug 31, 2000I 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
Removing A Filegroup
Apr 25, 2000Hello,
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
Removing Transaction Log From SQL 7 DB
Apr 7, 1999I 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?
Removing Filegroup
Mar 8, 2001Hi 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
Removing Replication
May 8, 2003Hi,
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
Removing SQL 6.5 Software
May 28, 1999Dear 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
Removing Certain Characters
Aug 18, 2006Hello 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
Removing Duplicates
Jan 27, 2005I 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
Removing A Paratiotion
Jun 24, 2008i 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 -:)
Removing Primary Key
Nov 27, 2005Hi How do i remove the primary key for this table
create table table2
(col1 int,
col2 int,
primary key(col2))
Thanks
Vic
Removing Last Two Char
Apr 25, 2007Afternoon all
What is the quickest way to remove the last two char from a colunm and leaving the rest of the colunm intact.
cheers
Removing Data
Jul 23, 2005I 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 RelatedRemoving Duplicates
Jul 20, 2005HiI 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 RelatedRemoving Zeros
Sep 24, 2007Hi,
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
Removing A Server Name
Mar 31, 2007SQL
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
Removing BUILTINAdministrators
May 1, 2007We 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.
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.
Removing Databases
Jun 5, 2006Using 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
Removing Row In Excel
Feb 25, 2008Hi 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
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
How To Removing Document Map
Jan 30, 2007I add document map to my report but I forgot
how can I remove it
thanks!
Removing Weekends??
Oct 24, 2006Hi, 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?