Help with SQL query in script

I am getting an eror on my SQL query string as shown below and I'm not
sure why the syntax is wrong....

ERROR: Microsoft OLE DB Provider for SQL Server: Line 12: Incorrect
syntax near 'item'

CODE:
Str_Connect = "Provider=SQLOLEDB;Data
Source=SQLSERVER;Trusted_Connection=Yes;Initial Catalog=assystDev2"
Set CnnSQL=CreateObject("ADODB.Connection")
CnnSQL.Open Str_Connect

If CnnSQL.State=1 Then
Wscript.Echo "Connected"
else
Wscript.Echo "Not Connected"
End If
Set Rs=CreateObject("ADODB.Recordset")
' ********** This is the line that produces the ERROR
***********
Set Rs=CnnSQL.Execute("SELECT item.item_n, item.expiry_date,
usr.email_add" & _
" FROM assyst.dbo.item item INNER JOIN assyst.dbo.usr usr" & _
"ON item.usr_id=usr.usr_id" & _
" WHERE (item.expiry_date>=(ts '2008-01-01 00:00:00') AND
item.expiry_date<(ts '2008-03-01 00:00:01'))")
Do while not Rs.EOF
Wscript.Echo (Rs(0) & space(3) & Rs(1) & space(3) & Rs(2) & space(3) &
Rs(3))
Rs.MoveNext
Loop

OVERVIEW: I need to run a daily report of contract expirations for
the next month and email the contract holder with that information.
This is being done manually using the following query:

SELECT "item"."item_n", "item"."expiry_date", "usr"."email_add"
FROM "assyst"."dbo"."item" "item" INNER JOIN "assyst"."dbo"."usr"
"usr" ON "item"."usr_id"="usr"."usr_id"
WHERE ("item"."expiry_date">={ts '2008-01-01 00:00:00'} AND
"item"."expiry_date"<{ts '2008-03-01 00:00:01'})

It returns the following:
Item_n expiry_date email_add


I have noticed that some of the records returned have some blank
(null?) data .
I also want to replace the hard coded dates with today and (today
+30) dates.

Thanks for your help!

Bob
Tue Apr 22 12:39:16 CDT 2008

Cmor wrote:
> I am getting an eror on my SQL query string as shown below and I'm not
> sure why the syntax is wrong....
>
> ERROR: Microsoft OLE DB Provider for SQL Server: Line 12: Incorrect
> syntax near 'item'
>
> CODE:
<snip>
> Set Rs=CreateObject("ADODB.Recordset")
> ' ********** This is the line that produces the ERROR
> ***********

You cannot debug a sql statement without knowing what it contains. In
your case, because you are using SQL Server, you have the option of
starting a trace using SQL Profiler to discover the actual statement
resulting from your vbscript statement below. The other option is to
look at it via vbscript. Start by assigning it to a variable:

sql="SELECT item.item_n, item.expiry_date,usr.email_add" & _
FROM assyst.dbo.item item INNER JOIN assyst.dbo.usr usr" & _
"ON item.usr_id=usr.usr_id" & _
" WHERE (item.expiry_date>=(ts '2008-01-01 00:00:00') AND " & _
"item.expiry_date<(ts '2008-03-01 00:00:01'))")

Then echo it, or write it to a text file.

'******comment out this line when everything works*******
wscript.echo sql
'*****************************************************************

Set Rs=CnnSQL.Execute(sql,,1)

If your vbscript is correct, you should be able to copy the sql
statement from the dialog or text file and run it without modification
in SSMS or Query Analyzer, depending on which version of SQL Server you
are using.



--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Tim
Tue Apr 22 21:12:20 CDT 2008

> " FROM assyst.dbo.item item INNER JOIN assyst.dbo.usr usr" & _
> "ON item.usr_id=usr.usr_id" & _

Need a space between "usr" and "ON"

Tim

"Cmor" <cmor1701d@gmail.com> wrote in message
news:aac2cd20-3558-485b-80ef-40f3ec0aefb2@d45g2000hsc.googlegroups.com...
>I am getting an eror on my SQL query string as shown below and I'm not
> sure why the syntax is wrong....
>
> ERROR: Microsoft OLE DB Provider for SQL Server: Line 12: Incorrect
> syntax near 'item'
>
> CODE:
> Str_Connect = "Provider=SQLOLEDB;Data
> Source=SQLSERVER;Trusted_Connection=Yes;Initial Catalog=assystDev2"
> Set CnnSQL=CreateObject("ADODB.Connection")
> CnnSQL.Open Str_Connect
>
> If CnnSQL.State=1 Then
> Wscript.Echo "Connected"
> else
> Wscript.Echo "Not Connected"
> End If
> Set Rs=CreateObject("ADODB.Recordset")
> ' ********** This is the line that produces the ERROR
> ***********
> Set Rs=CnnSQL.Execute("SELECT item.item_n, item.expiry_date,
> usr.email_add" & _
> " FROM assyst.dbo.item item INNER JOIN assyst.dbo.usr usr" & _
> "ON item.usr_id=usr.usr_id" & _
> " WHERE (item.expiry_date>=(ts '2008-01-01 00:00:00') AND
> item.expiry_date<(ts '2008-03-01 00:00:01'))")
> Do while not Rs.EOF
> Wscript.Echo (Rs(0) & space(3) & Rs(1) & space(3) & Rs(2) & space(3) &
> Rs(3))
> Rs.MoveNext
> Loop
>
> OVERVIEW: I need to run a daily report of contract expirations for
> the next month and email the contract holder with that information.
> This is being done manually using the following query:
>
> SELECT "item"."item_n", "item"."expiry_date", "usr"."email_add"
> FROM "assyst"."dbo"."item" "item" INNER JOIN "assyst"."dbo"."usr"
> "usr" ON "item"."usr_id"="usr"."usr_id"
> WHERE ("item"."expiry_date">={ts '2008-01-01 00:00:00'} AND
> "item"."expiry_date"<{ts '2008-03-01 00:00:01'})
>
> It returns the following:
> Item_n expiry_date email_add
>
>
> I have noticed that some of the records returned have some blank
> (null?) data .
> I also want to replace the hard coded dates with today and (today
> +30) dates.
>
> Thanks for your help!

Cmor
Wed Apr 23 11:19:36 CDT 2008

Tim,

Nice catch. Thanks.
Of course now I get an error "Incorrect syntax near '2008-01-01
00:00:00 " The line is:
" WHERE (item.expiry_date>=3D(ts '2008-01-01 00:00:00') AND " & _
"item.expiry_date<(ts '2008-03-01 00:00:01'))")

Can you or anyone else help witht the correct syntax in VBS?

Thanks,

Seymour


On Apr 22, 10:12=A0pm, "Tim Williams" <timjwilliams at gmail dot com>
wrote:
> > " FROM assyst.dbo.item item INNER JOIN assyst.dbo.usr usr" & _
> > "ON item.usr_id=3Dusr.usr_id" & _
>
> Need a space between "usr" and "ON"
>
> Tim
>
> "Cmor" <cmor17...@gmail.com> wrote in message
>
> news:aac2cd20-3558-485b-80ef-40f3ec0aefb2@d45g2000hsc.googlegroups.com...
>
>
>
> >I am getting an eror on my SQL query string as shown below and I'm not
> > sure why the syntax is wrong....
>
> > ERROR: Microsoft OLE DB Provider for SQL Server: Line 12: Incorrect
> > syntax near 'item'
>
> > CODE:
> > Str_Connect =3D "Provider=3DSQLOLEDB;Data
> > Source=3DSQLSERVER;Trusted_Connection=3DYes;Initial Catalog=3DassystDev2=
"
> > Set CnnSQL=3DCreateObject("ADODB.Connection")
> > CnnSQL.Open Str_Connect
>
> > If CnnSQL.State=3D1 Then
> > Wscript.Echo "Connected"
> > else
> > Wscript.Echo "Not Connected"
> > End If
> > Set Rs=3DCreateObject("ADODB.Recordset")
> > ' =A0********** =A0 =A0 =A0 =A0This is the line that produces the ERROR
> > ***********
> > Set Rs=3DCnnSQL.Execute("SELECT item.item_n, item.expiry_date,
> > usr.email_add" & =A0_
> > " FROM assyst.dbo.item item INNER JOIN assyst.dbo.usr usr" & _
> > "ON item.usr_id=3Dusr.usr_id" & _
> > " WHERE (item.expiry_date>=3D(ts '2008-01-01 00:00:00') AND
> > item.expiry_date<(ts '2008-03-01 00:00:01'))")
> > Do while not Rs.EOF
> > Wscript.Echo (Rs(0) & space(3) & Rs(1) & space(3) & Rs(2) & space(3) &
> > Rs(3))
> > Rs.MoveNext
> > Loop
>
> > OVERVIEW: =A0I need to run a daily report of contract expirations for
> > the next month and email the contract holder with that information.
> > This is being done manually using the following query:
>
> > SELECT "item"."item_n", "item"."expiry_date", "usr"."email_add"
> > FROM =A0 "assyst"."dbo"."item" "item" INNER JOIN "assyst"."dbo"."usr"
> > "usr" ON "item"."usr_id"=3D"usr"."usr_id"
> > WHERE =A0("item"."expiry_date">=3D{ts '2008-01-01 00:00:00'} AND
> > "item"."expiry_date"<{ts '2008-03-01 00:00:01'})
>
> > It returns the following:
> > Item_n expiry_date email_add
>
> > I have noticed that some of the records returned have some blank
> > (null?) data .
> > I also want to replace the hard coded dates with =A0today and (today
> > +30) dates.
>
> > Thanks for your help!- Hide quoted text -
>
> - Show quoted text -

Cmor
Wed Apr 23 11:30:44 CDT 2008

I got the date format working so no errors in the script now. It runs
but returns no data.hmmmm. back to work on this one.

Thanks.



On Apr 23, 12:19=A0pm, Cmor <cmor17...@gmail.com> wrote:
> Tim,
>
> Nice catch. Thanks.
> Of course now I get an error "Incorrect syntax near '2008-01-01
> 00:00:00 " =A0The line is:
> =A0 =A0 =A0" WHERE (item.expiry_date>=3D(ts '2008-01-01 00:00:00') AND " &=
_
> =A0 =A0 =A0 "item.expiry_date<(ts '2008-03-01 00:00:01'))")
>
> Can you or anyone else help witht the correct syntax in VBS?
>
> Thanks,
>
> Seymour
>
> On Apr 22, 10:12=A0pm, "Tim Williams" <timjwilliams at gmail dot com>
> wrote:
>
>
>
> > > " FROM assyst.dbo.item item INNER JOIN assyst.dbo.usr usr" & _
> > > "ON item.usr_id=3Dusr.usr_id" & _
>
> > Need a space between "usr" and "ON"
>
> > Tim
>
> > "Cmor" <cmor17...@gmail.com> wrote in message
>
> >news:aac2cd20-3558-485b-80ef-40f3ec0aefb2@d45g2000hsc.googlegroups.com...=

>
> > >I am getting an eror on my SQL query string as shown below and I'm not
> > > sure why the syntax is wrong....
>
> > > ERROR: Microsoft OLE DB Provider for SQL Server: Line 12: Incorrect
> > > syntax near 'item'
>
> > > CODE:
> > > Str_Connect =3D "Provider=3DSQLOLEDB;Data
> > > Source=3DSQLSERVER;Trusted_Connection=3DYes;Initial Catalog=3DassystDe=
v2"
> > > Set CnnSQL=3DCreateObject("ADODB.Connection")
> > > CnnSQL.Open Str_Connect
>
> > > If CnnSQL.State=3D1 Then
> > > Wscript.Echo "Connected"
> > > else
> > > Wscript.Echo "Not Connected"
> > > End If
> > > Set Rs=3DCreateObject("ADODB.Recordset")
> > > ' =A0********** =A0 =A0 =A0 =A0This is the line that produces the ERRO=
R
> > > ***********
> > > Set Rs=3DCnnSQL.Execute("SELECT item.item_n, item.expiry_date,
> > > usr.email_add" & =A0_
> > > " FROM assyst.dbo.item item INNER JOIN assyst.dbo.usr usr" & _
> > > "ON item.usr_id=3Dusr.usr_id" & _
> > > " WHERE (item.expiry_date>=3D(ts '2008-01-01 00:00:00') AND
> > > item.expiry_date<(ts '2008-03-01 00:00:01'))")
> > > Do while not Rs.EOF
> > > Wscript.Echo (Rs(0) & space(3) & Rs(1) & space(3) & Rs(2) & space(3) &=

> > > Rs(3))
> > > Rs.MoveNext
> > > Loop
>
> > > OVERVIEW: =A0I need to run a daily report of contract expirations for
> > > the next month and email the contract holder with that information.
> > > This is being done manually using the following query:
>
> > > SELECT "item"."item_n", "item"."expiry_date", "usr"."email_add"
> > > FROM =A0 "assyst"."dbo"."item" "item" INNER JOIN "assyst"."dbo"."usr"
> > > "usr" ON "item"."usr_id"=3D"usr"."usr_id"
> > > WHERE =A0("item"."expiry_date">=3D{ts '2008-01-01 00:00:00'} AND
> > > "item"."expiry_date"<{ts '2008-03-01 00:00:01'})
>
> > > It returns the following:
> > > Item_n expiry_date email_add
>
> > > I have noticed that some of the records returned have some blank
> > > (null?) data .
> > > I also want to replace the hard coded dates with =A0today and (today
> > > +30) dates.
>
> > > Thanks for your help!- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -