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
fetch next from cols into @colname
end
close cols
Deallocate cols
set @sql = @sql + ' from ' + @tablename
- --print @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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy