473,320 Members | 2,147 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

INSERT INTO - Data is not inserted

hi there

Created sproc - it stops dead in the first line

Why ????

Thanks in advance

CREATE PROCEDURE [dbo].[test] AS

insert into timesheet.dbo.table1 (RE_Code, PR_Code, AC_Code, WE_Date,
SAT, SUN, MON, TUE, WED, THU, FRI, NOTES, GENERAL, PO_Number,
WWL_Number, CN_Number)
SELECT RE_Code, PR_Code, AC_Code, WE_Date, SAT, SUN, MON, TUE,
WED, THU, FRI, NOTES, GENERAL, PO_Number, WWL_Number, CN_Number
FROM dbo.WWL_TimeSheets
WHERE (RE_Code = 'akram.i') AND (WE_Date = CONVERT(DATETIME,
'1999-12-03 00:00:00', 102))
GO
Jul 20 '05 #1
6 2896
ImraneA (i.*****@weir.co.uk) writes:
Created sproc - it stops dead in the first line

Why ????

Thanks in advance

CREATE PROCEDURE [dbo].[test] AS

insert into timesheet.dbo.table1 (RE_Code, PR_Code, AC_Code, WE_Date,
SAT, SUN, MON, TUE, WED, THU, FRI, NOTES, GENERAL, PO_Number,
WWL_Number, CN_Number)
SELECT RE_Code, PR_Code, AC_Code, WE_Date, SAT, SUN, MON, TUE,
WED, THU, FRI, NOTES, GENERAL, PO_Number, WWL_Number, CN_Number
FROM dbo.WWL_TimeSheets
WHERE (RE_Code = 'akram.i') AND (WE_Date = CONVERT(DATETIME,
'1999-12-03 00:00:00', 102))
GO

Didn't you post a question the other day, which I replied to? This
looks like a shortened version of the same procedure.

In any case, it is impossible to answer, unless your provide more
information. Do you get any error message? If you run the SELECT
without INSERT, do you get any data? Is there any trigger on the
table?

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
Hi there again

Been having a VERY VERY frustrating time with this sproc - not thinking
straight.

Question :-

Been using debugger throu Query Analyzer to check sproc.
Now I wish to call sproc throu Query Analyzer. Having a problem with
that. i.e How do I call it ??

recap :-
CREATE PROCEDURE procTimesheetInsert(
@TimesheetDetails varchar(1000) = NULL,
@TimesheetID int = NULL OUTPUT,
@RetCode int = NULL OUTPUT,
@RetMsg varchar(100) = NULL OUTPUT)

----
EXEC procTimesheetInsert @TimesheetDetails ="data.....",
@TimesheetID = NULL,
@RetCode = NULL,
@RetMsg = NULL)

Do I need to declare anything here ???

N.B.
1.Test "Table" used - no triggers or relationships etc..


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #3
Three things I noticed in the SP call (it may have just been typos from
your posting):

1) Use should be using single quotes (') not double quotes (") to
deliminate a string

2) You have an extra end parent at the end of your SP call

3) You have three of your SP parameters declared as OUTPUT, if you
expect to actually get this data back from the SP you need to assign the
Parameter to a Variable within the SP call, and then identify the
parameter as OUTPUT in the SP call. Such as

DECLARE @TS_OUT INT,
@RetCodeOut INT,
@RetMsgOut VARCHAR(100)

EXEC procTimesheetInsert @TimesheetDetails ='data.....',
@TimesheetID = @TS_OUT OUTPUT,
@RetCode = @RetCodeOut OUTPUT,
@RetMsg = @RetMsgOut OUTPUT
Although not doing #3 will not cause an error, just defeats the purpose
of the OUTPUT parameters.

HTH,
Matt
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #4

I give it try.

Thanks.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #5
Matt Rellick (ti**@yahoo.com) writes:
1) Use should be using single quotes (') not double quotes (") to
deliminate a string


However, Imrane's string includes a whole bunch of single quotes
itself. While you always can include single quotes in a literal by
doubling them:

DECLARE @str varchar(10)
SELECT @str = 'It isn''t'
SELECT @str

I would recommend Imrane to make use of the setting QUOTED IDENTIFIER:

SET QUOTED_IDENTIFIER OFF
go
EXEC the_sp @testdata="'HARLEY.I',03004,'A000-AA00'..."

In fact, when I tested his procedure, this was precisly what I did.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #6

Thanks for comments - did the job.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: DTB | last post by:
I am having trouble creating an INSTEAD OF trigger in SQL Server to replicate a BEFORE UPDATE trigger from ORACLE. Here is a sample of the ORACLE BEFORE UPDATE trigger: CREATE TRIGGER myTRIGGER ON...
2
by: george | last post by:
This is like the bug from hell. It is kind of hard to explain, so please bear with me. Background Info: SQL Server 7.0, on an NT box, Active Server pages with Javascript, using ADO objects. ...
6
by: Mark P | last post by:
Some time ago I posted here about inserting into a set with a hint: ...
16
by: Philip Boonzaaier | last post by:
I want to be able to generate SQL statements that will go through a list of data, effectively row by row, enquire on the database if this exists in the selected table- If it exists, then the colums...
10
by: Mike | last post by:
I know this sounds strange but I am at a loss. I am calling a simple funtion that opens a connection to a SQL Server 2000 database and executes an Insert Statement. private void...
2
by: Geoffrey KRETZ | last post by:
Hello, I'm wondering if the following behaviour is the correct one for PostGreSQL (7.4 on UNIX). I've a table temp_tab with 5 fields (f1,f2,f3,...),and I'm a launching the following request :...
3
by: teddysnips | last post by:
I need a trigger (well, I don't *need* one, but it would be optimal!) but I can't get it to work because it references ntext fields. Is there any alternative? I could write it in laborious code...
2
by: leedo | last post by:
Hi, I am almost going crazy with this. I have a table that I bulk insert into from another database using VS2005. I need to change the data in the records before committing the values in the...
0
by: magnolia | last post by:
i created a trigger that will record the changes made to a table .everything works fine except the insert query.whenerever i try to insert a record it fires insert and update triger at the same time...
7
by: anu b | last post by:
Hi I need to use Clr trigger for insert command My code is as below I am using SQL server 2005 and VS 2008.... but after running this code i didnt get the result as i expexted it shows the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.