Advanced Options In BULK INSERT Command

Jul 4, 2007

Generally bulk insert is used to insert bulk data from flat files or csv files into database tables. Thats good. But while inserting the data into table, if the schema of the table changes (say a column is added or removed) then are there any options for inserting the same data, with some changes, into the database table...
My work is to take backup of the tables and insert them to a new database with some changes in schema like adding a column or removing a column.

View 5 Replies


ADVERTISEMENT

Quotes Options For Bulk Insert

Sep 6, 2000

Hi,

I want to import in a table a file like this one, with a comma separator:

France, 1 , 1
"Congo,Démocratique", 1,2

Is there any options for Bulk Insert like this in Sybase :

LOAD [ INTO ] TABLE [ owner ].table-name [( column-name , ... )]
FROM 'filename -string '
[ load-option ... ]

Parameters

load-option :
CHECK CONSTRAINTS { ON | OFF }
| DEFAULTS { ON | OFF }
| DELIMITED BY string
| ESCAPE CHARACTER character
| ESCAPES { ON | OFF }
| FORMAT ASCII
| QUOTES { ON | OFF }
| STRIP { ON | OFF }
| WITH CHECKPOINT { ON | OFF }

or should we change the pre processing of our log files ??

Thanks to your answers
Axel

View 2 Replies View Related

Bulk Insert Command

Oct 13, 2005

I am trying to use the 'Bulk Insert' command to load a data file into aMS-SQL db. The line I am using is:Bulk Insert SVC_Details From "C:XFILE.TXT" With (FieldTerminator = ',')I have tried the file name with " around it and with ' around it and boththe " and ', but every time the Query Analyzer comes back with the followingerror:---Begin Error Msg---Server: Msg 4861, Level 16, State 1, Line 1Could not bulk insert because file ''C:XFILE.TXT'' could not be opened.Operating system error code 123(The filename, directory name, or volumelabel syntax is incorrect.).---End Msg---I am not sure what is wrong with this line. Can any one tell me what iswrong?Mikem charney at dunlap hospital dot org

View 1 Replies View Related

Bulk Insert Command With Dbf File

Dec 28, 2007

hi friends
i am using bulk insert command for txt files
but now i want to use bulk insert command for dbf files
so plz any one can tell me how to use this command for dbf files
thanx in advance.........

View 1 Replies View Related

How To Read A Remote CSV File Into SQL SERVER Using Bulk Insert Command..

Mar 24, 2008

Hi,
I have load a CSV file into one of the table in sql server 2005 using bulk insert command. But the csv file in remote system.
Please help me.....

View 1 Replies View Related

Sqldatasource And Advanced Options

Mar 13, 2007

Hello, I am trying to bind a sqldatasource control to the gridview.
I have selected the sqldatasource control and specified the connection string, on configure  Select statement page under advanced options both the check marks
Generate INSERT, UPDATE and DELETE Statements
Use Optimistic Concurrency  
are disabled for me.I have a proper SQL Server database not an express data base, how do i get to generate the InserCommand, EditCommand etc
Any help would be great .. relatively new here
thanks 
 

View 2 Replies View Related

Advanced SQL Generations Options

May 13, 2007

Advanced SQL generations options:
generate INSERT, UPDATE, and DELETE statements is all greyed out?
in my sql data source control.?
I have made a brand new instance with sql server management express....have I missed something?

View 4 Replies View Related

SQL Express SP1 With Advanced Options

Oct 11, 2006

I'm having trouble installing SQLEXPR_ADV. It says it's not a valid win32 app.

System:

Dell Precision 380

3.00ghz

2gb RAM

Windows XP Pro SP2

View 4 Replies View Related

Advanced SQL Generation Options In VS2005

Nov 7, 2005

Hi There

View 15 Replies View Related

SQL 2005 Express With Advanced Options

Jul 13, 2007

Why did Microsoft release a replacement for MSDE called SQL 2005 Express with Advanced Options without the SQL Agent? I bought into the MSDE and got rid of the other databases we supported now we have to go a create an equivilent to the SQL Agent for a) scheduled jobs b) backup automation. c) etc.

Does anybody really want a database that does not have these built in? I understand I can do these myself but this seems to defy logic to me?



thanks,

Larry

View 1 Replies View Related

Redistribution And Setting Advanced Options

Nov 28, 2005

I would like redistribute SQL 05 EPRX in a small application; trying something other than Access database.  I have the program written and I have query that requires the advanced ad hoc queries setting to be one.  I am wondering if anyone can point me to an article that explains how I can adjust these settings so they will install through my setup file properly.

View 3 Replies View Related

Why Are The Advanced Options Greyed Out, In The Connection Configuration

Jan 30, 2008

I have added several SqlDataSource controls to my C# project, but when I try to configure them, there seems to be a lot of greyed out buttons. Either the WHERE, ORDER BY, and ADVANCED buttons are greyed out, or when they are active, the ADVANCED button always displays the advabced options which are always greyed out. I doesn't make any sense to me. Please explain (in baby language please)

View 4 Replies View Related

Error: 15457 In NT Event Log - 'show Advanced Options'

May 3, 2002

In SQL 2000, the following message is logged to the NT event log every time the sp_configure 'show advanced options' command is executed. This did NOT occurr in SQL 7.

"Error: 15457, Severity: 0, State: 1
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install."

Does anyone know if I can turn OFF the event logging of this message. It is confusing to the end users of our product that uses SQL 2000 as a back-end. Some of our product's code issues the sp_configure command that causes this message. It shows up as an informational message, but then the first word in the text is "Error:". I think I'll be getting a lot of support calls about this if I can't figure out a way to make it stop writting this to the event log :(

Any insight or suggestions would be most appreciated.

View 2 Replies View Related

How Do You Use An Identity Column When Doing A Bulk Insert Using The Bulk Insert Task Editor

Apr 18, 2008



Hello,

I'm just learning SSIS and I've hit my first bump. I am doing a bulk import from a tab delimited text file to an empty sql table that has a Idendity column defined. How do I tell the bulk insert task to skip that column when inserting from the text file. If I remove the identity column it imports the data fine, but I want to create the indentity column in the table too.

Thanks.

View 8 Replies View Related

Turning On SET Options Before Bulk Load?

Jul 27, 2007



I need to execute the following:


SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF

before I do a bulk load because the table I am inserting into has an indexed view created on it. Whats the best way to set these options prior to a bulk load?

View 8 Replies View Related

How Read CSV File In Other Server Using Bulk Insert Command In Sql Server In Different Server...

Mar 24, 2008

Hi All,
 Please some one help me...
I have to insert a csv into one table in sql server. But the problem is the file is in one server and SQL SERVER 2005 is in other server..
 how do i insert the file....
please help me.....
 

View 1 Replies View Related

Bulk Insert Using Script And Not Bulk Insert Task

Nov 2, 2007



Does anyone know how to do a bulk insert using just the script task? I've been searching everyehere but can't seem to find a sample.

View 6 Replies View Related

Passing Command-line Options Through To Package.Execute()

Mar 8, 2008



Hi,
I'm looking into the idea of building an enhanced version of dtexec.exe that builds in some extra logging features. My utility will execute packages using the Package.Execute() method.

Thing is, I'd still want to support all of the command-line options that dtexec supports. For example, my utility should accept "/set package.variables[myvariable].Value;myvalue" and pass it through to the executing package but I can't find a way of doing it using Package.Execute().

Am I missing something or is this just not possible?

Thanks
Jamie


[Microsoft follow-up]

View 7 Replies View Related

Bulk Insert - Bulk Load Data Conversion Error

Jan 17, 2008

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 ?

View 13 Replies View Related

I Don't Suppose BULK UPDATE Exists?... Like BULK INSERT?

Sep 27, 2007

I have to update a field within a table of 60 records or so. Each record has a different field value. it's type varchar. i was given an excel file with the field values and was thinking of a bulk update like bulk insert, but i don't recall that it's possible that way.

Is the only way to create a table, bulk insert, then merge the two tables together with UPDATE?

Just wanted to see if there was an easier way to do it, otherwise i'll take the latter route. Thanks!

View 1 Replies View Related

Cannot Fetch A Row From OLE DB Provider BULK With Bulk Insert Task

Nov 23, 2005

Hi, folks:

View 18 Replies View Related

Command Line Installation Of SQL Server 2005 Express With Advanced Features

Oct 19, 2006

Hi,

I'm trying to install SQL Express 2005 with Reporting Services using Install Shield 11.5. The command line parameters which i am using is:

/qn ADDLOCAL=SQL_Engine,SQL_Data_Files,RS_Server,RS_Web_Interface,Client_Components SECURITYMODE=SQL SAPWD=<password> DISABLENETWORKPROTOCOLS=0 RSCONFIGURATION=Default RSSQLLOCAL=1 AUTOSTART=1



It installs the SQL Server and Reporting Services. It configures (creates Virtual Directories for report server and database) but it is not initializing the Report Server.

Having done the installation when i check the Report Server Status it was running and everything seems to be OK except it wasn't initialized.

Any idea??

Thanks,

Moby.

View 4 Replies View Related

Pros: How To Bulk Delete And Bulk Insert?

Oct 11, 2000

I have a table containing 8 million records.
I need to replace 2 million of these records with
a scaled down query that goes something like:
SELECT 1, ShareholderID, Assets1
FROM MyTable (Yields appx. 200,000 recods)
SELECT 2, ShareholderID, Assets2
FROM MyTable (Yields appx. 200,000 recods)
.
.
.
SELECT 10, ShareholderID, Assets1 + Assest2 + Assets3 + ... + Assets9
FROM MyTable (Yields appx. 200,000 recods)

Updates and cursors just seem to be too slow.

So far I have done the following, but was wondering if anyone could think of a better way.
SELECT 6 million records that don't need to be deleted into a #TempTable
Use statements above to select into same #TempTable
DROP and recreate Original Table
SELECT 6 + 2 million records INTO original table.

This seems rather convoluted. Is there a better approach? Would it be worth while to dump data to a file and use bcp / Bulk Insert


Any comments are appreciated,

-Marc

View 3 Replies View Related

Defining Command,commandtype And Connectionstring For SELECT Command Is Not Similar To INSERT And UPDATE

Feb 23, 2007

i am using visual web developer 2005 and SQL 2005 with VB as the code behindi am using INSERT command like this        Dim test As New SqlDataSource()        test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString()        test.InsertCommandType = SqlDataSourceCommandType.Text        test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@roll,@name, @age, @email) "                  test.InsertParameters.Add("roll", TextBox1.Text)        test.InsertParameters.Add("name", TextBox2.Text)        test.InsertParameters.Add("age", TextBox3.Text)        test.InsertParameters.Add("email", TextBox4.Text)        test.Insert() i am using UPDATE command like this        Dim test As New SqlDataSource()        test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString()        test.UpdateCommandType = SqlDataSourceCommandType.Text        test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll                                                         123 "        test.Update()but i have to use the SELECT command like this which is completely different from INSERT and  UPDATE commands   Dim tblData As New Data.DataTable()         Dim conn As New Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated                                                                                Security=True;User Instance=True")   Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn)   Dim da As New Data.SqlClient.SqlDataAdapter(Command)   da.Fill(tblData)   conn.Close()                   TextBox4.Text = tblData.Rows(1).Item("name").ToString()        TextBox5.Text = tblData.Rows(1).Item("age").ToString()        TextBox6.Text = tblData.Rows(1).Item("email").ToString()       for INSERT and UPDATE commands defining the command,commandtype and connectionstring is samebut for the SELECT command it is completely different. why ?can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?if its possible how to do ?please help me

View 2 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

BULK INSERT ERROR Using Format File - Bulk Load Data Conversion Error

Jun 29, 2015

I'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]...

View 5 Replies View Related

Questions About Bulk Copy Insert Using 'Memory Based Bulk Copy Operations'

Feb 1, 2007

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

View 1 Replies View Related

INSERT Failed Because Of Incorrect SET Options

Jul 23, 2005

I am trying to insert a row into a table using a stored procedure and Iget the following error if I try this from QA:INSERT failed because the following SET options have incorrectsettings: 'ANSI_NULLS., QUOTED_IDENTIFIER'.If I try to run this from Microsoft Access, I get a slightly differenterror:INSERT failed because the following SET options have incorrectsettings: 'ANSI_NULLS., QUOTED_IDENTIFIER, ARITHABORT'.This is what I'm trying to run in QA:declare @P1 intset @P1=NULLexec stpAddNewDistributionMaster 142, 2, 'INTRODUCTION OF FILTERASSEMBLY', 0, 1, @P1 outputselect @P1===========================Here are the relevant definitions:TABLE:CREATE TABLE [dbo].[tblDistributionMaster] ([fldDistributionID] [int] IDENTITY (1, 1) NOT NULL ,[fldDocumentID] [int] NULL ,[fldDocumentType] [int] NULL ,[fldDocumentTitle] [varchar] (255) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[fldDocumentSiteID] [int] NULL ,[fldActive] [bit] NOT NULL) ON [PRIMARY]GOALTER TABLE [dbo].[tblDistributionMaster] WITH NOCHECK ADDCONSTRAINT [DF__Temporary__fldDo__2739D489] DEFAULT (0) FOR[fldDocumentID],CONSTRAINT [DF__Temporary__fldDo__282DF8C2] DEFAULT (0) FOR[fldDocumentType],CONSTRAINT [DF__Temporary__fldDo__29221CFB] DEFAULT (0) FOR[fldDocumentSiteID],CONSTRAINT [DF__Temporary__fldAc__2A164134] DEFAULT (1) FOR[fldActive],CONSTRAINT [aaaaatblDistributionMaster_PK] PRIMARY KEY NONCLUSTERED([fldDistributionID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOCREATE INDEX [fldDistributionID] ON[dbo].[tblDistributionMaster]([fldDistributionID]) WITH FILLFACTOR =90 ON [PRIMARY]GOCREATE INDEX [fldDocumentID] ON[dbo].[tblDistributionMaster]([fldDocumentID]) WITH FILLFACTOR = 90 ON[PRIMARY]GOCREATE INDEX [fldDocumentSiteID] ON[dbo].[tblDistributionMaster]([fldDocumentSiteID]) WITH FILLFACTOR =90 ON [PRIMARY]GOCREATE INDEX [fldDocumentType] ON[dbo].[tblDistributionMaster]([fldDocumentType]) WITH FILLFACTOR = 90ON [PRIMARY]GO/****** The index created by the following statement is for internaluse only. ******//****** It is not a real index but exists as statistics only. ******/if (@@microsoftversion > 0x07000000 )EXEC ('CREATE STATISTICS [hind_37575172_1A_3A] ON[dbo].[tblDistributionMaster] ([fldDistributionID], [fldDocumentType])')GO/****** The index created by the following statement is for internaluse only. ******//****** It is not a real index but exists as statistics only. ******/if (@@microsoftversion > 0x07000000 )EXEC ('CREATE STATISTICS [hind_37575172_3A_1A] ON[dbo].[tblDistributionMaster] ([fldDocumentType], [fldDistributionID])')GO/****** The index created by the following statement is for internaluse only. ******//****** It is not a real index but exists as statistics only. ******/if (@@microsoftversion > 0x07000000 )EXEC ('CREATE STATISTICS [hind_37575172_2A_1A] ON[dbo].[tblDistributionMaster] ([fldDocumentID], [fldDistributionID]) ')GO/****** The index created by the following statement is for internaluse only. ******//****** It is not a real index but exists as statistics only. ******/if (@@microsoftversion > 0x07000000 )EXEC ('CREATE STATISTICS [hind_37575172_3A_2A] ON[dbo].[tblDistributionMaster] ([fldDocumentType], [fldDocumentID]) ')GO/****** The index created by the following statement is for internaluse only. ******//****** It is not a real index but exists as statistics only. ******/if (@@microsoftversion > 0x07000000 )EXEC ('CREATE STATISTICS [hind_37575172_2A_3A] ON[dbo].[tblDistributionMaster] ([fldDocumentID], [fldDocumentType]) ')GO/****** The index created by the following statement is for internaluse only. ******//****** It is not a real index but exists as statistics only. ******/if (@@microsoftversion > 0x07000000 )EXEC ('CREATE STATISTICS [hind_37575172_1A_2A_3A] ON[dbo].[tblDistributionMaster] ([fldDistributionID], [fldDocumentID],[fldDocumentType]) ')GOSET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOCREATE TRIGGER "tblDistributionMaster_UTrig" ONdbo.tblDistributionMaster FOR UPDATE ASSET NOCOUNT ON/* * PREVENT UPDATES IF DEPENDENT RECORDS IN 'tblJobs' */IF UPDATE(fldDistributionID)BEGINIF (SELECT COUNT(*) FROM deleted, tblJobs WHERE(deleted.fldDistributionID = tblJobs.fldDistributionID)) > 0BEGINRAISERROR 44446 'The record can''t be deleted orchanged. Since related records exist in table ''tblJobs'', referentialintegrity rules would be violated.'ROLLBACK TRANSACTIONENDENDGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOCREATE TRIGGER "tblDistributionMaster_DTrig" ONdbo.tblDistributionMaster FOR DELETE ASSET NOCOUNT ON/* * CASCADE DELETES TO 'tblJobs' */DELETE tblJobs FROM deleted, tblJobs WHERE deleted.fldDistributionID =tblJobs.fldDistributionIDGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO===========================SPROC:CREATE PROCEDURE stpAddNewDistributionMaster@DocumentID int,@DocumentType int,@Title varchar(255),@SiteID int,@Active bit,@DistributionID int OUTPUTASINSERT INTO tblDistributionMaster(fldDocumentID,fldDocumentType,fldDocumentTitle,fldActive,fldDocumentSiteID)VALUES(@DocumentID,@DocumentType,@Title,@Active,@SiteID)SET @DistributionID = IDENT_CURRENT('tblDistributionMaster')GO==============================Thanks in advanceEdward

View 4 Replies View Related

Can I Insert/Update Large Text Field To Database Without Bulk Insert?

Nov 14, 2007

I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind.  I've tried using the new .write() method in my update statement, but it cuts off the text after a while.  Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.

View 6 Replies View Related

How To Insert Data From A File Into Table Having Two Columns-BULK INSERT

Oct 12, 2007



Hi,
i have a file which consists data as below,

3
123||
456||
789||

Iam reading file using bulk insert and iam inserting these phone numbers into table having one column as below.


BULK INSERT TABLE_NAME FROM 'FILE_PATH'
WITH (KEEPNULLS,FIRSTROW=2,ROWTERMINATOR = '||')

but i want to insert the data into table having two columns. if iam trying to insert the data into table having two columns its not inserting.

can anyone help me how to do this?

Thanks,
-Badri

View 5 Replies View Related

Advanced (insert, Delete ..) Option Not Available

Feb 18, 2008

Hi,
i want to create a sqldatasource in VWD also with advanced (insert, delete, update) option.
This works fine for 'normal' tables, but when i try to do that with e.g. aspnet_roles table (indicated as vw_aspnet_Roles in dropdownlist of VWD), or aspnet_users table, that advanced option is not available, even when i select all fields.
Why, and how can i solve that?
Thanks
Tartuffe
 

View 1 Replies View Related

INSERT Failed Because The Following SET Options Have Incorrect Settings: 'ARITHABORT'

Oct 23, 2007

Hi

I am currently running the following query from Query Analyser, I am connected to Server_A and inserting records into
Server_B.Database_B.dbo.MyTable from Server_A .Database_A.dbo.TableRef

Insert into Server_B.Database_B.dbo.MyTable(Field1,Field2,Field3)
Select Field1_Ref,

Field2_Ref,
Field3_Ref
from Server_A .Database_A.dbo.TableRef

However Server_B.Database_B.dbo.MyTable is referenced within an Indexed View and whenever I run this query I get the following error:


Msg 1934, Level 16, State 1, Line 1

INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.


I have tried setting ARITHABORT to ON & OFF within the Query and within the database properties but still recieve the same error.

Does anyone have any ideas on why this would not work?

Thanks

View 19 Replies View Related

Insert Command Fails When I Want To Insert Records In Data Table

Apr 20, 2008

On my site users can register using ASP Membership Create user Wizard control.
I am also using the wizard control to design a simple question and answer  form that logged in users have access to.
it has 2 questions including a text box for Q1 and  dropdown list for Q2.
I have a table in my database called "Players" which has 3 Columns
UserId Primary Key of type Unique Identifyer
PlayerName Type String
PlayerGenre Type Sting
 
On completing the wizard and clicking the finish button, I want the data to be inserted into the SQl express Players table.
I am having problems getting this to work and keep getting exceptions.
 Be very helpful if somebody could check the code and advise where the problem is??
 
 
<asp:Wizard ID="Wizard1" runat="server" BackColor="#F7F6F3"
BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px"
DisplaySideBar="False" Font-Names="Verdana" Font-Size="0.8em" Height="354px"
onfinishbuttonclick="Wizard1_FinishButtonClick" Width="631px">
<SideBarTemplate>
<asp:DataList ID="SideBarList" runat="server">
<ItemTemplate>
<asp:LinkButton ID="SideBarButton" runat="server" BorderWidth="0px"
Font-Names="Verdana" ForeColor="White"></asp:LinkButton>
</ItemTemplate>
<SelectedItemStyle Font-Bold="True" />
</asp:DataList>
</SideBarTemplate>
<StepStyle BackColor="#669999" BorderWidth="0px" ForeColor="#5D7B9D" />
<NavigationStyle VerticalAlign="Top" />
<WizardSteps>
<asp:WizardStep runat="server">
<table class="style1">
<tr>
<td class="style4">
A<span class="style6">Player Name</span></td>
<td class="style3">
<asp:TextBox ID="PlayerName" runat="server"></asp:TextBox>
</td>
<td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
ControlToValidate="PlayerName" ErrorMessage="RequiredFieldValidator"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td class="style5">
 
<td class="style3">
<asp:DropDownList ID="PlayerGenre" runat="server" Width="128px">
<asp:ListItem Value="-1">Select Genre</asp:ListItem>
<asp:ListItem>Male</asp:ListItem>
<asp:ListItem>Female</asp:ListItem>
</asp:DropDownList>
</td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server"
ControlToValidate="PlayerGenre" ErrorMessage="RequiredFieldValidator"></asp:RequiredFieldValidator>
</td>
 
</tr>
</table>
  Sql Data Source
<asp:SqlDataSource ID="InsertArtist1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" InsertCommand="INSERT INTO [Playerst] ([UserId], [PlayerName], [PlayerGenre]) VALUES (@UserId, @PlayerName, @PlayerGenre)"
 
ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>">
<InsertParameters>
<asp:Parameter Name="UserId" Type="Object" />
<asp:Parameter Name="PlayerName" Type="String" />
<asp:Parameter Name="PlayerGenre" Type="String" />
</InsertParameters>
 
 
</asp:SqlDataSource>
</asp:WizardStep>
 
 Event Handler
 
To match the answers to the user I get the UserId and insert this into the database to.protected void Wizard1_FinishButtonClick(object sender, WizardNavigationEventArgs e)
{
 SqlDataSource DataSource = (SqlDataSource)Wizard1.FindControl("InsertArtist1");
MembershipUser myUser = Membership.GetUser(this.User.Identity.Name);
Guid UserId = (Guid)myUser.ProviderUserKey;String Gender = ((DropDownList)Wizard1.FindControl("PlayerGenre")).SelectedValue;
DataSource.InsertParameters.Add("UserId", UserId.ToString());DataSource.InsertParameters.Add("PlayerGenre", Gender.ToString());
DataSource.Insert();
 
}
 

View 1 Replies View Related







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