I want to bulkcopy a pipe delimited text file that has two header records to SQL Server using ADO / C#. The first record contains the datetime the file was recreated and the second record contains the column definitions, both records start with a # character (see below).
# May 15, 2008 12:12:12345
# col1|col2|col3
col1rec1data|col2rec1data|col3rec1data
col1rec2data|col2rec2data|col3rec2data
Is there a way to ignore the two header records when building the select statement for the text file or in the schema.ini?
I am importing data into a SQL table and there is a potential for duplicate records to be coming in. How do I simply ignore the duplicates and add only the records that do not violate the keys?
I am creating a DTS package to export a text file. My question is: does anyone have any ideas on how for one read of the tables I can produce 2 lines of output. Here is how the file layout needs to be...
HEADER DETAIL HEADER DETAIL HEADER DETAIL
I am a little confused about how I can stagger header and detail using the same data.
I appreciate any help you can give. Hopefully my explanation of the problem is understandable.
I discovered wherein lies our troubles. Our input file has a header record, followed by several detail records... and then, at some point, another header record, followed by more detail records.
Apparently, SSIS doesn't like this set up and so we're having to rewrite everything in C#.
I have two questions:
1 - How hard would it be for SSIS to split the input file into several input files, breaking at every header record to start a new file? 2 - Is this really something that SSIS has a problem with? Has anyone ever had to deal with/work around this?
Im trying to copy details from a specific header as details of a different header (eg. all sales items from invoice #10 copied as sales items of invoice #11).
So far I have two stored procedures: 1) sp_copyDetailsOne /*Create a recordset of the desired items to be copied*/ CREATE PROCEDURE sp_copyDetailsOne @invoiceIdFrom INT, @outCrsr CURSOR VARYING OUTPUT AS SELECT itemId, itemPrice, itemDescription, itemQuantity FROM tblSalesItems WHERE (invoiceId = @invoiceIdFrom) OPEN @outCrsr
2) sp_copyDetailsTwo CREATE PROCEDURE sp_copyDetailsTwo @invoiceIdFrom INT, @invoiceIdTo INT /*Allocate a cursor variable*/ DECLARE @crsrVar CURSOR
/*Loop through the recordset and insert a new record using the new invoiceId*/ FETCH NEXT FROM @crsrVar WHILE @@FETCH_STATUS = 0 BEGIN
/*Run the insert here*/ INSERT INTO tblSalesItems (invoiceId, itemId, itemPrice, itemDescription, itemQuantity) VALUES (@invoiceIdTo , 5, $25.00, N'Black T-Shirt', 30)
/*Fetch next record from cursor*/ FETCH NEXT FROM @crsrVar END
CLOSE @crsrVar DEALLOCATE @crsrVar
My question comes on the Insert of sp_copyDetailsTwo, as you can see the values are hard coded and I want them pulled from the cursor. However I don't know how to do this - do I need varables or can I access the cursor values directly in my VALUES clause? Or is this whole approach needing overhauled. Any advice is welcome.
I've got a main report with five subreports. Based on a value of a parameter in the main report one the subreports is filled with data, all the other subreports will have no records. When the report is displayed in on the reportserver it is working fine, bit when I export the data to a CVS format, also the element names of the subreports are added to the CSV Output.
When i change the value of Data Output of the subreport item in the main report to Auto it doesn't export the records of the filled subreport.
How can I disable the export of the dataelement names in the CSV export?
I need some help. I am writing a report in SSRS 2005 that I then need to export to Excel. When I put a report header I would expect the header to not display in the Excel spreadsheet until the Print Preview or the Print. The report footer works just fine I put some text in the footer, and it shows up in the footer. The header though, shows up as a row in the Excel spreadsheet that then causes columns to merge. How do I get the report header to act like a page header?
I am making a book-like report, I am using a report that has a header and calling a sub-report that has it's own header. However the sub-report header is not showing on the parent report. Parent report header is prevailing over the sub-report. Is it possible to have both headers displaying?
I have a report that I created and the report was working until I added some fields to a group footer row in a table.
My table has 5 group levels. I had information displaying in the 5th level header group and detail. It was working fine. Then I added some fields to the 4th level group footer. Now it displays only the Page header, Table header, and the 4th level group footer data.
What happened to the rest of the data?
All the cells and rows I want to display have the Visibility Hidden set to false. I tried removing the objects I added (to the 4th level group footer) and it still does not work. Is this a bug or did I set something that is hiding the data.
There is a one header in the report, when I publish and hit the report in IE(internet explor) the header appears fine on first page when I go to next page this header does not appear.
But in mozilla the header is visible on every page of the report. so it is working fine in mozilla.
In above format:15-10 means Oct 15, 15-09 means Sept 15I am getting issues while formatting this in SSRS.I need SSRS table headers to be dynamic. I need that it should be according to the sql table header...The table header should change according to the sql query
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
When i checked the machine this morning i saw that MS DTC service was stopped and when did start it, it was good for a sec or two then it stopped auto matically i was not sure waht was goin on...
Then i checked the Eventviewer, it said the log file is full i tried out to find ms dtc log reset or increase the size but could not find out..where can i find this.
One more thing what is the purpose of DTC, i know in sql 6.5 we have everything in enterprize manager but i can't see the same either in 7.0 or 2000 is the name changed or is it linked to some other source.
Like linked server , does this depend on MS DTC..how and what is the basic purpose of this..kindly tell me what i should do now...
Is there a way I can perform an update on a table, but ignore thetrigger (or disable the trigger) each time I run a particular updatescript from a DTS package?I would like subsequent DTS steps to use the trigger except for my lastupdate statement. Is this possible?Thanks,Frank*** Sent via Developersdex http://www.developersdex.com ***
In my stored procedure I'm calling a buggy and flaky stored procedurethat comes from a third party. When I run my stored proceure from QA,I'm getting a whole buch of errors raised inside the third party one.Is there any way I could just ignore them, so that if I run my SP fromQA, only errors from my code, if any, show up?TIA
Hi,I'm trying to upload a large number of log entries currently stored astext files into a database table using bcp. For a few rows I get a"right truncation" error and the offending rows are not uploaded to thetable.I don't want to increase the size of the table varchar fields becauseit's only about a dozen out of almost million rows that have thisproblem ... I want to provide an override - i.e. if a row will resultin truncated data, truncate but still bulk copy the offending row. Isthat possible?I couldn't find such an option in the documentation.Any help is greatly appreciated.Thanks,Mudassir Latif
Is there a way to make a SP ignore an error?e.g. I'm looping through each database on a server, checking of a tableexists then selecting a value from that table. Now I have a database putonto the server where the table exists but all column names aredifferent, my SP is not interested in this database so when it errorswith invalid column name I want it to move onto the next databse and notdisplay any error message.
I'm writing a store procedure to accept search strings from user on my site. Currently, this is what I have.
Code Snippet @schoolID int = NULL, @scholarship varchar(250) = NULL, @major varchar(250) = NULL, @requirement varchar(250) = NULL --@debug bit = 0 AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SELECT * FROM [scholarship] WHERE ([sectionID] = @schoolID OR @schoolID IS NULL) AND ([schlrPrefix] LIKE '%' + @scholarship + '%' OR [schlrName] LIKE '%' + @scholarship + '%' OR [schlrSufix] LIKE '%' + @scholarship + '%' OR @scholarship IS NULL ) AND ([Specification] LIKE '%' + @major + '%' OR @major IS NULL ) AND ([reqr1] LIKE '%' + @requirement + '%' OR [reqr2] LIKE '%' + @requirement + '%' OR [reqr3] LIKE '%' + @requirement + '%' OR [reqr4] LIKE '%' + @requirement + '%' OR [reqr5] LIKE '%' + @requirement + '%' OR @requirement IS NULL )
The problem is, somtimes the search doesn't work if there is a space behind or infront of the search string. I wonder if there is away to ignore any spaces and go right into whatever character comes next or after. If so, how do I implement that?
I have a stored procedure that does several steps. During the stored proc, error messages are produced when certain conditions warrant. But I want to continue anyway.... ex. in a loop in the proc...
SET @strSQL = 'Update Table1 SET col1 = ''' + @strVariable + '''' EXEC (@strSQL)
--ERROR created by the exec statement.....
When I schedule this as a job, the first error message makes the job fail. How can I force the proc to run completely, even if an error occurs?
Hi! I'm wondering if there is a way to have the AVG() not to include the zero amount as part of the calculation. I'm looking the field, PurchPrice and RepairCost that are used by the AVG() function...
SELECT expense_id, CAST(expense_id AS char(10)) + ' - ' + CAST(trip_km AS char(5))+ ' - ' + CAST(expense_amount AS char(5)) + ' - ' + charge_centre AS ExpenseDesc
If charge center is null, I need to ignore this field. How can I achieve this? The reason is that if any of the field is null, it will return ExpenseDesc as null.
if there is an error in the trigger then the update to the table does not happen. is there a way to make sql ignore errors in a trigger and still update the table
Hi, I am trying to import data from a Text file into a database Table using SQLserver BCP utility. I am able to do that when I have all new records in my Text file. But I am getting primary key violation error when I am trying to import the record which is already existing in the table. This is correct, but I want my program to ignore these errors and import only those records which are fine. I tried [-m maxerrors] option, but it is not working. My BCP program is getting interrupted at the first error itself, even if I give [-m100] option. my command looks something like this, bcp pub..employee in C:data.txt -b1 -m100 -c -t, -Sdatabase -Uuser -Ppassword
here -b1 is, processing 1 row per batch transaction -m100 is, ignoring first 100 errors
I am looking for best practice when passing a parameter to stored procedure that is not needed. For example, sometime the users will want the list to list only by certain state. Other times the user want all states. How can I make the SP to ignore the where clause if users want all states.
CREATE PROCEDURE usp_Example @State nvarchar(2) AS SELECT FirstName, LastName, State FROM SomeTable WHERE State = @FirstName; GO
Using SQL2000. According to Books Online, the avg aggregrate functionignores null values. ((3+3+3+3+Null)/5) predictably returns Null. Isthere a function to ignore the Null entry, adjust the divisor, andreturn a value of 3? For example:((3+3+3+3)/4) after ignoring Nullentry.If there's more than one null value, then adjust divisor accordingly.For example: ((5+5+5+4+Null+5+5+Null)/8) would be ((5+5+5+4+5+5)/6)after nulls ignored.Thanks for any help or advice.
Hi,I believe my SQL server was configured as Case sensitivity. I have anumber of stored procedures which were moved from a non-Casesensitivity SQL server. Because of the Case sensitivity, I have to doa lot of editing in those stored procedures. Is there a quick way toavoid the editing?Something like ignoring the case in one statement?Thanks in advance, your advice will be greatly appreciated.
I'm using Microsoft SQL Server Management Studio. I've created a view (very basic two field select with a inner join) -- I expect the query to take some time as there are about 1.7 million records in one table and about 3 million in the join table. Problem is, I always get a timeout error when I try to run the query.
I've checked the server settings and "Execution time-out" = 0 (no time-out). Checked this at the server and in Microsoft SQL Server Management Studio. So my question is why am I getting a time-out when I have it set to not time-out?
I connect, but when I try to access any table from this database I get an error indicating that the object doesn't exist and if I use the fullname xx.table I get no errors. What may be happening?
