Getting Error While Insert Rows Using Linkedserver

Aug 29, 2007

Hello All,
I created two database instances in the same MS SQL SERVER 2005 named hafeez and local. I created a linked server from hafeez database to local named HTOL

I created a dummy table in local database named samplocal.

Now i am able to list the records using the linked server, for this i used the following query.

SELECT * FROM OPENQUERY(HTOL,'SELECT nameandval FROM SAMPLOCAL');

Now the problem is, i am not able to insert the rows into the samplocal table using linkedserver. I am using the following query to insert the rows and getting the following errro.

INSERT INTO OPENQUERY(HTOL,'SELECT nameandval FROM SAMPLOCAL') VALUES('tertertetttwertw');

Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "HTOL" supplied inconsistent metadata for a column. The column "nameandval" (compile-time ordinal 1) of object "SELECT nameandval FROM SAMPLOCAL" was reported to have a "Incomplete schema-error logic." of 0 at compile time and 0 at run time.

Is the above query correct? or shall i miss anything?

Please guide me.

Thanks in Advance
Hafeez Shaik.

View 3 Replies


ADVERTISEMENT

LinkedServer Query Tries To Return All Rows Over Network

Apr 27, 1999

When running a query with a table from another SQL7 Server that is linked, if only 1 table from each server is specified, the query runs fast and returns only the data rows needed from the linked server. When another table is added on to query (on the server running the query) the query processor tries to run a remote query and return over 400,000 rows over the network. Any suggestions?

View 1 Replies View Related

Linkedserver Error

Mar 21, 2007

Hi

Im sorry if im in the wrong section. My problem is a very common one and it has been hard for me to find the fix.

Ive gone thru Component Services, Firewall settings, Registry and Microsoft Sites, but to avail.

"The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]."

My Begin Tran doesnt start at all.



begin tran
use smartpos
go


set identity_insert smartpos.dbo.smp_product on

insert into smartpos.dbo.smp_product
(prodId
,[Description]
,barcode
,BuyPriceExclVat
,BuyPriceVatTYpeId
,point
,SupplierID
,Ref
,QtyType
,QtyFactor
,MinStock
,MaxStock
,Active
,ItemType
,ClientLastUpdate
)
select prodId
,[Description]
,barcode
,BuyPriceExclVat
,BuyPriceVatTYpeId
,point
,SupplierID
,Ref
,QtyType
,QtyFactor
,MinStock
,MaxStock
,Active
,ItemType
,LastUpdate
from server.smartpos.dbo.smp_product svr
where not exists (select prodid from smartpos.dbo.smp_product where prodid=svr.prodid)

set identity_insert smartpos.dbo.smp_product off



Can anyone help me please.

Thanks

View 2 Replies View Related

Bulk Insert, Skip Rows With Duplicate Key Error?

May 21, 2007

Does sql server have a way to handle errors in a sproc which would allowone to insert rows, ignoring rows which would create a duplicate keyviolation? I know if one loops one can handle the error on a row by rowbasis. But is there a way to skip the loop and do it as a bulk insert?It's easy to do in Access, but I'm curious to know if SQL Server propercan handle like this. I am guessing that a looping operation would beslower to execute?

View 2 Replies View Related

Using SSIS 2005 To Strip Out Bad Rows In Excel And Then Insert Detailed Rows Into OLE DB Data Source

Apr 6, 2006

Environment:
 
Running this code on my PC via VS 2005
.Net version 2.0.50727 on the server (shown in IIS)
Code is in ASP.NET 2.0 and is a VB.NET Console application
SSIS 2005
 
Problem & Info:
 
I am bringing in an Excel file.  I need to first strip out any non-detail rows such as the breaks you see with totals and what not.  I should in the end have only detail rows left before I start moving them into my SQL Table.  I'm not sure how to first strip this information out in SSIS specfically how down to the right component and how to actually code the component to do this based on my Excel file here: http://www.webfound.net/excelfile.xls

Then, I assume I just use a Flat File Source coponent or something to actually take the columns in the Excel and split into an OLE DB Datasource to shove each column into a corresponding column in my SQL Server Table.  I have used a Flat File Source in the past to do so with a comma delimited txt file but never tried with an Excel.
 
Desired Help:

 
How to perform
 
1)       stripping out all undesired rows
2)       importing each column into sql table

View 1 Replies View Related

LinkedServer Problem

Nov 6, 2000

Hi Everybody,

I have 2 servers one has SQL 7.0 running on NT4.0 and another has SQL 2000 running on W2K Server. I have established a linked server for SQL 2000 from SQL 7.0.

I don't have any problem of creating linked servers for sql 2000 from sql 7.0. I am also able to create linked server logins to access the sql2000 tables. Even I am able to see the tables on the Databases.

EXEC sp_addlinkedserver 'SQL2K', '','SQLOLEDB','GENOMESQL2000'

sp_addlinkedsrvlogin @rmtsrvname = 'sql2k' ,@useself = 'false' ,@locallogin = 'genomeACCT1',@rmtuser = 'test2k',@rmtpassword = 'test2k'

exec sp_tables_ex N'sql2k' (Upto this I don't have any problem)

When I tried to execute a particular table from the database, I am getting the following error.

Select * From sql2k.northwind.test2k.payroll

Error:
******
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' does not contain table '"master"."test2k"."ven"'.

Can anybody give suggestions to solve this problem.

tks in advance,
Vasu

View 1 Replies View Related

Why I Can't Do A Selection By Using A LinkedServer?

Mar 21, 2007



I added a Linked Server, like this:

EXEC sp_addlinkedserver
@server = 'ServidorPrincipal',
@srvproduct = 'SQLEXPRESS',
@provider = 'SQLNCLI',
@datasrc = 'TIC-IISQLEXPRESS',
@catalog = 'BDPrincipal'
GO

But now I can't do a selection:

SELECT * FROM ServidorPrincipal.BDPrincipal.dbo.tblCADENA;

Because it sends this message:

Server: Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'SQLNCLI' supplied inconsistent metadata for a column. Metadata information was changed at execution time.

OLE DB error trace [Non-interface error: Column 'ID_CADENA' (compile-time ordinal 1) of object '"BDPrincipal"."dbo"."tblCADENA"' was reported to have changed. The exact nature of the change is unknown].



The linked Server is called "ServidorPrincipal".

The database is: BDPrincipal.

And the table is dbo.tblCADENA.

"changed at execution time" ¿What does it mean?

What is happening here?








View 4 Replies View Related

SMO : Changing A Linkedserver Datasource

May 31, 2007

Hi all,

I'm trying to change the datasource of a SQL Server LinkedServer using SMO

I've got the relevant linkedserver as an object, changed the DataSource property, and selected it again to confirm the change.
However, the change is lost as soon as the object is destroyed.
Looking at BoL I think I need to be using alter() method of the linkedserver class but I just get an error
Alter failed for LinkedServer '<servername>'.


any ideas how I should be using this class to do what I want?

I can post my script (PowerShell) if it would help.

Thanks, Robin.

View 2 Replies View Related

SSIS Vs LinkedServer TSQL

Jun 23, 2008

I'm copying almost all contents of one table from one server/db to another. In relation to speed, what is the difference of using SSIS vs a linked server and sp? I'm going to do the benchmarks, but I'm curious about behind the scenes kinda stuff, theoretically which one if any should be faster and why?

View 5 Replies View Related

Insert Rows With SQLServerce V3.5 Is Very Slow Can Anyone Help Insert Performance Poor

Feb 22, 2008



Hi All

I decided to change over from Microsoft Access Database file to the New SQLServerCe Compact edition. Although the reading of data from the database is greatly improved, the inserting of the new rows is extremely slow.

I was getting between 60 to 70 rows per sec using OLEDB and an Access Database but now only getting 14 to 27 rows per sec using SQLServerCe.

I have tried the below code changes and nothing seams to increase the speed, any help as I would prefer to use SQLServerCe as the database is much smaller and I€™m use to SQL Commands.

Details:
VB2008 Pro
.NET Frameworks 2.0
SQL Compact Edition V3.5
Encryption = Engine Default
Database Size = 128Mb (But needs to be changes to 999Mbs)

Where Backup_On_Next_Run, OverWriteQuick, CompressAns are Booleans, all other column sizes are nvarchar and size 10 to 30 expect for Full Folder Address size 260

TRY1

Direct Insert Using Data Adapter.

Me.BackupDatabaseTableAdapter1.Insert(Group_Name1, Full_Folder_Address1, File1, File_Size_KB1, Schedule_To_Run1, Backup_Time1, Last_Run1, Result1, Last_Modfied1, Last_Modfied1, Backup_On_Next_Run1, Total_Backup_Times1, Server_File_Number1, Server_Number1, File_Break_Down1, No_Of_Servers1, Full_File_Address1, OverWriteQuick, CompressAns)

14 to 20 rows per second (Was 60 to 70 when using OLEDB Access)


TRY 2

Using Record Sets

Private Sub InsertRecordsIntoSQLServerce(ByVal Group_Name1 As String, ByVal Full_Folder_Address1 As String, ByVal File1 As String, ByVal File_Size_KB1 As String, ByVal Schedule_To_Run1 As String, ByVal Backup_Time1 As String, ByVal Last_Run1 As String, ByVal Result1 As String, ByVal Last_Modfied1 As String, ByVal Latest_Modfied1 As String, ByVal Backup_On_Next_Run1 As Boolean, ByVal Total_Backup_Times1 As String, ByVal Server_File_Number1 As String, ByVal Server_Number1 As String, ByVal File_Break_Down1 As String, ByVal No_Of_Servers1 As String, ByVal Full_File_Address1 As String, ByVal OverWriteQuick As Boolean, ByVal CompressAns As Boolean)

Dim conn As SqlCeConnection = Nothing
Dim CommandText1 As String = "INSERT INTO BackupDatabase (Group_Name, Full_Full_Folder_Adress, File1,File_Size_KB, Schedule_To_Run, Backup_Time, Last_Run, Result, Last_Modfied, Latest_Modfied, Backup_On_Next_Run, Total_Backup_Times, Server_File_Number, Server_Number, File_Break_Down, No_Of_Servers, Full_File_Address, OverWrite, Compressed) VALUES ('" & Group_Name1 & "', '" & Full_Folder_Address1 & "', '" & File1 & "', '" & File_Size_KB1 & "', '" & Schedule_To_Run1 & "', '" & Backup_Time1 & "', '" & Last_Run1 & "', '" & Result1 & "', '" & Last_Modfied1 & "', '" & Latest_Modfied1 & "', '" & CStr(Backup_On_Next_Run1) & "', '" & Total_Backup_Times1 & "', '" & Server_File_Number1 & "', '" & Server_Number1 & "', '" & File_Break_Down1 & "', '" & No_Of_Servers1 & "', '" & Full_File_Address1 & "', '" & CStr(OverWriteQuick) & "', '" & CStr(CompressAns) & "')"
Try
conn = New SqlCeConnection(strConn)
conn.Open()

Dim cmd As SqlCeCommand = conn.CreateCommand()

cmd.CommandText = "SELECT * FROM BackupDatabase"
cmd.ExecuteNonQuery()
Dim rs As SqlCeResultSet = cmd.ExecuteResultSet(ResultSetOptions.Updatable Or ResultSetOptions.Scrollable)

Dim rec As SqlCeUpdatableRecord = rs.CreateRecord()

rec.SetString(1, Group_Name1)
rec.SetString(2, Full_Folder_Address1)
rec.SetString(3, File1)
rec.SetSqlString(4, File_Size_KB1)
rec.SetSqlString(5, Schedule_To_Run1)
rec.SetSqlString(6, Backup_Time1)
rec.SetSqlString(7, Last_Run1)
rec.SetSqlString(8, Result1)
rec.SetSqlString(9, Last_Modfied1)
rec.SetSqlString(10, Latest_Modfied1)
rec.SetSqlBoolean(11, Backup_On_Next_Run1)
rec.SetSqlString(12, Total_Backup_Times1)
rec.SetSqlString(13, Server_File_Number1)
rec.SetSqlString(14, Server_Number1)
rec.SetSqlString(15, File_Break_Down1)
rec.SetSqlString(16, No_Of_Servers1)
rec.SetSqlString(17, Full_File_Address1)
rec.SetSqlBoolean(18, OverWriteQuick)
rec.SetSqlBoolean(19, CompressAns)
rs.Insert(rec)
Catch e As Exception
MessageBox.Show(e.Message)
Finally
conn.Close()
End Try
End Sub

€™20 to 24 rows per sec

TRY 3

Using SQL Commands Direct

Private Sub InsertRecordsIntoSQLServerce(ByVal Group_Name1 As String, ByVal Full_Folder_Address1 As String, ByVal File1 As String, ByVal File_Size_KB1 As String, ByVal Schedule_To_Run1 As String, ByVal Backup_Time1 As String, ByVal Last_Run1 As String, ByVal Result1 As String, ByVal Last_Modfied1 As String, ByVal Latest_Modfied1 As String, ByVal Backup_On_Next_Run1 As Boolean, ByVal Total_Backup_Times1 As String, ByVal Server_File_Number1 As String, ByVal Server_Number1 As String, ByVal File_Break_Down1 As String, ByVal No_Of_Servers1 As String, ByVal Full_File_Address1 As String, ByVal OverWriteQuick As Boolean, ByVal CompressAns As Boolean)

Dim conn As SqlCeConnection = Nothing
Dim CommandText1 As String = "INSERT INTO BackupDatabase (Group_Name, Full_Full_Folder_Adress, File1,File_Size_KB, Schedule_To_Run, Backup_Time, Last_Run, Result, Last_Modfied, Latest_Modfied, Backup_On_Next_Run, Total_Backup_Times, Server_File_Number, Server_Number, File_Break_Down, No_Of_Servers, Full_File_Address, OverWrite, Compressed) VALUES ('" & Group_Name1 & "', '" & Full_Folder_Address1 & "', '" & File1 & "', '" & File_Size_KB1 & "', '" & Schedule_To_Run1 & "', '" & Backup_Time1 & "', '" & Last_Run1 & "', '" & Result1 & "', '" & Last_Modfied1 & "', '" & Latest_Modfied1 & "', '" & CStr(Backup_On_Next_Run1) & "', '" & Total_Backup_Times1 & "', '" & Server_File_Number1 & "', '" & Server_Number1 & "', '" & File_Break_Down1 & "', '" & No_Of_Servers1 & "', '" & Full_File_Address1 & "', '" & CStr(OverWriteQuick) & "', '" & CStr(CompressAns) & "')"

Try
conn = New SqlCeConnection(strConn)
conn.Open()

Dim cmd As SqlCeCommand = conn.CreateCommand()
cmd.CommandText = CommandText1
'cmd.CommandText = "INSERT INTO BackupDatabase (€¦"
cmd.ExecuteNonQuery()

Catch e As Exception
MessageBox.Show(e.Message)
Finally
conn.Close()
End Try
End Sub

€˜ 25 to 30 but mostly holds around 27 rows per sec I

Is this the best you can get or is there a better way. Any help would be greatly appericated

Kind Regards

John Galvin

View 3 Replies View Related

There Is A Question When I Work On Linkedserver~Pls Kindly Help Me:)

Nov 23, 2005

I have a question when I work on LinkedserverThe Linkedserver name is [Hp-server],the Datebase name isNewexec,the Table name is Customers_CoypTestThe SQLScript is below:Update [Hp-server].Newexec.dbo.Customers_CoypTestset Unitname=b.Unitnamefrom [Hp-server].Newexec.dbo.Customers_CoypTest a joinNewexec.dbo.Customers_CoypTest bon a.Cid=b.Cid and b.Cid='Tony'The server returns ERROR like this:a) can not open this table '"Newexec"."dbo"."Customers_CoypTest"'(come from OLE DB provide server 'SQLOLEDB'). provide server do notsupport index scan on the data source.b) [OLE/DB provider returned message:Error occured whenmulti-operate.If possible, please check each OLE DB status value.Nowork had been completed.]c) OLE DB Error trace[OLE/DB Provider 'SQLOLEDB'IOpenRowset::OpenRowset returned 0x80040e21:[PROPID=DBPROP_COMMANDTIMEOUT VALUE=600 STATUS=DBPROPSTATUS_OK],[PROPID=Unknown PropertyID VALUE=True STATUS=DBPROPSTATUS_OK],[PROPID=DBPROP_IRowsetIndex VALUE=TrueSTATUS=DBPROPSTATUS_NOTSUPPORTED]].

View 5 Replies View Related

LinkedServer And Pass Through Kind Of Security

Sep 14, 2006

Hi,

I am not expert on SQL server, i need to be able to access different server from withing sproc,

1. Is Linked Server is the best aproach.

2. If yes then how i set it up that it take all authenticated users of this server to remote, i tried with impersonate but it gives error that NT authority dont hae access etc etc (when i try run a sql that accesses remote server)

3. The reason i need to access remote server is ... one of the that table in my db (logTable) is getting to big it is 10 times the size of rest of the db altogether so i though i move it out not just from same db actully put it on a seprate server. so that all the tasks about main db would become easy etc.

any idea help

Sajjad

View 1 Replies View Related

Replace LinkedServer With Service Broker

Jun 15, 2007

Hi,

We currently have an application that primarily sits on SQL server and needs to access some legacy data in a DB2 database. The solution we are using is to use a linked server between the two servers. The problem with this is that complex queries occasionally cause the driver to fetch a complete table and filter that table locally. I wondered whether service broker (which I have never used) could allow me to access the DB2 database using MQSeries and thus speed up the application as our ODBC calls have low priority on our mainframe.



Thanks and sorry if this sounds a stupid use of service broker.

View 1 Replies View Related

Update Openquery (linkedserver, 'select...) Set Field = #

Sep 23, 2004

After running the following OpenQuery...

UPDATE OPENQUERY(LINKEDSERVER, 'SELECT START_ORDER_NO FROM "OECTLFIL" WHERE "FILE_KEY" = 1')
SET START_ORDER_NO = 0

----The START_ORDER_NO field contains a 48

SET START_ORDER_NO = 1~9

----The START_ORDER_NO field contains 49~57 respectively.

SET START_ORDER_NO = 10
---The START_ORDER_NO field contains 12337
SET START_ORDER_NO = 11
---The START_ORDER_NO field contains 12593
SET START_ORDER_NO = 12
---The START_ORDER_NO field contains 12849

incrementing by 256 as I increase the value passed...

ASCII 48-57 are the characters 0-9. The string '10' consists of the two bytes with the values 49 (0x31) and 48 (0x30). It is being viewed in reverse byte order as the value 0x3031 which equals 12337 (48*256 + 49).

The LinkedServer is Pervasive SQL 2000i using 'OLE DB Provider for ODBC'

The START_ORDER_NO field is a Numeric(8,0)

I'm thinking some kind of Unicode, or translation or code page issue, but I haven't had any luck yet.

I'm not sure how difficult this is, I don't think I'm a neophyte but I'm feeling like one...

View 1 Replies View Related

Force An INSERT INTO To Insert Rows Orderly

Apr 25, 2007

Hi All,

From what I know, when you execute an
INSERT INTO table1 (field0)
SELECT field0 FROM table 2 ORDER BY field0
the rows are inserted in whatever order the server engine thinks is better at that moment. Is it any way I can have field0 in table1 inserted in the order I need?

My problem is that I can not touch table1, it is used by a legacy application which I am not allowed to modify. I was thinking about creating a clustered index, adding an id field, etc, but I can not know how this will affect the front end application.

table1 and table2 have only a few hundred records.

table1 =
CREATE TABLE [FinalPlasma] ([name] [varchar] (2000) NULL )

table2 =
CREATE TABLE [Test_FinalPlasma] ([nameID] [int] NOT NULL ,
[name] [varchar] (2000) NULL
)
The update that is not giving the "good" order =

DELETE FROM FinalPlasma
INSERT INTO FinalPlasma SELECT name from dbo.Test_FinalPlasma

Unfortunately I can not order the [name] field after the update, because it looks something like

Donald McQ. Shaver
Mark Sheilds
R.J. Shirley
W.E. Sills
Kenneth A. Smee
A. Britton Smith
LCol Edward W. Smith
Harry V. Smith
M. E. Southern
Timothy A. Sparling
Spectrum Investment Management Limited


Thank you,

View 12 Replies View Related

SQL Server 2008 :: Insert Data Into Table Variable But Need To Insert 1 Or 2 Rows Depending On Data

Feb 26, 2015

I am writing a query to return some production data. Basically i need to insert either 1 or 2 rows into a Table variable based on a decision as to does the production part make 1 or 2 items ( The Raw data does not allow for this it comes from a look up in my database)

I can retrieve all the source data i need easily but when i come to insert it into the table variable i need to insert 1 record if its a single part or 2 records if its a twin part. I know could use a cursor but im sure there has to be an easier way !

Below is the code i have at the moment

declare @startdate as datetime
declare @enddate as datetime
declare @Line as Integer
DECLARE @count INT

set @startdate = '2015-01-01'
set @enddate = '2015-01-31'

[Code] .....

View 1 Replies View Related

Error: 0xC002F304 At Bulk Insert Task, Bulk Insert Task: An Error Occurred With The Following Error Message: Cannot Fetch A Row

Apr 8, 2008


I receive the following error message when I try to use the Bulk Insert Task to load BCP data into a table:


Error: 0xC002F304 at Bulk Insert Task, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 4. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (overflow) for row 1, column 1 (rowno).".

Task failed: Bulk Insert Task

In SSMS I am able to issue the following command and the data loads into a TableName table with no error messages:
BULK INSERT TableName
FROM 'C:DataDbTableName.bcp'
WITH (DATAFILETYPE='widenative');


What configuration is required for the Bulk Insert Task in SSIS to make the data load? BTW - the TableName.bcp file is bulk copy file as bcp widenative data type. The properties of the Bulk Insert Task are the following:
DataFileType: DTSBulkInsert_DataFileType_WideNative
RowTerminator: {CR}{LF}

Any help getting the bcp file to load would be appreciated. Let me know if you require any other information, thanks for all your help.
Paul

View 1 Replies View Related

Insert 10,000 Rows

Sep 26, 2007

Hey guys, i need to insert 10,000 rows into the database and I am wondering what the best way to do that  is.  Should I create a StringBuilder and build the 10,000 insert statements and execute it in 1 connection. Or should I be looking into creating a dataset and use an adapter to insert? Any ideas? Please only answer from experience and provide a sample in c#  Thanks. 

View 4 Replies View Related

Insert Into Many Rows

Jul 20, 2005

i have a table with about 40 columns in access 97( terrible i knw, butits not mine and im not allowed normalize or change it) . i have addeda column , which is a number , called year. it will contain 1997,1998, etc. there are about 1600 rows in the table. is there anythinglike a correlated sub query that will allow me to insert this into thedate column of each row??

View 2 Replies View Related

How To Get INSERT-SQL For Rows?

Sep 7, 2006

Hi

I'm using the SQL Server 2005 Express edition.
I've manually created some rows in a table "Books".

If I right-click on "dbo.Books" and choose "Script Table As --> Create To --> New Query Editor Window", I get the SQL for creating that table.

But how do I get the INSERT-SQL for the rows already inserted?

Kind Regards

View 3 Replies View Related

Cannot INSERT Data To 3 Tables Linked With Relationship (INSERT Statement Conflicted With The FOREIGN KEY Constraint Error)

Apr 9, 2007

Hello
 I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake...
Here's the sql management studio diagram :

 and here goes the  code1 DataSet ds = new DataSet();
2
3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection);
4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
5 myCommand1.FillSchema(ds, SchemaType.Source);
6 DataTable pTable = ds.Tables["Table"];
7 pTable.TableName = "SurveyTemplate";
8 myCommand1.InsertCommand = cb.GetInsertCommand();
9 myCommand1.InsertCommand.Connection = myConnection;
10
11 SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Question", myConnection);
12 cb = new SqlCommandBuilder(myCommand2);
13 myCommand2.FillSchema(ds, SchemaType.Source);
14 pTable = ds.Tables["Table"];
15 pTable.TableName = "Question";
16 myCommand2.InsertCommand = cb.GetInsertCommand();
17 myCommand2.InsertCommand.Connection = myConnection;
18
19 SqlDataAdapter myCommand3 = new SqlDataAdapter("select * from Possible_Answer", myConnection);
20 cb = new SqlCommandBuilder(myCommand3);
21 myCommand3.FillSchema(ds, SchemaType.Source);
22 pTable = ds.Tables["Table"];
23 pTable.TableName = "Possible_Answer";
24 myCommand3.InsertCommand = cb.GetInsertCommand();
25 myCommand3.InsertCommand.Connection = myConnection;
26
27 ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));
28 ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));
29
30 DataRow dr = ds.Tables["SurveyTemplate"].NewRow();
31 dr["name"] = o[0];
32 dr["description"] = o[1];
33 dr["active"] = 1;
34 ds.Tables["SurveyTemplate"].Rows.Add(dr);
35
36 DataRow dr1 = ds.Tables["Question"].NewRow();
37 dr1["questionIndex"] = 1;
38 dr1["questionContent"] = "q1";
39 dr1.SetParentRow(dr);
40 ds.Tables["Question"].Rows.Add(dr1);
41
42 DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();
43 dr2["answerIndex"] = 1;
44 dr2["answerContent"] = "a11";
45 dr2.SetParentRow(dr1);
46 ds.Tables["Possible_Answer"].Rows.Add(dr2);
47
48 dr1 = ds.Tables["Question"].NewRow();
49 dr1["questionIndex"] = 2;
50 dr1["questionContent"] = "q2";
51 dr1.SetParentRow(dr);
52 ds.Tables["Question"].Rows.Add(dr1);
53
54 dr2 = ds.Tables["Possible_Answer"].NewRow();
55 dr2["answerIndex"] = 1;
56 dr2["answerContent"] = "a21";
57 dr2.SetParentRow(dr1);
58 ds.Tables["Possible_Answer"].Rows.Add(dr2);
59
60 dr2 = ds.Tables["Possible_Answer"].NewRow();
61 dr2["answerIndex"] = 2;
62 dr2["answerContent"] = "a22";
63 dr2.SetParentRow(dr1);
64 ds.Tables["Possible_Answer"].Rows.Add(dr2);
65
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();
70

and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Question_SurveyTemplate". The conflict occurred in database
"ankietyzacja", table "dbo.SurveyTemplate", column
'id'.
The statement has been terminated.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"


Could You please tell me what am I missing here ?
Thanks a lot.
 

View 5 Replies View Related

OPENROWSET (INSERT) Insert Error: Column Name Or Number Of Supplied Values Does Not Match Table Definition.

Mar 24, 2008

Is there a way to avoid entering column names in the excel template for me to create an excel file froma  dynamic excel using openrowset.
I have teh following code but it works fien when column names are given ahead of time.
If I remove the column names from the template and just to Select * from the table and Select * from sheet1 then it tells me that column names donot match.
 Server: Msg 213, Level 16, State 5, Line 1Insert Error: Column name or number of supplied values does not match table definition.
here is my code...
SET @sql1='select * from table1'SET @sql2='select * from table2'  
IF @File_Name = ''      Select @fn = 'C:Test1.xls'     ELSE      Select @fn = 'C:' + @File_Name + '.xls'        -- FileCopy command string formation     SELECT @Cmd = 'Copy C:TestTemplate1.xls ' + @fn     
-- FielCopy command execution through Shell Command     EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT        -- Mentioning the OLEDB Rpovider and excel destination filename     set @provider = 'Microsoft.Jet.OLEDB.4.0'     set @ExcelString = 'Excel 8.0;HDR=yes;Database=' + @fn   
exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT *     FROM [Sheet1$]'')      '+ @sql1 + '')         exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT *     FROM [Sheet2$]'')      '+ @sql2 + ' ')   
 
 

View 4 Replies View Related

Insert > 100 Rows Per Second From Application

Mar 22, 2001

Does anyone have experience of geting SQL server to accept > 100 inserts per second?
We use a stored procedure to insert data and this has increased throughput from 30/sec with ODBC to 100-110 /sec but we desperately need to write to the DB faster than this!!
There doesnt seem to be any I/O backlog so i am assuming this "ceiling" is due to network overhead. (BCP can insert 3-4000 rows quite happily)
Is there any way to batch up these inserts or process them differently in order to improve throughput?

Much appreciated,

Damon

View 3 Replies View Related

Rows Insert Out Of Order

Oct 30, 2000

I am currently trying to insert or import some rows into a table and sql server always seems to sort it by one of the columns in a different order that I insert the data. I would appreciate any feedback on this issue. Here is my table structure.

columnA columnB columnC columnD columnE columnF columnG
char char int int int smallint char

it keeps sorting by column F and seperates them by odds and evens. Does any have a clue why this is happening? I am just using these two inserts.

insert into tableNAME values('AA', 'A55', 0, 31, 1, 1, 3)
insert into tableNAME values('AA', 'A55', 0, 31, 1, 2, 2)

These two rows would be seperated by any other rows already contained in the table. If I add more rows. It lumps them by odds and evens.

Thanks

View 1 Replies View Related

Don't Insert Duplicate Rows

Feb 20, 2004

Hi, I need to insert rows into table1 from table2 and table3 but I don't want to insert repeated combinations of col2, col3. So, table1 has the primary key col2, col3.

This the table1:

create table table1(
col1 int not null,
col2 int not null,
col3 int not null,
constraint PK_table1 primary key (col2, col3)
)

This is my "insert" code:

INSERT INTO table1
SELECT table2.col1,table2.col2, table3.col3
FROM table2, table3
WHERE table2.col1 = table3.col1

Wich conditions shoud i add to this code?

Thanks.

fmilano.

View 2 Replies View Related

How To Insert Rows Into Sql Ce From 2005

Jun 11, 2007

Hi!
I have a database in Sql Server 2005 and I want to transfer the data from 2005 to the Sql Server CE database. I've created a number of tables in CE but how do I write sql to transfer data?




Jesus saves. But Gretzky slaps in the rebound.

View 4 Replies View Related

Insert Rows From Other Server

Jul 20, 2005

Hi AllI want to insert rows from a table in a serverinto another table in another server usingINSERT SELECT command. For example :INSERT INTO Server1.database1.dbo.Tab1SELECT * FROM Server2.database2.dbo.Tab1WHERE Col1 = 1Can the command like this work ?If not, could you give me the solution ?Please help meThanks in advanceJohn Smile*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 3 Replies View Related

Which Rows To Update And Which To Insert?

Apr 9, 2008

Hello guys,

I have several rows I need to update OR insert. Right now, the code deletes all the "possibles" rows and then inserts.
I don't like this way because the index would be recreated each time it does do a DELETE/INSERT action.


Is there any "good practice" when inserting/updating rows? Any better way to do it?


Thanks.

View 4 Replies View Related

How To Do Multiple Rows Insert?

Nov 14, 2006

I want to do bulk insert of data into table.
I need to do it using System.Data.SqlServerCe namespace;


Already tryed 2 methods.

1)
SqlCeCommand command = Connection.CreateCommand();command.CommandText = "Insert INTO [Table] (col1, col2) Values (val1, val2); Insert INTO [Table] (col1, col2) Values(val11, val22)";if (Connection.State != System.Data.ConnectionState.Closed) {
  Connection.Close();
}
Connection.Open();command.ExecuteNonQuery();Connection.Close();

Doesn't work because of parsing error. Appearantly semicolon isn't understood in commandText, although if commandText is executed in SQL Management Studio it executes flawlessly.

2)
SqlCeCommand command = Connection.CreateCommand();
command.CommandText
= "INSERT INTO [Table] (col1, col2) SELECT val1, val2 UNION ALL SELECT val11, val12";
if (Connection.State != System.Data.ConnectionState.Closed) {
  Connection.Close();
}
Connection.Open();
command.ExecuteNonQuery();
Connection.Close();

Using this method i found out bug (or so i think).


I need to insert around 10000 rows of data and wouldn't want to run
Connection.Open();
Command.Execute();
Connection.Close();
cycle for 10000 times.

Any help would be appreciated. Thnx in advance.

P.S.
Sorry for bad english.

View 13 Replies View Related

Help! How To Insert Multiple Rows Into Database???

Jan 7, 2007

I keep getting this error but it will only insert the 1st row into my database table
The variable name '@CustId' has already been declared. Variable names must be unique within a query batch or stored procedure.
 
Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs)
 
 
 
 
Dim drow As GridViewRow
 
For Each drow In GridView1.Rows
 
Dim textBoxText As String = CType(drow.FindControl("Label2"), Label).Text
 
SqlDataSource2.InsertParameters.Add("CustId", TypeCode.String, Profile.UserName)
SqlDataSource2.InsertParameters.Add("OrderDate", TypeCode.DateTime, DateTime.Now.ToString)
SqlDataSource2.InsertParameters.Add("Total", TypeCode.Double, TotalUnitPrice)
SqlDataSource2.InsertParameters.Add("Quantity", TypeCode.Int32, textBoxText)
 
SqlDataSource2.Insert()
 
Next
 
 
 
 
Response.Redirect("checkout.aspx")
 
End Sub

View 3 Replies View Related

Can't Update, Insert, Or Delete Rows

Oct 20, 2007

I have recently started an ASP.Net application and am having some issues updating, inserting and deleting rows. When I started working with it, I was getting errors because it could not find any update command. Eventually, I figured out how to automatically generate the commands, by configuring my SQLDataSource control and clicking the "advanced" button. Right now though, I have generated the commands, but I still can not insert, update or delete rows. When I attempt to update anything, I recieve an error that says "The data types text and nvarchar are incompatible in the equal to operator." Nowhere in my table do I have any rows that use the datatype "nvarchar", only "text" and "int". I tried switching all of my text columns to "nvarchar(500)", which did not help. I am led to believe that the auto generated SQL procedures are trying to do something behind the scenes that is making my database act up, because even when I delete rows, I get the same exception, so the datatypes cannot be messed up there, because all that the datasource is doing is deleting rows, therefore there is no need to worry about data types. I only get the error when I check the "Use optimistic concurrency" box. When I do not use optimistic concurrency, I can delete, insert, and update rows... but nothing happens. There are no errors, but nothing is deleted, updated or inserted either. Upon postback, nothing has changed. I may upload a copy of the exact exception page, if someone thinks that it may help. Here is the update command that was generated: UPDATE [Record Information] SET [Speed] = @Speed, [Recording Company] = @Recording_Company, [Year] = @Year, [Artist] = @Artist, [Side 1 Track Title] = @Side_1_Track_Title, [Side 1 Track Duration] = @Side_1_Track_Duration, [Side 2 Track Title] = @Side_2_Track_Title, [Side 2 Track Duration] = @Side_2_Track_Duration, [Sleeve Description] = @Sleeve_Description WHERE [Record Database ID] = @original_Record_Database_ID
Apparently no stored procedures exist for any of these operations, and I am unsure why. The "Record Database ID" is my identity column, and is the only field that is (and is supposed to be) uneditable.

View 1 Replies View Related

Insert Rows Into Four Tables At One Time

Nov 14, 2007

Hello,I have 4 tables having Customer, Customer_personal_info, Customer_Financial_info, Customer_Other_infoIn this Customer table had a primary key CustomerID , related with every other table with fkey.I want to insert data into four tables using one form having TABs .I created class and storedProcedures to insert row  for each table.How to execute all four classes using beginTrans-commitTrans-Rollback-EndTrans. Thanking you, 

View 1 Replies View Related

Bulk Insert Skips Rows

Dec 6, 2007

Hi Guys,
My little bulk insert is only bringing every second row of a CSV file. this is not good as i need every row.
My SQLcommand is thus.
InsertCommand="BULK INSERT TBL_Unitel_services FROM 'C:/webroot/servicedesk/csvs_Services/csv.csv' WITH (FIRSTROW = 1, FIELDTERMINATOR = ',', ROWTERMINATOR = '', MAXERRORS = 0) "
 
 
 
 

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved