External scripts / imports / updates

Looking for suggestions on this one. What I want to do have have a text file that may have any number of rows and cols (with a predefined format) that a user can update or insert into a table. The definition of the row/cols and data mapping etc, has been done, it is the mechanics of actually doing the below I would appreciate help and advice on.

As the user is an 'end-user' (and has no SQL knowledge at all) the text file to import from will be placed in a predefined location and then a small script will be executed from their PC (as it happens, it's a Mac that runs an app that can exec an SQL command on the currently open database) that will in turn run a stored proc which is then reads in (imports or updates) the appropriate tables witht he contents of the external text file.

Sorry the explanation is a bit long winded but if anyone had any practical suggestions and examples, it would be greatly appreciated.

FYI, they are running SQL 2000 on both XP Pro and W2K3 server.

Thanks
StarbYou can take help of DTS package and schedule to run or give rights to the user to execute in order to import/export the data required.

Also can achieve with ISQL/OSQL utility, refer to books online for more information.|||You can take help of DTS package and schedule to run or give rights to the user to execute in order to import/export the data required.

Also can achieve with ISQL/OSQL utility, refer to books online for more information.
Thanks. Dont want to use a third party tool (OSQL etc) and can't use such as DTS and Exec as it is the 'End User' that will use the funtion. It must be run via a simple script from the Mac app.

Cheers
Starbsql

External notification of database updates

MS-SQL 2005, C# 2.0

I will have many client databases that will be updated. When they are updated I need to transfer some of that data to a central Server database somewhere on the Internet. Note, schemas do not necessarily match.

Transfering the data (Web Services, Remoting) is not a problem.

What I am looking for is a cool, correct, advisable way, for the client database to notify me of an Insert, Update or Delete. I can then initiate the connection to the Server and 'push' the data (maybe pull some back from the Server too).

Obviously Triggers may be a place to start... But I need to know 'external to the database' that an update has happened or can I send the data from within the SQL Server assembly?

Anyone have any ideas or a technique, maybe something new in SQL Server 2005 (all databases will be 2005), for acheiving this? Just so I don't go down the 'wrong' path...

There are some BLOB's involved (1-2 page PDF's), if that makes a difference.

I envisage that the process transfering the data will be a Windows Service running on each client. The connection may not always be available, so some kind of 'to do' list of outstanding data to be transfered is required.

I'm just starting on this, so any pointers would be great, I'm sure it's all been done before ;)

Thanks

Rob.

One interesting technique to consider would be using triggers and service broker. When an insert, update or delete occurs, send a message via service broker to your central server informing it of the change along with whatever data is necessary to identify it. The central server can then asynchronously receive and process the message.

Dan

external access denied to update a config file

Hello,

I'm having an issue with a CLR Stored procedure. Everything works great in a 32 bit environment, I have a CLR SP that updates an xml file stored on a local drive. When I execute the Stored proc it does go and update what I want it to in a 32 bit system. When I run the CLR SP on a 64 bit cluster, I seem to have give the "everyone" group write permissions to my G: drive (which is where the file is located that I'm updating). What security context is this SP running under? I thought it would be either under the SQL Service account (which is a domain user in the local administrators group) or what I'm logged in as when I run it from Management Studio (which is a domain admin, also in the local administrators group). If I have given the local administrators group "Full Control" access to the G: drive, why isn't this enough? Why do I have to give the Everyone group write access?

The security context seems odd to me, it seems like it's not running as either one of those 2 users I mentioned, because if it was, then it should be able to update the xml file.

Any help appreciated. Here's the error I'm getting:

Msg 6522, Level 16, State 1, Procedure usp_XMLWriter, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'usp_XMLWriter':

System.UnauthorizedAccessException: Access to the path 'g:\ssisPackages\BuildCalendar\andy.dtsConfig' is denied.

System.UnauthorizedAccessException:

at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)

at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)

at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options, String msgPath, Boolean bFromProxy)

at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share)

at System.Xml.XmlTextWriter..ctor(String filename, Encoding encoding)

at System.Xml.XmlDocument.Save(String filename)

at XMLWriter.StoredProcedures.usp_XMLWriter(String xmlDocPath, String xmlNodePath, String xmlInnerText)

.

I found out that I'm able to get this to work if I put the SQL Service User account directly into the security of the G: Drive. For some reason, even though the user is in the Local Administrators group, it doesn't seem to pick up the fact that my user in is in the group, and therefore won't let him update a file in the folder.

Does anyone know of any issues in a 64 bit cluster where a CLR Stored proc isn't able to decipher the users in a local Group?

|||This turned out to be a cluster issue. We weren't doing permissions through the cluster, we were doing permissions to the file shares through each node, which is a mistake in a cluster.