I have a scenario whereby I'd like to insert multiple rows into a table on a SQL server database as efficiently and easily as possible.
After some research, it looked like .NET 3.0's SqlBulkCopy class would do what I want. I've tried to set something up, but it's not working. It's not even throwing an error. The code executes but it simply hasn't done the insert by the end of it!
My table structure is simple. The name of the table is LPSTUnavailableDate. It has just two columns, one of them an auto-populated ID field:
I have an OLAP database which is required to update daily. First of all back track a bit about this OLAP database, It is built from an OLAP data source consists of a dozen "Dimension" tables and half a dozen "Fact" tables and they are set up in a normalized relationship. This 20 or so OLAP tables are created from a single "Production" data warehouse de-normalized table. By the way the database engine, SSAS, SSRS and SSIS are installed in one virtual server using Sql 2005 SP2.
Here are the steps I update the OLAP database manually.
At Sql database engine
Drop the existing data warehouse table at the virtual server
Copy the up-todate data warehouse table from a remote server and paste to the database at the virtual server
Drop all the OLAP Fact tables
Drop all the OLAP Dimension tables
Create the Dimension Tables using the Sql script I saved when first created
Create the Fact Tables using the Sql script I saved when first created
Populate the data to the Dimension Tables from the data warehouse table using the Sql script I saved when first created
Populate the data to the Fact tables from the data warehouse table using the Sql script I saved when first created
Assign indexs and constrains to the Dimension Tables
Assign indexs and constrains to the Fact Tables At Sql Server Analysis Services
Process the 15 Dimensions
Process the 2 OLAP cube I want to automate the whole process using SSIS. I am new to SSIS and I need some direction on how to make it work?
Hi, I have a query thatI need to make into one query for the sake of an application reading only one cursor.
Here's the syntax: select (select distinct(x.amount) from escrow k inner join e120 x on k.escrow = x.escrowinner join a10 g on x.escrow = g.escrow where k.ftype = 'S' group by x.amount, g.officer) As New,a.officer as Officer, count(distinct(j.fstatus))as Escrow_Type, count(distinct(j.amount))as Amount, count(distinct(d.open_date))as [Open], count(distinct(d.close_date)) as Closed, count(distinct(can_date))as Cancelled from a10 a inner join escrow d on a.escrow = d.escrowinner join e120 j on j.escrow = d.escrow where j.id_scr = 'e21' and j.fstatus = 'PAID' group by a.officer
The error message i'm recieving is the following:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I am currently stuck on how to make this 2 separate query work together, both work as i want them to individually, please note the syntax is according to a application i use that uses mysql to manipulate columns in an imported csv file.
CONCAT('at-', REPLACE([CSV_COL(18)],'http://www.homebuy.co.uk/product.php/','') ) removes last character i.e. / SUBSTRING([CSV_COL(18)], 1, CHAR_LENGTH([CSV_COL(18)]) - 1)
basically i need these 2 to work together to give me an output like this
at-09fd8903
from the this. URL...url above minus the "" as i said both work on there own, but not together.
Hi - I'm in a situation with a very large table, and trying to run an update that, any way I've approached it so far, seems to be taking unnacceptably long to run. Table has about 20 million rows, looks something like this:
ID - int, identity Type - varchar(50) PurchaseNumber - varchar(50) SalesAmount - Money
ID Type PurchaseNumber SalesAmount 1 A 3834AA38384 20.32$ 2 B 3834AA38384 11837.32$ 3 C 3834AA38384 666.32$ 4 C 887DF88U01H 23423.32$ 5 A 887DF88U01H 12.32$ 6 B OI83999FH28 4747.1$ 7 D 38438495985 9384.6 8 E 02939DDJJWI 22.22$ 9 F 07939SDFDF2 33.33$
The goal of the update is to make the [Type] uniform across [PurchaseNumbers], according to the max sales amount. For each PurchaseNumber a, set the type = the type of the row that has the MAX salesAmount. If there is only one entry for PurchaseNumber, leave the type alone. Expected update after completion would look like this:
ID Type PurchaseNumber SalesAmount 1 B 3834AA38384 20.32$ 2 B 3834AA38384 11837.32$ 3 B 3834AA38384 666.32$ 4 C 887DF88U01H 23423.32$ 5 C 887DF88U01H 12.32$ 6 B OI83999FH28 4747.1$ 7 D 38438495985 9384.6 8 E 02939DDJJWI 22.22$ 9 F 07939SDFDF2 33.33$
I got this out of a warehouse, and it definitely isn't normalized well. Was considering breaking down into a better model, but I'm not yet sure if that would make the update easier.
I've been approaching this with sub-queries (finding all the PurchaseNumbers with more then one entry, then the max sales purchase of that purchase Number, then the type of that purchase number and sales amount to update all of that purchase number) but this not only ends up a little messy, but also very slow.
The only other detail that may be important is that out of the 20 million total rows, about 19.5 million purchaseNumbers are unique. So, really, there are only about 500k rows I actually have to update.
I've thought of a few ways to make this work, but none of them seem fast and wanted to see if anyone had a pointer. Thanks!
Hello, I have what should be a very simple problem, but I cant solve it. I want to have a stored procedure return a table query (no problems here) but I also need to supply several parameters to the stored procedure (again, no problem!)
Here is the problem, I need to be able to supply a wildcard into the stored procedure as an argument somehow. I can do this already, but the results are incorrect!!! It seems like when local variables are used, the wildcard argument gets ignored. for example, I have included the following example:
DECLARE @Dv_id nchar(15)
SET @Drv_id = '%'
SELECT Diver.*, (ROW_NUMBER() OVER(ORDER BY Dv_id)) as RowNum FROM Diver WHERE Dv_id LIKE @Dv_id
SELECT Diver.*, (ROW_NUMBER() OVER(ORDER BY Dv_id)) as RowNum FROM Diver WHERE Dv_id LIKE '%'
OK, this is an example of my problem, the results I get from this are that the fist SELECT return 0 rows. The second SELECT returns the correct number of rows (everything in the table). Why is there a difference between: WHERE Drv_id LIKE @Drv_id and WHERE Drv_id LIKE '%' ? The wildcard statement '%' is supposed match everything, correct?? It seems like the local variable SET command syntax eats up my value of '%' and turns it into a NULL.
Good Morning, I've been searching through all the tutorials and questions, have tried many things. I am still getting "[SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)]"
as an error. This is what I've got:
I€™m using SQL Server Express 2005, installed with the default settings. Had not touched this program for anything until I started to follow directions to fix Error 26. Visual Web Developer Express ASP.NET is what I€™ve used to build the webpage. I was using the ASP.NET web configuration to add users to the database, which is set to use the provider ASPSqlServerProvider. SQL Server 2005 Surface Area Configuration Database Engine Remote Connections €“ set to €œLocal and Remote Connections €“ Using TCP/IP Only€? SQL Server Browser is Enabled and Running - is set to Active, under Built In Account €“ Network Service I have created Windows firewall exceptions for: sqlservr.exe sqlbrowser.exe udp port 1434 SQL Server Configuration Manager both SQL Server and SQL Server Browser are running. Under SQL Server 2005 Network Configuration Shared Memory and TCP/IP are enabled only.
SQL Native Client Configuration Shared Memory 1 TCP/IP 2 Named Pipes 3 all enabled
I read through the post at http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx but as I'm new to SqlServer I do not know how to check on the first three items.
I'm getting extremely frustrated, and would just like the login portion of this website to start working before my boot ends up through the computer. Please help, lol, thank you.
Below is the stored procedure i have it works fine if i have 1 value passed to @invited_by but i want to modify but i want this code to be working for multiple inputs .Lets say if i do
exec [dbo].[sp_GetInvitationStatusTest] 'Test1 . I get the desired output but i want this procedure to work for exec [dbo].[sp_GetInvitationStatusTest] 'Test1,Test2'. USE [merck_acronyms] GO
Clean vista install, Clean full office 2007plus install Clean visual studio 2008pro install Many failed sql 2005dev installed, did SKUUPGRADE-1 Now have office2007 Smallbiz, VS2008pro, SqlExpress and sql 2005dev installed. Note outlook 2007 error on first run after sql2005dev. Seams to have broken and created a new sql connection, don€™t think its the original. but that€™s not the current issue ! web admin page error, could not connect to db I did regsql.exe from the net dir and the web admin work and the db is created for roles and such in vs 2008 via web admin page and mssql. Managed to create db in apps data folder and modify connection in server explorer. Added table to aspx page from apps folder and it runs via f5, but errors under localhost. €œCannot open user default database. Login failed.€? €œLogin failed for user 'NT AUTHORITYNETWORK SERVICE'€? Have seen some reference to iis permissions, but not sure what to change? Is it a file level permission, a virtual permission Works in vs2008 using f5 run! http://localhost:50115/vs2008/ Can work with apps folder db file in vs 2008 server explorer Had to modify db connection back to sqlexpress in server explorer Can work with db in sql management studio, have three local instances; Pc Pcsqlexpress Pcsmallbiz Can display db data in aspx page via f5 , but not localhost unless vs2008 is not running!!! Have not been able to make vs2008, Sql2005dev, sqlexpress all work together using iis in vista via localhost and vs2008 f5 at same time?? Can€™t do anything with db in solutions explorer, but can work in server explorer if I modify connection back to sqlexpress Can€™t display data aspx page via localhost http://localhost/vs2008/ Cannot open user default database. Login failed. Login failed for user 'NT AUTHORITYNETWORK SERVICE' Localhost works in iis via virtual directory if I remove db grid in page or vs is not running Could someone test and tell how to use all of these together? What is going to happen if I get it to work local and then try to upload to shared host? <connectionStrings> <remove name="LocalSqlServer" /> <add name="LocalSqlServer" connectionString="Server=wabpc;Database=VS2008test;Integrated Security=true" providerName="System.Data.SqlClient" /> <add connectionString="Server=SQLEXPRESS;Database=Database.mdf;Integrated Security=true" name="sqlexpress" />
</connectionStrings> If I close vs2008 and the management studio, localhost works and displays the db info!! Open vs2008, modify connection back to sqlexpress and the aspx page breaks via localhost Close vs2008 and local host works Open vs2008 and get a error 25 in server explorer connection string invalid, but f5 works after the offline clears. I change db connection back to sqlexpress to work with db in server explorer! That breaks localhost, but f5 works
Sorry for the less then descriptive post title but I didn't find a better way to describe it. I'm developing an app in the express editions of VB and SQLserver. The application is a task/resource scheduler. The main form will have a datepicker or weekly overview and show all tasks scheduled per day. The problem is, I've got one or more people assigned to tasks and I wonder what's the best way to design this. Personally, I'd go for one Task table, a People table and a table that provides a link between them (several record per task, one for each person assigned linking TaskID and PplID). However, I don't see a nice way of showing this data to the end user, allowing him to edit/add etc on ONE screen.
To fix that the only way I see is just add columns to the Task table for every person with select boxes. This way everything can be done on one simple screen. This obviously does present some future issues.
On top of this, which people are available on a day varies and there should be an option to allow a user to set who is available on a specific day. Which would lead me to my first idea and add another table that would provide this. but then I'm having design issues again for the form.
I'm kinda stuck atm, can anyone shed some light on this. I'm sure there is an elegant way of doing this but I'm failing at finding it.
I am using SQLBULKCOPY to copy Excel spreadsheet into SQL Express Database table. The copying went okay, but the only problem I have is that few records in one of the columns is NULL. The column contains numeric and alphnumeric fields in the spreadsheet. After copying, the numeric fields got copy with no complication, but all the alphanumeric fields all becam NULL. Can someone help me with this problem?
HI, I have one doubt. Is it possible to transfer data from one SQL Server to other SQL Server over the LAN.( Both the SQL Server database are in different cities)
Hello, I was curious if anyone knew if using SqlBulkCopy in code required any special permissions on the database side. I wasn't sure if more permissions than writing capabilities were needed. Thanks.
I need to copy large amounts of data between SQL databases, and between SQL and Access. I've been reading a lot of good explanations of SQLBulkCopy, but the only good examples I've found are written in C# and I work in VB. I'm very new to ASP.NET 2.0 (about a month) and came from Classic ASP, not .NET 1.1. I'm still getting my feet wet and I'm afraid it doesn't take much to confuse me. Can someone point me to a good article or tutorial that includes a clear example written in VB? Diane
Hi, I am trying to use sqlbulkcopy to insert the contents of a data table to a temporary table in the database. The content of data table comes from a csv file. I can add the rows to the data table but it seems they are added as strings and when I try to WriteToServer(datatable) it chokes becuase my table has some int fields. Not sure how to do this.private static TimeSpan DoBulkCopy(string filePath) {Stopwatch stopWatch = new Stopwatch(); stopWatch.Start(); StreamReader sr = new StreamReader(filePath); prepareTable();string fullFileStr = sr.ReadToEnd(); sr.Close(); sr.Dispose(); string[] lines = fullFileStr.Split(''); DataTable dt=new DataTable() ;string[] sArr =lines[0].Split(',');foreach(string s in sArr) {dt.Columns.Add(new DataColumn()); } DataRow row;string finalLine = "";foreach (string line in lines) { row = dt.NewRow();finalLine = line.Replace(Convert.ToString(' '), "");row.ItemArray = finalLine.Split(','); dt.Rows.Add(row); } SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["connectionString"].ToString());System.Data.SqlClient.SqlBulkCopy bc = new System.Data.SqlClient.SqlBulkCopy(cn, SqlBulkCopyOptions.TableLock, null); bc.BatchSize = dt.Rows.Count; cn.Open();bc.DestinationTableName = "tmpTable"; bc.WriteToServer(dt); // <--------------- it dies here cn.Close(); bc.Close(); TimeSpan ts = stopWatch.Elapsed; stopWatch.Stop();return ts; }private static void prepareTable() {SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["connectionString"].ToString()); string sql = @"if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tmpTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[tmpTable]; CREATE TABLE [dbo].[tmpTable] ([Remote] [int],[KFP] [int]) ON [PRIMARY]";SqlCommand cmd = new SqlCommand(sql, cn); cn.Open(); cmd.ExecuteNonQuery(); cn.Close(); cmd.Dispose(); }
I have a problem using SqlBulkCopy for updating tables. In fact, I can´t update any table. I use this function for insert big groups of records, but I would like to know how I can configure SqlBulkCopy for update rows. I would like to see if there is a record (primary key exists) then update it.
Does SqlBulkCopy have any adverse affects on it's target table indexes? I like the performance gain but am worried about creating unnecessary table scans if the indexes/stats are not updated properly after it completes...
I'm using SqlBulkCopy. Does anyone know how I can output what row (its column names) are throwing a duplicate primary key message when I bulkCopy.WriteToServer(datatable1)?Thanks
I have a collection of around 16000 records, and have been trying to find the best way to update the information in the DB. I have done alot of reading about both BulkCopy and Batch Update, but haven't come to any clear solutions as to which performs better. I am not doing any inserting, just getting a dataset from the DB, changing the values, them want to update the Db again. Thanks for any help. Mick
I'm in the final throws of redesigning a web application, and one of the major improvements is adding in an admin page for the users so they can maintain the data and system structure without the need for us to get involved. One of the areas I'd held back on was promotion of data from SQL server to SQL server - once .NET 2 framework came in and we moved over to VS2005, I'm now in a position to do this work. I have tested it and it works perfectly well for small tables, but as soon as I try it on a reasonably large table (860,000 rows) I get the following error in VS's output window: "A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code Additional information: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." The application data is stored on SQL2005 servers, and I am running the application locally. I have tried running the copy from table to table on the same server (different databases) and i get the same result, which leads me to believe that it may be memory related. Running the SQL in SQL Server Management Studio works in just 15 to 20 seconds, but using VS falls over in just under a minute. My connection string has a timeout of 3600. Here's my C# code:// Execute reader... using (IDataReader vReader = vCmd.ExecuteReader()) { // Create SqlBulkCopy... SqlBulkCopy vBulkData = new SqlBulkCopy(aTargetConn); // Set destination table name... vBulkData.DestinationTableName = aTableName; // Write data... vBulkData.WriteToServer(vReader); }
aSourceConn and aTargetConn are the appropriate SqlConnections, and aTableName is the table to be populated with data (previously backed up and emtied of contents). Any help/advice suggestions gratefully received - if any more info needed please ask. Thanks Martin
I am trying to import a CSV file into an SQL Server table with the OleDbDataReader and SqlBulkCopy objects, like this: using (OleDbConnection dconn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\mystuff\;Extended Properties="text;HDR=No;FMT=Delimited"")) { using (OleDbCommand dcmd = new OleDbCommand("select * from mytable.csv", dconn)) { try { dconn.Open();
using (OleDbDataReader dreader = dcmd.ExecuteReader()) { try {
using (SqlConnection dconn2 = new SqlConnection(@"data source=MyDBServer;initial catalog=MyDB;user id=mydbid;password=mydbpwd")) { using (SqlBulkCopy bc = new SqlBulkCopy(dconn2)) { try { dconn2.Open(); bc.DestinationTableName = "dbo.mytable"; bc.WriteToServer(dreader); } finally { dconn2.Close(); } } } } finally { dreader.Close(); }
} } finally { dconn.Close(); } } } A couple of the columns for the destination table use a bit datatype. The CSV files uses the strings "1" and "0" to represent these.When I run this code, it throws this exception:Unhandled Exception: System.InvalidOperationException: The given value of type String from the data source cannot be converted to type bit of the specified target column. ---> System.FormatException: Failed to convert parameter value from a String to a Boolean. ---> System.FormatException: String was not recognized asa valid Boolean. at System.Boolean.Parse(String value) at System.String.System.IConvertible.ToBoolean(IFormatProvider provider) at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider) at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType) --- End of inner exception stack trace --- at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType) at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaDatametadata) --- End of inner exception stack trace --- at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaDatametadata) at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal() at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount) at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader) at MyClass.Main()It appears not to accept "1" and "0" as valid strings to convert to booleans. The System.Convert.ToBoolean method appears to work the same way. Is there any way to change this behavior? I discovered if you change the "1" to "true" and "0" to "false" in the CSV file it will accept them.
I am trying to use SQLBulkCopy to copy from an excel spreadsheet to a table in a SQL database and it is all working fine, but I have to now change the connection to the SQl database to an ODBC connection and it is now erroring. This is the error I get - keyword not supported 'dsn' Is it possible to use SQlBulkCopy when using an odbc connection to the SQL database?
I have encountered a very frustrating situation when trying to use SQLBulkCopy. I have two excel files that I am trying to import into two tables in an MSSQL Server 2005 Express DB. One excel file has 5,000 rows, while the other file has 500,000 rows.I was able to import the smaller file successfully using this vb.net code: Protected Sub L26ExcelToSQL() 'Declare Variables Dim sSQLTable As String = "Local26Members" Dim sExcelFileName As String = "Full Local 26 List Formatted.xls" Dim sWorkbook As String = "[Sheet1$]"
Dim sSqlConnectionString As String = ConfigurationManager.ConnectionStrings("SiteSqlServer").ConnectionString.ToString 'Execute a query to erase any previous data from our destination table Dim sClearSQL = "DELETE FROM " & sSQLTable Dim SqlConn As SqlConnection = New SqlConnection(sSqlConnectionString) Dim SqlCmd As SqlCommand = New SqlCommand(sClearSQL, SqlConn) SqlConn.Open() SqlCmd.ExecuteNonQuery() SqlConn.Close() 'Series of commands to bulk copy data from the excel file into our SQL table Dim OleDbConn As OleDbConnection = New OleDbConnection(sExcelConnectionString) Dim OleDbCmd As OleDbCommand = New OleDbCommand(("SELECT * FROM " & sWorkbook), OleDbConn) OleDbConn.Open() Dim dr As OleDbDataReader = OleDbCmd.ExecuteReader() Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(sSqlConnectionString) bulkCopy.DestinationTableName = sSQLTable bulkCopy.WriteToServer(dr) OleDbConn.Close() End Sub However, when I tried to import the 500,000 row excel file, I got the following error: Server Error in '/L26' Application.
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
[SqlException (0x80131904): A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +925466 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800118 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186 System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) +556 System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj) +164 System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected) +34 System.Data.SqlClient.TdsParserStateObject.ReadBuffer() +44 System.Data.SqlClient.TdsParserStateObject.ReadByte() +17 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +79 System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal() +1336 System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount) +916 System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader) +151 _Default.CSVToSQL() in d:hostingmemberwolsite1L26DuesDefault2.aspx.vb:440 _Default.ButtonTest3_Click(Object sender, EventArgs e) in d:hostingmemberwolsite1L26DuesDefault2.aspx.vb:905 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746
Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433 After I received this error message, I tried viewing my database through the MSSQL Control Panel utilized by my hosting provider (WebHost4Life). However, I was unable to connect to the database and received this error: ___________________Microsoft OLE DB Provider for SQL Server error '80040e14' Database 1496 cannot be autostarted during server shutdown or startup. /getDBinfo.asp, line 29
_____________________ Now here is the most frustrating/mysterious part. I figured that maybe the error message were a result of the large size of the second excel file, so just for testing purposes, I created a new table in my MSSQL database. The table just has two fields, both set to varchar(50). I then created a test excel file, that had one row with the word "test" in the first and second column. When I tried using the code above to import the test excel data into the test table, I got the same exact error as I did with the 500,000 row file!Please help, I'm really stumped and I am not sure when I am having so much trouble replicating the success I had the 5,000 row file. Any suggestions are much apprecaited. -Bryan
I have created an assembly which I load into SQL 2005. However, if I set my connection string = context connection = true... I will get an error saying something like this feature could not be used in this context... So I changed my function to insert each row.... Now the issue I have is the transfer takes 4X as long.... Before I made the change I was using the bulkcopy by specifying the actual connection string....but I also had to specify the password in the string...and since I wanted to get way from this specification...I attempted the context route. So...is there any other way of using the bulkcopy feature or something like it using the context connection?
Private Shared Function BulkDataTransfer2(ByVal _tblName As String, ByRef _dt As DataTable, ByRef emailLog As String) As Boolean
Is there a JDBC equivalent of the SqlBulkCopy command?
Simply using batched INSERTs, it takes days to insert 1m rows into SQL Server. However using C# client that uses SqlBulkCopy I can load it in about 1 hour.
I have deveoped a replacement for some an old bcp based applications in the .Net Framework that uses the SqlBulkCopy class.
I have run into some difficulties with code page translation:
The original BCP client runs with OEM Codepage 437 , thus the data "ëÄÆòÖ" gets loaded as "d-¦=+". DB is SQL_Latin1_General_CP1_CI_AS, column is varchar.
I have been unable to perform any code page Encoding in .Net that yields the same result.
I want to emulate this behaviour in my database loader but as yet have been able to find a way....
I have the following table: create table tTest ( x varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS )
I need to populate this table using SqlBulkCopy, however some symbols are inserted with mistakes.
Here is an example of the code that I€™m using: using (SqlBulkCopy copier = new SqlBulkCopy(ci.ConnectionString)) { copier.DestinationTableName = "tTest"; DataTable tbl = new DataTable(); tbl.Columns.Add(new DataColumn("x"));
SqlBulkCopy does not seem to have much flexibility. If your table has columns that dont allow duplicates, you are apparently screwed. Its a shame there is no switch or param setting you can issue the SqlBulkCopy class (if there is, please let me know!!)Example:Say I have a table "Cars" with fields "CarId" (identity) and "CarName" (varchar) and the CarName field has a unique constraint. Now, I have a DataTable that contains a bunch of CarNames to insert. If there are duplicates on CarName, the entire insert fails. This is nothing to do with the PK or identity field. The problem I have: I would much rather have an option to ignore or silently not insert that duplicate row, but continue to insert the rest of my data. Any known work arounds for this would be much appreciated. Maybe I am missing something?
Hi There, I'm trying to use a sql bulk copy to transfer data from xml file to a table in one of my page. In this page I'm doing 2 database related. The first is a simple insert that will return a value and the second one is the sql bulk copy data transfer. I'm using the same connection for both of them and the sql bulk copy always give me a "login failed" error while the insert is fine. Do I need to set a specific setting for the sql server account so that it can use sql bulk copy? Thank you
I have programmatically created a SqlConnection that begins a SqlTransaction. During the first part of this SqlTransaction, the contents of a table are deleted. The next part uses the SqlBulkCopy object to copy data from another database (in the form of a DataTable). The delete goes through fine, but the SqlBulkCopy always generates a SqlException with the message "Unexpected existing transaction." I cannot think of anything I am doing wrong. The code looks at an XML file for instructions on each transaction. Each transaction is composed of tasks. Each task will pull data from a different type of database (MVR.Command is a Factory Database object). Please view the code below and tell me if you can spot what I am doing wrong: using (SqlConnection destinationConnection = new SqlConnection(MVR.ConnectionSource.GetConnectionString(destinationServiceName))) { destinationConnection.Open();
using (SqlTransaction transaction = destinationConnection.BeginTransaction(IsolationLevel.Snapshot, "Transport")) { transaction.Save("Beginning");
int totalTasks = 0; int successfulTasks = 0;
foreach (XmlNode taskNode in transactionNode.SelectNodes("Tasks/Task")) { totalTasks += 1;
try { // Prepare the destination table (delete everything) int rowsDeleted = new SqlCommand("delete from " + destinationTablename, destinationConnection, transaction).ExecuteNonQuery();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection)) { bulkCopy.DestinationTableName = destinationTablename;
// Based on the success of all tasks, either commit or rollback if (successfulTasks == totalTasks) { transaction.Commit(); } else { transaction.Rollback(); } }
I have written an app that will allow you to send a query to Teradata, return the results into a Reader and then Bulk Copy that data into SQL Server 2005.
If the query results in a large dataset (ie 20,000,000 rows) is processed then while that data is being bulk copied into SQL Server, using the SQLBulkCopy class, then it prevents users on other computers from logging into SQL Server Management Studio.
Those that are already logged in are shut down also. Everything appears fine to the users but queries do not finish running.
Everything immediatly starts working as normal when either my program finishes or I shut down my program.
Is there any type of property to the SQLBulkCopy class or any other function that will prevent Management Studio from locking up?
