Getting a final version of a person into a DW

I have about 8 databases to integrate. All of the databases have ssno, address city...ect. I need to create a DW table with one unique record for each actual person. In other words,

Joe Smith,123 Main St, Anytown, State,....+ssno

goes into the DW table and is the same person as Joseph S. Smith,123 Main Street... and any other versions.

Could someone point me to a reference or give me an outline of how to do this in and SSIS package?

Is fuzzy logic used here?

Do I need to deduplicate the feeder systems first?

It needs to handle a situation in, for example, the Bronx New York where there could be an apartment buiding with 7 people named Jose Sanchez .

I hope I've been clear, I'm a newbie at this DW stuff, but it's fascinating. Any help would be appreciated. Thanks

Fuzzy could be of help here. The fuzzy grouping can be used for deduplicating, the fuzzy lookup to check whether you already have a record that resembles your new one.

You will need to spend some time (by testing) to figure out a similarity value that suits your situation. In a DW environment, I think this should be a business decision.

From what I understand, ssno probably needs to be involved. In the lookup (haven't used the grouping yet) you can set similarity values for ssno addressno and name. For example, ssno (or birthdate?) needs a similarity of 1, and the name needs to have a similarity > 0.6

Regards,

Pipo

geting timeout error in application when inserting record

i currently have more tha 3 million of records in my table in sql 7. i am getting timeout error in my web application when i try to insert a record in that table
what could be rhe reason for this? how to avoid this type of problem?
Thanks in advanceis it a single record insert or an insert select? how many indices do you have on the table? have you run a trace and looked at the duration column for the steps in the process? Do you want to post your code?sql

getdate() within a function

Im really new to SQL SProcs. I have a function that I wrote that I am trying to compare a date within a record to today's date. The problem is that you cant call getdate from within a function... So, I was thinking that I could create a temp table that had a a date column with a default date of today and select that out. However, I cant find any documentation on how you would create a temp table with a default value, or if this would even work. I dont want to have to pass todays date into the function, nor do I want to have to create a permanent table just to hold this data.

Any help, or other ideas?

Thanks.

You would create a temp table with a default value the same way you would create a non-temp table with a default value, except start the table name with a #.

Why can't you select the date from within the function?

I can't help but feel that you are doing something more basic incorrectly, or with a bad approach to your problem, but without any code, it's hard to tell what you are trying to do.

|||

Originally, I was trying to do something like this:

SET @.resDate = (SELECT ...)

IF @.resDate > getdate()
...

That didnt work because you cant call getdate from a function. Then I tried:

CREATE TABLE #tempDate
(
today datetime
DEFAULT(getdate())
)

Which also did not work for the same reason. So... other than passing getdate into the function, is there any other way to do it?

Josh

|||

You could also write a view that returns a datetime.

This is no more an issue in SQL 2005 though. So if you are using 2005, you are lucky.

|||what about this function(time1,getdate()). This is what I have ahad to use in the past.

GetDate

I am using GetDate() as the default value on selected fields to record the
date/time that a record is inserted into my tables, however, is it possible
to use a similar procedure to automatically insert the date/time into a
field, but ONLY if the record is subject to an update - thus recording the
date/time a record was last updated.
ThanksKeith
You have to write a TRIGGER FOR UPDATE (For more details please refer to the
BOL) .
"Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> I am using GetDate() as the default value on selected fields to record the
> date/time that a record is inserted into my tables, however, is it
possible
> to use a similar procedure to automatically insert the date/time into a
> field, but ONLY if the record is subject to an update - thus recording the
> date/time a record was last updated.
> Thanks
>|||Hi,
Either you have to explicitly update (Overwrite) the date column with an
Update statement or use Update triggers
to obtain this.
update table
set col1 = @.col1 ,col2 = @.col2,
date = getdate()
where ...
Thanks
Hari
MCDBA
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e9ywN5jFEHA.2944@.TK2MSFTNGP12.phx.gbl...
> Keith
> You have to write a TRIGGER FOR UPDATE (For more details please refer to
the
> BOL) .
>
> "Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
the
> possible
the
>|||Keith can you use your client app to do this...if it's an asp app...you can
use a hidden field to update the column....
"Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> I am using GetDate() as the default value on selected fields to record the
> date/time that a record is inserted into my tables, however, is it
possible
> to use a similar procedure to automatically insert the date/time into a
> field, but ONLY if the record is subject to an update - thus recording the
> date/time a record was last updated.
> Thanks
>|||I know I can do this, but as I am in the early stages of this app, I wanted
to try and shift as much as possible to server side to minimise the
client-server traffic and 'hopefully' increase security.
"SMAN" <ksanti@.nycap.rr.com> wrote in message
news:eZw2fKlFEHA.3080@.tk2msftngp13.phx.gbl...
> Keith can you use your client app to do this...if it's an asp app...you
can
> use a hidden field to update the column....
> "Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
the
> possible
the
>|||Would be nice, wouldn't it. Sybase SQL Anywhere has this functionality.
Maybe next year Yukon will have it.
Mike Kruchten
"Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> I am using GetDate() as the default value on selected fields to record the
> date/time that a record is inserted into my tables, however, is it
possible
> to use a similar procedure to automatically insert the date/time into a
> field, but ONLY if the record is subject to an update - thus recording the
> date/time a record was last updated.
> Thanks
>|||Actually, this functionality has been in place for over a decade in the form
of triggers:
create trigger triu_MyTable on MyTable after insert, update
as
if @.@.ROWCOUNT = 0 return
update MyTable
set
LastUpdateDateTime = getdate ()
where
PK in (select PK from inserted)
go
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike Kruchten" <mkruchten@.fsisolutions.com> wrote in message
news:#0KV3omFEHA.1240@.TK2MSFTNGP10.phx.gbl...
Would be nice, wouldn't it. Sybase SQL Anywhere has this functionality.
Maybe next year Yukon will have it.
Mike Kruchten
"Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> I am using GetDate() as the default value on selected fields to record the
> date/time that a record is inserted into my tables, however, is it
possible
> to use a similar procedure to automatically insert the date/time into a
> field, but ONLY if the record is subject to an update - thus recording the
> date/time a record was last updated.
> Thanks
>|||That's barely any client server traffic...plus triggers would eat up
more of your server resources...try both out and run some counters to
baseline some performance...
"Keith" <@..> wrote in message news:u5KuzTlFEHA.3724@.TK2MSFTNGP11.phx.gbl...
> I know I can do this, but as I am in the early stages of this app, I
wanted
> to try and shift as much as possible to server side to minimise the
> client-server traffic and 'hopefully' increase security.
>
> "SMAN" <ksanti@.nycap.rr.com> wrote in message
> news:eZw2fKlFEHA.3080@.tk2msftngp13.phx.gbl...
> can
news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> the
a
> the
>|||Yes, and do this in many places. However we removed these for performance re
asons on several tables, and the difference was measurable. Maybe using INST
EAD OF triggers for this would have helped the speed, though we never tested
this.
I don't know the performance implications of the SQL Anywhere solution as we
don't use the product. I just know the feature is available and it's specif
ied as DDL, kind of a default on update as well as insert.
It just sounded like a simple solution to a common requirement.
Mike Kruchten
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message news:ejHNOxmFEHA.35
40@.TK2MSFTNGP09.phx.gbl...
Actually, this functionality has been in place for over a decade in the form
of triggers:
create trigger triu_MyTable on MyTable after insert, update
as
if @.@.ROWCOUNT = 0 return
update MyTable
set
LastUpdateDateTime = getdate ()
where
PK in (select PK from inserted)
go
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike Kruchten" <mkruchten@.fsisolutions.com> wrote in message news:#0KV3omFE
HA.1240@.TK2MSFTNGP10.phx.gbl...
Would be nice, wouldn't it. Sybase SQL Anywhere has this functionality.
Maybe next year Yukon will have it.
Mike Kruchten
"Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> I am using GetDate() as the default value on selected fields to record the
> date/time that a record is inserted into my tables, however, is it
possible
> to use a similar procedure to automatically insert the date/time into a
> field, but ONLY if the record is subject to an update - thus recording the
> date/time a record was last updated.
> Thanks
>sql

Get XML from SQL Server 2000

I have a stored procedure, that returns a customer record from the customers table in the northwind database.
how can i return back an xml string of the row?
I mean, when the aspx page calls that procudure, I want to have somehting like:
<customers>
<customer>
<customerid>xxx</customerid>
<companyname>rrr</companyname>
</customer>
</customers>
can a stored procedure return such a string in xml form?
thanks alot

Read up on the FOR XML clause. Here's an article:http://www.sqljunkies.com/Article/296D1B56-8BDD-4236-808F-E62CC1908C4E.scuk and there's quite a bit of good info in BOL.

get top 2

i have this record in my table

name income
john 4500
peter 4500
jake 3000
jane 3000
paul 2000
gorge 1000

if i use the SELECT TOP 2 it will return me john and peter only how can i query to return me the 2 biggest income. that will return john peter jake and jane
thx.. just a newbie sory..i have this record in my table

name income
john 4500
peter 4500
jake 3000
jane 3000
paul 2000
gorge 1000

if i use the SELECT TOP 2 it will return me john and peter only how can i query to return me the 2 biggest income. that will return john peter jake and jane
thx.. just a newbie sory..

use ORDER BY. As in

SELECT TOP 2 *
FROM MyTable
ORDER BY income DESC

Regards,

hmscott|||hi

try this

SELECT
namefield
FROM
yourtable
WHERE
incomefield
IN
(SELECT DISTINCT TOP 2 incomefield FROM yourtable ORDER BY incomefield DESC)

Get the Top 3 record in same table.

Hi all,

I have some questions about sql statement. Is there anyway to get top 3 record for each item in the same table? I use before 'top' but it will give me the top value base on the price. Can someone give me an advice on this?

Thanks in advance.

Example:

Table 1

Item Name Price Date
A $10.00 15 Jan 2007
A $8.50 17 May 2006
A $8.00 1 Jan 2006
A $7.80 24 Sep 2005
B $12.20 2 Jan 2007
B $12.00 10 Oct 2006

I want get the result as below base on the example table 1. (Top3 Record for each item)

A $10.00 15 Jan 2007
A $8.50 17 May 2006
A $8.00 1 Jan 2006
B $12.20 2 Jan 2007
B $12.00 10 Oct 2006

select *
from tbl t
where price in (select top 3 price from tbl x where x.item_name = t.item_name)|||SELECT [Item Name], Price, Date
FROM (SELECT ROW_Number()OVER(PARTITION BY [Item Name] ORDER BY PRICE) as RowNum,[Item Name], Price, Date
FROM top3$)AS t1
WHERE RowNum<=3|||

Hi,

It really helps me solve my problem.

Thanks alot.

get the number of days it has been since a record was inserted

Hi

when inserting records into a table one of the fields is a date field. I am using the GETDATE() function to insert the date as the record is being inserted.

when i retrieve an entire record from the table i want to be able to select this date, but also to get the number of days it has been since that record was inserted.
eg: 3 days

if the record was inserted less than one day ago (<24 hrs ago) i would like it to return the number of hours.
e.g. 22 hrs

i dont want hours to be displayed if the days is >= 1.

please can anyone guide me with this?

thanks!

use the query like this

Declare @.MyVarasDateTime

Set @.Myvar='22/05/2007'

Select'satya', MyTime=

CASE

WHENDATEDIFF(hh,@.Myvar,GetDate())> 23THENConvert(varchar(10),DATEDIFF(d,@.Myvar,GetDate()))+' days'

ELSE

Convert(varchar(10),DATEDIFF(hh,@.Myvar,GetDate()))+' hours'

END

Use the appropriate fields according to your database and tables

|||

Thanks Satya, this was really useful. Can you help me modify this so that it returns 1 day and 1 hour instead of 1 days and 1 hours

Appreciate the help!

|||

Sure change the code where its + "days" or + "hours"

1Declare @.MyVaras DateTime23Set @.Myvar='22/05/2007'45Select'satya', MyTime=67CASE8WHENDATEDIFF(hh,@.Myvar,GetDate()) > 23THENConvert(varchar(10),DATEDIFF(d,@.Myvar,GetDate())) +' day'910ELSE1112Convert(varchar(10),DATEDIFF(hh,@.Myvar,GetDate())) +' hour'1314END1516

|||

Sorry, i dont think i explained what i meant properly...

I need it to say 'days' and 'hours' all the time but the only exceptions are when days = 1 and when hours = 1...in them cases it should say 1 day and 1hour.

so as an example it could out the following :

11 days

21 days

1 day

...and

22 hours

6 hours

1 hour.

Thanks again!

|||

Declare @.MyVaras DateTime Set @.Myvar='05/22/2007'Select'satya', MyTime=CASEWHENDATEDIFF(hh,@.Myvar,GetDate()) < 2THENConvert(varchar(10),DATEDIFF(hh,@.Myvar,GetDate())) +' hour'WHENDATEDIFF(hh,@.Myvar,GetDate()) < 23THENConvert(varchar(10),DATEDIFF(hh,@.Myvar,GetDate())) +' hours'WHENDATEDIFF(d,@.Myvar,GetDate()) < 2THENConvert(varchar(10),DATEDIFF(d,@.Myvar,GetDate())) +' day'WHENDATEDIFF(d,@.Myvar,GetDate()) > 1THENConvert(varchar(10),DATEDIFF(dd,@.Myvar,GetDate())) +' days'END

|||

Great, worked perfectly :)

Thanks.

|||

You are welcome... Answer it if solved

Get The Last Record by Grouping

I have a view listing tickets and reports for those tickets. I want to query LAST REPORT's OPERATOR

SELECT OPERATOR AS EXPR2, NUMBERPRGN, IS_BITIS AS EXPR1
FROM SCADMIN.V_ESKALASYON_2
WHERE (NUMBERPRGN = 'IM1289657')
ORDER BY NUMBERPRGN, IS_BITIS DESC

That query brings the resultset

IM1289657

OGUZY

04.12.2006 14:01:09

IM1289657

MUJDEO

01.12.2006 10:42:30

IM1289657

MUJDEO

28.11.2006 10:58:22

IM1289657

ILKERD

20.11.2006 14:36:12

IM1289657

ILKERD

13.11.2006 16:02:27

IM1289657

ILKERD

07.11.2006 14:02:21

IM1289657

ILKERD

31.10.2006 15:47:56

IM1289657

SINANK

19.10.2006 13:00:00

IM1289657

OGUZY

18.10.2006 17:25:56

Can you help to recover the query sentence above to return only the red marked record (LAST REPORT info written)

Thanks :)

SELECT TOP (1)...|||

Hoops, I have forgotten to say that I use that sentence to query from Oracle (SSIS). And there are lots of ticket numbers. I want to query only the red bold ones from Oracle (the criteria of red bold records is that they are last report for the ticket)

Could you help me?

IM1124672

ARIFOZ

16.11.2006 13:16

IM1124672

ARIFOZ

16.11.2006 13:16

IM1124672

ARIFOZ

26.10.2006 10:11

IM1124672

ARIFOZ

28.09.2006 11:30

IM1124672

ARIFOZ

08.09.2006 13:33

IM1124672

ARIFOZ

17.08.2006 14:18

IM1124672

ARIFOZ

02.08.2006 11:34

IM1124672

ARIFOZ

18.07.2006 08:20

IM1124672

ARIFOZ

04.07.2006 10:02

IM1124672

ARIFOZ

26.06.2006 11:25

IM1241042

ILKERD

28.11.2006 11:17

IM1241042

AYHANK

24.11.2006 10:00

IM1241042

ILKERD

20.11.2006 11:53

IM1241042

AYHANK

17.11.2006 12:10

IM1241042

ILKERD

13.11.2006 15:52

IM1241042

ILKERD

07.11.2006 13:42

IM1241042

ILKERD

30.10.2006 15:23

IM1241042

ILKERD

12.10.2006 11:19

IM1241042

ILKERD

05.10.2006 11:25

IM1241042

ILKERD

28.09.2006 14:47

IM1241042

ILKERD

22.09.2006 15:22

IM1241042

ILKERD

12.09.2006 14:30

IM1241042

ILKERD

07.09.2006 16:28

IM1241042

AYHANK

06.09.2006 12:10

IM1241042

AYHANK

04.09.2006 11:25

IM1251338

ILKERD

28.11.2006 11:22

IM1251338

ILKERD

20.11.2006 12:15

IM1251338

MUJDEO

15.11.2006 12:36

IM1251338

MUJDEO

09.11.2006 15:34

IM1251338

ILKERD

07.11.2006 13:44

IM1251338

ILKERD

30.10.2006 15:28

IM1251338

ILKERD

12.10.2006 11:23

IM1251338

ILKERD

05.10.2006 11:40

IM1251338

ILKERD

28.09.2006 14:57

IM1251338

ILKERD

22.09.2006 15:48

IM1251338

ALPS

15.09.2006 19:20

IM1251338

ALPS

15.09.2006 17:50

IM1251338

ILHANA

14.09.2006 16:24

IM1251338

HAKANM

14.09.2006 13:23

IM1253690

ILKERD

28.11.2006 11:26

IM1253690

ILKERD

13.11.2006 15:54

IM1253690

ILKERD

07.11.2006 13:45

IM1253690

ILKERD

30.10.2006 15:30

IM1253690

ILKERD

12.10.2006 11:46

IM1253690

ILKERD

05.10.2006 13:38

IM1253690

FERHATY

25.09.2006 15:30

IM1253690

ERCAND

23.09.2006 12:00

IM1253690

FERHATY

18.09.2006 15:00

IM1267973

ILKERD

28.11.2006 11:27

IM1267973

ILKERD

20.11.2006 14:11

IM1267973

ILKERD

13.11.2006 15:57

IM1267973

ILKERD

07.11.2006 13:48

IM1267973

ILKERD

30.10.2006 15:34

IM1267973

ILKERD

12.10.2006 12:26

IM1267973

ILKERD

05.10.2006 14:34

|||

I don't know Oracle very well... I hope that this works...

SELECT NUMBERPRGN, OPERATOR, IS_BITIS
FROM SCADMIN.V_ESKALASYON_2 AS Main INNER JOIN
(SELECT NUMBERPRGN, MAX(IS_BITIS) AS Date
FROM SCADMIN.V_ESKALASYON_2
GROUP BY NUMBERPRGN) AS Sub ON Main.NUMBERPRGN = Sub.NUMBERPRGN AND Main.IS_BITIS = Sub.Date
ORDER BY NUMBERPRGN

|||Thanks Lepaca, SQL is SQL (wherever queried). You know PL/SQL also, I think ;-)

Get the last 100 records

My sql database table gets filled automatically.

Every record gets a current date/time stamp.

I want to select the last 100 records, ordered by the date/time stamp.

The newest records should be the last record in the 100 recordset.

How can I do this?

select id, createdon from
(select top 100 id, createdon
from table
order by createdondesc) a
order by createdon

|||

I get an incorrect syntax error on the last ")":

SELECT

DT, VALUE

FROM

(SELECTTOP 10

DT, VALUE

FROM [CAS SHORT HISTORY]

ORDERBY DTDESC)

|||

Make sure you've given the derived table an alias. Here's a working example:

declare @.table1table (idint identity (1,1), createdondatetime)declare @.startdatedatetimedeclare @.enddatedatetimeset @.startdate ='20060101'set @.enddate ='20070101'while @.startdate < @.enddatebegininsert @.table1values (@.startdate)set @.startdate = @.startdate + 1endselect id, createdonfrom (select top 100 id, createdonfrom @.table1order by createdondesc) aorder by createdon

|||

Hi ca8msm,

You are filling a new table, but I'm having a table [CAS SHORT HISTORY] that is already filled, how should I create an alias for this table?

|||

The above is just an example table. Use the query at the bottom and change the table and field names.

|||

select id, createdonfrom
(select top 100 id, createdon
from @.table1
order by createdondesc) a
order by createdon

What is the "a" doing?

|||

It's creating an alias for the derived table.

|||

Bingo! Found it!

SELECT

*

FROM(SELECTTOP 10

DT

FROM

[CAS SHORT HISTORY] CASALIAS

ORDERBY

DT

DESC)

CASALIAS

ORDERBY

DT

ASC

Get Scope Identity Value using ObjectDataSource and Vb.Net

Hi,

I have been trying to get the scope Identity after inserting a record using an ObjectDataSource.

I can't find what event, or how to get the value that the scope identity returns.

Here is my Sproc.

ALTER PROCEDUREdbo.[YourCompany_LanCustomer_Insert]

(

@.DNNUserIDint,

@.FirstNamenvarchar(50),

@.LastNamenvarchar(50),

@.Addressnvarchar(50),

@.Address2nvarchar(50),

@.Citynvarchar(50),

@.Statenvarchar(50),

@.Zipnvarchar(50),

@.EmailAddressnvarchar(50),

@.PhoneNumbernvarchar(50),

@.CustomerIDint OUTPUT

)

AS

INSERT INTOYourCompany_LanCustomer

(DNNUserID, FirstName, LastName, Address, Address2, City, State, Zip, EmailAddress, PhoneNumber, DateEntered)

VALUES(@.DNNUserID, @.FirstName, @.LastName, @.Address, @.Address2, @.City, @.State, @.Zip, @.EmailAddress, @.PhoneNumber,getdate())

SET@.CustomerID =Scope_Identity()

RETURN

When I try to execute the stored procedure in Sql Manager I get the CustomerID Value, how do I get this value in the VB.Net code behind?

Any help is greatly appreciated.

In the Inserted event of the ObjectDataSource and you use the OutputParameters collection to get the value

Protected Sub ObjectDataSource1_Inserted(ByVal senderAs Object,ByVal eAs ObjectDataSourceStatusEventArgs)Dim _customerIdAs Integer =CInt(e.OutputParameters("@.CustomerID"))End Sub

Thanks

-Mark post(s) as "Answer" that helped you

|||

You'll get the collection of the insert parameters for the data source. You can retrieve the value of any parameter using yourDataSourceID.InsertParameters("parameterName"). You haven't explained how you're executing the stored procedure here. If possible post the aspx page code so that we can know how you've setup the data source.

|||

Now I'm lost, do I get the Scope Identity at the iteminserting or the iteminserted. I tried the first sample and get a parameter is not equal error.

Here is my ascx (I'm using dotnetnuke) for the objectDataSource.

<asp:ObjectDataSourceID="ObjectDataSource_Customer"runat="server"TypeName="YourCompany.Modules.Lan.LanCustomerController"SelectMethod="LanCustomer_GetCustomers"DataObjectTypeName="YourCompany.Modules.Lan.LanCustomerInfo"DeleteMethod="LanCustomer_Delete"OldValuesParameterFormatString="original_{0}"InsertMethod="LanCustomer_Insert">

</asp:ObjectDataSource>

here is the code behind (vb.net) that I am using on insert:

ProtectedSub NewItem(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.FormViewInsertEventArgs)Handles CustomerFormView.ItemInserting

Try

e.Values.Item("CustomerId") = 0

If e.Values.Item("DNNUserID") ="-1"Then

e.Values.Item("DNNUserID") ="0"

Else

e.Values.Item("DNNUserID") = UserId

EndIf

If e.Values.Item("FirstName") =""Then

e.Values.Item("FirstName") = Null.NullString

EndIf

If e.Values.Item("LastName") =""Then

e.Values.Item("LastName") = Null.NullString

EndIf

If e.Values.Item("Address") =""Then

e.Values.Item("Address") = Null.NullString

EndIf

If e.Values.Item("Address2") =""Then

e.Values.Item("Address2") = Null.NullString

EndIf

If e.Values.Item("City") =""Then

e.Values.Item("City") = Null.NullString

EndIf

If e.Values.Item("State") =""Then

e.Values.Item("State") = Null.NullString

EndIf

If e.Values.Item("Zip") =""Then

e.Values.Item("Zip") = Null.NullString

EndIf

If e.Values.Item("EmailAddress") =""Then

e.Values.Item("EmailAddress") = Null.NullString

EndIf

If e.Values.Item("PhoneNumber") =""Then

e.Values.Item("PhoneNumber") = Null.NullString

EndIf

Catch exAs Exception

ProcessModuleLoadException(Me, ex)

EndTry

EndSub

The insert sproc worked before, I just can't get it to work now.

|||

Hi,

SET @.CustomerID = Scope_Identity()

From the code you provided, the @.CustomerID is an OUTPUT parameter you set, right?

And we assume that you are using SqlCommand to execute the stored procedure in your business object method, and then you can retrieve the OUTPUT parameter in stored procedure by declaring a SqlParameter which in an OUTPUT direction. Make your business object method return the parameter's value after you invoking ExecuteNonQuery() method.

And then, in ObjectDataSource1_Selected event, try to get the value from RetrunValue property of ObjectDataSourceStatusEventArgs.

Thanks.

|||

Nai-Dong Jin - MSFT:

you can retrieve the OUTPUT parameter in stored procedure by declaring a SqlParameter which in an OUTPUT direction. Make your business object method return the parameter's value after you invoking ExecuteNonQuery() method.

And then, in ObjectDataSource1_Selected event, try to get the value from RetrunValue property of ObjectDataSourceStatusEventArgs.

Why do we need to "RETURN" the "OUTPUT PARAMETER" ? Do you know that RETURN values and OUTPUT parameters are independent of each other and we could retrieve either "RETURN" value or "OUTPUT" parameter or both of them?

Note: I dont know how the thread was marked as "Answer"

Thanks

-Mark post(s) as "Answer" that helped you

|||

Hi e_screw,

First, I think you've misunderstood my words. What I suggest is to declare an OUTPUT parameter in his stored procedure, and then assign the parameter with the value of Identity_Scope(). That's all. What the rest is retrieving parameters in .NET application by using SqlParameter which is in OUTPUT direction. Is there anything wrong? In stored procedure level, can you find any words on "RETURN" in my previous post?

Make your business object method return the parameter's value after you invoking ExecuteNonQuery() method.

And since the original poster was using ObjectDataSource, so he must had invoked the ExecuteNonQuery() in the business object method, right? What I said "return the parameter's value" means return the value from the business method. In this stage, that's totally nothing related with the OUTPUT parameter in procs.

Now I'm lost,

Second,of course, you also can use "Return" to achieve that, but since the original poster was lost, kept asking against previous solution and no one followed up, I just provide another solution for him to refer.

So if you are able to help him further with your solution, I appreciate it. And it also can be beneficial to other community members reading the thread.

Thanks.

|||

This is your previous post:

Nai-Dong Jin - MSFT:

And then, in ObjectDataSource1_Selected event, try to get the value from RetrunValue property of ObjectDataSourceStatusEventArgs.

Last post:

Nai-Dong Jin - MSFT:

First, I think you've misunderstood my words. What I suggest is to declare an OUTPUT parameter in his stored procedure, and then assign the parameter with the value of Identity_Scope(). That's all. What the rest is retrieving parameters in .NET application by using SqlParameter which is in OUTPUT direction. Is there anything wrong? In stored procedure level, can you find any words on "RETURN" in my previous post?

In the first you said, get the value from the ReturnValue property , after assigning the value of OUTPUT parameters to it. In the second, you are just talking about OUTPUT parameters.

Have you had looked at the ObjectDataSourceStatusEventArgs, there is OutputParameters (which returns a collection of output parameters and their values) and a ReturnValue (which gets the return value returned by the business object, if any). Now read your replies again.

Note: Its not with my solution or your solution. Its all about a correct solution, which helps many other community members.

Thanks

|||

Hi,

To Dan5150,

Here's the sample code for you which describes the solution in my previous posts.

First, in your Procedure:

set ANSI_NULLSONset QUOTED_IDENTIFIERONgoALTER PROCEDURE [dbo].[ProcName]@.TOINSERTNVARCHAR(50),@.RESULTINT OUTPUT-- THE OUTPUT Parameter has been set as OUTPUTAS INSERT INTO MYTABLE(TOINSERT)VALUES (@.TOINSERT)SET@.RESULT = SCOPE_IDENTITY();

Second, here's the method in business object class:

Public Function BusinessMethod(ByVal TOINSERTAs String)As String Dim connAs String = ConfigurationManager.ConnectionStrings("SampleDbConnectionString").ConnectionStringDim myconnAs New SqlConnection(conn)Dim mycommAs New SqlCommand() mycomm.Connection = myconn mycomm.CommandText ="ProcName" mycomm.CommandType = CommandType.StoredProcedureDim sp1As New SqlParameter() sp1.ParameterName ="TOINSERT" sp1.Value = TOINSERTDim sp2As New SqlParameter() sp2.ParameterName ="RESULT"' This parameter has been set in OUTPUT direction sp2.Direction = ParameterDirection.Output sp2.Size = 4 sp2.SqlDbType = SqlDbType.Int mycomm.Parameters.Add(sp1) mycomm.Parameters.Add(sp2) myconn.Open() mycomm.ExecuteNonQuery() myconn.close()' Return the parameter in OUTPUT direction.Return sp2.Value.ToString()End Function

Third, you can get the value in Inserted event of ODS by accessing ReturnValue property.

Protected Sub ObjectDataSource1_Inserted(ByVal senderAs Object,ByVal eAs ObjectDataSourceStatusEventArgs) Response.Write(e.ReturnValue.ToString())' You can get the id of new inserted row here.End Sub

To e_screw,

Please read my codes, and especially the comment parts in bold. And let's back to your solution which given in the second post:

Protected Sub ObjectDataSource1_Inserted(ByVal sender As Object, ByVal e As ObjectDataSourceStatusEventArgs)
Dim _customerId As Integer = CInt(e.OutputParameters("@.CustomerID"))
End Sub

You can use OutputParameters collection to retrieve the value, while output parameters would be ByRef (out in C#) parameters.

But since the original poster hadn't posted out his business method signature, how can you make sure that he was declaring parameters that are passed to the business object method by reference? If the parameters was passed by val, how could he get the value in OutputParameters collection?

Thanks.

Get records count from SQL cursor

Have you thought of declaring a int then adding one to it
per record ?
Peter
"Status quo, you know, that is Latin for "the mess we're
in."
Ronald Reagan

>--Original Message--
>Hi experts,
>I have created a SQL cursor for records processing in a
stored procedure. I
>probably can use the @.@.Cursor_Rows function in order to
obtain total rows of
>record contained inside the cursor. But once I declare
the cursor as
>FAST_FORWARD, it always return me -1. I need to declare
the cursor as
>FAST_FORWARD as it really helps in tuning the
performance. Else my stored
>procedure will take longer time to execute.
>Any other way I can use to get the total records being
returned? I've tried
>to signal another SQL statement to perform the records
count but this seems
>to create redundant overhead. I believe if I can do
anything to existing
>cursor without having extra Select Count statement, it
would help to reduce
>unnecessary processing and shorten the overall time
required.
>Really appreciate for any advice or suggestion. Thanks a
lot.
>.
>I've tested to append 1 to an int variable each time while looping the curso
r
but the effect is not so significant to boost the performance...Anyway,
thanks for the suggestion...
"Peter The Spate" wrote:

> Have you thought of declaring a int then adding one to it
> per record ?
> Peter
> "Status quo, you know, that is Latin for "the mess we're
> in."
> Ronald Reagan
>
> stored procedure. I
> obtain total rows of
> the cursor as
> the cursor as
> performance. Else my stored
> returned? I've tried
> count but this seems
> anything to existing
> would help to reduce
> required.
> lot.
>

Get RecordNumber with output

Hi everyone,
How can get the record number as column with my query output.
I dont want to insert the values in #temp table with IDENTITY function.
Any other trick...
RiyazYou don't state which version of SQL Server you are using. If you are using SQL Server 2005 you can use the new ROW_NUMBER() (http://msdn2.microsoft.com/en-us/library/ms186734.aspx) function.|||You don't state which version of SQL Server you are using. If you are using SQL Server 2005 you can use the new ROW_NUMBER() (http://msdn2.microsoft.com/en-us/library/ms186734.aspx) function.

Sorry for that

I am using SQL Server 2000

get record with latest date

if there are 2 records with different date

how to write query for --> get record with latest date

Hi,

use Order By clause.

For example,

Select top 1 * from YourTable Order By YourDateFieldName Desc

|||

Use something like this:

SELECT TOP 1 *
FROM MyTable
ORDER BY DateField DESC

Regards,
Martin

|||

Here is an example based on the NorthWinds database:

Select TOP 1 *from OrdersOrder by OrderDateDesc

|||

Here is another way to do it:

select *
from MyTable
where timestampfield = (select max(timestampfield)
from MyTable)

get record with earliest datetime value

Hello all,

Quick sql syntax question:

I have this table:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[REQUESTS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[REQUESTS]
GO

CREATE TABLE [dbo].[REQUESTS] (
[ROW_ID] [uniqueidentifier] NULL ,
[REQUEST_DATE] [datetime] NULL ,
[STATUS] [tinyint] NULL
) ON [PRIMARY]
GO

with these values:

insert into REQUESTS (REQUEST_DATE, STATUS)
values (getdate(), 0)
insert into REQUESTS (REQUEST_DATE, STATUS)
values (getdate(), 1)
insert into REQUESTS (REQUEST_DATE, STATUS)
values (getdate(), 0)

I need to select the single record with a STATUS = 0 with the earliest
REQUEST_DATE

I am using this query:
SELECT TOP 1 ROW_ID FROM REQUEST_LOG WHERE STATUS = 0 ORDER BY
REQUEST_DATE

not sure if this is the way to go...

pointer appreciated
thanksHow about doing this:
1: Change Row_ID from NULL to NOT NULL
CREATE TABLE [dbo].[REQUESTS] (
[ROW_ID] [uniqueidentifier] NOT NULL ,
[REQUEST_DATE] [datetime] NULL ,
[STATUS] [tinyint] NULL
) ON [PRIMARY]
GO

2: Add value for column ROW_ID in INSERT:
insert into REQUESTS (ROW_ID, REQUEST_DATE, STATUS)
values (NEWID(), getdate(), 0)
insert into REQUESTS (ROW_ID, REQUEST_DATE, STATUS)
values (NEWID(), getdate(), 1)
insert into REQUESTS (ROW_ID, REQUEST_DATE, STATUS)
values (NEWID(), getdate(), 0)

3: Use correct table name in SELECT - from REQUEST_LOG to REQUESTS
SELECT TOP 1 ROW_ID FROM REQUESTs WHERE STATUS = 0 ORDER BY
REQUEST_DATE

On Feb 9, 9:59 am, "hharry" <paulquig...@.nyc.comwrote:

Quote:

Originally Posted by

Hello all,
>
Quick sql syntax question:
>
I have this table:
>
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[REQUESTS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[REQUESTS]
GO
>
CREATE TABLE [dbo].[REQUESTS] (
[ROW_ID] [uniqueidentifier] NULL ,
[REQUEST_DATE] [datetime] NULL ,
[STATUS] [tinyint] NULL
) ON [PRIMARY]
GO
>
with these values:
>
insert into REQUESTS (REQUEST_DATE, STATUS)
values (getdate(), 0)
insert into REQUESTS (REQUEST_DATE, STATUS)
values (getdate(), 1)
insert into REQUESTS (REQUEST_DATE, STATUS)
values (getdate(), 0)
>
I need to select the single record with a STATUS = 0 with the earliest
REQUEST_DATE
>
I am using this query:
SELECT TOP 1 ROW_ID FROM REQUEST_LOG WHERE STATUS = 0 ORDER BY
REQUEST_DATE
>
not sure if this is the way to go...
>
pointer appreciated
thanks

|||apologies for the typos

what i should have asked is this:

Is TOP applied after the ORDER BY or before...can someone confirm
this ?|||Yes, TOP is applied after the result set rows are ordered with ORDER BY.

Regards,

Plamen Ratchev
http://www.SQLStudio.com

Get Record Number

I have a table containing 1000 records. I do have a primary key associated with the table. But I want to get the Record Number of each record. Is there a way to get it. That means for first record it should be 1 and so on and so forth.

That depends on wheter you are using SQL Server 2005 or any version below. SQL 2k5 introduced the ROWNUMBER() function which will let your create a rownumber according to some rules (only a new number if a grouping changes, etc.) if you are using SQL2k or bwlo you will have to go another way. What are you currently using ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Currently using SQL-2000|||

There is no straight-forward solution in SQL 2000.

Basically, you can create a temp table with an identity column (record number) and insert each row of your table into it. After that, select all the rows in the temp table out with the record number.

You can also only insert the PK column into the temp table and select using a join of the temp table and the original table.

|||

I tried to get thru this problem and found out a way to do it. the query specified below can solve my purpose:

SELECT emp_id, lname, fname, job_id, (SELECT COUNT(*) FROM employee e2 WHERE e2.lname <= e.lname) AS rownumber
FROM employee e
ORDER BY lname

But again there is a catch. This particular query will work only in case I have a unique field (like lname in this case). Now I again to resolve this thing by assigning a new unique ID to each row with the use of function NEWID(). But when I am trying to use NEWID() instead of lname it is not working. I tried as

select newid() NN, e.*, (select count(*) from (select newid() NID,* from employee) e2 where e2.NID <= e.NN ) as rownumber
from employee e

it gives me an error "Invalid column name 'NN' " Can anyone help me out in sorting this error and using the NEWID() as a unique field. I do not wish to use temp table.

|||

I tried to get thru this problem and found out a way to do it. the query specified below can solve my purpose:

SELECT emp_id, lname, fname, job_id, (SELECT COUNT(*) FROM employee e2 WHERE e2.lname <= e.lname) AS rownumber
FROM employee e
ORDER BY lname

But again there is a catch. This particular query will work only in case I have a unique field (like lname in this case). Now I again to resolve this thing by assigning a new unique ID to each row with the use of function NEWID(). But when I am trying to use NEWID() instead of lname it is not working. I tried as

select newid() NN, e.*, (select count(*) from (select newid() NID,* from employee) e2 where e2.NID <= e.NN ) as rownumber
from employee e

it gives me an error "Invalid column name 'NN' " Can anyone help me out in sorting this error and using the NEWID() as a unique field. I do not wish to use temp table.

Get Random record from SQL table

Hi,

I am in a situation where our developer is on leave (annual leave for a month), and I have to add a control to my website, which is in aspx apges.

To start with i have created a table in my SQL database. this table has records with one-liners from various movies, and the movie title. The tabel have 3 columns, i.e. ID, Liners, MTitle.

So i want to get random records on the page when ever its refreshed. I am totally non-coder/programmer guy.

I have got a SQL statement from the internet " SELECT TOP 1 * FROM <table name> ORDER By NEWID() "

So would anyone please help me out with it, as is it correct, how can i apply in the aspx pages.

Thank you.

the sql statement is correct. Just change the <table name> to your actual table name|||

Hi,

If you want to get random records from the database via a single SQL statememt, you may try the method below:

Assuming there is a unique identifier for each row, and that there is at least one record in the table, retrieving a random record can be quite easy. This method will work in SQL Server 7.0 and above (running on Windows 2000), but this could be a performance problem on larger tables / resultsets:

SELECT TOP 1 someColumn FROM someTable ORDER BY NEWID()

If you are not running on Windows 2000, then the following code will work if your table has a unique identifier (e.g. IDENTITY) column:

SELECT TOP 1 someColumn FROM someTable ORDER BY RAND((1000*IDColumn)*DATEPART(millisecond, GETDATE()))

Note that both of these methods also allow you to select 10 or 50 or 5000 random records, simply by altering the TOP parameter

Hope it helps.

get primary key of last inserted record

Ok I know this might not be the most accurate place to post this but I know someone here has an answer for me on it.

I need to get the product_ID of the new record that is created by this insert statement

INSERT
INTO products
( class_ID,category_ID,product_name,product_desc,product_image,product_dimension,product_o1,product_o2,product_o3,product_ac,product_ph,product_photo )
SELECT class_ID,category_ID,product_name,product_desc,product_image,product_dimension,product_o1,product_o2,product_o3,product_ac,product_ph,product_photo
FROM products
WHERE product_ID = @.productID

The answer you seek is here:

http://www.mikesdotnetting.com/Article.aspx?ArticleID=54

Get PK for inserted record in SQLdatasource

I have a table named invoice that contains the following columns

-invoiceno - Primary key and is set to autonumber
-customerno
-incoicedate

and on my VB code i did the following InsertCommand

SqlDataSource1.InsertCommand = "INSERT INTO invoice(customerno, invoicedate) VALUES('" & Session("UID") & "', GetDate()) "
SqlDataSource1.Insert()

My Question is how do i get the Primary Key Value it generated during the insert operation(invoice['incoiceno'])? Besides the creationg a stored procedere like the one in the MSDN Library

Well, since you really aren't using the sqldatasource as a datasource, just do it manually.

Dim conn as new sqlconnection("{Your connect string}")
conn.open
dim cmd as new sqlcommand("INSERT INTO invoice(customerno, invoicedate) VALUES(@.customerno,getdate()) SELECT @.newid=SCOPE_IDENTITY()",conn)
cmd.parameters.add("@.customerno",sqldbtype.uniqueidentifier).value=session("UID")
cmd.parameters.add("@.newid",sqldbtype.int).direction=output
cmd.executenonquery()
dim newid as integer=cmd.parameters("@.newid").value
conn.close

I believe you can also try to create a parameter on the insert with a direction of output as well, but I think you have to actually pull the value in sqldatasource1_inserted by referencing e.command.parameters("@.newid").value, but you can try and see if you can pull it directly after your insert too.

Get Minimum day in a continuous series

Hi,

l've a series of day which record the date of an event. l would like to count the # of continuous days for the event. In this case, it would be 14/5, 15/5, 16/5, 17/5, 18/5, 19/5 and 20/5. Any idea to do this in SQL?

Date
--
20/5
19/5
18/5
17/5
16/5
15/5
14/5
09/5
07/5
06/5
05/5
And what about May/5 May/6 and May/7?.. those also meet your request.|||

The only thing that comes to mind is to use a cursor to loop through your rows.

You can then check each row to determine if it is exactly one day beyond the last row.

Do you just want to get a count of the highest number of days? Or what do you want to return?

|||

If you are using SQL Server 2005 you
can do this (this can be modified to work
with SQL Server 2000 if required)

set dateformat dmy
create table #dates(dt datetime)

insert into #dates(dt) values('20/5/2006')
insert into #dates(dt) values('19/5/2006')
insert into #dates(dt) values('18/5/2006')
insert into #dates(dt) values('17/5/2006')
insert into #dates(dt) values('16/5/2006')
insert into #dates(dt) values('15/5/2006')
insert into #dates(dt) values('14/5/2006')
insert into #dates(dt) values('09/5/2006')
insert into #dates(dt) values('07/5/2006')
insert into #dates(dt) values('06/5/2006')
insert into #dates(dt) values('05/5/2006');

with dt_rn(dt,rn)
as
(select dt,
dt-rank() over(order by dt)
from #dates)
select convert(char(5),min(dt),103) as EventStart,
count(*) as ContinuousDays
from dt_rn
group by rn
having count(*)>1

drop table #dates

|||

Hello

To get the Minimum day of each sequence you could try this...

drop table #dates

set dateformat dmy
create table #dates(dt datetime)

insert into #dates(dt) values('20/5/2006')
insert into #dates(dt) values('19/5/2006')
insert into #dates(dt) values('18/5/2006')
insert into #dates(dt) values('17/5/2006')
insert into #dates(dt) values('16/5/2006')
insert into #dates(dt) values('15/5/2006')
insert into #dates(dt) values('14/5/2006')
insert into #dates(dt) values('09/5/2006')
insert into #dates(dt) values('07/5/2006')
insert into #dates(dt) values('06/5/2006')
insert into #dates(dt) values('05/5/2006')

select * from #dates od where not exists (select dt from #dates id where id.dt = od.dt-1)
and exists (select dt from #dates id2 where id2.dt = od.dt+1)

The 2nd Part of the where clause is needed to eliminate the 09/05 date... If you consider that single date a "sequence" also then remove the 2nd part.

Be warned though that this querry can eat up a lot of performance.

|||this query must return the event start date and count of continous event dates. you must to replace date_column and table_name

select start_date, count(event_date) as cnt from (
select date_column as event_date,
(select max(date_column) from (
select date_column
from table_name t1
left outer join table_name t2
on t1.date_column=dateadd(t2,1,date_column)
where t2.date is null
) as start_dates
where start_dates.date_column<=events.date_column
) as start_date
from table_name events
) as event_dates
group by event_start_date