problem with If Exist select

Have patience, I'm just a script kiddie. I'm trying to write a
vbsscript that queries a sql 2005 database to see if a record exists
and if so update some values and if it doesn't then insert an entry. On
the If Exists(Select * FROM HDW WHERE UserID = " & strID & ") line I'm
getting the following error:
Char: 11
Error: Syntax error
Code: 800a03ea
Can anyone tell me what I'm doing wrong.
Thanks.
Set objCon = CreateObject("adodb.connection")
objCon.Open("Driver={SQL Server}; Server=NDS-SQL2005; Database=User;
uid=something; pwd=something")
on error resume Next
If Exists(Select * FROM HDW WHERE UserID = " & strID & ")
strSQL = "UPDATE HDW SET IPAddress = '" & strIP & "' AND (MAC = '" &
strMac & "') AND (Processor = " & strProc & ") And (Memory =" & strMem
& ")"
Else
strSQL = "insert into HDW(UserID, IPAddress, MAC, Processor, Memory) "
& _
"values ('" & strID & "', '" & strIP & "', '" & strMac & "', " &
strProc & ", " & strMem & ")"
End If
if err.number <> 0 then
msgbox err.description
end if
on error resume next
objCon.Execute(strSQL)
if err.number <> 0 then
msgbox err.description
end if
objCon.Closemcgrew.michael@.gmail.com wrote:
> Have patience, I'm just a script kiddie. I'm trying to write a
> vbsscript that queries a sql 2005 database to see if a record exists
> and if so update some values and if it doesn't then insert an entry. On
> the If Exists(Select * FROM HDW WHERE UserID = " & strID & ") line I'm
> getting the following error:
> Char: 11
> Error: Syntax error
> Code: 800a03ea
> Can anyone tell me what I'm doing wrong.
> Thanks.
>
> Set objCon = CreateObject("adodb.connection")
> objCon.Open("Driver={SQL Server}; Server=NDS-SQL2005; Database=User;
> uid=something; pwd=something")
> on error resume Next
> If Exists(Select * FROM HDW WHERE UserID = " & strID & ")
> strSQL = "UPDATE HDW SET IPAddress = '" & strIP & "' AND (MAC = '" &
> strMac & "') AND (Processor = " & strProc & ") And (Memory =" & strMem
> & ")"
> Else
> strSQL = "insert into HDW(UserID, IPAddress, MAC, Processor, Memory) "
> & _
> "values ('" & strID & "', '" & strIP & "', '" & strMac & "', " &
> strProc & ", " & strMem & ")"
>
instead of using if exits (select...........)
u should open a recordset of this SQL query and check condition....
let say if Rst as recorset..
then ur statement should be like this
if Rst.eof then
strSQL = "UPDATE HDW SET IPAddress = '" & strIP & "' AND (MAC =
'" &
strMac & "') AND (Processor = " & strProc & ") And (Memory =" &
strMem
& ")"
else
....|||Better yet, put stored procedures in your database and call them from VB.
Pass criteria using the parameters objects. Dynamic SQL can get you into
all kinds of problems.
Also, you can include all of this logic in a single stored procedure and
make one call to the database, rather than having the VB app checking all of
this logic on the client. You need to look at the type of logic you need to
enforce and determine whether to do this on the client app or the database,
but it is worth considering.
SQL Injection and Parameters:
http://www.sqlservercentral.com/col...ectionpart1.asp
http://www.sqlservercentral.com/col...qlinjection.asp
Dynamic SQL in stored procedures:
http://www.sommarskog.se/dynamic_sql.html
"SQL-Star (Rajeev Shukla)" <dreams.alot@.gmail.com> wrote in message
news:1145465365.635163.158830@.e56g2000cwe.googlegroups.com...
> mcgrew.michael@.gmail.com wrote:
> instead of using if exits (select...........)
> u should open a recordset of this SQL query and check condition....
> let say if Rst as recorset..
> then ur statement should be like this
> if Rst.eof then
> strSQL = "UPDATE HDW SET IPAddress = '" & strIP & "' AND (MAC =
> '" &
> strMac & "') AND (Processor = " & strProc & ") And (Memory =" &
> strMem
> & ")"
> else
> ....
>

Problem with deadocks

Hi list, iam very new to SQL. i have a developer that is making a very
complex query over a SQL2000 + SP3 database, with a lots of union queries.
He recieves this error:
Transaction (Process ID 97) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
I dont know how to proceed with this, how can try to discover the problem?
Could be a resources problem?
Thanks for the advices...Aswers in microsoft.public.sqlserver.server
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Tinchos" wrote:

> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...|||http://www.microsoft.com/communitie...80-e67d8812bd2d
AMB
"Tinchos" wrote:

> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...|||In your from statement use WITH (NOLOCKS) when you are selecting data.
Rick
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:B5CBFB12-BF8F-4BA8-B71A-8D9A893C66F6@.microsoft.com...
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...

Problem with Deadlocks

Hi list, iam very new to SQL. i have a developer that is making a very
complex query over a SQL2000 + SP3 database, with a lots of union queries.
He recieves this error:
Transaction (Process ID 97) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
I dont know how to proceed with this, how can try to discover the problem?
Could be a resources problem?
Thanks for the advices...
See if this helps:
Tracing Deadlocks
http://www.sqlservercentral.com/colu...gdeadlocks.asp
AMB
"Tinchos" wrote:

> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...
|||Hi
Have a look as this thread:
http://groups.google.ch/group/micros...1e5ad772e285b7
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Tinchos" wrote:

> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...
|||There is a lot of information about troubleshooting deadlocks in SQL Server
Books Online. Check out the following topics:
Troubleshooting Deadlocks
Troubleshooting Locking
Minimizing Deadlocks
Detecting and Ending Deadlocks
Deadlocking
Handling Deadlocks
SET DEADLOCK_PRIORITY
Deadlocks Involving Locks
Deadlocks Involving Parallelism
Deadlocks Involving Threads
Also see:
http://vyaskn.tripod.com/administration_faq.htm#q14
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:3EF15EC2-E654-4E76-A516-1B6BA8A7C5D3@.microsoft.com...
Hi list, iam very new to SQL. i have a developer that is making a very
complex query over a SQL2000 + SP3 database, with a lots of union queries.
He recieves this error:
Transaction (Process ID 97) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
I dont know how to proceed with this, how can try to discover the problem?
Could be a resources problem?
Thanks for the advices...
|||First some questions :
How often does the deadlock occure ?
Can you reproduce the deadlock at will ?
If the deadlock occures often, it will be easier to get more information,
with the profiler you can get more information by trapping the deadlock.
(See Troubleshooting Deadlocks, Using Trace Flag 1204 in Books on Line).
If the deadlock occures all the time, it is very likely that the application
is deadlocking itself. (E.g. by reading the data in one connection and
by writing the 'same' data in another connection.)
With deadlocks there are at least two processes involved. But both
can be from one application. If they are from different applications
and the deadlocks are (very) rare, you can build coding which can
recover from a deadlock. (Rollback the transaction and try the
same transaction again).
If the deadlocks are caused within a single applicatioin, then the
application has to be altered. (Because of different concurrency
systems some Oracle programmers working in SQL-server do
not realise that read actions can block write actions and can
therefore cause deadlocks within one application).
If the deadlock occurs between two applications and you can not
alter one application, but you can alter the other application, you
can set a preverence which application should be used as a
deadlock victim. (Set deadlock priority).
Good luck,
and keep us informed.
Ben Brugman
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:3EF15EC2-E654-4E76-A516-1B6BA8A7C5D3@.microsoft.com...
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...

Problem with Deadlocks

Hi list, iam very new to SQL. i have a developer that is making a very
complex query over a SQL2000 + SP3 database, with a lots of union queries.
He recieves this error:
Transaction (Process ID 97) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
I dont know how to proceed with this, how can try to discover the problem?
Could be a resources problem?
Thanks for the advices...See if this helps:
Tracing Deadlocks
http://www.sqlservercentral.com/columnists/skumar/tracingdeadlocks.asp
AMB
"Tinchos" wrote:
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...|||Hi
Have a look as this thread:
http://groups.google.ch/group/microsoft.public.sqlserver.programming/browse_thread/thread/6f618594592ab8d7/381e5ad772e285b7?lnk=st&q=tibor+deadlock&rnum=1&hl=en#381e5ad772e285b7
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Tinchos" wrote:
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...|||There is a lot of information about troubleshooting deadlocks in SQL Server
Books Online. Check out the following topics:
Troubleshooting Deadlocks
Troubleshooting Locking
Minimizing Deadlocks
Detecting and Ending Deadlocks
Deadlocking
Handling Deadlocks
SET DEADLOCK_PRIORITY
Deadlocks Involving Locks
Deadlocks Involving Parallelism
Deadlocks Involving Threads
Also see:
http://vyaskn.tripod.com/administration_faq.htm#q14
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:3EF15EC2-E654-4E76-A516-1B6BA8A7C5D3@.microsoft.com...
Hi list, iam very new to SQL. i have a developer that is making a very
complex query over a SQL2000 + SP3 database, with a lots of union queries.
He recieves this error:
Transaction (Process ID 97) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
I dont know how to proceed with this, how can try to discover the problem?
Could be a resources problem?
Thanks for the advices...|||First some questions :
How often does the deadlock occure ?
Can you reproduce the deadlock at will ?
If the deadlock occures often, it will be easier to get more information,
with the profiler you can get more information by trapping the deadlock.
(See Troubleshooting Deadlocks, Using Trace Flag 1204 in Books on Line).
If the deadlock occures all the time, it is very likely that the application
is deadlocking itself. (E.g. by reading the data in one connection and
by writing the 'same' data in another connection.)
With deadlocks there are at least two processes involved. But both
can be from one application. If they are from different applications
and the deadlocks are (very) rare, you can build coding which can
recover from a deadlock. (Rollback the transaction and try the
same transaction again).
If the deadlocks are caused within a single applicatioin, then the
application has to be altered. (Because of different concurrency
systems some Oracle programmers working in SQL-server do
not realise that read actions can block write actions and can
therefore cause deadlocks within one application).
If the deadlock occurs between two applications and you can not
alter one application, but you can alter the other application, you
can set a preverence which application should be used as a
deadlock victim. (Set deadlock priority).
Good luck,
and keep us informed.
Ben Brugman
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:3EF15EC2-E654-4E76-A516-1B6BA8A7C5D3@.microsoft.com...
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...

Problem with Deadlocks

Hi list, iam very new to SQL. i have a developer that is making a very
complex query over a SQL2000 + SP3 database, with a lots of union queries.
He recieves this error:
Transaction (Process ID 97) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
I dont know how to proceed with this, how can try to discover the problem?
Could be a resources problem?
Thanks for the advices...See if this helps:
Tracing Deadlocks
http://www.sqlservercentral.com/col...ngdeadlocks.asp
AMB
"Tinchos" wrote:

> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...|||Hi
Have a look as this thread:
e5ad772e285b7" target="_blank">http://groups.google.ch/group/micro...
e5ad772e285b7
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Tinchos" wrote:

> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...|||There is a lot of information about troubleshooting deadlocks in SQL Server
Books Online. Check out the following topics:
Troubleshooting Deadlocks
Troubleshooting Locking
Minimizing Deadlocks
Detecting and Ending Deadlocks
Deadlocking
Handling Deadlocks
SET DEADLOCK_PRIORITY
Deadlocks Involving Locks
Deadlocks Involving Parallelism
Deadlocks Involving Threads
Also see:
http://vyaskn.tripod.com/administration_faq.htm#q14
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:3EF15EC2-E654-4E76-A516-1B6BA8A7C5D3@.microsoft.com...
Hi list, iam very new to SQL. i have a developer that is making a very
complex query over a SQL2000 + SP3 database, with a lots of union queries.
He recieves this error:
Transaction (Process ID 97) was deadlocked on lock | communication buffer
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.
I dont know how to proceed with this, how can try to discover the problem?
Could be a resources problem?
Thanks for the advices...|||First some questions :
How often does the deadlock occure ?
Can you reproduce the deadlock at will ?
If the deadlock occures often, it will be easier to get more information,
with the profiler you can get more information by trapping the deadlock.
(See Troubleshooting Deadlocks, Using Trace Flag 1204 in Books on Line).
If the deadlock occures all the time, it is very likely that the application
is deadlocking itself. (E.g. by reading the data in one connection and
by writing the 'same' data in another connection.)
With deadlocks there are at least two processes involved. But both
can be from one application. If they are from different applications
and the deadlocks are (very) rare, you can build coding which can
recover from a deadlock. (Rollback the transaction and try the
same transaction again).
If the deadlocks are caused within a single applicatioin, then the
application has to be altered. (Because of different concurrency
systems some Oracle programmers working in SQL-server do
not realise that read actions can block write actions and can
therefore cause deadlocks within one application).
If the deadlock occurs between two applications and you can not
alter one application, but you can alter the other application, you
can set a preverence which application should be used as a
deadlock victim. (Set deadlock priority).
Good luck,
and keep us informed.
Ben Brugman
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:3EF15EC2-E654-4E76-A516-1B6BA8A7C5D3@.microsoft.com...
> Hi list, iam very new to SQL. i have a developer that is making a very
> complex query over a SQL2000 + SP3 database, with a lots of union queries.
> He recieves this error:
> Transaction (Process ID 97) was deadlocked on lock | communication buffer
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction.
> I dont know how to proceed with this, how can try to discover the problem?
> Could be a resources problem?
> Thanks for the advices...

Problem with Date query

Hi!

I'm trying to query an Informix linked server to import data into a SQL database.

The OLE DB Provider is ifxoledbc. I can do general queries such as

Select * from openquery(INFLINKMON, 'SELECT * FROM mydatabase:mytable_t ')

but when I try to add the where clause

Select * from openquery(INFLINKMON, 'SELECT * FROM mydatabase:mytable_t where mydatabase:mytable_t.d_thisdate = "04/12/2006"')

I get the error:

Msg 7321, Level 16, State 2, Line 1

An error occurred while preparing the query "SELECT * FROM mydatabase:mytable_t where mydatabase:mytable_t.d_thisdate="04/12/2006"" for execution against OLE DB provider "Ifxoledbc" for linked server "INFLINKMON".

Does anyone know what I am doing wrong? I can do a where query on other fields but not on date.

Thanks for any input that you can give.

Does the query run on the native system, without executing it through the linked server ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver0205.de
|||

Thanks for responding...

Yes, the query runs on the native system, unix, by typing the select query on the command line exactly the same as the select in single quotes SELECT * FROM mydatabase:mytable_t where mydatabase:mytable_t.d_thisdate = "04/12/2006" .

Thanks Again for any input!