Solved

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

Posted on 2011-09-02
4
448 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Maintenance Plan 3 29
How to SQL Trace a SPECIFIC query 24 57
Syntax using Declare 4 38
TSQL previous 5 23
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…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…
A simple description of email encryption using a secure portal service. This is one of the choices offered by The Email Laundry for email encryption. The other choices are pdf encryption which creates an encrypted pdf of your email and any attachmen…

914 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

18 Experts available now in Live!

Get 1:1 Help Now