Reading Sql From A Disk File
It's probably a really easy thing to do, but I can't find out how to do it, so hopefully somebody can spare the time to help.
I want to read in T-SQL contained in a file held on disk and execute it in a stored procedure.
Anybody know how to get the contents of the text file into a variable so I can exec it ?
Thanks for your time.
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Reading Sql From A Disk File
It's probably a really easy thing to do, but I can't find out how to do it, so hopefully somebody can spare the time to help. I want to read in T-SQL contained in a file held on disk and execute it in a stored procedure. Anybody know how to get the contents of the text file into a variable so I can exec it ? Thanks for your time.
View Replies !
View Related
Could Not Open The File For Reading. Close Any Other Application That May Be Locking The File.
this error is a major pain in the bum. i have dealines to meetrequiring i import excel speadsheets into sql server. i have this onespreadsheet and i cannot for love nor money import it into sql server!i selected task manager to close any invisible instances of excel etc.i rebooted twice, knocked out indexing service on may machine etc. etc.then just for an experiment i removed the parenthesis from thefilename.... UK Brand Alias List (3 Dec 2004).xlslo and behold it worked, nice to know there are some small bugs likethis in a flagship enterprise level microsoft product!tristian2 of ewelme
View Replies !
View Related
Reading Log File
I have some records that have been deleted. I need to find out who did it and to do that I need to read the logs. Are there any utilities that will allow me to read login 7.0? How about 6.5? Chris
View Replies !
View Related
Reading An LDF File
I require the ability to view an .ldf file for disaster recovery reasons. We've tried Log Explorer by Lumigent for recovery but it does not port in text or any other BLOB data. This is a problem! Does anyone have a tool or know of a tool that will allow me to *read* the ldf file to salvage this data?
View Replies !
View Related
Reading A File Name That Changes
Brand new to SSIS so bear with me, if something is obvious. I want to be able to read a file from a certain directory. But the file name changes every day. So today its File20061203 tomorrow File20061304 or the next day it could be FileNB4434. The format in the file will always be the same though. I just want for a user to be able to drop a file in a directory and the package pick it up once a day. Would I have to to create a script task or could I use a variable. I have been trying to use the variable but have not been able to get them to work. This calls for only looking for 1 text file in a folder but any additional links that show some good variable examples would be appreciated. One where only part of the variable changes File(Variable)Division.txt Thanks in advance
View Replies !
View Related
Reading In An XML File From A URL
Hi I have a problem with SSIS in that I need to read an XML file in from a URL,which brings me back the below XML. What I need to be able to do is essentially create a single record, including the nested XML <image> into the same record. This information then needs to be entered into a database table. What I found is that if I use a data flow component, and have an XML source pointing to this URL, it essentially creates a different output for every level of nesting. This causes me serious issues because I would then have to somehow get to the information in the <image> tag, and then get everything below that, etc, to be able to get the images related to each <stock> record. This is a pain. I also looked at using an XML task, where I could use an XSLT to transform to the required output, however, I can't point this XML task to a URL to retrieve the XML. If anyone has any ideas I would greatly appreciate it Thanks Darrell XML Format ------------------ <feed date="2006-10-17 17:08:46"> <usedstock> <stock id="SFU22991"> <make>Peugeot</make> <model>206</model> <description>Peugeot 206 1.4 X-Line, 5 door Hatchback Tiptronic Auto 4Spd</description> <hotline>08451553610</hotline> <spec>Air Conditioning, Power Steering, Central Locking, Electric Windows, Alloy Wheels, CD Player</spec> <deepurl>http://www.regvardy.com/used_car/dseller/SFU22991</deepurl> <search>http://www.regvardy.com/used_cars/dseller/Peugeot/206</search> <regdate>2005-01-27</regdate> <colour>Blue</colour> <price>7495</price> <stocktype>USED</stocktype> <category>Small</category> <transmission>Tiptronic Auto 4Spd</transmission> <bodystyle>Hatchback</bodystyle> <fueltype>Petrol</fueltype> <type>NCV</type> <doors>5</doors> <engine>PE0BC</engine> <capcode>PE2614SED5HPIA</capcode> <capid>26440</capid> <image> <urls> <url>http://www.regvardy.com/imagedata/vehicles/g/pj54lyg_1.jpg</url> <url>http://www.regvardy.com/imagedata/vehicles/g/pj54lyg_2.jpg</url> <url>http://www.regvardy.com/imagedata/vehicles/g/pj54lyg_3.jpg</url> </urls> <libraryimage/> </image> </stock> </usedstock> </feed>
View Replies !
View Related
Reading XML File
Hello, I'm trying to read data from xml file (i want to use this file as enviornment config file). i tried 2 methods and failed in both of them: 1.a. I loaded the XML file to the report server 1.b. I wrote a custom code for reading the XML. i got #Error 2.a. I loaded the XML file to the report server 2.b. I used XML dataset for reading the XML file. thank you all
View Replies !
View Related
ASP.NET: Reading A CSV File With C#
I am trying to read from a datasource that is a CSV file using C#, but for some reason I keep getting an error that says: System.Exception: System.Data.OleDb.OleDbException: Syntax error in FROM clause. My code looks like this: String sql = " SELECT IDnumber FROM report.csv WHERE username = @Username "; ArrayList parameters = new ArrayList(); parameters.Add(new OleDbParameter("@Username", Username)); DataTable dt = OleDbUtility.getDataTable(sql, parameters, OleDbUtility.GetCNetIDConnectionString()); It worked with an Excel file, so any ideas on this one? It seems like the connection string is fine...
View Replies !
View Related
Reading ID From XML File???
Hello friends... We have sql server 2000 and i need to read one perticular xml and from that i want ID field stored in one table...is it possible by query analyzer to read xml and stored ID in table...I have only path of that xml file... like select ID from ("D:XMLFolder*.xml) that i want
View Replies !
View Related
Reading A File
I am storing one text file on the server.This text file contains some mobile numbers.The file look like 009198XXXXXXXX 009198XXXXXXXX 009198XXXXXXXX 009198XXXXXXXX etc Here I need to read this text file row by row mobile number and then insert these mobile numbers into a table by using sql procedure or sql trigger or any other method or coding. Is it is possible or not? If so then anybody can help me! regards Shaji
View Replies !
View Related
Reading In A Text File
I need to know how to import a text file into a stored procedure as one big varchar. I don’t want to import the data straight into my tables. I need to be able to work with it in the stored proc.
View Replies !
View Related
Reading XML File SQL Server
hi to all I have been trying to read XML file in SQL Server with Stored Procedure. but i coud't get any solution. can any one Help me. I have XML file with List Of Countryes each Country has Id. I need to read all Country Id,CountryName and need to be insert into DataBase or Return as String (output parameter). How do I achive this? please Help me. Cheers :confused: Buddu
View Replies !
View Related
Reading From An Excel File
I need to select a list of rows from excel file. I formed my query in this way : SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="MOC02c:empest/xls";Extended properties=Excel 97-2000')...xactions This gives an error Error is 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: Could not find installable ISAM.] If anybody knows how to read from an excel file to a query analyser , pelase reply. Regards Suresh
View Replies !
View Related
Reading A Flat File
I get the following error when reading a flat file : [Credit Information 1 [1]] Error: Data conversion failed. The data conversion for column "AccountName" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". I did check all the mappings, and everything seems to be fine, the field is read in as a string. I also check for any strange characters that can possibly cause this error but the value of the field only contains a person's name and spaces at the end. Does anyone have any ideas what might be the cause of the error?
View Replies !
View Related
Reading From A File Which Changes Its Name Dynamically
Hi,I have a requirement to load data from a flat file.The flat file has a naming convention and thus a timestamp is appended to it. eg : RMRS_07102007_001.delThis makes the file name different each day, Is there any method where by I can provide a dynamic name in the file connection manager.P.S. I tried a variable in For each loop with file enumatror and stored that in a variable , but I was not able to use that variable in File connection manager. pls guide if i was missing anything.Thanks in Advance, Ashish Prasad
View Replies !
View Related
Finding Changes By Reading The Log File
When importing data from a source server it is useful to know which rows have changed since the last import. Is there a good way to get this information from the SQL Server log files? Does "dbcc log" provide this function, for example? Is this something that is done a lot or would it be considered a kludge? Thanks, Ron Rice
View Replies !
View Related
Reading A Text File
Guys, need help! I know this is not area for VBScript question, but possible I will find someone to help. Here is my question. How can I read a text file of product IDs (ProductID contain only the first three character at the bigining of each line -- for example 220)and retrieve just those lines that meet a specified pattern? Thk
View Replies !
View Related
Restore DB From File On Disk
Hi, I am trying to create a scheduled task taht will restore a database from a backup file. I do not store my backups on a backup device, but on a local disk. To restore a DB from a backup device, the following statement will work: "Load DBName from BackupDeviceName". Does any know what statement to use to restore from a file, if the file is "E:DBName_db_dump_199909272220". Thanks in a advanced.
View Replies !
View Related
Reading Query From .sql File Error
I have a report that is based on a query that is read from a .sql file and is executed in the stored procedure. I found this code on the internet. When I run the SP in Query Analyser it tells me that "2 rows have been affected". I don't know why I don't get the data. Any help?? Stored procedure: CREATE PROCEDURE TimeTracker_Report_ClientNumbers2 AS DECLARE @SQLQuery as NVARCHAR(4000) BEGIN CREATE TABLE #tmpQuery (Query NVARCHAR(4000)) BULK INSERT #tmpQuery FROM '\servernameQueriesReport_ClientNumbers.sql' SELECT @SQLQuery = Query FROM #TMPQUERY EXECUTE sp_executesql @SQLQuery END GO and Report_ClientNumbers.sql contains the following query: SELECT Name, ClientNumber FROM Companies WHERE Not ClientNumber IS NULL ORDER BY ClientNumber
View Replies !
View Related
Reading Or Writing A File To A SQL Database
Halo, I am a bit new to this Please can someone help me, I would like to write a file(Any type) to a SQL database like a attached document(s) for the current record and be able to detatch the document when needed. I use VB.NET for a ASP.NET app. I basicly would like to attach documents to a piece of equipment may it be any kind and if the user views the equipment he will be able to detatch the documents for that piece of equipment and open it with the correct software. PLEASE HELP!!!!!!!
View Replies !
View Related
I/O Error Reading BCP Format File
I added a field to an exisiting table (CHAR 30), and I added to field to the BCP Format File. The BCP worked fine before the new field, and still works fine when I exclude the new field from the format file, but with the new field I receive the following error: SQLState = S1000, NativeError = 0 Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP format file (2 row(s) affected) Here is a copy of my format file: 7.0 11 1 SQLCHAR 0 6 "" 1 Empl_ser_no 2 SQLCHAR 0 2 "" 2 Empl_stat_cd 3 SQLCHAR 0 1 "" 3 Guest_type_fl 4 SQLCHAR 0 30 "" 4 Job_tl 5 SQLCHAR 0 14 "" 5 Pager_no 6 SQLCHAR 0 1 "" 6 Security_Clear_fl 7 SQLCHAR 0 9 "" 7 SSN 8 SQLCHAR 0 30 "" 8 Emer_cont_nm 9 SQLCHAR 0 14 "" 9 Emer_cont_no 10 SQLCHAR 0 1 "" 10 Citizen_fl 11 SQLCHAR 0 30 " " 11 Job_Res The input file is just a fixed positonal text file, and I have checkid it 100 times- it is fine. Any sugestions from any BCP gurus out there? THANKS
View Replies !
View Related
Reading File As One String, Then Parsing - How To Do This?
Hi, The suggestion to do this is buried deep in one of my posts, however I still do not have a clear idea of how to do this. I have a flat file which has several "bad rows" in it. Because file error redirection is buggy, I need a manual approach to get rid of these incomplete rows in my data file. Phil, you suggested I read the file as one long string, then parse out the bad rows (using a script?).... however I have no idea as to how to actually do this. I was wondering if it's possible to clarify the steps involved in doing this, or perhaps point me to an example I can look at, as I cannot seem to get around this problem on my own. Thanks much!!
View Replies !
View Related
Reading File Content Using SSIS
Hi, I have a requirement where I should be reading the file (I will use File system Task for this) and the header content. I should be able to read the header, validate and when the validation succeeds , I should start process the rest of the file. To elaborate this further, take a sample Example. Assume that the header will have the date information. In a given folder I should read the file, will check the date (header being the first line of the file). If it matches the current date, I will start processing and on completion I will archive my files. Can someone help me on this sample. Thanks Regards, Explorer
View Replies !
View Related
Reading Records In A Flat File
Hello, I am making my first attempt at creating a script for a Script Task. The script needs to do the following; 1. find the length of each record in a single fixed width flat file -file location; C:LearningSettlementDataTestSC15_CopiesSingleFile -file name; CDNSC.CDNSC.SC0015.111062006 (no file extension) 2. if a record is found that is longer than 384 characters; a. copy the record out to a text file -location;C:LearningSettlementDataTestSC15_CopiesErrantRecords -file name; ErrantRecords.txt b. delete record from the flat file where the record length is > 384. If I can get this to work on a single file, I want to implement it with multiple files. I would imagine that using a ForEachLoop container with the script task 'inside' would be the way to go for multiple files. I have a connection manager set up for the single file and a MultiFlatFile connection manager set up for the whole collection of files. All of the files have the same schema. I don't know if the connection managers are going to be useful to me with what I'm trying to do, but I have them set up. If you have some input on where I can find resources on how to do this, or have some code to pass along, please share. Thank you for your help! CSDunn
View Replies !
View Related
Reading Records From Excel File
Hi all, Whilst reading in records from an excel source via the SQL command method I've stumbled across a problem. my SQL query takes in all records where the date column is not NULL, this ensures that only populated rows are obtained. If the date is in a format that is incorrect i'd really like the whole data flow to fail. However... What seems to happen is that any rows with a fault in the date column are just missed out and not pulled through the pipeline. I have tried changing the error output from fail component to ignore error and redirect row but nothing seems to catch it. Does anyone have any suggestions as to why this may be the case? Many thanks, Grant
View Replies !
View Related
Reading And Writing To Flat File
I'm doing a test package which reads a flat file, makes an adjustment using the derived column task and writes to the same flat file. But, the read locks the flat file, so the write can't access it. Any ideas for a resolution? Thanks, Dave
View Replies !
View Related
Connection Reading XML Config File
I have setup a XML Configuration file that contains my connection string for my DB connections. My package has 2 exec SQL tasks and the a data flow task that use the sam connections. The 2 exec tasks work perfectly and I can see that they did what they were supposed to do. The problem is that when I get to the dataflow task it get login failed even though it is using the same connection from the connection collection. I even tried to delete the OLE DB input to the data flow and add it back but it will not even let me parse the query. Any help would be apprciated.
View Replies !
View Related
Sequentially Reading The Flat File
Hi, How do i sequentially read the lines of the flat file that have different structures inside on how to parse it and store them in a table? Let's say, I have this excerpt from the file: HA111Header1234 KLName1 KLName2 HA222Header4567 KLName3 KLName4 Below are the structures: If Record type = 'HA' then Length Recordtype 2 Code 3 Description 10 else if Record type = 'KL' then Length Recordtype 2 Name 5 Code 3 The Code in the KL record type is actually the code in the 'HA' line. So to store the KLName1 in the relational table, it's value for the code field is 111. The same goes for KLName4 which has 222 code. So, when a record type 'HA' is encountered, it's like i want to save its value of the code in a variable and use that to populate the code field of the following recordtype 'KL'. Can this be possible in Integration Services in which we will use the IS objects themselves to loop through the lines instead of creating a script (programming using script task... i think)? cherriesh cherriesh.
View Replies !
View Related
Reading First Few Lines From Flat File
Hi, I have a very simple question, how do i read the first couple of lines only from a flat file source. Let me illustrate with an example: **Source file*** Date of refresh 04/05/06 ** abc, 123 bac, 156 I need a way to read the first line 'Date of refresh 04/05/06 **' and get the date '04/05/06' ; and store the date in a variable. Right now, I have a flat file source and a script component, which receives a connection from the flat file source, The script component is reading the lines, but thrice instead of once. It would be great if I can program the script component to terminate after reading the first line only. Any help would be appreciated very much. Thanks Bidyut
View Replies !
View Related
I/O Error While Reading BCP Format File -- Pls Help..
I have this BCP command: Code SnippetBCP "SELECT Users.UserID as u_userid, Users.Name as u_name, Users.Login as u_login, Users.msg as u_msg FROM XYZ.dbo.USERS Users" QUERYOUT "C:USERS.DAT" -f"C:USERS.fmt" -T -S xyy.com And my Format file is like this: Code Snippet9.0 4 1 SQLCHAR 0 10 " " 1 u_userid 2 SQLCHAR 0 100 " " 2 u_name 3 SQLCHAR 0 50 " " 3 u_login 4 SQLCHAR 0 250 " " 4 u_msg Please note that I have a carriage return after the last row. All I want is to create a datafile with the columns in fixed width. This is because my record spills to the next line since one of its fields (msg) have newline character and even tabs in it. For instance, a msg value can look like this: Code Snippet hi, I would like to request for an access to this server: theserver.com thanks, me So I resorted to fixed width. Now, am having issues on this format file.. Please help..it's quite urgent
View Replies !
View Related
Reading Data From A Text File
I have text output files which are semi-structured.(Headers + irregular length tables below) Is there a simple method of getting them into sql format(line by line) to try and extract data from them? I know this won't be easy but its been worrying me for a long time. I have a method of importing the data into excel, but although difficult, it must be possible to get a system to get it into sql server. This must be a fairly common issue. John
View Replies !
View Related
Reading Tab Delimited Text File
Is there anyway Sql Server reads a "Tab Delimited Text File" and Compare each record with the Column in a table.. my question is.. I've a Country_Code table which has 3 letter Country Code and the Actual Country names are listed in a Tab Delimited Text File "Country Data" with Country Code and Country Name, how do i read each record and compare to get the Actual Country Name for Display. any ideas/suggestions. thanks
View Replies !
View Related
Moving Transaction Log File To Another Disk
Hi all, I have to give support to this new client of ours and the system out there was a real mess.The log files for all four databases were ranging from 5 to 9 GB's, no maintanance no nothing was done since the setup of the system. I have truncated the logs after taking proper backups since it all started after they compalined a DTS was not working, which was due to no disk space available ;). Now I have to shift the log files to a seperate disk. I know it can be done by detaching the database and attaching it back, I was wondering if we can do it without bringing the system offline? Anycomments welcome. Thankyou. regards, harshal.
View Replies !
View Related
Disk Backup Failures (file In Use)
I am getting occasional failures of a SQL Server 7.0 complete backup to disk on a production database. The errors seem to indicate that another process has the disk file open at the time of the backup. The errors contain the following texts : - 'Cannot open backup device' 'Operating System Error=32 Process cannot access file because it is being used by another process'. The only other process that should access the disk file is an ARCserveIT scheduled job to copy the disk backup to tape but this is completing long before. Any ideas or suggestions?
View Replies !
View Related
Log File Filled Up Disk When Restoring
Hi, I'm trying to write a restore script. In the past I've always used the SSMS GUI to restore. When running the script below, the process got stuck at around 90%. Eventually I aborted the restore and found out that my C drive was full. The log file of the database I was restoring was 32GB. Can anyone tell me what I'm doing wrong? Thanks. Code Snippet ALTER DATABASE [oms] SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO RESTORE DATABASE [oms] FROM DISK = N'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupOMS_Blank_Production.bak' WITH FILE = 1, REPLACE, STATS = 10 GO ALTER DATABASE [oms] SET MULTI_USER; GO
View Replies !
View Related
Move Log File To Another Physical Disk
Hi everyone, Due to running out of disk space and reducing I/O contention, I plan to seperate data files and log files to different disk arrays. My plan is to - detach all databases - copy log files to another disk - attach all databases The total size of log files is about 60GB. It becomes a problem becasue it spend too much time passing files. To reduce down time, is there any way I can do? Can I just create new log files and remove the current files by using sql management studio? any other suggestions? Thanks in advance
View Replies !
View Related
|
TRACKER
