I have setup an 'indexed view logged' article so that I can replicate my view to a table in the destination server. I need to do transactional replication without changing the data at the destination so i used 'Keep existing object unchanged' in the article but I'm always getting 'The process could not bulk copy into table '"nnn"."musictracks1"'.
any suggestions,
Thanks
SQL Server 2000 error message:
The process could not bulk copy out of table '[dbo].[syncobj_0x3944323636373031]'.
Snapshot replication was running fine up until last week. The e drive on the system failed. The server was rebooted and the drive returned. Since then the replication has failed. (e drive is the location of the database and log files)
The database is approx. 12 gig. I googled the error message an found several references to the drive being full. So I tried to map the replication to a different drive. The replication fails with the same error.
Also tried to drop and recreate the replication process. Still the same error.
Any other ideas?
TIA
Joel
Hi guys I have a new one....
Still in my republisher scheme I added a trigger at the publisher server created the snapshot than reinitialized the subscription , started the merge agent and the trigger passed. But at the republisher I followed the same technic that worked previously and now it gives me those errors when I start the merge agent.....
The process could not deliver the snapshot to the Subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
---------------------------------------------------------------------------------------------------------------
The process could not bulk copy into table '"dbo"."MSmerge_genhistory"'.
(Source: ***************** (Agent); Error number: 20037)
---------------------------------------------------------------------------------------------------------------
Cannot insert duplicate key row in object 'MSmerge_genhistory' with unique index 'unc1MSmerge_genhistory'.
(Source: ***************** (Data source); Error number: 2601)
---------------------------------------------------------------------------------------------------------------
Function sequence error
(Source: ***************** (ODBC); Error number: 0)
---------------------------------------------------------------------------------------------------------------
Does anyone have any idea of what to do ??? except delete the subscription....
Can I still use bcp comman to export data into a table when the target table has a referential integrity with another table ( parent child relationship ) .. thanks
Ahmed
Hello everybody,
I'm trying to setup transaction replication between 2 servers. This is a
one-way replication: Server A to Server B, not Server B to Server A.
I am able to replicate all the tables except one. I added
commands to the agent so that it would create an output file, possibly with
more or better information.
Here is a portion of the error causing the failure
Agent message code 20037. The process could not bulk copy into table
'"tblSuppContractFee"'.
[5/5/2006 8:02:10 PM]01sqlft003.distribution: {call
sp_MSadd_distribution_history(4, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?, 6, 0x01,
0x01)}
Adding alert to msdb..sysreplicationalerts: ErrorId = 65,
Transaction Seqno = 000075400000ff9b000b00000002, Command ID = 6
Message: Replication-Replication Distribution Subsystem: agent
01sqlft003-EDGE-01SQLFT004-4 failed. The process could not bulk copy into
table
'"tblSuppContractFee"'.[5/5/2006 8:02:10 PM]01sqlft003.distribution: {call
sp_MSadd_repl_alert(3, 4, 65, 14151, ?, 6, N'01sqlft003', N'EDGE',
N'01SQLFT004',
N'EDGE_REPLICATION', ?)}
ErrorId = 65, SourceTypeId = 1
ErrorCode = ''
ErrorText = ''
[5/5/2006 8:02:10 PM]01sqlft003.distribution: {call sp_MSadd_repl_error(65,
0, 1, ?, N'', ?)}
[5/5/2006 8:02:10 PM]01SQLFT004.EDGE_REPLICATION: exec
dbo.sp_MSupdatelastsyncinfo N'01sqlft003',N'EDGE', N'', 0, 6, N'The process
could not bulk copy into table
''"tblSuppContractFee"''.'
Can somebody help me in finding a solution for this error? I don't see any
Error Text and there are no resources available for the error code throwing up
in the log file.
Thanks in advance.
Hi~,
Before implementing memory based bulk copy insert with IRowsetFastLoad interface of SQL Server 2005 OLE DB provider, I want to know some considerations.
- performance : compared with T-SQL's "BULK INSERT ..." and bcp utility
- SQL Server's resource usage : when running memory based bulk copy, server resource's influence
- server side action(behavior) : when server is busy, delayed-update means IRowsetFastLoad::Commit(true) method can insert right after?
- row-count : The rowcount limitation can be inserted by IRowsetFastLoad::InsertRow() method before IRowsetFastLoad::Commit
- any other guide lines
Hi~, I have 3 questions about memory based bulk copy.
1. What is the limitation count of IRowsetFastLoad::InsertRow() method before IRowsetFastLoad::Commit(true)?
For example, how much insert row at below sample?(the max value of nCount)
for(i=0 ; i<nCount ; i++)
{
pIFastLoad->InsertRow(hAccessor, (void*)(&BulkData));
}
2. In above code sample, isn't there method of inserting prepared array at once directly(BulkData array, not for loop)
3. In OLE DB memory based bulk copy, what is the equivalent of below's T-SQL bulk copy option ?
BULK INSERT database_name.schema_name.table_name FROM 'data_file' WITH (ROWS_PER_BATCH = rows_per_batch, TABLOCK);
-------------------------------------------------------
My solution is like this. Is it correct?
// CoCreateInstance(...);
// Data source
// Create session
m_TableID.uName.pwszName = m_wszTableName;
m_TableID.eKind = DBKIND_NAME;
DBPROP rgProps[1];
DBPROPSET PropSet[1];
rgProps[0].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProps[0].colid = DB_NULLID;
rgProps[0].vValue.vt = VT_BSTR;
rgProps[0].dwPropertyID = SSPROP_FASTLOADOPTIONS;
rgProps[0].vValue.bstrVal = L"ROWS_PER_BATCH = 10000,TABLOCK";
PropSet[0].rgProperties = rgProps;
PropSet[0].cProperties = 1;
PropSet[0].guidPropertySet = DBPROPSET_SQLSERVERROWSET;
if(m_pIOpenRowset)
{
if(FAILED(m_pIOpenRowset->OpenRowset(NULL,&m_TableID,NULL,IID_IRowsetFastLoad,1,PropSet,(LPUNKNOWN*)&m_pIRowsetFastLoad)))
{
return FALSE;
}
}
else
{
return FALSE;
}
Hi guys,in my db i have these three tables1.Stores 2.Products3.Partstheir structure is something like :Stores ----Products ----PartsStores----------------StoreId, StoreNameProducts----------------ProductId, StoreId, ProductNameParts----------------PartId, ProductId, PartNamenow, in my application i wanna to implement a bulk-copy operation souser can copy products from one store to another one and when aproduct copied to new store;all of it's parts should copy too.in fact i need a method to insert a Product item in Products table andsynchronously copy it's parts into Parts table and repeat this stepsuntil all of proucts copied.how can i do that without cursors or loops ?Thanks
View 19 Replies View RelatedHi,
I am trying to run a very simple copy command from an execute process task:
copy O:myFoldermyFile*.txt D:myFolder
I have a working directory set also.
However, there is an error icon on the task, and if I attempt to run it I get the following error:
Error at Execute Process Task [Execute Process Task]: File/Process "" does not exist in directory "copy O:myFoldermyFile*.txt D:myFolder".
Error at Execute Process Task: There were errors during task validation.
This command works fine from the command line. What am I doing wrong here?
Thanks
I am trying to export a table to .CSV file using Sql 2005 BCP utility through .Net web page.
I get the following error: " An error occurred during the execution of xp_cmdshell. A call to 'CreateProcessAsUser' failed with error code: '1314'. "
I have done following steps:
-- Set up proxy for a windows account using command : sp_xp_cmdshell_proxy_account '<winAccount>', '<password>'
-- EXEC sp_grantdbaccess '<winAccount>'
-- GRANT exec ON xp_cmdshell TO <winAccount> -- BCP command is inside a stored proc, so I run the stored proc as : Create Proc dbo.Export_Table with execute as '<winAccount>'
What I am I missing? I can run the stored proc in SQL Managaement studio using that windows account.
I don't get the error when I use the Visual Studio Developement Web Server, the export file is created without any problem.
I get the error only when I run my .Net web page though IIS.
Please help.
Thanks
I'm running SQL 7.0 SP3 on two different machines (one with additional hotfixes). I'm taking a nightly snapshot of imported data on Server1 and pushing it out to another SQL 7.0 server on our network, Server2. All but one table is copied successfully. On the final table, I receive the message, "The process could not bulk copy into table '"%"'."
Error Information Category: Data Source, Source: Server2, Number 4813.
Full error message: "Expected the text length in data stream for bulk copy of text, ntext, or image data."
I've looked up 4813, but it's pretty ambiguous/generic. Also, when I SELECT from Server1 and INSERT INTO Server2 in the QA, I receive no errors. Does anyone have any insight?
Hi,I am attempting a bulk copy from a c program into SQL Server 2000 usingDBLib in freeTDS 0.63 RC11 (gcc 3.4.3, RH 9). I am getting an error messagethat I cannot find any documentation on.The server is sending back the following: "Received invalid row length 2from bcp client. Minimum row size is 4."I know the row is longer 2 bytes (see below). Once this happened I created atest table and C program. See below. Anyone with any ideas?ThanksProgram output ---------------->$ ./test_bcpbcp'ing This is a test with a length of 14sentMsg 4807, Level 16, State 1Server 'CENTIVIA_10', Line 1Received invalid row length 2 from bcp client. Minimum row size is 4.done<----------------------Table ddl --------------------->CREATE TABLE [xxx] ([col2] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GO<------------------------------------Compiled using gcc ------------------->gcc -g -I/home/test_user/dev/freetds-0.63RC11/include -Wall -Wno-strict-aliasing-g -O2 -c -o test_bcp.o test_bcp.cgcc -o test_bcp test_bcp.o -L/home/test_user/lib -lsybdb<----------------------------------program source (included the msg and error handlercode) -------------------------->#include <string.h>#include <stdio.h>#include <stdlib.h>#include <sqldb.h>intsyb_msg_handler(DBPROCESS * dbproc, DBINT msgno, int msgstate, int severity,char *msgtext, char *srvname, char *procname, int line){char var_value[31];int i;char *c;if (msgno == 5701 || /* database context change */msgno == 5703 || /* language changed */msgno == 5704) { /* charset changed */if (msgtext != NULL && (c = strchr(msgtext, ''')) != NULL){i = 0;for (++c; i <= 30 && *c != ' ' && *c != '''; ++c)var_value[i++] = *c;var_value[i] = ' ';}return 0;}if (severity >= 0 || msgno == 0) {if (msgno > 0 && severity > 0) {fprintf(stdout, "Msg %d, Level %d, State %d",(int) msgno, (int) severity, (int) msgstate);fprintf(stdout, "Server '%s'", srvname);if (procname != NULL && *procname != ' ')fprintf(stdout, ", Procedure '%s'",procname);if (line > 0)fprintf(stdout, ", Line %d", line);fprintf(stdout, "");fprintf(stdout, "%s", msgtext);fflush(stdout);} else {fprintf(stdout, "%s", msgtext);fflush(stdout);}}return 0;}intsyb_err_handler(DBPROCESS * dbproc, int severity, int dberr, int oserr, char*dberrstr, char *oserrstr){if (dberr == SYBESMSG)return INT_CANCEL;env_set(g_env, "batch_failcount", "1");fprintf(stdout,"DB-LIBRARY error (severity %d, dberr %d, oserr %d, dberrstr%s, oserrstr %s):",severity, dberr, oserr, dberrstr ? dberrstr : "(null)",oserrstr ? oserrstr : "(null)");fflush(stdout);if ((dbproc == NULL) || DBDEAD(dbproc)) {if (dberr != SYBECOFL) {exit(255);}}return INT_CANCEL;}int main(int argc, char *argv[]){DBPROCESS *dbproc;LOGINREC *login;DBINT test_int = 99999999;DBCHAR test_char[32] = "This is a test";dbinit();dberrhandle(syb_err_handler);dbmsghandle(syb_msg_handler);login = dblogin();DBSETLPWD(login, "audit");DBSETLUSER(login, "audit");dbproc = dbopen(login, "192.168.58.1");dbuse(dbproc, "audit_dev");bcp_init(dbproc,"xxx", (BYTE *)NULL, (BYTE *)NULL, DB_IN);printf("bcp'ing %s with a length of%d",test_char,strlen(test_char));//ii = bcp_bind(dbproc, (BYTE *) &test_int, 0, -1, NULL, 0, SYBINT4,1 );//printf("bound %d",ii;bcp_bind(dbproc, test_char, 0, strlen(test_char), NULL, 0, SYBCHAR,1 );bcp_sendrow(dbproc);printf("sent");bcp_batch(dbproc);printf("done");return 0;}<---------------------------------
View 1 Replies View RelatedI am using Bulk Copy command for Exporting data table wise from database to csv files and it was working fine. Since last 3-4 days when exporting for some tables data in csv file is coming junk.
View 1 Replies View RelatedI'm trying to use Bulk insert for the first time and getting the following error. I think it might have something to do with my Format File and from the error msg there's a conversion error for the first column. In my database the Field is nvarchar(6) so my best guess is to use SQLNChar for the first column. I've checked the end of each line is CR LF therefore the is correct for line 7 right?
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1, column 1 (ASXCode).
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
BULK
INSERTtbl_ASX_Data_temp
FROM
'M:DataASXImportTest.txt'
WITH
(FORMATFILE='M:DataASXSQLFormatImport.Fmt')
[code]...
i'm going nuts with SQL server notification thing. I have gone throigh this artical which tells how to set user http://www.codeproject.com/KB/database/SqlDependencyPermissions.aspx. This article show how to create new user and setup for sql server notification.But In my case user was alredy existing in database. which is very common senario in most cases. So i did following( check the SQL script below) but then i get this error
"A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)"
this my sql script
use [master]Go
-- Ensuring that Service Broker is enabled ALTER DATABASE [DatabaseName] SET ENABLE_BROKERGO
-- Switching to our databaseuse [DatabaseName]GO
CREATE SCHEMA schemaname AUTHORIZATION usernameGO
ALTER USER username WITH DEFAULT_SCHEMA = schemaname GO
/* * Creating two new roles. We're not going to set the necessary permissions * on the user-accounts, but we're going to set them on these two new roles. * At the end of this script, we're simply going to make our two users * members of these roles. */EXEC sp_addrole 'sql_dependency_subscriber' EXEC sp_addrole 'sql_dependency_starter'
-- Permissions needed for [sql_dependency_starter]GRANT CREATE PROCEDURE to [sql_dependency_starter] GRANT CREATE QUEUE to [sql_dependency_starter]GRANT CREATE SERVICE to [sql_dependency_starter]GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to [sql_dependency_starter] GRANT VIEW DEFINITION TO [sql_dependency_starter]
-- Permissions needed for [sql_dependency_subscriber] GRANT SELECT to [sql_dependency_subscriber] GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [sql_dependency_subscriber] GRANT RECEIVE ON QueryNotificationErrorsQueue TO [sql_dependency_subscriber] GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to [sql_dependency_subscriber]
-- Making sure that my users are member of the correct role.EXEC sp_addrolemember 'sql_dependency_starter', 'username'EXEC sp_addrolemember 'sql_dependency_subscriber', 'username'
Hello all,
I am running into an interesting scenario on my desktop. I'm running developer edition on Windows XP Professional (9.00.3042.00 SP2 Developer Edition). OS is autopatched via corporate policy and I saw some patches go in last week. This machine is also a hand-me-down so I don't have a clean install of the databases on the machine but I am local admin.
So, starting last week after a forced remote reboot (also a policy) I noticed a few of the databases didn't start back up. I chalked it up to the hard shutdown and went along my merry way. Friday however I know I shut my machine down nicely and this morning when I booted up, I was in the same state I was last Wenesday. 7 of the 18 databases on my machine came up with
FCB:pen: Operating system error 32(The process cannot access the file because it is being used by another process.) occurred while creating or opening file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataTest.mdf'. Diagnose and correct the operating system error, and retry the operation.
and it also logs
FCB:pen failed: Could not open file C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataTest.mdf for file number 1. OS error: 32(The process cannot access the file because it is being used by another process.).
I've caught references to the auto close feature being a possible culprit, no dice as the databases in question are set to False. Recovery mode varies on the databases from Simple to Full. If I cycle the SQL Server service, whatever transient issue it was having with those files is gone.
As much as I'd love to disable the virus scanner, network security would not be amused. The data and log files appear to have the same permissions as unaffected database files. Nothing's set to read only or archive as I've caught on other forums as possible gremlins. I have sufficient disk space and the databases are set for unrestricted growth.
Any thoughts on what I could look at? If it was everything coming up in RECOVERY_PENDING it's make more sense to me than a hit or miss type of thing I'm experiencing now.
Dear list
Im designing a package that uses Microsofts preplog.exe to prepare web log files to be imported into SQL Server
What Im trying to do is convert this cmd that works into an execute process task
D:SSIS ProcessPrepweblogProcessLoad>preplog ex.log > out.log
the above dos cmd works 100%
However when I use the Execute Process Task I get this error
[Execute Process Task] Error: In Executing "D:SSIS ProcessPrepweblogProcessLoadpreplog.exe" "" at "D:SSIS ProcessPrepweblogProcessLoad", The process exit code was "-1" while the expected was "0".
There are two package varaibles
User::gsPreplogInput = ex.log
User::gsPreplogOutput = out.log
Here are the task properties
RequireFullFileName = True
Executable = D:SSIS ProcessPrepweblogProcessLoadpreplog.exe
Arguments =
WorkingDirectory = D:SSIS ProcessPrepweblogProcessLoad
StandardInputVariable = User::gsPreplogInput
StandardOutputVariable = User::gsPreplogOutput
StandardErrorVariable =
FailTaskIfReturnCodeIsNotSuccessValue = True
SuccessValue = 0
TimeOut = 0
thanks in advance
Dave
How do I use the execute process task? I am trying to unzip the file using the freeware PZUnzip.exe and I tried to place the entire command in a batch file and specified the working directory as the location of the batch file, but the task fails with the error:
SSIS package "IngramWeeklyPOS.dtsx" starting.
Error: 0xC0029151 at Unzip download file, Execute Process Task: In Executing "C:ETLPOSDataIngramWeeklyUnzip.bat" "" at "C:ETLPOSDataIngramWeekly", The process exit code was "1" while the expected was "0".
Task failed: Unzip download file
SSIS package "IngramWeeklyPOS.dtsx" finished: Success.
Then I tried to specify the exe directly in the Executable property and the agruments as the location of the zip file and the directory to unzip the files in, but this time it fails with the following message:
SSIS package "IngramWeeklyPOS.dtsx" starting.
Error: 0xC002F304 at Unzip download file, Execute Process Task: An error occurred with the following error message: "%1 is not a valid Win32 application".
Task failed: Unzip download file
SSIS package "IngramWeeklyPOS.dtsx" finished: Success.
The command in the batch file when run from the command line works perfectly and unzips the file, so there is absolutely no problem with the command, I believe it is just the set up of the variables on the execute process task editor under Process. Any input on resolving this will be much appreciated.
Thanks,
Monisha
I am designing a utility which will keep two similar databases in sync. In other words, copying the new data from db1 to db2 and updating the old data from db1 to db2.
For this I am making use of the 'Tablediff' utility which when provided with server name, database, table info will generate .sql file which can be used to keep the target table in sync with the source table.
I am using the Execute Process Task and the process parameters I am providing are:
WorkingDirectory : C:Program Files (x86)Microsoft SQL Server90COM
Executable : C:SQL_bat_FilesSQL5TC_CTIcustomer.bat
The customer.bat file will have the following code:
tablediff -sourceserver "LV-SQL5" -sourcedatabase "TC_CTI" -sourcetable "CUSTOMER_1" -destinationserver "LV-SQL2" -destinationdatabase "TC_CTI" -destinationtable "CUSTOMER" -f "c:SQL_bat_Filessql5TC_CTIsql_filescustomer1"
the .sql file will be generated at: C:SQL_bat_Filessql5TC_CTIsql_filescustomer1.
The Problem:
The Execute Process Task is working fine, ie., the tables are being compared correctly and the .SQL file is being generated as desired. But the task as such is reporting faliure with the following error :
[Execute Process Task] Error: In Executing "C:SQL_bat_FilesSQL5TC_CTIpackage_occurrence.bat" "" at "C:Program Files (x86)Microsoft SQL Server90COM", The process exit code was "2" while the expected was "0". ]
Some of you may suggest to just set the ForceExecutionResult = Success (infact this is what I am doing now just to get the program working), but, this is not what I desire.
Can anyone help ?
I'm pulling data from Oracle db and load into MS-SQL 2008.For my data type checks during the data load process, what are options to ensure that the data being processed wouldn't fail. such that I can verify first in-hand with the target type of data and then if its valid format load it into destination table else mark it with error flag and push into errors table... All this at the row level.One way I can think of is to load into a staging table then get the source & destination table -column data types, compare them and proceed.
should I just try loading the data directly and if it fails try trouble shooting(which could be a difficult task as I wouldn't know what caused error...)
hi..
how to use bulk copy in MS SOL Server?
Hi,
I have a question...As per my knowledge bulk copying is not possible during the backup operation. If backup starts first backup will go and bulk copy will fail or bulk copy starts first backup fails and bulk copy will continue...
Today I was testing bcp to run in dts using the EXCUTE PROCESS TASK (with this task we run any Win32 Excutable or batch file). I am trying to bcp out from one database(source) and bcp in to another database(destination). While running this package backup also running...
I have started the database (destination) backup job and it was running and I started another job to run the dts (even I ran dts manually). Both the josbs succeeded and inserted the data into the table....
Can any one shed some light on this ....
Thanks,
Mohammed.
Hi!!
Is it possible to avoid transaction log generation throught DTS?
How?
Thanks,
Fabio
I want to bulk copy a table using 'SELECT INTO'
from a database in server1 to a database in server2.
The 'FROM' part of the select into only allows three
parameters 'database, user & table' within the the one server.
I remember seeing some option that allows one to
bulk copy across servers but cant find it ?????
hi , I am using command line for bulk copy operation. I do have a couple of tables with some triggers to move data from one table to another on an insert trigger, I was just wondering, Is those triggers going to fire when I import data into the tables using bcp command line?
Thanks for your help
Ali
bcp.exe northwind..products out "c:w_products.txt" –c -T
I am running this line of code, but I get an error. The error is:
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.
why? Please help...
Thanks
Salam
What the permission required for Bulk copy
Hi:
I am doing a full snapshot on couple of subcriptions but getting this message from last couple of days. I have to do this at night because tables are very large so it does not block the users. I am not sure which table this error is happening. It used to work fine but from last couple of days this has started to happen. I need to re-sync my subscription database with production database but some of the tables are giving problem.
The process could not bulk copy out of table '[dbo].[syncobj_0x3735393934363031]'.
I/O error while writing BCP data-file
(Source: ODBC SQL Server Driver (ODBC); Error number: 0)
---------------------------------------------------------------------------------------------------------------
Please let me know.
Thanks
Im having some issues with bulk insert.
This is the table:
CREATE TABLE [dbo].[tmp_GA_status](
[GA_recno] [int] NOT NULL,
[GA_desc] [varchar](40) NULL
)
This is the file (unicode):
1|"test1"
2|"test2"
3|"test3"
4|"test4"
5|"test5"
6|"test6"
7|"test7"
8|"test8"
and this is the sql:
bulk insert tmp_GA_status from 'C: empTextDumpGA_status.dta'
with (CODEPAGE='RAW', FIELDTERMINATOR='|', ROWTERMINATOR='
', DATAFILETYPE='widechar')
so yeah, pretty simple. But whatever I do I get this;
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (GA_desc).
So what am I doing wrong ?
With the following I try to save the content of an excel-sheet to a sql table. This works perfectly with SQL Server Express but not with MSDE, which I would need also. Here the code:String rootPath1 = Request.MapPath("~/Kontoauszug.xls");
String strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source=" + rootPath1 + ";Extended Properties=Excel 8.0;";
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [Mappe1$]", strConn);DataTable dtCustomers = new DataTable();
da.Fill(dtCustomers);
string ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();using (SqlConnection destinationConnection = new SqlConnection(ConnectionString))
{
// open the connection
destinationConnection.Open();using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection.ConnectionString))
{
bulkCopy.BatchSize = 500;
bulkCopy.BulkCopyTimeout = 90;bulkCopy.DestinationTableName = "dbo.Auszug";
bulkCopy.WriteToServer(dtCustomers);
bulkCopy.Close();
}
}
With MSDE I see the following error-message
Fehler bei der Anmeldung für den Benutzer 'sa'.
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: Fehler bei der Anmeldung für den Benutzer 'sa'.Source Error:
Line 148:
Line 149: // Write from the source to the destination.
Line 150: bulkCopy.WriteToServer(dtCustomers);
Line 151:
Line 152: Source File: d:InetpubWww_rootXXXXXXXXXXXXXXXXXXXXXXXX.cs Line: 150 Stack Trace:
[SqlException (0x80131904): Fehler bei der Anmeldung für den Benutzer 'sa'.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739123
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1956
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +170
System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +349
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +181
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
System.Data.SqlClient.SqlBulkCopy.CreateOrValidateConnection(String method) +64
System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount) +42
System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState) +176
System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table) +6
admin_Default.Button4_Click(Object sender, EventArgs e) in d:InetpubWww_rootetzregister.deconsumeropinionadminDefault.aspx.cs:150
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) +5102
The connection to the database works perfect also on MSDE on all other parts of the project.
Can anyone help me wit a solution or an idea?
Hi Techies,
I have a bcp which generates .txt file perfectly. I just wanted to know how can i generate a text file in distributed environment.
Assuming that my Sql Server is running in machine A. I wanted the bcp to generate in Machine B. What are the permission's i should give in order to generate it in Machine B.
Regards
--Tanveer
Dear All,
Can anyone tell me what's the best/easiest way to copy one column of data from one table to another column of another table.
Thanks in advance.
Hi there,
I'm in dire need for a solution to a "Bulk Insert" problem.
Environmental: SQL 7
The unc path used is a shared folder on the local machine with full rights.
This is the SQL Table:
CREATE TABLE [dbo].[ArgiefTransaksies] (
[TakDepotKodeRef] [char] (4) NULL ,
[HoofraamNommer] [varchar] (2) NULL ,
[Dokumentnommer] [decimal](2, 0) NULL ,
[DataLyn] [varchar] (133) NULL)
Here is the file that I try to upload, TESTFILE.PRN
RZGB|FT|12| |
RZGB|FT|12| |
RZGB|FT|12| |
RZGB|FT|12| |
RZGB|FT|12|This is a test|
RZGB|FT|12| |
RZGB|FT|12|Zone 1Zone 2|
RZGB|FT|12|Hello World||
RZGB|FT|12| 5 leading spaces|
RZGB|FT|12|Tab(10) - Hello|
Here is the SQL statement that I try to use:
Exec sp_dboption 'VKBTakDev', 'select into/bulkcopy', 'TRUE'
BULK INSERT ArgiefTransaksies
FROM '\HKDATA060XPPublicTESTFILE.PRN'
WITH
(
DATAFILETYPE = 'char',
FIELDTERMINATOR = '|',
ROWTERMINATOR = '|',
MAXERRORS = 0,
CODEPAGE = 'raw'
)
Here is the error that I keep getting...
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
The statement has been terminated.
Regards