Mixing
Update varbinary column with latest file using filepath column from record
I am trying to update a series of Document_Files that are stored in the following table.
Document_File:
- FileID int
- TypeID int
- FilePath nvarchar(255)
- FileData varbinary(max)
I am trying to update the FileData column for each record of a specific type using the FilePath to locate the BLOB to be imported.
I can update them one at a time using the following:
UPDATE Document_File
SET FileData = (SELECT * FROM OPENROWSET(
BULK 'C:ReportsReport - District1.xlsx', SINGLE_BLOB) AS T)
WHERE FileID = 123456
I've attempted to loop through the series of records of the desired file type using the following:
DECLARE @MyFile varchar(100)
DECLARE @LoopCounter int
DECLARE @FileID varchar(255)
DECLARE @isExists int
SET @LoopCounter = (SELECT COUNT(FilePath) FROM Document_File WHERE TypeID = 123)
SET @FileID = (SELECT TOP 1 FileID FROM Document_File WHERE TypeID = 123) -1
WHILE @LoopCounter > 0
BEGIN
SET @MyFile = (SELECT TOP 1 FilePath FROM Document_File WHERE TypeID = 123
AND FileID > @FileID)
EXEC master.dbo.xp_fileexist @MyFile, @isExists OUTPUT
IF(@isExists = 1)
BEGIN TRY
BEGIN TRAN
UPDATE Document_File
SET FileData = (SELECT * FROM OPENROWSET(BULK N'@MyFile', SINGLE_BLOB) AS T)
WHERE TypeID = 123 AND FileID > @FileID
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 BEGIN
ROLLBACK TRAN
END
END CATCH
IF @@TRANCOUNT > 0 BEGIN
COMMIT TRAN
END
SET @LoopCounter = @LoopCounter -1
SET @FileID = @FileID +1
END
I am getting an error stating:
Cannot bulk load. The file "@MyFile" does not exist
Does anyone know what I'm doing wrong and if there is a better way to accomplish this?
sql-server tsql sql-update bulk-load varbinarymax edited Nov 21 '18 at 5:22
marc_s
575k12811101257
asked Nov 21 '18 at 0:49
ryeguyryeguy
62
add a comment |
I am trying to update a series of Document_Files that are stored in the following table.
Document_File:
- FileID int
- TypeID int
- FilePath nvarchar(255)
- FileData varbinary(max)
I am trying to update the FileData column for each record of a specific type using the FilePath to locate the BLOB to be imported.
I can update them one at a time using the following:
UPDATE Document_File
SET FileData = (SELECT * FROM OPENROWSET(
BULK 'C:ReportsReport - District1.xlsx', SINGLE_BLOB) AS T)
WHERE FileID = 123456
I've attempted to loop through the series of records of the desired file type using the following:
DECLARE @MyFile varchar(100)
DECLARE @LoopCounter int
DECLARE @FileID varchar(255)
DECLARE @isExists int
SET @LoopCounter = (SELECT COUNT(FilePath) FROM Document_File WHERE TypeID = 123)
SET @FileID = (SELECT TOP 1 FileID FROM Document_File WHERE TypeID = 123) -1
WHILE @LoopCounter > 0
BEGIN
SET @MyFile = (SELECT TOP 1 FilePath FROM Document_File WHERE TypeID = 123
AND FileID > @FileID)
EXEC master.dbo.xp_fileexist @MyFile, @isExists OUTPUT
IF(@isExists = 1)
BEGIN TRY
BEGIN TRAN
UPDATE Document_File
SET FileData = (SELECT * FROM OPENROWSET(BULK N'@MyFile', SINGLE_BLOB) AS T)
WHERE TypeID = 123 AND FileID > @FileID
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 BEGIN
ROLLBACK TRAN
END
END CATCH
IF @@TRANCOUNT > 0 BEGIN
COMMIT TRAN
END
SET @LoopCounter = @LoopCounter -1
SET @FileID = @FileID +1
END
I am getting an error stating:
Cannot bulk load. The file "@MyFile" does not exist
Does anyone know what I'm doing wrong and if there is a better way to accomplish this?
sql-server tsql sql-update bulk-load varbinarymax edited Nov 21 '18 at 5:22
marc_s
575k12811101257
asked Nov 21 '18 at 0:49
ryeguyryeguy
62
add a comment |
I am trying to update a series of Document_Files that are stored in the following table.
Document_File:
- FileID int
- TypeID int
- FilePath nvarchar(255)
- FileData varbinary(max)
I am trying to update the FileData column for each record of a specific type using the FilePath to locate the BLOB to be imported.
I can update them one at a time using the following:
UPDATE Document_File
SET FileData = (SELECT * FROM OPENROWSET(
BULK 'C:ReportsReport - District1.xlsx', SINGLE_BLOB) AS T)
WHERE FileID = 123456
I've attempted to loop through the series of records of the desired file type using the following:
DECLARE @MyFile varchar(100)
DECLARE @LoopCounter int
DECLARE @FileID varchar(255)
DECLARE @isExists int
SET @LoopCounter = (SELECT COUNT(FilePath) FROM Document_File WHERE TypeID = 123)
SET @FileID = (SELECT TOP 1 FileID FROM Document_File WHERE TypeID = 123) -1
WHILE @LoopCounter > 0
BEGIN
SET @MyFile = (SELECT TOP 1 FilePath FROM Document_File WHERE TypeID = 123
AND FileID > @FileID)
EXEC master.dbo.xp_fileexist @MyFile, @isExists OUTPUT
IF(@isExists = 1)
BEGIN TRY
BEGIN TRAN
UPDATE Document_File
SET FileData = (SELECT * FROM OPENROWSET(BULK N'@MyFile', SINGLE_BLOB) AS T)
WHERE TypeID = 123 AND FileID > @FileID
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 BEGIN
ROLLBACK TRAN
END
END CATCH
IF @@TRANCOUNT > 0 BEGIN
COMMIT TRAN
END
SET @LoopCounter = @LoopCounter -1
SET @FileID = @FileID +1
END
I am getting an error stating:
Cannot bulk load. The file "@MyFile" does not exist
Does anyone know what I'm doing wrong and if there is a better way to accomplish this?
sql-server tsql sql-update bulk-load varbinarymax edited Nov 21 '18 at 5:22
marc_s
575k12811101257
asked Nov 21 '18 at 0:49
ryeguyryeguy
62
I am trying to update a series of Document_Files that are stored in the following table.
Document_File:
- FileID int
- TypeID int
- FilePath nvarchar(255)
- FileData varbinary(max)
I am trying to update the FileData column for each record of a specific type using the FilePath to locate the BLOB to be imported.
I can update them one at a time using the following:
UPDATE Document_File
SET FileData = (SELECT * FROM OPENROWSET(
BULK 'C:ReportsReport - District1.xlsx', SINGLE_BLOB) AS T)
WHERE FileID = 123456
I've attempted to loop through the series of records of the desired file type using the following:
DECLARE @MyFile varchar(100)
DECLARE @LoopCounter int
DECLARE @FileID varchar(255)
DECLARE @isExists int
SET @LoopCounter = (SELECT COUNT(FilePath) FROM Document_File WHERE TypeID = 123)
SET @FileID = (SELECT TOP 1 FileID FROM Document_File WHERE TypeID = 123) -1
WHILE @LoopCounter > 0
BEGIN
SET @MyFile = (SELECT TOP 1 FilePath FROM Document_File WHERE TypeID = 123
AND FileID > @FileID)
EXEC master.dbo.xp_fileexist @MyFile, @isExists OUTPUT
IF(@isExists = 1)
BEGIN TRY
BEGIN TRAN
UPDATE Document_File
SET FileData = (SELECT * FROM OPENROWSET(BULK N'@MyFile', SINGLE_BLOB) AS T)
WHERE TypeID = 123 AND FileID > @FileID
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 BEGIN
ROLLBACK TRAN
END
END CATCH
IF @@TRANCOUNT > 0 BEGIN
COMMIT TRAN
END
SET @LoopCounter = @LoopCounter -1
SET @FileID = @FileID +1
END
I am getting an error stating:
Cannot bulk load. The file "@MyFile" does not exist
Does anyone know what I'm doing wrong and if there is a better way to accomplish this?
sql-server tsql sql-update bulk-load varbinarymax sql-server tsql sql-update bulk-load varbinarymax edited Nov 21 '18 at 5:22
marc_s
575k12811101257
asked Nov 21 '18 at 0:49
ryeguyryeguy
62
edited Nov 21 '18 at 5:22
marc_s
575k12811101257
asked Nov 21 '18 at 0:49
ryeguyryeguy
62
edited Nov 21 '18 at 5:22
marc_s
575k12811101257
edited Nov 21 '18 at 5:22
marc_s
575k12811101257
edited Nov 21 '18 at 5:22
marc_s
575k12811101257
575k12811101257
asked Nov 21 '18 at 0:49
ryeguyryeguy
62
asked Nov 21 '18 at 0:49
ryeguyryeguy
62
asked Nov 21 '18 at 0:49
ryeguyryeguy
62
62
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
OPENROWSET doesn't accept parameters so your line:
(SELECT * FROM OPENROWSET(BULK N'@MyFile', SINGLE_BLOB)
fails, both because you've quoted the parameter name, and because OPENROWSET doesn't allow parameters.
For this approach to work you have to use dynamic SQL, i.e. dynamically build the SQL string and then execute it.
Here is an example.
answered Nov 21 '18 at 1:27
Dale BurrellDale Burrell
3,14932351
Yes this works for capturing the varbinary data but I can't seems to pass it into an UPDATE statement.
– ryeguy
Nov 22 '18 at 20:06
I've only shown you the select part of your update, you can make your entire update statement dynamic. If that is what you have tried to do please post your code.
– Dale Burrell
Nov 22 '18 at 20:39
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53403787%2fupdate-varbinary-column-with-latest-file-using-filepath-column-from-record%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
OPENROWSET doesn't accept parameters so your line:
(SELECT * FROM OPENROWSET(BULK N'@MyFile', SINGLE_BLOB)
fails, both because you've quoted the parameter name, and because OPENROWSET doesn't allow parameters.
For this approach to work you have to use dynamic SQL, i.e. dynamically build the SQL string and then execute it.
Here is an example.
answered Nov 21 '18 at 1:27
Dale BurrellDale Burrell
3,14932351
Yes this works for capturing the varbinary data but I can't seems to pass it into an UPDATE statement.
– ryeguy
Nov 22 '18 at 20:06
I've only shown you the select part of your update, you can make your entire update statement dynamic. If that is what you have tried to do please post your code.
– Dale Burrell
Nov 22 '18 at 20:39
add a comment |
OPENROWSET doesn't accept parameters so your line:
(SELECT * FROM OPENROWSET(BULK N'@MyFile', SINGLE_BLOB)
fails, both because you've quoted the parameter name, and because OPENROWSET doesn't allow parameters.
For this approach to work you have to use dynamic SQL, i.e. dynamically build the SQL string and then execute it.
Here is an example.
answered Nov 21 '18 at 1:27
Dale BurrellDale Burrell
3,14932351
Yes this works for capturing the varbinary data but I can't seems to pass it into an UPDATE statement.
– ryeguy
Nov 22 '18 at 20:06
I've only shown you the select part of your update, you can make your entire update statement dynamic. If that is what you have tried to do please post your code.
– Dale Burrell
Nov 22 '18 at 20:39
add a comment |
OPENROWSET doesn't accept parameters so your line:
(SELECT * FROM OPENROWSET(BULK N'@MyFile', SINGLE_BLOB)
fails, both because you've quoted the parameter name, and because OPENROWSET doesn't allow parameters.
For this approach to work you have to use dynamic SQL, i.e. dynamically build the SQL string and then execute it.
Here is an example.
answered Nov 21 '18 at 1:27
Dale BurrellDale Burrell
3,14932351
OPENROWSET doesn't accept parameters so your line:
(SELECT * FROM OPENROWSET(BULK N'@MyFile', SINGLE_BLOB)
fails, both because you've quoted the parameter name, and because OPENROWSET doesn't allow parameters.
For this approach to work you have to use dynamic SQL, i.e. dynamically build the SQL string and then execute it.
Here is an example.
answered Nov 21 '18 at 1:27
Dale BurrellDale Burrell
3,14932351
answered Nov 21 '18 at 1:27
Dale BurrellDale Burrell
3,14932351
answered Nov 21 '18 at 1:27
Dale BurrellDale Burrell
3,14932351
answered Nov 21 '18 at 1:27
Dale BurrellDale Burrell
3,14932351
3,14932351
Yes this works for capturing the varbinary data but I can't seems to pass it into an UPDATE statement.
– ryeguy
Nov 22 '18 at 20:06
I've only shown you the select part of your update, you can make your entire update statement dynamic. If that is what you have tried to do please post your code.
– Dale Burrell
Nov 22 '18 at 20:39
add a comment |
Yes this works for capturing the varbinary data but I can't seems to pass it into an UPDATE statement.
– ryeguy
Nov 22 '18 at 20:06
I've only shown you the select part of your update, you can make your entire update statement dynamic. If that is what you have tried to do please post your code.
– Dale Burrell
Nov 22 '18 at 20:39
Yes this works for capturing the varbinary data but I can't seems to pass it into an UPDATE statement.
– ryeguy
Nov 22 '18 at 20:06
Yes this works for capturing the varbinary data but I can't seems to pass it into an UPDATE statement.
– ryeguy
Nov 22 '18 at 20:06
I've only shown you the select part of your update, you can make your entire update statement dynamic. If that is what you have tried to do please post your code.
– Dale Burrell
Nov 22 '18 at 20:39
I've only shown you the select part of your update, you can make your entire update statement dynamic. If that is what you have tried to do please post your code.
– Dale Burrell
Nov 22 '18 at 20:39
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53403787%2fupdate-varbinary-column-with-latest-file-using-filepath-column-from-record%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
