September 29, 2011 at 7:41 am
Hi,
I need to make a script and need that the results are saved to txt file or sql file.
I now one way to do this, in the query option pick the option save results to file.
The problem is that i need to send this script to a lot of places and some do not have a person that can choose the option save to file before run the scripts.
In other places, i have people but they don't know how to do this.
So, can i put the option on the script to automaticly save the results to c:\FileName.SQL?
If yes, how?
Thank you
September 29, 2011 at 12:58 pm
Outside of a third party application, cmdshell is your only option.
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
September 30, 2011 at 12:42 am
you can also try BCP with queryout parameter (if your into batch files)
"-=Still Learning=-"
Lester Policarpio
October 3, 2011 at 9:29 am
I am trying like this:
use sgct
declare @var1 as varchar(8000)
set @var1 = (
select min ('insert into temporaria values ('''+ convert(varchar,a.datah,120)) + ''',', ''''+b.rgc+''') GO'
from
(select datah,codctb from h_contribuintesest
union
select datah,codctb from h_contribuinteInst
union
select datah,codctb from h_contribuinteE) a, allctb b
where a.codctb=b.codctb and b.rgc is not null and a.codctb
in
(select codctb from contribuinteE
where substring(replace(nifctb,'C',''),2,3)=(select replace(numrf,'.','')
from repfiscal where codrf =(select codrf from configuracao))
union
select codctb from contribuinteInst
where substring(replace(nifctb,'C',''),2,3)=(select replace(numrf,'.','')
from repfiscal where codrf =(select codrf from configuracao))
union
select codctb from contribuintesEST
where substring(replace(nifctb,'C',''),2,3)=(select replace(numrf,'.','')
from repfiscal where codrf =(select codrf from configuracao))
and substring(replace(nifctb,'C',''),1,1) ='2'
union
select codctb from contribuintesEST
where codrf = (select codrf from configuracao)
and substring(replace(nifctb,'C',''),1,1) ='1')
group by b.rgc
)
set @var1 = @var1 + ' > c:\test1.txt'
EXEC master..xp_cmdshell @var1
But i receive an error saying:
Msg 116, Level 16, State 1, Line 4
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Can someone help?
Thank you
October 3, 2011 at 9:41 am
change all the snippets like this:
...where codrf = (select ...
--TO
...where codrf IN (select ...
and somewhere in code you did not post, you have something like this:
...AND SOMECOLUMN IN= (select * FROM...
--Needs to be
...AND SOMECOLUMN IN= (select OneColumn FROM ...
Lowell
October 3, 2011 at 9:43 am
The problem is not with the query...
If i execute the query, it runs well.
The problem is when o try to save the results to a txt file
October 3, 2011 at 9:44 am
river1 (10/3/2011)
The problem is not with the query...If i execute the query, it runs well.
The problem is when o try to save the results to a txt file
where is the code you are trying to use to save the results?
Lowell
October 3, 2011 at 9:48 am
This is my code (it runs well):
select min ('insert into temporaria values ('''+ convert(varchar,a.datah,120)) + ''',', ''''+b.rgc+''') GO'
from
(select datah,codctb from h_contribuintesest
union
select datah,codctb from h_contribuinteInst
union
select datah,codctb from h_contribuinteE) a, allctb b
where a.codctb=b.codctb and b.rgc is not null and a.codctb
in
(select codctb from contribuinteE
where substring(replace(nifctb,'C',''),2,3)=(select replace(numrf,'.','')
from repfiscal where codrf =(select codrf from configuracao))
union
select codctb from contribuinteInst
where substring(replace(nifctb,'C',''),2,3)=(select replace(numrf,'.','')
from repfiscal where codrf =(select codrf from configuracao))
union
select codctb from contribuintesEST
where substring(replace(nifctb,'C',''),2,3)=(select replace(numrf,'.','')
from repfiscal where codrf =(select codrf from configuracao))
and substring(replace(nifctb,'C',''),1,1) ='2'
union
select codctb from contribuintesEST
where codrf = (select codrf from configuracao)
and substring(replace(nifctb,'C',''),1,1) ='1')
group by b.rgc
But i want to save the result of the query to a txt file or SQL file...
October 3, 2011 at 10:06 am
well to do it exclusively via xp_cmdshell, you've got to have the right command, and it's got limitations.
the maximum length of the string that you can use at the command prompt is 8191 characters.
the right wat to get a string into a file via cmdline is witht he ECHO command:
note that the single > directs to create a file, and the double >> means append.
echo "a string" > c:\data\contents.txt
echo "another line to put in the file " >> c:\data\contents.txt
if you looped thru the results, you could do it that way, but why are you not using bcp?
Lowell
October 3, 2011 at 10:11 am
Because i want to generate a file like this:
Insert into ....... values () GO
Insert into ........ values () GO
And when i try to execute this query from inside the bulk i always receive erros...
Do you have any ideia of how i can execute this query from inside a BCP?
query:
select min ('insert into temporaria values ('''+ convert(varchar,a.datah,120)) + ''',', ''''+b.rgc+''') GO'
from
(select datah,codctb from h_contribuintesest
union
select datah,codctb from h_contribuinteInst
union
select datah,codctb from h_contribuinteE) a, allctb b
where a.codctb=b.codctb and b.rgc is not null and a.codctb
in
(select codctb from contribuinteE
where substring(replace(nifctb,'C',''),2,3)=(select replace(numrf,'.','')
from repfiscal where codrf =(select codrf from configuracao))
union
select codctb from contribuinteInst
where substring(replace(nifctb,'C',''),2,3)=(select replace(numrf,'.','')
from repfiscal where codrf =(select codrf from configuracao))
union
select codctb from contribuintesEST
where substring(replace(nifctb,'C',''),2,3)=(select replace(numrf,'.','')
from repfiscal where codrf =(select codrf from configuracao))
and substring(replace(nifctb,'C',''),1,1) ='2'
union
select codctb from contribuintesEST
where codrf = (select codrf from configuracao)
and substring(replace(nifctb,'C',''),1,1) ='1')
group by b.rgc
October 3, 2011 at 10:17 am
I try with BCP and the same happens (errors):
declare @teste varchar(8000)
set @teste =(
select min ('insert into temporaria values ('''+ convert(varchar,a.datah,120)) + ''',', ''''+b.rgc+''') GO'
from
(select datah,codctb from h_contribuintesest
union
select datah,codctb from h_contribuinteInst
union
select datah,codctb from h_contribuinteE) a, allctb b
where a.codctb=b.codctb and b.rgc is not null and a.codctb
in
(select codctb from contribuinteE
where substring(replace(nifctb,'C',''),2,3)=(select replace(numrf,'.','')
from repfiscal where codrf =(select codrf from configuracao))
union
select codctb from contribuinteInst
where substring(replace(nifctb,'C',''),2,3)=(select replace(numrf,'.','')
from repfiscal where codrf =(select codrf from configuracao))
union
select codctb from contribuintesEST
where substring(replace(nifctb,'C',''),2,3)=(select replace(numrf,'.','')
from repfiscal where codrf =(select codrf from configuracao))
and substring(replace(nifctb,'C',''),1,1) ='2'
union
select codctb from contribuintesEST
where codrf = (select codrf from configuracao)
and substring(replace(nifctb,'C',''),1,1) ='1')
group by b.rgc
)
exec('bcp '+@teste+' queryout c:\authors.txt -U pedro -P pedro')
error:
Msg 116, Level 16, State 1, Line 6
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
October 3, 2011 at 11:34 am
PRINT @teste just before the attempt to bcp. it's probably a single INSERT command, right, and not a whole stack of inserts, because you are not putting your results together with FOR XML to concatenate them.
next, you should know with the number of posts that you have that you need to call bcp via xp_cmdshell. you cannot EXEC bcp.
this command:
EXEC('bcp ' + @teste + ' queryout c:\authors.txt -U pedro -P pedro')
will raise an error unless, by coincidence, you happen to have a procedure named bcp.
now, finally, you can already generated those INSERT... statements, and could have easily have copied and pasted them in notepad since the time you first posted this, so why are you trying to automate this to export regularly or something?
why not do it in a programming language, which can easily write to a file, connect to the server, and much more.
Lowell
October 4, 2011 at 1:50 pm
You can use DBMail to send an attachment of the results of a SELECT statement.
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL Server Mail Profile',
@recipients = 'recipname@somecompany.com',
@query = 'SELECT * FROM OrderDetails WHERE OrderID=3' ,
@subject = 'OrderDetails for Order 3',
@attach_query_result_as_file = 1 ;
This will send the results of the SELECT statement as a text file to the recipient. (suggested that you do research on sp_send_dbmail)
Only problem is your settings may not allow a file attachment (in SQL Server) to out that is that big (separate setting in SQL Server).
To see largest file size setting:
EXECUTE msdb.dbo.sysmail_help_configure_sp ;
To change largest attachment size to 2 MB:
EXECUTE msdb.dbo.sysmail_configure_sp 'MaxFileSize', '2097152' ;
If you already have DBMail set up, give it a try with your email address.
This is all code-based. You could build your select statement to run off input parameters, and your list of recipients' email addresses (with ; between them), within the code.
Hope this helps.
October 4, 2011 at 1:56 pm
Here is another example from a blog on SQL Server Central.
http://www.sqlservercentral.com/blogs/sqlrnnr/archive/2011/8/15/send-dbmail.aspx
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply