Duplicate Removal and Intelligent T-SQL

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.

Any ideas would be greatly appreciated.funkdm1@.yahoo.com (Dave) wrote in message news:<f5174e0f.0406071223.73e8fc2d@.posting.google.com>...
> 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.
> Any ideas would be greatly appreciated.

This may help - I've used it successfully to do fuzzy matching of
duplicate company names:

http://www.winnetmag.com/SQLServer/...6094/26094.html

Simon|||Simon,

That was a very interesting article. It also came with about 1200
lines of t-SQL. I dont think thats exactly what I need, but I can
definetely use pieces of it.

Thanks

sql@.hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0406072358.1cb5c387@.posting.google.com>...
> funkdm1@.yahoo.com (Dave) wrote in message news:<f5174e0f.0406071223.73e8fc2d@.posting.google.com>...
> > 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.
> > Any ideas would be greatly appreciated.
> This may help - I've used it successfully to do fuzzy matching of
> duplicate company names:
> http://www.winnetmag.com/SQLServer/...6094/26094.html
> Simon|||>> I am looking for some T_SQL code that would help us eliminate
duplicate data entry into our mailing db .. not rocket science, but I
am looking for a complete solution. <<

Yes, it is :) Get a package and save yourself 5-10 man-years.
Call the NCSC at 800-238-3150 and request the following free
publications:

PUB 25 - Designing Business Letter Mail
PUB 28 - Postal Addressing Standards
PUB 221 - Addressing for Success

Buy a mailing list product to scrub your data. It is much harder than
it looks. Here is an old list of companies that sell ZIP code and
mailing related product:

BCC Software Inc.
39 Saginaw Drive #630
Rochester, NY 14623

Comco, Inc.
2211 Grant Street
Box #349
Bettendorf, IA 52722

CD Light
8861 South Silverstone Way
Sandy, UT 84093
CD-Rom of ZIP+4 for $30

Dassgar Systems
Box #915
Bennington, VT 05201-0915
Global-Z; international postal codes for lots of places

Eric Isaacson Software
416 East Bloomington
Bloomington, IN 47401-4739
demo version of ZipKey

Group 1 Software
4200 Parliament Place #600
Lanham, MD 20706

Melissa Data Corporation
32122 Paseo Adelanto
San Juan Capistrano, CA 92675

Search Software America
1445 East Putnam Ave
Old Greenwich, CT 06870

(ask for their booklet "The Math, Myth & Magic of Name Searching and
Matching". It is quite informative)

For 'UK postcode databases', the Royal Mail holds the copyright and
will licence you an 'outwards' database (i.e. the first segment of the
full postcode) for about 150 a year, or an 'inwards' database (i.e.
the full 9- or 10-character postcode) for about 500 UK pounds per
year.

KJL Software (www.kjlsoftware.com) gives you 5-digit ZIP Code, City,
State,USPS Status Code, LATEST Area Code(s) from NANPA, Time
Zone,Latitude, and Longitude for all valid US Postal Service 5 digit
ZIP Codes/City/State combinations and a Distance Calculator.|||I'd also recommend http://www.qas.com/

We use them for address checking/changing and there is additional
functionality (in the UK at least) to check against the electoral role
to verify if someone is at a specific address.

Failing that, UK wise, Experian or Equifax may be of some help. I'm
not sure what they do outside the UK though.

Hope that helps.

duplicate primary key error

Im gettig the error "

---

Violation of PRIMARY KEY constraint 'PK_Employees'. "

for the following code..i cant seem to find the error in the code.

---

'data adapter

Dim dAdapt As New SqlClient.SqlDataAdapter

'create a command object

Dim objCommand As New SqlClient.SqlCommand

'command builder

Dim builder As SqlClient.SqlCommandBuilder

'connection string

Dim cnStr As String = "Data Source=ELEARN-FRM-BETA;Initial Catalog=StudentPlayGround;Integrated Security=True"

'connection

Dim cnObj As New SqlConnection(cnStr)

'dataset

Dim ds As DataSet

Dim courseID As Integer

Dim courseName As String

Dim desc As String

Dim maxT As Integer

Dim sql As StringBuilder

Dim cmdTxt As String

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

If Not IsPostBack Then

BindData()

End If

End Sub

Private Sub BindData()

connect()

DataBind()

End Sub

Private Sub connect()

'connection

objCommand.Connection = cnObj

'query string

Dim query As String = "SELECT * from StudentPlayground..Courses"

'setting the select command

dAdapt.SelectCommand = New SqlClient.SqlCommand(query, objCommand.Connection)

'dataset

ds = New DataSet("Course Listings")

cnObj.Open()

dAdapt.Fill(ds, "Courses")

cnObj.Close()

End Sub

Protected Sub submitButton1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submitButton1.Click

courseID = CInt(CType(FindControl("TextBox1"), TextBox).Text)

courseName = CType(FindControl("TextBox2"), TextBox).Text

desc = CType(FindControl("Textbox3"), TextBox).Text

maxT = CInt(CType(FindControl("TextBox4"), TextBox).Text)

REM Build our parameterized insert statement

sql = New StringBuilder("INSERT INTO Courses ")

sql.Append("(CourseID,CourseName,Description,MaxTrainees)")

sql.Append("VALUES (@.courseID,@.courseName,@.desc,@.maxT)")

cmdTxt = sql.ToString

REM Add parameter values to command

REM Parameters used to protect DB from SQL injection attacks

With objCommand.Parameters

.Clear()

.AddWithValue("@.courseID", courseID)

.AddWithValue("@.courseName", courseName)

.AddWithValue("@.desc", desc)

.AddWithValue("@.maxT", maxT)

End With

objCommand.CommandText = cmdTxt

objCommand.Connection = cnObj

REM Now execute the statement

'connection

cnObj.Open()

objCommand.ExecuteNonQuery()

cnObj.Close()

objCommand.Dispose()

submitButton1.Text = "Record Added!"

End Sub

Well in that particular listing, I don't see any insert into an Employees table. Are you familiar with the PK_Employees primary key constraint in your schema? What table does it exist on (my guess is employees) and on what colum(s) is it on?

I would look at what is going on when the one insert shown above does occur. Are there any triggers entering info that violates this constraint?

If not, I would reanalyze and make sure this is really the section of code causing this problem.

|||checkout whick table has this primary key defined 'pk_employees' (if u r not aware , use the syskeys system table to get that info) . ur code may be adding (repeating)values to that table either directly or thru a trigger.....try to resolve it from there|||

My Apologies...the error is Violation of PRIMARY KEY constraint 'PK_Courses'. Cannot insert duplicate key in object 'dbo.Courses'. This is on the CourseID column in the table Courses. there r no triggers on this table.

aThe strange thing is even though this error appears..my record is still getting inserted correctly in the db. Also this is the ONLY code-beind code present for this page. I just left out the import statements and classdefinition in my post.

|||

The PK constraint error is thrown because of a duplicate "value" (Course) not key attempting to insert into the table.

You can sloppily add On Error Resume Next into your vb.net so you don't see the server side errors, or you check for dupes before you insert records.

Adamus

|||there are no duplicate courses inthe table..i try and add new unique courses each time and experience the error.|||

Do you have a concatenated key or one primary key?

I've experienced similar errors using concatenation and had to remove all constraints and add one key at a time, then one record at a time until I found the error.

Adamus

|||just one primary key on the id field. thats it. no triggers.|||

1. Remove the key - drop the constraint (sp_helpconstraint)
2. Relocate the records
3. Recreate the key
4. Re-import the records

When in doubt, resort to the basics.

Adamus

sql

Duplicate Inserted Record

Hi Everybody

This Code duplicate the record in the database, can somebody help me understand why that happen.

Thanks a LOT

CompanyName:
<asp:textbox id="txtCompanyName" runat="server" /><br />
Phone:
<asp:textbox id="txtPhone" runat="server" /><br />
<br />
<asp:button id="btnSubmit" runat="server" text="Submit" onclick="btnSubmit_Click" />
<asp:sqldatasource id="SqlDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:dsn %>"
insertcommand="INSERT INTO [items] ([smId], [iTitleSP]) VALUES (@.CompanyName, @.Phone)"
selectcommand="SELECT * FROM [items]">
<insertparameters>
<asp:controlparameter controlid="txtCompanyName" name="CompanyName" />
<asp:controlparameter controlid="txtPhone" name="Phone" />
</insertparameters>
</asp:sqldatasource>

VB

Partial Class Default2
Inherits System.Web.UI.Page

Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
SqlDataSource1.Insert()
End Sub
End Class

--------------

Yes is an Identity the Primary Key of the Table items

Please include the btnSubmit_Click ~ Code ~

Also, verify that the primary key in items is an identity

DK

|||

You have the click event hooked up twice (remove one of the red sections):

CompanyName:
<asp:textbox id="txtCompanyName" runat="server" /><br />
Phone:
<asp:textbox id="txtPhone" runat="server" /><br />
<br />
<asp:button id="btnSubmit" runat="server" text="Submit"onclick="btnSubmit_Click" />
<asp:sqldatasource id="SqlDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:dsn %>"
insertcommand="INSERT INTO [items] ([smId], [iTitleSP]) VALUES (@.CompanyName, @.Phone)"
selectcommand="SELECT * FROM [items]">
<insertparameters>
<asp:controlparameter controlid="txtCompanyName" name="CompanyName" />
<asp:controlparameter controlid="txtPhone" name="Phone" />
</insertparameters>
</asp:sqldatasource>

VB

Partial Class Default2
Inherits System.Web.UI.Page

Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs)Handles btnSubmit.Click
SqlDataSource1.Insert()
End Sub
End Class

sql

Duplicate Entries

I have an issue where certain parts of data are repeated several times after i create my query. Without providing my SQL code for now could anyone suggest possibly the main reason(s) for data being duplicated?

Thanks

I reckon its because either.....

a) you have duplicate data in your tables

b) you are doing a JOIN to a table with a one -> many relationship

And as a solution i reckon you could....

a) use DISTINCT in your select statement

Am I close?

|||

I think your very close:

a) Definately not as i recreated another query without all the fields that i originally needed and had NO duplicate items.

b) This maybe it but ill have to investigate it and get back to you.

Based on your answers and solution

1. How can i easily define which tables may have 1-Many relationship?

2. Where would i use the Distinct statement?

Thanks

|||

1. Don't think you can programmatically find out 1->Many relationships. Hopefully you have your relationships defined by foreign keys which should point you in the right direction. Maybe use sp_fkeys to help identify them eg EXEC sp_fkey 'YourTable', 'dbo'

2. SELECT DISTINCT Col1, Col2, Col3.....

Check Books Online for more details.

sql

Duplicate a row in SP code

I'm sure there's a simple way to do it, I just haven't run into it yet:
I just want to duplicate a table record (row) using a stored procedure.
lqThe solution depends on exactly what you mean by 'duplicate'. Hopefully,
the following will get you started:

CREATE TABLE MyTable1
(
Col1 INT NOT NULL
CONSTRAINT PK_MyTable1
PRIMARY KEY
)

CREATE TABLE MyTable2
(
Col1 INT NOT NULL
CONSTRAINT PK_MyTable2
PRIMARY KEY
)

INSERT INTO MyTable1 VALUES(1)
INSERT INTO MyTable1 VALUES(2)
GO

CREATE PROCEDURE CopyRow
@.Col1 int
AS
INSERT INTO MyTable2
SELECT Col1 FROM MyTable1
WHERE Col1 = @.Col1
GO

EXEC CopyRow 1

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0402180610.60f76e50@.posting.google.c om...
> I'm sure there's a simple way to do it, I just haven't run into it yet:
> I just want to duplicate a table record (row) using a stored procedure.
> lq|||What do you mean by "duplicate a row"? Normally we aim to avoid duplicating
data in a database but here are two examples taken from Pubs.

Iinsert a duplicate of a row into a table:

INSERT INTO Authors
(au_id, au_lname, au_fname, phone, address, city, state,
zip, contract, total_sales, ytd_sales)
SELECT <new PK value>, au_lname, au_fname, phone, address, city, state,
zip, contract, total_sales, ytd_sales
FROM Authors
WHERE au_id = '172-32-1176'

Return duplicates of a single row:

SELECT au_id, au_lname, au_fname, phone, address, city, state,
zip, contract, total_sales, ytd_sales
FROM Authors
CROSS JOIN (SELECT 1 UNION ALL SELECT 1) AS T(x)
WHERE au_id = '172-32-1176'

Neither of these examples serves much of a useful purpose but HTH.

--
David Portas
SQL Server MVP
--|||You can use the INSERT COMMAND for two times to get the duplicate
records in your table, but be sure that there is no unique contrauint
on that table.

Regards

Prashant Thakwani

laurenquantrell@.hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72.0402180610.60f76e50@.posting.google.com>...
> I'm sure there's a simple way to do it, I just haven't run into it yet:
> I just want to duplicate a table record (row) using a stored procedure.
> lq

Dumping Sql after an exception

Does anyone have a code snippet for dumping the actual sql a command would be running?

What do you mean by "dumping" ? You can kill the session..

|||

No I mean like getting the actual SQL it was trying to execute.

|||

If you know the spid you can do DBCC INPUTBUFFER(spid) to see what the spid is executing.

dump stack question

When causes a dump stack in the Error Log folder to occur? I have one from
yesterday but don't know what caused it. I see the code that was genrated,
and theres tons more info, but have no idea what the deal is. How do you
make sense of all the info?
TIA, ChrisRThe details in the stack dump are most often used by microsoft product
support. You can contact them or search the web to see if this dump has been
reported by other people. It might have been fixed in a patch already.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
"ChrisR" <noemail@.bla.com> wrote in message
news:OGMUq#odFHA.1612@.tk2msftngp13.phx.gbl...
> When causes a dump stack in the Error Log folder to occur? I have one from
> yesterday but don't know what caused it. I see the code that was genrated,
> and theres tons more info, but have no idea what the deal is. How do you
> make sense of all the info?
> TIA, ChrisR
>|||Hi
You may want to look at dumpchk see
http://support.microsoft.com/defaul...b;en-us;315263. Also look in
the event log to see what was happening around the time the file was created
.
John
"ChrisR" wrote:

> When causes a dump stack in the Error Log folder to occur? I have one from
> yesterday but don't know what caused it. I see the code that was genrated,
> and theres tons more info, but have no idea what the deal is. How do you
> make sense of all the info?
> TIA, ChrisR
>
>sql

dump stack question

When causes a dump stack in the Error Log folder to occur? I have one from
yesterday but don't know what caused it. I see the code that was genrated,
and theres tons more info, but have no idea what the deal is. How do you
make sense of all the info?
TIA, ChrisR
The details in the stack dump are most often used by microsoft product
support. You can contact them or search the web to see if this dump has been
reported by other people. It might have been fixed in a patch already.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
"ChrisR" <noemail@.bla.com> wrote in message
news:OGMUq#odFHA.1612@.tk2msftngp13.phx.gbl...
> When causes a dump stack in the Error Log folder to occur? I have one from
> yesterday but don't know what caused it. I see the code that was genrated,
> and theres tons more info, but have no idea what the deal is. How do you
> make sense of all the info?
> TIA, ChrisR
>
|||Hi
You may want to look at dumpchk see
http://support.microsoft.com/default...;en-us;315263. Also look in
the event log to see what was happening around the time the file was created.
John
"ChrisR" wrote:

> When causes a dump stack in the Error Log folder to occur? I have one from
> yesterday but don't know what caused it. I see the code that was genrated,
> and theres tons more info, but have no idea what the deal is. How do you
> make sense of all the info?
> TIA, ChrisR
>
>

dump stack question

When causes a dump stack in the Error Log folder to occur? I have one from
yesterday but don't know what caused it. I see the code that was genrated,
and theres tons more info, but have no idea what the deal is. How do you
make sense of all the info?
TIA, ChrisRThe details in the stack dump are most often used by microsoft product
support. You can contact them or search the web to see if this dump has been
reported by other people. It might have been fixed in a patch already.
--
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
"ChrisR" <noemail@.bla.com> wrote in message
news:OGMUq#odFHA.1612@.tk2msftngp13.phx.gbl...
> When causes a dump stack in the Error Log folder to occur? I have one from
> yesterday but don't know what caused it. I see the code that was genrated,
> and theres tons more info, but have no idea what the deal is. How do you
> make sense of all the info?
> TIA, ChrisR
>|||Hi
You may want to look at dumpchk see
http://support.microsoft.com/default.aspx?scid=kb;en-us;315263. Also look in
the event log to see what was happening around the time the file was created.
John
"ChrisR" wrote:
> When causes a dump stack in the Error Log folder to occur? I have one from
> yesterday but don't know what caused it. I see the code that was genrated,
> and theres tons more info, but have no idea what the deal is. How do you
> make sense of all the info?
> TIA, ChrisR
>
>

dumb sql mistake or SQL Server error?

If I run the following I was hoping for the same result from both final
queries. The code below isn't what I am actually doing, what I'm actually
doing is putting values into a 'min' column of a data analysis table, and I
want to be able to put more than just numbers (for example, minimum dates) s
o
it should be easiest if that column is a varchar(100). But the error I'm
dealing with shows up with the following minimal code:
drop table #Values
go
create table #Values (value money)
go
insert into #Values Values (1)
insert into #Values Values (-21705503.4616)
go
select min(value) from #Values
select CAST(min(value) as varchar(100)) from #Values
The last two decimal places drop on the cast... That later ruins some stuff
I'm doing, I need all the decimal places to be there.
Any ideas? Am I missing something obvious or is this a SQL Server error?
It's such a simple thing really...
Thanks!First question: why are you converting from money to a varchar?
Second question: do you really need to use the money data type?
The money datattype is useful if you are storing currency delimiters
with your numeric data; if not, you can use numeric or decimal instead.
Stu|||WHOOPS.
Forgot to mention that if you must convert money to a character
datatype, use the CONVERT statement (not CAST) because it allows you to
specify a style.

>From the Books Online:
Value Output
0 (default) No commas every three digits to the left of the decimal
point, and two digits to the right of the decimal point; for example,
4235.98.
1 Commas every three digits to the left of the decimal point, and two
digits to the right of the decimal point; for example, 3,510.92.
2 No commas every three digits to the left of the decimal point, and
four digits to the right of the decimal point; for example, 4235.9819.
In your case,
SELECT CONVERT(varchar(100), MIN(value), 3) FROM #Values
HTH,
Stu|||Like I said above :) I have to use varchar because I have a column that is
going to have minimums of lots of different datatypes including datetime.
However, later I get values from this table and convert them back to their
real datatypes. It works in all cases except this.
This is an ETL process, the source data column is money, I don't have the
opportunity to change it. I am similarly not excited about money, I usually
strictly use numeric(9,2), numeric(9,4), numeric(19,2) numeric(19,4) and
with 6, etc. instead. I work for Company B who is doing the project for
Company A, so I definitely can't change the column type!!
So you get the same result running the code? If everyone gets the same
result seems like a bug to me.
"Stu" wrote:

> First question: why are you converting from money to a varchar?
> Second question: do you really need to use the money data type?
> The money datattype is useful if you are storing currency delimiters
> with your numeric data; if not, you can use numeric or decimal instead.
>
> Stu
>|||IMHO a more appropriate alternative would be to use the sql_variant datatype
,
if you really must store the values in a single column. It also 'remembers'
the original datatype.
That is, if you can live with the whole 8kB worth of sql-variant length.
ML
http://milambda.blogspot.com/|||Cool. So you are suggesting this line:
select convert(varchar(100), min(value), 2) from #Values
appended to the end of the sql I sent. That seems to work. This is
incredibly annoying because I now have to do this conditionally on the sourc
e
column data type. My actual code that does the min is inside some dynamic
sql and fires tens of times, not just once... Ughh... I can do it, though.
Microsoft: Update Cast on money->varchar so it will catch all decimal places
for money, make everyone's life easier :). Make default behavior no commas
and all significant figures beyond the decimal.
Thanks Stu!
"Stu" wrote:

> WHOOPS.
> Forgot to mention that if you must convert money to a character
> datatype, use the CONVERT statement (not CAST) because it allows you to
> specify a style.
>
> Value Output
> 0 (default) No commas every three digits to the left of the decimal
> point, and two digits to the right of the decimal point; for example,
> 4235.98.
> 1 Commas every three digits to the left of the decimal point, and two
> digits to the right of the decimal point; for example, 3,510.92.
> 2 No commas every three digits to the left of the decimal point, and
> four digits to the right of the decimal point; for example, 4235.9819.
>
> In your case,
> SELECT CONVERT(varchar(100), MIN(value), 3) FROM #Values
>
> HTH,
> Stu
>|||That sounds - so then the conversion is implicit and I would just updat
e
the column with no cast or convert? I'll play with that. That would be
great.
I am putting in the avg, min, max, stdev and other statistical information
about columns in this table, so it is not large although my statistical
analysis sp runs over tables that are quite large. The result table is no
bigger than 50X100, so 8kB is nothing.
Thanks!
"ML" wrote:

> IMHO a more appropriate alternative would be to use the sql_variant dataty
pe,
> if you really must store the values in a single column. It also 'remembers
'
> the original datatype.
> That is, if you can live with the whole 8kB worth of sql-variant length.
> ML
> --
> http://milambda.blogspot.com/|||It worked - I just changed the column data type in my 'summary table' and
touched no other code. Which is good as this is a 1000+ line stored
procedure and is really annoying to update...
"ML" wrote:

> IMHO a more appropriate alternative would be to use the sql_variant dataty
pe,
> if you really must store the values in a single column. It also 'remembers
'
> the original datatype.
> That is, if you can live with the whole 8kB worth of sql-variant length.
> ML
> --
> http://milambda.blogspot.com/|||Test well before use. :)
Also look up the SQL_VARIANT_PROPERTY system function in Books Online.
ML
http://milambda.blogspot.com/|||Hi
drop table #Values
go
create table #Values (value decimal(18,4))
go
insert into #Values Values (1)
insert into #Values Values (-21705503.4616)
go
select min(value) from #Values
select CAST(min(value) as varchar(100)) from #Values
"LightMiner" <LightMiner@.discussions.microsoft.com> wrote in message
news:61A8ED8F-08D2-456B-A540-B1EE1248902E@.microsoft.com...
> It worked - I just changed the column data type in my 'summary table' and
> touched no other code. Which is good as this is a 1000+ line stored
> procedure and is really annoying to update...
> "ML" wrote:
>

Dumb Question?

Does anyone know of amy SQL Code that will drop all the records in all the
tables at once and leave the db structure in place?
sp_msforeachtable 'TRUNCATE TABLE ?'
"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:D1EC25B0-237D-45BB-89FD-A4ECA18E0360@.microsoft.com...
> Does anyone know of amy SQL Code that will drop all the records in all the
> tables at once and leave the db structure in place?
|||truncate table may not work if the table is referenced by foreign keys. You
may try scripting out the schemas and run it in a separate database to
create all table structures.
"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:D1EC25B0-237D-45BB-89FD-A4ECA18E0360@.microsoft.com...
> Does anyone know of amy SQL Code that will drop all the records in all the
> tables at once and leave the db structure in place?

Dumb Question?

Does anyone know of amy SQL Code that will drop all the records in all the
tables at once and leave the db structure in place?sp_msforeachtable 'TRUNCATE TABLE ?'
"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:D1EC25B0-237D-45BB-89FD-A4ECA18E0360@.microsoft.com...
> Does anyone know of amy SQL Code that will drop all the records in all the
> tables at once and leave the db structure in place?|||truncate table may not work if the table is referenced by foreign keys. You
may try scripting out the schemas and run it in a separate database to
create all table structures.
"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:D1EC25B0-237D-45BB-89FD-A4ECA18E0360@.microsoft.com...
> Does anyone know of amy SQL Code that will drop all the records in all the
> tables at once and leave the db structure in place?

DTSX package will not run from SQL server agent

When I try to run a DTSX package from SQL server agent, I get this error message:

Started: 3:59:42 PM Error: 2007-08-13 15:59:42.39 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTSroperty" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2007-08-13 16:00:00.98 Code: 0xC001602A Source: CFASerialNoExport Connection manager "FTP Connection Manager" Description: An error occurred in the requested FTP operation. Detailed error description: The password was not allowed . End Error Error: 2007-08-13 16:00:00.98 Code: 0xC002918F Source: FTP Task FTP Task Description: Unable to connect to FTP server using "FTP Connection Manager". End Error DTExec: The packag... The package execution fa... The step failed.

All my other packages run fine. This one contains an FTP task and will not run, although it will run if I execute it through the visual studio.

May be package is set with encryption, save the package with encryption level "Dont Save Sensitive" (if you are using configuration file) or alternatively you can set the encryption level to "rely on server storage for access and roles" in sql server when importing the package.

Thanks

|||

See if this KB article helps:

http://support.microsoft.com/kb/918760

DTSX Package failure returning 0

I have a Web App that I'm trying to launch a DTSX pack with. I wrote
a Stored Procedure that executes a job that launches the package.
CODE:
Execute msdb.dbo.sp_start_Job
'Import_Contractor_Employee_Data_to_CCTS'
When everything is correct, it runs good. I found out this moring
that there is one potential situation that will cause the package to
fail. The problem is, when the package fails...with the above code,
it returns the below if I run it as a single line:
Job 'Import_Contractor_Employee_Data_to_CCTS' started successfully.
If I execute it through the complete stored procedure...:
ALTER PROCEDURE [dbo].[ASP_CCTS_RunImportContractorEmployee_Job]
AS
DECLARE @.intErrorCode int
BEGIN
SET NOCOUNT ON;
Execute @.intErrorCode = msdb.dbo.sp_start_Job
'Import_Contractor_Employee_Data_to_CCTS'
Select @.intErrorCode Error_Code
END
It returns a 0 as though nothing is wrong. What am I missing here?
ThanksHi Dave
You status says that the job started ok, not that it completed ok. Look at
sysjobs and you can see if it is currently executing and wait until it
finishes before checking the status.
John
"Dave" wrote:
> I have a Web App that I'm trying to launch a DTSX pack with. I wrote
> a Stored Procedure that executes a job that launches the package.
> CODE:
> Execute msdb.dbo.sp_start_Job
> 'Import_Contractor_Employee_Data_to_CCTS'
>
> When everything is correct, it runs good. I found out this moring
> that there is one potential situation that will cause the package to
> fail. The problem is, when the package fails...with the above code,
> it returns the below if I run it as a single line:
> Job 'Import_Contractor_Employee_Data_to_CCTS' started successfully.
> If I execute it through the complete stored procedure...:
> ALTER PROCEDURE [dbo].[ASP_CCTS_RunImportContractorEmployee_Job]
> AS
> DECLARE @.intErrorCode int
> BEGIN
> SET NOCOUNT ON;
> Execute @.intErrorCode = msdb.dbo.sp_start_Job
> 'Import_Contractor_Employee_Data_to_CCTS'
> Select @.intErrorCode Error_Code
> END
>
> It returns a 0 as though nothing is wrong. What am I missing here?
>
> Thanks
>

DTSTransform.DataConvert programmatic access?

I am trying building a package from code. I have been able to follow the SSIS samples and build my control flow with foreach loop and SQL Commands pretty easily.

The data flow has been a different story, I am struggling with input and output columns. I trying to read from a flatfile, convert data, perform a lookup, and update or insert based on the results of the lookup. I was able to build this package in the designer and it works just as I want, but I am having problems duplicating the data flow in the code.

I was able add the flatfile, data conversion, and insert controls on the data flow and linked them together. However, I cannot figure out how get the input columns in the data convert object to become selected and generate the converted output columns.

I have tried to refresh metadata and mappings column, but to no success. The only custom property for this component seems to be SourceInputColumnLineageId, but I cannot figure how to set it. Can someone give me nudge or push in the right direction?

Here is what is left of my code:

IDTSComponentMetaData90 convert= dataFlow.ComponentMetaDataCollection.New();
convert.ComponentClassID = "DTSTransform.DataConvert";

// Get the design time instance of the component and initialize the component
CManagedComponentWrapper instance = convert.Instantiate();
instance.ProvideComponentProperties();

IDTSPath90 path = dataFlow.PathCollection.New();
path.AttachPathAndPropagateNotifications(srcComponent.OutputCollection[0], onvComponent.InputCollection[0]);

// Reinitialize the metadata.
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();

// Iterate through the inputs of the component.
IDTSVirtualInput90 vInputLkUp = convert.InputCollection[0].GetVirtualInput();
foreach (IDTSVirtualInputColumn90 vColumn in vInputLkUp.VirtualInputColumnCollection)
{
IDTSInputColumn90 col = instance.SetUsageType(convert.InputCollection[0].ID, vInputLkUp, vColumn.LineageID, DTSUsageType.UT_READONLY);
//instance.SetInputColumnProperty(convert.InputCollection[0].ID, col.ID, "SourceInputColumnLineageId", 1);
}

Did you find a resolution to this issue? I am struggling through the same thing and have seen no example usage of the data conversion transformation anywhere.

Phil Burns

Aptify

DTSTransform.DataConvert programmatic access?

I am trying building a package from code. I have been able to follow the SSIS samples and build my control flow with foreach loop and SQL Commands pretty easily.

The data flow has been a different story, I am struggling with input and output columns. I trying to read from a flatfile, convert data, perform a lookup, and update or insert based on the results of the lookup. I was able to build this package in the designer and it works just as I want, but I am having problems duplicating the data flow in the code.

I was able add the flatfile, data conversion, and insert controls on the data flow and linked them together. However, I cannot figure out how get the input columns in the data convert object to become selected and generate the converted output columns.

I have tried to refresh metadata and mappings column, but to no success. The only custom property for this component seems to be SourceInputColumnLineageId, but I cannot figure how to set it. Can someone give me nudge or push in the right direction?

Here is what is left of my code:

IDTSComponentMetaData90 convert= dataFlow.ComponentMetaDataCollection.New();
convert.ComponentClassID = "DTSTransform.DataConvert";

// Get the design time instance of the component and initialize the component
CManagedComponentWrapper instance = convert.Instantiate();
instance.ProvideComponentProperties();

IDTSPath90 path = dataFlow.PathCollection.New();
path.AttachPathAndPropagateNotifications(srcComponent.OutputCollection[0], onvComponent.InputCollection[0]);

// Reinitialize the metadata.
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();

// Iterate through the inputs of the component.
IDTSVirtualInput90 vInputLkUp = convert.InputCollection[0].GetVirtualInput();
foreach (IDTSVirtualInputColumn90 vColumn in vInputLkUp.VirtualInputColumnCollection)
{
IDTSInputColumn90 col = instance.SetUsageType(convert.InputCollection[0].ID, vInputLkUp, vColumn.LineageID, DTSUsageType.UT_READONLY);
//instance.SetInputColumnProperty(convert.InputCollection[0].ID, col.ID, "SourceInputColumnLineageId", 1);
}

Did you find a resolution to this issue? I am struggling through the same thing and have seen no example usage of the data conversion transformation anywhere.

Phil Burns

Aptify

dtsrun works from command prompt, but hangs from Query Analyzer

I have a dts that works from the command prompt, but hangs in Query Analyzer. Here's the code inside Query Analyzer:
exec master..xp_cmdshell 'dtsrun /S BFHSQL4 /N vhl_dts_14144b /E'

From the command prompt, it's just:
dtsrun /S BFHSQL4 /N vhl_dts_14144b /E

Any ideas what's wrong? Thanks for your help!Did the dtsrun cause SQL Server hang or just Query Analyzer hang? Did you got any error message when executing the package from Query Analyzer? You need more information for troubleshooting: use SQL Profiler to respectively capture a trace when execute dtsrun to the same package from commandline and Query Analyzer.|||Thanks for your response. I guess hangs might not be the right word. There are no error messages. Query Analyzer just tries to execute the package forever. It takes less than a minute to run from the command prompt. I've let it run for 1/2 hour in Query Analyzer before cancelling the query, then it takes several minutes to cancel. Other packages run fine with the same syntax. I think the problem is with this particular package. The dts just runs an activeX script, as follows:
Function Main()
Dim fileName

fileName = "\\mypath\myExcelFile.xls"

dim excelObject
set excelObject = CreateObject("Excel.Application")

excelObject.workbooks.open fileName

excelObject.Quit
set excelObject = Nothing

Main = DTSTaskExecResult_Success
End Function

I'm not really familiar with SQL Profiler. Do you still think using it will be helpful for this scenario? Thanks again for your help.|||Turns out that there was code inside the Excel file that was trying to save a file to a path that didn't exist. I fixed the path and now everything works fine.

DTSRUN utility

I would like to run a dts package from the command-line and I am trying to integrate a process exit code in my DTS package which I can intercept in my command-line. Is this possible. Because the next step in my batch file is dependent on the outcome of my DTS package.

Let me know if you have already tackeled that problem.I just did this test in SQL 7.0.

I created a simple package that just had 1 Activex Script Task:

Function Main()
Main = DTSTaskExecResult_Failure
End Function

From the command-line I ran DTSRUN utility, after which I checked the status of errorlevel, the DOS error status variable and it was set to 1, error. A value of 0 is success.

c:\ dtsrun /S nike /E /Ptest /M
c:\ echo %errorlevel%|||So simple and yet so effective. THX

DTSRun Fails while executing from COM

Hi,
i wrote a VB function that runs DTS by DTSRUN command, its working fine but when i copy the same code in COM and run it from ASP page and VB it fails to execute.

Function ExeDTSRun(DTSName, mKey As String)
Dim mDTS, spServerName, spUid, spPwd As String

spUid = "sa"
spPwd = "test"
spServerName = "server"


mDTS = "DTSRun /S " & spServerName & " /U " & spUid & " /P " & spPwd & " /N " & DTSName & " /G " & mKey & " /W ""0"""
Shell mDTS, vbNormalFocus
ExeDTSRun = "Successfully Run DTS : " & DTSName
End Function

Regards
AdiDo you get an error?|||First guess: Does the account running the IIS thread have permission (NT permission) to execute DTSRUN.EXE. Also, if the DTS package is accessing resources other than on the local machine, you will need to grant permission for that as well.

DtsConnection -- Consuming DTS DataReader In Code w/ Parameters

I am trying to pass a parameter into the DtsCommand object, but always get the error "a requested parameter does not exist in the package". I have a variable defined at the top scope named EmailPromotion so I tried the following

command.Parameters.Add(new DtsDataParameter("User::EmailPromotion", 1));

and

command.Parameters.Add(new DtsDataParameter("EmailPromotion", 1));

no luck either way
It seems for the variable to be configurable by DtsClient,
it has to be in DtsClient namespace. Create a variable
DtsClient::EmailPromotion, and use the short name
"EmailPromotion" when constructing the parameter object.

Hope this helps,
Michael.

P.S. Sorry, I could not find any documentation for this.
I'll open a bug to get it documented.|||That was it.

Thx|||this is the method that reporting services uses to build reports on top of SSIS. I cannot get this to show up in our VS.NET report designer. I have posted in that forum with no responses. Any help there would be greatly appreciated.

I have already uncommented the SSIS data extension from the rsreportserver.config and restarted the service.

It shows up in web report manager, but not in vs.net

Note using sept ctp and vs.net RC|||I believe you should also uncomment SSIS sections
in RsReportDesigner.config.|||Does this exist for 2005? It looks like things are little different.|||

The RSReportDesigner.config file is in
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies

|||that fixed it. much appreciated|||For those of you who are looking for the steps to change the variable namespace, follow these steps:

1) In the SSIS package, open the variable window
2) In the buttons at the top of the variables window, click the one farthest on the right (Choose Variable Columns)
3) Click "Namespace" and press OK
4) For the variable you are populating in code, change the namespace to DtsClient (this is case sensitive)
5) Now, within any objects that utilize the variable, you'll have to change the variable reference (for example, change from User::VariableName to DtsClient::VariableName)
6) In the code of the app consuming the SSIS package, the ParameterName you'll set won't include the DtsClient:: portion of the variable name