Alter code - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Alter code

Author

Topic

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-10-27 : 10:35:27

Can the code in this thread :-
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

be altered to work with sqlserver 2005?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 10:40:07

it works with sql 2005. did you try it? Did you get any error?

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-10-27 : 10:44:10

yes, it errored when I tried to send the data to an external source:-

EXEC proc_generate_excel_with_columns 'databasename', 'viewname','\\servername\sharename'

any ideas please?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 10:52:48

what was the error message?

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-10-27 : 10:54:50

This is message:-

output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
User name not provided, either use -U to provide the user name or use -T for Trusted Connection
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
NULL

(13 row(s) affected)

output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
User name not provided, either use -U to provide the user name or use -T for Trusted Connection
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
NULL

(13 row(s) affected)

output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
The system cannot find the path specified.
NULL

(2 row(s) affected)

output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
The system cannot find the path specified.
NULL

(2 row(s) affected)

output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
User name not provided, either use -U to provide the user name or use -T for Trusted Connection
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
NULL

(13 row(s) affected)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 11:03:18

did you provide a username as specified in error?

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-10-27 : 11:07:53

How?, in what format?
like this?
EXEC proc_generate_excel_with_columns 'databasename', 'viewname','\\servername\sharename;Username=myusername;pwd=mypwd'

does not work?

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-10-28 : 05:25:17

I'm still not having any luck with this, could it be a permissions problem or am I using the wrong syntax?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-28 : 05:30:01

You are using wrong syntax.
See Books Online about the various setting for BCP command.

E 12°55'05.63"
N 56°04'39.26"

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-10-28 : 05:44:15

OK, thanks

could i embed the username and password into the function in the end of these lines?

set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c'''

set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c'''

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-28 : 07:33:27

It is much easier to build the dynamic string by itself and then execute with cmd_shell.
see http://weblogs.sqlteam.com/peterl/archive/2008/10/24/How-to-script-out-all-your-objects-one-per-file.aspx

E 12°55'05.63"
N 56°04'39.26"

travaz
Starting Member

2 Posts

Posted - 2008-10-28 : 12:06:17