February 18, 2008 at 6:12 am
Can anyone point me at a script that will generically build up a script to use in BCP or SQLCMD? It will be just a select "column list" from "table" where "filter" type thing.
Or would SSIS be better?
Many thanks
February 20, 2008 at 1:19 pm
So the script you want isn't a generic bcp format with its possible parameters
Like this
bcp AdventureWorks.Sales.Currency out "Currency Types.dat" -T -c
You want a generic select...from...where so you can then put this into the bcp parameter file name???
February 21, 2008 at 12:29 pm
Yes,
Sorry I wan't clear. I want a script to build a SELECT statement, which I can then pass to a bcp command.
February 21, 2008 at 12:35 pm
Again not sure if I follow...a 'shell' SELECT script is this
SELECT [column1]
,[column2]
,[column3]
,[column4]
,[column5]
,[column6]
,[column7]
FROM [dbname].[dbo].[tablename]
Is this what you want, something very generic so you can populate later?
February 21, 2008 at 12:40 pm
You could do something like this:
declare @cmd nvarchar(4000),
@Server nvarchar(50),
@database nvarchar(50),
@table nvarchar (50),
@username nvarchar(50),
@Password nvarchar(50),
@Columns nvarchar(500),
@filter nvarchar(500)
SET @Server = N'MYDBSERVER'
SET @database = N'DBName'
SET @table = N'TableName'
--If you do not supply a user name a trusted connection is used
SET @username = N'test'
SET @Password = N'test'
SET @Columns = N'*'
SET @cmd = N'SQLCMD -S ' + @Server + ' -d '+ @database
--If a filter is not specified it is not used in the query.
--FILTER SHOULD BE LIKE MYCOLUMN NAME = VALUE AND MYCOLMN2 = VALUE
SET @filter = N'MYCOLUMN = ''SOMETEXT'''
IF @username IS NOT NULL OR LEN(@username) > 0
BEGIN
SET @cmd = @cmd + ' -U ' + @username + ' -P ' + @password + ''
END
ELSE
BEGIN
SET @cmd = @cmd + ' -E ' --TRUSTED CONNECTION
END
SET @cmd = @cmd + ' -q "SELECT ' + @columns + ' FROM ' + @table
IF @filter IS NOT NULL OR LEN(@filter) > 0
BEGIN
SET @cmd = @cmd + ' WHERE ' + @filter + '"'
END
ELSE
BEGIN
SET @cmd = @cmd + '"'
END
EXEC Master..xp_CmdShell @cmd
February 21, 2008 at 12:56 pm
...keep in mind that unless you plan on picking and choosing columns, you don't need to create a select statement.
In other words - if you want to just export a table, use
BCP MyDatabaseName.MyUser.TableName OUT c:\myTextFileForOutput -T -c -S MyServerName
No query needed.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 26, 2008 at 11:52 am
thank you all for your help. I have been working on a generic script, and trying to incorporate the suggestions given, but I think I am getting in a bit of a mess between bcp and SQLCMD.
Which one should I use, and please could anyone clarify the correct syntax. Many thanks!
This is what I am doing:
declare @sql nvarchar(max),
@colname as nvarchar(1000),
@cmd nvarchar(4000),
@server nvarchar(50),
@database nvarchar(50),
@filter nvarchar(500),
@Targetfolder nvarchar(500),
@TargetFileName nvarchar(500),
@tablename sysname
SET @server = N'server'
SET @database = N'database'
set @tablename=''
set @TargetFolder = ''
set @targetFileName = ''
SET @cmd = N'SQLCMD -S ' + @Server + ' -d '+ @database
SET @filter = N'MYCOLUMN = ''SOMETEXT'''
SET @cmd = @cmd + ' -q '
declare names cursor for
select 'dbo.' + name from sysobjects where xtype='u'
and name = @tablename
open names
fetch next from names into @tablename
while @@fetch_status = 0
begin
select @sql = 'SELECT DETAILREC FROM (
SELECT -1 AS SORTKEY,
''H''+
'',''+ convert (varchar(8) , GETDATE(), 112)+
'',''
+right(''0''+convert(varchar(20),datepart(hh,getdate())),2)
+right(''0''+convert(varchar(20),datepart(mi,getdate())),2)
+right(''0''+convert(varchar(20),datepart(ss,getdate())),2)
AS DETAILREC
UNION '
declare cols cursor for
select case when colorder=mincol then 'SELECT 100 AS SORTKEY,
''D'' +
' else '' end +
' '','' + '+
case xtype
when 52 then ' CONVERT( VARCHAR (20),ISNULL(' -- int
when 56 then ' CONVERT( VARCHAR (20),ISNULL(' -- int
when 167 then '''"'' + rtrim(' -- varchar
when 231 then '''"'' + rtrim(' -- nvarchar
when 175 then '''"'' + rtrim(' -- char
when 58 then '''"'' + convert(varchar(20),' --
smalldatetime
when 61 then '''"'' + convert(varchar(20),' --
datetime
when 104 then '' -- bit
else '' end + rtrim(name) +
case xtype
when 52 then ','''' ) )' -- int
when 56 then ','''' ) )' -- int
when 167 then ') + ''"''' -- varchar
when 231 then ') + ''"''' -- nvarchar
when 175 then ') + ''"''' -- char
when 58 then ',112)+''"''' -- smalldatetime
when 61 then ',112)+''"''' -- datetime
when 104 then '' -- bit
else '' end
+ case when colorder=maxcol then '' else ' + ' end +
' -- ' + rtrim(name) + char(10) + char(13)
+ case when colorder=maxcol then '
AS DETAILREC' else ' + ' end
from syscolumns AS SYSCOL CROSS JOIN ( SELECT MAX(COLORDER) AS MAXCOL
FROM SYSCOLUMNS B
WHERE B.ID=object_id(@tablename) ) C
CROSS JOIN ( SELECT MIN(COLORDER) AS MINCOL FROM SYSCOLUMNS B
WHERE B.ID=object_id(@tablename) ) D
where id=object_id(@tablename)
order by colorder
open cols
fetch next from cols into @colname
while @@fetch_status = 0
begin
set @sql = @sql + @colname
fetch next from cols into @colname
end
close cols
Deallocate cols
set @sql = @sql + ' from ' + @tablename
- --print @sql
select @sql = @sql + '
UNION SELECT 999999999 AS SORTKEY,
''F,''+ CONVERT(VARCHAR(10), ( SELECT COUNT(*) FROM ' +
'
'+ @tablename + ' ) ) AS DETAILREC
) AS RESULTS ORDER BY SORTKEY
go'
fetch next from names into @tablename
end
close names
Deallocate names
IF @filter IS NOT NULL OR LEN(@filter) > 0
BEGIN
SET @cmd = @cmd + ' WHERE ' + @filter + '"'
END
ELSE
BEGIN
SET @cmd = @cmd + '"'
END
SET @cmd = @cmd + @sql + ' ' + @Targetfolder + '\' + @TargetFileName
PRINT @cmd
- --EXEC Master..xp_CmdShell @cmd
February 26, 2008 at 12:31 pm
Are you trying to run a query or output the query results to a file?
February 26, 2008 at 3:20 pm
I am trying t output the query to a text file
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply