How to raise error in script

I can't believe I can't find this in books online...

I have a transformation script component and I have a case where I want the object to completely fail if it is encountered. How do I code that? All my searching BOL could find is how to redirect rows to an error output which is not what I need in this case.... I'd think it would be something simple like Me.RaiseError("Something really bad happened") but I haven't found it ...

Chris,

A simple example from books online to raise an error in the Script Component is:

Dim myMetadata as IDTSComponentMetaData90

myMetaData = Me.ComponentMetaData

myMetaData.FireError(...)

Further details can be found on the Raising Events in the Script Component page at: http://msdn2.microsoft.com/en-us/library/aa337081.aspx.

Thanks,
Patrik

|||Thanks... I knew it it had to be in books online somewhere.. I just couldn't find it |||

One thing to note that I have found with this method is that it doesn't immediately error..

For example lets say you have a buffer (in a dataflow) containing 1000 rows. In my experience, if you code a FireError when it encounters null it will not exit the object on the first instance but continue with the whole buffer - then your dtslog will be filled with 1000 errors (if indeed each row of the 1000 had a null). Just something to keep in mind.

|||

Throw an exception to simulate the Fail Component disposition. This action will cause the transformation script component to immediately fail.

ComponentMetadata's FireError, as you noted, will not cause immediate failure, but rather, form a basis for comparison with the data flow's MaximumErrorCount (which may be useful in other cases)

|||

"Throw an exception to simulate the Fail Component disposition. This action will cause the transformation script component to immediately fail."

Thanks, Can you provide an example or a pointer to where to look in books online?

|||

From BOL, please use the following example.

Using Error Outputs in a Data Flow Component

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/a2a3e7c8-1de2-45b3-97fb-60415d3b0934.htm

First off, to establish the relevance of the above BOL link related to data flow component error outputs, recall that a script component (actually a script component host) is just another type of custom pipeline component. A ScriptComponentHost derives from PipelineComponent. PipelineComponent is the base class used when writing pipeline components. The ScriptComponentHost provides services to the auto-generated classes known as the "Script Component".

Anyway, in the BOL reference example, the FailComponent disposition is emulated by throwing an exception. Furthermore, Kirk Haselden, who I consider an authority on the subject of SSIS, states something similar in https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=601735&SiteID=1.

As far as script component example, say, for the sake of a simple example, you are adding non-negative numbers, the sum of which cannot exceeed 42. At some point, the sum exceeds 42. You can keep adding and calling Dts.FireError() repeatedly, indicating the sum is logically impossible in your event message. Instead, to immediately fail the component, the solution is to call Dts.FireError() once with the appropriate error message, and then throw an exception to fail the component.

|||

Thanks jaegd for the above.

I have followed your advice of the FireError() then 'throw new exception'

However if one has an OnError event (as I do) to send an email of the error description, then no less 4 messages are sent:

1. The FireError error

2. A result of the exception -"System.Exception"

3 A result of the exception "The ProcessInput method on component: " (something to do with a lookup transform up-line)

4.Another result of the exception "Thread "WorkThread0" has exited with error code 0x"

So how does one allow the FireError OnError to execute but suppress the other spurious events?

Thanks

|||Put a test in front of your email task to decide what gets sent. Then the recipient receives a single email from an error event cascade.

For one, suppress the errors 3 ("The process InputMethod on component"), and 4 ("Thread "WorkThread0" has exited...) via an expression based precedence constraint to your Send Mail task. The specific Error Codes are accessible in the handler upon which a suppression decision can be made.

That leaves you with two errors, which are both more relevant to why the data flow was halted. You can dispense with with FireError call, and just use the exception, leaving a single email.|||

Thanks

Of course this is only usefull if you know in advance what the extraneous error messages are going to be in all cases. So I don't consider that to be an elegant solution. If I knew why multiple error messages where generated for only the one error then I might have a chance at obtaining a better solution. Also access to a system variable such as ErrorCount would be usefull too. Nevermind, I'll attack this in a similar manner to what you have suggested (instead of the message I'll go off the error code).

how to raise an error and test rollbacks

I put this at the end of a stored procedure in which I update two
tables. I am trying to raise an error so that I can test whether or not
both tables will be rolled back if the event of error.
So far, this is not working to raise and error, and the therefore the
two tables are updating. I copied this out of the SQL Server Books
online
Help? And thank you.
--SQL transact that creates the procedure,
--inserts data in one table,
--retrieves value of identity column
--and then updates a second table
--and then attempts to provoke an error
--and rollback in both tables with:
RAISERROR ('sprStoredProcedureError', 16, 1)
Set @.STRERROR = @.@.ERROR
Select @.STRERROR
IF @.STRERROR = 0
COMMIT TRAN
ELSE
ROLLBACK TRAN
END
GORAISERROR is for raising the error to the calling app. At the point you are
"raising" the error, you have already determined that something is wrong in
the transaction :P This is not what you are looking for here.
In QA, if you have permissions you can right click on your proc and choose
debug to walk through it step by step. Set the values to something you know
will error and see if it goes into the rollback stage.
If you do not have permssions for debugging, just try inserting a row with
that you know will cause an error (duplicate PK for instance). In your erro
r
handler section Print 'ROLLBACK'. Then execute the proc and check messages.
HTH,
John Scragg
"JJ_377@.hotmail.com" wrote:

> I put this at the end of a stored procedure in which I update two
> tables. I am trying to raise an error so that I can test whether or not
> both tables will be rolled back if the event of error.
> So far, this is not working to raise and error, and the therefore the
> two tables are updating. I copied this out of the SQL Server Books
> online
> Help? And thank you.
> --SQL transact that creates the procedure,
> --inserts data in one table,
> --retrieves value of identity column
> --and then updates a second table
> --and then attempts to provoke an error
> --and rollback in both tables with:
> RAISERROR ('sprStoredProcedureError', 16, 1)
> Set @.STRERROR = @.@.ERROR
> Select @.STRERROR
> IF @.STRERROR = 0
> COMMIT TRAN
> ELSE
> ROLLBACK TRAN
> END
> GO
>|||Usually, it helps to include a complete script that can be used to reproduce
the problem. Below is one that I used; it is based almost entirely on your
code snippet and works correctly. There must be another issue that is not
apparent from the information you posted.
set nocount on
go
create table aa_junk1 (junk1_id int identity, more_stuff varchar(20) not
null)
go
create table aa_junk2 (junk2_id int identity, junk1_id int not null,
addl_stuff varchar(20) not null)
go
alter table aa_junk1 add constraint junk1_pk primary key (junk1_id)
go
alter table aa_junk2 add constraint junk2_pk primary key (junk2_id)
go
create procedure aa_junk as
declare @.STRERROR int
declare @.newid int
begin tran
insert aa_junk1 (more_stuff) values ('test1')
set @.newid = @.@.IDENTITY
insert aa_junk2 (junk1_id, addl_stuff)
values (@.newid, 'test1a')
select * from aa_junk1
select * from aa_junk2
RAISERROR ('sprStoredProcedureError', 16, 1)
Set @.STRERROR = @.@.ERROR
Select @.STRERROR
IF @.STRERROR = 0
BEGIN
PRINT 'COMMITTING'
COMMIT TRAN
END
ELSE
BEGIN
PRINT 'ROLLING BACK'
ROLLBACK TRAN
END
GO
exec aa_junk
go
select * from aa_junk1
select * from aa_junk2
go
drop table aa_junk2
drop table aa_junk1
drop procedure aa_junk
go|||Write correct code
begin tran
insert aa_junk1 (more_stuff) values ('test1')
SET @.STRERROR = @.@.ERROR -- @.@.ERROR must be saved immediately after DML
statement
IF @.STRERROR = 0
BEGIN
SET @.newid = SCOPE_IDENTITY() -- DONT USE @.@.IDENTITY !!!
insert aa_junk2 (junk1_id, addl_stuff) values (@.newid, 'test1a')
SET @.STRERROR = @.@.ERROR
IF @.STRERROR = 0
COMMIT TRAN
ELSE
BEGIN
RAISERROR('AA_JUNK2 ERROR NR. %d',16,1,@.STRERROR)
ROLLBACK TRAN
END
ELSE
BEGIN
RAISERROR(' AA_JUNK1 ERROR NR. %d',16,1,@.STRERROR)
ROLLBACK TRAN
END
Kuido
Scott Morris wrote:
>Usually, it helps to include a complete script that can be used to reproduc
e
>the problem. Below is one that I used; it is based almost entirely on your
>code snippet and works correctly. There must be another issue that is not
>apparent from the information you posted.
>set nocount on
>go
>create table aa_junk1 (junk1_id int identity, more_stuff varchar(20) not
>null)
>go
>create table aa_junk2 (junk2_id int identity, junk1_id int not null,
>addl_stuff varchar(20) not null)
>go
>alter table aa_junk1 add constraint junk1_pk primary key (junk1_id)
>go
>alter table aa_junk2 add constraint junk2_pk primary key (junk2_id)
>go
>create procedure aa_junk as
>declare @.STRERROR int
>declare @.newid int
>begin tran
>insert aa_junk1 (more_stuff) values ('test1')
>set @.newid = @.@.IDENTITY
>insert aa_junk2 (junk1_id, addl_stuff)
>values (@.newid, 'test1a')
>select * from aa_junk1
>select * from aa_junk2
>RAISERROR ('sprStoredProcedureError', 16, 1)
>Set @.STRERROR = @.@.ERROR
>Select @.STRERROR
>IF @.STRERROR = 0
>BEGIN
> PRINT 'COMMITTING'
> COMMIT TRAN
>END
>ELSE
>BEGIN
> PRINT 'ROLLING BACK'
> ROLLBACK TRAN
>END
>GO
>exec aa_junk
>go
>select * from aa_junk1
>select * from aa_junk2
>go
>drop table aa_junk2
>drop table aa_junk1
>drop procedure aa_junk
>go
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...server/200510/1

how to raise an error and test rollbacks

I put this at the end of a stored procedure in which I update two
tables. I am trying to raise an error so that I can test whether or not
both tables will be rolled back if the event of error.
So far, this is not working to raise and error, and the therefore the
two tables are updating. I copied this out of the SQL Server Books
online
Help? And thank you.
--SQL transact that creates the procedure,
--inserts data in one table,
--retrieves value of identity column
--and then updates a second table
--and then attempts to provoke an error
--and rollback in both tables with:
RAISERROR ('sprStoredProcedureError', 16, 1)
Set @.STRERROR = @.@.ERROR
Select @.STRERROR
IF @.STRERROR = 0
COMMIT TRAN
ELSE
ROLLBACK TRAN
END
GO
RAISERROR is for raising the error to the calling app. At the point you are
"raising" the error, you have already determined that something is wrong in
the transaction :P This is not what you are looking for here.
In QA, if you have permissions you can right click on your proc and choose
debug to walk through it step by step. Set the values to something you know
will error and see if it goes into the rollback stage.
If you do not have permssions for debugging, just try inserting a row with
that you know will cause an error (duplicate PK for instance). In your error
handler section Print 'ROLLBACK'. Then execute the proc and check messages.
HTH,
John Scragg
"JJ_377@.hotmail.com" wrote:

> I put this at the end of a stored procedure in which I update two
> tables. I am trying to raise an error so that I can test whether or not
> both tables will be rolled back if the event of error.
> So far, this is not working to raise and error, and the therefore the
> two tables are updating. I copied this out of the SQL Server Books
> online
> Help? And thank you.
> --SQL transact that creates the procedure,
> --inserts data in one table,
> --retrieves value of identity column
> --and then updates a second table
> --and then attempts to provoke an error
> --and rollback in both tables with:
> RAISERROR ('sprStoredProcedureError', 16, 1)
> Set @.STRERROR = @.@.ERROR
> Select @.STRERROR
> IF @.STRERROR = 0
> COMMIT TRAN
> ELSE
> ROLLBACK TRAN
> END
> GO
>
|||Usually, it helps to include a complete script that can be used to reproduce
the problem. Below is one that I used; it is based almost entirely on your
code snippet and works correctly. There must be another issue that is not
apparent from the information you posted.
set nocount on
go
create table aa_junk1 (junk1_id int identity, more_stuff varchar(20) not
null)
go
create table aa_junk2 (junk2_id int identity, junk1_id int not null,
addl_stuff varchar(20) not null)
go
alter table aa_junk1 add constraint junk1_pk primary key (junk1_id)
go
alter table aa_junk2 add constraint junk2_pk primary key (junk2_id)
go
create procedure aa_junk as
declare @.STRERROR int
declare @.newid int
begin tran
insert aa_junk1 (more_stuff) values ('test1')
set @.newid = @.@.IDENTITY
insert aa_junk2 (junk1_id, addl_stuff)
values (@.newid, 'test1a')
select * from aa_junk1
select * from aa_junk2
RAISERROR ('sprStoredProcedureError', 16, 1)
Set @.STRERROR = @.@.ERROR
Select @.STRERROR
IF @.STRERROR = 0
BEGIN
PRINT 'COMMITTING'
COMMIT TRAN
END
ELSE
BEGIN
PRINT 'ROLLING BACK'
ROLLBACK TRAN
END
GO
exec aa_junk
go
select * from aa_junk1
select * from aa_junk2
go
drop table aa_junk2
drop table aa_junk1
drop procedure aa_junk
go
|||Write correct code
begin tran
insert aa_junk1 (more_stuff) values ('test1')
SET @.STRERROR = @.@.ERROR -- @.@.ERROR must be saved immediately after DML
statement
IF @.STRERROR = 0
BEGIN
SET @.newid = SCOPE_IDENTITY() -- DONT USE @.@.IDENTITY !!!
insert aa_junk2 (junk1_id, addl_stuff) values (@.newid, 'test1a')
SET @.STRERROR = @.@.ERROR
IF @.STRERROR = 0
COMMIT TRAN
ELSE
BEGIN
RAISERROR('AA_JUNK2 ERROR NR. %d',16,1,@.STRERROR)
ROLLBACK TRAN
END
ELSE
BEGIN
RAISERROR(' AA_JUNK1 ERROR NR. %d',16,1,@.STRERROR)
ROLLBACK TRAN
END
Kuido
Scott Morris wrote:
>Usually, it helps to include a complete script that can be used to reproduce
>the problem. Below is one that I used; it is based almost entirely on your
>code snippet and works correctly. There must be another issue that is not
>apparent from the information you posted.
>set nocount on
>go
>create table aa_junk1 (junk1_id int identity, more_stuff varchar(20) not
>null)
>go
>create table aa_junk2 (junk2_id int identity, junk1_id int not null,
>addl_stuff varchar(20) not null)
>go
>alter table aa_junk1 add constraint junk1_pk primary key (junk1_id)
>go
>alter table aa_junk2 add constraint junk2_pk primary key (junk2_id)
>go
>create procedure aa_junk as
>declare @.STRERROR int
>declare @.newid int
>begin tran
>insert aa_junk1 (more_stuff) values ('test1')
>set @.newid = @.@.IDENTITY
>insert aa_junk2 (junk1_id, addl_stuff)
>values (@.newid, 'test1a')
>select * from aa_junk1
>select * from aa_junk2
>RAISERROR ('sprStoredProcedureError', 16, 1)
>Set @.STRERROR = @.@.ERROR
>Select @.STRERROR
>IF @.STRERROR = 0
>BEGIN
> PRINT 'COMMITTING'
> COMMIT TRAN
>END
>ELSE
>BEGIN
> PRINT 'ROLLING BACK'
> ROLLBACK TRAN
>END
>GO
>exec aa_junk
>go
>select * from aa_junk1
>select * from aa_junk2
>go
>drop table aa_junk2
>drop table aa_junk1
>drop procedure aa_junk
>go
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200510/1

how to raise an error and test rollbacks

I put this at the end of a stored procedure in which I update two
tables. I am trying to raise an error so that I can test whether or not
both tables will be rolled back if the event of error.
So far, this is not working to raise and error, and the therefore the
two tables are updating. I copied this out of the SQL Server Books
online
Help? And thank you.
--SQL transact that creates the procedure,
--inserts data in one table,
--retrieves value of identity column
--and then updates a second table
--and then attempts to provoke an error
--and rollback in both tables with:
RAISERROR ('sprStoredProcedureError', 16, 1)
Set @.STRERROR = @.@.ERROR
Select @.STRERROR
IF @.STRERROR = 0
COMMIT TRAN
ELSE
ROLLBACK TRAN
END
GORAISERROR is for raising the error to the calling app. At the point you are
"raising" the error, you have already determined that something is wrong in
the transaction :P This is not what you are looking for here.
In QA, if you have permissions you can right click on your proc and choose
debug to walk through it step by step. Set the values to something you know
will error and see if it goes into the rollback stage.
If you do not have permssions for debugging, just try inserting a row with
that you know will cause an error (duplicate PK for instance). In your error
handler section Print 'ROLLBACK'. Then execute the proc and check messages.
HTH,
John Scragg
"JJ_377@.hotmail.com" wrote:
> I put this at the end of a stored procedure in which I update two
> tables. I am trying to raise an error so that I can test whether or not
> both tables will be rolled back if the event of error.
> So far, this is not working to raise and error, and the therefore the
> two tables are updating. I copied this out of the SQL Server Books
> online
> Help? And thank you.
> --SQL transact that creates the procedure,
> --inserts data in one table,
> --retrieves value of identity column
> --and then updates a second table
> --and then attempts to provoke an error
> --and rollback in both tables with:
> RAISERROR ('sprStoredProcedureError', 16, 1)
> Set @.STRERROR = @.@.ERROR
> Select @.STRERROR
> IF @.STRERROR = 0
> COMMIT TRAN
> ELSE
> ROLLBACK TRAN
> END
> GO
>|||Usually, it helps to include a complete script that can be used to reproduce
the problem. Below is one that I used; it is based almost entirely on your
code snippet and works correctly. There must be another issue that is not
apparent from the information you posted.
set nocount on
go
create table aa_junk1 (junk1_id int identity, more_stuff varchar(20) not
null)
go
create table aa_junk2 (junk2_id int identity, junk1_id int not null,
addl_stuff varchar(20) not null)
go
alter table aa_junk1 add constraint junk1_pk primary key (junk1_id)
go
alter table aa_junk2 add constraint junk2_pk primary key (junk2_id)
go
create procedure aa_junk as
declare @.STRERROR int
declare @.newid int
begin tran
insert aa_junk1 (more_stuff) values ('test1')
set @.newid = @.@.IDENTITY
insert aa_junk2 (junk1_id, addl_stuff)
values (@.newid, 'test1a')
select * from aa_junk1
select * from aa_junk2
RAISERROR ('sprStoredProcedureError', 16, 1)
Set @.STRERROR = @.@.ERROR
Select @.STRERROR
IF @.STRERROR = 0
BEGIN
PRINT 'COMMITTING'
COMMIT TRAN
END
ELSE
BEGIN
PRINT 'ROLLING BACK'
ROLLBACK TRAN
END
GO
exec aa_junk
go
select * from aa_junk1
select * from aa_junk2
go
drop table aa_junk2
drop table aa_junk1
drop procedure aa_junk
go|||Write correct code
begin tran
insert aa_junk1 (more_stuff) values ('test1')
SET @.STRERROR = @.@.ERROR -- @.@.ERROR must be saved immediately after DML
statement
IF @.STRERROR = 0
BEGIN
SET @.newid = SCOPE_IDENTITY() -- DONT USE @.@.IDENTITY !!!
insert aa_junk2 (junk1_id, addl_stuff) values (@.newid, 'test1a')
SET @.STRERROR = @.@.ERROR
IF @.STRERROR = 0
COMMIT TRAN
ELSE
BEGIN
RAISERROR('AA_JUNK2 ERROR NR. %d',16,1,@.STRERROR)
ROLLBACK TRAN
END
ELSE
BEGIN
RAISERROR(' AA_JUNK1 ERROR NR. %d',16,1,@.STRERROR)
ROLLBACK TRAN
END
Kuido
Scott Morris wrote:
>Usually, it helps to include a complete script that can be used to reproduce
>the problem. Below is one that I used; it is based almost entirely on your
>code snippet and works correctly. There must be another issue that is not
>apparent from the information you posted.
>set nocount on
>go
>create table aa_junk1 (junk1_id int identity, more_stuff varchar(20) not
>null)
>go
>create table aa_junk2 (junk2_id int identity, junk1_id int not null,
>addl_stuff varchar(20) not null)
>go
>alter table aa_junk1 add constraint junk1_pk primary key (junk1_id)
>go
>alter table aa_junk2 add constraint junk2_pk primary key (junk2_id)
>go
>create procedure aa_junk as
>declare @.STRERROR int
>declare @.newid int
>begin tran
>insert aa_junk1 (more_stuff) values ('test1')
>set @.newid = @.@.IDENTITY
>insert aa_junk2 (junk1_id, addl_stuff)
>values (@.newid, 'test1a')
>select * from aa_junk1
>select * from aa_junk2
>RAISERROR ('sprStoredProcedureError', 16, 1)
>Set @.STRERROR = @.@.ERROR
>Select @.STRERROR
>IF @.STRERROR = 0
>BEGIN
> PRINT 'COMMITTING'
> COMMIT TRAN
>END
>ELSE
>BEGIN
> PRINT 'ROLLING BACK'
> ROLLBACK TRAN
>END
>GO
>exec aa_junk
>go
>select * from aa_junk1
>select * from aa_junk2
>go
>drop table aa_junk2
>drop table aa_junk1
>drop procedure aa_junk
>go
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200510/1

how to quote the tabel?

Hi,
I have table name like 'xyz\abc1234.test' in SQL Query Analyzer. xyz
is the network name.
If I put select * from xyz\abc1234.test, it will give me an error msg.
What shall I do?
Thanks a lot!
Michael
On Apr 12, 10:09 am, "Michael" <michae...@.gmail.com> wrote:
> Hi,
> I have table name like 'xyz\abc1234.test' in SQL Query Analyzer. xyz
> is the network name.
> If I put select * from xyz\abc1234.test, it will give me an error msg.
> What shall I do?
> Thanks a lot!
> Michael
don't x-post
use brackets
select * from [xyz\abc1234].test
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||On Apr 12, 10:09 am, "Michael" <michae...@.gmail.com> wrote:
> Hi,
> I have table name like 'xyz\abc1234.test' in SQL Query Analyzer. xyz
> is the network name.
> If I put select * from xyz\abc1234.test, it will give me an error msg.
> What shall I do?
> Thanks a lot!
> Michael
try select * from [xyz\abc1234.test]

how to quote the tabel?

Hi,
I have table name like 'xyz\abc1234.test' in SQL Query Analyzer. xyz
is the network name.
If I put select * from xyz\abc1234.test, it will give me an error msg.
What shall I do?
Thanks a lot!
MichaelOn Apr 12, 10:09 am, "Michael" <michae...@.gmail.com> wrote:
> Hi,
> I have table name like 'xyz\abc1234.test' in SQL Query Analyzer. xyz
> is the network name.
> If I put select * from xyz\abc1234.test, it will give me an error msg.
> What shall I do?
> Thanks a lot!
> Michael
don't x-post
use brackets
select * from [xyz\abc1234].test
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||On Apr 12, 10:09 am, "Michael" <michae...@.gmail.com> wrote:
> Hi,
> I have table name like 'xyz\abc1234.test' in SQL Query Analyzer. xyz
> is the network name.
> If I put select * from xyz\abc1234.test, it will give me an error msg.
> What shall I do?
> Thanks a lot!
> Michael
try select * from [xyz\abc1234.test]

how to quote the tabel?

Hi,
I have table name like 'xyz\abc1234.test' in SQL Query Analyzer. xyz
is the network name.
If I put select * from xyz\abc1234.test, it will give me an error msg.
What shall I do?
Thanks a lot!
MichaelOn Apr 12, 10:09 am, "Michael" <michae...@.gmail.com> wrote:
> Hi,
> I have table name like 'xyz\abc1234.test' in SQL Query Analyzer. xyz
> is the network name.
> If I put select * from xyz\abc1234.test, it will give me an error msg.
> What shall I do?
> Thanks a lot!
> Michael
don't x-post
use brackets
select * from [xyz\abc1234].test
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||On Apr 12, 10:09 am, "Michael" <michae...@.gmail.com> wrote:
> Hi,
> I have table name like 'xyz\abc1234.test' in SQL Query Analyzer. xyz
> is the network name.
> If I put select * from xyz\abc1234.test, it will give me an error msg.
> What shall I do?
> Thanks a lot!
> Michael
try select * from [xyz\abc1234.test]

how to quit an sproc

Hi

I am doing a check and on failing, i have a raiseerror command.
I was assuming, once proc reaches raiseerror, it would stop the proc,
but i see that the proc gives me an error message, but continues to
run thru all the rest of the steps after raiseerror.
how do i make the proc quit after reading raiseerror, only thru
labels ?

IF @.CheckIFFileHasOnlyOneOutputType 1
BEGIN
RAISERROR ('Process Stopped. Input xls file is invalid, as it has more
than one output type specified in the OutputType Column', 16, 1)
END
--more proc steps are here ...

Thanks in advance :
RS(rshivaraman@.gmail.com) writes:

Quote:

Originally Posted by

I am doing a check and on failing, i have a raiseerror command.
I was assuming, once proc reaches raiseerror, it would stop the proc,
but i see that the proc gives me an error message, but continues to
run thru all the rest of the steps after raiseerror.
how do i make the proc quit after reading raiseerror, only thru
labels ?

Which version of SQL Server are you using?

In SQL 2000 you will need to use the RETURN statement. Preferrably you
return a value 0, so that the caller knows that things went wrong.

On SQL 2005 you can embed the code in BEGIN TRY END TRY, and then in
BEGIN CATCH END CATCH have some generic error handling, including a
RETURN statement.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

how to quickly learn the syntax of SQL?

Hi, All:

I know oracle SQL, now I need to do a lot of SQL query on Microsoft SQLSERVER, can any one point out any place that I can find out the syntax of SQLserver SQL statement? Since this is just a short term assignment, so I don't want to buy a book, just hoping I can learn something quickly from online. I don't need learn anything deep, just need to know some simple syntax so I can do join, count, concatenate, min(), max(), sum () etc.
thanks in advance.Books Online, the definitive source for information about SQL Server and T-SQL, is available online here, to for free download here.|||

You will be suprised that for the standard things like joining and concatenating, SQL Server support most things the ANSI standard which lets you change your SQL strings easily. For special things like system functions you will need the appropiate TSQL equivalent but for ANSI joining like *= you can do a "soft-migration" to SQL Server. (And in addition you won′t need the dual table anymore :-) ) :-D

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||
Hi, Thanks for all of your replies.

How to quickly evaluate SSIS, programmer perspective

SSIS looks interesting. I haven't loaded any Yukon or read the 2005 BOL, so I'm coming in with a clean slate.

In the past I have found the graphical programming interface for DTS, limiting, annoying and unnatural. I much prefer coding in an OO language the conventional way.

I'm interested in evaluating SSIS as quickly as I can. The ideal is programming with a standard .NET language. I am happy if a design surface gives me a start but I expect to go into the code it writes and continue with a standard code editor.

What is the best reference to get a idea whether this is sensible to do and what the programming experience is like?

Thanks.That still isn't the experience. While SSIS has made great strides over DTS in terms of functionality, i.e. the ability to easily do looping constructs, etc it is still not like writing a Windows Forms application where you write code to perform various operations. The script transform is the exception to this but I have found myself using it very rarely.|||Thanks for that Chris.

I'm aware of a way of programmatically creating a DTS task. (I never used it, for real work, but I've read interesting write ups. If I remember correctly it involved exporting the package as a VBScript, that created the task by instantiating and using the underlying DTS object model. These VBScript files could then be used as the basis for creating a VB or VB.NET program, though this was probably not done often.)

I assumed that SSIS would, at least, retain that functionality, but with a .NET language in place of the VBScript. (Though I was hoping for something a lot better.)

(I really like the idea of a live diagram showing the code, but I still want to be able to access that code directly.)|||I can't seem to find a way to export a package like you used to be able to do in DTS. Doesn't mean that it isn't possible. Just isn't obvious to someone who has spent around 150 hours in the tool.|||That feature is not supported on SSIS.
K|||Thanks Kirk.

I don't understand that design decision. It cuts the traditional style of programming out of the loop. I was really looking forward to improved access to the ETL engines. (I guess that means carry on with other approaches to ETL in the CLR 2.0 era!)|||You still have a choice how how you build packages. Designer Vs Code.

You can still create/load and execute packages through code. What you cannot do is create a package and "script" it, in effect Save As VB as we had in DTS. It's a shame, since it was a great way to get a head start on creating a package in code, but at the end of the day not a major problem really.

I don't understand the comparison of SSIS with traditional style programming, since at the end of the day this is a ETL tool. You can access the objects through code if you want, but there will be limitations using the object model since it is designed to do a specific job, it is not a programming language.

On the programming theme, the ability to develop your own components for SSIS is so much easier, and more open, compared to DTS. You can write connection managers, tasks, pipeline components (a bit like old transforms), log providers and enumerators.|||Thanks Darren.

That revives my interest.

Recently I have given up using DTS in favour of coding the whole process. So, in my mind, I see a continuum between code and ETL tool. I think the design decision is driven by the idea of who the target user is. It's assumed the user is "Somebody who is not going to write conventional code". (In that case those who are comfortable writing code, kinda get driven out.) (The decision might also be based on one guy to do DTS, one guy to do code, one guy to test... which in my view is a recipe to turn a 2 man-hour job into a 9 man-day job!!)

As you say the loss of the ability to generate code from a wizard made package is unfortunate. The improved ability to write your own transforms (etc.) looks like it could have fixed an Achilles Heel of DTS. (It was an horrendous job to write script to perform real world serious transforms.)|||

MikeGale wrote:

Thanks Darren.

That revives my interest.

Recently I have given up using DTS in favour of coding the whole process. So, in my mind, I see a continuum between code and ETL tool. I think the design decision is driven by the idea of who the target user is. It's assumed the user is "Somebody who is not going to write conventional code". (In that case those who are comfortable writing code, kinda get driven out.) (The decision might also be based on one guy to do DTS, one guy to do code, one guy to test... which in my view is a recipe to turn a 2 man-hour job into a 9 man-day job!!)

As you say the loss of the ability to generate code from a wizard made package is unfortunate. The improved ability to write your own transforms (etc.) looks like it could have fixed an Achilles Heel of DTS. (It was an horrendous job to write script to perform real world serious transforms.)

Mike,
Don't discount the ability to write custom transformation functionality using the script component and the script task - they are fantastically powerful bits of kit and execute managed code rather than script code so its many times more efficient than ActiveX in DTS.

Donald Farmer has book coming out dedicated solely to scripting in SSIS and exactly what can be achieved. http://www.amazon.co.uk/exec/obidos/ASIN/1932577211/202-4310542-9360621?%5Fencoding=UTF8

-Jamie

How to quickly create and populate a table to 1GB?

Hi All
How do i create a table which is as big as 1GB? I have tried some thing
below, but it it taking too long. Is there any quick way to do it? Here is my
sample i have tried and it's taking too long:
CREATE TABLE TableD
( X text)
GO
insert into tableD
values (replicate('h', 500000000))
GO
All i want is to have tableD to be 1GB for testing.
Thank you in advance.
Try this.
while (select count(*) from TableD)<5000
begin
insert into tableD
values (replicate('h', 500000000))
continue
end
TableD is 40,768KB
Divide 1,000,000 by 40,768 = 24
Now, DTS table out to txt file
declare @.counter int
select @.counter=0
while @.counter<24
begin
bulk insert TableD from 'C:\tableD.txt'
select @.counter=@.counter+1
continue
end
Mike K
"MittyKom" wrote:

> Hi All
> How do i create a table which is as big as 1GB? I have tried some thing
> below, but it it taking too long. Is there any quick way to do it? Here is my
> sample i have tried and it's taking too long:
> CREATE TABLE TableD
> ( X text)
> GO
> insert into tableD
> values (replicate('h', 500000000))
> GO
> All i want is to have tableD to be 1GB for testing.
> Thank you in advance.
>
|||How about something like the following. I was able to generate it in about
90 seconds on a slow workstation.
-- Set database to simple recovery mode
Create table #Numbers (
Number int)
declare @.x int
set @.x = 1
while @.x < 127000
Begin
insert #Numbers values (@.x)
set @.x = @.x + 1
End
select cast(' ' as char(4100)) as ColX
into TableD
from #Numbers
drop table #Numbers
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:78E92EEE-3245-4DB6-8870-8AD5B00A7CE3@.microsoft.com...
> Hi All
> How do i create a table which is as big as 1GB? I have tried some thing
> below, but it it taking too long. Is there any quick way to do it? Here is
> my
> sample i have tried and it's taking too long:
> CREATE TABLE TableD
> ( X text)
> GO
> insert into tableD
> values (replicate('h', 500000000))
> GO
> All i want is to have tableD to be 1GB for testing.
> Thank you in advance.
>

How to quickly create and populate a table to 1GB?

Hi All
How do i create a table which is as big as 1GB? I have tried some thing
below, but it it taking too long. Is there any quick way to do it? Here is my
sample i have tried and it's taking too long:
CREATE TABLE TableD
( X text)
GO
insert into tableD
values (replicate('h', 500000000))
GO
All i want is to have tableD to be 1GB for testing.
Thank you in advance.Try this.
while (select count(*) from TableD)<5000
begin
insert into tableD
values (replicate('h', 500000000))
continue
end
TableD is 40,768KB
Divide 1,000,000 by 40,768 = 24
Now, DTS table out to txt file
declare @.counter int
select @.counter=0
while @.counter<24
begin
bulk insert TableD from 'C:\tableD.txt'
select @.counter=@.counter+1
continue
end
Mike K
"MittyKom" wrote:
> Hi All
> How do i create a table which is as big as 1GB? I have tried some thing
> below, but it it taking too long. Is there any quick way to do it? Here is my
> sample i have tried and it's taking too long:
> CREATE TABLE TableD
> ( X text)
> GO
> insert into tableD
> values (replicate('h', 500000000))
> GO
> All i want is to have tableD to be 1GB for testing.
> Thank you in advance.
>|||How about something like the following. I was able to generate it in about
90 seconds on a slow workstation.
-- Set database to simple recovery mode
Create table #Numbers (
Number int)
declare @.x int
set @.x = 1
while @.x < 127000
Begin
insert #Numbers values (@.x)
set @.x = @.x + 1
End
select cast(' ' as char(4100)) as ColX
into TableD
from #Numbers
drop table #Numbers
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:78E92EEE-3245-4DB6-8870-8AD5B00A7CE3@.microsoft.com...
> Hi All
> How do i create a table which is as big as 1GB? I have tried some thing
> below, but it it taking too long. Is there any quick way to do it? Here is
> my
> sample i have tried and it's taking too long:
> CREATE TABLE TableD
> ( X text)
> GO
> insert into tableD
> values (replicate('h', 500000000))
> GO
> All i want is to have tableD to be 1GB for testing.
> Thank you in advance.
>

How to quickly create and populate a table to 1GB?

Hi All
How do i create a table which is as big as 1GB? I have tried some thing
below, but it it taking too long. Is there any quick way to do it? Here is m
y
sample i have tried and it's taking too long:
CREATE TABLE TableD
( X text)
GO
insert into tableD
values (replicate('h', 500000000))
GO
All i want is to have tableD to be 1GB for testing.
Thank you in advance.Try this.
while (select count(*) from TableD)<5000
begin
insert into tableD
values (replicate('h', 500000000))
continue
end
TableD is 40,768KB
Divide 1,000,000 by 40,768 = 24
Now, DTS table out to txt file
declare @.counter int
select @.counter=0
while @.counter<24
begin
bulk insert TableD from 'C:\tableD.txt'
select @.counter=@.counter+1
continue
end
Mike K
"MittyKom" wrote:

> Hi All
> How do i create a table which is as big as 1GB? I have tried some thing
> below, but it it taking too long. Is there any quick way to do it? Here is
my
> sample i have tried and it's taking too long:
> CREATE TABLE TableD
> ( X text)
> GO
> insert into tableD
> values (replicate('h', 500000000))
> GO
> All i want is to have tableD to be 1GB for testing.
> Thank you in advance.
>|||How about something like the following. I was able to generate it in about
90 seconds on a slow workstation.
-- Set database to simple recovery mode
Create table #Numbers (
Number int)
declare @.x int
set @.x = 1
while @.x < 127000
Begin
insert #Numbers values (@.x)
set @.x = @.x + 1
End
select cast(' ' as char(4100)) as ColX
into TableD
from #Numbers
drop table #Numbers
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:78E92EEE-3245-4DB6-8870-8AD5B00A7CE3@.microsoft.com...
> Hi All
> How do i create a table which is as big as 1GB? I have tried some thing
> below, but it it taking too long. Is there any quick way to do it? Here is
> my
> sample i have tried and it's taking too long:
> CREATE TABLE TableD
> ( X text)
> GO
> insert into tableD
> values (replicate('h', 500000000))
> GO
> All i want is to have tableD to be 1GB for testing.
> Thank you in advance.
>

How to quickly create and populate a table 1GB?

Hi All
How do i create a table which is as big as 1GB? I have tried some thing
below, but it it taking too long. Is there any quick way to do it? Here is m
y
sample i have tried and it's taking too long:
CREATE TABLE TableD
( X text)
GO
insert into tableD
values (replicate('h', 500000000))
GO
All i want is to have tableD to be 1GB for testing.
Thank you in advance.Do you have the SQL Server 2000 Resource Kit? If so, try DBGen.
See:
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part11/c3961.mspx[/ur
l]
HTH
Jerry
If not you could use a WHILE loop or CROSS JOIN to produce your data or some
third-party tool/script.
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:AA5D17A7-D8C9-4781-8882-782ED5EF6507@.microsoft.com...
> Hi All
> How do i create a table which is as big as 1GB? I have tried some thing
> below, but it it taking too long. Is there any quick way to do it? Here is
> my
> sample i have tried and it's taking too long:
> CREATE TABLE TableD
> ( X text)
> GO
> insert into tableD
> values (replicate('h', 500000000))
> GO
> All i want is to have tableD to be 1GB for testing.
> Thank you in advance.|||Why dont you load an image into the SQL Server table? You can have really bi
g
images and you only need to insert 2 or 3 rows based on the size of the
image.
-Mark
"MittyKom" wrote:

> Hi All
> How do i create a table which is as big as 1GB? I have tried some thing
> below, but it it taking too long. Is there any quick way to do it? Here is
my
> sample i have tried and it's taking too long:
> CREATE TABLE TableD
> ( X text)
> GO
> insert into tableD
> values (replicate('h', 500000000))
> GO
> All i want is to have tableD to be 1GB for testing.
> Thank you in advance.|||Thanx Mark and Jerry.
I have a jpeg image saved on my disk. How can i insert it into a table?
"Mark" wrote:
> Why dont you load an image into the SQL Server table? You can have really
big
> images and you only need to insert 2 or 3 rows based on the size of the
> image.
> -Mark
> "MittyKom" wrote:
>|||Give a little more information about what type of testing you are wanting to
do, and perhaps you will get better advice about how to populate the table.
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:AA5D17A7-D8C9-4781-8882-782ED5EF6507@.microsoft.com...
> Hi All
> How do i create a table which is as big as 1GB? I have tried some thing
> below, but it it taking too long. Is there any quick way to do it? Here is
> my
> sample i have tried and it's taking too long:
> CREATE TABLE TableD
> ( X text)
> GO
> insert into tableD
> values (replicate('h', 500000000))
> GO
> All i want is to have tableD to be 1GB for testing.
> Thank you in advance.

how to quick query duplicate records?

any idea?

quick query duplicate records (speicifed fields.value are same) using T-SQL?

depends on how many columns you've got in the table but as an example:

say you have a table called Customer with one column called CustomerID then this simple query will tell you.

SELECT CustomerID, COUNT(*)

FROM Customer

GROUP BY CustomerID

HAVING COUNT(*) > 1

ORDER BY 2 DESC

|||

Thanks your reply what i want is

display duplicate records depends on multi-column

regards

|||

just add the extra columns in to the SELECT and GROUP BY clauses and it will work for multiple columns.

even better, can u post an example of the data that you are wanting to look for duplicates in?

|||

At the same time if you table own identity field you could use another query. Keeping on mind identity value you drop the one own less value.

I'm sorry but I don't have that query

|||

Because i want to remove duplicate records,so have to display first i think

I found an article

http://www.sqlservercentral.com/columnists/chawkins/dedupingdatainsqlserver2005.asp

I think it's cool, sharing

how to question

hey all,
Name, Inv(y/n), Desc
--
Cust1, null, Desc1
Cust1, null, Desc2
Cust1, Y, Desc1
Cust2, null, Desc1
Cust2, Y, Desc2
How would i select distinct customer names and get all the fields in my
result at the same time?
i know about select distinct name from table
but that only gives my 1 column and i need all the fields to show up.
thanks,
ariAri
The DISTINCT clause applies to ALL columns in the column list of the SELECT
statment. Based on the data you supplied, what are you expecting for the
resultset?
HTH
Jerry
"ari" <ari@.discussions.microsoft.com> wrote in message
news:0FEB617D-F1D3-4B11-A099-B375DB03D4A0@.microsoft.com...
> hey all,
> Name, Inv(y/n), Desc
> --
> Cust1, null, Desc1
> Cust1, null, Desc2
> Cust1, Y, Desc1
> Cust2, null, Desc1
> Cust2, Y, Desc2
> How would i select distinct customer names and get all the fields in my
> result at the same time?
> i know about select distinct name from table
> but that only gives my 1 column and i need all the fields to show up.
> thanks,
> ari|||ok i understand now. Thank you.
"Jerry Spivey" wrote:

> Ari
> The DISTINCT clause applies to ALL columns in the column list of the SELEC
T
> statment. Based on the data you supplied, what are you expecting for the
> resultset?
> HTH
> Jerry
> "ari" <ari@.discussions.microsoft.com> wrote in message
> news:0FEB617D-F1D3-4B11-A099-B375DB03D4A0@.microsoft.com...
>
>

How to query?

In the following query, @.CusVenRefLike is a string entered by user, it is by default equal to '%' so that the query return all string when user enter nothing.

SELECT Voucher.VoucherID, Voucher.VoucherRef, Customer.CustomerRef
FROM Voucher
LEFT Join Customer on Voucher.CustomerID=Customer.CustomerID
WHERE Customer.CustomerRef LIKE @.CusVenRefLike

I find that the resulting dataset contain ONLY rows that Voucher.CustomerID<>-1. The reason is that when Voucher.CustomerID= -1, Customer.CustomerRef=NULL which is not included in Customer.CustomerRef LIKE '%' filter.

How can I include those row with Voucher.CustomerID=-1?

Thanks

You need to add additional constraints to your query, depending on exactly how you want it to work.

A simple approach is to use Coalesce(CustomerRef, '' ) to force the null into an empty string. This allows the match to procede. In fact, if there is no special meaning to the CustomerRef being NULL you might want to add a default to the column.

If you want to include nulls in any search, then add Or CustomerRef Is Null to the Where clause. You could also add Or (CustomerRef Is Null and @.CusVenRefLike = '%').

|||

SELECT Voucher.VoucherID, Voucher.VoucherRef, Customer.CustomerRef
FROM Voucher
LEFT Join Customer on Voucher.CustomerID=Customer.CustomerID
WHERE Customer.CustomerRef LIKE @.CusVenRefLike

or Customer.CustomerRef is null

|||

Can do it as this as well..

SELECT Voucher.VoucherID, Voucher.VoucherRef, Customer.CustomerRef
FROM Voucher
LEFT Join Customer on Voucher.CustomerID=Customer.CustomerID
WHERE isnull(Customer.CustomerRef ,'') LIKE @.CusVenRefLike

How to Query... Please help

hi friends,

I have a table whose one field is 'monthname' containing month names. I want to get these month names as field values with an SQLQry. no Stored procedures or functions can be used.

Normal Result: (Select monthname Month,Count(ID) Count From TestData Group By Monthname)

MonthCountJan2Feb7Mar3Apr4May5Jun2Jul2Aug4Sept5Oct6Nov4Dec4

My Required Format:

IDJanFebMarAprMayJunJulAugSeptOctNovDec1231234888665555

(this month name is just an example, it can be anything. like year or Flight Name. I mean i cant hard code like 'Select * From TestData where monthName = 'Jan'')

Got me...?

Thanking,

:)

Search these forums for pivot. Or advanced search where I say something about pivot in this forum.

How to query xml data in column type xml

How can I query xml data in column type xml using an xml schema from the same table row in the xmlschema column and return values(not xml) to insert into a seperate table

XML Data Table

C1 =RecordID (int)

C2 =XMLData (xml)

C3 = XMLSchema(xml)

How can I query the XML data using the xmlshema column

and return values(not xml) to insert into table 2

Table 2

C1 = RecordID

C2 = User

C3 = date

thanks

I think you are looking for the XQuery "nodes()" function if I understand you correctly. It sounds like you want to reach into the column that is storing the schema, extract scalar values from that schema and store it in a relational table. If this is what you are trying to do, then the nodes() function should work for you.

Here is an example using the nodes() function on the xml data type.

http://msdn2.microsoft.com/en-us/ms188282.aspx

Once you get the data into relational form, you can CURSOR over it and call your INSERT statement with parameters, use INSERT INTO ... SELECT, or generate dynamic SQL.

How to query XML

This is the content of one of the rows of the Message column.
<ERINOT VersionMajor="1" VersionMinor="1"
xmlns:mstns="http://tempuri.org/Dataset1.xsd">
<MessageId>
<SenderId>900013581</SenderId>
<ReceiverId>900013574</ReceiverId>
<GenerationDateTime>2005-09-15T05:54:01</GenerationDateTime>
<MessageType>PAS</MessageType>
<MessageNo>22412022</MessageNo>
<MessageFunction>9</MessageFunction>
</MessageId>
</ERINOT>
How can I query let's say the MessageType?
I tried this:
SET ARITHABORT ON
SELECT ID, Message.value('(/ERINOT/MessageId/@.MessageType)[0]',
'varchar(40)') as MType
FROM ERINOT
But this does not work
Any suggestion is higly appreciated,
Erik
E!k Visser wrote:
> This is the content of one of the rows of the Message column.
> <ERINOT VersionMajor="1" VersionMinor="1"
> xmlns:mstns="http://tempuri.org/Dataset1.xsd">
> <MessageId>
> <SenderId>900013581</SenderId>
> <ReceiverId>900013574</ReceiverId>
> <GenerationDateTime>2005-09-15T05:54:01</GenerationDateTime>
> <MessageType>PAS</MessageType>
> <MessageNo>22412022</MessageNo>
> <MessageFunction>9</MessageFunction>
> </MessageId>
> </ERINOT>
> How can I query let's say the MessageType?
> I tried this:
> SET ARITHABORT ON
> SELECT ID, Message.value('(/ERINOT/MessageId/@.MessageType)[0]',
> 'varchar(40)') as MType
> FROM ERINOT
@.MessageType selects an attribute while you have a MessageType element.
Furthermore with XPath/XQuery the first node has index 1 not 0 so use
(/ERINOT/MessageId/MessageType)[1]

Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
|||Or if your XML is not constrained by a schema, the following is more
efficient by directly accessing the text node:
(/ERINOT/MessageId/MessageType/text())[1]
Best regards
Michael
"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:ObjdJ2osHHA.2164@.TK2MSFTNGP02.phx.gbl...
> E!k Visser wrote:
> @.MessageType selects an attribute while you have a MessageType element.
> Furthermore with XPath/XQuery the first node has index 1 not 0 so use
> (/ERINOT/MessageId/MessageType)[1]
>
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/
|||Michael,
Thanks for your contribution, but I do not understand what would be the
difference.
What would the query look like in you scenario?
Perhaps good to know I might be limited by the fact I have to use ADO to
access the database.
Erik
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> schreef in bericht
news:uexsXRpsHHA.412@.TK2MSFTNGP04.phx.gbl...
> Or if your XML is not constrained by a schema, the following is more
> efficient by directly accessing the text node:
> (/ERINOT/MessageId/MessageType/text())[1]
> Best regards
> Michael
> "Martin Honnen" <mahotrash@.yahoo.de> wrote in message
> news:ObjdJ2osHHA.2164@.TK2MSFTNGP02.phx.gbl...
>
|||Martin,
"Martin Honnen" <mahotrash@.yahoo.de> schreef in bericht
news:ObjdJ2osHHA.2164@.TK2MSFTNGP02.phx.gbl...
> E!k Visser wrote:
> @.MessageType selects an attribute while you have a MessageType element.
> Furthermore with XPath/XQuery the first node has index 1 not 0 so use
> (/ERINOT/MessageId/MessageType)[1]
Thank you very much! This works like a charm.
But no I have another question, how can I list all nodes?
This could be the XML:
<ContainerMatrixes>
<ContainerMatrix>
<ContRange>RNG20</ContRange>
<Number>0</Number>
<ContStatus>6</ContStatus>
</ContainerMatrix>
<ContainerMatrix>
<ContRange>RNG30</ContRange>
<Number>0</Number>
<ContStatus>6</ContStatus>
</ContainerMatrix>
<ContainerMatrix>
<ContRange>RNG40</ContRange>
<Number>0</Number>
<ContStatus>6</ContStatus>
</ContainerMatrix>
</ContainerMatrixes>
And now I Like to query the ContRange and have three rows as result. It
should be as generic as possible, as in this case a ship usually carries
more than three containers...
Any suggestions?
Erik
|||E!k Visser wrote:

> But no I have another question, how can I list all nodes?
> This could be the XML:
> <ContainerMatrixes>
> <ContainerMatrix>
> <ContRange>RNG20</ContRange>
> <Number>0</Number>
> <ContStatus>6</ContStatus>
> </ContainerMatrix>
> <ContainerMatrix>
> <ContRange>RNG30</ContRange>
> <Number>0</Number>
> <ContStatus>6</ContStatus>
> </ContainerMatrix>
> <ContainerMatrix>
> <ContRange>RNG40</ContRange>
> <Number>0</Number>
> <ContStatus>6</ContStatus>
> </ContainerMatrix>
> </ContainerMatrixes>
> And now I Like to query the ContRange and have three rows as result.
Here is an example, use the nodes method to shred the XML into rows,
then use one of the methods query or value to extract values.
DECLARE @.x XML;
SET @.x = ' <ContainerMatrixes>
<ContainerMatrix>
<ContRange>RNG20</ContRange>
<Number>0</Number>
<ContStatus>6</ContStatus>
</ContainerMatrix>
<ContainerMatrix>
<ContRange>RNG30</ContRange>
<Number>0</Number>
<ContStatus>6</ContStatus>
</ContainerMatrix>
<ContainerMatrix>
<ContRange>RNG40</ContRange>
<Number>0</Number>
<ContStatus>6</ContStatus>
</ContainerMatrix>
</ContainerMatrixes>';
SELECT T.ContRange.query('.')
FROM @.x.nodes('ContainerMatrixes/ContainerMatrix/ContRange') AS
T(ContRange);
Result is
<ContRange>RNG20</ContRange>
<ContRange>RNG30</ContRange>
<ContRange>RNG40</ContRange>

Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
|||Martin,

> Here is an example, use the nodes method to shred the XML into rows, then
> use one of the methods query or value to extract values.
> DECLARE @.x XML;
> SET @.x = ' <ContainerMatrixes>
> <ContainerMatrix>
> <ContRange>RNG20</ContRange>
> <Number>0</Number>
> <ContStatus>6</ContStatus>
> </ContainerMatrix>
> <ContainerMatrix>
> <ContRange>RNG30</ContRange>
> <Number>0</Number>
> <ContStatus>6</ContStatus>
> </ContainerMatrix>
> <ContainerMatrix>
> <ContRange>RNG40</ContRange>
> <Number>0</Number>
> <ContStatus>6</ContStatus>
> </ContainerMatrix>
> </ContainerMatrixes>';
> SELECT T.ContRange.query('.')
> FROM @.x.nodes('ContainerMatrixes/ContainerMatrix/ContRange') AS
> T(ContRange);
> Result is
> <ContRange>RNG20</ContRange>
> <ContRange>RNG30</ContRange>
> <ContRange>RNG40</ContRange>
Thanks for this, it is very helpfull. Studying the BOL I found the way to
use this with a table as well.
For the moment it leaves me with (another) question:
How can I tranform the XML result I get with this towards the value?
TIA,
Erik
|||Found, thanks anyway.
">> Result is
> Thanks for this, it is very helpfull. Studying the BOL I found the way to
> use this with a table as well.
> For the moment it leaves me with (another) question:
> How can I tranform the XML result I get with this towards the value?
> TIA,
> Erik
|||The query below would look like:
SELECT ID, Message.value('(/ERINOT/MessageId/@.MessageType/text())[1]',
'varchar(40)') as MType
FROM ERINOT
Best regards
Michael
"E!k Visser" <burod004@.planet.nl> wrote in message
news:5dqj01F33sav3U1@.mid.individual.net...
> Michael,
> Thanks for your contribution, but I do not understand what would be the
> difference.
> What would the query look like in you scenario?
> Perhaps good to know I might be limited by the fact I have to use ADO to
> access the database.
> Erik
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> schreef in bericht
> news:uexsXRpsHHA.412@.TK2MSFTNGP04.phx.gbl...
>
|||Thanks!
Had to like twice but now I see the difference.
regards,
Erik
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> schreef in bericht
news:%23IejCQEtHHA.3400@.TK2MSFTNGP03.phx.gbl...
> The query below would look like:
> SELECT ID, Message.value('(/ERINOT/MessageId/@.MessageType/text())[1]',
> 'varchar(40)') as MType
> FROM ERINOT
> Best regards
> Michael
> "E!k Visser" <burod004@.planet.nl> wrote in message
> news:5dqj01F33sav3U1@.mid.individual.net...
>

How to query XML

This is the content of one of the rows of the Message column.
<ERINOT VersionMajor="1" VersionMinor="1"
xmlns:mstns="http://tempuri.org/Dataset1.xsd">
<MessageId>
<SenderId>900013581</SenderId>
<ReceiverId>900013574</ReceiverId>
<GenerationDateTime>2005-09-15T05:54:01</GenerationDateTime>
<MessageType>PAS</MessageType>
<MessageNo>22412022</MessageNo>
<MessageFunction>9</MessageFunction>
</MessageId>
</ERINOT>
How can I query let's say the MessageType?
I tried this:
SET ARITHABORT ON
SELECT ID, Message.value('(/ERINOT/MessageId/@.MessageType)[0]',
'varchar(40)') as MType
FROM ERINOT
But this does not work
Any suggestion is higly appreciated,
ErikE!k Visser wrote:
> This is the content of one of the rows of the Message column.
> <ERINOT VersionMajor="1" VersionMinor="1"
> xmlns:mstns="http://tempuri.org/Dataset1.xsd">
> <MessageId>
> <SenderId>900013581</SenderId>
> <ReceiverId>900013574</ReceiverId>
> <GenerationDateTime>2005-09-15T05:54:01</GenerationDateTime>
> <MessageType>PAS</MessageType>
> <MessageNo>22412022</MessageNo>
> <MessageFunction>9</MessageFunction>
> </MessageId>
> </ERINOT>
> How can I query let's say the MessageType?
> I tried this:
> SET ARITHABORT ON
> SELECT ID, Message.value('(/ERINOT/MessageId/@.MessageType)[0]',
> 'varchar(40)') as MType
> FROM ERINOT
@.MessageType selects an attribute while you have a MessageType element.
Furthermore with XPath/XQuery the first node has index 1 not 0 so use
(/ERINOT/MessageId/MessageType)[1]
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/|||Or if your XML is not constrained by a schema, the following is more
efficient by directly accessing the text node:
(/ERINOT/MessageId/MessageType/text())[1]
Best regards
Michael
"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:ObjdJ2osHHA.2164@.TK2MSFTNGP02.phx.gbl...
> E!k Visser wrote:
> @.MessageType selects an attribute while you have a MessageType element.
> Furthermore with XPath/XQuery the first node has index 1 not 0 so use
> (/ERINOT/MessageId/MessageType)[1]
>
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/|||Michael,
Thanks for your contribution, but I do not understand what would be the
difference.
What would the query look like in you scenario?
Perhaps good to know I might be limited by the fact I have to use ADO to
access the database.
Erik
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> schreef in bericht
news:uexsXRpsHHA.412@.TK2MSFTNGP04.phx.gbl...
> Or if your XML is not constrained by a schema, the following is more
> efficient by directly accessing the text node:
> (/ERINOT/MessageId/MessageType/text())[1]
> Best regards
> Michael
> "Martin Honnen" <mahotrash@.yahoo.de> wrote in message
> news:ObjdJ2osHHA.2164@.TK2MSFTNGP02.phx.gbl...
>|||Martin,
"Martin Honnen" <mahotrash@.yahoo.de> schreef in bericht
news:ObjdJ2osHHA.2164@.TK2MSFTNGP02.phx.gbl...
> E!k Visser wrote:
> @.MessageType selects an attribute while you have a MessageType element.
> Furthermore with XPath/XQuery the first node has index 1 not 0 so use
> (/ERINOT/MessageId/MessageType)[1]
Thank you very much! This works like a charm.
But no I have another question, how can I list all nodes?
This could be the XML:
<ContainerMatrixes>
<ContainerMatrix>
<ContRange>RNG20</ContRange>
<Number>0</Number>
<ContStatus>6</ContStatus>
</ContainerMatrix>
<ContainerMatrix>
<ContRange>RNG30</ContRange>
<Number>0</Number>
<ContStatus>6</ContStatus>
</ContainerMatrix>
<ContainerMatrix>
<ContRange>RNG40</ContRange>
<Number>0</Number>
<ContStatus>6</ContStatus>
</ContainerMatrix>
</ContainerMatrixes>
And now I Like to query the ContRange and have three rows as result. It
should be as generic as possible, as in this case a ship usually carries
more than three containers...
Any suggestions?
Erik|||E!k Visser wrote:

> But no I have another question, how can I list all nodes?
> This could be the XML:
> <ContainerMatrixes>
> <ContainerMatrix>
> <ContRange>RNG20</ContRange>
> <Number>0</Number>
> <ContStatus>6</ContStatus>
> </ContainerMatrix>
> <ContainerMatrix>
> <ContRange>RNG30</ContRange>
> <Number>0</Number>
> <ContStatus>6</ContStatus>
> </ContainerMatrix>
> <ContainerMatrix>
> <ContRange>RNG40</ContRange>
> <Number>0</Number>
> <ContStatus>6</ContStatus>
> </ContainerMatrix>
> </ContainerMatrixes>
> And now I Like to query the ContRange and have three rows as result.
Here is an example, use the nodes method to shred the XML into rows,
then use one of the methods query or value to extract values.
DECLARE @.x XML;
SET @.x = ' <ContainerMatrixes>
<ContainerMatrix>
<ContRange>RNG20</ContRange>
<Number>0</Number>
<ContStatus>6</ContStatus>
</ContainerMatrix>
<ContainerMatrix>
<ContRange>RNG30</ContRange>
<Number>0</Number>
<ContStatus>6</ContStatus>
</ContainerMatrix>
<ContainerMatrix>
<ContRange>RNG40</ContRange>
<Number>0</Number>
<ContStatus>6</ContStatus>
</ContainerMatrix>
</ContainerMatrixes>';
SELECT T.ContRange.query('.')
FROM @.x.nodes('ContainerMatrixes/ContainerMatrix/ContRange') AS
T(ContRange);
Result is
<ContRange>RNG20</ContRange>
<ContRange>RNG30</ContRange>
<ContRange>RNG40</ContRange>
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/|||Martin,

> Here is an example, use the nodes method to shred the XML into rows, then
> use one of the methods query or value to extract values.
> DECLARE @.x XML;
> SET @.x = ' <ContainerMatrixes>
> <ContainerMatrix>
> <ContRange>RNG20</ContRange>
> <Number>0</Number>
> <ContStatus>6</ContStatus>
> </ContainerMatrix>
> <ContainerMatrix>
> <ContRange>RNG30</ContRange>
> <Number>0</Number>
> <ContStatus>6</ContStatus>
> </ContainerMatrix>
> <ContainerMatrix>
> <ContRange>RNG40</ContRange>
> <Number>0</Number>
> <ContStatus>6</ContStatus>
> </ContainerMatrix>
> </ContainerMatrixes>';
> SELECT T.ContRange.query('.')
> FROM @.x.nodes('ContainerMatrixes/ContainerMatrix/ContRange') AS
> T(ContRange);
> Result is
> <ContRange>RNG20</ContRange>
> <ContRange>RNG30</ContRange>
> <ContRange>RNG40</ContRange>
Thanks for this, it is very helpfull. Studying the BOL I found the way to
use this with a table as well.
For the moment it leaves me with (another) question:
How can I tranform the XML result I get with this towards the value?
TIA,
Erik|||Found, thanks anyway.
">> Result is
> Thanks for this, it is very helpfull. Studying the BOL I found the way to
> use this with a table as well.
> For the moment it leaves me with (another) question:
> How can I tranform the XML result I get with this towards the value?
> TIA,
> Erik|||The query below would look like:
SELECT ID, Message.value('(/ERINOT/MessageId/@.MessageType/text())[1]',
'varchar(40)') as MType
FROM ERINOT
Best regards
Michael
"E!k Visser" <burod004@.planet.nl> wrote in message
news:5dqj01F33sav3U1@.mid.individual.net...
> Michael,
> Thanks for your contribution, but I do not understand what would be the
> difference.
> What would the query look like in you scenario?
> Perhaps good to know I might be limited by the fact I have to use ADO to
> access the database.
> Erik
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> schreef in bericht
> news:uexsXRpsHHA.412@.TK2MSFTNGP04.phx.gbl...
>|||Thanks!
Had to like twice but now I see the difference.
regards,
Erik
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> schreef in bericht
news:%23IejCQEtHHA.3400@.TK2MSFTNGP03.phx.gbl...
> The query below would look like:
> SELECT ID, Message.value('(/ERINOT/MessageId/@.MessageType/text())[1]',
> 'varchar(40)') as MType
> FROM ERINOT
> Best regards
> Michael
> "E!k Visser" <burod004@.planet.nl> wrote in message
> news:5dqj01F33sav3U1@.mid.individual.net...
>

How to query two tables from different database

Hi,
I'm trying to query some data from two database (d1.tbl1 and d2.tbl2).
How can I do this? I'm new to SQL.
Thanks in advance,
SarahSELECT t1.col1, t2.col2
FROM database1.dbo.table1 t1
INNER JOIN database2.dbo.table2 t2
ON t1.pkey = t2.fkey
If you give more precise requirements (see my signature) we can give a more
precise answer.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"SG" <sguo@.coopervision.ca> wrote in message
news:uIQd0H4PFHA.2876@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm trying to query some data from two database (d1.tbl1 and d2.tbl2).
> How can I do this? I'm new to SQL.
> Thanks in advance,
> Sarah
>|||Thanks so much for your response. It works.
Sarah
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23Kqs$I4PFHA.3336@.TK2MSFTNGP09.phx.gbl...
> SELECT t1.col1, t2.col2
> FROM database1.dbo.table1 t1
> INNER JOIN database2.dbo.table2 t2
> ON t1.pkey = t2.fkey
> If you give more precise requirements (see my signature) we can give a
> more precise answer.
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "SG" <sguo@.coopervision.ca> wrote in message
> news:uIQd0H4PFHA.2876@.TK2MSFTNGP10.phx.gbl...
>

How to query two MS SQL DBs on the same server inside a Stored Procedure

Okay, so I have a problem and I would be REALLY grateful for any
assistance anyone can offer because I have found little or no help on
the web anywhere.

I want to access and do joins between tables in two different SQL db's
on the same server. Heres what Im dealing with.

In one database resides all of my security features for our clients,
where it decides who can login, etc etc...

In another database, I need to cross reference with a few fields in my
security db.

See the issue Im running into here is that because the way the people
have their databases set up for different products, I would normally
have to put these tables with security features in every database...
which is horrible, because every time I do an update I would have to
do it in 12 different places. Thats not efficient at all.

So I thought if I had one central DB, where all security features are
controlled from, that would be perfect... now the issue is cross
referencing and doing joins with other tables that ARENT in the same
db...

have I lost you yet?

I appreciate all of your help!

THANKS!!google@.digitallsd.com (JMack) wrote in message news:<472b479f.0402170642.37a57121@.posting.google.com>...
> Okay, so I have a problem and I would be REALLY grateful for any
> assistance anyone can offer because I have found little or no help on
> the web anywhere.
> I want to access and do joins between tables in two different SQL db's
> on the same server. Heres what Im dealing with.
> In one database resides all of my security features for our clients,
> where it decides who can login, etc etc...
> In another database, I need to cross reference with a few fields in my
> security db.
> See the issue Im running into here is that because the way the people
> have their databases set up for different products, I would normally
> have to put these tables with security features in every database...
> which is horrible, because every time I do an update I would have to
> do it in 12 different places. Thats not efficient at all.
> So I thought if I had one central DB, where all security features are
> controlled from, that would be perfect... now the issue is cross
> referencing and doing joins with other tables that ARENT in the same
> db...
>
> have I lost you yet?
> I appreciate all of your help!
> THANKS!!

As a general answer to your question, you can write code like this:

select *
from dbo.ThisTable t1
join ThatDatabase.dbo.ThatTable t2
on t1.KeyColumn = t2.KeyColumn

Assuming you have SQL2000 (you didn't mention the version), you should
review the "Using Ownership Chains" topic in Books Online for
information about cross-database ownership chains (and there is an
article in the current SQL Server Magazine also).

Simon|||well unfortunately I'm in SQL 7 so that option doesnt apply to me.

The only other way I can see around it, is taking the tables I need
available to all the db's and replicating them from one publishing db...

which is overkill, but because of the way the system is set up this is
the only other option i can think of aside from replication is trying to
get IT to updgrade to SQL 2000

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||
Oh damn buddy,

you have saved my life. IT WORKS.

THANK YOU SO MUCH!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||"Jeremy Mack" <google@.digitallsd.com> wrote in message
news:4032799b$0$199$75868355@.news.frii.net...
> well unfortunately I'm in SQL 7 so that option doesnt apply to me.
> The only other way I can see around it, is taking the tables I need
> available to all the db's and replicating them from one publishing db...
> which is overkill, but because of the way the system is set up this is
> the only other option i can think of aside from replication is trying to
> get IT to updgrade to SQL 2000
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Sorry if my explanation was misleading - joining on another database will
work fine in SQL7, but in SQL2000 the default cross-database chaining
behaviour changed in SP3, which is why it's worth reviewing the
documentation.

Simon|||JMack (google@.digitallsd.com) writes:
> Okay, so I have a problem and I would be REALLY grateful for any
> assistance anyone can offer because I have found little or no help on
> the web anywhere.
> I want to access and do joins between tables in two different SQL db's
> on the same server. Heres what Im dealing with.
> In one database resides all of my security features for our clients,
> where it decides who can login, etc etc...
> In another database, I need to cross reference with a few fields in my
> security db.
> See the issue Im running into here is that because the way the people
> have their databases set up for different products, I would normally
> have to put these tables with security features in every database...
> which is horrible, because every time I do an update I would have to
> do it in 12 different places. Thats not efficient at all.
> So I thought if I had one central DB, where all security features are
> controlled from, that would be perfect... now the issue is cross
> referencing and doing joins with other tables that ARENT in the same
> db...

I see that you have got a solution working.

But I am a little wary of hard-coding database references. The day
you need to set up a test environment on the same server, you have
trouble...

One alternative would be to have a central database which you maintain,
and then use replication to push those updates to the other places.
Although admittedly, replication might be a little heavy-duty for this...

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How to Query to get the Numeric fields Only in the Table

hi Good Day everyone.

how can i query numeric values in the specific fields for example EmployeeID only.

but the field EmployeeID has a datatype of varchar.

for example :

EmployeeID -> Field

001

002

a

b

c

how do i query 001 and 002 only. tnx

here it is,

Code Snippet

Create Table #employee (

[EmployeeID] Varchar(100)

);

Insert Into #employee Values('001');

Insert Into #employee Values('002');

Insert Into #employee Values('a');

Insert Into #employee Values('b');

Insert Into #employee Values('c');

Insert Into #employee Values('1E2');

Insert Into #employee Values('.');

Insert Into #employee Values('$');

Insert Into #employee Values('E0');

Select

*

from

#employee

Where

Isnumeric([EmployeeID]) = 1

And [EmployeeID]not like '%[^0-9]%'

|||Thanks Manivannan.D.Sekaran it helps me a lot.

How to query this?

Hello,
Long time I am puzzled over the following task. This problem should be
similar to many systems, and it would be interesting to know what are best
practices to solve this.
The setup:
Table, that contains log of events. Typical fields would be: 1) uniqueID; 2)
evtTime; 3)evtType. Then there are some additional fields that carry
additional logged event data.
Sample data:
ID evtTime evtType
1 10-11-2005 A
2 13-11-2005 B
3 20-11-2005 C
4 22-11-2005 B
Now, if I want to perform analysis on this data, typical need would be to
analyze sequence of event types. In the sample data above, the sequence is
A-B-C-B (ordering by date-time). I find it very complex to create a query
that would let me find sequence of events by given pattern.
For example, I could use query to discover the next element in sequence and,
thus, use this expression for querying:
SELECT evtType, (SELECT TOP 1 evtType FROM SampleData WHERE
evtTime>Level1.evtTime ORDER BY logDate) AS nextType FROM SampleData AS
Level1
It would be possible to extract also the second next event type in sequence
in similar fashion, but I am afraid that the complexity of the overall query
would be unreasonable and performance would be slow. Plus, if I want also,
for example, count days when the "event type A was in force", I need to add
nex subquery for the evtTime:
SELECT evtType, evtTime, (SELECT TOP 1 evtType FROM SampleData WHERE
evtTime>Level1.evtTime ORDER BY logDate) AS nextType, (SELECT TOP 1 evtTime
FROM SampleData WHERE evtTime>Level1.evtTime ORDER BY logDate) AS nextTime
FROM SampleData AS Level1
Only then I could find the sequences that, for example, have "event A in
force at least 10 days, then followed by event B, and then by C".
How this standard need could be solved so that I don't need to create large
temporary tables, populated with data by raw code?
PavilsOn Tue, 15 Nov 2005 16:30:55 +0200, Pavils Jurjans wrote:
(snip)
>The setup:
>Table, that contains log of events. Typical fields would be: 1) uniqueID; 2
)
>evtTime; 3)evtType. Then there are some additional fields that carry
>additional logged event data.
>Sample data:
>ID evtTime evtType
>1 10-11-2005 A
>2 13-11-2005 B
>3 20-11-2005 C
>4 22-11-2005 B
>Now, if I want to perform analysis on this data, typical need would be to
>analyze sequence of event types. In the sample data above, the sequence is
>A-B-C-B (ordering by date-time). I find it very complex to create a query
>that would let me find sequence of events by given pattern.
(snip)
>Only then I could find the sequences that, for example, have "event A in
>force at least 10 days, then followed by event B, and then by C".
Hi Pavils,
If this is your typical reporting need, then I recommend that you
transform the data from events to durations (or states, or whatever term
is appropriate in your case).
CREATE TABLE Durations
(StartDate smalldatetime NOT NULL,
EndDate smalldatetime DEFAULT NULL,
EventType char(1) NOT NULL,
PRIMARY KEY (StartDate),
UNIQUE (EndDate),
FOREIGN KEY (EndDate) REFERENCES Durations (StartDate),
CHECK(EndDate > StartDate)
)
In most cases, I'd make Durations a view instead of a table. This might
well be an exception to that rule. The view could be slow, since it
would require a self-join. If your queries would then involve joining
the view to itself, you'd effectively be joining the base table to an
exponential increasing number of copies of itself.
Durations as indexed view would be ideal, but I don't think it's
possible to write a query that computes the Durations correctly without
breaking the limitations for indexed views.
That leaves you with two options for how to populate the table:
1. Remove the Events table; change the stored procedure or front-end
code that currently popultes the Events table to make the correct
changes to the Durations table, or
2. Keep the Events table; leave front-end and stored procedures
unchanges, but add insert, update and delete triggers on the Events
table to copy over all changes to the Durations table.
Once you have the Durations table, the query you give in your final
example would be:
SELECT d1.StartDate, d2,StartDate, d3.StartDate, d3.EndDate
FROM Durations AS d1
INNER JOIN Durations AS d2
ON d2.StartDate = d1.EndDate
INNER JOIN Durations AS d3
ON d3.StartDate = d2.EndDate
WHERE d1.EndDate >= DATEADD(day, 10, d1.StartDate)
AND d1.EventType = 'A'
AND d2.EventType = 'B'
AND d3.EventType = 'C'
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks, Hugo for you input.
I studied it carefully. To extend my proposed sample to my real case, I must
add that the events are registered for large number of subjects, so the
events table also has reference to subject ID. Your query then could look
like
SELECT d1.StartDate, d2,StartDate, d3.StartDate, d3.EndDate
FROM Durations AS d1
INNER JOIN Durations AS d2
ON d2.StartDate = d1.EndDate AND d2.subID=d1.subID
INNER JOIN Durations AS d3
ON d3.StartDate = d2.EndDate AND d3.subID=d2.subID
WHERE d1.EndDate >= DATEADD(day, 10, d1.StartDate)
AND d1.EventType = 'A'
AND d2.EventType = 'B'
AND d3.EventType = 'C'
Perhaps that just adds another load to the view. Building a temp table could
be solution for some standard reports, but the data are updated every minute
and some reports are asking another criteria on the series, so perhaps view
or dynamically generated query, while potentially slow, could be the most
realistic alternative.
However, I am now a bit worried that it seems that theoretically there could
be cases when the dates of succeeding events could be identical, so
apparently I need another order field. It seems that the best (could easily
be used for self-joins) would be carrying reference to previous event ID,
but it involves difficulty how to order the events in the right order by
simple SQL server means. The only option left then is a clumsy "orderNumber"
that will be a hassle to populate and it will be even hasslier to insert a
new event in existing series. Lots of code will be required to keep that
structure intact with no errors in numeration. Perhaps I need to weigh how
much I need a query that would give ordered list of events vs doing this
ordering by code.
Another concern is making self-joins vs using subqueries. I could get the
fields from succeeding event quite easily, and also getting fields from next
succeeding event seems no problem. The question is only about how effective
is this. Perhaps only tests will show this. I am sort of dismayed that so
few tools are built in MSSQL to analyse record series - that is typical need
in complex analysis and caching all the analysis sample in application
memory for proprietary code analysis sometimes is very ineffective.
Regards,
Pavils Jurjans|||On Mon, 21 Nov 2005 19:37:59 +0200, Pavils Jurjans wrote:

>Thanks, Hugo for you input.
>I studied it carefully. To extend my proposed sample to my real case, I mus
t
>add that the events are registered for large number of subjects, so the
>events table also has reference to subject ID. Your query then could look
>like
>SELECT d1.StartDate, d2,StartDate, d3.StartDate, d3.EndDate
>FROM Durations AS d1
>INNER JOIN Durations AS d2
> ON d2.StartDate = d1.EndDate AND d2.subID=d1.subID
>INNER JOIN Durations AS d3
> ON d3.StartDate = d2.EndDate AND d3.subID=d2.subID
>WHERE d1.EndDate >= DATEADD(day, 10, d1.StartDate)
>AND d1.EventType = 'A'
>AND d2.EventType = 'B'
>AND d3.EventType = 'C'
Hi Pavils,
Yes, that's how you would change it. I'd probably also add the subject
to the SELECT clause.

>Perhaps that just adds another load to the view.
Depends. With the right indexes it *might* even speed things up.

> Building a temp table could
>be solution for some standard reports, but the data are updated every minut
e
>and some reports are asking another criteria on the series, so perhaps view
>or dynamically generated query, while potentially slow, could be the most
>realistic alternative.
Yes.
But if that's to slow, then consider permanently storing both the
beginning and the end of the events. There are various ways to do this:
1. Change it in whatever currently produces the data.
2. Use a stored procedure to handle insertions; add code to that SP to
fill the end date of the "previous" event.
3. Create a trigger on the table that will populate end dates as
appropriate.

>However, I am now a bit worried that it seems that theoretically there coul
d
>be cases when the dates of succeeding events could be identical, so
>apparently I need another order field.
Or you could store the time in addition to the date. The smalldatetime
datatype stores date + time with a precision of minutes; the datetime
datatype even has a precision of 3/1000th of a second.

>It seems that the best (could easily
>be used for self-joins) would be carrying reference to previous event ID,
If you choose that, then remove the the end date. I included that to
serve as a reference to the previous event. If you use a different
reference, there's no need to keep the end date.
However, don't ever rely on a system-generated identifier to serve as
the only key for a table. If the "real" key is clumsy, you can add a
system-generated identifier to serve as a "surrogate" key.

>but it involves difficulty how to order the events in the right order by
>simple SQL server means.
Are the events inserted in chronological order? In that case, it's quite
easy to have the system generate an ID value that will keep increasing.
That would make ordering trivial.
(snip)
> I am sort of dismayed that so
>few tools are built in MSSQL to analyse record series - that is typical nee
d
>in complex analysis and caching all the analysis sample in application
>memory for proprietary code analysis sometimes is very ineffective.
You might want to investigate Analysis Services. I must admit that the
only thing I know about it is it's name (in some circles, that's enough
to qualify as "expert"...). But who knows - maye it's just the tool for
your needs!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

how to query this table into the result like this?

im not very good at sql but need to query the database to use in my
programming script.
if the database is just like this

id name parent_id
1 A null <-- root
2 B 1
3 C 1
4 D 1
5 E 2
6 F 2
7 G 3
8 H 4
9 I 4
10 J 4
11 K 9

the data in the above table is just like some sort of tree data
which have parent and child node
and now,how to query into the result like this

the deepest node is in level 4

what i want is how deep this tree data is?
anyone show me the query script or store procedure to find the
result please?
many thanks in advance,

--
Posted via http://dbforums.com"per" <member30981@.dbforums.com> wrote in message news:3168898.1059634365@.dbforums.com...
> im not very good at sql but need to query the database to use in my
> programming script.
> if the database is just like this
> id name parent_id
> 1 A null <-- root
> 2 B 1
> 3 C 1
> 4 D 1
> 5 E 2
> 6 F 2
> 7 G 3
> 8 H 4
> 9 I 4
> 10 J 4
> 11 K 9
> the data in the above table is just like some sort of tree data
> which have parent and child node
> and now,how to query into the result like this
> the deepest node is in level 4
> what i want is how deep this tree data is?
> anyone show me the query script or store procedure to find the
> result please?
> many thanks in advance,
> --
> Posted via http://dbforums.com

CREATE TABLE Tree
(
node_id INT NOT NULL PRIMARY KEY,
node_name VARCHAR(10) NOT NULL UNIQUE,
parent_id INT NULL REFERENCES Tree (node_id)
)

-- Sample data
INSERT INTO Tree (node_id, node_name, parent_id)
VALUES (1, 'A', NULL)
INSERT INTO Tree (node_id, node_name, parent_id)
VALUES (2, 'B', 1)
INSERT INTO Tree (node_id, node_name, parent_id)
VALUES (3, 'C', 1)
INSERT INTO Tree (node_id, node_name, parent_id)
VALUES (4, 'D', 1)
INSERT INTO Tree (node_id, node_name, parent_id)
VALUES (5, 'E', 2)
INSERT INTO Tree (node_id, node_name, parent_id)
VALUES (6, 'F', 2)
INSERT INTO Tree (node_id, node_name, parent_id)
VALUES (7, 'G', 3)
INSERT INTO Tree (node_id, node_name, parent_id)
VALUES (8, 'H', 4)
INSERT INTO Tree (node_id, node_name, parent_id)
VALUES (9, 'I', 4)
INSERT INTO Tree (node_id, node_name, parent_id)
VALUES (10, 'J', 4)
INSERT INTO Tree (node_id, node_name, parent_id)
VALUES (11, 'K', 9)

-- Returns a table of all descendants of a given node and their level numbers
-- If the given node is NULL, use root node of tree
CREATE FUNCTION Descendants(@.root_node VARCHAR(10) = NULL)
RETURNS @.nodes TABLE
(node_id INT NOT NULL PRIMARY KEY,
level_number INT NOT NULL CHECK (level_number >= 0))
AS
BEGIN
IF @.root_node IS NULL
SELECT @.root_node = node_name
FROM Tree
WHERE parent_id IS NULL
IF NOT EXISTS (SELECT * FROM Tree WHERE node_name = @.root_node)
RETURN
DECLARE @.level_number INT,
@.next_level_number INT
SELECT @.level_number = 0,
@.next_level_number = 1
INSERT INTO @.nodes (node_id, level_number)
SELECT node_id, @.level_number
FROM Tree
WHERE node_name = @.root_node
WHILE EXISTS (SELECT * FROM @.nodes
WHERE level_number = @.level_number)
BEGIN
INSERT INTO @.nodes (node_id, level_number)
SELECT T.node_id, @.next_level_number
FROM @.nodes AS N
INNER JOIN
Tree AS T
ON N.level_number = @.level_number AND
N.node_id = T.parent_id
SELECT @.level_number = @.next_level_number,
@.next_level_number = @.next_level_number + 1
END
RETURN
END

CREATE VIEW TreeDepth (depth)
AS
SELECT COALESCE(MAX(level_number) + 1, 0)
FROM Descendants(DEFAULT)

SELECT depth
FROM TreeDepth

depth
4

To find the depth of the tree rooted at other than the tree's root node,
e.g., the depth of the tree rooted at node C, one can do the following

SELECT COALESCE(MAX(level_number) + 1, 0) AS depth
FROM Descendants('C')

depth
2

Regards,
jag