Run-time Error Vs. Compile Error!

How to understand whether an error is a run-time error or a compile
error?
Thanks,
ArpanCan you be more specific with your question? A compile error is a syntax
error. A good way to check this is to press the little check mark button in
QA and see if it compiles. Different objects/batches have different compile
requirements. One the code compiles, the next thing is to build a plan, and
the execute. Errors that happen here are considered run-time errors.
Do you have a specific situation that concerns you?
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1123287800.195685.193030@.g44g2000cwa.googlegroups.com...
> How to understand whether an error is a run-time error or a compile
> error?
> Thanks,
> Arpan
>|||Thanks, Louis, for your response. So does compile errors ALWAYS mean
there are some syntax errors i.e. aren't there any other types of
errors (of course, excluding run-time errors) that are considered to be
compile errors?
No, my question is not concerned to any specific situation.
Thanks once again,
Regards,
Arpan|||See the following article by Erland Sommarskog, SQL Server MVP:
http://www.sommarskog.se/error-handling-I.html
(particulary the "Scope Abortion" section)
Razvan|||Definitely read Erland's stuff, but syntax, bad column names, (bad table
names can be ignored for delayed resolution to allow for temporary tables)
that sort of thing. Anything that makes your code unintelligible to SQL
Server. If SQL Server recognizes the syntax like you cannot say IF
condition THEN (it will squawk.) even though IF and THEN are valid in other
places.
Once it feels it likes the syntax, it runs it and everything else is
run-time in that the compiler didn't see the problem, but the query engine
does..
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1123294036.972006.25270@.g14g2000cwa.googlegroups.com...
> Thanks, Louis, for your response. So does compile errors ALWAYS mean
> there are some syntax errors i.e. aren't there any other types of
> errors (of course, excluding run-time errors) that are considered to be
> compile errors?
> No, my question is not concerned to any specific situation.
> Thanks once again,
> Regards,
> Arpan
>

Run-time error (R6009)

I installed a program that put Microsoft SQL Server on my system, Windows XP. The next day I tried to load a MSDOS Database program, Nutplus (Ultrapls), now I get a Run-Time error, R6009. The program will not load. Please help!!!!!, Thank you in advance.This seems to be a third party application and if your query is not related to SQL Server then approach vendor for suitable fix.

run-time error - 2147217887

This is one strange situation I am facing, in SQL 2005 server-V.B 6.0 combination.

Here rsReport is an ADODB.Recordset and capturing data by sql 2005 connection string of adUseClient as cursor location.

strSql = sql string

rsReport.Open strSQL, Cn, adOpenDynamic, adLockOptimistic

rsReport!EXTRAFOREOS = 0
--at this point it gives the error - run-time error '2147217887(80040e21)
Multiple step opertion generated errors - check each value status
rsReport.Update

But if I use sql 2000 it is working fine..

So please suggest some way to execute it in sql 2005.

thanks & regards,

francoread brett's sticky at the top of this board. need to see sql.sql

Run-time error

Hello All:
I am in desparate need of assistance. We are using an application called PaperVision Enterprise which takes scanned invoices and puts them into a database for the purpose of easy reference to any scanned invoice in the db. 2 weeks ago while trying to perform a 'match and merge' of the newly scanned docs, we received a Run-time error '-2147217871 (80040e31)": Timeout expired message. After being told by the company who supports this software that they couldn't help me since it is using a MS SQL Server Table Lookup I started googling the error with minimal success. I am not very proficient with SQL so I'm kind of stuck. I have narrowed this down to an ODBC run-time error, but looking for some kind of guidance as to how I can resolve it. Any info would be greatly appreciated. We are using MS SQL Server 2000.Timeout expired errors are bad to deal with. They are not particularly bad for the data, but they are a pain to track down. The short of it is the client is giving up on the server, after the server takes 30 seconds (default) to return a message about the success or failure of the command. The easiest fix is generally to have the vendor increase their commandtimeout value. Outside of that, you would have to check to see if it is time to purge old documents, reindex tables (if the database is on the order of 10GB or more), or check to see what other activity is going on on the SQL Server (is someone running a giant report at the same time?). Good luck.|||We actually faced this problem 4 years ago on SQL Server 2000. The issue was resolved first by increasing the timeout value in the ASP application code itself (as a temporary measure). However, after tuning the query so that it executed much faster, we switched back to the default timeout in the ASP application code. Everything went fine. If this is static data (committed data) that you are matching and merging, it would be better to use the WITH (NOLOCK) hint to start with. Of course, the query would still have to be optimized. Another case was when a query (with around 5 joins between different tables) was causing Exception Access Violation and failing. When optimized, it ran in a split second with no issues. [As per a Forrester Report (I don't have a copy of it with me right now), 50-60% of database performance issues are caused by the quality of the code.]|||I have increased the timeout value, but still have the same issues. As for the other suggestions, could you possibly recommend any books that would explain how to do some of these things, since I don't know much about SQL?|||I wouldn't use WITH (NOLOCK) unless you know with certainty that the tables involved are not being written to.

It has its own sets of problems since it means you will be doing dirty reads, and can even cause new errors if page splits, etc, occur during the read.

For tips on optimizing queries, start reading the beginner articles at http://www.sql-server-performance.com

Run-Time Error

Private Sub cmd_DeleteTables_Click()
MsgBox "Click yes when prompted to delete records"
Dim sqldel As String
sqldel = "DELETE tbl_physical_main.*, tbl_physical_main.[Material Number]
FROM tbl_physical_main WHERE (((tbl_physical_main.[Material Number]) Is Not
Null))"
DoCmd.RunSQL sqldel, no
sqldel = "DELETE tbl_noDataForTag.* FROM tbl_noDataForTag"
DoCmd.RunSQL sqldel, no
sqldel = "DELETE tbl_SAPmaterials.* FROM tbl_SAPmaterials"
DoCmd.RunSQL sqldel, no
MsgBox "All previous inventory data has been deleted and the next inventory
load may continue."
End Sub
--
Run-Time Error '94'
Invalid use of Null
Please help me!

On the first delete, try removing the tbl_physical_main.[Material Number] from the delete section of the query.

BobP

|||sqldel = "DELETE FROM tbl_physical_main WHERE (((tbl_physical_main.[Material Number]) Is Not
Null))"|||

When you come upon these types of problems, prind out the commands that you are trying to execute without executing them. Then format them in a more readable manner, like:

DELETE tbl_physical_main.*,
tbl_physical_main.[Material Number]
FROM tbl_physical_main
WHERE (((tbl_physical_main.[Material Number]) Is Not Null))

DELETE tbl_noDataForTag.*
FROM tbl_noDataForTag

DELETE tbl_SAPmaterials.*
FROM tbl_SAPmaterials

Then try parsing/running them in SSMS or QA. The first problem is the bolded text. You delete from tables, not from columns. So:

DELETE tbl_physical_main
FROM tbl_physical_main
WHERE (((tbl_physical_main.[Material Number]) Is Not Null))

DELETE tbl_noDataForTag
FROM tbl_noDataForTag

DELETE tbl_SAPmaterials
FROM tbl_SAPmaterials

I would lose the superfluous parens too:

DELETE tbl_physical_main
FROM tbl_physical_main
WHERE tbl_physical_main.[Material Number] Is Not Null

I can't see any reason for the message (you should have gotten syntax errors) so you should be good.

run-time changeable queries

I need to extract and store a value from a table (or from a MS Access file with OpenDataSource) which is not always the same and it is therefore stored in the @.openfile variable. Something like this:
...
declare @.standardselect nvarchar(4000)
declare @.value int
select @.standardSelect='select top 1 @.value=val from ' + @.openfile
exec (@.standardSelect)
...

It obviously doesn't work because the variable @.value is not declared within the sql string.
However, since @.openfile is always different, I need to pass it through a string and the only way I know is within a variable. If I declare @.value inside the @.standardselect it is not accessible to the rest of the procedure, which is not acceptable for me.

Any suggestions?I have solved it using a temporary table, rather than a variable, where to store the value val. But if you have a better idea...|||

You can use EXEC statement

@.sSQL = 'select...from ' + @.tablename + ' where ...'
Exec @.sSQL

However this is a bad practice.

|||Andranik Khachatryan, I guess you haven't read the code in my first message?

Andranik Khachatryan wrote:

You can use EXEC statement

@.sSQL = 'select...from ' + @.tablename + ' where ...'
Exec @.sSQL

However this is a bad practice.

|||

Oops sorry :)

My bad, I am a bit careless today.

|||

You could use sp_executesql with output parameters to do this.

Declare @.StandardSelect nvarchar(4000)
Declare @.Value Int

Select @.StandardSelect = 'Select top 1 @.Value=Id From ' + @.OpenFile
exec sp_executesql @.StandardSelect, N'@.Value int output', @.value OUTPUT

Select @.value

|||

You don't really need dynamic SQL for doing this. You can do below instead:

declare @.source varchar(30)

-- ... initialize based on whether you want to query table or Access

set @.source =

declare @.value int

set @.value = (

select top 1 val from (

select val from your_table where @.source = 'table'

union all

select val from opendatasource(...) where @.source = 'access'

) as t

order by ...

)