August 12, 2004 at 10:09 am
We're trying to combine information from 20-70 groups tracking very similar information in very dissimilar ways. Our initial solution for those not having decent dbs is to have them submit the info via standardized spreadsheets which I could then import via DTS. Is it possible to automate the process based on the presence of a new file in a folder location?
My other question concerns combining ntext fields. Two of the required fields for the project above are postive and negative comments. In my db, I have 2 fields in different tables which have to be combined into the 2 fields in this other table......(See below)
Table1
id | comment
Table2 (left outer join on Table1.id)
id | type | comment
I need to combine Table1 comment with all comments of type=1 into one field and combine all comments from Table2 type=2 in another. Hopefully that made some sense. Anyway, I tried looping through the tables using a UDF, but am limited to using varchar or nvarchar because they can't handle the ntext type. I have one field with ~16kb, so it bombs immediately. How can I get around this?
August 12, 2004 at 6:03 pm
You can pretty easily create a SQLAgent Job that checks a directory every 5 mins. Use sp_filexists @filepath, @exists OUTPUT /* exists integer */
Then you can rename or move the file with master..xp_cmdshell @command . I'm not sure if you can put wildcards in sp_fileexists, which you would need if the filename varies. The alternative is to create a temp table with one varchar column and
Insert into #temptable
exec master..xp_cmdshell 'dir c:\data\*.dat'
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply