OPENROWSET Format File Dynamic
May 22, 2008
Hi,
Can someone please point me as to where I can find info for the following. I have seen this somewhere but I am unable to find it.
I am trying to do
INSERT INTO Table
SELECT ...
FROM OPENROWSET(BULK '\sharedDriveDataFile.txt',
FORMATFILE = '\sharedDriveFormatFile.txt',
FIRSTROW = 2)
AS Q
How can I make that shared drive dynamic? as such..hoping not to use dynaic SQL
INSERT INTO Table
SELECT ...
FROM OPENROWSET(BULK @SharedDrive DataFile.txt',
FORMATFILE = @SharedDrive FormatFile.txt',
FIRSTROW = 2)
AS Q
View 1 Replies
ADVERTISEMENT
Mar 2, 2014
I am trying to create an ssis package with dynamic csv file as output. and out format contains query output.
sample file name:
Unique identifier + query output + systemdate();
The expression is looking like this.
@[User::FilePath] + @[User::FileName] + ".CSV"
-- user filepath is a variable from ssis package. File name is the output from SQL query. using script task i have assigned the values to @[User::FileName] .
When I debugged the script task the value getting properly but same variable am using for Flafile destination. but its not working.
View 3 Replies
View Related
Sep 21, 2007
can someone tell me why the upper part of the dynamic sql doesn't work while the lower does? i use openrowset to connect to Oracle. thanks!
declare @abc varchar(10)
select @abc = 'table20070921'
execute ('select a.* from OPENROWSET(''MSDAORA'',''SCHEME1'';''USER'';''PASSWORD'',
''select * from ' + @abc + ' where flag = ''abc'') as a')
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'abc'.
Msg 105, Level 15, State 1, Line 2
Unclosed quotation mark after the character string ') as a'.
--------------------------------------------------------------------------------------------------------------------
declare @abc varchar(10)
select @abc = 'table20070921'
execute ('select a.* from OPENROWSET(''MSDAORA'',''SCHEME1'';''USER'';''PASSWORD'',
''select * from ' + @abc + ' where cust_num = 123456'') as a')
View 1 Replies
View Related
Oct 5, 2006
im running a dynamic query with open rowset in it
pseudocode:
@CMD=declare @ RETURN SELECT @RETURN =SUM(X) FROM OPENROWSET(....) SELECT @RETURN
EXEC @CMD
This pseudocode dipplay the result of @return
the problem:
capture @return into @myvalue outside the dynamic sql scope
something like
Select @myvalue=exec(@cmd)
I don't wanna run on ditributed transaction like this
insert mytable
exec(@cmd)
thanks,
joey
View 7 Replies
View Related
May 26, 2000
Has anyone had any experience using the openrowset function to access a dbase IV file? I can access the file using ADO from within VB but not getting anywhere using openrowset. If anyone knows the syntax or has an example it would be greatly appreciated.
Thanks in advance...
Jim
View 4 Replies
View Related
Mar 29, 2002
Can I use OPENROWSET in Query Analyzer to connect to a text file in a SELECT statement and/or an INSERT statement? I need to move data from a text file to a SQL Server db. But, I can't figure out the right parameters for the OPENROWSET method. Thanks for any help.
Gerald
View 1 Replies
View Related
May 7, 2008
Hi everyone,
I am currently using the OPENROWSET command to read a XML file.
-- Use OPENROWSET to read an XML file from the file system
SELECT @xml = BulkColumn
FROM OPENROWSET(BULK 'c:file.xml', SINGLE_BLOB) TempXML
However, the file I wish to process is being generated externally and published through a web server. The file is constantly being updated, and I need to have the latest data frequently. Is it possible to do something along the lines of:
-- Use OPENROWSET to read an XML file from the file system
SELECT @xml = BulkColumn
FROM OPENROWSET(BULK 'http://webserver/file.xml', SINGLE_BLOB) TempXML
If it is not possible, can someone recommend the best method to copy/download the file? I have considered standalone Windows Services and utilizing SQL Server. Are there significant pros or cons to either approach? Are there other methods I am not awere of? Any feedback on this would be greatly appreciated.
Thanks,
Matt K.
View 3 Replies
View Related
Aug 28, 2006
Hi There
I am using OPENROWSET to import a file from disk to a varbinary(max) column in sql server.
However as far as i can see OPENROWSET is only to import into Sql Server. However i need to reverse this as well, by that i mean export a varbinary(max) column data to a file on disk. How can i do this ? (The file is a compress text file).
How does this work for file types, i mean you can import any file as binary but when you export it back to disk will the file typr still work, will a .xls or a.exe imprted and exported like this still function 100%.
Thanx?
View 9 Replies
View Related
Mar 1, 2007
Hi everyone,
I'm trying to query an excel file and I get a mistake. The query is as follows:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:ExcelFile.xls', 'select * from Sheet1')
and I get the following error message:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'Book1'. Make sure the object exists and that you spell its name and the path name correctly.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].
I'm thanking any help that you can give.
Thanks,
Oscar.
View 7 Replies
View Related
Aug 8, 2007
I am using SQLServer 2005 SP2. I enabled the Ad Hoc Distributed Queries and DisallowAdhocAccess registry option is explicitly set to 0. Query is working fine when I remote desk to the server and execute when I run same query from my workstation I am getting following error
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
Any help is appreciated.
Thanks
--
Farhan
View 8 Replies
View Related
Mar 5, 2008
I have a .ttx file similar to this:
"xxxxxxxxx" "dc8" "184:30" "168:00" "00:00" "00:00" "00:00" "00:00" "00:00" "00:00" "00:00" "00:00" "00:00" "00:00" "00:00" 0 0 0 1
I want to read from this file from T-SQL, I saw in a previous post that its possible to do it using OPENROWSET (http://msdn2.microsoft.com/en-us/library/ms190312.aspx)
I dont have a clue what parameters i must use on the OPENROWSET T-sql command (provider name to ttx ?)
I would appreciate if anyone could give an example of how to do this
Thanks !
View 7 Replies
View Related
Mar 20, 2008
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D: esting.xls;', 'SELECT * FROM [SheetName$]') select * from pubs.dbo.authors
I am using similiar query to above to create a excel file, however for this to work, I need to create a template file which has the same columns as the authors table. Is there a way to NOT to define template columns , as I some times will not know which columns will be available... as teh query is dynamic....
View 4 Replies
View Related
Aug 3, 2007
select * from openrowset(bulk '\server1c$file.txt', SINGLE_BLOB) as t
works from sql server itself, but doesn't work from any other machine. I got
"Operating system error code 5(Access is denied.)."
I am running as the domain admin, the file.txt has full control for everyone. In server1 even log, I see Anonymous Logon.
Please help!
Thanks,
Bo
View 3 Replies
View Related
Jan 7, 2008
Hi, recently I encountered the following problem:
I tried to execute a stored procedure on the newly installed SQL 2005 Server (now on x64 Win Server 2003) which imports an Excel-File into a DB table.
We use OPENROWSET to access the Excel data. But I recognized this is dependent on Jet OLE DB which seems is not available for x64 windows.
Is there another way to import excel data using a stored procedure.
thank you in advance, rene
View 12 Replies
View Related
May 8, 2006
Hi,
I have to write a query that queries a database table and a text file wich is delimited using fixed width values. I have seen some examples using access or another databse but not much about a text file. Is this the best way? Idon't want to set up a linked server. I want everything to be within the query.
My understanding is that I should be able to do it since a text file can be a valed OLE DB datasource, but I am having trouble finding example syntax. Can all my connection info be passed with the OPENROWSET function? Can I do it by just writing a query? Or do I have to set up and configure other things?
Any guidence, tips, advice, examples, or links of syntax appreciated.
Mike
View 1 Replies
View Related
May 4, 2007
Hi
i want to access a CSV file using OpenRowSet function in SQL Server 2005.
Anyone having any idea; would be of great help.
Regards,
Salman Shehbaz.
View 8 Replies
View Related
Nov 16, 2006
Hi All,
I am stuck at one place, where I have to convert CSV format file data into SAP IDOC format file. In SSIS we don't have any such SAP adapter (though we have .NET Data Provider for mySAP suite [SSIS SAP Adapter] but this is still not fully supported by Microsoft, plus it doesn't have feature to convert data into IDOC format) that can do this. Can someone here please provide me some pointers on any third party adapters available in market to do this job or if anyone has already developed some custom approach to achieve this task?
Your quick response on this is highly appreciated.
Regards,
Kuldeep Chauhan
View 2 Replies
View Related
May 14, 2014
I have a directory with images and a table in my DB with the path of each file. The main application allow me to create reports where I can display an image, so I was thinking to use a query like:
SELECT [ID]
,[PT_CODE]
,[FILE_PATH],
CASE WHEN [FILE_PATH] IS NOT NULL THEN
(SELECT * FROM OPENROWSET(BULK [FILE_PATH], SINGLE_BLOB) TT)
END
AS IMAGE_LOADED
FROM [DB].[dbo].[TABLE_MR_FILES]But I keep getting the error:
Incorrect syntax near 'FILE_PATH'.I have try multiple combinations without luck to make the OPENROWSET read the path stored in the column [FILE_PATH]. What am I missing?
Note: I am using MSSQL 2012. I don't want to import the images into the DB just load them in the fly as needed by the report runned from the application. I have full access to the DB so if a store procedure is the solution I can go with it.
View 4 Replies
View Related
Jun 12, 2015
I have a test server (TEST1) running SQL 2012 and Windows 2012R2. One of the developers wants to use OPENROWSET to read in data from an Excel 2010 file (an xlsx file).
I have loaded the Microsoft Drivers in "AccessDatabaseEngine_64.exe" and enabled the Ad Hoc Distributed Queries option in SQL.
This is the sample code we are working with:
SELECT
X.MEMBID
FROM OPENROWSET(
'MSDASQL',
'Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=etworkserverFolder1Folder2MEMBIDs.xlsx',
'SELECT * FROM [Sheet1$]'
) AS X
I can run the sample query from my laptop with SSMS (I have admin rights) and I can also run it as SA from my laptop. So all is good, right?
But if I RDC into TEST1, I cannot run the query. I get this error:
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Excel Driver] Your network access was interrupted. To continue, close the database, and then open it again.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Excel Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x574 Thread 0xb74 DBC 0x1d07f08 Excel'.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Excel Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x574 Thread 0xb74 DBC 0x1d07f08 Excel'.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".But wait! It gets better.
I can run the query as SA from TEST1.
And of course, the developer can't run it either.
And it works fine in the production server.
I'm thinking the basics are there but something isn't right in some permission somewhere.
View 9 Replies
View Related
Mar 3, 2007
Maybe it worked once, but in most time it doesn't work, query like below
select top 10 *
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\ws8webablefilessitefiles4000010
eibcactive.xls',
'select * from [crap2$]')
I got error
OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".
but the same query can run without any problem on a SQL 2000 server run on a server in the same network.
Any idea?
View 12 Replies
View Related
Mar 3, 2008
i want to do something like this
i know it does not work this way, i want other way to do this
declare @a int
set @a = 2
select convert(numeric(18,@a), 76.3823)
View 7 Replies
View Related
Jan 2, 2008
Hey All,
Similar to a previous post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=244646&SiteID=1), I am trying to import data into a SQL Table.
I am trying to program a small application that will import product data obtained through suppliers via CD-ROM. One supplier in particular uses Fixed width colums, and data looks like this:
Example of Data
0124015Apple Crate 32.12
0124016Bananna Box 12.56
0124017Mango Carton 15.98
0124018Seedless Watermelon 42.98
My Table would then have:
ProductID as int
Name as text
Cost as money
How would I go about extracting the data with an XML Format file? I am stumbling over how to tell it where to start picking up data for a specific column.
Is there any way that I could trim the Name column (i.e.: "Mango Carton " --> "Mango Carton")?
I don't know if it makes any difference, but I've been calling SQL from my code by doing this:
Code in C# Form
SqlConnection SqlConnection = new SqlConnection(global::SQLClients.Properties.Settings.Default.ClientPhonebookConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO PhonebookTable(Name, PhoneNumber) VALUES('" + txtName.Text.ToString() + "', '" + txtPhoneNumber.Text.ToString() + "')";
cmd.Connection = SqlConnection;
SqlConnection.Open();
cmd.ExecuteNonQuery();
SqlConnection.Close();
RefreshData();
I am running Visual Studio C# Express 2005 and SQL Server Express 2005.
Thanks for your time,
Hayden.
View 1 Replies
View Related
Jul 23, 2005
Hi,I am trying to use BULK INSERT with format file. All of our data hasfew bytes of header in the data file which I would like to skip beforedoing BULK INSERT.Is it possible to write format file to skip these few bytes ofheader before doing BULK INSERT? For example, I have a 1 GB data filewith 1000 byte header. Except for first 1000 bytes, rest of the data isgood for BULK INSERT.Thanks in advance. Sorry if it is really a dumb question as I am newto BULK INSERT and practicing still.Bob
View 7 Replies
View Related
Jul 24, 2015
Need to know how I can get the dynamic filename created in the FlatFile destination for insert into a package audit table?
Scenario: Have created a package that successfully outputs Dynamiclly named flat files { Format: C:Test’Comms_File_’ + ‘User::FileNumber’+’_’+Date +’.txt’
E.g.: Comms_File_1_20150724.txt, Comms_File_2_20150724.txt etc} using Foreach Loop Container :
* Enumerator Set to: “Foreach ADO Enumerator” with the ADO object source variable selected to identify how many total loop iterations there are i.e. Let’s say 4 thus 4 files to be created
*Variable Mappings : added the User::FileNumber – indicates which file number current loop iteration is i.e. 1,2,3,4
For the DataFlow task have a OLDBSource and a FlatFile Destination where Flat File ConnectionString is set up as:
@[User::Output_Path] + "Comms_File"+ @[User:: FileNumber] +"_" + replace((DT_WSTR, 10) (DT_DBDATE) GETDATE(),"-","")+ ".txt"
All this successfully creates these 4 files:
Comms_File_1_20150724.txt, Comms_File_2_20150724.txt, Comms_File_3_20150724.txt, Comms_File_4_20150724.txt
Now the QUESTION is how do I get these filenames as I need to insert them into a DB Audittable. The audit table looks like this:
CREATE TABLE dbo.MMMAudit
(
AuditID INT IDENTITY(1, 1) NOT NULL,
PackageName VARCHAR(100) NULL,
FileName VARCHAR(100) NULL,
LoadTime DATETIME NULL,
NumberofRecords INT NULL
)
To save the Filename & how many records in each file in our Audit Table, am using an Execute SQL Task and configuring it as this:
Execute SQL Task
Parameter mapping - Mapped the User Variable (RecordsInserted) and System Variable( PackageName) to Insert statement as shown below
SQLStatement: INSERT INTO [dbo].[MMMAudit] (
PackageName,NumerofRecords,LoadTime)
(?,?.GETDATE)
Again this all works terrific & populates the dbo.MMMAudit table as shown below BUT I also need to insert the respsctive file name – How do I do that?
AuditID PackageName FileName NumberOfRecords
1 MMM NULL 12
2 MMM NULL 23
3 MMM NULL 14
4 MMM NULL 1
View 2 Replies
View Related
Oct 9, 2006
I am having an issue with the File System Task.
I was wondering if there is a way to 'Move File' with the File System Task inside of a For Each Loop container but to dynamically set the Destination path variable.
Currently, this is what I have:
FileDestinationPath variable - set to C:TestFiles
FileSourcePath variable - set to C:TestFiles
FileNameAndLocation variable - set to blank
For Each Loop Container Iterates through a folder C:TestFiles that has .txt files in it with dates in the file name. Ex: Test_09142006.txt. Sets the file path (fully qualified) to the Variable Mapping FileNameAndLocation.
Script Task (within For Each Loop, first step) Sets the FileDestinationPath to the correct dated folder within C:TestFiles. For example, if the text files I want to move are for the 14th of September, it takes FileDestinationPath and appends the date folder to the end of it. The text files have a date in the file name (test_09142006.txt) and I am picking this apart (from FileNameAndLocation in the For Each Loop) to get the folder date. (dts.Variables(User::FileDestinationPath?).Value = dts.Variables(User::FileDestinationPath?).Value & ? Month & _? & Day & _? & Year & ?) which gives me C:TestFiles 9_14_2006?.
File System Task (within For Each Loop, second step) This is where the action is supposed to occur. I want it to take the FileDestinationPath and move the FileNameAndLocation file (from the For Loop) into this folder for each run.
Now as for my problem. I want this package to run everyday but it has to set the FileDestinationPath variable dynamically according to that days date. Basically, how do I get this to work since I cant hard code the destination path variable from the start? I have the DestinationVariable on the File System Task set to the FileDestinationPath variable, after the script task builds it. However, using FileNameAndLocation as the SourceVariable on my File System Task tells me that the Variable FileNameAndLocation? is used as a source or destination and is empty.?
Let me know if I need to clarify further...I may be missing something very simple. Any help would be greatly appreciated!
View 10 Replies
View Related
Jan 16, 2008
I have a txt file with format as following
MailAddress:Kienpt@ifi.local
DomainName:ETH2K
[Date]2007/12/27 15:02:50 [Operation]
[Date]2007/12/27 15:02:50 [Operation]
[Date]2007/12/27 15:02:50 [Operation]
I want to use File Flat Connection to analyse format of this file. And i want each record after analysing include 4 fields as folowing:
- MailAddress, DomainName, Date, Operation
(Mail Address and DomainName is same in each record)
Can you help me?
View 1 Replies
View Related
Feb 27, 2008
Hi All,
I have a source files folder where the files generated everyday.
My goal is pick the latest file and copy this single file to another folder.
I used the Foreach loop container and got the latest file and stored the file name to a varible i.e. LatestFile
Then i want to use the File System Task to copy this to the destination.
On the beginning, I could not setup the Latestfile since I don't its name then, so when I setup the Source Connection property of the File system task, it is not allowed to leave the SourceVarible as blank!
Any suggestion?
Thanks
Micor
View 3 Replies
View Related
Mar 28, 2006
Hi,
we have one requirement to run the package daily basis.
The package should run at specific time on that day.
we are using windows schedular for that.
we will have one new flatfile everyday.
Is there any process to attach this file to flat file source dynamically?
The requirement is,
The flat file should be able to read the new flatfile everyday.
we have no option change it manually, the flatfile source should have to take the file automatically at that time.
So that it can take that flatfile and load it into database table.
View 1 Replies
View Related
Feb 20, 2007
Hi,
I want to retrive the values from the database 'northwind' and then i want to store the backup files in "D:/Sample/north_database.bak" format(local machine).
I retrive the database values in .txt,XML format. Now i want to take in .bak format.
give the Suitable solution for this.
Subashini.G
View 4 Replies
View Related
Mar 10, 1999
Backend: MSSQL 65
My datafiles are bcp out from Syabse 11.
Because the order of fields are different, I need to use format file with BCP.
I generated my fmt files by using MSSQL's bcp utility with the instructions from SQL Server books onLIne
(section: Using a Format File to Selectively Copy data)
I cannot bcp in with format file. I got
Starting copy...
DB-LIBRARY error:
Unexpected EOF encountered in BCP data-file.
DB-LIBRARY error:
Unexpected EOF encountered in BCP data-file.
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total = 50
Since the datafile is small, I manually shift the order.
Then, I bcp it in without format file and it worked fine.
Now I got data in my SQL Server
I thought it may be the datafile. Though it's text file, it's generated by Sybase bcp utility. :-)
So, I bcp out the data to a new ASCII file. I change the ID to make those records different.
Then, I re-generate the format file.
Now, I have everything generated by MSSQL. I tried again and I got the same error.
This table contains 1 Blob field and 4 varchar(255) fields. Will this be a problem?
We have to solve this problem. When our applications go live, the datafiles will be huge and we cannot edit it manually.
Please advise
Thanks in advance.
Elizabeth Pickering
View 1 Replies
View Related
Feb 15, 2005
set @date = getdate()
set @month = convert (varchar[2], datepart(mm,@date))
gives me 2 instead of 02 for month of February
any clues ?
View 3 Replies
View Related
Jan 27, 2004
What are format files and how do they help us in Bulk Load????
I referred to many issues send to the forum but could not get sufficient information on this.
My main issue is to bulk insert from a .CSV file in hte following format
12,23,"blrnnr","Address",45677,"bangalore".............68 columns
After bulk insert I am getting the double qoutes also into the table.
Now my problem is, How do I remove these double qoutes?
I don't want to use replace() to remove these quotes!!!!!!
I have attached a Screen Shot to describe this issue with this mail
Any information is appretiated regarding this issue.
Thanking you
Naveen
View 1 Replies
View Related
Mar 8, 2006
I am trying to copy the data in excel file into a table using the bcpand this is the code that I have. However the bcp utility does notseem to create a format file, which I thought it should do. I amprobably going about this all wrong so any help would be useful.exec master..xp_cmdshell '(FOR %i IN ("E:WUTemp*") DO (bcp#ProspectImportTest in "%i" -fE:WUTempProspect.fmt)'bulk insert #ProspectImportTest from 'E:WUTemp*."' with (formatfile ='E:WUTempProspect.fmt')Thanks.KR
View 2 Replies
View Related
