Create a view to get latest status for each application

I would like some help creating a view that will display the latest status for each application. The lastest status should be based on CreateDt.

For example:

Table Structure:

============

Application: ApplicationID, Name, Address, City, State, Zip, etc..

ApplicationAction: ApplicationActionID, ApplicationID, Status (Ex:new, reviewed, approved, closed), CreateDt

View should display:

==============

ApplicantID, ApplicantActionID, Status, CreateDt

Example:

==========

ApplicantID=4, Name=Bob Smith, etc...

ApplicantActionID=1, ApplicantID=4, Status=New, CreatDt=1/3/20071:00

ApplicantActionID=2, ApplicantID=4, Status=Reviewed, CreatDt=1/3/2007 2:00

ApplicantActionID=3, ApplicantID=4, Status=Approved, CreatDt=1/4/2007 1:00

... etc...

View should return:

Applicant=4, ApplicantActionID=3, Status=Approved, CreatDt=1/4/2007 1:00

etc...

Hint: Use MAX(CreatDt) to get the information you need.|||

well I got that far, I need help getting beyond that:( Any ideas?

|||Can you post what you have so far?|||

Oh well..

Declare @.ApTable (ApplicationIDint ,Name varchar(50), Addressvarchar(50), Cityvarchar(50), Statevarchar(50), Zipvarchar(50))Declare @.AATable (ApplicationActionIDint, ApplicationIDint, Statusvarchar(50), CreateDtdatetime)Insert into @.ApSELECt 4,'Bob Smith','123 street','SomeCity','SS','12345'UNIONALLSELECT 5,'New Smith','sss','ss','a','4455'Insert into @.AASELECT 1,4,'New','1/3/2007 1:00'UNIONALLSELECT 2,4,'Reviewed','1/3/2007 2:00'UNIONALLSELECT 3,4,'Approved','1/4/2007 1:00'UNIONALLSELECT 1,5,'New','5/24/2006 1:00'Select *from @.ApSelect *from @.AASELECT aa.*FROM (select applicationid,max(Createdt)as MxCreateDtfrom @.AAgroup by applicationid) Xjoin @.AA AAon aa.createdt = X.MXcreatedt

Create a Temp Table

If I am going to create a temp table and insert data into it, would I
be better of creating the indexes after I do the insert?
Does it matter?If the amount of rows to be inserted is huge, sure. Better to test it.
AMB
"Bob" wrote:

> If I am going to create a temp table and insert data into it, would I
> be better of creating the indexes after I do the insert?
> Does it matter?
>

create a procedure

Hello everyone, i need some help(((
i am creating a procedure as this :
CREATE procedure [dbo].[automated_mark]
with recompile
as
declare @.nom1 nvarchar(50)
declare @.nom2 nvarchar(50)
declare od cursor
for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
open od
while (@.@.fetch_status=0)
begin
fetch next from od into @.nom1,@.nom2
update products set Instock= '10000' where prodid = @.nom1;
end
close od;
deallocate od;
everything is ok, the command completed successfuly.
1-but when i run the procedure by doing :
exec automated_mark
the procedure run, but there is no update.
2- when i open the microsoftsql management studio, and i do right-
click on the percedure,, execute Stored procedure : then the procedure
run, and tha table is updated. BUT when i alter my procedure, and i
change the "update products set Instock= '10000' " with the "update
products set Instock= '2222' ", and i do right-click on the
percedure,, execute Stored procedure THEN the procedure run, BUT NO
UPDATE.
why and how to solve the problem?
thank for your help.
@.@.fetch_status is a global value for each connection. So you cannot depend
on @.@.fetch_status being zero when the stored proc is executed. If you have
previously looped thru a cursor with this connection, doing a
while(@.@.fetch_status=0), then when it left that loop, it was non zero, so it
is still non zero and you loop will not be executed at all. Instead of
open od
while (@.@.fetch_status=0)
begin
fetch next from od into @.nom1,@.nom2
update products set Instock= '10000' where prodid = @.nom1;
end
you want to code the cursor loop like:
open od
fetch next from od into @.nom1,@.nom2
while (@.@.fetch_status=0)
begin
update products set Instock= '10000' where prodid = @.nom1;
fetch next from od into @.nom1,@.nom2
end
Then you are not depending on @.@.fetch_status being zero before your first
fetch.
The other problem with this stored proc is that it uses a cursor. Cursor's
are almost always an ineffecient way to process data in SQL. It is almost
always much better and faster to use a set based solution. In your case, a
better stored proc would look like:
CREATE procedure [dbo].[automated_mark]
with recompile
as
update products
set Instock='10000'
where prodid in (select ProdID from Orders where Orders.Qty > 20);
Tom
"noumian" <n.noumia@.gmail.com> wrote in message
news:13ae24ed-37ea-4dbb-91f5-d278bb4f6b77@.d62g2000hsf.googlegroups.com...
> Hello everyone, i need some help(((
> i am creating a procedure as this :
> --
> CREATE procedure [dbo].[automated_mark]
> with recompile
> as
> declare @.nom1 nvarchar(50)
> declare @.nom2 nvarchar(50)
> declare od cursor
> for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
> open od
> while (@.@.fetch_status=0)
> begin
> fetch next from od into @.nom1,@.nom2
> update products set Instock= '10000' where prodid = @.nom1;
> end
> close od;
> deallocate od;
> --
> everything is ok, the command completed successfuly.
> 1-but when i run the procedure by doing :
> --
> exec automated_mark
> --
> the procedure run, but there is no update.
> 2- when i open the microsoftsql management studio, and i do right-
> click on the percedure,, execute Stored procedure : then the procedure
> run, and tha table is updated. BUT when i alter my procedure, and i
> change the "update products set Instock= '10000' " with the "update
> products set Instock= '2222' ", and i do right-click on the
> percedure,, execute Stored procedure THEN the procedure run, BUT NO
> UPDATE.
> why and how to solve the problem?
> thank for your help.
|||thank you very much Tom.
its now working.thanks.
Now, about the set based solution, i agree with you when i have an
update to do.
But, what if instead an update i have an insert to do, like this :
ALTER procedure [dbo].[automated_mark]
with recompile
as
declare @.nom1 nvarchar(50)
declare @.nom2 nvarchar(50)
declare od cursor
for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
open od
fetch next from od into @.nom1,@.nom2
while (@.@.fetch_status=0)
begin
insert into sales (prodid,qty,action) values (@.nom1,@.nom2,'sold');
fetch next from od into @.nom1,@.nom2
end
close od;
deallocate od;
is there a better way to do that?
thanks again.
|||On Feb 29, 4:14Xam, noumian <n.nou...@.gmail.com> wrote:
> thank you very much Tom.
> its now working.thanks.
> Now, about the set based solution, i agree with you when i have an
> update to do.
> But, what if instead an update i have an insert to do, like this :
> --
> ALTER procedure [dbo].[automated_mark]
> with recompile
> as
> declare @.nom1 nvarchar(50)
> declare @.nom2 nvarchar(50)
> declare od cursor
> X X X X for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
> open od
> X X X X fetch next from od into @.nom1,@.nom2
> X X X X while (@.@.fetch_status=0)
> X X X X X X X X begin
> X X X X X X X X X X X X insert into sales (prodid,qty,action) values (@.nom1,@.nom2,'sold');
> X X X X X X X X X X X X fetch next from od into @.nom1,@.nom2
> X X X X X X X X end
> close od;
> deallocate od;
> --
> is there a better way to do that?
> thanks again.
What about:
INSERT dbo.Sales(ProductID, Quantity) SELECT ProductID, Quantity FROM
dbo.Orders WHERE Orders.Quantity > 20
|||insert into sales (prodid,qty,action)
select Orders.ProdID,Orders.Qty, 'sold' from Orders where Orders.Qty > 20;
Tom
"noumian" <n.noumia@.gmail.com> wrote in message
news:7c48ae96-95b4-4497-818d-2450591dd2d6@.n77g2000hse.googlegroups.com...
> thank you very much Tom.
> its now working.thanks.
> Now, about the set based solution, i agree with you when i have an
> update to do.
> But, what if instead an update i have an insert to do, like this :
> --
> ALTER procedure [dbo].[automated_mark]
> with recompile
> as
> declare @.nom1 nvarchar(50)
> declare @.nom2 nvarchar(50)
> declare od cursor
> for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
> open od
> fetch next from od into @.nom1,@.nom2
> while (@.@.fetch_status=0)
> begin
> insert into sales (prodid,qty,action) values (@.nom1,@.nom2,'sold');
> fetch next from od into @.nom1,@.nom2
> end
> close od;
> deallocate od;
> --
> is there a better way to do that?
> thanks again.
|||On Feb 29, 4:39 pm, Sean <ColdFusion...@.gmail.com> wrote:
> On Feb 29, 4:14 am, noumian <n.nou...@.gmail.com> wrote:
>
>
>
> What about:
> INSERT dbo.Sales(ProductID, Quantity) SELECT ProductID, Quantity FROM
> dbo.Orders WHERE Orders.Quantity > 20
CHECK MY insert: insert into sales (prodid,qty,action) values
(@.nom1,@.nom2,'sold');
what about the values of my column action?
|||On Feb 29, 5:56 pm, "Tom Cooper"
<tomcoo...@.comcast.no.spam.please.net> wrote:[vbcol=seagreen]
> insert into sales (prodid,qty,action)
> select Orders.ProdID,Orders.Qty, 'sold' from Orders where Orders.Qty > 20;
> Tom
> "noumian" <n.nou...@.gmail.com> wrote in message
> news:7c48ae96-95b4-4497-818d-2450591dd2d6@.n77g2000hse.googlegroups.com...
>
CHECK MY insert: insert into sales (prodid,qty,action) values
(@.nom1,@.nom2,'sold');
what about the values of my column action?
|||On Feb 29, 10:22Xam, noumian <n.nou...@.gmail.com> wrote:
> On Feb 29, 5:56 pm, "Tom Cooper"
>
>
> <tomcoo...@.comcast.no.spam.please.net> wrote:
>
>
>
> CHECK MY insert: X insert into sales (prodid,qty,action) values
> (@.nom1,@.nom2,'sold');
> what about the values of my column action... Hide quoted text -
> - Show quoted text -
Argh, sorry it's friday... a very long... friday.
INSERT INTO Sales(prodid, qty, action) SELECT prodid, qty, 'Sold' as
Action FROM Orders WHERE qty > 20
That should do the trick.
|||ok, thanks very much.

create a procedure

Hello everyone, i need some help(((
i am creating a procedure as this :
--
CREATE procedure [dbo].[automated_mark]
with recompile
as
declare @.nom1 nvarchar(50)
declare @.nom2 nvarchar(50)
declare od cursor
for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
open od
while (@.@.fetch_status=0)
begin
fetch next from od into @.nom1,@.nom2
update products set Instock= '10000' where prodid = @.nom1;
end
close od;
deallocate od;
--
everything is ok, the command completed successfuly.
1-but when i run the procedure by doing :
--
exec automated_mark
--
the procedure run, but there is no update.
2- when i open the microsoftsql management studio, and i do right-
click on the percedure,, execute Stored procedure : then the procedure
run, and tha table is updated. BUT when i alter my procedure, and i
change the "update products set Instock= '10000' " with the "update
products set Instock= '2222' ", and i do right-click on the
percedure,, execute Stored procedure THEN the procedure run, BUT NO
UPDATE.
why and how to solve the problem?
thank for your help.@.@.fetch_status is a global value for each connection. So you cannot depend
on @.@.fetch_status being zero when the stored proc is executed. If you have
previously looped thru a cursor with this connection, doing a
while(@.@.fetch_status=0), then when it left that loop, it was non zero, so it
is still non zero and you loop will not be executed at all. Instead of
open od
while (@.@.fetch_status=0)
begin
fetch next from od into @.nom1,@.nom2
update products set Instock= '10000' where prodid = @.nom1;
end
you want to code the cursor loop like:
open od
fetch next from od into @.nom1,@.nom2
while (@.@.fetch_status=0)
begin
update products set Instock= '10000' where prodid = @.nom1;
fetch next from od into @.nom1,@.nom2
end
Then you are not depending on @.@.fetch_status being zero before your first
fetch.
The other problem with this stored proc is that it uses a cursor. Cursor's
are almost always an ineffecient way to process data in SQL. It is almost
always much better and faster to use a set based solution. In your case, a
better stored proc would look like:
CREATE procedure [dbo].[automated_mark]
with recompile
as
update products
set Instock='10000'
where prodid in (select ProdID from Orders where Orders.Qty > 20);
Tom
"noumian" <n.noumia@.gmail.com> wrote in message
news:13ae24ed-37ea-4dbb-91f5-d278bb4f6b77@.d62g2000hsf.googlegroups.com...
> Hello everyone, i need some help(((
> i am creating a procedure as this :
> --
> CREATE procedure [dbo].[automated_mark]
> with recompile
> as
> declare @.nom1 nvarchar(50)
> declare @.nom2 nvarchar(50)
> declare od cursor
> for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
> open od
> while (@.@.fetch_status=0)
> begin
> fetch next from od into @.nom1,@.nom2
> update products set Instock= '10000' where prodid = @.nom1;
> end
> close od;
> deallocate od;
> --
> everything is ok, the command completed successfuly.
> 1-but when i run the procedure by doing :
> --
> exec automated_mark
> --
> the procedure run, but there is no update.
> 2- when i open the microsoftsql management studio, and i do right-
> click on the percedure,, execute Stored procedure : then the procedure
> run, and tha table is updated. BUT when i alter my procedure, and i
> change the "update products set Instock= '10000' " with the "update
> products set Instock= '2222' ", and i do right-click on the
> percedure,, execute Stored procedure THEN the procedure run, BUT NO
> UPDATE.
> why and how to solve the problem?
> thank for your help.|||thank you very much Tom.
its now working.thanks.
Now, about the set based solution, i agree with you when i have an
update to do.
But, what if instead an update i have an insert to do, like this :
--
ALTER procedure [dbo].[automated_mark]
with recompile
as
declare @.nom1 nvarchar(50)
declare @.nom2 nvarchar(50)
declare od cursor
for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
open od
fetch next from od into @.nom1,@.nom2
while (@.@.fetch_status=0)
begin
insert into sales (prodid,qty,action) values (@.nom1,@.nom2,'sold');
fetch next from od into @.nom1,@.nom2
end
close od;
deallocate od;
--
is there a better way to do that?
thanks again.|||On Feb 29, 4:14=A0am, noumian <n.nou...@.gmail.com> wrote:
> thank you very much Tom.
> its now working.thanks.
> Now, about the set based solution, i agree with you when i have an
> update to do.
> But, what if instead an update i have an insert to do, like this :
> --
> ALTER procedure [dbo].[automated_mark]
> with recompile
> as
> declare @.nom1 nvarchar(50)
> declare @.nom2 nvarchar(50)
> declare od cursor
> =A0 =A0 =A0 =A0 for select Orders.ProdID,Orders.Qty from Orders where Orde=rs.Qty > 20
> open od
> =A0 =A0 =A0 =A0 fetch next from od into @.nom1,@.nom2
> =A0 =A0 =A0 =A0 while (@.@.fetch_status=3D0)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 begin
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 insert into sales (prodid,=qty,action) values (@.nom1,@.nom2,'sold');
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 fetch next from od into @.n=om1,@.nom2
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 end
> close od;
> deallocate od;
> --
> is there a better way to do that?
> thanks again.
What about:
INSERT dbo.Sales(ProductID, Quantity) SELECT ProductID, Quantity FROM
dbo.Orders WHERE Orders.Quantity > 20|||insert into sales (prodid,qty,action)
select Orders.ProdID,Orders.Qty, 'sold' from Orders where Orders.Qty > 20;
Tom
"noumian" <n.noumia@.gmail.com> wrote in message
news:7c48ae96-95b4-4497-818d-2450591dd2d6@.n77g2000hse.googlegroups.com...
> thank you very much Tom.
> its now working.thanks.
> Now, about the set based solution, i agree with you when i have an
> update to do.
> But, what if instead an update i have an insert to do, like this :
> --
> ALTER procedure [dbo].[automated_mark]
> with recompile
> as
> declare @.nom1 nvarchar(50)
> declare @.nom2 nvarchar(50)
> declare od cursor
> for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
> open od
> fetch next from od into @.nom1,@.nom2
> while (@.@.fetch_status=0)
> begin
> insert into sales (prodid,qty,action) values (@.nom1,@.nom2,'sold');
> fetch next from od into @.nom1,@.nom2
> end
> close od;
> deallocate od;
> --
> is there a better way to do that?
> thanks again.|||On Feb 29, 4:39 pm, Sean <ColdFusion...@.gmail.com> wrote:
> On Feb 29, 4:14 am, noumian <n.nou...@.gmail.com> wrote:
>
> > thank you very much Tom.
> > its now working.thanks.
> > Now, about the set based solution, i agree with you when i have an
> > update to do.
> > But, what if instead an update i have an insert to do, like this :
> > --
> > ALTER procedure [dbo].[automated_mark]
> > with recompile
> > as
> > declare @.nom1 nvarchar(50)
> > declare @.nom2 nvarchar(50)
> > declare od cursor
> > for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
> > open od
> > fetch next from od into @.nom1,@.nom2
> > while (@.@.fetch_status=0)
> > begin
> > insert into sales (prodid,qty,action) values (@.nom1,@.nom2,'sold');
> > fetch next from od into @.nom1,@.nom2
> > end
> > close od;
> > deallocate od;
> > --
> > is there a better way to do that?
> > thanks again.
> What about:
> INSERT dbo.Sales(ProductID, Quantity) SELECT ProductID, Quantity FROM
> dbo.Orders WHERE Orders.Quantity > 20
CHECK MY insert: insert into sales (prodid,qty,action) values
(@.nom1,@.nom2,'sold');
what about the values of my column action?|||On Feb 29, 5:56 pm, "Tom Cooper"
<tomcoo...@.comcast.no.spam.please.net> wrote:
> insert into sales (prodid,qty,action)
> select Orders.ProdID,Orders.Qty, 'sold' from Orders where Orders.Qty > 20;
> Tom
> "noumian" <n.nou...@.gmail.com> wrote in message
> news:7c48ae96-95b4-4497-818d-2450591dd2d6@.n77g2000hse.googlegroups.com...
> > thank you very much Tom.
> > its now working.thanks.
> > Now, about the set based solution, i agree with you when i have an
> > update to do.
> > But, what if instead an update i have an insert to do, like this :
> > --
> > ALTER procedure [dbo].[automated_mark]
> > with recompile
> > as
> > declare @.nom1 nvarchar(50)
> > declare @.nom2 nvarchar(50)
> > declare od cursor
> > for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
> > open od
> > fetch next from od into @.nom1,@.nom2
> > while (@.@.fetch_status=0)
> > begin
> > insert into sales (prodid,qty,action) values (@.nom1,@.nom2,'sold');
> > fetch next from od into @.nom1,@.nom2
> > end
> > close od;
> > deallocate od;
> > --
> > is there a better way to do that?
> > thanks again.
CHECK MY insert: insert into sales (prodid,qty,action) values
(@.nom1,@.nom2,'sold');
what about the values of my column action?|||On Feb 29, 10:22=A0am, noumian <n.nou...@.gmail.com> wrote:
> On Feb 29, 5:56 pm, "Tom Cooper"
>
>
> <tomcoo...@.comcast.no.spam.please.net> wrote:
> > insert into sales (prodid,qty,action)
> > select Orders.ProdID,Orders.Qty, 'sold' from Orders where Orders.Qty > 2=0;
> > Tom
> > "noumian" <n.nou...@.gmail.com> wrote in message
> >news:7c48ae96-95b4-4497-818d-2450591dd2d6@.n77g2000hse.googlegroups.com...=
> > > thank you very much Tom.
> > > its now working.thanks.
> > > Now, about the set based solution, i agree with you when i have an
> > > update to do.
> > > But, what if instead an update i have an insert to do, like this :
> > > --
> > > ALTER procedure [dbo].[automated_mark]
> > > with recompile
> > > as
> > > declare @.nom1 nvarchar(50)
> > > declare @.nom2 nvarchar(50)
> > > declare od cursor
> > > for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
> > > open od
> > > fetch next from od into @.nom1,@.nom2
> > > while (@.@.fetch_status=3D0)
> > > begin
> > > insert into sales (prodid,qty,action) values (@.nom1,@.nom2,'sold');
> > > fetch next from od into @.nom1,@.nom2
> > > end
> > > close od;
> > > deallocate od;
> > > --
> > > is there a better way to do that?
> > > thanks again.
> CHECK MY insert: =A0 insert into sales (prodid,qty,action) values
> (@.nom1,@.nom2,'sold');
> what about the values of my column action... Hide quoted text -
> - Show quoted text -
Argh, sorry it's friday... a very long... friday.
INSERT INTO Sales(prodid, qty, action) SELECT prodid, qty, 'Sold' as
Action FROM Orders WHERE qty > 20
That should do the trick.|||ok, thanks very much.

Create a constant calculated member

Hi everyone, my question is related to creating a calculated member that must not change when the user changes the selected filter in the dimensions.
I will try to explain it: I need the total number of rows of my cube for a concrete year (year is a dimension). I will use this value in a calculated formula to define the percentage of rows that contains a concrete value (selected in the filter dimension). It means that my calculated member has a factor that change with the filter (ie. number of rows with value 'XXX'), divide by a factor that not change (total number of rows per year).

I have tried in many different ways, but I did not succeed. I cannot believe this is not possible...Maybe some sample code or DDL will help...

I keep thinking cement for some reason...|||Hi Brett, I understand your question since this is quite strange, nevertheless, I will try to explain.

The main reason is that I need to detect the quality of the data (used to join with the dimensions) in the fact table . The formula to do that is getting the rate of rows with the correct information. One way to do it will be just dividing the total number of rows by the number of rows with dimension column equals null.

I can calculate the total number of rows creating a calculated member like "count([mydimension].AllMembers)", but this number of rows is true only for the dimension "mydimension". Other possibility is using a column with value 1 for every row, and doing a sum([mydimension].AllMembers,mycolumn1), but this is true only when I filter for mydimension.

So, the real question is, how can I get the real total number of rows, valid for any dimension?.

I hope this helps...
thx in advance
Oscar

Create 3 PageFiles (8 GB of RAM)

I have 8 GB of ram on my server, Microsoft recommends
creating Pagefiles for the server at 1.5 * (RAM on server).
I would like to create 3 Pagefiles on my server each at
4095 MB in size on the drives m, n, and o.
Please let me the procedures to complete this task.
Thank You,
StevenSteven,
Refer to this url.
http://www.petri.co.il/pagefile_optimization.htm
--
- Vishal
"Steven" <anonymous@.discussions.microsoft.com> wrote in message
news:03b401c3ae2a$5f6638a0$a501280a@.phx.gbl...
> I have 8 GB of ram on my server, Microsoft recommends
> creating Pagefiles for the server at 1.5 * (RAM on server).
> I would like to create 3 Pagefiles on my server each at
> 4095 MB in size on the drives m, n, and o.
> Please let me the procedures to complete this task.
> Thank You,
> Steven
>sql

Create "Object Type" in sql server

I'm facing problem while creating object type in sql server.
ex:sql query
"create type student as object ( name varchar2 ( 12) , no number ( 5))" is working fine in Oracle where 'student' can be used in any table as datatype, but its not working in sqlserver. Please can you help me how to create object type in sql server.

Thanks and Regrads,

Suzan:

To create a user defined datatype you need to use the sp_addtype procedure. You might want to look this up in books online. There is an example of use on this page:

http://msdn2.microsoft.com/en-us/library/aa259606(SQL.80).aspx

|||

Thanks , but what i am looking for is different.

i am looking for somting in sql server similar to Oracle OBJECT type

CREATE TYPE Pet_t AS OBJECT (
tag_no INTEGER,
name VARCHAR2(60),
MEMBER FUNCTION set_tag_no (new_tag_no IN INTEGER)
RETURN Pet_t
);

is there a way in sql server to do so....

Thanks

|||

If you're using SQL Server 2005 then you can create CLR user-defined types.

Check out this BOL link for more info:

http://msdn2.microsoft.com/en-us/library/ms131120.aspx

Chris

|||

i have found that this is possible using SQL server 2005, but i need a way to do it in sql server 200.

is it possible to do so?

suzan

|||

Nope.. It is not posible in SQL Server 2000.

SQL SERVER 2000 uses UDT as synonyms for pre-defined types (example - PhoneNumber := Varchar(24) ,etc).

The possible alternate solution is storing your data as BINARY. The issue here is you wont retrive your data on SQL statements (not visible on your QA), you have to depend on your UI/BL to retrive and view the data.

Other simple solution may be storing the data as XML.

|||Thanks ...|||Thanks , but do u have a link to where i can find how to store the data as xml

CR11 - How do I choose a NULL value (or ALL RECORDS) in a dynamic parameter?

We are using CR11 and our own OLE DB provider that, in turn, uses our SQL Server database.

We are creating dynamic parameters that allow the user to pick from all the distinct values that a field can have. All but NULL, and we need to allow the user to pick NULL if there's any in the data.

In reality, we just need to find a way to let the user pick ALL values. However, if we create a dynamic parameter that doesn't accept multiple values, the user is forced to pick one of the available values or, if he doesn't, then CR will say the value is not valid when you hit OK to the parameter selections. If instead we create a parameter that accepts multiple values instead, the user has the option to pick all the available values, which at first seems to solve the problem, but these wouldn't include NULLs and those records would be filtered out in the results.

Thank you in advance.Anybody?|||You can try using two separate parameters in your Crystal Report (http://www.shelko.com). One can be a choice between all or specific and the second could be your list of specifics. Then base your selection criteria on the combination of these two parameters.

CR 10 creating charts

I am using Visual Studio 2005 VB.NET and Crystal Reports 10. I need to create a line graph on a crystal report by sending it a dataset from the vb.net page. I am new to this. Does anyone have any example code or know of any websites that demonstrate how to do this?Please see here (http://www.dev-archive.com/forum/showthread.php?t=409951)

Couple of SQL Agent questions

Hi all,
I've got a customer that is creating a LOCAL PACKAGE/SQL AGENT Job to run a
TextImport.exe program on the server, which loads a table and creates a log
file.
They want to be able to print the log file - to a network printer, as the
final step. I do not see PRINT type tasks in the design window. Any
suggestions?
Also, what they have so far runs when they EXECUTE the package from LOCAL
PACKAGES. But when they try to START JOB from SQL AGENT it fails and they
get a message for step 1 saying:
"The step is improperly defined (it needs a valid owner and/or command) and
so could not be run. The step failed."
Thanks.Here is a suggestion.
Use the command task executing print.exe /d:lpt1 c:\filename.log
Most of the time when executing packages through Agent this is a security
issue.
Tushar
"Steve Z" <szlamany@.antarescomputing_no_spam.com> wrote in message
news:eNuHobsLEHA.2440@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I've got a customer that is creating a LOCAL PACKAGE/SQL AGENT Job to run
> a
> TextImport.exe program on the server, which loads a table and creates a
> log
> file.
> They want to be able to print the log file - to a network printer, as the
> final step. I do not see PRINT type tasks in the design window. Any
> suggestions?
> Also, what they have so far runs when they EXECUTE the package from LOCAL
> PACKAGES. But when they try to START JOB from SQL AGENT it fails and they
> get a message for step 1 saying:
> "The step is improperly defined (it needs a valid owner and/or command)
> and
> so could not be run. The step failed."
> Thanks.
>

Couple of SQL Agent questions

Hi all,
I've got a customer that is creating a LOCAL PACKAGE/SQL AGENT Job to run a
TextImport.exe program on the server, which loads a table and creates a log
file.
They want to be able to print the log file - to a network printer, as the
final step. I do not see PRINT type tasks in the design window. Any
suggestions?
Also, what they have so far runs when they EXECUTE the package from LOCAL
PACKAGES. But when they try to START JOB from SQL AGENT it fails and they
get a message for step 1 saying:
"The step is improperly defined (it needs a valid owner and/or command) and
so could not be run. The step failed."
Thanks.Here is a suggestion.
Use the command task executing print.exe /d:lpt1 c:\filename.log
Most of the time when executing packages through Agent this is a security
issue.
Tushar
"Steve Z" <szlamany@.antarescomputing_no_spam.com> wrote in message
news:eNuHobsLEHA.2440@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I've got a customer that is creating a LOCAL PACKAGE/SQL AGENT Job to run
> a
> TextImport.exe program on the server, which loads a table and creates a
> log
> file.
> They want to be able to print the log file - to a network printer, as the
> final step. I do not see PRINT type tasks in the design window. Any
> suggestions?
> Also, what they have so far runs when they EXECUTE the package from LOCAL
> PACKAGES. But when they try to START JOB from SQL AGENT it fails and they
> get a message for step 1 saying:
> "The step is improperly defined (it needs a valid owner and/or command)
> and
> so could not be run. The step failed."
> Thanks.
>

Couple of SQL Agent questions

Hi all,
I've got a customer that is creating a LOCAL PACKAGE/SQL AGENT Job to run a
TextImport.exe program on the server, which loads a table and creates a log
file.
They want to be able to print the log file - to a network printer, as the
final step. I do not see PRINT type tasks in the design window. Any
suggestions?
Also, what they have so far runs when they EXECUTE the package from LOCAL
PACKAGES. But when they try to START JOB from SQL AGENT it fails and they
get a message for step 1 saying:
"The step is improperly defined (it needs a valid owner and/or command) and
so could not be run. The step failed."
Thanks.
Here is a suggestion.
Use the command task executing print.exe /d:lpt1 c:\filename.log
Most of the time when executing packages through Agent this is a security
issue.
Tushar
"Steve Z" <szlamany@.antarescomputing_no_spam.com> wrote in message
news:eNuHobsLEHA.2440@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I've got a customer that is creating a LOCAL PACKAGE/SQL AGENT Job to run
> a
> TextImport.exe program on the server, which loads a table and creates a
> log
> file.
> They want to be able to print the log file - to a network printer, as the
> final step. I do not see PRINT type tasks in the design window. Any
> suggestions?
> Also, what they have so far runs when they EXECUTE the package from LOCAL
> PACKAGES. But when they try to START JOB from SQL AGENT it fails and they
> get a message for step 1 saying:
> "The step is improperly defined (it needs a valid owner and/or command)
> and
> so could not be run. The step failed."
> Thanks.
>

Counting unique entries in a SQL Statement

I have a complicated problem, and I'm new to SQL so any help would be greatly appreciated.

I am creating an export file (fixed width) that contains a breakdown of items on an invoice, and each "export file" can contain many invoices. The problem is that I need to apply an incremental "invoice" count on each line. This isn't as simple as doing a running sum of "1" on each record, because the first 5 rows may all be on the same invoice, and all rows need to be identified as being associated with "invoice 1". The next invoice will be known as "invoice 2" and again may contain many rows, all requiring "invoice 2".

Does this make sense?

EG.: I am shipping products, and the breakdown is: Vessel, Voyage, Invoice No, Product, Mark.....

SAGMIR 025 001 HEM/FIR HLF550...

SAGMIR 025 001 HEM/FIR KILN-D HLF505...

SAGMIR 025 002 HEM/FIR HLF660....

The SQL statement that produces the above is a Select query with a grouping on VES/VOY/BL_ID/PRO/MARK where the "BL_ID" indicates they are on the same invoice, but is not the incremental number I require. Complicated, i know...

Thanks in advance for anyone who can help.....if this explanation isn't clear please tell me!

Michael

Yes, this is not clear to me yet. Could you explain this a bit in detail ? There is a function in SQL Server which can produce a rank based on several columns. The example for procuding such number would be to get new running number for Order Details per order number. Is is this what you are trying to do ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Your best bet is probably a temporary table... and then join back to it.something like the following :

create table #tempinv
(
invid int identity(1,1),
bl_id int not null
)

insert into #tempinv
(
bl_id
)
select distinct BL_ID from invoices
order by BL_ID

select i.VES, i.VOY, t.invid, i.PRO, i.MARK
from invoices i
inner join #tempinv t on t.bl_id = i.BL_ID
order by i.BL_ID

drop table #tempinv

I think this is what you want....