Solved

varbinary(max) filestream - how to insert/reference file location?

Posted on 2011-09-02
4
445 Views
Last Modified: 2012-05-12
I have the below table - when I use the below insert - and then set a full text index - it is apparent, it is just storing the file name or something as when I check the indexed words - only the document name is indexed - so my question is - when trying to use filestream and varbinary(max) - is the simple example below correct?

CREATE TABLE cptDocuments(
  [docID] [int] IDENTITY(1,1) NOT NULL,
  [document] varbinary(max) FILESTREAM NULL,
  [docExtension] varchar(30),
  [RowGuid] UNIQUEIDENTIFIER NOT NULL  ROWGUIDCOL UNIQUE DEFAULT NEWID(),
CONSTRAINT [PK_cptDocuments_docID] PRIMARY KEY CLUSTERED
(
  [docID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO cptDocuments(Document, [docExtension]  )
VALUES (
CAST('C:\inetpub\wwwroot\PDE\CPTAssistant\Archives\Updated Reporting Instruction for Hydration Infusion (Code 90760).htm' as varbinary(max))
,'.htm'
)
0
Comment
Question by:tbaseflug
  • 2
  • 2
4 Comments
 
LVL 3

Expert Comment

by:tknudsen-qec
ID: 36475072
Short answer, no.  

INSERT INTO cptDocuments(Document)
    SELECT *
    FROM OpenRowSet(BULK N'C:\inetpub\wwwroot\PDE\CPT....', SINGLE_BLOB) AS Document

You can also drop it into a variable thusly:

DECLARE @mybinary VARBINARY(MAX)
SELECT @mybinary = BulkColumn
FROM OPENROWSET(BULK N'C:\inetpub\wwwroot\PDE\CPT....', SINGLE_BLOB) AS Document

And then insert that.

HTH
0
 

Author Comment

by:tbaseflug
ID: 36475090
is there a way - say after I do that or during that - to put in a few other variables - such as 'file name here.htm' and '.htm' - the file name and the extension - as part of that insert statement in openrowset?
0
 
LVL 3

Accepted Solution

by:
tknudsen-qec earned 500 total points
ID: 36475127
Absolutely.  Its particularly clear with the second example, since you can insert your extensions PLUS the variable:

INSERT INTO MyTable (ExtensionColumn, BinaryColumn) VALUES ('.htm', @myBinary)


But with the first example, that "SELECT *" only returns one column (BulkColumn).  You could also:
"SELECT *, '.htm' as extension"
if you like.  

If I'd been using both my braincells I would have used BulkColumn in both examples.
0
 

Author Closing Comment

by:tbaseflug
ID: 36475191
Thanks!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
sql Audit table 3 47
table fragmentation 40 73
Help in SQL Full Text Search 3 28
Best way to use SSMS intellisense when querying 5 45
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now