January 23, 2011 at 7:46 am
Dear All,
Hi ! I need a QUERY based solution to Export an Image or PDF file into Remote SQL Server Database table from local machine.
For example: -
I have an Image file on my local drive named "Text.jpg" & I have to export it in "Image" column of Table named "ImageData" exists on Remote Server (for example: - on 84.14.14.14). I have full access on this server via SQL Management studio.
Kindly guide me.
January 23, 2011 at 9:18 pm
I believe that you can use the OPENROWSET function to accomplish this task.
Satnam
January 23, 2011 at 11:18 pm
Dear Satnam,
Thanks for your reply.
I need to copy an Image or PDF file from local computer to remote sql server either in a table field or in a folder.
Can you please explain how to do the same by OpenRowSet?
January 24, 2011 at 2:38 am
Hi! Saw this thread and wanted to share what I have found in the past. . .
Here's an example that selects all data from the Customers table from the local instance of SQL Server Northwind database and from the Orders table from the Access Northwind database stored on the same computer. (***NOTE: This example assumes that Access is installed. To run this example, you must install the Northwind database.***)
USE Northwind
GO
SELECT c.*, o.*
FROM Northwind.dbo.Customers AS c
INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';'admin';'', Orders)
AS o
ON c.CustomerID = o.CustomerID ;
GO
And here is another one that is perhaps a bit closer to your need. This example creates a small table for demonstration purposes, and inserts file data from a file named Text1.txt located in the C: root directory into a varbinary(max) column. (***NOTE: This is using SQL 2008 and the AdventureWorks db with that system. You will need to play with it a bit for SQL 2005).
USE AdventureWorks2008R2
GO
CREATE TABLE myTable(FileName nvarchar(60),
FileType nvarchar(60), Document varbinary(max));
GO
INSERT INTO myTable(FileName, FileType, Document)
SELECT 'Text1.txt' AS FileName,
'.txt' AS FileType,
* FROM OPENROWSET(BULK N'C:\Text1.txt', SINGLE_BLOB) AS Document;
GO
Just play with the code a bit. You'll need to customize it to your own need.
Hope that helps!
January 24, 2011 at 2:59 am
Dear Yarger,
Hi ! In your thread all is for Local system i.e. take a file from Local system & export in SQL Server of Local System.
My requirement is that I have a file say .jpg or .pdf on my Local System & need to transfer it on a Remote SQL Server either in Table or in a Folder. To do the same I need a MS-SQL QUERY BASED solution.
January 24, 2011 at 3:17 am
First of all let me tell u that u need to connect to SQL Server Management Studio using sa login credentials to accomplish this task.
Now consider an example wherein u have a table named student which has 3 columns named student_id, student_name and student_image.
Consider the image named student.jpg is located in a folder named student which is present on the C drive of the machine.This is the image which we want to insert into the table named student.
When inserting the record we want the value of student_id to be 1 and student_name to be 'ABC'
Consider the below query:
INSERT INTO student (student_id,student_name,student_image)
SELECT 1,1,
* FROM OPENROWSET(BULK N'C:\Student\student.jpg', SINGLE_BLOB) AS student_image;
GO
Satnam
January 24, 2011 at 7:10 am
if you have access to your remote sql server then you can add your remote sql server as a LINKED SERVER on your local database server.
Then to access your tables on the remote database you will use four part name
e.g.
LINKEDSERVER.Database.dbo.ObjectName
LINKEDSERVER.Database.dbo.TableName
LINKEDSERVER.Database.dbo.ProcedureName.
Rest of the SQL will remain same.
Combine this with the other solutions suggested by other geeks and get it working.
Please read about LInked server...
http://msdn.microsoft.com/en-us/library/ms188279.aspx
Creating a Linked server
http://msdn.microsoft.com/en-us/library/ff772782.aspx#SSMSProcedure
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply