Problem with Bulk Upload. Uploads twice.

When I run the following stored procedure I get these results. My file has
24 rows in it. However I am uploading it twice for some reason.
Stored Procedure:
CREATE PROCEDURE [dbo].[dsp_Manual_Import]
(
@.PathFileName varchar(50),
@.SQL varchar(2000)
)
AS
SET @.SQL = "BULK INSERT dbo.dtbl_Manual_Process FROM '"+@.PathFileName+"'
WITH (FIELDTERMINATOR = ',') "
EXEC (@.SQL)
return
GO
Results:
(24 row(s) affected)
(24 row(s) affected)
Stored Procedure: ILVS.dbo.dsp_Manual_Import
Return Code = 0Have you confirmed the duplicate rows were inserted by running a query
against the table afterward?
Are there any triggers on the table, perhaps it is inserting to an audit
table?
"meverts" <meverts@.discussions.microsoft.com> wrote in message
news:29839035-56E2-46E4-814F-C473CEA2E99D@.microsoft.com...
> When I run the following stored procedure I get these results. My file
> has
> 24 rows in it. However I am uploading it twice for some reason.
>
> Stored Procedure:
> CREATE PROCEDURE [dbo].[dsp_Manual_Import]
> (
> @.PathFileName varchar(50),
> @.SQL varchar(2000)
> )
> AS
>
> SET @.SQL = "BULK INSERT dbo.dtbl_Manual_Process FROM '"+@.PathFileName+"'
> WITH (FIELDTERMINATOR = ',') "
> EXEC (@.SQL)
>
> return
> GO
> Results:
> (24 row(s) affected)
>
> (24 row(s) affected)
> Stored Procedure: ILVS.dbo.dsp_Manual_Import
> Return Code = 0|||Yes I checked the table and it is importing twice. There are no triggers.
"JT" wrote:

> Have you confirmed the duplicate rows were inserted by running a query
> against the table afterward?
> Are there any triggers on the table, perhaps it is inserting to an audit
> table?
> "meverts" <meverts@.discussions.microsoft.com> wrote in message
> news:29839035-56E2-46E4-814F-C473CEA2E99D@.microsoft.com...
>
>|||Another concern is that I only am importing 24 records, and it is reporting
48.
"JT" wrote:

> Have you confirmed the duplicate rows were inserted by running a query
> against the table afterward?
> Are there any triggers on the table, perhaps it is inserting to an audit
> table?
> "meverts" <meverts@.discussions.microsoft.com> wrote in message
> news:29839035-56E2-46E4-814F-C473CEA2E99D@.microsoft.com...
>
>|||That's strange. Just as an experiment, try the following and confirm if they
all behave the same way.
#1 Execute dsp_Manual_Import manually from Query Analyzer instead of from
your application.
#2 Execute the same bulk insert command from Query Analyzer instead of
from within the stored procedure.
#3 Execute a similar bulk insert command against a different table.
"meverts" <meverts@.discussions.microsoft.com> wrote in message
news:F28E3D8A-5386-4173-998C-5865E2866C23@.microsoft.com...
> Another concern is that I only am importing 24 records, and it is
> reporting 48.
> "JT" wrote:
>

Problem with BULK INSERT in SQL Server 2005 Express

Hi, group
I'm having a problem when trying to use 'BULK INSERT' to import data from a
plain file to a SQL Server 2005 Express database; i have simplified both
the table and the data file, but i'm still unable to understand the
error... I'm using a (simplified) format file, specifically this one:
9.0
2
1 SQLINT 0 4 ";" 1 Id
""
2 SQLCHAR 2 255 "\r\n" 2 T_CODIGO
SQL_Latin1_General_CP1_CI_AS
The (simplified) data file i'm trying to import have now only one line:
1;8015
(his content, as seen in a hex editor, is exactly 313B383031350D0A)
The (simplified) database is being created with this:
create table ARTICULOS (Id int identity, T_CODIGO varchar(255))
However, when i execute this command:
BULK INSERT ARTICULOS FROM "D:\access\ARTICULOSpeq.txt" WITH
(FORMATFILE='D:\access\articulos.fmt')
i'm getting the following error:
Msg 4866, Level 16, State 7, Line 1
The bulk load failed. The column is too long in the data file for row 1,
column 2. Verify that the field terminator and row terminator are specified
correctly.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error.
The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
I have done some tests changing the terminators, adding more lines,
changing the size of the fields, but they have failed and, what is worse, i
don't understand where is the problem
Does anyone know what is happening? Any help would be welcomed...
Jose Luis BravoJose,
You should have 0 in the "prefix length" column for T_CODIGO instead of 2.
Try this format file:
9.0
2
1 SQLINT 0 4 ";" 1 Id ""
2 SQLCHAR 0 255 "\r\n" 2 T_CODIGO
SQL_Latin1_General_CP1_CI_AS
The prefix length column is used for native format imports, not text
file imports.
(This information is harder to find in the 2005 documentation,
unfortunately.)
Steve Kass
Drew University
Jose Luis Bravo wrote:

>Hi, group
>I'm having a problem when trying to use 'BULK INSERT' to import data from a
>plain file to a SQL Server 2005 Express database; i have simplified both
>the table and the data file, but i'm still unable to understand the
>error... I'm using a (simplified) format file, specifically this one:
>9.0
>2
>1 SQLINT 0 4 ";" 1 Id
>""
>2 SQLCHAR 2 255 "\r\n" 2 T_CODIGO
>SQL_Latin1_General_CP1_CI_AS
>
>The (simplified) data file i'm trying to import have now only one line:
>1;8015
>(his content, as seen in a hex editor, is exactly 313B383031350D0A)
>The (simplified) database is being created with this:
>create table ARTICULOS (Id int identity, T_CODIGO varchar(255))
>
>However, when i execute this command:
>BULK INSERT ARTICULOS FROM "D:\access\ARTICULOSpeq.txt" WITH
>(FORMATFILE='D:\access\articulos.fmt')
>i'm getting the following error:
>Msg 4866, Level 16, State 7, Line 1
>The bulk load failed. The column is too long in the data file for row 1,
>column 2. Verify that the field terminator and row terminator are specified
>correctly.
>Msg 7399, Level 16, State 1, Line 1
>The OLE DB provider "BULK" for linked server "(null)" reported an error.
>The provider did not give any information about the error.
>Msg 7330, Level 16, State 2, Line 1
>Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
>
>I have done some tests changing the terminators, adding more lines,
>changing the size of the fields, but they have failed and, what is worse, i
>don't understand where is the problem
>
>Does anyone know what is happening? Any help would be welcomed...
>
>Jose Luis Bravo
>|||El Wed, 08 Mar 2006 08:54:09 -0500, Steve Kass escribi:
Thanks, Steve! Effectively, i hadn't seen that information in the docs but,
as i had made the original format file with the bcp utility, i incorrectly
supposed it should be OK.
Thanks again.

> Jose,
> You should have 0 in the "prefix length" column for T_CODIGO instead of 2.
> Try this format file:
> 9.0
> 2
> 1 SQLINT 0 4 ";" 1 Id ""
> 2 SQLCHAR 0 255 "\r\n" 2 T_CODIGO
> SQL_Latin1_General_CP1_CI_AS
> The prefix length column is used for native format imports, not text
> file imports.
> (This information is harder to find in the 2005 documentation,
> unfortunately.)
> Steve Kass
> Drew University

Problem with BULK INSERT ASCII file into nvarchar column

Hi,

I have a problem with BULK INSERT. I created the following table:

Code Snippet

create table Test
(id char(4), name nvarchar(16), last char(1))

I am trying to bulk insert data from ASCII (not unicode) file with only two rows:

0011First name
0018Second name

Since it is a fixed length file, I am using the following format file:

Code Snippet

8.0
3
1 SQLCHAR 0 4 "" 1 ID HEBREW_CI_AS
2 SQLCHAR 0 16 "" 2 NAME HEBREW_CI_AS
3 SQLCHAR 0 0 "\r\n" 3 Last HEBREW_CI_AS

With bcp utility everything works just fine!

Code Snippet

bcp Demo.dbo.test in c:\test -T -f c:\test.fmt

But when I use BULK INSERT in the following form:

Code Snippet

BULK INSERT Test FROM 'c:\Test'
WITH
(
FORMATFILE='c:\Test.fmt',
CODEPAGE='OEM'
);

I am getting error

Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 1, column 2 (name).

Now, one interesting thing: if I change the name field from nvarchar to varchar, it is working with BULK INSERT as well.

Can anybody explain what is going on here?

I am using MS SQL 2000 and MSDE

Thanks in advance,

Eugene.

Another thing is that if I set the format file to specify row delimiter for that nvarchar field, it will also work.

Code Snippet

8.0
2
1 SQLCHAR 0 4 "" 1 ID HEBREW_CI_AS
2 SQLCHAR 0 16 "\r\n" 2 NAME HEBREW_CI_AS

But then in the real system i can't have multiple fields within the file...

|||

On SQL2005 the problem does not exist! Then it seems like a bug in SQL2000!

sql

Problem with bulk insert

Hi all,
I am newbie in all the stuff about xml importing into sql server.
What I try to do is simple. It is take an xml file and drop it into a
table. I am using VS2005, SQLXML 4.0 and SQL Server 2000 (I think
there is no problem of compatibility)
When I run my program using the SQLXMLBulkLoad4Class class,
everythings seems to run perfect and there is no errors. But when I
check my DB there isnt any record inserted.
My schema is:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="table1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ele1" type="xsd:string"/>
<xsd:element name="ele2" type="xsd:string"/>
<xsd:element name="ele3" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
My xml:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<ENGROLE>
<EROLE>
<ele1>dieg01p</ele1>
<ele2>IE01</ele2>
<ele3>IEL01</ele3>
</EROLE>
Hello,
This happens because your xml doesn't match the schema definition.
You have to update the schema as follows:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="ENGROLE" sql:isconstant="true">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="EROLE" sql:relation="Table1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ele1" type="xsd:string"/>
<xsd:element name="ele2" type="xsd:string"/>
<xsd:element name="ele3" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
I hope this helps.
Regards,
Monica Frintu
"VicToro" wrote:

> Hi all,
> I am newbie in all the stuff about xml importing into sql server.
> What I try to do is simple. It is take an xml file and drop it into a
> table. I am using VS2005, SQLXML 4.0 and SQL Server 2000 (I think
> there is no problem of compatibility)
> When I run my program using the SQLXMLBulkLoad4Class class,
> everythings seems to run perfect and there is no errors. But when I
> check my DB there isnt any record inserted.
> My schema is:
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="table1">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="ele1" type="xsd:string"/>
> <xsd:element name="ele2" type="xsd:string"/>
> <xsd:element name="ele3" type="xsd:string"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
>
> My xml:
> <?xml version="1.0" encoding="UTF-8" standalone="no"?>
> <ENGROLE>
> <EROLE>
> <ele1>dieg01p</ele1>
> <ele2>IE01</ele2>
> <ele3>IEL01</ele3>
> </EROLE>
> .
> .
> .
> </ENGROLE>
>
> and my table definition where I try to insert:
> CREATE TABLE [dbo].[table1](
> [ele1] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [ele2] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [ele3] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> ) ON [PRIMARY]
> As you see it is very simple, but I cannot get it work. Can anyone
> give a hand?
> Thank!!
>

Problem with bulk insert

Hi all,
I am newbie in all the stuff about xml importing into sql server.
What I try to do is simple. It is take an xml file and drop it into a
table. I am using VS2005, SQLXML 4.0 and SQL Server 2000 (I think
there is no problem of compatibility)
When I run my program using the SQLXMLBulkLoad4Class class,
everythings seems to run perfect and there is no errors. But when I
check my DB there isnt any record inserted.
My schema is:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="table1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ele1" type="xsd:string"/>
<xsd:element name="ele2" type="xsd:string"/>
<xsd:element name="ele3" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
My xml:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<ENGROLE>
<EROLE>
<ele1>dieg01p</ele1>
<ele2>IE01</ele2>
<ele3>IEL01</ele3>
</EROLE>Hello,
This happens because your xml doesn't match the schema definition.
You have to update the schema as follows:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="ENGROLE" sql:isconstant="true">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="EROLE" sql:relation="Table1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ele1" type="xsd:string"/>
<xsd:element name="ele2" type="xsd:string"/>
<xsd:element name="ele3" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
I hope this helps.
Regards,
--
Monica Frintu
"VicToro" wrote:

> Hi all,
> I am newbie in all the stuff about xml importing into sql server.
> What I try to do is simple. It is take an xml file and drop it into a
> table. I am using VS2005, SQLXML 4.0 and SQL Server 2000 (I think
> there is no problem of compatibility)
> When I run my program using the SQLXMLBulkLoad4Class class,
> everythings seems to run perfect and there is no errors. But when I
> check my DB there isnt any record inserted.
> My schema is:
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="table1">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="ele1" type="xsd:string"/>
> <xsd:element name="ele2" type="xsd:string"/>
> <xsd:element name="ele3" type="xsd:string"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
>
> My xml:
> <?xml version="1.0" encoding="UTF-8" standalone="no"?>
> <ENGROLE>
> <EROLE>
> <ele1>dieg01p</ele1>
> <ele2>IE01</ele2>
> <ele3>IEL01</ele3>
> </EROLE>
> .
> .
> .
> </ENGROLE>
>
> and my table definition where I try to insert:
> CREATE TABLE [dbo].[table1](
> [ele1] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [ele2] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [ele3] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> ) ON [PRIMARY]
> As you see it is very simple, but I cannot get it work. Can anyone
> give a hand?
> Thank!!
>

Problem with Bulk insert

Hello,
I use the statement "Bulk insert" to insert data from a file to one table on
SQL Server.
I do like this:
bulk insert MyBase.dbo.Clients from 'D:\DataMyBase\TableClients.txt'
wiht (FieldSeparator = ',', Rowseparator='\n')
Table Clients(num int, name char(50), comment varchar(max))
But im my file "TableClients.txt", I have one row like this:
7,Peter,'Hello,World"\n
You can see, I want to use "," in my column comment but I have this message
error:
Type mismatch...
It's because I use "," in my comment and for statement "bulk insert", it's a
field terminator and then I have a number of colums different from my table
"Client".
How Can I resolve this problem I want to use "," in column "comment"?
Thanks!I'm losing anything, I dont' see anywhere 'fieldseparator' or 'rowseparator
'
options.
"bubixx" wrote:

> Hello,
> I use the statement "Bulk insert" to insert data from a file to one table
on
> SQL Server.
> I do like this:
> bulk insert MyBase.dbo.Clients from 'D:\DataMyBase\TableClients.txt'
> wiht (FieldSeparator = ',', Rowseparator='\n')
> Table Clients(num int, name char(50), comment varchar(max))
> But im my file "TableClients.txt", I have one row like this:
> 7,Peter,'Hello,World"\n
> You can see, I want to use "," in my column comment but I have this messag
e
> error:
> Type mismatch...
> It's because I use "," in my comment and for statement "bulk insert", it's
a
> field terminator and then I have a number of colums different from my tabl
e
> "Client".
> How Can I resolve this problem I want to use "," in column "comment"?
> Thanks!
>|||You can define what is your fieldsepartore in your file txt and what is your
rowseparator.
In my case, fieldseparator=','
rowseparator='\n'
But my question, it's: can we use ',' in a string in one line of filetext'
Consult the first message which I sent for more details
"bubixx" wrote:

> Hello,
> I use the statement "Bulk insert" to insert data from a file to one table
on
> SQL Server.
> I do like this:
> bulk insert MyBase.dbo.Clients from 'D:\DataMyBase\TableClients.txt'
> wiht (FieldSeparator = ',', Rowseparator='\n')
> Table Clients(num int, name char(50), comment varchar(max))
> But im my file "TableClients.txt", I have one row like this:
> 7,Peter,'Hello,World"\n
> You can see, I want to use "," in my column comment but I have this messag
e
> error:
> Type mismatch...
> It's because I use "," in my comment and for statement "bulk insert", it's
a
> field terminator and then I have a number of colums different from my tabl
e
> "Client".
> How Can I resolve this problem I want to use "," in column "comment"?
> Thanks!
>

problem with bcp using format file

The running of my bcp (with queryout) is aborting, with the message below. A
t
first I identified that it happened only with column with NULL value, but no
w
I realize this occurrence happened in other field without NULL value.
Anyone has suggestions ? Thanks a lot
My format file has the following contents : 8.0 (version) 2 (number of
columns)
1 SQLCHAR 0 20 "\t" 1 numeroaj Latin1_General_CI_AS
2 SQLCHAR 0 54 "\r\n" 2 acervoespecializada Latin1_General_CI_AS
My bad run :
D:\Users\sql>bcp "SELECT numeroaj, acervoespecializada from
Siga.dbo.vwHerancaJa
cente" queryout C:\dts\hjacente.txt -f d:\users\sql\pgm3.fmt -Smyserver
-Umyuser -Pmypwd
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Erro de E/S ao ler o arquivo no
forma
to BCP ==> Translating... Error of I/O when read the format filewhere do you run this bcp statement, on the client or on the server itself.
"d:\" must be local to where ever you run the bcp statement.
-oj
"Adalberto Andrade" <Adalberto Andrade@.discussions.microsoft.com> wrote in
message news:014C3C25-592D-4A88-A320-325C52D49D2A@.microsoft.com...
> The running of my bcp (with queryout) is aborting, with the message below.
> At
> first I identified that it happened only with column with NULL value, but
> now
> I realize this occurrence happened in other field without NULL value.
> Anyone has suggestions ? Thanks a lot
> My format file has the following contents : 8.0 (version) 2 (number of
> columns)
> 1 SQLCHAR 0 20 "\t" 1 numeroaj Latin1_General_CI_AS
> 2 SQLCHAR 0 54 "\r\n" 2 acervoespecializada
> Latin1_General_CI_AS
> My bad run :
> D:\Users\sql>bcp "SELECT numeroaj, acervoespecializada from
> Siga.dbo.vwHerancaJa
> cente" queryout C:\dts\hjacente.txt -f d:\users\sql\pgm3.fmt -Smyserver
> -Umyuser -Pmypwd
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Erro de E/S ao ler o arquivo no
> forma
> to BCP ==> Translating... Error of I/O when read the format file|||Hi oj,
On the client. And "d:\" is a local drive (in my machine). With
others columns it worked OK.
Thanks
Adalberto Andrade
"oj" wrote:

> where do you run this bcp statement, on the client or on the server itself
.
> "d:\" must be local to where ever you run the bcp statement.
> --
> -oj
>
> "Adalberto Andrade" <Adalberto Andrade@.discussions.microsoft.com> wrote in
> message news:014C3C25-592D-4A88-A320-325C52D49D2A@.microsoft.com...
>
>|||Adalberto,
Please check to see if there is a carriage return at the
end of your format file and that there are no extra tabs
or anything else in it. Also be sure the format file
is not open in an editor when you run the command.
The error message mentions the format file, not the
data file, so I think the problem is with the format file.
Steve Kass
Drew University
Adalberto Andrade wrote:
>Hi oj,
> On the client. And "d:\" is a local drive (in my machine). With
>others columns it worked OK.
>
> Thanks
> Adalberto Andrade
>
>
>"oj" wrote:
>
>|||Steve,
I made a complete revision of all components of my environment.
Things like : cr (carriage return), opened format file and others wrongs
caracters inside of format file are not the problem. I am thankful for yours
suggestions,but the problem continue. Now I substituted the third column of
my output file and the bcp utility started the copy, but the generated file
has mixed data with stranger caracters. This new column has valids datas and
also null values in some registers and it is the great difference between th
e
others columns (first e second ones). Without this third column everything
work 100% OK. I already try to change the value of prefix length (field of
the format file) to -1 or 2 with the hope to solve it, but the output
generated file continue with stranger and mixed caracters (like ASCII
caracters). I really don't have any idea of what I can do to put it to work.
I am not sure, but perhaps I will need to make other configuration in my
format file, but exactly what ? Do you have other help for me ?
Thanks again
Adalberto Andrade
"Steve Kass" wrote:

> Adalberto,
> Please check to see if there is a carriage return at the
> end of your format file and that there are no extra tabs
> or anything else in it. Also be sure the format file
> is not open in an editor when you run the command.
> The error message mentions the format file, not the
> data file, so I think the problem is with the format file.
> Steve Kass
> Drew University
> Adalberto Andrade wrote:
>
>|||Adalberto,
I am not sure what the problem is, but here are three separate
suggestions.
1. Try to use bcp without a format file, since TAB and NEWLINE
are the defaults for bcp. If this creates a Unicode file, you will need
to change the format file to say SQLNCHAR instead of SQLCHAR,
and you will also need to put the Unicode two-byte signature into
the beginning of the file yourself, since bcp does not do this for you.
2. Be sure the format file is saved as ASCII, not Unicode, then try
again with the format file.
3. Verify the data lengths and types of the output,
3A. Run this and provide the output.
select top 1
numeroaj, acervoespecializada
into CheckTypesTable
from Siga.dbo.vwHerancaJacente
select * from CheckTypesTable
3B. In Query Analyzer, refresh the current database
and for [CheckTypesTable] choose "Script Table To
New Window" to verify the data types of these columns
and provide the output.
3C. After doing this, you can DROP the table CheckTypesTable.
It might help if you post the definition of Siga.dbo.vwHerancaJa.
(If it is a view, also post CREATE TABLE statements from the
tables it uses for the columns numeroaj and acervoespecializada
(and the third column, since at one point you mention three columns.
Also, when you have three columns, what is your query?)
SK
Adalberto Andrade wrote:
>Steve,
> I made a complete revision of all components of my environment.
>Things like : cr (carriage return), opened format file and others wrongs
>caracters inside of format file are not the problem. I am thankful for your
s
>suggestions,but the problem continue. Now I substituted the third column of
>my output file and the bcp utility started the copy, but the generated file
>has mixed data with stranger caracters. This new column has valids datas an
d
>also null values in some registers and it is the great difference between t
he
>others columns (first e second ones). Without this third column everything
>work 100% OK. I already try to change the value of prefix length (field of
>the format file) to -1 or 2 with the hope to solve it, but the output
>generated file continue with stranger and mixed caracters (like ASCII
>caracters). I really don't have any idea of what I can do to put it to work
.
>I am not sure, but perhaps I will need to make other configuration in my
>format file, but exactly what ? Do you have other help for me ?
>
> Thanks again
>Adalberto Andrade
>"Steve Kass" wrote:
>
>|||Steve,
Forgive me for delay in my reply. I was very busy. Let 's go. Really
you are correct. When I ran the bcp utility in the prompt without my format
file, it show the message explaining that happened a truncate. In true there
was a difference between the data length of one column and your value define
d
for this size in the format file. Summarizing, the problem is over and your
suggesntions 1 and 3 were very helpful.
Thanks a lot
Adalberto
Andrade
Rio de Janeiro's
City Hall
"Steve Kass" wrote:

> Adalberto,
> I am not sure what the problem is, but here are three separate
> suggestions.
> 1. Try to use bcp without a format file, since TAB and NEWLINE
> are the defaults for bcp. If this creates a Unicode file, you will need
> to change the format file to say SQLNCHAR instead of SQLCHAR,
> and you will also need to put the Unicode two-byte signature into
> the beginning of the file yourself, since bcp does not do this for you.
> 2. Be sure the format file is saved as ASCII, not Unicode, then try
> again with the format file.
> 3. Verify the data lengths and types of the output,
> 3A. Run this and provide the output.
> select top 1
> numeroaj, acervoespecializada
> into CheckTypesTable
> from Siga.dbo.vwHerancaJacente
> select * from CheckTypesTable
> 3B. In Query Analyzer, refresh the current database
> and for [CheckTypesTable] choose "Script Table To
> New Window" to verify the data types of these columns
> and provide the output.
> 3C. After doing this, you can DROP the table CheckTypesTable.
>
> It might help if you post the definition of Siga.dbo.vwHerancaJa.
> (If it is a view, also post CREATE TABLE statements from the
> tables it uses for the columns numeroaj and acervoespecializada
> (and the third column, since at one point you mention three columns.
> Also, when you have three columns, what is your query?)
> SK
> Adalberto Andrade wrote:
>
>

problem with BCP

Dear all,
I created a BCP file from SQL Server2000 with .BCP Extension.
Now i am trying to load data in the SQL Server2005 Database table.
First was getting the error of remote connection which i solved .
Now i am getting this below error
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
NULL
I am executing this code
exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
Pls help
from
Dollerhi
there is an error in your syntex, it's complaining about your error file
u have -n -"EPI-IT-SUFIAN\SQL_DATA"
is this the location for your error / log ilfe -"EPI-IT-SUFIAN\SQL_DATA"
if so then you have to add the -e switch and add a file name as well
and u are specifying login and password with blank values '
-U -P
"doller" wrote:
> Dear all,
> I created a BCP file from SQL Server2000 with .BCP Extension.
> Now i am trying to load data in the SQL Server2005 Database table.
> First was getting the error of remote connection which i solved .
> Now i am getting this below error
> SQLState = HY000, NativeError = 0
> Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
> NULL
> I am executing this code
> exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
> epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
> Pls help
> from
> Doller
>|||Hi
Assuming epinav.dbo.Sp_who_Perocess is a table or view, you should get the
command working from a command prompt and then fit that into the xp_cmdshell
call.
Try the following adding the correct loginid and password:
exec master..xp_cmdshell ' BCP "epinav.dbo.Sp_who_Perocess" in
"c:\Sp_who_Perocess.bcp" -n -S "EPI-IT-SUFIAN\SQL_DATA" -U {loginid} -P
{password}'
John
"doller" wrote:
> Dear all,
> I created a BCP file from SQL Server2000 with .BCP Extension.
> Now i am trying to load data in the SQL Server2005 Database table.
> First was getting the error of remote connection which i solved .
> Now i am getting this below error
> SQLState = HY000, NativeError = 0
> Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
> NULL
> I am executing this code
> exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
> epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
> Pls help
> from
> Doller
>|||Hi
This assumed that EPI-IT-SUFIAN\SQL_DATA was the database server if not
please let us know what it is supposed to be!
You can check the format and parameters of the BCP utility in books online.
John
"John Bell" wrote:
> Hi
> Assuming epinav.dbo.Sp_who_Perocess is a table or view, you should get the
> command working from a command prompt and then fit that into the xp_cmdshell
> call.
> Try the following adding the correct loginid and password:
> exec master..xp_cmdshell ' BCP "epinav.dbo.Sp_who_Perocess" in
> "c:\Sp_who_Perocess.bcp" -n -S "EPI-IT-SUFIAN\SQL_DATA" -U {loginid} -P
> {password}'
> John
>
> "doller" wrote:
> > Dear all,
> >
> > I created a BCP file from SQL Server2000 with .BCP Extension.
> > Now i am trying to load data in the SQL Server2005 Database table.
> > First was getting the error of remote connection which i solved .
> > Now i am getting this below error
> >
> > SQLState = HY000, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
> > NULL
> >
> > I am executing this code
> > exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
> > epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
> >
> > Pls help
> >
> > from
> > Doller
> >
> >|||Hi all,
EPI-IT-SUFIAN is my server name and SQL_DATA is my instance name.
i resolved the connection issue but now i am getting diffrent type of
error
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
NULL
pls help
from
doller
John Bell wrote:
> Hi
> This assumed that EPI-IT-SUFIAN\SQL_DATA was the database server if not
> please let us know what it is supposed to be!
> You can check the format and parameters of the BCP utility in books online.
> John
> "John Bell" wrote:
> > Hi
> >
> > Assuming epinav.dbo.Sp_who_Perocess is a table or view, you should get the
> > command working from a command prompt and then fit that into the xp_cmdshell
> > call.
> >
> > Try the following adding the correct loginid and password:
> >
> > exec master..xp_cmdshell ' BCP "epinav.dbo.Sp_who_Perocess" in
> > "c:\Sp_who_Perocess.bcp" -n -S "EPI-IT-SUFIAN\SQL_DATA" -U {loginid} -P
> > {password}'
> >
> > John
> >
> >
> > "doller" wrote:
> >
> > > Dear all,
> > >
> > > I created a BCP file from SQL Server2000 with .BCP Extension.
> > > Now i am trying to load data in the SQL Server2005 Database table.
> > > First was getting the error of remote connection which i solved .
> > > Now i am getting this below error
> > >
> > > SQLState = HY000, NativeError = 0
> > > Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
> > > NULL
> > >
> > > I am executing this code
> > > exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
> > > epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
> > >
> > > Pls help
> > >
> > > from
> > > Doller
> > >
> > >|||hi doller,
the following line might be enough (it's been tested from DOS session)
bcp <db>.<table> in <path>.<file> -n -S<server> -U<login> -P<password>
--
current location: alicante (es)
"doller" wrote:
> Hi all,
> EPI-IT-SUFIAN is my server name and SQL_DATA is my instance name.
> i resolved the connection issue but now i am getting diffrent type of
> error
> SQLState = HY000, NativeError = 0
> Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
> NULL
> pls help
> from
> doller
>
> John Bell wrote:
> > Hi
> >
> > This assumed that EPI-IT-SUFIAN\SQL_DATA was the database server if not
> > please let us know what it is supposed to be!
> >
> > You can check the format and parameters of the BCP utility in books online.
> >
> > John
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > Assuming epinav.dbo.Sp_who_Perocess is a table or view, you should get the
> > > command working from a command prompt and then fit that into the xp_cmdshell
> > > call.
> > >
> > > Try the following adding the correct loginid and password:
> > >
> > > exec master..xp_cmdshell ' BCP "epinav.dbo.Sp_who_Perocess" in
> > > "c:\Sp_who_Perocess.bcp" -n -S "EPI-IT-SUFIAN\SQL_DATA" -U {loginid} -P
> > > {password}'
> > >
> > > John
> > >
> > >
> > > "doller" wrote:
> > >
> > > > Dear all,
> > > >
> > > > I created a BCP file from SQL Server2000 with .BCP Extension.
> > > > Now i am trying to load data in the SQL Server2005 Database table.
> > > > First was getting the error of remote connection which i solved .
> > > > Now i am getting this below error
> > > >
> > > > SQLState = HY000, NativeError = 0
> > > > Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
> > > > NULL
> > > >
> > > > I am executing this code
> > > > exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
> > > > epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
> > > >
> > > > Pls help
> > > >
> > > > from
> > > > Doller
> > > >
> > > >
>|||Hi ,
Thanks for all ur help.
I was getting the error unable to open the BCP file because i was using
-U -P as i was working on the server means with trustedconnection so i
have to use -T in place of -U and -P
from
Doller
Enric wrote:
> hi doller,
> the following line might be enough (it's been tested from DOS session)
> bcp <db>.<table> in <path>.<file> -n -S<server> -U<login> -P<password>
> --
> current location: alicante (es)
>
> "doller" wrote:
> > Hi all,
> >
> > EPI-IT-SUFIAN is my server name and SQL_DATA is my instance name.
> >
> > i resolved the connection issue but now i am getting diffrent type of
> > error
> > SQLState = HY000, NativeError = 0
> > Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
> > NULL
> >
> > pls help
> >
> > from
> > doller
> >
> >
> >
> > John Bell wrote:
> > > Hi
> > >
> > > This assumed that EPI-IT-SUFIAN\SQL_DATA was the database server if not
> > > please let us know what it is supposed to be!
> > >
> > > You can check the format and parameters of the BCP utility in books online.
> > >
> > > John
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > Assuming epinav.dbo.Sp_who_Perocess is a table or view, you should get the
> > > > command working from a command prompt and then fit that into the xp_cmdshell
> > > > call.
> > > >
> > > > Try the following adding the correct loginid and password:
> > > >
> > > > exec master..xp_cmdshell ' BCP "epinav.dbo.Sp_who_Perocess" in
> > > > "c:\Sp_who_Perocess.bcp" -n -S "EPI-IT-SUFIAN\SQL_DATA" -U {loginid} -P
> > > > {password}'
> > > >
> > > > John
> > > >
> > > >
> > > > "doller" wrote:
> > > >
> > > > > Dear all,
> > > > >
> > > > > I created a BCP file from SQL Server2000 with .BCP Extension.
> > > > > Now i am trying to load data in the SQL Server2005 Database table.
> > > > > First was getting the error of remote connection which i solved .
> > > > > Now i am getting this below error
> > > > >
> > > > > SQLState = HY000, NativeError = 0
> > > > > Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
> > > > > NULL
> > > > >
> > > > > I am executing this code
> > > > > exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
> > > > > epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
> > > > >
> > > > > Pls help
> > > > >
> > > > > from
> > > > > Doller
> > > > >
> > > > >
> >
> >

problem with BCP

Dear all,
I created a BCP file from SQL Server2000 with .BCP Extension.
Now i am trying to load data in the SQL Server2005 Database table.
First was getting the error of remote connection which i solved .
Now i am getting this below error
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
NULL
I am executing this code
exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
Pls help
from
Dollerhi
there is an error in your syntex, it's complaining about your error file
u have -n -"EPI-IT-SUFIAN\SQL_DATA"
is this the location for your error / log ilfe -"EPI-IT-SUFIAN\SQL_DATA"
if so then you have to add the -e switch and add a file name as well
and u are specifying login and password with blank values '
-U -P
"doller" wrote:

> Dear all,
> I created a BCP file from SQL Server2000 with .BCP Extension.
> Now i am trying to load data in the SQL Server2005 Database table.
> First was getting the error of remote connection which i solved .
> Now i am getting this below error
> SQLState = HY000, NativeError = 0
> Error = [Microsoft][SQL Native Client]Unable to open BCP error-fil
e
> NULL
> I am executing this code
> exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
> epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
> Pls help
> from
> Doller
>|||Hi
Assuming epinav.dbo.Sp_who_Perocess is a table or view, you should get the
command working from a command prompt and then fit that into the xp_cmdshell
call.
Try the following adding the correct loginid and password:
exec master..xp_cmdshell ' BCP "epinav.dbo.Sp_who_Perocess" in
"c:\Sp_who_Perocess.bcp" -n -S "EPI-IT-SUFIAN\SQL_DATA" -U {loginid} -P
{password}'
John
"doller" wrote:

> Dear all,
> I created a BCP file from SQL Server2000 with .BCP Extension.
> Now i am trying to load data in the SQL Server2005 Database table.
> First was getting the error of remote connection which i solved .
> Now i am getting this below error
> SQLState = HY000, NativeError = 0
> Error = [Microsoft][SQL Native Client]Unable to open BCP error-fil
e
> NULL
> I am executing this code
> exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
> epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
> Pls help
> from
> Doller
>|||Hi
This assumed that EPI-IT-SUFIAN\SQL_DATA was the database server if not
please let us know what it is supposed to be!
You can check the format and parameters of the BCP utility in books online.
John
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Assuming epinav.dbo.Sp_who_Perocess is a table or view, you should get th
e
> command working from a command prompt and then fit that into the xp_cmdshe
ll
> call.
> Try the following adding the correct loginid and password:
> exec master..xp_cmdshell ' BCP "epinav.dbo.Sp_who_Perocess" in
> "c:\Sp_who_Perocess.bcp" -n -S "EPI-IT-SUFIAN\SQL_DATA" -U {loginid}
-P
> {password}'
> John
>
> "doller" wrote:
>|||Hi all,
EPI-IT-SUFIAN is my server name and SQL_DATA is my instance name.
i resolved the connection issue but now i am getting diffrent type of
error
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
NULL
pls help
from
doller
John Bell wrote:[vbcol=seagreen]
> Hi
> This assumed that EPI-IT-SUFIAN\SQL_DATA was the database server if not
> please let us know what it is supposed to be!
> You can check the format and parameters of the BCP utility in books online
.
> John
> "John Bell" wrote:
>|||hi doller,
the following line might be enough (it's been tested from DOS session)
bcp <db>.<table> in <path>.<file> -n -S<server> -U<login> -P<password>
--
current location: alicante (es)
"doller" wrote:

> Hi all,
> EPI-IT-SUFIAN is my server name and SQL_DATA is my instance name.
> i resolved the connection issue but now i am getting diffrent type of
> error
> SQLState = HY000, NativeError = 0
> Error = [Microsoft][SQL Native Client]Unable to open BCP error-fil
e
> NULL
> pls help
> from
> doller
>
> John Bell wrote:
>|||Hi ,
Thanks for all ur help.
I was getting the error unable to open the BCP file because i was using
-U -P as i was working on the server means with trustedconnection so i
have to use -T in place of -U and -P
from
Doller
Enric wrote:[vbcol=seagreen]
> hi doller,
> the following line might be enough (it's been tested from DOS session)
> bcp <db>.<table> in <path>.<file> -n -S<server> -U<login> -P<password>
> --
> current location: alicante (es)
>
> "doller" wrote:
>

problem with BCP

Dear all,
I created a BCP file from SQL Server2000 with .BCP Extension.
Now i am trying to load data in the SQL Server2005 Database table.
First was getting the error of remote connection which i solved .
Now i am getting this below error
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
NULL
I am executing this code
exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
Pls help
from
Doller
hi
there is an error in your syntex, it's complaining about your error file
u have -n -"EPI-IT-SUFIAN\SQL_DATA"
is this the location for your error / log ilfe -"EPI-IT-SUFIAN\SQL_DATA"
if so then you have to add the -e switch and add a file name as well
and u are specifying login and password with blank values ?
-U -P
"doller" wrote:

> Dear all,
> I created a BCP file from SQL Server2000 with .BCP Extension.
> Now i am trying to load data in the SQL Server2005 Database table.
> First was getting the error of remote connection which i solved .
> Now i am getting this below error
> SQLState = HY000, NativeError = 0
> Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
> NULL
> I am executing this code
> exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
> epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
> Pls help
> from
> Doller
>
|||Hi
Assuming epinav.dbo.Sp_who_Perocess is a table or view, you should get the
command working from a command prompt and then fit that into the xp_cmdshell
call.
Try the following adding the correct loginid and password:
exec master..xp_cmdshell ' BCP "epinav.dbo.Sp_who_Perocess" in
"c:\Sp_who_Perocess.bcp" -n -S "EPI-IT-SUFIAN\SQL_DATA" -U {loginid} -P
{password}'
John
"doller" wrote:

> Dear all,
> I created a BCP file from SQL Server2000 with .BCP Extension.
> Now i am trying to load data in the SQL Server2005 Database table.
> First was getting the error of remote connection which i solved .
> Now i am getting this below error
> SQLState = HY000, NativeError = 0
> Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
> NULL
> I am executing this code
> exec master..xp_cmdshell ' BCP c:\Sp_who_Perocess.bcp in
> epinav.dbo.Sp_who_Perocess -n -"EPI-IT-SUFIAN\SQL_DATA" -U -P'
> Pls help
> from
> Doller
>
|||Hi
This assumed that EPI-IT-SUFIAN\SQL_DATA was the database server if not
please let us know what it is supposed to be!
You can check the format and parameters of the BCP utility in books online.
John
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Assuming epinav.dbo.Sp_who_Perocess is a table or view, you should get the
> command working from a command prompt and then fit that into the xp_cmdshell
> call.
> Try the following adding the correct loginid and password:
> exec master..xp_cmdshell ' BCP "epinav.dbo.Sp_who_Perocess" in
> "c:\Sp_who_Perocess.bcp" -n -S "EPI-IT-SUFIAN\SQL_DATA" -U {loginid} -P
> {password}'
> John
>
> "doller" wrote:
|||Hi all,
EPI-IT-SUFIAN is my server name and SQL_DATA is my instance name.
i resolved the connection issue but now i am getting diffrent type of
error
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
NULL
pls help
from
doller
John Bell wrote:[vbcol=seagreen]
> Hi
> This assumed that EPI-IT-SUFIAN\SQL_DATA was the database server if not
> please let us know what it is supposed to be!
> You can check the format and parameters of the BCP utility in books online.
> John
> "John Bell" wrote:
|||hi doller,
the following line might be enough (it's been tested from DOS session)
bcp <db>.<table> in <path>.<file> -n -S<server> -U<login> -P<password>
current location: alicante (es)
"doller" wrote:

> Hi all,
> EPI-IT-SUFIAN is my server name and SQL_DATA is my instance name.
> i resolved the connection issue but now i am getting diffrent type of
> error
> SQLState = HY000, NativeError = 0
> Error = [Microsoft][SQL Native Client]Unable to open BCP error-file
> NULL
> pls help
> from
> doller
>
> John Bell wrote:
>
|||Hi ,
Thanks for all ur help.
I was getting the error unable to open the BCP file because i was using
-U -P as i was working on the server means with trustedconnection so i
have to use -T in place of -U and -P
from
Doller
Enric wrote:[vbcol=seagreen]
> hi doller,
> the following line might be enough (it's been tested from DOS session)
> bcp <db>.<table> in <path>.<file> -n -S<server> -U<login> -P<password>
> --
> current location: alicante (es)
>
> "doller" wrote:

Problem with Batch files!

Hi,
I am trying to write a batch file which does the following:
1. Move all the files created on weekdays to be sorted to their respective days folder Example: Files created on Tuesday should be moved to a folder named "Tuesday"(When the batch file is run on Wednesday).
2. Only that particular day's created files should be present in the main directory and the other files should be moved to their respective days(created days) folder!!!!!
Could you please help me out!!!!!
Thanks.
AUse xp_cmdshall with regular dos dir, copy, etc. You can save list of files in folder to temporary or permanent table like this:

create table #tmp(res varchar(700))
insert #tmp
master..xp_cmdshell 'dir myfolder'

problem with attaching mdf file

Bob wrote:
> Hi,
> i installed sql server 2005 express on a windows xp prof. sp2 system.
> i also installed the 2005 studio management tool.
> I can connect to the sql server (sqlcmd -S), i can create a new database in
> the 2005 studio management tool, but i can't attach a mdf file. I'm
> administrator so have all rights. I have several mdf (and ldf) files on the
> disc.
> I did this:
> rightclick on Databases, then Attach: i see right an empty windows with an
> ADD button.
> When i click on that button, i get the error:
> c:\myapp\app_data\
> cannot access the specified path or file. Verify that you have the necessary
> privileges ...
> If you know that the service account a specific file, type the path ...
> The sevice account is NT AUTHORITY\NetworkService, but when i see the list
> of the accounts, i can't find it into that list.
>
> Any help would be appreciated.
> Thanks
> Bob
>
>
>
>
Create an actual user to be used as the service account, then make sure
that user has read/write permissions in your data directory.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Bob wrote:
> Thanks for replying.
> Can you tell me how to do that? Do you mean: a windows xp account? And how
> to link it to NT AUTHORITY\Network? I can't even find it in the list of the
> windows accounts.
>
Yes, a Windows XP account or a domain account, whichever is appropriate
for your environment. You don't "link" this new user to NT
AUTHORITY\Network, you'll configure the SQL Server services to run as
the new user that you create. NT AUTHORITY\Network isn't a real user
account.
You could also configure the SQL Server services to run as "Local
System", which will give SQL system-level access to your machine.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

problem with attaching mdf file

Bob wrote:
> Hi,
> i installed sql server 2005 express on a windows xp prof. sp2 system.
> i also installed the 2005 studio management tool.
> I can connect to the sql server (sqlcmd -S), i can create a new database in
> the 2005 studio management tool, but i can't attach a mdf file. I'm
> administrator so have all rights. I have several mdf (and ldf) files on the
> disc.
> I did this:
> rightclick on Databases, then Attach: i see right an empty windows with an
> ADD button.
> When i click on that button, i get the error:
> c:\myapp\app_data\
> cannot access the specified path or file. Verify that you have the necessary
> privileges ...
> If you know that the service account a specific file, type the path ...
> The sevice account is NT AUTHORITY\NetworkService, but when i see the list
> of the accounts, i can't find it into that list.
>
> Any help would be appreciated.
> Thanks
> Bob
>
>
>
>
Create an actual user to be used as the service account, then make sure
that user has read/write permissions in your data directory.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Bob wrote:
> Thanks for replying.
> Can you tell me how to do that? Do you mean: a windows xp account? And how
> to link it to NT AUTHORITY\Network? I can't even find it in the list of the
> windows accounts.
>
Yes, a Windows XP account or a domain account, whichever is appropriate
for your environment. You don't "link" this new user to NT
AUTHORITY\Network, you'll configure the SQL Server services to run as
the new user that you create. NT AUTHORITY\Network isn't a real user
account.
You could also configure the SQL Server services to run as "Local
System", which will give SQL system-level access to your machine.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
sql

problem with attaching mdf file

Hi,
i installed sql server 2005 express on a windows xp prof. sp2 system.
i also installed the 2005 studio management tool.
I can connect to the sql server (sqlcmd -S), i can create a new database in
the 2005 studio management tool, but i can't attach a mdf file. I'm
administrator so have all rights. I have several mdf (and ldf) files on the
disc.
I did this:
rightclick on Databases, then Attach: i see right an empty windows with an
ADD button.
When i click on that button, i get the error:
c:\myapp\app_data\
cannot access the specified path or file. Verify that you have the necessary
privileges ...
If you know that the service account a specific file, type the path ...
The sevice account is NT AUTHORITY\NetworkService, but when i see the list
of the accounts, i can't find it into that list.
Any help would be appreciated.
Thanks
BobBob wrote:
> Hi,
> i installed sql server 2005 express on a windows xp prof. sp2 system.
> i also installed the 2005 studio management tool.
> I can connect to the sql server (sqlcmd -S), i can create a new database in
> the 2005 studio management tool, but i can't attach a mdf file. I'm
> administrator so have all rights. I have several mdf (and ldf) files on the
> disc.
> I did this:
> rightclick on Databases, then Attach: i see right an empty windows with an
> ADD button.
> When i click on that button, i get the error:
> c:\myapp\app_data\
> cannot access the specified path or file. Verify that you have the necessary
> privileges ...
> If you know that the service account a specific file, type the path ...
> The sevice account is NT AUTHORITY\NetworkService, but when i see the list
> of the accounts, i can't find it into that list.
>
> Any help would be appreciated.
> Thanks
> Bob
>
>
>
>
Create an actual user to be used as the service account, then make sure
that user has read/write permissions in your data directory.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks for replying.
Can you tell me how to do that? Do you mean: a windows xp account? And how
to link it to NT AUTHORITY\Network? I can't even find it in the list of the
windows accounts.
"Tracy McKibben" <tracy@.realsqlguy.com> schreef in bericht
news:459BF31D.9050605@.realsqlguy.com...
> Bob wrote:
>> Hi,
>> i installed sql server 2005 express on a windows xp prof. sp2 system.
>> i also installed the 2005 studio management tool.
>> I can connect to the sql server (sqlcmd -S), i can create a new database
>> in the 2005 studio management tool, but i can't attach a mdf file. I'm
>> administrator so have all rights. I have several mdf (and ldf) files on
>> the disc.
>> I did this:
>> rightclick on Databases, then Attach: i see right an empty windows with
>> an ADD button.
>> When i click on that button, i get the error:
>> c:\myapp\app_data\
>> cannot access the specified path or file. Verify that you have the
>> necessary privileges ...
>> If you know that the service account a specific file, type the path ...
>> The sevice account is NT AUTHORITY\NetworkService, but when i see the
>> list of the accounts, i can't find it into that list.
>>
>> Any help would be appreciated.
>> Thanks
>> Bob
>>
>>
>>
> Create an actual user to be used as the service account, then make sure
> that user has read/write permissions in your data directory.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Thanks for replying.
I copied mydb.mdf and .ldf to the directory where i created a new database,
but in Management Studio, it's the same: i don't see it in the list of the
databases and can't attach it.
"Hari Prasad" <HariPrasad@.discussions.microsoft.com> schreef in bericht
news:7601E1FD-2A85-496E-A92F-06189BC57855@.microsoft.com...
> Easy way is copy the required MDF and LDF files to the default data
> directory
> (The place where you create the new database)
> using the windows explorer and then try attaching the database. This will
> defenetely work out.
> The issue is you have full access to machine but once you login to SQL
> Server you will have access rights only belong to
> account "NT AUTHORITY\NetworkService". The account may not have access to
> folder c:\myapp\app_data\.
> My best suggestion is copy the file to the folder where the new database
> is
> created and attach it.
> Thanks
> Hari
> "Bob" wrote:
>> Hi,
>> i installed sql server 2005 express on a windows xp prof. sp2 system.
>> i also installed the 2005 studio management tool.
>> I can connect to the sql server (sqlcmd -S), i can create a new database
>> in
>> the 2005 studio management tool, but i can't attach a mdf file. I'm
>> administrator so have all rights. I have several mdf (and ldf) files on
>> the
>> disc.
>> I did this:
>> rightclick on Databases, then Attach: i see right an empty windows with
>> an
>> ADD button.
>> When i click on that button, i get the error:
>> c:\myapp\app_data\
>> cannot access the specified path or file. Verify that you have the
>> necessary
>> privileges ...
>> If you know that the service account a specific file, type the path ...
>> The sevice account is NT AUTHORITY\NetworkService, but when i see the
>> list
>> of the accounts, i can't find it into that list.
>>
>> Any help would be appreciated.
>> Thanks
>> Bob
>>
>>
>>
>>|||Bob wrote:
> Thanks for replying.
> Can you tell me how to do that? Do you mean: a windows xp account? And how
> to link it to NT AUTHORITY\Network? I can't even find it in the list of the
> windows accounts.
>
Yes, a Windows XP account or a domain account, whichever is appropriate
for your environment. You don't "link" this new user to NT
AUTHORITY\Network, you'll configure the SQL Server services to run as
the new user that you create. NT AUTHORITY\Network isn't a real user
account.
You could also configure the SQL Server services to run as "Local
System", which will give SQL system-level access to your machine.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks it works now
"Tracy McKibben" <tracy@.realsqlguy.com> schreef in bericht
news:459C069B.5080403@.realsqlguy.com...
> Bob wrote:
>> Thanks for replying.
>> Can you tell me how to do that? Do you mean: a windows xp account? And
>> how to link it to NT AUTHORITY\Network? I can't even find it in the list
>> of the windows accounts.
> Yes, a Windows XP account or a domain account, whichever is appropriate
> for your environment. You don't "link" this new user to NT
> AUTHORITY\Network, you'll configure the SQL Server services to run as the
> new user that you create. NT AUTHORITY\Network isn't a real user account.
> You could also configure the SQL Server services to run as "Local System",
> which will give SQL system-level access to your machine.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

problem with attaching mdf file

Hi,
i installed sql server 2005 express on a Windows XP prof. sp2 system.
i also installed the 2005 studio management tool.
I can connect to the sql server (sqlcmd -S), i can create a new database in
the 2005 studio management tool, but i can't attach a mdf file. I'm
administrator so have all rights. I have several mdf (and ldf) files on the
disc.
I did this:
rightclick on Databases, then Attach: i see right an empty windows with an
ADD button.
When i click on that button, i get the error:
c:\myapp\app_data\
cannot access the specified path or file. Verify that you have the necessary
privileges ...
If you know that the service account a specific file, type the path ...
The sevice account is NT AUTHORITY\NetworkService, but when i see the list
of the accounts, i can't find it into that list.
Any help would be appreciated.
Thanks
BobBob wrote:
> Hi,
> i installed sql server 2005 express on a Windows XP prof. sp2 system.
> i also installed the 2005 studio management tool.
> I can connect to the sql server (sqlcmd -S), i can create a new database i
n
> the 2005 studio management tool, but i can't attach a mdf file. I'm
> administrator so have all rights. I have several mdf (and ldf) files on th
e
> disc.
> I did this:
> rightclick on Databases, then Attach: i see right an empty windows with an
> ADD button.
> When i click on that button, i get the error:
> c:\myapp\app_data\
> cannot access the specified path or file. Verify that you have the necessa
ry
> privileges ...
> If you know that the service account a specific file, type the path ...
> The sevice account is NT AUTHORITY\NetworkService, but when i see the list
> of the accounts, i can't find it into that list.
>
> Any help would be appreciated.
> Thanks
> Bob
>
>
>
>
Create an actual user to be used as the service account, then make sure
that user has read/write permissions in your data directory.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Easy way is copy the required MDF and LDF files to the default data director
y
(The place where you create the new database)
using the windows explorer and then try attaching the database. This will
defenetely work out.
The issue is you have full access to machine but once you login to SQL
Server you will have access rights only belong to
account "NT AUTHORITY\NetworkService". The account may not have access to
folder c:\myapp\app_data\.
My best suggestion is copy the file to the folder where the new database is
created and attach it.
Thanks
Hari
"Bob" wrote:

> Hi,
> i installed sql server 2005 express on a Windows XP prof. sp2 system.
> i also installed the 2005 studio management tool.
> I can connect to the sql server (sqlcmd -S), i can create a new database i
n
> the 2005 studio management tool, but i can't attach a mdf file. I'm
> administrator so have all rights. I have several mdf (and ldf) files on th
e
> disc.
> I did this:
> rightclick on Databases, then Attach: i see right an empty windows with an
> ADD button.
> When i click on that button, i get the error:
> c:\myapp\app_data\
> cannot access the specified path or file. Verify that you have the necessa
ry
> privileges ...
> If you know that the service account a specific file, type the path ...
> The sevice account is NT AUTHORITY\NetworkService, but when i see the list
> of the accounts, i can't find it into that list.
>
> Any help would be appreciated.
> Thanks
> Bob
>
>
>
>|||Thanks for replying.
Can you tell me how to do that? Do you mean: a Windows XP account? And how
to link it to NT AUTHORITY\Network? I can't even find it in the list of the
windows accounts.
"Tracy McKibben" <tracy@.realsqlguy.com> schreef in bericht
news:459BF31D.9050605@.realsqlguy.com...
> Bob wrote:
> Create an actual user to be used as the service account, then make sure
> that user has read/write permissions in your data directory.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Thanks for replying.
I copied mydb.mdf and .ldf to the directory where i created a new database,
but in Management Studio, it's the same: i don't see it in the list of the
databases and can't attach it.
"Hari Prasad" <HariPrasad@.discussions.microsoft.com> schreef in bericht
news:7601E1FD-2A85-496E-A92F-06189BC57855@.microsoft.com...[vbcol=seagreen]
> Easy way is copy the required MDF and LDF files to the default data
> directory
> (The place where you create the new database)
> using the windows explorer and then try attaching the database. This will
> defenetely work out.
> The issue is you have full access to machine but once you login to SQL
> Server you will have access rights only belong to
> account "NT AUTHORITY\NetworkService". The account may not have access to
> folder c:\myapp\app_data\.
> My best suggestion is copy the file to the folder where the new database
> is
> created and attach it.
> Thanks
> Hari
> "Bob" wrote:
>|||Bob wrote:
> Thanks for replying.
> Can you tell me how to do that? Do you mean: a Windows XP account? And how
> to link it to NT AUTHORITY\Network? I can't even find it in the list of th
e
> windows accounts.
>
Yes, a Windows XP account or a domain account, whichever is appropriate
for your environment. You don't "link" this new user to NT
AUTHORITY\Network, you'll configure the SQL Server services to run as
the new user that you create. NT AUTHORITY\Network isn't a real user
account.
You could also configure the SQL Server services to run as "Local
System", which will give SQL system-level access to your machine.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks it works now
"Tracy McKibben" <tracy@.realsqlguy.com> schreef in bericht
news:459C069B.5080403@.realsqlguy.com...
> Bob wrote:
> Yes, a Windows XP account or a domain account, whichever is appropriate
> for your environment. You don't "link" this new user to NT
> AUTHORITY\Network, you'll configure the SQL Server services to run as the
> new user that you create. NT AUTHORITY\Network isn't a real user account.
> You could also configure the SQL Server services to run as "Local System",
> which will give SQL system-level access to your machine.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Problem with Accessing Unix Share Drive When a SSIS Job Runs

Hi I am trying to schedule a job to copy an MDB data file from Unix server to Windows 2003 server (Accfp1_data2_server). I have created a file copy SSIS package and tested it in the SSIS Visual Studio environment where it runs ok. The package was created while logged in as a domain administrator.

I then created a job to run this package (which is stored on a folder) using the credential of the same domain administrator who has full access privilege to both of these servers. However, the job fails whenever it is run manually or scheduled? The error message displayed is given below

Message
Executed as user: FORTIES\ABCITYG.
Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005.
All rights reserved. Started: 14:26:07 Error: 2007-09-13 14:26:12.56 Code: 0xC001401E
Source: CommunityContact - Copy MS Access Database Connection manager "CONTACT.mdb On Accfp1_data2_server"
Description: The file name "\\Accfp1_data2_server\DATA2\Arts&rec\Apps\Contacts\CONTACT.mdb" specified in the connection was not valid.
End Error Error: 2007-09-13 14:26:12.56 Code: 0xC001401D Source: CommunityContact - Copy MS Access Database Description: Connection "CONTACT.mdb On Accfp1_data2_server" failed validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 14:26:07 Finished: 14:26:12 Elapsed: 5.297 seconds. The package execution failed. The step failed.

Please note that the job runs without problem when I change the source file to a Windows 2000 server share . How bizzare? Hope this is not a Microsoft's Trick?

Can anyone help?

Just to check, the FORTIES\ABCITYG account is the domain administrator that has access to this share?

|||

Thanks for pointing to this. Apology for my dyslexic reading of the error message. This account does not have access. I will ask our team to look into this.

I overlooked that the job was running under FORTIES\ABCITYG (local) account. It is weird because when I created the credential I had entered a different domain admin account but I noticed that the identiry has been automatically reverted to FORTIES\ABCITYG account. In fact I recreated the credential with domainserver\abcityg but the identity for this account is automatically refreshed with FORTIES\ABCITYG again and again. Any guess?

|||

Just to emphasise the fact that I am unable to create a new credential that uses other domain user. (I used the option menu Security/Credential) . And this appears to be the root of the problem.

I can select a user who is not a user in the current server (forties) but is a domain admin (<domainserver>\admin) from "select User or Group" window. But when I click on OK button the Identity field displays 'forties\admin'. How bizzare? I would have expected it to be '<domainserver>\admin'. In fact whenever I use any other <user>from the domain user list the Identity field is replaced by forties\<user>.

If this is not a bug then how on earth could you create a Domain Level Credential?

Any suggestion?|||

That is weird. I can't repro this problem.

Do you have permissions (BOL says "Requires ALTER ANY CREDENTIAL permission to create or modify a credential. Requires ALTER ANY LOGIN permission to map a login to a credential.")

Anyway, I'm not an expert on Agent Proxy account, please try this question in Tools forum:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=84&SiteID=1

|||

Yes, I do have full permission. As suggested by (Michael) I have added a new thread at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2160775&SiteID=1 , but it is not going anywhere.

By the way, the sql server and agent are running under LocalSystem account. Will this be the problem? Will reinstalling the SQL Server using a window domain user resolve the issue? Come on Microsoft, Please advise.

Problem with Accessing Unix Share Drive When a SSIS Job Runs

Hi I am trying to schedule a job to copy an MDB data file from Unix server to Windows 2003 server (Accfp1_data2_server). I have created a file copy SSIS package and tested it in the SSIS Visual Studio environment where it runs ok. The package was created while logged in as a domain administrator.

I then created a job to run this package (which is stored on a folder) using the credential of the same domain administrator who has full access privilege to both of these servers. However, the job fails whenever it is run manually or scheduled? The error message displayed is given below

Message
Executed as user: FORTIES\ABCITYG.
Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005.
All rights reserved. Started: 14:26:07 Error: 2007-09-13 14:26:12.56 Code: 0xC001401E
Source: CommunityContact - Copy MS Access Database Connection manager "CONTACT.mdb On Accfp1_data2_server"
Description: The file name "\\Accfp1_data2_server\DATA2\Arts&rec\Apps\Contacts\CONTACT.mdb" specified in the connection was not valid.
End Error Error: 2007-09-13 14:26:12.56 Code: 0xC001401D Source: CommunityContact - Copy MS Access Database Description: Connection "CONTACT.mdb On Accfp1_data2_server" failed validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 14:26:07 Finished: 14:26:12 Elapsed: 5.297 seconds. The package execution failed. The step failed.

Please note that the job runs without problem when I change the source file to a Windows 2000 server share . How bizzare? Hope this is not a Microsoft's Trick?

Can anyone help?

Just to check, the FORTIES\ABCITYG account is the domain administrator that has access to this share?

|||

Thanks for pointing to this. Apology for my dyslexic reading of the error message. This account does not have access. I will ask our team to look into this.

I overlooked that the job was running under FORTIES\ABCITYG (local) account. It is weird because when I created the credential I had entered a different domain admin account but I noticed that the identiry has been automatically reverted to FORTIES\ABCITYG account. In fact I recreated the credential with domainserver\abcityg but the identity for this account is automatically refreshed with FORTIES\ABCITYG again and again. Any guess?

|||

Just to emphasise the fact that I am unable to create a new credential that uses other domain user. (I used the option menu Security/Credential) . And this appears to be the root of the problem.

I can select a user who is not a user in the current server (forties) but is a domain admin (<domainserver>\admin) from "select User or Group" window. But when I click on OK button the Identity field displays 'forties\admin'. How bizzare? I would have expected it to be '<domainserver>\admin'. In fact whenever I use any other <user>from the domain user list the Identity field is replaced by forties\<user>.

If this is not a bug then how on earth could you create a Domain Level Credential?

Any suggestion?|||

That is weird. I can't repro this problem.

Do you have permissions (BOL says "Requires ALTER ANY CREDENTIAL permission to create or modify a credential. Requires ALTER ANY LOGIN permission to map a login to a credential.")

Anyway, I'm not an expert on Agent Proxy account, please try this question in Tools forum:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=84&SiteID=1

|||

Yes, I do have full permission. As suggested by (Michael) I have added a new thread at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2160775&SiteID=1 , but it is not going anywhere.

By the way, the sql server and agent are running under LocalSystem account. Will this be the problem? Will reinstalling the SQL Server using a window domain user resolve the issue? Come on Microsoft, Please advise.

Problem with Accessing Unix Share Drive When a SSIS Job Runs

Hi I am trying to schedule a job to copy an MDB data file from Unix server to Windows 2003 server (Accfp1_data2_server). I have created a file copy SSIS package and tested it in the SSIS Visual Studio environment where it runs ok. The package was created while logged in as a domain administrator.

I then created a job to run this package (which is stored on a folder) using the credential of the same domain administrator who has full access privilege to both of these servers. However, the job fails whenever it is run manually or scheduled? The error message displayed is given below

Message
Executed as user: FORTIES\ABCITYG.
Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005.
All rights reserved. Started: 14:26:07 Error: 2007-09-13 14:26:12.56 Code: 0xC001401E
Source: CommunityContact - Copy MS Access Database Connection manager "CONTACT.mdb On Accfp1_data2_server"
Description: The file name "\\Accfp1_data2_server\DATA2\Arts&rec\Apps\Contacts\CONTACT.mdb" specified in the connection was not valid.
End Error Error: 2007-09-13 14:26:12.56 Code: 0xC001401D Source: CommunityContact - Copy MS Access Database Description: Connection "CONTACT.mdb On Accfp1_data2_server" failed validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 14:26:07 Finished: 14:26:12 Elapsed: 5.297 seconds. The package execution failed. The step failed.

Please note that the job runs without problem when I change the source file to a Windows 2000 server share . How bizzare? Hope this is not a Microsoft's Trick?

Can anyone help?

Just to check, the FORTIES\ABCITYG account is the domain administrator that has access to this share?

|||

Thanks for pointing to this. Apology for my dyslexic reading of the error message. This account does not have access. I will ask our team to look into this.

I overlooked that the job was running under FORTIES\ABCITYG (local) account. It is weird because when I created the credential I had entered a different domain admin account but I noticed that the identiry has been automatically reverted to FORTIES\ABCITYG account. In fact I recreated the credential with domainserver\abcityg but the identity for this account is automatically refreshed with FORTIES\ABCITYG again and again. Any guess?

|||

Just to emphasise the fact that I am unable to create a new credential that uses other domain user. (I used the option menu Security/Credential) . And this appears to be the root of the problem.

I can select a user who is not a user in the current server (forties) but is a domain admin (<domainserver>\admin) from "select User or Group" window. But when I click on OK button the Identity field displays 'forties\admin'. How bizzare? I would have expected it to be '<domainserver>\admin'. In fact whenever I use any other <user>from the domain user list the Identity field is replaced by forties\<user>.

If this is not a bug then how on earth could you create a Domain Level Credential?

Any suggestion?|||

That is weird. I can't repro this problem.

Do you have permissions (BOL says "Requires ALTER ANY CREDENTIAL permission to create or modify a credential. Requires ALTER ANY LOGIN permission to map a login to a credential.")

Anyway, I'm not an expert on Agent Proxy account, please try this question in Tools forum:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=84&SiteID=1

|||

Yes, I do have full permission. As suggested by (Michael) I have added a new thread at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2160775&SiteID=1 , but it is not going anywhere.

By the way, the sql server and agent are running under LocalSystem account. Will this be the problem? Will reinstalling the SQL Server using a window domain user resolve the issue? Come on Microsoft, Please advise.

sql

Problem with .mdf File

Hi,

i got a .mdf file with a database
but i dont have the transaction-protocol.

Is it possible to recover this database?

Thanks
Philipp"Philipp Pongratz" <dontmailme@.nospam.de> wrote in message
news:btj5pg$a7p$05$1@.news.t-online.com...
> Hi,
> i got a .mdf file with a database
> but i dont have the transaction-protocol.
> Is it possible to recover this database?

"Maybe".

You can try a sp_attach_single_file_db.

If that does not work, you can always call MS Support and pay for an answer.
But note that it's likely in that case you'll end up with a DB that has
inconsistencies.

> Thanks
> Philipp

Problem with "phantom rows"

Hi,

I am seeing something strange. I have a data file that has 6 rows that looks like:

ABC, "OPENING BALANCE", 1234, etc

ABC, "CLOSING BALANCE", 1235, etc

ABC, garbage data, etc

XYZ, "OPENING BALANCE", 1234, etc

XYZ, "CLOSING BALANCE", 1235, etc

[][] -- weird box things that shows up in my flat file conn mgr

I have a script transformation that reads the incoming rows as a single line, then checks for the value of the row.line, whether it's "OPENING BALANCE", or "CLOSING BALANCE". It ignores all other lines.

I even added a message box that pops up when it finds "OPENING BALANCE" or "CLOSING BALANCE". It only pops up 4 times, like it should.

However, when I check the database, it has 6 rows! The 4 good rows are there, and 2 garbage rows with a bunch of NULLS in them.

I really don't understand how this is happening. Please, any ideas.

Thanks

A script transformation doesn't block rows. Use a conditional split instead.|||

Hi,

Are you saying to get rid of my script component and use a conditional split instead?

I would have no clue how to do this, though!

If you could show an example syntax, I would appreciate greatly.

Thanks

|||A conditional split component just uses expressions to test rows. If the expression evaluates to true, then the row goes down that output path.

Since you are working with the row as one big column, here's a sample config:
OUTPUT NAME: BalanceRows
Expression: FINDSTRING("OPENING BALANCE",[Column],1) > 0 || FINDSTRING("ENDING BALANCE",[Column],1) > 0

Then, back in the data flow, just grab the green arrow and hook it to the next component in line. It will prompt you for which output you want to use. Select the "BalanceRows" output.|||

Ok, but then how do I break up the line into columns so that I can map them to my table columns?

Use a script component after that?

|||

sadie519590 wrote:

Ok, but then how do I break up the line into columns so that I can map them to my table columns?

Use a script component after that?

Sure. Or use a derived column and use substrings.|||

Could you explain how to set up the derived column? I don't see how you specify the input or output in this case.

Thanks

|||Never mind, I see now|||

Yes, I see how I could do this, if only I could find some DOCUMENTATION on SSIS expressions.

I don't understand why Microsoft creates something that is so specific that only they can provide documentation, but then they don't bother adding any documentation. This is incredibly frustrating.

I don't see one single example of how to use "SUBSTRING", if there even is such a thing, because I can't find ANY information on using string functions in expressions.

Help.

:-(

|||When you click on substring in the list of available functions, it tells you how to use it. I know you're frustrated with this, but it's right there.

And for that matter, it's all in BOOKS ONLINE. The first link returned when SEARCHING for "substring ssis" yielded the page you apparently think is missing.|||

Ok, after a ridiculous amount of searching, I found the reference page I was looking for.

But I REALLY don't see how I can possibly use substring or findstring to parse my row because it requires that you know what you're looking for first, which I don't.

SUBSTRING(character_expression, position, length)

That is, you have to provide the character_expression which I don't have because each row is different.

Is this what you really mean? Because it doesn't look like it's gonna work.

|||

I am just going to use my script, since I can use the index to select which columns I want to use.

I think using an expression to do this would be very complicated, as the values would have to be determined by the number of commas found, or something like that.

Anyways, even before I get to that issue, I am bummed b/c the conditional split isn't working.

This is my expression:

FINDSTRING("OPENING",Column0,1) > 0 || FINDSTRING("CLOSING",Column0,1) > 0

My data viewer shows nothing being sent to the next component after the conditional split.

Any ideas why?

|||character_expression is your row, or column since you are reading in the row as one column.

If it's not a fixed width row and positions change, then use the script and however you were going to do it before.|||

Yes, I think the script is easier.

But any ideas why the conditional split isn't working as expected?

Thanks

|||

Aha, I see you're just keeping me on my toes :-)

It's like this: FINDSTRING("CLOSING",Column0,1) > 0

character expression comes first, then search string