Help for Fine Tune Store procedure to increase its performance

I want to change following store procedure, it takes around 5 minutes to
execute & I calls for each vessel / voyage combination... , Could any one
help me to fine tune this store procedure.
--
CREATE PROCEDURE get_ContainerListinExpBL
@.VesselCode varchar(10) ,
@.VoyageCode varchar(10),
@.LinerCode varchar(10) ,
@.BookingList varchar(1000) = null ,
@.BLNumber varchar(20) = null
AS
select distinct t.ContainerNo,
T.ContainerType,
(select BookingNote
from trnActivity TA
inner join trnReleasenote RA on RA.ReleaseNoteCode = TA.DO and RA.Liner = TA.LinerCode
where TA.ContainerNo = T.ContainerNo
and TA.LinerCode = t.LinerCode
and isnull(TA.VesselCode,'') = isnull(t.ImpVesselcode ,'')
and isnull(TA.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
and DO is not Null
and TA.ActivityDate = (select Max(activityDate ) from
trnActivity TB
where TB.ContainerNo = t.ContainerNo
and TB.LinerCode = t.LinerCode
and isnull(TB.VesselCode,'') = isnull(t.ImpVesselCode , '')
and isnull(TB.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
and TB.DO is not null)) BookingNote ,
t.ImpVesselCode ,
t.ImpVoyageCode ,
t.ReleaseRefNo
from trnExpTracking t
--inner join mstISO O on O.ContainerType = T.ContainerType
where t.ExpVesselCode = @.VesselCode
and t.ExpVoyagecode = @.VoyageCode
and t.LinerCode = @.LinerCode
union
select
c.ContainerNo,
c.ContainerType,
c.BookingNote,
c.ImpVesselCode ,
c.ImpVoyageCode ,
c.ReleaseRefNo
from trnExpContainerDetail c
where C.LinerCode = @.LinerCode
and C.VesselCode = @.VesselCode
and C.VoyageCode = @.VoyageCode
and C.BLnumber = @.BLnumber
GO
-- --
Thanks in advancePlease also post DDL (CREATE TABLE statements, including constraints and
indexes). The underlying schema can affect query performance significantly.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Nitu" <Nitu@.discussions.microsoft.com> wrote in message
news:05229BAA-ABA5-4C85-A298-616D82F629D3@.microsoft.com...
>I want to change following store procedure, it takes around 5 minutes to
> execute & I calls for each vessel / voyage combination... , Could any one
> help me to fine tune this store procedure.
> --
> CREATE PROCEDURE get_ContainerListinExpBL
> @.VesselCode varchar(10) ,
> @.VoyageCode varchar(10),
> @.LinerCode varchar(10) ,
> @.BookingList varchar(1000) = null ,
> @.BLNumber varchar(20) = null
> AS
> select distinct t.ContainerNo,
> T.ContainerType,
> (select BookingNote
> from trnActivity TA
> inner join trnReleasenote RA on RA.ReleaseNoteCode = TA.DO and RA.Liner => TA.LinerCode
> where TA.ContainerNo = T.ContainerNo
> and TA.LinerCode = t.LinerCode
> and isnull(TA.VesselCode,'') = isnull(t.ImpVesselcode ,'')
> and isnull(TA.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and DO is not Null
> and TA.ActivityDate => (select Max(activityDate ) from
> trnActivity TB
> where TB.ContainerNo = t.ContainerNo
> and TB.LinerCode = t.LinerCode
> and isnull(TB.VesselCode,'') = isnull(t.ImpVesselCode , '')
> and isnull(TB.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and TB.DO is not null)) BookingNote ,
> t.ImpVesselCode ,
> t.ImpVoyageCode ,
> t.ReleaseRefNo
> from trnExpTracking t
> --inner join mstISO O on O.ContainerType = T.ContainerType
> where t.ExpVesselCode = @.VesselCode
> and t.ExpVoyagecode = @.VoyageCode
> and t.LinerCode = @.LinerCode
> union
> select
> c.ContainerNo,
> c.ContainerType,
> c.BookingNote,
> c.ImpVesselCode ,
> c.ImpVoyageCode ,
> c.ReleaseRefNo
> from trnExpContainerDetail c
> where C.LinerCode = @.LinerCode
> and C.VesselCode = @.VesselCode
> and C.VoyageCode = @.VoyageCode
> and C.BLnumber = @.BLnumber
> GO
>
> -- --
> Thanks in advance|||Nitu,
You should post the DDL, including all indexes, that is key. Also, ask
yourself if you really need to handle NULL values in VesselCode and/or
VoyageCode, because the use of ISNULL() will disallow the use of an
index for those columns.
Gert-Jan
Nitu wrote:
> I want to change following store procedure, it takes around 5 minutes to
> execute & I calls for each vessel / voyage combination... , Could any one
> help me to fine tune this store procedure.
> --
> CREATE PROCEDURE get_ContainerListinExpBL
> @.VesselCode varchar(10) ,
> @.VoyageCode varchar(10),
> @.LinerCode varchar(10) ,
> @.BookingList varchar(1000) = null ,
> @.BLNumber varchar(20) = null
> AS
> select distinct t.ContainerNo,
> T.ContainerType,
> (select BookingNote
> from trnActivity TA
> inner join trnReleasenote RA on RA.ReleaseNoteCode = TA.DO and RA.Liner => TA.LinerCode
> where TA.ContainerNo = T.ContainerNo
> and TA.LinerCode = t.LinerCode
> and isnull(TA.VesselCode,'') = isnull(t.ImpVesselcode ,'')
> and isnull(TA.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and DO is not Null
> and TA.ActivityDate => (select Max(activityDate ) from
> trnActivity TB
> where TB.ContainerNo = t.ContainerNo
> and TB.LinerCode = t.LinerCode
> and isnull(TB.VesselCode,'') = isnull(t.ImpVesselCode , '')
> and isnull(TB.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and TB.DO is not null)) BookingNote ,
> t.ImpVesselCode ,
> t.ImpVoyageCode ,
> t.ReleaseRefNo
> from trnExpTracking t
> --inner join mstISO O on O.ContainerType = T.ContainerType
> where t.ExpVesselCode = @.VesselCode
> and t.ExpVoyagecode = @.VoyageCode
> and t.LinerCode = @.LinerCode
> union
> select
> c.ContainerNo,
> c.ContainerType,
> c.BookingNote,
> c.ImpVesselCode ,
> c.ImpVoyageCode ,
> c.ReleaseRefNo
> from trnExpContainerDetail c
> where C.LinerCode = @.LinerCode
> and C.VesselCode = @.VesselCode
> and C.VoyageCode = @.VoyageCode
> and C.BLnumber = @.BLnumber
> GO
> -- --
> Thanks in advance|||On Jul 28, 2:14 pm, Nitu <N...@.discussions.microsoft.com> wrote:
> I want to change following store procedure, it takes around 5 minutes to
> execute & I calls for each vessel / voyage combination... , Could any one
> help me to fine tune this store procedure.
> --
> CREATE PROCEDURE get_ContainerListinExpBL
> @.VesselCode varchar(10) ,
> @.VoyageCode varchar(10),
> @.LinerCode varchar(10) ,
> @.BookingList varchar(1000) = null ,
> @.BLNumber varchar(20) = null
> AS
> select distinct t.ContainerNo,
> T.ContainerType,
> (select BookingNote
> from trnActivity TA
> inner join trnReleasenote RA on RA.ReleaseNoteCode = TA.DO and RA.Liner => TA.LinerCode
> where TA.ContainerNo = T.ContainerNo
> and TA.LinerCode = t.LinerCode
> and isnull(TA.VesselCode,'') = isnull(t.ImpVesselcode ,'')
> and isnull(TA.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and DO is not Null
> and TA.ActivityDate => (select Max(activityDate ) from
> trnActivity TB
> where TB.ContainerNo = t.ContainerNo
> and TB.LinerCode = t.LinerCode
> and isnull(TB.VesselCode,'') = isnull(t.ImpVesselCode , '')
> and isnull(TB.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and TB.DO is not null)) BookingNote ,
> t.ImpVesselCode ,
> t.ImpVoyageCode ,
> t.ReleaseRefNo
> from trnExpTracking t
> --inner join mstISO O on O.ContainerType = T.ContainerType
> where t.ExpVesselCode = @.VesselCode
> and t.ExpVoyagecode = @.VoyageCode
> and t.LinerCode = @.LinerCode
> union
> select
> c.ContainerNo,
> c.ContainerType,
> c.BookingNote,
> c.ImpVesselCode ,
> c.ImpVoyageCode ,
> c.ReleaseRefNo
> from trnExpContainerDetail c
> where C.LinerCode = @.LinerCode
> and C.VesselCode = @.VesselCode
> and C.VoyageCode = @.VoyageCode
> and C.BLnumber = @.BLnumber
> GO
> -- --
> Thanks in advance
Hi, try removing the union and use a temp table instead. For example:
select distinct t.ContainerNo etc...
into #my_temp
from trnExpTracking t
--inner join mstISO O on O.ContainerType = T.ContainerType
where t.ExpVesselCode = @.VesselCode
and t.ExpVoyagecode = @.VoyageCode
and t.LinerCode = @.LinerCode
insert #mytemp
select distinct
c.ContainerNo etc...
from trnExpContainerDetail c
where C.LinerCode = @.LinerCode
and C.VesselCode = @.VesselCode
and C.VoyageCode = @.VoyageCode
and C.BLnumber = @.BLnumber
and not exists (select 1 from #my_temp where #my_temp. ContainerNo =c. ContainerNo
and #my_temp. ContainerType = c. ContainerType and #my_temp.
BookingNote = c. BookingNote
and #my_temp. ImpVesselCode = c. ImpVesselCode and #my_temp.
ImpVoyageCode = c. ImpVoyageCode and #my_temp. ReleaseRefNo = c.
ReleaseRefNo )
That should move the timetable to less than 5 minutes to run. I think
that is acceptable. You can fine tune it further by using more #temp
tables for inner subqueries and joining them with #my_temp using
updates statements.|||> That should move the timetable to less than 5 minutes to run. I think
> that is acceptable. You can fine tune it further by using more #temp
> tables for inner subqueries and joining them with #my_temp using
> updates statements.
SB, how can you make these recommendations without knowledge of the
underlying schema? I'm not saying that employing a temp tables won't
improve performance, it's just that I've often seen temp tables used as a
kludge workaround when the real issue is lack of useful indexes and
non-saragable query expressions. IMHO, a better approach is to understand
and address the underlying cause for the poor performance.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"SB" <othellomy@.yahoo.com> wrote in message
news:1185777942.351695.318730@.q75g2000hsh.googlegroups.com...
> On Jul 28, 2:14 pm, Nitu <N...@.discussions.microsoft.com> wrote:
>> I want to change following store procedure, it takes around 5 minutes to
>> execute & I calls for each vessel / voyage combination... , Could any
>> one
>> help me to fine tune this store procedure.
>> --
>> CREATE PROCEDURE get_ContainerListinExpBL
>> @.VesselCode varchar(10) ,
>> @.VoyageCode varchar(10),
>> @.LinerCode varchar(10) ,
>> @.BookingList varchar(1000) = null ,
>> @.BLNumber varchar(20) = null
>> AS
>> select distinct t.ContainerNo,
>> T.ContainerType,
>> (select BookingNote
>> from trnActivity TA
>> inner join trnReleasenote RA on
>> RA.ReleaseNoteCode = TA.DO and RA.Liner =>> TA.LinerCode
>> where TA.ContainerNo = T.ContainerNo
>> and TA.LinerCode = t.LinerCode
>> and isnull(TA.VesselCode,'') =>> isnull(t.ImpVesselcode ,'')
>> and isnull(TA.VoyageCode, '') =>> isnull(t.ImpVoyageCode, '')
>> and DO is not Null
>> and TA.ActivityDate =>> (select Max(activityDate ) from
>> trnActivity TB
>> where TB.ContainerNo = t.ContainerNo
>> and TB.LinerCode = t.LinerCode
>> and isnull(TB.VesselCode,'') =>> isnull(t.ImpVesselCode , '')
>> and isnull(TB.VoyageCode, '') =>> isnull(t.ImpVoyageCode, '')
>> and TB.DO is not null)) BookingNote ,
>> t.ImpVesselCode ,
>> t.ImpVoyageCode ,
>> t.ReleaseRefNo
>> from trnExpTracking t
>> --inner join mstISO O on O.ContainerType = T.ContainerType
>> where t.ExpVesselCode = @.VesselCode
>> and t.ExpVoyagecode = @.VoyageCode
>> and t.LinerCode = @.LinerCode
>> union
>> select
>> c.ContainerNo,
>> c.ContainerType,
>> c.BookingNote,
>> c.ImpVesselCode ,
>> c.ImpVoyageCode ,
>> c.ReleaseRefNo
>> from trnExpContainerDetail c
>> where C.LinerCode = @.LinerCode
>> and C.VesselCode = @.VesselCode
>> and C.VoyageCode = @.VoyageCode
>> and C.BLnumber = @.BLnumber
>> GO
>> -- --
>> Thanks in advance
> Hi, try removing the union and use a temp table instead. For example:
> select distinct t.ContainerNo etc...
> into #my_temp
> from trnExpTracking t
> --inner join mstISO O on O.ContainerType = T.ContainerType
> where t.ExpVesselCode = @.VesselCode
> and t.ExpVoyagecode = @.VoyageCode
> and t.LinerCode = @.LinerCode
> insert #mytemp
> select distinct
> c.ContainerNo etc...
> from trnExpContainerDetail c
> where C.LinerCode = @.LinerCode
> and C.VesselCode = @.VesselCode
> and C.VoyageCode = @.VoyageCode
> and C.BLnumber = @.BLnumber
> and not exists (select 1 from #my_temp where #my_temp. ContainerNo => c. ContainerNo
> and #my_temp. ContainerType = c. ContainerType and #my_temp.
> BookingNote = c. BookingNote
> and #my_temp. ImpVesselCode = c. ImpVesselCode and #my_temp.
> ImpVoyageCode = c. ImpVoyageCode and #my_temp. ReleaseRefNo = c.
> ReleaseRefNo )
>
> That should move the timetable to less than 5 minutes to run. I think
> that is acceptable. You can fine tune it further by using more #temp
> tables for inner subqueries and joining them with #my_temp using
> updates statements.
>|||On Jul 30, 6:35 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> > That should move the timetable to less than 5 minutes to run. I think
> > that is acceptable. You can fine tune it further by using more #temp
> > tables for inner subqueries and joining them with #my_temp using
> > updates statements.
> SB, how can you make these recommendations without knowledge of the
> underlying schema? I'm not saying that employing a temp tables won't
> improve performance, it's just that I've often seen temp tables used as a
> kludge workaround when the real issue is lack of useful indexes and
> non-saragable query expressions. IMHO, a better approach is to understand
> and address the underlying cause for the poor performance.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "SB" <othell...@.yahoo.com> wrote in message
> news:1185777942.351695.318730@.q75g2000hsh.googlegroups.com...
>
> > On Jul 28, 2:14 pm, Nitu <N...@.discussions.microsoft.com> wrote:
> >> I want to change following store procedure, it takes around 5 minutes to
> >> execute & I calls for each vessel / voyage combination... , Could any
> >> one
> >> help me to fine tune this store procedure.
> >> --
> >> CREATE PROCEDURE get_ContainerListinExpBL
> >> @.VesselCode varchar(10) ,
> >> @.VoyageCode varchar(10),
> >> @.LinerCode varchar(10) ,
> >> @.BookingList varchar(1000) = null ,
> >> @.BLNumber varchar(20) = null
> >> AS
> >> select distinct t.ContainerNo,
> >> T.ContainerType,
> >> (select BookingNote
> >> from trnActivity TA
> >> inner join trnReleasenote RA on
> >> RA.ReleaseNoteCode = TA.DO and RA.Liner => >> TA.LinerCode
> >> where TA.ContainerNo = T.ContainerNo
> >> and TA.LinerCode = t.LinerCode
> >> and isnull(TA.VesselCode,'') => >> isnull(t.ImpVesselcode ,'')
> >> and isnull(TA.VoyageCode, '') => >> isnull(t.ImpVoyageCode, '')
> >> and DO is not Null
> >> and TA.ActivityDate => >> (select Max(activityDate ) from
> >> trnActivity TB
> >> where TB.ContainerNo = t.ContainerNo
> >> and TB.LinerCode = t.LinerCode
> >> and isnull(TB.VesselCode,'') => >> isnull(t.ImpVesselCode , '')
> >> and isnull(TB.VoyageCode, '') => >> isnull(t.ImpVoyageCode, '')
> >> and TB.DO is not null)) BookingNote ,
> >> t.ImpVesselCode ,
> >> t.ImpVoyageCode ,
> >> t.ReleaseRefNo
> >> from trnExpTracking t
> >> --inner join mstISO O on O.ContainerType = T.ContainerType
> >> where t.ExpVesselCode = @.VesselCode
> >> and t.ExpVoyagecode = @.VoyageCode
> >> and t.LinerCode = @.LinerCode
> >> union
> >> select
> >> c.ContainerNo,
> >> c.ContainerType,
> >> c.BookingNote,
> >> c.ImpVesselCode ,
> >> c.ImpVoyageCode ,
> >> c.ReleaseRefNo
> >> from trnExpContainerDetail c
> >> where C.LinerCode = @.LinerCode
> >> and C.VesselCode = @.VesselCode
> >> and C.VoyageCode = @.VoyageCode
> >> and C.BLnumber = @.BLnumber
> >> GO
> >> -- --
> >> Thanks in advance
> > Hi, try removing the union and use a temp table instead. For example:
> > select distinct t.ContainerNo etc...
> > into #my_temp
> > from trnExpTracking t
> > --inner join mstISO O on O.ContainerType = T.ContainerType
> > where t.ExpVesselCode = @.VesselCode
> > and t.ExpVoyagecode = @.VoyageCode
> > and t.LinerCode = @.LinerCode
> > insert #mytemp
> > select distinct
> > c.ContainerNo etc...
> > from trnExpContainerDetail c
> > where C.LinerCode = @.LinerCode
> > and C.VesselCode = @.VesselCode
> > and C.VoyageCode = @.VoyageCode
> > and C.BLnumber = @.BLnumber
> > and not exists (select 1 from #my_temp where #my_temp. ContainerNo => > c. ContainerNo
> > and #my_temp. ContainerType = c. ContainerType and #my_temp.
> > BookingNote = c. BookingNote
> > and #my_temp. ImpVesselCode = c. ImpVesselCode and #my_temp.
> > ImpVoyageCode = c. ImpVoyageCode and #my_temp. ReleaseRefNo = c.
> > ReleaseRefNo )
> > That should move the timetable to less than 5 minutes to run. I think
> > that is acceptable. You can fine tune it further by using more #temp
> > tables for inner subqueries and joining them with #my_temp using
> > updates statements.- Hide quoted text -
> - Show quoted text -
Dan I don't need schema. If I need one I will ask tl from you. Thanks.|||With all due respect SB, if you want to provide real help to the OP you DO
need schema, indexes and possibly even data distribution information. Your
stab in the dark could well be WORSE performing due the large I/O overhead
associated with temporary tables (and the sproc recompiles associated with
them too). Even worse is that it completely avoids the possibility that a
single appropriate index addition could improve the sproc performance 1-4
orders of magnitude.
We on this forum should strive to give posters the right tools to help them
in the future as well as simply attempting to solve their current problem.
With just a bit more information from the OP we could have a dialog that
provided good mentoring to all while providing the BEST solution to Nitu.
--
TheSQLGuru
President
Indicium Resources, Inc.
"SB" <othellomy@.yahoo.com> wrote in message
news:1185852523.248498.24130@.d55g2000hsg.googlegroups.com...
> On Jul 30, 6:35 pm, "Dan Guzman" <guzma...@.nospam-
> online.sbcglobal.net> wrote:
>> > That should move the timetable to less than 5 minutes to run. I think
>> > that is acceptable. You can fine tune it further by using more #temp
>> > tables for inner subqueries and joining them with #my_temp using
>> > updates statements.
>> SB, how can you make these recommendations without knowledge of the
>> underlying schema? I'm not saying that employing a temp tables won't
>> improve performance, it's just that I've often seen temp tables used as a
>> kludge workaround when the real issue is lack of useful indexes and
>> non-saragable query expressions. IMHO, a better approach is to
>> understand
>> and address the underlying cause for the poor performance.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "SB" <othell...@.yahoo.com> wrote in message
>> news:1185777942.351695.318730@.q75g2000hsh.googlegroups.com...
>>
>> > On Jul 28, 2:14 pm, Nitu <N...@.discussions.microsoft.com> wrote:
>> >> I want to change following store procedure, it takes around 5 minutes
>> >> to
>> >> execute & I calls for each vessel / voyage combination... , Could any
>> >> one
>> >> help me to fine tune this store procedure.
>> >> --
>> >> CREATE PROCEDURE get_ContainerListinExpBL
>> >> @.VesselCode varchar(10) ,
>> >> @.VoyageCode varchar(10),
>> >> @.LinerCode varchar(10) ,
>> >> @.BookingList varchar(1000) = null ,
>> >> @.BLNumber varchar(20) = null
>> >> AS
>> >> select distinct t.ContainerNo,
>> >> T.ContainerType,
>> >> (select BookingNote
>> >> from trnActivity TA
>> >> inner join trnReleasenote RA on
>> >> RA.ReleaseNoteCode = TA.DO and RA.Liner =>> >> TA.LinerCode
>> >> where TA.ContainerNo = T.ContainerNo
>> >> and TA.LinerCode = t.LinerCode
>> >> and isnull(TA.VesselCode,'') =>> >> isnull(t.ImpVesselcode ,'')
>> >> and isnull(TA.VoyageCode, '') =>> >> isnull(t.ImpVoyageCode, '')
>> >> and DO is not Null
>> >> and TA.ActivityDate =>> >> (select Max(activityDate ) from
>> >> trnActivity TB
>> >> where TB.ContainerNo = t.ContainerNo
>> >> and TB.LinerCode = t.LinerCode
>> >> and isnull(TB.VesselCode,'') =>> >> isnull(t.ImpVesselCode , '')
>> >> and isnull(TB.VoyageCode, '') =>> >> isnull(t.ImpVoyageCode, '')
>> >> and TB.DO is not null)) BookingNote ,
>> >> t.ImpVesselCode ,
>> >> t.ImpVoyageCode ,
>> >> t.ReleaseRefNo
>> >> from trnExpTracking t
>> >> --inner join mstISO O on O.ContainerType = T.ContainerType
>> >> where t.ExpVesselCode = @.VesselCode
>> >> and t.ExpVoyagecode = @.VoyageCode
>> >> and t.LinerCode = @.LinerCode
>> >> union
>> >> select
>> >> c.ContainerNo,
>> >> c.ContainerType,
>> >> c.BookingNote,
>> >> c.ImpVesselCode ,
>> >> c.ImpVoyageCode ,
>> >> c.ReleaseRefNo
>> >> from trnExpContainerDetail c
>> >> where C.LinerCode = @.LinerCode
>> >> and C.VesselCode = @.VesselCode
>> >> and C.VoyageCode = @.VoyageCode
>> >> and C.BLnumber = @.BLnumber
>> >> GO
>> >> -- --
>> >> Thanks in advance
>> > Hi, try removing the union and use a temp table instead. For example:
>> > select distinct t.ContainerNo etc...
>> > into #my_temp
>> > from trnExpTracking t
>> > --inner join mstISO O on O.ContainerType = T.ContainerType
>> > where t.ExpVesselCode = @.VesselCode
>> > and t.ExpVoyagecode = @.VoyageCode
>> > and t.LinerCode = @.LinerCode
>> > insert #mytemp
>> > select distinct
>> > c.ContainerNo etc...
>> > from trnExpContainerDetail c
>> > where C.LinerCode = @.LinerCode
>> > and C.VesselCode = @.VesselCode
>> > and C.VoyageCode = @.VoyageCode
>> > and C.BLnumber = @.BLnumber
>> > and not exists (select 1 from #my_temp where #my_temp. ContainerNo =>> > c. ContainerNo
>> > and #my_temp. ContainerType = c. ContainerType and #my_temp.
>> > BookingNote = c. BookingNote
>> > and #my_temp. ImpVesselCode = c. ImpVesselCode and #my_temp.
>> > ImpVoyageCode = c. ImpVoyageCode and #my_temp. ReleaseRefNo = c.
>> > ReleaseRefNo )
>> > That should move the timetable to less than 5 minutes to run. I think
>> > that is acceptable. You can fine tune it further by using more #temp
>> > tables for inner subqueries and joining them with #my_temp using
>> > updates statements.- Hide quoted text -
>> - Show quoted text -
> Dan I don't need schema. If I need one I will ask tl from you. Thanks.
>

Help connecting ole/db linked server to msaccess database in a different machine than sql server

Hi,

I have a msaccess linked server that I use to execute sql 2000 stored procedures from a front end in adp (access data project) format without any problem, if it is used on the same machine where the sql server resides (with any user logged on). In any other machine on the local network where I also need to use it, I get an ole/db error message saying that the microsoft jet database engine can not open the file because it's allready opened exclusivly or because it do not has permissions. I created the linked server with both UNC and normal path with the same result.

Thank's for all the help/clues you can give me.

Hi!

This is usually a permissions issue. The account that the SQL Server runs under needs to have NT permissions to the directory where the MDB is stored.

|||

Thank's for your help Cindy, but the case is that I can't figure out what to do to solve the problem.

I am sorry but I don't know exactly what do you mean by "the account that SQL Server runs".

The users that are running the sp that reports the error all have rights in the local network directory where the mdb is stored.

Could you please be kind enough to continue helping me on this?

|||Running SQL Server Service needs a service account to run with. The account can be determined using e.g. the Service Control manager. The access for file access is impersonated using the service account which is running SQL Server. If this has no access to the UNC share (e.g. System Account) you won′t be able to access the file / establish a link to the "server" / Access database. In addition to the explanation you should not use a mapped drive letter always use UNC paths working with SQL Server. it cannot be guranteed that the account which is running the process has also the mapped drived letter assigned to his profile.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

The service account SQL Server is using is the network login account of the users.
All of them have access to the network drive that is referenced in the linked server by the UNC path.

This is the code I used to set the linked server:

USE master
GO
EXEC sp_addlinkedserver
@.server = 'CRED85', -- Name of the linked Server
@.provider = 'Microsoft.Jet.OLEDB.4.0', -- Access Provider
@.srvproduct = 'dbaccess', -- may be anything
@.datasrc = '\\Gcxncliflsv301h\Aplic\AplicDep\DAI\APLIC\Produtos\Cred85.mdb' -- UNC path + Access db name
GO

and after that:

exec sp_addlinkedsrvlogin 'dbaccess', false, 'GRUPOCGD\c008645', 'Admin', NULL

So, my question remains:
- Why does it work only when the front end is executed in the machine where resides the server (with any user)
and NOT in any other machine? What's wrong?

Help connecting ole/db linked server to msaccess database in a different machine than sql server

Hi,

I have a msaccess linked server that I use to execute sql 2000 stored procedures from a front end in adp (access data project) format without any problem, if it is used on the same machine where the sql server resides (with any user logged on). In any other machine on the local network where I also need to use it, I get an ole/db error message saying that the microsoft jet database engine can not open the file because it's allready opened exclusivly or because it do not has permissions. I created the linked server with both UNC and normal path with the same result.

Thank's for all the help/clues you can give me.

Hi!

This is usually a permissions issue. The account that the SQL Server runs under needs to have NT permissions to the directory where the MDB is stored.

|||

Thank's for your help Cindy, but the case is that I can't figure out what to do to solve the problem.

I am sorry but I don't know exactly what do you mean by "the account that SQL Server runs".

The users that are running the sp that reports the error all have rights in the local network directory where the mdb is stored.

Could you please be kind enough to continue helping me on this?

|||Running SQL Server Service needs a service account to run with. The account can be determined using e.g. the Service Control manager. The access for file access is impersonated using the service account which is running SQL Server. If this has no access to the UNC share (e.g. System Account) you won′t be able to access the file / establish a link to the "server" / Access database. In addition to the explanation you should not use a mapped drive letter always use UNC paths working with SQL Server. it cannot be guranteed that the account which is running the process has also the mapped drived letter assigned to his profile.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

The service account SQL Server is using is the network login account of the users.
All of them have access to the network drive that is referenced in the linked server by the UNC path.

This is the code I used to set the linked server:

USE master
GO
EXEC sp_addlinkedserver
@.server = 'CRED85', -- Name of the linked Server
@.provider = 'Microsoft.Jet.OLEDB.4.0', -- Access Provider
@.srvproduct = 'dbaccess', -- may be anything
@.datasrc = '\\Gcxncliflsv301h\Aplic\AplicDep\DAI\APLIC\Produtos\Cred85.mdb' -- UNC path + Access db name
GO

and after that:

exec sp_addlinkedsrvlogin 'dbaccess', false, 'GRUPOCGD\c008645', 'Admin', NULL

So, my question remains:
- Why does it work only when the front end is executed in the machine where resides the server (with any user)
and NOT in any other machine? What's wrong?

help about sql server job

I created a stored procedure.When i execute the sp in Query Analyzer or in
dts packages, it worked well.But when i scheduled it as job,the job
failed.There are 51 jobs at the server totally.
I also scheduled the sp as job on another server,this time it worked well
too.
Could anyone tell me the reason?Should I set any configurations at the
server?
Below is the sp script,I used linked server in this sp:
CREATE PROCEDURE sp1 AS
declare @.last_upd_tm datetime
set @.last_upd_tm=(select top 1 rspn_upd_tm from TBL_eCardGEMSRpt_load_tm
order by rspn_upd_tm desc)
delete trspn from TBL_eCardGEMS_Response trspn join
linkedserver.eCardGEMsRpt.dbo.Response erspn
on trspn.ResponseID=erspn.ResponseID
where erspn.LastUpdateDate>@.last_upd_tm
insert into TBL_eCardGEMS_Response
select * from linkedserver.eCardGEMsRpt.dbo.Response erspn
where erspn.LastUpdateDate>@.last_upd_tm
delete tkt from TBL_eCardGEMS_Ticket tkt join
linkedserver.eCardGEMsRpt.dbo.Response erspn
on tkt.TicketID=erspn.TicketID
where erspn.LastUpdateDate>@.last_upd_tm
insert into TBL_eCardGEMS_Ticket
select distinct tkt.* from linkedserver.eCardGEMsRpt.dbo.Response erspn
join
FMSQLPRD002.eCardGEMsRpt.dbo.Ticket tkt on
erspn.TicketID=tkt.TicketID
where erspn.LastUpdateDate>@.last_upd_tm
insert into TBL_eCardGEMSRpt_load_tm
select max(lastupdatedate) from linkedserver.eCardGEMsRpt.dbo.Response
GOAaron,
Verify if the database that the job is calling is the correct Database. By
default the database selected is master.
[ ]'s
PEDRO HENRIQUE NUNES - Brasil
"Aaron Huang" <Aapursueron@.hotmail.com> wrote in message
news:bgsh1m$lgj$1@.news01.intel.com...
> I created a stored procedure.When i execute the sp in Query Analyzer or in
> dts packages, it worked well.But when i scheduled it as job,the job
> failed.There are 51 jobs at the server totally.
> I also scheduled the sp as job on another server,this time it worked well
> too.
> Could anyone tell me the reason?Should I set any configurations at the
> server?
> Below is the sp script,I used linked server in this sp:
> CREATE PROCEDURE sp1 AS
> declare @.last_upd_tm datetime
> set @.last_upd_tm=(select top 1 rspn_upd_tm from TBL_eCardGEMSRpt_load_tm
> order by rspn_upd_tm desc)
> delete trspn from TBL_eCardGEMS_Response trspn join
> linkedserver.eCardGEMsRpt.dbo.Response erspn
> on trspn.ResponseID=erspn.ResponseID
> where erspn.LastUpdateDate>@.last_upd_tm
> insert into TBL_eCardGEMS_Response
> select * from linkedserver.eCardGEMsRpt.dbo.Response erspn
> where erspn.LastUpdateDate>@.last_upd_tm
> delete tkt from TBL_eCardGEMS_Ticket tkt join
> linkedserver.eCardGEMsRpt.dbo.Response erspn
> on tkt.TicketID=erspn.TicketID
> where erspn.LastUpdateDate>@.last_upd_tm
> insert into TBL_eCardGEMS_Ticket
> select distinct tkt.* from linkedserver.eCardGEMsRpt.dbo.Response erspn
> join
> FMSQLPRD002.eCardGEMsRpt.dbo.Ticket tkt on
> erspn.TicketID=tkt.TicketID
> where erspn.LastUpdateDate>@.last_upd_tm
> insert into TBL_eCardGEMSRpt_load_tm
> select max(lastupdatedate) from linkedserver.eCardGEMsRpt.dbo.Response
> GO

Help - sp_help_revlogin does not work in 2005

I compiled the sp_help_revlogin code on my 2005 server.
However, when I try to execute it I get:
Msg 208, Level 16, State 1, Procedure sp_help_revlogin, Line 12
Invalid object name 'master..sysxlogins'.
This has always worked fine in 2000, but apparently one of the
underlying tables has changed or been renamed.
Has anyone run into this problem and how do you fix it?
If you can't use sp_help_revlogin, how do you migrate your logins
otherwise? I've never done it any other way.
ThanksI have used this update successfully:
http://blogs.msdn.com/lcris/archive.../03/567680.aspx
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks for the link. However, when I run the output, for each CREATE
LOGIN stmt, I get this error:
Msg 15433, Level 16, State 1, Line 6
Supplied parameter sid is in use.
However, these logins do not yet exist on the server.
?
Paul Ibison wrote:
> I have used this update successfully:
> http://blogs.msdn.com/lcris/archive.../03/567680.aspx
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Sorry to ask the obvious, but are you sure you're using this output on the
destination server
It's just that I find it very strange that these SIDs have been used.
Have a look at the sys.server_principals table to see the names of the
logins that are using your SIDs.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||No problem. As per your suggestion, I look in the principals table, and
lo and behold, all the logins were already there. How they got there
before I ran the sp_rev_login script, I'm not sure. Nor were they
showing up under Logins in the Management Studio console, even when I
refreshed the list. However, by reconnecting, they showed up when I
refresh.
But I'm still not sure how the logins could have already been there
before I ran the sp_help_revlogin script, as this is an entirely new
installation of SQL Server on a brand new server. Any ideas?
Paul Ibison wrote:
> Sorry to ask the obvious, but are you sure you're using this output on the
> destination server
> It's just that I find it very strange that these SIDs have been used.
> Have a look at the sys.server_principals table to see the names of the
> logins that are using your SIDs.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||The create date in the sys.server_principals view might give a bit of a
clue.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Help - sp_help_revlogin does not work in 2005

I compiled the sp_help_revlogin code on my 2005 server.
However, when I try to execute it I get:
Msg 208, Level 16, State 1, Procedure sp_help_revlogin, Line 12
Invalid object name 'master..sysxlogins'.
This has always worked fine in 2000, but apparently one of the
underlying tables has changed or been renamed.
Has anyone run into this problem and how do you fix it?
If you can't use sp_help_revlogin, how do you migrate your logins
otherwise? I've never done it any other way.
Thanks
I have used this update successfully:
http://blogs.msdn.com/lcris/archive/2006/04/03/567680.aspx
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks for the link. However, when I run the output, for each CREATE
LOGIN stmt, I get this error:
Msg 15433, Level 16, State 1, Line 6
Supplied parameter sid is in use.
However, these logins do not yet exist on the server.
?
Paul Ibison wrote:
> I have used this update successfully:
> http://blogs.msdn.com/lcris/archive/2006/04/03/567680.aspx
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Sorry to ask the obvious, but are you sure you're using this output on the
destination server
It's just that I find it very strange that these SIDs have been used.
Have a look at the sys.server_principals table to see the names of the
logins that are using your SIDs.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||No problem. As per your suggestion, I look in the principals table, and
lo and behold, all the logins were already there. How they got there
before I ran the sp_rev_login script, I'm not sure. Nor were they
showing up under Logins in the Management Studio console, even when I
refreshed the list. However, by reconnecting, they showed up when I
refresh.
But I'm still not sure how the logins could have already been there
before I ran the sp_help_revlogin script, as this is an entirely new
installation of SQL Server on a brand new server. Any ideas?
Paul Ibison wrote:
> Sorry to ask the obvious, but are you sure you're using this output on the
> destination server
> It's just that I find it very strange that these SIDs have been used.
> Have a look at the sys.server_principals table to see the names of the
> logins that are using your SIDs.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||The create date in the sys.server_principals view might give a bit of a
clue.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Help - sp_help_revlogin does not work in 2005

I compiled the sp_help_revlogin code on my 2005 server.
However, when I try to execute it I get:
Msg 208, Level 16, State 1, Procedure sp_help_revlogin, Line 12
Invalid object name 'master..sysxlogins'.
This has always worked fine in 2000, but apparently one of the
underlying tables has changed or been renamed.
Has anyone run into this problem and how do you fix it?
If you can't use sp_help_revlogin, how do you migrate your logins
otherwise? I've never done it any other way.
ThanksI have used this update successfully:
http://blogs.msdn.com/lcris/archive/2006/04/03/567680.aspx
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks for the link. However, when I run the output, for each CREATE
LOGIN stmt, I get this error:
Msg 15433, Level 16, State 1, Line 6
Supplied parameter sid is in use.
However, these logins do not yet exist on the server.
?
Paul Ibison wrote:
> I have used this update successfully:
> http://blogs.msdn.com/lcris/archive/2006/04/03/567680.aspx
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Sorry to ask the obvious, but are you sure you're using this output on the
destination server :)
It's just that I find it very strange that these SIDs have been used.
Have a look at the sys.server_principals table to see the names of the
logins that are using your SIDs.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||No problem. As per your suggestion, I look in the principals table, and
lo and behold, all the logins were already there. How they got there
before I ran the sp_rev_login script, I'm not sure. Nor were they
showing up under Logins in the Management Studio console, even when I
refreshed the list. However, by reconnecting, they showed up when I
refresh.
But I'm still not sure how the logins could have already been there
before I ran the sp_help_revlogin script, as this is an entirely new
installation of SQL Server on a brand new server. Any ideas?
Paul Ibison wrote:
> Sorry to ask the obvious, but are you sure you're using this output on the
> destination server :)
> It's just that I find it very strange that these SIDs have been used.
> Have a look at the sys.server_principals table to see the names of the
> logins that are using your SIDs.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||The create date in the sys.server_principals view might give a bit of a
clue.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Help - how to execute an sp using linked server

When I run from Main server which has linked server connection - sp executes but the resultset does not get displayed. When I call the same proc from SSRS - I am getting 'an error occured when retrieving parameters for the query. sp does not exist'

Thanks

you will have a tab called 'Command Type' there you change it to Stored Procedure. Then try to execute also make sure you have declared all the parameters before executing this.

Hope it solves your problem.

Help -- How do I exit a Foreach Loop

I have a foreach loop that is processing files in a directory. One of the tasks is the execute sql task that calls a stored proc. If this stored proc returns a certain value then I need to exit the loop and not process any more files. But I have been unable to figure out how to make this happen.

Any help would be greatly appreciated.

Thanks!

GN

Hi,

I am not sure whether this will work or not. But just try. In the 'Foreach loop' container, select 'Expressions' tab and assign a boolean variable to the propery 'Disable'. Then based on the result, when required, set the value of the variable to 'True'. If it does not work, try this option for 'ForceExectionResult' also.

Let me know if any one of it works.

|||

GN wrote:

I have a foreach loop that is processing files in a directory. One of the tasks is the execute sql task that calls a stored proc. If this stored proc returns a certain value then I need to exit the loop and not process any more files. But I have been unable to figure out how to make this happen.

Any help would be greatly appreciated.

Thanks!

GN

have you considered using the script task instead?|||

Place the workflow in a sequence inside the ForEach Loop.

Place a script task at the beginning of the loop with a precedence constraint going to the sequence.

Place an expression on the precedence constraint to only execute if a variable is true, call the variable, "ExecuteBody". ExecuteBody==true

Set op to expression only

When the loop should terminate, set the ExecuteBody variable to false. The loop will traverse the entire remaining collection, but because there is nothing to execute, it will rip through it extremely fast and terminate.

K

HELP-- execution dts in transaction -- URGENT

please I need to know what is required to properly execute a dts with steps joined to transactions in it.

i know MSDTC must be running on server but it must be running in local machine too?

user running dts must be sysadmin? is it required ?

any other requirements needed?

please write down a full list of required items or a BOL reference to look forMaybe if you tell us what you're trying to do?|||what i want is to know what are the requirements to execute a dts with steps joined to transactions in it. I talk about requirements related to services, user privileges and so on

I want to know this info that applied for a general problem (that is how to execute a transactional dts).

Anyway i will tell you what is my specific problem related to it. I have a DTS who loops over files in a directory processing each file (that is inserting rows in several tables of a sql server database) in a way that all files must be processed or if one is not processed due to an error (logged to a log table) then all previous files must be deprocessed (changes made to database must be 'rollback'ed). Thus transacctions are needed into the DTS for some steps (those who change database) while others mustn't (those who perform tests and just write to log table)

This is so easy to do in a DTS but i have experienced several problems due to transacctions. first, with MSDTC service running in server machine (where SQLServer is running) I cannot execute successfully the dts unless MSDTC also running in my local machine. Also is not possible for my user to execute DTS from dts editor unless my user being sysadmin in SQLServer server machine. So i want to know what is exactly necessary to execute such kind of DTS (transactional DTS) in terms of services, security, user profiles and roles, etc

Help - Can Execute return a value?

Hello
I'm relatively new to SQL Server/T-SQL and find myself stuck with this
problem:
I need to do something like this:
Declare @.someValue NVarchar(100)
Declare @.someFunction Varchar(100)
--
-- Assign value to @.someValue from a Cursor
--
--
-- Assign name of the function to @.someFunction from a Cursor
--
Declare @.ret NVarchar(100)
Execute 'Select @.ret = ' + @.someFunc + '(''' + @.someValue + ''')'
I expect the last Execute statement to leave the return value from the
function in @.ret.
What I get is
Must declare the variable '@.ret'.
I have also tried
Execute sp_ExecuteSQL 'Select @.ret = ' + @.someFunc + '(''' +
@.someValue + ''')'
with the same result.
Any help with making this work or other ways of doing this will be very
much appreciated!
TIA.
Vamsi.Vamsi,
Use sp_executesql.
Declare @.ret NVarchar(100)
declare @.sql nvarchar(4000)
set @.sql = N'Select @.ret = ' + @.someFunc + '(''' + @.someValue + ''')'
exec sp_executesql @.sql, N'@.ret NVarchar(100) output', @.ret output
print @.ret
go
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB|||Splendid!!
I still can't understand that one line of code, but, it worked
perfectly :-)
Thanks very much, AMB.
V.|||tvamsidhar (tvamsidhar@.gmail.com) writes:
> Splendid!!
> I still can't understand that one line of code, but, it worked
> perfectly :-)
For more details on sp_executesql and dynamic SQL in general, see
an article on my web site: http://www.sommarskog.se/dynamic_sql.html.
By the way, if you are new to T-SQL, dynamic SQL is probably not where
you should start.
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|||Thanks Erland, I'll study the article! Its already in my Google
bookmarks :)
As for starting with dynamic SQL, I really don't have a choice :-) I
need to be able to run validations and formatting on type-less text
data that should be "interpreted" to be of datatypes defined in a
metadata repository (SQL Server tables) and satisfying validations
(reg. expressions, TSQL functions/SPs, etc) specified in the same
repository.
Running these on the app. server turned out to be way too inefficient
and cumbursome; hence the dynamic SQL. And although I'm more
experienced with PL/SQL, the powers that be insist on using SQL Server
I thank you for the input.
V.