Bulk insert error file problem

Transact-SQL

Index ‹ SQL Server

- Previous
  - 1
    - MDX to aggregate measure over specific dimensions Hi, yes I thought those were my two options. I don't want to create another measure group as I'll have duplicate measures and the table with this measure in is large (it's a requirement in our system to keep processing time to a minimum). I was hoping that there would be a solution where I didn't have to list every dimension I wanted to remove from the slice (and remember to add to the list if I add dimensions in the future), but at least I have a solution! Many thanks for your help. James Tag: Bulk insert error file problem
  - 2
    - SQL Statements with inner join too slow in 2005 runs faster in 2000 The trigger is invoked on Study_Area_Support table and this table belongs to dbo schema. All tables in this database belongs to "dbo" schema. All Views in this database belongs to "apl_code" schema. All StoredProcedures in this database belongs to "apl_code" schema. Hope this helps. Thanks again for your help. Tag: Bulk insert error file problem
  - 3
    - Distinct Count Problem I am using a standard star schema structure with a sales fact table and dimension tables joined by surrogate foreign keys. My product's dimension contains a hierarchy for SKU (the lowest level) and product class. I created a distinct count measure for "SKU Count" using the product key in the sales fact table. This works fine with no problem. Now I need to create a second distinct count measure for product class. I cannot get this to work. Product class exists as an attribute in the product dimension. I have tried several ways to get this to work but each time it results in an error. For example, I created an MDX calculation using both COUNT and DISTINCT COUNT and none of these worked. I also added the product class attribute into the fact table to see if this could work by creating a new distinct count against this column. This resulted in an error message. Can anyone help with this Basically, I am looking to create a distinct count measure against the product key in the fact table (which works fine) and than I want to create a second distinct count measure against the product class attribute that comes from the product dimension but is based off of the unique occurences of each product class in the fact table (referenced through the product class key). David Tag: Bulk insert error file problem
  - 4
    - sub-reports don't start in the first page Thanks. I have resorted to using Crystal Reports.NET since this handles subreports more flexibly and looks like it will work for the requirements of this report. Tag: Bulk insert error file problem
  - 5
    - Partitionning a Table Hi guys, I am creating a program, and for that i implements function to partionate a Table (which are too big). In Oracle it's simple : ALTER TABLE myTable SPLIT PARTITION .... But for MS Sql Server 2005, I encounter some problems. Do you have an idea How can i partitionate a Table without make TSQL Functions Is it possible how can I split Tables simply. Thanks ;) Tag: Bulk insert error file problem
  - 6
    - Questions on attributes acting as both inputs and predictive? Hi, Jamie, Thank you very much for your kind advices. It's been really helpful. So as you kindly advised, all the attributes acting both as inputs and predictives will be ignored for themselves's outputs for all SQL Server Analysis Services built-in algorithms. With best regards, Yours sincerely, Tag: Bulk insert error file problem
  - 7
    - Subtotal question Hopefully this is a simple question. We have a report with several subreports that display certain totals by a group. What we want to do now it to show a running total on the main report. Is there a way to pass data from the subreport (the subtotal) to the main report for a Grand Total Thanks for any information. Tag: Bulk insert error file problem
  - 8
    - How do I encapsulate Transactions in a data entry form with multiple insert and update statements? Oh, you can do that I did think that was the problem when I got this issue the first time, but I'm just a beginner, and I had just copy-pasted the code for passing SQL commands in VB from another thread. So I wasn't sure what the results would be. I didn't know I could keep the connection open throughout and not have to open and close it for every SQL command that I have to pass from that subprocedure/eventprocedure. I'll try that and let you know what happens. Tag: Bulk insert error file problem
  - 9
    - MessageSource expression causes false "max width" exception - why? Greetings, I have a package that uses a complex expression for the MessageSource property in a Send Mail task. I receive the email that is composed based on the expression and it is formatted properly. However, the application also throws an exception complaining that the width of the expression may be too wide. This is the error message (with the long expression represented by "...") from the exception: Error Code: -2147192616 Error Description: The result string for expression " ... " may be truncated if it exceeds the maximum length of 4000 characters. The expression could have a result value that exceeds the maximum size of a DT_WSTR. I tried wrapping the expression in SUBSTRING(expression, 1, 4000) to guarantee that the resolved expression could not cause an exception but that didn't help. Numbers lower than 4000 also do not solve the problem. Does anyone have a solution or advice Thanks, BCB Tag: Bulk insert error file problem
  - 10
    - Check for column before adding a column How can I test to see if a column exists before adding a column to a sql mobile table thanks, Luis Tag: Bulk insert error file problem
  - 11
    - Why is this Query Failing I am running 2 queries using a temp table My first query runs fine and creates the temp table #TEMPCOMM1 I then try to run the next query and I get the following error: Msg 245, Level 16, State 1, Line 17 Conversion failed when converting the varchar value ',' to data type int. Code Snippet Select Distinct DLSiteID , PropID , CommAnomalies = substring ( ( SELECT ',' + ErrorLevel as [text()] FROM #TEMPCOMM1 t1 WHERE t1 . DLSiteID = t2 . DLSiteID FOR XML path ( '' ), elements ), 3 , 1000 ) FROM #TEMPCOMM1 t2 Any idea on what I do to fix this problem Tag: Bulk insert error file problem
  - 12
    - Error executing SQL Script from VB:NET Hi. I have a complete script from a Database. But when I try to execute this script (script.txt) from VB.NET shows me many errors. Basically, the special caracters (vbreturn, vbtab, etc.) How execute this script or format the text to generate the database without problems Thanks 4all Tag: Bulk insert error file problem
  - 13
    - Index Fragmentation on SQL 2000 Hi Frank, I saw it, you beat me by a minute or less. The good thing is the consistency of the answers. Cheers, AMB Tag: Bulk insert error file problem
  - 14
    - Store and Retrive Images (JPEG/PNG/GIF/JPG) in sql server express 2005 in ASP.net through C# Hi friends plese help me for my little things. I m student and develop a website. i want to know how images /pics and video and audio files are store in Sql server2005 database. and ya i want to store whole file not just its address. i using asp page like uploadImage.aspx in that i put UploadFiles control now i m confuesd to storing Images so plese help me and give code or some good easy trick at my level And once store image or video and audio file in sql database then after how i used in my asp.net page. which control i wish to used for this files so plese this also slove it. give also code or any easy trick for fullfil my task.. i hope u all help me Thanx in advance to u all friends Tag: Bulk insert error file problem
  - 15
    - Cannot Apply SP2 to 2 new nodes in a 4 node cluster ? HI There We had an existing 2 node active / active cluster, 1 running a default instance of Sql Server 2005 Enterprise Edition 9.0.3152 (SP2 + Hotfixes) and the other running a named instance of the same version. We recently added 2 new nodes to the cluster, they were successfully added and we tested the cluster group failover successfuly to the new nodes. Last night we tried to install Sql Server 2005 Enterprise edition on the new nodes. I followed to proper proceudure of modifying the installation for both instances and selecting the 2 new nodes to apply them to. This went 100%. Sql Server 2005 successfully installed for both instances on the 2 new nodes, all log files were successful. We then tried to apply SP2, we tried the following: 1. We ran SP2 from the active node, but when we go to the screen to select what you want to apply SP2 too we could not select anything, if you clicked on database engine the message said that these instances were already at a later version and we could not proceed. This is how i successfully applied SP2 to the original 2 node cluster but it does not work for additional nodes to an exisitng cluster. This is also what all the documentation we could find said, refer to SP2 release notes under the topic "Failover Cluster Installation", it is also the method we found when googling. 2. We then tried what is described under SP2 release notes "Rebuild a SQL Server 2005 SP2 Failover Cluster Node". We ran SP2 from the new nodes while they were passive, but when we got the screen where you select what to apply the SP2 too we could not select database engine the message at the bottom said that SP2 must be run from the active node and that we were attempting it from the passive node, this is what we tried in step 1 described above. 3. This was a last resort. We were advised to try failing over the instance to the new node and then running SP2. Personally i thought this was a bad idea, one should never fail over a instance of sql server to a node with incompatible binary versions and secondly when we installed sql server on the new nodes a warnng popped up before hand stating that the instances were at a later version and that the new nodes must be at this version before attempting fail over. I thought that sql would not even start, to my surprie we successfully failed over the sql group to the new node, when we ran SP2 it looked good we could select the database engine on the new node to apply SP2 too, BUT after clicking next after a few seconds the SP2 installation just closed, NO INFORMAITONAL MESSAGES NO ERRORS NO WARNINGS it just closed an never came back. I had never seen this happen on a cluster before, needless to say this made me very nervous so we failed the sql group back to the original nodes and gave up. PLEASE can some tell me how to apply SP2 to 2 new nodes in a 4 node cluster all methods descibed in SP2 release notes and other documentation as descibed above in step 1 and 2 do not work ! Thank You Tag: Bulk insert error file problem
  - 16
    - Data transfer from ODBC to Sql Server Thanks Jamie. Previsoly in Sql Server 2000 I used the DTS tool to transfer, No problem for me. To learn abt SSIS I placed this thread in this forum. Is learning a Crime Hope you people will encourage us to learn more. Tag: Bulk insert error file problem
- Next
  - 1
    - Is there any method to back up the database to place outside of the local server system? No problem at all, we are all here to help don't forget to mark the answer to your question, so the thread is considered "resolved" Tag: Bulk insert error file problem
  - 2
    - Error "Connection is busy with results for another command" I have looked at other threads regarding errors similar to this, but I think mine is a bit different. I am using SQL2005 Standard Edition and my application is coded with C# using ADO.Net. OLEDB connection is used. The error occurs when the application has only one thread accessing the database. It does not happen consistently, so it is very puzzling. I am wondering if anyone could offer any tip to diagnose this. Thanks in advance! Tag: Bulk insert error file problem
  - 3
    - SQL2K CmdExec Proxy Account Setup Were you using the local server name for your domain name Tag: Bulk insert error file problem
  - 4
    - Cubes and Cost-Forecast Here it is: iif ( not isEmpty ([Measures].[Gewinn]), [Measures].[Gewinn], iif ( [Time].[Date]. PrevMember IS NULL , null , ([Measures].[Geplanter Gewinn], [Time].[Date]. PrevMember ) ) ) Btw: I also use currency conversions created by the wizzard. The code for the currency comes first; then this calculation comes ... Best wishes, Manfred Tag: Bulk insert error file problem
  - 5
    - INSERTING NULL VALUES VIA STORED PROCEDURES Hi, Becouse some of my stored procedure parameters can be NULL, for every parameter with potential NULL value I have to do checking like this: errorParams[3].Value = (e.InnerException== null ) ( object ) DBNull .Value:( object )e.InnerException; When I do it like this ( it is a part of preparing values for insert ): errorParams[3].Value = e.InnerException no row is added. Is there any way to pass over that check : (e.InnerException== null ) ( and do it easier This the sample exception message for that issue: {System.Data.SqlClient.SqlException: Procedure or Function 'sp_addError' expects parameter '@InnerException' , which was not supplied. Thanks, Pawe Tag: Bulk insert error file problem
  - 6
    - Parse Error I am getting the following error whenever we tries to parse any SQL statements in Micorsoft SQL Management Studio 2005 ��.Net SqlClient Data Provider: Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded.�� Please help me. Svas Tag: Bulk insert error file problem
  - 7
    - Full text prefix search for phrase with noise word? I have data (in SQL Server 2005 SP2) that looks like: ID, Name 1, elbonia university of medical science 2, elb univ med sci 3, elbonia university, med sci 4, elbonia has universities, blah blah blah medium sized scientists And a query that looks like: SELECT ID FROM tbl WHERE CONTAINS(Name, '"elb univ med sci*"') I want it to get 1-3 and not 4, but it just gets 2 and 3. I think the noise word 'of' is throwing if off but if I try: SELECT ID FROM tbl WHERE CONTAINS(Name, '"elb univ of med sci*"') I get nothing! WTF Does a noise word break prefix searches The only message is " (0 row(s) affected)" SELECT ID FROM tbl WHERE CONTAINS(Name, '"elbonia university of medical science"') returns 1 but add the * for stemming: SELECT ID FROM tbl WHERE CONTAINS(Name, '"elbonia university of medical science*"') and it also returns nothing. Thanks for looking! -Andrew Tag: Bulk insert error file problem
  - 8
    - SQL Server Express error when using NUnit Thanks Jens. There are really only 2 things going on in my test code. I just re-create my database and tables (using the "using" construct as shown above) and then test the resulting Membership table to confirm that it has 3 users. When I run the test, the database and tables are rebuilt successfully, but the Membership test fails and gives me the error I listed in my last post. But if I comment out the TestFixtureSetUp code that re-builds the database, and then re-run the test, then the Membership test passes. This makes me think that the only possibility is that NUnit is holding on to the process when I include the rebuild script in my test fixture. But I don't know why this would be if I have wrapped that rebuild code in a "using" statement. Any insights would be appreciated. Terry Tag: Bulk insert error file problem
  - 9
    - Best practice to copy a lot of data between databases Hi, I have a database on sqlserver 2000 which contains some tables which receive Create/Read/Update statements from applications, and +-5 tables which contain a sort of read-only data: the applications are only reading from these tables. Sometimes these 5 tables need to be updated with new data. Currently I am doing this as follows: Execute a long-running operation (e.g. 1 week), which will add new data, against a Test-database (which is a restore from a backup of the original database) Some people do some checks to see if the new data in the Test-database is correct If (2) is OK, the only thing which needs to be done is copying the data of the 5 tables in testdatabase to the 5 tables of the production-database. For (3), I currently use a DTS-package which consists of a "Copy SQL Server Objects"-task. This task is configured to copy the 5 tables (objects) from testdatabase to productiondatabase. The data in these 5 tables is around 20GB, and this task takes a lot of time compared to a backup/restore of the same size of data. I already tried to speed it up by creating different filegroups and wanted to restore only 1 filegroup but you can read here that making a filegroup backup on a testdatabase and trying to restore it on a production database won't work. Is there some other way to speed this up Is my current way of working good practice Thx! Tag: Bulk insert error file problem
  - 10
    - DTS old style and Dynamic Properties Tasks Thanks. You are right, but I am limited in options . Ultimately the brief of this project was to be as quick as possible. I'd love to rewrite them - a chance to learn new stuff! But sadly this is deemed "risky". Pah! Ironically I think we would have been better off rewriting due to issues that could easily be solved with SSIS. The root of this is customer requirement to have a test and production setup on one server, not something we support or recommend! Personally I think we should tell them not to be so tight and get another server onto 2005 already. But again, I dont get to make the decisions...! I think there is no "easy" way to do this. Looks like a fun couple of hours repointing dynamic property tasks. Tag: Bulk insert error file problem
  - 11
    - Help plz - need query to get the following kind of output sorry CetinBasoz i forgot to mention about that.. and thank u so much for ur help.. so long i hv been struggling for this query.. thank u very much.. bye Tag: Bulk insert error file problem
  - 12
    - RAISERROR does not cause SQL task to fail - why? As long as it's posted to the messages tab, to my knowledge, you cannot capture that inside SSIS. Tag: Bulk insert error file problem
  - 13
    - Moving Average using Select Statement or Cursor based? Thanks for picking me up! Tag: Bulk insert error file problem
  - 14
    - Grouping Hi, I have a stored procedure which is returning results in the following format: No.of substances Count 1 29 2 89 3 876 .. .. 15 56 16 89 Now i need to display like this: No.of substances Count % 1 20 2 89 .. .. >=9 8766 Total All the substances which are >=9 have display no.of substances =>9 and Count is sum of all counts which have the no.of substances>=9 How to achieve this Thanks in advance Tag: Bulk insert error file problem
  - 15
    - Problem with "phantom rows" Ok, but then how do I break up the line into columns so that I can map them to my table columns Use a script component after that Tag: Bulk insert error file problem
  - 16
    - SQL Express Help Files Where do I find the Help files for SQL Express. When I click on help it says, help is not available. Tag: Bulk insert error file problem

tunesmith

I am doing a bulk insert of some data that contains a column of xml data. I get an error when trying to do the bulk insert and added the ERRORFILE option. What happens is the error file shows up while the command is running, but as soon as I get the actual error in the messages window of SQL Server Management Studio the error file disappears. The documentation indicates that the error file should contain the records that produce the error, but since the file disappears I'm not able to see the record(s) that produce the error. Please advise, any help is very much appreciated .. thanks, john

I get the following in the messages window:

Msg 9411, Level 16, State 1, Line 1

XML parsing: line 1, character 725, semicolon expected

My bulk insert command:

bulk insert Assessor.dbo.Taxes

from '\\scux00\ExportSQL\exporttaxes.txt'

with (FORMATFILE = 'C:\Taxstatement2columns.xml',

MAXERRORS = 50,ERRORFILE = 'C:\Errors.txt')

the format file:

< xml version="1.0" >
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="10" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\n" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="ParcelID" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="2" NAME="xmlStream" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>