Dear Readers
I have been away from this blog for much too long, but I'm back and getting back into posting about some of my favorite technologies. I ran into an interesting problem today. It's definitely common, but it was the first time for me. I was running a stored procedure on SQL Server 2008 R2 and got an "Out of Memory" exception.
I googled around and came across a fix which is quite easy. Well, let me not call it a fix since obviously it might not work for everyone; however it is currently working for me (running as I'm typing) so I thought I'd share.
Apparently, what happens is that you receive a .NET Exception. This happens when the result set that your query returns (in my case, I am using a "SELECT" statement to extract values into a cursor) is very large and you exceed the limits of a 32-bit virtual address space (2GB). The workaround is to run the stored procedure using the "sqlcmd" utility (as obviously CLI tools require way less resources).
Simply type 'sqlcmd' -S <servername> into your command prompt and press enter. Then type 'Use <database>' name to change to the relevant database. Press Enter' Type 'exec dbo.<sp_name>' to execute the stored procedure. Quite simple. Please let me know if this doesn't work for you. It would be interesting to try to solve more complicated problems.
Happy coding!
Hermann
I have been away from this blog for much too long, but I'm back and getting back into posting about some of my favorite technologies. I ran into an interesting problem today. It's definitely common, but it was the first time for me. I was running a stored procedure on SQL Server 2008 R2 and got an "Out of Memory" exception.
I googled around and came across a fix which is quite easy. Well, let me not call it a fix since obviously it might not work for everyone; however it is currently working for me (running as I'm typing) so I thought I'd share.
Apparently, what happens is that you receive a .NET Exception. This happens when the result set that your query returns (in my case, I am using a "SELECT" statement to extract values into a cursor) is very large and you exceed the limits of a 32-bit virtual address space (2GB). The workaround is to run the stored procedure using the "sqlcmd" utility (as obviously CLI tools require way less resources).
Simply type 'sqlcmd' -S <servername> into your command prompt and press enter. Then type 'Use <database>' name to change to the relevant database. Press Enter' Type 'exec dbo.<sp_name>' to execute the stored procedure. Quite simple. Please let me know if this doesn't work for you. It would be interesting to try to solve more complicated problems.
Happy coding!
Hermann
No comments:
Post a Comment