New Application - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

New Application

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-07-13 : 07:55:36

Hi,

I have a folder in the server which is populated with collections (in form of csv.files). I have SQL 2005 installed in thi server and can use to communicate with the folder.

My task is:

1) Automate a process to check if there is a new csv file for a practice in the folder. If yes, then update a table in the SQL database by incrementing a field called col_uid and set flags to send and receive as true.

I DO NOT need to do any data transfer whatsoever.

Whats the best way to approach this task..

A colleague suggested to have an application to run through the folder to check for new collections by creating a VB.NET application - (then have a scheduler to run the application)..

Then create a trigger where when a new collection comes in it creates the colu_uid and updates the send and received fields...

Any ideas please.. many thanks

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-07-13 : 08:13:09

Which version of sql server are you using? If version >= 2008 you can do like this (if not then switch the table variable to a temp-table):


DECLARE @cmd nvarchar(200)
DECLARE @Files table (
	ID int NOT NULL IDENTITY(1, 1),
	Filename varchar(200)
	)
	
SET @cmd = 'dir /b d:\somefolder\*.csv'

INSERT INTO @Files (Filename)
EXEC xp_cmdshell @cmd

SELECT * 
FROM @Files

This is a method to get hold of all the filenames of a certain type in a specific folder. To find out which files are "new" you'll have to compare the contents of the table variable to a list of "old" files.

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-07-13 : 09:18:39

How can I update this ?
I have SQL version 2005

Many thanks

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-07-13 : 09:18:58

How can I update this ?
I have SQL version 2005

Many thanks

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-07-13 : 09:42:12

[code]DECLARE @cmd nvarchar(200)
CREATE TABLE #Files (
ID int NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
Filename varchar(200)
)

SET @cmd = 'dir /b d:\somefolder\*.csv'

INSERT INTO #Files (Filename)
EXEC xp_cmdshell @cmd

SELECT *
FROM @Files

DROP TABLE #Files[/code]

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-07-13 : 10:01:05

Ok,

I open the database and open a query and had the following code;

DECLARE @cmd nvarchar(200)
CREATE TABLE #Files (
ID int NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
Filename varchar(200)
)

SET @cmd = 'dir /b G:\Test\*.csv'

INSERT INTO #Files (Filename)
EXEC xp_cmdshell @cmd

SELECT *
FROM @Files

DROP TABLE #Files

It executed with an error "Msg 1087, level 15, State 2, Line 13
Must declare the table variable "@Files".

Please help!!

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-07-13 : 10:31:35

quote:
Please help!!

Have you even looked at the code?? It is clearly a typo on my end but if you spent about half a calories worth of effort you'd probably figure it out.

-> SELECT * FROM #Files

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-07-13 : 11:33:25

Tried the following;

DECLARE @cmd nvarchar(200)
CREATE TABLE dbo.TblFilenameList (
ID int NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
Filename varchar(200)
)

SET @cmd = 'dir /b G:\Test\*.csv'

INSERT INTO dbo.TblFilenameList (Filename)
EXEC xp_cmdshell @cmd

SELECT *
FROM dbo.TblFilenameList

Received the error;

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

(0 row(s) affected)

vaari
Starting Member

15 Posts

Posted - 2011-07-13 : 13:10:27

Follow the steps listed in the link below to enable xp_cmdshell

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources