A few years ago I had to enable users to use bcp. I recall that I had to go
in and set the proxy account via the Job System tab of the SQL Server Agent.
I thought that was ALL I had to do. But now it appears as though I need to
grant them exec rights on xp_cmdshell itself? That sucks for obvious reasons.
Is there a way around it? I really dont recall having to do that, but as
mentioned it was years ago.
TIA,
ChrisR
Chris,
Try BULK INSERT.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:A86E8074-8781-4EF2-BA84-D566C7663BB4@.microsoft.com...
>A few years ago I had to enable users to use bcp. I recall that I had to go
> in and set the proxy account via the Job System tab of the SQL Server
> Agent.
> I thought that was ALL I had to do. But now it appears as though I need to
> grant them exec rights on xp_cmdshell itself? That sucks for obvious
> reasons.
> Is there a way around it? I really dont recall having to do that, but as
> mentioned it was years ago.
> --
> TIA,
> ChrisR
sql
2012年3月20日星期二
bcp permissions for non SA
A few years ago I had to enable users to use bcp. I recall that I had to go
in and set the proxy account via the Job System tab of the SQL Server Agent.
I thought that was ALL I had to do. But now it appears as though I need to
grant them exec rights on xp_cmdshell itself? That sucks for obvious reasons.
Is there a way around it? I really dont recall having to do that, but as
mentioned it was years ago.
--
TIA,
ChrisRChris,
Try BULK INSERT.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:A86E8074-8781-4EF2-BA84-D566C7663BB4@.microsoft.com...
>A few years ago I had to enable users to use bcp. I recall that I had to go
> in and set the proxy account via the Job System tab of the SQL Server
> Agent.
> I thought that was ALL I had to do. But now it appears as though I need to
> grant them exec rights on xp_cmdshell itself? That sucks for obvious
> reasons.
> Is there a way around it? I really dont recall having to do that, but as
> mentioned it was years ago.
> --
> TIA,
> ChrisR
in and set the proxy account via the Job System tab of the SQL Server Agent.
I thought that was ALL I had to do. But now it appears as though I need to
grant them exec rights on xp_cmdshell itself? That sucks for obvious reasons.
Is there a way around it? I really dont recall having to do that, but as
mentioned it was years ago.
--
TIA,
ChrisRChris,
Try BULK INSERT.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:A86E8074-8781-4EF2-BA84-D566C7663BB4@.microsoft.com...
>A few years ago I had to enable users to use bcp. I recall that I had to go
> in and set the proxy account via the Job System tab of the SQL Server
> Agent.
> I thought that was ALL I had to do. But now it appears as though I need to
> grant them exec rights on xp_cmdshell itself? That sucks for obvious
> reasons.
> Is there a way around it? I really dont recall having to do that, but as
> mentioned it was years ago.
> --
> TIA,
> ChrisR
bcp permissions for non SA
A few years ago I had to enable users to use bcp. I recall that I had to go
in and set the proxy account via the Job System tab of the SQL Server Agent.
I thought that was ALL I had to do. But now it appears as though I need to
grant them exec rights on xp_cmdshell itself? That sucks for obvious reasons
.
Is there a way around it? I really dont recall having to do that, but as
mentioned it was years ago.
--
TIA,
ChrisRChris,
Try BULK INSERT.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:A86E8074-8781-4EF2-BA84-D566C7663BB4@.microsoft.com...
>A few years ago I had to enable users to use bcp. I recall that I had to go
> in and set the proxy account via the Job System tab of the SQL Server
> Agent.
> I thought that was ALL I had to do. But now it appears as though I need to
> grant them exec rights on xp_cmdshell itself? That sucks for obvious
> reasons.
> Is there a way around it? I really dont recall having to do that, but as
> mentioned it was years ago.
> --
> TIA,
> ChrisR
in and set the proxy account via the Job System tab of the SQL Server Agent.
I thought that was ALL I had to do. But now it appears as though I need to
grant them exec rights on xp_cmdshell itself? That sucks for obvious reasons
.
Is there a way around it? I really dont recall having to do that, but as
mentioned it was years ago.
--
TIA,
ChrisRChris,
Try BULK INSERT.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:A86E8074-8781-4EF2-BA84-D566C7663BB4@.microsoft.com...
>A few years ago I had to enable users to use bcp. I recall that I had to go
> in and set the proxy account via the Job System tab of the SQL Server
> Agent.
> I thought that was ALL I had to do. But now it appears as though I need to
> grant them exec rights on xp_cmdshell itself? That sucks for obvious
> reasons.
> Is there a way around it? I really dont recall having to do that, but as
> mentioned it was years ago.
> --
> TIA,
> ChrisR
2012年3月11日星期日
BCP help, or alternative
I need some help on this.
I currently use BCP to transfer my tab delimted text file into my SQL
2000 database. I use a format file. This has been working well, but I am
currently receiving an error message about some data. I am not concerned
about that at this time.
I'd prefer to get some feedback from you all. What other options do I
have besides BCP? The data file I have has 108 columns, and about 8000
rows so it's alot of data. I only NEED to import about 40 of those
columns. I know BCP allows this, hence why I ahve been using it.
I have a shared SQL server and do not believe I can create a DTS on it.
What other options do I have? Currently a PERL files runs all my
commands for BCP, so maybe I can use something else in the PERL file,
besides BCP.
I also have my own server, so there are no limitations there. Just with
the shared SQL box.
*** Sent via Developersdex http://www.examnotes.net ***C# is great for whipping up a quick parsing tool that will process the file
and output something a little more palatable to SQL Server. Ideally, you'd
have an output file with only the data you need, and one that can be
imported into the database using something as simple as BULK INSERT.
"Joey Martin" <joey@.kytechs.com> wrote in message
news:O2TfqxGRGHA.2532@.TK2MSFTNGP10.phx.gbl...
>
> I need some help on this.
> I currently use BCP to transfer my tab delimted text file into my SQL
> 2000 database. I use a format file. This has been working well, but I am
> currently receiving an error message about some data. I am not concerned
> about that at this time.
> I'd prefer to get some feedback from you all. What other options do I
> have besides BCP? The data file I have has 108 columns, and about 8000
> rows so it's alot of data. I only NEED to import about 40 of those
> columns. I know BCP allows this, hence why I ahve been using it.
> I have a shared SQL server and do not believe I can create a DTS on it.
> What other options do I have? Currently a PERL files runs all my
> commands for BCP, so maybe I can use something else in the PERL file,
> besides BCP.
> I also have my own server, so there are no limitations there. Just with
> the shared SQL box.
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Yeah BULK INSERT is supposed to be faster than bcp too. It has similar
arguments and can be run from a SQL script instead of a batch file. You can
even use your format file with it ( I think!).
I'm a big fan of both bcp and BULK INSERT, not so much DTS.
Damien
"Aaron Bertrand [SQL Server MVP]" wrote:
> C# is great for whipping up a quick parsing tool that will process the fil
e
> and output something a little more palatable to SQL Server. Ideally, you'
d
> have an output file with only the data you need, and one that can be
> imported into the database using something as simple as BULK INSERT.
>
>
> "Joey Martin" <joey@.kytechs.com> wrote in message
> news:O2TfqxGRGHA.2532@.TK2MSFTNGP10.phx.gbl...
>
>|||Aaron Bertrand [SQL Server MVP] (ten.xoc@.dnartreb.noraa) writes:
> C# is great for whipping up a quick parsing tool that will process the
> file and output something a little more palatable to SQL Server.
> Ideally, you'd have an output file with only the data you need, and one
> that can be imported into the database using something as simple as BULK
> INSERT.
Tsssk! Joey is already using Perl, and what better tool is there to
read and parse a file than Perl?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> Tsssk! Joey is already using Perl, and what better tool is there to
> read and parse a file than Perl?
Perl, C#, whatever. My point was that *any* application language parsing,
trimming and re-formatting the file is going to do more for you in the long
run than messing with the BCP format file and leaving the input files
untouched. My experience is with C#, not with Perl.
2012年2月23日星期四
BCP and Column Names
Hi -
I would like to export the column names and data from a table to a tab delimited file. I understand that when using BCP, one would first extract the column names and then concatenate the data. I am looking for a means to dump the column names. I am
an extremely novice user, so an example would be greatly appreciated.
Thanks!
Lily
Actually it's very easy, open query analyzer, go to options, select
Results tab, then select Results to File in Default results target drop
down box, then select the delimiter in the drop down box below, make
sure Print column headers box is checked, then click OK
then type in the follow:
set nocount on
select * from [your table]
run the query and it will ask you for the filename
Eric Li
SQL DBA
MCDBA
Lily wrote:
> Hi -
> I would like to export the column names and data from a table to a tab delimited file. I understand that when using BCP, one would first extract the column names and then concatenate the data. I am looking for a means to dump the column names. I a
m an extremely novice user, so an example would be greatly appreciated.
> Thanks!
> Lily
|||BCP will not export the column heading. You can use OSQL to do this. Look
at Books on LIne for the arguments to pass to get it to be formated as you
want.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Hi,
Easy way is to use the Import and Export Utility from SQL server program
groups. Select the source as sql server and destination as Text file.
In the "destination file format" Screen in the wizard check the option
"First row has column names".
This will give you the text file out put with comma seperated with column
headings.
Thanks
Hari
MCDBA
"Lily" <anonymous@.discussions.microsoft.com> wrote in message
news:88A999B6-73A3-4612-A6CE-93F9403039D1@.microsoft.com...
> Hi -
> I would like to export the column names and data from a table to a tab
delimited file. I understand that when using BCP, one would first extract
the column names and then concatenate the data. I am looking for a means
to dump the column names. I am an extremely novice user, so an example
would be greatly appreciated.
> Thanks!
> Lily
|||Everyone always suggests using the EM or QA tools - but they are not on every workstation - nor should they be.
How about going into EXCEL - DATA>IMPORT EXTERNAL DATA>IMPORT DATA>NEW SQL SERVER CONNECTION...
Follow that through - open the table you want and you get data in EXCEL - with headings.
EXCEL can save as TDF, CSV, XLS - whatever you want.
Once you create a NEW SQL SERVER CONNECTION, it remains in the list for future use.
I would like to export the column names and data from a table to a tab delimited file. I understand that when using BCP, one would first extract the column names and then concatenate the data. I am looking for a means to dump the column names. I am
an extremely novice user, so an example would be greatly appreciated.
Thanks!
Lily
Actually it's very easy, open query analyzer, go to options, select
Results tab, then select Results to File in Default results target drop
down box, then select the delimiter in the drop down box below, make
sure Print column headers box is checked, then click OK
then type in the follow:
set nocount on
select * from [your table]
run the query and it will ask you for the filename
Eric Li
SQL DBA
MCDBA
Lily wrote:
> Hi -
> I would like to export the column names and data from a table to a tab delimited file. I understand that when using BCP, one would first extract the column names and then concatenate the data. I am looking for a means to dump the column names. I a
m an extremely novice user, so an example would be greatly appreciated.
> Thanks!
> Lily
|||BCP will not export the column heading. You can use OSQL to do this. Look
at Books on LIne for the arguments to pass to get it to be formated as you
want.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Hi,
Easy way is to use the Import and Export Utility from SQL server program
groups. Select the source as sql server and destination as Text file.
In the "destination file format" Screen in the wizard check the option
"First row has column names".
This will give you the text file out put with comma seperated with column
headings.
Thanks
Hari
MCDBA
"Lily" <anonymous@.discussions.microsoft.com> wrote in message
news:88A999B6-73A3-4612-A6CE-93F9403039D1@.microsoft.com...
> Hi -
> I would like to export the column names and data from a table to a tab
delimited file. I understand that when using BCP, one would first extract
the column names and then concatenate the data. I am looking for a means
to dump the column names. I am an extremely novice user, so an example
would be greatly appreciated.
> Thanks!
> Lily
|||Everyone always suggests using the EM or QA tools - but they are not on every workstation - nor should they be.
How about going into EXCEL - DATA>IMPORT EXTERNAL DATA>IMPORT DATA>NEW SQL SERVER CONNECTION...
Follow that through - open the table you want and you get data in EXCEL - with headings.
EXCEL can save as TDF, CSV, XLS - whatever you want.
Once you create a NEW SQL SERVER CONNECTION, it remains in the list for future use.
BCP and Column Names
Hi -
I would like to export the column names and data from a table to a tab delim
ited file. I understand that when using BCP, one would first extract the c
olumn names and then concatenate the data. I am looking for a means to dum
p the column names. I am
an extremely novice user, so an example would be greatly appreciated.
Thanks!
LilyActually it's very easy, open query analyzer, go to options, select
Results tab, then select Results to File in Default results target drop
down box, then select the delimiter in the drop down box below, make
sure Print column headers box is checked, then click OK
then type in the follow:
set nocount on
select * from [your table]
run the query and it will ask you for the filename
Eric Li
SQL DBA
MCDBA
Lily wrote:
> Hi -
> I would like to export the column names and data from a table to a tab delimited f
ile. I understand that when using BCP, one would first extract the column names an
d then concatenate the data. I am looking for a means to dump the column names.
I a
m an extremely novice user, so an example would be greatly appreciated.
> Thanks!
> Lily|||BCP will not export the column heading. You can use OSQL to do this. Look
at Books on LIne for the arguments to pass to get it to be formated as you
want.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Hi,
Easy way is to use the Import and Export Utility from SQL server program
groups. Select the source as sql server and destination as Text file.
In the "destination file format" Screen in the wizard check the option
"First row has column names".
This will give you the text file out put with comma seperated with column
headings.
Thanks
Hari
MCDBA
"Lily" <anonymous@.discussions.microsoft.com> wrote in message
news:88A999B6-73A3-4612-A6CE-93F9403039D1@.microsoft.com...
> Hi -
> I would like to export the column names and data from a table to a tab
delimited file. I understand that when using BCP, one would first extract
the column names and then concatenate the data. I am looking for a means
to dump the column names. I am an extremely novice user, so an example
would be greatly appreciated.
> Thanks!
> Lily|||Everyone always suggests using the EM or QA tools - but they are not on ever
y workstation - nor should they be.
How about going into EXCEL - DATA>IMPORT EXTERNAL DATA>IMPORT DATA>NEW SQL S
ERVER CONNECTION...
Follow that through - open the table you want and you get data in EXCEL - wi
th headings.
EXCEL can save as TDF, CSV, XLS - whatever you want.
Once you create a NEW SQL SERVER CONNECTION, it remains in the list for futu
re use.
I would like to export the column names and data from a table to a tab delim
ited file. I understand that when using BCP, one would first extract the c
olumn names and then concatenate the data. I am looking for a means to dum
p the column names. I am
an extremely novice user, so an example would be greatly appreciated.
Thanks!
LilyActually it's very easy, open query analyzer, go to options, select
Results tab, then select Results to File in Default results target drop
down box, then select the delimiter in the drop down box below, make
sure Print column headers box is checked, then click OK
then type in the follow:
set nocount on
select * from [your table]
run the query and it will ask you for the filename
Eric Li
SQL DBA
MCDBA
Lily wrote:
> Hi -
> I would like to export the column names and data from a table to a tab delimited f
ile. I understand that when using BCP, one would first extract the column names an
d then concatenate the data. I am looking for a means to dump the column names.
I a
m an extremely novice user, so an example would be greatly appreciated.
> Thanks!
> Lily|||BCP will not export the column heading. You can use OSQL to do this. Look
at Books on LIne for the arguments to pass to get it to be formated as you
want.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Hi,
Easy way is to use the Import and Export Utility from SQL server program
groups. Select the source as sql server and destination as Text file.
In the "destination file format" Screen in the wizard check the option
"First row has column names".
This will give you the text file out put with comma seperated with column
headings.
Thanks
Hari
MCDBA
"Lily" <anonymous@.discussions.microsoft.com> wrote in message
news:88A999B6-73A3-4612-A6CE-93F9403039D1@.microsoft.com...
> Hi -
> I would like to export the column names and data from a table to a tab
delimited file. I understand that when using BCP, one would first extract
the column names and then concatenate the data. I am looking for a means
to dump the column names. I am an extremely novice user, so an example
would be greatly appreciated.
> Thanks!
> Lily|||Everyone always suggests using the EM or QA tools - but they are not on ever
y workstation - nor should they be.
How about going into EXCEL - DATA>IMPORT EXTERNAL DATA>IMPORT DATA>NEW SQL S
ERVER CONNECTION...
Follow that through - open the table you want and you get data in EXCEL - wi
th headings.
EXCEL can save as TDF, CSV, XLS - whatever you want.
Once you create a NEW SQL SERVER CONNECTION, it remains in the list for futu
re use.
订阅：
博文 (Atom)