bcp unicode files using format files

I have a problem with bcp and format files.

We changed our databases from varchar to nvarchar to support unicode. No

problems so fare with that. It is working fine.

But now I need a format file for the customer table and and it is not
working. It is working fine with the old DB with varchar, but with
nvarchar I'm not able to copy the data. The biggest problem is, that I
got no error message. BCP starts copying to table and finished without
error message.

This is my table:

CREATE TABLE [dbo].[Customer] (
[ID] [int] NOT NULL ,
[CreationTime] [datetime] NULL ,
[ModificationTime] [datetime] NULL ,
[DiscoveryTime] [datetime] NULL ,
[Name_] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[Class] [int] NULL ,
[Subclass] [int] NULL ,
[Capabilities] [int] NULL ,
[SnapshotID] [int] NOT NULL ,
[CompanyName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NOT
NULL ,
[TargetRCCountry] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI
NOT NULL ,
[LocationID] [int] NULL ,
[MirrorID] [binary] (16) NULL ,
[DeleteFlag] [bit] NULL ,
[AdminStatus] [bit] NULL
) ON [PRIMARY]
GO

and this is the format file:

8.0
13
1 SQLINT 1 12 "#~@.~#" 1 ID ""
2 SQLDATETIME 1 24 "#~@.~#" 2 CreationTime ""
3 SQLDATETIME 1 24 "#~@.~#" 3 ModificationTime ""
4 SQLDATETIME 1 24 "#~@.~#" 4 DiscoveryTime ""
5 SQLNCHAR 2 510 "#~@.~#" 5 Name_
SQL_Latin1_General_CP1_CI_AS
6 SQLINT 1 12 "#~@.~#" 6 Class ""
7 SQLINT 1 12 "#~@.~#" 7 Subclass ""
8 SQLINT 1 12 "#~@.~#" 8 Capabilities ""
9 SQLINT 1 12 "#~@.~#" 9 SnapshotID ""
10 SQLNCHAR 2 510 "#~@.~#" 10 CompanyName
SQL_Latin1_General_CP1_CI_AS
11 SQLNCHAR 2 510 "#~@.~#" 11 TargetRCCountry
SQL_Latin1_General_CP1_CI_AS
12 SQLINT 1 12 "#~@.~#" 12 LocationID ""
13 SQLBINARY 1 33 "#~@.~# \r \r \n"13 MirrorID ""

"#~@.~#" is the field terminator. We have a lot of text files with all
kind of charachers in it. So we think this is a set that will never
occur in our files.

Thanks for your help!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Bernd Lambertz <virus@.securiy.com> wrote in message news:<3fb38797$0$196$75868355@.news.frii.net>...
> I have a problem with bcp and format files.
> We changed our databases from varchar to nvarchar to support unicode. No
> problems so fare with that. It is working fine.
> But now I need a format file for the customer table and and it is not
> working. It is working fine with the old DB with varchar, but with
> nvarchar I'm not able to copy the data. The biggest problem is, that I
> got no error message. BCP starts copying to table and finished without
> error message.
> This is my table:
> CREATE TABLE [dbo].[Customer] (
> [ID] [int] NOT NULL ,
> [CreationTime] [datetime] NULL ,
> [ModificationTime] [datetime] NULL ,
> [DiscoveryTime] [datetime] NULL ,
> [Name_] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> [Class] [int] NULL ,
> [Subclass] [int] NULL ,
> [Capabilities] [int] NULL ,
> [SnapshotID] [int] NOT NULL ,
> [CompanyName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NOT
> NULL ,
> [TargetRCCountry] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI
> NOT NULL ,
> [LocationID] [int] NULL ,
> [MirrorID] [binary] (16) NULL ,
> [DeleteFlag] [bit] NULL ,
> [AdminStatus] [bit] NULL
> ) ON [PRIMARY]
> GO
> and this is the format file:
> 8.0
> 13
> 1 SQLINT 1 12 "#~@.~#" 1 ID ""
> 2 SQLDATETIME 1 24 "#~@.~#" 2 CreationTime ""
> 3 SQLDATETIME 1 24 "#~@.~#" 3 ModificationTime ""
> 4 SQLDATETIME 1 24 "#~@.~#" 4 DiscoveryTime ""
> 5 SQLNCHAR 2 510 "#~@.~#" 5 Name_
> SQL_Latin1_General_CP1_CI_AS
> 6 SQLINT 1 12 "#~@.~#" 6 Class ""
> 7 SQLINT 1 12 "#~@.~#" 7 Subclass ""
> 8 SQLINT 1 12 "#~@.~#" 8 Capabilities ""
> 9 SQLINT 1 12 "#~@.~#" 9 SnapshotID ""
> 10 SQLNCHAR 2 510 "#~@.~#" 10 CompanyName
> SQL_Latin1_General_CP1_CI_AS
> 11 SQLNCHAR 2 510 "#~@.~#" 11 TargetRCCountry
> SQL_Latin1_General_CP1_CI_AS
> 12 SQLINT 1 12 "#~@.~#" 12 LocationID ""
> 13 SQLBINARY 1 33 "#~@.~# \r \r \n"13 MirrorID ""
> "#~@.~#" is the field terminator. We have a lot of text files with all
> kind of charachers in it. So we think this is a set that will never
> occur in our files.
> Thanks for your help!
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Your table has 15 columns, but the format file has 13 - according to
"Using a Data File with Fewer Fields" in Books Online you should still
have all the columns in the format file, but use 0 for the field
length and column number of the columns you don't want to load. You
should also not specify a terminator for those columns.

If doing that doesn't resolve the issue, perhaps you could post the
BCP command you're using, as well as the error and out files?

Simon|||I tried it with 15 columns but with the same result.

I'm wondering if you have a look at the Online Books page you mentioned they have only SQLCHAR typs for
everything. Okay it is a text file with char in it. But if I have a look at the description on the BCP
page the use SQLINT and SQLBINARY...

What is the right way here. I tried both without success.....

The bcp command is running as a batch job and I get no error. It just looks like a time out,
but no message. So I tried BULK COPY in the query analyzer now:
BULK INSERT unicode_db..customer FROM 'C:\inbox\csv_import\customer.csv'
WITH (FORMATFILE = 'C:\inbox\csv_import\formats\customer.fmt', DATAFILETYPE = 'widechar')

and this is the result:

Server: Msg 4832, Level 16, State 1, Line 1
Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005: The provider
did not give any information about the error.].
The statement has been terminated.

By the way, I'm using the same stuff for NON-UNICODE with a formatfile with 13 columns all SQLCHAR and
the same table (just VARCHAR instead of NVARCHAR)
and it works fine......

Is that the problem that I have a .csv file saved as unicode and the .fmt file saved as ANSI?
Is the field terminator '#~@.~#' a problem and not supported?
BCP only except ANSI format files??

Simon Hayes wrote:

> Bernd Lambertz <virus@.securiy.com> wrote in message news:<3fb38797$0$196$75868355@.news.frii.net>...
> > I have a problem with bcp and format files.
> > We changed our databases from varchar to nvarchar to support unicode. No
> > problems so fare with that. It is working fine.
> > But now I need a format file for the customer table and and it is not
> > working. It is working fine with the old DB with varchar, but with
> > nvarchar I'm not able to copy the data. The biggest problem is, that I
> > got no error message. BCP starts copying to table and finished without
> > error message.
> > This is my table:
> > CREATE TABLE [dbo].[Customer] (
> > [ID] [int] NOT NULL ,
> > [CreationTime] [datetime] NULL ,
> > [ModificationTime] [datetime] NULL ,
> > [DiscoveryTime] [datetime] NULL ,
> > [Name_] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
> > [Class] [int] NULL ,
> > [Subclass] [int] NULL ,
> > [Capabilities] [int] NULL ,
> > [SnapshotID] [int] NOT NULL ,
> > [CompanyName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NOT
> > NULL ,
> > [TargetRCCountry] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI
> > NOT NULL ,
> > [LocationID] [int] NULL ,
> > [MirrorID] [binary] (16) NULL ,
> > [DeleteFlag] [bit] NULL ,
> > [AdminStatus] [bit] NULL
> > ) ON [PRIMARY]
> > GO
> > and this is the format file:
> > 8.0
> > 13
> > 1 SQLINT 1 12 "#~@.~#" 1 ID ""
> > 2 SQLDATETIME 1 24 "#~@.~#" 2 CreationTime ""
> > 3 SQLDATETIME 1 24 "#~@.~#" 3 ModificationTime ""
> > 4 SQLDATETIME 1 24 "#~@.~#" 4 DiscoveryTime ""
> > 5 SQLNCHAR 2 510 "#~@.~#" 5 Name_
> > SQL_Latin1_General_CP1_CI_AS
> > 6 SQLINT 1 12 "#~@.~#" 6 Class ""
> > 7 SQLINT 1 12 "#~@.~#" 7 Subclass ""
> > 8 SQLINT 1 12 "#~@.~#" 8 Capabilities ""
> > 9 SQLINT 1 12 "#~@.~#" 9 SnapshotID ""
> > 10 SQLNCHAR 2 510 "#~@.~#" 10 CompanyName
> > SQL_Latin1_General_CP1_CI_AS
> > 11 SQLNCHAR 2 510 "#~@.~#" 11 TargetRCCountry
> > SQL_Latin1_General_CP1_CI_AS
> > 12 SQLINT 1 12 "#~@.~#" 12 LocationID ""
> > 13 SQLBINARY 1 33 "#~@.~# \r \r \n"13 MirrorID ""
> > "#~@.~#" is the field terminator. We have a lot of text files with all
> > kind of charachers in it. So we think this is a set that will never
> > occur in our files.
> > Thanks for your help!
> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!
> Your table has 15 columns, but the format file has 13 - according to
> "Using a Data File with Fewer Fields" in Books Online you should still
> have all the columns in the format file, but use 0 for the field
> length and column number of the columns you don't want to load. You
> should also not specify a terminator for those columns.
> If doing that doesn't resolve the issue, perhaps you could post the
> BCP command you're using, as well as the error and out files?
> Simonsql

bcp out with column name.

Hi,
I need make a scheduling job for bcp export data from databases' table to a
text file with column name. Does anyone know any method I can use?
Regards!
-ChenFirst I would try to create a stored procedure that uses xp_cmdshell to
execute a BCP? You could also create a linked server to an ODBC driver
for text, and insert records into that from a stored procedure. Either
method could then be scheduled. I'm sure there are other ways too.
You didn't mention which SQL Server you have, but I'm sure there is a
DTS solution and / or an Integration solution. Pick your poison!
Good luck...
-Rick|||Chen,
May be using osql.exe
Example:
c:\>osql -S(local) -E -Q"select orderid from northwind.dbo.orders"
-o"c:\temp\test.txt
AMB
"Chen" wrote:
> Hi,
> I need make a scheduling job for bcp export data from databases' table to a
> text file with column name. Does anyone know any method I can use?
> Regards!
> -Chensql

bcp out with column name.

Hi,
I need make a scheduling job for bcp export data from databases' table to a
text file with column name. Does anyone know any method I can use?
Regards!
-ChenFirst I would try to create a stored procedure that uses xp_cmdshell to
execute a BCP? You could also create a linked server to an ODBC driver
for text, and insert records into that from a stored procedure. Either
method could then be scheduled. I'm sure there are other ways too.
You didn't mention which SQL Server you have, but I'm sure there is a
DTS solution and / or an Integration solution. Pick your poison!
Good luck...
-Rick|||Chen,
May be using osql.exe
Example:
c:\>osql -S(local) -E -Q"select orderid from northwind.dbo.orders"
-o"c:\temp\test.txt
AMB
"Chen" wrote:

> Hi,
> I need make a scheduling job for bcp export data from databases' table to
a
> text file with column name. Does anyone know any method I can use?
> Regards!
> -Chen

BCP IN with text/ntext column fails! Is this is a bug?

Hi,

We are in process to test SQL Server 2005 migration; everything seems to work very well,
except one job that transfer data between two databases using BCP tool.
In fact the BCP fail only for one table that have ntext column, If I use the table directly in BCP OUT/IN command it works fine, but if I use a View it fail (I'm using view because the ORDER of columns in target database could be different than source database).

This is the error I receive:
SQLState = S1000, NativeError = 606
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Metadata inconsistency. Filegroup id 0 specified for table '' does not exist. Run DBCC CHECKDB or CHECKCATALOG.

Note : If I change the ntext column to nvarchar(max) it works very well

Is this is a known bug ?
Do I have to change my ntext/text columns to nvarchar(max)/varchar(max) types ?

Any advice is welcome
Thank you.

I have repro-ed the proble. However I have a workaround.

Try BCP IN the data from the BCP file into a table with the same column order as the view, and it will work.

Now create a view on this table. This will resolve your problem.

|||

We have the same problem. The problem with the workaround is that it is very inefficient. If you wanted to do this same thing on tables that have millions of rows, it means you now have to create a table, load the new table and then transfer the data from this new table to the one you want, and delete the new table, instead of using a view like you could in SQL 2000, SQL 7.5 and SQL 6.5. Not only is that a lot slower, but it consumes a lot more disk space (assuming your tables with Text or image data contain lots of data)

It seems like an obvious bug to me. The documentation for BCP has always allowed you to specify a view instead of a table. I can't image why anyone would design this to not work for image or text data in SQL 2005.

BCP IN with text/ntext column fails! Is this is a bug?

Hi,

We are in process to test SQL Server 2005 migration; everything seems to work very well,
except one job that transfer data between two databases using BCP tool.
In fact the BCP fail only for one table that have ntext column, If I use the table directly in BCP OUT/IN command it works fine, but if I use a View it fail (I'm using view because the ORDER of columns in target database could be different than source database).

This is the error I receive:
SQLState = S1000, NativeError = 606
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Metadata inconsistency. Filegroup id 0 specified for table '' does not exist. Run DBCC CHECKDB or CHECKCATALOG.

Note : If I change the ntext column to nvarchar(max) it works very well

Is this is a known bug ?
Do I have to change my ntext/text columns to nvarchar(max)/varchar(max) types ?

Any advice is welcome
Thank you.

I have repro-ed the proble. However I have a workaround.

Try BCP IN the data from the BCP file into a table with the same column order as the view, and it will work.

Now create a view on this table. This will resolve your problem.

|||

We have the same problem. The problem with the workaround is that it is very inefficient. If you wanted to do this same thing on tables that have millions of rows, it means you now have to create a table, load the new table and then transfer the data from this new table to the one you want, and delete the new table, instead of using a view like you could in SQL 2000, SQL 7.5 and SQL 6.5. Not only is that a lot slower, but it consumes a lot more disk space (assuming your tables with Text or image data contain lots of data)

It seems like an obvious bug to me. The documentation for BCP has always allowed you to specify a view instead of a table. I can't image why anyone would design this to not work for image or text data in SQL 2005.

BCP IN with text/ntext column fails! Is this is a bug?

Hi,

We are in process to test SQL Server 2005 migration; everything seems to work very well,
except one job that transfer data between two databases using BCP tool.
In fact the BCP fail only for one table that have ntext column, If I use the table directly in BCP OUT/IN command it works fine, but if I use a View it fail (I'm using view because the ORDER of columns in target database could be different than source database).

This is the error I receive:
SQLState = S1000, NativeError = 606
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Metadata inconsistency. Filegroup id 0 specified for table '' does not exist. Run DBCC CHECKDB or CHECKCATALOG.

Note : If I change the ntext column to nvarchar(max) it works very well

Is this is a known bug ?
Do I have to change my ntext/text columns to nvarchar(max)/varchar(max) types ?

Any advice is welcome
Thank you.

I have repro-ed the proble. However I have a workaround.

Try BCP IN the data from the BCP file into a table with the same column order as the view, and it will work.

Now create a view on this table. This will resolve your problem.

|||

We have the same problem. The problem with the workaround is that it is very inefficient. If you wanted to do this same thing on tables that have millions of rows, it means you now have to create a table, load the new table and then transfer the data from this new table to the one you want, and delete the new table, instead of using a view like you could in SQL 2000, SQL 7.5 and SQL 6.5. Not only is that a lot slower, but it consumes a lot more disk space (assuming your tables with Text or image data contain lots of data)

It seems like an obvious bug to me. The documentation for BCP has always allowed you to specify a view instead of a table. I can't image why anyone would design this to not work for image or text data in SQL 2005.

BCP copy in failed...The SQL Server cannot obtain a LOCK resource

I have 2 identical databases (DB1, DB2) in SQL Server 2000 (OS:
Windows 2003 with SP1). I want to copy some data from DB1 to DB2 with
BCP.
I BCP out the data to the data files no problem with the following
command:
bcp "SELECT * FROM DB1.dbo.Table1 WHERE id>=587738400000000000"
queryout Table1.dat -n -Usa -Ppass -Shost -eTable1_out_error.txt
However, I failed to BCP in the data to the database with this
command:
bcp DB2.dbo.Table1 in Table1.dat -n -Usa -Ppass -Shost -
eTable1_in_error.txt
Here is the error message:
<SKIP>
1000 rows sent to SQL Server. Total sent: 141497000
SQLState = S1000, NativeError = 1204
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The SQL Server
cannot obtain a LOCK resource at this time. Rerun your statement when
there are fewer active users or ask the system administrator to check
the SQL Server lock and memory configuration.
BCP copy in failed
The error files "Table1_out_error.txt" and "Table1_in_error.txt" are
empty.
I had used BCP to copy similar (type and among of) data to an Oracle
database without any problem in the same server. Am I messing
something here?
Any help will be highly appreciated.
Helen
On Jul 5, 9:31 am, hele...@.gmail.com wrote:
> I have 2 identical databases (DB1, DB2) in SQL Server 2000 (OS:
> Windows 2003 with SP1). I want to copy some data from DB1 to DB2 with
> BCP.
> I BCP out the data to the data files no problem with the following
> command:
> bcp "SELECT * FROM DB1.dbo.Table1 WHERE id>=587738400000000000"
> queryout Table1.dat -n -Usa -Ppass -Shost -eTable1_out_error.txt
> However, I failed to BCP in the data to the database with this
> command:
> --
> bcp DB2.dbo.Table1 in Table1.dat -n -Usa -Ppass -Shost -
> eTable1_in_error.txt
> --
> Here is the error message:
> --
> <SKIP>
> 1000 rows sent to SQL Server. Total sent: 141497000
> SQLState = S1000, NativeError = 1204
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The SQL Server
> cannot obtain a LOCK resource at this time. Rerun your statement when
> there are fewer active users or ask the system administrator to check
> the SQL Server lock and memory configuration.
> BCP copy in failed
> --
> The error files "Table1_out_error.txt" and "Table1_in_error.txt" are
> empty.
> I had used BCP to copy similar (type and among of) data to an Oracle
> database without any problem in the same server. Am I messing
> something here?
> Any help will be highly appreciated.
> Helen
Your server is running out of memory either because of your actions or
because of someone else's actions.
use sp_lock to check who is doing it .
Cheers,

BCP copy in failed...The SQL Server cannot obtain a LOCK resource

I have 2 identical databases (DB1, DB2) in SQL Server 2000 (OS:
Windows 2003 with SP1). I want to copy some data from DB1 to DB2 with
BCP.
I BCP out the data to the data files no problem with the following
command:
bcp "SELECT * FROM DB1.dbo.Table1 WHERE id>=587738400000000000"
queryout Table1.dat -n -Usa -Ppass -Shost -eTable1_out_error.txt
However, I failed to BCP in the data to the database with this
command:
--
bcp DB2.dbo.Table1 in Table1.dat -n -Usa -Ppass -Shost -
eTable1_in_error.txt
--
Here is the error message:
--
<SKIP>
1000 rows sent to SQL Server. Total sent: 141497000
SQLState = S1000, NativeError = 1204
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The SQL S
erver
cannot obtain a LOCK resource at this time. Rerun your statement when
there are fewer active users or ask the system administrator to check
the SQL Server lock and memory configuration.
BCP copy in failed
--
The error files "Table1_out_error.txt" and "Table1_in_error.txt" are
empty.
I had used BCP to copy similar (type and among of) data to an Oracle
database without any problem in the same server. Am I messing
something here?
Any help will be highly appreciated.
HelenOn Jul 5, 9:31 am, hele...@.gmail.com wrote:
> I have 2 identical databases (DB1, DB2) in SQL Server 2000 (OS:
> Windows 2003 with SP1). I want to copy some data from DB1 to DB2 with
> BCP.
> I BCP out the data to the data files no problem with the following
> command:
> bcp "SELECT * FROM DB1.dbo.Table1 WHERE id>=587738400000000000"
> queryout Table1.dat -n -Usa -Ppass -Shost -eTable1_out_error.txt
> However, I failed to BCP in the data to the database with this
> command:
> --
> bcp DB2.dbo.Table1 in Table1.dat -n -Usa -Ppass -Shost -
> eTable1_in_error.txt
> --
> Here is the error message:
> --
> <SKIP>
> 1000 rows sent to SQL Server. Total sent: 141497000
> SQLState = S1000, NativeError = 1204
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The SQL
Server
> cannot obtain a LOCK resource at this time. Rerun your statement when
> there are fewer active users or ask the system administrator to check
> the SQL Server lock and memory configuration.
> BCP copy in failed
> --
> The error files "Table1_out_error.txt" and "Table1_in_error.txt" are
> empty.
> I had used BCP to copy similar (type and among of) data to an Oracle
> database without any problem in the same server. Am I messing
> something here?
> Any help will be highly appreciated.
> Helen
Your server is running out of memory either because of your actions or
because of someone else's actions.
use sp_lock to check who is doing it .
Cheers,

BCP copy in failed...The SQL Server cannot obtain a LOCK resource

I have 2 identical databases (DB1, DB2) in SQL Server 2000 (OS:
Windows 2003 with SP1). I want to copy some data from DB1 to DB2 with
BCP.
I BCP out the data to the data files no problem with the following
command:
bcp "SELECT * FROM DB1.dbo.Table1 WHERE id>=587738400000000000"
queryout Table1.dat -n -Usa -Ppass -Shost -eTable1_out_error.txt
However, I failed to BCP in the data to the database with this
command:
--
bcp DB2.dbo.Table1 in Table1.dat -n -Usa -Ppass -Shost -
eTable1_in_error.txt
--
Here is the error message:
--
<SKIP>
1000 rows sent to SQL Server. Total sent: 141497000
SQLState = S1000, NativeError = 1204
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The SQL Server
cannot obtain a LOCK resource at this time. Rerun your statement when
there are fewer active users or ask the system administrator to check
the SQL Server lock and memory configuration.
BCP copy in failed
--
The error files "Table1_out_error.txt" and "Table1_in_error.txt" are
empty.
I had used BCP to copy similar (type and among of) data to an Oracle
database without any problem in the same server. Am I messing
something here?
Any help will be highly appreciated.
HelenOn Jul 5, 9:31 am, hele...@.gmail.com wrote:
> I have 2 identical databases (DB1, DB2) in SQL Server 2000 (OS:
> Windows 2003 with SP1). I want to copy some data from DB1 to DB2 with
> BCP.
> I BCP out the data to the data files no problem with the following
> command:
> bcp "SELECT * FROM DB1.dbo.Table1 WHERE id>=587738400000000000"
> queryout Table1.dat -n -Usa -Ppass -Shost -eTable1_out_error.txt
> However, I failed to BCP in the data to the database with this
> command:
> --
> bcp DB2.dbo.Table1 in Table1.dat -n -Usa -Ppass -Shost -
> eTable1_in_error.txt
> --
> Here is the error message:
> --
> <SKIP>
> 1000 rows sent to SQL Server. Total sent: 141497000
> SQLState = S1000, NativeError = 1204
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The SQL Server
> cannot obtain a LOCK resource at this time. Rerun your statement when
> there are fewer active users or ask the system administrator to check
> the SQL Server lock and memory configuration.
> BCP copy in failed
> --
> The error files "Table1_out_error.txt" and "Table1_in_error.txt" are
> empty.
> I had used BCP to copy similar (type and among of) data to an Oracle
> database without any problem in the same server. Am I messing
> something here?
> Any help will be highly appreciated.
> Helen
Your server is running out of memory either because of your actions or
because of someone else's actions.
use sp_lock to check who is doing it .
Cheers,

Batch file check database available during weekend.

Hi Everyone,
I need create the batch file to check sql server databases available during
the weekend.
I try to do run query use osql in batch file and the result write to a log
file. And check if the log file include "fail" string then email or page
someone, if not that means database is OK. Which batch command can implement
this check?
Any input much appreciates!
ChenYou can run a simple query (any simple query) accessing a table (such as
sysobjects or perhaps another smaller ) in that database.
Personally, I don't think it's a good idea to use a batch file for this
purpose because batch files are rather limited in their ability to handle
errors returned from a program. I'd use a real programming language, connect
to the SQL instance using ADO or ADO.NET, and walk through the returned erro
r
messages to verify that there is indeed a problem with accessing the databas
e.
Linchi
"Chen" wrote:

> Hi Everyone,
> I need create the batch file to check sql server databases available durin
g
> the weekend.
> I try to do run query use osql in batch file and the result write to a log
> file. And check if the log file include "fail" string then email or page
> someone, if not that means database is OK. Which batch command can impleme
nt
> this check?
> Any input much appreciates!
> Chen|||For service availability monitoring better option is to go for tools like:-
NetIQ'a Diagnostic Mangager
or
BMC Patrol
Thanks
Hari
SQL Server MVP
"Chen" wrote:

> Hi Everyone,
> I need create the batch file to check sql server databases available durin
g
> the weekend.
> I try to do run query use osql in batch file and the result write to a log
> file. And check if the log file include "fail" string then email or page
> someone, if not that means database is OK. Which batch command can impleme
nt
> this check?
> Any input much appreciates!
> Chen|||"Chen" <Chen@.discussions.microsoft.com> wrote in message
news:31D219AE-B65C-47AD-AAF2-BB80D73FBDE2@.microsoft.com...
> Hi Everyone,
> I need create the batch file to check sql server databases available
during
> the weekend.
> I try to do run query use osql in batch file and the result write to a log
> file. And check if the log file include "fail" string then email or page
> someone, if not that means database is OK. Which batch command can
implement
> this check?
We use Servers Alive product for this (and many other things.)

> Any input much appreciates!
> Chen

Batch file check database available during weekend.

Hi Everyone,
I need create the batch file to check sql server databases available during
the weekend.
I try to do run query use osql in batch file and the result write to a log
file. And check if the log file include "fail" string then email or page
someone, if not that means database is OK. Which batch command can implement
this check?
Any input much appreciates!
ChenYou can run a simple query (any simple query) accessing a table (such as
sysobjects or perhaps another smaller ) in that database.
Personally, I don't think it's a good idea to use a batch file for this
purpose because batch files are rather limited in their ability to handle
errors returned from a program. I'd use a real programming language, connect
to the SQL instance using ADO or ADO.NET, and walk through the returned error
messages to verify that there is indeed a problem with accessing the database.
Linchi
"Chen" wrote:
> Hi Everyone,
> I need create the batch file to check sql server databases available during
> the weekend.
> I try to do run query use osql in batch file and the result write to a log
> file. And check if the log file include "fail" string then email or page
> someone, if not that means database is OK. Which batch command can implement
> this check?
> Any input much appreciates!
> Chen|||For service availability monitoring better option is to go for tools like:-
NetIQ'a Diagnostic Mangager
or
BMC Patrol
Thanks
Hari
SQL Server MVP
"Chen" wrote:
> Hi Everyone,
> I need create the batch file to check sql server databases available during
> the weekend.
> I try to do run query use osql in batch file and the result write to a log
> file. And check if the log file include "fail" string then email or page
> someone, if not that means database is OK. Which batch command can implement
> this check?
> Any input much appreciates!
> Chen|||"Chen" <Chen@.discussions.microsoft.com> wrote in message
news:31D219AE-B65C-47AD-AAF2-BB80D73FBDE2@.microsoft.com...
> Hi Everyone,
> I need create the batch file to check sql server databases available
during
> the weekend.
> I try to do run query use osql in batch file and the result write to a log
> file. And check if the log file include "fail" string then email or page
> someone, if not that means database is OK. Which batch command can
implement
> this check?
We use Servers Alive product for this (and many other things.)
> Any input much appreciates!
> Chen

Basic Security

I am trying to understand the basic security that RS uses.
During install, a userid is used to install/create the databases. That
userid is then tied to the basic services, like scheduling etc...
Is this correct?
Are all services using that first-supplied ID?
After that when users run a report with a connection that has the
credentials stored, are those the credentials used for the database
access?
I am currently using RS and have 2 shared connections (one to live dB
and one to Test dB), both with stored credentials. Is is safe to assume
that any report activity using that connection will show that userid?
I just want to make sure I can track down problems when they arise.
Thanks.The credentials you supply when you install RS are completely seperate from
the connections you use in your reports. They are only used so that the
report server can talk to the reportserver (metadata) database. A report
that is using stored credentials will use those credentials independent of
the setup credentials.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Geddes" <john_g@.alamode.com> wrote in message
news:eX2q$NR7EHA.4040@.TK2MSFTNGP14.phx.gbl...
>I am trying to understand the basic security that RS uses.
> During install, a userid is used to install/create the databases. That
> userid is then tied to the basic services, like scheduling etc...
> Is this correct?
> Are all services using that first-supplied ID?
> After that when users run a report with a connection that has the
> credentials stored, are those the credentials used for the database
> access?
>
> I am currently using RS and have 2 shared connections (one to live dB and
> one to Test dB), both with stored credentials. Is is safe to assume that
> any report activity using that connection will show that userid?
> I just want to make sure I can track down problems when they arise.
> Thanks.
>

basic restore question

Is it possible in SS2K5 to restore from a backup between databases with
different MDF/LDF file locations? I don't have the option to recreate the
original directory structure on the target.
PaulCheck Books Online for the MOVE option to RESTORE DATABASE command.
"PJ6" <noone@.nowhere.net> wrote in message
news:ujFvDp1BIHA.536@.TK2MSFTNGP06.phx.gbl...
> Is it possible in SS2K5 to restore from a backup between databases with
> different MDF/LDF file locations? I don't have the option to recreate the
> original directory structure on the target.
> Paul
>|||Never mind, I got it. I finally found an example of RESTORE with MOVE and
discovered that the original file names must be specified without a path or
extention.
Paul
"PJ6" <noone@.nowhere.net> wrote in message
news:ujFvDp1BIHA.536@.TK2MSFTNGP06.phx.gbl...
> Is it possible in SS2K5 to restore from a backup between databases with
> different MDF/LDF file locations? I don't have the option to recreate the
> original directory structure on the target.
> Paul
>

Basic Replication trouble.

I'm trying to run a simple update script on this database which is
created from a replication agent. Several individual databases are
replicated into one consolidated. I've not worked with replication
before and I was hoping someone could tell me what this error means.

UPDATE
CUSTOMER
SET
[TIMESTAMP]='20060920090453'
[IVBTYPE]='M '
WHERE
[ROWID]='9f83bc89-76f8-4140-a0cc-58fa34962638'

yields:

Server: Msg 208, Level 16, State 1, Procedure
upd_0119B5A9AA624A55AF1B73F2E32A7A0C, Line 14
Invalid object name 'dbo.sysmergearticles'.

Do I have to do something to the database before trying to update it?P.S. I notice I'm missing a comma in there after the first set, missed
it while I was copying it over and didn't notice until I posted.