How to edit a .bat file to insert fields into a ta

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to edit a .bat file to insert fields into a ta

Author

Topic

pras2007
Posting Yak Master

216 Posts

Posted - 2010-04-09 : 11:09:32

Hello All,

I have a bat file that is executed by a job that copy a file called test_file.rar to another directory and I want to know if it is possible for that process to insert the “File name” and “File Location” into a table called Processed_Files?

The below is the .bat file script:

copy "S:\test_file.rar" \\DVCN\test_file
Below is the create table for the Processed_Files table:

CREATE TABLE [dbo].[Processed_Files](
[ID] [int] IDENTITY(1,1) NOT NULL,
[File_Name] [varchar](50) NULL,
[File_Location] [varchar](50) NULL,
[Process_Date] [smalldatetime] NULL CONSTRAINT [DF_Processed_Files_Process_Date] DEFAULT (getdate()),
CONSTRAINT [PK_Processed_Files] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

For the above example the following should be inserted into the

Processed_Files table:

- File_Name = test_file.rar
- File_Location = \\DVCN\test_file
Please advice

Thanks.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-09 : 12:10:33

Have a look in BOL for the sqlcmd command line tool. That will do what you need.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2010-04-09 : 15:26:46

You could do something like:
SQLCMD -E -S MYSERVER\MYINSTANCE -d mydb -q INSERT INTO PROCESSED_FILES......

as part of your process. The command above will use Windows Authentication , for sql server authentication look at the -U and -P switches

Jack Vamvas
--------------------
http://www.ITjobfeed.com

pras2007
Posting Yak Master

216 Posts

Posted - 2010-04-11 : 07:57:48

Thanks for the response all... jackv, when I used your method I get the following error:

Executed as user: asd\user. C:\WINDOWS\system32>SQLCMD -E -S MYSERVER\MYINSTANCE -d mydb -q INSERT INTO [mydb].[dbo].[Processed_Files] VALUES('test_file.rar','\\DVCN\test_file',getdate())Sqlcmd: 'INTO [mydb].[dbo].[Processed_Files] VALUES('test_file.rar','\\DVCN\test_file',getdate())': Unexpected argument. Enter '-?' for help. Process Exit Code 1. The step failed.

Does anyone knows what's wrong with my syntax?

Please advice..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources