I'm having trouble importing a text file that was ftped down from an AS400 fixed length flat file. The text file appears to be formated correctly when I open it (1 record per line fixed format) but when I try to import it ASCII delimited it doesn't appear to be in that format and I'm not familiar at all with the AS/400 file formatting. This seems to only be a problem when the last field in the file is variable length. If the last field is a char 1 field it imports OK. Any idea on what I'm doing wrong with the import? I'm I going about this all wrong? Is there an easier way to get data into SQL Server from this AS400 flat file?
Any help would be greatly appreciated!!
Thanks,
DeidreDo you have IBM Client Access installed on your SQL Server? If so, you can create an ODBC connection to the AS/400. I do this for several AS/400 files at work and it couldn't be easier.|||No I don't have IBM Client Access installed on our SQL Server that's why we were ftping to a text file then trying to import it. I was trying to find a download for the IBM Client Access on IBM's website but I guess that's something you have to buy extra?|||I was under the impression that Client Access is something provided when you purchase an AS400. What kind of terminal emulator do your users use now to access the 400? The ODBC connection is what you really need more than all of the emulation that Client Access provides.
I hope I'm not beating around the issue here, but if you can get an ODBC connection, it would be much easier than the text file download.|||No thanks for the help! I think that would be alot easier too - if I can get the ODBC connection.
I know nothing about the AS400 side and I think that is my main problem. There is another group that handles the AS400 system and I just need a file they created to import into my system. I've been trying to get information from the other group with not alot of luck that is why I was trying the text file download. I will try to see if they can get me Client Access.|||You haven't mentioned how your doing the "import"
Is this AS400 file from DB2?
Also, what's the lrecl and how many rows?|||Do you have a file layout for the AS400 file? If the file is fixed width you will have to set the width of each column manually based on the file layout.|||You mention ascii delimited when referencing dts, but you also mention that the file is fixed length file - which is it. You have the option to import a fixed length field file as well in dts.|||Even easier...
If the file is less than 8000 bytes...dump it in to a temp table with 1 column..you can then parse it out, do validity checks, ect...
CREATE TABLE myTable99(Col1 varchar(8000))
GO
BULK INSERT myTable99 FROM 'd:\data\filename.txt'
GO
SELECT COUNT(*) FROM myTable99
GO|||I am importing using the dts wizard. I did try the fixed length ANSI option and it doesn't work either. It seems that all the fields are fixed length except the last field but none of the row delimiter options work for me. When I choose {CR}{LF} it catches the end of the first row then wraps the other rows that are longer than the first row. If I try any of the other row delimiter options I get this message "Could not find the selected row delimiter within the first 8 kb of data. Is the selected row delimiter valid?" but I see a double vertical line separating each row.
I am having this problem on all the files except 1 that I am trying to import from the AS400. The one that I am not having problems with last field is a 1 char field not variable length.
The AS400 guy said the file is not DB2 its just a AS400 fixed length flat file and I have the file format for all the files. The simples one is one with 2 fields- 1st field 2 chars - 2nd field 30 chars - variable row count.
Any other suggestion?
Thanks again for all the suggestions,
Deidre|||So it looks like it is pipe dilimited. Have you tried that delimimter yet?|||I don't see pipe as a row delimiter option - I've tried all the options I see - {CR}{LF}, {CR},{LF},Semicolon, Comma, Tab, Vertical Bar and none of them work.
Deidre|||In the Row Delimiter drop down you can add delimiters. Just click in the drop down and put in a pipe.|||Although vertical bar may be the same as pipe. Just to see if you can get it in a database, try importing it into access. The wizard is a little easier.|||It would be a lot easier if you could post a sample...
You say
but I see a double vertical line separating each row.
Do you mean row? Or Column? Quite different you know...
How much data are we talking about?
Did you try my example...should be pretty easy|||SHICKS -I tried entering the pipe delimiter in the row delimiter but it still didn't work. It imported in to Access great just used the wizard fixed width options and set the column breaks after the 2 and 32 chars.
--
Brett -- This works but I still don't see why I can't do it will a dts import:
CREATE TABLE #tblTemp(Col1 varchar(32))
GO
BULK INSERT #tblTemp FROM 'C:\test.txt'
GO
SELECT * FROM #tblTemp
insert into tblStageMajor
(sMajorCode,
vsMajorDescription,
dtImportDate)
select substring(Col1,1,2), substring(Col1,3,30),getdate()
from #tblTemp
drop table #tblTemp
I attached the file that goes along with this as an example of what I'm trying to import. I have several different files but this is the smallest. 2 columns 32 char width total - 27 rows - 1st column 2 chars -2nd column 30 chars.
The largest files is still only 147 char total row width, variable rows count(around 10,000 is normal), 12 fields.
Thanks,
Deidre|||I don't see no stinkin pipes (|)|||but I see a double vertical line separating each row
I agree with Brett - no vertical lines in that file. It is a little early in my timezone to see double anything yet - you haven't been sipping from the champagne bottle a little early, have you ? :-)|||Originally posted by rnealejr
I agree with Brett - no vertical lines in that file. It is a little early in my timezone to see double anything yet - you haven't been sipping from the champagne bottle a little early, have you ? :-)
And what time zone is that?
Also I tried to import that file...if you play around with the end of line parameter, you'll see that there's some kind of garbage at the end...|||OK I loaded it using my method, and everything looks fine
bizzare...try this
select LEN(Col1) from #tblTemp
SELECT '123456789012345678901234567890'
UNION ALL
select Col1 from #tblTemp
I thought I'd see extra bytes at the end...but I don't..
Hey ANOTHER reason not to use DTS...they just keep stacking up...|||That garbage is 0D0A - typical carriage return/linefeed ending|||Well DTS doesn't like it...did you try to import the test.txt file?|||Yep. Just add the control characters to the delimiter area.
dsweatman - Try the following in your dts script as a transformation:
Function Main()
strSource=DTSSource("Col001")
DTSDestination("Col001") = left(strSource,2)
DTSDestination("Col002") = right(strSource,len(strSource) - 2)
Main = DTSTransformStat_OK
End Function|||Check this out...I added 1 line...it looks like it's taking the line length definition from the first row...
bizzaro...
EDIT: And I just imported it Excel and Access...NO PROBLEMS...
DTS, you quirky little thing...
I even cut and pasted all the rows in to another file..same problem...|||I got it to work if I set the text file options to comma delimited.
and did the following in the transformation.
Originally posted by rnealejr
Function Main()
strSource=DTSSource("Col001")
DTSDestination("Col001") = left(strSource,2)
DTSDestination("Col002") = right(strSource,len(strSource) - 2)
Main = DTSTransformStat_OK
End Function
Thanks everyone!!|||Happy to help and Happy New Year !
Actually, in your example, any of the delimiters work - as long as your row delimter is set for cr/lf.|||Originally posted by dsweatman
I'm having trouble importing a text file that was ftped down from an AS400 fixed length flat file. The text file appears to be formated correctly when I open it (1 record per line fixed format) but when I try to import it ASCII delimited it doesn't appear to be in that format and I'm not familiar at all with the AS/400 file formatting. This seems to only be a problem when the last field in the file is variable length. If the last field is a char 1 field it imports OK. Any idea on what I'm doing wrong with the import? I'm I going about this all wrong? Is there an easier way to get data into SQL Server from this AS400 flat file?
Any help would be greatly appreciated!!
Thanks,
Deidre
hi,
first you confirm have specified carrage return while creating text file
in AS/400, If you are using client access system will take care
regarding carriage return
if you are using CPYFRMIMPF than you should specify carriage return
regards
NANAIAH
MUMBAI
Showing posts with label trouble. Show all posts
Showing posts with label trouble. Show all posts
Sunday, March 25, 2012
Wednesday, March 21, 2012
Friday, February 17, 2012
dts import/export problems
Im having trouble trying to export data from one sql server instance to another on a separate server.
i choose 'sql server authentication' as the option for destination but my username / password is not accepted. user failed error. i presume this would be a username / password within the 'security/logins' of the destination server? i have access to this server so know the username / password is set up in there.
why is this not working? i have also tried an import from the destination server, but this creates the same login failed error.
how can i connect between these 2 servers and run this export?
--
Message posted via http://www.sqlmonster.comMark
What is amount of data you are moving?
Have a look at BACKUP/RESTORE commands.
Have you specified an output file to see what is exactly the error?
"Mark Evans via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:8aeb166002c84fa382c7595cae5484b7@.SQLMonster.com...
> Im having trouble trying to export data from one sql server instance to
another on a separate server.
> i choose 'sql server authentication' as the option for destination but my
username / password is not accepted. user failed error. i presume this would
be a username / password within the 'security/logins' of the destination
server? i have access to this server so know the username / password is set
up in there.
> why is this not working? i have also tried an import from the destination
server, but this creates the same login failed error.
> how can i connect between these 2 servers and run this export?
> --
> Message posted via http://www.sqlmonster.com|||im only moving a table with about 150 entries, hence trying to selectively export this table rather than backup / restore the whole database, which i would rather not do as im unsure the data in every table has been kept up to date.
not sure what you mean by output file but error i get when i click on refresh is -
Error Description: Login failed for user 'EVANSM'
Context: Error during initialization of the provider.
i presume that when i click refresh the login should be verified and i can select the server / database names from the dropdowns?
--
Message posted via http://www.sqlmonster.com|||Mark
Does this user have login on desination server, does he/she exist there?
"Mark Evans via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:663fad94a4154d2dbd1d79e600338f64@.SQLMonster.com...
> im only moving a table with about 150 entries, hence trying to selectively
export this table rather than backup / restore the whole database, which i
would rather not do as im unsure the data in every table has been kept up to
date.
> not sure what you mean by output file but error i get when i click on
refresh is -
> Error Description: Login failed for user 'EVANSM'
> Context: Error during initialization of the provider.
> i presume that when i click refresh the login should be verified and i can
select the server / database names from the dropdowns?
> --
> Message posted via http://www.sqlmonster.com|||Yes, within enterprise manager... security / logins, or do you mean as a windows nt user?
--
Message posted via http://www.sqlmonster.com|||Does the user have any permissions on the table?
Look, I try get more info because I think it is matter of user's permission
or something wrong with a connection.
"Mark Evans via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ca8ffd6abbee4d229dfca4252a329070@.SQLMonster.com...
> Yes, within enterprise manager... security / logins, or do you mean as a
windows nt user?
> --
> Message posted via http://www.sqlmonster.com|||yes, this user has access to the relevant database and all admin roles are ticked.
is there anything within sql server that i need to alter to allow remote connections via dts import / export?
--
Message posted via http://www.sqlmonster.com|||This is a multi-part message in MIME format.
--=_NextPart_000_0735_01C4F23C.763D39A0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
Unless you have correctly configured for Kerberos Delegation in the Active
Directory, you will have to create a SQL Server Authenticated login account
on the destination SQL Server to use, not the Windows Account. Also, the
destination SQL Server will have to be configured for mixed-mode
authentication.
Check with your DBA/AD Admin about the current configurations.
Sincerely,
Anthony Thomas
"Mark Evans via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:8aeb166002c84fa382c7595cae5484b7@.SQLMonster.com...
Im having trouble trying to export data from one sql server instance to
another on a separate server.
i choose 'sql server authentication' as the option for destination but my
username / password is not accepted. user failed error. i presume this would
be a username / password within the 'security/logins' of the destination
server? i have access to this server so know the username / password is set
up in there.
why is this not working? i have also tried an import from the destination
server, but this creates the same login failed error.
how can i connect between these 2 servers and run this export?
--
Message posted via http://www.sqlmonster.com
--=_NextPart_000_0735_01C4F23C.763D39A0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Unless you have correctly configured =for Kerberos Delegation in the Active Directory, you will have to create a SQL Server =Authenticated login account on the destination SQL Server to use, not =the Windows Account. Also, the destination SQL Server will have to be configured for mixed-mode authentication.
Check with your DBA/AD Admin about =the current configurations.
Sincerely,
Anthony Thomas
--
"Mark Evans via SQLMonster.com" =wrote in message news:8aeb16=6002c84fa382c7595cae5484b7@.SQLMonster.com...Im having trouble trying to export data from one sql server instance to =another on a separate server.i choose 'sql server authentication' as =the option for destination but my username / password is not accepted. =user failed error. i presume this would be a username / password within the 'security/logins' of the destination server? i have access to this =server so know the username / password is set up in there.why is this =not working? i have also tried an import from the destination server, but =this creates the same login failed error.how can i connect between =these 2 servers and run this export?-- Message posted via http://www.sqlmonster.com
--=_NextPart_000_0735_01C4F23C.763D39A0--|||it is configured for mixed mode authentication already.
in security there is a 'start and run sql server in the following account' option that has a username and password entered.
is this what you mean by a SQL Server Authenticated login account?
--
Message posted via http://www.sqlmonster.com
i choose 'sql server authentication' as the option for destination but my username / password is not accepted. user failed error. i presume this would be a username / password within the 'security/logins' of the destination server? i have access to this server so know the username / password is set up in there.
why is this not working? i have also tried an import from the destination server, but this creates the same login failed error.
how can i connect between these 2 servers and run this export?
--
Message posted via http://www.sqlmonster.comMark
What is amount of data you are moving?
Have a look at BACKUP/RESTORE commands.
Have you specified an output file to see what is exactly the error?
"Mark Evans via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:8aeb166002c84fa382c7595cae5484b7@.SQLMonster.com...
> Im having trouble trying to export data from one sql server instance to
another on a separate server.
> i choose 'sql server authentication' as the option for destination but my
username / password is not accepted. user failed error. i presume this would
be a username / password within the 'security/logins' of the destination
server? i have access to this server so know the username / password is set
up in there.
> why is this not working? i have also tried an import from the destination
server, but this creates the same login failed error.
> how can i connect between these 2 servers and run this export?
> --
> Message posted via http://www.sqlmonster.com|||im only moving a table with about 150 entries, hence trying to selectively export this table rather than backup / restore the whole database, which i would rather not do as im unsure the data in every table has been kept up to date.
not sure what you mean by output file but error i get when i click on refresh is -
Error Description: Login failed for user 'EVANSM'
Context: Error during initialization of the provider.
i presume that when i click refresh the login should be verified and i can select the server / database names from the dropdowns?
--
Message posted via http://www.sqlmonster.com|||Mark
Does this user have login on desination server, does he/she exist there?
"Mark Evans via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:663fad94a4154d2dbd1d79e600338f64@.SQLMonster.com...
> im only moving a table with about 150 entries, hence trying to selectively
export this table rather than backup / restore the whole database, which i
would rather not do as im unsure the data in every table has been kept up to
date.
> not sure what you mean by output file but error i get when i click on
refresh is -
> Error Description: Login failed for user 'EVANSM'
> Context: Error during initialization of the provider.
> i presume that when i click refresh the login should be verified and i can
select the server / database names from the dropdowns?
> --
> Message posted via http://www.sqlmonster.com|||Yes, within enterprise manager... security / logins, or do you mean as a windows nt user?
--
Message posted via http://www.sqlmonster.com|||Does the user have any permissions on the table?
Look, I try get more info because I think it is matter of user's permission
or something wrong with a connection.
"Mark Evans via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ca8ffd6abbee4d229dfca4252a329070@.SQLMonster.com...
> Yes, within enterprise manager... security / logins, or do you mean as a
windows nt user?
> --
> Message posted via http://www.sqlmonster.com|||yes, this user has access to the relevant database and all admin roles are ticked.
is there anything within sql server that i need to alter to allow remote connections via dts import / export?
--
Message posted via http://www.sqlmonster.com|||This is a multi-part message in MIME format.
--=_NextPart_000_0735_01C4F23C.763D39A0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
Unless you have correctly configured for Kerberos Delegation in the Active
Directory, you will have to create a SQL Server Authenticated login account
on the destination SQL Server to use, not the Windows Account. Also, the
destination SQL Server will have to be configured for mixed-mode
authentication.
Check with your DBA/AD Admin about the current configurations.
Sincerely,
Anthony Thomas
"Mark Evans via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:8aeb166002c84fa382c7595cae5484b7@.SQLMonster.com...
Im having trouble trying to export data from one sql server instance to
another on a separate server.
i choose 'sql server authentication' as the option for destination but my
username / password is not accepted. user failed error. i presume this would
be a username / password within the 'security/logins' of the destination
server? i have access to this server so know the username / password is set
up in there.
why is this not working? i have also tried an import from the destination
server, but this creates the same login failed error.
how can i connect between these 2 servers and run this export?
--
Message posted via http://www.sqlmonster.com
--=_NextPart_000_0735_01C4F23C.763D39A0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Unless you have correctly configured =for Kerberos Delegation in the Active Directory, you will have to create a SQL Server =Authenticated login account on the destination SQL Server to use, not =the Windows Account. Also, the destination SQL Server will have to be configured for mixed-mode authentication.
Check with your DBA/AD Admin about =the current configurations.
Sincerely,
Anthony Thomas
--
"Mark Evans via SQLMonster.com"
--=_NextPart_000_0735_01C4F23C.763D39A0--|||it is configured for mixed mode authentication already.
in security there is a 'start and run sql server in the following account' option that has a username and password entered.
is this what you mean by a SQL Server Authenticated login account?
--
Message posted via http://www.sqlmonster.com
dts import/export problems
Im having trouble trying to export data from one sql server instance to another on a separate server.
i choose 'sql server authentication' as the option for destination but my username / password is not accepted. user failed error. i presume this would be a username / password within the 'security/logins' of the destination server? i have access to this s
erver so know the username / password is set up in there.
why is this not working? i have also tried an import from the destination server, but this creates the same login failed error.
how can i connect between these 2 servers and run this export?
Message posted via http://www.sqlmonster.com
Mark
What is amount of data you are moving?
Have a look at BACKUP/RESTORE commands.
Have you specified an output file to see what is exactly the error?
"Mark Evans via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:8aeb166002c84fa382c7595cae5484b7@.SQLMonster.c om...
> Im having trouble trying to export data from one sql server instance to
another on a separate server.
> i choose 'sql server authentication' as the option for destination but my
username / password is not accepted. user failed error. i presume this would
be a username / password within the 'security/logins' of the destination
server? i have access to this server so know the username / password is set
up in there.
> why is this not working? i have also tried an import from the destination
server, but this creates the same login failed error.
> how can i connect between these 2 servers and run this export?
> --
> Message posted via http://www.sqlmonster.com
|||im only moving a table with about 150 entries, hence trying to selectively export this table rather than backup / restore the whole database, which i would rather not do as im unsure the data in every table has been kept up to date.
not sure what you mean by output file but error i get when i click on refresh is -
Error Description: Login failed for user 'EVANSM'
Context: Error during initialization of the provider.
i presume that when i click refresh the login should be verified and i can select the server / database names from the dropdowns?
Message posted via http://www.sqlmonster.com
|||Mark
Does this user have login on desination server, does he/she exist there?
"Mark Evans via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:663fad94a4154d2dbd1d79e600338f64@.SQLMonster.c om...
> im only moving a table with about 150 entries, hence trying to selectively
export this table rather than backup / restore the whole database, which i
would rather not do as im unsure the data in every table has been kept up to
date.
> not sure what you mean by output file but error i get when i click on
refresh is -
> Error Description: Login failed for user 'EVANSM'
> Context: Error during initialization of the provider.
> i presume that when i click refresh the login should be verified and i can
select the server / database names from the dropdowns?
> --
> Message posted via http://www.sqlmonster.com
|||Yes, within enterprise manager... security / logins, or do you mean as a windows nt user?
Message posted via http://www.sqlmonster.com
|||Does the user have any permissions on the table?
Look, I try get more info because I think it is matter of user's permission
or something wrong with a connection.
"Mark Evans via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ca8ffd6abbee4d229dfca4252a329070@.SQLMonster.c om...
> Yes, within enterprise manager... security / logins, or do you mean as a
windows nt user?
> --
> Message posted via http://www.sqlmonster.com
|||yes, this user has access to the relevant database and all admin roles are ticked.
is there anything within sql server that i need to alter to allow remote connections via dts import / export?
Message posted via http://www.sqlmonster.com
|||Unless you have correctly configured for Kerberos Delegation in the Active
Directory, you will have to create a SQL Server Authenticated login account
on the destination SQL Server to use, not the Windows Account. Also, the
destination SQL Server will have to be configured for mixed-mode
authentication.
Check with your DBA/AD Admin about the current configurations.
Sincerely,
Anthony Thomas
"Mark Evans via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:8aeb166002c84fa382c7595cae5484b7@.SQLMonster.c om...
Im having trouble trying to export data from one sql server instance to
another on a separate server.
i choose 'sql server authentication' as the option for destination but my
username / password is not accepted. user failed error. i presume this would
be a username / password within the 'security/logins' of the destination
server? i have access to this server so know the username / password is set
up in there.
why is this not working? i have also tried an import from the destination
server, but this creates the same login failed error.
how can i connect between these 2 servers and run this export?
Message posted via http://www.sqlmonster.com
|||it is configured for mixed mode authentication already.
in security there is a 'start and run sql server in the following account' option that has a username and password entered.
is this what you mean by a SQL Server Authenticated login account?
Message posted via http://www.sqlmonster.com
i choose 'sql server authentication' as the option for destination but my username / password is not accepted. user failed error. i presume this would be a username / password within the 'security/logins' of the destination server? i have access to this s
erver so know the username / password is set up in there.
why is this not working? i have also tried an import from the destination server, but this creates the same login failed error.
how can i connect between these 2 servers and run this export?
Message posted via http://www.sqlmonster.com
Mark
What is amount of data you are moving?
Have a look at BACKUP/RESTORE commands.
Have you specified an output file to see what is exactly the error?
"Mark Evans via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:8aeb166002c84fa382c7595cae5484b7@.SQLMonster.c om...
> Im having trouble trying to export data from one sql server instance to
another on a separate server.
> i choose 'sql server authentication' as the option for destination but my
username / password is not accepted. user failed error. i presume this would
be a username / password within the 'security/logins' of the destination
server? i have access to this server so know the username / password is set
up in there.
> why is this not working? i have also tried an import from the destination
server, but this creates the same login failed error.
> how can i connect between these 2 servers and run this export?
> --
> Message posted via http://www.sqlmonster.com
|||im only moving a table with about 150 entries, hence trying to selectively export this table rather than backup / restore the whole database, which i would rather not do as im unsure the data in every table has been kept up to date.
not sure what you mean by output file but error i get when i click on refresh is -
Error Description: Login failed for user 'EVANSM'
Context: Error during initialization of the provider.
i presume that when i click refresh the login should be verified and i can select the server / database names from the dropdowns?
Message posted via http://www.sqlmonster.com
|||Mark
Does this user have login on desination server, does he/she exist there?
"Mark Evans via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:663fad94a4154d2dbd1d79e600338f64@.SQLMonster.c om...
> im only moving a table with about 150 entries, hence trying to selectively
export this table rather than backup / restore the whole database, which i
would rather not do as im unsure the data in every table has been kept up to
date.
> not sure what you mean by output file but error i get when i click on
refresh is -
> Error Description: Login failed for user 'EVANSM'
> Context: Error during initialization of the provider.
> i presume that when i click refresh the login should be verified and i can
select the server / database names from the dropdowns?
> --
> Message posted via http://www.sqlmonster.com
|||Yes, within enterprise manager... security / logins, or do you mean as a windows nt user?
Message posted via http://www.sqlmonster.com
|||Does the user have any permissions on the table?
Look, I try get more info because I think it is matter of user's permission
or something wrong with a connection.
"Mark Evans via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ca8ffd6abbee4d229dfca4252a329070@.SQLMonster.c om...
> Yes, within enterprise manager... security / logins, or do you mean as a
windows nt user?
> --
> Message posted via http://www.sqlmonster.com
|||yes, this user has access to the relevant database and all admin roles are ticked.
is there anything within sql server that i need to alter to allow remote connections via dts import / export?
Message posted via http://www.sqlmonster.com
|||Unless you have correctly configured for Kerberos Delegation in the Active
Directory, you will have to create a SQL Server Authenticated login account
on the destination SQL Server to use, not the Windows Account. Also, the
destination SQL Server will have to be configured for mixed-mode
authentication.
Check with your DBA/AD Admin about the current configurations.
Sincerely,
Anthony Thomas
"Mark Evans via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:8aeb166002c84fa382c7595cae5484b7@.SQLMonster.c om...
Im having trouble trying to export data from one sql server instance to
another on a separate server.
i choose 'sql server authentication' as the option for destination but my
username / password is not accepted. user failed error. i presume this would
be a username / password within the 'security/logins' of the destination
server? i have access to this server so know the username / password is set
up in there.
why is this not working? i have also tried an import from the destination
server, but this creates the same login failed error.
how can i connect between these 2 servers and run this export?
Message posted via http://www.sqlmonster.com
|||it is configured for mixed mode authentication already.
in security there is a 'start and run sql server in the following account' option that has a username and password entered.
is this what you mean by a SQL Server Authenticated login account?
Message posted via http://www.sqlmonster.com
dts import/export problems
Im having trouble trying to export data from one sql server instance to anot
her on a separate server.
i choose 'sql server authentication' as the option for destination but my us
ername / password is not accepted. user failed error. i presume this would b
e a username / password within the 'security/logins' of the destination serv
er? i have access to this s
erver so know the username / password is set up in there.
why is this not working? i have also tried an import from the destination se
rver, but this creates the same login failed error.
how can i connect between these 2 servers and run this export?
Message posted via http://www.droptable.comyes, this user has access to the relevant database and all admin roles are t
icked.
is there anything within sql server that i need to alter to allow remote con
nections via dts import / export?
Message posted via http://www.droptable.com|||Unless you have correctly configured for Kerberos Delegation in the Active
Directory, you will have to create a SQL Server Authenticated login account
on the destination SQL Server to use, not the Windows Account. Also, the
destination SQL Server will have to be configured for mixed-mode
authentication.
Check with your DBA/AD Admin about the current configurations.
Sincerely,
Anthony Thomas
"Mark Evans via droptable.com" <forum@.droptable.com> wrote in message
news:8aeb166002c84fa382c7595cae5484b7@.SQ
droptable.com...
Im having trouble trying to export data from one sql server instance to
another on a separate server.
i choose 'sql server authentication' as the option for destination but my
username / password is not accepted. user failed error. i presume this would
be a username / password within the 'security/logins' of the destination
server? i have access to this server so know the username / password is set
up in there.
why is this not working? i have also tried an import from the destination
server, but this creates the same login failed error.
how can i connect between these 2 servers and run this export?
Message posted via http://www.droptable.com|||it is configured for mixed mode authentication already.
in security there is a 'start and run sql server in the following account' o
ption that has a username and password entered.
is this what you mean by a SQL Server Authenticated login account?
Message posted via http://www.droptable.com|||Mark
What is amount of data you are moving?
Have a look at BACKUP/RESTORE commands.
Have you specified an output file to see what is exactly the error?
"Mark Evans via droptable.com" <forum@.droptable.com> wrote in message
news:8aeb166002c84fa382c7595cae5484b7@.SQ
droptable.com...
> Im having trouble trying to export data from one sql server instance to
another on a separate server.
> i choose 'sql server authentication' as the option for destination but my
username / password is not accepted. user failed error. i presume this would
be a username / password within the 'security/logins' of the destination
server? i have access to this server so know the username / password is set
up in there.
> why is this not working? i have also tried an import from the destination
server, but this creates the same login failed error.
> how can i connect between these 2 servers and run this export?
> --
> Message posted via http://www.droptable.com|||im only moving a table with about 150 entries, hence trying to selectively e
xport this table rather than backup / restore the whole database, which i wo
uld rather not do as im unsure the data in every table has been kept up to d
ate.
not sure what you mean by output file but error i get when i click on refres
h is -
Error Description: Login failed for user 'EVANSM'
Context: Error during initialization of the provider.
i presume that when i click refresh the login should be verified and i can s
elect the server / database names from the dropdowns?
Message posted via http://www.droptable.com|||Mark
Does this user have login on desination server, does he/she exist there?
"Mark Evans via droptable.com" <forum@.droptable.com> wrote in message
news:663fad94a4154d2dbd1d79e600338f64@.SQ
droptable.com...
> im only moving a table with about 150 entries, hence trying to selectively
export this table rather than backup / restore the whole database, which i
would rather not do as im unsure the data in every table has been kept up to
date.
> not sure what you mean by output file but error i get when i click on
refresh is -
> Error Description: Login failed for user 'EVANSM'
> Context: Error during initialization of the provider.
> i presume that when i click refresh the login should be verified and i can
select the server / database names from the dropdowns?
> --
> Message posted via http://www.droptable.com|||Yes, within enterprise manager... security / logins, or do you mean as a win
dows nt user?
Message posted via http://www.droptable.com|||Does the user have any permissions on the table?
Look, I try get more info because I think it is matter of user's permission
or something wrong with a connection.
"Mark Evans via droptable.com" <forum@.droptable.com> wrote in message
news:ca8ffd6abbee4d229dfca4252a329070@.SQ
droptable.com...
> Yes, within enterprise manager... security / logins, or do you mean as a
windows nt user?
> --
> Message posted via http://www.droptable.com
her on a separate server.
i choose 'sql server authentication' as the option for destination but my us
ername / password is not accepted. user failed error. i presume this would b
e a username / password within the 'security/logins' of the destination serv
er? i have access to this s
erver so know the username / password is set up in there.
why is this not working? i have also tried an import from the destination se
rver, but this creates the same login failed error.
how can i connect between these 2 servers and run this export?
Message posted via http://www.droptable.comyes, this user has access to the relevant database and all admin roles are t
icked.
is there anything within sql server that i need to alter to allow remote con
nections via dts import / export?
Message posted via http://www.droptable.com|||Unless you have correctly configured for Kerberos Delegation in the Active
Directory, you will have to create a SQL Server Authenticated login account
on the destination SQL Server to use, not the Windows Account. Also, the
destination SQL Server will have to be configured for mixed-mode
authentication.
Check with your DBA/AD Admin about the current configurations.
Sincerely,
Anthony Thomas
"Mark Evans via droptable.com" <forum@.droptable.com> wrote in message
news:8aeb166002c84fa382c7595cae5484b7@.SQ
droptable.com...
Im having trouble trying to export data from one sql server instance to
another on a separate server.
i choose 'sql server authentication' as the option for destination but my
username / password is not accepted. user failed error. i presume this would
be a username / password within the 'security/logins' of the destination
server? i have access to this server so know the username / password is set
up in there.
why is this not working? i have also tried an import from the destination
server, but this creates the same login failed error.
how can i connect between these 2 servers and run this export?
Message posted via http://www.droptable.com|||it is configured for mixed mode authentication already.
in security there is a 'start and run sql server in the following account' o
ption that has a username and password entered.
is this what you mean by a SQL Server Authenticated login account?
Message posted via http://www.droptable.com|||Mark
What is amount of data you are moving?
Have a look at BACKUP/RESTORE commands.
Have you specified an output file to see what is exactly the error?
"Mark Evans via droptable.com" <forum@.droptable.com> wrote in message
news:8aeb166002c84fa382c7595cae5484b7@.SQ
droptable.com...
> Im having trouble trying to export data from one sql server instance to
another on a separate server.
> i choose 'sql server authentication' as the option for destination but my
username / password is not accepted. user failed error. i presume this would
be a username / password within the 'security/logins' of the destination
server? i have access to this server so know the username / password is set
up in there.
> why is this not working? i have also tried an import from the destination
server, but this creates the same login failed error.
> how can i connect between these 2 servers and run this export?
> --
> Message posted via http://www.droptable.com|||im only moving a table with about 150 entries, hence trying to selectively e
xport this table rather than backup / restore the whole database, which i wo
uld rather not do as im unsure the data in every table has been kept up to d
ate.
not sure what you mean by output file but error i get when i click on refres
h is -
Error Description: Login failed for user 'EVANSM'
Context: Error during initialization of the provider.
i presume that when i click refresh the login should be verified and i can s
elect the server / database names from the dropdowns?
Message posted via http://www.droptable.com|||Mark
Does this user have login on desination server, does he/she exist there?
"Mark Evans via droptable.com" <forum@.droptable.com> wrote in message
news:663fad94a4154d2dbd1d79e600338f64@.SQ
droptable.com...
> im only moving a table with about 150 entries, hence trying to selectively
export this table rather than backup / restore the whole database, which i
would rather not do as im unsure the data in every table has been kept up to
date.
> not sure what you mean by output file but error i get when i click on
refresh is -
> Error Description: Login failed for user 'EVANSM'
> Context: Error during initialization of the provider.
> i presume that when i click refresh the login should be verified and i can
select the server / database names from the dropdowns?
> --
> Message posted via http://www.droptable.com|||Yes, within enterprise manager... security / logins, or do you mean as a win
dows nt user?
Message posted via http://www.droptable.com|||Does the user have any permissions on the table?
Look, I try get more info because I think it is matter of user's permission
or something wrong with a connection.
"Mark Evans via droptable.com" <forum@.droptable.com> wrote in message
news:ca8ffd6abbee4d229dfca4252a329070@.SQ
droptable.com...
> Yes, within enterprise manager... security / logins, or do you mean as a
windows nt user?
> --
> Message posted via http://www.droptable.com
Tuesday, February 14, 2012
DTS import does not import all rows / records
Hi,
I am having trouble importing data from an excel spreadsheet into MS
SQL Server 2000 using DTS Wizard. The DTS import process is
successfull, no errors, but only 50 rows of approx. 1500 rows of data
are imported. I tried to remove 20 rows in the excel spreadsheet in
the interval row 0-50. When i later ran the import, only 30 rows were
imported. I deleted almost every row in the interval 0-50, with the
result of the import having 0 rows imported (but job ran
successfully). I decided to delete rows 0-100 in the spreadsheet in
order to see if the resolved the problem, but it didn't. As I
suspected something in the excel file to be the cause, I exported the
excel spreadsheeet to a tab delimited textfile, with only one row. A
DTS import resulted in importing approx 100 rows, double the amount of
the textfile, but the other 1400 rows were not imported. The data in
the column is containing numeric values only.
Please help me! What could possibly be the cause of DTS skipping rows
like that. DTS doesn't feel reliable at all :/
Regards,
BjrnYou may want to check your spreadsheet to see if it has a named range
and if that is what you are using in DTS. If the named range only
includes rows 1-50 then DTS will only import those rows.
I'm not sure what you were doing with the text file, since you said
that you exported one row and it imported 100.
HTH,
-Tom.|||Maybe it would be a better idea to avoid the DTS Wizard. Make a local
package on your server and you will have much more control over it.
And if an error occures, DTS will tell you what kind of error that is.
Elaksomfan wrote:
> Hi,
> I am having trouble importing data from an excel spreadsheet into MS
> SQL Server 2000 using DTS Wizard. The DTS import process is
> successfull, no errors, but only 50 rows of approx. 1500 rows of data
> are imported. I tried to remove 20 rows in the excel spreadsheet in
> the interval row 0-50. When i later ran the import, only 30 rows were
> imported. I deleted almost every row in the interval 0-50, with the
> result of the import having 0 rows imported (but job ran
> successfully). I decided to delete rows 0-100 in the spreadsheet in
> order to see if the resolved the problem, but it didn't. As I
> suspected something in the excel file to be the cause, I exported the
> excel spreadsheeet to a tab delimited textfile, with only one row. A
> DTS import resulted in importing approx 100 rows, double the amount of
> the textfile, but the other 1400 rows were not imported. The data in
> the column is containing numeric values only.
> Please help me! What could possibly be the cause of DTS skipping rows
> like that. DTS doesn't feel reliable at all :/
> Regards,
> Bjrn|||First save the excel sheet in csv format.
Then use DTS to import form the csv file.
Tawfiq.
Elaksomfan wrote:
> Hi,
> I am having trouble importing data from an excel spreadsheet into MS
> SQL Server 2000 using DTS Wizard. The DTS import process is
> successfull, no errors, but only 50 rows of approx. 1500 rows of data
> are imported. I tried to remove 20 rows in the excel spreadsheet in
> the interval row 0-50. When i later ran the import, only 30 rows were
> imported. I deleted almost every row in the interval 0-50, with the
> result of the import having 0 rows imported (but job ran
> successfully). I decided to delete rows 0-100 in the spreadsheet in
> order to see if the resolved the problem, but it didn't. As I
> suspected something in the excel file to be the cause, I exported the
> excel spreadsheeet to a tab delimited textfile, with only one row. A
> DTS import resulted in importing approx 100 rows, double the amount
of
> the textfile, but the other 1400 rows were not imported. The data in
> the column is containing numeric values only.
> Please help me! What could possibly be the cause of DTS skipping rows
> like that. DTS doesn't feel reliable at all :/
>
> Regards,
> Bjrn
Subscribe to:
Posts (Atom)