Easy BCP question ;)

Hey all,

A nice easy BCP question for y'all...
I'm currently running:

DECLARE @.sql varchar(2000)

SET @.sql = 'BCP master..sysobjects OUT C:\sysobjects.csv -c -t, -T -S' + @.@.ServerName

EXEC master..xp_cmdshell @.sql

Which works fine!
But how on earth do I save it to my local PC?
May sound silly, but this is one of my first adventures into BCP and I'd rather not "pollute" the server with my test files!

Thankyall!Maybe if you ran the bcp command from your local command line? There is no real reason you need xp_cmdshell.|||indeed. another possibility is to write the file to a share (but then you are susceptible to network glitches of course)

BCP master..sysobjects OUT \\SOME_OTHER_SERVER\sysobjects.csv ...|||There is no real reason you need xp_cmdshell.

Can I have a hit off of that?

This is the way I always do this

Unless I'm having a problem, then I do command line to get a better error message

George, what do you mean by pollute?

You can use xp_... to delete the damn files as well|||The guys downstairs get antsy when I leave csv files on the server that I maintain.
They polluted one of our production servers with an image of a laptop once (15Gb when we had 15.1Gb free!) and ever since then are very quick to point out when I leave a 0.5Mb csv on a production server.

anyhow, enough office politics.
I wouldn't know what to do from the command line - I tried running the above straight in (ok, I had 11 seconds left of my working day - I wasn't thorough (or bothered)) and I just got a "BCP is not a recognized function" (or similar).

Anyhow, it's pretty much a non-issue I suppose because I just have to remember to delete them, or as Brett suggested, I will go digging for the xp_.. to delete the bugger when I have some play time.

Thanks all :)|||oh never mind|||The guys downstairs get antsy when I leave csv files on the server that I maintain.
They polluted one of our production servers with an image of a laptop once (15Gb when we had 15.1Gb free!) and ever since then are very quick to point out when I leave a 0.5Mb csv on a production server.

Scrubs

This message is too short|||Anyhow, it's pretty much a non-issue I suppose because I just have to remember to delete them, or as Brett suggested, I will go digging for the xp_.. to delete the bugger when I have some play time.

It's called DOS youngster and the command is DEL. Kids today and their GUIs.

anyhow, enough office politics.

just remember one thing...

crush your enemies, drive them before you, and hear the lamentations of their women|||Sean, I learned a lot in DOS... But it's been far too long since I've had to use it!
Nothing would sodding run through the GUI on my Windows 3.0 PC ;)

Dir /w :D

Dynamically use variables in SQL in EXECUTE

Hi,
What I want to do is:
DECLARE @.sqlName varchar(255)
DECLARE @.temp NVARCHAR(100)
SET @.sqlName =(select name from master.dbo.sysdatabases where name like
'Job_%')
SET @.temp = 'USE ' + RTRIM(@.sqlName)
PRINT @.sqlName
EXEC (@.temp)
GO
--rest of my SQL code
--
Now basically I am going to have this script to run on multiple
databases where the database could be something different.
ex.
Computer1 - DB: Job_1234
Computer2 - DB: Job_5678
Before I run my code I want to make sure it runs under the correct
database. It finds the right database using select name from
master.dbo.sysdatabases where name like 'Job_%'
but how do I execute the USE @.temp statement. It says it executes, but
it still displays the master database in Query Analyzer. Any ideas on
how to do this? I just basically need to get this dynamic USE
statement to work. Thanks in advance.stuart.k...@.gmail.com wrote:
> Hi,
> What I want to do is:
> DECLARE @.sqlName varchar(255)
> DECLARE @.temp NVARCHAR(100)
> SET @.sqlName =(select name from master.dbo.sysdatabases where name like
> 'Job_%')
> SET @.temp = 'USE ' + RTRIM(@.sqlName)
> PRINT @.sqlName
> EXEC (@.temp)
> GO
> --rest of my SQL code
> --
> Now basically I am going to have this script to run on multiple
> databases where the database could be something different.
> ex.
> Computer1 - DB: Job_1234
> Computer2 - DB: Job_5678
> Before I run my code I want to make sure it runs under the correct
> database. It finds the right database using select name from
> master.dbo.sysdatabases where name like 'Job_%'
> but how do I execute the USE @.temp statement. It says it executes, but
> it still displays the master database in Query Analyzer. Any ideas on
> how to do this? I just basically need to get this dynamic USE
> statement to work. Thanks in advance.
Your code should work but the USE is scoped to the EXEC statement. Once
the EXEC is done you are returned to where you started. You need to put
some other code into the EXEC string as well if you want it to execute
in the context of another database.
EXEC is a pretty useless tool for this kind of thing. It's much easier
to parameterize the database in a connection string or at the OSQL
command prompt.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks a lot.
This worked if I ran something like
EXEC (@.temp + ' ' + @.code)
where @.code is the rest of my code that I wanted to run. I would use
OSQL if I could but unfortunately I can't.
Thanks again for your quick response.
-Stu
David Portas wrote:
> stuart.k...@.gmail.com wrote:
> Your code should work but the USE is scoped to the EXEC statement. Once
> the EXEC is done you are returned to where you started. You need to put
> some other code into the EXEC string as well if you want it to execute
> in the context of another database.
> EXEC is a pretty useless tool for this kind of thing. It's much easier
> to parameterize the database in a connection string or at the OSQL
> command prompt.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --|||You want to change context switching.
You can search sp_executeresultset on SQL Server 2000 SP3 later.
Not S2K5.
You can use below sample query.
DECLARE @.PROC NVARCHAR(4000)
SET @.PROC ='job_1234' + '.DBO.SP_EXECRESULTSET'
EXEC @.PROC @.SQLSTMT
"stuart.karp@.gmail.com"?? ??? ??:

> Hi,
> What I want to do is:
> DECLARE @.sqlName varchar(255)
> DECLARE @.temp NVARCHAR(100)
> SET @.sqlName =(select name from master.dbo.sysdatabases where name like
> 'Job_%')
> SET @.temp = 'USE ' + RTRIM(@.sqlName)
> PRINT @.sqlName
> EXEC (@.temp)
> GO
> --rest of my SQL code
> --
> Now basically I am going to have this script to run on multiple
> databases where the database could be something different.
> ex.
> Computer1 - DB: Job_1234
> Computer2 - DB: Job_5678
> Before I run my code I want to make sure it runs under the correct
> database. It finds the right database using select name from
> master.dbo.sysdatabases where name like 'Job_%'
> but how do I execute the USE @.temp statement. It says it executes, but
> it still displays the master database in Query Analyzer. Any ideas on
> how to do this? I just basically need to get this dynamic USE
> statement to work. Thanks in advance.
>

Dynamically creating new worksheets to an existing excel document

Hi All,

I have an exiting excel workbook say master.xls. Now I need to dynamically create and append a new worksheet to the above master.xls every month end using the Reporting services.

Could you please guide me how dynamically creating the worksheets task can be achieved using the reporting services?

Your any guidance or help in this matter will be highly appreciated.

Thanks in advance

Regards

Raman Kohli

You have a few options.

You could write a VBA macro in your excel book that renders the report as excel and then use the VBA code to move the worksheet from the generated book to your master.xls

Your other option might be to use rs.exe. You will need to create a .rss file, which is basically a VB Script file with access to the RS object model to create the excel download. You could potentially add the code to move the worksheet to your master.xls here too. You then execute this from the command line.

Check this area of MSDN http://msdn2.microsoft.com/en-us/library/ms152908.aspx