Save SP in EM to text file ? - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Save SP in EM to text file ?

Author

Topic

loiter99
Starting Member

38 Posts

Posted - 2005-01-12 : 15:02:27

Hello,
Is it possible to have a stored procedure write it's results to a text file?

thanks
J

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-12 : 16:28:50

[code]
USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(TABLE_NAME sysname)
GO

CREATE PROC mySproc99
AS
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.Tables
GO

INSERT INTO myTable99(TABLE_NAME) EXEC mySproc99

DECLARE @cmd varchar(8000)
SELECT @cmd = 'bcp myTable99 OUT d:\test.dat -c -U -P -S'
EXEC master..xp_cmdshell @cmd

SET NOCOUNT OFF
DROP PROC mySproc99
DROP TABLE myTable99
GO

[/code]

Brett

8-)

loiter99
Starting Member

38 Posts

Posted - 2005-01-13 : 09:04:21

Thanks, I am getting this error though:

Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'myTable99'.

Thanks again

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-13 : 09:41:00

Where are you executing the code from?

Did you use Query Analyzer?

Did you cut and paste the whole code snippet?

Brett

8-)

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-01-16 : 14:56:47

-- qualify the tablename:

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE dbo.myTable99(TABLE_NAME sysname)
GO

CREATE PROC dbo.mySproc99
AS
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.Tables
GO

INSERT INTO myTable99(TABLE_NAME) EXEC mySproc99

DECLARE @cmd varchar(8000)
SELECT @cmd = 'bcp northwind.dbo.myTable99 OUT d:\test.dat -c -U -P -S'
EXEC master..xp_cmdshell @cmd

SET NOCOUNT OFF
DROP PROC mySproc99
DROP TABLE myTable99
GO

loiter99
Starting Member

38 Posts

Posted - 2005-01-24 : 11:51:04

I got it working .. thanks every.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources