Getting File Handle from FileStream Column

[Ammar S. Mitoori]

Hi

I created a FileStream enabled sql instance and database, then created a
filestream enabled table with following code

CREATE TABLE Attachments(
RowId uniqueidentifier ROWGUIDCOL NOT NULL UNIQUE DEFAULT WSEQUENTIALID),
Attachment varbinary(max) FILESTREAM DEFAULT(0x))

so to get a specific file to open from the filestream column i need to get
its hande and to get the andle i need to know its
"GET_FILESTREAM_TRANSACTION_CONTEXT()" and its "PathName()"

when i ran the below query in sql window just for test before coding in my
application

SELECT GET_FILESTREAM_TRANSACTION_CONTEXT(), Attachment.PathName() FROM
Attachments

i got the below error

Msg 5537, Level 16, State 1, Line 1
Function PathName is only valid on columns with the FILESTREAM attribute.

as you can see i made the column Attachment a filestream colum so i dont
know what the problem ?

[Ammar S. Mitoori]

hi

i created the table again it works fine and i added some columns to it but
it seems its very sensitive when i rearange the columns positions in design
view i start getting the error below so i delete it and create again why ?

i also made the uniqidentifire a key column is that fine ? and can i add
forgin key colum to a file stream table ? and link key colums to it from
other tables in a sql diagram ? im afraid i might get the same error and
start all over again ?

also when i add some rows to it and run the below query

"SELECT GET_FILESTREAM_TRANSACTION_CONTEXT(), Attachment.PathName() FROM
Attachments"

i get null values how can i get the file hande of some record then ?

[Bob Beauchemin]

Hi Ammar, here's some answers,

If you have in a database NULL value into the table you'll get no PathName.
In that case, you'll need to update the column (from T-SQL) so that it's
non-null and then you can get it handle to the file. You need to do that in
T-SQL, there is no way to create the file to replace a NULL value (or in an
insert statement) directly with the streaming APIs.

Filestream just requires a uniqueidentifier column with the ROWGUIDCOL
property to work. It doesn't matter whether this column is a key or not.
Filestream storage uses the varbinary(max) data type, so it's too big to be
a key column. It can't be used as an included column in an index either.

Hope this helps,
Bob Beauchemin
SQLskills

"Ammar S. Mitoori" <msne...@nospam.nospam> wrote in message
news:B5FD6576-00EE-4044...@microsoft.com...

[Erland Sommarskog]

Ammar S. Mitoori (msne...@nospam.nospam) writes:
> i created the table again it works fine and i added some columns to it
> but it seems its very sensitive when i rearange the columns positions in
> design view i start getting the error below so i delete it and create
> again why ?

Do not use the table designer in Mgmt Studio to modify your tables. It is
seriously buggy and there are all sorts of issues with it. In this
particular case, it does not preserve the FILESTREAM attribute when it
recreates the table. (And it has to create a new table and copy data over,
since there is no ALTER TABLE command to change column order. Then again
the Table Designer drops and recreates in many situations where it could
have used ALTER TABLE as well.)

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Mark Han[MSFT]]

Hi Msnews08

After reviewing the Bob Beauchemin and Erland Sommarskog's answer, I think
they have give you good answer and explanantion. if you have any concerns,
please let me know

Regards
Mark Han
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msd...@microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

[Neil W]

For some reason, the start of this thread has been archived off. Can
someone repeat whether it is possible to get a file handle for a filestream
column, and if so, how you do it? Thanks.

"Mark Han[MSFT]" <v-fa...@online.microsoft.com> wrote in message
news:RPq55PHI...@TK2MSFTNGHUB02.phx.gbl...

[Erland Sommarskog]

Neil W (ne...@netlib.com) writes:
> For some reason, the start of this thread has been archived off. Can
> someone repeat whether it is possible to get a file handle for a
> filestream column, and if so, how you do it? Thanks.

The answer to the original question was that you should stay away from
the Table Designer. (When the poster changed the table schema through the
table designer, the FILESTREAM property disappeared.)

As for how to access FILESTREAM data, look at the topic
"Designing and Implementing FILESTREAM Storage" in Books Online.