How To Open A Text File In A Stored Procedure
Aug 23, 2004
Hai..
I have data in text files ( not in csv format but in a properitary format).
My requirment is to read the text files and parse it into corresponding data and then store it into the MSSQL Server Database.
Is there any way to do this by using Stored Procedure or extended stored prodcedure in SQL Server. Or is there any other way to do this in
SQL Server. My database is there in SQL 2000.
Any help will be appreciated..
with regards
Sudar
View 14 Replies
ADVERTISEMENT
Apr 3, 2008
Hi..
I have a website in which the user uploads a text file to the webserver. and then i am calling a stored procedure which loads the data into the staging database using bulk copy. since my sql database and the web server are in different machines but on the same domain.. i am accessing the .txt file from the stored procedure by giving http://Ipaddress/ApplicationName/Directory/plan.txt
and the format file using the same address i.e http://Ipaddress/ApplicationName/Directory/plan.fmt
But when i press ctrl + click on the text file it take me to that location but when i do the same for the format file it says the page cannot be found.. Since it cannot access the .fmt file my import doesnt work.
Any help will be appreicated..
Thank you,
Karen
View 4 Replies
View Related
Apr 3, 2008
Hi..
I have a website in which the user uploads a text file to the webserver. and then i am calling a stored procedure which loads the data into the staging database using bulk copy. since my sql database and the web server are in different machines but on the same domain.. i am accessing the .txt file from the stored procedure by giving http://Ipaddress/ApplicationName/Directory/plan.txt
and the format file using the same address i.e http://Ipaddress/ApplicationName/Directory/plan.fmt
But when i press ctrl + click on the text file it take me to that location but when i do the same for the format file it says the page cannot be found.. Since it cannot access the .fmt file my import doesnt work.
Any help will be appreicated..
Thank you,
Karen
View 5 Replies
View Related
Jan 31, 2008
What is the easiest way to accomplish this task with SSIS?
Basically I have a stored procedure that unions multiple queries between databases. I need to be able to export this to a text file on a daily basis and add a total records: row to the end of the text file.
Thanks in advance for any help.
View 7 Replies
View Related
Aug 4, 2004
Hi guys,
I need to be able to send a text file with data seperated by tabs to a stored procedure that populates my table.
I am new to SQL and SPROCs that I am not sure how to even start. If you guys have any ideas I would really like to hear them or maybe any on-line docs and examples.
Thanks in advance!
View 2 Replies
View Related
Apr 13, 2001
Hi List
I have stored procedure which need 4 input variables. I want to send the stored procedure output to Table or text file. Is there any way I can do it let me know. Here is the stored procedure.
Exec TestProcedure 'USD',@test1 output, @test2 output, @test3 output
Thanks in advance
Wang...
View 2 Replies
View Related
Sep 7, 1999
Is there a way to use BCP or something else within a stored procedure to extract data from a select statement out to a text file?
View 4 Replies
View Related
Aug 7, 1998
Hi!!!
Is it possible to create a stored procedure which will create a text file (containing information from some tables) and send it via e-mail to a list of user.
I know that I will have to configure the SQL Mail.
If it is possible, can someone give me sample code.
Thanks you.
View 1 Replies
View Related
Oct 19, 2011
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 Related
Sep 26, 2013
Want to write from a table variable to a text file from a stored procedure.Read about xp_cmdshell bcp etc. but worried because it's supposed to be a security problem and needs to be from a permanent database.Also am getting error "The EXECUTE permission was denied on the object 'xp_cmdshell'..."
1. Is xp_cmdshell a bad idea to use even if I get permissions ?
2. Can a "permanent" table be used in a stored procedure starting out fresh each time with 0 rows rather than use a table variable ?
View 2 Replies
View Related
Sep 9, 2005
Greetings,I have a sp that dumps text into a textfile but I am having troublecreating the textfile.EXEC master.dbo.xp_cmdShell '\servernamed$The Filesubfilename.dat'The directory "The File" has a space in it. I've tried putting thecarat ^ before the space, and putting double quotes...but I keepgetting this error'\servernamed$The' is not recognized as an internal or externalcommand, operable program or batch file.If I do EXEC master.dbo.xp_cmdShell '"\servernamed$TheFilesubfilename.dat"' I get the same thing.If I do EXEC master.dbo.xp_cmdShell '""\servernamed$TheFilesubfilename.dat""' I get'"\servernamed$The Filesubfilename.dat"'is not recognized as an internal or external command, operable programor batch file.Does anybody see what I am doing wrong?
View 3 Replies
View Related
Mar 29, 2006
hi
Writing to text file from table/view is done using osql,bcp etc. How do we write output of stored procedure into text file??
Thank you
View 4 Replies
View Related
Mar 6, 2007
I have a C# SQL 2005 .net stored procedure which scrubs a text file looking for characters not in a range of characters and replacing them with another character. This works fine except when the process is killed. When this happens the file handle of the file being scrubbed is not released. I use a try catch finally block when opening the file and the output file. The finally section fiushes the output file and closes all files and streams but still when I go to access the file again or use the file in explorer it says the file is still in use. Should I be handling this some other way? How do I know the files will always be closed correctly.
View 1 Replies
View Related
Apr 29, 2009
I am having a Stored Procedure Or SQL Script to be attached to Job Scheduler. When this Stored procedure executes it generates some output text. I need to store this output to text file when ever this store Procedure (or) SQL Script executed by job Scheduler.
View 9 Replies
View Related
Nov 8, 2006
Hello, I want to load data from text file to MS SQL DB table.
In MySQL, it is the "LOAD DATA INFILE..." query statement.
What is sutable query if I want to migration from Mysql to MS SQL Server 2005 Express?
View 2 Replies
View Related
Jan 24, 2008
Hi guys..
i'm having a hard time to do this one..
you see these are the requirements..
1. there is a text file named "Loan"
2. i must create a program using ASP.net wherein the text file can be imported in the system.
3. after importing, i must save the whole text file in a database.
my questions are:
1. what code can i do to open up a text file?
2. how can i save the text file into a database?
3. is there any way that i could import the file and view its content in my application?
4. after viewing, how can i save the text file as a text file in a database?
thanks to the help
View 17 Replies
View Related
Jun 21, 2006
Hi,
I want to create a text file and write to text it by calling its assembly from Stored Procedure. Full Detail is given below
I write a code in class to create a text file and write text in it.
1) I creat a class in Visual Basic.Net 2005, whose code is given below:
Imports System
Imports System.IO
Imports Microsoft.VisualBasic
Imports System.Diagnostics
Public Class WLog
Public Shared Sub LogToTextFile(ByVal LogName As String, ByVal newMessage As String)
Dim w As StreamWriter = File.AppendText(LogName)
LogIt(newMessage, w)
w.Close()
End Sub
Public Shared Sub LogIt(ByVal logMessage As String, ByVal wr As StreamWriter)
wr.Write(ControlChars.CrLf & "Log Entry:")
wr.WriteLine("(0) {1}", DateTime.Now.ToLongTimeString(), DateTime.Now.ToLongDateString())
wr.WriteLine(" :")
wr.WriteLine(" :{0}", logMessage)
wr.WriteLine("---------------------------")
wr.Flush()
End Sub
Public Shared Sub LotToEventLog(ByVal errorMessage As String)
Dim log As System.Diagnostics.EventLog = New System.Diagnostics.EventLog
log.Source = "My Application"
log.WriteEntry(errorMessage)
End Sub
End Class
2) Make & register its assembly, in SQL Server 2005.
3)Create Stored Procedure as given below:
CREATE PROCEDURE dbo.SP_LogTextFile
(
@LogName nvarchar(255), @NewMessage nvarchar(255)
)
AS EXTERNAL NAME
[asmLog].[WriteLog.WLog].[LogToTextFile]
4) When i execute this stored procedure as
Execute SP_LogTextFile 'C:Test.txt','Message1'
5) Then i got the following error
Msg 6522, Level 16, State 1, Procedure SP_LogTextFile, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'SP_LogTextFile':
System.UnauthorizedAccessException: Access to the path 'C:Test.txt' is denied.
System.UnauthorizedAccessException:
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, ileOptions options)
at System.IO.StreamWriter.CreateFile(String path, Boolean append)
at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize)
at System.IO.StreamWriter..ctor(String path, Boolean append)
at System.IO.File.AppendText(String path)
at WriteLog.WLog.LogToTextFile(String LogName, String newMessage)
View 13 Replies
View Related
Nov 2, 2005
Hi, i has another problem i think you can help me on,
I need to open a encrypted stored procedure, how can i do it????
Thanks.
View 2 Replies
View Related
Jan 25, 2007
Hi,
I would like to create a stored procedure, which accept RunAsUser, MasterKeyPassword and also ASymmetricKeyPassword. In this stored procedure, it call OPEN MASTER KEY and etc. I put these code in stored procedure so that my support staff can call it when doing maintenance by passing the parameter. I don't expect them to remember OPEN MASTER KEY and etc syntax.
However, I hit error "Invalid Syntax" when I run my code as below. Any ideas?
Thank you
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE GrantMe
-- Add the parameters for the stored procedure here
@RunAsUser varchar(20), @MasterKey varchar(30), @ASKey varchar(30)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
EXEC AS USER=@RunAsUser;
OPEN MASTER KEY DECRYPTION BY PASSWORD=@MasterKey;
OPEN SYMMETRIC KEY HRMS
DECRYPTION BY ASYMMETRIC KEY FlexHRMS WITH PASSWORD=@AsKey ;
END
GO
View 12 Replies
View Related
Feb 15, 2006
Hi all,
When I try to open a connection it gives me error "Stored Procedure sp_sdidebug not found"
Can anyone help me in this respect?
I was running the same application with the SQL Server 2005 only earlier when it was running.But now after I reinstalled VS.NET & SQL Server I am getting this error.What could be the problem?
Please help... I am badly stuck up.
PAM
View 3 Replies
View Related
Jun 16, 2015
What might be the reason for this error:
A propos above error, traying to open [URL] ....
I am getting this error:
User 'domainuserName' does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.
How to proceed with this ?
View 2 Replies
View Related
Jan 5, 2015
when i click on the modify option on a stored procedure it is not opening for editing as earlier. it opens as if for scripting.for example i clicked modify procedure on stored proc. "text" and the result is as follows.
/****** Object: StoredProcedure [dbo].[test] Script Date: 05/01/2015 7:50:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test]') AND type in (N'P', N'PC'))
[code]....
View 4 Replies
View Related
Nov 2, 2013
I'm working on a report to show financial transactions from a table over a certain period. For most transactions there is a PDF document that is stored in a separate table in a binairy format. In my report I would like to include a link on every line with transaction information in the report that opens the PDF that is linked to that transaction. Just to be clear, I don't want to embed the PDF in the report but I want the users of the report to have the option to view the PDF that is related to that transaction in their standard pdf reader (adobe).
Code to do the following:
Once a user clicks on the link to view the PDF I need the code to get the binairy data of the PDF file from the table, convert it back to a PDF and open it in the default pdf reader (for example adobe reader). If it can't directly open the file then it's maybe possible to activate the 'open or download' pop up that you also get when you download something from a website.
View 4 Replies
View Related
May 6, 2002
How can I determine the length of a text value from a column defined as text? Len works really well with char and varchar but not text. How can I do it?
Also, need same for nchar, nvarchar, and image.
Thanks,
Chris
View 3 Replies
View Related
May 27, 2008
hi guys..
i need some assistance.. i have couple of .txt file to import into database.. @ the moment i am doing by improting process in sql management.. but it's realy pain and time taking.. i got a stander file format that i import,. and the destination table if exist then ookay otherwise i just create one ..
Is it possible that i can write a Store procedure so that i can use that .... Please help me.. i dont' a single clue about this..
Thanks
View 1 Replies
View Related
Jan 16, 2008
Hi,I've got some XML which exists as a text variable in a temp table in SQL Server 2000.I need to pass this XML into sp_xml_preparedocument so I can rebuild a table out of it. But I can't figure out the syntax.If I try doing this:declare @idoc intexec sp_xml_preparedocument @idoc output, (select XmlResult from #cache)I get an error, with or without the brackets round the select statement.The temp table is created using an SP, but I can't call that directly either. This:declare @idoc intexec sp_xml_preparedocument @idoc output, exec Search$GetCache @searchIDAlso throws an error.I can't put it into a
local variable because they can't be of type text. I can't pass it into
the SP somewhere as it's being generated on the fly.How can I get my xml into sp_xml_preparedocument?Cheers,Matt
View 3 Replies
View Related
Apr 17, 2007
Hi,
I have a problem with importing text into a database.
I have an application in .net where I fill a textbox with a certain text. When I press a button the text shall be separated and inserted into a database. The big question here is: how do I seperate the text so I get it into different colums in the database?
I know there is someting called InString, should I use this or is there an other way? I was thinking I could use a stored procedure.
Thanks in advance.
View 3 Replies
View Related
Jun 5, 2007
How to find a text within a SP,by issuing a sql query or command?
Thanks in advance.
View 2 Replies
View Related
Jan 16, 2008
Hi,
I've got some XML which exists as a text variable in a temp table in SQL Server 2000.
I need to pass this XML into sp_xml_preparedocument so I can rebuild a table out of it. But I can't figure out the syntax.
If I try doing this:
declare @idoc int
exec sp_xml_preparedocument @idoc output, (select XmlResult from #cache)
I get an error, with or without the brackets round the select statement.
The temp table is created using an SP, but I can't call that directly either. This:
declare @idoc int
exec sp_xml_preparedocument @idoc output, exec Search$GetCache @searchID
Also throws an error.
I can't put it into a local variable because they can't be of type text. I can't pass it into the SP somewhere as it's being generated on the fly.
How can I get my xml into sp_xml_preparedocument?
Cheers,
Matt
View 4 Replies
View Related
Oct 20, 2006
does sql server 2005 standard store blob or text file in MS WORD format in a hirarchical manner (like how xml is stored) or does it store it as a long and single line of text in a column. Thanks!!
View 8 Replies
View Related
Jun 7, 2007
I am trying to inject dynamically generated text into a Sql2000 stored procedure. What am I doing wrong?A code behind routine generates the following string value based on a visitor entering 'sail boats' in TextBox1. The routine splits the entry and creates the below string.Companies.L_Keywords LIKE '%sail%' AND Companies.L_Keywords LIKE '%boats%'
I am trying to place this string result in the WHERE statement of a Sql2000 Stored Procedure using parameter @VisitorKeywords.
PROCEDURE dbo.KWsearchAS SELECT DISTINCT Companies.L_Name, Companies.L_ID, Companies.L_EnabledWHERE ( @visitorKeywords ) AND (Companies.L_Enabled = 1)ORDER BY Companies.L_Name
I am wanting the resulting WHERE portion to be:
WHERE ( Companies.L_Keywords LIKE '%sail%' AND Companies.L_Keywords LIKE '%boats%' ) AND (Companies.L_Enabled = 1)
Thank you
View 10 Replies
View Related
Mar 19, 2001
I want to sort a stored procedure based on a variable passed to it... what is the easiest way to do this? Here's an example of what I want to do:
sp_select_thing 10, 'thing_name', 'asc'
It would run the query then somehow evaluate parameters 2 and 3 to be placed in the ORDER BY clause. I'm not sure if they should be quoted as strings or not, I don't have an idea how to pass a "reference to a variable" as a parameter to a stored procedure... or even if such a thing is possible
Thanks!
View 1 Replies
View Related
Jan 27, 2006
Hi
I'm trying to create a stored procedure using the northwind db which will do the following:
SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice, UnitsInStock
FROM Products
However, where UnitsInStocks = 0 I would like the words "Sorry, out of stock" to appear. I will then call this from an ASP page.
Can anyone help please?
Cheers
Woolly
View 7 Replies
View Related
