CLR run .sql file

I have a file called test.sql (saved locally) which I would like to run through a CLR.
 
I have no Idea what script I should write to do this.
 
Can anybody help?
 
Thanks
Mr_ShawAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

santhimurthydCommented:
I have no exp on calling an SQL file from any .Net language (CLR)

Where as , we can read the file into an String and exetue the string in SQL server by Command Text

in C#

using (SqlConnection conn = new SqlConnection(connectionstring))
{
    SqlCommand com = new SqlCommand(conn);
    com.CommandType = CommandType.Text;
    com.CommandText =  SQLReadfromfile;
    com.ExecuteNonQuery();
}
Mr_ShawAuthor Commented:
how do I read the file into a string?
Christopher GordonSenior Developer AnalystCommented:
A SQL Server CLR is usually a managed code assembly that is usually called w/in a stored procedure or a function.  

I'm not sure exactly what you mean by trying to "run a .SQL file through a CLR".

What are you trying to accomplish with the Test.SQL?  Is it some sort of Unit Test script that is written to test the SQL objects (procs/functions) that call the CLR?  

Sorry to answer your question with more questions!
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Mr_ShawAuthor Commented:
Hi gohord,

Just as we can run/execute a .SQL file through xp_cmdshell, for example;

EXEC xp_cmdshell 'SQLCMD -S . -i"C:\FileName.sql"',no_output

I would like to execute a .SQL file through a CLR.
Mr_ShawAuthor Commented:
Hi santhimurthyd,

Maybe something like this?

http://msdn.microsoft.com/en-us/library/aa287534(v=vs.71).aspx
santhimurthydCommented:
Excatly you got it in right place :-)
Christopher GordonSenior Developer AnalystCommented:
@Mr_Shaw,

Just to make sure we're talking apples and apples, when you say "CLR", are you referring to an Assembly that has been loaded into database and is visible in the "Assemblies" folder in Management Studio?

Thanks!


Mr_ShawAuthor Commented:
yes
Christopher GordonSenior Developer AnalystCommented:
I'm not aware of any other reason to load a CLR assembly into SQL Server without the explicit purpose of creating a stored procedure, function, or other object that calls the assembly directly.

If you right click on the assembly and chose "View Dependencies", you can see what SQL object are using the assembly.

If this is the case you'd access the assembly behavior via the SQL Objects.


What is the .SQL file trying to do?  Perhaps I'm being thrown off by the name, but are you attempting to test the SQL objects which use the assembly as a resource with the file?

If @santhimurthyd is already provided you an answer let me know and I'll bow out of the thread.  

Thanks!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mr_ShawAuthor Commented:
the .SQL file does a data update. I need to perform this task as part of a testing sweet.
Christopher GordonSenior Developer AnalystCommented:
You can use the xp_cmdshell method to execute the file or "osql" for command line access to a SQL Server database.  

Both of these methods would take a file OR a SQL query syntax as input parameters.  

Regardless of the scenario, you would never pass a .SQL file directly to a CLR assembly in order to test it.  From my perspective, the more logical approach would be to test the SQL objects that call the CLR assembly.
Mr_ShawAuthor Commented:
I have used the xp_cmdshell and it causes a time out in my testing sweet. I was thinking of using CLR as an alternative.
Christopher GordonSenior Developer AnalystCommented:
Have you confirmed that the SQL statement that you are testing via xp_cmdshell functions correctly in management studio?

Unless I'm still not understanding, I don't see how a CLR assembly can be used as an alternative to executing a .SQL statement via a testing suite.
Mr_ShawAuthor Commented:
thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.