Creating a stored procedure from VB.net and runnning it

[MiloCold]

Greetings Peoples,

My current project takes one tab-delim text file and produces several tab-delim text files (that are logically related); these files are formatted in such a way that each record--in each file--can be sucked into their related sql table without any modifications via a related loader app running server side (no DTS involved). So 10 or 12 text files are xcopied to their perspective directories on the server where they wait to be loaded one at a time.

This, I think, sucks (and I created the bloody thing). First off, if all the files don't make it across the network we's in a world of s&#t because these records are related and must be loaded as "all-or-none". To remedy this (aka add more duct tape) I was thinking about having the app create a SQL stored procedure which then could be xcopied to it's proper place, this also would give me the ROLLBACK advantage.

QUESTIONS:

1) Creating a stored procedure from VB.Net I imagine is just like creating any other text file just with the proper TSQL statements and ".sql" extension, correct? Is there a different way?

/** More of a SQL Question **/
2) If I xcopy this stored procedure over to the server, how would I run it? I mean, what directory does it have to be in? I couldn't find anything while surfing through our instance's directory. I would think that the SP would have to reside in a certain dir in order to allow me to EXECUTE it...am I wrong? Can I run the SP from the VB.net project's Binn Dir?

FYI: This project won't be in use for long, just until our bidirectional transactional replication is dependable, so no making fun of me...alright, you can poke fun!

Thanks in advance,

M. Cold

 

[TechGnome]

COpy the .sql files doesn't "make" the stored procedure. It's just a file. To actualy create the stored procedure, you need to RUN the SQL in query analyzer. Once. Then just call the SP from VB. Once the SP has been created, the file can (in theory) be thrown away (in practice, it's easier to modify the SP if you hang on to it.)

If you are doing this in .NET, you may want to look into loading the data into a dataset, then invoking the UpdateCommand to do it all transactionaly. I've never done that (I deal in nothing bu SPs) so I can't give an example on how to do it.

Tg

 

[MiloCold]

Hmm...thanks! I totally forgot about how SPs work, DOH! Anywho...currently I am using datasets & the sqlcommand class so I guess I'll leave that as it is until our replication is fully operational.

Thanks!

M.Cold