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 DB Constraints exist on a table

If we alter the table by disabling the constraints (use NOCHECK CONSTRAINT
ALL). Is there any command to verify that the constraints are disabled or
don't exist.Check out sp_helpconstraint in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"SQL_Learner" <SQL_Learner@.discussions.microsoft.com> wrote in message
news:E615AA31-2B87-4B85-BC6B-6CC01BF0D8C8@.microsoft.com...
If we alter the table by disabling the constraints (use NOCHECK CONSTRAINT
ALL). Is there any command to verify that the constraints are disabled or
don't exist.|||Thanks Tom, this command is what I am looking for.
In SQL server, without checking if we enable all constraints (irrespective
of they are enabled or disabled at that point) are the any issues? What are
your thoughts on this.
Thanks in Advance
"Tom Moreau" wrote:

> Check out sp_helpconstraint in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "SQL_Learner" <SQL_Learner@.discussions.microsoft.com> wrote in message
> news:E615AA31-2B87-4B85-BC6B-6CC01BF0D8C8@.microsoft.com...
> If we alter the table by disabling the constraints (use NOCHECK CONSTRAINT
> ALL). Is there any command to verify that the constraints are disabled or
> don't exist.
>|||That depends. If you attempt to enable the constraints WITH CHECK, and
there are existing violations of those constraints, then re-enabling will
fail. However, if you re-enable WITH NOCHECK, then it will succeed.
That said, if you have a partitioned view, then you'd want to use WITH
CHECK, so as to take advantage of the performance benefits that having such
constraints will give you.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"SQL_Learner" <SQLLearner@.discussions.microsoft.com> wrote in message
news:62ABA723-4797-4C44-AE67-D0D9F853F6D6@.microsoft.com...
Thanks Tom, this command is what I am looking for.
In SQL server, without checking if we enable all constraints (irrespective
of they are enabled or disabled at that point) are the any issues? What are
your thoughts on this.
Thanks in Advance
"Tom Moreau" wrote:

> Check out sp_helpconstraint in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "SQL_Learner" <SQL_Learner@.discussions.microsoft.com> wrote in message
> news:E615AA31-2B87-4B85-BC6B-6CC01BF0D8C8@.microsoft.com...
> If we alter the table by disabling the constraints (use NOCHECK CONSTRAINT
> ALL). Is there any command to verify that the constraints are disabled or
> don't exist.
>|||I am enabling constraint in the following way:
STMT1: ALTER TABLE Customers CHECK CONSTRAINT ALL
Disabling as:
STMT2: ALTER TABLE Customers NOCHECK CONSTRAINT ALL
In disabled or enabled state, if I am running STMT1 again and again in the
Query Analyser, I am not getting any error. Does that mean in disabled state
the SQL server will enable the constraints and in enabled state it will
ignore to reenable the constraints?
What is the performance issue in this case where I don't have a partitioned
view.
Thanks!|||Yes, it will ignore things if they're already enabled.
In some cases, you could get a performance hit if you're doing a query like:
select
*
from
MyTable m
where exists
(
select
*
from
OtherTable o
where
o.FK = m.PK
)
... and you've disabled the foreign key from OtherTable to MyTable or
re-enabled it with NOCHECK. The optimizer can take advantage of the fact
that it knows something about the data in OtherTable, due to the constraint.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"SQL_Learner" <SQLLearner@.discussions.microsoft.com> wrote in message
news:C227BE86-B58A-4739-B435-F737427C900B@.microsoft.com...
I am enabling constraint in the following way:
STMT1: ALTER TABLE Customers CHECK CONSTRAINT ALL
Disabling as:
STMT2: ALTER TABLE Customers NOCHECK CONSTRAINT ALL
In disabled or enabled state, if I am running STMT1 again and again in the
Query Analyser, I am not getting any error. Does that mean in disabled state
the SQL server will enable the constraints and in enabled state it will
ignore to reenable the constraints?
What is the performance issue in this case where I don't have a partitioned
view.
Thanks!|||Thanks Tom for your post!

Character set, Sort Order, Unicode Collation

Please, a command/script to see how I installed my Sql
Server (Character set, Sort Order, Unicode Collation), I
was looking for them at Knowledge Base, but couldn't find
anything.
ThanksThis should do it:
select serverproperty('collation')
Regards,
Paul Ibison|||Hi,
Add on , Execute the below procedure to get the charecter set and sort
order. This procedure will work for all versions.
sp_helpsort
--
Thanks
Hari
MCDBA
"Robert Duval" <r.duval@.discussions.microsoft.com> wrote in message
news:1ddf001c45512$bfedc6e0$a601280a@.phx.gbl...
> Please, a command/script to see how I installed my Sql
> Server (Character set, Sort Order, Unicode Collation), I
> was looking for them at Knowledge Base, but couldn't find
> anything.
> Thanks

Character set, Sort Order, Unicode Collation

Please, a command/script to see how I installed my Sql
Server (Character set, Sort Order, Unicode Collation), I
was looking for them at Knowledge Base, but couldn't find
anything.
ThanksHi,
Add on , Execute the below procedure to get the charecter set and sort
order. This procedure will work for all versions.
sp_helpsort
Thanks
Hari
MCDBA
"Robert Duval" <r.duval@.discussions.microsoft.com> wrote in message
news:1ddf001c45512$bfedc6e0$a601280a@.phx
.gbl...
> Please, a command/script to see how I installed my Sql
> Server (Character set, Sort Order, Unicode Collation), I
> was looking for them at Knowledge Base, but couldn't find
> anything.
> Thanks

Character set, Sort Order, Unicode Collation

Please, a command/script to see how I installed my Sql
Server (Character set, Sort Order, Unicode Collation), I
was looking for them at Knowledge Base, but couldn't find
anything.
Thanks
Hi,
Add on , Execute the below procedure to get the charecter set and sort
order. This procedure will work for all versions.
sp_helpsort
Thanks
Hari
MCDBA
"Robert Duval" <r.duval@.discussions.microsoft.com> wrote in message
news:1ddf001c45512$bfedc6e0$a601280a@.phx.gbl...
> Please, a command/script to see how I installed my Sql
> Server (Character set, Sort Order, Unicode Collation), I
> was looking for them at Knowledge Base, but couldn't find
> anything.
> Thanks

changing Top lower margin

I been looking for this for quite a while but have not been able to find anything.

Is there a command like MySQL Limit command, or a way to mimic that behavior in SQL?

For those that don’t know the command it is similar to Top but allows you to say what is the first record you want to receive and how many records from there. For instance given the following example recordset:

A
B
C
D
E
F

The following query would return (note Limit low margin is zero based):

Select * From [Table] Limit 2, 3

C
D
E

So far I have figured that I may be able to create a temporary table with an identity field that would allow me to do a where against, but I’m not sure if that would be the most effective way to mimic this behavior, and again, there may be a built in way to do it that I’m not aware off.
AFAIK, there is no built in command in SQL for something like that. You'd have to write a query with a TOP 3 from the table where ID > ( get the TOP 2nd ID) order by the primary key or whatever column you want the results ordered by.|||

There is no built-in way. Limit is a proprietary syntax like TOP or SET ROWCOUNT. You can do something like below using ANSI SQL ranking function (will work in SQL Server 2005, Oracle, DB2):

Code Snippet

select *

from (

select *, ROW_NUMBER() OVER(ORDER BY some_column) as limit

from your_table

) as t

where t.limit between @.start and @.end -- t.limit between 3 and 5 in your case