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 constraint - SQL problem

Hi,

I am new to database development and am writing a database as part of a
university course

I have created a table as below called CableWire - the table is created ok.

CREATE TABLE CableWire
(CableWireID CHAR(7),
BSstandard CHAR(16),
Colour VARCHAR(16),
Material VARCHAR(16),
MetresInStock INTEGER,
PRIMARY KEY (CableWireID));

However when I try to alter the table by adding a CHECK constraint:

ALTER TABLE CableWire
ADD CHECK (MetresInStock >= 0);

I get a pop-up box: "Line: 21
SQLSTATE = 37000
[Microsoft][ODBC dBase Driver] Syntax error in field definition, Continue?"

(line 21 equated to the 2nd of those 2 lines). The syntax seems perfectly
acceptable to me. Any help appreciated.

Regards,

MaryHello, Mary

The syntax is perfectly acceptable in Microsoft SQL Server 2000, but
are you using SQL Server or dBase ? The error message indicates that
the ODBC dBase driver is involved. If you want this to run on dBase,
perhaps you should try your question on another newsgroup.

Razvan|||Some minor commetns about the design and some questions.

1) Why is every non-key column NULL-able?

2) I don't know the wirte business, so who defines the cablewire_id
codes? I know the ISO stuff for machine screws, etc.

3) Likewise, what is the BS Standard. My first guess was British
Standards, since you spelled color wrong :)

4) Don' t you use Pantone or Land color numbers? Can you give me an
example of CHAR(16) color name? I assume that it is a name, not a
code, but since you did not follow ISO-11179 rules, I don't know.

CREATE TABLE CableWire
(cablewire_id CHAR(7) NOT NULL PRIMARY KEY,
bs_standard CHAR(16 NOT NULL),
colour_name VARCHAR(16) NOT NULL,
material_type VARCHAR(16) NOT NULL,
stock_level INTEGER DEFAULT 0 NOT NULL
CHECK (stock_level >= 0));

Otherwise, your syntax was fine.|||Hi Mary,
I have just created table and added check constraint using alter
statement without any error on SQL SERVER 2000. Could you send more
detail about environment where you encountered this error.
Ash
http://www.astragalaxy.com

Chart lists different builds of SQL Server and MDAC needed

I want to find out which build of MDAC is required by different build of SQL
Server 2000 and 2005.
Also, are all the keywords listed in the link below supported by all the
MDAC used for SQL Server 2000 and 2005? Are the acceptable values for the
keywords same for both 32bit and 64bit driver? For example, is the name of
the network library for tcp and named pipes same for both 32bit and 64bit?
http://msdn2.microsoft.com/en-us/library/aa177865(SQL.80).aspx
Thanks.Hi,
I've done some research but I couldn't find any document that ties the
keywords to MDAC specifically. I'm using the internal MS databases so
it's probably quite safe to say that no such document(s) exist(s). I
believe that the keywords are listed per SQL Server version rather than
per MDAC version.
What I would suggest you do is to test your applications against the
different MDAC versions. Alternatively, instead of testing all your
applications, you could also build a dummy application that does nothing
more than use the different keywords/functions specifically for testing
against the multiple MDAC versions efficiently and quickly.
The following link gives you a SQL Server 2005 version of the document
you included in your posting -
http://msdn2.microsoft.com/en-us/library/ms130822.aspx.
As far as any difference in acceptable keyword values between 32 and 64
bit platforms are concerned, I do not believe there would be a
difference. From my perspective portability between platforms should be
a high priority for any developer so I don't see why MS would introduce
such a big headache for itself and its customers. Again testing is your
safest option here.
Regarding the question of which MDAC build is required by different
builds of SQL Server I cannot find any MDAC updates being released as
part of a SQL Server build. To me this means that builds are not
dependent on any specific MDAC version otherwise MS would have no option
but to include the MDAC update as part of the SQL Server update. So I
would say that MDAC requirements are placed at a version level rather
than a build level unless any KB article specifically indicates
otherwise. My research uncovered no such KB articles.
Jonathan
Peter wrote:
> I want to find out which build of MDAC is required by different build of S
QL
> Server 2000 and 2005.
> Also, are all the keywords listed in the link below supported by all the
> MDAC used for SQL Server 2000 and 2005? Are the acceptable values for the
> keywords same for both 32bit and 64bit driver? For example, is the name of
> the network library for tcp and named pipes same for both 32bit and 64bit?
> http://msdn2.microsoft.com/en-us/library/aa177865(SQL.80).aspx
>
> Thanks.
>
>

CHARINDEX is not working

i am using the below code but its not working .. what could be the reason?
CHARINDEX(@.SPName,@.DateAndIntCols)
to CHARINDEX function i am passing to variables, cant i pass variables to
CHARINDEX?Please elaborate on "not working" . Are there errors? Don't you get expected
results? Don't you get any results? What?
ML
http://milambda.blogspot.com/|||On Thu, 1 Dec 2005 15:33:02 -0800, KL wrote:

>i am using the below code but its not working .. what could be the reason?
>CHARINDEX(@.SPName,@.DateAndIntCols)
>to CHARINDEX function i am passing to variables, cant i pass variables to
>CHARINDEX?
Hi KL,
The following code returns 4 on my computer, which is as expected. What
are the values of @.SPName and @.DateAndIntCols in your case, and what was
the result you got?
DECLARE @.SPName varchar(10), @.DateAndIntCols varchar(100)
SET @.SPName = 'xyz'
SET @.DateAndIntCols = 'uvwxyzabcdef'
SELECT CHARINDEX(@.SPName,@.DateAndIntCols)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I am passing the below values to the variables but its still returning 0...
SET @.SPName ='eUpdated'
SET @.DateAndIntCols = 'eUpdated,eDeleted'
but its working (returning 1) when use the following one...
SET @.DateAndIntCols = 'eUpdated,eDeleted'
CHARINDEX('eUpdated',@.DateAndIntCols)
"ML" wrote:

> Please elaborate on "not working" . Are there errors? Don't you get expect
ed
> results? Don't you get any results? What?
>
> ML
> --
> http://milambda.blogspot.com/|||On Thu, 1 Dec 2005 16:03:03 -0800, KL wrote:

>I am passing the below values to the variables but its still returning 0...
>SET @.SPName ='eUpdated'
>SET @.DateAndIntCols = 'eUpdated,eDeleted'
>but its working (returning 1) when use the following one...
>SET @.DateAndIntCols = 'eUpdated,eDeleted'
>CHARINDEX('eUpdated',@.DateAndIntCols)
Hi KL,
Works for me:
DECLARE @.SPName varchar(10), @.DateAndIntCols varchar(100)
SET @.SPName ='eUpdated'
SET @.DateAndIntCols = 'eUpdated,eDeleted'
SELECT CHARINDEX(@.SPName,@.DateAndIntCols)
Can you please post your complete code?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Here is the code i am using ....
DECLARE
@.SPName varchar(128) ,
@.DateAndIntCols varchar(1000)
SELECT @.SPName='eUpdated'
SELECT @.DateAndIntCols=DateAndIntCols FROM MetaData WHERE SPID='CC-3'
SELECT CHARINDEX(@.SPName,@.DateAndIntCols)
And when i printed the variable @.DateAndIntCols its showing as
'eUpdated,eDeleted'....
"Hugo Kornelis" wrote:

> On Thu, 1 Dec 2005 16:03:03 -0800, KL wrote:
>
> Hi KL,
> Works for me:
> DECLARE @.SPName varchar(10), @.DateAndIntCols varchar(100)
> SET @.SPName ='eUpdated'
> SET @.DateAndIntCols = 'eUpdated,eDeleted'
> SELECT CHARINDEX(@.SPName,@.DateAndIntCols)
>
> Can you please post your complete code?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||Here is the complete code i am using ....
DECLARE
@.SPName varchar(128) ,
@.DateAndIntCols varchar(1000)
SELECT @.SPName='eUpdated'
SELECT @.DateAndIntCols=DateAndIntCols FROM MetaData WHERE SPID='CC-3'
SELECT CHARINDEX(@.SPName,@.DateAndIntCols)
And when i printed the variable @.DateAndIntCols its showing as
'eUpdated,eDeleted'....
"Hugo Kornelis" wrote:

> On Thu, 1 Dec 2005 16:03:03 -0800, KL wrote:
>
> Hi KL,
> Works for me:
> DECLARE @.SPName varchar(10), @.DateAndIntCols varchar(100)
> SET @.SPName ='eUpdated'
> SET @.DateAndIntCols = 'eUpdated,eDeleted'
> SELECT CHARINDEX(@.SPName,@.DateAndIntCols)
>
> Can you please post your complete code?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||On Thu, 1 Dec 2005 16:26:02 -0800, KL wrote:

>Here is the complete code i am using ....
>DECLARE
>@.SPName varchar(128) ,
>@.DateAndIntCols varchar(1000)
>SELECT @.SPName='eUpdated'
>SELECT @.DateAndIntCols=DateAndIntCols FROM MetaData WHERE SPID='CC-3'
>SELECT CHARINDEX(@.SPName,@.DateAndIntCols)
>And when i printed the variable @.DateAndIntCols its showing as
>'eUpdated,eDeleted'....
Hi KL,
Since I don;t have your table, I had to add some lines to the script.
Here's what I executed:
CREATE TABLE MetaData
(SPID char(4) NOT NULL PRIMARY KEY,
DateAndIntCols varchar(1000) NOT NULL)
INSERT INTO MetaData (SPID, DateAndIntCols)
VALUES ('CC-3', 'eUpdated,eDeleted')
go
DECLARE
@.SPName varchar(128) ,
@.DateAndIntCols varchar(1000)
SELECT @.SPName='eUpdated'
SELECT @.DateAndIntCols=DateAndIntCols FROM MetaData WHERE SPID='CC-3'
SELECT CHARINDEX(@.SPName,@.DateAndIntCols)
go
DROP TABLE MetaData
go
And here's the output:
1
This is the expected output. What output are you getting? Also, what is
the output if you execute
SELECT @.@.VERSION
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)