Prev: Q;re Descriptive stats
Next: Retrieve Records if a Value is Null/Empty in SQl Server 2005
From: odeddror on 5 Mar 2010 05:53
Hi there,

I'm using SQL Server 2008 on Windows Vista x64

In Northwind database I wrote

DECLARE @List varchar(50)
DECLARE c1 CURSOR READ_ONLY
FOR
select Table_NAME from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE'
OPEN c1
FETCH NEXT FROM c1
INTO @List
WHILE @@FETCH_STATUS = 0
BEGIN
Print @List
--DECLARE @sql VARCHAR(1000)
-- Select @sql = 'bcp "northwind..' + @List + ' ' +' queryout
"C:\mobiledoc\"'+@List+ '.txt'' -c -t,-T -S.\' + @@Servername
-- Exec master..xp_cmdshell @sql
FETCH NEXT FROM c1 INTO @List
END
CLOSE c1
DEALLOCATE c1

When I run (with Print @List) it give me all table names with no problen
but when I use the
DECLARE @sql VARCHAR(1000)
Select @sql = 'bcp "northwind..' + @List + ' ' +' queryout
"C:\mobiledoc\"'+@List+ '.txt'' -c -t,-T -S.\' + @@Servername
Exec master..xp_cmdshell @sql

Intead, it try to create all names every loop and nutting happend

How do I solve this problem?

Thanks,
Oded Dror



From: Plamen Ratchev on 5 Mar 2010 10:36
It helps if you PRINT the BCP statement and see how it will look. You have some double and single quotes wrong. Try
something like this:

SET @sql = 'bcp "northwind..' + @List + '" out "C:\mobiledoc\' + @List + '.txt" -c -t,-T -S.\' + @@SERVERNAME;

--
Plamen Ratchev
http://www.SQLStudio.com
From: Oded Dror on 5 Mar 2010 17:04
Plamen,

When I add your line (only the
select @sql = 'bcp "northwind..' + @List + ' ' +' queryout
"C:\mobiledoc\"'+@List+ '.txt'' -c -t,-T -S.\' + @@Servername;

Nothing happend - Command(s) completed successfully.

when I added the Exec master..xp_cmdshell @sql (I fix the queryout to out
same error)

I'm getting error Copy direction must be either 'in', 'out' or 'format'.

Thank's
Oded Dror


"Plamen Ratchev" wrote:

> It helps if you PRINT the BCP statement and see how it will look. You have some double and single quotes wrong. Try
> something like this:
>
> SET @sql = 'bcp "northwind..' + @List + '" out "C:\mobiledoc\' + @List + '.txt" -c -t,-T -S.\' + @@SERVERNAME;
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com
> .
>
From: Plamen Ratchev on 5 Mar 2010 17:37
You still need to use xp_cmdshell. Please post your final code, most likely you have the syntax for BCP incorrect (check
double and single quotes, again - best to use PRINT and very all looks good).

--
Plamen Ratchev
http://www.SQLStudio.com
From: odeddror on 6 Mar 2010 09:29
Palmen,
I fix it

DECLARE @List varchar(50)
DECLARE c1 CURSOR READ_ONLY
FOR
select Table_NAME from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE'
OPEN c1
FETCH NEXT FROM c1
INTO @List
WHILE @@FETCH_STATUS = 0
BEGIN
--print @List
declare @sql varchar(1000)
Select @sql = 'bcp "Select * from Northwind.dbo.'+ @List + '"' + ' queryout
"C:\Mobiledoc\'+ @List + '.txt" -c -Uxxxxx -Pxxxxxx -S.\SQLEXPRESS'
Exec master..xp_cmdshell @sql
FETCH NEXT FROM c1 INTO @List
END
CLOSE c1
DEALLOCATE c1

Thnak you very much for your time.
Oded Dror


"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:a8ednZ3w4ucvGgzWnZ2dnUVZ_jadnZ2d(a)speakeasy.net...
> You still need to use xp_cmdshell. Please post your final code, most
> likely you have the syntax for BCP incorrect (check double and single
> quotes, again - best to use PRINT and very all looks good).
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com

 | 
Pages: 1
Prev: Q;re Descriptive stats
Next: Retrieve Records if a Value is Null/Empty in SQl Server 2005