Showing posts with label dts. Show all posts
Showing posts with label dts. Show all posts

Monday, March 26, 2012

help! scheduled package does nothing...

I am totally lost on this one:
I have a dts package which transforms data from tabula database into sql
server through a propriatary odbc driver.
When executed manually (through 'execute package') all is well, connection
is made to both sides
and data flows nicely.
When I schedule this package to a job it ran but nothing happaned.. Not a
single record
retrieved, nor did I get any error massage. It just vanished after 1 second
into thin air...
Both the sqlserver and the sqlserveragent services are ran by the same user
(a domain administrator) which is also the logged on user on
the sqlserver machine.
Sqlserver version is 2000 with sp installed.
The server's os is windows server 2003 enterprise edition.
Any ideas?
At this stage any suggestions will be greatly appriciated.
TIA
Rea
Rea
Have you checked that SQL Agent is running?
"Rea Peleg" <rea_p@.afek.co.il> wrote in message
news:uw18R6SLEHA.268@.TK2MSFTNGP11.phx.gbl...
> I am totally lost on this one:
> I have a dts package which transforms data from tabula database into sql
> server through a propriatary odbc driver.
> When executed manually (through 'execute package') all is well, connection
> is made to both sides
> and data flows nicely.
> When I schedule this package to a job it ran but nothing happaned.. Not a
> single record
> retrieved, nor did I get any error massage. It just vanished after 1
second
> into thin air...
> Both the sqlserver and the sqlserveragent services are ran by the same
user
> (a domain administrator) which is also the logged on user on
> the sqlserver machine.
> Sqlserver version is 2000 with sp installed.
> The server's os is windows server 2003 enterprise edition.
> Any ideas?
> At this stage any suggestions will be greatly appriciated.
> TIA
> Rea
>

help! scheduled package does nothing...

I am totally lost on this one:
I have a dts package which transforms data from tabula database into sql
server through a propriatary odbc driver.
When executed manually (through 'execute package') all is well, connection
is made to both sides
and data flows nicely.
When I schedule this package to a job it ran but nothing happaned.. Not a
single record
retrieved, nor did I get any error massage. It just vanished after 1 second
into thin air...
Both the sqlserver and the sqlserveragent services are ran by the same user
(a domain administrator) which is also the logged on user on
the sqlserver machine.
Sqlserver version is 2000 with sp installed.
The server's os is windows server 2003 enterprise edition.
Any ideas'
At this stage any suggestions will be greatly appriciated.
TIA
ReaRea
Have you checked that SQL Agent is running?
"Rea Peleg" <rea_p@.afek.co.il> wrote in message
news:uw18R6SLEHA.268@.TK2MSFTNGP11.phx.gbl...
> I am totally lost on this one:
> I have a dts package which transforms data from tabula database into sql
> server through a propriatary odbc driver.
> When executed manually (through 'execute package') all is well, connection
> is made to both sides
> and data flows nicely.
> When I schedule this package to a job it ran but nothing happaned.. Not a
> single record
> retrieved, nor did I get any error massage. It just vanished after 1
second
> into thin air...
> Both the sqlserver and the sqlserveragent services are ran by the same
user
> (a domain administrator) which is also the logged on user on
> the sqlserver machine.
> Sqlserver version is 2000 with sp installed.
> The server's os is windows server 2003 enterprise edition.
> Any ideas'
> At this stage any suggestions will be greatly appriciated.
> TIA
> Rea
>

help! scheduled package does nothing...

I am totally lost on this one:
I have a dts package which transforms data from tabula database into sql
server through a propriatary odbc driver.
When executed manually (through 'execute package') all is well, connection
is made to both sides
and data flows nicely.
When I schedule this package to a job it ran but nothing happaned.. Not a
single record
retrieved, nor did I get any error massage. It just vanished after 1 second
into thin air...
Both the sqlserver and the sqlserveragent services are ran by the same user
(a domain administrator) which is also the logged on user on
the sqlserver machine.
Sqlserver version is 2000 with sp installed.
The server's os is windows server 2003 enterprise edition.
Any ideas'
At this stage any suggestions will be greatly appriciated.
TIA
ReaRea
Have you checked that SQL Agent is running?
"Rea Peleg" <rea_p@.afek.co.il> wrote in message
news:uw18R6SLEHA.268@.TK2MSFTNGP11.phx.gbl...
> I am totally lost on this one:
> I have a dts package which transforms data from tabula database into sql
> server through a propriatary odbc driver.
> When executed manually (through 'execute package') all is well, connection
> is made to both sides
> and data flows nicely.
> When I schedule this package to a job it ran but nothing happaned.. Not a
> single record
> retrieved, nor did I get any error massage. It just vanished after 1
second
> into thin air...
> Both the sqlserver and the sqlserveragent services are ran by the same
user
> (a domain administrator) which is also the logged on user on
> the sqlserver machine.
> Sqlserver version is 2000 with sp installed.
> The server's os is windows server 2003 enterprise edition.
> Any ideas'
> At this stage any suggestions will be greatly appriciated.
> TIA
> Rea
>sql

HELP! Restore DTS, Jobs, Etc.

crap! Any immediate help would be greatly appreciated!
Our sql 2000 database server crashed after installing MS's latest patches
and now we're having to rebuild the OS and all the configurations (we've
tried everything else - this is a last resort). The data for the server is
on a separate logical drive, so we should have all the data (also backed
up). The problem is, we have several DTS packages, Jobs, and linked servers
that I'm uncertain how we can "restore."
Is there any method of getting this information back, when we're going to
have to re-install SQL 2000?
Thanks, Jordan
All this information is in your MSDB database. If it is part of your backup
and recovery plan, you will be OK. You may have noticed this during your
backup and recovery test. You did test your plan, didn't you?
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jordan" <jfritts@.learn.colostate.edu> wrote in message
news:%23B45l$tDFHA.328@.tk2msftngp13.phx.gbl...
> crap! Any immediate help would be greatly appreciated!
> Our sql 2000 database server crashed after installing MS's latest patches
> and now we're having to rebuild the OS and all the configurations (we've
> tried everything else - this is a last resort). The data for the server is
> on a separate logical drive, so we should have all the data (also backed
> up). The problem is, we have several DTS packages, Jobs, and linked
servers
> that I'm uncertain how we can "restore."
> Is there any method of getting this information back, when we're going to
> have to re-install SQL 2000?
> Thanks, Jordan
>
|||Yes - i do backup the MSDB. The issue now is, since this is the production
server, i needed to get things online ASAP. So, i tried the "rebuild master"
utility, but my versions of sql server were off a little (they both had sp3a
on them so I'm not sure why). Anyway, to restore services, I simply
attached all my databases and recreated the 6-7 logins that we use.
So, I don't think I can import the MSDB database from my "crashed" server
can I? Won't it overwrite everything I've just done. I'm thinking I may be
able to build another machine and restore the MSDB database to that one,
then move the files from server to server.
ideas?
thanks, j
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:O6NKzNvDFHA.2156@.TK2MSFTNGP10.phx.gbl...
> All this information is in your MSDB database. If it is part of your
backup[vbcol=seagreen]
> and recovery plan, you will be OK. You may have noticed this during your
> backup and recovery test. You did test your plan, didn't you?
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Jordan" <jfritts@.learn.colostate.edu> wrote in message
> news:%23B45l$tDFHA.328@.tk2msftngp13.phx.gbl...
patches[vbcol=seagreen]
is[vbcol=seagreen]
> servers
to
>
|||Backup your existing msdb. Restore the 'old' one. If you have changed the
SQL instance (server) name, you will have to hack the tables to change the
system name in the job tables. If you are using the same server and
instance name, it should work. Master contains the database and login
information. Msdb contains the backup and restore records and the SQL agent
information along with DTS packages. Worst case, restore the 'new' msdb and
you are no worse off than right now.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jordan" <jfritts@.learn.colostate.edu> wrote in message
news:ObIymlwDFHA.1668@.TK2MSFTNGP10.phx.gbl...
> Yes - i do backup the MSDB. The issue now is, since this is the production
> server, i needed to get things online ASAP. So, i tried the "rebuild
master"
> utility, but my versions of sql server were off a little (they both had
sp3a[vbcol=seagreen]
> on them so I'm not sure why). Anyway, to restore services, I simply
> attached all my databases and recreated the 6-7 logins that we use.
> So, I don't think I can import the MSDB database from my "crashed" server
> can I? Won't it overwrite everything I've just done. I'm thinking I may be
> able to build another machine and restore the MSDB database to that one,
> then move the files from server to server.
> ideas?
> thanks, j
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:O6NKzNvDFHA.2156@.TK2MSFTNGP10.phx.gbl...
> backup
your[vbcol=seagreen]
> patches
(we've[vbcol=seagreen]
server[vbcol=seagreen]
> is
backed
> to
>
|||FYI: You must install MS03-031 after SP3a. I didn't see this mentioned,
but you are correct - the versions must match:
8.00.076 SQL Server 2000 SP3
8.00.818 SQL Server 2000 SP3 w/ Cumulative Patch MS03-031
I'm sure this is much after the fact, but worth mentioning for future
reference.
A McGuire
"Jordan" <jfritts@.learn.colostate.edu> wrote in message
news:ObIymlwDFHA.1668@.TK2MSFTNGP10.phx.gbl...
> Yes - i do backup the MSDB. The issue now is, since this is the production
> server, i needed to get things online ASAP. So, i tried the "rebuild
> master"
> utility, but my versions of sql server were off a little (they both had
> sp3a
> on them so I'm not sure why). Anyway, to restore services, I simply
> attached all my databases and recreated the 6-7 logins that we use.
> So, I don't think I can import the MSDB database from my "crashed" server
> can I? Won't it overwrite everything I've just done. I'm thinking I may be
> able to build another machine and restore the MSDB database to that one,
> then move the files from server to server.
> ideas?
> thanks, j
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:O6NKzNvDFHA.2156@.TK2MSFTNGP10.phx.gbl...
> backup
> patches
> is
> to
>

Friday, March 23, 2012

HELP! Restore DTS, Jobs, Etc.

crap! Any immediate help would be greatly appreciated!
Our sql 2000 database server crashed after installing MS's latest patches
and now we're having to rebuild the OS and all the configurations (we've
tried everything else - this is a last resort). The data for the server is
on a separate logical drive, so we should have all the data (also backed
up). The problem is, we have several DTS packages, Jobs, and linked servers
that I'm uncertain how we can "restore."
Is there any method of getting this information back, when we're going to
have to re-install SQL 2000?
Thanks, JordanAll this information is in your MSDB database. If it is part of your backup
and recovery plan, you will be OK. You may have noticed this during your
backup and recovery test. You did test your plan, didn't you?
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jordan" <jfritts@.learn.colostate.edu> wrote in message
news:%23B45l$tDFHA.328@.tk2msftngp13.phx.gbl...
> crap! Any immediate help would be greatly appreciated!
> Our sql 2000 database server crashed after installing MS's latest patches
> and now we're having to rebuild the OS and all the configurations (we've
> tried everything else - this is a last resort). The data for the server is
> on a separate logical drive, so we should have all the data (also backed
> up). The problem is, we have several DTS packages, Jobs, and linked
servers
> that I'm uncertain how we can "restore."
> Is there any method of getting this information back, when we're going to
> have to re-install SQL 2000?
> Thanks, Jordan
>|||Yes - i do backup the MSDB. The issue now is, since this is the production
server, i needed to get things online ASAP. So, i tried the "rebuild master"
utility, but my versions of sql server were off a little (they both had sp3a
on them so I'm not sure why). Anyway, to restore services, I simply
attached all my databases and recreated the 6-7 logins that we use.
So, I don't think I can import the MSDB database from my "crashed" server
can I? Won't it overwrite everything I've just done. I'm thinking I may be
able to build another machine and restore the MSDB database to that one,
then move the files from server to server.
ideas?
thanks, j
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:O6NKzNvDFHA.2156@.TK2MSFTNGP10.phx.gbl...
> All this information is in your MSDB database. If it is part of your
backup
> and recovery plan, you will be OK. You may have noticed this during your
> backup and recovery test. You did test your plan, didn't you?
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Jordan" <jfritts@.learn.colostate.edu> wrote in message
> news:%23B45l$tDFHA.328@.tk2msftngp13.phx.gbl...
> > crap! Any immediate help would be greatly appreciated!
> >
> > Our sql 2000 database server crashed after installing MS's latest
patches
> > and now we're having to rebuild the OS and all the configurations (we've
> > tried everything else - this is a last resort). The data for the server
is
> > on a separate logical drive, so we should have all the data (also backed
> > up). The problem is, we have several DTS packages, Jobs, and linked
> servers
> > that I'm uncertain how we can "restore."
> >
> > Is there any method of getting this information back, when we're going
to
> > have to re-install SQL 2000?
> >
> > Thanks, Jordan
> >
> >
>|||Backup your existing msdb. Restore the 'old' one. If you have changed the
SQL instance (server) name, you will have to hack the tables to change the
system name in the job tables. If you are using the same server and
instance name, it should work. Master contains the database and login
information. Msdb contains the backup and restore records and the SQL agent
information along with DTS packages. Worst case, restore the 'new' msdb and
you are no worse off than right now.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jordan" <jfritts@.learn.colostate.edu> wrote in message
news:ObIymlwDFHA.1668@.TK2MSFTNGP10.phx.gbl...
> Yes - i do backup the MSDB. The issue now is, since this is the production
> server, i needed to get things online ASAP. So, i tried the "rebuild
master"
> utility, but my versions of sql server were off a little (they both had
sp3a
> on them so I'm not sure why). Anyway, to restore services, I simply
> attached all my databases and recreated the 6-7 logins that we use.
> So, I don't think I can import the MSDB database from my "crashed" server
> can I? Won't it overwrite everything I've just done. I'm thinking I may be
> able to build another machine and restore the MSDB database to that one,
> then move the files from server to server.
> ideas?
> thanks, j
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:O6NKzNvDFHA.2156@.TK2MSFTNGP10.phx.gbl...
> > All this information is in your MSDB database. If it is part of your
> backup
> > and recovery plan, you will be OK. You may have noticed this during
your
> > backup and recovery test. You did test your plan, didn't you?
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> > I support the Professional Association for SQL Server
> > www.sqlpass.org
> >
> > "Jordan" <jfritts@.learn.colostate.edu> wrote in message
> > news:%23B45l$tDFHA.328@.tk2msftngp13.phx.gbl...
> > > crap! Any immediate help would be greatly appreciated!
> > >
> > > Our sql 2000 database server crashed after installing MS's latest
> patches
> > > and now we're having to rebuild the OS and all the configurations
(we've
> > > tried everything else - this is a last resort). The data for the
server
> is
> > > on a separate logical drive, so we should have all the data (also
backed
> > > up). The problem is, we have several DTS packages, Jobs, and linked
> > servers
> > > that I'm uncertain how we can "restore."
> > >
> > > Is there any method of getting this information back, when we're going
> to
> > > have to re-install SQL 2000?
> > >
> > > Thanks, Jordan
> > >
> > >
> >
> >
>|||FYI: You must install MS03-031 after SP3a. I didn't see this mentioned,
but you are correct - the versions must match:
8.00.076 SQL Server 2000 SP3
8.00.818 SQL Server 2000 SP3 w/ Cumulative Patch MS03-031
I'm sure this is much after the fact, but worth mentioning for future
reference.
--
A McGuire
"Jordan" <jfritts@.learn.colostate.edu> wrote in message
news:ObIymlwDFHA.1668@.TK2MSFTNGP10.phx.gbl...
> Yes - i do backup the MSDB. The issue now is, since this is the production
> server, i needed to get things online ASAP. So, i tried the "rebuild
> master"
> utility, but my versions of sql server were off a little (they both had
> sp3a
> on them so I'm not sure why). Anyway, to restore services, I simply
> attached all my databases and recreated the 6-7 logins that we use.
> So, I don't think I can import the MSDB database from my "crashed" server
> can I? Won't it overwrite everything I've just done. I'm thinking I may be
> able to build another machine and restore the MSDB database to that one,
> then move the files from server to server.
> ideas?
> thanks, j
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:O6NKzNvDFHA.2156@.TK2MSFTNGP10.phx.gbl...
>> All this information is in your MSDB database. If it is part of your
> backup
>> and recovery plan, you will be OK. You may have noticed this during your
>> backup and recovery test. You did test your plan, didn't you?
>> --
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> Senior Database Administrator
>> Careerbuilder.com
>> I support the Professional Association for SQL Server
>> www.sqlpass.org
>> "Jordan" <jfritts@.learn.colostate.edu> wrote in message
>> news:%23B45l$tDFHA.328@.tk2msftngp13.phx.gbl...
>> > crap! Any immediate help would be greatly appreciated!
>> >
>> > Our sql 2000 database server crashed after installing MS's latest
> patches
>> > and now we're having to rebuild the OS and all the configurations
>> > (we've
>> > tried everything else - this is a last resort). The data for the server
> is
>> > on a separate logical drive, so we should have all the data (also
>> > backed
>> > up). The problem is, we have several DTS packages, Jobs, and linked
>> servers
>> > that I'm uncertain how we can "restore."
>> >
>> > Is there any method of getting this information back, when we're going
> to
>> > have to re-install SQL 2000?
>> >
>> > Thanks, Jordan
>> >
>> >
>>
>

HELP! Restore DTS, Jobs, Etc.

crap! Any immediate help would be greatly appreciated!
Our sql 2000 database server crashed after installing MS's latest patches
and now we're having to rebuild the OS and all the configurations (we've
tried everything else - this is a last resort). The data for the server is
on a separate logical drive, so we should have all the data (also backed
up). The problem is, we have several DTS packages, Jobs, and linked servers
that I'm uncertain how we can "restore."
Is there any method of getting this information back, when we're going to
have to re-install SQL 2000?
Thanks, JordanAll this information is in your MSDB database. If it is part of your backup
and recovery plan, you will be OK. You may have noticed this during your
backup and recovery test. You did test your plan, didn't you?
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jordan" <jfritts@.learn.colostate.edu> wrote in message
news:%23B45l$tDFHA.328@.tk2msftngp13.phx.gbl...
> crap! Any immediate help would be greatly appreciated!
> Our sql 2000 database server crashed after installing MS's latest patches
> and now we're having to rebuild the OS and all the configurations (we've
> tried everything else - this is a last resort). The data for the server is
> on a separate logical drive, so we should have all the data (also backed
> up). The problem is, we have several DTS packages, Jobs, and linked
servers
> that I'm uncertain how we can "restore."
> Is there any method of getting this information back, when we're going to
> have to re-install SQL 2000?
> Thanks, Jordan
>|||Yes - i do backup the MSDB. The issue now is, since this is the production
server, i needed to get things online ASAP. So, i tried the "rebuild master"
utility, but my versions of sql server were off a little (they both had sp3a
on them so I'm not sure why). Anyway, to restore services, I simply
attached all my databases and recreated the 6-7 logins that we use.
So, I don't think I can import the MSDB database from my "crashed" server
can I? Won't it overwrite everything I've just done. I'm thinking I may be
able to build another machine and restore the MSDB database to that one,
then move the files from server to server.
ideas?
thanks, j
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:O6NKzNvDFHA.2156@.TK2MSFTNGP10.phx.gbl...
> All this information is in your MSDB database. If it is part of your
backup
> and recovery plan, you will be OK. You may have noticed this during your
> backup and recovery test. You did test your plan, didn't you?
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Jordan" <jfritts@.learn.colostate.edu> wrote in message
> news:%23B45l$tDFHA.328@.tk2msftngp13.phx.gbl...
patches[vbcol=seagreen]
is[vbcol=seagreen]
> servers
to[vbcol=seagreen]
>|||Backup your existing msdb. Restore the 'old' one. If you have changed the
SQL instance (server) name, you will have to hack the tables to change the
system name in the job tables. If you are using the same server and
instance name, it should work. Master contains the database and login
information. Msdb contains the backup and restore records and the SQL agent
information along with DTS packages. Worst case, restore the 'new' msdb and
you are no worse off than right now.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jordan" <jfritts@.learn.colostate.edu> wrote in message
news:ObIymlwDFHA.1668@.TK2MSFTNGP10.phx.gbl...
> Yes - i do backup the MSDB. The issue now is, since this is the production
> server, i needed to get things online ASAP. So, i tried the "rebuild
master"
> utility, but my versions of sql server were off a little (they both had
sp3a
> on them so I'm not sure why). Anyway, to restore services, I simply
> attached all my databases and recreated the 6-7 logins that we use.
> So, I don't think I can import the MSDB database from my "crashed" server
> can I? Won't it overwrite everything I've just done. I'm thinking I may be
> able to build another machine and restore the MSDB database to that one,
> then move the files from server to server.
> ideas?
> thanks, j
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:O6NKzNvDFHA.2156@.TK2MSFTNGP10.phx.gbl...
> backup
your[vbcol=seagreen]
> patches
(we've[vbcol=seagreen]
server[vbcol=seagreen]
> is
backed[vbcol=seagreen]
> to
>|||FYI: You must install MS03-031 after SP3a. I didn't see this mentioned,
but you are correct - the versions must match:
8.00.076 SQL Server 2000 SP3
8.00.818 SQL Server 2000 SP3 w/ Cumulative Patch MS03-031
I'm sure this is much after the fact, but worth mentioning for future
reference.
A McGuire
"Jordan" <jfritts@.learn.colostate.edu> wrote in message
news:ObIymlwDFHA.1668@.TK2MSFTNGP10.phx.gbl...
> Yes - i do backup the MSDB. The issue now is, since this is the production
> server, i needed to get things online ASAP. So, i tried the "rebuild
> master"
> utility, but my versions of sql server were off a little (they both had
> sp3a
> on them so I'm not sure why). Anyway, to restore services, I simply
> attached all my databases and recreated the 6-7 logins that we use.
> So, I don't think I can import the MSDB database from my "crashed" server
> can I? Won't it overwrite everything I've just done. I'm thinking I may be
> able to build another machine and restore the MSDB database to that one,
> then move the files from server to server.
> ideas?
> thanks, j
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:O6NKzNvDFHA.2156@.TK2MSFTNGP10.phx.gbl...
> backup
> patches
> is
> to
>

Monday, March 19, 2012

HELP! HOW TO CALL A DTS PACKAGE FROM A STORED PROC

I'm a rookie with MSSQL. I need to run a DTS package to export a result set
to an MX Excel spread sheet. I need to call the DTS from a stored procedure
and pass it three values, depending on the input parameters to the stored
proc.

DTS package is no problem. Pretty easy with the DTS wizard. My problem is
that I can't figure out how to instansiate the DTS package object from a
stored proc and pass the three values as parameters to the DTS package so
they can populate the parameters I created in it.

I found an article related to it, but I'm too much of a rookie to grasp it.
It showed how to do this from a stored procedure:

EXEC @.hr = sp_OASetProperty @.oPKG, 'GlobalVariables("MyGVName").Value,
'MyGVValue'
IF @.hr <> 0
BEGIN
PRINT '*** GlobalVariable Assignment Failed'
EXEC sp_displayoaerrorinfo @.oPKG, @.hr
END

I have three values and tree global variables to populate. Do I need to do
the above 3 times?

How do I instantiate the package object? I've read up some on sp_OACreate,
but I don't get it, yet.

How do I initiate the variable @.oPKG?
It contains the name of the sp_OACreate string, right? How do I address a
DTS package in the sp_OACreate string?

I would really appreciate just writing out the sp_OACreate string and how I
pass values for three existing global variables to a DTS package named
"DTS_1".

I'm under some real pressure to get this done.

Thanks for any help I can get.

GunnyHi

Check out

http://www.databasejournal.com/feat...cle.php/1459181

A good resource for everything DTS are books online and
http://www.sqldts.com

John

"Cox News Server" <cfgunny@.cox.net> wrote in message
news:RJClb.97602$AH4.96936@.lakeread06...
> I'm a rookie with MSSQL. I need to run a DTS package to export a result
set
> to an MX Excel spread sheet. I need to call the DTS from a stored
procedure
> and pass it three values, depending on the input parameters to the stored
> proc.
> DTS package is no problem. Pretty easy with the DTS wizard. My problem is
> that I can't figure out how to instansiate the DTS package object from a
> stored proc and pass the three values as parameters to the DTS package so
> they can populate the parameters I created in it.
> I found an article related to it, but I'm too much of a rookie to grasp
it.
> It showed how to do this from a stored procedure:
> EXEC @.hr = sp_OASetProperty @.oPKG, 'GlobalVariables("MyGVName").Value,
> 'MyGVValue'
> IF @.hr <> 0
> BEGIN
> PRINT '*** GlobalVariable Assignment Failed'
> EXEC sp_displayoaerrorinfo @.oPKG, @.hr
> END
> I have three values and tree global variables to populate. Do I need to do
> the above 3 times?
> How do I instantiate the package object? I've read up some on sp_OACreate,
> but I don't get it, yet.
> How do I initiate the variable @.oPKG?
> It contains the name of the sp_OACreate string, right? How do I address a
> DTS package in the sp_OACreate string?
> I would really appreciate just writing out the sp_OACreate string and how
I
> pass values for three existing global variables to a DTS package named
> "DTS_1".
> I'm under some real pressure to get this done.
> Thanks for any help I can get.
>
> Gunny

Monday, March 12, 2012

Help! Dates and SQL

Hi
I would like to create a SP where it will populate TableA based from TableB.
TableB will be populated on a monthly basis using a DTS and within that I
would like to run the SP to populate TableA.

Can someone here please help me create the sql statements as a starting
point.

TIA!
Bob

TableB (source)
from_date to_date curr_code ex_rate
1/1/2004 1/10/2004 CAD .75000
1/11/2004 1/16/2004 CAD .74321
1/17/2004 2/4/2004 CAD .72222
2/5/2004 2/20/2004 CAD .71111
2/21/2004 2/28/2004 CAD .77888
3/1/2004 3/3/2004 CAD .79002
3/4/2004 3/14/2004 CAD .76803
3/15/2004 3/23/2004 CAD .70022
3/24/2004 4/2/2004 CAD .73365
etc...

TableA (destination):
date curr_code ex_rate
1/2004 CAD 0.738477 calculation:(.75000+
..74321+.72222) / 3
2/2004 CAD 0.737403
(.72222+.71111+.77888) / 3
3/2004 CAD 0.74798
(.79002+.76803+.70022+.73365) / 4
etc.."B" <no_spam@.no_spam.com> wrote in message
news:8bWdnSP-M6T0E_HcRVn-ig@.rcn.net...
> Hi
> I would like to create a SP where it will populate TableA based from
> TableB.
> TableB will be populated on a monthly basis using a DTS and within that I
> would like to run the SP to populate TableA.
> Can someone here please help me create the sql statements as a starting
> point.
> TIA!
> Bob
>
> TableB (source)
> from_date to_date curr_code ex_rate
> 1/1/2004 1/10/2004 CAD .75000
> 1/11/2004 1/16/2004 CAD .74321
> 1/17/2004 2/4/2004 CAD .72222
> 2/5/2004 2/20/2004 CAD .71111
> 2/21/2004 2/28/2004 CAD .77888
> 3/1/2004 3/3/2004 CAD .79002
> 3/4/2004 3/14/2004 CAD .76803
> 3/15/2004 3/23/2004 CAD .70022
> 3/24/2004 4/2/2004 CAD .73365
> etc...
> TableA (destination):
> date curr_code ex_rate
> 1/2004 CAD 0.738477 calculation:(.75000+
> .74321+.72222) / 3
> 2/2004 CAD 0.737403
> (.72222+.71111+.77888) / 3
> 3/2004 CAD 0.74798
> (.79002+.76803+.70022+.73365) / 4
> etc..
>

Here's one possible solution. In future, please post CREATE TABLE and INSERT
statements for your tables and data - other people can then simply cut and
paste into Query Analyzer, and we don't have to guess about data types,
keys, constraints etc.:

http://www.aspfaq.com/etiquette.asp?id=5006

Note that 'date' is a reserved keyword, so you should avoid using it as a
column name - I've used start_of_month instead. See "Reserved Keywords" in
Books Online.

Simon

create table b (
from_date datetime not null,
to_date datetime not null,
curr_code char(3) not null,
ex_rate decimal(6,5) not null,
constraint pk_b primary key (from_date, to_date, curr_code)
)
go
insert into b (from_date, to_date, curr_code, ex_rate)
values ('20040101', '20040110', 'CAD', 0.75000)
insert into b (from_date, to_date, curr_code, ex_rate)
values ('20040111', '20040116', 'CAD', 0.74321)
insert into b (from_date, to_date, curr_code, ex_rate)
values ('20040117', '20040204', 'CAD', 0.72222)
insert into b (from_date, to_date, curr_code, ex_rate)
values ('20040205', '20040220', 'CAD', 0.71111)
insert into b (from_date, to_date, curr_code, ex_rate)
values ('20040221', '20040228', 'CAD', 0.77888)
insert into b (from_date, to_date, curr_code, ex_rate)
values ('20040301', '20040303', 'CAD', 0.79002)
insert into b (from_date, to_date, curr_code, ex_rate)
values ('20040304', '20040314', 'CAD', 0.76803)
insert into b (from_date, to_date, curr_code, ex_rate)
values ('20040315', '20040323', 'CAD', 0.70022)
insert into b (from_date, to_date, curr_code, ex_rate)
values ('20040324', '20040402', 'CAD', 0.73365)
go

select dt.start_of_month, max(b.curr_code) as 'curr_code', avg(b.ex_rate) as
'ex_rate'
from
(
select cast(convert(char(6), from_date, 112) + '01' as datetime) as
'start_of_month', count(*) as 'num'
from b
group by cast(convert(char(6), from_date, 112) + '01' as datetime)
) dt
join b
on dt.start_of_month = cast(convert(char(6), b.from_date, 112) + '01' as
datetime)
or dt.start_of_month = cast(convert(char(6), b.to_date, 112) + '01' as
datetime)
group by dt.start_of_month
go

drop table b
go|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:416d1418$1_1@.news.bluewin.ch...
> "B" <no_spam@.no_spam.com> wrote in message
> news:8bWdnSP-M6T0E_HcRVn-ig@.rcn.net...
>> Hi
>> I would like to create a SP where it will populate TableA based from
>> TableB.
>> TableB will be populated on a monthly basis using a DTS and within that I
>> would like to run the SP to populate TableA.
>>
>> Can someone here please help me create the sql statements as a starting
>> point.
>>
>> TIA!
>> Bob
>>
>>
>> TableB (source)
>> from_date to_date curr_code ex_rate
>> 1/1/2004 1/10/2004 CAD .75000
>> 1/11/2004 1/16/2004 CAD .74321
>> 1/17/2004 2/4/2004 CAD .72222
>> 2/5/2004 2/20/2004 CAD .71111
>> 2/21/2004 2/28/2004 CAD .77888
>> 3/1/2004 3/3/2004 CAD .79002
>> 3/4/2004 3/14/2004 CAD .76803
>> 3/15/2004 3/23/2004 CAD .70022
>> 3/24/2004 4/2/2004 CAD .73365
>> etc...
>>
>> TableA (destination):
>> date curr_code ex_rate
>> 1/2004 CAD 0.738477 calculation:(.75000+
>> .74321+.72222) / 3
>> 2/2004 CAD 0.737403
>> (.72222+.71111+.77888) / 3
>> 3/2004 CAD 0.74798
>> (.79002+.76803+.70022+.73365) / 4
>> etc..
>>
>>
>>
>>
> Here's one possible solution. In future, please post CREATE TABLE and
> INSERT statements for your tables and data - other people can then simply
> cut and paste into Query Analyzer, and we don't have to guess about data
> types, keys, constraints etc.:
> http://www.aspfaq.com/etiquette.asp?id=5006
> Note that 'date' is a reserved keyword, so you should avoid using it as a
> column name - I've used start_of_month instead. See "Reserved Keywords" in
> Books Online.
> Simon
> create table b (
> from_date datetime not null,
> to_date datetime not null,
> curr_code char(3) not null,
> ex_rate decimal(6,5) not null,
> constraint pk_b primary key (from_date, to_date, curr_code)
> )
> go
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040101', '20040110', 'CAD', 0.75000)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040111', '20040116', 'CAD', 0.74321)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040117', '20040204', 'CAD', 0.72222)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040205', '20040220', 'CAD', 0.71111)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040221', '20040228', 'CAD', 0.77888)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040301', '20040303', 'CAD', 0.79002)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040304', '20040314', 'CAD', 0.76803)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040315', '20040323', 'CAD', 0.70022)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040324', '20040402', 'CAD', 0.73365)
> go

<snip
Oops - the query I posted before won't handle multiple currencies correctly.
This version should.

select dt.start_of_month, dt.curr_code, avg(b.ex_rate) as 'ex_rate'
from
(
select cast(convert(char(6), from_date, 112) + '01' as datetime) as
'start_of_month', curr_code, count(*) as 'num'
from b
group by cast(convert(char(6), from_date, 112) + '01' as datetime),
curr_code
) dt
join b
on dt.curr_code = b.curr_code and
(
dt.start_of_month = cast(convert(char(6), b.from_date, 112) + '01' as
datetime)
or dt.start_of_month = cast(convert(char(6), b.to_date, 112) + '01' as
datetime)
)
group by dt.start_of_month, dt.curr_code

Simon|||The solution you recently posted is exactly what I am looking for.

And I am aware that "date" is a reserved word, i quickly sent the original
post out of haste.

Many thanks for your time!
Bob

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:416d1418$1_1@.news.bluewin.ch...
> "B" <no_spam@.no_spam.com> wrote in message
> news:8bWdnSP-M6T0E_HcRVn-ig@.rcn.net...
> > Hi
> > I would like to create a SP where it will populate TableA based from
> > TableB.
> > TableB will be populated on a monthly basis using a DTS and within that
I
> > would like to run the SP to populate TableA.
> > Can someone here please help me create the sql statements as a starting
> > point.
> > TIA!
> > Bob
> > TableB (source)
> > from_date to_date curr_code ex_rate
> > 1/1/2004 1/10/2004 CAD .75000
> > 1/11/2004 1/16/2004 CAD .74321
> > 1/17/2004 2/4/2004 CAD .72222
> > 2/5/2004 2/20/2004 CAD .71111
> > 2/21/2004 2/28/2004 CAD .77888
> > 3/1/2004 3/3/2004 CAD .79002
> > 3/4/2004 3/14/2004 CAD .76803
> > 3/15/2004 3/23/2004 CAD .70022
> > 3/24/2004 4/2/2004 CAD .73365
> > etc...
> > TableA (destination):
> > date curr_code ex_rate
> > 1/2004 CAD 0.738477 calculation:(.75000+
> > .74321+.72222) / 3
> > 2/2004 CAD 0.737403
> > (.72222+.71111+.77888) / 3
> > 3/2004 CAD 0.74798
> > (.79002+.76803+.70022+.73365) / 4
> > etc..
> Here's one possible solution. In future, please post CREATE TABLE and
INSERT
> statements for your tables and data - other people can then simply cut and
> paste into Query Analyzer, and we don't have to guess about data types,
> keys, constraints etc.:
> http://www.aspfaq.com/etiquette.asp?id=5006
> Note that 'date' is a reserved keyword, so you should avoid using it as a
> column name - I've used start_of_month instead. See "Reserved Keywords" in
> Books Online.
> Simon
> create table b (
> from_date datetime not null,
> to_date datetime not null,
> curr_code char(3) not null,
> ex_rate decimal(6,5) not null,
> constraint pk_b primary key (from_date, to_date, curr_code)
> )
> go
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040101', '20040110', 'CAD', 0.75000)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040111', '20040116', 'CAD', 0.74321)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040117', '20040204', 'CAD', 0.72222)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040205', '20040220', 'CAD', 0.71111)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040221', '20040228', 'CAD', 0.77888)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040301', '20040303', 'CAD', 0.79002)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040304', '20040314', 'CAD', 0.76803)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040315', '20040323', 'CAD', 0.70022)
> insert into b (from_date, to_date, curr_code, ex_rate)
> values ('20040324', '20040402', 'CAD', 0.73365)
> go
> select dt.start_of_month, max(b.curr_code) as 'curr_code', avg(b.ex_rate)
as
> 'ex_rate'
> from
> (
> select cast(convert(char(6), from_date, 112) + '01' as datetime) as
> 'start_of_month', count(*) as 'num'
> from b
> group by cast(convert(char(6), from_date, 112) + '01' as datetime)
> ) dt
> join b
> on dt.start_of_month = cast(convert(char(6), b.from_date, 112) + '01' as
> datetime)
> or dt.start_of_month = cast(convert(char(6), b.to_date, 112) + '01' as
> datetime)
> group by dt.start_of_month
> go
> drop table b
> go

Friday, March 9, 2012

Help! Are my DTS Packages gone for good?

Something was wrong with my local SQL Server environment. I was unable to
connect to my own SQL Server running on my own workstation. So I uninstalled
and reinstalled. I knew my database files would be safe and sure enough, I
just reattached them after reinstalling.
But where are my DTS Packages? Are these gone for good, or are these
still in some location from which they can be recovered?
Thanks for your advice,
Joe Geretz
depends. If you save the DTS packages as VB files, you still have a change
to find them in OS directory. Otherwise, they are gone after msdb is
uninstalled.
Don't you have a msdb backup?
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:%23nGKAopwEHA.2172@.TK2MSFTNGP14.phx.gbl...
> Something was wrong with my local SQL Server environment. I was unable to
> connect to my own SQL Server running on my own workstation. So I
> uninstalled and reinstalled. I knew my database files would be safe and
> sure enough, I just reattached them after reinstalling.
> But where are my DTS Packages? Are these gone for good, or are these
> still in some location from which they can be recovered?
> Thanks for your advice,
> Joe Geretz
>
|||do you have msdb backup? any DTS backup?
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:%23nGKAopwEHA.2172@.TK2MSFTNGP14.phx.gbl...
> Something was wrong with my local SQL Server environment. I was unable to
> connect to my own SQL Server running on my own workstation. So I
> uninstalled and reinstalled. I knew my database files would be safe and
> sure enough, I just reattached them after reinstalling.
> But where are my DTS Packages? Are these gone for good, or are these
> still in some location from which they can be recovered?
> Thanks for your advice,
> Joe Geretz
>
|||Point well taken. All source is saved to Visual Source Safe but database
procs and utilities - weak link! I guess I'd always assumed these to be
resident in one particular database or another, but obviously they're not.
How do you do a DTS Backup? We use DTS to backup our databases from one
server to another, but backing up the DTS packages themselves? I didn't know
you could do that.
Thanks,
- Joe Geretz -
"Patrick" <patriarck@.gmail.com> wrote in message
news:uEWLQtpwEHA.1452@.TK2MSFTNGP11.phx.gbl...
> do you have msdb backup? any DTS backup?
>
> "Joseph Geretz" <jgeretz@.nospam.com> wrote in message
> news:%23nGKAopwEHA.2172@.TK2MSFTNGP14.phx.gbl...
>
|||You can save as VBscript or just save as .dts file
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:OcFw21pwEHA.2172@.TK2MSFTNGP14.phx.gbl...
> Point well taken. All source is saved to Visual Source Safe but database
> procs and utilities - weak link! I guess I'd always assumed these to be
> resident in one particular database or another, but obviously they're not.
> How do you do a DTS Backup? We use DTS to backup our databases from one
> server to another, but backing up the DTS packages themselves? I didn't
> know you could do that.
> Thanks,
> - Joe Geretz -
> "Patrick" <patriarck@.gmail.com> wrote in message
> news:uEWLQtpwEHA.1452@.TK2MSFTNGP11.phx.gbl...
>
|||Patrick,
DTS saved as VB cannot be saved back to a server. Not as easy. You would
have to include it into a VB snippet and save it from VB. That would loose
you graphical layout and comments. Regular way would be backing up msdb or
saving DTS as structured files.
Ilya
"Patrick" <patriarck@.gmail.com> wrote in message
news:%23xEVEIrwEHA.1392@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> You can save as VBscript or just save as .dts file
>
> "Joseph Geretz" <jgeretz@.nospam.com> wrote in message
> news:OcFw21pwEHA.2172@.TK2MSFTNGP14.phx.gbl...
not.[vbcol=seagreen]
and[vbcol=seagreen]
these
>
|||Yes, very important, depending to whom you speak... BACK UP YOUR SYSTEM
DATABASES...ALWAYS!
Sincerely,
Anthony Thomas
"Ilya Margolin" wrote:

> Patrick,
> DTS saved as VB cannot be saved back to a server. Not as easy. You would
> have to include it into a VB snippet and save it from VB. That would loose
> you graphical layout and comments. Regular way would be backing up msdb or
> saving DTS as structured files.
> Ilya
> "Patrick" <patriarck@.gmail.com> wrote in message
> news:%23xEVEIrwEHA.1392@.tk2msftngp13.phx.gbl...
> not.
> and
> these
>
>
|||If you didn't save them as files, just restore your backup of msdb.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:%23nGKAopwEHA.2172@.TK2MSFTNGP14.phx.gbl...
> Something was wrong with my local SQL Server environment. I was unable to
> connect to my own SQL Server running on my own workstation. So I uninstalled
> and reinstalled. I knew my database files would be safe and sure enough, I
> just reattached them after reinstalling.
> But where are my DTS Packages? Are these gone for good, or are these
> still in some location from which they can be recovered?
> Thanks for your advice,
> Joe Geretz
>
|||Guys you can get a realy good tool called DTSBackup from SQLDTS.com.
We back up 3000 scripts weekly. Just for peace of mind.
"Tibor Karaszi" wrote:

> If you didn't save them as files, just restore your backup of msdb.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Joseph Geretz" <jgeretz@.nospam.com> wrote in message
> news:%23nGKAopwEHA.2172@.TK2MSFTNGP14.phx.gbl...
>
>

Help! Are my DTS Packages gone for good?

Something was wrong with my local SQL Server environment. I was unable to
connect to my own SQL Server running on my own workstation. So I uninstalled
and reinstalled. I knew my database files would be safe and sure enough, I
just reattached them after reinstalling.
But where are my DTS Packages' Are these gone for good, or are these
still in some location from which they can be recovered?
Thanks for your advice,
Joe Geretzdepends. If you save the DTS packages as VB files, you still have a change
to find them in OS directory. Otherwise, they are gone after msdb is
uninstalled.
Don't you have a msdb backup?
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:%23nGKAopwEHA.2172@.TK2MSFTNGP14.phx.gbl...
> Something was wrong with my local SQL Server environment. I was unable to
> connect to my own SQL Server running on my own workstation. So I
> uninstalled and reinstalled. I knew my database files would be safe and
> sure enough, I just reattached them after reinstalling.
> But where are my DTS Packages' Are these gone for good, or are these
> still in some location from which they can be recovered?
> Thanks for your advice,
> Joe Geretz
>|||do you have msdb backup? any DTS backup?
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:%23nGKAopwEHA.2172@.TK2MSFTNGP14.phx.gbl...
> Something was wrong with my local SQL Server environment. I was unable to
> connect to my own SQL Server running on my own workstation. So I
> uninstalled and reinstalled. I knew my database files would be safe and
> sure enough, I just reattached them after reinstalling.
> But where are my DTS Packages' Are these gone for good, or are these
> still in some location from which they can be recovered?
> Thanks for your advice,
> Joe Geretz
>|||Point well taken. All source is saved to Visual Source Safe but database
procs and utilities - weak link! I guess I'd always assumed these to be
resident in one particular database or another, but obviously they're not.
How do you do a DTS Backup? We use DTS to backup our databases from one
server to another, but backing up the DTS packages themselves? I didn't know
you could do that.
Thanks,
- Joe Geretz -
"Patrick" <patriarck@.gmail.com> wrote in message
news:uEWLQtpwEHA.1452@.TK2MSFTNGP11.phx.gbl...
> do you have msdb backup? any DTS backup?
>
> "Joseph Geretz" <jgeretz@.nospam.com> wrote in message
> news:%23nGKAopwEHA.2172@.TK2MSFTNGP14.phx.gbl...
>|||You can save as VBscript or just save as .dts file
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:OcFw21pwEHA.2172@.TK2MSFTNGP14.phx.gbl...
> Point well taken. All source is saved to Visual Source Safe but database
> procs and utilities - weak link! I guess I'd always assumed these to be
> resident in one particular database or another, but obviously they're not.
> How do you do a DTS Backup? We use DTS to backup our databases from one
> server to another, but backing up the DTS packages themselves? I didn't
> know you could do that.
> Thanks,
> - Joe Geretz -
> "Patrick" <patriarck@.gmail.com> wrote in message
> news:uEWLQtpwEHA.1452@.TK2MSFTNGP11.phx.gbl...
>|||Patrick,
DTS saved as VB cannot be saved back to a server. Not as easy. You would
have to include it into a VB snippet and save it from VB. That would loose
you graphical layout and comments. Regular way would be backing up msdb or
saving DTS as structured files.
Ilya
"Patrick" <patriarck@.gmail.com> wrote in message
news:%23xEVEIrwEHA.1392@.tk2msftngp13.phx.gbl...
> You can save as VBscript or just save as .dts file
>
> "Joseph Geretz" <jgeretz@.nospam.com> wrote in message
> news:OcFw21pwEHA.2172@.TK2MSFTNGP14.phx.gbl...
not.[vbcol=seagreen]
and[vbcol=seagreen]
these[vbcol=seagreen]
>|||Yes, very important, depending to whom you speak... BACK UP YOUR SYSTEM
DATABASES...ALWAYS!
Sincerely,
Anthony Thomas
"Ilya Margolin" wrote:

> Patrick,
> DTS saved as VB cannot be saved back to a server. Not as easy. You would
> have to include it into a VB snippet and save it from VB. That would loose
> you graphical layout and comments. Regular way would be backing up msdb or
> saving DTS as structured files.
> Ilya
> "Patrick" <patriarck@.gmail.com> wrote in message
> news:%23xEVEIrwEHA.1392@.tk2msftngp13.phx.gbl...
> not.
> and
> these
>
>|||If you didn't save them as files, just restore your backup of msdb.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:%23nGKAopwEHA.2172@.TK2MSFTNGP14.phx.gbl...
> Something was wrong with my local SQL Server environment. I was unable to
> connect to my own SQL Server running on my own workstation. So I uninstall
ed
> and reinstalled. I knew my database files would be safe and sure enough, I
> just reattached them after reinstalling.
> But where are my DTS Packages' Are these gone for good, or are these
> still in some location from which they can be recovered?
> Thanks for your advice,
> Joe Geretz
>|||Guys you can get a realy good tool called DTSBackup from SQLDTS.com.
We back up 3000 scripts weekly. Just for peace of mind.
"Tibor Karaszi" wrote:

> If you didn't save them as files, just restore your backup of msdb.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Joseph Geretz" <jgeretz@.nospam.com> wrote in message
> news:%23nGKAopwEHA.2172@.TK2MSFTNGP14.phx.gbl...
>
>

Help! Are my DTS Packages gone for good?

Something was wrong with my local SQL Server environment. I was unable to
connect to my own SQL Server running on my own workstation. So I uninstalled
and reinstalled. I knew my database files would be safe and sure enough, I
just reattached them after reinstalling.
But where are my DTS Packages' Are these gone for good, or are these
still in some location from which they can be recovered?
Thanks for your advice,
Joe Geretzdepends. If you save the DTS packages as VB files, you still have a change
to find them in OS directory. Otherwise, they are gone after msdb is
uninstalled.
Don't you have a msdb backup?
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:%23nGKAopwEHA.2172@.TK2MSFTNGP14.phx.gbl...
> Something was wrong with my local SQL Server environment. I was unable to
> connect to my own SQL Server running on my own workstation. So I
> uninstalled and reinstalled. I knew my database files would be safe and
> sure enough, I just reattached them after reinstalling.
> But where are my DTS Packages' Are these gone for good, or are these
> still in some location from which they can be recovered?
> Thanks for your advice,
> Joe Geretz
>|||do you have msdb backup? any DTS backup?
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:%23nGKAopwEHA.2172@.TK2MSFTNGP14.phx.gbl...
> Something was wrong with my local SQL Server environment. I was unable to
> connect to my own SQL Server running on my own workstation. So I
> uninstalled and reinstalled. I knew my database files would be safe and
> sure enough, I just reattached them after reinstalling.
> But where are my DTS Packages' Are these gone for good, or are these
> still in some location from which they can be recovered?
> Thanks for your advice,
> Joe Geretz
>|||Point well taken. All source is saved to Visual Source Safe but database
procs and utilities - weak link! I guess I'd always assumed these to be
resident in one particular database or another, but obviously they're not.
How do you do a DTS Backup? We use DTS to backup our databases from one
server to another, but backing up the DTS packages themselves? I didn't know
you could do that.
Thanks,
- Joe Geretz -
"Patrick" <patriarck@.gmail.com> wrote in message
news:uEWLQtpwEHA.1452@.TK2MSFTNGP11.phx.gbl...
> do you have msdb backup? any DTS backup?
>
> "Joseph Geretz" <jgeretz@.nospam.com> wrote in message
> news:%23nGKAopwEHA.2172@.TK2MSFTNGP14.phx.gbl...
>> Something was wrong with my local SQL Server environment. I was unable to
>> connect to my own SQL Server running on my own workstation. So I
>> uninstalled and reinstalled. I knew my database files would be safe and
>> sure enough, I just reattached them after reinstalling.
>> But where are my DTS Packages' Are these gone for good, or are these
>> still in some location from which they can be recovered?
>> Thanks for your advice,
>> Joe Geretz
>>
>|||You can save as VBscript or just save as .dts file
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:OcFw21pwEHA.2172@.TK2MSFTNGP14.phx.gbl...
> Point well taken. All source is saved to Visual Source Safe but database
> procs and utilities - weak link! I guess I'd always assumed these to be
> resident in one particular database or another, but obviously they're not.
> How do you do a DTS Backup? We use DTS to backup our databases from one
> server to another, but backing up the DTS packages themselves? I didn't
> know you could do that.
> Thanks,
> - Joe Geretz -
> "Patrick" <patriarck@.gmail.com> wrote in message
> news:uEWLQtpwEHA.1452@.TK2MSFTNGP11.phx.gbl...
>> do you have msdb backup? any DTS backup?
>>
>> "Joseph Geretz" <jgeretz@.nospam.com> wrote in message
>> news:%23nGKAopwEHA.2172@.TK2MSFTNGP14.phx.gbl...
>> Something was wrong with my local SQL Server environment. I was unable
>> to connect to my own SQL Server running on my own workstation. So I
>> uninstalled and reinstalled. I knew my database files would be safe and
>> sure enough, I just reattached them after reinstalling.
>> But where are my DTS Packages' Are these gone for good, or are these
>> still in some location from which they can be recovered?
>> Thanks for your advice,
>> Joe Geretz
>>
>>
>|||Patrick,
DTS saved as VB cannot be saved back to a server. Not as easy. You would
have to include it into a VB snippet and save it from VB. That would loose
you graphical layout and comments. Regular way would be backing up msdb or
saving DTS as structured files.
Ilya
"Patrick" <patriarck@.gmail.com> wrote in message
news:%23xEVEIrwEHA.1392@.tk2msftngp13.phx.gbl...
> You can save as VBscript or just save as .dts file
>
> "Joseph Geretz" <jgeretz@.nospam.com> wrote in message
> news:OcFw21pwEHA.2172@.TK2MSFTNGP14.phx.gbl...
> > Point well taken. All source is saved to Visual Source Safe but database
> > procs and utilities - weak link! I guess I'd always assumed these to be
> > resident in one particular database or another, but obviously they're
not.
> >
> > How do you do a DTS Backup? We use DTS to backup our databases from one
> > server to another, but backing up the DTS packages themselves? I didn't
> > know you could do that.
> >
> > Thanks,
> >
> > - Joe Geretz -
> >
> > "Patrick" <patriarck@.gmail.com> wrote in message
> > news:uEWLQtpwEHA.1452@.TK2MSFTNGP11.phx.gbl...
> >> do you have msdb backup? any DTS backup?
> >>
> >>
> >> "Joseph Geretz" <jgeretz@.nospam.com> wrote in message
> >> news:%23nGKAopwEHA.2172@.TK2MSFTNGP14.phx.gbl...
> >> Something was wrong with my local SQL Server environment. I was unable
> >> to connect to my own SQL Server running on my own workstation. So I
> >> uninstalled and reinstalled. I knew my database files would be safe
and
> >> sure enough, I just reattached them after reinstalling.
> >>
> >> But where are my DTS Packages' Are these gone for good, or are
these
> >> still in some location from which they can be recovered?
> >>
> >> Thanks for your advice,
> >>
> >> Joe Geretz
> >>
> >>
> >>
> >>
> >
> >
>|||Yes, very important, depending to whom you speak... BACK UP YOUR SYSTEM
DATABASES...ALWAYS!
Sincerely,
Anthony Thomas
"Ilya Margolin" wrote:
> Patrick,
> DTS saved as VB cannot be saved back to a server. Not as easy. You would
> have to include it into a VB snippet and save it from VB. That would loose
> you graphical layout and comments. Regular way would be backing up msdb or
> saving DTS as structured files.
> Ilya
> "Patrick" <patriarck@.gmail.com> wrote in message
> news:%23xEVEIrwEHA.1392@.tk2msftngp13.phx.gbl...
> > You can save as VBscript or just save as .dts file
> >
> >
> > "Joseph Geretz" <jgeretz@.nospam.com> wrote in message
> > news:OcFw21pwEHA.2172@.TK2MSFTNGP14.phx.gbl...
> > > Point well taken. All source is saved to Visual Source Safe but database
> > > procs and utilities - weak link! I guess I'd always assumed these to be
> > > resident in one particular database or another, but obviously they're
> not.
> > >
> > > How do you do a DTS Backup? We use DTS to backup our databases from one
> > > server to another, but backing up the DTS packages themselves? I didn't
> > > know you could do that.
> > >
> > > Thanks,
> > >
> > > - Joe Geretz -
> > >
> > > "Patrick" <patriarck@.gmail.com> wrote in message
> > > news:uEWLQtpwEHA.1452@.TK2MSFTNGP11.phx.gbl...
> > >> do you have msdb backup? any DTS backup?
> > >>
> > >>
> > >> "Joseph Geretz" <jgeretz@.nospam.com> wrote in message
> > >> news:%23nGKAopwEHA.2172@.TK2MSFTNGP14.phx.gbl...
> > >> Something was wrong with my local SQL Server environment. I was unable
> > >> to connect to my own SQL Server running on my own workstation. So I
> > >> uninstalled and reinstalled. I knew my database files would be safe
> and
> > >> sure enough, I just reattached them after reinstalling.
> > >>
> > >> But where are my DTS Packages' Are these gone for good, or are
> these
> > >> still in some location from which they can be recovered?
> > >>
> > >> Thanks for your advice,
> > >>
> > >> Joe Geretz
> > >>
> > >>
> > >>
> > >>
> > >
> > >
> >
> >
>
>|||If you didn't save them as files, just restore your backup of msdb.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:%23nGKAopwEHA.2172@.TK2MSFTNGP14.phx.gbl...
> Something was wrong with my local SQL Server environment. I was unable to
> connect to my own SQL Server running on my own workstation. So I uninstalled
> and reinstalled. I knew my database files would be safe and sure enough, I
> just reattached them after reinstalling.
> But where are my DTS Packages' Are these gone for good, or are these
> still in some location from which they can be recovered?
> Thanks for your advice,
> Joe Geretz
>|||Guys you can get a realy good tool called DTSBackup from SQLDTS.com.
We back up 3000 scripts weekly. Just for peace of mind.
"Tibor Karaszi" wrote:
> If you didn't save them as files, just restore your backup of msdb.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Joseph Geretz" <jgeretz@.nospam.com> wrote in message
> news:%23nGKAopwEHA.2172@.TK2MSFTNGP14.phx.gbl...
> > Something was wrong with my local SQL Server environment. I was unable to
> > connect to my own SQL Server running on my own workstation. So I uninstalled
> > and reinstalled. I knew my database files would be safe and sure enough, I
> > just reattached them after reinstalling.
> >
> > But where are my DTS Packages' Are these gone for good, or are these
> > still in some location from which they can be recovered?
> >
> > Thanks for your advice,
> >
> > Joe Geretz
> >
> >
>
>

Wednesday, March 7, 2012

HELP! " the table schema changed after the cursor was declared"

I have a DTS package working from backend and command line. Then I did an enhancement. It works from backend but not from command line. The error says:

Step Error Description:Could not complete cursor operation because the table schema changed after the cursor was declared.

But I don't see "table schema" change?!! Any idea?

Thanks,

LiliOriginally posted by lili3000
I have a DTS package working from backend and command line. Then I did an enhancement. It works from backend but not from command line. The error says:

Step Error Description:Could not complete cursor operation because the table schema changed after the cursor was declared.

But I don't see "table schema" change?!! Any idea?

Thanks,

Lili

I ran in to the same problem. As it turned out I was running the application at 1:00 am to process credit card transaction (This application is a batched response from shipper) Anyway. I had inadvertly schedule maintence during this time. The problem was resolved by moving the Maintence time.

I hope this helps.

Terry

Help! - DTS/Job Task (SQL Server 2000)

Hello all,
I have this situation, I have a DTS package consisting of a series of data
transformations and data transfers from our server to remote servers. If I
execute the DTS from the Data Transformation Services --> Local Packages, it
runs smoothly without any problems. But, when I schedule that same DTS
package and have SQL (Server 2000) run it as a task on a daily manner it
runs "successfully" (according to SQL) but when I see the job history I see
that no records were transferred even though it says "successfully" (which,
obviously, isn't)
So, my question is, what could be causing that a DTS package running from a
Job Task transfers no records and when I run it directly from Data
Transformation Services --> Local Packages, it transfers the right data
(between 180 and 200 records depending on the day, but NEVER 0 records)
is it permissions? it it something else? (btw, my DTS package doesn't need
parameters or anything like that)
Thanks in advance for all your help,
SB-R
> So, my question is, what could be causing that a DTS package running from
> a Job Task transfers no records and when I run it directly from Data
> Transformation Services --> Local Packages, it transfers the right data
> (between 180 and 200 records depending on the day, but NEVER 0 records)
> is it permissions? it it something else? (btw, my DTS package doesn't need
> parameters or anything like that)
If you run the package remotely using Enterprise Manager, it runs on the
client machine. However, the package runs on the sever when launched via a
job. Perhaps this is reason for the difference.
Hope this helps.
Dan Guzman
SQL Server MVP
"segis bata" <segisbata@.hotmail.com> wrote in message
news:u6xcRrWWIHA.4896@.TK2MSFTNGP06.phx.gbl...
> Hello all,
> I have this situation, I have a DTS package consisting of a series of data
> transformations and data transfers from our server to remote servers. If I
> execute the DTS from the Data Transformation Services --> Local Packages,
> it runs smoothly without any problems. But, when I schedule that same DTS
> package and have SQL (Server 2000) run it as a task on a daily manner it
> runs "successfully" (according to SQL) but when I see the job history I
> see that no records were transferred even though it says "successfully"
> (which, obviously, isn't)
> So, my question is, what could be causing that a DTS package running from
> a Job Task transfers no records and when I run it directly from Data
> Transformation Services --> Local Packages, it transfers the right data
> (between 180 and 200 records depending on the day, but NEVER 0 records)
> is it permissions? it it something else? (btw, my DTS package doesn't need
> parameters or anything like that)
> Thanks in advance for all your help,
> SB-R
>
|||On Jan 18, 7:13Xam, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
>
> If you run the package remotely using Enterprise Manager, it runs on the
> client machine. XHowever, the package runs on the sever when launched via a
> job. XPerhaps this is reason for the difference.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "segis bata" <segisb...@.hotmail.com> wrote in message
> news:u6xcRrWWIHA.4896@.TK2MSFTNGP06.phx.gbl...
>
>
>
>
> - Show quoted text -
Please check the log of the job and the step details.
Thanks
Ajay Rengunthwar
MCDBA,MCTS
|||On Jan 17, 5:38 pm, "segis bata" <segisb...@.hotmail.com> wrote:
> Hello all,
> I have this situation, I have a DTS package consisting of a series of data
> transformations and data transfers from our server to remote servers. If I
> execute the DTS from the Data Transformation Services --> Local Packages, it
> runs smoothly without any problems. But, when I schedule that same DTS
> package and have SQL (Server 2000) run it as a task on a daily manner it
> runs "successfully" (according to SQL) but when I see the job history I see
> that no records were transferred even though it says "successfully" (which,
> obviously, isn't)
> So, my question is, what could be causing that a DTS package running from a
> Job Task transfers no records and when I run it directly from Data
> Transformation Services --> Local Packages, it transfers the right data
> (between 180 and 200 records depending on the day, but NEVER 0 records)
> is it permissions? it it something else? (btw, my DTS package doesn't need
> parameters or anything like that)
> Thanks in advance for all your help,
> SB-R
I came across a similar problem when I was moving data from sever A to
server B. I was able to fix this by adding a lag between when the data
was available in server A and when it got pulled by the job into
server B. This may or may not solve your problem but its worth a try.
|||Dan,
I know that, that's why when I run it (I should've specified this in my
previous message) I use remote desktop, so I'm running "locally" from within
the same server the job task must run.
But thanks for your pointing that out,
SB-R
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:8DE4CE3E-6435-43CA-A152-A21982BF06F7@.microsoft.com...
> If you run the package remotely using Enterprise Manager, it runs on the
> client machine. However, the package runs on the sever when launched via
> a job. Perhaps this is reason for the difference.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "segis bata" <segisbata@.hotmail.com> wrote in message
> news:u6xcRrWWIHA.4896@.TK2MSFTNGP06.phx.gbl...
>
|||Ajay,
here's what the log said: "Executed as user: usr999xxx. ...te Table
[ourDB].[dbo].[tblABC001] Step DTSRun OnFinish: Create Table
[ourDB].[dbo].[tblABC001] Step DTSRun OnStart: Copy Data from tblABC001
to [ourDB].[dbo].[tblABC001] Step DTSRun OnProgress: Copy Data from
tblABC001 to [ourDB].[dbo].[tblABC001] Step; 0 Rows have been transformed or
copied.; PercentComplete = 0; ProgressCount = 0 DTSRun OnFinish: Copy
Data from tblABC001 to [ourDB].[dbo].[tblABC001] Step DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnProgress:
DTSStep_DTSDataPumpTask_1; 0 Rows have been transformed or copied.;
PercentComplete = 0; ProgressCount = 0 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun OnStart: Create Table
[ourDB].[dbo].[tblABC002] Step DTSRun OnFinish: Create Table
[ourDB].[dbo].[tblABC002] Step D... Process Exit Code 0. The step
succeeded."
so, 0 records. No good...
"Ajay Rengunthwar" <ajudba@.gmail.com> wrote in message
news:85670f10-d8fd-4f3c-b2b1-37c1f8baa466@.s12g2000prg.googlegroups.com...
On Jan 18, 7:13 am, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
>
> If you run the package remotely using Enterprise Manager, it runs on the
> client machine. However, the package runs on the sever when launched via a
> job. Perhaps this is reason for the difference.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "segis bata" <segisb...@.hotmail.com> wrote in message
> news:u6xcRrWWIHA.4896@.TK2MSFTNGP06.phx.gbl...
>
>
>
>
> - Show quoted text -
Please check the log of the job and the step details.
Thanks
Ajay Rengunthwar
MCDBA,MCTS
|||Mark,
can you give me a couple of examples on how to do that lag? thanks!
SB-R
"Mark T" <vwttracer@.hotmail.com> wrote in message
news:69674cef-811a-4cbd-b5eb-533ad36db918@.q39g2000hsf.googlegroups.com...
> On Jan 17, 5:38 pm, "segis bata" <segisb...@.hotmail.com> wrote:
> I came across a similar problem when I was moving data from sever A to
> server B. I was able to fix this by adding a lag between when the data
> was available in server A and when it got pulled by the job into
> server B. This may or may not solve your problem but its worth a try.
|||On Jan 18, 2:54 pm, "segis bata" <segisb...@.hotmail.com> wrote:[vbcol=seagreen]
> Mark,
> can you give me a couple of examples on how to do that lag? thanks!
> SB-R
> "Mark T" <vwttra...@.hotmail.com> wrote in message
> news:69674cef-811a-4cbd-b5eb-533ad36db918@.q39g2000hsf.googlegroups.com...
>
>
>
Something like this in a different step will work: WAITFOR DELAY
'0:03'
|||I believe that when you manually run a dts package the permissions and
rights of your remote desktop login are being used but when the dts job is
executed by a job it uses whatever the permissions of the sqlagent are
(which may be the system account of the server the contains the dts
package).
Sincerely,
John K
Knowledgy Consulting
www.knowledgy.org
Atlanta's Business Intelligence and Data Warehouse Experts
"segis bata" <segisbata@.hotmail.com> wrote in message
news:u6xcRrWWIHA.4896@.TK2MSFTNGP06.phx.gbl...
> Hello all,
> I have this situation, I have a DTS package consisting of a series of data
> transformations and data transfers from our server to remote servers. If I
> execute the DTS from the Data Transformation Services --> Local Packages,
> it runs smoothly without any problems. But, when I schedule that same DTS
> package and have SQL (Server 2000) run it as a task on a daily manner it
> runs "successfully" (according to SQL) but when I see the job history I
> see that no records were transferred even though it says "successfully"
> (which, obviously, isn't)
> So, my question is, what could be causing that a DTS package running from
> a Job Task transfers no records and when I run it directly from Data
> Transformation Services --> Local Packages, it transfers the right data
> (between 180 and 200 records depending on the day, but NEVER 0 records)
> is it permissions? it it something else? (btw, my DTS package doesn't need
> parameters or anything like that)
> Thanks in advance for all your help,
> SB-R
>

Help! - DTS/Job Task (SQL Server 2000)

Hello all,
I have this situation, I have a DTS package consisting of a series of data
transformations and data transfers from our server to remote servers. If I
execute the DTS from the Data Transformation Services --> Local Packages, it
runs smoothly without any problems. But, when I schedule that same DTS
package and have SQL (Server 2000) run it as a task on a daily manner it
runs "successfully" (according to SQL) but when I see the job history I see
that no records were transferred even though it says "successfully" (which,
obviously, isn't)
So, my question is, what could be causing that a DTS package running from a
Job Task transfers no records and when I run it directly from Data
Transformation Services --> Local Packages, it transfers the right data
(between 180 and 200 records depending on the day, but NEVER 0 records)
is it permissions? it it something else? (btw, my DTS package doesn't need
parameters or anything like that)
Thanks in advance for all your help,
SB-R
> So, my question is, what could be causing that a DTS package running from
> a Job Task transfers no records and when I run it directly from Data
> Transformation Services --> Local Packages, it transfers the right data
> (between 180 and 200 records depending on the day, but NEVER 0 records)
> is it permissions? it it something else? (btw, my DTS package doesn't need
> parameters or anything like that)
If you run the package remotely using Enterprise Manager, it runs on the
client machine. However, the package runs on the sever when launched via a
job. Perhaps this is reason for the difference.
Hope this helps.
Dan Guzman
SQL Server MVP
"segis bata" <segisbata@.hotmail.com> wrote in message
news:u6xcRrWWIHA.4896@.TK2MSFTNGP06.phx.gbl...
> Hello all,
> I have this situation, I have a DTS package consisting of a series of data
> transformations and data transfers from our server to remote servers. If I
> execute the DTS from the Data Transformation Services --> Local Packages,
> it runs smoothly without any problems. But, when I schedule that same DTS
> package and have SQL (Server 2000) run it as a task on a daily manner it
> runs "successfully" (according to SQL) but when I see the job history I
> see that no records were transferred even though it says "successfully"
> (which, obviously, isn't)
> So, my question is, what could be causing that a DTS package running from
> a Job Task transfers no records and when I run it directly from Data
> Transformation Services --> Local Packages, it transfers the right data
> (between 180 and 200 records depending on the day, but NEVER 0 records)
> is it permissions? it it something else? (btw, my DTS package doesn't need
> parameters or anything like that)
> Thanks in advance for all your help,
> SB-R
>
|||On Jan 18, 7:13Xam, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
>
> If you run the package remotely using Enterprise Manager, it runs on the
> client machine. XHowever, the package runs on the sever when launched via a
> job. XPerhaps this is reason for the difference.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "segis bata" <segisb...@.hotmail.com> wrote in message
> news:u6xcRrWWIHA.4896@.TK2MSFTNGP06.phx.gbl...
>
>
>
>
> - Show quoted text -
Please check the log of the job and the step details.
Thanks
Ajay Rengunthwar
MCDBA,MCTS
|||On Jan 17, 5:38 pm, "segis bata" <segisb...@.hotmail.com> wrote:
> Hello all,
> I have this situation, I have a DTS package consisting of a series of data
> transformations and data transfers from our server to remote servers. If I
> execute the DTS from the Data Transformation Services --> Local Packages, it
> runs smoothly without any problems. But, when I schedule that same DTS
> package and have SQL (Server 2000) run it as a task on a daily manner it
> runs "successfully" (according to SQL) but when I see the job history I see
> that no records were transferred even though it says "successfully" (which,
> obviously, isn't)
> So, my question is, what could be causing that a DTS package running from a
> Job Task transfers no records and when I run it directly from Data
> Transformation Services --> Local Packages, it transfers the right data
> (between 180 and 200 records depending on the day, but NEVER 0 records)
> is it permissions? it it something else? (btw, my DTS package doesn't need
> parameters or anything like that)
> Thanks in advance for all your help,
> SB-R
I came across a similar problem when I was moving data from sever A to
server B. I was able to fix this by adding a lag between when the data
was available in server A and when it got pulled by the job into
server B. This may or may not solve your problem but its worth a try.
|||Dan,
I know that, that's why when I run it (I should've specified this in my
previous message) I use remote desktop, so I'm running "locally" from within
the same server the job task must run.
But thanks for your pointing that out,
SB-R
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:8DE4CE3E-6435-43CA-A152-A21982BF06F7@.microsoft.com...
> If you run the package remotely using Enterprise Manager, it runs on the
> client machine. However, the package runs on the sever when launched via
> a job. Perhaps this is reason for the difference.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "segis bata" <segisbata@.hotmail.com> wrote in message
> news:u6xcRrWWIHA.4896@.TK2MSFTNGP06.phx.gbl...
>
|||Ajay,
here's what the log said: "Executed as user: usr999xxx. ...te Table
[ourDB].[dbo].[tblABC001] Step DTSRun OnFinish: Create Table
[ourDB].[dbo].[tblABC001] Step DTSRun OnStart: Copy Data from tblABC001
to [ourDB].[dbo].[tblABC001] Step DTSRun OnProgress: Copy Data from
tblABC001 to [ourDB].[dbo].[tblABC001] Step; 0 Rows have been transformed or
copied.; PercentComplete = 0; ProgressCount = 0 DTSRun OnFinish: Copy
Data from tblABC001 to [ourDB].[dbo].[tblABC001] Step DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnProgress:
DTSStep_DTSDataPumpTask_1; 0 Rows have been transformed or copied.;
PercentComplete = 0; ProgressCount = 0 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun OnStart: Create Table
[ourDB].[dbo].[tblABC002] Step DTSRun OnFinish: Create Table
[ourDB].[dbo].[tblABC002] Step D... Process Exit Code 0. The step
succeeded."
so, 0 records. No good...
"Ajay Rengunthwar" <ajudba@.gmail.com> wrote in message
news:85670f10-d8fd-4f3c-b2b1-37c1f8baa466@.s12g2000prg.googlegroups.com...
On Jan 18, 7:13 am, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
>
> If you run the package remotely using Enterprise Manager, it runs on the
> client machine. However, the package runs on the sever when launched via a
> job. Perhaps this is reason for the difference.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "segis bata" <segisb...@.hotmail.com> wrote in message
> news:u6xcRrWWIHA.4896@.TK2MSFTNGP06.phx.gbl...
>
>
>
>
> - Show quoted text -
Please check the log of the job and the step details.
Thanks
Ajay Rengunthwar
MCDBA,MCTS
|||Mark,
can you give me a couple of examples on how to do that lag? thanks!
SB-R
"Mark T" <vwttracer@.hotmail.com> wrote in message
news:69674cef-811a-4cbd-b5eb-533ad36db918@.q39g2000hsf.googlegroups.com...
> On Jan 17, 5:38 pm, "segis bata" <segisb...@.hotmail.com> wrote:
> I came across a similar problem when I was moving data from sever A to
> server B. I was able to fix this by adding a lag between when the data
> was available in server A and when it got pulled by the job into
> server B. This may or may not solve your problem but its worth a try.
|||On Jan 18, 2:54 pm, "segis bata" <segisb...@.hotmail.com> wrote:[vbcol=seagreen]
> Mark,
> can you give me a couple of examples on how to do that lag? thanks!
> SB-R
> "Mark T" <vwttra...@.hotmail.com> wrote in message
> news:69674cef-811a-4cbd-b5eb-533ad36db918@.q39g2000hsf.googlegroups.com...
>
>
>
Something like this in a different step will work: WAITFOR DELAY
'0:03'

Help! - DTS/Job Task (SQL Server 2000)

Hello all,
I have this situation, I have a DTS package consisting of a series of data
transformations and data transfers from our server to remote servers. If I
execute the DTS from the Data Transformation Services --> Local Packages, it
runs smoothly without any problems. But, when I schedule that same DTS
package and have SQL (Server 2000) run it as a task on a daily manner it
runs "successfully" (according to SQL) but when I see the job history I see
that no records were transferred even though it says "successfully" (which,
obviously, isn't)
So, my question is, what could be causing that a DTS package running from a
Job Task transfers no records and when I run it directly from Data
Transformation Services --> Local Packages, it transfers the right data
(between 180 and 200 records depending on the day, but NEVER 0 records)
is it permissions? it it something else? (btw, my DTS package doesn't need
parameters or anything like that)
Thanks in advance for all your help,
SB-R> So, my question is, what could be causing that a DTS package running from
> a Job Task transfers no records and when I run it directly from Data
> Transformation Services --> Local Packages, it transfers the right data
> (between 180 and 200 records depending on the day, but NEVER 0 records)
> is it permissions? it it something else? (btw, my DTS package doesn't need
> parameters or anything like that)
If you run the package remotely using Enterprise Manager, it runs on the
client machine. However, the package runs on the sever when launched via a
job. Perhaps this is reason for the difference.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"segis bata" <segisbata@.hotmail.com> wrote in message
news:u6xcRrWWIHA.4896@.TK2MSFTNGP06.phx.gbl...
> Hello all,
> I have this situation, I have a DTS package consisting of a series of data
> transformations and data transfers from our server to remote servers. If I
> execute the DTS from the Data Transformation Services --> Local Packages,
> it runs smoothly without any problems. But, when I schedule that same DTS
> package and have SQL (Server 2000) run it as a task on a daily manner it
> runs "successfully" (according to SQL) but when I see the job history I
> see that no records were transferred even though it says "successfully"
> (which, obviously, isn't)
> So, my question is, what could be causing that a DTS package running from
> a Job Task transfers no records and when I run it directly from Data
> Transformation Services --> Local Packages, it transfers the right data
> (between 180 and 200 records depending on the day, but NEVER 0 records)
> is it permissions? it it something else? (btw, my DTS package doesn't need
> parameters or anything like that)
> Thanks in advance for all your help,
> SB-R
>|||Dan,
I know that, that's why when I run it (I should've specified this in my
previous message) I use remote desktop, so I'm running "locally" from within
the same server the job task must run.
But thanks for your pointing that out,
SB-R
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:8DE4CE3E-6435-43CA-A152-A21982BF06F7@.microsoft.com...
>> So, my question is, what could be causing that a DTS package running from
>> a Job Task transfers no records and when I run it directly from Data
>> Transformation Services --> Local Packages, it transfers the right data
>> (between 180 and 200 records depending on the day, but NEVER 0 records)
>> is it permissions? it it something else? (btw, my DTS package doesn't
>> need parameters or anything like that)
> If you run the package remotely using Enterprise Manager, it runs on the
> client machine. However, the package runs on the sever when launched via
> a job. Perhaps this is reason for the difference.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "segis bata" <segisbata@.hotmail.com> wrote in message
> news:u6xcRrWWIHA.4896@.TK2MSFTNGP06.phx.gbl...
>> Hello all,
>> I have this situation, I have a DTS package consisting of a series of
>> data transformations and data transfers from our server to remote
>> servers. If I execute the DTS from the Data Transformation Services -->
>> Local Packages, it runs smoothly without any problems. But, when I
>> schedule that same DTS package and have SQL (Server 2000) run it as a
>> task on a daily manner it runs "successfully" (according to SQL) but when
>> I see the job history I see that no records were transferred even though
>> it says "successfully" (which, obviously, isn't)
>> So, my question is, what could be causing that a DTS package running from
>> a Job Task transfers no records and when I run it directly from Data
>> Transformation Services --> Local Packages, it transfers the right data
>> (between 180 and 200 records depending on the day, but NEVER 0 records)
>> is it permissions? it it something else? (btw, my DTS package doesn't
>> need parameters or anything like that)
>> Thanks in advance for all your help,
>> SB-R
>|||Ajay,
here's what the log said: "Executed as user: usr999xxx. ...te Table
[ourDB].[dbo].[tblABC001] Step DTSRun OnFinish: Create Table
[ourDB].[dbo].[tblABC001] Step DTSRun OnStart: Copy Data from tblABC001
to [ourDB].[dbo].[tblABC001] Step DTSRun OnProgress: Copy Data from
tblABC001 to [ourDB].[dbo].[tblABC001] Step; 0 Rows have been transformed or
copied.; PercentComplete = 0; ProgressCount = 0 DTSRun OnFinish: Copy
Data from tblABC001 to [ourDB].[dbo].[tblABC001] Step DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnProgress:
DTSStep_DTSDataPumpTask_1; 0 Rows have been transformed or copied.;
PercentComplete = 0; ProgressCount = 0 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun OnStart: Create Table
[ourDB].[dbo].[tblABC002] Step DTSRun OnFinish: Create Table
[ourDB].[dbo].[tblABC002] Step D... Process Exit Code 0. The step
succeeded."
so, 0 records. No good...
"Ajay Rengunthwar" <ajudba@.gmail.com> wrote in message
news:85670f10-d8fd-4f3c-b2b1-37c1f8baa466@.s12g2000prg.googlegroups.com...
On Jan 18, 7:13 am, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> > So, my question is, what could be causing that a DTS package running
> > from
> > a Job Task transfers no records and when I run it directly from Data
> > Transformation Services --> Local Packages, it transfers the right data
> > (between 180 and 200 records depending on the day, but NEVER 0 records)
> > is it permissions? it it something else? (btw, my DTS package doesn't
> > need
> > parameters or anything like that)
> If you run the package remotely using Enterprise Manager, it runs on the
> client machine. However, the package runs on the sever when launched via a
> job. Perhaps this is reason for the difference.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "segis bata" <segisb...@.hotmail.com> wrote in message
> news:u6xcRrWWIHA.4896@.TK2MSFTNGP06.phx.gbl...
>
> > Hello all,
> > I have this situation, I have a DTS package consisting of a series of
> > data
> > transformations and data transfers from our server to remote servers. If
> > I
> > execute the DTS from the Data Transformation Services --> Local
> > Packages,
> > it runs smoothly without any problems. But, when I schedule that same
> > DTS
> > package and have SQL (Server 2000) run it as a task on a daily manner it
> > runs "successfully" (according to SQL) but when I see the job history I
> > see that no records were transferred even though it says "successfully"
> > (which, obviously, isn't)
> > So, my question is, what could be causing that a DTS package running
> > from
> > a Job Task transfers no records and when I run it directly from Data
> > Transformation Services --> Local Packages, it transfers the right data
> > (between 180 and 200 records depending on the day, but NEVER 0 records)
> > is it permissions? it it something else? (btw, my DTS package doesn't
> > need
> > parameters or anything like that)
> > Thanks in advance for all your help,
> > SB-R- Hide quoted text -
> - Show quoted text -
Please check the log of the job and the step details.
Thanks
Ajay Rengunthwar
MCDBA,MCTS|||Mark,
can you give me a couple of examples on how to do that lag? thanks!
SB-R
"Mark T" <vwttracer@.hotmail.com> wrote in message
news:69674cef-811a-4cbd-b5eb-533ad36db918@.q39g2000hsf.googlegroups.com...
> On Jan 17, 5:38 pm, "segis bata" <segisb...@.hotmail.com> wrote:
>> Hello all,
>> I have this situation, I have a DTS package consisting of a series of
>> data
>> transformations and data transfers from our server to remote servers. If
>> I
>> execute the DTS from the Data Transformation Services --> Local Packages,
>> it
>> runs smoothly without any problems. But, when I schedule that same DTS
>> package and have SQL (Server 2000) run it as a task on a daily manner it
>> runs "successfully" (according to SQL) but when I see the job history I
>> see
>> that no records were transferred even though it says "successfully"
>> (which,
>> obviously, isn't)
>> So, my question is, what could be causing that a DTS package running from
>> a
>> Job Task transfers no records and when I run it directly from Data
>> Transformation Services --> Local Packages, it transfers the right data
>> (between 180 and 200 records depending on the day, but NEVER 0 records)
>> is it permissions? it it something else? (btw, my DTS package doesn't
>> need
>> parameters or anything like that)
>> Thanks in advance for all your help,
>> SB-R
> I came across a similar problem when I was moving data from sever A to
> server B. I was able to fix this by adding a lag between when the data
> was available in server A and when it got pulled by the job into
> server B. This may or may not solve your problem but its worth a try.|||I believe that when you manually run a dts package the permissions and
rights of your remote desktop login are being used but when the dts job is
executed by a job it uses whatever the permissions of the sqlagent are
(which may be the system account of the server the contains the dts
package).
--
Sincerely,
John K
Knowledgy Consulting
www.knowledgy.org
Atlanta's Business Intelligence and Data Warehouse Experts
"segis bata" <segisbata@.hotmail.com> wrote in message
news:u6xcRrWWIHA.4896@.TK2MSFTNGP06.phx.gbl...
> Hello all,
> I have this situation, I have a DTS package consisting of a series of data
> transformations and data transfers from our server to remote servers. If I
> execute the DTS from the Data Transformation Services --> Local Packages,
> it runs smoothly without any problems. But, when I schedule that same DTS
> package and have SQL (Server 2000) run it as a task on a daily manner it
> runs "successfully" (according to SQL) but when I see the job history I
> see that no records were transferred even though it says "successfully"
> (which, obviously, isn't)
> So, my question is, what could be causing that a DTS package running from
> a Job Task transfers no records and when I run it directly from Data
> Transformation Services --> Local Packages, it transfers the right data
> (between 180 and 200 records depending on the day, but NEVER 0 records)
> is it permissions? it it something else? (btw, my DTS package doesn't need
> parameters or anything like that)
> Thanks in advance for all your help,
> SB-R
>