Create vCard file from SQL Server -
i want create vcard sql server (v 2012) , after want send via email db_mail. create vcard file using xp_cmdshell. prepare cmd commands in 1 varchar variable , execute xp_cmdshell using variable command text. result not expect. receive first line of text in generated vcard file. when take same text , run batch file vcard file created properly.
declare @iiduser int = 1; declare @iidcontact int = 1; declare @iresult int; declare @sexecute_vcardtext varchar(8000) = ''; declare @crlf char(2) = char(13) + char(10); declare @sfile varchar(1000) = ' >> c:\databases\_tmp_sql\pes_erp\user.vcf'; select @sexecute_vcardtext = @sexecute_vcardtext + @crlf + 'echo ' + 'begin:vcard' + ' > c:\databases\_tmp_sql\pes_erp\user.vcf' + @crlf + 'echo ' + 'version:2.1' + @sfile + @crlf + 'echo ' + 'categories:' + + isnull(cnt.contactrole_name_en,'') + ',' + isnull(cnt.contactrelationshiptype_name_en,'') + ',' + isnull(cnt.visibilitytype_name_en,'') + @sfile + @crlf + 'echo ' + 'fn:' + isnull(cnt.contacttitle_name_en,'') + @sfile + ' ' + isnull(cnt.name,'') + ' ' + isnull(cnt.surname,'') + @crlf + 'echo ' + 'n:' + isnull(cnt.surname,'') + @sfile + ';' + isnull(cnt.name,'') + ';' + isnull(cnt.contacttitle_name_en,'') + @sfile + @crlf + 'echo ' + 'title:' + isnull(cnt.jobtitle,'') + @sfile + @crlf + 'echo ' + 'bday:' + isnull(convert(varchar(16),cnt.birthdate,112),'') + @sfile + @crlf + 'echo ' + 'birth:' + isnull(cnt.birthmunicipality_name,'') + @sfile + @crlf + 'echo ' + 'tel;type=work:' + isnull(cnt.business_phone,'') + @sfile + @crlf + 'echo ' + 'tel;type=work:' + isnull(cnt.business_phone2,'') + @sfile + @crlf + 'echo ' + 'tel;type=work,cell:' + isnull(cnt.business_mobile,'') + @sfile + @crlf + 'echo ' + 'tel;type=work,cell:' + isnull(cnt.business_mobile2,'') + @sfile + @crlf + 'echo ' + 'tel;type=work,fax:' + isnull(cnt.business_fax,'') + @sfile + @crlf + 'echo ' + 'adr;type=work:;;' + isnull(cnt.business_address,'') + ';' + isnull(cnt.business_city,'') + ';' + isnull(cnt.business_municipality_name,'') + ' (' + isnull(cnt.business_region_name,'') + ') ' + ';' + isnull(cnt.business_cap,'') + ';' + isnull(case when (@iidlanguage = 40001) cnt.business_country_name_en when (@iidlanguage = 40002) cnt.business_country_name_it when (@iidlanguage = 40003) cnt.business_country_name_de when (@iidlanguage = 40004) cnt.business_country_name_fr else cnt.business_country_name_en end,'') + @sfile + @crlf + 'echo ' + 'email;type=internet,work,pref:' + isnull(cnt.business_email,'') + @sfile + @crlf + 'echo ' + 'email;type=internet,work:' + isnull(cnt.business_email2,'') + @sfile + @crlf + 'echo ' + 'tel;type=home:' + isnull(cnt.home_phone,'') + @sfile + @crlf + 'echo ' + 'tel;type=home,cell:' + isnull(cnt.home_mobile,'') + @sfile + @crlf + 'echo ' + 'adr;type=work:;;' + isnull(cnt.home_address,'') + ';' + isnull(cnt.home_city,'') + ';' + isnull(cnt.home_municipality_name,'') + ';' + isnull(cnt.home_cap,'') + ';;' + @sfile + @crlf + 'echo ' + 'email;type=internet,home:' + isnull(cnt.home_email,'') + @sfile + @crlf + 'echo ' + 'note:' + isnull(cnt.note,'') + @sfile + @crlf + 'echo ' + 'rev:' + replace(replace(replace(convert(varchar,getdate(),120),'-',''),':',''),' ','t') + 'z' + @sfile + @crlf + 'echo ' + 'end:vcard' + @sfile dbo.fun_contacts(@iiduser) cnt (cnt.idcontact = @iidcontact); exec @iresult = master..xp_cmdshell @sexecute_vcardtext; select @iresult a; can give me clue wrong?
why echo commands?
it makes code more complicated.
here re-write creating formatted string. please check syntax.
-- declare variables declare @crlf char(2) = char(13) + char(10); declare @sqlstmt varchar(max) = ''; -- make string select @sqlstmt = 'begin:vcard' + @crlf + 'version:2.1' + @crlf + 'categories:' + isnull(c.contactrole_name_en,'') + ',' + isnull(c.contactrelationshiptype_name_en,'') + ',' + isnull(c.visibilitytype_name_en,'') + @crlf + 'fn:' + isnull(c.contacttitle_name_en,'') + ' ' + isnull(c.name,'') + ' ' + isnull(c.surname,'') + @crlf + 'n:' + isnull(c.surname,'') + ';' + isnull(c.name,'') + ';' + isnull(c.contacttitle_name_en,'') + @crlf + 'title:' + isnull(c.jobtitle,'') + @crlf + 'bday:' + isnull(convert(varchar(16),c.birthdate,112),'') + @crlf + 'birth:' + isnull(c.birthmunicipality_name,'') + @crlf + 'tel;type=work:' + isnull(c.business_phone,'') + @crlf + 'tel;type=work:' + isnull(c.business_phone2,'') + @crlf + 'tel;type=work,cell:' + isnull(c.business_mobile,'') + @crlf + 'tel;type=work,cell:' + isnull(c.business_mobile2,'') + @crlf + 'tel;type=work,fax:' + isnull(c.business_fax,'') + @crlf + 'adr;type=work:;;' + isnull(c.business_address,'') + ';' + isnull(c.business_city,'') + ';' + isnull(c.business_municipality_name,'') + ' (' + isnull(c.business_region_name,'') + ') ' + ';' + isnull(c.business_cap,'') + ';' + isnull ( case when (@iidlanguage = 40001) c.business_country_name_en when (@iidlanguage = 40002) c.business_country_name_it when (@iidlanguage = 40003) c.business_country_name_de when (@iidlanguage = 40004) c.business_country_name_fr else c.business_country_name_en end,'') ) + @crlf + 'email;type=internet,work,pref:' + isnull(c.business_email,'') + @crlf + 'email;type=internet,work:' + isnull(c.business_email2,'') + @crlf + 'tel;type=home:' + isnull(c.home_phone,'') + @crlf + 'tel;type=home,cell:' + isnull(c.home_mobile,'') + @crlf + 'adr;type=work:;;' + isnull(c.home_address,'') + ';' + isnull(c.home_city,'') + ';' + isnull(c.home_municipality_name,'') + ';' + isnull(c.home_cap,'') + ';;' + @crlf + 'email;type=internet,home:' + isnull(c.home_email,'') + @crlf + 'note:' + isnull(c.note,'') + @crlf + 'rev:' + replace(replace(replace(convert(varchar,getdate(),120),'-',''),':',''),' ','t') + 'z' + @crlf + 'end:vcard' + @crlf dbo.fun_contacts(@iiduser) c (c.idcontact = @iidcontact); i purposely did not string yet since want cover design decisions.
1 - command prompt has limit of under 8k (8192 characters).
this issue. echo's , destinations exceeding limit.
works batch file not sql server.
2 - if think going exceed limit, there couple of solutions.
a - break commands separate calls. use guid or such guarantee file uniqueness. if need file output.
b - if emailing file, why not use @attach_query_result_as_file option? option turns output query attachment.
http://technet.microsoft.com/en-us/library/ms190307.aspx
like in life, fix on issue , create another. think default file attachment size 1 mb. have increase value if file attachment exceeds limit.
c - this can done in ssis instead of straight t-sql. both work. ssis more flexible when solving complex problems.
good luck task.
Comments
Post a Comment