Check if files exist Stored Proc

Hi,
This stored proc code uses DOS Copy command and xp_cmdshell stored
procedure to copy files form one location to another. See below the
code. It is working fine!. What I need is to add a check if the files in
the Source location exist or not. If not, then send an email (using
xp_sendmail) to us saying "files do not exist". If yes, then start
copying the files. How do I do that?
Thanks for your help.
declare @.source varchar(150)
declare @.destination varchar(150)
declare @.DOScmd varchar(300)
select @.source =
case @.@.servername
when 'A' then '\\server1\folder1\a*.*'
when 'B' then '\\server2\folder2\b*.*'
when 'C' then '\\server3\folder3\c*.*'
end,
@.destination =
case @.@.servername
when 'A' then '\\serverx\folderx'
when 'B' then '\\serverx\folderx'
when 'C' then '\\serverx\folderx'
end
-- copy only if the files in the source folder exist, otherwise send an
email for "files do not exixts".
set @.DOScmd = 'Copy ' + '"'+ rtrim(@.source) +'"'+ ' ' +'"'+
rtrim(@.destination)+'"'
exec master.dbo.xp_cmdshell @.DOScmd
GO
*** Sent via Developersdex http://www.examnotes.net ***Look up xp_fileexist in Books Online.
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:%23CYCQDguFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Hi,
> This stored proc code uses DOS Copy command and xp_cmdshell stored
> procedure to copy files form one location to another. See below the
> code. It is working fine!. What I need is to add a check if the files in
> the Source location exist or not. If not, then send an email (using
> xp_sendmail) to us saying "files do not exist". If yes, then start
> copying the files. How do I do that?
> Thanks for your help.
> declare @.source varchar(150)
> declare @.destination varchar(150)
> declare @.DOScmd varchar(300)
> select @.source =
> case @.@.servername
> when 'A' then '\\server1\folder1\a*.*'
> when 'B' then '\\server2\folder2\b*.*'
> when 'C' then '\\server3\folder3\c*.*'
> end,
> @.destination =
> case @.@.servername
> when 'A' then '\\serverx\folderx'
> when 'B' then '\\serverx\folderx'
> when 'C' then '\\serverx\folderx'
> end
> -- copy only if the files in the source folder exist, otherwise send an
> email for "files do not exixts".
> set @.DOScmd = 'Copy ' + '"'+ rtrim(@.source) +'"'+ ' ' +'"'+
> rtrim(@.destination)+'"'
> exec master.dbo.xp_cmdshell @.DOScmd
> GO
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Whoops, never mind! I forgot this proc is not documented / supported.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uwg8xEguFHA.2568@.TK2MSFTNGP15.phx.gbl...
> Look up xp_fileexist in Books Online.|||A particular file can be checked with:
EXEC master..xp_fileexist 'c:\boot.ini'
HTH, Jens Suessmeyer.|||If this were implemented as a DTS package, you would perhaps find the file
system object more suitable for copying files, etc.
http://msdn.microsoft.com/library/d...ystemObject.asp
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:%23CYCQDguFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Hi,
> This stored proc code uses DOS Copy command and xp_cmdshell stored
> procedure to copy files form one location to another. See below the
> code. It is working fine!. What I need is to add a check if the files in
> the Source location exist or not. If not, then send an email (using
> xp_sendmail) to us saying "files do not exist". If yes, then start
> copying the files. How do I do that?
> Thanks for your help.
> declare @.source varchar(150)
> declare @.destination varchar(150)
> declare @.DOScmd varchar(300)
> select @.source =
> case @.@.servername
> when 'A' then '\\server1\folder1\a*.*'
> when 'B' then '\\server2\folder2\b*.*'
> when 'C' then '\\server3\folder3\c*.*'
> end,
> @.destination =
> case @.@.servername
> when 'A' then '\\serverx\folderx'
> when 'B' then '\\serverx\folderx'
> when 'C' then '\\serverx\folderx'
> end
> -- copy only if the files in the source folder exist, otherwise send an
> email for "files do not exixts".
> set @.DOScmd = 'Copy ' + '"'+ rtrim(@.source) +'"'+ ' ' +'"'+
> rtrim(@.destination)+'"'
> exec master.dbo.xp_cmdshell @.DOScmd
> GO
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks for the respone. The "xp_fileexist" works great if you know the
exact file name. In my case, I dont know the file name. All I need is to
capture the files start with "a" i.e. a*.*
So, EXEC master..xp_fileexist '\\server1\folder1\a*.*' does not work.
Any other ideas?
*** Sent via Developersdex http://www.examnotes.net ***|||CREATE TABLE #files
(
filename SYSNAME NULL
)
SET NOCOUNT ON
INSERT #files EXEC master..xp_cmdshell 'dir \\server1\folder1\a*.* /b'
SELECT COUNT(*) FROM #files WHERE filename IS NOT NULL
SELECT * FROM #files WHERE filename IS NOT NULL
DROP TABLE #files
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:uvoSyaguFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Thanks for the respone. The "xp_fileexist" works great if you know the
> exact file name. In my case, I dont know the file name. All I need is to
> capture the files start with "a" i.e. a*.*
> So, EXEC master..xp_fileexist '\\server1\folder1\a*.*' does not work.
> Any other ideas?
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||If you're already planning on using xp_cmdshell, then try this:
declare @.cmd varchar(1024)
declare @.path varchar(1024)
create table #fs
(
fId int identity (1, 1) primary key
,fName varchar(1024)
)
set @.path = 'c:\Inetpub*.*'
set @.cmd = 'dir /b ' + @.path
insert #fs
(
fName
)
exec master.dbo.xp_cmdshell @.cmd
if (exists (
select fs.fId
from #fs fs
where (fs.fName is not null)
))
begin
print 'exists'
end
else
begin
print 'does not exist'
end
ML|||Aaron! Thanx a lot!!!. It works like a champ!!!
*** Sent via Developersdex http://www.examnotes.net ***

check if cast is possible

I use to import data from DBF Clipper databases into SQL Server. When a table is just imported its date fields have string format. I need to copy their data to tables of database where they ahve to be converted into date. Direct operator INSERT doesn't convert properly (I've not successed in changing default date format so it'll be covertable) but using CAST I can get result of strings like 13.05.1970 0:00:00 as datetime type. But not all records can be coverted this way. For ones can't be converted I've solved to make NULL fields there. But I don't know how to make CAST operation return NULL when convertion isn't possible. The query
INSERT INTO people_temp
(reg_num, surname, stname, patronymic, foreing, gender, birthdate, fam_pos, dwell_type, children, nation, par_not, region, stud_fml, parn_fml,
com_prob, sp_prob, sn_passport, nn_passport, dv_passport, wg_passport)
SELECT STUDENTs_temp.REG_NOM, STUDENTs_temp.FAMILY, STUDENTs_temp.NAME, STUDENTs_temp.PARN_NAME, STUDENTs_temp.INOSTR,

STUDENTs_temp.SEX,
CAST(PSPR_temp.DATA_BORN AS smalldatetime), PSPR_temp.SEM_POL, PSPR_temp.XAR_JT, PSPR_temp.CHILDREN,

PSPR_temp.NATION,
PSPR_temp.SV_ROD1 + PSPR_temp.SV_ROD2 AS Expr1, PSPR_temp.REGION, PSPR_temp.STUD_FML,

PSPR_temp.PARN_FML,
PSPR_temp.OB_STAJ, PSPR_temp.SP_STAJ, PSPR_temp.SN_PASPORT, PSPR_temp.NN_PASPORT, PSPR_temp.DV_PASPORT,
PSPR_temp.WG_PASPORT
FROM STUDENTs_temp INNER JOIN
PSPR_temp ON STUDENTs_temp.REG_NOM = PSPR_temp.REG_NOM
gets an error 'The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value'. Tell me please how can make type casting return NULL if convertion isn't possible.I might try something like so:

Insert Into MyTable(MyDateField)
SELECT (CASE WHEN isDate(DateFieldTobeImported) = 1 Then DateFieldTobeImported ELSE Null End)
From TableBeingImported

If this does not work, please post some sample data.|||Thank you, it works.

check file date and copy file

Hi,

I need to set up create a package so that I could check the date of the files posted in a folder, e.g. H:\source. If there is no file created later than one day exists, then continue to check again one hour later. If files do exists, then copy then to c:\dest and then upzip the files. Once this is done, sent an notification email to user@.mydomain.com.

Thanks,

Check out the FileWatcher task on SQLIS.com. It should help with identifying when the file appears. The rest of the tasks mentioned here are included with SSIS. You can use the File System task to copy files, and the Execute Process task to run a commandline utility to unzip them. The Send Mail task is used to send emails.|||

Hi,

I installed the program in the sqlis.com, but when I open the ssis business intelligent console, I can't find the filewatcher task in the toolbox. Can you tell me how to add this task in?

Thanks,

|||

There are instructions on SQLIS.com.

"The component is provided as an MSI file, however to complete the installation, you will have to add the task to the Visual Studio toolbox manually. Right-click the toolbox, and select Choose Items.... Select the SSIS Control Flow Items tab, and then check the File Watcher Task from the list."

Cheapest way to purchase SQL Reporting Services

Within my department we have 1 copy of of vb studio.net standard and 2 copies
of vb studio.net enterprise and run sql server 2000.
One of the guys is writing a system which I have to report on using SQL
Reporting Services. As I don't have any .net product, do I have to purchase a
.net product to then get a development edition of SQL Reporting Services to
write any reports etc.
Looking for the cheapest way to be able to use SQL Reporting Services as I
work in the NHS (lucky me).
Any help will be much appreciated.
Thanks, MarkYour SQL license gives you RS. All you need to develop report is a copy of
VS. The cheapest way to aquire it is VB.net standard.
=?Utf-8?B?bWFya19tZW56aWVz?= <mark_menzies@.discussions.microsoft.com>
wrote in news:DC6ABF54-CFC9-417F-A898-1DB7FCB21549@.microsoft.com:
> Within my department we have 1 copy of of vb studio.net standard and 2
> copies of vb studio.net enterprise and run sql server 2000.
> One of the guys is writing a system which I have to report on using
> SQL Reporting Services. As I don't have any .net product, do I have to
> purchase a .net product to then get a development edition of SQL
> Reporting Services to write any reports etc.
> Looking for the cheapest way to be able to use SQL Reporting Services
> as I work in the NHS (lucky me).
> Any help will be much appreciated.
> Thanks, Mark
>|||Also, RS 2005 comes with a version of VS so no extra purchase is required.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Asher_N" <compguy666@.hotmail.com> wrote in message
news:Xns970F5808B7AFDcompguy666hotmailcom@.207.46.248.16...
> Your SQL license gives you RS. All you need to develop report is a copy of
> VS. The cheapest way to aquire it is VB.net standard.
> =?Utf-8?B?bWFya19tZW56aWVz?= <mark_menzies@.discussions.microsoft.com>
> wrote in news:DC6ABF54-CFC9-417F-A898-1DB7FCB21549@.microsoft.com:
>> Within my department we have 1 copy of of vb studio.net standard and 2
>> copies of vb studio.net enterprise and run sql server 2000.
>> One of the guys is writing a system which I have to report on using
>> SQL Reporting Services. As I don't have any .net product, do I have to
>> purchase a .net product to then get a development edition of SQL
>> Reporting Services to write any reports etc.
>> Looking for the cheapest way to be able to use SQL Reporting Services
>> as I work in the NHS (lucky me).
>> Any help will be much appreciated.
>> Thanks, Mark
>>
>

Chart display fails when XCopy

When I copy the rdl file to the production server, the report runs but fails
to display the chart (displays the little icon). Any ideas? Thanks.
DanHi Dan,
Thanks for your post.
From your descriptions, I understood you could see Chart well on VS.NET but
failed to see it after the deployment. If I have misunderstood your
concern, please feel free to point it out.
Based on my knowledge, This can be caused by the boundaries of the Chart
control. Make sure it's outer edges don't go outside the page and Move the
chart boundaries around and re-deploying the report until it looks right.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Character limit on Data tab?

Does anyone know if there is a character limit on the data tab of SRS 2000? I have a report that requires a lottt of sql and when I copy and paste the sql from Query Analyzer to the data tab, not all of it gets copied there, and it will not let me enter another character.

Thanks,

Jeff

It may make more sense to encapsulate in a stored procedure if possible. It does sound like you have a limitation there.|||Yah...good thought...thanks.