DTS Fixed Field length File Limitations

Hi,
I am trying to upload a fixed field text file to a sqlserver table using the DTS wizard. The txt file has 111 columns and the total length of a single row is 5897. The problem is when I use the wizard to specify the starting and ending of each column, its not allowing me to specify the columns beyond the position 4095.
Is there a limitation on this? if so is there a work around ? to solve this.
Any help on this is truly appreciated.

Thanks much. :)I've never encountered this problem but then I have never had a file quite that wide....

Personally what I would do is write a quick wee app or ActiveX Script to slice the file in half and then do the import in two stages... probably doesn't help much but it's the best suggestion I can give you.

DTS export problem

Hello

i can 't use DTS export via entreprise manager betwen 2 identic server (same os ,same
sqlserver 7 ,same pack et...)

I have this message

The licensing mode don t let to use DTS export ...

Where i find a parameter ?
Where i find a tool to change the licensing mode ?

RegardsControl Panel/Licensingsql

DTS export (lincensing problem)

I have a problem with licencing mode in sqlserver when i use I export table
from database to another database on different servers
On the same server the dts export run correctely between 2 database
but on different servers i have this message
The servers are on sqlserver 7 with tha same configuration

The licensing mode of sqlserver don t permit to run the DTS export

Where i can change this parameter

RegardsCall your vendor and buy the licenses. It will automagically change for you.|||Hello

We have buy the licence but the problem is output ?

DTS execution from client machine fails connecting to Oracle

I have created a DTS package that pulls data in from Oracle into SQL
Server. When I run it directly on the server (from Enterprise
Manager), it works fine. When I run it from Enterprise Manager on a
client machine that does not have the Oracle client software, it does
not run, giving me the error: "The Oracle client and networking
components were not found...". I was hoping I wouldn't need to have
the client software installed on a machine other than the server where
Sql Server is running. The problem here is that there are a number of
machines from where I would like to execute this DTS package that I do
not want to install/configure the Oracle client software. I don't
quite understand why the configuration of the client is important
here. In my mind, when I am using enterprise manager from a client
machine, I am using it sort of like a terminal services client to
connect to the server. I guess there is a lot more happening in the
background.

Thanks for any feedback,
MarcusDo you have the proper ODBC driver that you are using in the DTS on the
client machine? same as the one on your server.

"Marcus" <holysmokes99@.hotmail.com> wrote in message
news:1783abaf.0307250838.70a1aaa4@.posting.google.c om...
> I have created a DTS package that pulls data in from Oracle into SQL
> Server. When I run it directly on the server (from Enterprise
> Manager), it works fine. When I run it from Enterprise Manager on a
> client machine that does not have the Oracle client software, it does
> not run, giving me the error: "The Oracle client and networking
> components were not found...". I was hoping I wouldn't need to have
> the client software installed on a machine other than the server where
> Sql Server is running. The problem here is that there are a number of
> machines from where I would like to execute this DTS package that I do
> not want to install/configure the Oracle client software. I don't
> quite understand why the configuration of the client is important
> here. In my mind, when I am using enterprise manager from a client
> machine, I am using it sort of like a terminal services client to
> connect to the server. I guess there is a lot more happening in the
> background.
> Thanks for any feedback,
> Marcus|||Marcus wrote:

> I have created a DTS package that pulls data in from Oracle into SQL
> Server. When I run it directly on the server (from Enterprise
> Manager), it works fine. When I run it from Enterprise Manager on a
> client machine that does not have the Oracle client software, it does
> not run, giving me the error: "The Oracle client and networking
> components were not found...". I was hoping I wouldn't need to have
> the client software installed on a machine other than the server where
> Sql Server is running. The problem here is that there are a number of
> machines from where I would like to execute this DTS package that I do
> not want to install/configure the Oracle client software. I don't
> quite understand why the configuration of the client is important
> here. In my mind, when I am using enterprise manager from a client
> machine, I am using it sort of like a terminal services client to
> connect to the server. I guess there is a lot more happening in the
> background.
> Thanks for any feedback,
> Marcus

The Oracle client is required. So is paying attention to the license
agreement.

--
Daniel Morgan
http://www.outreach.washington.edu/...oad/oad_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)sql

DTS Execute SQL Task failing in SQLServer 2005

Hello All,
We migrtated to SQLServer 2005 yesterday. On the same server, I uninstalled
SQLServer 2000 Enterprise Edition 32-bit Sp4 and installed SQLServer 2005
Enterprise Edition 32-bit SP2.
The new version is working fine and the performance is also comparable. For
the DTS packages, I download and installed the DTS Runtime components. All
the DTS packages work except for one. The package, calls a SP residing in the
user database in "Execute SQL" Task and then copies the data into a flat file
via DataPump Task on a remote share. The remote share is available all the
time.
Last night the Execute SQL task had failed and this morning I troubleshooted
it and found out that this is happening randomly and whenever it fails I see
this in log that it writes to. I tested it once after the migration and I ran
it once again now and in both the cases it worked fine.
This process never used to fail in SQLServer 2000. So, I was wondering if
this has anything to do with SQLServer 2005.
Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:[DBNETLIB][ConnectionRead (recv()).]General network
error. Check your network documentation.
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:0
Thanks,
rgnHi
Error number 80004005 is a generic error and has quite a wide range of
causes for instance:
http://tutorials.aspfaq.com/8000xxxxx-errors/80004005-errors.html
http://tinyurl.com/2s9qqm
In the long term you should be considering moving over to SSIS.
John
"rgn" wrote:
> Hello All,
> We migrtated to SQLServer 2005 yesterday. On the same server, I uninstalled
> SQLServer 2000 Enterprise Edition 32-bit Sp4 and installed SQLServer 2005
> Enterprise Edition 32-bit SP2.
> The new version is working fine and the performance is also comparable. For
> the DTS packages, I download and installed the DTS Runtime components. All
> the DTS packages work except for one. The package, calls a SP residing in the
> user database in "Execute SQL" Task and then copies the data into a flat file
> via DataPump Task on a remote share. The remote share is available all the
> time.
> Last night the Execute SQL task had failed and this morning I troubleshooted
> it and found out that this is happening randomly and whenever it fails I see
> this in log that it writes to. I tested it once after the migration and I ran
> it once again now and in both the cases it worked fine.
> This process never used to fail in SQLServer 2000. So, I was wondering if
> this has anything to do with SQLServer 2005.
>
> Step Error Source: Microsoft OLE DB Provider for SQL Server
> Step Error Description:[DBNETLIB][ConnectionRead (recv()).]General network
> error. Check your network documentation.
> Step Error code: 80004005
> Step Error Help File:
> Step Error Help Context ID:0
> Thanks,
> rgn

DTS Error

Hi,
Can any body help me to sort out this problem

I am running a DTS package source as dbf file and destination as sqlserver database the following error occurs

for row number 2035 errors encountered so far on this
task 1. insert error, column 3 ('arch_dt', DB_TYPE DBTIMESTAMP)

when i was opening dbf file in foxbase its showing (arch_dt field) date as datatype the same when i was importing to access the datatype is changing to text type.

so daily manually i am opening in excel file and delete the records where the line error occurs.

in excel file that particular row will have some text format instead of dateformat.

how can i solve this error without manual interaction..

with regards
rajkumarIf you insert it as a chracter datatype then you can check the value and correct it.
You could also probably use an activex script on it but that would be slow.

DTS DB2 Dates 0001-01-01

Hi,

I am trying to transfer a table from DB2 to SQLServer 2000 through a DTS package. The DB2 table contains fields with default dates of "0001-01-01". The DTS package errors out whenever it reads this date as "invalid data value". In SQLServer 2000, the date fields are of type ShortDateTime. I have searched the Internet but did not find a workable solution. Please, can anyone help me find a solution?

Older applications sometimes used dates like '9999-12-31' or '0001-01-01' to signify that the date was either NULL, invalid or not-entered. If DB2 is using that date as a valid point in time then the following would not work. However if it is a sentinal value used to represent NULL then you could transform such dates into NULL on-the-fly as you extract them from DB2; in pseudocode:

SELECT

NullIf(theDate, '0001-01-01') as theDate, <other fields>

FROM theTable

(You would need to find out the equivalent function for NullIf in DB2 parlance) This would have the effect of leaving all dates alone except for '0001-01-01' which would be translated to NULL, which would keep SSIS happy.

If you translated it to NULL then you would have to be careful that you did not break other business rules.

|||Thanks but I don't have any control over the creation of DB2 tables hence I was looking for DTS to do the trick. Can DTS handle this?|||In the source component of your DTS package, use the SELECT statement and conversion functions as detailed in my previous reply.|||

Thanks.

I am rather new to DTS and am trying to get an example of creating a package using vbscript. I have looked at Books on line but the examples are a little confusing for me right now. Do you by chance have any sample code that shows how to do something similar like this ?

Appreciate your help.

DTS Creation issue

Hi all,

Here is the problem : I can not create a DTS package in SQLServer.
=> Error description : access denied
Environment :
- Windows NT4 sp6 Server, french, logon on as "Administrator".
- SQLServer 7.0 sp4, french, logon as "sa" on the NT Server with Enterprise Manager.
- SQLServer Agent runs under an Administrator account

Why can't i create single DTS package ? Strange, isn't it ?
Many thanks for your ideas

BertrandLook for the latest Service Pack (I don't know the exact one but I think it's 3)

Good luck.|||Actually it is the french sp4 for SQLServer 7.0 french.

I can design a DTS package but i can not save it : "Access denied".
Thanks for your help

Originally posted by Crespo-n00b
Look for the latest Service Pack (I don't know the exact one but I think it's 3)

Good luck.|||On SQL ver7 SP4 is the last and latest despite of language.

Can you try creating DTS package using DTS wizard.

DTS "The Parameter Is Incorrect" Error

I'm running SqlServer 2000 and am trying to write a DTS. For some reason I keep getting that error for my Dynamic Property Task and not sure why. I've tried giving my Package a password like was mentioned in another thread on here, but that didn't work either.

I'm trying to dynamically set a Data Source for an Excel connected, using a query.

Query:

DECLARE @.directory VARCHAR(1000)
SET @.directory = 'C:\'
EXEC [Legacy].[dbo].[spr_GetNewestExcel] @.directory

The Store Procedure:

CREATE PROCEDURE spr_GetNewestExcel
(
@.directory VARCHAR(1000)
)
AS
BEGIN
DECLARE @.cmdParam VARCHAR(8000)

--Temp Table that saves the output of a DOS command.
CREATE TABLE #output
(
output VARCHAR(300)
)

--Temp Table that saves the output of a "dir" DOS output, in parts.
CREATE TABLE #files
(
fileDate VARCHAR(20),
fileSize VARCHAR(20),
fileName VARCHAR(100)
)

--Sets the command parameter
SET @.cmdParam = 'dir "' + @.directory + '"'

--Inserts the output of the above DOS command into a table
INSERT INTO #output
EXEC master..xp_cmdshell @.cmdParam

--Parses the #output table for the files with the correct extension and breaks it up into
--Date, Size, and Name.
INSERT INTO #files
SELECT
SUBSTRING(output, 0, 21) As fileDate,
LTRIM(SUBSTRING(output, 21, 18)) As fileSize,
SUBSTRING(output, 40, LEN(output))
FROM #output
WHERE output LIKE '%.xls'

--Displays the values
SELECT TOP 1
CASE
WHEN SUBSTRING(@.directory, LEN(@.directory), 1) = '\' THEN @.directory + fileName
ELSE @.directory + '\' + fileName
END AS fileName
FROM #files ORDER BY fileDate DESC

DROP TABLE #output
DROP TABLE #files
END

The Stored Procedure runs great on its own, it displays a list of filenames with the .xls extension in the desired directory, which is what I want. It should put the first in the list((if there is more than one)) as the datasource, but it doesn't.

When I go to the "Add/Edit Assignment" window for it, and push "Refresh" to preview it, it gives me a "The Parameter Is Incorrect".

Any help would be much appreciated, this has had me stumped for quite some time.

peace,
CR
you're in the wrong forum. this forum is about ssis, not dts.|||fair enough, I saw other threads similar to this one, so I posted here. And since the description mentions data transforms, seemed fitting. Though feel free to point me in the right direction since the other boards don't seem anywhere near as fitting as this one. Least not to me.|||Well after a while I finally determined what was wrong, but I must say, what a vague error.

Anyway, apparently Dynamic Properties don't like when you use Inserts, that is all.

Thanks for reading!

peace,
CR|||

There is no forum for DTS but the old newsgroup is still alive and well: microsoft.public.sqlserver.dts

-Jamie

|||

There is no forum for DTS but the old newsgroup is still alive and well: microsoft.public.sqlserver.dts

-Jamie

|||

I spect that you understand my english. About this problem it isn′t that Dynamic Properties don't like Inserts the problem is the messages that SQL return when you make an insert. You have to add to your code "SET NOCOUNT ON " to ommit the messages.

DTS "The Parameter Is Incorrect" Error

I'm running SqlServer 2000 and am trying to write a DTS. For some reason I keep getting that error for my Dynamic Property Task and not sure why. I've tried giving my Package a password like was mentioned in another thread on here, but that didn't work either.

I'm trying to dynamically set a Data Source for an Excel connected, using a query.

Query:

DECLARE @.directory VARCHAR(1000)
SET @.directory = 'C:\'
EXEC [Legacy].[dbo].[spr_GetNewestExcel] @.directory

The Store Procedure:

CREATE PROCEDURE spr_GetNewestExcel
(
@.directory VARCHAR(1000)
)
AS
BEGIN
DECLARE @.cmdParam VARCHAR(8000)

--Temp Table that saves the output of a DOS command.
CREATE TABLE #output
(
output VARCHAR(300)
)

--Temp Table that saves the output of a "dir" DOS output, in parts.
CREATE TABLE #files
(
fileDate VARCHAR(20),
fileSize VARCHAR(20),
fileName VARCHAR(100)
)

--Sets the command parameter
SET @.cmdParam = 'dir "' + @.directory + '"'

--Inserts the output of the above DOS command into a table
INSERT INTO #output
EXEC master..xp_cmdshell @.cmdParam

--Parses the #output table for the files with the correct extension and breaks it up into
--Date, Size, and Name.
INSERT INTO #files
SELECT
SUBSTRING(output, 0, 21) As fileDate,
LTRIM(SUBSTRING(output, 21, 18)) As fileSize,
SUBSTRING(output, 40, LEN(output))
FROM #output
WHERE output LIKE '%.xls'

--Displays the values
SELECT TOP 1
CASE
WHEN SUBSTRING(@.directory, LEN(@.directory), 1) = '\' THEN @.directory + fileName
ELSE @.directory + '\' + fileName
END AS fileName
FROM #files ORDER BY fileDate DESC

DROP TABLE #output
DROP TABLE #files
END

The Stored Procedure runs great on its own, it displays a list of filenames with the .xls extension in the desired directory, which is what I want. It should put the first in the list((if there is more than one)) as the datasource, but it doesn't.

When I go to the "Add/Edit Assignment" window for it, and push "Refresh" to preview it, it gives me a "The Parameter Is Incorrect".

Any help would be much appreciated, this has had me stumped for quite some time.

peace,
CR
you're in the wrong forum. this forum is about ssis, not dts.|||fair enough, I saw other threads similar to this one, so I posted here. And since the description mentions data transforms, seemed fitting. Though feel free to point me in the right direction since the other boards don't seem anywhere near as fitting as this one. Least not to me.|||Well after a while I finally determined what was wrong, but I must say, what a vague error.

Anyway, apparently Dynamic Properties don't like when you use Inserts, that is all.

Thanks for reading!

peace,
CR|||

There is no forum for DTS but the old newsgroup is still alive and well: microsoft.public.sqlserver.dts

-Jamie

|||

There is no forum for DTS but the old newsgroup is still alive and well: microsoft.public.sqlserver.dts

-Jamie

|||

I spect that you understand my english. About this problem it isn′t that Dynamic Properties don't like Inserts the problem is the messages that SQL return when you make an insert. You have to add to your code "SET NOCOUNT ON " to ommit the messages.

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
|

DST and Notification Services

I need some clarification on Daylight Saving Time changes for SQL
Server 2005.
I read MicroSoft Article 931815.
Per the doc, I have determined that I have Notification Services
installed (determined via registry).
However, I do not seem to have any Notification Services to stop (step
1) and not sure what name to use for NSMainDatabase (running the osql
script in step 5).
>From the SQL Server Management Studio, I can determine that I have
version 9.0.242.0 of Notification Services, but it tells me that NO
instances are registered on this machine.
So, please advise if you have any input. I am about 80% confident that
I don't need to do this fix.
Thanks,
RogerIf you do not have an instance of Notification Services deployed, then you
do not have to worry about patching. The binary files of Notification
Services are not affected. You have to apply the update only when you create
an instance. If there are no services running and no NS* databases, then
really nothing to do. Also, for instances deployed after SQL Server 2005 SP2
is installed you do not have to do anything, as instances created with SP2
will have the correct DST info.
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||On Mar 6, 2:59 pm, "Plamen Ratchev" <Pla...@.SQLStudio.com> wrote:
> If you do not have an instance of Notification Services deployed, then you
> do not have to worry about patching. The binary files of Notification
> Services are not affected. You have to apply the update only when you crea
te
> an instance. If there are no services running and no NS* databases, then
> really nothing to do. Also, for instances deployed after SQL Server 2005 S
P2
> is installed you do not have to do anything, as instances created with SP2
> will have the correct DST info.
> HTH,
> Plamen Ratchevhttp://www.SQLStudio.com
I thank you for your comments. Much appreciated.