Quick history, I have substantial experience using MS Access, VBA, and Regular Expressions to clean up messy text files for use in MS Access.
I want to upgrade an MS Access back end to SQL Server (2008 to be specific). I need to upload several delimited files on a daily basis. The files are full of formatting that I strip out using VBA and Regular Expressions. I've poked around on the site and found some UDFs that implement the VbScript Regular Expressions object as a stored procedure in Transact SQL.
My questions is, am I better off processing the files externally using a procedural language like VB or can SQL Server handle the processing as gracefully as MS Access/VBA/RegEx does?
Thanks for letting me tap into your experience.
I could simply puzzle it out myself by implementing it in SQL Server but I'd rather not waste the time as a newb to transact SQL and then find out that I'd be better off doing it externally.
I had received a message that my log file is full and it do not enable to me to do a database backup before free up disk space. How do i clean up de log file (_log.ldf)?
Hi, I need some advise in the log shipping. The log files in the primary server get cleaned up according to what I have specified in the maintenance plan. But the log files that got shipped to the secondary server stay there for ever wasting my hard disc. Will it make any problem if I remove them or can I set it up to remove all files earlier than past 2 hrs? Please advise. Thanks
I have a custom Data Flow task that creates temp files to the system temp directory during processing. A lot of times, we'll use SSIS to do one data transformation, running and tweaking the package along the way... we do this in the designer ... if we notice something that's incorrect in the data view, we just hit the stop button and fix it. However, when we do this, the Cleanup() function isn't called, and my temp files are left in the temp directory, when they really ought to be disposed of.
Is there a method that gets called every time when the DtsDebugHost quits, whether it finished, didn't finish properly, or was stopped in the middle? What would be a good way (other than having some service that monitors what temp files are used by what processes) to clean up temp files after we don't need them?
I have text data files from a third party and they use comma as field delimiters and enclose the text for each column in double-quotes. Not a problem for most of the data files until they start sending files where there is " within the column values. SSIS package fails with the error:
The column delimiter for column "Column 1" was not found.
Any ideas on how to resolve this issue will be greatly appreciated.Thankspcp
I am trying to access some SQL 2005 reports externally from the network, however when i try to open the reports i get the following error -
· "The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel. The remote certificate is invalid according to the validation procedure."
We have sql 2005 setup pulling sharepoint list data and we have created a report from this data. We can view this report through sharepoint internally however when we try to view the report externally we cant view it?
How is it possible for others to browse to my localmachine and view the reports? They tried browsing to http://machinename/reports and http://10.1.3.123/reports
But the message the users get is: You are not authorized to view this page.
Then In my localmachine, I changed the IIS setting for the Reports and ReportServer to allow anonymous access. Then the users were able to see the Home page of the reports but not the reports themselves to click on for viewing. Any thoughts please? Thanks
I've created a linked server (and set up the corresponding schema.ini file) in order to perform bulk-inserts from some CSV text files into SQL tables (from my standpoint the text files are just for reading purposes). The linked server works fine (I can select the data in the files without a problem).
Now the question: is possible to automatically detect when one or more of those files change in order to start the import process automatically? Something like having a trigger created on the CSV files Or there's no easy way to do that so I have, to say something, to create a Job that periodically checks if the files have changed programatically (say, recording each file's timestamp everytime is imported and comparing the recorded value with the current one, or whatever)?
Hi there,Can SQL Server 2005 be made to report the date as GMT using the getDate() function? (without changing the local time on the box that it's installed on?)If I could get the current offset value within a Stored Procedure that would help.. Thank you!
Hi! I have installed SQL 2000 and SQL 2005 in my computer.I tried to use some new features like row_number(),try..catch.. but are not working giving syntax error.� So someone told me that i had to check the version and when I cheked I realized I was working with SQL 2000: “Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)� So, how can I change it for working with SQL 2005?. It’s like I’m working with SQL 2005 interface but internally it works as SQL 2000. Can you help me please?
Hi all,i have a problem and couldnt find anything even close to it. please help me, here is the description of what i m trying to accomplish:I have a trigger that is generating a column value and calling a stored procedure after the value is generated. And this stored procedure is setting this generated value as an output parameter. But my problem is:my asp.net page is only sending an insert parameter to the table with the trigger, trigger is running some code depending on the insert parameter and calling this other stored procedure internally. So basically i m not calling this last stored procedure that sets the output parameter within my web form. How can i get the output parameter in my webform? Everthing is working now, whenever an insert hits the table trigger runs and generates this value and called stored procedure sets it as an output parameter. I can get the output parameter with no problem in query analyzer, so the logic has no problem but i have no idea how this generated output parameter can be passed in my webform since its not initiated there.any help will greately be appreciated, i m sure asp.net and sql server 2000 is powerful and flexible enough to accomplish this but how??-shane
I have installed SQL 2000 and SQL 2005 in my computer. But none of 2005 feature is working. So someone told me that i had to check the version and when I cheked I realized I was working with SQL 2000: €œMicrosoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)€?
So, how can I change it for working with SQL 2005?. It€™s like I€™m working with SQL 2005 interface but internally it works as SQL 2000. Can you help me please?
We are trying a cluster setup on MS SQL 2005 with one m/c as Publisher (Primary) and another as Subscriber (Secondary). When Publisher and Subscriber both are running, everything goes fine but when Publisher server goes down, subscriber server troubles.
We are using MSADO15.DLL for database connectivity. We have a table where ID column is set to primary key with Auto Increment true. We use AddNew() function of this library to insert a new record. We fill a structure with all necessary values to pass it to AddNew() with ID field set to 0. When we use AddNew() on the above table to insert a record, the ID auto inserted in table is correct but returned structure contains wrong ID value.
We tried to trace this problem using SQL Server Profiler Tool of SQL Server. AddNew() function performs following operations in back end 1. Inserts the record to the table using INSERT 2. Calculates the auto increment field ID using SELECT @@IDENTITY 3. Fills this ID in the structure passed to AddNew() and returns it. But ID returned by SELECT @@IDENTITY query is wrong. There are other ways also to retrieve the last ID inserted in table by IDENT_CURRENT(€˜table_name€™) which return right ID.
Can we change the call of SELECT @@IDENTITY to IDENT_CURRENT in AddNew() functions behavior of MSADO DLL? Or there is another way of retrieving right ID?
Using the MSSQL JDBC 1.2 driver (Oct 2007 release), we don't seem to be able to call stored procedures which internally uses app_name() function to fill into database tables .
This driver fails to access such stored procedures in both SQL Server 2000 and SQL Server 2005 databases. The previous 1.1 driver (2006), suceeds in both cases.
Here is a test case which demonstrates this problem.
CONSTRAINT SimpleTestConstraint CHECK((len(ltrim([PrimaryIdentifier])) > 0 and len(ltrim([ServiceProvider])) > 0 and len(ltrim([SecondaryID])) > 0 and len(ltrim([PanoramaObject])) > 0 and [salary] <> 0.0))) Stored procedure id defined as follows:
I was asked at work to create an audit log to track user changes to our SQL Server 2005 database. My plans were to use a trigger to store the changes in a database table. I was just told that rather then storing these changes the database, that I should write these changes to a text file. I am having problems finding a good example of how to write to a text file in T-SQL. Does anyone have an example of how to do this? Thanks
I have a table T1 with three Cols. Code VARCHAR(10), INISetting TEXT, TSystem VARCHAR(10).
Right now I are storing the INI Files directly in the database. I would like to move this to text files in to a SAN box and just store the Pointers in the IniSetting Column and name the text files some thing like Code.txt
When I do a SELECT Code , INISetting , TSystem FROM T1.
I want to read the Contents as is from the Text files stored in the SAN Box?.
I want to import multiple text files into a single table. I know I have to use BCP or DTS. But, I want import all files at once, instead of one at a time. And the file names are in sequence, viz. file1, file2, file3 etc. Can anybody tell me, How I can achieve this.
I NEED TO READ A TEXT FILE INTO A SQL SERVER 6.5 TABLE. THE FILE HAS VARIABLE LENGTH FIELDS AND THE FIELDS ARE SEPARATED BY PLUS SIGNS ("+"). ANY IDEAS ? THANKS FOR YOUR TIME.
I would like to export SQLServer data from a table to a text file within a stored procedure that is fired from an after insert trigger. In Oracle the is a UTL_FILE Package that does this. Is there any way to do this in a SQLServer stored procedure.
I have to import 18000 text files into a sql database. Each file contains 10 fields and around 5000 records. I am currently doing this with DTS.
What I am wondering is this: Is DTS the most efficient i.e. quickest way to import all this data. Bearing in mind there is about 90 million records to import in all.
I would appreciate the benefit of somebody elses experience when dealing with this type of thing.
Hello!, I have two applications that import data from text files to Access 97 tables. One of this applications is in Access 97 and the other in VB6, know they are requesting me to import this text information to a SQL table. I do not have any experience with SQL Server, can some one give a idea of what I could do to accomplish this?, I would appreciate a lot anyone's help!, thanks! ...
Ok, I have an sp that'll build an SAP feed based on parameter input (params control which type of file I want to create). I want to build 9 files in total.
I have a table set up with my parameters and output file names.
Question 1: Process from DTS I have a DTS package which will build a file based on params and filename from table, pulled with a Dynamic Properties task. How can I iterate through my table of parmas to create the muliple files?
Question 2: Process from a stored proc I have a stored proc, from which the interation through values is simple. How can I create and export to the text files from the stored proc? I think I may be having a mental fart on this one. I could create a text linked server dynamically, but I have not played with them much, How to I write to one (create table etc).
I am using BCP and it works just fine. I get a txt file with tab seperated fields. Can i specify the character positions or lengths of each field so that all my fields line up at the same positions.
Help is appreciated.
I created a stored procedure with the below declare @filename varchar(50), @bcpcommand varchar(2000)
set @filename = 'c: eportmedia.txt' print @filename set @bcpcommand = 'bcp "select * from table" queryout "'+ @filename -U -P' exec master..xp_cmdshell @bcpCommand
Hi All I have a situation in my SSIS Project where I have a folder in which contains 10 text files, what I need to do Loop through all these files and select the data from those text files and insert them in SQL Database table. I will be able to get the names of those file using For Each Loop Container but I don’t have idea how to insert the data in SQL table using for each loop container. I don’t want to use multiple data flow task. Any help will really appreciate Many thanks
Thank you for the help and support you have given me. Now i am confronted with a new problem. I have to import some textfiles to SQL Server Tables . I have to create a tool to automate the porting using C# .The columns in textfile is seperated with pipe"|" . I f any body knows this please help me .
Thank you for the help and support you have given me. Now i am confronted with a new problem. I have to import some textfiles to SQL Server Tables .The columns in textfile is seperated with pipe"|" . I f any body knows this please help me .
