| Author |
Topic |
|
Alexh89
Starting Member
6 Posts |
 Posted - 2012-02-27 : 05:30:29
|
| Hello,I'm not sure to what category this question belongs, so i'm sorry if it's misplaced.What I want is to use C# code for timed jobs/stored procedures. I know it is possible to execute a .exe from within a job, but it would be great if the C# code could be actually created within the job itself. Is this possible? What are the other options for C# in SQL Server? Thanks in advance |
|
|
Jayam.cnu
Starting Member
45 Posts |
Posted - 2012-02-27 : 05:37:53
|
| Hi Alex .....As per my understanding it is not possible to do that in sql server....because job is completely T-SQL-language window. |
 |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-02-27 : 06:05:00
|
| well you can write CLR extensions in c# to do stuff which you can call as if they were sps... (http://en.wikipedia.org/wiki/SQL_CLR)However, why? what is it you are trying to achieve here?Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Alexh89
Starting Member
6 Posts |
Posted - 2012-02-27 : 06:35:45
|
quote:
Originally posted by Transact Charlie
well you can write CLR extensions in c# to do stuff which you can call as if they were sps... (http://en.wikipedia.org/wiki/SQL_CLR)However, why? what is it you are trying to achieve here?Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Thank you both for your answers. I currently follow an internship at a company that needs timed jobs in SQL Server 2008. They use C# .NET for their applications and are looking for a solution with this language. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-02-27 : 06:42:46
|
| If you want to do an operation on the database (like auditing or data manipulation) then you are much better of using the inbuilt sql-server agent and simply write SQL to do it for you. A c# extension or calling an external application is:1) Complete overkill2) A very complicated way to do something simpleStart here: http://msdn.microsoft.com/en-us/library/ms189237.aspxeverything you probably want to do to the database in a scheduled way can probably be done using sql server agent. Including scheduling backups / index cleanup / etc / etc.It would be very rare you'd want to call a c# routine regularly from the db.Again -- if you flesh out the actual end goal someone would be able to advise more.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Alexh89
Starting Member
6 Posts |
Posted - 2012-02-27 : 06:50:59
|
quote:
Originally posted by Transact Charlie
If you want to do an operation on the database (like auditing or data manipulation) then you are much better of using the inbuilt sql-server agent and simply write SQL to do it for you. A c# extension or calling an external application is:1) Complete overkill2) A very complicated way to do something simpleStart here: http://msdn.microsoft.com/en-us/library/ms189237.aspxeverything you probably want to do to the database in a scheduled way can probably be done using sql server agent. Including scheduling backups / index cleanup / etc / etc.It would be very rare you'd want to call a c# routine regularly from the db.Again -- if you flesh out the actual end goal someone would be able to advise more.Charlie ===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
goal: Check a directory on a server, to see if there is a file that needs to be imported into the database. If so, import the file. This has to happen at a specific hour of the day. They think this is very hard with SQL. (btw, sorry for my English:p) |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-02-27 : 06:56:49
|
| Had a look at BULK INSERT:http://msdn.microsoft.com/en-us/library/ms188365.aspxWith some sps to insure that the data goes to the right place?or even an SSIS package (but that's probably overkill)Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Alexh89
Starting Member
6 Posts |
Posted - 2012-02-27 : 07:07:42
|
| Thanks, I did not know that it is possible to use a file with Bulk.However, I have to bother you once more.My colleague looked doubtful, he mumbled that it is probably not possible with the type of file on the server, especially with editing the data (while importing). So there's no easy way to do it with C# |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-02-27 : 07:17:32
|
| why would you want to?there are two usual ways1) BULK IMPORT or BCP the file into a staging table (a table that matches the file structure you are expecting). Then a stored proc or set of stored procs that clean and process the data in the staging table (checking for data errors etc). In some cases the staging table is very generic (columns are all text). In others, if you know the structure of the file, then there is tying done early (importing as INT / money / whatever). This has the advantage in that it's pretty easy to check the data and either invalidate the who thing if there are failures or move error rows off to a reporting table.2) SSIS package which reads the file and then does the cleaning / manipulation in SSIS. This certainly isn't a zero configuration option but it may be something you want to consider.Rolling your own c# application to read a file and then issue INSERT statements seems quite a wasteful thing to do. You loose all the benefits of doing things in sets.I'd be surprised if you had a file that you couldn't read with a BULK IMPORTCharlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Alexh89
Starting Member
6 Posts |
Posted - 2012-02-27 : 07:19:58
|
quote:
Originally posted by Transact Charlie
why would you want to?there are two usual ways1) BULK IMPORT or BCP the file into a staging table (a table that matches the file structure you are expecting). Then a stored proc or set of stored procs that clean and process the data in the staging table (checking for data errors etc). In some cases the staging table is very generic (columns are all text). In others, if you know the structure of the file, then there is tying done early (importing as INT / money / whatever). This has the advantage in that it's pretty easy to check the data and either invalidate the who thing if there are failures or move error rows off to a reporting table.2) SSIS package which reads the file and then does the cleaning / manipulation in SSIS. This certainly isn't a zero configuration option but it may be something you want to consider.Rolling your own c# application to read a file and then issue INSERT statements seems quite a wasteful thing to do. You loose all the benefits of doing things in sets.I'd be surprised if you had a file that you couldn't read with a BULK IMPORTCharlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
We will try these options. Thank you for your time! |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-02-27 : 07:20:37
|
| no problem.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
Use C# in job