March 17, 2008 at 11:19 am
Hi everybody,
I want create a job and I want to purchase a select a few complex with subselect. How I can doing this?
select distinct substring(s.Text,charindex('!!FirstName',s.Text)+12,charindex('!!LastName',s.Text)-(charindex('!!FirstName',s.Text)+12)) FIRST_NAME
from systemlog s WITH (NOLOCK)
where s.id=(select max(sy.id) from systemlog sy
where substring(sy.Text,charindex('!!Email',sy.Text)+8,charindex('!!Prefijo',sy.Text)-(charindex('!!Email',sy.Text)+8))=
substring(s.Text,charindex('!!Email',s.Text)+8,charindex('!!Prefijo',s.Text)-(charindex('!!Email',s.Text)+8))
and (sy.text like 'Welcome1!!Paso1Id:%' or sy.text like 'Welcome!!Paso1Id:%'))
Thanks for your help.
March 17, 2008 at 12:38 pm
There are multiple ways to accomplish this task. You can use BCP, SQL CMD or SSIS. I would choose SSIS. All you have to do is right-click a database --> tasks --> export data. Choose the database for the source and choose an Excel file for the destination. The Excel file does not have to exist. The the wizard will ask you want to export a table or write a query. Choose write a query and paste your query.
If you want to use SQLCMD or BCP, it may be easier to create a view then export all data from the view.
March 17, 2008 at 12:50 pm
Thanks Adam by your answer.
I want to use the BCP. Sorry is the first time that I going to use this function, can yu help me, step by step how I can use the BCP with a simple example.
Thanks a lot.
March 17, 2008 at 1:13 pm
You can schedule the method I mentioned to you. It performs a bulk operation and will automatically generate the package for you. The package can then be stored in SQL Server MSDB or on the file system. You can schedule a job to run this package as often as you like.
If you want to do bcp the you are going to have top open xp_cmdshell, in the job. Your script will look like this.
bcp "Select * from MyDB..MyView" queryout "C:\MyXls.xls" -c -U MyUserName -P MyPassword -S MyServerInstance
Edited closing code tag
March 17, 2008 at 1:37 pm
Adam, finally I can export the data...but How i can export the data with headers of columns the table?.
Thanks a lot by your help.
March 17, 2008 at 2:53 pm
BCP does not export column names. You would have to use SQLCMD or OSQL to export the column. Keep in mind that if you plan on bulk importing then you should not worry about column names because in you import specifications you can provide them.
SQLCMD would be something like this, but I do not think it is what you are looking for.
SQLCMD -S MyServer -E -d MyDatabase -Q "SELECT * FROM MyDb..MyView" -o "C:\Excel.xls"
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply