Bulk insert error file problem 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> Tag: SQL Express Help Files
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: SQL Express Help Files
SQL2K CmdExec Proxy Account Setup In 2000 the links below is where you get all you need to create the proxy.
http://msdn2.microsoft.com/en-us/library/aa260689(SQL.80).aspx
http://msdn2.microsoft.com/en-us/library/Aa260700(SQL.80).aspx Tag: SQL Express Help Files
Cubes and Cost-Forecast Hi,
I've tried this. But SSAS seems to get into an infinity-recursion ...
How to work around this issue
Best Whishes,
Manfred Tag: SQL Express Help Files
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: SQL Express Help Files
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: SQL Express Help Files
SQL Server Express error when using NUnit Thanks Jens. That got me closer, but I am now getting a different error. I set the default database for user ¡®TERRY-COMPUTER\Terry¡¯ to master (using SQL Server Management Studio in the \Security\Login folder) and that seemed to get rid of the default database error. But I am now getting the following error:
MyApp_Tests.DatabaseMembershipTestFixture.MembershipUsersTest : System.Data.SqlClient.SqlException : Unable to open the physical file "C:\MyApps\MyAppsWeb\App_Data\MyApp.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
An attempt to attach an auto-named database for file C:\MyApps\ MyAppsWeb\App_Data\MyApp.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
Again, this MembershipUsersTest runs successfully if I comment out the code that re-builds the database. I¡¯m not clear why the code to rebuild the database would lock up the database if I have wrapped that code in a "using" construct (ensuring that the connections close). Based on this error however, it seems like NUnit is somehow keeping a link to this database. Any insights would be appreciated. Tag: SQL Express Help Files
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: SQL Express Help Files
DTS old style and Dynamic Properties Tasks Thanks. No, unfortunately it is not. Would you be able to elaborate on how to set that up
In the Dynamic Properties Task, to see what value is being picked up for a given eg connection, I click through to a window entitled Add/Edit Assignment.
Source is set to INI File.
There is then the File box which allows me to enter eg c:\myconfig\myconfig.ini
Are you saying it is possible to add a variable into that box Tag: SQL Express Help Files
Moving Average using Select Statement or Cursor based? WOW!!! Marvelous. I have been thinking and figuring out this for a long long time. How To do it without using cursor and looping the cursor row by row. THANK YOU VERY MUCH !!! I never thought it was so straightforward. The reason I can not figure it out because I never thought you can declare variable at update statement to store the previous calculations.
Can I just use the Select Statement from the CTE directly in the Insert statement to insert the record without using the CTE Why do you use CTE anyway Why do use the row_number() I have read the help file, but i dont understand. I believe the purpose is to build the result set in sequential order. What is the purpose to use seq while you can just order the result set What do you think about the performance Because each time user add new or adjust sales data I need to be able determine the cost average on the fly
And If I want to adopt the FIFO costing method do I need to add another table or Field I know this more about db design issues but please help me Tag: SQL Express Help Files
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: SQL Express Help Files
Problem with "phantom rows" Hi,
Are you saying to get rid of my script component and use a conditional split instead
I would have no clue how to do this, though!
If you could show an example syntax, I would appreciate greatly.
Thanks Tag: SQL Express Help Files
Design question - Foreign key indexes Hi:
I think there is another consideration and it is performance -not on insertion of data - but at ETL time, when you want to determine if a given row is already present in a dimension (locating a row).
Most of the times, I just index for ETL speed. OLAP Processing , because of the amount of rows read, may easily go to be a table/partition scan, making indexes almost useless.However, that also depends on the cardinality of your attributes, and what kind of processing you use in your OLAP structures
Alejandro Leguizamo
SQL Server MVP
Colombia Tag: SQL Express Help Files
When to reindex? All you ever needed to know about indexes and more....
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx Tag: SQL Express Help Files
Calling CLR stored procedure from within a CLR table-valued function giving errors You cannot use SqlPipe inside TVF, as well as call stored procedures. This restriction is based on requirement that functions must not change the content of the database, so if functions would be able to call SPs they would be able to update database through these SPs. Tag: SQL Express Help Files
Scheduled Backup suddenly started giving errors I agree with Deepak. This definitely seems like a permissions error. Perhaps the priveleges of an account have changed recently Setting the schedule to run as "sa" should do the trick.
Remember, you can run manual backups using the T-SQL BACKUP command or through Management Studio while you troubleshoot this problem.
HTH! Tag: SQL Express Help Files
OLEDB Access PB Hi plab,
You may even use a DataConversion transformation to change type DT_WSTR to DT_STR with a required length.
Thanks
Subhash Subramanyam Tag: SQL Express Help Files
Maximum number of users (licensing) I need a small SQL server for up to 10 users. If I understand the EULAs and FAQs correctly, there is no limit on the number of users imposed by the SQL Server 2005 Express license. If so, then the number of users is only limited by the OS's license so all I need is SQL Server 2005 Express (free), Windows XP Pro (10 users limit) and no CALs for either Windows or SQL Server. I'd be thankful if someone could verify this. Tag: SQL Express Help Files
Scheduling sqlcmd script in sql agent I was hoping that we could run a sqlcmd script in a normal agent job, similar to the ability to run them in sql management studio window. The advantage of doing that is, the entire solution is in one place.
From your email it sounds as if, there is no way to do this and the only choice is to run a cmdshell agent job or to schedule in the Windows scheduler. Tag: SQL Express Help Files
How do i change collation? You have to change all the collations of the tables and BIN(binary sort) require case sensitive data including your queries. You have to look for script or manually ALTER each table with Enterprise Manager, it will take some time because it drops your original table and create a new one under the hood. Post again if you still need help. Tag: SQL Express Help Files
connecting to sql server from mobile device It's all good and pretty except for one little thing: when i try to open the connection i get a "PlatformNotSupportedException" is there any reason that exception could appears Tag: SQL Express Help Files
Scheduling report delivery for the first WORKING day in the month? Has anyone figured out a good way of doing this The only suggestions I have seen along these lines involve scheduling the job every day and then manually editing the SQL Agent job that is created. However the problem with this is that it completely removes the ability for non programmers to set the schedule themselves using the web interface. Any ideas Tag: SQL Express Help Files
fb301
Where do I find the Help files for SQL Express. When I click on help it says, help is not available.