BCP problem - help?

I'm attempting to run bcp to copy some files into a database and
receiving a format file error (below). I'm using someone else's format
file that I know already works for them, and I don't know what the
issue is. Below is the error, my bcp statement and format file....can
anyone tell what the problem is? (Note - some of the Collation
values are wrapped b/c of my newsreader - in the format file they are
all on the appropriate line and tab delimited). Thanks.
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP
format file
bcp TIGER..TIGER_01 in "c:\documents and settings\user\my
documents\geocoding\TGR37001.RT1" -f "c:\documents and settings\user\my
documents\geocoding\tiger1.txt" -S HOSTNAME -U sa -P Password -e
"c:\documents and settings\user\my
documents\geocoding\errors\TGR37001.RT1.error.txt" -m50000
8.0
44
1SQLCHAR01""1RTSQL_Latin1_General_CP1_CI_AS
2SQLCHAR04""2VERSIONNULL
3SQLCHAR010""3TLIDNULL
4SQLCHAR01""4SIDE1NULL
5SQLCHAR01""5SOURCESQL_Latin1_General_CP1_CI_AS
6SQLCHAR02""6FEDIRPSQL_Latin1_General_CP1_CI_AS
7SQLCHAR030""7FENAMESQL_Latin1_General_CP1_CI_AS
8SQLCHAR04""8FETYPESQL_Latin1_General_CP1_CI_AS
9SQLCHAR02""9FEDIRSSQL_Latin1_General_CP1_CI_AS
10SQLCHAR03""10CFCCSQL_Latin1_General_CP1_CI_AS
11SQLCHAR011""11FRADDLNULL
12SQLCHAR011""12TOADDLNULL
13SQLCHAR011""13FRADDRNULL
14SQLCHAR011""14TOADDRNULL
15SQLCHAR01""15FRIADDLSQL_Latin1_General_CP1_CI_AS
16SQLCHAR01""16TOIADDLSQL_Latin1_General_CP1_CI_AS
17SQLCHAR01""17FRIADDRSQL_Latin1_General_CP1_CI_AS
18SQLCHAR01""18TOIADDRSQL_Latin1_General_CP1_CI_AS
19SQLCHAR05""19ZIPLNULL
20SQLCHAR05""20ZIPRNULL
21SQLCHAR05""21AIANHHFPLNULL
22SQLCHAR05""22AIANHHFPRNULL
23SQLCHAR01""23AIHHTLILSQL_Latin1_General_CP1_CI_AS
24SQLCHAR01""24AIHHTLIRSQL_Latin1_General_CP1_CI_AS
25SQLCHAR01""25CENSUS1SQL_Latin1_General_CP1_CI_AS
26SQLCHAR01""26CENSUS2SQL_Latin1_General_CP1_CI_AS
27 SQLCHAR02""27STATELNULL
28SQLCHAR02""28STATERNULL
29SQLCHAR03""29COUNTYLNULL
30SQLCHAR03""30COUNTYRNULL
31SQLCHAR05""31COUSUBLNULL
32SQLCHAR05""32COUSUBRNULL
33SQLCHAR05""33SUBMCDLNULL
34SQLCHAR05""34SUBMCDRNULL
35SQLCHAR05""35PLACELNULL
36SQLCHAR05""36PLACERNULL
37SQLCHAR06""37TRACTLNULL
38SQLCHAR06""38TRACTRNULL
39SQLCHAR04""39BLOCKLNULL
40SQLCHAR04""40BLOCKRNULL
41SQLCHAR010""41FRLONGNULL
42SQLCHAR0 9""42FRLATNULL
43SQLCHAR010""43TOLONGNULL
44SQLCHAR09"\r\n"44TOLATNULL
The error message indicates an I/O error, are there any network problems, can
SQL Server see the file, does SQL Server have permissions to access the file?
http://sqlservercode.blogspot.com/
"Corey Bunch" wrote:

> I'm attempting to run bcp to copy some files into a database and
> receiving a format file error (below). I'm using someone else's format
> file that I know already works for them, and I don't know what the
> issue is. Below is the error, my bcp statement and format file....can
> anyone tell what the problem is? (Note - some of the Collation
> values are wrapped b/c of my newsreader - in the format file they are
> all on the appropriate line and tab delimited). Thanks.
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP
> format file
>
> bcp TIGER..TIGER_01 in "c:\documents and settings\user\my
> documents\geocoding\TGR37001.RT1" -f "c:\documents and settings\user\my
> documents\geocoding\tiger1.txt" -S HOSTNAME -U sa -P Password -e
> "c:\documents and settings\user\my
> documents\geocoding\errors\TGR37001.RT1.error.txt" -m50000
> 8.0
> 44
> 1SQLCHAR01""1RTSQL_Latin1_General_CP1_CI_AS
> 2SQLCHAR04""2VERSIONNULL
> 3SQLCHAR010""3TLIDNULL
> 4SQLCHAR01""4SIDE1NULL
> 5SQLCHAR01""5SOURCESQL_Latin1_General_CP1_CI_AS
> 6SQLCHAR02""6FEDIRPSQL_Latin1_General_CP1_CI_AS
> 7SQLCHAR030""7FENAMESQL_Latin1_General_CP1_CI_AS
> 8SQLCHAR04""8FETYPESQL_Latin1_General_CP1_CI_AS
> 9SQLCHAR02""9FEDIRSSQL_Latin1_General_CP1_CI_AS
> 10SQLCHAR03""10CFCCSQL_Latin1_General_CP1_CI_AS
> 11SQLCHAR011""11FRADDLNULL
> 12SQLCHAR011""12TOADDLNULL
> 13SQLCHAR011""13FRADDRNULL
> 14SQLCHAR011""14TOADDRNULL
> 15SQLCHAR01""15FRIADDLSQL_Latin1_General_CP1_CI_AS
> 16SQLCHAR01""16TOIADDLSQL_Latin1_General_CP1_CI_AS
> 17SQLCHAR01""17FRIADDRSQL_Latin1_General_CP1_CI_AS
> 18SQLCHAR01""18TOIADDRSQL_Latin1_General_CP1_CI_AS
> 19SQLCHAR05""19ZIPLNULL
> 20SQLCHAR05""20ZIPRNULL
> 21SQLCHAR05""21AIANHHFPLNULL
> 22SQLCHAR05""22AIANHHFPRNULL
> 23SQLCHAR01""23AIHHTLILSQL_Latin1_General_CP1_CI_AS
> 24SQLCHAR01""24AIHHTLIRSQL_Latin1_General_CP1_CI_AS
> 25SQLCHAR01""25CENSUS1SQL_Latin1_General_CP1_CI_AS
> 26SQLCHAR01""26CENSUS2SQL_Latin1_General_CP1_CI_AS
> 27 SQLCHAR02""27STATELNULL
> 28SQLCHAR02""28STATERNULL
> 29SQLCHAR03""29COUNTYLNULL
> 30SQLCHAR03""30COUNTYRNULL
> 31SQLCHAR05""31COUSUBLNULL
> 32SQLCHAR05""32COUSUBRNULL
> 33SQLCHAR05""33SUBMCDLNULL
> 34SQLCHAR05""34SUBMCDRNULL
> 35SQLCHAR05""35PLACELNULL
> 36SQLCHAR05""36PLACERNULL
> 37SQLCHAR06""37TRACTLNULL
> 38SQLCHAR06""38TRACTRNULL
> 39SQLCHAR04""39BLOCKLNULL
> 40SQLCHAR04""40BLOCKRNULL
> 41SQLCHAR010""41FRLONGNULL
> 42SQLCHAR0 9""42FRLATNULL
> 43SQLCHAR010""43TOLONGNULL
> 44SQLCHAR09"\r\n"44TOLATNULL
>

BCP problem - help?

I'm attempting to run bcp to copy some files into a database and
receiving a format file error (below). I'm using someone else's format
file that I know already works for them, and I don't know what the
issue is. Below is the error, my bcp statement and format file....can
anyone tell what the problem is? (Note - some of the Collation
values are wrapped b/c of my newsreader - in the format file they are
all on the appropriate line and tab delimited). Thanks.
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP
format file
bcp TIGER..TIGER_01 in "c:\documents and settings\user\my
documents\geocoding\TGR37001.RT1" -f "c:\documents and settings\user\my
documents\geocoding\tiger1.txt" -S HOSTNAME -U sa -P Password -e
"c:\documents and settings\user\my
documents\geocoding\errors\TGR37001.RT1.error.txt" -m50000
8.0
44
1 SQLCHAR 0 1 "" 1 RT SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 4 "" 2 VERSION NULL
3 SQLCHAR 0 10 "" 3 TLID NULL
4 SQLCHAR 0 1 "" 4 SIDE1 NULL
5 SQLCHAR 0 1 "" 5 SOURCE SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 2 "" 6 FEDIRP SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 30 "" 7 FENAME SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 4 "" 8 FETYPE SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 2 "" 9 FEDIRS SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 3 "" 10 CFCC SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 11 "" 11 FRADDL NULL
12 SQLCHAR 0 11 "" 12 TOADDL NULL
13 SQLCHAR 0 11 "" 13 FRADDR NULL
14 SQLCHAR 0 11 "" 14 TOADDR NULL
15 SQLCHAR 0 1 "" 15 FRIADDL SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 1 "" 16 TOIADDL SQL_Latin1_General_CP1_CI_AS
17 SQLCHAR 0 1 "" 17 FRIADDR SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 0 1 "" 18 TOIADDR SQL_Latin1_General_CP1_CI_AS
19 SQLCHAR 0 5 "" 19 ZIPL NULL
20 SQLCHAR 0 5 "" 20 ZIPR NULL
21 SQLCHAR 0 5 "" 21 AIANHHFPL NULL
22 SQLCHAR 0 5 "" 22 AIANHHFPR NULL
23 SQLCHAR 0 1 "" 23 AIHHTLIL SQL_Latin1_General_CP1_CI_AS
24 SQLCHAR 0 1 "" 24 AIHHTLIR SQL_Latin1_General_CP1_CI_AS
25 SQLCHAR 0 1 "" 25 CENSUS1 SQL_Latin1_General_CP1_CI_AS
26 SQLCHAR 0 1 "" 26 CENSUS2 SQL_Latin1_General_CP1_CI_AS
27 SQLCHAR 0 2 "" 27 STATEL NULL
28 SQLCHAR 0 2 "" 28 STATER NULL
29 SQLCHAR 0 3 "" 29 COUNTYL NULL
30 SQLCHAR 0 3 "" 30 COUNTYR NULL
31 SQLCHAR 0 5 "" 31 COUSUBL NULL
32 SQLCHAR 0 5 "" 32 COUSUBR NULL
33 SQLCHAR 0 5 "" 33 SUBMCDL NULL
34 SQLCHAR 0 5 "" 34 SUBMCDR NULL
35 SQLCHAR 0 5 "" 35 PLACEL NULL
36 SQLCHAR 0 5 "" 36 PLACER NULL
37 SQLCHAR 0 6 "" 37 TRACTL NULL
38 SQLCHAR 0 6 "" 38 TRACTR NULL
39 SQLCHAR 0 4 "" 39 BLOCKL NULL
40 SQLCHAR 0 4 "" 40 BLOCKR NULL
41 SQLCHAR 0 10 "" 41 FRLONG NULL
42 SQLCHAR 0 9 "" 42 FRLAT NULL
43 SQLCHAR 0 10 "" 43 TOLONG NULL
44 SQLCHAR 0 9 "\r\n" 44 TOLAT NULLThe error message indicates an I/O error, are there any network problems, can
SQL Server see the file, does SQL Server have permissions to access the file?
http://sqlservercode.blogspot.com/
"Corey Bunch" wrote:
> I'm attempting to run bcp to copy some files into a database and
> receiving a format file error (below). I'm using someone else's format
> file that I know already works for them, and I don't know what the
> issue is. Below is the error, my bcp statement and format file....can
> anyone tell what the problem is? (Note - some of the Collation
> values are wrapped b/c of my newsreader - in the format file they are
> all on the appropriate line and tab delimited). Thanks.
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP
> format file
>
> bcp TIGER..TIGER_01 in "c:\documents and settings\user\my
> documents\geocoding\TGR37001.RT1" -f "c:\documents and settings\user\my
> documents\geocoding\tiger1.txt" -S HOSTNAME -U sa -P Password -e
> "c:\documents and settings\user\my
> documents\geocoding\errors\TGR37001.RT1.error.txt" -m50000
> 8.0
> 44
> 1 SQLCHAR 0 1 "" 1 RT SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 0 4 "" 2 VERSION NULL
> 3 SQLCHAR 0 10 "" 3 TLID NULL
> 4 SQLCHAR 0 1 "" 4 SIDE1 NULL
> 5 SQLCHAR 0 1 "" 5 SOURCE SQL_Latin1_General_CP1_CI_AS
> 6 SQLCHAR 0 2 "" 6 FEDIRP SQL_Latin1_General_CP1_CI_AS
> 7 SQLCHAR 0 30 "" 7 FENAME SQL_Latin1_General_CP1_CI_AS
> 8 SQLCHAR 0 4 "" 8 FETYPE SQL_Latin1_General_CP1_CI_AS
> 9 SQLCHAR 0 2 "" 9 FEDIRS SQL_Latin1_General_CP1_CI_AS
> 10 SQLCHAR 0 3 "" 10 CFCC SQL_Latin1_General_CP1_CI_AS
> 11 SQLCHAR 0 11 "" 11 FRADDL NULL
> 12 SQLCHAR 0 11 "" 12 TOADDL NULL
> 13 SQLCHAR 0 11 "" 13 FRADDR NULL
> 14 SQLCHAR 0 11 "" 14 TOADDR NULL
> 15 SQLCHAR 0 1 "" 15 FRIADDL SQL_Latin1_General_CP1_CI_AS
> 16 SQLCHAR 0 1 "" 16 TOIADDL SQL_Latin1_General_CP1_CI_AS
> 17 SQLCHAR 0 1 "" 17 FRIADDR SQL_Latin1_General_CP1_CI_AS
> 18 SQLCHAR 0 1 "" 18 TOIADDR SQL_Latin1_General_CP1_CI_AS
> 19 SQLCHAR 0 5 "" 19 ZIPL NULL
> 20 SQLCHAR 0 5 "" 20 ZIPR NULL
> 21 SQLCHAR 0 5 "" 21 AIANHHFPL NULL
> 22 SQLCHAR 0 5 "" 22 AIANHHFPR NULL
> 23 SQLCHAR 0 1 "" 23 AIHHTLIL SQL_Latin1_General_CP1_CI_AS
> 24 SQLCHAR 0 1 "" 24 AIHHTLIR SQL_Latin1_General_CP1_CI_AS
> 25 SQLCHAR 0 1 "" 25 CENSUS1 SQL_Latin1_General_CP1_CI_AS
> 26 SQLCHAR 0 1 "" 26 CENSUS2 SQL_Latin1_General_CP1_CI_AS
> 27 SQLCHAR 0 2 "" 27 STATEL NULL
> 28 SQLCHAR 0 2 "" 28 STATER NULL
> 29 SQLCHAR 0 3 "" 29 COUNTYL NULL
> 30 SQLCHAR 0 3 "" 30 COUNTYR NULL
> 31 SQLCHAR 0 5 "" 31 COUSUBL NULL
> 32 SQLCHAR 0 5 "" 32 COUSUBR NULL
> 33 SQLCHAR 0 5 "" 33 SUBMCDL NULL
> 34 SQLCHAR 0 5 "" 34 SUBMCDR NULL
> 35 SQLCHAR 0 5 "" 35 PLACEL NULL
> 36 SQLCHAR 0 5 "" 36 PLACER NULL
> 37 SQLCHAR 0 6 "" 37 TRACTL NULL
> 38 SQLCHAR 0 6 "" 38 TRACTR NULL
> 39 SQLCHAR 0 4 "" 39 BLOCKL NULL
> 40 SQLCHAR 0 4 "" 40 BLOCKR NULL
> 41 SQLCHAR 0 10 "" 41 FRLONG NULL
> 42 SQLCHAR 0 9 "" 42 FRLAT NULL
> 43 SQLCHAR 0 10 "" 43 TOLONG NULL
> 44 SQLCHAR 0 9 "\r\n" 44 TOLAT NULL
>

BCP problem - help?

I'm attempting to run bcp to copy some files into a database and
receiving a format file error (below). I'm using someone else's format
file that I know already works for them, and I don't know what the
issue is. Below is the error, my bcp statement and format file....can
anyone tell what the problem is? (Note - some of the Collation
values are wrapped b/c of my newsreader - in the format file they are
all on the appropriate line and tab delimited). Thanks.
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading B
CP
format file
bcp TIGER..TIGER_01 in "c:\documents and settings\user\my
documents\geocoding\TGR37001.RT1" -f "c:\documents and settings\user\my
documents\geocoding\tiger1.txt" -S HOSTNAME -U sa -P Password -e
"c:\documents and settings\user\my
documents\geocoding\errors\TGR37001.RT1.error.txt" -m50000
8.0
44
1 SQLCHAR 0 1 "" 1 RT SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 4 "" 2 VERSION NULL
3 SQLCHAR 0 10 "" 3 TLID NULL
4 SQLCHAR 0 1 "" 4 SIDE1 NULL
5 SQLCHAR 0 1 "" 5 SOURCE SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 2 "" 6 FEDIRP SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 30 "" 7 FENAME SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 4 "" 8 FETYPE SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 2 "" 9 FEDIRS SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 3 "" 10 CFCC SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 11 "" 11 FRADDL NULL
12 SQLCHAR 0 11 "" 12 TOADDL NULL
13 SQLCHAR 0 11 "" 13 FRADDR NULL
14 SQLCHAR 0 11 "" 14 TOADDR NULL
15 SQLCHAR 0 1 "" 15 FRIADDL SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 1 "" 16 TOIADDL SQL_Latin1_General_CP1_CI_AS
17 SQLCHAR 0 1 "" 17 FRIADDR SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 0 1 "" 18 TOIADDR SQL_Latin1_General_CP1_CI_AS
19 SQLCHAR 0 5 "" 19 ZIPL NULL
20 SQLCHAR 0 5 "" 20 ZIPR NULL
21 SQLCHAR 0 5 "" 21 AIANHHFPL NULL
22 SQLCHAR 0 5 "" 22 AIANHHFPR NULL
23 SQLCHAR 0 1 "" 23 AIHHTLIL SQL_Latin1_General_CP1_CI_A
S
24 SQLCHAR 0 1 "" 24 AIHHTLIR SQL_Latin1_General_CP1_CI_A
S
25 SQLCHAR 0 1 "" 25 CENSUS1 SQL_Latin1_General_CP1_CI_AS
26 SQLCHAR 0 1 "" 26 CENSUS2 SQL_Latin1_General_CP1_CI_AS
27 SQLCHAR 0 2 "" 27 STATEL NULL
28 SQLCHAR 0 2 "" 28 STATER NULL
29 SQLCHAR 0 3 "" 29 COUNTYL NULL
30 SQLCHAR 0 3 "" 30 COUNTYR NULL
31 SQLCHAR 0 5 "" 31 COUSUBL NULL
32 SQLCHAR 0 5 "" 32 COUSUBR NULL
33 SQLCHAR 0 5 "" 33 SUBMCDL NULL
34 SQLCHAR 0 5 "" 34 SUBMCDR NULL
35 SQLCHAR 0 5 "" 35 PLACEL NULL
36 SQLCHAR 0 5 "" 36 PLACER NULL
37 SQLCHAR 0 6 "" 37 TRACTL NULL
38 SQLCHAR 0 6 "" 38 TRACTR NULL
39 SQLCHAR 0 4 "" 39 BLOCKL NULL
40 SQLCHAR 0 4 "" 40 BLOCKR NULL
41 SQLCHAR 0 10 "" 41 FRLONG NULL
42 SQLCHAR 0 9 "" 42 FRLAT NULL
43 SQLCHAR 0 10 "" 43 TOLONG NULL
44 SQLCHAR 0 9 "\r\n" 44 TOLAT NULLThe error message indicates an I/O error, are there any network problems, ca
n
SQL Server see the file, does SQL Server have permissions to access the file
?
http://sqlservercode.blogspot.com/
"Corey Bunch" wrote:

> I'm attempting to run bcp to copy some files into a database and
> receiving a format file error (below). I'm using someone else's format
> file that I know already works for them, and I don't know what the
> issue is. Below is the error, my bcp statement and format file....can
> anyone tell what the problem is? (Note - some of the Collation
> values are wrapped b/c of my newsreader - in the format file they are
> all on the appropriate line and tab delimited). Thanks.
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading
BCP
> format file
>
> bcp TIGER..TIGER_01 in "c:\documents and settings\user\my
> documents\geocoding\TGR37001.RT1" -f "c:\documents and settings\user\my
> documents\geocoding\tiger1.txt" -S HOSTNAME -U sa -P Password -e
> "c:\documents and settings\user\my
> documents\geocoding\errors\TGR37001.RT1.error.txt" -m50000
> 8.0
> 44
> 1 SQLCHAR 0 1 "" 1 RT SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 0 4 "" 2 VERSION NULL
> 3 SQLCHAR 0 10 "" 3 TLID NULL
> 4 SQLCHAR 0 1 "" 4 SIDE1 NULL
> 5 SQLCHAR 0 1 "" 5 SOURCE SQL_Latin1_General_CP1_CI_AS
> 6 SQLCHAR 0 2 "" 6 FEDIRP SQL_Latin1_General_CP1_CI_AS
> 7 SQLCHAR 0 30 "" 7 FENAME SQL_Latin1_General_CP1_CI_AS
> 8 SQLCHAR 0 4 "" 8 FETYPE SQL_Latin1_General_CP1_CI_AS
> 9 SQLCHAR 0 2 "" 9 FEDIRS SQL_Latin1_General_CP1_CI_AS
> 10 SQLCHAR 0 3 "" 10 CFCC SQL_Latin1_General_CP1_CI_AS
> 11 SQLCHAR 0 11 "" 11 FRADDL NULL
> 12 SQLCHAR 0 11 "" 12 TOADDL NULL
> 13 SQLCHAR 0 11 "" 13 FRADDR NULL
> 14 SQLCHAR 0 11 "" 14 TOADDR NULL
> 15 SQLCHAR 0 1 "" 15 FRIADDL SQL_Latin1_General_CP1_CI_AS
> 16 SQLCHAR 0 1 "" 16 TOIADDL SQL_Latin1_General_CP1_CI_AS
> 17 SQLCHAR 0 1 "" 17 FRIADDR SQL_Latin1_General_CP1_CI_AS
> 18 SQLCHAR 0 1 "" 18 TOIADDR SQL_Latin1_General_CP1_CI_AS
> 19 SQLCHAR 0 5 "" 19 ZIPL NULL
> 20 SQLCHAR 0 5 "" 20 ZIPR NULL
> 21 SQLCHAR 0 5 "" 21 AIANHHFPL NULL
> 22 SQLCHAR 0 5 "" 22 AIANHHFPR NULL
> 23 SQLCHAR 0 1 "" 23 AIHHTLIL SQL_Latin1_General_CP1_CI_A
S
> 24 SQLCHAR 0 1 "" 24 AIHHTLIR SQL_Latin1_General_CP1_CI_A
S
> 25 SQLCHAR 0 1 "" 25 CENSUS1 SQL_Latin1_General_CP1_CI_AS
> 26 SQLCHAR 0 1 "" 26 CENSUS2 SQL_Latin1_General_CP1_CI_AS
> 27 SQLCHAR 0 2 "" 27 STATEL NULL
> 28 SQLCHAR 0 2 "" 28 STATER NULL
> 29 SQLCHAR 0 3 "" 29 COUNTYL NULL
> 30 SQLCHAR 0 3 "" 30 COUNTYR NULL
> 31 SQLCHAR 0 5 "" 31 COUSUBL NULL
> 32 SQLCHAR 0 5 "" 32 COUSUBR NULL
> 33 SQLCHAR 0 5 "" 33 SUBMCDL NULL
> 34 SQLCHAR 0 5 "" 34 SUBMCDR NULL
> 35 SQLCHAR 0 5 "" 35 PLACEL NULL
> 36 SQLCHAR 0 5 "" 36 PLACER NULL
> 37 SQLCHAR 0 6 "" 37 TRACTL NULL
> 38 SQLCHAR 0 6 "" 38 TRACTR NULL
> 39 SQLCHAR 0 4 "" 39 BLOCKL NULL
> 40 SQLCHAR 0 4 "" 40 BLOCKR NULL
> 41 SQLCHAR 0 10 "" 41 FRLONG NULL
> 42 SQLCHAR 0 9 "" 42 FRLAT NULL
> 43 SQLCHAR 0 10 "" 43 TOLONG NULL
> 44 SQLCHAR 0 9 "\r\n" 44 TOLAT NULL
>sql

BCP output

Hi All,
The syntaxt below is not outputting any text file at the location
specified. Any Ideas !
use tempdb
go
Create view vw_bcpMasterSysobjects as
select
name = '"' + name + '"' ,
crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
crtime = '"' + convert(varchar(8), crdate, 108) + '"'
from master..sysobjects
go
declare @.sql varchar(8000)
select @.sql = 'bcp "select * from
tempdb..vw_bcpMasterSysobjects
order by crdate desc, crtime desc"
queryout c:\sysobjects.txt -c -t, -T -
S'
+ @.@.servername
exec master..xp_cmdshell @.sql
Thanks in advance
bcp "select * from tempdb..vw_bcpMasterSysobjects order by crdate
desc, crtime desc" queryout d:\sysobjects.txt -c -t, -T -SA03
is working fine . Put the bcp in a single line . There is no space
between S and server name . Also be aware , this will create a file in
SQL Server System and not in client system where you execute the code
M A Srinivas
On Mar 9, 1:42 pm, "Swagener" <riqb...@.gmail.com> wrote:
> Hi All,
> The syntaxt below is not outputting any text file at the location
> specified. Any Ideas !
> use tempdb
> go
> Create view vw_bcpMasterSysobjects as
> select
> name = '"' + name + '"' ,
> crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
> crtime = '"' + convert(varchar(8), crdate, 108) + '"'
> from master..sysobjects
> go
> declare @.sql varchar(8000)
> select @.sql = 'bcp "select * from
> tempdb..vw_bcpMasterSysobjects
> order by crdate desc, crtime desc"
> queryout c:\sysobjects.txt -c -t, -T -
> S'
> + @.@.servername
> exec master..xp_cmdshell @.sql
> Thanks in advance
|||works fine on my server -- the only difference is that I put the bcp line as
1 line , not spread across 3.
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>
"Swagener" <riqband@.gmail.com> wrote in message
news:1173429734.273739.316500@.j27g2000cwj.googlegr oups.com...
> Hi All,
> The syntaxt below is not outputting any text file at the location
> specified. Any Ideas !
> use tempdb
> go
> Create view vw_bcpMasterSysobjects as
> select
> name = '"' + name + '"' ,
> crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
> crtime = '"' + convert(varchar(8), crdate, 108) + '"'
> from master..sysobjects
> go
> declare @.sql varchar(8000)
> select @.sql = 'bcp "select * from
> tempdb..vw_bcpMasterSysobjects
> order by crdate desc, crtime desc"
> queryout c:\sysobjects.txt -c -t, -T -
> S'
> + @.@.servername
> exec master..xp_cmdshell @.sql
>
> Thanks in advance
>
|||Thanks for all these replies,
I have managed to run the code with your guys help.
It was the bcp syntax splitted into 3 lines.
Thanks again.

BCP output

Hi All,
The syntaxt below is not outputting any text file at the location
specified. Any Ideas !
use tempdb
go
Create view vw_bcpMasterSysobjects as
select
name = '"' + name + '"' ,
crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
crtime = '"' + convert(varchar(8), crdate, 108) + '"'
from master..sysobjects
go
declare @.sql varchar(8000)
select @.sql = 'bcp "select * from
tempdb..vw_bcpMasterSysobjects
order by crdate desc, crtime desc"
queryout c:\sysobjects.txt -c -t, -T -
S'
+ @.@.servername
exec master..xp_cmdshell @.sql
Thanks in advancebcp "select * from tempdb..vw_bcpMasterSysobjects order by crdate
desc, crtime desc" queryout d:\sysobjects.txt -c -t, -T -SA03
is working fine . Put the bcp in a single line . There is no space
between S and server name . Also be aware , this will create a file in
SQL Server System and not in client system where you execute the code
M A Srinivas
On Mar 9, 1:42 pm, "Swagener" <riqb...@.gmail.com> wrote:
> Hi All,
> The syntaxt below is not outputting any text file at the location
> specified. Any Ideas !
> use tempdb
> go
> Create view vw_bcpMasterSysobjects as
> select
> name = '"' + name + '"' ,
> crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
> crtime = '"' + convert(varchar(8), crdate, 108) + '"'
> from master..sysobjects
> go
> declare @.sql varchar(8000)
> select @.sql = 'bcp "select * from
> tempdb..vw_bcpMasterSysobjects
> order by crdate desc, crtime desc"
> queryout c:\sysobjects.txt -c -t, -T -
> S'
> + @.@.servername
> exec master..xp_cmdshell @.sql
> Thanks in advance|||works fine on my server -- the only difference is that I put the bcp line as
1 line , not spread across 3.
--
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=uk/">http://www.itjobfeed.com">UK IT Jobs</a>
"Swagener" <riqband@.gmail.com> wrote in message
news:1173429734.273739.316500@.j27g2000cwj.googlegroups.com...
> Hi All,
> The syntaxt below is not outputting any text file at the location
> specified. Any Ideas !
> use tempdb
> go
> Create view vw_bcpMasterSysobjects as
> select
> name = '"' + name + '"' ,
> crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
> crtime = '"' + convert(varchar(8), crdate, 108) + '"'
> from master..sysobjects
> go
> declare @.sql varchar(8000)
> select @.sql = 'bcp "select * from
> tempdb..vw_bcpMasterSysobjects
> order by crdate desc, crtime desc"
> queryout c:\sysobjects.txt -c -t, -T -
> S'
> + @.@.servername
> exec master..xp_cmdshell @.sql
>
> Thanks in advance
>|||It worked just fine for me.
1. Make sure that the bcp command is in one line.
2. Print the contents of the @.sql variable and post it here.
3. Also provide error messages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Swagener" <riqband@.gmail.com> wrote in message
news:1173429734.273739.316500@.j27g2000cwj.googlegroups.com...
> Hi All,
> The syntaxt below is not outputting any text file at the location
> specified. Any Ideas !
> use tempdb
> go
> Create view vw_bcpMasterSysobjects as
> select
> name = '"' + name + '"' ,
> crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
> crtime = '"' + convert(varchar(8), crdate, 108) + '"'
> from master..sysobjects
> go
> declare @.sql varchar(8000)
> select @.sql = 'bcp "select * from
> tempdb..vw_bcpMasterSysobjects
> order by crdate desc, crtime desc"
> queryout c:\sysobjects.txt -c -t, -T -
> S'
> + @.@.servername
> exec master..xp_cmdshell @.sql
>
> Thanks in advance
>|||Thanks for all these replies,
I have managed to run the code with your guys help.
It was the bcp syntax splitted into 3 lines.
Thanks again.

BCP output

Hi All,
The syntaxt below is not outputting any text file at the location
specified. Any Ideas !
use tempdb
go
Create view vw_bcpMasterSysobjects as
select
name = '"' + name + '"' ,
crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
crtime = '"' + convert(varchar(8), crdate, 108) + '"'
from master..sysobjects
go
declare @.sql varchar(8000)
select @.sql = 'bcp "select * from
tempdb..vw_bcpMasterSysobjects
order by crdate desc, crtime desc"
queryout c:\sysobjects.txt -c -t, -T -
S'
+ @.@.servername
exec master..xp_cmdshell @.sql
Thanks in advancebcp "select * from tempdb..vw_bcpMasterSysobjects order by crdate
desc, crtime desc" queryout d:\sysobjects.txt -c -t, -T -SA03
is working fine . Put the bcp in a single line . There is no space
between S and server name . Also be aware , this will create a file in
SQL Server System and not in client system where you execute the code
M A Srinivas
On Mar 9, 1:42 pm, "Swagener" <riqb...@.gmail.com> wrote:
> Hi All,
> The syntaxt below is not outputting any text file at the location
> specified. Any Ideas !
> use tempdb
> go
> Create view vw_bcpMasterSysobjects as
> select
> name = '"' + name + '"' ,
> crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
> crtime = '"' + convert(varchar(8), crdate, 108) + '"'
> from master..sysobjects
> go
> declare @.sql varchar(8000)
> select @.sql = 'bcp "select * from
> tempdb..vw_bcpMasterSysobjects
> order by crdate desc, crtime desc"
> queryout c:\sysobjects.txt -c -t, -T -
> S'
> + @.@.servername
> exec master..xp_cmdshell @.sql
> Thanks in advance|||It worked just fine for me.
1. Make sure that the bcp command is in one line.
2. Print the contents of the @.sql variable and post it here.
3. Also provide error messages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Swagener" <riqband@.gmail.com> wrote in message
news:1173429734.273739.316500@.j27g2000cwj.googlegroups.com...
> Hi All,
> The syntaxt below is not outputting any text file at the location
> specified. Any Ideas !
> use tempdb
> go
> Create view vw_bcpMasterSysobjects as
> select
> name = '"' + name + '"' ,
> crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
> crtime = '"' + convert(varchar(8), crdate, 108) + '"'
> from master..sysobjects
> go
> declare @.sql varchar(8000)
> select @.sql = 'bcp "select * from
> tempdb..vw_bcpMasterSysobjects
> order by crdate desc, crtime desc"
> queryout c:\sysobjects.txt -c -t, -T -
> S'
> + @.@.servername
> exec master..xp_cmdshell @.sql
>
> Thanks in advance
>|||works fine on my server -- the only difference is that I put the bcp line as
1 line , not spread across 3.
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>
"Swagener" <riqband@.gmail.com> wrote in message
news:1173429734.273739.316500@.j27g2000cwj.googlegroups.com...
> Hi All,
> The syntaxt below is not outputting any text file at the location
> specified. Any Ideas !
> use tempdb
> go
> Create view vw_bcpMasterSysobjects as
> select
> name = '"' + name + '"' ,
> crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
> crtime = '"' + convert(varchar(8), crdate, 108) + '"'
> from master..sysobjects
> go
> declare @.sql varchar(8000)
> select @.sql = 'bcp "select * from
> tempdb..vw_bcpMasterSysobjects
> order by crdate desc, crtime desc"
> queryout c:\sysobjects.txt -c -t, -T -
> S'
> + @.@.servername
> exec master..xp_cmdshell @.sql
>
> Thanks in advance
>|||Thanks for all these replies,
I have managed to run the code with your guys help.
It was the bcp syntax splitted into 3 lines.
Thanks again.

bcp import help

I am trying to import data thru a bcp call to pull data from an access database. I am having trouble accessing the access database. Below is the bcp I tried, along with an openrowset attempt. Neither of them are working. Any help would be greatly appreciated.

bcp select datetime,groupNumber,lineSubgroupNumber,lineNumber ,lineName,inCall,noCallAnswer,
noOutCall,abandonCall,noCallAD,noCallABT,noHelpcal l,noTxcall,noNtcall,totalInNormalTime,
totalOutNormalTime,totalHoldNormalTime,totalAbando nTime,totalLineBusyTime,totalTransTime,
ansCallBin0,ansCallBin1,ansCallBin2,ansCallBin3,an sCallBin4,ansCallBin5,ansCallBin6,
abnCallBin0,abnCallBin1,abnCallBin2,abnCallBin3,ab nCallBin4,abnCallBin5,abnCallBin6
from Line Report in I:\2007\11\D1107.MDB -q -UXX -PXX

select datetime,groupNumber,lineSubgroupNumber,lineNumber ,lineName,inCall,noCallAnswer,
noOutCall,abandonCall,noCallAD,noCallABT,noHelpcal l,noTxcall,noNtcall,totalInNormalTime,
totalOutNormalTime,totalHoldNormalTime,totalAbando nTime,totalLineBusyTime,totalTransTime,
ansCallBin0,ansCallBin1,ansCallBin2,ansCallBin3,an sCallBin4,ansCallBin5,ansCallBin6,
abnCallBin0,abnCallBin1,abnCallBin2,abnCallBin3,ab nCallBin4,abnCallBin5,abnCallBin6
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'I:\2007\10\D1007.MDB';'XX';'XX', 'Line Report')Are you able to add the mdb as a linked server - you could then simply run SQL statements irectly on the data? I'm afraid I am unable to test this from my current location so I'm not able to check!|||I can add it as a linked server, but then I cant actually query against any of the access tables.

sp_addlinkedserver 'D1107', 'Access 97', 'Microsoft.Jet.OLEDB.4.0',
'\\ctisvr\stats\2007\11\D1107.MDB'

SELECT *
FROM D1107...Line Report

when I run the select statment I then get:
'OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Cannot open database ''. It may not be a database that your application recognizes, or the file may be corrupt.]'|||Do you get the same error message for the OPENROWSET query? Also, what version is the Access database created in?

As a side note, you have probably already learned that BCP is used solely for "flat files". Pure ASCII/UNICODE characters.|||Same error. Not sure what version it was created in as I am importing it from a 3rd party vendor.|||Hi.
why not use a DTS package? and test this SELECT *
FROM D1107...[Line Report]|||Did you try the DTS Wizard?

I don't have a full version of Sql Server, only MSDE. So I didn't have the DTS Wizard. Then I looked in an old Office 2000 disk I've got, and found it. I copied over dtswiz.exe and some other .dll and .rll files to my Sql Server\tools\binn folder and it worked.

bcp export stored procedure with a date in the statement

Hi

Please can someone help me with the statement below. I am trying to export, via bcp a stored procedure which requires two dates and cannot seem to work out the correct way of typing it into the statement. I know that the dates are meant to have an ' around them but cant work out how to get this concatenated correctly.

Any help would be appreciated.

Paul

select @.sql = 'bcp "Exec CHC_Data_V2..TestSP 05/01/07, 01/01/07" queryout "c:\entitytext.txt" -SAJR\SQLEXPRESS -T -c -t'
exec master..xp_cmdshell @.sq

use the following query...

Code Snippet

declare @.sql as varchar(1000)

select @.sql = 'bcp "Exec CHC_Data_V2..TestSP ''05/01/07'', ''01/01/07''" queryout "c:\entitytext.txt" -SAJR\SQLEXPRESS -T -c -t'

exec master..xp_cmdshell @.sql

|||Thanks very much for your help

BCP Error Message

Hi All,
I am getting the below error message when I am executing the BCP command
BCP command
==========
xp_cmdshell 'c:\MSSQL7\BINN\BCP SQLManager.dbo.z_DBAdmin_Fwd_MAXDRBUS02_DBAdmin_db o_tbl_CheckServerRoleMembers_20040331161058 in "c:\Temp\DBAdmin_Fwd_MAXDRBUS02_DBAdmin_tbl_CheckS erverRoleMembers.bcp" /SSQLSER /UDUser /Ptest /c /b1000 /m1'
Error Message
==========
SQLState = 08001, NativeError = 6
Error = [Microsoft][ODBC SQL Server Driver][Named Pipes]Specified SQL server not found.
SQLState = 01000, NativeError = 53
Warning = [Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionOpen (CreateFile()).
Thanks & Regards
Balaji
Balaji (anonymous@.discussions.microsoft.com) writes:
> I am getting the below error message when I am executing the BCP command
> BCP command
>==========
> xp_cmdshell 'c:\MSSQL7\BINN\BCP SQLManager.dbo.z_DBAdmin_Fwd_MAXDRBUS02_DBAdmin_db o_tbl_CheckServerRoleMembers_20040331161058 in "c:\Temp\DBAdmin_Fwd_MAXDRBUS02_DBAdmin_tbl_CheckS erverRoleMembers.bcp" /SSQLSER /UDUser /Ptest /c /b1000 /m1'
> Error Message
>==========
> SQLState = 08001, NativeError = 6
> Error = [Microsoft][ODBC SQL Server Driver][Named Pipes]Specified SQL server not found.
> SQLState = 01000, NativeError = 53
> Warning = [Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionOpen (CreateFile()).
That means that BCP cannot find the server you are trying to connect to.
Why it cannot do so, I have no idea. To start with, you have not provided
any information on why you should be able to connect to this server.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

BCP Error

Hi,
I'm trying to bcp data in/out of a sql server instance database and I'm
getting an error. The statement and error are below. The same error happens
at command line and query analyzer. I've been searching online but the
closest thing I can come up with is that it's a serv pack issue (?). Any
thoughts would be great. It works on my local machine. I have sysadmin
rights on the sql server.
Statement:
EXEC master..xp_cmdshell 'bcp CRMDB\SQLSERVER.Healthscience.dbo.events out
c:\temp\events030905.txt -n -T'
SQLState = 08001, NativeError = 14
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]Invalid
connection.
SQLState = 01000, NativeError = 14
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen
(Invalid Instance()).
NULLTry,
bcp Healthscience.dbo.events out "c:\temp\events030905.txt" -n -S
CRMDB\SQLSERVER -T
AMB
"dfate" wrote:
> Hi,
> I'm trying to bcp data in/out of a sql server instance database and I'm
> getting an error. The statement and error are below. The same error happens
> at command line and query analyzer. I've been searching online but the
> closest thing I can come up with is that it's a serv pack issue (?). Any
> thoughts would be great. It works on my local machine. I have sysadmin
> rights on the sql server.
> Statement:
> EXEC master..xp_cmdshell 'bcp CRMDB\SQLSERVER.Healthscience.dbo.events out
> c:\temp\events030905.txt -n -T'
> SQLState = 08001, NativeError = 14
> Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]Invalid
> connection.
> SQLState = 01000, NativeError = 14
> Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen
> (Invalid Instance()).
> NULL
>
>

BCP Error

Hi,
I'm trying to bcp data in/out of a sql server instance database and I'm
getting an error. The statement and error are below. The same error happens
at command line and query analyzer. I've been searching online but the
closest thing I can come up with is that it's a serv pack issue (?). Any
thoughts would be great. It works on my local machine. I have sysadmin
rights on the sql server.
Statement:
EXEC master..xp_cmdshell 'bcp CRMDB\SQLSERVER.Healthscience.dbo.events out
c:\temp\events030905.txt -n -T'
SQLState = 08001, NativeError = 14
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]Invalid
connection.
SQLState = 01000, NativeError = 14
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen
(Invalid Instance()).
NULL
Try,
bcp Healthscience.dbo.events out "c:\temp\events030905.txt" -n -S
CRMDB\SQLSERVER -T
AMB
"dfate" wrote:

> Hi,
> I'm trying to bcp data in/out of a sql server instance database and I'm
> getting an error. The statement and error are below. The same error happens
> at command line and query analyzer. I've been searching online but the
> closest thing I can come up with is that it's a serv pack issue (?). Any
> thoughts would be great. It works on my local machine. I have sysadmin
> rights on the sql server.
> Statement:
> EXEC master..xp_cmdshell 'bcp CRMDB\SQLSERVER.Healthscience.dbo.events out
> c:\temp\events030905.txt -n -T'
> SQLState = 08001, NativeError = 14
> Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]Invalid
> connection.
> SQLState = 01000, NativeError = 14
> Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen
> (Invalid Instance()).
> NULL
>
>

BCP Error

Hi,
I'm trying to bcp data in/out of a sql server instance database and I'm
getting an error. The statement and error are below. The same error happens
at command line and query analyzer. I've been searching online but the
closest thing I can come up with is that it's a serv pack issue (?). Any
thoughts would be great. It works on my local machine. I have sysadmin
rights on the sql server.
Statement:
EXEC master..xp_cmdshell 'bcp CRMDB\SQLSERVER.Healthscience.dbo.events out
c:\temp\events030905.txt -n -T'
SQLState = 08001, NativeError = 14
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]Invali
d
connection.
SQLState = 01000, NativeError = 14
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]Conn
ectionOpen
(Invalid Instance()).
NULLTry,
bcp Healthscience.dbo.events out "c:\temp\events030905.txt" -n -S
CRMDB\SQLSERVER -T
AMB
"dfate" wrote:

> Hi,
> I'm trying to bcp data in/out of a sql server instance database and I'm
> getting an error. The statement and error are below. The same error happen
s
> at command line and query analyzer. I've been searching online but the
> closest thing I can come up with is that it's a serv pack issue (?). Any
> thoughts would be great. It works on my local machine. I have sysadmin
> rights on the sql server.
> Statement:
> EXEC master..xp_cmdshell 'bcp CRMDB\SQLSERVER.Healthscience.dbo.events out
> c:\temp\events030905.txt -n -T'
> SQLState = 08001, NativeError = 14
> Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]Inva
lid
> connection.
> SQLState = 01000, NativeError = 14
> Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]Co
nnectionOpen
> (Invalid Instance()).
> NULL
>
>

BCP and column defaulting

I am bringing data from a file using bcp as below in a stored procedure.
SET @.SQLString = 'BCP STAGING IN "' + @.file_path + '" -c -F2 -Uxyz -Pxyz'
EXEC master..xp_cmdshell @.SQLString
The table has only one column varchar (1024). I need to add one more column
called code which i can pass to the stored procedure but need to be added to
the staging table when rows gets inserted. Because the stored procedure
could be called at the same time with two different codes. So based on what
code is passed to the SP all the rows inserted by bcp in that instance of
the SP should have that code. How would i do this?
Thanks,
DavidHi
You may want to look at using the BULK INSERT command and load it into a
temporary table.
CREATE TABLE staging( id int not null,
[name] varchar(10),
col1 varchar(80),
col2 varchar(80) )
CREATE PROCEDURE MyLoad ( @.Name varchar(10), @.filename varchar(256) )
AS
BEGIN
CREATE TABLE #test ( id int not null,
col1 varchar(80),
col2 varchar(80) )
EXEC ('BULK INSERT #test FROM ''' + @.filename + '''')
INSERT INTO Staging ( id, name, col1, col2 )
SELECT id, @.Name, col1, col2
FROM #test
END
EXEC MyLoad 'Test1', 'C:\temp\test.txt'
John
"Viji" wrote:

> I am bringing data from a file using bcp as below in a stored procedure.
> SET @.SQLString = 'BCP STAGING IN "' + @.file_path + '" -c -F2 -Uxyz -Pxyz'
> EXEC master..xp_cmdshell @.SQLString
> The table has only one column varchar (1024). I need to add one more colum
n
> called code which i can pass to the stored procedure but need to be added
to
> the staging table when rows gets inserted. Because the stored procedure
> could be called at the same time with two different codes. So based on wha
t
> code is passed to the SP all the rows inserted by bcp in that instance of
> the SP should have that code. How would i do this?
> Thanks,
> David
>
>

BCP - Skip the first column in a BCP opperation

How can I skip the first column in a BCP operation. My data file does not contain the data for the first column. From the sample below I want to skip the column named GCRecord.

Table schema [StopOrderCode]

[GCRecord] int NULL,
[Id] int NOT NULL,
[EmployerPayCode] nvarchar(100) NULL,
[EmployerName] nvarchar(100) NULL,
[EnglishEmployerName] nvarchar(100) NULL,
[AfrikaansEmployerName] nvarchar(100) NULL,
[IsGovernmentCode] bit NULL

Format file

9.0
6
1 SQLINT "" 4 "\t" 2 Id ""
2 SQLNCHAR "" 200 "\t" 3 EmployerPayCode ""
3 SQLNCHAR "" 200 "\t" 4 EmployerName Latin1_General_CI_AS
4 SQLNCHAR "" 200 "\t" 5 EnglishEmployerName Latin1_General_CI_AS
5 SQLNCHAR "" 200 "\t" 6 AfrikaansEmployerName Latin1_General_CI_AS
6 SQLBIT "" 1 "\r\n" 7 IsGovernmentCode

Data sample (Tab deliminated)

Id EmployerPayCode EmployerName EnglishEmployerName AfrikaansEmployerName IsGovernmentCode
676 9271 Abakor Bpk Abakor Bpk Abakor Bpk 0
837 9639 Aberdare Telecom Division Aberdare Telecom Division Aberdare Telecom Division 0

BCP statement

bcp DBName.dbo.StopOrderCode in ".\Table Data\StopOrderCode.txt" -S DBServer\InstanceName -T -f ".\Table Data\StopOrderCode.fmt" -C ACP -b 1000 -F 2

I am using sql 2005.

Thanks in advance

When the datafile contains more columns than the table,

bcp into a 'Staging' table that maps to the data file, and then copy appropriate columns to the final table.

When the table has more columns than the datafile,

CREATE a VIEW that maps the data to the table, and bcp into the VIEW.

BCP Formatting Output

Hi,

I wrote the below code and procedure that exports two tables contents into 2 separate Excel files. Is there a way to export contents of two tables via BCP utility into 1 Excel file but 2 different Worksheets of this file?

DECLARE @.FileName varchar(50),
@.FileName1 varchar(50),
@.bcpCommand varchar(2000)
SET @.FileName = 'E:\GPPD_db_stats.XLS'
SET @.FileName1 = 'E:\GPPD_file_stats.XLS'
print @.FileName
SET @.bcpCommand = 'bcp "master.dbo.spdbdesc" OUT ' + @.FileName + ' -Samex-srv-gppdb -T -c'
print @.bcpCommand
EXEC master..xp_cmdshell @.bcpCommand

SET @.bcpCommand = 'bcp "master.dbo.spfiledesc" OUT ' + @.FileName1 + ' -Samex-srv-gppdb -T -c'
print @.bcpCommand
EXEC master..xp_cmdshell @.bcpCommand
exec master.dbo.xp_stopmail
set @.bcpCommand = ' ' + @.FileName + '; ' + @.FileName1 + ''
DECLARE @.body VARCHAR(1024)
SET @.body = 'Please find enclosed files with the database status reports as of '+
CONVERT(VARCHAR, GETDATE()) + '. Please DO NOT respond to this email or the ones coming in the future ' +
'with data files as this email address is not monitored for incoming emails. However, if you have any ' +
'questions/concerns please contact ...'


EXEC master..xp_sendmail
@.recipients='alla.levit@.amex.com',
@.message = @.body,
@.subject = 'Database Weekly Statistics Report',
@.attachments = @.bcpCommand

Thanks in advance!
-AllaGood luck. I've never been able to pull this off.

Batch Requests/Sec counter

Hi,
I hope that somebody can help me to clarify for this.
Below is an excerpt extracted from the www.sql-server-performance.com website.
The batch requests per second shown on the performance monitor screen is in the scale of 1000.
My current server has 6 CPUs but with only 100mb network card. I'm a bit concern on the values shown for this particular counter which is average over 27243187 batch requests/Sec. Also lately the CPU utilization is also getting higher in the range of abov
e 80%. The question here is whether a network bottleneck in this can cause an increase in CPU utilization? if yes may I know why also?
Thanks
-debbie-
To get a feel of how busy SQL Server is, monitor the SQLServer: SQL Statistics: Batch Requests/Sec counter. This counter measures the number of batch requests that SQL Server receives per second, and generally follows in step to how busy your server's CPU
s are. Generally speaking, over 1000 batch requests per second indicates a very busy SQL Server, and could mean that if you are not already experiencing a CPU bottleneck, that you may very well soon. Of course, this is a relative number, and the bigger yo
ur hardware, the more batch requests per second SQL Server can handle.
From a network bottleneck approach, a typical 100Mbs network card is only able to handle about 3000 batch requests per second. If you have a server that is this busy, you may need to have two or more network cards, or go to a 1Gbs network card.
I find it hard to believe that your system is processing 27 million batch
requests per second<g>. Where are you getting the value from?
Andrew J. Kelly SQL MVP
"debcwong" <debcwong@.discussions.microsoft.com> wrote in message
news:92A9ACE5-072E-4DF4-822F-B5E23B669B49@.microsoft.com...
> Hi,
> I hope that somebody can help me to clarify for this.
> Below is an excerpt extracted from the www.sql-server-performance.com
website.
> The batch requests per second shown on the performance monitor screen is
in the scale of 1000.
> My current server has 6 CPUs but with only 100mb network card. I'm a bit
concern on the values shown for this particular counter which is average
over 27243187 batch requests/Sec. Also lately the CPU utilization is also
getting higher in the range of above 80%. The question here is whether a
network bottleneck in this can cause an increase in CPU utilization? if yes
may I know why also?
>
> Thanks
> -debbie-
> ----
--
> To get a feel of how busy SQL Server is, monitor the SQLServer: SQL
Statistics: Batch Requests/Sec counter. This counter measures the number of
batch requests that SQL Server receives per second, and generally follows in
step to how busy your server's CPUs are. Generally speaking, over 1000 batch
requests per second indicates a very busy SQL Server, and could mean that if
you are not already experiencing a CPU bottleneck, that you may very well
soon. Of course, this is a relative number, and the bigger your hardware,
the more batch requests per second SQL Server can handle.
> From a network bottleneck approach, a typical 100Mbs network card is only
able to handle about 3000 batch requests per second. If you have a server
that is this busy, you may need to have two or more network cards, or go to
a 1Gbs network card.
> ----
--
>
|||Sounds like you pulled the raw counters from sysperfinfo. Those are not
time-adjusted. See http://support.microsoft.com/?id=555064 for a short
explanation. You wil need to use the Performance Monitor tool to see the
actual per/second values.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"debcwong" <debcwong@.discussions.microsoft.com> wrote in message
news:92A9ACE5-072E-4DF4-822F-B5E23B669B49@.microsoft.com...
> Hi,
> I hope that somebody can help me to clarify for this.
> Below is an excerpt extracted from the www.sql-server-performance.com
website.
> The batch requests per second shown on the performance monitor screen is
in the scale of 1000.
> My current server has 6 CPUs but with only 100mb network card. I'm a bit
concern on the values shown for this particular counter which is average
over 27243187 batch requests/Sec. Also lately the CPU utilization is also
getting higher in the range of above 80%. The question here is whether a
network bottleneck in this can cause an increase in CPU utilization? if yes
may I know why also?
>
> Thanks
> -debbie-
> ----
--
> To get a feel of how busy SQL Server is, monitor the SQLServer: SQL
Statistics: Batch Requests/Sec counter. This counter measures the number of
batch requests that SQL Server receives per second, and generally follows in
step to how busy your server's CPUs are. Generally speaking, over 1000 batch
requests per second indicates a very busy SQL Server, and could mean that if
you are not already experiencing a CPU bottleneck, that you may very well
soon. Of course, this is a relative number, and the bigger your hardware,
the more batch requests per second SQL Server can handle.
> From a network bottleneck approach, a typical 100Mbs network card is only
able to handle about 3000 batch requests per second. If you have a server
that is this busy, you may need to have two or more network cards, or go to
a 1Gbs network card.
> ----
--
>
|||The performance monitor shows it at the scale of 1000 so
after you times it it's around the same value as
sysperfinfo. So I guess that I shouldn't times the scale?

>--Original Message--
>Sounds like you pulled the raw counters from
sysperfinfo. Those are not
>time-adjusted. See http://support.microsoft.com/?
id=555064 for a short
>explanation. You wil need to use the Performance
Monitor tool to see the
>actual per/second values.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"debcwong" <debcwong@.discussions.microsoft.com> wrote in
message
>news:92A9ACE5-072E-4DF4-822F-
B5E23B669B49@.microsoft.com...[vbcol=seagreen]
performance.com[vbcol=seagreen]
>website.
monitor screen is[vbcol=seagreen]
>in the scale of 1000.
network card. I'm a bit
>concern on the values shown for this particular counter
which is average
>over 27243187 batch requests/Sec. Also lately the CPU
utilization is also
>getting higher in the range of above 80%. The question
here is whether a
>network bottleneck in this can cause an increase in CPU
utilization? if yes[vbcol=seagreen]
>may I know why also?
--[vbcol=seagreen]
>--
SQLServer: SQL
>Statistics: Batch Requests/Sec counter. This counter
measures the number of
>batch requests that SQL Server receives per second, and
generally follows in
>step to how busy your server's CPUs are. Generally
speaking, over 1000 batch
>requests per second indicates a very busy SQL Server,
and could mean that if
>you are not already experiencing a CPU bottleneck, that
you may very well
>soon. Of course, this is a relative number, and the
bigger your hardware,[vbcol=seagreen]
>the more batch requests per second SQL Server can handle.
network card is only
>able to handle about 3000 batch requests per second. If
you have a server
>that is this busy, you may need to have two or more
network cards, or go to[vbcol=seagreen]
>a 1Gbs network card.
--
>--
>
>.
>
|||The scale on performance monitor only affects the position of the graph.
The counter value should be the correct number.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:240a801c45f46$fb515f60$a301280a@.phx.gbl...[vbcol=seagreen]
> The performance monitor shows it at the scale of 1000 so
> after you times it it's around the same value as
> sysperfinfo. So I guess that I shouldn't times the scale?
> sysperfinfo. Those are not
> id=555064 for a short
> Monitor tool to see the
> message
> B5E23B669B49@.microsoft.com...
> performance.com
> monitor screen is
> network card. I'm a bit
> which is average
> utilization is also
> here is whether a
> utilization? if yes
> --
> SQLServer: SQL
> measures the number of
> generally follows in
> speaking, over 1000 batch
> and could mean that if
> you may very well
> bigger your hardware,
> network card is only
> you have a server
> network cards, or go to
> --
|||hmm... the scale of the graph is the same as the counter
but the scale in the column beneath it is 1000...
sorry... very confusing to me.

>--Original Message--
>The scale on performance monitor only affects the
position of the graph.[vbcol=seagreen]
>The counter value should be the correct number.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
><anonymous@.discussions.microsoft.com> wrote in message
>news:240a801c45f46$fb515f60$a301280a@.phx.gbl...
so[vbcol=seagreen]
scale?[vbcol=seagreen]
in[vbcol=seagreen]
this.[vbcol=seagreen]
server-[vbcol=seagreen]
performance[vbcol=seagreen]
counter[vbcol=seagreen]
CPU[vbcol=seagreen]
--[vbcol=seagreen]
and[vbcol=seagreen]
that[vbcol=seagreen]
handle.[vbcol=seagreen]
If[vbcol=seagreen]
--
>
>.
>
|||It's easier to see the actual values in the Report mode vs. the graph mode
or Perfmon.
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:2466001c45fca$da3d27d0$a601280a@.phx.gbl...[vbcol=seagreen]
> hmm... the scale of the graph is the same as the counter
> but the scale in the column beneath it is 1000...
> sorry... very confusing to me.
> position of the graph.
> so
> scale?
> in
> this.
> server-
> performance
> counter
> CPU
> --
> and
> that
> handle.
> If
> --

Batch Requests/Sec counter

Hi,
I hope that somebody can help me to clarify for this.
Below is an excerpt extracted from the www.sql-server-performance.com websit
e.
The batch requests per second shown on the performance monitor screen is in
the scale of 1000.
My current server has 6 CPUs but with only 100mb network card. I'm a bit con
cern on the values shown for this particular counter which is average over 2
7243187 batch requests/Sec. Also lately the CPU utilization is also getting
higher in the range of abov
e 80%. The question here is whether a network bottleneck in this can cause a
n increase in CPU utilization? if yes may I know why also?
Thanks
-debbie-
----
--
To get a feel of how busy SQL Server is, monitor the SQLServer: SQL Statisti
cs: Batch Requests/Sec counter. This counter measures the number of batch re
quests that SQL Server receives per second, and generally follows in step to
how busy your server's CPU
s are. Generally speaking, over 1000 batch requests per second indicates a v
ery busy SQL Server, and could mean that if you are not already experiencing
a CPU bottleneck, that you may very well soon. Of course, this is a relativ
e number, and the bigger yo
ur hardware, the more batch requests per second SQL Server can handle.
From a network bottleneck approach, a typical 100Mbs network card is only ab
le to handle about 3000 batch requests per second. If you have a server that
is this busy, you may need to have two or more network cards, or go to a 1G
bs network card.
----
--I find it hard to believe that your system is processing 27 million batch
requests per second<g>. Where are you getting the value from?
Andrew J. Kelly SQL MVP
"debcwong" <debcwong@.discussions.microsoft.com> wrote in message
news:92A9ACE5-072E-4DF4-822F-B5E23B669B49@.microsoft.com...
> Hi,
> I hope that somebody can help me to clarify for this.
> Below is an excerpt extracted from the www.sql-server-performance.com
website.
> The batch requests per second shown on the performance monitor screen is
in the scale of 1000.
> My current server has 6 CPUs but with only 100mb network card. I'm a bit
concern on the values shown for this particular counter which is average
over 27243187 batch requests/Sec. Also lately the CPU utilization is also
getting higher in the range of above 80%. The question here is whether a
network bottleneck in this can cause an increase in CPU utilization? if yes
may I know why also?
>
> Thanks
> -debbie-
> ----
--
> To get a feel of how busy SQL Server is, monitor the SQLServer: SQL
Statistics: Batch Requests/Sec counter. This counter measures the number of
batch requests that SQL Server receives per second, and generally follows in
step to how busy your server's CPUs are. Generally speaking, over 1000 batch
requests per second indicates a very busy SQL Server, and could mean that if
you are not already experiencing a CPU bottleneck, that you may very well
soon. Of course, this is a relative number, and the bigger your hardware,
the more batch requests per second SQL Server can handle.
> From a network bottleneck approach, a typical 100Mbs network card is only
able to handle about 3000 batch requests per second. If you have a server
that is this busy, you may need to have two or more network cards, or go to
a 1Gbs network card.
> ----
--
>|||Sounds like you pulled the raw counters from sysperfinfo. Those are not
time-adjusted. See http://support.microsoft.com/?id=555064 for a short
explanation. You wil need to use the Performance Monitor tool to see the
actual per/second values.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"debcwong" <debcwong@.discussions.microsoft.com> wrote in message
news:92A9ACE5-072E-4DF4-822F-B5E23B669B49@.microsoft.com...
> Hi,
> I hope that somebody can help me to clarify for this.
> Below is an excerpt extracted from the www.sql-server-performance.com
website.
> The batch requests per second shown on the performance monitor screen is
in the scale of 1000.
> My current server has 6 CPUs but with only 100mb network card. I'm a bit
concern on the values shown for this particular counter which is average
over 27243187 batch requests/Sec. Also lately the CPU utilization is also
getting higher in the range of above 80%. The question here is whether a
network bottleneck in this can cause an increase in CPU utilization? if yes
may I know why also?
>
> Thanks
> -debbie-
> ----
--
> To get a feel of how busy SQL Server is, monitor the SQLServer: SQL
Statistics: Batch Requests/Sec counter. This counter measures the number of
batch requests that SQL Server receives per second, and generally follows in
step to how busy your server's CPUs are. Generally speaking, over 1000 batch
requests per second indicates a very busy SQL Server, and could mean that if
you are not already experiencing a CPU bottleneck, that you may very well
soon. Of course, this is a relative number, and the bigger your hardware,
the more batch requests per second SQL Server can handle.
> From a network bottleneck approach, a typical 100Mbs network card is only
able to handle about 3000 batch requests per second. If you have a server
that is this busy, you may need to have two or more network cards, or go to
a 1Gbs network card.
> ----
--
>|||The performance monitor shows it at the scale of 1000 so
after you times it it's around the same value as
sysperfinfo. So I guess that I shouldn't times the scale?

>--Original Message--
>Sounds like you pulled the raw counters from
sysperfinfo. Those are not
>time-adjusted. See http://support.microsoft.com/?
id=555064 for a short
>explanation. You wil need to use the Performance
Monitor tool to see the
>actual per/second values.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"debcwong" <debcwong@.discussions.microsoft.com> wrote in
message
>news:92A9ACE5-072E-4DF4-822F-
B5E23B669B49@.microsoft.com...
performance.com[vbcol=seagreen]
>website.
monitor screen is[vbcol=seagreen]
>in the scale of 1000.
network card. I'm a bit[vbcol=seagreen]
>concern on the values shown for this particular counter
which is average
>over 27243187 batch requests/Sec. Also lately the CPU
utilization is also
>getting higher in the range of above 80%. The question
here is whether a
>network bottleneck in this can cause an increase in CPU
utilization? if yes
>may I know why also?
--[vbcol=seagreen]
>--
SQLServer: SQL[vbcol=seagreen]
>Statistics: Batch Requests/Sec counter. This counter
measures the number of
>batch requests that SQL Server receives per second, and
generally follows in
>step to how busy your server's CPUs are. Generally
speaking, over 1000 batch
>requests per second indicates a very busy SQL Server,
and could mean that if
>you are not already experiencing a CPU bottleneck, that
you may very well
>soon. Of course, this is a relative number, and the
bigger your hardware,
>the more batch requests per second SQL Server can handle.
network card is only[vbcol=seagreen]
>able to handle about 3000 batch requests per second. If
you have a server
>that is this busy, you may need to have two or more
network cards, or go to
>a 1Gbs network card.
--[vbcol=seagreen]
>--
>
>.
>|||The scale on performance monitor only affects the position of the graph.
The counter value should be the correct number.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:240a801c45f46$fb515f60$a301280a@.phx
.gbl...[vbcol=seagreen]
> The performance monitor shows it at the scale of 1000 so
> after you times it it's around the same value as
> sysperfinfo. So I guess that I shouldn't times the scale?
>
> sysperfinfo. Those are not
> id=555064 for a short
> Monitor tool to see the
> message
> B5E23B669B49@.microsoft.com...
> performance.com
> monitor screen is
> network card. I'm a bit
> which is average
> utilization is also
> here is whether a
> utilization? if yes
> --
> SQLServer: SQL
> measures the number of
> generally follows in
> speaking, over 1000 batch
> and could mean that if
> you may very well
> bigger your hardware,
> network card is only
> you have a server
> network cards, or go to
> --|||hmm... the scale of the graph is the same as the counter
but the scale in the column beneath it is 1000...
sorry... very confusing to me.

>--Original Message--
>The scale on performance monitor only affects the
position of the graph.
>The counter value should be the correct number.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
><anonymous@.discussions.microsoft.com> wrote in message
> news:240a801c45f46$fb515f60$a301280a@.phx
.gbl...
so[vbcol=seagreen]
scale?[vbcol=seagreen]
in[vbcol=seagreen]
this.[vbcol=seagreen]
server-[vbcol=seagreen]
performance[vbcol=seagreen]
counter[vbcol=seagreen]
CPU[vbcol=seagreen]
--[vbcol=seagreen]
and[vbcol=seagreen]
that[vbcol=seagreen]
handle.[vbcol=seagreen]
If[vbcol=seagreen]
--[vbcol=seagreen]
>
>.
>|||It's easier to see the actual values in the Report mode vs. the graph mode
or Perfmon.
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:2466001c45fca$da3d27d0$a601280a@.phx
.gbl...[vbcol=seagreen]
> hmm... the scale of the graph is the same as the counter
> but the scale in the column beneath it is 1000...
> sorry... very confusing to me.
>
> position of the graph.
> so
> scale?
> in
> this.
> server-
> performance
> counter
> CPU
> --
> and
> that
> handle.
> If
> --

batch file help

I wish to have a batch file(test.bat) that does an OSQL as below. The OSQL
statement below needs some modification as well.
OSQL -E -S%1 -Q"select @.@.servername" -oc:\xyz\%1output.txt
When i enter test.bat, i want it to prompt for servername so i can add it at
the prompt
Also i would like the prompted servername to be filled in %1 parameter in
the OSQL . Can this be done ?Hi Hassan,
This is not the right place to ask any batch file related questions.
Anyway try this ....
You can specify server name in the command propmt. If you haven't specify
the server name in the command prompt it will prompt for it.
Regards,
Suhanthan, V.
suhan@.jhc.lk
----
@.ECHO OFF
IF NOT _%1_ == __ GOTO WITHPARAM
GOTO WITHOUTPARAM
:WITHPARAM
OSQL -E -S%1 -Q"select @.@.servername" -oc:\xyz\%1output.txt
GOTO EXITTHIS
:WITHOUTPARAM
SET /P SERVER="Enter SQL server name :"
OSQL -E -S%SERVER% -Q"select @.@.servername" -oc:\xyz\%1output.txt
SET SERVER=
:EXITTHIS
----
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O2$uiVRnDHA.1084@.tk2msftngp13.phx.gbl...
> I wish to have a batch file(test.bat) that does an OSQL as below. The OSQL
> statement below needs some modification as well.
> OSQL -E -S%1 -Q"select @.@.servername" -oc:\xyz\%1output.txt
> When i enter test.bat, i want it to prompt for servername so i can add it
at
> the prompt
> Also i would like the prompted servername to be filled in %1 parameter in
> the OSQL . Can this be done ?
>
>

Basic security questions

Hi,

I am new to SQL 2005, can someone give me some details instructions about how to do below two tasks:

1. All my developers are in a window domain user group, I need to grant dbo privileges to that domain group so then can do the their development work. The rule is all objects they create need to be owned by dbo not by there ID.( I can’t do it because I got “ The “Deafult_Schema clause cannot be used with a windows group”)
2. Same as above but this time they only need select permission on tables nothing else.

Many thanks.

PC

See http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=678115&SiteID=1 and the thread referenced there.

Thanks
Laurentiu