Please help 1

[sarahnice]

I am relatively new to SQL Server but I hope that someone out there can help me with the following:

I need a stored procedure or user defined function to generate a file of the information in a table in the database. However, the file must be of a type that will allow me to create a pivot table in Microsoft Excel.

Any help would be greatly appreciated

Tanx

 

[rajeevnandanmishra]

Hi there,
You can create flat files (Text file with delimeter) by using "bcp" utility. Only restriction is the files can only be created on the SQL server Hard Disk.
May be some one here can help you in creating text files on any machine.

 

[ColinM]

You can use something like this:

exec sp_msforeachtable @command1 = 
"exec master.dbo.xp_cmdshell
'bcp pubs.? out v:\bcp\?.csv /T /Syourserver /c /t,'"

This will output all tables in the database, connecting with a trusted connection, into comma-delimited files which can be read by excel. You can output to a mapped network drive.

 

[sarahnice]

I tried the bcp utility but it doesn't work.

 

[ColinM]

Did you get an error message?

 

[sarahnice]

I'm getting an error saying that Login failed to the requested database, but I'm giving the correct login name and password. Should I have the tables in the database configured a certain way?

 

[ColinM]

You could try a simpler version. Just go into a dos box and type something like:

bcp pubs.dbo.authors out c:\temp\authors.txt -Usa -Ppass -c

(Note: bcp commands can be case-sensitive)

 

[sarahnice]

I got rid of the error I had earlier but now I am getting an error saying that SELECT permission is denied on that object (the table). I went into the permissions and the SELECT checkbox is ticked.

Any help??
Please

 

[ColinM]

Sounds like you might be running BCP with a user account, try running with the SA account if possible.