bcp and transaction problem

Hi,
We are creating a procedure in which we use bcp
(xp_cmdshell) to archive out the data for particular
month. When I execute procedure without begin tran/commit
tran it works fine and bcp out the data from table to hard
disk. But when i try to use transaction handling using
begin tran i see it to be waiting and dbcc inputbuffer of
waiting process shows:
SET FMTONLY ON select * from db..bcptable SET FMTONLY OFF
Why sql server is starting seperate process within
procedure ...IS there any special way to handle
transaction when using bcp and xp_cmdshell in the
procedure?
This is the only procedure running on machine.
Thanks
--harvinderharvinder,
> We are creating a procedure in which we use bcp
> (xp_cmdshell) to archive out the data for particular
> month. When I execute procedure without begin tran/commit
> tran it works fine and bcp out the data from table to hard
> disk. But when i try to use transaction handling using
> begin tran i see it to be waiting and dbcc inputbuffer of
> waiting process shows:
> SET FMTONLY ON select * from db..bcptable SET FMTONLY OFF
> Why sql server is starting seperate process within
> procedure ...IS there any special way to handle
> transaction when using bcp and xp_cmdshell in the
> procedure?
Bcp is running as a separate connection, not as a part of your
transaction. This is normal. It is being blocked by the connection
that opened the transaction. You cannot wrap a call to bcp in a
transaction.
Linda

BCP and Copy permissions

Hi,
I'm creating a number of CSV files in a stored procedure and I want to
copy them to a mapped network drive. I can create the CSV files
locally without any problem but I'm having trouble copying them to the
network drive. While I try to write to the network drive directly
using BCP I get the following error:
Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host
data-file
Any when I try to use a batch file (which is trigger by the query) to
copy the files locally to the network I get the following error:
The system cannot find the drive specified.
If I run either through the command prompt they both work fine.
I've looked at the permissions on the destination folder and set that
to all full control for everyone and I've also changed the user
account SQL Agent run under without any success.
Any help on this would be much appreciated.
Thanks
SimonOn Oct 11, 12:35 pm, accyboy1981 <accyboy1...@.gmail.com> wrote:
> Hi,
> I'm creating a number of CSV files in a stored procedure and I want to
> copy them to a mapped network drive. I can create the CSV files
> locally without any problem but I'm having trouble copying them to the
> network drive. While I try to write to the network drive directly
> using BCP I get the following error:
> Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host
> data-file
> Any when I try to use a batch file (which is trigger by the query) to
> copy the files locally to the network I get the following error:
> The system cannot find the drive specified.
> If I run either through the command prompt they both work fine.
> I've looked at the permissions on the destination folder and set that
> to all full control for everyone and I've also changed the user
> account SQL Agent run under without any success.
> Any help on this would be much appreciated.
> Thanks
> Simon
I'm not positive about this, but aren't network drives mapped on a per-
user basis? Try using the UNC path instead of a mapped drive letter.
Sandy Barnabas

Batch Jobs ..

Hi Frndz,

Will anyone plz help me in creating simple jobs in Sql Server. And how to run that Job. Actually i Created a job in the AGENT and schedule it daily at 2.30 pm . But it doesn't run automatically . . .

Thnx in Advance . .

Regards

(M. Nedu . . .)Hi,

The simple way to schedule a package is to right click on the package and you will get an option for scheduling. Once you are through it, you would find your package under "Jobs", check for the option which states "Enabled" and it should be "True". Try to run your Job manually and see whether it's executing fine. If it executes fine, then I am sure that it would execute on scheduled time.

Thanks & Regards
Mudasar

Quote:

Originally Posted by nedu

Hi Frndz,

Will anyone plz help me in creating simple jobs in Sql Server. And how to run that Job. Actually i Created a job in the AGENT and schedule it daily at 2.30 pm . But it doesn't run automatically . . .

Thnx in Advance . .

Regards

(M. Nedu . . .)

Batch File.

I am creating one batch file which move datafile and logfile into different
machine but I want to ask one question like if one command successfully
executed then I have to wait for 20 seconds and then execute the second
command .... can any one know the command which I can use in batch file...
I think there should be any wait or delay sort of command available....
Thanks in advance.
Joh wrote:
> I am creating one batch file which move datafile and logfile into
> different machine but I want to ask one question like if one command
> successfully executed then I have to wait for 20 seconds and then
> execute the second command .... can any one know the command which I
> can use in batch file... I think there should be any wait or delay
> sort of command available....
>
> Thanks in advance.
If you kick off the batch file from a stored procedure or SQL batch
file, you can use the WAITFOR DELAY command to wait for 20 seconds.
David Gugick
Imceda Software
www.imceda.com
|||I have created the Batch File in which I wrote this...
NET STOP SQLSERVERAGENT
NET STOP MsSqlserver
COPY "c:\Program Files\Microsoft SQL Server\MSSQL\Data\master.mdf"
C:\Backup\SystemDatabase
COPY "c:\Program Files\Microsoft SQL Server\MSSQL\Data\mastlog.ldf"
C:\Backup\SystemDatabase
COPY "c:\Program Files\Microsoft SQL Server\MSSQL\Data\msdbdata.mdf"
C:\Backup\SystemDatabase
COPY "c:\Program Files\Microsoft SQL Server\MSSQL\Data\msdblog.ldf"
C:\Backup\SystemDatabase
NET START SQLSERVERAGENT
NET START MSSQLSERVER
Now I want to wait after first copy so what I have to write after that?
Thanks in advance.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#spQKiNVFHA.1200@.TK2MSFTNGP14.phx.gbl...
> Joh wrote:
> If you kick off the batch file from a stored procedure or SQL batch
> file, you can use the WAITFOR DELAY command to wait for 20 seconds.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Joh wrote:
> I have created the Batch File in which I wrote this...
> NET STOP SQLSERVERAGENT
> NET STOP MsSqlserver
> COPY "c:\Program Files\Microsoft SQL Server\MSSQL\Data\master.mdf"
> C:\Backup\SystemDatabase
> COPY "c:\Program Files\Microsoft SQL Server\MSSQL\Data\mastlog.ldf"
> C:\Backup\SystemDatabase
> COPY "c:\Program Files\Microsoft SQL Server\MSSQL\Data\msdbdata.mdf"
> C:\Backup\SystemDatabase
> COPY "c:\Program Files\Microsoft SQL Server\MSSQL\Data\msdblog.ldf"
> C:\Backup\SystemDatabase
> NET START SQLSERVERAGENT
> NET START MSSQLSERVER
> Now I want to wait after first copy so what I have to write after
> that?
>
This is not a DOS newsgroup, and what I remember from those days is
somewhat limited. My suggestion was if you were using a stored procedure
to kick off the batch files, you could delay them using the waitfor
command. You would need to use xp_cmdshell to kick off the first batch
file, wait, then kick off the second, all from a stored procedure.
Why do you need to wait after the first copy?
David Gugick
Imceda Software
www.imceda.com
|||How can you start the SQL Service through SQL Stored procedure and through
XP_CMDSHELL .... ?
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:uI5jN6WVFHA.3044@.TK2MSFTNGP10.phx.gbl...
> Joh wrote:
> This is not a DOS newsgroup, and what I remember from those days is
> somewhat limited. My suggestion was if you were using a stored procedure
> to kick off the batch files, you could delay them using the waitfor
> command. You would need to use xp_cmdshell to kick off the first batch
> file, wait, then kick off the second, all from a stored procedure.
> Why do you need to wait after the first copy?
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Joh wrote:
> How can you start the SQL Service through SQL Stored procedure and
> through XP_CMDSHELL .... ?
>
You can't. I only suggested it before I actually knew what you were
doing. You need to start SQL Server outside a stored procedure, for
obvious reasons.
David Gugick
Imceda Software
www.imceda.com

Basic SQL Server Question

After creating database in SQL Server , it generates .mdf , .ndf
datafiles .
Generally if we get those datafiles then we can create the database
with tables/data on another machine by using those datafiles.

I want to know, if we can we protect this? I don't want another user
to take those files and create database on another SQL Server. Is
there any option available in SQL server while creating database to
protect this file with password or any darn thing for protection that
would not allow to create the database by using those file or any
workaround.

Thanks in advance for your help."Ashwani" <engg_akyadav@.yahoo.com> wrote in message
news:c1710804.0410050549.7c3ab340@.posting.google.c om...
> After creating database in SQL Server , it generates .mdf , .ndf
> datafiles .
> Generally if we get those datafiles then we can create the database
> with tables/data on another machine by using those datafiles.
> I want to know, if we can we protect this? I don't want another user
> to take those files and create database on another SQL Server. Is
> there any option available in SQL server while creating database to
> protect this file with password or any darn thing for protection that
> would not allow to create the database by using those file or any
> workaround.
> Thanks in advance for your help.

No, that's not possible (you can set a password on a backup set, though).
However, since only administrators should be able to access the server
filesystem, the best approach is simply to ensure that you're following
standard security procedures: NTFS permissions are properly set
(administrators and the MSSQL service only), no users have direct access to
the filesystem, no one has sysadmin access except sysadmins etc.

You can use the MBSA to check your current security configuration:

http://www.microsoft.com/technet/se...s/mbsahome.mspx

Also see the resources here about securing SQL Server:

http://www.microsoft.com/sql/techin...ity/default.asp

Simon

basic replication question

I'm interested in creating a replicated SQL server database on my laptop to develop without having to be connected to the network. I'd like to make changes and then synchronize when connected, both data and design changes. With SQL it looks like replication only applies to data and not to schema, stored procedures, dts packages, etc.

Do I understand this correctly.Do I understand this correctly.
Nope.

See
Schema changes under replication in bol.
A stored proc can be an published article.
DTS packages are stored in msdb..sysdtspackages if you keep them in sql server and you can copy them by copying the contents of this table.
It's usually easier just to copy all the stored procs too.

Have you thuoght about restoring a backup rater than replication which has quite a large learning curve and administrative overhead.