DTS Export Object

I have created a DTS object to import a text. This works fine, however, I
need to find a way to 'generate a script' to be able to recreate this same
DTS in another places.
ArthurArthur,
when you Save As ... the package, in the Location dropdown you get 3
choices. You can save to a server, local or remote, as a structured storage
file or as a vb file. I have not worked with a vb file. But if you save as
a structured storage file, you can copy it to other place and there right
click Local Packages and choose Open.
hth
Quentin
"Arthur C" <arthur.christy@.tamut.edu.delete.me> wrote in message
news:O7gND6lmDHA.1884@.TK2MSFTNGP09.phx.gbl...
> I have created a DTS object to import a text. This works fine, however, I
> need to find a way to 'generate a script' to be able to recreate this same
> DTS in another places.
> Arthur
>

DTS Export fails with "Invalid Object Name"

I have a database that used to have a table called MALL_Customers. The
table was renamed to tbl_Users, and a view was created called MALL_Customers
which simply selects all rows from tbl_Users, for backwards compatibility
with the web app accessing the database.
The database works fine; the web app works fine. All permissions are set
properly on all objects. The view is accessible via EM, as is the table.
However, when I attempt to run a DTS Export of this database to an empty
database on the same server, I get "invalid object name" on MALL_Customers.
Apparently somewhere, in some trigger, stored proc, or some other object,
there is a reference to MALL_Customers that doesn't recognize the VIEW as a
valid object, but is instead looking for a TABLE with that name. I cannot
find it anywhere, and I don't know where to start.
The overall question then is, how do I find all references to the text
"MALL_Customers" in the database schema so that I can track down why my DTS
package is failing. I have another database with a similar problem, and I
think the solution to this problem will also solve the other -- hidden
references somewhere in the database that cause "invalid object name" errors
when using DTS.
Where do I start?
Thanks in Advance,
Marcyou can search through the system table syscomments which, contains entries
for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint,
and stored procedure
Ex:
select * from syscomments where text like '%MALL_Customers%'
Also you can use stored procedure sp_depends to know dependencies of the
existing object.
Ex:
sp_depends 'MALL_Customers'
--
-Vishal
Marc Funaro <marc@.advantex.net> wrote in message
news:xyK7b.3731$nF5.632@.news02.roc.ny...
> I have a database that used to have a table called MALL_Customers. The
> table was renamed to tbl_Users, and a view was created called
MALL_Customers
> which simply selects all rows from tbl_Users, for backwards compatibility
> with the web app accessing the database.
> The database works fine; the web app works fine. All permissions are set
> properly on all objects. The view is accessible via EM, as is the table.
> However, when I attempt to run a DTS Export of this database to an empty
> database on the same server, I get "invalid object name" on
MALL_Customers.
> Apparently somewhere, in some trigger, stored proc, or some other object,
> there is a reference to MALL_Customers that doesn't recognize the VIEW as
a
> valid object, but is instead looking for a TABLE with that name. I cannot
> find it anywhere, and I don't know where to start.
> The overall question then is, how do I find all references to the text
> "MALL_Customers" in the database schema so that I can track down why my
DTS
> package is failing. I have another database with a similar problem, and I
> think the solution to this problem will also solve the other -- hidden
> references somewhere in the database that cause "invalid object name"
errors
> when using DTS.
> Where do I start?
> Thanks in Advance,
> Marc
>

DTS Execute from Com Object (Workgroup Version)

Hey Guys,

I have written some code that executes a DTS package from a COM object. It works great on my staging server which is MSSQL 2000 Standard Edition. I just got a new live server which has MSSQL Server 2000 Workgroup Edition. Now I recieve an error message when trying to execute the DTS package from the COM object. Is this perhaps something that is not supported with the Workgroup edition? Is there anyway to varify for sure because as you all know it would cost me a good amount of money to upgrade.

Thanks in advance!

JayStang wrote:

Hey Guys,

I have written some code that executes a DTS package from a COM object. It works great on my staging server which is MSSQL 2000 Standard Edition. I just got a new live server which has MSSQL Server 2000 Workgroup Edition. Now I recieve an error message when trying to execute the DTS package from the COM object. Is this perhaps something that is not supported with the Workgroup edition? Is there anyway to varify for sure because as you all know it would cost me a good amount of money to upgrade.

Thanks in advance!

I recommend you try the DTS newsgroup microsoft.public.sqlserver.dts

DTS Delimited Record Question

Hello-
I am using SQL Server 2000 and am processing records via DTS. The
records are delimited and I have no trouble breaking them using the
file object but every record ends with a tilde (~) that I would like to
strip off. Is it possible to set the record delimiter to "Tilde+cr+lf"
so that the tilde would get chopped off each record before being passed
to my AxtiveX script? The records do not have the same number of
elements, so I can not simply chmop the tilde off the Nth field.
If the above will not work, how can I determine the final field in each
record and remove the offending tilde?
Thanks!
--greg
Hi
If you do not expect a tilda in any field you can just replace any occurence
in every field. If you activeX scripts assumes that there are at most n
fields you can check backwards to the last non-blank field and remove the
tilda.
John
"tubaranger@.gmail.com" wrote:

> Hello-
> I am using SQL Server 2000 and am processing records via DTS. The
> records are delimited and I have no trouble breaking them using the
> file object but every record ends with a tilde (~) that I would like to
> strip off. Is it possible to set the record delimiter to "Tilde+cr+lf"
> so that the tilde would get chopped off each record before being passed
> to my AxtiveX script? The records do not have the same number of
> elements, so I can not simply chmop the tilde off the Nth field.
> If the above will not work, how can I determine the final field in each
> record and remove the offending tilde?
> Thanks!
> --greg
>
sql

DTS Delimited Record Question

Hello-
I am using SQL Server 2000 and am processing records via DTS. The
records are delimited and I have no trouble breaking them using the
file object but every record ends with a tilde (~) that I would like to
strip off. Is it possible to set the record delimiter to "Tilde+cr+lf"
so that the tilde would get chopped off each record before being passed
to my AxtiveX script? The records do not have the same number of
elements, so I can not simply chmop the tilde off the Nth field.
If the above will not work, how can I determine the final field in each
record and remove the offending tilde?
Thanks!
--gregHi
If you do not expect a tilda in any field you can just replace any occurence
in every field. If you activeX scripts assumes that there are at most n
fields you can check backwards to the last non-blank field and remove the
tilda.
John
"tubaranger@.gmail.com" wrote:

> Hello-
> I am using SQL Server 2000 and am processing records via DTS. The
> records are delimited and I have no trouble breaking them using the
> file object but every record ends with a tilde (~) that I would like to
> strip off. Is it possible to set the record delimiter to "Tilde+cr+lf"
> so that the tilde would get chopped off each record before being passed
> to my AxtiveX script? The records do not have the same number of
> elements, so I can not simply chmop the tilde off the Nth field.
> If the above will not work, how can I determine the final field in each
> record and remove the offending tilde?
> Thanks!
> --greg
>

DTS & .NET

I have a simple DTS package that is being executed within a .Net application using the MS DTSPackage Object Library. Since I have the SQL Server 2000 desktop tools installed, the package executes with no problems, however, when another user tries to execute it, the following exception is thrown:

Retrieving the COM class factory for component with CLSID {10020200-EB1C-11CF-AE6E-00AA004A34D5} failed due to the following error: 80040154

I've tried including all the DTS references, rather than just the Package object reference, and that didn't help.

Thanks in advance for your help. THis is really frustrating.

Hi toneho,

are you using PackageEvents interface?

Can you provide us a sample?

|||0x80040154 is 'class not registered', i.e. DTS is not installed on this machine.
Solution - you need to install it :)

P.S. This is SSIS forum, not DTS.|||

It is my understanding that the DTS forum was changed to the SSIS forum.

So I included the DTS packageobject interop in my application - what more needs to be done to install DTS?

|||

toneho wrote:

It is my understanding that the DTS forum was changed to the SSIS forum.

So I included the DTS packageobject interop in my application - what more needs to be done to install DTS?

Try: http://groups.google.com/group/microsoft.public.sqlserver.dts/topics?lnk=srg|||Interop is just that - interop, that allows you to talk to COM object from .NET code. You need the actual COM object installed - e.g. install DTS from SQL 2000 CD :)

DTS - Recovery Model

SQL Server 2000 SP3.
Prior to SP3 the recovery model was switched to simple during transfer (Copy object task) and changed back to the previouis setting after DTS was complete.
Nice thing because performance was increased and T-Log was keep small.

Now I assume that the recovery model is switched to bulk-logged causing the T-Log to explode, to be onest not in all my databases.

1.Is my interpretation regarding recovery model correct?
2.Does anybody knows the reason of this change?

Any suggestion is really appreciate.
Thank you very much - kind regards.

FrancoYikes!

If I read this correctly:

1. You have a DTS package
2. The DTS package changes the Database logging option from Full (or something other than Simple) to Simple
3. You import your data
4. The DTS package then switches the Database logging option back to its original setting

As far as I know, nothing regarding the database recovery model was changed in SP3. There were, however, many security changes to SP3. It may be possible that the switch to the Simple model is failing due to a permissions issue (the context under which DTS is running may not be permitted to alter database settings).

You do realize, of course, that your backups and transaction logs are useless after the DTS package runs? I mean, you can recover your data up to the point that the DTS package starts, but everything after that is toast?

I'd like to ask others on the forum if this is a common practice; I use full logging myself and I do run into issues with an oversized transaction log, but I have it on a separate partition and I generally don't worry about it too much. I'd be interested to hear other experiences.

Hugh Scott
Originally posted by franco
SQL Server 2000 SP3.
Prior to SP3 the recovery model was switched to simple during transfer (Copy object task) and changed back to the previouis setting after DTS was complete.
Nice thing because performance was increased and T-Log was keep small.

Now I assume that the recovery model is switched to bulk-logged causing the T-Log to explode, to be onest not in all my databases.

1.Is my interpretation regarding recovery model correct?
2.Does anybody knows the reason of this change?

Any suggestion is really appreciate.
Thank you very much - kind regards.

Franco|||This is the explanation I have from another forum that makes light on the subject:

The problem was that as well as switching on select into/bulk copy it also performed a

dump tran databasename with no_log

This invalidated your log backup chain and required a full database backup to be performed immediately to maintain recoverability. It was never intended to do this and there was no real indication on this (it was in the event log but it was not a documented side effect of the copy objects task) so this bug was fixed in SP3. As the transaction log is no longer truncated the log chain is kept valid. However it still uses bcp functionality and should be minimally logged BUT your transaction log backups will be much bigger. I have done testing to see that this was fixed but must say I haven't checked the log sizes. I will do some testing and post back. Hopefully my above rambling explains why this change was made.

HTH
Jasper Smith|||RE:
Yikes!

If I read this correctly:

1. You have a DTS package
2. The DTS package changes the Database logging option from Full (or something other than Simple) to Simple
3. You import your data
4. The DTS package then switches the Database logging option back to its original setting

Q1 I'd like to ask others on the forum if this is a common practice; I use full logging myself and I do run into issues with an oversized transaction log, but I have it on a separate partition and I generally don't worry about it too much. I'd be interested to hear other experiences. Hugh Scott

A1 As with many things, much depends on available resources, the nature of the application / purpose of the DB, the size / importance / purpose of the loads involved, etc., etc., etc..

For example, in some mission critical (and resource / budget constrained) production environments sometimes load schemes amount to something like: Full (final TL dump) --> Simple (dbo / single user) --> (perform data loads) --> Diff dump --> Full (multi user). Obviously, if large numbers of OLTP inserts / updates, etc. must be allowed in a fully logged fashion (7/24) such a scheme is not appropriate.

dt_verstamp006

Hi, I was looking on SQLServer log files in EM, I found the following
message :
"EXECUTE permission denied on object 'dt_verstamp006', database 'mydaabase',
owner 'dbo'.."
just before this message there is :
"Error: 229, Severity: 14, State: 5"
So, what is "dt_verstamp006" object ? the user executing the sql is dbo user
Thanks in advance
Hello,
dt_verstamp006 was created when when you created and saved a database
diagram in Enterprise
Manager. Please refer to the following article for details:
327145 FIX: You cannot create diagrams in SQL Enterprise Manager if you are
not
http://support.microsoft.com/?id=327145
You may want to run following command to grant exec permssion to the SP
Grant exec on dt_verstamp006 to [user].
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: dt_verstamp006
| thread-index: AcVA048EkrLc/oC0RQKNwLeu08tSyQ==
| X-WBNR-Posting-Host: 194.51.27.4
| From: "=?Utf-8?B?U2FsYW1FbGlhcw==?=" <eliassal@.online.nospam>
| Subject: dt_verstamp006
| Date: Thu, 14 Apr 2005 02:23:03 -0700
| Lines: 8
| Message-ID: <EA9A9625-C77F-4B49-91B0-B1686B590F45@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| Path: TK2MSFTNGXA01.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:52446
| NNTP-Posting-Host: tk2msftngxa03.phx.gbl 10.40.2.157
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Hi, I was looking on SQLServer log files in EM, I found the following
| message :
| "EXECUTE permission denied on object 'dt_verstamp006', database
'mydaabase',
| owner 'dbo'.."
| just before this message there is :
| "Error: 229, Severity: 14, State: 5"
| So, what is "dt_verstamp006" object ? the user executing the sql is dbo
user
| Thanks in advance
|

dt_verstamp006

Hi, I was looking on SQLServer log files in EM, I found the following
message :
"EXECUTE permission denied on object 'dt_verstamp006', database 'mydaabase',
owner 'dbo'.."
just before this message there is :
"Error: 229, Severity: 14, State: 5"
So, what is "dt_verstamp006" object ? the user executing the sql is dbo user
Thanks in advanceHello,
dt_verstamp006 was created when when you created and saved a database
diagram in Enterprise
Manager. Please refer to the following article for details:
327145 FIX: You cannot create diagrams in SQL Enterprise Manager if you are
not
http://support.microsoft.com/?id=327145
You may want to run following command to grant exec permssion to the SP
Grant exec on dt_verstamp006 to [user].
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: dt_verstamp006
| thread-index: AcVA048EkrLc/oC0RQKNwLeu08tSyQ==
| X-WBNR-Posting-Host: 194.51.27.4
| From: "examnotes" <eliassal@.online.nospam>
| Subject: dt_verstamp006
| Date: Thu, 14 Apr 2005 02:23:03 -0700
| Lines: 8
| Message-ID: <EA9A9625-C77F-4B49-91B0-B1686B590F45@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.server
| Path: TK2MSFTNGXA01.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:52446
| NNTP-Posting-Host: tk2msftngxa03.phx.gbl 10.40.2.157
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Hi, I was looking on SQLServer log files in EM, I found the following
| message :
| "EXECUTE permission denied on object 'dt_verstamp006', database
'mydaabase',
| owner 'dbo'.."
| just before this message there is :
| "Error: 229, Severity: 14, State: 5"
| So, what is "dt_verstamp006" object ? the user executing the sql is dbo
user
| Thanks in advance
|