phoenix
phoenixPhoenix - Your AI Assistant
Inactive session will be ended in 24 hours
End Session

I need advice on SQL Filestream

  • Posted:
  • Proposals: 3
  • Remote
  • #1316073
  • PRE-FUNDED
  • Awarded
Tom C.Mark R.Abdul R. have already sent a proposal.
  • 0

Description

Experience Level: Intermediate
General information for the website: A file hosting website
Database management system (DBMS): Microsoft SQL Server
Description of requirements/features: I need information on how I can modify my code to make it run properly. I will pay £10 for the information on how I can resolve my problem. No coding is required as I can do that myself. If you have the answer then this could take you just a few minutes to tell me.

The problem I have is:

I have a Sql Database with a filestream. I'm using the SqlFilestream API to write huge files into the filestream. The problem I have is that whilst copying information to/from the filestream using the SqlFilestream API I cannot run Select queries as they wait for the filestream connection to close before running. This is an issue as huge files take several minutes to complete. Whilst copying I cannot search the database and have to wait for it to finish.

Specific queries on a single row still work but when searching through the entire table they do not.

For example:

This query works fine:
select id, idvideo from videos WHERE display = 1 and idvideo= '785B4E32-C8BC-4EDD-955A-3A3FAE16F8DD'

This one does not work:
SELECT id, idvideo from VIDEOS

I cannot close the connection part way through as this will create several small files until the cleanup functions.
Extra notes:

New Proposal

Login to your account and send a proposal now to get this project.

Log in

Clarification Board Ask a Question

  • Tom C.

    Hi Christopher,

    You should run the query "SELECT id, idvideo from VIDEOS" within a (different) transaction, in order for it to be run straight away and not wait for the SqlFilestream writing connection to close.

    You can find this information on the official MSDN documentation here: https://msdn.microsoft.com/en-us/library/gg471497.aspx under the last section of the page "File System APIs and Supported Isolation Levels" within the 7th row of the table:

    Transaction 1: Open for write - (this is your SqlFilestream transaction)
    Transaction 2: Open for SELECT - (this is your "SELECT id, idvideo from VIDEOS" transaction)
    Outcome: Both succeed.

    Obviously, I just only hope that you are a trustworthy individual so eventually you'll award the job to me.

    Cheers,
    Tom

  • Gnanasekar S.

    Chris, it looks like a table lock or overall database level setting related to isolation. Do you have elevated rights to check those?