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?
View 1 RepliesHi,
I tried to backup the master key by the following syntax :
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mypassword'
BACKUP MASTER KEY TO FILE = 'c: empmaster' ENCRYPTION BY PASSWORD = 'mypassword'
but it failed and i got the following message:
Cannot write into file 'c: empmaster'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.
NB: I am using the "sa" user to execute this command.
I know that we have a security permission issue , but where and how ?
Regards,
Tarek Ghazali
SQL Server MVP
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 RelatedI 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.
View 5 Replies View RelatedI use asp.net 2.0 and SqlDataSource to connect mdf file,
I want the date could write in xlm file, How do it,
Please help, thanks
SQL script : <aspqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT å?ˆé¤?編號, å?ˆé¤?å??稱, å?ˆé¤?價錢, 說明, 顯示, 更新日期 FROM lunch WHERE (顯示 = 'A') >
</aspqlDataSource>
Hi,
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.
- Abhijit
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?
View 1 Replies View RelatedHi,
I want to do the following with a ssis package:
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?
Thanks
Danny
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
Is it possible to do? I'm getting lock violations in I try to execute several tasks in parallel.
View 4 Replies View RelatedI 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...
Thoughts, suggestions?
Thanks.
J
I need how we can write into a file result of query in SQL.
i need to write into a file the details we get after executing a query.
I'm a junior programmer, and i must write/append several string into a file
but i don't know how to do it??
In oracle i used UTL_FILE
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
View 1 Replies View RelatedHi,
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!"
Dim strFile As String = "MyFile"
intLock = clsWDD.LockTextFile(strAppl, strFile) 'line 35
intLeng = clsWDD.WriteTextFile(strAppl, strFile, strData, WriteAction.Create)
intLock = clsWDD.UnlockTextFile(intLock)
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.
View 1 Replies View Related
How to write to a flat file, using a stored procedure?
Thank you,
SQL Server 2005 (SP2).
Smith
How do I write the record set to a .csv file using oledb in vb.net? If anyone has sample code to write to .csv file, I greatly appreciated.
Hello, theres,
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 ?
Regards,
Agi
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.
I know oracle uses UTL_FILE procedures lik putline, but microsoft?
Thank you for trying to help.
hello!
can somebody tell me how i can redirect the result of my
select statement to a file.
thank's
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!
how to write a query output into a text file in a c: directory
the field datatype is text .
Thanks
Al
Hi,
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.
Thank very much for your help
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..
hope someone anwers me...i really need help!
thanks!
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.
The Code is
Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Imports Microsoft.VisualBasic
Imports System.Security.Permissions
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.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)
at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Object assemblyOrString, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)
at System.Security.CodeAccessSecurityEngine.CheckSetHelper(PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle rmh, Object assemblyOrString, SecurityAction action, Boolean throwException)
at System.Security.CodeAccessSecurityEngine.CheckSetHelper(CompressedStack cs, PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle rmh, Assembly asm, SecurityAction action)
at StoredProcedures.PriceSum(SqlInt32 intFecha)
I use
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
I really appreciate your help, it could be a different way to do this or a simple step by step "how to grant permission"
thank you very much
Adolfo Ponce
Hi,
SQL Server 2005, sp2
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?
Thanks much
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?
View 6 Replies View RelatedCan 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 ?
CREATE Procedure [spOB_AddtblOB_Properties]
@strPropertyRef varchar (100),
@strRouteNo numeric,
@strAdd1 nvarchar(1000),
@strPostcode nvarchar(10)
as
Insert into tblOB_Properties (
PR_PropertyRef,
PR_RouteNo,
PR_Add1,
PR_Postcode)
values(
@strPropertyRef,
@strRouteNo,
@strAdd1,
@strPostcode)
GO
hello,
I have to write a batch file program that will just copy the names of the files from a particular folder.
Which means some command that could go into that folder through a path provided and get/copy just the name of the file.
Regards
Thank You