I am looking for some T_SQL code that would help us eliminate
duplicate data entry into our mailing db. Finding and removing the
existing dups is not rocket science, but I am looking for a complete
solution.
We also want to be able to come up with a T-SQL script to be
intelligent enough to know if:
"Bill Jones" is the same as "Billy Jones" or
"Bill Jones" is the same as "William Jones" or
"Bill Jones at 123 My street" is the same as "Bill Jones at 123 My
St."
Purchasing scripts from a company is not out of the question. Its a
matter of script cost versus my hourly rate to create them... Needless
to say, something cheap.
We have only heard of one app that is dynamic enough to handle this,
and the cost was way, way out of our range, 6 figures is almost 2
years salary.
SELECT * from table where city like '%woshington%' Now this would return nothing, as there is no city like woshington. but google find results and ask as r u looking washington. so would u please tell how I may write an intelligent SQL like query
First, I installed the sql server 2005, Second, I installed the VS.net 2005 (vb.net). After that, I uninstalled the VS.NET 2005. Then I found after I uninstalled the VS.NET, the Business Intelligent Management Studio is gone too. I install the VS.NET 2005 again.
Now I could use the Business Intelligent Management Studio to open a SSIS project and a package, but there is nothing in the toolbox in it. Also the IDE could not recognize any object inside the ETL package, like the Task, the Compoment, etc. So I think that the Business Intelligent Management Studio is still not be really installed. The IDE is the VB.NET, and something needed to deal with the ETL package is missing.
How to fix the Business Intelligent Management Studio? I mean, how to make my vb.net to deal with an ETL package agian?
Hi all, I run several contact websites. One of them is www.ramc.org.uk - they used to fill in a form and results emailed to me. I then manually copied and pasted into page on server. Very tiresome.
I was hoping with sql express i could create a page where they enter there details into the database after verifying details via a link..... Then as the new details are added they would be displayed on the contacts page in alphabetical order.
hi all i try to get data from sql as select * from datetable where @d between firstdate and lastdate i add parameter @d and the type is datetime when i excute the quiry
An error occurred while reading data from the query result set.Arithmetic overflow error converting expression to data type smalldatetime. how can i fix that plz
I desperately need to setup the AD-HOC reporting on SQL server 2005. I think one of the templates that I need is in Business Intelligent Project Templets Reporting Model Template in Visual Studio Templates.
But I do not see it in there. Can some one please let me know where can I get the Reporting Model Template, or a link to where I can download or how to create it.
I've been struggling with an interesting problem. I currently have a solution but it is very slow.
I will be cycling data through a table. Each cycle has 1 million records with 60 fields. One procedure I need to perform on this data is a character cleanse. I have a list of 12 characters that need to be removed.
Right now I have a stored procedure that pulls the characters from a table one at a time. It feeds it to a nested loop that replaces the character with nothing ('') on records that contain the character (something like "update tbl1 set FIELD = replace(FIELD, '&', '') where Field like '%&%'"). This works... but seems rather inefficient. It can take 10 minutes to do a 250,000 record table.
I have tried borrowing regular expressions from VBscript using com objects, it worked and seemed more efficient at first but then I threw a large file at it and it took a half hour to complete.
Im running SQL 2005 on a dual Xeon 3.4 box with 2 gb of ram.
We can use rs.exe to publish a report automatically, but how do we do the reverse - remove a report automatically?
Is there a way by using a MS utility?
Is there a way by writing code, using library objects?
Part of the problem we face is having servers with RS all set up with a particular connection string and published reports. We then want to change everything and we want to do it automatically.
I have a situation where my client wants to ensure that data which is removed from a SQL Server database is truly removed. That is, if I delete a record, or delete data from a row, can I be sure that the data is truly gone and that it's not just the internal B-Tree pointers that were removed? Can I trust that someone with access to the .mdb file could not perform some binary pattern matching on the file and find the data that I assumed was deleted (regardless of how difficult that task would be)? I'm using SQL Server 2000. Thanks in advance.
Hi, Ive got a server which replicates an awful lot of constantly changing data - the db itself could be around 2gb in size, however with the replication data in it it has ballooned to 6gb. On top of this, there is all the data in the D:MSSQLREPLDATAunc directories which seem to represent another log of some description.
Can I delete this data? I cant see what it would be used for and it totals some 25Gb! Obviously I cannot have this lying around on my server.
Does anyone know what it is used for? thanks Pete Storey
I currently have a lot of XML blobs being stored in several of the production databases that I look after. They're there as a application "logging" feature, so that the developers can investigate issues if they ever occur.
I want to get these out of my OLTP databases asap and am currently looking at options. I know Brent Ozar recently posted an article about Content Addressable Storage systems [URL] ....
How do you handle point in time recovery? Any other ways of storing this data?
is there a way to ensure a merge subscription is synchronised before it is removed from the publisher using SMO? - (it is a push subscription)
I thought I had it as there is a MergeSynchronizationAgent object on the SubscriptionDatabase object, but calling Synchronize() on this throws an exception saying it can't contact the publisher.
I created a setup package and installed it on my SQL2005 server. I cant seem to find anywhere on the server where they are installed, but I can access them from the Job schedule tool, which work for me.
However, now I have to upgrade the deployed SSIS and I cant seem to install on top of an existing one (gave me no errors but after testing it i realized it is still running the old version.)
My questions are:
1) How can I find and delete these SSIS packages from the server? 2) How can I just upgrade for a new version of a SSIS package?
I need to be able to do both things as at some point I may want to just remove some packages.
We needed to detach a database that contained a target ServiceBroker service and wanted to do this without impacting the rest of our system. The idea was the ServiceBroker would enqueue message to this service to be pickedup when the database was re-attached. Our sequence to do this was:
1. Disable the queue on the target service forcing ServiceBroker to queue in the initating side transmission queue.
2. Detach the database.
3. Re-attach the database.
4. Re-enable the queue
Messages sent to the service have a specified LIFETIME. What appeared to happen was that messages were being stored in the transmission queue as expected but on hiting the LIFETIME period they were being removed from the transmission queue and were therfore "lost".
Has anyone else experienced this or can anyone suggest what we are doing wrong.
Incidentally, beaware that detaching a database will disable ServiceBroker in that database when it is re-attached - we discovered that one the hard way :-)
Hi everyone, I am using this temporary data table which gets cluttered after certain time (table is used for registering data waiting for email confirmation). Is there a possibility to empty a data table automatically every day (at a certain moment)? Kind regards,Maxime
I need help using the TSQL Command.... I want to display car reviews by authors.... a car may be reviewed by multiple authors.... but the latest entry should be displayed... the older enteries will not be displayed....the following is the query </P><FONT color=#0000ff size=2> <P><FONT color=#000000>SELECT CarReviews.Date AS Date, CarReviews.Company AS Company, CarReviews.Name AS Reviewer, CarMake.MakeName, CarModel.ModelName, CarModel.YearName, CarMake.KeyName + '/' + CarModel.Image AS Image, CarReviews.Id AS IdFROM CarReviews INNER JOIN CarMake ON CarReviews.MakeId = CarMake.Id INNER JOIN CarModel ON CarReviews.ModelId = CarModel.IdGROUP BY CarMake.MakeName, CarModel.ModelName, CarModel.YearName, CarMake.KeyName + '/' + CarModel.Image, CarReviews.Id, CarReviews.Name, CarReviews.Company, CarReviews.DateORDER BY CarReviews.Date DESC</FONT></FONT></P><FONT color=#0000ff size=2> <P></P></FONT> <P>Results of above statement.... Date Company Reviewer MakeName ModelName YearName Image Id ----------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------- ----------------------------------------------------------- ----------- 10/3/2005 5:12:25 PM LATimes.com Dan Neil MINI Cooper S 2005 RAJ-0122/RAJ-0122-82.jpg 8 10/3/2005 5:11:41 PM Star-Telegram.com G. Chambers Williams III MINI Cooper S 2005 RAJ-0122/RAJ-0122-82.jpg 7 10/3/2005 4:51:31 PM MotoFlare.com Joe Wiesenfelder MINI Cooper S 2005 RAJ-0122/RAJ-0122-82.jpg 6 10/3/2005 4:49:22 PM MotoFlare.com Jim Flammang MINI Cooper S 2005 RAJ-0122/RAJ-0122-82.jpg 5 10/1/2005 4:19:25 PM MotoFlare.com Erphan Rajput Audi A4 (2005.5) 2005 RAJ-0089/RAJ-0089-80.jpg 3 10/1/2005 4:19:25 PM MotoFlare.com Jim Flammang Audi A4 (2005.5) 2005 RAJ-0089/RAJ-0089-80.jpg 4 Problem is repeation... In the above statement .... the statement should return only 2 records... but it is displaying 6 records....record one should be of MINI Cooper S 2005 & second should be of Audi A4 (2005.5) 2005Urgent reply will be highy appreciated...Thanks,Erphan Rajput..
I am working with one of the production database around 200 GB. This database has above 350 tables and more than 500 Indexes. I am feeling the database has so many Indexes than the required ones
When I run the below query, it gives me some indexes read value "0". The server was restarted a month ago.Is it ok to remove those indexes?
SELECT OBJECT_NAME(s.[object_id]) AS [Table Name] , i.name AS [Index Name] , i.index_id , user_updates AS [Total Writes] , user_seeks + user_scans + user_lookups AS [Total Reads] , user_updates - ( user_seeks + user_scans + user_lookups ) AS [Difference]
We have a N+1 SQL 2005 x65 SP2 + 3159 cluster which was running fine until the service account which runs Windows cluster and the SQL services was removed as a login from SQL. This was a new setup so the only SQL accounts are (SA) as SA, Windows Domain GROUP's which one group is configured as SA and the cluster account is added to this group. I cannot start SQL service on the cluster now. I've tried logging into one node as the service account which is member of a AD group which has SA rights to SQL as well as my personal account. If anyone knows a way ot fix this without reinstalling I'd appreciate your help.
I am going crazy trying to figure out how to do this. I have a flat file which I am massaging the data and loading into a table here is an example of a line out of the flat file:
My problem is that I have one field which is this address in a concatinated form. The fields that do not apply to this entry are suite#, floor# and other columns which are designated by the "" characters. The final concatinated addres field looks like this:
"550""""N""FAIR OAKS"""
I would like to remove the "" characters in the concatinated string. I just don't know the best way to do this? I was told DTS had a way of removing the "" from the flat file source. Since I have not used DTS extensively I am not sure if this is true. I was wondering how in SSIS I could go about removing the "" marks without removing the "" say if someone is quoted eg. John said "This is only a test". Removing the quotation marks in this instance would be changing the data. I am not sure how to do this and any help or advice is greatly appreciated!
I've done already the schemas for all the tables a you suggested.But when I created a new table the dbo prefix (dbo.ADM.NewTable) appears.I'd like this dbo not to automatically appear whenever i created a new table — as it requires me to alter whenever I created a new table.Is this something that is required to determine the creator of the table?Otherwise, I'd like that to remove.
I'm looking to dynamically remove records from tables dynamically using the information schema within SQL Server. Looking to remove records from all the tables within a schema. I have gotten as far as generating the script dynamically then using a while exist clause to execute the delete statements.
I am using the Import/Export wizard to import data from an ODBC data source. This can only be done from a query to specify the data to transfer.
When I try to create the tables, for the query, I am getting the following error:
Msg 2714, Level 16, State 4, Line 12
There is already an object named 'UserID' in the database.
Msg 1750, Level 16, State 0, Line 12
Could not create constraint. See previous errors.
I have duplicated this error with the following script:
USE [testing]
IF OBJECT_ID ('[testing].[dbo].[users1]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users1]
CREATE TABLE [testing].[dbo].[users1] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
IF OBJECT_ID ('[testing].[dbo].[users2]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users2]
CREATE TABLE [testing].[dbo].[users2] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
IF OBJECT_ID ('[testing].[dbo].[users3]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users3]
CREATE TABLE [testing].[dbo].[users3] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
I have searched the "2714 duplicate error msg," but have found references to duplicate table names, rather than multiple field names or column name duplicate errors, within a database.
I think that the schema is only allowing a single UserID primary key.
I'm working to improve performance on a database I've inherited, and there are several thousand indexes. I've got a list of ones which should definitely exist within the database, and I'm looking to strip out all the others and start fresh, though this list is still quite large (1000 or so).
Is there a way I can remove all the indexes that are not in my list without too much trouble? I.e. without having to manually go through them all individually. The list is currently in a csv file.
I'm looking to either automate the removal of indexes not in the list, or possibly to generate the Create statements for the indexes on the list and simply remove all indexes and then run these statements.
As an aside, when trying to list all indexes in the database, I've found various scripts to do this, but found they all seem to produce differing results. What is the best script to list all indexes?
I have been researching on the proper steps or sequence to follow to completely remove SQL Server 2012 Transactional Replication. I have read articles about using SSMS as well as using replication stored procedures and some procedures use SQLCMD or just regular TSQL executed in SSMS. I have also read articles where people said all you really need is connect to the Publisher instance, find the publication you want to remove and choose "Delete" and everything will be taken care of behind the scene. I have three SQL servers that participate in transactional replication. SQL-P (publisher),
SQL-D (distributor) and SQL-S (subscriber). Do I need to connect to the distributor instance and the subscriber instance when removing transactional replication or is it just really connecting to the publisher and click delete on the publication? I want everything gone including any metadata, systems tables, distributions db and any other replication objects created during the initial configuration.
I've begun to get the above error from my package. The error message refers to two output columns.
Anyone know how this could happen from within the Visual Studio 2005 UI? I've seen the other posts on this subject, and they all seemed to be creating the packages in code.
Is there any way to see all of the columns in the data flow? Or is there any other way to find out which columns it's referring to? Thanks!
How can I compare numerous rows in the same table that have the same fname, lname and access_id but different identity values (and other columns) and then keep only the one row which is the most current based on a date col?
This seems so easy, but today I just can't come up with it. Do I have to use a cursor of some kind?
insert into Table1 select distinct FKID from Table2 where FKID not in (select PKID from Table1)
The above SQL statement is in a DTS package which raises "Duplicate key ignored" error... i can't see how that can happen since i am already checking the key if it already exists. ..
Hi! Very grateful for some advice... duplicate key.. but how do I solve it. By Lookup ? [OLE DB Destination [1647]] Error: An OLE DB error has occurred. Error code: 0x80040E2F. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "Violation of PRIMARY KEY constraint 'PK_Dim_Date'. Cannot insert duplicate key in object 'dbo.Dim_Date'.".
OLE db SQL: SELECT DISTINCT convert (char(8),date,112) as day, cast(datepart(year, date) as varchar(4)) + cast(datepart(week, date) as varchar(2)) as weeknr, cast(datepart(year, date) as varchar(4)) + Substring(convert(varchar(10), date, 121), 6, 2) as month, YEAR(date) AS year FROM Purchase
I'm currently working on a Game DB, To where i can set Primary Key's and such, otherwise the Emu reads it wrong. The current table i am working on, has duplicate row's of every row. (3600 rows instead of 1800) and the duplicates have the same ID as the original row. How would i go about Deleting the Duplicate rows without harming the original? (Sql 2005)
I have a stored procedure that inserts records into a table with a Unique Clustered Index with ignore_Dup_Key ON.
I can run the stored procedure fine, and get the message that duplicate keys were ignored, and I have the unique data that I want.
When I try to execute this in a DTS package, it stops the package execution because an error message was returned.
I have tried setting the fail on errors to OFF, but this has no effect.
I found the bug notification that says this was corrected with service pack 1, and have now updgraded all the way to service pack 4, and still get the issue.
I tried adding the select statement as described as a work-around in the bug, and still can't get it past the DTS.
I have verified the service pack, re-booted, etc.....
