Hi
I have decided to approach the problem here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=103310
by using a Stored procedure, someone mentioned it's possible
for a SP to read the contents of a text file ?
IS it ?
For example i have a DB table
with fields to,from,subject,body
problem is the body field references a file (full path)
and i need to extract all info into a txt file
the full txt body, so can i perform some sort of inner
operation on the select body .. part of the SP ?
Hi,Using Chandu Thota's book Mappoint.net I attempted to set up a findnearby query using his example Implementing Spatial Search Using SQL Server. The book uses C# I have attempted to convert the code to call the stored procedure as follows: TryDim units As Int16 = 0 Dim cmd As SqlCommand = New SqlCommand("FindNearby") 'Assign input values to the sql command cmd.CommandType = CommandType.StoredProcedurecmd.Parameters.AddWithValue("@CenterLat", latitude) cmd.Parameters.AddWithValue("@CenterLon", longitude)cmd.Parameters.AddWithValue("@SearchDistance", distance) cmd.Parameters.AddWithValue("@Units", units)Dim con As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("VehicleMarshallConnectionString").ConnectionString) If con.State = ConnectionState.Closed Then con.Open() If con.State = ConnectionState.Open Then cmd.Connection = conDim dreader As SqlDataReader = cmd.ExecuteReader() If dreader.Read() Then If Not dreader.IsDBNull(1) ThenDim ordnum As String = dreader(1).ToString End If End If End IfIf con.State = ConnectionState.Open Then con.Close() Catch ex As Exception Finally End Try
The code returns no values but when I execute the stored procedure from the server explorer in visual studio I get the records I would expect in the output window. How would I read the returned values from my code. Output Window Running [dbo].[FindNearby] ( @CenterLat = 53.10531, @CenterLon = -2.4769, @SearchDistance = 100, @Units = 0 ). ID ordnum ProxDistance ----------- ------------------------- ------------------------- 1 009999/USP 0 2 109999/USP 14.5971373147639 3 119999/USP 57.7144756947325 No rows affected. (3 row(s) returned) @RETURN_VALUE = 0 Finished running [dbo].[FindNearby]. Regards, JoeBo
I am using SQL 2000 and would like to dynamically assign ODBC data source to transform data task. Do you have a stored procedure to perform read/write from/to ODBC data source? I would like to input data source and table name.
I need to create a flat file as word document, may i know how to write text from stored procedure if a file is already exist then the text will append, how to do it ?
CREATE PROCEDURE TestDataRead @TestString varchar(20) OUTPUT AS
SET @TestString = 'Mr.String' GO ----------------------------------------------------------------------- SSIS package details.
OLEDB connection manager; Connection works fine. Execute SQL Task Editor properties are as follows. ResultSetà None SQLStatementà exec TestDataRead SQLSourceTypeirectInput Parameter Mapping: VariableName: selected the user variable from the list. Direction: Output DataType:varchar Parameter Name: 0 Parameter Type: 20 When I run I am getting the following error.
SSIS package "TEST SSIS1.dtsx" starting. Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec TestDataRead" failed with the following error: "Procedure 'TestDataRead' expects parameter '@TestString', which was not supplied.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. ........................................... ...................................................................
I have two database files, one .mdf and one .ndf. The creator of these files has marked them readonly. I want to "attach" these files to a new database, but cannot do so because they are read-only. I get this message:
Server: Msg 3415, Level 16, State 2, Line 1 Database 'TestSprintLD2' is read-only or has read-only files and must be made writable before it can be upgraded.
What command(s) are needed to make these files read_write?
Hi I wounder if there is a sp in the master db that can give me what usergroup a username is connected to / and or give me info if the username is valid (Windows NT login). If not: then how do I read what usergroup a username is connected to? sysxlogin table in master db? And can I connect to a sp in master db from another db? Can I create a sp in my db that reads in the master db? Thanks!! Best Regards Staffan
Stored Procedure hanging when try to execute in READ_ONLY Database. Though the stored procedure is highly complicated,it runs smoothly on the same server if the Database option is set to READ/WRITE. Not all stored procedures are hanging in READ_ONLY database,only if the stored procedure tries to retrieve more records (say 10000 ).
If the SQL Statements present inside the stored procedures are executed,they are getting executed perfectly but hangs if it is executed as stored procedure.
On restarting the SQL Server, stored procedure getting executed only for the first time in READ_ONLY database but executing it again for the second time, it starts hanging. ( I tried executing the stored procedure after clearing the procedure cache but still hangs ).
Environment:
Windows NT 4.0 (Service Pack 5 ) SQL Server 7.0 ( Service Pack 3 ) Multi Processor ( 2 - 500 Mhz )
In a stored procedure, I am trying to create a table and then read it from within that SP or from another one (nestsed). When I run the below code (simplified version of my real SP), no records are returned even though I know that there is data. The table is created and records are inserted, I just cannot read the records from the SP or one that calls it with an EXEC.
Any ideas? My code is listed below. (When I get this to work, I plan to convert the code to manipulate a temporaty table).
Alter Procedure spWod_rptWoStatusSummary_9
As BEGIN CREATE TABLE tblStatusSummary (TckStaffCd_F Char(3), RecType Char(20)) INSERT INTO tblStatusSummary SELECT TckStaffCd_F, RecType END
I am coding a DataList control for 2 column data from a stored procedure. The 1st column isprogramName and the 2nd is programLink. I used Edit Template -> Edit DataBonding to add<h ref="http.....>, but can not get it right. Please help. Also, what is the Parameter Sourcefor using Logon User ID as a stored procedure paramter when configuring a SqlDataSource? None, cookie, control,..? TIA,Jeffrey <asp:DataList ID="DataList1" runat="server" DataSourceID="SqlDataSource1" Style="z-index: 16;left: 29px; position: absolute; top: 82px"><ItemTemplate>programName:<asp:Label ID="programNameLabel" runat="server" Text='<%# Eval("programName") %>'></asp:Label><br />programlink:<asp:Label ID="programlinkLabel" runat="server" Text='<%# Eval("programlink") %>'></asp:Label><br /><br /></ItemTemplate></asp:DataList><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:iTemplatesConnectionString %>"SelectCommand="MyPrograms" SelectCommandType="StoredProcedure"><SelectParameters><asp:Parameter Name="meid" Type="Int32" DefaultValue="3532" /></SelectParameters></asp:SqlDataSource>
I was wondering how I could pass on the following parameters from an ini file to a stored procedure within a DTS package. The parameters in the ini file look like:
[DatabaseCleaner] ! -- TableToBeCleaned_N=<table name>,<months to hold on db>,<months to hold on hd> ! -- <N> must be a successive number starting from 1 ... TableToBeCleaned_1=Transactions,24,24 TableToBeCleaned_2=Payments,24,24 TableToBeCleaned_3=PresenceTickets,24,24
As I do not know how many tables that will be declared in the ini file I have to loop through until the last parameters and pass it over to the SP.
In general as understand if we have a stored procedure that does operations like inserts or updates, it makes perfect sense to use a rollback operation within a transaction.
So, if something goes wrong and the transaction does not complete, all changes will be reverted and an error description will be thrown for example.
Nevertheless, does using a rollback within a try catch statement, make sense in a read only stored procedure, that practically executes some dynamic sql just to select data from some tables?
I have around 100 Stored procedures, all of them read only. Today a colleague suggested adding try-catch blocks with rollback to all of them. But since they are just selecting data, I don't see a clear benefit of doing so, compared to the hassle of changing such a big number of SP's.
Hi, I am wondering how can we return each file in a folder ? I am trying to get each file in a folder to perform other job. Below is the description ... while folder is not empty foreach file run the job end for end while
I am executing xcopy with xp_cmdshell in a stored procedure and it is not copying all the files. There are about 600 files in the source directory and only around 190 are copied to the destination.
The command in the stored procedure is this N'xcopy c:source*.* c:dest'
Some records got deleted from our DB. We don't have any triggers to log this delete transaction. Is there anyway to find out from log files or someother places to find out about this deletion. Is this information stored in transaction log. We do complete backup every night. So I think the transaction log will be truncated after the complete backup right? Please advise on this.
I saw some .trc files will be created by SQL Server Express I would like to view them (I suppose they are 'trace files') in order to find info about problems I encounter.
Somewhere I read 'SQL Server Profiler' should be used to read them but (to me) it seems that's not being installed using the downloads I found (SQLEXPR_ADV.exe and SQLEXPR_TOOLKIT.exe) so I wonder how to get it or how to view them differently
I have 2 tables EmployeeA(Eng) and EmployeeB(Spanish) kept in seperate mdb's. I want to add the records into Sql Server 2005 table called StateEmployee.
Procedure: 1. Loop through 2 folders..one containing table EmployeeA in mdb and other containing tbl EmployeeB in diff mdb's. 2. Pick a file from EmployeeA and EmployeeB, both at the same time. 3. Count the total no of rows in both files. If equal proceed. 4. Compare the 'employeeid' of one row of employeeA to the employeeid of EmployeeB. 5. If employee id matches, load both the rows in Sql server else file it to the error table. 6. Loop through all rows simultaneously till end of row. 7. Go to next mdb.
How do i go about this step by step. I am fairly new to SSIS.
I have written a small code that is used to read records from a txt or a csv file, but when i try to read from a txt or csv file which is in unicode, i get the following error
No value given for one or more required parameters........this error is recieved on the following line
dtadpt.Fill(DataSet.Tables("table2"))
The code for reading from a file is as follows
Dim dtrow As DataRow
Dim cmdtxt, file, path
Dim dtadpt As New System.Data.OleDb.OleDbDataAdapter
Dim Command As System.Data.OleDb.OleDbCommand
Dim Conn As System.Data.OleDb.OleDbConnection
file = 'name of the file'
path = 'path of file'
Conn = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
cmdtxt = "Select * from " & file & " where id = " & id
Command = New System.Data.OleDb.OleDbCommand(cmdtxt, Conn)
dtadpt.SelectCommand = Command
Next
DataSet.Tables.Add("table2")
dtadpt.Fill(DataSet.Tables("table2"))
this code works if i choose a csv or txtfile which is in ANSI format. But i want to read from a unicode file because i have some information in that unicode format that i want to store
i need to store a bmp or zipped file in a field of a ms sql db. i read the file using vb6 o.net, and in my mind i think sto store it in binary mode. the files could be more of 12 mega. whitch kind of field a could use?
I just want to know how to search text/record inside the Html files. I had one column in my database named €˜Path€™ I saved the html file names in this column and the physical files in a folder. Full Text search is enabled on this column. Whenever I try to search it returns nothing.
Just need one small example to know how it works, what functions used to search inside the files and how.
So did some troubleshooting on the my previous post -http://forums.microsoft.com/forums/ShowPost.aspx?PostID=272319&SiteID=1
Thanks to everyone who tried to help... So I got some insight into what is happening.
My package was having trouble reading files on a remote domain although I mapped the drives locally. This does not cause either running the package to failed either through directly running under VSS, or Execution Utility. But it failed when I try to schedule it through SQL server agent.
I wonder if it is the security context problem and asking for help of how to get around that issue.
So I have a for each file loop container that retrieve filenames on a remote domain which has a different security account then my local account. So how I got around that was to map that drive locally to a drive letter...example W:
then I use W: as my path within my package. I was trying to figure out where I can declare the connection within my package specifically, but don't know if I could do that.
If I run this through VSS or exectuion utility, it works okay. I was able to see W: and all the files on that mapped drive and read in the data.
However, when I schedule it under SQL agent, it doesn't see any files then exited the package as success right the way, because it has nothing to do....
So is there a different between the security context on mapped drive between VSS and my SQL agent? If so...How do I get around that challenge?
BTW, the two domain do not have share accounts between each others. I had to specified and map the drives explicitly.
I have a package that reads a fixed width flat file data into a single CHAR column in an sql server 2005 table.
In the "Columns" tab of the Flat File Connection Manager, I set the RowDelimiter value to {LF} and nothing for the ColumnDelimiter (since I read the entire row from the flat file into a single column in my sql table).
However, in the "Advanced" tab, the ColumnDelimiter Misc property shows {LF}. This was working fine for me.
The problem I was facing was with some files which were recently identified to have rows containing a special character (probably ASCII ZERO) in the middle of the row. So, now if the record was having 400 characters and the 200 th character had this special character, the package was writing the first 200 characters into the sql table and ignoring the rest of the characters.
I am sure that the special character was ASCII ZERO - I wrote a script to read each character in the line and find the ascii code for it.
Has anyone faced this problem ever. If so, pls let me know your solution or any ideas that can help sort this problem. Your help would be much appreciated.
a. Read data from Different CSV files. b. insert and update data to Data base in multiple table using joins.
This execution runs for 1-2 hours.I can use C# with Ado.net, but only concern I see is if in between execution fails due to some connection or other error. All insert data has to be cleaned up again.I feel writing and Store procedure inside transaction, which will take path's for CSV file as input and insert data in database. using transaction we will have flexibility rollback to original state.
I'm trying to upgrade from SQL Server 2000 to 2005. The problem I am having is that when I try to attach the existing db files I get a message that says "database cannot be upgraded because it is read only or has read only files...."
Thing is... there is no write protection on the files.
Can anyone advise me on how to overcome this problem so that I can attach the db, please?
I'm contemplating a sql server archive strategy that rolls really old data off any sort of dbms and onto low cost media like dvds in a non relational archive format. I dont want to ever worry about these archives spanning different versions of sql when i go to retrieve a range of data that happens to span sql versions (eg one disc was sourced from 2005 another by 2008 but my report needs a union of both).
So I'm thinking about neutral/efficient formats for these archives and a live homegrown catalog that can determine exactly what disc(s) need to be mounted based on passed from and to date parameters...all so that the data that might span discs (and versions and maybe even schemas) can be merged and loaded into my sql version d'jour's "throw away" archive database for a one time report or other unplanned activity.
I remember raw data types being very convenient as an ETL format for our customers who have ssis, but wouldn't want our sqlexpress customers to be left without the archiving capability. Do the "things" that read and write raw data files really originate in some special T-SQL command that all sql editions can use, or is it strictly an ssis thing?
We are in the process of upgrading to a new SQL 2012 server but we have many packages that load data from dbf files created with FoxPro into one of our databases. We have not converted the packages and run them with DTS but we get the following error:
Error: 2013-08-26 11:05:27.36 Code: 0xC0209303 Source: BenchmarkLoad Connection manager "OLEDB NPIONE.Investment.middleTierSQL" Description: The requested OLE DB provider SQLNCLI.1 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000.
[Code] .....
I searched for OLEDB and ODBC drivers for SQL 2012 64 bit but cannot find any that is newer than the 2005 that we have. What can I do short of changing the source files to overcome this issue?
