April 14, 2009 at 3:20 pm
I threw together a really (REALLY REALLY DO NOT LAUGH I MEAN REALLY) crude cursor... I am having problems applying the OPENROWSET function syntactically. Here is what I have.
USE [BFM]
GO
/****** Object: StoredProcedure [dbo].[add_employees] Script Date: 04/14/2009 10:43:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[add_employee_photos] --@myrecno int
as begin
set nocount on
declare @image image
declare @imagepath varchar(255)
declare @fullname varchar(255)
DECLARE image_cursor CURSOR for
select (select * from OPENROWSET(BULK N "'photo_path'", SINGLE_BLOB)) as photopath
From BFM.dbo.EMPLOYEES
OPEN image_cursor
FETCH NEXT FROM image_cursor
INTO @image
While @@FETCH_STATUS = 0
Begin
insert into BFM.dbo.employees (
picture
)
FETCH NEXT FROM image_cursor
INTO @image
end
close image_cursor
deallocate image_cursor
set nocount off
end
the error is:
Msg 102, Level 15, State 1, Procedure add_employee_photos, Line 11
Incorrect syntax near 'N'.
Msg 156, Level 15, State 1, Procedure add_employee_photos, Line 23
Incorrect syntax near the keyword 'FETCH'.
Obviously I am not using that correctly. How should it read?
April 14, 2009 at 3:22 pm
table =
USE [BFM]
GO
/****** Object: Table [dbo].[Employees] Script Date: 04/14/2009 17:20:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Employees](
[first_name] [varchar](50) NULL,
[last_name] [varchar](50) NULL,
[birthday] [datetime] NULL,
[blood_type] [varchar](30) NULL,
[med_conditions] [varchar](255) NULL,
[hair_color] [varchar](25) NULL,
[eye_color] [varchar](25) NULL,
[height] [varchar](10) NULL,
[weight] [varchar](10) NULL,
[empid] [uniqueidentifier] NULL,
[print_badge] [bit] NULL,
[job_function] [varchar](255) NULL,
[job_title] [varchar](255) NULL,
[active] [bit] NULL,
[badgeid] [varchar](255) NULL,
[middle_name] [varchar](255) NULL,
[photo_url] [varchar](255) NULL,
[createdon] [datetime] NULL,
[picture] [image] NULL,
[photo_path] [varchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
There are some columns I will be removing that I used for a bulk import, and also while I am trying to figure this thing out so they are just dummy columns.
April 14, 2009 at 3:32 pm
fixed the small syntax error on my insert, but still get the error near N' so my openrowset syntax is wrong.
USE [BFM]
GO
/****** Object: StoredProcedure [dbo].[add_employees] Script Date: 04/14/2009 10:43:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[add_employee_photos] --@myrecno int
as begin
set nocount on
declare @image image
declare @imagepath varchar(255)
declare @fullname varchar(255)
DECLARE image_cursor CURSOR for
select (select * from OPENROWSET(BULK N "'photo_path'", SINGLE_BLOB)) as photopath
From BFM.dbo.EMPLOYEES
OPEN image_cursor
FETCH NEXT FROM image_cursor
INTO @image
While @@FETCH_STATUS = 0
Begin
insert into BFM.dbo.employees (
picture
)
Select @image
FETCH NEXT FROM image_cursor
INTO @image
end
close image_cursor
deallocate image_cursor
set nocount off
end
April 15, 2009 at 7:46 am
I too am having some troubles getting this update to work. There are definitely issues with your cursor but when I reworked it I discovered that the image datatype is not allowed in t-sql. It seems like the best choice for your one time update is to write some quick code. How is your skill with .net? I think you said you are pretty comfortable with vb? I wrote a piece to do something like this in c# not long ago. I can try my hand at converting it or just send you the c#. I am not very fluent in vb anymore. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 15, 2009 at 7:57 am
If you could send what you have in C# maybe that would be a starting point for me. Thanks!
April 15, 2009 at 8:29 am
Try this as a great starting point. It has all the code for you in vb.net. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 16, 2009 at 9:06 am
Assuming a one time execution, I would populate a table (tblPicturesToImport_b) with the list of names with paths to import then execute something like the following:
[P]
[font="Courier New"]
DECLARE @SQL AS VARCHAR(2000)
, @file AS VARCHAR(200)
SELECT @file = ' ' --initialize
WHILE ISNULL(@file,'') '' Begin
SELECT @file = (SELECT TOP 1 [bFilePathAndName]
FROM tblPicturesToImport_b
WHERE [bFilePathAndName] > @File
ORDER BY [bFilePathAndName])
SELECT @SQL = '
INSERT INTO [dbo].[tblBinaryData_a]
( [aFilePathAndName]
,[aFileContent]
)
SELECT [bFilePathAndName]
, (SELECT * FROM OPENROWSET(BULK N' + CHAR(39) + @file + CHAR(39) + ', SINGLE_BLOB) AS FileContent)
FROM tblPicturesToImport_b
WHERE [bFilePathAndName] =' + CHAR(39) + @file + CHAR(39)
EXEC (@SQL)
END
[/font]
[/P]
Note that [bFilePathAndName] will be populated with values like:
C:\ITC MIMS\VISimages\P1010001.JPG
April 22, 2009 at 1:19 pm
Old Hand was right. I successed to bulk image to table's field with "text" datatype using this shape of code:
UPDATE table_name
SET fild_name = (
SELECT a.*
FROM OPENROWSET(BULK 'c:\avatar.JPG', SINGLE_CLOB) AS a)
WHERE some_another_field = 1
April 22, 2009 at 1:23 pm
I developed the windows application that successfully uploads the images into the DB. My issue is with all of the employees already in the database with no images. The thing is the file name is different for each employee. The file name is (first name)_(last name).bmp for each picture, for each employee. Rather then uploading each image individually through my app, I wanna run a command that says if the image doesnt exist, find it and upload it. Does that make sense?
April 22, 2009 at 1:25 pm
Better yet, I updated the DB with a new column. Each record has a column that has the full filepath and filename. How do I work with that?
April 23, 2009 at 10:02 am
Why not just store the file path/file name in the database and keep the images on a separate directory?
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply