January 28, 2013 at 10:01 am
Hi,
Can someone please refresh my mind and tell me how can I pass parameters or variables into SQLCMD? I just don't remember ...
I am testing manually on a batch file, and this is what I am trying to do ..
:setvar path= "\\Myserver\something.txt"
"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE" -S SQLSERVER -i \\MyServer\script.sql -o path
I will later use that as a step inside a SQL job.
Basically, I want to assign the path or variable to "o", which is a txt file and will be dynamic.
Thanks in advance,
*** EDIT ***
Just noticed I created this on the wrong forum, apologies for that. Moderator, please move as needed,
Thanks,
January 28, 2013 at 3:00 pm
i have provided you an example of one i did for work, you use this as an example t achieve your task.
Create proc [dbo].[CSV_EXPORT] ( @path varchar(255))
as
/*
Ref: TASK1082
Name: Export_to_csv.sql
Version: 1.0
Author: Glen Wass
Date Created: 25/01/2013
Description:
Impact:
Example exec:
:
Version History: DateVersionAuthorComments
---------------------------------------------------------------------------
25/01/2013|1.0| GW | Initial release
*/
Begin
declare @filename varchar(50),
@exec varchar (2000),
@chk char(1)
set @chk=substring(reverse(@path),0,2)
if @chk !='\' --'
begin
set @path=@path+'\' --'
end
set @filename='CSV_EXPORT_'
set @filename=@filename+cast(datepart(YY,getdate()) as varchar)+substring(convert(varchar,getdate(),121),6,2)+
substring(convert(varchar,getdate(),121),9,2)+'_'+substring(convert(varchar,getdate(),121),12,2)+
substring(convert(varchar,getdate(),121),15,2)
set @filename=@filename+'.csv'
if not exists (select 1 from sysobjects where name='tmp_invoice' and xtype='u')
begin
create table tmp_invoice (InvoiceOIDvarchar(20),
InvoiceNumbervarchar (50),
PartnerOIDvarchar(20),
Templatevarchar(250),
AddressOIDvarchar(20),
InvoiceDatevarchar(25),
CreationUserOIDvarchar(20))
end
else
begin
truncate table tmp_invoice
end;
Begin tran doinsert
insert into tmp_invoice select 'InvoiceOID','InvoiceNumber','PartnerOID','Template','AddressOID','InvoiceDate','CreationUserOID'
union all
select top 100 cast(I.InvoiceOID as varchar),cast(InvoiceNumber as varchar),cast(PartnerOID as varchar),
Template,cast(AddressOID as varchar),convert(varchar,InvoiceDate,121),cast(CreationUserOID as varchar)
from invoice I inner join udinvoice UDI on I.invoiceoid=UDI.invoiceoid
where exported is null
order by 1 desc
if @@error !=0
begin
rollback tran doinsert
RAISERROR ('An error occured on fulecard import',10,1)
end
else
begin
commit tran doinsert
set @exec ='exec master.dbo.xp_cmdshell '
set @exec=@exec+ char(39)+'SQLCMD -S servername -E -Q " set nocount on select * from NVD..tmp_invoice" -o"'+@path+@filename+'" -h-1 -s"," -W ' +char(39)
exec (@exec)
end
Begin try
begin tran doupdate
update UDI set exported=getdate()
from UDinvoice UDI join tmp_invoice INV on inv.invoiceoid=UDI.invoiceoid
where isnumeric(inv.invoiceoid)=1
commit tran doupdate
End try
Begin catch
if (xact_state()) = -1
begin
print'the transaction is in an uncommittable state.' +'rolling back transaction.'
rollback tran doupdate;
end;
if (xact_state()) = 1
begin
commit tran doupdate ;
end;
end catch;
end
***The first step is always the hardest *******
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply