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
I need to write data returned by a stored proc to an XML file. The resultant file will then be consumed by an external application. The file has to adhere to a specific schema. Is there a way to do this through SSIS? Is .NET CLR a better option?
I need to write data returned by a stored proc to an XML file. The resultant file will then be consumed by an external application. The file has to adhere to a specific schema. Is there a way to do this through .NET CLR in SQL Serer 2005? Is SSIS a better option? Thanks.
I need to export data from a table of SQL server 2000 database, into an Excel 2000 sheet. I tried following query from the sql query analyzer
Code SnippetSELECT * INTO [Excel 4.0;Database=F:ew.xls].[sheet1] FROM tab1
It gave me following error
Code SnippetServer: Msg 2760, Level 16, State 1, Line 1 Specified owner name 'Excel 4.0;Database=F:ew.xls' either does not exist or you do not have permission to use it.
The F:ew.xls file is present and it has no additional security applied, hence I think it should be writable.
Please tell me where am I doing wrong.
I don't know if this is the correct news group or not. Let me know if I have to post to some other news group.
I want to check a table to see what rows have been updated today, then write to a text file some data from the selected rows; then I want to automate this (DTS package? TSQL stored procedure job?) to run every night at midnight. Is the DTS Wizard the best way, that's what I did, but have not confirmed that it is writing a new text file every day, and does each new version write over the old one?
INPUT: A table contains 2 columns with data i need. column A=Filename and column B=FileContent
PROCESS: I need to loop through ea record in the table and retrieve columns A and B. Then for ea column i need to write the Content hold in column B into File hold in column A.
I so far found out, that i need a Execute SQL Task in Control Flow querying the table and get columns A and B into 2 variables, plus a 3rd var holding the object. Then the output goes into a Foreach Loop Container. From this point i don't know how to continue. I tried to put a Data Flow Task inside the Foreach Loop, but couldn't find out how i now get the 2 variables to the Data Flow Task and use them to for the file to be written and the content to be placed in the file.
Is there any example similiar to that so i could learn how to start on that?
Hello All Just wondered if someone could help me with a bit of T-SQL, i have a application in ASP.NET/VB that allows the user to update a message board by clicking a button "update" this in turn triggers my Stored Procedure for inserting this data into a table, which works great. It inserts the data into its respective fields and also takes The Title, Line 1, Line 2 and so on and creates a XML file (Using FOR XML) which is stored in the same Table under a column call XML_Data. Which again works great. My problem now is how do i output this XML_Data to an actual XML file that is on my local machine, i.e. It be created in say C:Inetpubwwwrootxmlfiles("xml file name inserted here from another column that holds xml file name").xml Any help on this would be greatfully apreciated Thanks In Advance Neil
I need to write a process to get file size in kb and record count in a file. I was planning on writing a c# console app that takes the file path and name as a param however should i use a CLR?
I cant put a script in the ssis when it's bringing the file down because it has been deemed that we only use ssis for file consumption.
Is there any real purpose to the File Connection Manager? Not the Flat File Connection Manager, just the plain old File Connection Manager.
I have one in my SSIS package because I thought it might make writing to a file easier. But from what I can tell, it doesn't really do much. I have to pull the file name from the connection manager and basically open and manage the file myself in my scripts.
I went this route because I have two scripts that write to the file in turn... one writing out parent records and the other inserting the child records after each parent.
The script looks like this:
Code Block Dim outFile As String outFile = Dts.Connections("TestFile.LDIF").AcquireConnection(Dts.Transaction).ToString()
Dim sw As New StreamWriter(outFile, True) sw.WriteLine() sw.WriteLine("dn: " & Dts.Variables("GroupID").Value.ToString()) sw.WriteLine("changeType: add")
sw.Close()
I have to close the connection every time because the other script uses the file. It seems that this is quite inefficient. Am I using the File Connection Manager incorrectly? What's a more appropriate way to do this?
I could open the file in a different script and store the StreamWriter object in a global variable, I suppose...
Hi, How can I read a file on the server and insert it into my SQL Server database. The user is not uploading the file, but it already exists on the Server. Thanks
I need help please. I have a image field in SQL called AttachData. I need to retrieve the information and save it on my computer. I tried the following but it fails on the line 7. How can I write the data from sql to my computer? Here is my table in sql: CREATE TABLE [dbo].[MailAttachment]( [MsgID] [int] NOT NULL, [AttachName] [nvarchar](255) COLLATE Hebrew_CI_AS NULL, [AttachType] [nvarchar](255) COLLATE Hebrew_CI_AS NULL, [AttachSize] [int] NOT NULL DEFAULT (0), [AttachCompSize] [int] NOT NULL DEFAULT (0),[AttachData] [image] NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]1 Dim s As New _ 2 System.IO.FileStream("c:emails" _ 3 & "" & AttachName.ToString, System.IO.FileMode.CreateNew,System.IO.FileAccess.Write) 4 6 7 ' s.Write(CType(AttachData, Byte()), 0, CInt(AttachSize)) 8 10 11 s.Close()
I don't have direct access to the SQL server. I have to write/read through a class I recieved from the IT department. The class also had an example in it but I can't get it to work correctly.
I get an error: Object reference not set to an instance of an object. Line 35
Dim clsWDD As New WebDevDB.WebDevDB Dim intLeng As Int32 Dim intLock As Int32
Dim strAppl As String = "MyApplication" Dim strData As String = "This is my text data!"
I am using bcp to write from a query or table into a text file from a stored procedure. No problem. However, what do I do if I want to write to a text file from another stored procedure which returns a record set? Any help gratefully received. thanks.
I have a request to write to a file whenever new record added to the table.
When records insert row by row, it goes well. but when more than 2 session insert at the same time, sometimes it duplicate some record in the file. I try to add synchonize code ( like lock , Monitor) but it doesn't work. any idea ?
Hi, I m new to SQL,I have created a Stored Procedure that writes a file on SQL server,but I want that it should write file on another machine.How can it be done.
May i know whether i can write some text/string to a file from inside a stored procedures? Currently i can retrieve data from a query and use bcp to write it to a file but i need to add some others text to the same file. Thanks for any reply!
I've created SQL Server job that executes my IS package in regular schedule, is there a way to redirect IS logging (Progress tab when the package is executed from VS) to flat file in same path as my packge.
When my job fails I want to know wich particular step fail and progress tab olso contain usefull info like the duration for each step.
basically i am trying to create a program wherein after saving a new transaction to the sql database, the fields saved will be retrieved and then written to a text file.
i read a thread here which is similar to what i am trying to do but it was in xml format..
Hi I wrote a clr storred procedure that use fileopen, fileput an fileclose to write a report from a sql 2005 database but I don't know how to give it permissions to do its job.
Partial Public Class StoredProcedures <Microsoft.SqlServer.Server.SqlProcedure> Public Shared Sub PriceSum( ByVal intFecha as sqlInt32 )
dim strFecha as string strFecha = cstr(intFecha)
Dim f As New FileIOPermission(PermissionState.none) f.AllLocalFiles = FileIOPermissionAccess.write f.Demand()
FileOpen(1, "G:AuVeJAPParchivo.txt", OpenMode.Random, OpenAccess.ReadWrite, OpenShare.LockReadWrite, 10) Using connection As New SqlConnection("context connection=true") Connection.Open() dim strConsulta as string strConsulta = "declare cur_unidades cursor for select id_tda_vdp from tda_venta_departamental_vdp where date_operacion_vdp = " & strFecha & " group by id_tda_vdp order by id_tda_vdp"
Dim command As New SqlCommand(strConsulta, connection) Dim reader As SqlDataReader reader = command.ExecuteReader() using reader Dim intKon as integer intKon = 0
While reader.Read() fileput(1, reader.GetSqlstring(0) & chr(0), intKon) intKon = intKon + 1 End While
End Using End Using fileClose(1) End Sub End Class
As you could see, the report needs a chr(0) at the end of each line, thats why I spent a lot of time learning clr/sql, but when I finally success in crate the assembly an procedure in sql 2005 it gives me this frustrating error:
Msg 6522, Level 16, State 1, Procedure prueba1, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'prueba1':
System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
What I am trying to do is very simple. I just want to run 2 stored procedures, then have the output from both sp's written to a single file.
I created a Data Flow task, then put 2 OLE DB Source adapters in the Data Flow task, one for each stored procedure.
Next, I dropped a Flat File destination object onto the page. However, I can only connect ONE arrow from one of the OLE DB Source adapters to the Flat File destination. It won't let me connect both.
What am I doing wrong here? How can I accomplish what I am trying to do here?
I have a sp which saves the necessary information regarding the status of action(whether success or failure, rows affected etc) to a log table say( StatusLog )After this, I was sending a database mail with information taken from the log table via sp_send_dbmail. Now I would like to write the status information to a 'txt' file instead of sending via mail.How can I write to a text file from a stored procedure in ms sql server 2005?
Can you, and if yes, how do you, write an xml file using a stored procedure. For example the sp below writes this new record to a sql table. How would I change it to write it to an xml file ?
