RSS Text Data Mining: zip

Showing posts with label zip. Show all posts

Wednesday, March 28, 2012

Running a program from a SQL job

Hi
I'm having a strange problem which puzzels me a little bit.
I have created a SQL job that can zip a file that has been generated from a
provious job. I've made a bat file that contains the string to run to zip
the file. When I run this bat file manually just by doubleclicking it, it
runs fine and do the job. When I set the SQL job to execute the same bat
file, it executes the bat file, but the bat file doesn't zip the file. It
reports that everything is ok, but the file isn't being created.
It seems like the zip program I've found that can be executed from a command
line (it's a program called 7-zip) will only do the job if it can "open" a
command prompt in the background which SQL apparently doesn't do.
My question is now if any of you know a way to let a SQL job "open" a
command prompt and then execute a program from there. It could also be as
simple as using a different zip program. 7-zip was just one I found that
could zip a file from a command line.
Regards
SteenHi,
Use XP_CMDSHELL to fire a OS command from command prompt.
Before commentng more, can you send out the script you are trying out.
--
Thanks
Hari
MCDBA
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uKWljChUEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Hi
> I'm having a strange problem which puzzels me a little bit.
> I have created a SQL job that can zip a file that has been generated from
a
> provious job. I've made a bat file that contains the string to run to zip
> the file. When I run this bat file manually just by doubleclicking it, it
> runs fine and do the job. When I set the SQL job to execute the same bat
> file, it executes the bat file, but the bat file doesn't zip the file. It
> reports that everything is ok, but the file isn't being created.
> It seems like the zip program I've found that can be executed from a
command
> line (it's a program called 7-zip) will only do the job if it can "open" a
> command prompt in the background which SQL apparently doesn't do.
> My question is now if any of you know a way to let a SQL job "open" a
> command prompt and then execute a program from there. It could also be as
> simple as using a different zip program. 7-zip was just one I found that
> could zip a file from a command line.
> Regards
> Steen
>|||Hi Hari
Thanks for the advise. I've just tried it, but it seems like it has the same
problem as when I run it un a job.
The commandline I'm running is : "c:\program files\7-zip\7z" a -tzip
filename.zip filename.mdc
This works fine when running it either as a bat file or just in a cmd
window, but from within SQL it seems to fail. The output I get from running
it either as a job or with the XP_CmdShell is :
NULL
7-Zip 2.30 Beta 28 Copyright (c) 1999-2003 Igor Pavlov 2003-02-16
Scanning
NULL
Updating archive filename.zip
NULL
NULL
Everything is Ok
NULL
It reports that everything is ok, but it just don't create the zip file. It
might be that this 7-zip program just can't run properly without having an
open cmd prompt - which of course is a bit weird.
Regards
Steen
"Hari" <hari_prasad_k@.hotmail.com> skrev i en meddelelse
news:eCrlkGhUEHA.484@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Use XP_CMDSHELL to fire a OS command from command prompt.
> Before commentng more, can you send out the script you are trying out.
> --
> Thanks
> Hari
> MCDBA
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:uKWljChUEHA.1764@.TK2MSFTNGP10.phx.gbl...
> > Hi
> >
> > I'm having a strange problem which puzzels me a little bit.
> > I have created a SQL job that can zip a file that has been generated
from
> a
> > provious job. I've made a bat file that contains the string to run to
zip
> > the file. When I run this bat file manually just by doubleclicking it,
it
> > runs fine and do the job. When I set the SQL job to execute the same bat
> > file, it executes the bat file, but the bat file doesn't zip the file.
It
> > reports that everything is ok, but the file isn't being created.
> >
> > It seems like the zip program I've found that can be executed from a
> command
> > line (it's a program called 7-zip) will only do the job if it can "open"
a
> > command prompt in the background which SQL apparently doesn't do.
> > My question is now if any of you know a way to let a SQL job "open" a
> > command prompt and then execute a program from there. It could also be
as
> > simple as using a different zip program. 7-zip was just one I found that
> > could zip a file from a command line.
> >
> > Regards
> > Steen
> >
> >
>|||Hi,
Login to SQL server from command prompt:-
OSQL -Usa -Ppassword -S SQl_server
This will allow you to go to a SQL prompt:-
1>
Here you execute the xp_cmdshell
1>master..xp_cmdsehll 'c:\program files\7-zip\7z a -tzip'
2>go
Note:
Please use the syntax clearly
--
Thanks
Hari
MCDBA
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uPWCBNhUEHA.3988@.tk2msftngp13.phx.gbl...
> Hi Hari
> Thanks for the advise. I've just tried it, but it seems like it has the
same
> problem as when I run it un a job.
> The commandline I'm running is : "c:\program files\7-zip\7z" a -tzip
> filename.zip filename.mdc
> This works fine when running it either as a bat file or just in a cmd
> window, but from within SQL it seems to fail. The output I get from
running
> it either as a job or with the XP_CmdShell is :
> NULL
> 7-Zip 2.30 Beta 28 Copyright (c) 1999-2003 Igor Pavlov 2003-02-16
> Scanning
> NULL
> Updating archive filename.zip
> NULL
> NULL
> Everything is Ok
> NULL
> It reports that everything is ok, but it just don't create the zip file.
It
> might be that this 7-zip program just can't run properly without having an
> open cmd prompt - which of course is a bit weird.
> Regards
> Steen
>
>
> "Hari" <hari_prasad_k@.hotmail.com> skrev i en meddelelse
> news:eCrlkGhUEHA.484@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > Use XP_CMDSHELL to fire a OS command from command prompt.
> >
> > Before commentng more, can you send out the script you are trying out.
> >
> > --
> > Thanks
> > Hari
> > MCDBA
> > "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> > news:uKWljChUEHA.1764@.TK2MSFTNGP10.phx.gbl...
> > > Hi
> > >
> > > I'm having a strange problem which puzzels me a little bit.
> > > I have created a SQL job that can zip a file that has been generated
> from
> > a
> > > provious job. I've made a bat file that contains the string to run to
> zip
> > > the file. When I run this bat file manually just by doubleclicking it,
> it
> > > runs fine and do the job. When I set the SQL job to execute the same
bat
> > > file, it executes the bat file, but the bat file doesn't zip the file.
> It
> > > reports that everything is ok, but the file isn't being created.
> > >
> > > It seems like the zip program I've found that can be executed from a
> > command
> > > line (it's a program called 7-zip) will only do the job if it can
"open"
> a
> > > command prompt in the background which SQL apparently doesn't do.
> > > My question is now if any of you know a way to let a SQL job "open" a
> > > command prompt and then execute a program from there. It could also be
> as
> > > simple as using a different zip program. 7-zip was just one I found
that
> > > could zip a file from a command line.
> > >
> > > Regards
> > > Steen
> > >
> > >
> >
> >
>|||Hi Hari
Thansk for your suggestions, but it simply won't do it. I get the same
result when running is with the Osql command. Apparently this zip program
only works when it's launched from it's "own" cmd prompt.
Steen
"Hari" <hari_prasad_k@.hotmail.com> skrev i en meddelelse
news:%23zI$HThUEHA.1020@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Login to SQL server from command prompt:-
> OSQL -Usa -Ppassword -S SQl_server
> This will allow you to go to a SQL prompt:-
> 1>
> Here you execute the xp_cmdshell
> 1>master..xp_cmdsehll 'c:\program files\7-zip\7z a -tzip'
> 2>go
> Note:
> Please use the syntax clearly
> --
> Thanks
> Hari
> MCDBA
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:uPWCBNhUEHA.3988@.tk2msftngp13.phx.gbl...
> > Hi Hari
> >
> > Thanks for the advise. I've just tried it, but it seems like it has the
> same
> > problem as when I run it un a job.
> >
> > The commandline I'm running is : "c:\program files\7-zip\7z" a -tzip
> > filename.zip filename.mdc
> >
> > This works fine when running it either as a bat file or just in a cmd
> > window, but from within SQL it seems to fail. The output I get from
> running
> > it either as a job or with the XP_CmdShell is :
> >
> > NULL
> > 7-Zip 2.30 Beta 28 Copyright (c) 1999-2003 Igor Pavlov 2003-02-16
> > Scanning
> > NULL
> > Updating archive filename.zip
> > NULL
> > NULL
> > Everything is Ok
> > NULL
> >
> > It reports that everything is ok, but it just don't create the zip file.
> It
> > might be that this 7-zip program just can't run properly without having
an
> > open cmd prompt - which of course is a bit weird.
> >
> > Regards
> > Steen
> >
> >
> >
> >
> >
> > "Hari" <hari_prasad_k@.hotmail.com> skrev i en meddelelse
> > news:eCrlkGhUEHA.484@.TK2MSFTNGP10.phx.gbl...
> > > Hi,
> > >
> > > Use XP_CMDSHELL to fire a OS command from command prompt.
> > >
> > > Before commentng more, can you send out the script you are trying out.
> > >
> > > --
> > > Thanks
> > > Hari
> > > MCDBA
> > > "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> > > news:uKWljChUEHA.1764@.TK2MSFTNGP10.phx.gbl...
> > > > Hi
> > > >
> > > > I'm having a strange problem which puzzels me a little bit.
> > > > I have created a SQL job that can zip a file that has been generated
> > from
> > > a
> > > > provious job. I've made a bat file that contains the string to run
to
> > zip
> > > > the file. When I run this bat file manually just by doubleclicking
it,
> > it
> > > > runs fine and do the job. When I set the SQL job to execute the same
> bat
> > > > file, it executes the bat file, but the bat file doesn't zip the
file.
> > It
> > > > reports that everything is ok, but the file isn't being created.
> > > >
> > > > It seems like the zip program I've found that can be executed from a
> > > command
> > > > line (it's a program called 7-zip) will only do the job if it can
> "open"
> > a
> > > > command prompt in the background which SQL apparently doesn't do.
> > > > My question is now if any of you know a way to let a SQL job "open"
a
> > > > command prompt and then execute a program from there. It could also
be
> > as
> > > > simple as using a different zip program. 7-zip was just one I found
> that
> > > > could zip a file from a command line.
> > > >
> > > > Regards
> > > > Steen
> > > >
> > > >
> > >
> > >
> >
> >
>|||Did you qualify the path for both the input as well as output files?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message news:uPWCBNhUEHA.3988@.tk2msftngp13.phx.gbl...
> Hi Hari
> Thanks for the advise. I've just tried it, but it seems like it has the same
> problem as when I run it un a job.
> The commandline I'm running is : "c:\program files\7-zip\7z" a -tzip
> filename.zip filename.mdc
> This works fine when running it either as a bat file or just in a cmd
> window, but from within SQL it seems to fail. The output I get from running
> it either as a job or with the XP_CmdShell is :
> NULL
> 7-Zip 2.30 Beta 28 Copyright (c) 1999-2003 Igor Pavlov 2003-02-16
> Scanning
> NULL
> Updating archive filename.zip
> NULL
> NULL
> Everything is Ok
> NULL
> It reports that everything is ok, but it just don't create the zip file. It
> might be that this 7-zip program just can't run properly without having an
> open cmd prompt - which of course is a bit weird.
> Regards
> Steen
>
>
> "Hari" <hari_prasad_k@.hotmail.com> skrev i en meddelelse
> news:eCrlkGhUEHA.484@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > Use XP_CMDSHELL to fire a OS command from command prompt.
> >
> > Before commentng more, can you send out the script you are trying out.
> >
> > --
> > Thanks
> > Hari
> > MCDBA
> > "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> > news:uKWljChUEHA.1764@.TK2MSFTNGP10.phx.gbl...
> > > Hi
> > >
> > > I'm having a strange problem which puzzels me a little bit.
> > > I have created a SQL job that can zip a file that has been generated
> from
> > a
> > > provious job. I've made a bat file that contains the string to run to
> zip
> > > the file. When I run this bat file manually just by doubleclicking it,
> it
> > > runs fine and do the job. When I set the SQL job to execute the same bat
> > > file, it executes the bat file, but the bat file doesn't zip the file.
> It
> > > reports that everything is ok, but the file isn't being created.
> > >
> > > It seems like the zip program I've found that can be executed from a
> > command
> > > line (it's a program called 7-zip) will only do the job if it can "open"
> a
> > > command prompt in the background which SQL apparently doesn't do.
> > > My question is now if any of you know a way to let a SQL job "open" a
> > > command prompt and then execute a program from there. It could also be
> as
> > > simple as using a different zip program. 7-zip was just one I found that
> > > could zip a file from a command line.
> > >
> > > Regards
> > > Steen
> > >
> > >
> >
> >
>|||Solved it.....
In my bat file I just changed to the directory where the files where
placed - that did the trick(..should have thought about that from the very
beginning...).
It might as well work if I specify the full path for both input and output
files as Tibor suggest, but I haven't tried it.
Thanks for all your suggestions.
Regards
Steen
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> skrev i
en meddelelse news:%23lkrJmhUEHA.2692@.TK2MSFTNGP09.phx.gbl...
> Did you qualify the path for both the input as well as output files?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uPWCBNhUEHA.3988@.tk2msftngp13.phx.gbl...
> > Hi Hari
> >
> > Thanks for the advise. I've just tried it, but it seems like it has the
same
> > problem as when I run it un a job.
> >
> > The commandline I'm running is : "c:\program files\7-zip\7z" a -tzip
> > filename.zip filename.mdc
> >
> > This works fine when running it either as a bat file or just in a cmd
> > window, but from within SQL it seems to fail. The output I get from
running
> > it either as a job or with the XP_CmdShell is :
> >
> > NULL
> > 7-Zip 2.30 Beta 28 Copyright (c) 1999-2003 Igor Pavlov 2003-02-16
> > Scanning
> > NULL
> > Updating archive filename.zip
> > NULL
> > NULL
> > Everything is Ok
> > NULL
> >
> > It reports that everything is ok, but it just don't create the zip file.
It
> > might be that this 7-zip program just can't run properly without having
an
> > open cmd prompt - which of course is a bit weird.
> >
> > Regards
> > Steen
> >
> >
> >
> >
> >
> > "Hari" <hari_prasad_k@.hotmail.com> skrev i en meddelelse
> > news:eCrlkGhUEHA.484@.TK2MSFTNGP10.phx.gbl...
> > > Hi,
> > >
> > > Use XP_CMDSHELL to fire a OS command from command prompt.
> > >
> > > Before commentng more, can you send out the script you are trying out.
> > >
> > > --
> > > Thanks
> > > Hari
> > > MCDBA
> > > "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> > > news:uKWljChUEHA.1764@.TK2MSFTNGP10.phx.gbl...
> > > > Hi
> > > >
> > > > I'm having a strange problem which puzzels me a little bit.
> > > > I have created a SQL job that can zip a file that has been generated
> > from
> > > a
> > > > provious job. I've made a bat file that contains the string to run
to
> > zip
> > > > the file. When I run this bat file manually just by doubleclicking
it,
> > it
> > > > runs fine and do the job. When I set the SQL job to execute the same
bat
> > > > file, it executes the bat file, but the bat file doesn't zip the
file.
> > It
> > > > reports that everything is ok, but the file isn't being created.
> > > >
> > > > It seems like the zip program I've found that can be executed from a
> > > command
> > > > line (it's a program called 7-zip) will only do the job if it can
"open"
> > a
> > > > command prompt in the background which SQL apparently doesn't do.
> > > > My question is now if any of you know a way to let a SQL job "open"
a
> > > > command prompt and then execute a program from there. It could also
be
> > as
> > > > simple as using a different zip program. 7-zip was just one I found
that
> > > > could zip a file from a command line.
> > > >
> > > > Regards
> > > > Steen
> > > >
> > > >
> > >
> > >
> >
> >
>|||Make sure the sqlagent account has enough permission to zip. Also you might
try winzip 8.0 command line addon. It's working for me.
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uKWljChUEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Hi
> I'm having a strange problem which puzzels me a little bit.
> I have created a SQL job that can zip a file that has been generated from
a
> provious job. I've made a bat file that contains the string to run to zip
> the file. When I run this bat file manually just by doubleclicking it, it
> runs fine and do the job. When I set the SQL job to execute the same bat
> file, it executes the bat file, but the bat file doesn't zip the file. It
> reports that everything is ok, but the file isn't being created.
> It seems like the zip program I've found that can be executed from a
command
> line (it's a program called 7-zip) will only do the job if it can "open" a
> command prompt in the background which SQL apparently doesn't do.
> My question is now if any of you know a way to let a SQL job "open" a
> command prompt and then execute a program from there. It could also be as
> simple as using a different zip program. 7-zip was just one I found that
> could zip a file from a command line.
> Regards
> Steen
>|||In article <uKWljChUEHA.1764@.TK2MSFTNGP10.phx.gbl>, "Steen Persson" <SPE@.REMOVEdatea.dk> wrote:
>Hi
>I'm having a strange problem which puzzels me a little bit.
>I have created a SQL job that can zip a file that has been generated from a
>provious job. I've made a bat file that contains the string to run to zip
>the file. When I run this bat file manually just by doubleclicking it, it
>runs fine and do the job. When I set the SQL job to execute the same bat
>file, it executes the bat file, but the bat file doesn't zip the file. It
>reports that everything is ok, but the file isn't being created.
>It seems like the zip program I've found that can be executed from a command
>line (it's a program called 7-zip) will only do the job if it can "open" a
>command prompt in the background which SQL apparently doesn't do.
>My question is now if any of you know a way to let a SQL job "open" a
>command prompt and then execute a program from there. It could also be as
>simple as using a different zip program. 7-zip was just one I found that
>could zip a file from a command line.
>Regards
>Steen
Use PKZip.
The original and best cmdline tool.

Saturday, February 25, 2012

rules on a table

In My table i'm collecting information about our customers.In that i have fields zip and phone.So i wanted to implemement a rule that zip should be atlaest of of 5 characters and phone should be 13 characters Including '-'s(333-333-3333).How can i implement these two rules on my table.First of all, decide if you really want to add those constraints. They will automagically limit your database to dealing with data like what you'd find in the United States.

If that is an Ok thing, then I would suggest that you add constraints to your table. You could use something like:ALTER TABLE myTable
ADD CONSTRAINT XCK01myTable CHECK (5 <= Len(zip))
, ADD CONSTRAINT XCK02myTable CHECK (phone
LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')Constraints like these make me nervous, since I see them as rather arbitrary, but they are much better done as constraints than as code because you can easily change them in the database if needed, instead of having to hunt down hundreds or thousands of snippets of code!

-patP|||I'm with Pat. I think you'll end up regretting this as the users start to complain about limiting their ability to enter data. The zip-code you might get away with, though what about zip-4 extensions? I don't think enforcing a 13 character phone number is going to make you a lot of friends with the users.|||True, but you can fix this in one place (the database) with one command (DROP CONSTRAINT) if it turns out to be a problem. This is far better than coding it into an executable or a web page, at least in my opinion.

-PatP|||Oh, I agree. Table constraints are the best places to store design flaws, hands down. :p|||I don't know that it's a design flaw to insure data consistency in the data layer, even if that means adding "another" layer to the data. It's isn't so much to cover design flaws as it is to allow the data group to insure data is consistent and secure for the enterprise.|||It's not a design flaw to enforce data integrity. I'm just concerned about enforcing this particular constraint, based on past experience. I guess I'd say it is a design flaw to enforce unnecessary constraints that place arbitrary restrictions on the users. Unless there is some sort of application process that depends upon the phone number being absolutely 13 characters, then why place an artificial limit on how the database can be used? You are locking out the possibility of international numbers, extensions, etc...|||i agree with blindman

"it is a design flaw to enforce unnecessary constraints that place arbitrary restrictions on the users"

what about the guy who paid a few thousand bucks to secure the number 1-800-BEST-DBA

this guy's going to be p1ssed if you force him to put the dash where you want him to put the dash

note for those who still don't get it: there is no dash on the phone keypad|||That's not an unnecessary constraint. 1800BESTDBA isn't a phone number. It's a way to represent a phone number. You can't dial it though. You can't feed it to a dialer or use it for customer service. Anyone looking at the number is going to wonder what the idiot was thinking putting it into the database like that anyway. If you want to have a descriptor for the phone number, then have one. He can put whatever he wants in there. 1-800-BEST-DBA, 1-800-STUPID-Q...whatever.

The number is 18003334444. The display handled on the front-end is 1-800-333-4444. The descriptiong, which can be displayed or not is 1-800-STUPID-Q.|||well, that just proves my point

the number is not 18003334444

it's 18002378322 -- you could look it up!

and yes, i sure can dial 1-800-BEST-DBA

even on my rotary phone!!

and of course your very descriptive "what the idiot was thinking..." reveals an attitude that might best be set aside when dealing with people in the real world

Wednesday, March 28, 2012

Running a program from a SQL job

Running a program from a SQL job

Running a program from a SQL job

Saturday, February 25, 2012

rules on a table

RSS Text Data Mining

Blog Archive

About Me