MS SQL Server :: How Do I Prevent An Insert Into Statement To Increase Tempdb Data Files So Much

Cannot INSERT Data To 3 Tables Linked With Relationship (INSERT Statement Conflicted With The FOREIGN KEY Constraint Error)
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 Replies !

Increase The Speed Of Insert Statment
Hi all

i'm using sqlserver 2005

this statment take 1:30 min to execute

****insert into temotable (select key from table1)

if i used a select statment alone it takes 4 sec

but with insert statment it take 1:30min

by the way i put indexes on the table1

plz how i can increase the speed of insert statment.

thanks in advance

View Replies !

Prevent Duplicate Insert
How can I prevent duplicate inserts or entries to a table?
Thank you.
Note: I am using SQL Server and coding ASP.net pages in VB.

View Replies !

Bulk Insert With CSV Data Files
SQL Server 7.0 doesn't seem to support data files for bulk insert that have quoted text fields.

e.g.
" 1","Farmer","Joe","AAA","Smith John","",20001001,

I've tried using the format file to strip out the quotes. But, this doesn't seem to work.

My format file looks like this:
4.2
9
1 SQLCHAR 0 0 """ 0 dummy1
2 SQLCHAR 0 9 "","" 2 EmployeeID
3 SQLCHAR 0 35 "","" 3 LastName
4 SQLCHAR 0 35 "","" 4 FirstName
5 SQLCHAR 0 10 "","" 5 Category
6 SQLCHAR 0 35 "","" 6 Supervisor
7 SQLCHAR 0 5 ""," 7 OpCode
8 SQLCHAR 0 8 "," 8 HireDate
9 SQLCHAR 0 8 "
" 9 TermDate

Any idea on how I can bulk insert a data file where some of the fields are qutoed.

View Replies !

Bulk Insert Fails To Import Data Files Created On Unix
It seems to me that files created on Unix machines with line terminator , or chr(10), cannot be imported using the Bulk Insert statement. Is this a bug, or an oversight by Microsoft? Does this mean that unless one replaces all with
, there is no way to use Bulk Insert to import Unix files? This is a very strange behavior by MSSQL. Even lessor programs such as Excel and Word automatically recognize chr(10) as a line termination character. Am I missing something, or is this just the way MSSQL is?

View Replies !

Insert Statement Results In Data Duplication Using MDAC 2.8
I am using Remote Data service to Query an Sql Server Database using MDAC. The Os in which server is loaded in Window 2003 and the MDAC 2.8 version is installed. Now I create a table X with identity column. Then when I try to insert records in that table using Insert into X select * from Y statement. The statement gets executed by when i check the X table I find that the duplicate records are present with different identity no's.

Even when i truncate and retry the same thing occurs.

What could be the reason for the same??

Regards
Pranali.cons

View Replies !

Insert Statement That Pulls Column Data From Another Table?
hi.

i cant get this quite right.

i have a table and i need to insert one column with data from another table. it goes something like this (although i know this is wrong, just here for a visual explaination) :

Code:

INSERT INTO List
(list_date, email_address, list_status, list_email)
values
(
GetDate()
, 'name@rice.edu'
, 0
, SELECT emailAddress FROM Users WHERE id = '72'
)

so, list_email needs the email address from the Users
table. i tried messing around with inner joins but, well,
here i am...

thanks in advace.

View Replies !

Problem With Data Refreshing After Insert Or Update Statement
Dear All,

Im using VS2008, visual basic and SQL Server express.

General questions please: Is it necessary, to issue some sort of command, to 'commit' data to a SQL express database after a INSERT or UPDATE sql command?

I'm getting strange behavior where the data is not refreshed unless i exit my app and re-enter. In other words, i can run a sql command , the data is apparantly saved (because i get no errors) then if i refresh a data set or do a sql select query the data that i expect to return is not there.

Im fairly new to SQL express (and SQL server generally) so i dont know if its my coding or i need to switch some 'feature'
on/off or not.

I hope thats clear

Also, could someone point me to documentation that explains each parameter in the connection string

Many Thanks

Chris Anderson

My code is:

ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=C:myfile.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"

Connection = New SqlConnection

Connection.ConnectionString = ConnectionString

Connection.Open()
'''''''''''''''''the above code is done at the start of my application

'''''''this code below called during application many times

dim sql as string = "my sql string here"

Dim cmd As SqlCommand = Nothing

cmd = New SqlCommand(sql, Connection)

Try

cmd.ExecuteNonQuery()

Catch err As SqlException

MessageBox.Show(err.Message.ToString())

MessageBox.Show(sql, "SQL ERROR: ", MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try

cmd.Dispose()

View Replies !

How To Drop One Of The Tempdb Files
Hi all, I have a tempdb that consists of 8 datafiles, tempdb_data_1 totempdb_data_8, each is 8GB. Now how can I drop 7 of them and leaveonly tempdb_data_1? Can this be done? Thanks a lot.

View Replies !

TempDB Log Files Are Filling Up.
What is the best way to fix issues with your log files in TempDB when you start to see them causing error msgs?

Thansk for your time.

View Replies !

Relocation Of Tempdb And Log Files
Can someone help me please... I am new to SQL. We have just installed SMS 2.0 and SQL 7.0. I want to know how to relocate the tempdb and other log files. During the custom installation it asked for places to put the program files and data files, but not log files. I tried going in and moving the location of tempdb, to get an error that states "once created, it can't be moved". The books say that to optimize SQL, it's recommended to put the tempdb on a separate drive.

Thanks in advance.

View Replies !

TempDB Disk Placement With Log Files?
Hello,I've a couple of questions on setting up my lovely new server... I'vedecided on R1 (2*36GB) for the OS, R1 (2*73GB) primarily for the LogFiles and R5 (5*73GB) for the Data files.I was wondering if I could increase performance by partitioning off/creating a new volume on my log array and placing on there the tempdband possibly another volume for the indexes.ndf.Any ideas on whether its OK to have other volumes use the same pysicaldisks as the log array?And, is it best to have as many data files for each database as thereare on your disk array, so 5 in my case (or is it 4 as it's R5 , i.e.1-n disks).Setup Ideas welcomed!Rob

View Replies !

System.Data.OleDb.OleDbException: Syntax Error In INSERT INTO Statement.
Hi All I'm having a bit of trouble with an sql statement being inserted into a database - here is the statement: string sql1;
sql1 = "INSERT into Customer (Title, FirstName, FamilyName, Number, Road, Town,";
sql1 += " Postcode, Phone, DateOfBirth, email, PaymentAcctNo)";
sql1 += " VALUES (";
sql1 += "'" + TxtTitle.Text + "'," ;
sql1 += "'" + TxtForename.Text + "'," ;
sql1 += "'" + TxtSurname.Text + "'," ;
sql1 += "'" + TxtHouseNo.Text + "',";
sql1 += "'" + TxtRoad.Text + "',";
sql1 += "'" + TxtTown.Text + "',";
sql1 += "'" + TxtPostcode.Text + "',";
sql1 += "'" + TxtPhone.Text + "',";
sql1 += "'" + TxtDob.Text + "',";
sql1 += "'" + TxtEmail.Text + "',";
sql1 += "'" + TxtPayAcc.Text + "')"; Which generates a statement like:INSERT into Customer (Title, FirstName, FamilyName,
Number, Road, Town, Postcode, Phone, DateOfBirth, email, PaymentAcctNo) VALUES ('Mr','Test','Test','129','Test Road','Plymouth','PL5
1LL','07855786111','14/04/1930','mr@test.com','123456') I cannot for the life of me figure out what is wrong with this statement. I've ensured all the fields within the database have no validation (this is done within my ASP code) that would stop this statement being inserted. Line 158: dbCommand.Connection = conn;Line 159: conn.Open();Line 160: dbCommand.ExecuteNonQuery();Is the line that brings up the error - I presume this could be either an error in the statement or maybe some settings on the Database stopping the values being added. Any ideas which of this might be ? I'm not looking for someone to solve this for me, just a push in the right direction! Thanks!

View Replies !

Archive Data Instead Of Deleting It To Prevent 4GB Data Limit
We are running SQL Server 2005 express on Windows 2003. The database server gets significant amounts of data.

Because of the 4GB data limit we have a daily cron task which goes through and deletes data older then 90 days.

We would like a way to archive this data instead of deleting it. Is there any way to take data and compress it and store it in a different way, so that if needed, customers can query directly out from the compressed data? Cleary querying from compressed would be slower but that is ok.

Any other solutions that would allow us to archive data instead of deleting it? Thanks.

View Replies !

How To Start SQL Server When Tempdb Files Were Relocated?
It's been a long time since I've tried this, but I have a SQL Server that needs to be restored (including master) to a server whose drives and corresponding folders match the source server, with the exception of tempdb. When SQL Server initially starts I believe it will fail since it cannot find tempdb. I just don't recall if it fails to startup or if it starts up reporting errors and recreates tempdb in the same location as master. Does anyone recall the steps needed to point SQL Server to the new location of tempdb?

Dave

View Replies !

1 Log File Can Contain More Records After Performing Backup Database Statement, 2 Why Can't Insert Data When Is Log Is Not Full
question 1:

i found that database log file can contain more records after performing backup database statement.

for example:

i create a database and limit the log file to 2mb. then i create a table and insert data.

If i backup the database before i insert data , the database file can contain 192 records unitl the log file is full.

If i don't perform the 'backup database' statement.
The 'dbcc sqlperf(logspace)' indicate the utilization ratio is less than 40% after inserting 192 records

why?

I list my code:

Code Snippet
create database db_test
on primary
(
name=db_test,
filename='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatadb_test.mdf'
)
log on
(
name=db_test_log,
filename='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatadb_test_log.ldf',
maxsize=2mb
)
go
backup database db_test to disk='db_test.bak' --- if i don't execute this line, log file can contain a lot of record
go
create table db_test..table1(col char(8000))
--insert data to fill up the database log
declare @n int
set @n=0
while @n<192
begin
insert into db_test..table1 values(replicate('a',8000))
set @n=@n+1
end

question 2:
i create a database and limit the log file to 2mb. Then i create a table and insert data in an endless loop.

After the inserting operation executing for a while, the 9002 error occurs, indicate the log file for the database is full.
But the 'dbcc sqlperf(logspace)' command indicate the unilization ratio is low, and log_reuse_wait_desc in sys.database is 'CHECKPOINT'
And I can insert data , and i'm sure the state of log_use_wait_desc is 'CHECKPOINT'.

As i known, the checkpoint can't truncate log under full recovery model. Only the back log operation can truncate the transaction log.
So log is not full, why 9002 error is encounterd. and why the log_reuse_wait_desc return 'CHECKPOINT'?

I list my code:

Code Snippet
create database db_test
on primary
(
name=db_test,
filename='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatadb_test.mdf'
)
log on
(
name=db_test_log,
filename='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatadb_test_log.ldf',
maxsize=2mb
)
go
create table db_test..table1(col char(8000))

--insert data to fill up the database log
declare @n int
set @n=0
while @n<>-1
begin
insert into db_test..table1 values(replicate('a',8000))
end

any suggestions?

thanks in advance.

View Replies !

Waht Is Tempdb Database Used For? And How Can We Determine What Files Can Be Deleted From It?
Hi, all experts here,

Thank you very much for your kind attention.

Just found that my tempdb is always full whenever I run a query against a large database. Could please any experts here give me any advices on what is tempdb database used for and how to determine what files can be deleted from it?

I am looking forward to hearing from you and thanks a lot in advance.

With best regards,

Yours sincerely,

View Replies !

How To Increase The Data File Size?
Hi All,

Is it possible to increase the data file size in SQL Server 2005 Express edition?

If yes then how?

Thanks,

Varun

View Replies !

How To Increase The Data File Size?
Hi All,
Is it possible to increase the size of data file in SQL Server 2005 Express Edition.
I think the licensed limit is 4096MB whcih i am unable to increase.
Could anyone let if is it possible to increase the data file size and if yes then how?

Thanks,
Varun

View Replies !

Prevent Data Being Inserted Twice
I have a table with 3 columns: ID, Status, DateTime.

I created a stored procedure to insert a staus value for each ID. This will run every hour. The DateTime stores the time, date when the Status was inserted.

If the procedure was to be run a second time in hour window I do not want any Status to be inserted.

Note: that I cannot rely on the procedure being run at exactly the right time - if it was scheduled to run on the hour (i.e at 1:00, 2:00, 3 :00 etc) but didn't run until 1:20 it sould still be able to run at 2:00.

Does anyone know if there is anyway I can gaurd against this?

View Replies !

How Can I Prevent That All Data Can Be Seen With Notepad?
Hello,

is there a way to say to SQL Server to make the data not readable?

Regards
Markus

View Replies !

Delete Data, But File Size Increase
I encounter one weird problem, I have a database with around 7 GB ...when I delete a bunch of data from it, it suppose to reduce thedatabase file size, but weirdly, the file size increase to 8 GB.Wondering why. Is it suppose to be like that?Is it the architecture is designed to work like that?Is there any way for me to reduce the database file size?Thanks.Peter CCH

View Replies !

Wants To Increase Digits Of Numeric Data Type.

Hi! Every one,
I need to increase my numeric digit in a table.previous column name was "Amount",Precision=18 and scale=6, when I increase Precision=28 it show "Airthmatec Error",
even when increase precision in a new table it work properly.

Thanks.

View Replies !

How Can I Prevent From Inserting Duplicate Data?

I have a table storing only 2 FKs, let's say PID, MID
Is there any way that I can check distinct data before row is added to this table?
For example, current data is
PID MID------------100 2001100 2005101 3002102 1009102 7523102 2449
If my query is about to insert PID 100, MID 2001, since it's existing data, i don't want to add it. Can I use trigger to solve this issue?

Thanks.

View Replies !

How To Prevent DBA From Getting Confidential Data Stored In SQL Server?
Suppose I have a database storing some confidential information, such
as legal information, medical or financial records, etc., and a
Web site with a membership system so that only authorized users can
view the information.

I understand I can encrypt the information, and the user's passwords,
so that if the database is compromised it still shouldn't be possible
for an outsider to view the confidential information.

However, what about people who have legitimate access to the database,
such as the DBA, Web developer, etc., but who should not be able to
view the confidential information? For example, even though the
user's password was encrypted, what would stop the DBA from replacing
the user's password with his own (encrypted) password, then logging in
and viewing the user's info, then copying back the original encrypted
password? Or, adding a new user for himself with whatever
permissions he chooses?

View Replies !

Prevent Other Users From Changing Data Of A Table
hi..

How do i prevent other users from changing the data of my tables? Means one can change data using only my login rest others cannot even DBA or also from server administrator

View Replies !

Does Db_denydatawriter Prevent Procs From Updating Data?

If a user is a member of a role which would allow him to execute a proc which updates a table, and he is then granted db_denydatawriter , can he still update the table through the proc? SS2000 SP4.

Thanks,

michael

Since posting this I tested the scenario and YES! The user was still able to execute an update proc even though the user excuting the proc was granted db_denydatawriter.

View Replies !

Deadlocks In Tempdb With Insert Exec SQL 6.5
Hi,

has anyone come across deadlocks on sysindexes in tempdb where the insert/exec combination is used.

eg

create table #fred (IntColumn int)

insert into #fred exec ProcThatSelectsAnIntColumn

This is being done in a stored procedure, and is deadlocking with other procs which are doing vanilla #table work - creating, inserting into, updatind, selecting from, etc.

I have noticed similar deadlocks where a #table is created inside an explict transaction, and I wondered whether there is an implicit transaction created, but @@Nestlevel is not changing either before or after the insert/exec.

I can't find any references in knowledgebase.

Any pointers appreciated.

Cheers
Simon
________________________
Simon Davis
Bankers Trust Australia Limited
Asset Management Technology
Ph: 61 2 9259 9137
<mailto:Simon.Davis@Bankerstrust.com.au>

View Replies !

Prevent Insert && Update If Field &&"archived&&" = True
Hi,

I've got a table containing calculated values, so i created a field named "archived" (bit datatype) on this table, to prevent the values to be updated if this field is set to true.

Is it possible to created a constraint, to prevent the row to be updated if ARCHIVED=true ? How can i do it ?

View Replies !

Stored Procs: How To Prevent Return Of Uneccesary Data?
Hi, I have a stored procedure that looks like this:...WHILE @@FETCH_STATUS = 0BEGINDECLARE @MyCount int ; EXEC spLoanQuestionnaireCriteria @AuditSelectedLoanID, @Criteria, @MyCount OUTPUTEND ...SELECT * FROM #Categories... Executing this stored procedure will return me 1 table for each time the EXEC statement is called that only has on column (MyCount)I really don't need this data to be returned, it is only used for some internal calculations in the stored procedureThe stored procedure should only return the results from SELECT * FROM #Categories in 1 table.Is there a Keyword I can use to exclude the EXEC results being returned to the dataset? Thanks in advance,Andre

View Replies !

Large Insert Causing Problem With TempDB
Hello,
I have an SSIS package that basically inserts a large amount of data into a SQL Server table. The table contains sixty five columns, and a single load of data can contain two million records.

The 'loads' are split up into several 'daily' flat files. The package uses a ForEachFile loop to process each of the files. As each file is processed, the data from the files is loaded into a SQL Server table (destination).

Apparently, as the package is running, tempDB begins to consume a lot of disk space. The data file for TempDB on this particular server is configured to grow in 50mb increments with unrestricted file growth. During the last run of the package, the data file grew to 17GB. I ran the following and got the data file size down to 50mb;

USE TempDb

GO

DBCC SHRINKFILE(tempdev, 1)

Should I consider incorporating this code as part of the package, or is there something else I should consider to configure the SSIS package so that I don't run into space problems with TempDB?

Thank you for your help!

cdun2

View Replies !

How To Prevent System Administrator To View And Edit A Database Structure And Data
I represent a software development house and we have developed a client server system based on SQL Server. Most of our customers have already purchased Enterprise License of SQL Server, therefore they own the SA Login and Password. We are bound to attach our Database with their Server on their machine.

My question is how can we stop a System Administrator of SQL Server to view our Database Structure, Queries, Data installed on their SQL Server on their machine.

Our database structure is a trade secret and we cant reveal the structure to the client.

please answer this question by email to me at farhandotcom@gmail.com

Thanks & Regards
Farhan

View Replies !

Extract Data In &"Insert Into...&" Statement Format
Is there a way in SQL Server 2000 to extract data from a table, such thatthe result is a text file in the format of "Insert Into..." statements, i.e.if the table has 5 rows, the result would be 5 lines of :insert into Table ([field1], [field2], .... VALUES a,b,c)insert into Table ([field1], [field2], .... VALUES d, e, f)insert into Table ([field1], [field2], .... VALUES g, h, i)insert into Table ([field1], [field2], .... VALUES j, k, l)insert into Table ([field1], [field2], .... VALUES m, n, o)Thanks in advance

View Replies !

Strange Problem: SQL Insert Statement Does Not Insert All The Fields Into Table From Asp.net C# Webpage
An insert statement was not inserting all the data into a table. Found it very strange as the other fields in the row were inserted. I ran SQL profiler and found that sql statement had all the fields in the insert statement but some of the fields were not inserted. Below is the sql statement which is created dyanmically by a asp.net C# class. The columns which are not inserted are 'totaltax' and 'totalamount' ...while the 'shipto_name' etc...were inserted.there were not errors thrown. The sql from the code cannot be shown here as it is dynamically built referencing C# class files.It works fine on another test database which uses the same dlls. The only difference i found was the difference in date formats..@totalamount=1625.62,@totaltax=125.62are not inserted into the database.Below is the statement copied from SQL profiler.exec sp_executesql N'INSERT INTO salesorder(billto_city, billto_country, billto_line1, billto_line2, billto_name,billto_postalcode, billto_stateorprovince, billto_telephone, contactid, CreatedOn, customerid, customeridtype,DeletionStateCode, discountamount, discountpercentage, ModifiedOn, name, ordernumber,pricelevelid, salesorderId, shipto_city, shipto_country,shipto_line1, shipto_line2, shipto_name, shipto_postalcode, shipto_stateorprovince,shipto_telephone, StateCode, submitdate, totalamount,totallineitemamount, totaltax ) VALUES(@billto_city, @billto_country, @billto_line1, @billto_line2,@billto_name, @billto_postalcode, @billto_stateorprovince, @billto_telephone, @contactid, @CreatedOn, @customerid,@customeridtype, @DeletionStateCode, @discountamount,@discountpercentage, @ModifiedOn, @name, @ordernumber, @pricelevelid, @salesorderId,@shipto_city, @shipto_country, @shipto_line1, @shipto_line2,@shipto_name, @shipto_postalcode, @shipto_stateorprovince, @shipto_telephone,@StateCode, @submitdate, @totalamount, @totallineitemamount, @totaltax)',N'@billto_city nvarchar(8),@billto_country nvarchar(13),@billto_line1 nvarchar(3),@billto_line2 nvarchar(4),@billto_name nvarchar(15),@billto_postalcode nvarchar(5),@billto_stateorprovince nvarchar(8),@billto_telephone nvarchar(3),@contactid uniqueidentifier,@CreatedOn datetime,@customerid uniqueidentifier,@customeridtype int,@DeletionStateCode int,@discountamount decimal(1,0),@discountpercentage decimal(1,0),@ModifiedOn datetime,@name nvarchar(33),@ordernumber nvarchar(18),@pricelevelid uniqueidentifier,@salesorderId uniqueidentifier,@shipto_city nvarchar(8),@shipto_country nvarchar(13),@shipto_line1 nvarchar(3),@shipto_line2 nvarchar(4),@shipto_name nvarchar(15),@shipto_postalcode nvarchar(5),@shipto_stateorprovince nvarchar(8),@shipto_telephone nvarchar(3),@StateCode int,@submitdate datetime,@totalamount decimal(6,2),@totallineitemamount decimal(6,2),@totaltax decimal(5,2)',@billto_city=N'New York',@billto_country=N'United States',@billto_line1=N'454',@billto_line2=N'Road',@billto_name=N'Hillary Clinton',@billto_postalcode=N'10001',@billto_stateorprovince=N'New York',@billto_telephone=N'124',@contactid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@CreatedOn=''2008-04-18 13:37:12:013'',@customerid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@customeridtype=2,@DeletionStateCode=0,@discountamount=0,@discountpercentage=0,@ModifiedOn=''2008-04-18 13:37:12:013'',@name=N'E-Commerce Order (Before billing)',@ordernumber=N'BRKV-CC-OKRW5764YS',@pricelevelid='B74DB28B-AA8F-DC11-B289-000423B63B71',@salesorderId='9CD0E11A-5A6D-4584-BC3E-4292EBA6ED24',@shipto_city=N'New York',@shipto_country=N'United States',@shipto_line1=N'454',@shipto_line2=N'Road',@shipto_name=N'Hillary Clinton',@shipto_postalcode=N'10001',@shipto_stateorprovince=N'New York',@shipto_telephone=N'124',@StateCode=0,@submitdate=''2008-04-18 14:37:10:140'',@totalamount=1625.62,@totallineitemamount=1500.00,@totaltax=125.62

thanks

View Replies !

How To Write Query To Insert 10,000 Rows In A Table Using Insert Statement One Time
create table and inserting 10,000 row values at a time using single insert statment

ex: I want to create table employee having two coloumns
like employeeid , name

note : name can be static i.e : same name for 10,000 rows

employeeid is unique

can any one write a query for me?

View Replies !

Case Statement Error In An Insert Statement
Hi All,
I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance.
My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.

Here is my code:
Insert into myTblA
(TblA_ID,
mycasefield =
case
when mycasefield = 1 then 99861
when mycasefield = 2 then 99862
when mycasefield = 3 then 99863
when mycasefield = 4 then 99864
when mycasefield = 5 then 99865
when mycasefield = 6 then 99866
when mycasefield = 7 then 99867
when mycasefield = 8 then 99868
when mycasefield = 9 then 99855
when mycasefield = 10 then 99839
end,
alt_min,
alt_max,
longitude,
latitude
(
Select MTB.LocationID
MTB.model_ID
MTB.elevation, --alt min
null, --alt max
MTB.longitude, --longitude
MTB.latitude --latitude
from MyTblB MTB
);

The error I'm getting is:
Incorrect syntax near '='.

I have tried various versions of the case statement based on examples I have found but nothing works.
I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.

View Replies !

Tempdb LOG - DATA ?
Hi:

Is necessary to have log and data in tempdb database? I ask this because some provider recomend
to have 300Mb in data and 200Mb in logs, but we need to know which is the result of have this
settings in tempdb instead of have 500Mb of logs or 500Mb of data.

View Replies !

Insert Statement Which Uses A Return Value From An SP As An Insert Value
I'm quite stuck with this:I have an import table called ReferenceMatchingImport which containsdata that has been sucked from a data submission. The contents ofthis table have to be imported into another table ExternalReferencewhich has various foreign keys.This is simple but one of these keys says that the value inExternalReference.CompanyRef must be in the CompanyReference table.Of course if this is an initial import then it will not be so as partof my script I must insert a new row into CompanyReference andpopulate ExternalReference.CompanyRef with the identity column of thistable.I thought a good idea would be to use an SP which inserts a new rowand returns @@Identity as the value to insert. However this doesn'twork as far as I can tell. Is there a approved way to perform thissort of opperation? My code is below.Thanks.ALTER PROCEDURE SP00ReferenceMatchingImportAS/*Just some integrity checking going on here*/INSERT ExternalReference(ExternalSourceRef,AssetGroupRef,CompanyUnitRef,EntityTypeCode,CompanyRef, --this is the unknown ref which is returned by the spExternalReferenceTypeCode,ExternalReferenceCompanyReferenceMapTypeCode,StartDate,EndDate,LastUpdateBy,LastUpdateDate)SELECT rmi.ExternalDataSourcePropertyRef,rmi.AssetGroup,rmi.CompanyUnit,rmi.EntityType,SP01InsertIPDReference rmi.EntityType, --here I'm trying to run thesp so that I can use the return value as the insert value1,1,GETDATE(),GETDATE(),'RefMatch',GETDATE()FROM ReferenceMatchingImport rmiWHERE rmi.ExternalDataSourcePropertyRef NOT IN (SELECT ExternalSourceRefFROM ExternalReference)

View Replies !

Data Caching Tempdb When It's In RAM.
We are building a large database on 6.5 (40-50G). We are loading the box
with four Xeon Processors and multiple Compaq Smart 2 controllers in an
effort to keep the processors fed. We are planning to run NT Ent. and SQL
Ent. editions and putting 3G of RAM in the box. Here's a key question I'm
tossing to the guru's for input (any and all input appreciated):

We may add a third-party RAM disk product to go beyond 3G and placing tempdb
in RAM. Our thinking is, since this is a Financial Package, it's likely to
behave like OLTP for 20 days a month and DSS during the other 10 days...lots
and lots of report gyrations at EOM. If we place tempdb in RAM, is the data
ALSO cached in SQL Servers data caches for reads/writes to tempdb?

Anyone priced SSD's lately?

Any and all help appreciated

Thanks,

Marc

Marc Stewart
Senior DBA
Brobeck, Phelgar and Harrison, LLP
Marc_Stewart@mindspring.com

View Replies !

Inaccurate Data For Tempdb
Presently, we have allocated 600MB to tempdb and when I run the sp_spaceused command for tempdb I notice that the unallocated space is a negative number. I have not received any errors in the error log with regards to expanding the segment because segment is full. Is this just a bug in SQL 6.5 or something else?

View Replies !

Interaction Between &&"instead Of Insert&&" Trigger And Output Clause Of Insert Statement

This problem is being seen on SQL 2005 SP2 + cumulative update 4

I am currently successfully using the output clause of an insert statement to return the identity values for inserted rows into a table variable

I now need to add an "instead of insert" trigger to the table that is the subject of the insert.

As soon as I add the "instead of insert" trigger, the output clause on the insert statement does not return any data - although the insert completes successfully. As a result I am not able to obtain the identities of the inserted rows

Note that @@identity would return the correct value in the test repro below - but this is not a viable option as the table in question will be merge replicated and @@identity will return the identity value of a replication metadata table rather than the identity of the row inserted into my_table

Note also that in the test repro, the "instead of insert" trigger actually does nothing apart from the default insert, but the real world trigger has additional code.

To run the repro below - select each of the sections below in turn and execute them
1) Create the table
2) Create the trigger
3) Do the insert - note that table variable contains a row with column value zero - it should contain the @@identity value
4) Drop the trigger
5) Re-run the insert from 3) - note that table variable is now correctly populated with the @@identity value in the row

I need the behaviour to be correct when the trigger is present

Any thoughts would be much appreciated

aero1

/************************************************
1) - Create the table
************************************************/
CREATE TABLE [dbo].[my_table](
      [my_table_id] [bigint] IDENTITY(1,1) NOT NULL,
      [forename] [varchar](100) NULL,
      [surname] [varchar](50) NULL,
CONSTRAINT [pk_my_table] PRIMARY KEY NONCLUSTERED
(
      [my_table_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 70) ON [PRIMARY]
)

GO
/************************************************
2) - Create the trigger
************************************************/
CREATE TRIGGER [dbo].[trig_my_table__instead_insert] ON [dbo].[my_table]
INSTEAD OF INSERT
AS
BEGIN

      INSERT INTO my_table
            (
            forename,
            surname)
      SELECT
            forename,
            surname
      FROM inserted

END

/************************************************
3) - Do the insert
************************************************/

DECLARE @my_insert TABLE( my_table_id     bigint )

declare                 @forename                     VARCHAR(100)
declare                 @surname                      VARCHAR(50)

set         @forename = N'john'
set         @surname = N'smith'

INSERT INTO my_table (
                                    forename
                                    , surname
                                    )
OUTPUT inserted.my_table_id INTO @my_insert
VALUES(     @forename
            , @surname
            )

select @@identity -- expect this value in @my_insert table
select * from @my_insert -- OK value without trigger - zero with trigger

/************************************************
4) - Drop the trigger
************************************************/

drop trigger [dbo].[trig_my_table__instead_insert]
go

/************************************************
5) - Re-run insert from 3)
************************************************/
-- @my_insert now contains row expected with identity of inserted row
-- i.e. OK

View Replies !

How Do I Prevent An Insert Into Statement To Increase Tempdb Data Files So Much

I'm running this procedure which insert into table_name(id, name.....) select id, name.... from table_name. For some reason the tempdb data file grow up to 200GB. The tempdb is set to expand unrestricted by 10%. How can I prevent that from hapening? Thanks.